When supporting SQL Server databases, there are some very common performance problems, so these are the items that you might want to look at first when troubleshooting your performance issues.
There are lots of caches in SQL Server, but the most well-known is the data cache (aka buffer pool). The easiest way to describe the data cache is that it is the data stored in memory, rather than persisted to disk. Being able to store lots of data in memory long term is desirable because working with data in memory is generally much quicker than having to perform physical I/O.
Typically what you might see along with the disk waits and disk latency is a PLE (Page Lifetime Expectancy) that is fairly low for your server. The larger your buffer cache is, the higher your “critical” threshold will be for PLE. The more data there is to swap in and out of the buffer, the worse off you will be when the swap actually happens. Another consideration is NUMA.
All of these things together spell memory issues, and there are various ways to deal with them. The important thing to remember is that this is not a disk issue. When troubleshooting a performance issue, look at everything before you assume one solution will fix your server issue.
Outside of some very unusual behavior, there are two basic types of timeouts you might deal with for SQL Server. These are connection timeouts and operation (or query) timeouts. In both cases these are values set by the client connecting to the SQL Server.
The pst common, the operation timeout, will induce panic because the error can look a bit intimidating. Remember, It is not much different than hitting the stop button in SSMS because the query was taking too long. In fact, it will show up exactly the same in a profiler trace with Error = 2 (Aborted). It tells us that queries are taking longer than expected. We should go into “performance tuning” mode. The error information from the client is really just some good information on where you might start to focus your tuning efforts.
CXPACKET Wait Type
This is probably the most common type of wait on a server with performance issues. More often than not the problem can be handled by proper indexing or statistics maintenance. It could also be that the plan cached for this query is just not optimal, and you can mark it for recompile using sp_recompile, set recompile at the query level, or just evict the plan using DBCC FREEPROCCACHE with a plan handle. It is best to exhaust these options before deciding to change MAXDOP to 1 because you could be throwing away a lot of processing power without realizing it.
The problem here is that the thresholds for triggering auto statistics updates end up being the same in most cases, even for a very large table. The threshold is ~20% of the rows in the table. So on a really big table it takes a lot of data change to trigger an update. The lesson here is really to keep an eye on statistics, and make sure they’re updated regularly, especially on large tables, which are becoming more and more common. Another option here can be to use trace flag 2371 to actually change the formula used to trigger the update.
Tempdb PAGELATCH Contention
The symptoms of this can vary, but high PAGELATCH waits in tempdb and poor performance will be noticed when running processes using tempdb. If you get a list of the slowest queries, it will show queries that use temp tables. These queries usually run in milliseconds, and should never be counted among the slowest for the server. This can have people feeling like these queries are a large part of the problem, but that is not necessarily the case at all. Look at your tempdb configuration and see if you can solve that problem first.
Query to show slowest queries:
SELECT creation_time, last_execution_time, total_physical_reads, total_logical_reads, total_logical_writes, execution_count, total_worker_time, total_elapsed_time, total_elapsed_time / execution_count AS avg_elapsed_time, SUBSTRING(st.text,(qs.statement_start_offset/2)+1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(st. text) ELSE qs.statement_end_offset END - qs.statement_start_offset) /2)+1) AS statement_text FROM sys.dm_exec_query_stats AS qs CROSSAPPLY sys.dm_exec_sql_text(qs.sql_handle) st ORDER BY total_elapsed_time / execution_count DESC;