Clustered and Non-clustered Index

Question

Clustered and Non-clustered Index

kuna.sharma

SSC Veteran

Points: 201
More actions
December 17, 2008 at 6:45 am

#216601

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]
127.0.0.1
[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 better index plan.
I have attached the queries / column table image file.
Images file described columns called details
(https://www.sqlservercentral.com/Forums/Uploads/image-unavailable.png or
http://xs.to/xs.php?h=xs434&d=08513&f=queries-columns997.jpg)
Please help..
Thanks
Sharma

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398913 More actions · Answer 1

Can you generate and post a couple of execution plans? That will help show what's happening under the covers on your system, with your data.

This will help if you haven't use execution plans before.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Also, can you post the full table definition, including data types and constraints (if any)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 3

I would define the clustered index on one of the columns used in the ORDER BY clause. The USER_NAME or LOG_DATE_TIME columns might be good candidates.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Username is typically a very bad choice for a clustered index. It's wide, it's nowhere near unique and it's not ever increasing. LOG_DATE_TIME may be a good candidate. Still need to see the data type and some more info though.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

Grant Fritchey SSC Guru Points: 398913 More actions · Answer 5

I would really want to see the common access paths of the queries prior to trying to decide the cluster. Honestly, if SITE_ID is the most frequently used query mechanism, I'd probably put it there, but not knowing what we're doing, any suggestions at this point are just speculation.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 6

we have a table with 300 million rows and had an identity/PK column as the clustered index. lots of index scans on some queries. changed the Clustered index to another column and it's a lot faster now.

you have to look at the data distribution in the columns in the where clauses before you decide which to make the clustered index

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 7

Clustered indexes are best on columns used in the GROUP BY/ORDER BY part of queries, and/or if the column in question is used to retrieve a range of values.

Non-clustered indexes are best on columns used in "point" queries that retrieve a relatively small number of records.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]