SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Performance Issue: Execution Plan different (involves cursors)


Performance Issue: Execution Plan different (involves cursors)

Author
Message
sotn
sotn
Mr or Mrs. 500
Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)

Group: General Forum Members
Points: 576 Visits: 469
On our production server SQL 2008 R2 10.50.4000 our ERP system runs this query (nothing we can do about the cursors)

declare @p1 int
set @p1=NULL
declare @p3 int
set @p3=16
declare @p4 int
set @p4=1
declare @p5 int
set @p5=1
exec sp_cursoropen @p1 output,N'SELECT *,DATALENGTH("Picture") FROM "dbname"."dbo"."Item_table"
WITH (READUNCOMMITTED, INDEX("Item_table$0"))
WHERE "No_">=@P1 ORDER BY "No_" ', @p3 output,@p4 output,@p5 output,N'@P1 varchar(20)','AAA111'
select @p1, @p3, @p4, @p5

Our ERP system coding is the same between test and live (I'm told) and we cannot change it. The index hint is referring to the clustered key.
On the production server this is taking 1 minute and 3.7 million reads, but on the production server it takes 6 milliseconds and 4 reads. The data is very close to being the same.

The execution plans are almost the same (although the fast one has higher CPU and I/O costs).
The quick one is this
Clustered Index Insert (tempdb primary key) 93% <- Compute Scalar <- Compute Scalar <- Clustered Index Seek (7%)

The slow one is
Clustered Index Insert (tempdb primary key) 93% <- Sequence Project (Compute Scalar) <- Seqment <- Compute Scalar <- Clustered Index Seek (7%)

If I run the query direclty within SSMS I have to change @P3 from 16 to 28688 as 16 gives me the error
Msg 137, Level 15, State 2, Line 2
Must declare the scalar variable "@P1".
Msg 16945, Level 16, State 2, Procedure sp_cursoropen, Line 1
The cursor was not declared.

When I change P3 so it runs, it runs perfectly, returning the 1 record as per in @P5

Does anyone have an idea as to what the difference means and why it is much slower, it looks like it is trying to capture the entire recordset into the cursor and not just the 1 record.
@SQLFRNDZ
@SQLFRNDZ
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2261 Visits: 1222
sotn (12/27/2012)
On our production server SQL 2008 R2 10.50.4000 our ERP system runs this query (nothing we can do about the cursors)

declare @p1 int
set @p1=NULL
declare @p3 int
set @p3=16
declare @p4 int
set @p4=1
declare @p5 int
set @p5=1
exec sp_cursoropen @p1 output,N'SELECT *,DATALENGTH("Picture") FROM "dbname"."dbo"."Item_table"
WITH (READUNCOMMITTED, INDEX("Item_table$0"))
WHERE "No_">=@P1 ORDER BY "No_" ', @p3 output,@p4 output,@p5 output,N'@P1 varchar(20)','AAA111'
select @p1, @p3, @p4, @p5

Our ERP system coding is the same between test and live (I'm told) and we cannot change it. The index hint is referring to the clustered key.
On the production server this is taking 1 minute and 3.7 million reads, but on the production server it takes 6 milliseconds and 4 reads. The data is very close to being the same.

The execution plans are almost the same (although the fast one has higher CPU and I/O costs).
The quick one is this
Clustered Index Insert (tempdb primary key) 93% <- Compute Scalar <- Compute Scalar <- Clustered Index Seek (7%)

The slow one is
Clustered Index Insert (tempdb primary key) 93% <- Sequence Project (Compute Scalar) <- Seqment <- Compute Scalar <- Clustered Index Seek (7%)

If I run the query direclty within SSMS I have to change @P3 from 16 to 28688 as 16 gives me the error
Msg 137, Level 15, State 2, Line 2
Must declare the scalar variable "@P1".
Msg 16945, Level 16, State 2, Procedure sp_cursoropen, Line 1
The cursor was not declared.

When I change P3 so it runs, it runs perfectly, returning the 1 record as per in @P5

Does anyone have an idea as to what the difference means and why it is much slower, it looks like it is trying to capture the entire recordset into the cursor and not just the 1 record.



As you said everything is similar including execuion plans,data and server configurations then it should run perfectly same when it has also the same user activity and utilization of the resources.


--SQLFRNDZ

sotn
sotn
Mr or Mrs. 500
Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)

Group: General Forum Members
Points: 576 Visits: 469
If we rebuild the indexes on the table, then all is well again but we are having to do this every day, to 'handle' the problem.
Wedo have overnight tasks to modify certain fields (not key fields) in the table.
Next time, I will check the statistics, but as the correct index is used/forced (the primary key), I'm not sure where exactly to look further.
TheSQLGuru
TheSQLGuru
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33531 Visits: 8682
Rebuilding index gets you a 100% refresh of statistics. I would be willing to bet (without additional information) that this is a stale statistics issue whereby the optimizer doesn't "know" about a particular parameter value due to stats being out of date and it "guesses" and gets a bad plan (scan from the sound of it).

Make a job to nightly refresh stats on that one table and see if that helps. Use Ola.Hallengren.com's AWESOME maintenance suite for this. VERY amazing, FLEXIBLE, DOCUMENTED and FREE script suite for handling all your maintenance needs.

BTW, I have some clients that run stats updates as frequently as every hour on some critical tables. The auto-update rule of 20% modified rows just doesn't cut it in MANY MANY cases!! Statistics are the core underpinnings of the optimization process, and if they aren't valid per current data, nothing good will happen with your queries! Cool

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search