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

Need Sugestion on Which Columns I need To Create A NonClustered Index? Expand / Collapse
Author
Message
Posted Tuesday, June 18, 2013 2:58 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: 2 days ago @ 6:56 AM
Points: 469, Visits: 588
I have a huge amount of data table EMP with 15 columns, and it has only one primary key with its clustered index, no other indexes are there on table columns.

Now I want to create a non clustered index on this table. How you come to know what are the columns should be there in new index. I dont want to search for how many procedures are using EMP and what columns are they using.
Tell me the way to create Index.


Post #1464513
Posted Tuesday, June 18, 2013 3:19 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 42,771, Visits: 35,870
You need to examine the procedures using the EMP table and see what columns they're using.

http://www.sqlservercentral.com/articles/Indexing/68439/
http://www.sqlservercentral.com/articles/Indexing/68563/
http://www.sqlservercentral.com/articles/Indexing/68636/



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1464525
Posted Tuesday, June 18, 2013 3:54 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: 2 days ago @ 6:56 AM
Points: 469, Visits: 588
Thanks Gila Monster for the reply.But There is an exception with out searching which procedures are getting used
the table and what are the columns getting used is it possible to identify the non clustered index columns?
Post #1464538
Posted Tuesday, June 18, 2013 4:05 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:13 AM
Points: 2,631, Visits: 4,723
Ananth@Sql (6/18/2013)
Thanks Gila Monster for the reply.But There is an exception with out searching which procedures are getting used
the table and what are the columns getting used is it possible to identify the non clustered index columns?


No. You can use tools like the SQL Server Profiler and DTA to get some suggestions.
But these are only suggestions which should never be accepted without proper analysis and testing.



Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1464545
Posted Tuesday, June 18, 2013 4:07 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 42,771, Visits: 35,870
Ananth@Sql (6/18/2013)
But There is an exception with out searching which procedures are getting used the table and what are the columns getting used is it possible to identify the non clustered index columns?


No. Did you read the articles I referenced?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1464546
Posted Wednesday, June 26, 2013 3:12 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, September 27, 2013 5:37 AM
Points: 306, Visits: 524
GilaMonster (6/18/2013)
You need to examine the procedures using the EMP table and see what columns they're using.

http://www.sqlservercentral.com/articles/Indexing/68439/
http://www.sqlservercentral.com/articles/Indexing/68563/
http://www.sqlservercentral.com/articles/Indexing/68636/



Thanks GILA for sharing all three article


Neeraj Prasad Sharma
Sql Server Tutorials
Post #1467523
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse