How to Speed Up Your SQL Server Using Database Performance Monitoring

Maintaining a high-performing SQL Server is essential not only to your organization’s operations but also to your customers. When your servers are slow to respond—or don’t respond at all—you lose business. Here are three key areas to monitor closely to ensure your SQL Server performance is optimal and ways to improve performance if your metrics show you have a problem. 

Indexes

If your indexes are functioning well, they contribute to a fast, smoothly running system. However, if your indexes suffer from any of these common afflictions, you may find your SQL Server performance is negatively affected. 

Metric to monitor: fill factor

Fill factor is a setting in SQL Server that instructs indexes to fill each page X percent. It seems intuitive that you’d want to fill each page 100 percent, but in reality, overfilling pages can cause page splits. If an administrator adds an additional row to a full page, the new row pushes about half of the existing rows to a new page and the new row is added to the bottom of the first page. 

Page splits can cause performance problems because of the increased number of I/O operations and the potential for fragmentation. However, too much space on a page can also hurt performance because resources are being expended needlessly.

To get the best performance results from your fill factor settings, don’t set a system-wide value. Look at your indexes individually and set fill factor at an appropriate level for each. For example, indexes that are frequently fragmented need a different fill factor value than indexes that are hardly ever used.

Metric to monitor: fragmentation

Index fragmentation occurs when users add and delete records. Moving things around is a normal part of a database’s day-to-day operations, but over time, fragmentation affects performance. Fragmentation can cause a couple of problems: 

• Too much blank space on too many pages slows down scans and uses memory unnecessarily
• New pages are added out of order so it takes longer for the server to find data

When fragmentation starts to affect performance, you will need to run Rebuild or Reorganize to clean up the fragmented indexes. Running Rebuild will create a new index and remove the extra blank space. Reorganize puts the leaf pages in order so the server can find the data more easily.  

Buffer Cache

When SQL Server can read pages in buffer cache, your response time will be faster. Performance suffers when the server has to go to disk to find a page because it has to go through a series of steps before saving the page to buffer and then reading it. There are a couple of metrics you can track that will help keep buffer cache running at peak performance.

Metric to monitor: page life expectancy

Page life expectancy (PLE) is how long a page stays in the buffer cache without being called before it is sent back to disk. Increasing PLE will improve performance, but it also uses memory that may be needed for other functions. If you are going to adjust PLE, you need to figure out how much memory you can use without  affecting performance somewhere else.

Metric to monitor: buffer cache hit ratio

Buffer cache hit ratio is the percentage of pages located in the buffer pool versus all page requests. The buffer cache hit ratio should be above 90 for best performance (i.e., SQL Server doesn’t have to read from disk). Take care when analyzing your buffer cache hit ratio data. A small decrease in the ratio may not indicate a real problem. Analyze the data in conjunction with PLE to determine whether an actual performance issue exists.

Query Optimization

Slow and underperforming queries are one of the most common SQL Server performance problems. There are many possible reasons your queries aren’t running well, so it may be difficult to pinpoint an exact cause. But there are a few things you can try early in your investigation that may improve query performance with minimal effort. 

Metric to monitor: index scan 

Checking the health of your SQL Server should be a regular part of your DBA duties. When queries are running slowly, spend a little extra time with the query optimizer. One important thing to check is whether SQL Server index scan has any problems you need to address.

Another potential performance killer is index scan running more frequently than it needs to. Index scan takes far longer than index seek because the query has to scan every row looking for useful data. Set up your query optimizer so it seeks indexes more often than it scans them. 

Rewriting a problematic query is another way to boost performance. Sometimes simplifying queries will remove the portion that is causing the query to bog down. You can also try adding indexes to speed up queries, but this works better in some cases than others. 

Adding indexes is a good solution for fields involved in searching, grouping, or sorting; frequently accessed fields; and fields with unique values. It’s generally not effective to add indexes on fields with duplicate values such as yes/no or for small tables. 

SQL Server performance monitoring is critical to maintaining high-performing databases. Keeping your SQL Server running at top speed can only be achieved through diligent attention to performance metrics. Add the metrics discussed above to your maintenance checklist to identify and correct SQL Server performance issues early.