1 / 43

Parallel Database Maintenance with 24/7 Systems and Huge DBs

This presentation by Grzegorz Łyp at SQLBits 2018 delves into strategies for efficient database maintenance, covering topics such as index fragmentation, statistics, and speeding up maintenance tasks for extensive databases. Discover practical demonstrations using the World Wide Importers database and learn essential maintenance techniques for improving database performance and reliability.

nswanson
Download Presentation

Parallel Database Maintenance with 24/7 Systems and Huge DBs

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Parallel Database Maintenance with 24/7 Systems and Huge DBs SQLBits 2018

  2. Grzegorz Łyp • MS SQL Architect & Optimizer • MS SQL, .NET & Java Developer • Product and Project Manager • 15 years in professional IT • 10 years in MS SQL • Member of Data Community Poland • Blog: www.lyp.pl/blog • Mail: grzegorz.lyp@assecobs.pl • Twitter: @GrzegorzLyp • LinkedIn: https://www.linkedin.com/in/grzegorzlyp/ • Facebook: https://www.facebook.com/profile.php?id=100001054878385

  3. Agenda • Background • Database Maintenance • Speedupmaintenancetasks • Lockingduringdatabasemaintenance • Creatingspecificstatistics

  4. DEMOS • WorldWideImporters • https://github.com/Microsoft/sql-server-samples/releases/tag/wide-world-importers-v1.0

  5. Background • Once upon a time there was… a database • At the moment of start it worked for 8 hours a day… • DBA had rest of the time for maintenance

  6. Background • As the databaseownercompanygrewdatabaseusagegrew • Aftercertainamount of time the maintenacetime went down to „0” • DBA losthisjob as he could not do the databasemaintenance in suchconditions • Did he try hard ? • Let’stry to satisfy 24/7 system maintenanceneeds!

  7. Database Maintenance • Indexes • Statistics

  8. Indexes • Index fragmentation • Whatis the actualvalue of fragmentation ? • Isfragmentationbadorgood ? • DEMO

  9. Indexes

  10. Indexes • Page splitting does not directly depend on index fragmentation • Read ahead is off when reading data from pages that are not neighbours • Data sizenecessary on physicalstorage and in memoryhigher for higherfragmentation • avg_fragmentation_in_percent is not enough for saving space - avg_page_space_user_in_percent gives additional details • Typically we assumethatat 5-20% of fragmentation REORGANIZE index and over 20% we should REBUILD

  11. REORGANIZE VS REBUILD

  12. Conslusions • Gatheringinformationaboutfragmentationcan be slow • REBUILD canonly run on Enterprise systems with ONLINE = ON and need a lot of freespace • REOGRANIZE isslow but itis not blockingeven in standard edition • It canblock on SQL Server 2014 SP2 (fixed in CU) • How to speedup index fragmentationgatheringprocess ? • How to speedup REORGANIZE ?

  13. Statistics • Settings • Auto CreateStatistics – verydangerous in case of unexpectedstatistics • Youcan’tseewhichstatisticsareused in query plan before SQL 2017 • Auto Update Statistics – unpredictible moment of statistics update • Causesrecompilations and possiblechanges in executionplans • For big tablesrefreshistoorare – especially for estimatorbefore SQL 2014 • Same for Auto Update StatisticsAsynchronously • Manual statisticsmaintenance • Periodical • Updatingstatisticscantakesometime • Defaultsample for update can be not enough for rarerecords • DEMO

  14. Conclusions • Auto CreateStatistics Off • Auto Update Statistics Off • Periodicalstatistics update (eveneveryday) • How to getpropersample ? • How to reducetime of statistics update ?

  15. How to getproper update statisticssample ? • Important for seeking with directvalues • Allseekvaluesshould be in the sample • For optimizer 120 orhighervalues out of statisticsare not of 0 cardinalitywhen no directvalueisused, but itmight be not enough

  16. SpeedupMaintenanceTasks

  17. Typicalmaintenanceprocess • Iterateacrossindexes • For each index • Get index fragmenation • Defragmentifnecessary (REORGANIZE or REBUILD) • Update statisticsifnecessary

  18. Proposedmaintenaceprocess • Iterateacrossindexes and makequeue • Spreadacrossnumber of threadseach index in queue • Single threadtask • Get index fragmenation • Defragmentifnecessary • Update statisticsifnecessary

  19. Proposedmaintenaceprocess 4 1 2 3 5 6 7 8 9 10 11 12 13 TIME

  20. Proposedmaintenace proces - queue 1 2 3 4 5 6 7 8 9 10 11 12 13

  21. Proposedmaintenace proces4 threads timing 9 Thread 1 13 1 5 Thread 2 6 2 10 7 8 12 3 Thread 3 11 4 Thread 4 TIME

  22. Proposedmaintenace proces4 threads timing vs Single thread Thread 1 13 9 5 1 Thread 2 2 6 10 7 8 12 3 Thread 3 11 4 Thread 4 TIME Single thread 4 1 2 3 5 6 7 8 9 10 11 12 13 TIME

  23. Proposedmaintenace proces – queue – order by length 13 4 11 3 5 6 9 10 1 2 12 7 8

  24. Proposedmaintenace proces4 threads timing – queueordered by length Thread 1 10 13 7 Thread 2 4 9 12 6 Thread 3 11 2 Thread 4 5 8 3 1 TIME

  25. Proposedmaintenace proces4 threads timing – comparison Thread 1 10 13 7 Thread 2 4 9 12 6 Thread 3 11 2 Thread 4 5 8 3 1 TIME Thread 1 13 9 5 1 Thread 2 2 6 10 7 8 12 3 Thread 3 11 4 Thread 4 TIME

  26. Proposedmaintenace proces • DEMO

  27. Proposedmaintenance proces profit

  28. Proposedmaintenance proces • Optimalnumber of threadscan be calculatedbased on real index operationtimes • The shortestpossibletimeis the time of the longest index to maintain • Morethreadsareapplicable as each index mainenanceis uniform to eachother

  29. Proposedmaintenaceprocess – v2 • Iterateacrossindexes and makequeue • Spreadacrossnumber of threadseach index in queue • Single threadperform • Get index fragmenation • Store index fragmentation in the table • Removeindexesbelowfragmentationlimits • Spreadacross numer of treadseach index in queue • Single threadperform • Do index maintenance (fragmentation & statistics)

  30. Proposedmaintenaceprocess – v2 • Have to measuretime for index fragmentationgathering and index maintenanceseparately • Should we split index defragmentation and statistics update ? • Thismight be version 3 – tryyourself • Isitbetter ?

  31. Split maintenancetaskintoparts Read currentfragmentation Do index defragmentation Update statistics 4 1 2 3 5 6 7 8 9 10 11 12 13 TIME

  32. Three stages – betterdecisions 9 2 12 1 6 12 5 Thread 1 1 3 10 2 9 10 Thread 2 6 2 7 9 13 5 13 3 4 13 Thread 3 11 7 8 4 Thread 4 5 7 8 11 11

  33. Proposedmaintenaceprocess – summary • Parallelmaintenanceshortensprocessingtime • Eachmaintenancetasksplit to 2 or 3 stages for betterresourcesuse • Operations timegather for betterthreadtasksallocation • Processcan be fullyautomated

  34. Paralleltasks - implementation

  35. Paralleltasks – how to implement ? • Jobs • Need to createjobs • No performance balancebuilt-in • Service broker • Need to create service broker objects (queues, services) • Number of parallelexecutionsconfigurable and performance dependent • Createdrequeststreated as system requests (cannotkill) • CLR • Unsafeassembly with multithreadimplementation • Need to implementbreakingmethod (killingdoes not work)

  36. Service Broker – paralleltasksimplementation DEMO T-SQL SourceQueue Destination Queue T-SQL T-SQL T-SQL

  37. Service Broker - conclusions • Easy to implement • Somedifficulties in error handling • Parallelismcontrolled by server with MAX_QUEUE_READERS suggestion • Fasterthreadallocationcan be obtained with CLR

  38. Conclusions

  39. Conclusions • Database index maintenancecanconsumetime and resources • For 24/7 systemsthereare no technicalbreaks and everytime we expect plan stability • Typicalmaintenance proces is single-threaded • Makingmaintenancemulti-threadreducetime and makebetteruse from resources • The finalsolutioncan be parametrizedaccording to specific hardware abilities (number of cores, storagetype) • We have non-blocking, adaptive, multi-threadedmaintenaceprocessthatcanworkanytime

  40. If we havemoretime… • BONUS DEMO

  41. Just like Jimi Hendrix …  We love to get feedback Please complete the session feedback forms

  42. SQLBits - It's all about the community... Please visit Community Corner, we are trying this year to get more people to learn about the SQL Community, equally if you would be happy to visit the community corner we’d really appreciate it.

  43. Q&A ThankYou for the attention

More Related