{"id":587,"date":"2016-01-09T18:29:30","date_gmt":"2016-01-10T00:29:30","guid":{"rendered":"https:\/\/jackdonnell.com\/?p=587"},"modified":"2016-01-09T18:29:30","modified_gmt":"2016-01-10T00:29:30","slug":"find-object-execution","status":"publish","type":"post","link":"https:\/\/jackdonnell.com\/?p=587","title":{"rendered":"Find Object Execution"},"content":{"rendered":"<p>I found a useful <a href=\"http:\/\/www.databasejournal.com\/features\/mssql\/article.php\/3687186\/Monitoring-Stored-Procedure-Usage.htm\" target=\"_blank\">script<\/a> to find executions of stored procedure. This will give you a good idea of what is utilization. Run it multiple times to compare how often a procedure of group of procedures executes within certain time periods. <\/p>\n<div id=\"attachment_611\" style=\"width: 1128px\" class=\"wp-caption alignnone\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-611\" class=\"size-full wp-image-611\" src=\"https:\/\/jackdonnell.com\/wp-content\/uploads\/2016\/01\/procedureexecutions.gif\" alt=\"Stored Procedure Executions\" width=\"1118\" height=\"171\" \/><p id=\"caption-attachment-611\" class=\"wp-caption-text\">Stored Procedure Executions<\/p><\/div>\n<p>For example, you have a under performing billing process. Your system fires multiple procedures multiple times. You could use something like this to get an idea of execution counts, worker time, average elapsed time and max logical reads. It will point you in the right direction procedure that can be optimized. Tweak this to fit your needs.<br \/>\n<!--more--><br \/>\n&nbsp;<\/p>\n<pre lang=\"tsql\">\/*\r\n Object Executions\r\n 1\/6\/2016 JCD\r\n Adapted from http:\/\/www.databasejournal.com\/features\/mssql\/article.php\/3687186\/Monitoring-Stored-Procedure-Usage.htm\r\n *\/\r\n\r\nSET NOCOUNT ON\r\n GO\r\n SELECT CAST(SERVERPROPERTY('ServerName') AS VARCHAR(60)) [instance]\r\n ,CAST(DB_Name() as VARCHAR(128)) [database]\r\n ,OBJECT_SCHEMA_NAME(qt.objectid ,dbid)+'.'+OBJECT_NAME(qt.objectid ,dbid) [object]\r\n ,(SELECT type from sys.sysobjects where id = qt.objectid) [object_type]\r\n ,CONVERT(VARCHAR(20),qs.last_execution_time, 22) [last_execution_time]\r\n ,qs.total_worker_time\r\n ,qs.execution_count\r\n ,ISNULL(qs.total_elapsed_time\/qs.execution_count, 0) [avg_elapsed_time]\r\n ,qs.max_logical_reads\r\n ,CONVERT(VARCHAR(20),GETDATE(), 22)[sample_time]\r\n FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)\r\n CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt\r\n WHERE OBJECTPROPERTY(objectid, 'ISMSShipped')= 0 -- Exclude system objects\r\n AND qt.[dbid] = DB_ID() -- Filter by current database\r\nAND qt.objectid = OBJECT_ID('dbo.CleanBrokenSnapshots')-- Filter by specific object\r\n ORDER BY 3,4\r\n GO\r\n<\/pre>\n<p>Have fun.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I found a useful script to find executions of stored procedure. This will give you a good idea of what is utilization. Run it multiple times to compare how often a procedure of group of procedures executes within certain time &hellip;<\/p>\n<p class=\"read-more\"><a href=\"https:\/\/jackdonnell.com\/?p=587\">Read more &raquo;<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"footnotes":""},"categories":[273,266,22,351,5],"tags":[303,263,385,383,248,249,35,295,52,386,384,314],"class_list":["post-587","post","type-post","status-publish","format-standard","hentry","category-administration","category-dba","category-sql-server","category-ssms","category-t-sql","tag-dba","tag-dmv","tag-execution_count","tag-peformance","tag-performance","tag-script","tag-select","tag-sql-server","tag-ssms","tag-stored-procedure","tag-sys-dm_exec_query_stats","tag-sys-dm_exec_sql_text"],"_links":{"self":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts\/587","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=587"}],"version-history":[{"count":6,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts\/587\/revisions"}],"predecessor-version":[{"id":614,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts\/587\/revisions\/614"}],"wp:attachment":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=587"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=587"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=587"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}