July 22, 2008 at 3:29 am
When running a report on my server it has the following text at the top:
"This report identifies the queries currently residing in the plan cache that have caused the most IO on average each time they execute. This data is aggregated over the lifetime of the plan in the cache and is available only for plans currently in the cache."
After making some changes I would like to reset the report to see how the performance looks now. Any ideas of how to do this?
July 22, 2008 at 5:37 am
stefan (7/22/2008)
When running a report on my server it has the following text at the top:"This report identifies the queries currently residing in the plan cache that have caused the most IO on average each time they execute. This data is aggregated over the lifetime of the plan in the cache and is available only for plans currently in the cache."
After making some changes I would like to reset the report to see how the performance looks now. Any ideas of how to do this?
Are you saying making changes to the text on top?? or to a query?? if for a query then pls post the query.
thanks
July 22, 2008 at 5:41 am
I would like to change the query or empty the cache so that the report will start from zero again. Or if the query holds a certain start and end time for the statistics I would like to change the start time. The thing is that I do not know how to access or where to find that query because I have never worked with the reports before.
July 22, 2008 at 5:55 am
I assume you're talking about the SSMS report "Performance Top Queries by Average IO".
The results of this report are based several DMV's.
The only way to reset the values is stop SQL Server, which is probably not what you want.
A better idea is writing your own custom report which filters on columns in sys.dm_exec_query_stats like last_execution_time and/or last_logical_reads/writes.
This is the query used by the standard report:
begin try
select top 10 rank() over (order by (total_logical_reads+total_logical_writes)/(execution_count+0.0) desc,sql_handle,statement_start_offset ) as row_no
, (rank() over (order by (total_logical_reads+total_logical_writes)/(execution_count+0.0) desc,sql_handle,statement_start_offset ))%2 as l1
, creation_time
, last_execution_time
, (total_worker_time+0.0)/1000 as total_worker_time
, (total_worker_time+0.0)/(execution_count*1000) as [AvgCPUTime]
, total_logical_reads as [LogicalReads]
, total_logical_writes as [LogicalWrites]
, execution_count
, total_logical_reads+total_logical_writes as [AggIO]
, (total_logical_reads+total_logical_writes+0.0)/execution_count as [AvgIO]
, case when sql_handle IS NULL
then ' '
else ( substring(st.text,(qs.statement_start_offset+2)/2,(case when qs.statement_end_offset = -1 then len(convert(nvarchar(MAX),st.text))*2 else qs.statement_end_offset end - qs.statement_start_offset) /2 ))
end as query_text
, db_name(st.dbid) as database_name
, st.objectid as object_id
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(sql_handle) st
where (total_logical_reads+total_logical_writes ) > 0
order by [AvgIO] desc
end try
begin catch
select -100 AS row_no
, 1 AS l1, 1 AS creation_time, 1 AS last_execution_time, 1 AS total_worker_time, 1 AS AvgCPUTime, 1 AS logicalReads, 1 AS LogicalWrites
, ERROR_NUMBER() AS execution_count
, ERROR_SEVERITY() AS AggIO
, ERROR_STATE() AS AvgIO
, ERROR_MESSAGE() AS query_text
end catch
[font="Verdana"]Markus Bohse[/font]
July 22, 2008 at 12:27 pm
Then on to the next newbee question, how do I create a report? Should I go through Visual Studio to do that?
July 23, 2008 at 10:43 am
Yes, you need either Visual Studio or Business Inteligence Developmnet Studio.
If you never build a report before, I would advise starting with the tutorials in BOL.
[font="Verdana"]Markus Bohse[/font]
July 23, 2008 at 1:11 pm
Actually, sys.dm_exec_query_stats and sys.dm_exec_cached_plans can essentially be reset by freeing the procedure cache (issue DBCC FREEPROCCACHE).
See this link http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1316979&SiteID=1
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 27, 2008 at 12:47 am
The below statement will free up your cache and you can check your performance after executing the below statement.
DBCC FREEPROCCACHE [ WITH NO_INFOMSGS ]
Thanks and Regards,
Venkatesan Prabu, 😛
My Blog:
http://venkattechnicalblog.blogspot.com/
July 27, 2008 at 1:11 am
Venkatesan Prabu (7/27/2008)
The below statement will free up your cache and you can check your performance after executing the below statement.
DBCC FREEPROCCACHE [ WITH NO_INFOMSGS ]
Can we execute the statement when users are connected to the database.? Will it affect the performance anyway?? thanks
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply