Cleveland DBA

SQL Server administration in Cleveland, OH

SQL Server A to Z – DMV

Today’s episode of SQL Server A to Z is brought to you by the letter D.  D is for Dynamic Management View (DMV).  DMVs were introduced in SQL 2005 as a way to provide administrators with a window into what’s going on in SQL Server at any given moment.  This can range from OS statistics, to information about replication, to what execution plans are currently in cache.  Today I’ll give you a brief look at a few of the DMVs I use.


This view is like having Perfmon in T-Sql, you’ll see same SQL Server performance metrics in either.  I happen to like Perfmon for gathering performance stats over a period of time, like when I’m getting a baseline for a server.  But sometimes you just want a glimpse of how things are running right now, at this moment.  Or maybe you want to see those stats next to a list of what processes are currently running in the database.  That’s difficult, or impossible to do with Perfmon.  So that’s when you’d use this DMV.  Let’s take a look at the counters this DMV exposes.

SELECT distinct object_name from sys.dm_os_performance_counters

And if we drill down a little further:

SELECT * from sys.dm_os_performance_counters
where object_name like '%Buffer Manager%'

And there you see the same counters for Buffer Manager that you’d see if you ran Perfmon. This is one of the DMVs I use in my script to see how SQL Server is using its memory;.


I’ll admit this DMV doesn’t get used a lot. But when it is used, it’s very handy. The sys.dm_exec_query_memory_grants DMV returns information about current sessions that have been granted memory to execute or are waiting on memory grants. If I start seeing resource_semaphore waits in my database, this is where I go first. It will tell me how much memory each session has been granted, how much it requested, how much it would have requested given unlimited resources (useful for identifying very bad queries), the sql and plan handles (so you can go get the exact query) etc. Let’s take a look.

SELECT * from sys.dm_exec_query_memory_grants


The last DMV I’m going to cover is one I use quite regularly to determine what indexes are being used, how much they’re used, and when they’re used. The sys.dm_db_index_usage_stats view contains a record for every index that’s been used since the instance last started, along with counts for the various read and write operations that have been executed against it. Why is this helpful? If you monitor this view over time and find an index that has high user updates but no user seeks, scans or lookups, in most cases this index isn’t being used by the application and you might consider removing the index to eliminate the overhead of maintaining it. Now before you go out and start dropping indexes, there are some caveats to keep in mind. Those are outside the scope of this post, but google “SQL Server drop unused indexes” before you do anything else.

I know I said that would be the last one, but I feel I should mention a few DMVs that compliment sys.dm_db_index_usage_stats nicely. sys.dm_db_missing_index_groups, sys.dm_db_missing_index_group_stats, and sys.dm_db_missing_index_details views are commonly joined together to provide a list of indexes that SQL Server recommends based on usage. Like so:

SELECT, migs_Adv.index_advantage
, s.avg_user_impact
, s.avg_total_user_cost
, s.last_user_seek
,d.equality_columns, d.inequality_columns, d.included_columns, d.[statement]
from sys.dm_db_missing_index_group_stats s
inner join sys.dm_db_missing_index_groups g on g.index_group_handle=s.group_handle
inner join sys.dm_db_missing_index_details d on d.index_handle=g.index_handle
inner join sys.objects o (nolock) on o.object_id=d.object_id
inner join (select user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) as index_advantage,
              migs.* from sys.dm_db_missing_index_group_stats migs) as migs_adv on migs_adv.group_handle=g.index_group_handle
order by migs_adv.index_advantage desc, s.avg_user_impact desc

Keep in mind that all of these stats are reset at system startup. So you want to develop a procedure to retain this data over time. Personally, I have a script that captures the information once a week and I didn’t start using it until I had 4 months of data. Why so long? There are some processes that only run once a month. And there are a few that only run quarterly. If I based my actions on only a few weeks worth of statistics, I might end up dropping an index that’s critical to the performance of one of our quarterly reports. So be careful!

So there’s a taste of what DMVs I use. Which ones are your personal favorites?

July 18, 2011 Posted by | Features | , | Leave a comment

How is SQL Server using all that memory, anyway?

I want to stick with the subject of memory because I think it’s probably the most misunderstood (and sometimes downright mysterious) component of SQL Server. I know I, for one, could benefit from a better understanding of its inner workings. So today I’d like to share that little bit of code I mentioned last week, that I find very handy for getting an overall picture of how memory is being used by a SQL instance. Personally I run it as a complete script, but I’ll break it down here and provide a little description of each section. I should say, too, that I didn’t write any of this from scratch. For most of it I started with code found here, here, and here, and modified it to suit.

So, without further ado, let’s get started. The first section provides an big-picture look at how SQL is currently allocating memory between the database page cache, procedure cache, and miscellaneous memory usage. It also provides the total memory usage, and I added in the maximum available workspace memory.


declare @plan_cache_size float, @obj_data_size float , @avail_workspace_size float

-- plan cache size

select @plan_cache_size = (cntr_value*8)/1024.0 FROM sys.dm_os_performance_counters
	WHERE object_name like '%Plan Cache%' and counter_name = 'Cache Pages' and instance_name = '_Total'


select @obj_data_size = (cntr_value*8)/1024.0  FROM sys.dm_os_performance_counters
	WHERE object_name like '%Buffer Manager%' and counter_name like 'Database pages%'

-- Maximum workspace available for sorts, hashes, etc

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

select   @obj_data_size [Database_Page_Cache_MB]
 , @plan_cache_size [Procedure_Cache_MB]
 , [Misc_Memory_Usage_MB]
 , [Misc_Memory_Usage_MB] + @obj_data_size + @plan_cache_size [Total_Memory_Usage_MB]
 , @avail_workspace_size [Maximum_Workspace_MB]
 select sum(cntr_value)/1024.0 [Misc_Memory_Usage_MB]
 from sys.dm_os_performance_counters
 where object_name like '%memory%'
 and (
   counter_name like '%Connection Memory (KB)%'
   counter_name like '%Granted Workspace Memory (KB)%'
   counter_name like '%Lock Memory (KB)%'
   counter_name like '%Optimizer Memory (KB)%'
   counter_name like '%SQL Cache Memory (KB)%'
) x

The output:

The next section uses sys.dm_os_memory_cache_counters to examine how the procedure cache is being broken down.

-- How the Procedure cache is being used

	LEFT([name], 20) as [name],
	LEFT([type], 20) as [type],
	([single_pages_kb] + [multi_pages_kb])/1024 AS cache_mb,
FROM sys.dm_os_memory_cache_counters
order by single_pages_kb + multi_pages_kb DESC

The output:

The third part of the script goes deeper into the procedure cache and displays the top 25 cached plans by size. Because it displays some application code, I’m not going to include the results here.

-- Top cached plans by size

select top 25
, ObjType
, (pagesUsed * 8192)/1024.0/1024.0 [space_used_MB]
, db_name(dbid) [database_name]
, object_name(objid, dbid) [object_name]
, [sql]
from master..syscacheobjects (nolock)
order by pagesUsed desc

And, finally, the last section shows us how much of the buffer pool is being utilized by each database, including the dirty and clean page counts, sorted by total memory.

-- Buffer Pool Memory Per DB

(CASE WHEN ([database_id] = 32767) THEN 'Resource Database' ELSE DB_NAME (database_id) END) AS 'Database Name',
SUM(CASE WHEN ([is_modified] = 1) THEN 1 ELSE 0 END) AS DirtyPageCount,
SUM(CASE WHEN ([is_modified] = 1) THEN 0 ELSE 1 END) AS CleanPageCount,
count(*)AS TotalPageCount,
cast(count(*) * 8192.0 / (1024.0 * 1024.0) as decimal(8,2)) as BufferPoolMB
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY TotalPageCount desc

And the output:

And there you have it. I hope you find this useful!

May 2, 2011 Posted by | General, SQL Tuning, Troubleshooting | , , , | 1 Comment

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