Jeremy Carter

SQL Server guru Paul Randal is hosting this month’s TSQL Tuesday and he has asked a great question that all good companies should be asking themselves. Why are DBA skills necessary?

I would answer that in today’s market being mediocre does not cut it. Companies need to stay ahead of the curve if they want to make it. They have a responsibility to themselves and to their customers. I think that it used to be a badge of honor when sites went down because of some new product or a great deal of attention was thrown their way. That is no longer the case. Web sites are expected to be up and running 24×7, no questions asked. And what is sitting behind most web sites? You can bet there is some sort of database server(s) setting behind it, feeding everything from content, to user preferences, to processing orders.

Yes it is true that you can install SQL Server, create a database and the thing will just run. The question though is how long and how well will it run? Is it all going to come tumbling down on your biggest sales day or during your latest promotion? In my experience if you do not have the dba skills and have been proactively taking care of your server, these are the very times you are going to experience failures.

So when should a dba start looking after the server? In my opinion there should be someone with some dba skills involved before SQL Server is even installed. Questions for server specs such as cpu, memory, disk, all need to be answered from the onset of the project. Now if you have a little 5 or 10GB db do you need to have a full time DBA? Most likely not, but in my experience it is usually somewhere around 10GB that you’ll start to see issues if no one is looking out for the server.

Someone with dba skills should be involved, not only insuring the hardware is up to snuff, but they should also be looking ahead to insure that best practices are being followed and all of the options are configured correctly. Basic things like recovery, max server memory, index maintenance plans, etc all need to be addressed before the lack of attention sneaks up and bites you.

The question of when do you need a full time dba is a bit tougher question to answer. In a nod to Paul, I have to say “It depends.” Most people would probably say that it depends on the size of the database; the larger the database, the more time needs to be spent on it. While this is generally true, it certainly is not a hard and fast rule. I have seen some customers with very small 10GB databases that seem to need constant tuning while others with multi-terabyte databases get by with little tweaking.

How can that be so you ask? The difference is in the forethought put into the server’s physical hardware, the database schema, and the queries being ran against it. If you have good hardware, a good schema, and well written queries you’ll be able to get more bang for your buck.

As an example the other day I had a client that was asking for help because their db server was just getting hammered. He already knew that corners had been cut in the past and the code was bad and that he needed more hardware to compensate. I was able to do some tuning to get them by but it would not be enough for the upcoming holiday season. Now because of negligence in the past, he was having to double the size of the hardware required. And not only was he going to be out money for the hardware but he still has to do something about the code or the site will just never scale for their planned future growth.

I can’t help but think that had a good dba been involved sooner that much of his pain could have been prevented and more money kept in their pocket. Poor planning costs money to redo code, purchase additional hardware, lost sales, and lost customer satisfaction. All too often people get caught up in what is good for the moment and forget to look ahead to the future.

A good dba with good skills can help anticipate and prevent those fires from ever starting, let alone growing out of control. A little TLC can go along way to ensure happy customers, coworkers, bosses, and spouses.

It’s time once again for that monthly dose of TSQL Tuesday. This month we have database whisperer Michael Swart hosting and he has asked everyone to blog about indexes. As Michael so keenly pointed out indexing covers a huge swath of possibilities. For my part I want to speak about getting rid of those pesky, no good indexes. OK, well really I want to speak about first considering the existing indexes before adding new ones. All too often I find people who have just gone crazy adding an index for every column or have just listed every possible combination of columns they could think of.

So how do we do this? The first thing I like to do is look at the overall number of heaps vs clustered vs non-clustered indexes. This gives me some feel for how well the database may or may not be indexed. Have they taken the time to create clustered indexes on most of their tables? Is their a disproportionately large number of non-clustered vs clustered indexes? The following script will show you the count for each of the heaps, clustered, and non-clustered indexes.

--show a count of indexes by type
select
i.type_desc,
count(*) as cnt
from sys.indexes i
inner join sys.objects o
	on i.object_id = o.object_id
WHERE o.type<>'S'
GROUP BY i.type_desc

Once I get a feel for the overall numbers and types of indexes I like to dive a little deeper and look at the individual tables. I pay particular attention to the tables with the most indexes, especially if they have gone past the single digits into 15, 20, 30, or more indexes on a table for an OLTP workload.

--show a count of indexes per table
--does not count the clustered or heap
select
	o.name as [Table Name],
	(select count(*)
from sys.indexes i
	 WHERE i.object_id = o.object_id
AND i.index_id>1)as cnt
from sys.objects o
WHERE o.type='U'
order by cnt desc

Once I am armed with a list of the “over” indexed tables I compare that to the actual index usage. Indexes come at a cost and they are not cheap. Every Time you insert, update, or delete data the indexes also have to be kept up. Then of course you have to perform regular index maintenance such as rebuilds and reorgs, not to mention keeping the statistics updated. So it is important to insure that your indexes are actually of use and not just costing you expensive cpu and disk io to maintain.

The following script is one I picked up from Glenn Berry’s excellent blog. It will show those indexes which have been written to more than they have been read. These are good candidates to consider dropping when you need to cut your index bloat. I wanted to note that I have modified Glenn’s script in a couple of ways. First I have added a column which contains the DROP INDEX statement which makes it easy to drop the index once I have VERIFIED it is actually not needed. Secondly you have to keep in mind that sys.dm_db_index_usage_stats only has statistics for indexes which are in cache. This is important for two reasons. First you may have indexes which are only used once in a while say for an end of month report. You definitely don’t want to drop indexes which prolong the CEO’s favorite report. Secondly you may have indexes that just aren’t even being used at all. Why waste disk space and time maintaining indexes that are not even being used?

-- Possible Bad Indexes (writes > reads)
SELECT
sch.name + '.' + t.name AS [Table Name], i.name AS [Index Name], i.type_desc,
ISNULL(user_updates,0) AS [Total Writes], ISNULL(user_seeks + user_scans + user_lookups,0) AS [Total Reads],
        ISNULL(user_updates,0) - ISNULL((user_seeks + user_scans + user_lookups),0) AS [Difference],
        'DROP INDEX [' + i.name + '] ON [' + sch.name + '].[' + t.name + ']' AS [Drop_CMD]
FROM sys.indexes AS i WITH (NOLOCK)
LEFT OUTER JOIN sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
	ON s.object_id = i.object_id
	AND i.index_id = s.index_id
	AND s.database_id=db_id()
AND objectproperty(s.object_id,'IsUserTable') = 1
INNER JOIN sys.tables AS t WITH (NOLOCK)
	on i.object_id=t.object_id
INNER JOIN sys.schemas AS sch WITH (NOLOCK)
	on t.schema_id=sch.schema_id
WHERE
--ISNULL(user_updates,0) >= ISNULL((user_seeks + user_scans + user_lookups),0) --shows all indexes including those that have not been used
ISNULL(user_updates,0) - ISNULL((user_seeks + user_scans + user_lookups),0)>0 --only shows those indexes which have been used
AND i.index_id > 1
AND i.is_primary_key<>1
AND i.is_unique_constraint<>1
ORDER BY [Table Name], [index name]

So there you have it. Before I ever even consider adding new indexes I take a dive into the existing indexes. I need to insure that what already exists is running optimally and that I am not going to bring things to a grinding halt by piling on new indexes. As always be incredibly careful before you make any changes to production. Adding and removing indexes can both have huge effects on performance so be doubly sure before you execute those CREATE and DROP statements.

***Bonus*** Hypothetical Indexes
Occasionally I still come across databases that have hypothetical indexes. What are hypothetical indexes? These are created by the Database Engine Tuning Advisor while it makes its recommendations. While normally DTA will remove these when it is done, occasionally it will leave them in place. You can read a little more (but not much) about them over at MSDN. The following query will show if the database  has hypothetical indexes. Once the hypothetical indexes have been identified, they can be safely removed.

--shows hypothetical indexes
SELECT object_name(object_id), name AS Hypothetical FROM sys.indexes
WHERE Is_Hypothetical = 1

The last script I have for you will generate all of the drop commands for the hypothetical indexes.

SELECT 'DROP INDEX ['+i.name+'] ON ['+s.name+'].['+t.name+']' as drop_cmd from sys.indexes i
inner join sys.tables t
	ON i.object_id=t.object_id
inner join sys.schemas s
	on t.schema_id=s.schema_id
where i.Is_Hypothetical=1

Well here we are, it is already time for another TSQL Tuesday. This month Jason Brimhall has asked what a dba should do before they head out on vacation. This is certainly a topic near and dear to my heart. While I certainly work my tail off and I am more than happy to be the first in and last to leave, I also like to play hard to.

In my current position I am pretty lucky all the way around but particularly when it comes to vacation time. I get a good amount of time each year and for the most part my team is very flexible when and how I spend that time. I’m not a production dba so there are not any systems which I am directly responsible. Instead I work as part of a small team providing consulting services for more than 9,000 servers running SQL Server.

So while I cannot make specific recommendations on what I do, I will try to make some recommendations from the standpoint of anyone walking into the unknown. There are occassions where I get called in because the companies dba is out on vacation. Whether it is a consultant or coworker filling in there are several things that can be done to help things run smoothly in your absence.

#1 Documentation- Document the heck out of everything. What is the application, who is it serving, what are the availability requirements, and how do we connect are great places to start. Take a few minutes and think about the db, the instance, and the server. Are there any quirks, any special jobs (particularly if they are scheduled outside of sql), or anything else that may not be inherently self documenting. The more information you provide the better, and the more likely your much needed days of R&R will go uninterrupted.

#2 Change Management - While this is a variation of #1 I think it is important enough to warrant bringing up seperately. I come from a law enforcement background and we were constantly told, “If it is not written down, it never happened.” I still firmly believe in this. Whether it is 3 days or 3 years later it is important to know what has been changed, when it was changed, and why it was changed. I can remember one time spending two days chasing down an issue only to find out someone had made a very simple and seemingly innocuous change. Had we known about the change and the time frame, we could have put two and two together and saved a whole heck of a lot of hassle.

#3 Monitoring – You are monitoring your servers aren’t you? It is great to see what is currently happening but it is even better to have a history of it. A good baseline of what is “normal” can go a long way. I have seen some servers that can easily handle and regularly do handle 1200 connections. Other times I may be on a server that regularly has 200 connections, but at 250 it falls over. Again this is about saving time and hopefully keeping the person filling in for you from running down unnecessary paths.

Looking over the post, I noticed a definite theme. In reality everything is a matter of good documentation and having good polices and procedures in place AHEAD of time. In the end it should not matter if you are skipping town for a week on the trip of a lifetime or if you need to take a last minute day off because of xyz… It is just a good practice to be prepared and make sure your absence goes smoothly. There is something wrong if everything will fall apart while you are gone and it is not a good situation for you or your employer. The real hero of the day will be missed because they are a true asset, not because the world stopped in their absence.