August 15, 2014 at 5:11 pm
I am running upgrade advisor 2012 on Sql server 2008R2 instance and hitting the below error. Please let me know the solution.
Timeout expired: The Timeout period elapsed prior to completion of the operation or the server is not responding . FRAMEWORKASSEMBLIES
August 15, 2014 at 7:31 pm
The purpose of a client's timeout setting is to avoid consuming the server's resources for an indefinite period of time. When the client has waited up to its timeout setting, the client sends an Attention event to SQL Server. In response to the attention event, SQL Server cancels execution of the query that was running.
There are various reasons a client is waiting longer than it wants to/expected to wait. The reasons invariably concern contention for resources within SQL Server (a performance problem). Troubleshooting performance is a very large topic. I can only offer you some brief tips. While the Upgrade Advisor is waiting, you can poll sys.dm_os_waiting_tasks in an attempt to capture what its query task was waiting upon. Search this site's scripts for "sys.dm_os_waiting_tasks". In a broader sense, the server itself can be experiencing overall waits that also can impede executions of upgrade advisor queries (and other queries). Search this site for scripts that contain "sys.dm_os_wait_stats". Another cause of slowness is lock contention - search this site for "blocking scripts". It may also be that the operating system is out of physical memory, and instead using its paging file. While the Upgrade Advisor is running, you can run perfmon.msc, and you can check the SQL Server errorlog before and during the last period of time the Upgrade Advisor was run (and you can ensure SQL Server's max server memory setting is less than the amount of physical memory). Because SQL Server handles an Attention event, you can also run a (preferably server-side) trace while the query is running. That trace should collect (at the barest) the Attention Event and the SP:StmtStarting event. Within the collected trace file you will see the statement starting and its spid (system process id), after the time of that event you will see the same spid handling an Attention event. The statement that was seen to be starting is either the culprit or the victim. The sys.dm_os_wait polls, in conjunction with the trace, should be able to help you determine cause of the timeout.
For a more comprehensive performance picture, you may want to consider using codeplex.com's pssdiag and sqlnexus, but there are many alternative tools (and many alternative ways to consider performance). Pssdiag will collect the DMVs I mentioned, the perfmon log, and a server side trace, all at once. SQL Nexus will report on what pssdiag collected. Keep in mind that tracing requires adequate disk I/O, which if lacking can mean that running pssdiag itself can cause a performance problem...
If you are upgrading because performance is slow, I do not believe upgrading to SQL Server 2012 will offer you tangible relief. In such a situation I would recommend resolving the performance problem first, before upgrading. For example, if the system's disk IO is inadequate for the queries being submitted by SQL Server's clients (Upgrade Advisor is just one such client), and if indexing/statistics are being properly maintained, then upgrading to SQL Server 2012 will not make the disks perform adequately.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply