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