October 20, 2008 at 11:50 am
Hi everyone, I need your help to get some approaches of how to test SQL 2K5 performance against any other SQL versions. I have done a migration from SQL 7.0 to SQL 2K5. After the migration on my SQL 2K5, I re-did my indexes, and updated my stats, that improved a litte my SQL 2K5's performance. We run a series of daily processes on both servers, and surprinsingly sql server 7.0 always does this processes way faster than sql 2k5. I have been trying to change my maxdop to 2 and 1 because I noticed that I had a CXPACKET wait time high, but I have not gotten any real improvements from that side. Another thing I have noticed is that during the execution of our daily processes, I have a lot of locks on our tables, then I thought that maybe this performance issue may be caused due to sql server 2k5 row level locking, would that be right? Well I have assumed that is the performance issue, and know I would like to prove that this is 'problem' between this servers, how could I test this kinda stuff between my sqls servers?
Any thoughts and opinions will be highly appreciated. Thanks!
October 21, 2008 at 8:00 am
no ideas no one?
October 21, 2008 at 8:52 am
First of all, is the hardware the same? Or how is it different? Check drivers, especially disk drivers, as issues there can really affect performance.
Next, you have to examine plans and see if SQL Server is choosing similar ones. If so, then something in hardware has to be different. If not, rebuild indexes and look for differences. SQL 2005 almost always runs better, but it's built differently and it's possible that your code needs to be rewritten to take advantage of how 2005 works instead of 7.
Once you find some low level issues, at the statement or proc level, then post them in the Performance section and see if someone can help you tune them.
October 21, 2008 at 11:40 am
Steve Jones - Editor (10/21/2008)
First of all, is the hardware the same? Or how is it different? Check drivers, especially disk drivers, as issues there can really affect performance.Next, you have to examine plans and see if SQL Server is choosing similar ones. If so, then something in hardware has to be different. If not, rebuild indexes and look for differences. SQL 2005 almost always runs better, but it's built differently and it's possible that your code needs to be rewritten to take advantage of how 2005 works instead of 7.
Once you find some low level issues, at the statement or proc level, then post them in the Performance section and see if someone can help you tune them.
I have been testing cursors performance. In SQL 7.0 what the optimizer does is first a table scan(17%), then a sort procedure(83%), population Query/Synchronous(0%), snapshot/Read only (0%) and then the declare cursor (0%).
In 2k5, first the scan (8%), then the sorting (21%), Parallelism (Gather Streams)(7%), Segment(0%), Compute Scalar(0%), Sequence Project(0%), Clustered Index Insert (tempdb.dbo.CWT_PrimaryKey)(65%), Population Query (0%)and gets joint with a branch that starts with a Clustered Index Seek (tempdb.dbo.CWT_PrimaryKey)(0%) and a Fetch Query(0%). I don't really understand why 2K5 does all this but like Steve said I think I will have to rewrite my code. It just performs really bad in SQLServer 2005 and I dunno why. But I also will start looking into hard drive problems which probably are a problem too. My SQL Server 7.0 is running on Microsoft Windows NT 4.0, we use for this server an IBM NetFinity 6000r (rack) server with 1GB RAM, 40 GB. HD (Redundant SAN) and 2 Proccesors Pentium Xeon III 700 Mhz. Now my SQL Server 2k5 is running on Windows 2003 Server Enterprise Edition. It has one 2.33 GHz Quad-Core Intel Xeon with 4 GB RAM and 146GB of hard drive. My HD where 2k5 is installes is installed with RAID 5.
Thanks again for the help. I am still running some test and I will post any outcome I get. Thanks in advance.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply