SQL Server Denali CTP1 Diagnostic Information Queries

Here is the first SQL Server Denali–specific version of my Diagnostic Information Queries. There are actually only two queries (which are marked in the script) that are different from the SQL Server 2008 and 2008 R2 version of these queries.

There will be additional Denali-specific items added in the future…

You just need to click on the link above to download the script from Dropbox. Just to make them easier to find, the two changed queries are shown below:

-- Hardware information from SQL Server Denali (new virtual_machine_type_desc column)
-- (Cannot distinguish between HT and multi-core)
-- Denali Specific Query
SELECT cpu_count AS [Logical CPU Count], hyperthread_ratio AS [Hyperthread Ratio],
cpu_count/hyperthread_ratio AS [Physical CPU Count], 
physical_memory_kb/1024 AS [Physical Memory (MB)], 
affinity_type_desc, virtual_machine_type_desc, sqlserver_start_time
FROM sys.dm_os_sys_info OPTION (RECOMPILE);



-- Memory Clerk Usage for instance
-- Look for high value for CACHESTORE_SQLCP (Ad-hoc query plans)
-- Denali Specific Query
SELECT TOP(10) [type] AS [Memory Clerk Type], SUM(pages_kb) AS [SPA Mem, Kb] 
FROM sys.dm_os_memory_clerks 
GROUP BY [type]  
ORDER BY SUM(pages_kb) DESC OPTION (RECOMPILE);


-- CACHESTORE_SQLCP  SQL Plans         These are cached SQL statements or batches that aren't in 
--                                     stored procedures, functions and triggers
-- CACHESTORE_OBJCP  Object Plans      These are compiled plans for stored procedures, 
--                                     functions and triggers
-- CACHESTORE_PHDR   Algebrizer Trees  An algebrizer tree is the parsed SQL text that 
--                                     resolves the table and column names
This entry was posted in SQL Server Denali and tagged . Bookmark the permalink.

2 Responses to SQL Server Denali CTP1 Diagnostic Information Queries

  1. Jerry says:

    I am going to take advantage of Kendra Little’s trick for including a link to an MSDN search for various tables, etc. It might be helpful to return similar links in some of your queries. I commented on how I had suggested that Microsoft provide metadata tables for things like wait events, but that got rejected. I mentioned how you filter out certain wait events, that the filtered wait events have changed over time and how useful it would be if they put that into metadata tables (like Oracle). Maybe you could have a table function that read the current filtered wait events from your website and then have a version of the query that joins with the table function.

  2. Pingback: May 2011 SQL Server 2008 Diagnostic Information Queries | Glenn Berry's SQL Server Performance

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s