{"id":6492,"date":"2019-03-12T08:52:57","date_gmt":"2019-03-12T08:52:57","guid":{"rendered":"https:\/\/ittutorial.org\/?p=6492"},"modified":"2019-11-28T07:00:42","modified_gmt":"2019-11-28T07:00:42","slug":"sql-server-update-database-statistic","status":"publish","type":"post","link":"https:\/\/ittutorial.org\/sql-server-update-database-statistic\/","title":{"rendered":"SQL Server Update Database Statistic"},"content":{"rendered":"<p>Hi,<\/p>\n<p>Database objects stats should be up to date for making the right decision of SQL Server optimizer.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-6274\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/03\/sql-server-optimizer.png\" alt=\"\" width=\"216\" height=\"384\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/03\/sql-server-optimizer.png 216w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/03\/sql-server-optimizer-169x300.png 169w\" sizes=\"auto, (max-width: 216px) 100vw, 216px\" \/><\/p>\n<p><!--more--><\/p>\n\n<p>&nbsp;<\/p>\n<p>If database objects stats are not up to date then SQL Server database optimizer will make a wrong decision for execution plan of transaction.<\/p>\n<p>You can update all database statistics with following script in a SQL Server Instance.<\/p>\n<p>&nbsp;<\/p>\n<p>You should execute following query in a spare time of database for example evening or weekends.<\/p>\n<p>&nbsp;<\/p>\n<pre>DECLARE @SQL VARCHAR(1000)  \r\nDECLARE @DB sysname  \r\n\r\nDECLARE curDB CURSOR FORWARD_ONLY STATIC FOR  \r\n   SELECT [name]  \r\n   FROM master..sysdatabases \r\n   WHERE [name] NOT IN ('model', 'tempdb') \r\n   ORDER BY [name] \r\n     \r\nOPEN curDB  \r\nFETCH NEXT FROM curDB INTO @DB  \r\nWHILE @@FETCH_STATUS = 0  \r\n   BEGIN  \r\n       SELECT @SQL = 'USE [' + @DB +']' + CHAR(13) + 'EXEC sp_updatestats' + CHAR(13)  \r\n       PRINT @SQL  \r\n       FETCH NEXT FROM curDB INTO @DB  \r\n   END  \r\n    \r\nCLOSE curDB  \r\nDEALLOCATE curDB<\/pre>\n<p>&nbsp;<\/p>\n\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, Database objects stats should be up to date for making the right decision of SQL Server optimizer.<\/p>\n","protected":false},"author":1,"featured_media":6274,"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":[2227],"tags":[1028,1031,2425,1600,1610,2231,2264,2263,2228,2268,2271,2272,2259,2232,2273,2237,2236,2267,2426,2270,2269,2424,2423,2230,2229,2265,2266],"class_list":["post-6492","post","type-post","status-publish","format-standard","has-post-thumbnail","","category-sql-server-2","tag-mehmet-deveci","tag-mehmet-salih-deveci","tag-sp_updatestats","tag-sql-server-2012","tag-sql-server-2014","tag-sql-server-2019","tag-sql-server-active-sessions","tag-sql-server-active-sessions-and-status","tag-sql-server-collection-inventory-script","tag-sql-server-database-objects-statistics","tag-sql-server-database-stats","tag-sql-server-execution-plan","tag-sql-server-fragmented-index","tag-sql-server-inventory-script","tag-sql-server-optimizer","tag-sql-server-select-inventory","tag-sql-server-select-machine-name","tag-sql-server-sessions","tag-sql-server-sp_updatestats","tag-sql-server-statistics","tag-sql-server-stats","tag-sql-server-stats-update","tag-sql-server-update-stats","tag-sql-server-useful-query","tag-sql-server-useful-script","tag-sql-server-whoisactive","tag-whoisactive"],"aioseo_notices":[],"jetpack_featured_media_url":"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/03\/sql-server-optimizer.png","jetpack_sharing_enabled":true,"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/6492","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=6492"}],"version-history":[{"count":2,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/6492\/revisions"}],"predecessor-version":[{"id":12951,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/6492\/revisions\/12951"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media\/6274"}],"wp:attachment":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media?parent=6492"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/categories?post=6492"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/tags?post=6492"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}