{"id":6343,"date":"2019-03-09T13:01:13","date_gmt":"2019-03-09T13:01:13","guid":{"rendered":"https:\/\/ittutorial.org\/?p=6343"},"modified":"2019-11-28T07:04:41","modified_gmt":"2019-11-28T07:04:41","slug":"sql-server-databases-cpu-usage-stats","status":"publish","type":"post","link":"https:\/\/ittutorial.org\/sql-server-databases-cpu-usage-stats\/","title":{"rendered":"SQL Server Databases CPU Usage Stats"},"content":{"rendered":"

Hi,<\/p>\n

Generally There are many databases on SQL Server Instance. Running databases into same instance is very important for SQL Server license and consolidation.<\/p>\n

\"\"<\/p>\n

<\/p>\n\n

When many databases run in same SQL Server Instance then Resource usage of databases are very criticial.<\/p>\n

If any database uses lots of CPU resource then we should monitor database and why that database consume more CPU resource than others.<\/p>\n

We can learn that all databases CPU resources usage with following script.<\/p>\n

WITH DB_CPU_STATS_ON_INSTANCE\r\nAS\r\n(SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName], SUM(total_worker_time) AS [CPU_Time_Ms]\r\nFROM sys.dm_exec_query_stats AS qs\r\nCROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID] \r\nFROM sys.dm_exec_plan_attributes(qs.plan_handle)\r\nWHERE attribute = N'dbid') AS F_DB\r\nGROUP BY DatabaseID)\r\nSELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [row_num],\r\nDatabaseName, [CPU_Time_Ms], \r\nCAST([CPU_Time_Ms] * 1.0 \/ SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPUPercent]\r\nFROM DB_CPU_STATS_ON_INSTANCE\r\nWHERE DatabaseID > 4 \r\nAND DatabaseID <> 32767 \r\nORDER BY row_num OPTION (RECOMPILE);\r\n\r\n\r\n<\/pre>\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, Generally There are many databases on SQL Server Instance. Running databases into same instance is very important for SQL Server license and consolidation.<\/p>\n","protected":false},"author":1,"featured_media":6287,"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":[2353,1028,1031,1600,1610,2231,2351,2350,2352,2349,2230,2229],"class_list":["post-6343","post","type-post","status-publish","format-standard","has-post-thumbnail","","category-sql-scripts","category-sql-server-2","category-sql-server-dba-scripts","tag-database-cpu-usage-stats","tag-mehmet-deveci","tag-mehmet-salih-deveci","tag-sql-server-2012","tag-sql-server-2014","tag-sql-server-2019","tag-sql-server-cpu-stats","tag-sql-server-cpu-usage-stats","tag-sql-server-database-cpu-stats","tag-sql-server-databases-cpu-usage-stats","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-database.png","jetpack_sharing_enabled":true,"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/6343","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=6343"}],"version-history":[{"count":2,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/6343\/revisions"}],"predecessor-version":[{"id":12965,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/6343\/revisions\/12965"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media\/6287"}],"wp:attachment":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media?parent=6343"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/categories?post=6343"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/tags?post=6343"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}