Analyser index recommendations
-
Hello all, I ran the trace file and then analysed this data in the Anaylser in SQL Server 2008. I have a table consisting of 2 million rows, and the analyser recommended 15 indexes. I am fairly new to this company and was not sure how to go about this. Is there some other way to figure which index would be really required? I have a field which is the Primary Key, a datetime field, a unique identified field. Thank you!
-
Hello all, I ran the trace file and then analysed this data in the Anaylser in SQL Server 2008. I have a table consisting of 2 million rows, and the analyser recommended 15 indexes. I am fairly new to this company and was not sure how to go about this. Is there some other way to figure which index would be really required? I have a field which is the Primary Key, a datetime field, a unique identified field. Thank you!
I usually try and pick the index that is going to hit the most granular field and create that first, rerun the analysis and repeat until satisfied with the performance. This often reduces the number of indexes actually required to gain significance performance gains.
Never underestimate the power of human stupidity RAH
-
Hello all, I ran the trace file and then analysed this data in the Anaylser in SQL Server 2008. I have a table consisting of 2 million rows, and the analyser recommended 15 indexes. I am fairly new to this company and was not sure how to go about this. Is there some other way to figure which index would be really required? I have a field which is the Primary Key, a datetime field, a unique identified field. Thank you!
As Mycroft suggests, create the most granular index first. Indexes tend to be more useful when the distribution of data in the indexed column(s) is high. And create one index at a time and rerun the query to see how much performance improvement you've gained, if any. This way, if you find that the query has actually slowed down (which may happen sometimes), you can always disable or drop the index and proceed with the next one.