{"id":1219,"date":"2017-03-23T12:35:34","date_gmt":"2017-03-23T07:05:34","guid":{"rendered":"http:\/\/pramodsingla.com\/?p=1219"},"modified":"2017-03-23T12:35:34","modified_gmt":"2017-03-23T07:05:34","slug":"sql-server-tuning-top-10-high-memory-consuming-queries","status":"publish","type":"post","link":"https:\/\/pramodsingla.com\/?p=1219","title":{"rendered":"SQL Server Tuning &#8211; Top 10 high memory consuming Queries"},"content":{"rendered":"<h2><strong>Problem Statement: <\/strong>Script to find top 10 high memory consuming Queries.<\/h2>\n<h2><strong>Solution:<\/strong><\/h2>\n<blockquote>\n<pre><span style=\"font-family:'Courier New';font-size:small;\"><span style=\"color:blue;\">SELECT<\/span>\u00a0<span style=\"color:blue;\">TOP<\/span>\u00a0<span style=\"color:black;\">10<\/span>\u00a0<span style=\"color:fuchsia;\"><i>Object_name<\/i><\/span><span style=\"color:maroon;\">(<\/span><span style=\"color:maroon;\">qt<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">objectid<\/span><span style=\"color:maroon;\">)<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:blue;\">AS<\/span>\u00a0<span style=\"color:red;\">'SP\u00a0Name'<\/span><span style=\"color:silver;\">,<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:fuchsia;\"><i>Substring<\/i><\/span><span style=\"color:maroon;\">(<\/span><span style=\"color:maroon;\">qt<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">text<\/span><span style=\"color:silver;\">,<\/span>\u00a0<span style=\"color:maroon;\">(<\/span>\u00a0<span style=\"color:maroon;\">qs<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">statement_start_offset<\/span>\u00a0<span style=\"color:silver;\">\/<\/span>\u00a0<span style=\"color:black;\">2<\/span>\u00a0<span style=\"color:maroon;\">)<\/span>\u00a0<span style=\"color:silver;\">+<\/span>\u00a0<span style=\"color:black;\">1<\/span><span style=\"color:silver;\">,<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:maroon;\">(<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:maroon;\">(<\/span>\u00a0<span style=\"color:blue;\">CASE<\/span>\u00a0<span style=\"color:maroon;\">qs<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">statement_end_offset<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:blue;\">WHEN<\/span>\u00a0<span style=\"color:silver;\">-<\/span><span style=\"color:black;\">1<\/span>\u00a0<span style=\"color:blue;\">THEN<\/span>\u00a0<span style=\"color:fuchsia;\"><i>Datalength<\/i><\/span><span style=\"color:maroon;\">(<\/span><span style=\"color:maroon;\">qt<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">text<\/span><span style=\"color:maroon;\">)<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:blue;\">ELSE<\/span>\u00a0<span style=\"color:maroon;\">qs<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">statement_end_offset<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:blue;\">END<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:silver;\">-<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:maroon;\">qs<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">statement_start_offset<\/span>\u00a0<span style=\"color:maroon;\">)<\/span>\u00a0<span style=\"color:silver;\">\/<\/span>\u00a0<span style=\"color:black;\">2<\/span>\u00a0<span style=\"color:maroon;\">)<\/span>\u00a0<span style=\"color:silver;\">+<\/span>\u00a0<span style=\"color:black;\">1<\/span><span style=\"color:maroon;\">)<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:blue;\">AS<\/span>\u00a0<span style=\"color:maroon;\">statement_text<\/span><span style=\"color:silver;\">,<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:maroon;\">total_logical_reads<\/span><span style=\"color:silver;\">,<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:maroon;\">qs<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">execution_count<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:blue;\">AS<\/span>\u00a0<span style=\"color:red;\">'Execution\u00a0Count'<\/span><span style=\"color:silver;\">,<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:maroon;\">total_logical_reads<\/span>\u00a0<span style=\"color:silver;\">\/<\/span>\u00a0<span style=\"color:maroon;\">qs<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">execution_count<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:blue;\">AS<\/span>\u00a0<span style=\"color:red;\">'AvgLogicalReads'<\/span><span style=\"color:silver;\">,<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:maroon;\">qs<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">execution_count<\/span>\u00a0<span style=\"color:silver;\">\/<\/span>\u00a0<span style=\"color:fuchsia;\"><i>Datediff<\/i><\/span><span style=\"color:maroon;\">(<\/span><span style=\"color:maroon;\">minute<\/span><span style=\"color:silver;\">,<\/span>\u00a0<span style=\"color:maroon;\">qs<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">creation_time<\/span><span style=\"color:silver;\">,<\/span>\u00a0<span style=\"color:fuchsia;\"><i>Getdate<\/i><\/span><span style=\"color:maroon;\">(<\/span><span style=\"color:maroon;\">)<\/span><span style=\"color:maroon;\">)<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:blue;\">AS<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:red;\">'Calls\/minute'<\/span><span style=\"color:silver;\">,<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:maroon;\">qs<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">total_worker_time<\/span>\u00a0<span style=\"color:silver;\">\/<\/span>\u00a0<span style=\"color:maroon;\">qs<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">execution_count<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:blue;\">AS<\/span>\u00a0<span style=\"color:red;\">'AvgWorkerTime'<\/span><span style=\"color:silver;\">,<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:maroon;\">qs<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">total_worker_time<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:blue;\">AS<\/span>\u00a0<span style=\"color:red;\">'TotalWorkerTime'<\/span><span style=\"color:silver;\">,<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:maroon;\">qs<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">total_elapsed_time<\/span>\u00a0<span style=\"color:silver;\">\/<\/span>\u00a0<span style=\"color:maroon;\">qs<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">execution_count<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:blue;\">AS<\/span>\u00a0<span style=\"color:red;\">'AvgElapsedTime'<\/span><span style=\"color:silver;\">,<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:maroon;\">qs<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">total_logical_writes<\/span><span style=\"color:silver;\">,<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:maroon;\">qs<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">max_logical_reads<\/span><span style=\"color:silver;\">,<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:maroon;\">qs<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">max_logical_writes<\/span><span style=\"color:silver;\">,<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:maroon;\">qs<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">total_physical_reads<\/span><span style=\"color:silver;\">,<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:maroon;\">qt<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">dbid<\/span><span style=\"color:silver;\">,<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:maroon;\">qp<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">query_plan<\/span> \n<span style=\"color:blue;\">FROM<\/span>\u00a0\u00a0\u00a0<span style=\"color:maroon;\">sys<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">dm_exec_query_stats<\/span>\u00a0<span style=\"color:blue;\">AS<\/span>\u00a0<span style=\"color:maroon;\">qs<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:blue;\">CROSS<\/span>\u00a0<span style=\"color:maroon;\">APPLY<\/span>\u00a0<span style=\"color:maroon;\">sys<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:#ff0080;\"><b>dm_exec_sql_text<\/b><\/span><span style=\"color:maroon;\">(<\/span><span style=\"color:maroon;\">qs<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">sql_handle<\/span><span style=\"color:maroon;\">)<\/span>\u00a0<span style=\"color:blue;\">AS<\/span>\u00a0<span style=\"color:maroon;\">qt<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:blue;\">OUTER<\/span>\u00a0<span style=\"color:maroon;\">APPLY<\/span>\u00a0<span style=\"color:maroon;\">sys<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:#ff0080;\"><b>dm_exec_query_plan<\/b><\/span><span style=\"color:maroon;\">(<\/span><span style=\"color:maroon;\">qs<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">plan_handle<\/span><span style=\"color:maroon;\">)<\/span>\u00a0<span style=\"color:maroon;\">qp<\/span> \n<span style=\"color:blue;\">WHERE<\/span>\u00a0\u00a0<span style=\"color:maroon;\">qt<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">dbid<\/span>\u00a0<span style=\"color:silver;\">=<\/span>\u00a0<span style=\"color:fuchsia;\"><i>Db_id<\/i><\/span><span style=\"color:maroon;\">(<\/span><span style=\"color:maroon;\">)<\/span>\u00a0<span style=\"color:green;\"><i>--\u00a0Filter\u00a0by\u00a0current\u00a0database<\/i><\/span> \n<span style=\"color:blue;\">ORDER<\/span>\u00a0\u00a0<span style=\"color:blue;\">BY<\/span>\u00a0<span style=\"color:maroon;\">total_logical_reads<\/span>\u00a0<span style=\"color:blue;\">DESC<\/span>\u00a0 <\/span><\/pre>\n<\/blockquote>\n","protected":false},"excerpt":{"rendered":"<p>Problem Statement: Script to find top 10 high memory consuming Queries. Solution: SELECT\u00a0TOP\u00a010\u00a0Object_name(qt.objectid) \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0AS\u00a0&#8216;SP\u00a0Name&#8217;, \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Substring(qt.text,\u00a0(\u00a0qs.statement_start_offset\u00a0\/\u00a02\u00a0)\u00a0+\u00a01, \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0( \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0(\u00a0CASE\u00a0qs.statement_end_offset \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0WHEN\u00a0-1\u00a0THEN\u00a0Datalength(qt.text) \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ELSE\u00a0qs.statement_end_offset \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0END[&#8230;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_mi_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0},"categories":[17,23,27],"tags":[75,111,137,152,170],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/pramodsingla.com\/index.php?rest_route=\/wp\/v2\/posts\/1219"}],"collection":[{"href":"https:\/\/pramodsingla.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/pramodsingla.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/pramodsingla.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/pramodsingla.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1219"}],"version-history":[{"count":0,"href":"https:\/\/pramodsingla.com\/index.php?rest_route=\/wp\/v2\/posts\/1219\/revisions"}],"wp:attachment":[{"href":"https:\/\/pramodsingla.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1219"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/pramodsingla.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1219"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/pramodsingla.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1219"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}