Ever wanted to know the reason behind the Database color scheme in SQL Server Management Studio?
I have always wondered why all the Databases in management studio were Yellow (Online), Grey (Read-Only), Lighter Yellow with text next to it (something like Restoring), or Yellow with a Red Icon (Offline). Have you ever wondered why there aren’t any more useful colors like Red for when a Transaction Log is Full or Purple when your Statistics are Outdated. I have a theory on this that I have entitled “The Theory that you have to actually work to call yourself a DBA.”
Lots of people think that DBA work consists of the following (limited) tasks:
- Set up maintenance (Backups, Index Defrag, Stats Updates, etc) and let them run unmonitored
- Create a check-list of the worst Development practices and make sure that all development is code reviewed against it and there will never be any problems or slow queries
- Grant and Revoke access to individuals and move on
- Hit the so-called “Easy” button (known in some shops as adding NOLOCK to all queries) and make everything faster
- And my favorite, always be available for “DBA support” – which includes Development, giving people access 24/7 since everything is an emergency, and of course a DBA stands for “Default Blame Acceptor” so make sure that you realize part of your duties is to admit guilt on every issue
Let me use an example to demonstrate how short sighted that type of thinking is. Imagine that you are a farmer, and every year you grow corn and you harvest it five times a year with a warehouse that stores that exact capacity (five harvests worth of space) and you are only allowed to sell your crop at the end of the year. One year, you discover that the corn is growing faster than you anticipated and it looks like you may have 10 harvests that year! Amazing right? But you have a problem, your warehouse cannot store enough corn and you can only sell at the end of the year. Anyone would tell you build a new warehouse or expand the one you currently have. So he ends up harvesting the first five crops and throwing away the rest. What a waste right?
Additionally,, this farmer does not understand the value of his investment. He does not take care of it as he should. He does not fertilize it, check it for pesticides, or check to make sure the soil is healthy. So even though he is able to get five harvests that year, the quality is not marketable. So he ends up losing everything he worked so hard to gain because he was not consistently monitoring the progress of his current crop.
So what’s the moral of that long and boring story? Your data is a cash crop and your database is like the farm. You need a place to store your data safely and you need to maintain that place regularly. Your data won’t go bad like crops will, but your statistics will get stale and your performance will suffer which will cost you when your users are tired of waiting for queries to execute. You will run out of space if you don’t plan well and you will end up losing data. Your backups will fail without you knowing and then your transaction log will fail along with your database and then you lose all your data not just the new stuff.
So my theory is that Management Studio is not the DBA, you are! If Management Studio did everything for you, then you would take for granted simple things like checking query performance by regularly browsing relevant DMVs. You would stop monitoring disk usage. You would stop doing test restores on your backups because if there was anything wrong with the database then Management Studio would tell you or the backups would not work. The list will just keep piling up.
DBAs are proactive, or should be, and not just reactive. A DBA’s job is not to do development, although many do that, but they are to make sure that your data is safe and everything is running efficiently On top of that, they have to make sure that you are ready to grow and scale out. Data never gets less in qualtity, it always increases. Some data needs to be archived, and your DBA should be doing that. The DBA is like a mechanic, and if you don’t change your oil, check your tire press, and do regular maintenance your car will not perform optimally or you will ruin it. Software also needs regular “oil changes” and maintenance to work properly and thus it needs an intelligent DBA sitting with his/her tools and fixing problems before they occur.
Here are a few things that DBAs should be doing regularly in their environment because servers do not take care of themselves:
- Establishing baselines for performance metrics (CPU, Disk Usage/IO Speed, RAM Usage). All of these can be found using various Data Managment Views (DMVs) inside SQL Server (sys.dm_os_wait_stats for example) or using PerfMon in Windows Server.
- Regularly checking Index performance statistics. Again there are DMVs in SQL Server that will help you here (sys.dm_db_index_physical_stats, sys.dm_db_index_usage_stats, sys.dm_db_missing_index_details) to name a few.
- Check SQL Queries before putting them out in production or check currently slow queries in production. Here are three steps that can help with this:
- Find your worst offending queries using this DMV sys.dm_exec_query_stats. This books online link can help you out http://msdn.microsoft.com/en-us/library/ms189741.aspx
- Run those queries with the Estimated Execution Plan then re-run it with the Actual Execution Plan. Compare the results between the two and focus on row counts. If your Estimated Plan seems too low then you may have outdated statistics. Also, the Execution Plans will show you what is taking the most resources and you can tune that portion.
- Use these wonderful Query Hints with your queries SET STATISTICS IO ON and/or SET STATISTICS TIME ON. You should put this before your query and it shows great statistics. IO ON shows logical/physical reads and can help you understand how hard SQL Server is working to get the data for you using those metrics. TIME ON shows you the amount of time portions of multiple queries and stored procedures take, so you can narrow down which parts are taking longer and tune them. TIME ON is pretty pointless if you only have one SQL Statement.
- Test backups on different servers. Meaning, restore your backups but not on the same server they were done on, different ones. Also, test restoring different backup types together (FULL, DIFF, TLOGs). This helps you practice as well as makes sure that your backups are working as they should.
- Regularly check Database and Log File space usage and growth. With regards to space usage, you can set up Data Collector (previously known at SQL Server Performance Studio) to track database and log file sizes over time. Resize your transaction logs so that they don’t grow a lot otherwise you will have tons of Virtual Log Files which affects performance. You can use the following two commands to help you with the Transaction Log:
- DBCC LOGINFO – Returns one row for every Virtual Log File (VLF) that you have. Here is an excellent article by Kimberly Tripp about VLFs http://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/.
- DBCC SQLPERF(logspace) – Tells you the size of your log files and how much space is used.
- Most importantly, know that you have an important job. Don’t let this blog posting be your last stop in being a good DBA. I’ve only just scratched the surface there are tons more tasks that a DBA has to do to keep the system running well. You should be a detective that looks for problems and fixes them before things get worse. You should be a visionary that plans for future growth and scale out. You should be diligent in making sure you test Backups, Code, and anything new that you are going to do to your system (patching, encryption, replication, etc) to make sure it is ready for production.