Cleveland DBA

SQL Server administration in Cleveland, OH

Available Workspace Memory

My research into the Resource Governor question and its answer brought up another question for me:

SELECT * FROM sys.dm_exec_query_resource_semaphores

Where does that total_memory_kb number for the default pool actually come from? After all, I have a max server memory of 26000MB for the instance, so why 18175MB?  The BOL description for this field wasn’t very helpful.  Googling for total_memory_kb didn’t really yield me much.  Most of what I found was unrelated to this particular DMV.

So I started looking at the sys.dm_os_performance_counters, and there I finally found something that looked promising.

 select cntr_value FROM sys.dm_os_performance_counters 
WHERE object_name like '%Memory Manager%' and counter_name = 'Maximum Workspace Memory (KB)' 

If you add up the 2 total_memory_kb values for the default pool, you get the maximum workspace memory. Woot!

That still didn’t tell me how that particular value was derived, but it gave me something better to search on. And that search returned this article at which says (emphasis mine):

The SQL Server: Memory Manager object includes the following workspace-related PerfMon counters: Memory Grants Pending, which tells you the number of waiting queries; Memory Grants Outstanding, which counts the number of queries that have grants and are currently executing; Granted Workspace Memory (KB), which measures the total amount of grants SQL Server has made to running queries; and Maximum Workspace Memory (KB), which measures the maximum amount of memory SQL Server is currently willing to devote to workspace-memory grants. In general, SQL Server is able to devote between 25 and 75 percent of the buffer pool’s virtual memory to workspace memory. The value varies based on the demand for memory for other uses (such as database page cache and plan cache). If demand is low, SQL Server devotes a larger fraction of memory to workspace memory needs; if demand is high, SQL Server devotes a smaller fraction to workspace memory.

So there you have it. I feel better, don’t you feel better?

That entire article is worth reading for a better understanding of SQL Server memory. In fact, it helped me put together a bit of code to show me how memory is being used by SQL Server. More on that in a future post (I’ve got to give you a reason to keep coming back, right?).


April 28, 2011 Posted by | General, SQL Tuning, Troubleshooting | , | Leave a comment


I found the answer to my Resource Governor quandary in this outstanding article, Understanding SQL Server Memory Grant, on MSDN.  The juicy bit:

The server calculates the memory limit for one query. By default, this is 25% (20% on 32bit SQL 2005) of total query memory (which is set by memory broker as about 90% of server memory). This per-query limit helps to prevent one query from dominating the whole server. This percentage is configurable on SQL 2008.

And how is it configurable, you ask?  By using Resource Governor to override it.

Really though, read the article, it’s extremely informative.  I’d like to give the author a great big hug.

April 25, 2011 Posted by | Features, Troubleshooting | , , | 1 Comment

Resource Governor causing memory problem??

So.  Last week I blogged about the experiment I was running with Resource Governor for one of our newly upgraded development databases.  I had enabled Resource Governor and created a procedure to categorize sessions, but I hadn’t set any limits and everyone was sharing the same default pool.  The plan, if you’ll recall, was to monitor the Resource Governor stats to get a feel for the resources different apps were using.

This week, users have been doing a lot of testing in this environment.  A developer called me on Wednesday morning and said that users were complaining of very slow performance.  So I took a look and saw several sessions waiting on resource_semaphores.  Now, I’ve been supporting this application’s database for years now and I’ve never seen any memory waits, so this got my attention quick.  One of the sessions that was running was one of the reports that I’d enabled Resource Governor to address in the first place.  A look at sys.dm_exec_query_memory_grants told me this query was requesting almost 19GB of memory (or, put another way, all of the available memory).  What the…?

I captured the query (and asked the user to kill his report) and ran the query in our production (SQL 2005) database.  In that environment it was only requesting 7GB of memory.  Still a lot, but do-able in a system with 40GB available.  The query plans were slightly different between the 2 environments, but updating stats didn’t change that at all.  I decided to use Resource Governor as it was intended, and I set a 40% memory grant limit for the Reports workload group.  I re-ran the query and this time it was only requesting 7GB of RAM.

The immediate problem was remediated, but it niggled at the back of my brain; why had the query requested that much memory in the first place?  Thursday morning I got another call.  This time, when I checked the system, a different application was requesting all of the available memory.  I could have set a restriction for that workload group, too, but this was just treating the symptoms.  There had to be a root cause.

I started searching the Great Gazoogle for known memory issues in SQL 2008, but found nothing that explained why queries were suddenly using a lot more memory than they did in SQL 2005.  Think.  Think.  Think.  What if Resource Governor is the problem?  So I ran a series of tests:  with Resource Governor disabled, with it enabled and the memory limit set to 40%, and enabled with the memory limit set to 100% (or unlimited).  I also ran it in our 2005 environment just for comparison purposes, and I monitored sys.dm_exec_query_memory_grants for each test.  Here are the very interesting results.

Again I say:  what the…?  With Resource Governor disabled the query only uses 4.8GB of memory.  Enabling Resource Governor actually appears to cause the query to request more memory.  It almost looks like, rather than being a limit, the memory grant % is telling the query how much to use (putting ideas into its head).  I know that can’t be the case, but that’s how it looks.  I thought maybe I’d misunderstood the settings, that maybe I’d mis-configured Resource Governor, but I don’t think I did.

Memory Grant %
Specify the maximum amount of memory that a single request can take from the pool. Range is 0 to 100.

And technically, that’s what SQL Server did.  I said “this group can use 100% of the memory,” and it gave that query 100% of the memory.  Or tried to, anyway.  But if it really needed all that memory, why doesn’t it ask for it with Resource Governor disabled?

So far I haven’t been able to find any bugs to explain this behavior.  And a post to SQLServerCentral’s forums hasn’t yielded any responses.  I guess for now I’m going to leave it disabled and keep searching for an explanation.  If anyone out there has any insight, please post a comment.  I’d really appreciate it.

April 22, 2011 Posted by | Features, Troubleshooting | , , , | 1 Comment

Resource Governor – Getting started

One of the databases I’m responsible for is a fairly large OLTP system.  For the majority of the time, it runs great.  Unfortunately, there’s a select group of users who insist on using it as an OLAP system, running very large reports or queries during business hours and consuming a lot of system resources in the process.  There have been a few occasions where these reports have brought this very robust server to a crawl.  (We’ve gone over several options for offloading this reporting, but nothing was “real-time” enough for their use, so here we are.)

This year we’ll be upgrading this database to SQL Server 2008 and I’m very excited to make use of the Resource Governor feature.  I figure if I can’t get them off the system I can at least prevent them from killing the system, right?  But how do I know what resource limit(s) to set?  Pick some arbitrary number?  Probably not a good idea.  I want to control them, not strangle them.  So what I did was something recommended here, in Scenario 1.  I tweaked the code a little for my own use.

-- Create 3 workload groups based on the nature of their workload.
-- One handles ad hoc requests, the second handles reports, and the
-- third handles admin requests. These groups all use the default
-- settings for workload groups.
-- These workloads are divided into groups that cover ad hoc queries,
-- reports, and administration jobs.
-- Create a classification function.
-- Note that any request that does not get classified goes into
-- the 'default' group.
CREATE FUNCTION dbo.rgclassifier_v1() RETURNS sysname
 DECLARE @grp_name sysname
 SET @grp_name = 'GroupAdhoc'
 IF (SUSER_NAME() = 'sa')
 SET @grp_name = 'GroupAdmin'
 SET @grp_name = 'GroupReports'
 RETURN @grp_name
-- Register the classifier function with Resource Governor
-- Start Resource Governor

Notice I’m not setting any resource limits here.  The purpose is to categorize sessions into the appropriate group and monitor the normal resource usage.  I can see this usage by querying sys.dm_resource_governor_workload_groups.

USE master;
SELECT * FROM [sys].[dm_resource_governor_workload_groups]

In particular, I’m going to be keeping an eye on the max_request_cpu_time_ms and max_request_grant_memory_kb columns.  Once I get an idea of what they’re currently using, I’ll be better able to decide what limits to set.  At that point I can set resource restrictions within Resource Governor or I can choose to have an alert triggered if a limit is exceeded.  I know – very exciting stuff!

(But I’m even more excited about the prospect of AlwaysOn mirroring in SQL Server Denali for offloading these reports.  I mean, what’s more “real-time” than “real-time”??)


April 13, 2011 Posted by | Features | | 1 Comment

Composite indexes – Does column order matter?

When it comes to creating composite indexes, a common bit of advice we’re given is to put the most selective column first.  And I’ve always just taken this at face value.  But recently I got to thinking, is this still true?  I’d never actually tested this theory myself.  Does this rule still apply?

So let’s take a look.  I started by creating a test table in AdventureWorks using existing data.  My goal was to create a table with one high-cardinality column and at least one very low-cardinality column.

USE AdventureWorks;


ID int IDENTITY(1,1),

SELECT LastName, StateProvinceCode FROM Person.Contact CROSS JOIN Person.StateProvince ;

SELECT    COUNT(distinct ID) AS DistinctIDs,
 COUNT(DISTINCT surname) AS DistinctNames,
 COUNT(DISTINCT location) AS DistinctLocs
FROM myBigTable;

Perfect.  ID is unique and location is very low cardinality.  I’ll use those 2 columns in my test indexes.

CREATE INDEX i_highcard ON myBigTable (ID, location);  -- high cardinality first
CREATE INDEX i_lowcard ON myBigTable (location, ID);    -- low cardinality first

Before we go any further, let’s take a look at the physical size of the indexes, just to see if there’s any difference there.

select name, index_id from sys.indexes where object_id = OBJECT_ID('myBigTable')
SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('myBigTable'), NULL , NULL, 'LIMITED');

Judging by the fragment_count and page_count values, I’d say there’s no difference in size.  Now, on to query performance.  First we’ll query the table for a particular ID value.  I’ll use the INDEX hint to force the optimizer to use each index.

SELECT * FROM myBigTable WITH (INDEX = i_highcard) WHERE ID = 77321
SELECT * FROM myBigTable WITH (INDEX = i_lowcard) WHERE ID = 77321

Check out those execution plans.  When we query based on the first column in the index, the optimizer is able to do an index seek.  But when we query based on the second column in the index, as in the second query, the optimizer doesn’t know where to enter the index, so it’s forced to do an index scan.  (In fact, the optimizer even suggests we create an index on ID.)  We can also see the impact of these different execution plans in the IO statistics.

From the first query:
Table ‘myBigTable’. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

And from the second query:

Table ‘myBigTable’. Scan count 5, logical reads 9563, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

That’s a lot more IO the database has to do.  And we all know, when it comes to IO, less is more.

Now, those results were pretty predictable.  But what about when both indexed columns are in the where clause?  Based on what I’ve been told, the index with the high-cardinality column first will be more efficient.  But I want to see that for myself.

SELECT * FROM myBigTable WITH (INDEX = i_highcard) WHERE ID = 77321 AND LOCATION = '04'
SELECT * FROM myBigTable WITH (INDEX = i_lowcard) WHERE ID = 77321 AND LOCATION = '04'

First, the IO stats:

Table ‘myBigTable’. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘myBigTable’. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Exactly the same.  What about the execution plans?

Also the same.  Even down to the cost.

That surprises me.  I really thought the i_highcard index would be more efficient.  So, I’m back to my original question, when it comes to composite indexes, does column order matter?  And I guess the answer is:  it depends.  SQL Server only maintains statistics on the first column in an index, so having a highly selective first column can be more efficient if you’re using that column in your WHERE clauses.  But if you’re using both columns as filter criteria, it doesn’t seem to matter.  This another reason is why it’s extremely important to know your application and how it accesses the data.

April 7, 2011 Posted by | General, SQL Tuning | , | 2 Comments

Table Value Constructors

So I was poking around on SQLServerCentral today and I wandered into the QotD section. At random, I picked a TSQL question that introduced me to a new functionality in SQL Server 2008: table value constructors (TVC). Let me state for the record that I am not a developer (in case you hadn’t already noticed). So if I’m the last person to hear about this feature, and you’re all “duh, where the hell have you been?” feel free to move along. If you haven’t heard of this, read on.

In a nutshell, a TVC is a way of specifying multiple rows of data in a single DML statement. For example, suppose you had a table MyBooks and you wanted to insert several records into the table. In previous versions of SQL Server, you’d either have to use multiple insert statements, or a single insert with multiple SELECTs merged with a UNION ALL.

Title		varchar(100),
Author		varchar(100),
Published	varchar(4))

INSERT INTO #mybooks values('Gone With The Wind', 'Margaret Mitchell', '1936');
INSERT INTO #mybooks values('Go Dog Go', 'P.D. Eastman', 1966);
INSERT INTO #mybooks values('The Holy Bible', 'God', '');


INSERT INTO #mybooks
SELECT 'Gone With The Wind', 'Margaret Mitchell', '1936'
SELECT 'Go Dog Go', 'P.D. Eastman', 1966
SELECT 'The Holy Bible', 'God', ''

But with a TVC, you can insert multiple rows using a single INSERT statement.

DELETE #mybooks
INSERT into #myBooks VALUES ('Gone With The Wind', 'Margaret Mitchell', '1936')
							, ('Go Dog Go', 'P.D. Eastman', 1966)
							, ('The Holy Bible', 'God', '')

Be careful, though. If we query #mybooks we’ll see another new feature of SQL Server 2008.

SELECT * FROM #mybooks

Take a look at the published column for the Bible row. 0? But we inserted an empty string into a varchar column, right? In SQL 2008, you now have to be careful of implicit conversions when using a TVC (or an INSERT…SELECT…UNION ALL for that matter). From BOL:

The values specified in a multi-row INSERT statement follow the data type conversion properties of the UNION ALL syntax. This results in the implicit conversion of unmatched types to the type of higher precedence. If the conversion is not a supported implicit conversion, an error is returned.

So even though the destination column is a varchar datatype, because you’re inserting an integer (1966) SQL Server will implicitly convert all of the other values to an integer before inserting them into the varchar. (I’m sure there’s a reason for this, but it doesn’t make a lot of sense to me.)

With that in mind, if we try to execute the following insert, we should get an error.

INSERT into #myBooks VALUES ('Gone With The Wind', 'Margaret Mitchell', '1936')
							, ('Go Dog Go', 'P.D. Eastman', 1966)
							, ('The Holy Bible', 'God', 'c.34')

And we do.

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value ‘c.34’ to data type int.

But wait, there’s more! TVCs aren’t limited to INSERT statements. You can also use a TVC in the FROM clause of a query.

SELECT * FROM (VALUES('Gone With The Wind', 'Margaret Mitchell', '1936')
							, ('Go Dog Go', 'P.D. Eastman', 1966)
							, ('The Holy Bible', 'God', '')) as somebooks (title, author, published)

Or in a MERGE statement.

You probably expected an example of a MERGE statement using a TVC here.  But you're not going to get one because I'm not well-versed in MERGE statements.  Hey, I said I wasn't a developer.

April 4, 2011 Posted by | T-SQL, Uncategorized | Leave a comment