SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Query Performance


SQL Query Performance

Author
Message
SQLSACT
SQLSACT
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8363 Visits: 2993
Hi All

I want to investigate Query Performance on my SQL Instance

I've been looking at the sys.dm_exec_query_stats DMV

How up to date is this DMV? Will it show me the longest run queries on my instance since SQL was started?

Thanks
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)

Group: General Forum Members
Points: 146043 Visits: 33199
The DMV stores data for queries that are currently in cache. As soon as a plan ages out of cache all the information in that DMV is lost. So it really depends on how volatile the plan cache on your machine is for how much information is available to you through that DMV. For a complete measure of query performance, I'd go with extended events.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
SQLSACT
SQLSACT
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8363 Visits: 2993
Grant Fritchey (7/31/2012)
The DMV stores data for queries that are currently in cache. As soon as a plan ages out of cache all the information in that DMV is lost. So it really depends on how volatile the plan cache on your machine is for how much information is available to you through that DMV. For a complete measure of query performance, I'd go with extended events.


Thank You

So I would use that DMV as part of troubleshooting for a "SQL is slow NOW" situation

Where would I start for a "SQL has been slow erratically"

Thanks
SQLSACT
SQLSACT
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8363 Visits: 2993
Grant Fritchey (7/31/2012)
The DMV stores data for queries that are currently in cache. As soon as a plan ages out of cache all the information in that DMV is lost. So it really depends on how volatile the plan cache on your machine is for how much information is available to you through that DMV. For a complete measure of query performance, I'd go with extended events.


I could be wrong but I remember reading somewhere that when plans are flushed out of cache or when they are flushed too quickly, it affects performance of SQL Server

Am I on the right track here?

Thanks
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)

Group: General Forum Members
Points: 146043 Visits: 33199
SQLSACT (7/31/2012)
Grant Fritchey (7/31/2012)
The DMV stores data for queries that are currently in cache. As soon as a plan ages out of cache all the information in that DMV is lost. So it really depends on how volatile the plan cache on your machine is for how much information is available to you through that DMV. For a complete measure of query performance, I'd go with extended events.


I could be wrong but I remember reading somewhere that when plans are flushed out of cache or when they are flushed too quickly, it affects performance of SQL Server

Am I on the right track here?

Thanks


Plans flush out of cache all the time. It doesn't necessarily affect performance at all. It's when they are constantly flushing, or never being reused that you have an issue. You can get an idea of how active your cache is by simply looking at the oldest date within that DMV.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)

Group: General Forum Members
Points: 146043 Visits: 33199
SQLSACT (7/31/2012)
Grant Fritchey (7/31/2012)
The DMV stores data for queries that are currently in cache. As soon as a plan ages out of cache all the information in that DMV is lost. So it really depends on how volatile the plan cache on your machine is for how much information is available to you through that DMV. For a complete measure of query performance, I'd go with extended events.


Thank You

So I would use that DMV as part of troubleshooting for a "SQL is slow NOW" situation

Where would I start for a "SQL has been slow erratically"

Thanks



Actually, if "SQL Server is slow NOW" I'd look first at sys.dm_exec_requests and then combine that, as needed with sys.dm_exec_query_stats. Again, depending on the volatility of your cache, this is a good place to look. It's just potentially not good because the queries do leave cache. For detailed, specific views, capture every call with extended events. Just be ready to deal with large amounts of data.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
pooyan_pdm
pooyan_pdm
SSC Eights!
SSC Eights! (884 reputation)SSC Eights! (884 reputation)SSC Eights! (884 reputation)SSC Eights! (884 reputation)SSC Eights! (884 reputation)SSC Eights! (884 reputation)SSC Eights! (884 reputation)SSC Eights! (884 reputation)

Group: General Forum Members
Points: 884 Visits: 434
when the cached plans are flushed off the cache next time the query is being executed the plan should be generated again which consumes CPU clocks and could have negative impact on SQL Server performance and if the memory allocated to the plan cache is not big enough the plans are flushed off more quickly

Pooyan
SQLSACT
SQLSACT
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8363 Visits: 2993
Grant Fritchey (7/31/2012)
SQLSACT (7/31/2012)
Grant Fritchey (7/31/2012)
The DMV stores data for queries that are currently in cache. As soon as a plan ages out of cache all the information in that DMV is lost. So it really depends on how volatile the plan cache on your machine is for how much information is available to you through that DMV. For a complete measure of query performance, I'd go with extended events.


I could be wrong but I remember reading somewhere that when plans are flushed out of cache or when they are flushed too quickly, it affects performance of SQL Server

Am I on the right track here?

Thanks


Plans flush out of cache all the time. It doesn't necessarily affect performance at all. It's when they are constantly flushing, or never being reused that you have an issue. You can get an idea of how active your cache is by simply looking at the oldest date within that DMV.


Thanks

Besides checking the oldest date,

How can I check if plans are never being reused?

Is there some sort of standard as to how long a plan stays in cache? 1 day, 2 days etc..


Thank you
SQLSACT
SQLSACT
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8363 Visits: 2993
Grant Fritchey (7/31/2012)
SQLSACT (7/31/2012)
Grant Fritchey (7/31/2012)
The DMV stores data for queries that are currently in cache. As soon as a plan ages out of cache all the information in that DMV is lost. So it really depends on how volatile the plan cache on your machine is for how much information is available to you through that DMV. For a complete measure of query performance, I'd go with extended events.


Thank You

So I would use that DMV as part of troubleshooting for a "SQL is slow NOW" situation

Where would I start for a "SQL has been slow erratically"

Thanks



Actually, if "SQL Server is slow NOW" I'd look first at sys.dm_exec_requests and then combine that, as needed with sys.dm_exec_query_stats. Again, depending on the volatility of your cache, this is a good place to look. It's just potentially not good because the queries do leave cache. For detailed, specific views, capture every call with extended events. Just be ready to deal with large amounts of data.



Thanks

I've been doing some reading on the topic of performance, I've taken little pieces from different sections.

I've put this together for a "SQL is Slow Now" situation, am I on the right track here?

select ER.session_id,ES.login_time,ER.Command, ER.start_time,ER.blocking_session_id 
,ER.wait_resource
,ER.wait_type
,ER.wait_time
,ER.reads
,ER.writes
,DB_NAME(ER.database_id)
from sys.dm_exec_requests ER
inner join sys.dm_exec_sessions ES
on ER.session_id = ES.session_id
inner join sys.dm_os_waiting_tasks WT
on ES.session_id = WT.session_id
where ES.is_user_process <> 0




Thanks
pooyan_pdm
pooyan_pdm
SSC Eights!
SSC Eights! (884 reputation)SSC Eights! (884 reputation)SSC Eights! (884 reputation)SSC Eights! (884 reputation)SSC Eights! (884 reputation)SSC Eights! (884 reputation)SSC Eights! (884 reputation)SSC Eights! (884 reputation)

Group: General Forum Members
Points: 884 Visits: 434
By checking the usecount column in sys.dm_exec_cached_plans you can look for plns with value of 1 .duration of a plan staying in the cache depends on the memory available to sql server and frequency of the plan being used. Plans that are not used for a while if there is a need to free up some space for new plans, are flushed off.

Pooyan
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search