Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQL Query Performance Expand / Collapse
Author
Message
Posted Tuesday, July 31, 2012 7:05 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 3:20 AM
Points: 1,375, Visits: 2,661
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
Post #1337878
Posted Tuesday, July 31, 2012 9:06 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 3:51 AM
Points: 15,517, Visits: 27,897
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1337966
Posted Tuesday, July 31, 2012 10:25 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 3:20 AM
Points: 1,375, Visits: 2,661
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

Post #1338032
Posted Tuesday, July 31, 2012 10:28 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 3:20 AM
Points: 1,375, Visits: 2,661
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
Post #1338036
Posted Tuesday, July 31, 2012 11:21 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 3:51 AM
Points: 15,517, Visits: 27,897
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1338062
Posted Tuesday, July 31, 2012 11:25 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 3:51 AM
Points: 15,517, Visits: 27,897
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1338065
Posted Tuesday, July 31, 2012 11:34 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, May 3, 2014 6:20 PM
Points: 114, Visits: 396
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

Cheers ,
Pooyan D
________________________________________________
Microsoft Certified Technology Specialist : SQL Server 2008
Post #1338067
Posted Tuesday, July 31, 2012 11:49 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 3:20 AM
Points: 1,375, Visits: 2,661
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
Post #1338305
Posted Tuesday, July 31, 2012 11:53 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 3:20 AM
Points: 1,375, Visits: 2,661
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
Post #1338307
Posted Wednesday, August 1, 2012 1:20 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, May 3, 2014 6:20 PM
Points: 114, Visits: 396
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.

Cheers ,
Pooyan D
________________________________________________
Microsoft Certified Technology Specialist : SQL Server 2008
Post #1338339
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse