First, you need to find out if SQL Server is really getting slower. The answer to this questions is not as easy as you might think. Performance is often a subjective measurement, and really needs to be quantified. The best way to do this is to have already performed benchmark tests on specific queries, or perhaps have historical Performance Monitor results you can use to compare against current performance. Only by doing this can you know for sure that performance is actually decreasing. Of course, if you don’t have any historical data, then you will have to use subjective measurements. This is why it is important to benchmark your SQL Server periodically.
Once you have determined there is a definite performance problem, the next step is to identify it. Some of the best tools to do this include Performance Monitor, Profiler, Query Analyzer, Index Wizard, the relevant SQL Server 2005 tool, or other third-party tools. Only by identifying the specific problem will you be able to effectively resolve the performance problem. How you remedy the problems you find will depend on the nature of the problem.
Besides using the tools described above, you will also want to investigate the changing workload on the server. Many performance problems that seem to “pop-up” all of a sudden are due to an increased workload. For example, has the number of users increased, has the number of transactions or reports increased, has the amount of data stored increased? If the SQL Server is not a dedicated server, has any of the other programs sharing the SQL Server increased its workload? All this information will help you to identify and resolve performance problems.
Another avenue to investigate is to see if the Index Statistics have been recently updated. Outdated index statistics can cause queries to be improperly optimized by the query analyzer, slowing performance.
Find out when was the last time that the indexes on your tables were last rebuilt. Over time, indexes become less efficient if they are not rebuilt. All indexes should be rebuilt periodically. We rebuild ours once a week. The more INSERT, UPDATE, and DELETE activity a database is subject to, the more often indexes need to be rebuilt.
Also, over time, how your data is used, or the content of the data, changes. This may mean that the indexes you set up originally are no longer the ideal set of indexes for your data. Use the Profiler and Index Wizard (or the appropriate SQL Server 2005 tools) to evaluate the current state of your database’s indexes, and make any necessary changes.
In most cases, after taking all of the steps recommended above, you can identify and correct most performance problems that just seem to “start up” on their own over time.
]]>