{"id":6252,"date":"2019-03-07T07:58:19","date_gmt":"2019-03-07T07:58:19","guid":{"rendered":"https:\/\/ittutorial.org\/?p=6252"},"modified":"2019-11-28T07:07:10","modified_gmt":"2019-11-28T07:07:10","slug":"sql-server-performance-top-cpu-query","status":"publish","type":"post","link":"https:\/\/ittutorial.org\/sql-server-performance-top-cpu-query\/","title":{"rendered":"SQL Server Performance TOP CPU Query -1"},"content":{"rendered":"

Hi,<\/p>\n

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>\n

<\/p>\n\n

 <\/p>\n

SQL Server DBA should monitor database everytime and if there are many sqls which is running long execution time or\u00a0consuming a lots of CPU resource then it should be reported to the developer and developer and dba should examine these sqls.<\/p>\n

 <\/p>\n

You can find TOP CPU queries in SQL Server database with following query.<\/p>\n

select top 50\r\nquery_stats.query_hash,\r\nSUM(query_stats.total_worker_time) \/ SUM(query_stats.execution_count) as avgCPU_USAGE,\r\nmin(query_stats.statement_text) as QUERY\r\nfrom (\r\nselect qs.*,\r\nSUBSTRING(st.text,(qs.statement_start_offset\/2)+1,\r\n((case statement_end_offset\r\nwhen -1 then DATALENGTH(st.text)\r\nelse qs.statement_end_offset end\r\n- qs.statement_start_offset)\/2) +1) as statement_text\r\nfrom sys.dm_exec_query_stats as qs\r\ncross apply sys.dm_exec_sql_text(qs.sql_handle) as st \r\n) as query_stats\r\ngroup by query_stats.query_hash\r\norder by 2 desc<\/pre>\n
 <\/p>\n\n
Query result will be like following screenshot<\/p>\n
\"\"<\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
Do you want to learn Microsoft SQL Server DBA Tutorials for Beginners, then read the following articles.<\/span><\/h4>\n
https:\/\/ittutorial.org\/sql-server-tutorials-microsoft-database-for-beginners\/<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
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":6253,"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,2237,2240,2239,2241,2242,2230,2229],"class_list":["post-6252","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-select-inventory","tag-sql-server-top-cpu","tag-sql-server-top-cpu-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\/03\/sql-server-top_query.png","jetpack_sharing_enabled":true,"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/6252","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=6252"}],"version-history":[{"count":3,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/6252\/revisions"}],"predecessor-version":[{"id":12978,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/6252\/revisions\/12978"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media\/6253"}],"wp:attachment":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media?parent=6252"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/categories?post=6252"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/tags?post=6252"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}