Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Index with Include


Index with Include

Author
Message
thava
thava
SSC Veteran
SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)

Group: General Forum Members
Points: 264 Visits: 556
Hi

hope this is the correct forum to this question if not please redirect to me the correct forum

recently my company has upgraded the database to sqlserver2008 R2

with help of this link
i have did some minor change in it to avoid the duplication of the index name

i can improve lot of query's performance, but the scenario is change now, in a single table i have more than 10 indexes, the table only has around 10 to 15 columns

the script produce the index like this


--Most Impacted time : 900.45
CREATE INDEX IX_AdUserPrivilegeTrial_UserID_RoleID_SiteID ON [VG-3.0].[dbo].[AdUserPrivilegeTrial] ([UserID], [RoleID], [SiteID]) INCLUDE ([Version])
Go
--Most Impacted time : 854.84
CREATE INDEX IX_AdUserPrivilegeTrial_UserID_RoleID_SiteID ON [VG-3.0].[dbo].[AdUserPrivilegeTrial] ([UserID], [RoleID], [SiteID]) INCLUDE ([UnitID], [NoAccess], [ViewAccess], [FullAccess], [userlevel])
Go



i am confused a lot please clarify me,
now my question is
if i need to keep the index as it is, explain me why i need to create a index on the same column
or shall i change it like this

CREATE INDEX IX_AdUserPrivilegeTrial_UserID_RoleID_SiteID ON [VG-3.0].[dbo].[AdUserPrivilegeTrial] ([UserID], [RoleID], [SiteID]) INCLUDE ([Version],[UnitID], [NoAccess], [ViewAccess], [FullAccess], [userlevel])



sorry for my poor english



Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script
GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14385 Visits: 9729
The index suggestions do that kind of thing a lot.

Go with the combined index, not two separate indexes that are so similar to each other.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
ScottPletcher
ScottPletcher
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3950 Visits: 6686
Don't rely solely on the missing index recommendations from those tables. SQL typically suggests way too many indexes. You need to have someone review all available information before just creating the stated indexes, or you will end up with too many indexes and very slow table DELETEs, INSERTs and UPDATEs.

SQL DBA,SQL Server MVP('07, '08, '09)

Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
thava
thava
SSC Veteran
SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)

Group: General Forum Members
Points: 264 Visits: 556
thanks for the reply



Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script
Arjun Sivadasan
Arjun Sivadasan
SSC-Addicted
SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)

Group: General Forum Members
Points: 454 Visits: 976
Also, keep in mind that SQL Server stores statistics for left-based subsets of an index. You may want to look at all SPs or queries which refer to the table in question and redesign indexes.

Read more about that here - http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search