Solved

Upgrade SQL 2008 to R2 creates timeouts

Posted on 2011-03-23
15
618 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
  • 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
 
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

David Varnum recently wrote up his impressions of PRTG, based on a presentation by my colleague Christian at Tech Field Day at VMworld in Barcelona. Thanks David, for your detailed and honest evaluation!
Use of TCL script on Cisco devices:  - create file and merge it with running configuration to apply configuration changes
After creating this article (http://www.experts-exchange.com/articles/23699/Setup-Mikrotik-routers-with-OSPF.html), I decided to make a video (no audio) to show you how to configure the routers and run some trace routes and pings between the 7 sites…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

760 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now