April 30, 2007 at 6:38 am
I have just re-wrote a stored procedure that was using a datediff in the where clause so that it now does
where Stamp>@SearchStamp AND Stamp<=@SearchStamp
instead of
where datediff(day,Stamp,@SearchStamp) = 0
as the Execution Plan was showing that it was using the non-clustered indexes that are on the other columns used in the where clause
rather than the clustered index that I have set up on the Stamp column.
Sure enough when I re-ran the tests on our development box the Execution Plan now shows that its using a clustered index seek on the Stamp index.
However when I copied the changes to our production server and re-ran the tests the Execution Plan is still using a non-clustered scan on a different
index and then doing a Key lookup.
I did run DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE before running the tests. So can someone tell me why the two servers are using different
execution plans when the set up is the same. I would have thought that it would always want to use the clustered index seek if possible so why
the difference?
Thanks for any help.
April 30, 2007 at 8:51 am
Is the amount of data and it's distribution the same between dev & production. Do you have the latest statistics in both environments? Do you have an index maintenance plan in both environments to selectively defrag the indexes? It didn't say, but did you use the same parameters between the two environments? Any of these that are radically different can lead to different query plans being generated.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 30, 2007 at 9:42 am
I used exactly the same parameters on both Dev / Production.
The amount of data within each DB is drastically different as its a user log table that holds hundreds of thousands of rows per day so there is only a few thousand rows at most on the dev box.
I only created these tables on dev/production the other day. Running DBCC SHOW_STATISTICS shows that both tables have up to date stats
From comparing the DBCC SHOWCONTIG on dev and production
scan denisity extent frag logical frag page density
dev 66.6
% 66.6 22.2% 94.8%
prod 96.4
% 9.74 1.0% 94.1%
The clustered index has a fill factor of 100% as its just used for searching.
April 30, 2007 at 10:56 am
You really need to have a read up on indexes as your basic assumptions are missplaced.
The optimiser will choose the best plan, that doesn't mean you know better < grin > I'd have a look at the io stats and or profile it to see the actual differences.
The fact your data is different will be the reason the plan is chosen differently, you need to examine the query plans and io to see why. A clustered index is not always the best index, however that's another discussion.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
April 30, 2007 at 12:19 pm
The differences in the data are most likely going to cause different query plans. I suspect that's the most likely issue.
One thing you said that concerns me a bit. You've created your clustered index on a table that is getting thousands of inserts a day, but made the fragementation zero because it's going to be used only for reads? It's still getting updated with each insert and you could be see a lot of thrash on the system from it reorganizing to ensure that it's at 100%. Also, since each index contains a pointer to the clustered index, they also have to get reorganized as the cluster gets reorganized... something to think about.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 30, 2007 at 5:07 pm
Nope, sorry I was wrong when I said that.
I have the main log table which accepts inserts througout the day. I think the fill factor is 60% (off top of my head can't check as im not at work now). This log table only holds a days worth of records. Its used for searching as well as inserts. I then have a history table which has exactly the same struture, indexes as the main log table and its this table that has a fill factor of 100% as its just used for reporting. I have a nightly job that drops all the indexes on the table, transfers all the days records from the main log table and then rebuilds the indexes with a fill factor of 100%. Sorry for the confusion.
Anyway as for my misconception about indexing / query plans. I am sure that I read an article on this site some time ago that said it was always best practise to make the WHERE clause predicates SARGABLE and gave examples how to do this with date columns eg change the variable with a function and not the column. It said the goal was to try and remove scans and lookups and get the plan to use clustered index seeks. As I did manage this by changing the datediff to a >= AND <= and then seeing that the plan did in fact use a clustered index seek rather than the scan/lookup it was previously doing I thought that it must have done some good. So maybe the article was wrong and its not always preferable to have clustered index seeks over scans and bookmark lookups I don't know now!
May 1, 2007 at 5:17 am
No, you and the article are correct. Seeks are better than scans. Eliminating, as much as you can, bookmark lookups is the way to go. The issue isn't whether or not you did the right thing. You did. The issue is that you're testing against an environment that will result in different plans than production.
If you're rebuilding the index every night, fragementation shouldn't be a concern, at all. Nor, I believe, should outdated statistics. That's your production box. Now you need to make the dev/qa/perf environment behave the same way with, as near as you can, the same amount & type of data. Eliminating differences between the environments is going to be the key. However, even with a different set of data, you should be able to get similar plans between these environments if the same structures exist in both environments and they're both on the same service pack & hotfix, etc. Double (at this point, probably triple or quadruple) check that.
Also, just as an aside, did you try using BETWEEN? It effectively does an AND query and it's a little easier to mantain.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 3, 2007 at 2:20 am
It's still getting updated with each insert and you could be see a lot of thrash on the system from it reorganizing to ensure that it's at 100%.
Fill factor is not maintained by inserts. It's only used at the creation/rebuilt time of the index.
That said, if the cluster is build with 100% fill, unless it's on a very well chosed field, you're going to get tonnes of page splits, which are not good for insert performance.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 3, 2007 at 8:20 am
Thanks for the correction. 100% accurate. Apologies for the bit of bad info.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 3, 2007 at 8:37 am
As I said in my previous post the table with 100% fill factor does not recieve any inserts/updates throughout the day.
Once a night I drop all the indexes, insert all the days data from the log table into this history table and then rebuild the indexes with a fillfactor of 100%. As the history table is only used for searching I was under the impression that this would be ok.
The log table that recieves inserts throughout the day and is used for searching has a fill factor set to 60%.
May 4, 2007 at 12:17 am
Ah, sorry fot the misread. Yeah, that's fine.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 31, 2007 at 7:12 am
i'm troubleshooting a similar issue. We have an app that usually uses a subscriber for R/O access. Few weeks ago we failed it over to the publisher while we worked on replication problems. friday we got complaints and saw a very bad execution plan on this huge query. it touches close to 20 tables in 3 databases with lots of joins.
On the subscriber execution plan was fine. all index seeks. on the publisher mostly scans including an index scan on a 29 million row table.
rebuilt an index on the big table and it worked OK for the weekend and yesterday back to index scans. last night rebuilt two indexes on the big table and for now it's working OK. I did a drop and recreate with online = off.
yesterday i checked the query on the subscriber and a few QA servers and it ran with index seeks everywhere else. tonight i might start dropping and recreating PK's since that is where most of the joins are on.
August 1, 2007 at 6:48 am
You could always force the issue with an index hint. However, if the optimizer is choosing an different index, there is most likely a reason why. (it may still be wrong, but there is a reason). You also have the ability to use the exact xml plan if you really want to ensure it runs the same way in dev.
You might want to try the query with and without the hints using the profiler, or just server statistics and verify which way REALLY performs better. All optimizers work basically by taking a guess as to what they think every way possible will take of the system resources. What ever "estimates" to the lowest number is chosen. Since your dev and prod systems are so different. Garbage in = Garbage out. Comes to mind (I am not talking about your data, but the statistics in dev are pretty useless)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply