<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
»
Notification Services
»
Administration
»
Primary Key Vs Clustered Index: Performance...
14 posts, Page 1 of 2
1
2
»»
Primary Key Vs Clustered Index: Performance Tuning
Rate Topic
Display Mode
Topic Options
Author
Message
iamanks
iamanks
Posted Tuesday, December 21, 2010 12:31 AM
Grasshopper
Group: General Forum Members
Last Login: Thursday, January 06, 2011 9:39 PM
Points: 20,
Visits: 101
I've a performance related issue.
I've one big table A which is been used in one query having join with another big table B. Table A has no Primary key but a clustered index defined on four columns (composite) and two more nonclustered indexes. Table B also has no primary key but a clustered index with five columns (again composite) and three non clustered indexes. All of the indexes are on more than one column.
While running the query, it taking too much time and execution plan shows 23% of sort (execution plan shows its in one of the case statements with "in (select ")) on one column of table A which is part of clustered index. How should I resolve it ? Secondly, what is the significance of PK and clustered index separately ? Table A comprises of 35% of index seek and Table B shows 18% of index seek.
The query comprises of joins between these two tables and inserting in another table. Lots of columns in select statement and also few case statements with "exists (select" in them.
Post #1037538
spaghettidba
spaghettidba
Posted Tuesday, December 21, 2010 1:15 AM
SSCarpal Tunnel
Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:29 AM
Points: 4,804,
Visits: 8,067
Can you post the actual execution plan?
You may also take a look at this article on how to post performace problems:
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Get your two-cent-answer quickly
The Spaghetti DBA
Post #1037554
iamanks
iamanks
Posted Tuesday, December 21, 2010 6:08 AM
Grasshopper
Group: General Forum Members
Last Login: Thursday, January 06, 2011 9:39 PM
Points: 20,
Visits: 101
Not possible to send execution plan here. I created two new tables form above ones without any indexes and tried after replacing those into the query for a common set of parameters. Created primary clustered key constraint on both tables (with three columns on each table). Thereafter created one non clustered index for the three columns showing 18% in Hash Join in Table A (new one). Still taking approx same time (Just 20 seconds efficiency). Kindly suggest.
Post #1037658
GilaMonster
GilaMonster
Posted Tuesday, December 21, 2010 6:41 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Yesterday @ 5:49 PM
Points: 37,671,
Visits: 29,925
Without seeing the exec plan and the table/index definitions, it's really hard to answer these kind of questions. You're just not posting enough information for us to be able to do more than wildly guess.
Why no exec plan?
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 #1037670
spaghettidba
spaghettidba
Posted Tuesday, December 21, 2010 6:42 AM
SSCarpal Tunnel
Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:29 AM
Points: 4,804,
Visits: 8,067
Without a query plan to look at, I would end up with a useless suggestion.
I'm sure you can live without it.
Get your two-cent-answer quickly
The Spaghetti DBA
Post #1037671
iamanks
iamanks
Posted Tuesday, December 21, 2010 6:43 AM
Grasshopper
Group: General Forum Members
Last Login: Thursday, January 06, 2011 9:39 PM
Points: 20,
Visits: 101
For more information, I took help of sys.dm_db_missing_index_details and other DMVs to check out for missin index details. Table A and B both contains around 64 Lac records and their join result shows around 25k records. Select statement contains few case statements and overall query is taking 3 and half minutes to complete. Table A clustered index seek shows 66% participation.
Post #1037673
iamanks
iamanks
Posted Tuesday, December 21, 2010 10:56 PM
Grasshopper
Group: General Forum Members
Last Login: Thursday, January 06, 2011 9:39 PM
Points: 20,
Visits: 101
Here's the execution plan.....
Post Attachments
final.sqlplan
(
40 views,
146.70 KB
)
Post #1038074
GilaMonster
GilaMonster
Posted Wednesday, December 22, 2010 12:37 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Yesterday @ 5:49 PM
Points: 37,671,
Visits: 29,925
Table and index definitions please?
I'm guessing you didn't read the article that Gianluca posted.
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 #1038091
iamanks
iamanks
Posted Wednesday, December 22, 2010 12:56 AM
Grasshopper
Group: General Forum Members
Last Login: Thursday, January 06, 2011 9:39 PM
Points: 20,
Visits: 101
Here they are...
Post Attachments
Tab A.txt
(
41 views,
7.43 KB
)
Tab B.txt
(
20 views,
9.86 KB
)
Post #1038096
iamanks
iamanks
Posted Wednesday, December 22, 2010 2:39 AM
Grasshopper
Group: General Forum Members
Last Login: Thursday, January 06, 2011 9:39 PM
Points: 20,
Visits: 101
Any suggestions ???? Eagerly waiting your advice... Already tried and tested lots of things but query taking around 3:15 mins at best....
Post #1038127
« Prev Topic
|
Next Topic »
14 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.
