How to check Performance ON Cluster Index on table

Question

How to check Performance ON Cluster Index on table

santoshkal

SSC-Addicted

Points: 497
More actions
October 9, 2014 at 5:01 am

#290820

Hi,
I have a table of 50 million records with 8 columns & a cluster index with 7 columns all are int type in ascending
so how do i check the performance of the cluster index on the table
please help me out
thanks

Viewing 15 posts - 1 through 15 (of 22 total)

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

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

What, exactly, do you mean by 'performance of the clustered index'?

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

santoshkal SSC-Addicted Points: 497 More actions · Answer 2

we created the cluster index so want to check how performance effect on the table

USE [Live_Demo_Redux]

GO

/****** Object: Index [CDX_APPID] Script Date: 10/09/2014 16:41:03 ******/

CREATE CLUSTERED INDEX [CDX_APPID] ON [dbo].[MASTERFLATFILE]

(

[APPID] ASC,

[GEOGID] ASC,

[PRODID] ASC,

[CAID] ASC,

[VARID] ASC,

[OUID] ASC,

[CRID] ASC,

[PERID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

GO

so how can we check the performance or whether it is good to include all columns or not

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

No, it's not a good idea to include all columns. Clustered indexes should be narrow, that one is not.

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

As for how you test, identify the frequent queries which run against the table (SQL Trace), run them before, run them after, compare results.

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

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 4

Maybe also compare if INSERTS have slowed down or not.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

TheRedneckDBA SSChampion Points: 14001 More actions · Answer 5

And look at the disk space the table is using before and after the index. Especially if you do a lot of included columns in that index.

The Redneck DBA

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 6

Jason Shadonix (10/9/2014)
And look at the disk space the table is using before and after the index. Especially if you do a lot of included columns in that index.

It's a clustered index, it can't have included columns. 😉

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

santoshkal SSC-Addicted Points: 497 More actions · Answer 7

so how to improve performance of the table & when we have 50 million records.

if we create non clustered index like

CREATE NONCLUSTERED INDEX [IDX_APPID_1] ON [dbo].[MASTERFLATFILE]

(

[GEOGID] ASC,

[VARID] ASC,

[APPID] ASC,

[OUID] ASC,

[CAID] ASC

)

INCLUDE ( [PRODID],

[CRID],

[PERID],

[DFID],

[VariableValue]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

or any other which can improve performance when i run the select * from

& checked the execution plan its cost 100%

so need to help

thanks

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

santoshkal (10/9/2014)
when i run the select * from
& checked the execution plan its cost 100%

Well, of course it is.

Costs have to add up to 100%, so if you're doing a SELECT * with no where clause, the only operator in the plan which can have any cost is the table scan/index scan, so it has to have a cost of 100%

so how to improve performance of the table & when we have 50 million records.

Write queries in such a way that they can benefit from indexes, create indexes to support the queries. That's all that can be said with the limited info you've provided.

Indexes support searches. Queries with WHERE clause predicates and/or joins. A SELECT * FROM Table has to read the entire table, there's no index which can magically make that faster.

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: 398882 More actions · Answer 9

You can get a determination of the performance and usefulness of an index three ways. First, you measure the time on a given query before and after applying the index. You also measure the I/O of that query before and after applying the index. You can do both these through SET STATISTICS IO and SET STATISTICS TIME in a T-SQL window. You can also capture them using extended events. The other measure is the execution plan. Is the index being used in an optimal manner, for example, a seek in a point lookup situation.

Putting all the columns in the key as you've done means that your WHERE clause of your query is likely to use all those columns. Is that accurate? If not, I think you're misunderstanding how indexes work.

"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

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 10

santoshkal (10/9/2014)
or any other which can improve performance when i run the select * from
& checked the execution plan its cost 100%

The execution plan will always be 100% (relative to the batch if it's the only query).

See this screenshot for this very easy query:

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

santoshkal SSC-Addicted Points: 497 More actions · Answer 11

after using where clause also its taking 100% cost ?

any other way to improve ?

thanks

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 12

santoshkal (10/9/2014)
after using where clause also its taking 100% cost ?
any other way to improve ?
thanks

I'll say it again.

If it is the only query in the batch, it will always be 100%. Always. Again: always. It's a relative cost.

Check execution time, CPU cycles and I/O reads instead.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 13

santoshkal (10/9/2014)
after using where clause also its taking 100% cost ?
any other way to improve ?
thanks

That's a measure of an estimate in comparison to measures of other estimates. There are no other estimates, so it takes 100%. That's not a measure of performance. It's just a measure of plan cost, which is also not a measure of performance. Look at my other post. You want to measure execution time and I/O and then evaluate the plan itself (note, that's not just getting the plan cost because that's not a measure of performance).

"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 14

santoshkal (10/9/2014)
after using where clause also its taking 100% cost ?

Of course it it. It's still the only operation in the batch, the costs have to add to 100%, that's what a % is.

any other way to improve ?

No idea. You haven't given us the query, you haven't given any details of the table, you haven't given any evidence that there's anything that needs to be improved.

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