December 2, 2010 at 9:56 am
I had a situation today where a parameterized query rendered a different query plan than the hard coded one. Because I needed to complete this I forced it to use the correct index. I've run into this issue when working with stored procedures and have found ways around this. Namely, setting the passed variables to local variables. This problem threw me off a bit since this isn't an SP and I'm using local variables. It stilled acted like a parameter sniffing problem I've had in the past. But I would have thought that if it were an ad-hoc query, parameter sniffing would not apply. Is my thinking off on this? How can I prevent this from happening on an ad-hoc query?
Thoughts and comments are greatly appreciated.
December 2, 2010 at 12:43 pm
Check if you have overlapping auto stats. That could cause this.
-Roy
December 2, 2010 at 12:54 pm
Thanks for the prompt reply from my twitter request;) I'll check them out. I'm also thinking it's a statistics issue I just don't know why yet. I'm still a little confused why how the following queries would render a different plan.
declare @start int
declare @end int
set @start = 1
set @end = 20000
update myTable
set myColumn = 'a new value'
where id between @start and @end
and...
update myTable
set myColumn = 'a new value'
where id between 1 and 20000
Does SQL Server analyze statistics differently depending on whether it's using hard code or parameter values?
December 2, 2010 at 1:19 pm
It could take a different plan based on whether it is parametazid or not. I read that in Grant Fritcheys blog.
The overlapping auto stats is the one that makes queries behave very strangely. I had a problem like this and @sqldba provided me with a query to find these stats and I dropped them. After that it worked fine.
-Roy
December 2, 2010 at 1:22 pm
Ad hoc batches in 2000, IIRC, are inspected differently. They actually inspect the entire query batch, and it is case and white space sensitive as well. Since your batches are different, they will get different plans. Even in 2008 R2 I get two different plans for your code - one AdHoc and one Prepared. I get a new AdHoc plan if I change SET to upper case on the non-parameterized version of the update.
2008 introduced some features (which I told you about on twitter) that can help you control plan cache:
"Optimize for ad hoc workloads" setting
http://www.sqlskills.com/BLOGS/KIMBERLY/post/Procedure-cache-and-optimizing-for-adhoc-workloads.aspx
"Parameterization" setting - forced vs. simple
http://sqlserverpedia.com/wiki/Query_Processing_-_Forced_Parameterization
These won't help today, since you're using 2000, but might give some better understanding...
December 2, 2010 at 1:26 pm
Hi Aaron, thanks for those two links. pretty good info in those two. 🙂
-Roy
December 2, 2010 at 1:33 pm
Happy to help. Sadly it's not going to help the OP using 2000. 🙁
December 2, 2010 at 1:44 pm
aaron.bertrand (12/2/2010)
Happy to help. Sadly it's not going to help the OP using 2000. 🙁
We're working on migrating to 2008 so these will definitely be of help to me. It'll give me something to read tonight;)
I took a look at the statistics by running dbcc show_statistics for the primary key index and found something odd. Or it seems odd to me. The second resultset that shows all density, average length and columns has two rows. One is the primary key (which is what I would have expected to see, and a second one with the primary key and another field. In this case, it's a zipcode field which there happens to be another index on the zipcode field as well. Shouldn't there only be one record if it's a primary key?
December 2, 2010 at 2:12 pm
Assuming there are rows in the table, this should show each stat for the table:
SELECT name, *
FROM sysindexes AS i
WHERE i.id = OBJECT_ID('mytable')
AND [first] IS NULL;
How many rows do you get? What do you get when you use each in:
DBCC SHOW_STATISTICS('myTable', <name from above>);
Do any of these stats use the columns in question? Which index/stat name were you entering when you ran this command originally? What exactly was the output?
These could be the overlapping statistics that Roy mentioned earlier. Tough to guess without access to the system of course.
December 2, 2010 at 2:25 pm
Zero rows. There are 19 rows when I remove the [first] is null section.
I should also add... There are about 11M rows in the table.
December 2, 2010 at 2:33 pm
Is it possible this table has 19 indexes?
December 2, 2010 at 2:36 pm
No. Just checked and there are only 5 indexes.
December 2, 2010 at 2:38 pm
So what are the other 14 rows in sysindexes? Did you ever run the index tuning wizard against this database?
December 2, 2010 at 3:06 pm
Haven't run the index tuning wizard yet.
The other 14 rows appear to be statistics for ad-hoc queries. I say that because the columns that are used are not something that our web app would be using. Would be safe to delete the statistics for those columns to see what happens? I have a test environment where I'm able to reproduce the problem.
December 3, 2010 at 9:06 am
Aaron -- I tried the DTA and it recommended that I create another index. I tried it and it worked. I forget how useful the DTA is. It's not something that I use very often but I will probably start from now on:) What still has me perplexed is how my where clause, 'where ID between 1 and 2500000' and 'where ID between @start and @end' resulted in different query plans when they got evaluated. Select section stayed the same.
Thanks to Roy as well for the help. This was also a great lesson in understanding the underpinnings of SQL Server. Thanks again!
J.D.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply