Maintaining high-performing SQL Server instances is a huge portion of a DBA’s job responsibilities. Failure to detect and correct unusual activity can affect internal operations as well as hurt the business’s bottom line.
If you notice peak activity changes or anomalies in a SQL Server instance, here are three places to start your search for answers.
Page Life Expectancy
An instance’s page life expectancy (PLE) should maintain a fairly consistent value range. If that value drops and remains low, that’s a sign that the buffer pool is experiencing increased demand.
Before you run out and up the memory, take a look at the workload activity. If the workload has increased, that would account for the additional pressure on the buffer pool. But if the workload hasn’t changed, you will need to look more closely to identify what is using the extra memory.
Possible reasons for a drop in PLE include actively running maintenance jobs, index rebuilds or statistic updates, DBCC operations, and changes to query plan.
If you notice a drop in PLE that isn’t associated with an increase in workload, there are a few things you can try to improve an instance’s PLE:
- Drop unused indexes
- Merge duplicate indexes
- Watch for big queries
- Purge data
WRITELOG Wait Time
When WRITELOG wait time is too big a proportion of total wait time, you probably have a bottleneck on your SQL Server instance. The bottleneck is likely caused either by a problem on the disk where the transaction log is stored or by data being committed inefficiently.
To determine what kind of bottleneck you’re dealing with, start by analyzing the number of SQL statements waiting for the WRITELOG event. If there are a lot of statements waiting, you have a disk bottleneck. If there are only a few statements waiting, data is probably being committed too often.
There are several ways to resolve high WRITELOG wait time once you have figured out whether your bottleneck is disk-related or commit-related:
- Add I/O bandwidth to the disk where the transaction log is stored
- Move non-transaction log I/O from the disk
- Move the transaction log to a less busy disk
- Reduce the size of the transaction log
- Be sure the COMMIT statement is placed in the code so the data isn’t committed too frequently
TempDB is a temporary workspace in SQL Server that holds temporary objects. Because the objects held in TempDB are transient, a SQL Server instance recreates TempDB every time it restarts. This makes optimizing TempDB critical to maintaining performance and avoiding operational bottlenecks.
TempDB contention is one of the main culprits of performance degradation. Contention occurs when multiple resources need access to TempDB but there is only one TempDB data file. This causes a bottleneck because processes can’t access TempDB fast enough, leading to connections timing out and the processes being deallocated.
Fortunately, TempDB bottlenecks can be resolved fairly easily by adjusting the number and size of TempDB files. Upon installation, the SQL Server default is one TempDB data file. If you notice contention occurring, it is recommended that you add eight new data files and determine whether that fixes the issue. If the problem isn’t resolved, try adding additional data files in multiples of four until performance is restored.
Though it’s great to know where to start looking when you experience performance problems, each of the issues above and the resulting bottlenecks could be mitigated or avoided altogether by implementing one hard and fast rule: Monitoring performance metrics is not optional. Here are some examples of key metrics to track:
Page life expectancy: Track PLE with continuous monitoring and be proactive when it drops and stays below the typical value for a particular SQL Server instance.
WRITELOG wait times: Monitor metrics such as log growth, log shrinkage, percent log used, and log flush waits/sec.
TempDB inefficiency: Monitor what is being allocated to user objects, version store, or internal objects. Track how they are trending over time, then determine what sessions are consuming TempDB and how much.
There are some excellent feature-rich, affordable SQL Server performance monitoring tools on the market that can help you stay in front of performance-degrading issues. Make yourself the company’s DBA MVP by proactively researching solutions that keep both inward-facing operations and outward-facing business services running at peak performance.