May 10, 2010 at 7:17 am
This was discussed a little bit on a different thread but I thought I'd present it here since I'm still baffled.
I have a stored procedure with one parameter that executes a query that joins a number of tables. In most cases, the optimizer generates a query plan that works relatively efficiently; however, we have one customer where, for some reason, the query plan generated by the optimizer is not optimal and performs a number of index scans.
The indexes have been rebuilt and all the statistics are up to date and were built using FULLSCAN.
The query does reference a very ugly view that does a series of recursive joins against a table with 23 rows in it. That's why there are a bunch of table scans in the query plan. You will note that these table scans appear in both of the query plans attached.
The cardinality of the tables is roughly the same for most of the cases that I've seen work properly versus the one that doesn't. I've also seen it work properly against much larger data sets and much smaller data sets.
There are two equivalent databases on the same server and it works fine on one and doesn't on the other so I don't think hardware configuration or SQL Server configuration is an issue either.
There are three files in the attachment.
spSelDocSchedInfo.sql is the stored proc.
spSelDocSchedInfoBob.sqlplan is the query plan that is a problem.
spSelDocSchedInfoWorks.sqlplan is the query plan that works just fine.
Any one have any idea as to why the behavior might be different? I'm running out of ideas.
"Beliefs" get in the way of learning.
May 10, 2010 at 7:22 am
Attachment? <looks around>
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 10, 2010 at 7:29 am
Sorry. No coffee yet.
This is one you looked at Friday. I've added the "good" query plan as well.
"Beliefs" get in the way of learning.
May 10, 2010 at 7:34 am
I'm probably not going to get a chance to look at this, got a lot of work this week. Maybe Grant feels like a challenge.
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 10, 2010 at 7:53 am
GilaMonster (5/10/2010)
I'm probably not going to get a chance to look at this, got a lot of work this week. Maybe Grant feels like a challenge.
I don't have access to an RAR upacker at work. Can you zip the files?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 10, 2010 at 7:57 am
Hmm the Optimizer is timing out. Reason for early Termination = "Time Out".
If you remove the view does it execute ok ?
Try an outer apply to the view.
--Tried to reZip the files for Grant but firewall is stopping the upload
May 10, 2010 at 8:06 am
Grant Fritchey (5/10/2010)
GilaMonster (5/10/2010)
I'm probably not going to get a chance to look at this, got a lot of work this week. Maybe Grant feels like a challenge.I don't have access to an RAR upacker at work. Can you zip the files?
Try this one.
"Beliefs" get in the way of learning.
May 10, 2010 at 8:07 am
Robert Frasca (5/10/2010)
Grant Fritchey (5/10/2010)
GilaMonster (5/10/2010)
I'm probably not going to get a chance to look at this, got a lot of work this week. Maybe Grant feels like a challenge.I don't have access to an RAR upacker at work. Can you zip the files?
Try this one.
Sorry, the ZIP is coming up as invalid.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 10, 2010 at 8:11 am
Grant Fritchey (5/10/2010)
Robert Frasca (5/10/2010)
Grant Fritchey (5/10/2010)
GilaMonster (5/10/2010)
I'm probably not going to get a chance to look at this, got a lot of work this week. Maybe Grant feels like a challenge.I don't have access to an RAR upacker at work. Can you zip the files?
Try this one.
Sorry, the ZIP is coming up as invalid.
Tried to use WinRAR to convert to zip. This should be the real deal.
"Beliefs" get in the way of learning.
May 10, 2010 at 8:13 am
Whats the Patient_info_field table ? Its table scanning , is there no indexes on that ?
Can you post the view definition to ?
May 10, 2010 at 8:13 am
Dave Ballantyne (5/10/2010)
Hmm the Optimizer is timing out. Reason for early Termination = "Time Out".
Considering the size, I'm hardly surprised. Looking at that thing last week I felt like I was exploring a jungle without a map. Kept looking for the end of the plan and just finding more, and more, and more...
Robert, did you get a chance to try the 'divide and conquer' strategy? Insert results of view into temp table and join in. Did it make any difference?
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 10, 2010 at 8:20 am
Dave Ballantyne (5/10/2010)
Whats the Patient_info_field table ? Its table scanning , is there no indexes on that ?Can you post the view definition to ?
That's the table referenced in the view that only has 23 rows in it.
I've attached the definition of the view. Here's the table definition. It has a primary key but that's it.
/****** Object: Table [PM].[Patient_Info_Fields] Script Date: 05/10/2010 10:18:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [PM].[Patient_Info_Fields](
[Patient_Info_Field_ID] [int] IDENTITY(1,1) NOT NULL,
[Patient_Info_Field_TS] [timestamp] NOT NULL,
[Entry_Sequence] [int] NOT NULL,
[Searchable_Field] [bit] NOT NULL,
[Required_Field] [bit] NOT NULL,
[Field_Name] [varchar](40) NOT NULL,
[Field_Type] [int] NOT NULL,
[Field_Length] [int] NULL,
[Field_Format] [varchar](20) NULL,
[Field_Values] [varchar](1000) NULL,
[Export_Sequence] [int] NOT NULL,
CONSTRAINT [PK__Patient_Info_Fie__58A8A999] PRIMARY KEY NONCLUSTERED
(
[Patient_Info_Field_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [PM].[Patient_Info_Fields] ADD DEFAULT (0) FOR [Export_Sequence]
GO
"Beliefs" get in the way of learning.
May 10, 2010 at 8:20 am
Dave Ballantyne (5/10/2010)
Whats the Patient_info_field table ? Its table scanning , is there no indexes on that ?
Apparently there are 20 or so rows in it. Index may help, I did recommend one.
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 10, 2010 at 8:32 am
Sorry, that's the wrong view definition. That's one I was playing with that didn't work. Here's the live version.
"Beliefs" get in the way of learning.
May 10, 2010 at 8:33 am
I tried adding the index on Entry_Sequence on the Patient_Info_Fields table but it didn't use it.
"Beliefs" get in the way of learning.
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply