Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Upgrade SQL 2008 to R2 creates timeouts

Posted on 2011-03-23
15
Medium Priority
?
631 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
Threat Trends for MSPs to Watch

See the findings.
Despite its humble beginnings, phishing has come a long way since those first crudely constructed emails. Today, phishing sites can appear and disappear in the length of a coffee break, and it takes more than a little know-how to keep your clients secure.

 
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

Threat Trends for MSPs to Watch

See the findings.
Despite its humble beginnings, phishing has come a long way since those first crudely constructed emails. Today, phishing sites can appear and disappear in the length of a coffee break, and it takes more than a little know-how to keep your clients secure.

Question has a verified solution.

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

As dyndns has reduced the capabilities of the free service, I looked around for other free providers of Dynamic DNS service. After testing several I decided to move my DNS hosting to Hurricane Electric as then domains that require dynamic hostnam…
Quality of Service (QoS) options are nearly endless when it comes to networks today. This article is merely one example of how it can be handled in a hub-n-spoke design using a 3-tier configuration.
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…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

670 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