Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Index with Include Expand / Collapse
Author
Message
Posted Wednesday, October 24, 2012 8:32 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, September 14, 2014 12:30 AM
Points: 253, Visits: 540
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
Post #1376516
Posted Wednesday, October 24, 2012 9:35 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
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
Post #1376554
Posted Wednesday, October 24, 2012 9:40 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:22 PM
Points: 2,121, Visits: 3,207
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1376556
Posted Wednesday, October 24, 2012 10:39 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, September 14, 2014 12:30 AM
Points: 253, Visits: 540
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
Post #1376767
Posted Thursday, October 25, 2012 5:59 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, September 25, 2014 12:24 AM
Points: 406, Visits: 775
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/
Post #1376942
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse