October 14, 2011 at 5:55 pm
What kind of hdd's?(interface, speed, etc)
What is buffer cache hit ratio?
Disk queue length for each drive?
How big are db's?
Workload read or write intensive?
How many transactions/sec?
Any other io errors in event log?
Perfmon counters for mb/sec for each drive for reads and writes?
your problem is either faulty io subsystem, or under powered hw.
Don't guess about how to fix this by adding files to temp db or adjusting other random things. Measure, research and implement based off of facts.
Edit: I scrolled right past your reply with the perfmon stats (thats what i get for reading the forums on my phone). You're right, it doesn't look like IO on this server is the problem.
kb with really good mirror performance troubleshooting information:
October 14, 2011 at 10:20 pm
ketan_katkar (10/14/2011)
Hi,It's seem to be MS SQL Server's I/O operation performance issue. To imporove you MS SQL I/O performance you need to do as per below.
Kindly restructure your MS SQL DB files as per below. Considerting you have C, D & E drive respectively.
* As you told C drivde will be use for OS
* Keep your .mdf & .ldf file on D drive
Changes as per following
1 Create new .ndf file for your DB & keep it on "E" dirve.
2 Create new .ndf file for your Temp DB & keep it on "E" drive.
3 Also provide more Auto incremental space for Temp DB. With "percent %" auto incremental.
4 If there are any specific tables in which you are doing these actvity then create Clustered Index on them
& create these clustered index on "E" drive.
Note: I To find out which Proc takeing time use MS SQL Profiler.
II To find out I/O operation use PERFMON to monitor.
Use below link for more details.
http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/06/21/642314.aspx
TC,
KK
A lot of bad advice. Horrible actually. For the good of the forum:
1. a). adding a second data file won't magically make your existing data pack-up and re-distribute itself evenly over both data files. Sure, new writes will be placed proportionally across files in the file group based on free space, but if you want your data evenly split across a new ndf, you need to move it yourself. (e.g. add 2 files to a new filegroup, and rebuild indexes on that new filegroup)
b). And what is the rational that we need an additional ndf? We have two drives to use for sql server, D and E, and zero information on the workload. My first thought would be Data on D, logs on E. If that's the case, there is no performance gain for having 2 data files vs 1 on the same volume.
2. a). There are two reasons to add additional tempdb data files. One is excessive PAGELATCH waits (not PAGEIOLATCH). This is memory contention, not related to IO (Read Paul Randall's blog) and the driver behind the recommendation for adding tempdb data files based on the number of cpu cores.
b). The other reason would be a tempdb workload so heavy that you needed additional spindles to handle it (PAGEIOLATCH). With only 2 drives available and insufficient information, there is no basis to make this recommendation.
3. a) %percent growth? Really? Auto-growth settings only come into play when the files grow. If i set growth to be fixed at 1 MB or 500 GB, or 10% or 600%, that will make absolutely zero difference in performance unless the database actually grows.
b) Best practice is to pre-size your files appropriately for the next X months (where X is 3 or 6 or 12 or whatever is appropriate depending on the db). If you want a safety net, then auto-growth by a fixed amount is appropriate (maybe a couple hundred MB).
c) also, Auto-growth for t-log by % files can cause an excessive amount of virtual log files to be created, which can cause all sorts of problems. google "8 steps to transaction log throughput"
4. --first piece of advice that isn't 100% junk. Yes, appropriate indexes can reduce IO for reads. However, changing a heap to a clustered index will not reduce the network IO for mirroring. Additionally (You didn't recommend this, but I'll throw it in anyway) adding extra non-clustered indexes means more data that has to get mirrored to the secondary server, so, removing unused indexes might help as well.
October 14, 2011 at 10:46 pm
ketan_katkar (10/14/2011)
1. Create new .ndf file for your DB & keep it on "E" dirve.2 Create new .ndf file for your Temp DB & keep it on "E" drive.
Those are near certain to make the IO problem far worse, not better. The log should be separated from data for 2 reasons - performance and recoverability. Adding a second data file on the log drive is going to hinder both.
Please don't do this.
3 Also provide more Auto incremental space for Temp DB. With "percent %" auto incremental.
% growth is not recommended for any database because the growth duration is unpredictable. It's especially bad for TempDB. Please don't do this either
http://www.sqlserver-dba.com/2011/03/sql-server-files-and-filegroups-improving-database-performance.html
The advice in there is vague at best, and not good at all.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 14, 2011 at 10:52 pm
Ruan, I'll be honest, I think you probably need to get someone in for a few days on this...
The highest wait time is mirroring. That means either the network or the mirror server can't keep up with the log records, I can't tell which. Do some perfmon on the mirror and look at the physical disk counters, look at the send and redo queues and see if you can isolate where the problem is.
You do have slow IOs. (the 15 second IO messages). You need either more hardware (more spindles and probably RAID) or you need to reduce the IO load. That can be done by tuning queries or adding memory. You especially need to either add more spindles to TempDB (not by putting extra files on the log drive) and/or reduce the TempDB usage of your system.
It's hard to say more, I'd need to look at perfmon counters and other stats to be able to say more. I do recommend a book that may help you narrow things down (disclaimer, I contributed to it): http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/ It is available from Amazon.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 16, 2011 at 11:54 pm
Hi Gail
Thanks for the response.
Luckily I know that the logs and data should be seperated.
So, in conclusion, the issue is with the hdd's? I believe they are running at 7200rpm.
These are also new drives, so enquering for new drives will be out of the question.
We have spoken to the client regarding more memory. How will I be able to determine how much more memory is needed?
And also, can you please elaborate on the counters I need to be monitoring?
Thanks in advance
October 17, 2011 at 1:41 am
ruan.keyser (10/16/2011)
Luckily I know that the logs and data should be seperated.So, in conclusion, the issue is with the hdd's? I believe they are running at 7200rpm.
In other words, they're desktop drives. Running SQL databases on a single spindle (no raid) is unlikely to provide the necessary performance
These are also new drives, so enquering for new drives will be out of the question.
It's not new drives you probably need, it's more drives. You qute likely don't have the necessary throughput on single drives.
Will comment on the rest when I get home.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 17, 2011 at 1:44 am
Thank you so much.
Unfortunately, our client is not willing to pay what they need to.
That's why we have to make use with what we have
October 17, 2011 at 2:43 am
ruan.keyser (10/14/2011)
I have made the incremental percentage 10% (as default)I don't necessarily think this is an issue with the IO,
I am currently busy monitoring the following Counters in Perfmon:
Avg. Disk sec/Read - 0.005
Avg. Disk sec/Write - 0.036
Avg. Disk Queue length - 0.095
% Disk Time - 3.104
Could this not be a network issue between mir01 and mir02 (seeing that the DB mirroring was 1st in the top 10 waits?)
I think you are probably right, the average disc queue length at less than 0.1 indicates that the drives are coping comfortably with the load. The average write time looks a bit high, so if the workload is read dominated it might indicate the comparatively rare writes having an IO problem; on the other hand it may be an artefact of mirroring.
You should probably use perfmon to look at max values as well as average, as people will complain if the system is occassionally slow just as much as if it is always slow, and it can be hard to discover from the complaints whether it's a continuous issue or just a frequent issue.
Also, I think you could usefully look at some of perfmon's sql mirroring statistics - log harden time, send/receive ack time, transaction delay.
Using 7200 RPM drives for an sql production system seems strange; 10krpm and 15krpm drives are not terribly expensive, and often deliver a much higher performance boost than the rotation rates suggest (because the improvement in seek times is generally proportionally much higher than the improvement in rotational latency - unless 7200rpm drives have much better seek times than they used to - so for IO with a significant random access component the performance boost is surprisingly big; if you have a drive dedicated to logging, it's latency is maybe less important as it's mostly serial IO).
Lastly (something Gail has said already) don't use percentage increases for tempdb (nor for transaction logs, nor for anything else); you can usually pick a sensible absolute increment which will give better results than a percentage.
Tom
October 17, 2011 at 3:05 am
Thanks for the update.
I would love to have some one's email address, as I believe this is going to be a looooong problem.
I've been put in an environment that was lacking performance from the beginning. I have managed to improve it a bit (from my perspective) and the BA leader also confirmed this.
However, there are still complaints from users that the performance are too slow.
SQL truly is like a little puppy, VERY difficult to keep happy. 🙁
October 17, 2011 at 6:25 am
I have read a little about the CXPACKET wait type.
Now this is one of our top waits.
Would increasing the MDOP to 4 help this out?
I have requested from one of the BA's to compare the execution times of his queries by running them as he normally does and then adding the OPTION (MAXDOP 4) to see if there are any improvements on the execution time.
October 17, 2011 at 6:33 am
ruan.keyser (10/17/2011)
Thanks for the update.I would love to have some one's email address, as I believe this is going to be a looooong problem.
I've been put in an environment that was lacking performance from the beginning. I have managed to improve it a bit (from my perspective) and the BA leader also confirmed this.
However, there are still complaints from users that the performance are too slow.
SQL <deleted> VERY difficult to keep happy. 🙁
Knowing that many of the top posters here are women you might edit that last comment of yours.
October 17, 2011 at 6:33 am
ruan.keyser (10/17/2011)
I have read a little about the CXPACKET wait type.Now this is one of our top waits.
Would increasing the MDOP to 4 help this out?
I have requested from one of the BA's to compare the execution times of his queries by running them as he normally does and then adding the OPTION (MAXDOP 4) to see if there are any improvements on the execution time.
CXPACKET is not a real wait. You can pretty much ignore it unless you're 100% this is the real issue and you're not there yet.
October 17, 2011 at 6:40 am
Apologies about the joke. I have edited it accordingly.
October 17, 2011 at 6:41 am
ruan.keyser (10/17/2011)
Apologies about the joke. I have edited it accordingly.
Don't worry about it. Except for the comparaison, I was agreeing with you 100% :-D.
October 17, 2011 at 7:09 am
Since you're not really looking for a paid consult, here's the best any of us can offer... written by Gail
@Gail the OP is from South Africa so I guess this one falls in your hands.
Viewing 15 posts - 16 through 30 (of 39 total)
You must be logged in to reply to this topic. Login to reply