This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing one aggregation script to find the total number of occupied connections and CPU usage by each SQL Server client or programs.
This is a very important script for SQL Server DBAs. Using this script, they can find information like ProgramName, TotalOpenConnections and Usage Of CPU for each Program which are connected to SQL Server Database.
When you are facing the overhead connection problem, you can easily find the total number of connections occupied by each program or clients of SQL Server. Later, it helps you to debug your problem in the proper area.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT convert(varchar(50), program_name) as ProgramName ,count(*) as TotalInstances ,sum(cpu) as CPUSum ,sum(datediff(second, login_time, getdate())) as SumOfSecond ,convert(float, sum(cpu)) / convert(float, sum(datediff(second, login_time, getdate()))) as PerformanceScore ,convert(float, sum(cpu)) / convert(float, sum(datediff(second, login_time, getdate()))) / count(*) as ProgramPerformance FROM master..sysprocesses WHERE spid > 50 GROUP BY convert(varchar(50), program_name) ORDER BY PerformanceScore DESC |
