Top 13 Mistakes And Missteps In SQL Server – #6 File Placement
What Brings a Good Looking DBA Like You to a Place Like This?
You’ve most likely found this post because of one of the following reasons:
- You’ve got this shiny new server and are thinking about installing SQL Server on it.
- You’ve been following this series from the beginning (thank you by the way) and are curious where it’s going.
- You installed and configured SQL Server on a server a while back and are having one of those Oh Shutthefrontdoor moments.
- You’re my mom and read all my stuff because you’re so very proud of how far you’ve gone what with all the emotional trauma of growing up in the circus.
- Oops! This isn’t the toaster. Where are my glasses?
No matter how you found yourself here I promise you’ll learn something even if it is that you need to do a better job of utilizing that search engine of yours.
But My C-Drive is Huge
If you walk away with one thing from this post please make it be this: Don’t place your SQL Server data files, log files, nor backup files anywhere on the C drive – the drive that should be dedicated to the operating system files, application files required to support a health server (anti-virus, backup tools, etc.), and the SQL Server executables themselves. Secondarily, don’t install applications that are consuming your SQL Server database resources on the same server as the SQL databases themselves. No matter what your Independent Software Vendor tells you, so long as their development staff has followed generally accepted practices for software development in place since the dawn of distributed computing THERE SHOULD BE ABSOLUTELY NO NEED TO HOST YOUR APPLICATIONS AND THEIR SQL SERVER DATABASES ON THE SAME PHYSICAL SERVER. Sure, some of you may not have a choice. You may only have servers with a single drive. You may be a small Mom ‘n Pop concern. In those cases please be sure you have more than enough space to host your files and keep a watchful eye on that drive. You may also have to expose yourself to loss off recovery options by running in Simple recovery mode (not recommended) to reduce the chance that transaction log backups don’t run as scheduled and your log file grows unexpectedly. You also need to ensure you have a good understanding of how your databases are going to be accessed for inserts in that a surprise load of a significant amount of data may result in a data file that undergoes a very large growth event. Both of these issues only come into play when you allow autogrowth on your database files but not allowing autogrowth opens you up to a possibility that you’ve undersized your data or log file to properly accommodate the required usage. Vigilance once again comes into play.
Stop Touching Your C-Drive
Going forward I’m talking specifically to those of you who have a server or servers with multiple drives. Rumor is you’re getting a lot of action on that C drive of yours. You really need to be more discriminating. Perhaps try something new. It might feel awkward at first, but work with me. This isn’t going to be easy. It’s going to feel like work, but in the end you’ll be happy with the results.
Stop playing with your C drive. Keep it dedicated to just the O/S, SQL Server installation and executable files, and those necessary applications (anti-virus, backup software and so-forth) used to keep the server viable and protected. Work with your Server and SAN Admins to provision separate disks for user database data files and log files; for tempdb; for system databases; and for backups.
Different Strokes for Different Folks
Data files and index files are accessed differently than transaction log files. Logs are (primarily) written to though they are read during processes such as restorations, and HA operations. These actions occur sequentially. Data and index files are read from randomly during the querying process and are written to in the fashion at times when SQL Server needs to harden the changes made through INSERT, UPDATE, and DELETE statements from the transaction logs to the data and index files. That process does not occur at the end of every query operation and should be noted as such because it does not get in the way of users and their interaction with the SQL environment under normal, healthy circumstances. What does matter is that under normal use and interaction with SQL Server, users will request data or a change to the data in the database. This will result in a random read from the data/index files (if the information needs to be pulled from disk.) If the operation modifies data in any fashion that needs to be persisted in the database a sequential write is issued to the log file and an eventual random write is made in the data/index file(s) where those changed pages reside. Ignoring solid state drives (SSD) this means we want to have disk structures optimized specifically for the type of operation that will be performed against the types of files that reside on the drive. That is why DBAs ask for drives dedicated to logs versus different data types. We want the write head moving consistently across the disk with no random thrashing as it competes with random reads and writes against non-log files. We accept that we can co-host the data and log files for master, model, and msdb because they’re small and not typically accessed during normal user operations. We want tempdb separated because it’s shared amongst the users of the SQL instance.
Grooming is Necessary
The issue with not using the O/S volume for SQL Server data, index, log files has already been addressed: there are going to be times where things don’t go according to plan. SQL Agent may encounter a problem and miss a cycle of transaction log backups. Perhaps your environment allowed for someone to create a database and do so in full recovery mode. If you’re not paying attention (you should pay attention) you may find yourself with a volume filling due to lack of properly maintained databases. No transaction log backup and over time you have a very large transaction log file that has consumed your O/S volume. Additionally, large operations constructed in an improper fashion may lead to a bloated tempdb (consult a doctor if this bloating last longer that 4ms) or a transaction log that rapidly expanded to record said operation. Even with frequent log backups you may run across this and if your O/S volume is where your log file resides or tempdb files reside you may find yourself in big trouble. Pay attention to your SQL Servers and keep things clean.
Enough Foreplay; Tell Me What to Do Big Boy
To summarize in descending order of importance:
- If possible separate your data, log, and index files on separate volumes from the O/S drive.
- Dedicate a drive specifically for user log files (RAID 10 if possible – most likely you’ll be settling for RAID 5.)
- Provision a dedicated log volume for tempdb and dedicated data volume for tempdb.
- Separate the system database files for master, model and msdb from your user database files and tempdb files.
- Be prepared to provision separate volumes for data and index files and file groups should performance tuning analysis warrant.
Following this process will help to ensure that the performance problems you do run across are going to be less-likely to be caused by physical disk provisioning. You will still be sure to run across performance issues. When this happens contact your doctor Database Administrator, or a reputable SQL Server Consultant. I know a few I can point you to.
Next Up… #5 SQL Server Management Studio is Not a Tool for the Masses