In this post I cover some ways you can maintain your larger databases within SQL Server better. In reality, it’s based on things that I have seen at various sites.
One reason I’ve decided to do this is because I have noticed that some teams try to use one solution. And that solution having the same settings for all databases.
Now this usually work for smaller databases. However, when it comes to larger databases you have more to take into consideration.
In fact, I’ve encountered various issues where people have attempted a one size fits all solution. With this in mind, I thought I would address how to get the most out of maintaining your larger databases here.
However, I will try to avoid the more obvious items like using multiple filegroups. I do advise testing any recommendations in a test environment first before using on Production.
In fact, this post is first in a series and covers general items. If you want to see the other posts in this series click on the links below.
One thing in particular I must stress is that maintenance windows are critical. Database users can get upset if they discover that you are doing maintenance work against the database during the working day.
Especially if they are doing something like a large import at the time and discover you are trying to do index maintenance as well.
My advice is to schedule the maintenance work to be done during the evenings and weekends. In addition, actively monitor it to avoid it running during the working hours of your business.
Also, avoid doing maintenance when large imports or other ETL jobs are being done against the databases.
Now, if you’re using older versions of SQL Server then you will probably want to look at an older post of mine. Which discusses using CheckDB with databases that have filtered indexes and indexes that use computed columns.
In fact, this can make a big difference to CheckDB running on larger databases. You can read that post in detail here.
Anti-Virus exclusions for CheckDB
Now hopefully you all know you should have anti-virus exclusions in place for database files. In fact, Microsoft have their own documentation on what should be excluded. Which you can read in detail here.
However, there is one in particular you may need for larger databases that is not that well known. Which is for the snapshot files that CheckDB creates for those databases.
These snapshot files are usually in the format of mdf_MSSQL_DBCCxx and ndf_MSSQL_DBCCxx. I thought where xx was version number of database, however the Microsoft documentation suggests differently.
For example, Symantec anti-virus uses a question mark for their wildcard character in their exclusions list. Therefore, you might have to put an filetype exclusion in such as ‘.mdf_MSSQL_DBCC??’.
Test your backups
Now, I did say I’d avoid the more obvious items in this post. However, because of issues in the past I’m still going to mention testing restoring your backups here.
Never take it for granted backups are being done just because it says so in the log. Otherwise, you might end up being caught out and having some explaining to do.
Of course, there is another good thing about restoring your backups frequently. And that is that everybody knows what to do to restore your larger databases in the event of an emergency.
If you are using a traditional backup method with your SQL databases, consider the timing of your backups.
For example, if you do your full backups before reindexing has completed those changes will be in each differential backup until you do your next full backup.
One way to improve performance of queries in larger tables is to implement partitioning. However, if you do look to use this make sure you get your partition scheme right though.
Otherwise you could end up with poor performance or having to scan your entire table every time you go to create a new partition.
I know sp_updatestats is still heavily used in places. However, the problem with this is that it only updates using the last used sample rate. Which is usually the default sample value for SQL Server.
If you’re encountering performance issues caused by this it’s worth thinking about writing your own job to update statistics based on the size of your tables.
Make sure at the very least your instances with your larger databases in which are critical to your business are being monitored. Troubleshoot performance issues retrospectively on larger databases takes longer.
There’s a whole load of other things to take into consideration as well when dealing with larger databases. Part two follows this post so look out for it. Of course, feel free to comment below.