Clustered and Non-clustered Index

[plugsharma]

Hi,

I have one table having more than 3 millions rows with following columns:

[SITE_ID]

[USER_NAME]

[ACTIVITY_NAME]

[PAGE_TYPE]

[ACTION]

[DOC_ID]

[FROM_PAGE]

[TO_PAGE]

[ORG_NAME]

[LOG_DATE_TIME]

[SESSION_ID]

[IP]

[MACHINE_ID]



I am using 8 different queries, all thru stored procedure, to fetch data.

SITE_ID, USER_NAME, ORG_NAME and ACTION are in all queries WHERE clause.

I have created SITE_ID as non-unique clustered index and USER_NAME, ORG_NAME and ACTION as non-unique non-clustered index.

Problem is that data fetching without indexs is faster than when creating idex.

Is there any problem in index columns.

Can you please suggest me a proper indexing plan.

Images file described columns called details

(

http://xs434.xs.to/xs434/08513/queries-columns997.jpg

or

http://xs.to/xs.php?h=xs434&d=08513&f=queries-olumns997.jpg)

Please help..


Thanks

Sharma

 

[JayKusch]

Have you executed a query plan against your stored procedures to tell if the indexes are being leveraged and/or if there is other contention in the stored procedure(s) that may be causing you pains.

Also ... What is columns are your clustered index/Primary Key based if any?

Thanks

J. Kusch

 

[mrdenny]

See my answer over on the IT Knowledge Exchange.

http://itknowledgeexchange.techtarget.com/itanswers/clustered-and-non-clustered-index

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2008 Implementation and Maintenance / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Admin (SQL 2005/2008) / Database Dev (SQL 2005)

My Blog