|
|
We recently upgraded from SQL Server 2000 to SQL 2005. We simultaneously did a serious upgrade of the hardware. The old server was a dual processor 32 bit machine. The new server is quad processor 64 bit machine. The dual core PLUS hyperthreading makes it appear as 16 virtual processors to the operating sytem:
Yet despite throwing tremendous amounts of hardware for SQL Server to use, we experienced terrible performance after upgrading to 2005. Getting the performance to the level where it was acceptable took literally weeks of work. Detailed knowledge of SQL Server internals was gathered by porting over release notes, MSDN articles, personal blogs written by Microsoft SQL Server developers, 3rd party website and the help of several different people in Microsoft support. This blog entry was created so that anyone else going through the same process and experiencing similar problems would have a road map to follow:
---------------------------------------------------------
1) INSTALL Service Pack 1. Now that's it available...this is a "no brainer". You will save yourself weeks of aggravation that those of us unfortunate who migrated pre-SP1 experienced. From the enahancement list:
"Several performance problems were fixed in the Database Engine query optimizer. These changes improve the upgrade process for third-party products that use SQL Server as their back-end database when you upgrade from SQL Server 2000 to SQL Server 2005."
There are also some very necessary fixes such as:
a) pre SP1, the database maintainence tasks for deleting .TRN and .BAK files didn't offer the ability to delete recursively on subfolders (as it used to in 2000). So it was a step backwards, and without it, sites with more than 1 database have to create a manual step for EVERY database added. This added alot of unnecessary administrative overhead. This oversight was restored/fixed in SP1.
b) DTA bomb outs: The Database Tuning Advisor would bomb out quite easily, and we were forced to do thing like loading workloads a small piece at a time and seperating runs for tables from indexed views and limiting the time it could run. Thankfully, this has been fixed.
2) Migration:
- MANUALLY Update Statistics. http://msdn2.microsoft.com/en-us/library/ms144267(SQL.90).aspx
sp_MSForEachTable 'Update Statistics ? WITH FULLSCAN'
Yes, your database is probably set for "auto update statistics" and the English meaning of "auto" means it's a substitude having to do something manually. But just becuase the feature is named that way, doesn't mean that it acts that way. For the first few hours after migration, we thought we might be able to get away without updating stats. So as a test I ran the same query thousands of times. But the "auto" never seemed to kick in. Maybe the "auto" was a Yugo because the performance was dismal. So I took the hit and ran stats (it takes about 13-15 hours on our database due to the size...even with all of our hardware)..and what a difference it made. Throughput sped up on the query about 3x.
Why is this necessary? The engine for 2005 is so different than it was back in 2000 that the 2000 stats are of little use. So this should be the first thing you do after upgrading to SP1...even if it increases your down time...it is worth it!
- Rebuild your indices (optional...but highly recommended):
Microsoft advertises SQL Server's advantage over Oracle as the fact that it is self-tuning. This is "compatively" true, if you compare it to Oracle (which seems like a prima-donna compared to SQL Server). But it's not "absolutely" true.
Some people are not aware that things like indices degrade with every INSERT/UPDATE and fragment. This is just like disk fragmentation...it slows down your server more and more over time until performance becomes unacceptable. So since your database is down for the migration, it's also a good time to rebuild them. Yes you can do a reorganization/defrag without taking it online, but this process is not as thorough and can't remove all types of fragmentation.
But what about "online rebuilding of indices" in 2005...you don't really need to take it offline to build it anymore do you? Well, the answer to that you can see in another of my blog postings. Like just say that real world databases are almost guaranteed to have a large # of tables that online rebuilding doesn't yet handle (maybe in SQL Server 2010)? Anyway, my advice is that since you're already offline to do the migration, do this offline too and get a fresh start with your indexes.
- Update your usage stats.
DBCC UPDATEUSAGE
This is a little trick that is on MSDN. It avoids having your statistics messed up and performance suffering becuase of it..
"In earlier versions of SQL Server, the values for the table and index row counts and page counts can become incorrect. To correct any invalid row or page counts, we recommend that you run DBCC UPDATEUSAGE on all databases following upgrade."
http://msdn2.microsoft.com/en-us/library/ms144267(SQL.90).aspx
- Update the compatibility level.
I know you're thinking "Wait a minute. I went through all the work and stress of upgrading this thing to 2005 and it's up and running, and you're telling me it isn't truly 2005 compatible yet"? The answer is "Believe it or not...yes".
It actually makes sense (although it would be nice to know about this in the upgrade wizard, rather than having to fish it out indirectly). But your upgraded database is set to be 2000 compatible, and won't support many of the cool new 2005 features such as Management views, etc. So you need to upgrade it:
sp_dbcmptLevel <dbname>,90
(where 90=sql 2005)
A word of warning...Microsoft doesn't upgrade the compatibility for a reason. It's becuase query behavior changes in 2005 on some things. So check out the 2005 documentation on the differences, update your apps for any changes (you've probably already done this before upgrading), and then you're ready to do the above.
3) Operating System settings
- If you're running a quad processor machine and installed Windows 2003 x64 Standard Edition (even with SP1), then the operating system supports your 4 processors fully, right? WRONG!
If this is your situation, you need to buy an upgrade license to R2 right away. Most people think that R2 is an excuse by Microsoft to charge you for a bunch of stuff that knowledgeable people can already download for free off of the internet. That is true for a majority of the features.
However, there is a legitimate reason to upgrade to R2 that makes it worth paying for, that you can't get via a free download. Pre R2 supports 4 processors...but not SYMETRIC processing. What this means is that if you have a workload, pre R2 will fill up your first cpu until it's bursting at the seams at 100% (leaving 2 through 4 compeltely idle). Only then will it go to #2 (leaving 3 and 4 idle). Then when it's bursting at the seams, it will go to #3 and so on. This is extremely inefficient. It's like buying a high performance race car, and driving it with a manual transmission and wondering why it fells like your Honda Accord. And since SQL Server accesses the processors through the OS (it doesn't have the ability to access hardware directly)...your SQL Server is doing the same thing.
R2 on the other hand supports symetric processing. What this means is that the workload will be distributed evenly on all 4 processors, keeping them smoothly running.
So if you have 4 processors, UPGRADE TODAY.
Incidentally, this was hard won information. It took two seperately opened incidents and talking to 4 different Microsoft people before I was directed to a very helpful person in the OS team who knew and explained the difference and the consequences.
4) Hardware
- The cool new hyperthreading on your expensive new box is advertised by Intel as increasing your performance. And that "could" be true. It also could be true that it is making your system run like a dog. Here are some examples of the latter:
http://blogs.msdn.com/slavao/archive/2005/11/12/492119.aspx
The only way to know for sure, is to test your server under load with it on and off and see the difference. For us, hyperthreading works...it gave us a 10% boost:
https://www.rentacoder.com/CS/blogs/real_life_it/archive/2006/04/18/SQL_Server_2005_performance_testing_with_hyperthreading_and_MAX_DOP.aspx
But if you're having performance problems, you need to check this out for yourself.
- Adjust MAXDOP (Max Degree of parrellism) for your # of processors.
If you're upgrading from an older non-hyperthreaded/non-dual core machine to one that is...this is important for you.
MAXDOP controls the maximum # of "processors" that SQL Server uses in a parallel query. The "processors" are actually virtual processors, because you may be like us and have 4 processors, but when you add in hypthreading (x2) and dual core (x2 again) SQL Server considers it 16 "processors".
If you set MAXDOP to a #, SQL Server will only use that # of processors in parralel queries. By deafult, SQL Server sets MAXDOP=0 which means it will be set to wahtever # of processors SQL Sever can see. On your old machine, this was a good setting, because MAXDOP= the # of physical processors (which was the old recommended practice).
However, now that your new machine has dual core and hyperthreading, that is no longer what you want. Why is this an issue? Don't you want to maximize the use of your processors? The answer is "no". That is because there is overhead in every extra processor used in a parrallel query. Add a small # and it makes the query run faster. But as you continue adding them, the overhead builds up, and eventually can cause the query to run slower than if you had just left it running on a single processor.
The old/traditional advice was to set MAXDOP=# of physical processors and ignore hyperthreading: http://blogs.msdn.com/sqltips/archive/2005/09/14/466387.aspx
However, nowhere in that blog or anywhere on the net could I find what to do about dual core processors. Should those be counted and added to MAXDOP or not? I ahd to call Microsoft support to get the updated answer. I was told that the new rule is that MAXDOP should be set to the # of "physical sockets". And each core is considered a physical socket. So for us we have 4 CPUs x2 (because they are dual core)=8 physical sockets. So our proper setting is MAXDOP=8.
This little jewel was hard won information...so I hope it helps you.
- Memory: Beef it up!
Beefing up your memory is always a good idea. But there is an even more pressing reason to do it if you have a multi gigabyte database.
As of this moment there is a bug/feature (even after installing post SP1) in SQL Server 2005 backups. When you do your nightly full backup and do a "verify" on it, it will drain memory while it is doing it. A Microsoft tech theorized that perhaps it was loading the entire file into memory. In our case, we have 16GB of memory and a 60GB database. So the math tells you already what happens every night. All the memory is sucked out...down to the last MB, and eventually forces SQL Server to give up memory that it has claimed (flushing caches, etc.) It takes a good 45 minutes to run, even on our high speed machine, so during this time, users are treated to timeouts and pages that take extremely long to run (if at all).
The workaround was to turn off "verify" and copy it to a 2nd system and verify it there. Unfortunately, for some reason the copy process also causes a similar drain (which Microsoft is looking at right now). So currently...both the bug/feature and the workaround cause the same problem.
So the current temporary solution is that the database has several extra GIGs of memory installed and SQL Server to set to not use it. This helps allevaite the effects of the drain to a more tolerable 10-15 minutes (rather than 45).
Long term we are waiting on a true solution.
Software
- Tune with DTA--but carefully.
Database Tuning Advisor will run on a trace of your production workloads and automatically tune the indices and stats so that it will be optimized for your situation. This is a great feature and you should be running this often.
But HOW you've run it has changed (at least for us) in 2005. If you run a 24/7 environment (such as an international web site like we do) then it will change for you as well.
In SQL Server 2000 we could run the tuning advisor on the database directly (to get recommendations only...not actually apply them. That would be done later.). We could do this at any time and it wouldn't lock out people or cause any problems other than a little more load. But that is no longer the case in 2005. We tried it 3 times, and all 3 times it started causing deadlocks and all sorts of problems. On two ocassions, just stopping DTA didn't fix the problem...and we had to physically reboot the server to release the locks. A 24/7 database can't be taken down every time you need to run DTA.
There is a new remote tuning option in DTA where you setup a 2nd SQL server and it will copy the table structure of your production database to a 2nd database and tune it from there. This not only helps prevent overloading your production server, but it stops the locking while the process is running...at least in theory. Again, real life practice showed something else.
We tried it several times during off-peak hours, and a few times we got it to work okay. However most of the time it would also produce timeouts for users while copying the tables. (We have a ticket open with MSFT on this issue).
So, the workaround is that we copy a backup to the 2nd server and then run DTA on it there, and take the recommendations and run them on production during off peak hours. It isn't ideal, because copying a multi GB file even over a GB network is slow, and it really slows down the # of iterations of DTA we can run. However it is better than not running DTA at all, so perhaps this idea will be useful to you.
--------------------------------------
Side note: IMPORTANT BAD side effect of DTA bombouts that you should know about!
DTA creates hypothetical statistics and indices on your database and then uses them to make its recomendations. When it's done it cleans them out. However if DTA bombs out (as it did often pre SP1), this can cause a problem. It means bogus/hypoetical indices and stats will be on your production database. Any query can come in and pick one of them instead of your good/real indices and stats and your performance will suffer becuase of it. Also, they will slow down your stats refreshing and index defragging jobs unnecessarily.
A MSFT tech working on another issue found turned us onto this and we found over 4000 of these dead objects in our database. (Apparently pre SQL 2000 SP4 or SP5 there was another flaw that would cause these objects to accumulate as well...and this database is old enough to have been around back then). And sure enough, some were being used by queries and producing sub-optimal plans and contributing to bad performance.
So if DTA crashes, makes sure you clean up its mess. Examples:
drop statistics [YourTable].[hind_88491494_3A_2A_8A]
drop statistics [YourTable].[hind_88491494_1A_4A_8A]
This is perhaps another reason to optimize on a 2nd machine and not the production server. Here is MSDN info on deleting hypothetical indices:
http://msdn2.microsoft.com/en-us/library/ms190172.aspx
- If you use SQL Server as the back end for a website that offers users the ability to page through data, swap out your inefficient SQL Server 2005 paging queries for the new ROW_NUMBER() feature. Doing this on the main query used on the site dropped processor utlization an eye popping 50%! Details are here:
http://rentacoder.com/CS/blogs/real_life_it/archive/2006/04/29/481.aspx
- Strongly consider "Forcing Paramaterization"
This this new 2005 feature cut CPU performance on an adhoc query load by 1/5th. It's almost too good to believe and is probably the most underhyped yet best feature in SQL Server 2005 (far better than the ubiquitously marketed "online index rebuilding" feature which don't stand up to real life situations):
Details are here:
https://www.rentacoder.com/CS/blogs/real_life_it/archive/2006/04/30/487.aspx
- 5/26/2006:
If your multi processor machine is not working as quickly as it should...strongly consider installing the just released SQL Server Cumulative hotfix package which fixes this issue:
"Processing performance on multiprocessor computers is somewhat slower than expected."
http://support.microsoft.com/kb/918222
Ian Ippolito
Anonymous comments are disabled
|
|