September 30, 2015 at 10:08 am
What is the threshold that warrants the need for creating an index on a table. I have a system with a TABLE that has only 500 rows, very static, but accessed many many times. The cardinality from the statistics of Actual vs. Estimate rows is off by only 5 rows. I have all clustered index scans. Just curious what the DB Gurus think and what I need to be looking for as I tune this DB. I am going through every stored procedure to finely tune the DB I am supporting.
Thanks.
September 30, 2015 at 10:15 am
>1.
The cardinality estimates don't imply whether or not you need an index. They just tell you about estimation errors. What tells you that you probably need an index here is that you're reading 500 rows every time you need one row. A clustered index scan is a full table scan.
If the table's static, then there's no cause for concern about the insert/update/delete overhead of an index, so why not create one to help those very frequent selects.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 30, 2015 at 10:23 am
That is the information I was looking for. I will begin adding indexes because these are multi "WHERE CLAUSES" column1 = @param1 AND column2 = @param2 AND @column3 = @param3 and an index on that combination will help performance.
September 30, 2015 at 10:52 am
http://sqlinthewild.co.za/index.php/2010/09/14/one-wide-index-or-multiple-narrow-indexes/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 30, 2015 at 11:39 am
🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply