Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Migration issue - slow login, slow SSMS response

Author  Topic 

mtl777
Yak Posting Veteran

63 Posts

Posted - 2012-09-13 : 13:26:54
Hey guys, I've been pulling my hair out on this one and badly need your help. Our server containing our SS 2000 database crashed. We took the opportunity to upgrade our SS 2000 db to SS 2005 and migrate it to a new server. I restored the SS 2000 db full backup file to SS 2005. The restore was successful. But when users login through our app, or even if I just login through SSMS, it takes long, abnormally long. And when I'm able to login to SSMS, if I even just right-click on a table to view its properties, it also takes long before the menu where I can select Properties shows. And then when I select Properties that takes long again.

The server's OS is Windows Server 2008 Standard. I have updated it to SP2, and also updated SS 2005 to SP4, all to no avail. I have also tried unchecking "Check for server certificate revocation" in IE Tools > Internet Options > Advanced, as suggested in some sites I read, but it didn't fix the problem.

Oh, and one more thing. I also couldn't create maintenance plans. When I right-click on Maintenance Plans to attempt to create one, after a long wait it gives me an "OLE DB error 0x80004005 (Unable to complete login process due to delay in opening server connection) occurred while enumerating packages. A SQL statement was issued and failed."

Any idea what's going on? I hope one of you has encountered this problem and already knows the solution.

BTW, how do I change the login timeout of SQL Server?

Your help would be so very appreciated. Thanks in advance!

chadmat
The Chadinator

1974 Posts

Posted - 2012-09-13 : 13:32:59
Did you Rebuild indexes/update stats? Also, you should run perfmon and see were your bottlneck is.

-Chad
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-09-13 : 13:34:30
You need to diagnose where the problem is via Performance Monitor and Profiler. You need to see where the bottleneck is. Is it disks, CPU, memory? Is it long-running queries or out-of-date stats? Could be lots of things. You need to diagnose where the problem is before proceeding further.

Did you update stats with fullscan on all tables after the upgrade? That is definitely recommended.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

mtl777
Yak Posting Veteran

63 Posts

Posted - 2012-09-13 : 15:31:24
Thanks for your replies!

@Chadmat: Does SS 2005 have different indexing than 2000? Why the need to rebuild indexes? They were already rebuilt in 2000 shortly before the full backup that was restored in 2005.

@tkizer: I'll try the update stats with fullscan, as well as the other stuff you mentioned. Thanks for the tip!
Go to Top of Page

chadmat
The Chadinator

1974 Posts

Posted - 2012-09-13 : 16:28:15
It was more about updating stats, but if you rebuild the indexes, you get the update stats along with it, so I asked if you did either.

-Chad
Go to Top of Page

mtl777
Yak Posting Veteran

63 Posts

Posted - 2012-09-13 : 18:42:07
In the SQL Server Configuration Manager, can I disable the SQL Server Browser service? What does the SQL Server Browser service affect? I'm trying to disable services that are not being used in our system or applications.

Another thing I'm looking at is the Named Pipes protocol in SQL Server Configuration Manager. What is that for? Can I disable that too?

Thanks!
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-09-13 : 19:22:51
If you are considering disabling these, you are going down the wrong path in troubleshooting this issue. Leave those in place. Even if they weren't being used, you will not even notice a difference in performance if you disabled them.

You've got a serious problem here that should be easy to spot with the proper tools, such as PerfMon and Profiler.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Rimsky
Starting Member

12 Posts

Posted - 2012-09-14 : 02:54:27
Hi,
Just another road to explore: Whenever you restore a database to a different path or server, your logins might get orphaned. For the user experiencing problems, try this on the master database:
exec sp_change_users_login 'Auto_Fix', '<username>'
Go to Top of Page

mtl777
Yak Posting Veteran

63 Posts

Posted - 2012-09-18 : 23:14:09
I have run the Profiler. The CPU and Memory are fine. It's the Network and the Disk that seem to be maxing out, more so the Network. The strange thing is that the slowness causing the lock timeout error still happens even late at night when no one else is using the database. Is it an issue with the NIC? Or maybe there are BIOS settings that might be incompatible or inappropriate?

I would also like to add that I have already done the Update Statistics With FullScan for all tables except a few ones that are not being used anymore. Database queries have become faster, though some users are still having a login timeout when logging in through our VB app. But once logged in, the database queries from the VB app run fast (at least compared to before when we had that old, slow server). So it seems to be just an issue when logging in, or anytime that a login is being checked, such as when you open a stored procedure or try to get the properties of a table in SSMS.

I forgot to mention that the new server was hastily bought and built, with only a 2TB, 7200 rpm Western Digital SATA drive and no RAID for the database, and an SSD for the OS (Windows Server 2008 in 32-bit mode). The CPU is a single 3.2GHz Intel Core I7 3930K (6 cores, 12 threads). The memory is 16GB DDR3, but because we installed the OS in 32-bit mode, only 4GB of that is usable as far as I know. Just putting these specs out in case they're relevant to your analysis of the problem.

I'm not sure where to go from here. Any ideas to solve this problem?

Thanks!
Go to Top of Page

chadmat
The Chadinator

1974 Posts

Posted - 2012-09-19 : 18:21:07
You really should reinstall in 64 bit, but if that is not possible, you can still use the memory for buffer pool. Just turn on AWE. There are so many other problems that can crop up in 3r bit though, I wouldn't recommend staying on 32 bit.

-Chad
Go to Top of Page

mtl777
Yak Posting Veteran

63 Posts

Posted - 2012-09-19 : 20:40:34
quote:
Originally posted by chadmat

You really should reinstall in 64 bit, but if that is not possible, you can still use the memory for buffer pool. Just turn on AWE. There are so many other problems that can crop up in 3r bit though, I wouldn't recommend staying on 32 bit.



We installed 32 bit because with our database coming from SS 2000 which is 32 bit, we thought we might have issues with 64 bit. Is SS 2005 fully compatible with 64 bit OS?

What is AWE and how do I turn it on?

If I choose to change to 64 bit, can I do an in-place upgrade of the OS and SS 2005 to 64 bit so that I won't have to reformat the hard drive and reinstall the OS and apps?

BTW, when we were on SS 2000 (32 bit), we did not have this problem. So why should we now need to upgrade to 64 bit to fix the problem? Does SS 2005 have problems when running in 32 bit OS?

Thanks!
Go to Top of Page

chadmat
The Chadinator

1974 Posts

Posted - 2012-09-20 : 14:48:10
AWE is a config option (sp_configure, or server properties->memory tab).

Not sure about in place upgrade, I would guess not. I have no idea if that is the cause of the problem that you are talking about, I just think it is a really bad idea to build a 32 bit SQL server in this day and age.

-Chad
Go to Top of Page

mtl777
Yak Posting Veteran

63 Posts

Posted - 2012-09-24 : 15:03:55
Thanks Chad. I tried setting AWE to ON. Still the same thing. Upgrading to 64 bit may not be possible at this point due to the amount of work and downtime involved. Any other ideas?
Go to Top of Page

mtl777
Yak Posting Veteran

63 Posts

Posted - 2012-09-28 : 16:08:29
Update: If I go to the server machine itself and open SSMS directly at the server, I do not have the slowdown issue. It is only when I open SSMS at a workstation that I'm having this issue. So I am now almost certain that this problem is related to something in the network configuration or hardware. Does anybody have a similar experience with this? This is so frustrating and I would really appreciate any suggestions maybe on the networking aspect. Thanks!
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-09-28 : 16:44:31
http://blogs.msdn.com/b/euanga/archive/2006/07/11/662053.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

mtl777
Yak Posting Veteran

63 Posts

Posted - 2012-10-02 : 15:25:36
quote:
Originally posted by tkizer

http://blogs.msdn.com/b/euanga/archive/2006/07/11/662053.aspx

Tara Kizer



Thanks Tara! I tried the suggestions in the link you gave and unfortunately it didn't fix the problem. Actually I had already tried them before I started this thread, and I tried again last night but no dice. What could it possibly be?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-10-02 : 15:57:44
I honestly have no idea. I'd recommend opening a case with Microsoft at this point.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page
   

- Advertisement -