June 5, 2013 at 4:52 am
Hey,
I have a SQL 2008 R2, running on Windows Server 2008, with all latest service packs and updates. It runs approx. 30 databases that totals over 500GB (data and logs). The server has 8 cores (each hardly ever getting above 50%) and 16 GB RAM (fully used); it is a VMWare server in a datacenter. The webapplication (other server) that our customers use runs okay, and the speed (queries etc.) is good. There are no other applications running on this server. Last weekend the SQL Server was restarted.
Lately, when we run the MMS (I know, not best practise), we encounter that it runs very slow. Sometimes altering a simple little query takes 30 seconds. Opening a folder with 200 Stored Procedures takes more than a minute to expand. An application (running on another server) that connects to SQL, gave this error after a restart today:
[font="Courier New"]SQL Connection failed: Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=4; handshake=14981;[/font]
What can I do to find out what is going on?
Thanks,
Raymond
June 5, 2013 at 5:03 am
HI
you need to find out what is keeping your server busy? have you looked wait state issues on the server.
otherwise setup a trace to get some perf data to analyse
see links for how to:
June 5, 2013 at 5:30 am
Raymond van Laake (6/5/2013)
Hey,I have a SQL 2008 R2, running on Windows Server 2008, with all latest service packs and updates. It runs approx. 30 databases that totals over 500GB (data and logs). The server has 8 cores (each hardly ever getting above 50%) and 16 GB RAM (fully used); it is a VMWare server in a datacenter. The webapplication (other server) that our customers use runs okay, and the speed (queries etc.) is good. There are no other applications running on this server. Last weekend the SQL Server was restarted.
Lately, when we run the MMS (I know, not best practise), we encounter that it runs very slow. Sometimes altering a simple little query takes 30 seconds. Opening a folder with 200 Stored Procedures takes more than a minute to expand. An application (running on another server) that connects to SQL, gave this error after a restart today:
[font="Courier New"]SQL Connection failed: Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=4; handshake=14981;[/font]
What can I do to find out what is going on?
Thanks,
Raymond
This usually has to do with network latency.
What is your ping response times to the SQL Server?
June 5, 2013 at 5:35 am
All our servers are in the (same) datacenter, all are in VMWare, and with all SP's and updates. When I check latency with PING it's always less than 1ms. We've already asked our provider to check their network, and there are no issues.
I believe it has to do with SQL because the MMS is also very slow.
June 5, 2013 at 6:24 am
Maybe ahave a read through this, seen it causing all sorts of problems before.
http://support.microsoft.com/kb/942861
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply