Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SuperGhost
Starting Member
12 Posts |
 Posted - 2010-04-15 : 14:28:51
|
A little bit of background...My schema is:[id] [int] IDENTITY(1,1) NOT NULL,[roleid] [int] NOT NULL,[agencyid] [int] NOT NULLThe table was created with 2 indexes:CREATE NONCLUSTERED INDEX [NCIX_agencyid] ON [dbo].[RoleAgencyComplete] ( [agencyid] ASC) ON [PRIMARY] andCREATE NONCLUSTERED INDEX [NCIX_roleid] ON [dbo].[RoleAgencyComplete] ( [roleid] ASC) ON [PRIMARY] When I run my stored proc with the above indexes I get a very slow query 10sec+ (query is large so I'll forego posting it)However when I add the following index:CREATE NONCLUSTERED INDEX [NCIX_AgencyId_RoleId] ON [dbo].[RoleAgencyComplete] ( [agencyid] ASC, [roleid] ASC) ON [PRIMARY] My query runs in under a second... Great! But looking through both execution plans they are identical. Also in the execution plan the node that uses RoleAgencyComplete only shows 1% of the entire query for both plans, yet it is clearly what's causing the slow response.My question is why is there no difference in the actual execution plan and under the same circumstances is there any way I could easily troubleshoot something like this? I had to jump through so many hoops and it took me a few days to get to the bottom of this issue... I just don't understand why :) |
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-04-15 : 17:31:49
|
quote:
Originally posted by SuperGhostMy question is why is there no difference in the actual execution plan and under the same circumstances is there any way I could easily troubleshoot something like this? I had to jump through so many hoops and it took me a few days to get to the bottom of this issue... I just don't understand why :)
Without seeing the query or the other tables, it's difficult to answer, however, if the query plan is the same, then it's safe to say it's not using the index NCIX_AgencyId_RoleId. You'll probably find the reason it runs quicker the second time is due to caching. If you run the query with the index, drop the index, then re-run the query, it will still run faster, because it doesn't have to read the data from the disk the second time.------------------------------------------------------------------------------------Any and all code contained within this post comes with a 100% money back guarantee. |
 |
|
|
SuperGhost
Starting Member
12 Posts |
Posted - 2010-04-15 : 17:58:59
|
| I run the query twice, once forcing the use of NCIX_AgencyId_RoleId (fast) and the second one forcing NCIX_agencyid (slow) so I don't believe I am seeing the caching, first one is quick second is slow as expected... I just thought I would see some sort of difference in the plan. When comparing both plans they both equally take 50% of the overall batch ~ I would have expected the first one to be smaller overall... Client statistics definitely show the differences. |
|
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-04-15 : 18:39:38
|
| Are you sure both the plans are identical, and NCIX_AgencyId_RoleId is not being used in the second plan?------------------------------------------------------------------------------------Any and all code contained within this post comes with a 100% money back guarantee. |
|
|
|
|
|
|
|
|
How to troubleshoot a slow query