April 18, 2007 at 2:32 pm
Hello, I am running sql 7.0 and I watch my performance monitor daily. I also run a query to look at sysprocesses throughout the day. In the last few months we have been having slowness on the floor. I never have blocking but I see a lot of WriteLog wait types. Does this indicate bottleneck? Also, I have spids that hang around for a while, but not all of them. I have 150 users that are contantly making/dropping sql connections but at any given time i have about 20 spids (not including sa/sqlserveragent ones) and if you run dbcc inputbuffer on them, most will be empty. Is this a problem?
Thanks in advance!
April 19, 2007 at 2:51 am
What's the wait time on those writelog waits?
Is the log on a dedicated drive?
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
April 19, 2007 at 8:14 am
Not sure if I am providing the correct info but the last_batch indicates the spid has been open anywhere from a few seconds to 20 minutes, the cmd will be AWAITING COMMAND and the status will say sleeping but the spid still remains. It eventually dies but not sure why a select few do this because i would bet there are 100's of connections made every second or so so it isn't every spid "hanging around". It isn't the same user each time either. Another layer is that we are using Citrix thin clients for my users to connect to sql through vb6. Unfortunately (because of financial restraints) my sql server is 3 drives 76 G, raid 5, scsi ultra 320's, but my database, backups, and transaction logs are all on this drive. I am concerned that i have an i/o bottleneck and have read a lot of things but can't get a clear way to determine where the problem may lie...thanks, I hope I answered your question...
April 19, 2007 at 8:36 am
A connection will stay open until it's explicitly closed. SQL doesn't close them automatically. If they're awaiting command, then they are idle.
There's a column in sysprocesses waittime and another lastwaittype. For the connections that have a lastwaittype of writelog, what are the wait times?
With the log on the same drive as the data file, you're going to see longer than normal waits on log writes while the drive system repositions the drive head for the log write
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
April 19, 2007 at 12:32 pm
I have been running the query looking at waittimes and they are all zeros, so I guess this not a concern although I do explicit close connections on each connection otherwise I would have many connections so I'm still not sure why certain ones hang around although not forever, they eventually die but not when I close the connection?.? Is there anything else to look at for slowness where I don't have blocking or locking? It just seems that randomly throughout the day the server slows down and i see my spids number rise but I can't see anyone doing anything "heavy" and there is no blocking..??
April 19, 2007 at 10:55 pm
Got connection pooling in place? That would account for the remaining connections. Reusing an open connection is faster than opening a new.
As for the slow down, there could be hundreds of reasons. I'd suggest you start by profiling the box for a day and see what queries are running during the slow period. that should give you some idea where to start.
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply