May 21, 2009 at 7:59 am
Is there a way to count number of queries executed on sql server in the past and does it really help me to predict server perofrmance in any way?
May 21, 2009 at 8:07 am
We also use Quest Spot Light, does it give any such information about number of queries.
May 24, 2009 at 1:28 pm
>>Is there a way to count number of queries executed on sql server in the past
Try this to pull the queries that have been executed the most on the server from the last server reboot:
*EDIT* After reminded by Gail, For completeness, I am adding this. Plans are cached in procedure cache and there are many reasons, plans can be flushed out. Gail has listed good number of reasons below and am adding few more.
IF you have auto_close on and the last connectin has disconnected.
If you drop a snapshot from SQL Server 2005.
If you rebuild a log.
If you run DBCC CheckDB.
-- Cached SP's By Execution Count
SELECT TOP (25) qt.text AS 'SP Name', qs.execution_count AS 'Execution Count',
qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Calls/Second',
qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',
qs.total_worker_time AS 'TotalWorkerTime',
qs.total_elapsed_time/qs.execution_count AS 'AvgElapsedTime',
qs.max_logical_reads, qs.max_logical_writes, qs.total_physical_reads,
DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache'
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.dbid = db_id() -- Filter by current database
ORDER BY qs.execution_count DESC;
>>does it really help me to predict server perofrmance in any way?
Not sure if I can understand the intent of this question. Can you elaborate?
Server Performance is measured by CPU, memory, IO usage. It will help if you get a good read on this white paper from MSFT.
Troubleshooting Performance Problems in SQL Server 2005
[font="Courier New"]Sankar Reddy | http://SankarReddy.com/[/url][/font]
May 24, 2009 at 1:47 pm
Sankar Reddy (5/24/2009)
Try this to pull the queries that have been executed the most on the server from the last server reboot:
-- Cached SP's By Execution CountSELECT TOP (25) qt.text AS 'SP Name', qs.execution_count AS 'Execution Count',
qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Calls/Second',
qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',
qs.total_worker_time AS 'TotalWorkerTime',
qs.total_elapsed_time/qs.execution_count AS 'AvgElapsedTime',
qs.max_logical_reads, qs.max_logical_writes, qs.total_physical_reads,
DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache'
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.dbid = db_id() -- Filter by current database
ORDER BY qs.execution_count DESC;
That's not necessarily since last boot. That's querying the plan cache and will only return info for queries who's plans are still in cache. There are a number of things, other than a server restart that could result in plans being dropped from cache.
Stored proc recompile due to stats change or DDL change
Memory pressure
Explicit recompile request
Aging of plans out of cache
Any operation that empties the cache (DB restore, DB offline, DBCC FREEPROCCACHE, etc)
When a query's plan is dropped from cache, the accumulated info for it in query stats is also dropped.
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
May 24, 2009 at 1:50 pm
Gail, you are absolutely right. I should put more effort in clearly specifying that.
[font="Courier New"]Sankar Reddy | http://SankarReddy.com/[/url][/font]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply