February 10, 2011 at 7:25 am
We just moved to a new colo site(i.e. new web servers, new database servers etc) and we are now experiencing timeouts. I have already rebuilt all of the indexes and updated statistics as part of the implementation to this new environment. I've reviewed the database server setting to see if there is a difference in the new settings versus the old setting. Everything seems to be in order. I know from the application developers that the time out setting in the application is 30 seconds. I have trace flag 1222 turned on to identify any deadlocks and have been running sql profile using the duration template(milliseconds set to 25000) to identify any long running queries. Any better ideas for identifying time outs?
Your suggestions are greatly appreciated!
Thanks in advance!
February 10, 2011 at 7:40 am
Are connections being successfully made to the SQL Server? are the symptoms occurring on more than 1 sql server or more than 1 database... The first steps would be to narrow down your issue? Are the app servers on same site? is the network link the same as before? what version of SQL Server?
Some things you could attempt:
> DO a FTS on a medium size table from a server where you are getting timeouts from.. work out transfer speed based on time taken.
> check for blocking/locking
> CHeck waits
SELECT st.text AS [SQL Text],
w.session_id,
w.wait_duration_ms,
w.wait_type, w.resource_address,
w.blocking_session_id,
w.resource_description FROM sys.dm_os_waiting_tasks AS w
INNER JOIN sys.dm_exec_connections AS c ON w.session_id = c.session_id
CROSS APPLY (SELECT * FROM sys.dm_exec_sql_text(c.most_recent_sql_handle))
AS st WHERE w.session_id > 50
AND w.wait_duration_ms > 0
February 10, 2011 at 8:05 am
thanks for the info. I will check into these items that you brought forth. We are still running sql server 2005 same as the old environment.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply