SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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]

-- 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


Posted by Jason Brimhall on 17 March 2011

I should add your queries to my blog list of tools.

Leave a Comment

Please register or log in to leave a comment.