May 28, 2009 at 8:36 am
We recently moved one of our applications (build in a 3GL called Uniface) from SQL 2000 to SQL 2005.
In some parts of the application we experience a strange performance issue where almost standard queries taking much longer than expected.
One example:
We have a base table called PERSOON (4M records) with guest info containing a clustered primaty key on pers_id (persoon_pk), with several fields like name (ZOEKNAAM), date of birth (GEBOORTE_DTM) and zip code (WOON_PTC). An index on name and date of birth (persoonI2) and another index on zip code (persoonI5).
The application uses a view called V_PERSOON which is almost a 1-on-1 select of the base table.
In the application there's a search screen which allows you to search on zip code (the field is indexed so it should be fast). Then the results are displayed on a screen sorted on name and date of birth.
The Uniface application supports a so-called stepped hitlist mechanism by using a cursor (sp_cursorprepexec) to retrieve the data.
The following SQL (captured with profiler) is executed (the SQL is generated automatically by the programming language), duration approximately 10 seconds:
declare @p1 int
set @p1=1073742182
declare @p2 int
set @p2=180150581
declare @p5 int
set @p5=4
declare @p6 int
set @p6=1
declare @p7 int
set @p7=-1
exec sp_cursorprepexec @p1 output,@p2 output,N'@P1 char(7)',N'select pers_id,zoeknaam,geboorte_dtm,woon_ptc
from v_persoon
where ((woon_ptc=@P1))
order by zoeknaam asc ,geboorte_dtm asc ',@p5 output,@p6 output,@p7 output,'2641 VZ'
select @p1, @p2, @p5, @p6, @p7
Execution plan:
Clustered Index Insert(OBJECT: (CWT), SET: ([CWT].[COLUMN0] = [vips70].[dbo].[persoon].[pers_id],[CWT].[ROWID] = [Expr1005]))
|--Compute Scalar(DEFINE: ([Expr1005]=CWT_ROWID()))
|--Filter(WHERE: ([vips70].[dbo].[persoon].[dbo].[persoon].[woon_ptc]=[@P1]))
|--Nested Loops(Inner Join, OUTER REFERENCES: ([vips70].[dbo].[persoon].[pers_id]))
|--Index Scan(OBJECT: ([vips70].[dbo].[persoon].[persoonI2]), ORDERED FORWARD)
|--Clustered Index Seek(OBJECT: ([vips70].[dbo].[persoon].[persoon_pk]), SEEK: ([vips70].[dbo].[persoon].[pers_id]=[vips70].[dbo].[persoon].[pers_id]) LOOKUP ORDERED FORWARD)
Instead of using the expected index persoonI5 on zip_code (WOON_PTC) the query is using the persoonI2 index (ZOEKNAAM,GEBOORTE_DTM) and then a clustered index seek to filter out the zip code.
When performing the same query (without cursor) directly in Management Studio, the query behaves as expected (querytime < 100ms).
Execution plan:
Sort(ORDER BY: ([Vips].[dbo].[persoon].[zoeknaam] ASC, [Vips].[dbo].[persoon].[geboorte_dtm] ASC))
|--Nested Loops(Inner Join, OUTER REFERENCES: ([Vips].[dbo].[persoon].[pers_id]) OPTIMIZED)
|--Index Seek(OBJECT: ([Vips].[dbo].[persoon].[persoonI5]), SEEK: ([Vips].[dbo].[persoon].[woon_ptc]='1963 EC') ORDERED FORWARD)
|--Clustered Index Seek(OBJECT: ([Vips].[dbo].[persoon].[persoon_pk]), SEEK: ([Vips].[dbo].[persoon].[pers_id]=[Vips].[dbo].[persoon].[pers_id]) LOOKUP ORDERED FORWARD)
The above is the most simple screen where we observe this problem. Other screens involve views on multiple tables (always the PERSOON table) and all those screens have the same sorting on ZOEKNAAM, GEBOORTE_DTM of PERSOON. As soon as the filtering occurs on one of the (indexed) fields of another table the query plan changes and prefers to use the persoonI2 index instead of the better index available.
Has anyone else experienced this kind of strange behaviour? Any possible solutions? Or did we just encounter another SQL Server Bug on cursors?
We're currently using SQL Server 2005 SP3 cump. patch 2 (build 4211), 64-bits, Standard Edition
May 28, 2009 at 8:48 am
Hi there,
Could you psot the execution plan a .sqlplan to this thread makes it easier to read.
The second thing is that if might be good to have an index that covers all three fields that would mean the optimizer can get all the data/filter from 1 index.
I'll know more when I see the plan, sorry I'm not great on text plans yet 🙂
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
May 28, 2009 at 9:50 am
Just a dumb question, did you by any chance update the statistics of the tables after you moved to SQL 2005? I read somewhere that you have to run the statistics update after you move to 2005 from 2000. Please ignore this comment if I am totally of the target.
-Roy
May 28, 2009 at 10:36 am
I suspect you're seeing parameter sniffing (do a search on that term for possible solutions). I'm saying this based on the fact that you see this: '[woon_ptc]=[@P1]' in the first execution plan and the second has [woon_ptc] equal to some value.
SQL Server 2000 & 2005 will certainly come up with different execution plans based on the same queries.
I'm not sure if the cursor method call has anything to do with it or not.
"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 28, 2009 at 3:13 pm
I omitted al unnecessary fields from the query (to make it as clear as possible). So it's not just adding one field to the index to make it index covered.
Parameter sniffing only involves stored procedures (as far as I know).
All statistics have been updated.
Grant Fritchey (5/28/2009)
I suspect you're seeing parameter sniffing (do a search on that term for possible solutions). I'm saying this based on the fact that you see this: '[woon_ptc]=[@P1]' in the first execution plan and the second has [woon_ptc] equal to some value.
I was just to lazy to declare the parameter and set it to the value, but the query plan for that variant is exactly the same.
May 28, 2009 at 4:16 pm
What you could be seeing is two different cached plans based upon different connection options. I would verify that the connection options in SSMS match the connection options from your application.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 29, 2009 at 12:57 am
I have tried that too and that doesn't make any difference...
May 29, 2009 at 2:10 am
I know it may not help but please could you post the two graphical execution plans ?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
May 29, 2009 at 5:03 am
Both graphical query plans attached...
May 29, 2009 at 5:33 am
I do hate doing this, but those are both estimated plans. Can you post the actual plans?
As to why you're getting a seek or scan, if you look at the pop-up lists, the two operations are showing two different sets of outputs. Each one has the same leading edge on the index, pers_id. I think the two radically different operations, an insert into a clustered index, which is what the cursor is doing, and the straight select statement, are looking at slightly different statistics, both based on that leading edge of the index, but arriving at different plans. As to what is happening, directly, at a low level within the engine... who's to say, but that leading edge on the indexes could be the cause of the confusion, especially if the other columns are being referenced in both queries.
"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 29, 2009 at 5:47 am
Actual plans are the same.
May 29, 2009 at 6:34 am
So the estimated & actual rows are the same? Actual number of executions, rebinds, rewinds... There is more data to it than just the operators. Sorry, just trying to understand what's going on in an attempt at helping.
"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
June 4, 2009 at 3:48 am
We have been able to track down the problem. In the current Uniface version (8.4.06 patch D607) we use, there seems to be a problem with one of the MSSQL driver settings (keysetcursor on) that should force the cursor to be keysetdriven. But (as can be seen from my original posted query) instead of keysetdriven it now uses the forward-only mode of the cursor (@p5 = 4, is forward-only, is the @scrollopt input for the sp_cursorprepexec).
Reading the bugfix list of Uniface we noticed that there was a change in keysetcursor behaviour from patch D606 to D607. So we tested the application to this previous version and all query plans changed to the expected ones and all performance problems were gone.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply