December 2, 2009 at 10:44 pm
I am using SQL 2005 Express Edition,Developer Edition and Enterprise Edition in different level. I have clustered and non-clustered index defined on a table having 50 lac of records which may grow more than double in a couple of months. This table is heavily used in transaction from my java code.
This table is having one clustered index defined on its only primary key column. And there are 4 other non-clustered index defined on this table. One of the non-clustered index is having all the columns in that table.
My problem is still the select and update queries taking too much time.
Note: I have already done re-indexing on that table.
Can any one suggest how to optimize the query execution by adding/updating index. Or any other approach i should take.
Thanks & Regards,
Sashikanta Mishra!!
December 2, 2009 at 11:01 pm
500000 records is not a big table.
1) What is your configuration.
2) Please post the query and its execution plan to see if any indexes can help
December 2, 2009 at 11:42 pm
provide the most important queries and table structure..
Regards,
Raj
December 3, 2009 at 1:01 am
...and attach an execution plan, if possible...
December 3, 2009 at 1:34 am
Re-indexing is ok but we need to analyze whether we have correct indexes. These indexes should help data retrieval process instead of bothering this. Post us the queries and the structure of all the tables involved in these queries.
-lk
December 3, 2009 at 1:36 am
Please post queries, table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
One of the non-clustered index is having all the columns in that table.
Why? Is that index used? Does it really need to be that large?
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
December 3, 2009 at 7:11 am
GilaMonster (12/3/2009)
Please post queries, table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/One of the non-clustered index is having all the columns in that table.
Why? Is that index used? Does it really need to be that large?
Another vote for that being a pretty dangerous thing. You do realize a non-clustered index with every column is effectively the same as the clustered index? It's just bigger since it's also going to store the clustered index key along with the non-clustered index information.
"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
December 3, 2009 at 11:47 pm
Here is my non Clustered Index:
Primary Key column is : [PLGID]
CREATE NONCLUSTERED INDEX [_dta_index_STOCK_5_877298235__K6_1_33_35_37_38_39_40_41_42_43_44_45_46_48_49_50_51_52_54_55_56_57_58_59_60_61_62_63_64_65_66_] ON [dbo].[STOCK]
(
[PLFTX] ASC
)
INCLUDE
(
[PLGID],
[PAQCO],
[PNARR],
[PLTRF],
[PORFY],
[PEXDT],
[PEXTM],
[PRFOR],
[PRFLN],
[PRFSL],
[PRFFY],
[PTRRE],
[PRESP],
[PTRCU],
[PTRSC],
[PTRRT],
[PERTY],
[PLHID],
[PMHID],
[PCHID],
[PETDT],
[PETTM],
[PDBCR],
[PIAMD],
[PEXVL],
[PXPDT],
[PLCVL],
[PORRV],
[POOHB],
[PNOHB],
[PRFID],
[PPLCD],
[PFRE1],
[PFRE2],
[PFRE3],
[PFRE4],
[PFRE5],
[PFRE6]
) 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].
I agree that all the column should not be part of index column. So i only choose the column to be indexed and rest columns are in INCLUDE . So there is some improvement around 20% improvement.
My question is that should i trim all those in the INCLUDE part. Is that will be helpful.
Thanks a lot for viewing my question and posting valuable answer. Please suggest if i can do any other thing.
Thanks & Regards,
Sashikanta Mishra.
December 4, 2009 at 12:20 am
sashikanta.mishra (12/3/2009)
I agree that all the column should not be part of index column. So i only choose the column to be indexed and rest columns are in INCLUDE . So there is some improvement around 20% improvement
We're saying that having all the columns in the index, as key or include, is a very bad idea. Does the index really, really, really need to be that large? Is that need sufficient to justify using double (or more) the space of the table itself in total? Does it justify the costs of rebuilding an index that large and maintaining and index that large?
I can't tell you whether you should or shouldn't remove those includes without seeing the queries that run against that table. Do some investigation, look at all the queries that run against that table. Which ones use that index? Will they still use it if it's smaller?
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
December 4, 2009 at 5:55 am
GilaMonster (12/4/2009)
sashikanta.mishra (12/3/2009)
I agree that all the column should not be part of index column. So i only choose the column to be indexed and rest columns are in INCLUDE . So there is some improvement around 20% improvementWe're saying that having all the columns in the index, as key or include, is a very bad idea. Does the index really, really, really need to be that large? Is that need sufficient to justify using double (or more)
More, absolutely more. The key values plus the include values plus the clustered key, it has to add up to more.
the space of the table itself in total? Does it justify the costs of rebuilding an index that large and maintaining and index that large?
I can't tell you whether you should or shouldn't remove those includes without seeing the queries that run against that table. Do some investigation, look at all the queries that run against that table. Which ones use that index? Will they still use it if it's smaller?
110% agreement.
Even if this index is getting "used" doesn't mean it should be there. This is a classic case of achieving balance between index management and the desire to eliminate key lookups.
"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
December 4, 2009 at 6:20 am
Grant Fritchey (12/4/2009)
More, absolutely more. The key values plus the include values plus the clustered key, it has to add up to more.
Depends what the key is. If it's bigger than the clustering key or the index is unique, sure. If it's unique (ie the clustering key isn't needed at the intermediate levels) and the key is smaller than the cluster, it should be the same size or slightly smaller. Leaf level'll be the same size as the leaf of the cluster.
Needs testing. And maybe blogging.
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
December 4, 2009 at 6:24 am
You should post the table structure, index(es) as well as the select statement being issued. One can only speculate what the issues are without that information.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
December 4, 2009 at 6:53 am
GilaMonster (12/4/2009)
Grant Fritchey (12/4/2009)
More, absolutely more. The key values plus the include values plus the clustered key, it has to add up to more.Depends what the key is. If it's bigger than the clustering key or the index is unique, sure. If it's unique (ie the clustering key isn't needed at the intermediate levels) and the key is smaller than the cluster, it should be the same size or slightly smaller. Leaf level'll be the same size as the leaf of the cluster.
Needs testing. And maybe blogging.
Funny, I was thinking the same thing. Now, if only I could add about three hours to the day...
"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
December 4, 2009 at 7:43 am
Only three?
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
December 4, 2009 at 8:08 am
GilaMonster (12/4/2009)
Only three?
For this one.
"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
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply