September 28, 2002 at 5:07 pm
We are moving a production server to a new server of similar configuration - same #processors, same RAM. The new server even has more disk space. A job that generates a set of reports took < 2 hours to run in the old server. The same job (using identical databases restored onto the new server) is taking 5 hours to complete. I tried reindex and update statistics. The result stays the same. How could this be possible? What else can I do to improve the processing time?
September 29, 2002 at 4:57 am
It is pure TSQL or something more complex? Probably a good place to start is to see if you're getting identical query plans. Possibly there is something different between the servers - maybe you're getting errors in the process due to bad permissions, cant access a file share, something like that? Hows the disk io look? Disk caching enabled? Same number of drives/same speed? Same sector and stripe sizes?
Andy
September 29, 2002 at 5:09 pm
This job is triggered from a VB application and involves reading from the database to generate reports through a series of steps - opening cursors, creating temp tables, etc. A quick check on the largest query in the job indicates Query plan is the same.
You are correct that there are differences in server configuration. The old server use all 18gb drives with RAID 0+1. The new server has one logical drive that's ~200gb using 72gb drives with RAID 5 setting. This drive holds a large database that is mostly read-only. I thought RAID 5 is better, if not the same, for read than 0+1. Could this be a factor causing poor performance?
Where do I check if disk caching is enabled?
I'll do more analysis on the queries including disk IO the next day or so.
Thanks for the feedback. I love your site. It's loaded with info. The forum has been great in helping me to resolve quite a few problem.
September 29, 2002 at 8:21 pm
Pretty close. Differences could be the # of spindles? Caching is handled by the controller, so it'll either in a util provided by the manuf or in a boot up bios option.
Thanks for the feedback. We try to help - dont always have the answers, but sometimes the right question is enough to get you looking in the right place.
Andy
September 30, 2002 at 7:56 am
You can check the model of your SCSI array controller and see if it supports read/write caching. A controller with a write cache performs significantly better than one without if you are doing a lot of writing, to include temp tables.
Have you monitored how the disk(s) are performing while the job is running? You can use perfmon to watch things like Avg. Queue Length to see if the drives are the bottleneck.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply