Database slowdown a few times a day using mirroring

Question

Database slowdown a few times a day using mirroring

MarcF-SQL

SSC Journeyman

Points: 99
More actions
June 17, 2014 at 6:00 am

#287657

Hello
Our web application suffers DB query timeouts a few times a day at random times. Can you help to work out the cause, e.g. deadlocks, mirroring (disk or network), etc?
We have two identical database servers (and a third witness server) with MSSQL 2008 R2 mirroring in high safety mode with automatic failover. Two DBs are mirrored and each server is principal in one so in the event of a failure, both DBs will run from one server. As far as I can see, the problem only occurs on one of these two databases.
Using performance monitor, I can see the "Log Harden Time (ms)" on the principal server spikes to 212,301.206ms at the exact time a timeout occurred (07:30 today). The average from a 24hr period is 22.712ms.
What else should I be looking at to determine the cause of this slowdown?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

free_mascot One Orange Chip Points: 27169 More actions · Answer 1

Did you get a chance to find out any error in SQL OR WINDOW log?

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

MarcF-SQL SSC Journeyman Points: 99 More actions · Answer 2

On the principal server for the database in question (rbsv3), the following event was logged about 10 seconds before the slowdown:

SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [D:\Databases\pes.ldf] in database [pes] (5). The OS file handle is 0x00000000000012D4. The offset of the latest long I/O is: 0x000000a8a03c00

Please note that this is about a different database which is the mirror on the rbsv3 principal server. I'm inclined to think this is unrelated. There is no similar log on the mirror server for rbsv3 (which is the principal for pes).

Our webapp reports the slow running SQL statements and it seems a good number of the slow page execution alerts point to an SQL statement either inserting or updating a table we use to keep track of page views. This may be misleading though as it's always the first UPDATE or INSERT statement that executes during the page load. Regardless, it seems these commit's are waiting on the mirror to acknowledge.

UPDATE whosonline SET datetime = GETDATE(), url = '/admin/users/add' WHERE username = 'theTenantID\theUsername (session_ID)'; SELECT SCOPE_IDENTITY()

Took 2.91797s

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Not much we can really do from here with the information provided. My suggestion is to get the actual execution plan from the queries involved and look at the indexing of the tables. That is just a start. If you are getting I/O errors you may also want to look at your I/O system and how your databases are laid out on your storage system.

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 4

Two guesses, possibly out of date statistics on the tables in question or possibly something up with your I/O sub-system since you're seeing an I/O error.

"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

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 5

Two guesses, possibly out of date statistics on the tables in question or possibly something up with your I/O sub-system since you're seeing an I/O error.

EDIT

Sorry for the duplicate post. There was an error and it looked like it didn't go through.

"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

MarcF-SQL SSC Journeyman Points: 99 More actions · Answer 6

Hi Lynn/Grant

I'm seeing an unusually high Log Harden Time which coincides with the slowdown. I don't think the I/O error posted has anything to do with this as it mentions a completely different database, though perhaps I'm wrong.

The SQL query posted is for a table tracking who's online, it's incredibly simple with just an ID (PK), username (nvarchar), datetime (datetime) & url (nvarchar). I don't believe this table is causing the problem, rather it's always the first one executed on page load and so a momentary slowdown with the database will affect this query first.

I'm seeking help as to which other performance counters I can look at to determine if the problem is disk I/O on the principal or mirror, network latency/bandwidth between the servers or something else.

What other information can I provide to help?

EDIT: Grant, I've checked and no statistics were updated around the time of the slowdown this morning.

Many thanks,

Marcus