This thread looks to be a little on the old side and therefore may no longer be relevant. Please see if there is a newer thread on the subject and ensure you're using the most recent build of any software if your question regards a particular product.
This thread has been locked and is no longer accepting new posts, if you have a question regarding this topic please email us at support@mindscape.co.nz
|
Hi guys,
one of my queries is suffering pretty badly from parameter sniffing (sql server 2008). (ie a query that with the correct plan runs in a few milliseconds, is running in around 30 seconds - checking the actual plan used shows it is reusing a plan generated for a different set of parameters). The user is waiting on this query to return so it is pretty important
The issue appears to be the use of sp_executesql - which is great if you want you query plan cached, but is not so good if the parameters sent in can dramatically change the result set. Ideally I would like to be able to (optionally) execute the generated sql with the parameters in place (ie in a string) - an option on the unit of work (as opposed to the context) would be great as this allows for more specific selection of queries.
Alternatively any work-arounds would be appreciated - my underlying system is generic enough to make restructruring around specific cases difficult. I'm about to try executing "dbcc freeproccache" before each statement - which will hopefully hold the fort, but is far from ideal (and from your perspective not db independant - which is in part why i suggested the "string" solution above - at least it should work across all db's).
cheers, Ben
|
|
|
|
|
Hi Ben, Have you looked at making the offending queries stored proc's and then applying the suggested approach for dealing with sniffed parameters (e.g. as described in http://blogs.msdn.com/b/queryoptteam/archive/2006/03/31/565991.aspx)
Jeremy |
|
|
|
|
Hey Jeremy, thanks for the response. At the time I thought that stored procedures also (always) suffered from parameter sniffing - after you post a little more research showed that using local variables within the proc will give the desired results (as an aside, I'm a little confused by the provided link - since as far as I know SQL Server will still sniff procedural parameters - you need to make them local to get a 'general' plan - as explained here: http://www.sqlpointers.com/2006/11/parameter-sniffing-stored-procedures.html ). In the end the solution for my particular situation was actually to remove an index, leaving the optimiser really only 1 way to go, which was optimal for the big result sets, and still acceptable for the smaller ones. That said I still think my feature suggestion would be useful - I would be nice to be able to solve these issues within Lightspeed, rather than needing to custom code a stored proc
cheers, Ben |
|
|
|
|
Agreed - Ill pop an item on our backlog to have a look at adding a query option which would generate the local variables and associated assignment which seems to be the most "common" workaround people are using for this.
Jeremy |
|
|
|
