Suggested Indexes on RG_AllObjects_V4
Lxocram
Posts: 25 Bronze 3
Database Engine Tuning Advisor ( :twisted: ) keeps advising me to add indices on some tables in tempdb related to SQL Source Control.
Why is tempdb used for this table and not a dedicated redgate database? (presumably tempdb is on the fastest disk/cleanup on server restart...)
Would SQL Source Control(comparison/analytical phase) indeed be faster if these indices were to be applied? Or would it rather be slower (fetching the objects)?
As always this also contains some reduntant indices(same key more or less same included columns)
What's the general stance on that?
I've renamed the indexes from their default "missing_index_random" to the more informative table_keys_columns notation:
USE tempdb
go
CREATE NONCLUSTERED INDEX [IX_RG_AllObjects_v4_Matched_K3_4_5_9] ON [dbo].[RG_AllObjects_v4] ([Matched]) INCLUDE ([DatabaseID], [ObjectID], [ObjectType], [ParentObjectID])
GO
CREATE NONCLUSTERED INDEX [IX_RG_AllObjects_v4_Matched_TypeOfAction_K3_4_5] ON [dbo].[RG_AllObjects_v4] ([Matched], [TypeOfAction]) INCLUDE ([DatabaseID], [ObjectID], [ObjectType])
GO
CREATE NONCLUSTERED INDEX [IX_RG_AllObjects_v4_ObjectType_K3_5] ON [dbo].[RG_AllObjects_v4] ([ObjectType]) INCLUDE ([DatabaseID], [ObjectID])
GO
CREATE NONCLUSTERED INDEX [IX_RG_AllObjects_v4_ObjectType_TypeOfAction_K3_5_9_11] ON [dbo].[RG_AllObjects_v4] ([ObjectType], [TypeOfAction]) INCLUDE ([DatabaseID], [ObjectID], [ParentObjectID], [ParentObjectType])
GO
CREATE NONCLUSTERED INDEX [IX_RG_AllObjects_v4_ParentObjectID_Matched_K3_4_5] ON [dbo].[RG_AllObjects_v4] ([ParentObjectID], [Matched]) INCLUDE ([DatabaseID], [ObjectID], [ObjectType])
GO
CREATE NONCLUSTERED INDEX [IX_RG_AllObjects_v4_TypeOfAction_K1_3_4_5_8] ON [dbo].[RG_AllObjects_v4] ([TypeOfAction]) INCLUDE ([AllObjectsID], [DatabaseID], [ModifyDate], [ObjectID], [ObjectType])
GO
CREATE NONCLUSTERED INDEX [IX_RG_AllObjects_v4_TypeOfAction_K3_4_5] ON [dbo].[RG_AllObjects_v4] ([TypeOfAction]) INCLUDE ([DatabaseID], [ObjectID], [ObjectType])
GO
CREATE NONCLUSTERED INDEX [IX_RG_AllObjects_v4_TypeOfAction_K3_4_5_13] ON [dbo].[RG_AllObjects_v4] ([TypeOfAction]) INCLUDE ([DatabaseID], [Matched], [ObjectID], [ObjectType])
GO
CREATE NONCLUSTERED INDEX [IX_RG_AllObjects_v4_TypeOfAction_ParentObjectID_K3_4_8] ON [dbo].[RG_AllObjects_v4] ([TypeOfAction], [ParentObjectID]) INCLUDE ([DatabaseID], [ModifyDate], [ObjectType])
GO
Why is tempdb used for this table and not a dedicated redgate database? (presumably tempdb is on the fastest disk/cleanup on server restart...)
Would SQL Source Control(comparison/analytical phase) indeed be faster if these indices were to be applied? Or would it rather be slower (fetching the objects)?
As always this also contains some reduntant indices(same key more or less same included columns)
What's the general stance on that?
I've renamed the indexes from their default "missing_index_random" to the more informative table_keys_columns notation:
USE tempdb
go
CREATE NONCLUSTERED INDEX [IX_RG_AllObjects_v4_Matched_K3_4_5_9] ON [dbo].[RG_AllObjects_v4] ([Matched]) INCLUDE ([DatabaseID], [ObjectID], [ObjectType], [ParentObjectID])
GO
CREATE NONCLUSTERED INDEX [IX_RG_AllObjects_v4_Matched_TypeOfAction_K3_4_5] ON [dbo].[RG_AllObjects_v4] ([Matched], [TypeOfAction]) INCLUDE ([DatabaseID], [ObjectID], [ObjectType])
GO
CREATE NONCLUSTERED INDEX [IX_RG_AllObjects_v4_ObjectType_K3_5] ON [dbo].[RG_AllObjects_v4] ([ObjectType]) INCLUDE ([DatabaseID], [ObjectID])
GO
CREATE NONCLUSTERED INDEX [IX_RG_AllObjects_v4_ObjectType_TypeOfAction_K3_5_9_11] ON [dbo].[RG_AllObjects_v4] ([ObjectType], [TypeOfAction]) INCLUDE ([DatabaseID], [ObjectID], [ParentObjectID], [ParentObjectType])
GO
CREATE NONCLUSTERED INDEX [IX_RG_AllObjects_v4_ParentObjectID_Matched_K3_4_5] ON [dbo].[RG_AllObjects_v4] ([ParentObjectID], [Matched]) INCLUDE ([DatabaseID], [ObjectID], [ObjectType])
GO
CREATE NONCLUSTERED INDEX [IX_RG_AllObjects_v4_TypeOfAction_K1_3_4_5_8] ON [dbo].[RG_AllObjects_v4] ([TypeOfAction]) INCLUDE ([AllObjectsID], [DatabaseID], [ModifyDate], [ObjectID], [ObjectType])
GO
CREATE NONCLUSTERED INDEX [IX_RG_AllObjects_v4_TypeOfAction_K3_4_5] ON [dbo].[RG_AllObjects_v4] ([TypeOfAction]) INCLUDE ([DatabaseID], [ObjectID], [ObjectType])
GO
CREATE NONCLUSTERED INDEX [IX_RG_AllObjects_v4_TypeOfAction_K3_4_5_13] ON [dbo].[RG_AllObjects_v4] ([TypeOfAction]) INCLUDE ([DatabaseID], [Matched], [ObjectID], [ObjectType])
GO
CREATE NONCLUSTERED INDEX [IX_RG_AllObjects_v4_TypeOfAction_ParentObjectID_K3_4_8] ON [dbo].[RG_AllObjects_v4] ([TypeOfAction], [ParentObjectID]) INCLUDE ([DatabaseID], [ModifyDate], [ObjectType])
GO
Comments
thank you for your post into the forum.
Are you using the Shared Development Model, where developers share a single copy of the database linked to source control?
I ask the above question, as SQL Source Control reads information about who made changes from the default trace and saves it in tempdb. If you are using the Shared Development Model, you may wish to create the change log database as per this Help Article.
Many Thanks
Eddie
Senior Product Support Engineer
Redgate Software Ltd
Email: support@red-gate.com
Thanks for the feedback.
We use the dedicated model. I was mostly just wondering why tempdb is used and why these suggested indices would not be pre-included.
I often use the missing_indices DMV (performance dashboard) to spot performance problems and I stumbled upon these suggested indices many of times so I thought I'd ask around on the forum to satisfy my curiosity
Btw I was wrong to blame the DTA (in this instance) , it was the missing indices dmv/report
Lx
Thank you for your reply.
The RG_AllObjects_V4 table in the tempdb is still used in the dedicated model to record who made the change. Also it is used within the Polling mechanism, to figure which objects are changed to place the blue blobs in the object explorer which is used regardless of the development model used.
I hope this answers your question.
Many Thanks
Eddie
Senior Product Support Engineer
Redgate Software Ltd
Email: support@red-gate.com