This is a odd problem where a bad plan was chosen again and again, but
then not.
Using the profiler, I identified an application-issued statement that
performed poorly. It took this form:
exec sp_executesql N'SELECT col1, col2 FROM t1 WHERE (t2= @Parm1)',
N'@Parm1 int', @Parm1 = 8609
t2 is a foreign key column, and is indexed.
I took the statement into query analyzer and executed it there. The
query plan showed that it was doing a scan of the primary key index,
which is clustered. That's a bad choice.
I then fiddled with it to see what would result in a good plan.
1) I changed it to hard code the query value (but with the parm
definition still in place. )
It performed well, using the correct index.
Here's how it looked.
exec sp_executesql N'SELECT cbord.cbo1013p_AZItemElement.AZEl_Intid AS
[Oid], cbord.cbo1013p_AZItemElement.incomplete_flag AS [IsIncomplete],
cbord.cbo1013p_AZItemElement.traceflag AS [IsTraceAmount],
cbord.cbo1013p_AZItemElement.standardqty AS [StandardAmount],
cbord.cbo1013p_AZItemElement.Uitem_intid AS [NutritionItemOid],
cbord.cbo1013p_AZItemElement.AZeldef_intid AS [AnalysisElementOid] FROM
cbord.cbo1013p_AZItemElement WHERE (Uitem_intid= 8609)', N'@Parm1 int',
@Parm1 = 8609
After doing this, re-executing the original form still gave bad
results.
2) I restored the use of the parm, but removed the 'exec' from the
start.
It performed well.
After that (surprise!) it also performed well in the original form.
What's going on here? 3 10347
elRoyFlynn (li*@twcny.rr.com) writes: t2 is a foreign key column, and is indexed.
I took the statement into query analyzer and executed it there. The query plan showed that it was doing a scan of the primary key index, which is clustered. That's a bad choice.
Sometimes it is, sometimes it's not. This is a delicate choice that
the optimizer have to make. Non-clustered index + bookmark lookup, or
clustered index scan? The first strategy fantastic if there are only
a few hits, but disastrous if you hit, say, 30% of the rows. Many page
will be accessed more than once, and it will be a lot slower than a CI
scan.
2) I restored the use of the parm, but removed the 'exec' from the start. It performed well.
After that (surprise!) it also performed well in the original form.
Probably parameter sniffing. SQL Server caches the query plan for the
query, and the cached plan is built from the parameter value that
query first was run for. That value may have been handled best with
a CI scan.
But it might also be that the statistics were poor initially, and caused
SQL Server to make an incorrect estimate. But SQL Server has auto-
statistics, so it could be that statistics were updated, and the plan
was flushed, and a new plan built.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
In this case, the "bad plan" clearly is: a 6-second response time vs.
a sub-second response when the "best" plan is used.
Problem is, the application generated this query in the "exec sp..."
form hundreds of times, getting the bad result each time. It was while
the app was still running that I worked in query analyzer. I executed
the problem sql multiple times, duplicating the bad result, before
trying it the other way. The first time I did it the other way, it
worked well, which also immediately fixed the application. Coincidence
, unrelated to the different execution form, that just at that moment
mss figured out that the other plan was better? I'm skeptical. I
think that something about 'exec sp_.." vs. plain "sp_..." had an
unintended effect.
But thanks for the response, I'll think about it.
elRoyFlynn (li*@twcny.rr.com) writes: In this case, the "bad plan" clearly is: a 6-second response time vs. a sub-second response when the "best" plan is used.
It should be admitted that this is quite common. The optimizer seems to
be overly conservative with regards to non-clustered indexes.
Coincidence , unrelated to the different execution form, that just at that moment mss figured out that the other plan was better? I'm skeptical. I think that something about 'exec sp_.." vs. plain "sp_..." had an unintended effect.
It could be that the misisng "exec" triggered a recompile of the query,
but from what I know about how the cache works, I can't really see that
it would matter.
What could matter, though, is whether you changed somehting inside
the query. With regards to single queries, the cache is both case-
and space-sensitive. (But the part "EXEC sp_executesql" is not in
the cache, only the first argument to sp_executesql is.)
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Evgeny Gopengauz |
last post by:
For example, I have a table FORMULA_TABLE with the column FORMULA which
contains some function y=f(x) in its symbol description like '@X*2+1'
create table FORMULA_TABLE(
ID int,
FORMULA...
|
by: Dipak Patel |
last post by:
Platform: MS SQL 7.00 - 7.00.1063 Standard Edition / Win2000
I wish to store the numeric result of a query into a variable, as
described below. This does not work, it fails on the last line with...
|
by: philipdm |
last post by:
I am building a SQL statement that returns a number.
when I execute the Built SQL statment EXEC(@Build). What I need to do
now is take that number that comes back and store it in another
variable...
|
by: LineVoltageHalogen |
last post by:
Greetings All, currentley there is a heated discussion in my place of
work over which method is better/more efficient for simple selects.
Background:
1.) Simple Application that uses sql server...
|
by: sjoshi |
last post by:
Hello
I have 2 procedures setup in master database, sp_RebuildIndexesMain and
sp_RebuildIndexesSub
The Sub just shows and execute DBCC commands for passed database
context
...
|
by: Scarab |
last post by:
Hi all,
When I use following sql to get data in stored procedure, error occurs:
insert into #tmp
EXECUTE dbo.prc_1 @date1,@date1
Here is the source code, Thanks
CREATE TABLE (
NULL ,
...
|
by: warth33 |
last post by:
Hello
I have a php site. Some page needs to call an external program. The
programs are home made c# applications.
It uses to work without problem. For a while. Maybe it work for some
hour....
|
by: eliben |
last post by:
Hello,
In a Python program I'm writing I need to dynamically generate
functions and store them in a dict. eval() can't work for me
because a function definition is a statement and not an...
|
by: Nadeem |
last post by:
Hello all,
I'm trying to write a function that will dynamically generate other
functions via exec. I then want to be able to import the file (module)
containing this function and use it in other...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |