May 22, 2009 at 9:18 am
I recently found a performance issue with the plan optomizer. I have a frequentlly accessed table with the following definition.
CREATE TABLE [dbo].[hosp_rpt_nmrc] (
[RPT_REC_NUM] [bigint] NOT NULL ,
[WKSHT_CD] [char] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[LINE_NUM] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CLMN_NUM] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ITM_VAL_NUM] [bigint] NULL ,
[ITM_VAL_NUM-F] [real] NULL ,
[Flag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[line_num_cons] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[clmn_num_cons] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[wksht_cd_3pos] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[line_num_cons_l2] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [IX_HOSP_RPT_NMRC_1] ON [dbo].[hosp_rpt_nmrc]([RPT_REC_NUM], [wksht_cd_3pos], [line_num_cons_l2], [clmn_num_cons]) ON [PRIMARY]
GO
CREATE INDEX [IX_HOSP_RPT_NMRC] ON [dbo].[hosp_rpt_nmrc]([wksht_cd_3pos], [LINE_NUM], [CLMN_NUM], [RPT_REC_NUM], [ITM_VAL_NUM]) ON [PRIMARY]
GO
CREATE INDEX [IX_HOSP_RPT_NMRC_2] ON [dbo].[hosp_rpt_nmrc]([wksht_cd_3pos], [line_num_cons], [CLMN_NUM], [RPT_REC_NUM], [ITM_VAL_NUM]) ON [PRIMARY]
GO
The table contains 150M rows and Auto create statistics is on.
However the following query
SELECT *
FROM hosp_rpt_nmrc
WHERE (wksht_cd_3pos = 'E0A') AND (LINE_NUM between '05000' and '10000')
does a cluster index scan, running almost 2 minutes to return 3 records.
If I add a hint "with (index =ix_HOSP_RPT_NMRC)" the query plan becomes index seek with sub-second response time. I have rebuilt the indexes but that didn't change the behavior.
I'm running SQL 2000
Any suggestions before I have to add a bunch of hints to other querys ?
May 22, 2009 at 9:27 am
I suggest comparing the two actual execution plans. You may also want to post the plans here, but I'm not sure how to have you do that with SQL Server 2000.
Hopefully someone with a little more experience there can provide you guidance with this.
May 22, 2009 at 9:38 am
Hey Lynn, I am not an expert in this, but couldn't the Index fragmentation and the fact that the query is trying to retrieve all columns make the query engine to choose a clustered index?
-Roy
May 22, 2009 at 9:47 am
OK here's the text version of the plans. If anyone can tell me how I can get the graphical version into the forum, I try
(FYI the plan text has been edited to surround :'s with blanks, otherwise they become "frowny faces" )
StmtText
SELECT *
FROM hosp_rpt_nmrc with (index =ix_HOSP_RPT_NMRC)
WHERE (wksht_cd_3pos = 'E0A') AND (LINE_NUM between '05000' and '10000')
(1 row(s) affected)
StmtText
|--Bookmark Lookup(BOOKMARK : ([Bmk1000]), OBJECT : ([Cost_report_090331].[dbo].[hosp_rpt_nmrc]))
|--Index Seek(OBJECT : ([Cost_report_090331].[dbo].[hosp_rpt_nmrc].[IX_HOSP_RPT_NMRC]), SEEK : ([hosp_rpt_nmrc].[wksht_cd_3pos]='E0A' AND [hosp_rpt_nmrc].[LINE_NUM] >= '05000' AND [hosp_rpt_nmrc].[LINE_NUM] ='05000') AND [hosp_rpt_nmrc].[LINE_NUM]<='10000'))
(2 row(s) affected)
May 22, 2009 at 9:51 am
The only way I know of for the plan in 2000 is a screen shot, and if it doesn't fit on one, multiple ones.
The text plans are harder to read, but it does show the ordering of steps (reverse)
May 22, 2009 at 9:54 am
I think Roy has an interesting point.
The optimizer might be going for the clustered index because it thinks that using the bookmark might be expensive which it usually is.
I'd be interested to see what happens if you tell the optimizer to not use parallelism if perhaps it then uses the non-clustered index.
could try adding this to the end of you code
OPTION (MAXDOP 1)
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
May 22, 2009 at 9:58 am
I am not an expert in this, but couldn't the Index fragmentation and the fact that the query is trying to retrieve all columns make the query engine to choose a clustered index?
-Roy
I had the same thought, so I ran DBCC DBREINDEX on all indexes last night (took 8 hours), but it had no impact on the results. Thats when I opened this topic
May 22, 2009 at 10:07 am
I'd be interested to see what happens if you tell the optimizer to not use parallelism if perhaps it then uses the non-clustered index.
could try adding this to the end of you code
OPTION (MAXDOP 1)
You are right!! Adding the option, changed it back to using the non-clustered index with sub-second response. That helps understand the problem, but it's as ugly a solution as adding a lot of hints.
May 22, 2009 at 10:10 am
Yeah it's a not a perfect solution but I have had to use it sometimes, as I often find that the overhead of parallelism is too much for my poor queries...
I've seen a create example of this on a simple count(*) statement 🙂
I'm not too clued up on index defrag and stuff, so I think this is as much as I can help I'll hang-around though and see if I can learn something 😉
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
May 22, 2009 at 2:23 pm
Lynn Pettis (5/22/2009)
You may also want to post the plans here, but I'm not sure how to have you do that with SQL Server 2000.
Add the following line above the query and run the whole lot with output to grid
SET STATISTICS PROFILE ON
GO
There will be an extra resultset. Copy that, paste it in Excel. Save the spreadsheet, zip it and attach it.
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
May 22, 2009 at 2:56 pm
Here is it first with the hint then without hint
May 22, 2009 at 3:07 pm
Gail,
Tell me if I am reading this right. Does the query plan say that the parallel query "should" be more efficient (cost less)? I'm not sure what the various columns are as there are no headers.
May 22, 2009 at 3:11 pm
Lynn - yes the parallel plan is listed as having a lower cost, however it doesn't.
Gail said to run the query in grid mode so that's what I did. But I also ran it in text mode and pasted that into the spreadsheet on a separate tab. You might want to look at it for the column headings.
Thanks to both of you for you insights
May 22, 2009 at 3:25 pm
Ed Klein (5/22/2009)
Lynn - yes the parallel plan is listed as having a lower cost, however it doesn't.Gail said to run the query in grid mode so that's what I did. But I also ran it in text mode and pasted that into the spreadsheet on a separate tab. You might want to look at it for the column headings.
Thanks to both of you for you insights
Thanks, had it been a snake it would have bit me.
May 22, 2009 at 3:26 pm
Bear in mind that all costs are estimates, no matter whether it's an estimated or actual plan. There are a number of factors that could make them wrong.
Will look at plans in detail tomorrow if problem's still open. To get column headers I usually just run a simple query with the stats profile on so that I can match position to name. Got tonne of stuff that needs finishing tonight.
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
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply