This post is part of the SQL Community Project #DBAJumpStart by John Sansom. As I mentioned yesterday, this post has been split into 3 more manageable chunks as I know you’re all busy people. You can find yesterdays post here - Remaining Vigilant, my contribution to #DBAJumpStart – Part 1
“If you could give a DBA just one piece of advice, what would it be?”
Last year John asked 19 successful and experienced SQL Server professionals and me (for some baffling reason) this exact question. Due to some technical problems and a number of time constraints this post is a smidgen later than some of the other collaborators, but better late than never. In the post below I share my own thoughts with you don’t forget that you can find all our collective wisdom inside DBA JumpStart, a unique collection of inspiring content just for SQL Server DBAs. Be sure to get your free copy of DBA JumpStart today!
Baselining
Baselining needs to be on every DBA’s to do list if it’s not being done already. A lot of people don’t really understand why though. To answer this let me first ask you a question: How many calls a day do you receive from users saying their application is slow and how do you measure that? Perceptions of time become very distorted when people are stressed, you need a way to categorically say what the difference between now and a “good level of performance” is to know if there truly is a problem. How do we do this? You nailed it – baselines.
Simply put baselines are the average values over a period of time that allow you to compare performance levels to another point. I’m not going to tell you everything that you need to cover here as baselining is a very involved topic. You can check out a white paper that I co-authored here that includes a portion on baselining as well as a webcast that I recorded for Dell Software called The Day After Tomorrow;Why you need to baseline.
Once you have created your baseline to create your benchmark or comparison point you simply take another and take a look at the differences. This way you can quantify to the user if there are performance problems or not based upon the workloads you are seeing. Once again, this all comes down to being proactive and vigilant. Really you need to be taking your baselines regularly and for different types of workloads, all this is covered in the webcast.
Documenting
Yes documenting is boring and it is time consuming, but hey you now have a bunch more time from automating a load of tasks that we covered in a previous section of this post! There’s lots of reasons why you should participate in both creating and reading documentation. Let me provide a few examples:
Creating documentation.
I’m going to be covering DR (Disaster Recovery) plans in more detail later on. DR Plans however, are probably the most important document you are going to write, possibly only second to writing your CV/resume or your will. There’s also a good chance that if you don’t have good DR documentation then you are going to need up to date copies of your CV/resume and/or will sooner than you would have liked!
Create coding standards. You may think I am either mad or very anal for saying this, there is good reason though so stick with me on this. Ever heard of plan cache pollution? Plan cache pollution is where you have a number of very similar plans in the plan cache that really could have been consolidated into one plan. What does this mean to you? Well it means that this area in memory contains more information than it needs to, this is memory that could and should be used to store data in memory so you don’t have to make expensive trips to your IO subsystem.
Let me show you how this works. The examples below were created in SQL Server 2012 Developer edition, they really should work in anything older than 2008 though, as long as you choose an appropriate database and query.
The first thing I am going to do is to select the database I wish to use and clear the plan cache. PLEASE DO NOT DO THIS ON A PRODUCTION INSTANCE
USE AdventureWorks2012 ;
GO
DBCC FREEPROCCACHE ;
GO
We’re then going to run the following queries:
SELECT type FROM sys .objects WHERE name = ‘sysfiles1′
GO
SELECT type FROM sys .objects WHERE name = ‘sysprivs’
GO
We’re not really bothered about the results here.
Next we’re going to query the plan cache to look at the plans that were created. We’re going to take special note of the query_hash attribute. Microsoft has this to say about the query_hash attribute:
“When searching for resource-intensive queries, you should consider how to find and tune similar queries that collectively consume significant system resources. The sys.dm_exec_query_stats and sys.dm_exec_requests dynamic management views provide query hash and query plan hash values that you can use to help determine the aggregate resource usage for similar queries and similar query execution plans.”
This query will allow you to look for the above queries in the plan cache:
– Find the query execution statistics row(s) for the query
SELECT sql_handle , plan_handle , execution_count , query_hash
FROM sys .dm_exec_query_stats AS qs
CROSS APPLY sys. dm_exec_sql_text ( qs . plan_handle ) AS sql
WHERE sql . text LIKE ‘SELECT type FROM sys.objects WHERE name %’
GO
The results will look something like this:
As a vigilant DBA I would be thinking; Hang on, if these plans are similar what can I do to make them use the same plan and make better use of my resources. That’s going to benefit all of my users.
How do we do this? Prepare your queries!
The queries I performed before were “adhoc” queries. If I were to write them so that they were prepared then they would share the same plan and I can save space in my cache. This means more memory is free elsewhere, it will probably be used to store data in my buffer pool meaning less expensive trips to my IO subsystem and that has to be worth doing.
So now if you run the following code you will see that both queries will use the same plan:
USE AdventureWorks2012 ;
GO
DBCC FREEPROCCACHE ;
GO
/*
SELECT type FROM sys.objects WHERE name = ‘sysfiles1′
GO
SELECT type FROM sys.objects WHERE name = ‘sysprivs’
GO
*/
DECLARE @MyVarcharParm VARCHAR( 20 )
SET @MyVarcharParm = ‘sysfiles1′
EXEC sp_executesql
N’SELECT type FROM sys.objects WHERE name = @Parm’ ,
N’@Parm VARCHAR(20)’,
@MyVarcharParm;
GO
DECLARE @MyVarcharParm VARCHAR( 20 )
SET @MyVarcharParm = ‘sysprivs’
EXEC sp_executesql
N’SELECT type FROM sys.objects WHERE name = @Parm’ ,
N’@Parm VARCHAR(20)’,
@MyVarcharParm
– Find the query execution statistics row(s) for the query
SELECT sql_handle , plan_handle , execution_count , query_hash
FROM sys .dm_exec_query_stats AS qs
CROSS APPLY sys. dm_exec_sql_text ( qs . plan_handle ) AS sql
WHERE sql . text LIKE ‘%SELECT type FROM sys.objects WHERE name %’
GO
Obviously on my laptop this really won’t amount to much of a saving, but what if you had recently consolidated lots of environments in a physical to virtual project, some of these databases are likely to have chatty applications and could be hitting the same SAN. Any reduction you can provide here will make an improvement to the overall performance of not just than one database, but the guest, the host and every other instance that shares that SAN.
Hopefully you have found this post useful, I’d like to thank you for putting aside the time to read it and would love to hear your comments. In the last of the three part series I will be talking about DR plans.
