Skip to content

Instantly share code, notes, and snippets.

@cpruitt

cpruitt/notes.md

Created February 27, 2014 17:17
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save cpruitt/9254622 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save cpruitt/9254622 to your computer and use it in GitHub Desktop.
Download ZIP
SQL Query Cash Queries - Some Queries to examine the contents of the SQL Server query cache and related system stuff
Raw
query_cache_get_query_text_top_100_queries.sql
-- Get the text of the top 100 queries in the query cache
-- sorted by obj type (adhoc will be first) and then most memory
SELECT TOP 100
objtype,
usecounts,
p.size_in_bytes / 1024 'size_in_kb',
LEFT([sql].[text], 200) as [text]
FROM sys.dm_exec_cached_plans p
OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) sql
ORDER BY objtype, size_in_kb DESC
Raw
stats_on_cached_queries_by_type.sql
-- Show stats for the number of cached plans, total memory size
-- and average plan use count by query type
SELECT
objtype AS 'Cached Object Type',
COUNT(*) AS 'Number of Plans',
SUM(CAST(size_in_bytes AS BIGINT))/1024/1024 AS 'Plan Cache Size (MB)',
AVG(usecounts) AS 'Avg Use Count'
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY 'Plan Cache Size (MB)' DESC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment