I recently downloaded Brent Ozar's sp_BlitzIndex stored proc. I've done lots of index management over the years but am uncertain about some of the recommendations this stored proc is making. Here is the finding...
Aggressive Indexes: Total lock wait time > 5 minutes (row + page) with long average waits
Here is the recommended course of action...
ALTER TABLE [dbo].[ActivityEnrollments] ADD CONSTRAINT [PK_dbo.ActivityEnrollments] PRIMARY KEY ( [Id] ) WITH (FILLFACTOR=100, ONLINE=?, SORT_IN_TEMPDB=?, DATA_COMPRESSION=?);
I've never messed with CONSTRAINTs before. What is the risk of making this change vs the potential benefit?
Thanks.
"Blitz" recommendations are generally very simplified, boilerplate things.
For specifically this one, I'd say it's a very bad idea. Yes, the table very probably needs a clustered index (seems like it doesn't already have one), but you need to determine the best clustered index for that table(*), not just slap a pk on an identity column by default.
(*) Determining the best clus index requires looking at least at: missing index stats and current index usage stats, and most often some index operational stats. You may also need to look at cardinality (degree of unique values) of existing columns, and, if a very critical query(ies) go against this table, you might need to look at those query(ies) as well.
1 Like
Btw, if you want some advice regarding indexes for that table, I'd be glad to assist.