How to Update SQL Server Statistics in Heavy Workload Database

The estimated reading time for this post is 8 minutes

Sometimes we do need to disable AUTO_STATS_UPDATE or AUTO_STATS_UPDATE_ASYNC due to many reasons such as database performance impact in heavy workload environment. Updating statistics based on regular schedule also takes long time and impact the database performance.

Updating statistics are necessary due to SQL Server engine behavior, updated statistics object can lead the database and queries to have higher performance compare to non-updated statistics object. In the environment that automatic statistics update is disabled, then how should we perform this operation? is it possible to update the statistics object on different server and bring the result into the production server without harming the performance?

Imagine that statistics are updated within a second in production environment without hurting the overall performance. Since SQL Server 2005, UPDATE STATISTICS command has an optional parameter called STATS_STREAM which is the binary of the statistic object and updating the statistic object with this option can replace the entire statistics histogram and content.

There is a table called dbo.StressData which contains many rows with two columns as ID and Padding. The following figure shows the Padding column statistics object and rows are 942469 with density of 0.04589024.

The mentioned table is heavily transactional and updating statistics are scheduled to improve the insert and update commands performance, but updating statistics takes quite long time and result in transaction suspension due to update statistics operations. To update the statistics without hurting the production database performance, we do need to take a backup and restore into UAT/DEV server and then update the Padding statistic object in recently restored database.

The following script restores the StressDB database into different server (or as different database) then update the statistic object.

BACKUP DATABASE [StressDB] TO DISK = 'E:\StressDB.bak' WITH INIT;
USE [master]
RESTORE DATABASE [StressDB2] FROM  DISK = N'E:\StressDB.bak' 
WITH  MOVE N'StressDB' TO N'E:\StressDB.mdf',  
MOVE N'StressDB_log' TO N'E:\StressDB_log.ldf', REPLACE;

Once the database is restored, we do need to fully update the Padding statistic object. The following figure shows the statistic object detail once it is update in StressDB2 database. have a look at the row numbers which increased to 1002469 and density value changed to 0.03773573.

Now, it is the time to update the Padding statistic object in StressDB immediately by using the statistic stream from StressDB2 database. The following script updates the statistics with STATS_STREAM option and it works very quickly.

USE [StressDB2]
--Copy the Stats_Stream value;
DBCC SHOW_Statistics ('[dbo].[StressData]','Padding') WITH  Stats_Stream;
Go
USE StressDB
DROP STATISTICS dbo.StressData.Padding;
--Create the statistics object with Stats_Stream value; 
CREATE STATISTICS [Padding] ON [dbo].[StressData]([Padding]) WITH Stats_Stream = 0x01000000010000000000000000000000EFC74A90000000000D2B010000000000CD2A010000000000A7020000A7000000320000000000000008D000340000000007000000CE53B80071A70000E54B0F0000000000E54B0F0000000000C9901A3DA42ADF370000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000AB000000AB00000001000000100000000000D84150BE7449000000000000D84100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001B000000000000000000000000000000BE2600000000000001290100000000005129010000000000592901000000000058050000000000008A05000000000000BC05000000000000EE05000000000000200600000000000052060000000000008406000000000000B606000000000000E8060000000000001A070000000000004C070000000000007E07000000000000B007000000000000E207000000000000140800000000000046080000000000007808000000000000AA08000000000000DC080000000000000E0900000000000040090000000000007209000000000000A409000000000000D609000000000000080A0000000000003A0A0000000000006C0A0000000000009E0A000000000000D00A000000000000020B000000000000340B000000000000660B000000000000980B000000000000CA0B000000000000FC0B0000000000002E0C000000000000600C000000000000920C000000000000C40C000000000000F60C000000000000280D0000000000005A0D0000000000008C0D000000000000BE0D000000000000F00D000000000000220E000000000000540E000000000000860E000000000000B80E000000000000EA0E0000000000001C0F0000000000004E0F000000000000800F000000000000B20F000000000000E40F000000000000161000000000000048100000000000007A10000000000000AC10000000000000DE10000000000000101100000000000042110000000000007411000000000000A611000000000000D8110000000000000A120000000000003C120000000000006E12000000000000A012000000000000D2120000000000000413000000000000361300000000000068130000000000009A13000000000000CC13000000000000FE13000000000000301400000000000062140000000000009414000000000000C614000000000000F8140000000000002A150000000000005C150000000000008E15000000000000C015000000000000F215000000000000241600000000000056160000000000008816000000000000BA16000000000000EC160000000000001E1700000000000050170000000000008217000000000000B417000000000000E61700000000000018180000000000004A180000000000007C18000000000000AE18000000000000E018000000000000121900000000000044190000000000007619000000000000A819000000000000DA190000000000000C1A0000000000003E1A000000000000701A000000000000A21A000000000000D41A000000000000061B000000000000381B0000000000006A1B0000000000009C1B000000000000CE1B000000000000001C000000000000321C000000000000641C000000000000961C000000000000C81C000000000000FA1C0000000000002C1D0000000000005E1D000000000000901D000000000000C21D000000000000F41D000000000000261E000000000000581E0000000000008A1E000000000000BC1E000000000000EE1E000000000000201F000000000000521F000000000000841F000000000000B61F000000000000E81F0000000000001A200000000000004C200000000000007E20000000000000B020000000000000E220000000000000142100000000000046210000000000007821000000000000AA21000000000000DC210000000000000E2200000000000040220000000000007222000000000000A422000000000000D62200000000000008230000000000003A230000000000006C230000000000009E23000000000000D0230000000000000224000000000000342400000000000066240000000000009824000000000000CA24000000000000FC240000000000002E2500000000000060250000000000009225000000000000C425000000000000F62500000000000028260000000000005A260000000000008C26000000000000300010000000E040000000000000803F04000001003200323031372D30352D31322030393A34383A34342E373039383435313000100000006743008063457BBFEC4104000001003200323031372D30352D31322030393A34383A34352E35343036323632300010000000754300801D45D477564204000001003200323031372D30352D31322030393A34383A34352E36363031383132300010000000A842007075455F1E054204000001003200323031372D30352D31322030393A34383A35382E303838303236353000100000008C4300A8B645DCA5A44204000001003200323031372D30352D31322030393A34383A35382E33353831383034300010000000444300E07545ED73944204000001003200323031372D30352D31322030393A34383A35382E353238323439333000100000008C430098B3456D1D844204000001003200323031372D30352D31322030393A34383A35382E37373832393338300010000000854300B0A64558EEB74204000001003200323031372D30352D31322030393A34383A35392E303138353038363000100000008C430090BC4559538C4204000001003200323031372D30352D31322030393A34383A35392E323738323432323000100000008C4300D07945390EDE4204000001003200323031372D30352D31322030393A34383A35392E343538333439383000100000008C4300F02445B76D574204000001003200323031372D30352D31322030393A34383A35392E353738333836303000100000008C4300F08F45DDD39E4204000001003200323031372D30352D31322030393A34383A35392E373738363837353000100000808843000891459A99844204000001003200323031372D30352D31322030393A34383A35392E393738383733303000100000006E4300D00245D1453E4204000001003200323031372D30352D31322030393A34393A30302E30373837323830300010000080884300B0834500B0034304000001003200323031372D30352D31322030393A34393A30302E323539303030343000100000008C4300E02145F06C5C4204000001003200323031372D30352D31322030393A34393A30302E333739303533343000100000008543009882451CC79A4204000001003200323031372D30352D31322030393A34393A30302E36303932303131300010000000044200F05E458BA3DD4004000001003200323031372D30352D31322030393A34393A31322E31393036323833300010000000184200A07245DD97034104000001003200323031372D30352D31322030393A34393A31342E30333130393133300010000000184200E07C451E970E4104000001003200323031372D30352D31322030393A34393A31352E37353235303635300010000000104200B033455B131E4104000001003200323031372D30352D31322030393A34393A31362E37323333353034300010000000044200A08B45D6BC194104000001003200323031372D30352D31322030393A34393A31382E333233393935323000100000001C4200907745E0E7254104000001003200323031372D30352D31322030393A34393A31392E363235353032383000100000001C4200108A4565F82E4104000001003200323031372D30352D31322030393A34393A32302E39323636343639300010000000184200E059455F05434104000001003200323031372D30352D31322030393A34393A32312E39323733313730300010000000184200106F45F86C324104000001003200323031372D30352D31322030393A34393A32332E30323833353437300010000000144200808045E011244104000001003200323031372D30352D31322030393A34393A32342E353238363133343000100000001C4200EC0B4655D5254104000001003200323031372D30352D31322030393A34393A32372E3533303837313330001000000020420090BC45D3D24A4104000001003200323031372D30352D31322030393A34393A32392E32333137393331300010000000184200E09445D3D23A4104000001003200323031372D30352D31322030393A34393A33302E363333333732393000100000001C4200F04D4546262F4104000001003200323031372D30352D31322030393A34393A33312E36333430323331300010000000204200308245DC3C2C4104000001003200323031372D30352D31322030393A34393A33322E39333531323230300010000000184200B010453333394104000001003200323031372D30352D31322030393A34393A33332E363335383235383000100000001C4200A8AA4538672E4104000001003200323031372D30352D31322030393A34393A33352E34393639353432300010000000D84100D039450ABA134104000001003200323031372D30352D31322030393A34393A33362E36333738343935300010000000204200789245968B254104000001003200323031372D30352D31322030393A34393A33382E333338393738343000100000000042006057453F2C2A4104000001003200323031372D30352D31322030393A34393A33392E3433393837333530001000000008420068B545F1931B4104000001003200323031372D30352D31322030393A34393A34312E353430383836343000100000000C4200501745521B194104000001003200323031372D30352D31322030393A34393A34322E3334313834353530001000000014420058AA451A771B4104000001003200323031372D30352D31322030393A34393A34342E3338343038373330001000000020420078BB45D40B0F4104000001003200323031372D30352D31322030393A35313A30372E363936303939303000100000001C4200005D4599EA354104000001003200323031372D30352D31322030393A35313A30382E36393637383030300010000000184200303945462F254104000001003200323031372D30352D31322030393A35313A30392E353937323237353000100000001C42003071457437374104000001003200323031372D30352D31322030393A35313A31302E363938363038363000100000001C420030AE45CF88344104000001003200323031372D30352D31322030393A35313A31322E33303938333530300010000000104200606D45E0223C4104000001003200323031372D30352D31322030393A35313A31332E34303035313339300010000000204200C0A2456D193C4104000001003200323031372D30352D31322030393A35313A31342E39303136343134300010000000184200005B45C9673D4104000001003200323031372D30352D31322030393A35313A31352E39303231303933300010000000104200D06145387A3A4104000001003200323031372D30352D31322030393A35313A31372E303033303736323000100000001442000084458957114104000001003200323031372D30352D31322030393A35313A31382E323034313432303000100000000C4200B07445B0F8324104000001003200323031372D30352D31322030393A35313A31392E34303530313034300010000000F84100588B453D25224104000001003200323031372D30352D31322030393A35313A32312E303036313935373000100000001C4200202E4526CB0F4104000001003200323031372D30352D31322030393A35313A32322E33313734353633300010000000204200906C45B7B9F24004000001003200323031372D30352D31322030393A35313A32342E333038333737303000100000001C4200201345B5A0ED4004000001003200323031372D30352D31322030393A35313A32352E35303837333034300010000000144200007F4530FC214104000001003200323031372D30352D31322030393A35313A32372E313739393634383000100000001C4200D88345D1C53F4104000001003200323031372D30352D31322030393A35313A32382E333830343132343000100000000C4200788345AB4A2F4104000001003200323031372D30352D31322030393A35313A32392E36383136373030300010000000084200B07F45826D394104000001003200323031372D30352D31322030393A35313A33302E383632373836353000100000002042006880450993284104000001003200323031372D30352D31322030393A35313A33322E303533393230353000100000000C4200C8BF458DDF2C4104000001003200323031372D30352D31322030393A35313A33332E383534383734373000100000001C420038804506083B4104000001003200323031372D30352D31322030393A35313A33352E313135323931323000100000001C4200988045A6A5414104000001003200323031372D30352D31322030393A35313A33362E33343633323639300010000000C04100D8BF45E9B93E4104000001003200323031372D30352D31322030393A35313A33382E31383735393731300010000000084200A07F45965A494104000001003200323031372D30352D31322030393A35313A33392E3339373839363230001000000014420040FE44560C444104000001003200323031372D30352D31322030393A35313A33392E3937373839323630001000000018420048804543FA374104000001003200323031372D30352D31322030393A35313A34312E313538363436323000100000001C4200907F45FCEC3A4104000001003200323031372D30352D31322030393A35313A34322E32393932333138300010000000184200E0FD449A194B4104000001003200323031372D30352D31322030393A35313A34322E383739363233303000100000001C4200D07F4529E2484104000001003200323031372D30352D31322030393A35313A34342E3039303930393930001000000010420000FF445D74394104000001003200323031372D30352D31322030393A35313A34342E37353132373133300010000000144200407E456C91404104000001003200323031372D30352D31322030393A35313A34352E39363137343937300010000000104200707E450000314104000001003200323031372D30352D31322030393A35313A34372E32323235303931300010000000004200A8BF45467F394104000001003200323031372D30352D31322030393A35313A34392E30363433313437300010000000584200D07E458C60BA4104000001003200323031372D30352D31322030393A35313A35302E33353239303230300010000000604200A07F4515CB364104000001003200323031372D30352D31322030393A35313A35312E35353038393037300010000000744200E880450407624204000001003200323031372D30352D31322030393A35313A35322E373037323031343000100000003C420098E34557D4614204000001003200323031372D30352D31322030393A35313A35342E37333835353831300010000000644200207F451CC7624204000001003200323031372D30352D31322030393A35313A35352E3837393234323730001000000000420010A0451BF5594204000001003200323031372D30352D31322030393A35313A35372E3336333639363630001000000074420020004617215F4204000001003200323031372D30352D31322030393A35313A35392E363736333137343000100000005C4200D07F452346604204000001003200323031372D30352D31322030393A35323A30302E383332363238323000100000005C4200F07E45729C624204000001003200323031372D30352D31322030393A35323A30312E39373333313238300010000000704200007E4558AF5E4204000001003200323031372D30352D31322030393A35323A30332E313239363233353000100000005C42007400469E3B614204000001003200323031372D30352D31322030393A35323A30352E3432363631393430001000000010420050E345E68C614204000001003200323031372D30352D31322030393A35323A30372E34353739373535300010000000784200407E451E53534204000001003200323031372D30352D31322030393A35323A30382E36373637383935300010000000604200407F45E4C75F4204000001003200323031372D30352D31322030393A35323A30392E38333331303033300010000000584200D07D4543855E4204000001003200323031372D30352D31322030393A35323A31302E39383934313130300010000000604200F07F453162604204000001003200323031372D30352D31322030393A35323A31322E3134353732323330001000000058420010FF45CF9D5F4204000001003200323031372D30352D31322030393A35323A31342E343432373138333000100000000C4200503845E8905E4204000001003200323031372D30352D31322030393A35323A31352E32383635313236300010000000744200383D4630024F4204000001003200323031372D30352D31322030393A35323A31382E39353835373936300010000000744200C07D453C775E4204000001003200323031372D30352D31322030393A35323A32302E31313438393039300010000000544200AA8046E8215A4204000001003200323031372D30352D31322030393A35323A32342E38343935313433300010000000584200707E45ECC4504204000001003200323031372D30352D31322030393A35323A32362E303833393533383000100000004C420020B645BDC5204204000001003200323031372D30352D31322030393A35323A32382E333635333234323000100000006C42000087451E1EFE4104000001003200323031372D30352D31322030393A35323A33302E353036303631333000100000006842008003458887F74104000001003200323031372D30352D31322030393A35323A33312E35383432343339300010000000004000307A45C50B384204000001003200323031372D30352D31322030393A35323A33322E39353933313439300010000000704200B07E455555594204000001003200323031372D30352D31322030393A35323A33342E31343638373739300010000000404200068046519E574204000001003200323031372D30352D31322030393A35323A33382E39313237353233300010000000504200207E45A6C85B4204000001003200323031372D30352D31322030393A35323A34302E3038343638393730001000000040420060804673A4594204000001003200323031372D30352D31322030393A35323A34342E383139333133373000100000005C4200F07E4528AF564204000001003200323031372D30352D31322030393A35323A34362E303232353031363000100000005842001800469E145C4204000001003200323031372D30352D31322030393A35323A34382E3336363337343730001000000054420058FF45D83B544204000001003200323031372D30352D31322030393A35323A35302E3738383337363630001000000060410070F245CA155C4204000001003200323031372D30352D31322030393A35323A35332E303037323434333000100000001C4200D4A0464A6B5A4204000001003200323031372D30352D31322030393A35323A35382E393133383034353000100000006C420058FF45F49E554204000001003200323031372D30352D31322030393A35333A30312E333230313739373000100000005042002C7D469FF55A4204000001003200323031372D30352D31322030393A35333A30352E393631303438323000100000003C4200F8FE4597085B4204000001003200323031372D30352D31322030393A35333A30382E33303439323139300010000000744200E8FF4588B3544204000001003200323031372D30352D31322030393A35333A31302E37323639323338300010000000744200E8FE45D8FA5A4204000001003200323031372D30352D31322030393A35333A31332E303730373936393000100000006C42005A80466EE2584204000001003200323031372D30352D31322030393A35333A31372E38323130343634300010000000504200307E45790D564204000001003200323031372D30352D31322030393A35333A31392E30323432333439300010000000604200F07F4500C04C4204000001003200323031372D30352D31322030393A35333A32302E3238393932363030001000000050420030404676BD584204000001003200323031372D30352D31322030393A35333A32332E383532363133323000100000006C4200F07F452FBA544204000001003200323031372D30352D31322030393A35333A32352E30373134323732300010000000344200884046CB0E5B4204000001003200323031372D30352D31322030393A35333A32382E36303238363236300010000000544200F8FE450A8E524204000001003200323031372D30352D31322030393A35333A33312E30343034393037300010000000504200F07F4566665A4204000001003200323031372D30352D31322030393A35333A33322E32323830353330300010000000504200A0FF45D4080D4204000001003200323031372D30352D31322030393A35333A33352E38363838373032300010000000144200F07F450080E34104000001003200323031372D30352D31322030393A35333A33382E31333436313339300010000000504100407F45FE90104...

 

Hamid J. Fard

I am SQL Server Data Platform Expert with more than 9 years’ of professional experience, I am currently Microsoft Certified Master: SQL Server 2008, Microsoft Certified Solutions Master: Charter-Data Platform, Microsoft Data Platform MVP and CIW Database Design Specialist. I also do Pig, Hive and Sqoop development regarding Hadoop Big Data platform. After a few years of being a production database administrator I jumped into the role of Data Platform Expert. Being a consultant allows me to work directly with customers to help solve questions regarding database issues for SQL Server.

More Posts

Follow Me:
FacebookLinkedIn


Leave a Comment

2 Comments on "How to Update SQL Server Statistics in Heavy Workload Database"

Notify of
avatar
1000
Sort by:   newest | oldest | most voted
prince rastogi
Guest

Hey Hamid,

From where we will collect the value of Stats_Stream?

wpDiscuz