Solved

Upgrade SQL 2008 to R2 creates timeouts

Posted on 2011-03-23
15
626 Views
Last Modified: 2012-05-11
Hi,
I upgraded an exisiting 2008 Developer Edition (default instance) on a server to 2008 R2.
Since the upgrade the connection from client-pc (in domain) to the database has become real slow.
Simply connecting SSMS to the database engine takes about 45 seconds, which used to be like one second before the upgrade.
These delays makes the db engine useless because all our software using the db constantly has timeouts.
Where to look to fix this?
Thanks, Michael
0
Comment
Question by:IggyBlue
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 6
15 Comments
 

Author Comment

by:IggyBlue
ID: 35197058
Oh yes, important info I guess:

- Locally (on the server) apps using that db and ssms work just fine
- Shared Memory, Named Pipes and TCP/IP are active
- I use the default port
- Remote connections are allowed
- Server timeout is 600s
- Unlimited concurrent connections
- Mixed mode
- 64Bit
- Remote Login Timeout 20s
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35197127
Please also post following query results:

SELECT TOP 10 *
FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC

Open in new window

0
 

Author Comment

by:IggyBlue
ID: 35197182
On the server:

wait_type      waiting_tasks_count      wait_time_ms      max_wait_time_ms      signal_wait_time_ms
LOGMGR_QUEUE      129      1473840      1199995      2
LAZYWRITER_SLEEP      1501      1473156      1000      50
CHECKPOINT_QUEUE      5      1472815      1469728      0
SQLTRACE_INCREMENTAL_FLUSH_SLEEP      369      1472084      4000      0
XE_TIMER_EVENT      51      1470183      30000      1470035
REQUEST_FOR_DEADLOCK_SEARCH      295      1470084      5000      1470084
FT_IFTS_SCHEDULER_IDLE_WAIT      25      1440010      60000      0
SLEEP_TASK      6298      737855      1024      101
BROKER_TO_FLUSH      721      737322      1024      0
XE_DISPATCHER_WAIT      2      90150      90150      0

On the Client:
wait_type      waiting_tasks_count      wait_time_ms      max_wait_time_ms      signal_wait_time_ms
LAZYWRITER_SLEEP      1890      1860795      1000      50
XE_TIMER_EVENT      64      1860192      30000      1860044
XE_DISPATCHER_WAIT      4      1860192      1620038      0
REQUEST_FOR_DEADLOCK_SEARCH      373      1860106      5000      1860106
SQLTRACE_INCREMENTAL_FLUSH_SLEEP      466      1860106      4000      0
FT_IFTS_SCHEDULER_IDLE_WAIT      31      1800012      60000      0
CHECKPOINT_QUEUE      7      1783379      1469728      3
LOGMGR_QUEUE      132      1474123      1199995      2
SLEEP_TASK      7933      931502      1024      101
BROKER_TO_FLUSH      910      930869      1024      0
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35197277
What do you mena by on the client?
Please also try to update statistics on upgraded sqervr - run sp_updatestats:
http://msdn.microsoft.com/en-us/library/ms173804.aspx
0
 

Author Comment

by:IggyBlue
ID: 35197304
I mean my Developer machine by client (Win7 64Bit).
The DB-server runs on a Win2008 server.
I will update the statistics.
Thanks for you help!
0
 

Author Comment

by:IggyBlue
ID: 35197327
I ran sp_updatestats on master, is that ok?
Results:
Updating [dbo].[spt_fallback_db]
    0 index(es)/statistic(s) have been updated, 0 did not require update.
 
Updating [dbo].[spt_fallback_dev]
    0 index(es)/statistic(s) have been updated, 0 did not require update.
 
Updating [dbo].[spt_fallback_usg]
    0 index(es)/statistic(s) have been updated, 0 did not require update.
 
Updating [sys].[queue_messages_1003150619]
    [queue_clustered_index], update is not necessary...
    [queue_secondary_index], update is not necessary...
    0 index(es)/statistic(s) have been updated, 2 did not require update.
 
Updating [sys].[queue_messages_1035150733]
    [queue_clustered_index], update is not necessary...
    [queue_secondary_index], update is not necessary...
    0 index(es)/statistic(s) have been updated, 2 did not require update.
 
Updating [sys].[queue_messages_1067150847]
    [queue_clustered_index], update is not necessary...
    [queue_secondary_index], update is not necessary...
    0 index(es)/statistic(s) have been updated, 2 did not require update.
 
Updating [sys].[syscommittab]
    [ci_commit_ts], update is not necessary...
    [si_xdes_id], update is not necessary...
    0 index(es)/statistic(s) have been updated, 2 did not require update.
 
Updating [dbo].[MSreplication_options]
    [_WA_Sys_00000001_45544755] has been updated...
    1 index(es)/statistic(s) have been updated, 0 did not require update.
 
Updating [dbo].[spt_monitor]
    0 index(es)/statistic(s) have been updated, 0 did not require update.
 
Updating [dbo].[spt_values]
    [spt_valuesclust], update is not necessary...
    [ix2_spt_values_nu_nc], update is not necessary...
    0 index(es)/statistic(s) have been updated, 2 did not require update.
 
Statistics for all tables have been updated.
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35197360
Run this:

exec sp_MSforeachdb '
USE ?
IF DB_NAME() NOT LIKE ''%temp%''
BEGIN 
EXEC sp_updatestats
SELECT ''Stats update for ''+DB_NAME()+'' was executed''
END'

Open in new window

0
 

Author Comment

by:IggyBlue
ID: 35197480
Done, see att for results Query-Results.txt
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35197554
Is your server behaves better now?
You wrote that 'Locally (on the server) apps using that db and ssms work just fine' which I've overlooked, so please check whether your network does not suffer from hardware or other problems.
Check your system logs, device manager.
0
 

Author Comment

by:IggyBlue
ID: 35202055
Hi Daniel,
No, the (remote) performance still is very bad.
System logs are fully checked and OK.
The problem literally started after I upgraded SQL Server 2008 DE to R2.
I am 99% sure the problem is caused by the upgrade :(
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35202195
Please try to connect by SSMS using tcp/ip protocol. You can do it by typing server_ip:port instead of it's name, e.g.: 72.21.211.176:1433
0
 

Author Comment

by:IggyBlue
ID: 35208652
Hi Daniel,
Sorry for the delay in feedback.
Using the IP instead of servername doesn't solve the issue.
It doesn't matter if I also use the port or not.
So my problem is still actual :(
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35212981
What edition is it now in R2, still Developer?
It indeed may be R2 issue, I've read about problems on HP servers:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_26597709.html
0
 

Accepted Solution

by:
IggyBlue earned 0 total points
ID: 35297710
Hi Daniel,
Sorry for the long delay.
I know about the NVidea issue.
This however is not applicable to my case:

> Locally (on the server) no issues, only remote (whatever client)
> Servers and workstations are Dell
> It is the connection which takes too long. Once connected queries run as expected

Anyway, I uninstalled R2 and installed 2008 again => no problem anymore.
I installed R2 on another server => no problem.
So I guess it must be a MS bug :(
Cheers,

Michael
0
 

Author Closing Comment

by:IggyBlue
ID: 37301709
Problem wasn't solved but did a workaround
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Network ports are the threads that hold network communication together. They are an essential part of networking that can be easily ignore or misunderstood, my goals is to show those who don't have a strong network foundation how network ports opera…
PRTG Network Monitor lets you monitor your bandwidth usage, so you know who is using up your bandwidth, and what they're using it for.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

626 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question