March 10, 2014 at 5:32 am
I am seeing a timeout to the Pricincipel SQL server (2008 R2)
The exact error I am seeing is this:
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. This failure occured while attempting to connect to the Principle server.
System.Data.SqlClient.SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. This failure occured while attempting to connect to the Principle server. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out at XXXX
What I am trying to understand is is this a standard timeout, ie. the DB was unable to return the required results within the allowed time? Or does the "Principle Server" wording signify another issue?
I have not been able to find much about this specific error and need to know if its a performance issue that tuning the indexes and procs will help resolve or is there another issue I need to look at instead.
March 10, 2014 at 5:49 am
I don't think that's a mirroring or replication error, so it's likely that it's coming from the client application. If so, yeah, it's likely that you need to do some tuning inside the SQL Server system.
"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
June 5, 2014 at 2:18 am
So I guess all the following errors are related:
This failure occured while attempting to connect to the Failover server
This failure occured while attempting to connect to the Principle server
And a regular "The wait operation timed out", they all mean the same, depending on whether there is a mirroring scene or not.
(notice the typo on the first two errors 🙂
I'm getting every other day, but not at really specific times of day, timeout errors like the first one I mentioned.
I know there could be a myriad of reasons for this. We try to keep every query we write optimized. But I realize at some moments there are spikes in activity that produce these timeouts, I suspect that could be the cause but what do I know !
Would you think that this could be pin-pointed somehow with Profiler ? And if so, what could I try looking for ? Duration of queries ? We have some long running queries that we have to live with, that run not too often, so I guess looking for durations could lead to false positives, but I might be wrong. That's why I think there might be other things to look for that I don't realize.
I'm looking forward to getting some insights as to how to approach this 🙂
Cordially, Agustin.
June 5, 2014 at 4:30 am
Connection timeouts can be tough to monitor for since you're usually monitoring for activity and this is the failure to generate any activity at all. By and large I would have consistent and strong monitoring in place so that when I get a report of a timeout I can go to the server in question and see what the activity looked like at that time. What was the CPU, I/O, Memory behavior? What queries were running? Was the system spiked or spiking or was it quiet? If the latter, the issues might lie outside of SQL Server itself with the network. But it really comes down to setting up monitoring so that you can roll back to a moment in time to understand what was going on with the system. This means collecting metrics from Perfmon and SQL Server through the dynamic management views and collecting query metrics (I prefer extended events, but realize that's not as attractive in a 2008 system, so trace can be used).
Or, recognize how valuable your time is and instead of trying build a monitoring system pick up a third party monitoring tool ,even if you just use the Management Data Warehouse (MDW) from Microsoft. It's not great, but it's better than no monitoring at all and certainly saves you time. There are also really excellent tools from various vendors. My preferred ones are SQL Monitor from Red Gate or SQL Sentry's Performance Advisor. Full disclosure, I work for Red Gate.
Regardless of how you do it, without system metrics to understand what exactly is happening on your system, you're just guessing at causes, especially with connection timeouts.
"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
June 5, 2014 at 4:36 am
Thank you all for your replies.
We have steadily been adding more metrics to the system and optimising bottle necks as we find them.
we have seen a continuous improvement taking a step by step approach.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply