June 19, 2008 at 10:37 pm
Hi ,
In SQL server 2000,we are facing a severe performance degrade IIS is running in that server . Sleeping Sessions are not released even if we try restarting the same .
We tried the option given in " Orphaned session " Of SQL server 2000 books online by changing the keep alive but no considerable imprvement any idea?
June 20, 2008 at 12:42 am
Are you sure that the sleeping sessions come from IIS?
Are these sessions blocking other sessions? Why are you concerned about them?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 20, 2008 at 6:08 am
Sleeping connections is not blocking anything ,but too many exist .
and site is very slow
Tried changing Time settings in Registry key , refer "Orphaned sessions in Books online of SQL server 2000 books online "
KeepAliveTime in HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters This also not improving IIS performance
Any luck ?
June 20, 2008 at 7:23 am
Are you sure they're from IIS?
If you check the slow running queries in sysprocesses, what is the wait type that's listed for them?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 20, 2008 at 7:40 am
Nothing to worry about sleeping sessions.. Just Keep an eye on the Blocked Sessions and status='runnable'
Again, IIS uses connection pooling right?, so there should be no issues... but in certain Circumstances, you have to restart the IIS... this will help resolve your application performance issue... (By experience) also.. do a
DBCC OPENTRAN (DBNAME) and see if there are any open Transactions.. try this several times... Also check to see the BUFFER CACHE HIT RATIO... and see if it is Optimally used...
Maninder
www.dbanation.com
June 25, 2008 at 12:45 am
I tried restoring the database from PROD to backup server which is having similar setup,there site performance is good no issues found,after rebooting the server also sleeping does not get released in PROD server.No blocks or long running queries found but site is slow
June 25, 2008 at 7:35 am
Are you sure this is not a Hardware Issue. Network Card, or need to check the disk I/O. Run a perfmon and look at values for DISK I/O and Also SQL Server Buffer Cache Hit RAtio...
Maninder
www.dbanation.com
June 26, 2008 at 7:25 am
Up to a point, having lots of spids sleeping won't affect performance. This is normal with thread pooling.
You stated that you had no blocking and no long-running queries (which I assume you gathered by doing what Gail said and monitoring sysprocesses). If that is the case then it would seem that the problem with slow website lies outside of sql server. Have you done all of the normal performance analysis items for poor website response?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 27, 2008 at 10:25 am
If restoring to a similiar build eleviated the problem then look to competing resources on the prod box.
Based on the database being used for a website I'm assuming most transactions are of read nature.
Did you run a perfmon and look at the average disk read queue length for each drive? The Average here should be under 2, if it's not look to add more storage or better seperate your storage files. Let us know these guys love to help.
Zach
John Zacharkan
June 27, 2008 at 11:27 am
zach_john (6/27/2008)
Did you run a perfmon and look at the average disk read queue length for each drive? The Average here should be under 2, if it's not look to add more storage or better seperate your storage files.
That metric is useless for many if not most installations these days due to RAID and SANs. Avg disk sec/read and avg disk sec/write are much better metrics to use.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 11, 2008 at 3:20 pm
This issue is resolved - following steps taken
1.AWE enabled
2.RAID levels checked
3.SQL server maximum memory setting changed to more value(like 5Gb) with /3GB and /PAE.
4.Max degree of parallelism brought to 0
5.Both Db and operating system files kept in separate disk
This is SQL server 2005 with windows 2003 server
October 12, 2008 at 2:34 am
rajganesh.dba (10/11/2008)
3.SQL server maximum memory setting changed to more value(like 5Gb) with /3GB and /PAE.
How much memory on that server? Ususally if you've gopt 4 GB then you would want to have the /3GB switch. If you've got more than 4 you want the /PAE (and AWE enabled). You generally shouldn't run both switches, especially if you've got more than 8GB memory as it can starve the OS kernal of memory.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 12, 2008 at 11:26 am
8GB physical RAM
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply