May 31, 2017 at 12:18 pm
Good afternoon,
I really working on tuning one our production servers. By the counters and logs, I have it doing really well.
The batch request/sec is around 220.
The average disk queue length is 0.
The processor time is .477
With all of the rebuilding of indexes and adding new statistics, it looks ok. Then I executed sp_who2 and I see about 69 connections. Some of the connections have a very high cputime and diskio. They seem to be growing?
1. Why do I have connections with high cputime and diski0 and they are sleeping?
2. I checked dbcc opentran and there are no open transactions.
There are 8 cpus, 16 GB, windows server 12R2 and SQL Server 2016.
Any thoughts?
Thanks.
Tony
Things will work out. Get back up, change some parameters and recode.
May 31, 2017 at 12:42 pm
WebTechie - Wednesday, May 31, 2017 12:18 PMGood afternoon,I really working on tuning one our production servers. By the counters and logs, I have it doing really well.
The batch request/sec is around 220.
The average disk queue length is 0.
The processor time is .477With all of the rebuilding of indexes and adding new statistics, it looks ok. Then I executed sp_who2 and I see about 69 connections. Some of the connections have a very high cputime and diskio. They seem to be growing?
1. Why do I have connections with high cputime and diski0 and they are sleeping?
2. I checked dbcc opentran and there are no open transactions.There are 8 cpus, 16 GB, windows server 12R2 and SQL Server 2016.
Any thoughts?
Thanks.
Tony
cpu and io are cummulative values - did you check LastBatch time?
There are many reasons you could be seeing this. Are they processes that could be doing work, then sleeping then doing work? Are they system or user processes? If you are using sp_who2, do you know anything about the ProgramName listed? Did you look at what the sessions last executed? You could check by just doing dbcc inputbuffer.
Sue
May 31, 2017 at 1:03 pm
Hello,
It is a production floor with several pcs running the same application. Each time an employee scan a product at their station, the database keeps updated.
So yes, I recognize the programname. I would imagine then that the VB6 code in this custom app is not sending back the commit transaction as it should and SQL Server is still waiting for it. Is that right?
I can understand that. However, why does the CPUTime and DISKio keep increasing? Is that degrading performance at the server level. Which. as I said earlier, I am not seeing a problem.
Just odd and thought I need to learn what is going on.
Thanks a lot for your thoughts.
Things will work out. Get back up, change some parameters and recode.
May 31, 2017 at 1:15 pm
So yes, I recognize the programname. I would imagine then that the VB6 code in this custom app is not sending back the commit transaction as it should and SQL Server is still waiting for it. Is that right?
You would have seen the opentran if there was no commit on the transaction.
The app is probably just reusing the same sessions for it's processing. What likely happens is that each time a scan is done, the session does some work. So you see the values increasing. So the earlier scenario I mentioned - does some work, then sleeps, the does some work, then sleeps. It's not at all unusual to see that kind of a pattern.
You could always trace a session for awhile to see what it is doing. Or you could just watch the LastBatch times for one and do a dbcc inputbuffer after the times change to see what it's doing. Tracing or extended events to monitor a process is a good way to see what the application does regularly.
Sue
May 31, 2017 at 1:25 pm
Having connections that are sleeping is not necessarily a bad thing, there might be connection pooling going on, where an application doesn't physically close the database connection, just leaves it in an inactive state, so that the next request doesn't need to take the time to reconnect. For a classic client server program, the programmer may only be closing the database connection at program exit time instead of after each request finishes.
You can see if there are open transactions like this:
--users sessions not executing anything but have open transactions
SELECT *
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
AND open_transaction_count > 0
May 31, 2017 at 2:29 pm
WebTechie - Wednesday, May 31, 2017 1:03 PMHowever, why does the CPUTime and DISKio keep increasing?
Because the CPU and DiskIO are cumulative, so if the app makes a connection, runs a query, leaves the connection open, runs another query a minute later, repeat for the whole day, the CPU and DiskIO are the total since the connection was established and if you look between the queries running, you see the connection sleeping
Is that degrading performance at the server level.
No more than any queries running against the server degrade performance. There's nothing unusual in what you're seeing.
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
May 31, 2017 at 2:47 pm
Thank you both. I thought I had done a good job with my performance tuning. When I saw those connections with high CPUtime and Diskio, it made be question my tuning.
Thanks for the clarification. I've looked at the program. When the user logs into the app it does a check for an open connection to the database. If the state is not opened, it opens a connection. It keeps it open until the app is closed at the end of the day!
Again, thanks for the clarification.
Tony
Things will work out. Get back up, change some parameters and recode.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply