January 3, 2006 at 11:18 am
I am interested to know of people's real world experience of the performance of SQL 2005 compared to SQL 2000. I have seen figures mentioned such as "up to 3 times faster" but is that really case?
We have an application that is written entirely in stored procedures that does intensive calculations on tables that can have several million rows. They can take several hours to run. SQL 2005 does not offer any new features that will specifically help us, but we would upgrade if we could get a noticeable improvement in performance.
We will at some point evaluate the performance of 2005 ourselves, but as we are a small company, freeing up somebody to do this is proving difficult as we heavily are under-resourced at the moment. If people who's opinions I trust (i.e. the people who post to these forums) can say that they are getting noticeable performance improvement, then I will make sure our evaluation gets higher priority.
Any comments for or against will be welcomed!
January 4, 2006 at 2:10 am
Performance is improved but unless you have optimised your current code to it's limits then you are unlikely to see much benefit in simply upgrading.
Performance is almost always improved by optimising what you have.
January 4, 2006 at 2:20 am
We have a constant program of optimisation and re-working of the code to improve performance, but any extra speed increace we can get is would be welcomed.
January 4, 2006 at 3:37 am
more than likely your performance boost will come if you are running into locking issues where the new snapshot isolation issue may be of assistance.
also large tables and indexes can be partitioned, as well as using the online index rebuilds (if downtime is a difficult thing toi arrange)
these are all features that should improve performance in one way or another without upgradeing your application code or database.
if performance is of the utmost concern then you could always look at your existing 2000 installation and try and reconfigure the drives to reduce the bottlenecks
microsoft suggest
c: mirrored for operating system
d: page file
e: raid 1+0 (mirrored and striped) for data
f: mirrored for transaction logs
you could also add additional array
g: tempdb
h: index files (raid 1+0 - make it as fast as the data drive)
h: text images (for out of row data such as ntext,text,binary etc)
MVDBA
January 4, 2006 at 3:54 am
Enabling snapshot isolation and partitioning tables is something that does not require upgrading (e.g. altering, as I understand the context to mean) databases or app code?? Those both require extensive testing and planning to do right, and for snapshot isolation specifically it would be an almost certain recipe for disaster to simply enable it on a database without doing anything else. Tempdb use would probably kill the server before long.
Just by upgrading there should not be a lot of improvement. Sure, there are a lot of things done under the hood that might give some boost in places, things like dynamic scheduling on multi-CPU systems for instance, but those are not likely to do a lot by themselves. Nowhere near 3x times performance.
Upgrading to SQL Server 2005 does offer a lot of new possibilities though. In this case it might very well be possible to gain a good performance increase by rewriting the stored procedures in managed code using the CLR integration feature, but that would of course need careful testing and planning and a lot of work to do.
January 4, 2006 at 8:01 am
Mike & Chris,
Thanks for these comments, you have both given me some new areas to research. We are not afraid to do a fair amount of re-writing of the code, certainly we will be setting aside at least 3 months to do any upgrades or conversions that we eventually decide on.
January 4, 2006 at 8:10 am
Chris,
my apologies - i didn't word it very clearly
what i actually meant was that range partitioning and snapsot isolation "should" in theory be transparent to your application code and database design
initiating range prtitioning "shouldn't" require any input from your application
although maybe i forgot to mention checking your server for table scans and indexing correctly might be a first port of call to improve performance.
MVDBA
January 4, 2006 at 10:15 am
Mike, ok with that I agree. And in this case partitioning might actually be of help to the performance. It will take some work, like I said, but as you say it should be transparent to the app.
January 4, 2006 at 10:16 am
SQL 2005 manages memory more flexibly. If your app has a lot of stored procs it may enlarge the procedure cache (at the expense of buffer pool) to keep more execution plans in memory. I can't translate this to a % performance improvement, it is highly dependent on your hardware and your application. I wouldn't expect this to be a big factor unless profiling of your existing system shows a lot of procedure compiles and an unstressed buffer pool (higher than average cache page lifetime).
January 4, 2006 at 10:30 pm
We started investigating the new SQL2005 Snapshot isolation level and so far are encouraged by the results.
From experience on 2000 and 2005 we found:
1 - Indexes need a lot of attention and a robust maintenance plan, especially Manaually updating the Statistics for heavily updated tables. SQL 2000 does not do a good job updating the stats under heavy write operations even though it is setup to autmatically update those stats. Keep investigating the index performance.
2 - Keep looking at your appliations for improvments. We use an IBM utility called DataJoiner to update SQL 2000 with data replicated from a mainframe and we used to suffer on weekly basis some locks. After upgrading to the newer IBM version we immediatley improved the locking situation by 90%!
3 - SQL 2005 Snapshot is a critical for heavily updated databases. You have to use it but with caution. Make sure the server is well equipped for it especailly tempdb ...
January 5, 2006 at 1:43 am
maybe if "out for justice" could post us some info about his setup we could be a little more specific about what may be the easiest way to squeeze some performance out of it - especially in 2005
perhaps - drive configurations? amount of ram, database size? sql edition? is it an OLTP system or data warehouse? etc etc etc
MVDBA
January 5, 2006 at 4:19 am
Thanks for all your coments so far, your help is invaluable.
Here is a bit more information abour our system:
We have a dual processor win2k server with 2gb of ram. Drives C and D are both separate striped mirrored raid arrays of 300gb. We are running the Sql server 2000 enterprise edition, fully patched.
All system databases, including tempdb reside on drive C. On drive D there are 2 databases which are shared by all clients and then a single database per client with client specific data in. The shared databases are a market data database which is about 20gb in size and a standing data db which is less than 1gb. The client databases vary between 2 & 10 gb, depending on the amount of client data submitted.
The system is an OLTP process which runs a series of stored procedures, some of which use temporary tables, which initially takes data from the market data, standing data and client databases to produce a series of results, which are stored in the client database. These results are then aggregated and rolled up into another set of tables in the same database, using WITH CUBE statements where appropriate.
January 5, 2006 at 4:39 am
just a though here, (and please feel free to contradict me)
it might be a quick and easy performance upgrade to ensure your t-log files and data files are on seperate arrays.
also since you're using temp tables then moving tempdb to it's own array may give you a good boost
disk I/o is the biggest bottleneck for most SQL servers and maybe these 2 changes could give you more of a boost than an upgrade to sql 2005
at the cost of say 6 disks (2 for a mirrored tempdb and 4 for a raid 1+0 t-log drive) this might appeal more
MVDBA
January 5, 2006 at 5:22 am
Thanks for your thoughts Mike, I had been wondering if moving the databases around might give us a boost, certainly I have found that having tempdb on a different array to the client databases has helped in the past.
January 5, 2006 at 10:09 am
SQL Enterprise edition with only 2GB of RAM? You should at least be at 4GB, more if your hardware & OS support it. You should have the log files on separate drives from the data files, tempdb isolated on another drive, and everything (including system dbs) off of drive C: which probably has your virtual memory paging file. Hardware upgrades like this will undoubtedly give you a noticable performance improvement, probably much more than you would see from an upgrade to 2005.
How do you forsee upgrading to 2005? If you have one mission-critical server and plan to upgrade in place, you could have a disaster. It is possible to have a failure in the middle of the upgrade process that leaves both SQL 2000 and 2005 inoperable. I would suggest waiting until you can get a new server for SQL 2005, and keep the SQL 2000 server around as a backup.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply