Bizarre slow query problem (again)

[wizofaus@hotmail.com]

I previously posted about a problem where it seemed that changing the
case of the word "BY" in a SELECT query was causing it to run much much
faster.

Now I've hit the same thing again, where basically almost any change I
make to how the query is executed (so that it still performs the same
function) causes the performance to jump from a dismal 7 or 8 seconds
to instantaneous. It's a very simple query of the form:

SELECT Min(MyValue) FROM MyTable WHERE MyKey = @0

which was running fine until a moment ago, when it suddently started
running hopelessly slowly. If change anything in the query to
lowercase (or the Min to uppercase), it runs fine again. Last time
someone suggested something about a bad plan being cached, and after a
bit of research I found the commands DBCC DROPCLEANBUFFER S and DBCC
FREEPROCCACHE. Sure enough, after running these, the query started
running fine again. The question is

a) why is this happening? Is it a bug in my code, or in SQL server?
b) is it worth detecting it and fixing it automatically? I.e, should I
put some code in that notices that a query is running far too slowly,
then runs "DBCC DROPCLEANBUFFER S; DBCC FREEPROCCACHE"? Or will that
cause other problems?

Thanks

[Uri Dimant]

Re: Bizarre slow query problem (again)

Hi

http://blogs.msdn.com/khen1234/archive/2005/06/02/424228.aspx

<wizofaus@hotma il.comwrote in message
news:1168494297 .719888.324130@ 77g2000hsv.goog legroups.com...

>I previously posted about a problem where it seemed that changing the
case of the word "BY" in a SELECT query was causing it to run much much
faster.
>
Now I've hit the same thing again, where basically almost any change I
make to how the query is executed (so that it still performs the same
function) causes the performance to jump from a dismal 7 or 8 seconds
to instantaneous. It's a very simple query of the form:
>
SELECT Min(MyValue) FROM MyTable WHERE MyKey = @0
>
which was running fine until a moment ago, when it suddently started
running hopelessly slowly. If change anything in the query to
lowercase (or the Min to uppercase), it runs fine again. Last time
someone suggested something about a bad plan being cached, and after a
bit of research I found the commands DBCC DROPCLEANBUFFER S and DBCC
FREEPROCCACHE. Sure enough, after running these, the query started
running fine again. The question is
>
a) why is this happening? Is it a bug in my code, or in SQL server?
b) is it worth detecting it and fixing it automatically? I.e, should I
put some code in that notices that a query is running far too slowly,
then runs "DBCC DROPCLEANBUFFER S; DBCC FREEPROCCACHE"? Or will that
cause other problems?
>
Thanks
>

[wizofaus@hotmail.com]

Re: Bizarre slow query problem (again)

Uri Dimant wrote:

Hi

http://blogs.msdn.com/khen1234/archive/2005/06/02/424228.aspx

>

Thanks for that...amazingl y enough it turned that that was exactly my
problem, although I'm using ad-hoc queries rather than stored procs. I
did some more testing, and it turned out that it was because it was
executing the same query twice, the first time with an atypical
parameter value, and the second time with a more typical one, that the
query was running so slowly. That is, executing

SELECT Min(MyValue) FROM MyTable WHERE MyKey = @0, with @0 = 999
followed by
SELECT Min(MyValue) FROM MyTable WHERE MyKey = @0, with @0 = 123

Caused the second query to run absurdly slowly, because in the first
case only very few rows in the table had MyKey = 999 whereas almost
every row had MyKey = 123. After doing a DBCC FREEPROCCACHE and
swapping the queries around, they both ran fine.

In the end I ended up de-parameterizing the query just for this case,
but now I'm worried - how can I be sure that my other queries won't
suffer from the same problem? Should I never use parameters because of
this possibility?

[Uri Dimant]

Re: Bizarre slow query problem (again)

In the end I ended up de-parameterizing the query just for this case,

but now I'm worried - how can I be sure that my other queries won't
suffer from the same problem? Should I never use parameters because of
this possibility?
>

An ability using parameters is very powerful , don't afraid using parameters
, just test it carefuly




<wizofaus@hotma il.comwrote in message
news:1168509843 .760497.149900@ i56g2000hsf.goo glegroups.com.. .

Uri Dimant wrote:

>Hi
>http://blogs.msdn.com/khen1234/archi...02/424228.aspx
>>

Thanks for that...amazingl y enough it turned that that was exactly my
problem, although I'm using ad-hoc queries rather than stored procs. I
did some more testing, and it turned out that it was because it was
executing the same query twice, the first time with an atypical
parameter value, and the second time with a more typical one, that the
query was running so slowly. That is, executing
>
SELECT Min(MyValue) FROM MyTable WHERE MyKey = @0, with @0 = 999
followed by
SELECT Min(MyValue) FROM MyTable WHERE MyKey = @0, with @0 = 123
>
Caused the second query to run absurdly slowly, because in the first
case only very few rows in the table had MyKey = 999 whereas almost
every row had MyKey = 123. After doing a DBCC FREEPROCCACHE and
swapping the queries around, they both ran fine.
>
In the end I ended up de-parameterizing the query just for this case,
but now I'm worried - how can I be sure that my other queries won't
suffer from the same problem? Should I never use parameters because of
this possibility?
>

[wizofaus@hotmail.com]

Re: Bizarre slow query problem (again)


Uri Dimant wrote:

In the end I ended up de-parameterizing the query just for this case,
but now I'm worried - how can I be sure that my other queries won't
suffer from the same problem? Should I never use parameters because of
this possibility?

An ability using parameters is very powerful , don't afraid using parameters
, just test it carefuly
>

Sure, except that the content of the database is out of my control -
this particular scenario (where nearly all the records matched a
particular key, but the query was first run against a different key)
could easily arrise in a production environment. More to the point,
I've seen no evidence that I'm getting any performance benefits from
using parameterized queries.
I suspect I will at least add a configuration option to avoid
parameterized queries (relatively straightforward , as I have a layer of
code that handles query parameters) if I see a problem like this again.

[Bob Barrows [MVP]]

Re: Bizarre slow query problem (again)

wizofaus@hotmai l.com wrote:

Uri Dimant wrote:

>>In the end I ended up de-parameterizing the query just for this
>>case,

"de-parameterizing" ? You mean changing to dynamic sql and leaving yourself
vulnerable to sql injection??

>>but now I'm worried - how can I be sure that my other queries won't
>>suffer from the same problem? Should I never use parameters
>>because of this possibility?
>>>

>An ability using parameters is very powerful , don't afraid using
>parameters , just test it carefuly
>>

Sure, except that the content of the database is out of my control -
this particular scenario (where nearly all the records matched a
particular key, but the query was first run against a different key)
could easily arrise in a production environment. More to the point,
I've seen no evidence that I'm getting any performance benefits from
using parameterized queries.
I suspect I will at least add a configuration option to avoid
parameterized queries (relatively straightforward , as I have a layer
of
code that handles query parameters) if I see a problem like this
again.

This is a ridiculous overreaction. Problems due to parameter-sniffing are
too rare to justify eliminating the benefits of using parameters. Talk about
"throwing the baby out with te bath water".

The article showed two, no three, ways to alleviate the problems caused by
parameter sniffing and still use parameters. So what do you do? ignore the
article's advice and "de-parameterize" your query...

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Stephen Howe]

Re: Bizarre slow query problem (again)

a) why is this happening? Is it a bug in my code, or in SQL server?

b) is it worth detecting it and fixing it automatically? I.e, should I
put some code in that notices that a query is running far too slowly,
then runs "DBCC DROPCLEANBUFFER S; DBCC FREEPROCCACHE"? Or will that
cause other problems?

Pardon me but

Why did you not say which version of SQL Server you are running on?
And why not mention the version of MDAC you have installed (since you posted
to microsoft.publi c.data.ado)?
And why not mention something about the Primary Keys & Indexes of the table
you are querying - I take it you have an index on MyKey?
And have you checked the documentation, whitepapers, MSDN for details on
query performance?
And have you checked the query plan in Query Analyser?

Cheers

Stephen Howe

[wizofaus@hotmail.com]

Re: Bizarre slow query problem (again)


Bob Barrows [MVP] wrote:

wizofaus@hotmai l.com wrote:

Uri Dimant wrote:

>In the end I ended up de-parameterizing the query just for this
>case,

>
"de-parameterizing" ? You mean changing to dynamic sql and leaving yourself
vulnerable to sql injection??

No, because the parameter values are fully under my control - they are
not submitted directly by the user. At any rate, in this case it's
always just a simple integer.

>

>but now I'm worried - how can I be sure that my other queries won't
>suffer from the same problem? Should I never use parameters
>because of this possibility?
>>
An ability using parameters is very powerful , don't afraid using
parameters , just test it carefuly
>

Sure, except that the content of the database is out of my control -
this particular scenario (where nearly all the records matched a
particular key, but the query was first run against a different key)
could easily arrise in a production environment. More to the point,
I've seen no evidence that I'm getting any performance benefits from
using parameterized queries.
I suspect I will at least add a configuration option to avoid
parameterized queries (relatively straightforward , as I have a layer
of
code that handles query parameters) if I see a problem like this
again.

>
This is a ridiculous overreaction. Problems due to parameter-sniffing are
too rare to justify eliminating the benefits of using parameters. Talk about
"throwing the baby out with te bath water".

Well, yes, but as I said, the testing I've done has revealed that my
app definitely is suffering badly from parameter-sniffing problems
(I've come across yet another one since), and that the only reliable
way I've found to solve it is to NOT use parameters, which doesn't seem
to be adversely affecting performance.

>
The article showed two, no three, ways to alleviate the problems caused by
parameter sniffing and still use parameters. So what do you do? ignore the
article's advice and "de-parameterize" your query...

Yes, but they use stored procs. I'm trying to avoid stored procs in
order to keep RDBMS independence (although for the time being, we've no
immediate need to support other databases).

[Alex Kuznetsov]

Re: Bizarre slow query problem (again)


wizof...@hotmai l.com wrote:

>
Yes, but they use stored procs. I'm trying to avoid stored procs in
order to keep RDBMS independence (although for the time being, we've no
immediate need to support other databases).

I would not do that unless there is a very strong business reason to do
so.
IMO maintaining RDBMS independence is like living in an RV instead of
living in a house - you get less comfort for your money, you pay a high
price for your mobility.
Achieving true RDBMS independence is both complex and expensive.
Details here

http://www.devx.com/dbzone/Article/32852

Under most circumstances I would settle down and use SQL Server
proprietary features to get the biggest bang for my buck. Should a need
arise to move - I would move and settle down again.

-----------------------
Alex Kuznetsov

http://sqlserver-tips.blogspot.com/

http://sqlserver-puzzles.blogspot.com/

[Roy Harvey]

Re: Bizarre slow query problem (again)

On 11 Jan 2007 08:35:45 -0800, "Alex Kuznetsov"
<AK_TIREDOFSPAM @hotmail.COMwro te:

>IMO maintaining RDBMS independence is like living in an RV instead of
>living in a house - you get less comfort for your money, you pay a high
>price for your mobility.

I like it!

Roy Harvey
Beacon Falls, CT

[Gert-Jan Strik]

Re: Bizarre slow query problem (again)

IMO maintaining RDBMS independence is like living in an RV instead of

living in a house - you get less comfort for your money, you pay a high
price for your mobility.
Achieving true RDBMS independence is both complex and expensive.

Hahaha, now that is picture that will never make it in any of Joe
Celko's books :-)

Gert-Jan

[swaroop.atre@gmail.com]

Re: Bizarre slow query problem (again)

be EXTREMELY wary when using DBCC DROPCLEANBUFFER S and DBCC
FREEPROCCACHE on a production machine. They clear all cached SPs,
queries and plans. The instance is bound to run under extreme stress
for a considerable amount of time.





wizof...@hotmai l.com wrote:

I previously posted about a problem where it seemed that changing the
case of the word "BY" in a SELECT query was causing it to run much much
faster.
>
Now I've hit the same thing again, where basically almost any change I
make to how the query is executed (so that it still performs the same
function) causes the performance to jump from a dismal 7 or 8 seconds
to instantaneous. It's a very simple query of the form:
>
SELECT Min(MyValue) FROM MyTable WHERE MyKey = @0
>
which was running fine until a moment ago, when it suddently started
running hopelessly slowly. If change anything in the query to
lowercase (or the Min to uppercase), it runs fine again. Last time
someone suggested something about a bad plan being cached, and after a
bit of research I found the commands DBCC DROPCLEANBUFFER S and DBCC
FREEPROCCACHE. Sure enough, after running these, the query started
running fine again. The question is
>
a) why is this happening? Is it a bug in my code, or in SQL server?
b) is it worth detecting it and fixing it automatically? I.e, should I
put some code in that notices that a query is running far too slowly,
then runs "DBCC DROPCLEANBUFFER S; DBCC FREEPROCCACHE"? Or will that
cause other problems?
>
Thanks

[wizofaus@hotmail.com]

Re: Bizarre slow query problem (again)


swaroop.a...@gm ail.com wrote:

be EXTREMELY wary when using DBCC DROPCLEANBUFFER S and DBCC
FREEPROCCACHE on a production machine. They clear all cached SPs,
queries and plans. The instance is bound to run under extreme stress
for a considerable amount of time.
>

The instance? You mean it affects all databases?
In this case, I determined I'd have to do it before every single query
call, so obviously that's not practical.

[Erland Sommarskog]

Re: Bizarre slow query problem (again)

(wizofaus@hotma il.com) writes:

Sure, except that the content of the database is out of my control -
this particular scenario (where nearly all the records matched a
particular key, but the query was first run against a different key)
could easily arrise in a production environment. More to the point,
I've seen no evidence that I'm getting any performance benefits from
using parameterized queries.
I suspect I will at least add a configuration option to avoid
parameterized queries (relatively straightforward , as I have a layer of
code that handles query parameters) if I see a problem like this again.

Which version of SQL Server are you using? Here is a test that you can
try to see that you can do to actually test the benefit of
parameterised queries. First create this database:

CREATE DATABASE many_sps
go
USE many_sps
go
DECLARE @sql nvarchar(4000),
@x int
SELECT @x = 1000
WHILE @x 0
BEGIN
SELECT @sql = 'CREATE PROCEDURE abc_' + ltrim(str(@x)) +
'_sp @orderid int AS
SELECT O.OrderID, O.OrderDate, O.CustomerID, C.CompanyName,
Prodcnt = OD.cnt, Totalsum = OD.total
FROM Northwind..Orde rs O
JOIN Northwind..Cust omers C ON O.CustomerID = C.CustomerID
JOIN (SELECT OrderID, cnt = COUNT(*), total = SUM(Quantity * UnitPrice)
FROM Northwind..[Order Details]
GROUP BY OrderID) AS OD ON OD.OrderID = O.OrderID
WHERE O.OrderID = @orderid'
EXEC(@sql)
SELECT @x = @x - 1
END

(Don't worry if you don't have Northwind on your server, you are not going
to run these procedures.)

Then use F7 to get the Summary page, and navigate to the Stored Procedures
node for many_sps. Select some 200 procedures, right-click and select
Script As Create To New Query Window. Go for a cup of coffee - this will
take some time depending on your hardware.

When the operation has completed (or you have gotten tired of waiting
and killed SSMS), issue this command:

ALTER DATABASE db SET PARAMETERIZATIO N FORCED

Redo the scripting operation. It will now complete in five seconds.

The reason for this is that SQL Server Management Studio does not use
parameterised queries. For every procedure it scripts, Mgmt Studio
issues around five queries. All these queries makes it to the
cache that explodes, and all these queries are compiled.

When you set a database to forced parameterisatio n, SQL Server will
auto-parameterise all statements (with some exceptions documented in
Books Online); normally it only auto-parameterise very simple queries.
In the case of Mgmt Studio it's reallly a go-faster switch.

So dismissing caching of parameterised queries can be a serious mistake.
But it is certainly true that there are situations where parameter
sniffing can be a problem. If it is possible for you to tell in the
appliocation "this is an odd value that needs a special plan", then
you can modify the query text by adding a redudant condition like
"AND 1 = 1". Actually as you have found, changing "BY" to "by" or even
adding extra spaces help. This is because the lookup in the cache
is done on a hash without first collapsing spaces or parsing the
query text.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Erland Sommarskog]

Re: Bizarre slow query problem (again)

Erland Sommarskog (esquel@sommars kog.se) writes:

When the operation has completed (or you have gotten tired of waiting
and killed SSMS), issue this command:
>
ALTER DATABASE db SET PARAMETERIZATIO N FORCED
>
Redo the scripting operation. It will now complete in five seconds.

By the way, this is something important for your application as well.
Say that a DBA finds out that your app is thrashing the cache by not
using parameterised queries, and sets the database to forced
parameterisatio n, you will get back the behaviour you have now.

A bettery remedy is to add OPTION (RECOMPILE) at the end of sensitive
queries. This forces a statement recompile, and the query will not be
put in cache. This means that you can still use parameterised queries
and get the other benefits of it. (Protection for SQL injection and
repsecting the user's regional settings.) You also avoid thrashing
the cache.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx