NetInverse Developers Blog

Useful SQL Queries

Showing the plan hash values:

SELECT s.execution_count
      ,s.query_hash
      ,s.query_plan_hash
      ,t.text
FROM   sys.dm_exec_query_stats s
       CROSS APPLY sys.dm_exec_sql_text(s.plan_handle) t

Showing execution plans in the procedure cache:

SELECT * FROM sys.dm_exec_cached_plans

Showing fragmentation:

SELECT  s.avg_fragmentation_in_percent
       ,s.fragment_count
       ,s.page_count
       ,s.avg_page_space_used_in_percent
       ,s.record_count
       ,avg_record_size_in_bytes
FROM    sys.dm_db_index_physical_stats(DB_ID('AdventureWorks2008'),
                                       OBJECT_ID(N'dbo.t1'), NULL, NULL,
                                       'Sampled') AS s

Show SQL memory usages:

CACHESTORE_OBJCP: These are compiled plans for stored procedures, functions and triggers.
CACHESTORE_SQLCP: These are cached SQL statements or batches that aren’t in stored procedures, functions and triggers. This includes any dynamic SQL or raw SELECT statements sent to the server.

SELECT * FROM sys.dm_exec_cached_plans

SELECT * FROM sys.dm_os_memory_cache_clock_hands
WHERE TYPE IN ('CACHESTORE_SQLCP','CACHESTORE_OBJCP')
ORDER BY removed_all_rounds_count desc 

SELECT TOP 512
	st.text,
	cp.cacheobjtype,
	cp.objtype,
	cp.refcounts,
	cp.usecounts,
	cp.size_in_bytes,
	cp.bucketid,
	cp.plan_handle
FROM sys.dm_exec_cached_plans as cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) as st
WHERE cp.cacheobjtype = 'Compiled Plan' AND cp.objtype = 'Prepared'
ORDER BY cp.usecounts desc

SELECT  TOP 10
	LEFT([name], 20) as [name],
	LEFT([type], 20) as [type],
	[single_pages_kb] + [multi_pages_kb] AS cache_kb,
	[entries_count]
FROM sys.dm_os_memory_cache_counters
ORDER BY single_pages_kb + multi_pages_kb DESC

SELECT TOP 10 type,
	(sum(single_pages_kb) + sum(multi_pages_kb)) as 'Total Pages(KB)',
	sum(single_pages_kb) as 'Single Pages(KB)',
	sum(multi_pages_kb) as 'Multi Pages(KB)',
	sum(virtual_memory_reserved_kb) as 'VM Reserved(KB)',
	sum(virtual_memory_committed_kb) as 'VM Committed(KB)',
	sum(awe_allocated_kb) as 'AWE Allocated(KB)',
	sum(shared_memory_reserved_kb) as 'Shared Memory Reserved(KB)',
	sum(shared_memory_committed_kb) as 'Shared Memory Committed(KB)'
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY sum(single_pages_kb) + sum(multi_pages_kb) desc

SQL Performance Tips

Top SQL Server 2005 Performance Issues for OLTP Applications: http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/oltp-performance-issues.mspx
Troubleshooting Performance Problems in SQL Server 2005: http://technet.microsoft.com/en-us/library/cc966540.aspx
Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005: http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx