December 5, 2017 at 8:26 pm
Hi All,
We have a number of stored procs and some of them are running quite slow. I have currently run the below script and not quite sure what it's telling me. Is there a better way to check on stored proc performance or can someone tell me what this actually means
SELECT QT.text,O.name,
qp.query_plan,
qs.total_elapsed_time / qs.execution_count / 1000000.0 AS average_seconds,
qs.total_elapsed_time / 1000000.0 AS total_seconds,
qs.execution_count
FROM (
SELECT TOP 1000 *
FROM sys.dm_exec_query_stats
ORDER BY total_worker_time DESC
) AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS QT
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
INNER JOIN sys.objects o ON qt.objectid = o.object_id
WHERE O.name LIKE '%Api_%' --AND Qt.text not like '%LM_%' AND Qt.text NOT LIKE '%Update_Report%'
ORDER BY 4 DESC
Thanks
Kris
Kris
December 11, 2017 at 11:13 am
The meat of your script, sys.dm_exec_query_stats, gives you performance data for cached query plans. The results are fairly straightforward:
text: this is the text of the batch identified by the sql_handle from sys.dm_exec_query_stats
name: this is the name of the object referenced by sys.dm_exec_sql_text
query_plan: this is a clickable XML link that will show you the query plan. Click on this and dig through to find your high cost areas in your execution and identify your slowdowns and places that need improvement
average_seconds: this is your average seconds per execution
total_seconds: this is the total seconds for all executions
execution_count: this is how many times that particular query plan was used
This is all sorted so by average longest running first.
December 11, 2017 at 1:14 pm
To add to this, running that query once will probably only show you a limited view of what's all going on in your system depending on how long query plans stay in cache. It may make sense to run it every day, maybe even multiple times a day, over the course of multiple days, to get a bigger picture feel of all the slow queries:
https://www.sqlpassion.at/archive/2015/04/20/how-to-find-your-worst-performing-sql-server-queries/
https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-query-stats-transact-sql
Sorting this by average time is a common way to look at this data. It may also make sense to try sorting it by total_elapsed_time DESC to make it more obvious when a somewhat slow query may be running very frequently consuming more time than a very slow query which is only run occasionally.
January 11, 2018 at 5:23 am
This was removed by the editor as SPAM
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply