February 15, 2013 at 6:17 am
Hi All,
I want to know the average amount of memory spent per query, or the memory spent for each query that was executed.
Can anyone help,suggest something?
Regards
IgorMi
Igor Micev,My blog: www.igormicev.com
February 18, 2013 at 3:19 am
SELECT TOP(100) [text], cp.size_in_bytes
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cp.cacheobjtype = N'Compiled Plan'
AND cp.objtype = N'Adhoc'
AND cp.usecounts = 1
ORDER BY cp.size_in_bytes DESC;
this query might help you
February 19, 2013 at 9:52 am
The above poster is correct; here's something with that same data plus a bit more.
Note there are two columns (qs.query_hash and qs.query_plan_hash) useful only on 2008+ in the GROUP BY clause.
For 2005 only in the GROUP BY clause, qs.plan_handle is used for ad-hoc queries.
-- Originally from http://www.databasejournal.com/features/mssql/article.php/3687186/Monitoring-Stored-Procedure-Usage.htm
SELECT DB_NAME(st.dbid) DBName
,cp.objtype
,cp.cacheobjtype
,OBJECT_SCHEMA_NAME(objectid,st.dbid) SchemaName
,OBJECT_NAME(objectid,st.dbid) OBJECT
,MAX(cp.usecounts) execution_count
,SUM(CONVERT(BIGINT,qs.total_physical_reads) + CONVERT(BIGINT,qs.total_logical_reads) + CONVERT(BIGINT,qs.total_logical_writes)) total_IO
,SUM(CONVERT(BIGINT,qs.total_physical_reads) + CONVERT(BIGINT,qs.total_logical_reads) + CONVERT(BIGINT,qs.total_logical_writes)) / (MAX(cp.usecounts)) avg_IO
,SUM(CONVERT(BIGINT,qs.total_physical_reads)) total_physical_reads
,SUM(CONVERT(BIGINT,qs.total_physical_reads)) / (MAX(cp.usecounts) * 1.0) avg_physical_read
,SUM(CONVERT(BIGINT,qs.total_logical_reads)) total_logical_reads
,SUM(CONVERT(BIGINT,qs.total_logical_reads)) / (MAX(cp.usecounts) * 1.0) avg_logical_read
,SUM(CONVERT(BIGINT,qs.total_logical_writes)) total_logical_writes
,SUM(CONVERT(BIGINT,qs.total_logical_writes)) / (MAX(cp.usecounts) * 1.0) avg_logical_writes
,SUM(CONVERT(BIGINT,qs.total_worker_time)) total_cpu_time
,SUM(CONVERT(BIGINT,qs.total_worker_time)) / (MAX(cp.usecounts) * 1.0) avg_cpu_time
,SUM(CONVERT(BIGINT,qs.total_elapsed_time)) total_elapsed_time
,SUM(CONVERT(BIGINT,qs.total_elapsed_time)) / MAX(cp.usecounts) avg_elapsed_time
,SUM(CONVERT(BIGINT,cp.size_in_bytes)) AS SumSize_in_bytes
,AVG(CONVERT(BIGINT,cp.size_in_bytes)) AS AvgSize_in_bytes
,MAX(qs.plan_generation_num) AS plan_generation_num
,MIN(qs.creation_time) AS Creation_time
,MAX(qs.last_execution_time) AS Last_execution_time
,MAX(st.text) AS SQLtext
--,MAX(qs.query_hash) AS MaxQueryHash -- 2008 and up only.
--,MAX(qs.query_plan_hash) AS MaxQueryPlanHash -- 2008 and up only.
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
INNER JOIN sys.dm_exec_cached_plans cp ON qs.plan_handle = cp.plan_handle
-- WHERE
-- DB_NAME(st.dbid) is NOT NULL -- in SQL2005, this removes Ad-hoc as well as system entries. In SQL2012, this removes some system entries, but not Adhoc.
-- cp.objtype = 'Proc'
-- cp.objtype = 'Prepared'
-- cp.objtype = 'Trigger'
-- cp.objtype = 'Adhoc'
-- cp.objtype <> 'Adhoc'
GROUP BY DB_NAME(st.dbid)
,OBJECT_SCHEMA_NAME(objectid,st.dbid)
,OBJECT_NAME(objectid,st.dbid)
,cp.cacheobjtype
,cp.objtype
,qs.plan_handle -- SQL2005 way of differentiating between Adhoc statements. Even changes in only literal values are seen as different!
--,qs.query_hash -- normal SQL2008 and up Adhoc subgrouping (non-literal differences)- Queries that differ only by literal values have the same query hash
--,qs.query_plan_hash -- alternate SQL2008 and up Adhoc subgrouping (execution plan differences + non-literal differences) - Query execution plans that have the same physical and logical operator tree structure, as well as identical attribute values for the subset of important operator attributes, will have the same query plan hash. When queries with identical query hashes are executed against different data, the differences in the cardinality of query results can cause the query optimizer to choose different query execution plans, resulting in different query plan hashes.
--ORDER BY MAX(st.text)
ORDER BY SUM(CONVERT(BIGINT,qs.total_physical_reads) + CONVERT(BIGINT,qs.total_logical_reads) + CONVERT(BIGINT,qs.total_logical_writes)) DESC
--ORDER BY AVG(CONVERT(BIGINT,cp.size_in_bytes)) DESC
--ORDER BY SUM(CONVERT(BIGINT,qs.total_worker_time)) / (MAX(cp.usecounts) * 1.0) DESC
February 19, 2013 at 1:32 pm
Thank you guys! These two queries are really useful for me.
Best
IgorMi
Igor Micev,My blog: www.igormicev.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply