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