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