Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Performance optimization on huge data Expand / Collapse
Author
Message
Posted Wednesday, December 2, 2009 10:44 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, July 24, 2012 3:21 AM
Points: 79, Visits: 110
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!!
Post #827946
Posted Wednesday, December 2, 2009 11:01 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 12:00 AM
Points: 630, Visits: 1,291
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


-- Roshan Joe
*******************************************
Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help
Custom cleanup script for backups
Post #827950
Posted Wednesday, December 2, 2009 11:42 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 12:44 AM
Points: 490, Visits: 1,341
provide the most important queries and table structure..

Regards,
Raj

Strictlysql.blogspot.com
Post #827958
Posted Thursday, December 3, 2009 1:01 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, October 20, 2014 2:24 AM
Points: 363, Visits: 1,324
...and attach an execution plan, if possible...
Post #827987
Posted Thursday, December 3, 2009 1:34 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 11, 2014 12:39 PM
Points: 310, Visits: 659
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
Post #827996
Posted Thursday, December 3, 2009 1:36 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:37 PM
Points: 40,210, Visits: 36,619
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 2008, MVP
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

Post #827998
Posted Thursday, December 3, 2009 7:11 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 6:53 AM
Points: 13,890, Visits: 28,285
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
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #828135
Posted Thursday, December 3, 2009 11:47 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, July 24, 2012 3:21 AM
Points: 79, Visits: 110
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.
Post #828653
Posted Friday, December 4, 2009 12:20 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:37 PM
Points: 40,210, Visits: 36,619
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 2008, MVP
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

Post #828658
Posted Friday, December 4, 2009 5:55 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 6:53 AM
Points: 13,890, Visits: 28,285
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% 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)

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
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #828792
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse