SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Migration issue - slow login, slow SSMS response
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mtl777
Yak Posting Veteran

63 Posts

Posted - 09/13/2012 :  13:26:54  Show Profile  Reply with Quote
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

USA
1974 Posts

Posted - 09/13/2012 :  13:32:59  Show Profile  Visit chadmat's Homepage  Reply with Quote
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

USA
37296 Posts

Posted - 09/13/2012 :  13:34:30  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 - 09/13/2012 :  15:31:24  Show Profile  Reply with Quote
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

USA
1974 Posts

Posted - 09/13/2012 :  16:28:15  Show Profile  Visit chadmat's Homepage  Reply with Quote
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 - 09/13/2012 :  18:42:07  Show Profile  Reply with Quote
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

USA
37296 Posts

Posted - 09/13/2012 :  19:22:51  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Netherlands
12 Posts

Posted - 09/14/2012 :  02:54:27  Show Profile  Reply with Quote
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 - 09/18/2012 :  23:14:09  Show Profile  Reply with Quote
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!

Edited by - mtl777 on 09/19/2012 18:18:43
Go to Top of Page

chadmat
The Chadinator

USA
1974 Posts

Posted - 09/19/2012 :  18:21:07  Show Profile  Visit chadmat's Homepage  Reply with Quote
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 - 09/19/2012 :  20:40:34  Show Profile  Reply with Quote
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!

Edited by - mtl777 on 09/19/2012 22:58:13
Go to Top of Page

chadmat
The Chadinator

USA
1974 Posts

Posted - 09/20/2012 :  14:48:10  Show Profile  Visit chadmat's Homepage  Reply with Quote
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 - 09/24/2012 :  15:03:55  Show Profile  Reply with Quote
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 - 09/28/2012 :  16:08:29  Show Profile  Reply with Quote
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

USA
37296 Posts

Posted - 09/28/2012 :  16:44:31  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 - 10/02/2012 :  15:25:36  Show Profile  Reply with Quote
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

USA
37296 Posts

Posted - 10/02/2012 :  15:57:44  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000