September 24, 2007 at 4:23 am
Anyone tried this?
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
September 24, 2007 at 7:39 am
I have not, although I HAVE used force parameterization on other non-system databases. Given the typical activities that go on in tempdb it is difficult to construct a scenario where one would want to do this. Can I ask WHY you are considering it?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 24, 2007 at 9:12 am
I have hundreds of queries in cache against temporary tables that look as if they should be parameterised.
That said I have hundreds within the user databases but my best efforts to force parameterisation have failed - been using plan guides = only they don't parameterise.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
September 24, 2007 at 10:08 am
Are your auto-parameterizations failing?? There are some perf-mon counters you can check to validate this. In my experience and training, forced parameterization is useful only in limited scenarios and can lead (as you probably found) to unintended consequences. One client of mine has a lot of really old/bad ADO code and we got 30-40% increase in throughput with FP on since the code was so 'sequential' in nature.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 25, 2007 at 10:13 am
yeah as far as I can see the plan guide doesn't work correctly. It's only a simple query too e.g. select xxx where col=value.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
September 25, 2007 at 1:08 pm
If you have widely disparate spread of data you will often wind up with a bad execution plan in cache. Say you have a million rows and all but a handful of values in the where clause return 3 or fewer rows, but some of the values return 5-20% of the rows in the table. Depending on which value gets called initially (or when the query gets flushed from cache) you can get a REALLY bad query plan. In this case you can use the OPTIMIZE FOR clause to force a nested-loop query plan expecting that the vast majority of the time you will get values that benefit from an index seek/bookmark lookup. Note that the large-spread values will run MUCH longer since they will do nested loop lookups! Another option is to put it in a sproc and use RECOMPILE option, or use the RECOMPILE option during execution.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 27, 2007 at 6:47 am
a proc isn't an option , no I tested with similar spreads to avoid that scenario, it's just I'd prefer not to have hundreds of ad-hoc queries clogging the cache! We already have a large number of plans which force recompiles ( don't ask please ) but excessive recompiles create other problems in the server, surprise surpise. I will have to do some more tests and/or make sure I'm actually monitoring for parameterised queries correctly, but I think I am as no plan ever shows a use >1 unless I query with the exact same value.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
September 27, 2007 at 7:35 am
Well, nothing for it but to give it a try then!! Test it out and see if it improves performance, keeps it the same, or worsens it. Drop a line back here when you are done if you will so we can all benefit from your testing.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply