November 20, 2010 at 6:17 am
Hi,
Last week we upgraded our system
Sql server 2005 to Sql server 2008
Windows 2003 to Windows 2008
32 cpu 64 gb ram to 64 cpu 256 gb ram
One server strcuture to Microsoft cluster
But our system is running slowly. Sometimes (random times) our queries are so much slow such as 10-20 times then older time.
When i connect to sql server as domain user it takes (the login time) about 8-9 seconds.
As sql server user (SA) the login times takes less then 1 second.
Our sql server service is running now as domain user. Is it possible that the reason for slowing is running service as domain user?
November 20, 2010 at 11:25 am
I doubt how you connect to instance (Windows\SQL) would cause performance issues. You may want to check if any other processes running on the instance are slowing down this process.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
November 20, 2010 at 11:58 am
ocolakoglu (11/20/2010)
Hi,Last week we upgraded our system
Sql server 2005 to Sql server 2008
Windows 2003 to Windows 2008
32 cpu 64 gb ram to 64 cpu 256 gb ram
One server strcuture to Microsoft cluster
But our system is running slowly. Sometimes (random times) our queries are so much slow such as 10-20 times then older time.
When i connect to sql server as domain user it takes (the login time) about 8-9 seconds.
As sql server user (SA) the login times takes less then 1 second.
Our sql server service is running now as domain user. Is it possible that the reason for slowing is running service as domain user?
How many nodes are in the cluster?
Can you provide more detailed info as to the steps you performed to complete the upgrade?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 21, 2010 at 3:45 am
We have change the sql server startup user from domain account to local system and it works much better now. I couldnt find the problem about domain user but it is ok with local system account.
November 21, 2010 at 3:45 am
we have. 2 cluster nodes. Both are HP 580 G7. Same machine same configuration.
November 21, 2010 at 4:32 am
ocolakoglu (11/21/2010)
We have change the sql server startup user from domain account to local system
how are you changing the user account for the sql server service?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 21, 2010 at 4:35 am
Computer management>Services>Sql server> Log On as Local system account
I do this for both nodes.
November 21, 2010 at 5:34 am
Ok, thats undoubtedly part of the issue. You must use SQL Server configuration manager to change the service user account\password credentials!
Note: Local system will automatically have full rights to all objects.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 21, 2010 at 6:42 am
We have change the sql server startup user from domain account to local system and it works much better now.
The restart of SQL Server may be the difference.
I have done about 15 upgrades over the last 3 months and encountered some similar performance and stability problems. Can you answer these questions?
Was as stress test of network communication speed performed ?
Are NICs set to "autodetect" ?
What are Disk IO rates (run the virtual io stats data management view)
If using a SAN, was the HBA queue depth setting increased from the default ?
Did you configure Windows and SQL Server for memory management ?
Was Windows file cache size limited? Registry key is HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\SessionManager\MemoryManagement\SystemCacheDirtyPageThreshold
Does SQL Server have lock pages in memory privilege?
Does SQL Server have instant file initialization privilege? ( windows right for "Perform Volume Maintenance Tasks")
Have SQL Server minimum and maximum memory been set?
After a database was migrated to SQL Server 2008, were the following run?
DBCC UPDATEUSAGE()
DBCC CHECKDB with data_purity
rebuild of all indexes
update of all statistics
removal of any column statistics where the column is also the first column of an index
removal of any hypothetical indexes
SQL = Scarcely Qualifies as a Language
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply