Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Unique Index Causing Slow Read Performance Expand / Collapse
Author
Message
Posted Friday, September 04, 2009 8:28 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:47 AM
Points: 41,525, Visits: 34,442
TheSQLGuru (9/4/2009)
1) it doesn't have to be a bug if the optimizer for 2000 derives a suboptimal query plan for a complex query involving 15 tables in a mishmash of views.


Agreed. The optimiser's job is not to find the best plan. Never has been. It's job is to find a good plan fast and it's only allowed a certain amount of time to find said plan. If it doesn't identify a 'good enough' plan by the time the time limit is up, it'll return with the best plan found up til that point. Which can be very bad indeed.

2) Gail is correct in that it can often truly benefit the optimizer to subdivide large queries such as this into smaller interim steps using temp tables (NOT table variables) because each step along the way can then have it's own set of statistics that prevents small skews from becoming large query performance problems.


Also because the optimiser gets to work on several smaller queries rather than one massive great big one. The search-space for the plans is smaller (much smaller) and the chance that it'll find a good enough plan is a lot better.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #782963
Posted Friday, September 04, 2009 6:42 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 7:44 AM
Points: 76, Visits: 379
Thanks, again, for your replies.

Yes, I was worried that I might be swapping one set of fast and slow plans for a new, different, set of fast and slow plans on the the basis of one which just happenend to be fast in SQL2005.
Tha main reason I converted to SQL2005 was to try to give you the more 'user friendly' set of plans, but clearly I can't do that.

Gail, I am still interested in what is going on here, specifically that the single change in an index from unique to nonunique can flip the query plan on its head.
I'm also interested in how you analyse the plan for this. I suspect there is not any way to 'nudge' the query plan back, but if you worked one out I would be most impressed. This is probably about as complex as it gets, so if it can be fixed, I'd guess just about anything could be. That would be a trick worth knowing.
But obviously, not urgent.

If you feel it's a better use of time, I could give you my skype ID and do a verbal rundown rather than try and type it all.

Re the recent comments (TheSQLGuru), I understand that this is not strictly a 'bug'.
The point is that the optimiser CAN derive a good plan, and even does derive a good plan for the tables and the views (ie. indexing is adequate, it's not a 'bad design' issue), but change this one index and it all goes down the gurgler for no apparent reason.
We can't expect that the optimiser will _always_ find a good plan, but when it doesn't it would be great to be able to feed it some hints to nudge it in the right direction.
But it seems that the hints are exactly that - only hints which may be ignored - and what I'm trying to find out is whether there is some way to do this more reliably rather than hint and pray, ie: am I missing something ?
I have tried the hints which would be logical here with no success.

Is there any way of giving the optimiser a bit more time to work out its plan ?

I realise the temp tables are a viable workaround (and one I have used in places), but this is for reporting and it means rather than simply constructing a query for a report I have to deal with instantiating and dropping possibly several temp tables with dynamic names (since I have multiple sessions and each name needs to have the sessionid added to the end).
It raises the complexity of things greatly and given I have about 200 reports in the database, you can understand what this means in terms of time.

Now, if only I could get that pesky query plan to work ...

Ben
Post #783225
Posted Friday, September 04, 2009 8:14 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 8:43 PM
Points: 4,128, Visits: 5,836
>>but change this one index and it all goes down the gurgler for no apparent reason.

Maybe it simply isn't apparent to you. You have changed the information and options available to the optimizer in a very complex construct. It does what it can with that information.

>>We can't expect that the optimiser will _always_ find a good plan, but when it doesn't it would be great to be able to feed it some hints to nudge it in the right direction.
But it seems that the hints are exactly that - only hints which may be ignored - and what I'm trying to find out is whether there is some way to do this more reliably rather than hint and pray, ie: am I missing something ?
I have tried the hints which would be logical here with no success.

hints are not a panacea, and again just because they may seem logical to you does not mean they will be so 'logical' to the optimizer engine. :) BTW, have you tried unwinding all of the views in your query and maing them into a single statement and trying to work from that from a 'hint' perspective?

>>Is there any way of giving the optimiser a bit more time to work out its plan ?

Not to my knowledge

>>I realise the temp tables are a viable workaround (and one I have used in places), but this is for reporting and it means rather than simply constructing a query for a report I have to deal with instantiating and dropping possibly several temp tables with dynamic names (since I have multiple sessions and each name needs to have the sessionid added to the end). It raises the complexity of things greatly and given I have about 200 reports in the database, you can understand what this means in terms of time.

Actually I don't understand this. Why do you need dynamic temp table names with the sessionid added to the end of the names?? You can execute the same code in 50 different SSMS windows or 50 different calls of the same sproc at the same time without any temp table name collisions.


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #783232
Posted Saturday, September 05, 2009 2:13 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:47 AM
Points: 41,525, Visits: 34,442
ben.mcintyre (9/4/2009)
Gail, I am still interested in what is going on here, specifically that the single change in an index from unique to nonunique can flip the query plan on its head.
I'm also interested in how you analyse the plan for this. I suspect there is not any way to 'nudge' the query plan back, but if you worked one out I would be most impressed.


Same way you'd analyse any other plan, and same fix as for any other case of poor plans - rewrite the query, maybe add hints

If you feel it's a better use of time, I could give you my skype ID and do a verbal rundown rather than try and type it all.


I don't use skype, don't have the bandwidth to support it.

But it seems that the hints are exactly that - only hints which may be ignored - and what I'm trying to find out is whether there is some way to do this more reliably rather than hint and pray, ie: am I missing something ?


In my experience, hints are directives that the optimiser has to obey. That said, with tonnes of view and extra tables, the hints will probably have to be applied in the views, not in the outer query, plus it will be very hard to work out an optimal set of hints, especially when working with just the ones available in SQL 2000.

I have to deal with instantiating and dropping possibly several temp tables with dynamic names (since I have multiple sessions and each name needs to have the sessionid added to the end).


Why? SQL gives temp tables dynamic names itself. There's no need for you to do its work for it.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #783282
Posted Tuesday, September 08, 2009 2:07 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 7:44 AM
Points: 76, Visits: 379
> Maybe it simply isn't apparent to you. You have changed the information and options available to the optimizer in a very
> complex construct. It does what it can with that information.

True, but I have changed only one thing: a unique index to a nonunique index. Now call me old fashioned, but I believe that there should be either no difference in how these are implemented for a read-only scenario, or the unique index should be more efficient, hence this shouldn't cause a significant change in the plan.
This is why I have the audacity to call it a bug.

The way I see it, the plan is big, but that's not necessarily the same as complex. Big things that can be broken down in to small simple chunks are not necessarily that complex, and the indexing and constraints are the fuel to drive the problem solving on a micro level. This should scale.
I'm kinda wanting a little more consistency and control than I'm getting here.

But I do acknowledge that I'm very much out on my own here, and I concede that you guys, being some of the most experienced around here, are clearly right. Thank you.

> In my experience, hints are directives that the optimiser has to obey. That said, with tonnes of view and extra tables, the
> hints will probably have to be applied in the views, not in the outer query, plus it will be very hard to work out an optimal
> set of hints, especially when working with just the ones available in SQL 2000.

Totally agree - low likelihood a hint which is good for one query will be good for the next. I tried applying a hint to an inner view just as an experiment and it didn't work. I felt relieved once I'd got it out of there.

> have you tried unwinding all of the views in your query and maing them into a single statement a

This is a solution, but we are wandering OT a bit - lets get back to the point of the OP.

I badly need my views here. Due to the size of the data chunks I'm dealing with I have to wrap them in views to stay sane. eg. Produce details are needed frequently, so there's a view wrapping the produce table and the associated brands, grades, classes, product type, varieties, pack types, pack weights, colours, etc (about 10 tables). If I had to deal with these individually in every query, my head would explode, and I would have to upgrade to that 26" monitor.
I'm one programmmer and this is a large database (and we haven't even touched on the actual app ...).

Similarly, with temporary tables for partial results, I generally need to make them part of a view 'upstream' to avoid horribly complicated queries.
Can't do this with temp tables (they can't be incorporated into views).

Now, I HAVE re-worked some critical reports with partial result tables etc, and it took nearly a day for about six reports, and it turned the code into a maintenance nightmare.
Then I noticed that I could dramatically alter query speeds with a few little modifications, and as I mentioned in the OP, this was found to be due to the query plan not the indexing.
But results were highly inconsistent (adding and removing seemingly inconsequential tables has an almost random effect), and I can never get back to my original query without the speed hit.

So what I'm asking in this thread is can I nudge this query plan specifically WITHOUT significant changes to the structure of any of my current SQL, views, or application.
Seems the answer is 'not reliably'.

Anyway, I'm very happy with SQL 2005's performance in this regard (so far ...), and believe this thread is pretty much wrapped up.
If you still want to compare the query plans and see if you can work out what has caused the shift, I'd be intrigued.


I would love it if I could have more control, but I suspect that it would require some sort of grammar/language for describing join behaviour far more sophisticated that what is there at present.
It is interesting though that as we inevitably move towards RAM-based database servers and work on the 'object/relational impedance mismatch', I think a well written API-interactive query parser/optimiser is going to be a major part of the solution in the long term.


Regarding the original reasons for the session-related views/tables, it took me a while to remember the reasoning - this project was coded mainly in 2001-2003.

I believe that I originally used the sessionIDs because I had SPs to do the very complex charges calculations, and these used dynamic SQL. Global cursors are needed to be visible to the dynamic SQL, which means one SP per session. I also wanted to use the temp views to feed selected data into the charge calculation SPs, and because temp tables/views aren't visible from dynamic SQL, again these need to be declared as non-temp and hence need the SessionID.

(Hey, please, I know this site is prettly much dedicated to the hunting down and elimination of dynamic SQL. I know what rbar is and who Jeff Moden is. I respect and agree with all of that. The dynamic SQL is actually quite necessary )

Add this to the fact you can't add temp tables to views and that pretty much clinched it.
Thanks for the reminder though - true temp tables could be a useful tool to throw in the mix.
Post #784040
Posted Tuesday, September 08, 2009 3:03 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:47 AM
Points: 41,525, Visits: 34,442
ben.mcintyre (9/8/2009)
I badly need my views here. Due to the size of the data chunks I'm dealing with I have to wrap them in views to stay sane. eg. Produce details are needed frequently, so there's a view wrapping the produce table and the associated brands, grades, classes, product type, varieties, pack types, pack weights, colours, etc (about 10 tables). If I had to deal with these individually in every query, my head would explode, and I would have to upgrade to that 26" monitor.


Unfortunately layers of views often tend to result in performance problems, partially from the complexity of the resulting query, partially from the chance that a lower level view will have to be materialised in its entirely, and partially from high compile times as SQL tries to trim down the unrolled query

(Hey, please, I know this site is prettly much dedicated to the hunting down and elimination of dynamic SQL. I know what rbar is and who Jeff Moden is. I respect and agree with all of that. The dynamic SQL is actually quite necessary )


No it's not. There's nothing wrong with dynamic SQL used correctly. It's cursors and while loops that we're allergic to here.
You're using global cursors?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #784062
Posted Tuesday, September 08, 2009 6:13 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 7:44 AM
Points: 76, Visits: 379
GilaMonster (9/8/2009)
ben.mcintyre (9/8/2009)
I badly need my views here. Due to the size of the data chunks I'm dealing with I have to wrap them in views to stay sane. eg. Produce details are needed frequently, so there's a view wrapping the produce table and the associated brands, grades, classes, product type, varieties, pack types, pack weights, colours, etc (about 10 tables). If I had to deal with these individually in every query, my head would explode, and I would have to upgrade to that 26" monitor.


Unfortunately layers of views often tend to result in performance problems, partially from the complexity of the resulting query, partially from the chance that a lower level view will have to be materialised in its entirely, and partially from high compile times as SQL tries to trim down the unrolled query

Yah. Bummer.

GilaMonster (9/8/2009)

(Hey, please, I know this site is prettly much dedicated to the hunting down and elimination of dynamic SQL. I know what rbar is and who Jeff Moden is. I respect and agree with all of that. The dynamic SQL is actually quite necessary )


No it's not. There's nothing wrong with dynamic SQL used correctly. It's cursors and while loops that we're allergic to here.
You're using global cursors?


(Giggle) Sorry, I meant cursors and while loops. Unfortunately, these tend to occur with Dynamic SQL for me, hence the association.
Global cursors. Disgusting, I know. In this case, I need to declare the cursor using dynamic SQL. Hence the global cursor. A local one evaporates with the EXEC statement.
I believe there is no other way (please correct me if there is !).
Don't worry, there aren't many cursors and they are local cursors where there's no dynamic SQL.
I would argue that the cursors are actually necessary where used as well, but that's another rather long piece of string.

I do love this site, but I have to 'come out' to being an application developer as well as SQL afficionado. It's hard sometimes. Sometimes I feel like an Israeli citizen from a Palestinian family. It's fine as long as I don't mention procedural code or OR/M (in the SQL forums), or query tuning and performance (in the coding forums).
Post #784172
Posted Tuesday, September 08, 2009 7:50 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 8:43 PM
Points: 4,128, Visits: 5,836
True, but I have changed only one thing: a unique index to a nonunique index. Now call me old fashioned, but I believe that there should be either no difference in how these are implemented for a read-only scenario, or the unique index should be more efficient, hence this shouldn't cause a significant change in the plan.
This is why I have the audacity to call it a bug.

The way I see it, the plan is big, but that's not necessarily the same as complex. Big things that can be broken down in to small simple chunks are not necessarily that complex, and the indexing and constraints are the fuel to drive the problem solving on a micro level. This should scale.
I'm kinda wanting a little more consistency and control than I'm getting here.


1) Nothing to do with "old fashioned" - just not understanding the optimization process in sufficient detail. Not sure why you don't think it could be a VERY important piece of information to the optimizer that a column (or set of them) can only exist zero or one times in a table though - instead of possibly umpteen kajillion times.

2) As the plan gets big, the number of permutations (and the CPU effort and duration it takes to calculate them all) grows . . . hmm, is it geometrically or exponentially? Doesn't really matter which - both are bad. More importantly though is that every piece of statistical information used to do the math in the cost-based optimizer becomes less precise and meaningful and any skews are magnified with each layer of complexity/size/join.

I would love it if I could have more control


To coin a phrase: "You can't HANDLE more control!!!" Seriously, you are complaining vociferously about how complex your queries and views are, and the amount of effort and maintenance hastle it is to tune/refactor them. Do you honestly think having to LEARN and truly UNDERSTAND the rocket-science that is the optimization engine and how to tweak it to the Nth degree will be LESS effort?? No way Hose! There are actually many things one can do to 'override' the optimizer, and there are certainly times to do so. But thankfully 98.3% of the developers (and code) out there don't NEED to do so. And when one does need it, that is what a hired-gun is for. Get someone who DOES know the engine back and forth and who's line of work is improving SQL Server query performance.

One other thing - when one DOES override the optimizer to make a particular query fly for a given input or set of inputs, there is quite often some other set of inputs that are HORRIBLY inefficient as a result of the 'tuning'. The best I have ever done on a single sproc for tuning (just a hair under 6 ORDERS OF MAGNITUDE performance improvement) was primarily the result of removing forced index hints.


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #784252
Posted Tuesday, September 08, 2009 8:12 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:59 AM
Points: 35,951, Visits: 30,239
GilaMonster (9/8/2009)
(Hey, please, I know this site is prettly much dedicated to the hunting down and elimination of dynamic SQL. I know what rbar is and who Jeff Moden is. I respect and agree with all of that. The dynamic SQL is actually quite necessary )


No it's not. There's nothing wrong with dynamic SQL used correctly. It's cursors and while loops that we're allergic to here.


You took the words right out of my mouth. Thanks, Gail.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #784276
Posted Tuesday, September 08, 2009 8:14 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:47 AM
Points: 41,525, Visits: 34,442
TheSQLGuru (9/8/2009)
[quote]
2) As the plan gets big, the number of permutations (and the CPU effort and duration it takes to calculate them all) grows . . . hmm, is it geometrically or exponentially?


I suspect it's close to n! (though I haven't bothered to check the theory to confirm). It's pretty bloody fast-growing whichever way. There's a reason why plan generation/plan modification is a major field of research both in databases and artificial intelligence.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #784280
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse