<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
Administering
»
Performance optimization on huge data
20 posts, Page 1 of 2
1
2
»»
Performance optimization on huge data
Rate Topic
Display Mode
Topic Options
Author
Message
sashikanta.mishra
sashikanta.mishra
Posted Wednesday, December 02, 2009 10:44 PM
SSC 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
joeroshan
joeroshan
Posted Wednesday, December 02, 2009 11:01 PM
Mr or Mrs. 500
Group: General Forum Members
Last Login: 2 days ago @ 3:45 AM
Points: 572,
Visits: 1,157
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
arr.nagaraj
arr.nagaraj
Posted Wednesday, December 02, 2009 11:42 PM
SSC-Addicted
Group: General Forum Members
Last Login: Wednesday, May 08, 2013 12:27 AM
Points: 483,
Visits: 1,191
provide the most important queries and table structure..
Regards,
Raj
Strictlysql.blogspot.com
Post #827958
dmoldovan
dmoldovan
Posted Thursday, December 03, 2009 1:01 AM
Old Hand
Group: General Forum Members
Last Login: Tuesday, May 14, 2013 5:51 AM
Points: 360,
Visits: 1,260
...and attach an execution plan, if possible...
Post #827987
luckysql.kinda
luckysql.kinda
Posted Thursday, December 03, 2009 1:34 AM
Old Hand
Group: General Forum Members
Last Login: Wednesday, April 24, 2013 11:19 AM
Points: 309,
Visits: 645
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
GilaMonster
GilaMonster
Posted Thursday, December 03, 2009 1:36 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 12:59 PM
Points: 37,640,
Visits: 29,895
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
Grant Fritchey
Grant Fritchey
Posted Thursday, December 03, 2009 7:11 AM
SSChampion
Group: General Forum Members
Last Login: Today @ 4:50 AM
Points: 13,371,
Visits: 25,143
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans
Product Evangelist for
Red Gate Software
Post #828135
sashikanta.mishra
sashikanta.mishra
Posted Thursday, December 03, 2009 11:47 PM
SSC 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
GilaMonster
GilaMonster
Posted Friday, December 04, 2009 12:20 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 12:59 PM
Points: 37,640,
Visits: 29,895
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
Grant Fritchey
Grant Fritchey
Posted Friday, December 04, 2009 5:55 AM
SSChampion
Group: General Forum Members
Last Login: Today @ 4:50 AM
Points: 13,371,
Visits: 25,143
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 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 »
20 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
