October 14, 2024 at 11:07 pm
hi everyone
I am planning on adding a composite key for my tables. I have SPs that are writing to the tables so having the least impact on run time is ideal. What type of index should I be using?
Thank you
October 15, 2024 at 9:12 am
Depends on the access pattern. Usually one clustered index per table is preferred to prevent ghost-records.
I suggest watching https://www.brentozar.com/training/think-like-sql-server-engine/ for an introduction how sql server works and when sql may use your index or not use your index
October 15, 2024 at 1:57 pm
Definitely focus on the clustered index, first and foremost!
The best way to choose the keys is to look at the missing index stats and current index usage stats and decide which keys would be most beneficial with the current query patterns you have against that table. (Don't believe any myths like "always use an identity as the first (and only) clustering key".)
In general, if possible, you want a key that's naturally ascending if possible. For example, an add datetime, but if and only if that matches your query patterns.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 15, 2024 at 3:44 pm
There are lists of exceptions to this as long as... well, very long. However, as a general rule, pick the clustered index based on the most frequently used path to the data. By that I mean, for a given table, how is the data most frequently retrieved. For many tables, that's the primary key to the table. For some, it's other column (or columns) that are used most frequently to retreive the data. Since the clustered index defines data storage, make the clustered key the most frequent path to the storage.
Again, just a rule of thumb. Tons of reasons why, in a given situation, that's a bad choice. However, as a fast way to determine the best choice for a clustered index, this one works well.
After that, testing is your buddy. Too many variables to make much in the way of suggestions.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 17, 2024 at 8:07 am
This was removed by the editor as SPAM
October 26, 2024 at 3:03 am
thank you everyone for your feedback. i will implement as noted.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply