We moved a large database & application from a single server with RAID
drives to a clustered server with a SAN and are not seeing any performance
gains. Statistics were updated, procs recompiled, etc. after the move. The
CPU load is lower (as seen in perfmon), and the SAN disks are also not
working nearly as hard as the RAID was, but the application shows no speed
improvement. Any thoughts on where to look next?
thx.
k
Hi
Generally, moving to a SAN will not improve performance, but might decrease
it due to the higher IO latency.
Disk layout is important, make sure your transaction logs are on a seperate
volume, with RAID-1 or RAID 10. If possible, don't put your data on RAID-5,
but RAID-10.
Run profiler to find your slowest queries and see if you can not optimize
them with indexing.
Regards
Mike
"wasnotwas" wrote:
> We moved a large database & application from a single server with RAID
> drives to a clustered server with a SAN and are not seeing any performance
> gains. Statistics were updated, procs recompiled, etc. after the move. The
> CPU load is lower (as seen in perfmon), and the SAN disks are also not
> working nearly as hard as the RAID was, but the application shows no speed
> improvement. Any thoughts on where to look next?
> thx.
> k
>
>
Showing posts with label moved. Show all posts
Showing posts with label moved. Show all posts
Monday, March 26, 2012
Tuesday, March 20, 2012
Performance problem on copy of db
I have a performance problem here I could really do with some help on.
I am running SQL Server 7 on Win2k on a Dual Xeon box.
I moved the database from a much older machine and had some initial problems
running a particular complex query because of parallelism. Essentially,
doing an sp_updatestats and using only 1 of the 2 CPUs for parallel
execution of queries solved these problems. Clearly, this was an issue with
the more powerful processors on the new box.
Anyhow, once the performance problem was solved I made a copy of the
database using Backup/Restore and then changes were made to the data in the
new database copy (essentially, the application that uses the db was rolled
out to a different group of users).
All was well when I was last involved in this project back in October.
Recently, however, the same performance problems reappeared following a
reboot. I managed today to solve the performance problems on the original
database by running sp_updatestats.
However, running sp_updatestats on the second database did not solve the
performance problems. I've tried making another copy of the well-performing
database and the copy performs fine. Not sure what I was trying to prove by
doing this, but I was running out of ideas!
I also did a copy of the badly performing db using backup and restore and,
surprise, surprise, this made no difference.
Now that several months have passed, the copy I made contains different data
to the original so I can't simply overwrite it.
Apart from running sp_updatestats, I'm at a bit of a loss in terms of what
else to try. To give you some idea of the magnitude of the problem, the
query in question takes 30 seconds to run on the "good" database and 30
minutes to run on the "bad" one.
Looking at the execution plan, SQL Server does it completely differently.
The good db involves lots of "nested loops/inner join" processes. The bad
db involves lots of "has match/inner join" processes and these, I think, are
what slow things down. The good db has a "subtree cost" of 3.43 whereas the
bad one has something like 250.
Any ideas appreciated.Paul Welsh wrote:
sounds like you could have different indexes on the two databases.
you should also read up on fragmentation (dbcc showcontig, dbreindex,
dbindexdefrag, and defragging heaps).
I am running SQL Server 7 on Win2k on a Dual Xeon box.
I moved the database from a much older machine and had some initial problems
running a particular complex query because of parallelism. Essentially,
doing an sp_updatestats and using only 1 of the 2 CPUs for parallel
execution of queries solved these problems. Clearly, this was an issue with
the more powerful processors on the new box.
Anyhow, once the performance problem was solved I made a copy of the
database using Backup/Restore and then changes were made to the data in the
new database copy (essentially, the application that uses the db was rolled
out to a different group of users).
All was well when I was last involved in this project back in October.
Recently, however, the same performance problems reappeared following a
reboot. I managed today to solve the performance problems on the original
database by running sp_updatestats.
However, running sp_updatestats on the second database did not solve the
performance problems. I've tried making another copy of the well-performing
database and the copy performs fine. Not sure what I was trying to prove by
doing this, but I was running out of ideas!
I also did a copy of the badly performing db using backup and restore and,
surprise, surprise, this made no difference.
Now that several months have passed, the copy I made contains different data
to the original so I can't simply overwrite it.
Apart from running sp_updatestats, I'm at a bit of a loss in terms of what
else to try. To give you some idea of the magnitude of the problem, the
query in question takes 30 seconds to run on the "good" database and 30
minutes to run on the "bad" one.
Looking at the execution plan, SQL Server does it completely differently.
The good db involves lots of "nested loops/inner join" processes. The bad
db involves lots of "has match/inner join" processes and these, I think, are
what slow things down. The good db has a "subtree cost" of 3.43 whereas the
bad one has something like 250.
Any ideas appreciated.Paul Welsh wrote:
quote:
> Looking at the execution plan, SQL Server does it completely differently.
> The good db involves lots of "nested loops/inner join" processes. The bad
> db involves lots of "has match/inner join" processes and these, I think, a
re
> what slow things down. The good db has a "subtree cost" of 3.43 whereas t
he
> bad one has something like 250.
sounds like you could have different indexes on the two databases.
you should also read up on fragmentation (dbcc showcontig, dbreindex,
dbindexdefrag, and defragging heaps).
Performance problem on copy of db
I have a performance problem here I could really do with some help on.
I am running SQL Server 7 on Win2k on a Dual Xeon box.
I moved the database from a much older machine and had some initial problems
running a particular complex query because of parallelism. Essentially,
doing an sp_updatestats and using only 1 of the 2 CPUs for parallel
execution of queries solved these problems. Clearly, this was an issue with
the more powerful processors on the new box.
Anyhow, once the performance problem was solved I made a copy of the
database using Backup/Restore and then changes were made to the data in the
new database copy (essentially, the application that uses the db was rolled
out to a different group of users).
All was well when I was last involved in this project back in October.
Recently, however, the same performance problems reappeared following a
reboot. I managed today to solve the performance problems on the original
database by running sp_updatestats.
However, running sp_updatestats on the second database did not solve the
performance problems. I've tried making another copy of the well-performing
database and the copy performs fine. Not sure what I was trying to prove by
doing this, but I was running out of ideas!
I also did a copy of the badly performing db using backup and restore and,
surprise, surprise, this made no difference.
Now that several months have passed, the copy I made contains different data
to the original so I can't simply overwrite it.
Apart from running sp_updatestats, I'm at a bit of a loss in terms of what
else to try. To give you some idea of the magnitude of the problem, the
query in question takes 30 seconds to run on the "good" database and 30
minutes to run on the "bad" one.
Looking at the execution plan, SQL Server does it completely differently.
The good db involves lots of "nested loops/inner join" processes. The bad
db involves lots of "has match/inner join" processes and these, I think, are
what slow things down. The good db has a "subtree cost" of 3.43 whereas the
bad one has something like 250.
Any ideas appreciated.Paul Welsh wrote:
> Looking at the execution plan, SQL Server does it completely differently.
> The good db involves lots of "nested loops/inner join" processes. The bad
> db involves lots of "has match/inner join" processes and these, I think, are
> what slow things down. The good db has a "subtree cost" of 3.43 whereas the
> bad one has something like 250.
sounds like you could have different indexes on the two databases.
you should also read up on fragmentation (dbcc showcontig, dbreindex,
dbindexdefrag, and defragging heaps).
I am running SQL Server 7 on Win2k on a Dual Xeon box.
I moved the database from a much older machine and had some initial problems
running a particular complex query because of parallelism. Essentially,
doing an sp_updatestats and using only 1 of the 2 CPUs for parallel
execution of queries solved these problems. Clearly, this was an issue with
the more powerful processors on the new box.
Anyhow, once the performance problem was solved I made a copy of the
database using Backup/Restore and then changes were made to the data in the
new database copy (essentially, the application that uses the db was rolled
out to a different group of users).
All was well when I was last involved in this project back in October.
Recently, however, the same performance problems reappeared following a
reboot. I managed today to solve the performance problems on the original
database by running sp_updatestats.
However, running sp_updatestats on the second database did not solve the
performance problems. I've tried making another copy of the well-performing
database and the copy performs fine. Not sure what I was trying to prove by
doing this, but I was running out of ideas!
I also did a copy of the badly performing db using backup and restore and,
surprise, surprise, this made no difference.
Now that several months have passed, the copy I made contains different data
to the original so I can't simply overwrite it.
Apart from running sp_updatestats, I'm at a bit of a loss in terms of what
else to try. To give you some idea of the magnitude of the problem, the
query in question takes 30 seconds to run on the "good" database and 30
minutes to run on the "bad" one.
Looking at the execution plan, SQL Server does it completely differently.
The good db involves lots of "nested loops/inner join" processes. The bad
db involves lots of "has match/inner join" processes and these, I think, are
what slow things down. The good db has a "subtree cost" of 3.43 whereas the
bad one has something like 250.
Any ideas appreciated.Paul Welsh wrote:
> Looking at the execution plan, SQL Server does it completely differently.
> The good db involves lots of "nested loops/inner join" processes. The bad
> db involves lots of "has match/inner join" processes and these, I think, are
> what slow things down. The good db has a "subtree cost" of 3.43 whereas the
> bad one has something like 250.
sounds like you could have different indexes on the two databases.
you should also read up on fragmentation (dbcc showcontig, dbreindex,
dbindexdefrag, and defragging heaps).
Saturday, February 25, 2012
Performance Monitors with SAN
We have recently moved our database to a SAN and I am trying to monitor performance. Does anyone know of a good way to monitor performance going to the SAN? I have been watching things like %Disk time and Avg Disk Queue Length, but does anyone know if these are acurrate when referring to a SAN? My %Disk Time is going up to numbers as high as 1200. The Disk queue length seems kind of low for seeing that high disk time as well.
Any help would be great.
Thanks much.I a junior DBA here and the senior dba in charge of our sql cluster monitors disk write bytes/sec, disk read bytes/sec, and average disk queue length among others things and I'm pretty sure our stuff's on a SAN. He says anything approaching 1 or above for queue length is cause for alarm. I think read & write throughput are specific to the hardware.
Wish I could give you a better answer...|||So what the performance monitor is telling you is that your disk is writing twelve times as fast as it can (not real likely using conventional hardware, but it happens frequently using a SAN). That would tend to lead to really short disk queue lengths.
I'd suggest that you talk to your SAN vendor. They should be able to give you some really good ideas on what you can monitor, especially within the SAN drivers themselves (if you are using SAN specific drivers).
You obviously have to adjust any counters delivered with the OS, since they are tuned for a very different world than a SAN. A good first guess is to scale the stock values by about a factor of 20, but you'll probably have to adjust that once you get a better feel for how your particular configuration performs.
-PatP|||We already did ask the SAN vender and they told us to purchase this software package they created to monitor performance. Personally I don't think that is necessary, so I was hoping I could monitor performance through these counters. I just don't know how they should be adjusted for the SAN.
Any help would be great.
Thanks much.I a junior DBA here and the senior dba in charge of our sql cluster monitors disk write bytes/sec, disk read bytes/sec, and average disk queue length among others things and I'm pretty sure our stuff's on a SAN. He says anything approaching 1 or above for queue length is cause for alarm. I think read & write throughput are specific to the hardware.
Wish I could give you a better answer...|||So what the performance monitor is telling you is that your disk is writing twelve times as fast as it can (not real likely using conventional hardware, but it happens frequently using a SAN). That would tend to lead to really short disk queue lengths.
I'd suggest that you talk to your SAN vendor. They should be able to give you some really good ideas on what you can monitor, especially within the SAN drivers themselves (if you are using SAN specific drivers).
You obviously have to adjust any counters delivered with the OS, since they are tuned for a very different world than a SAN. A good first guess is to scale the stock values by about a factor of 20, but you'll probably have to adjust that once you get a better feel for how your particular configuration performs.
-PatP|||We already did ask the SAN vender and they told us to purchase this software package they created to monitor performance. Personally I don't think that is necessary, so I was hoping I could monitor performance through these counters. I just don't know how they should be adjusted for the SAN.
Subscribe to:
Posts (Atom)