THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Buck Woody

Carpe Datum!

After the Upgrade, it runs differently…

I got a question yesterday in the mail that I thought I would just answer here in a broad context. While I can’t troubleshoot or do performance tuning from a distance, there are some interesting concepts and suggestions this e-mail brings up:

“I have recently seen a change from SQL Server from 2005 to 2008 in where it handles CASE statements differently. Previously we saw a tremendous improvement in performance by using CASE statements instead of OR statements. However when one of our client upgraded to 2008 they began to notice unusually long runtimes with a few of these queries (orders of magnitude larger runtimes). Swapping it to an OR statement allows it to run in 0.075 seconds... so my question to you is do you know of any changes to the exectution engine that would account for this and what is your recommendation?”

It’s a great question. Basically it boils down to “I changed versions, now something acts differently.” Before we talk about what might be the issue, let’s talk about some things you should do after you upgrade from one version of SQL Server to another.

First, open the database properties and change the compatibility level to 10.0, unless you know you shouldn’t. Next, update your statistics – all of them. Third, ensure you have all of the proper service packs applied to the operating system and SQL Server. And finally, check the code you have for deprecated statements, or for places where you could optimize the code to use new statements or formats. There are other steps to follow, but these basics will help.

Now, with all of that done, let’s move on to things that work differently. First, you need to find out what the  code is doing – and the primary way to do that is to examine the Query Execution Plan. There are a lot of resources to teach you how to do that, but the general idea is that you turn that plan on (in the Query menu), run the query, look at the graphical plan and check three items: the overall plan, the icons that show the highest percentage of use, and the thickest arrows. Evaluating this on the “before” system and the “after” system, and that will show you what changed.

Maybe.

The point is, the query might take exactly the same path, but a different component may show stress because you might have a different box or configuration. Perhaps the drive layouts changed (or should), you have more memory (or better access to it) and so on. In that case, you simply follow standard performance tuning methodologies to locate what’s waiting, and what is showing pressure.

Now to the question at hand – does SQL Server (any version) handle a CASE statement differently than an OR statement? Well, once again, the execution plan will show you that answer, but the CASE statement is used for a different purpose than an OR statement – without having all of the code it’s difficult to say which to use in a given situation. The best thing to do is to evaluate the documentation on each and decide which fits the situation best.

CASE: http://msdn.microsoft.com/en-us/library/ms181765.aspx

OR: http://msdn.microsoft.com/en-us/library/ms188361.aspx

Did we make changes to CASE or OR? Not directly – but each change to the product may have orthogonal implications, which is where I point you back to the steps I mentioned for the “after upgrade” process.

Published Tuesday, December 01, 2009 9:06 AM by BuckWoody

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Chris Wood said:

Buck,

If possible shouldn't you re-index the tables. That should always update the statistics but if you cannot re-index should the update statistics be a fullscan?

Thanks

Chris

Great to have you blogging here now.

December 1, 2009 1:11 PM
 

Alex K said:

Buck,

Which tools would you recommend to automate the following task: "check the code you have for deprecated statements, or for places where you could optimize the code to use new statements or formats".

December 1, 2009 1:50 PM
 

BuckWoody said:

Use the Profiler Trace template of "Deprecated Features".

December 1, 2009 2:07 PM
 

BuckWoody said:

You should not have to rebuild indexes - they are just tables in the database and that came up with the restore or the attach.

December 1, 2009 2:08 PM
 

ALZDBA said:

strictly technical, you shouldn't have to rebuild indexes after restore or attach.

However, that may be the optimal occasion (planned downtime) to perform the index rebuilds and start you engine with optimal organized objects.

What we do after every restore/attach:

-- checkdb with content-re-eveluation

   DBCC CHECKDB (0) WITH ALL_ERRORMSGS, DATA_PURITY;

Whenever possible:

Exec sp_dbcmptlevel .... to the current engine level

-- rebuild all indexes

exec sp_dba_Rebuild_Reorganize

-- update usage

DBCC UPDATEUSAGE (0) with count_rows ;

-- update statistics

exec sp_updatestats

-- sync sqlusers and/or windows accounts

exec sp_change_users_login @Action = 'Report' ...

-- set dbowner to sa (unless demanded other)

exec sp_changedbowner 'sa', 'true'

-- last but not least create a new full db backup

BACKUP DATABASE ...

print 'Now ... monitor closely for the next timeframe !!'

December 2, 2009 6:53 AM
 

Zack Jones said:

"Next, update your statistics – all of them"

Just curious - how does one know when all of the statistics have been updated? We have some databases that were upgraded from 2000 to 2005 (we're behind the times) and since upgrading some queries are running slower. I thought I did everything I should after upgrading but I'm wondering if I missed something.

December 7, 2009 3:26 PM

Leave a Comment

(required) 
(required) 
Submit

About BuckWoody

http://buckwoody.com/BResume.html

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement