By: Ayman El-Ghazali (@TheSQLPro) - Guest Contributor
This article originally appeared on Ayman's blog, TheSQLPro.com.
About the Author: Ayman El-Ghazali is a SQL Server DBA/Developer and BI Developer. He hold a MSCE Data Platform, MSCA in SQL Server 2012, and a MCITP SQL 2008 DBA certification and I’m also a MCT (Microsoft Certified Trainer) and is passionate about spreading useful knowledge. See more of his work at thesqlpro.com.
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 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 you are a farmer, and every year you grow corn, harvest it five times a year and store in a warehouse that can hold 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 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 the first five crops are harvested and the rest thrown away. What a waste.
Additionally, this farmer does not understand the value of the investment. Crops go unfertilized and unchecked for pesticides and healthy soil. So even though the farmer is able to get five harvests that year, the quality is not marketable. The farmer loses everything he/she worked so hard to gain because he/she was not consistently monitoring the progress of the current crop.
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 end up losing data. Your backups will fail without you knowing it 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 should be proactive 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 lessens in quantity, 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 check 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.
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 and 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.
About Infusive Solutions:
Infusive Solutions is a technical staffing firm based in Manhattan facilitating software development, Windows engineering, technical support and SQL Server careers in New York City and the rest of the Tri-state area.