July 15, 2008 at 12:00 am
how to identify a poorly performing query?:w00t:
July 15, 2008 at 12:03 am
Hi
You can display estimated query plan and analysis the steps of every satement.
You can use Tuning Advisor or SQL Server Profiler
July 15, 2008 at 12:16 am
waht i want to ask is that so many queries r running i cant view estimated query plan for all,
is there any way from MMS to know that particular query is running very slow:)
July 15, 2008 at 12:23 am
Hi Shahbaz,
Yes, you can check the query execution plan of every statement/line using "Display Estimated Query Plan". Just Select all the queries and click on the button "Display Estimated Query Plan" in management studio.
July 15, 2008 at 12:30 am
Thanks Hari,:)
i googled and came out with 1 solution we can use REPORT tab in SSMS which displays all the queries and identifies the particular one with poor performing...:unsure:
thanks
syed
July 15, 2008 at 1:19 am
Run profiler against your system for a while (an hour or so) and then examine the saved trace to identify the queries with the highest duration, reads and CPU.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 15, 2008 at 6:03 am
SQL 2005 keeps track of the usage of all the queries in sys.dm_exec_query_stats. You can query this systemtable to see the usage of queries. Keep in mind that the results are reset whenever the SQL-service is restarted.
SELECT creation_time
,last_execution_time
,total_physical_reads
,total_logical_reads
,total_logical_writes
, execution_count
, total_worker_time
, total_elapsed_time
, total_elapsed_time / execution_count avg_elapsed_time
,SUBSTRING
(st.text, (qs.statement_start_offset/2) + 1,
(
(CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END
- qs.statement_start_offset
)/2
) + 1
) AS statement_text
FROM
sys.dm_exec_query_stats AS qs
CROSS APPLY
sys.dm_exec_sql_text(qs.sql_handle) st
July 15, 2008 at 6:06 am
HanShi (7/15/2008)
Keep in mind that the results are reset whenever the SQL-service is restarted.
Entries disappear from the query_stats DMV when the query plan is discarded from the plan cache. Depending on the memory available, the amount of procs run and the stability of the plan, that could be a long time, or it could be a very short time.
It's a good place to look, but I wouldn't trust it to have everything
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 15, 2008 at 6:13 pm
GilaMonster (7/15/2008)
HanShi (7/15/2008)
Keep in mind that the results are reset whenever the SQL-service is restarted.Entries disappear from the query_stats DMV when the query plan is discarded from the plan cache. Depending on the memory available, the amount of procs run and the stability of the plan, that could be a long time, or it could be a very short time.
It's a good place to look, but I wouldn't trust it to have everything
If the earliest last_execution_time value in the DMV is, say, from a week ago, wouldn't it be safe to assume that query plans that are a week old or less are still in the plan cache? (I'm not sure query plans are discarded from the cache based on age only though...)
But if this is the case, that would be a straightforward means of determining the time range over which data in the query-stats DMV are 'reliable'.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
July 16, 2008 at 12:41 am
Marios Philippopoulos (7/15/2008)
last_execution_time[/b] value in the DMV is, say, from a week ago, wouldn't it be safe to assume that query plans that are a week old or less are still in the plan cache? (I'm not sure query plans are discarded from the cache based on age only though...)
No, because the aging out algotrithm is based on usage as well as time. A proc that's run every minute may be in the cache for a week or more, while a query run once a day may be aged out after a couple of hours.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 16, 2008 at 7:27 am
GilaMonster (7/16/2008)
Marios Philippopoulos (7/15/2008)
last_execution_time[/b] value in the DMV is, say, from a week ago, wouldn't it be safe to assume that query plans that are a week old or less are still in the plan cache? (I'm not sure query plans are discarded from the cache based on age only though...)No, because the aging out algotrithm is based on usage as well as time. A proc that's run every minute may be in the cache for a week or more, while a query run once a day may be aged out after a couple of hours.
Thanks Gail, you've raised an important point that I will try to remember when using the query stats DMV.
It would be interesting if someone collected all known limitations of DMVs in an article. I've already come across a couple.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply