How to check Performance ON Cluster Index on table
-
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
-
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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
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
-
No, it's not a good idea to include all columns. Clustered indexes should be narrow, that one is not.
http://qa.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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
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 -
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
-
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 -
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
-
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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
-
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 -
after using where clause also its taking 100% cost ?
any other way to improve ?
thanks
-
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 -
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
-
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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply