Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

The setup

We have an application loading a lot of transaction data for later querying. It's sharded on 4 SQL Server 2008R2 Enterprise Edition on Windows Server 2008 64 bits. All servers are configured exactly the same (or so I'm told). 72GB of memory, 2x6 core CPU, each attached to their own SAN. The databases are currently about 2TB in size.

They all load about the same number of transactions daily (+/- 10%), it's mostly inserts. Almost no updates. The process is I/O bound, the CPUs sits at 10-15% usage.

The problem

One of the servers is much slower than all the others. It only inserts transactions at a quarter of the speed of the other three. It has always been like this. It eventually catches up during quieter hours, but the other servers keep up with traffic at all time.

The SAN people have told us that all four SANs are performing the same, with no errors. Looking at the I/O graph on the Activity Monitor, the I/O is about the same on all servers.

I have extracted the schema for the databases and compared them, no difference. There doesn't seem to be any difference in the SQL Server configs either. They all use about the same amount of memory with the same distribution.

The question

What else should I be looking at?

We have been looking at this for months and can't come up with anything. There must be some explanation. I've looked at a lot of different performance metrics and I can't seem to find any glaring difference except that the overall inserts are slower.

share|improve this question
2  
I won't post as an answer because it's not comprehensive, but take a look at fragmentation and indexing on the tables. I know you say the volumes are about the same, but is the structure of the data itself the same? Is the distribution the same? Are there updates as well as inserts? –  JNK Jan 4 '12 at 21:57
    
Very few updates concentrated in one table. I made sure there is no locking problems. –  Mathieu Longtin Jan 5 '12 at 2:53
2  
This is like the canonical perfect post at first glance. –  jcolebrand Jan 5 '12 at 5:29
1  
Double check the network settings, if they're set to auto they may have negotiated down to 100mb. –  SqlACID Jan 5 '12 at 23:44
    
Just to keep you updated. The customer finally agreed that it might be hardware related. They ran IOZone against the SAN and they were all with 1% of each other. So we still don't know. –  Mathieu Longtin Feb 6 '12 at 21:05
add comment

4 Answers

I'd be inclined to point a finger at the storage of the poorly performing server.

Sounds like you're in the unfortunate position of having a storage admin telling you everything is ok when it quite possibly isn't. Only way to be sure is to prove it.

If you have the luxury of a maintenance window, run a batch of SQLIO tests against each server and compare the results. @BrentOzar has a good intro article on SQLIO which also covers some alternative tools such as Crystal Diskmark, which might be enough to prove the point in your case. Run the tests in collaboration with the storage admin, so they can witness the issues first hand. Perhaps arm yourself with How to Prove It’s a SAN Problem before hand.

If IO testing proves fruitless or impossible, throw sp_whoisactive or a similar monitoring solution at the problem. Kendra Little's Collecting Data from sp_WhoIsActive in a Table would be the best approach for a long running ETL process.

Before doing any of the above, take 1 minute to check something very very basic that crops up time and time again... the NTFS allocation unit size on each of the arrays.

share|improve this answer
    
That's what I'm thinking, storage issues. Thanks for the tip on SQLIO. –  Mathieu Longtin Jan 5 '12 at 2:57
add comment

I'd suggest starting this afresh as a performance problem - have you looked at the top waits on this particular server? Many scripts abound, but I've enclosed a link to Paul Randal's website, and his SQL query is towards the bottom. Do IO waits show at the top?

http://sqlskills.com/BLOGS/PAUL/post/Survey-what-is-the-highest-wait-on-your-system.aspx

share|improve this answer
1  
I/O waits definitively at the top, buffer I/O, Latch and Buffer Latch are usually at the top of the "Resource Waits" section of the activity monitor. I made sure the app does minimal locking. –  Mathieu Longtin Jan 5 '12 at 2:50
add comment

I suggest you look at pure I/O speeds on the slow server. Grab a large file (e.g. 1GB) and measure how long it takes to copy to/from your slow server. Compare the same against the other servers. Try copying to and from different directories on the same logical drive, across different drives, etc.

I suspect the problem is either something wrong with your network connection or with the particular region on the SAN that the slow server is allocated.

share|improve this answer
add comment

Please use at your own risk. If you have a maintenance window, try running a Geekbench benchmarking test. You will have to install the application (a few MB). The install and testing is pretty quick and the output report is detailed. I have blogged about this here.

What I like about Geekbench is that I get a single numerical score for comparison. You can compare the score of your four servers. Note that the applications running during benchmarking may make a difference in the score.

This may help you determine if there is a significant difference between your four servers. After that you can try the nice methods suggested in the other answers to identify the reason for slowness.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.