February 18, 2009 at 11:42 am
I would be curious if you looked at the statistics io if they are the same on both machines. They should be but my thinking is that perhaps the new server is doing some Physical reads that the old machine was not doing, which could certainly cause some extra time to complete.
I am also curious if the new server has more than one SQL instance running? If so perhaps you are not able to use all of the memory that is allocated to the machine and that could also cause an issue with performance if the old server was dedicated.
It may also be worth looking into the type of disk drives that the database is sitting on and number of them as that can affect performance in terms of the amount of IO that you can do during a period of time. You mention you were not on raid on one environment but were on another. Which one was which (old or new) and what is the RAID configured to be? If the new one is on a RAID (and I am guessing on a SAN) perhaps the SAN itself is not configured to handle requests correctly (perhaps it has Write Cache set really high and Read Cache set really low).
The issue screams hardware, but anything is possible.
February 18, 2009 at 12:05 pm
skt5000 (2/18/2009)
Unfortunately, still at 30 secsJim
And are you still seeing parallelism in the execution plan?
There has to be something going on, something different. If you simply air drop a database onto a new piece of hardware and everything else is exactly the same, you shouldn't see a radical decrease in performance. So, there's a difference somewhere that is negatively impacting you. Have you checked the server settings between both machines, not just the database settings? Are the log & data files on the same drive? What drives were they on in the old system? Something is up. Has to be.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 18, 2009 at 12:05 pm
noeld (2/18/2009)
Did you update the stats "WITH FULLSCAN, ALL" ?
Yes, I did.
February 18, 2009 at 12:16 pm
Mike (2/18/2009)
I would be curious if you looked at the statistics io if they are the same on both machines. They should be but my thinking is that perhaps the new server is doing some Physical reads that the old machine was not doing, which could certainly cause some extra time to complete.I am also curious if the new server has more than one SQL instance running? If so perhaps you are not able to use all of the memory that is allocated to the machine and that could also cause an issue with performance if the old server was dedicated.
It may also be worth looking into the type of disk drives that the database is sitting on and number of them as that can affect performance in terms of the amount of IO that you can do during a period of time. You mention you were not on raid on one environment but were on another. Which one was which (old or new) and what is the RAID configured to be? If the new one is on a RAID (and I am guessing on a SAN) perhaps the SAN itself is not configured to handle requests correctly (perhaps it has Write Cache set really high and Read Cache set really low).
The issue screams hardware, but anything is possible.
Okay, this is getting way over my head, I'm just a simple caveman programmer.:D
I don't have alter trace permissions on the old server so can't run profiler. The old server, as noted above, is a "monster" -- quad processor on a RAID (don't know which RAID), and the new one is not on a RAID at all. I'm just surprised that I'm the only one on the new box -- I have no competitors for resources -- and still a simple query takes so long. It worries me what about what will happen when I start using it for reports.
What question(s) should I ask the admin?
Jim
February 18, 2009 at 12:30 pm
The old box (the monster) I would definitely want to know what the RAID configuration is set to for the drives that the SQL Server resides on and exactly how many drives were in that configuration. The fact that the old box was on RAID and the new is not is definitely something that can have a HEAVY impact on performance.
As an example, on our production system (roughly 150 users, 300 GB DB) we recently did a revamping of our SAN configuration to add more drives and to configure them appropriately based on the sort of IO's that were being done for the specific file types, and without moving to a new server, no index additions, no changes of any other kind other than having more drives under the DB we essentially gained 30% performance improvement across the board (rough numbers). We needed that additional IO that adding more spindles to our configuration brought. You have now potentially lost (by moving off of the RAID) that and it could be hurting you. If you went from your DB sitting on 10 Mirrored drives to a single drive, you could be losing 2X-5X the IO throughput potentially. This has nothing to do with the total amount of disk space on the drives, but the amount of IO that each of the disks is able to handle.
February 26, 2009 at 11:34 am
You could run Perf Mon to identify whether disk IO is an issue.
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply