SQL Server Maintenance Best Practices

SWISS SQL SERVER SATURDAY A PASS Community Connections Event Session Objectives And Agenda with Maintenance in Mind  Integrity Checks  Database Files  Optimizations  Backups  Scheduling  Miscellaneous Tasks  Q&A 19 September 2009 SQL Server Saturday – Zürich, Switzerland SQL Server Maintenance Best Practices A DBA Task List  Hardware Roger Breu Technology Solution Professional DataPlatform Microsoft Schweiz GmbH roger.breu@microsoft.com www.microsoft.ch 19 September 2009 SQL Server Saturday – Zürich, Switzerland Maintenance – What, Why, When How  Maintenance Plans  What  Which maintenance operations are needed to ensure a reliable,     available, performing system?  Why  Understand each maintenance task and why it is needed  Backing up for the sake of backing up  When  Do you have a maintenance window?  Impact of maintenance on workload  Know which operations just add more work for you later Home-grown or Built-in? Only you can determine that Maint Plans are less flexible and can be limiting Home-grown may require more work up front Either way you MUST understand what is actually happening  How… and Where 19 September 2009 SQL Server Saturday – Zürich, Switzerland 19 September 2009 SQL Server Saturday – Zürich, Switzerland 1 Hardware and Maintenance    Why talk about Hardware? Maintenance is resource intensive  Rebuilding and reorganizing indexes SQL Server Maintenance Best Practices  What is the primary cause of corruption in SQL Server databases? Hardware ! Use SQLIOSim to test out your IO system Use database page checksums and backup checksums to detect hardware issues   19 September 2009 SQL Server Saturday – Zürich, Switzerland Hardware Configuration   RAID Uses  Memory  4-6GB per CPU Core are common RAID 1 – Mirrored  O/S, Logs, Tempdb CPUs  Mostly oversized, 2 Quads are common  Beware of Hyper Threaded processors  MAXDOP – Max Degree of Parallelism ○ Server-level setting ○ Statement-level setting  RAID 5 – Striped & Parity  Data (read intensive)  Not as preferred as it used to be due to performance  Drive Arrays  Know your disk configuration  RAID 0+1 or 1+0 - Striped & Mirrored  Data, Tempdb, Logs  RAID 0 – Striped  Temporary or Intermediate Backups 19 September 2009 SQL Server Saturday – Zürich, Switzerland 19 September 2009 SQL Server Saturday – Zürich, Switzerland 2 SAN Considerations   Are you in full control of your file and data placement on your SAN? You likely want to separate data and log for both performance and reliability  But that might not be your Storage Admin’s priority  The virtualization on the SAN can often lead to unmet expectations  Magnified by maintenance operations 19 September 2009 SQL Server Saturday – Zürich, Switzerland 19 September 2009 SQL Server Saturday – Zürich, Switzerland Typical SAN RAID 5 Stripe + Parity SQL Server Maintenance Best Practices Drive H: Drive G: 19 September 2009 SQL Server Saturday – Zürich, Switzerland 3 Database Integrity  Example: What Gets Checked For a Leaf Index Page?   DBCCs check the following in a database:  Consistency between the system tables  Consistency of disk space allocation structures  Index and data pages are correctly linked  Indexes are in their proper sort order  Text Pointers are consistent  Page offsets are reasonable  The data on each page is reasonable  Low-level checks on critical system tables  Data Purity checks  Row Overflow Pointers are consistent  Checks Service Broker internal objects  Checks indexed view and XML index contents  Torn page and Checksum errors Page and record audits B-tree checks  Page linkages and key ranges  Partitioning  Key ordering between records  Per-record checks  Complex columns  Computed columns  Data purity  1-to-1 mapping with base table row  Page / row counts 19 September 2009 SQL Server Saturday – Zürich, Switzerland 19 September 2009 SQL Server Saturday – Zürich, Switzerland DBCC CHECKDB  Databases to Check        CHECKALLOC CHECKTABLE CHECKCATALOG  allows you to specify a database ID Master  SQL Server 2005+ also checks the resource database No need to run CHECKALLOC, CHECKCATALOG or CHECKTABLE if running DBCC CHECKDB Model MSDB Tempdb?  SQL Server 2005+ does not run CheckAlloc or CheckCatalog and uses Shared table locks, since it cannot use snapshots  User databases 19 September 2009 SQL Server Saturday – Zürich, Switzerland 19 September 2009 SQL Server Saturday – Zürich, Switzerland 4 When and Where to Run DBCC?  Run DBCC CHECKDB as often as needed for you to feel good about your data  If there is a problem you’d like to know as soon as possible  Checksums!     Use PHYSICAL_ONLY option for faster or intermediate checks Use TABLOCK option for faster results but may block In a large configure separate maintenance plans for a group of Databases Use NO_INFOMSGS option SQL Server Maintenance Best Practices 19 September 2009 SQL Server Saturday – Zürich, Switzerland File-Level Fragmentation Minimizing File-level Fragmentation  Create your DB files large enough to suit your needs in the future  No penalty having files too large, but big penalty if files are too .mdf VB File .ldf 1GB Word Doc .mdf  small Don’t let AutoGrow kick in. Be proactive instead.  Always grows at busiest time of the day   Avoid auto-growing during maintenance window  Do enable AutoGrow, but don’t rely on it  Traceflag 1117 for SQL Server 2008 to grow all files together .mdf 1GB .ldf Word Doc VB File  Keep other apps off your DB Server 19 September 2009 SQL Server Saturday – Zürich, Switzerland 19 September 2009 SQL Server Saturday – Zürich, Switzerland 5 Shrinking the Database    When should you shrink the Database? Almost never… Why not?   Very costly and will fragment your data & indexes Fully Logged Operation! 19 September 2009 SQL Server Saturday – Zürich, Switzerland 19 September 2009 SQL Server Saturday – Zürich, Switzerland Optimizations Fragmentation Overview  There are essentially two aspects of Optimization  Logical Fragmentation ○ When the Physical order does not match the Logical order  Rows or pages become non-contiguous ○ Caused mainly by Page-Splits SQL Server Maintenance Best Practices ○ Affects range scans, therefore more DW than OLTP  Page Fullness (Fill Factor) ○ When the amount of free space on a page is more or less than ideal ○ Caused by Inserts / Updates and Deletes ○ Causes more IOs than necessary to read given amount of data 19 September 2009 SQL Server Saturday – Zürich, Switzerland 6 How Logical Fragmentation Occurs How Logical Fragmentation Occurs (cont’d) Index leaf level of newly-built index Newly-built index leaf after a single page split red arrow is the physical order black arrows are following the logical order red arrow is the physical order black arrows are following the logical order 19 September 2009 SQL Server Saturday – Zürich, Switzerland 19 September 2009 SQL Server Saturday – Zürich, Switzerland How Logical Fragmentation Occurs (cont’d) Optimizations Re-indexing  Why rebuild indexes?  Primarily to remove fragmentation and improve performance  Not to recover from corruption as many may think Index leaf level after random inserts/deletes    ALTER INDEX REBUILD ALTER INDEX REORGANIZE sys.dm_db_index_physical_stats red arrow is the physical order black arrows are following the logical order 19 September 2009 SQL Server Saturday – Zürich, Switzerland 19 September 2009 SQL Server Saturday – Zürich, Switzerland 7 ALTER INDEX REBUILD  ALTER INDEX REBUILD  Pros  Rebuilds one or all indexes on a table  Deals internally with existing dependent indexes  Adheres to Fill Factors & Pad Indexes  Updates the statistics Cons  Locks the entire table and indexes for the duration if OffLine Option is used  One big transaction. Cannot clear the log until done.  Can take a while and can eat up a lot of resources  Best chance of contiguous indexes when done  Can use parallelism (Enterprise Edition only) ○ But be aware of impact on other work  Since SQL Server 2005 can be an online operation  Requires ~1.2x the size of the index in free space 19 September 2009 SQL Server Saturday – Zürich, Switzerland 19 September 2009 SQL Server Saturday – Zürich, Switzerland ALTER INDEX REORGANIZE  ALTER INDEX REORGANIZE  Pros  Takes minimal locks, essentially online operation  Will fill pages up to the fill factor during the Cons  Possible to log several times the size of the index  Does not update the statistics  Single threaded  Rebuilds only the leaf level  Works on a single file at a time  Does not remove interleaving problems compacting phase  Can be stopped and restarted  The log can be backed up during the operation  Does not require extra free space in the data files or tempdb 19 September 2009 SQL Server Saturday – Zürich, Switzerland 19 September 2009 SQL Server Saturday – Zürich, Switzerland 8 How and When to Run?  How and When to Run (cont’d.)  How and when you reindex depends on:  Your maintenance window  Size of tables  Number of tables and indexes  Hardware Which ones to reindex?  System Tables? ○ Not supported on System Tables  User Tables? ○ Absolutely  Online vs. Offline  Data usage (OLTP vs. DW)  How fast they get fragmented  Do not blindly reindex all indexes  Determine based on the fragmentation level ○ Books Online examples: SQL Server 2005/2008: sys.dm_db_index_physical_stats  Or some other predetermined schedule  REORGANIZE for less than 30% of fragmentation, REBUILD for higher 19 September 2009 SQL Server Saturday – Zürich, Switzerland 19 September 2009 SQL Server Saturday – Zürich, Switzerland Updating Statistics    Not usually needed, especially if Auto-Update statistics is turned on Depends on your data changes Choices are:  sp_updatestats ○ All tables and indexes  UPDATE STATISTICS ○ Table or index at a time  MS Whitepaper «Statistics used by the Query Optimizer»  http://msdn.microsoft.com/en-us/library/dd535534.aspx 19 September 2009 SQL Server Saturday – Zürich, Switzerland 19 September 2009 SQL Server Saturday – Zürich, Switzerland 9 Virtual Log Files (VLFs) Optimization Logfile Initial Size or Growth_increment < 64MB 64MB <> 1024MB > 1024MB VLF Count* 2-4 8 16     Use DBCC Loginfo to get the VLF count of your database In general, VLF counts <100 do not affect the performance Shrink the Logfile and grow it as large as you need it (only time shrink is not bad ;-)) VLF size should not be larger than 512MB  growth_increment should not be larger than 8GB * If database is mirrored and Autogrow enabled VLF count can be much higher! 19 September 2009 SQL Server Saturday – Zürich, Switzerland 19 September 2009 SQL Server Saturday – Zürich, Switzerland Backups     Full Differential Log File and Filegroup Which one to use?  Of course, that depends SQL Server Maintenance Best Practices   Keep recovery time in mind as well 19 September 2009 SQL Server Saturday – Zürich, Switzerland 10 Backup Guidelines – System DBs  Backup Guidelines – User DBs  Master / Model / MSDB  Backing up Master also backs up the Resource DB FULL backup once a night if you can  Larger ones may be less frequent  File or File Group in between  FULL backup at least once a night  DIFF backups can save recovery time LOG backups depending on how much data you can afford to lose  Usually SIMPLE mode, no LOG backups available  So can only recover to last Full or Differential   Master can not do log backups 19 September 2009 SQL Server Saturday – Zürich, Switzerland 19 September 2009 SQL Server Saturday – Zürich, Switzerland What About Verify?     Backup Location  RESTORE VERIFYONLY checks to see that backup set is complete and volumes are readable SQL Server verifies the structure of the data Backups are never any good unless you test it SQL Server helps with CHECKSUM option  Disk or tape? I prefer to disk first when ever possible  Usually faster and cleaner  Latest backups are always handy for restores   Backup to tape from disk for long-term and off-site storage Never backup to the same disk array that holds the data or log files  Many, many people do this !  If backing up to a remote share, use UNC pathing, not mapped drives  Due to permissions / security 19 September 2009 SQL Server Saturday – Zürich, Switzerland 19 September 2009 SQL Server Saturday – Zürich, Switzerland 11 Backup Device Cleanup  Backup History  Simple backups that reuse Devices with INIT require no cleanup Backups to individual Devices will need to be periodically deleted Maintenance Plan does this for you Custom jobs require user code Have you ever noticed that MSDB just keeps getting larger and larger? Every backup adds history records to MSDB SQL Server 2005 manages this for you as a Maintenance Plan step ... If you add it      19 September 2009 SQL Server Saturday – Zürich, Switzerland 19 September 2009 SQL Server Saturday – Zürich, Switzerland sp_delete_backuphistory    DECLARE @DT DATETIME SET @DT = DATEADD(dd, -7, GETDATE()) EXEC MSDB.dbo.sp_delete_backuphistory @DT SQL Server Maintenance Best Practices 19 September 2009 SQL Server Saturday – Zürich, Switzerland 12 Scheduling  Scheduling (cont’d.)  Always know your full schedule  Know what jobs may conflict Think about the order of execution  Full backup before or after reindexing?  What about archiving or purging?  What about weekly or monthly jobs?   Don’t forget about non-agent–related tasks  Tasks running against your server from other remote SQL Jobs that require similar resources can be a killer if scheduled at the same time  Reindexing & CheckDB  Backups & Reindexing  Backups & Backups  Archiving & Reindexing Servers  Applications submitting batches 19 September 2009 SQL Server Saturday – Zürich, Switzerland 19 September 2009 SQL Server Saturday – Zürich, Switzerland Miscellaneous Maintenance Tasks     Check for free disk space Check for free space in the files Delete old backup files Could you recover from scratch if needed?  Script Logins  Script Jobs  Script databases SQL Server Maintenance Best Practices   Check for errors in the logs Check for scheduled job failures 19 September 2009 SQL Server Saturday – Zürich, Switzerland 13 Resources   SQL Server Manageability Team Blog:  http://blogs.msdn.com/sqlrem/default.aspx Understanding SQL Server Backups  http://technet.microsoft.com/en- us/magazine/2009.07.sqlbackup.aspx   Transactionlog Management  http://technet.microsoft.com/en-us/library/ms345583.aspx SQL Server BOL «understanding databases», with informations to files/filegroups, maintenance, optimizing  http://msdn.microsoft.com/en-us/library/ms189317.aspx  Optimized Index Defrag  http://msdn.microsoft.com/en-us/library/ms188917.aspx 19 September 2009 SQL Server Saturday – Zürich, Switzerland 19 September 2009 SQL Server Saturday – Zürich, Switzerland Community  Swiss PASS Chapter  Monthly SQL-Related Sessions  www.sqlpass.ch  Swiss IT Pro user group  www.swissitpro.ch Thank you…  Swiss TechNet Blog  http://blogs.technet.com/chitpro-en  http://blogs.technet.com/chitpro-de 19 September 2009 SQL Server Saturday – Zürich, Switzerland 19 September 2009 SQL Server Saturday – Zürich, Switzerland 14