{"id":6374,"date":"2019-03-11T08:08:50","date_gmt":"2019-03-11T08:08:50","guid":{"rendered":"https:\/\/ittutorial.org\/?p=6374"},"modified":"2019-11-28T07:01:30","modified_gmt":"2019-11-28T07:01:30","slug":"sql-server-performance-top-io-query-2","status":"publish","type":"post","link":"https:\/\/ittutorial.org\/sql-server-performance-top-io-query-2\/","title":{"rendered":"SQL Server Performance TOP IO Query -2"},"content":{"rendered":"<p>Hi,<\/p>\n<p>If you got slowness complaint from customer,\u00a0 you need to monitor SQL Server Instance and database which sql is consuming a lots of resource.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-6247\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/03\/sql-server.png\" alt=\"\" width=\"466\" height=\"383\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/03\/sql-server.png 466w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/03\/sql-server-300x247.png 300w\" sizes=\"auto, (max-width: 466px) 100vw, 466px\" \/><\/p>\n<p><!--more--><\/p>\n\n<p>&nbsp;<\/p>\n<p>SQL Server DBA should monitor database everytime and if there are many sqls which is running long execution time or\u00a0consuming a lots of IO resource then it should be reported to the developer and developer and dba should examine these sqls.<\/p>\n<p>&nbsp;<\/p>\n<p>You can find TOP 50 IO queries in SQL Server database with following query.<\/p>\n<p>&nbsp;<\/p>\n<pre>select \r\n    q.[text],\r\nSUBSTRING(q.text, (highest_cpu_queries.statement_start_offset\/2)+1, \r\n        ((CASE highest_cpu_queries.statement_end_offset\r\n          WHEN -1 THEN DATALENGTH(q.text)\r\n         ELSE highest_cpu_queries.statement_end_offset\r\n         END - highest_cpu_queries.statement_start_offset)\/2) + 1) AS statement_text,    \r\n    \r\n    highest_cpu_queries.total_worker_time,\r\n    highest_cpu_queries.total_logical_reads,\r\n\thighest_cpu_queries.last_execution_time,\r\n    highest_cpu_queries.execution_count,\r\n    q.dbid,\r\n    q.objectid,\r\n    q.number,\r\n    q.encrypted,\r\n     highest_cpu_queries.plan_handle\r\nfrom \r\n    (select top 50 \r\n          qs.last_execution_time,\r\n          qs.execution_count,\r\n        qs.plan_handle, \r\n        qs.total_worker_time,\r\n        qs.statement_start_offset,\r\n        qs.statement_end_offset,\r\n        qs.total_logical_reads\r\n    from \r\n        sys.dm_exec_query_stats qs\r\n    order by qs.total_worker_time desc) as highest_cpu_queries\r\n    cross apply sys.dm_exec_sql_text(plan_handle) as q\r\norder by highest_cpu_queries.total_logical_reads desc;<\/pre>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<h4><span style=\"color: #ff0000;\">Do you want to learn Microsoft SQL Server DBA Tutorials for Beginners, then read the following articles.<\/span><\/h4>\n<p><a href=\"https:\/\/ittutorial.org\/sql-server-tutorials-microsoft-database-for-beginners\/\">https:\/\/ittutorial.org\/sql-server-tutorials-microsoft-database-for-beginners\/<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Hi, If you got slowness complaint from customer,\u00a0 you need to monitor SQL Server Instance and database which sql is consuming a lots of resource.<\/p>\n","protected":false},"author":1,"featured_media":5171,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[3828,2227,3827],"tags":[1028,1031,1600,1610,2231,2243,2238,2244,2246,2237,2240,2239,2245,2241,2242,2230,2229],"class_list":["post-6374","post","type-post","status-publish","format-standard","has-post-thumbnail","","category-sql-scripts","category-sql-server-2","category-sql-server-dba-scripts","tag-mehmet-deveci","tag-mehmet-salih-deveci","tag-sql-server-2012","tag-sql-server-2014","tag-sql-server-2019","tag-sql-server-performance-scripts","tag-sql-server-performance-top-cpu-query","tag-sql-server-performance-top-io-query","tag-sql-server-scripts","tag-sql-server-select-inventory","tag-sql-server-top-cpu","tag-sql-server-top-cpu-query","tag-sql-server-top-io-query","tag-sql-server-top-queries","tag-sql-server-top-query","tag-sql-server-useful-query","tag-sql-server-useful-script"],"aioseo_notices":[],"jetpack_featured_media_url":"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/01\/sql-server-perf-icon.jpg","jetpack_sharing_enabled":true,"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/6374","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/comments?post=6374"}],"version-history":[{"count":2,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/6374\/revisions"}],"predecessor-version":[{"id":12956,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/6374\/revisions\/12956"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media\/5171"}],"wp:attachment":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media?parent=6374"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/categories?post=6374"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/tags?post=6374"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}