Welcome to the third and final in the series of SQL Server Performance articles concentrating on indexes. In previous articles, we have looked at how to identify duplicated indexes, and also how to identify potentially bad indexes. This one follows on from there and asks the question - ‘Do I have any Missing Indexes in my SQL Server Database’?
Trying to find a missing index (or indexes) is a bit of a two-edged sword if you do it in a piecemeal fashion, the management view (DMV) that identifies missing indexes works on individual SQL Statements rather than as a whole. This means that taking the advice offered and creating the missing index would almost definitely improve the individual query it was generated from, but may also have the unwanted side effect of reducing overall performance for some of the other TSQL queries at the same time.
The script below can be used to identify the top 50 missing indexes from your database:
Again, the results gained from the missing indexes script should be used as a guide rather than a full recommendation. If you do want to consider a larger range of SQL queries then you should definitely take the time to get to know how the Database Tuning Advisor works properly.
Celerity have a number of consultants who are experts with a wide range of Microsoft products including SQL Server. If you have any concerns about the performance of your system, or are looking for a health check, we would be happy to discuss your requirements in more detail and tailor a solution to fit. Please do not hesitate to contact us.
*** Disclaimer *** The scripts used in this article may have been created by other people and/or gathered from specialist sites on the internet. No implication of ownership should be inferred.
Mike Metcalf, Technical Consultant, Celerity Limited
To view this article on Celerity Limited website, please click here.
No comments:
Post a Comment