SQL is spiking!!!! What do I do?
In my 5 years as a DBA I often encounter this very standard set of issues
SQL is spiking at 100%, my queries are slow, and their server is not responsive. My queries time out and the list goes on. The truth is there is no easy answer to this problem. There is no magic option to be set or no magic command that will solve this. The best thing to do is run a trace on the database and see what is going on. Most likely you will come across queries that are causing unnecessary disk io operations or page splits, you might find some that are using table scanning all the time (this means you do not have indexes set up correctly).
Here are a few possible reasons for processing spiking
• Poorly designed queries
• Bad table design
• Database files not set up for optimal performance
• No or incorrect indexes on tables
• Indexes are on tables but they are fragmented and need to be rebuilt
• Server does not have enough processing power or ram to handle the tasks required of it
By running a trace you can identify all of these situations and even inexperienced users should be able to determine most issues. Its fixing them that is the hard thing.
So let us go over these issues one by one and give some advice on each one.
• Poorly designed queries
This is the first thing you should check for because it does not require any hardware upgrades and also is the most common issue. Remember that just because queries return the correct result does not make them right. Query tuning is one of the hardest things to master. Thank god we have some great tools to aid us like profile and DTA (database engine tuning advisor).
In my carrier I have seen some really bad queries most of the time due to the developer not understanding how the storage engine works and how to take advantage of the query plan. I would strongly suggest reading “inside sql server 2005 the storage engine” By Microsoft press. This will empower you with the knowledge you need to write high performance queries. In later blogs I will dive in to this topic further and try and explain the best practices of writing queries. I am also always willing to help anyone out (If I can ) on this topic so just drop me a line (see my email at the bottom) .
• Bad table design
This is by far the hardest to fix and often imposable, no matter how good your queries are if the table (s) you are hitting are not set up properly you will have performance issues. You can think of it like this. If SQL has to work harder to get the information it needs then it’s going to take longer for it to return the dataset. Again in later blogs I will dive further in to this topic as it can be placed in a book itself.
• Database files not set up for optimal performance
This is a very common issue and is reacted to the actual storage media used to store the data files for the database. Database files should be stored on a raid configuration at the least. And most enterprise level solutions have multiple database files per database stored on different raid drives. This is the most expensive issue to fix and most of the tomes clients are unwilling to spend the money to resolve this issue.
Also people tend to throw new hardware at problems before determining or fixing other issues related to bottlenecks. In most cases even if you have the greatest hardware, if your tables, indexes, or queries are not doing their job it will not solve the problem.
• No or incorrect indexes on tables
SQL server implements a very powerful indexing system that can be the difference for a query running 2 minutes or 2 hours. Indexing a table gives huge read benefits to queries. They should however be used with caution. The more indexes you place on a table the longer it will take to update and insert records in to the table. I often hear arguments like “why can’t we index the whole table” . The reason is you would have a huge bottleneck on every update and insert statement you though at a table. So you need to think about the impact on every column you index. Things like “how often is this table going to be updated”. Again this is a huge topic that really needs a full understanding of when and what to index. The DTA is a good place to start and will usually pick the right indexes. Again feel free to drop me a line with any specific questions,
• Indexes are on tables but they are fragmented and need to be rebuilt
This is actually the easiest to fix. The problem is once an index is created it can become fragmented over time due to updates and inserts. I won’t go in to much detail about this issue right here. I am planning to do a whole series on indexes in the next couple of weeks and will dive in to the different types of fragmentation and how to rebuild the index (and when to rebuild and when to recreate) again if you need help just drop me a line.
• Server does not have enough processing power or ram to handle the tasks required of it
Sometimes it is simply a matter of asking your server to do too much. SQL Server Needs the hardware to be able to perform its tasks. That’s as simple as I can put it.
You could do everything right but if the server does not have the cycles to execute tasks in a high performing way then there’s really nothing that can be done other then upgrading the box . I have a client who has 2 servers both doing the exact same thing (one is production and one is history) . The production server has 4 CPUS 30 gigs of ram while the other server has one cpu and 8 gigs of ram.
If you ran the same query on both servers the production server would finish in let’s say 5 minutes while the history one would take over an hour.
I hope this at least can get you in the right mind set for tackling your bottlenecks
As always feel free to drop me a line for more advise or questions. I will answer all emails send at mccannblogs@gmail.com
McCann Out
ipoint.to 
Which Cable Reaches the Database? A Sunday Puzzle
20 hours ago
