Help With Query

Question

Post reply

Help With Query

curious_sqldba

SSC-Dedicated

Points: 36494
More actions
November 20, 2015 at 3:35 pm

#305481

I want to get total CPU used by all the sql agent jobs, i am not sure how would i do a group on a column with like condition? Also would like to know what % of CPU is used by sqlagent with respect to total CPU. Below is my sample data and schema. Thanks in advance
Create table Query_Test
(CPU int,
ApplicationName varchar(100),
LoginName varchar(100)
)
Insert into Query_Test
values (1222,'.net','User1')
Insert into Query_Test
values (1223,'.net','User2')
Insert into Query_Test
values (1224,'SQLagent123','User1')
Insert into Query_Test
values (1225,'SQLagent1233434','User2')
Insert into Query_Test
values (1226,'SQLagent123222','User1')
Insert into Query_Test
values (1227,'SQLagent1237989','User5')
Insert into Query_Test
values (1228,'SQLagent1230900','User1')
Insert into Query_Test
values (1229,'SQLagent123ABC','User34')
Insert into Query_Test
values (12200,'.net','User1')
Insert into Query_Test
values (122244,'SQLagent1233467','User1')
Insert into Query_Test
values (1222455,'SQLagent123rtrtet','User1')
Insert into Query_Test
values (1222,'SQLagent123aeerer','User1')
Insert into Query_Test
values (1222,'SQLagent123werwerqwe','User1')
Insert into Query_Test
values (1222,'SQLagent1235778','User1')
Insert into Query_Test
values (1222,'SQLagent123ABCNDDRTR','User1')

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 1

Perhaps something like this?

CREATE TABLE Query_Test

(

CPU INT ,

ApplicationName VARCHAR(100) ,

LoginName VARCHAR(100)

);

INSERT INTO Query_Test

VALUES ( 1222, '.net', 'User1' );

INSERT INTO Query_Test

VALUES ( 1223, '.net', 'User2' );

INSERT INTO Query_Test

VALUES ( 1224, 'SQLagent123', 'User1' );

INSERT INTO Query_Test

VALUES ( 1225, 'SQLagent1233434', 'User2' );

INSERT INTO Query_Test

VALUES ( 1226, 'SQLagent123222', 'User1' );

INSERT INTO Query_Test

VALUES ( 1227, 'SQLagent1237989', 'User5' );

INSERT INTO Query_Test

VALUES ( 1228, 'SQLagent1230900', 'User1' );

INSERT INTO Query_Test

VALUES ( 1229, 'SQLagent123ABC', 'User34' );

INSERT INTO Query_Test

VALUES ( 12200, '.net', 'User1' );

INSERT INTO Query_Test

VALUES ( 122244, 'SQLagent1233467', 'User1' );

INSERT INTO Query_Test

VALUES ( 1222455, 'SQLagent123rtrtet', 'User1' );

INSERT INTO Query_Test

VALUES ( 1222, 'SQLagent123aeerer', 'User1' );

INSERT INTO Query_Test

VALUES ( 1222, 'SQLagent123werwerqwe', 'User1' );

INSERT INTO Query_Test

VALUES ( 1222, 'SQLagent1235778', 'User1' );

INSERT INTO Query_Test

VALUES ( 1222, 'SQLagent123ABCNDDRTR', 'User1' );

--First solution I thought of. Based on some quick tests not nearly as efficient as my second idea.

WITH CTE

AS ( SELECT CASE WHEN ApplicationName LIKE 'SQLagent%' THEN 1

ELSE 0

END AS IsAgent ,

CPU ,

SUM(CPU) OVER ( PARTITION BY NULL ) AS TotalCPU

FROM dbo.Query_Test

)

SELECT SUM(CPU) AS TotalAgentCPU ,

SUM(CPU * 1.0 / TotalCPU) AS PercentAgentCPU

FROM CTE

WHERE CTE.IsAgent = 1;

--My second, seemingly better idea

SELECT TotalAgentCPU = SUM(CASE WHEN ApplicationName LIKE 'SQLagent%'

THEN CPU

ELSE 0

END) ,

PercentAgentCPU = ( 1.0

* SUM(CASE WHEN ApplicationName LIKE 'SQLagent%'

THEN CPU

ELSE 0

END) ) / SUM(CPU)

FROM dbo.Query_Test;

DROP TABLE dbo.Query_Test;

Cheers!

EDIT: I added a second solution, because I had a hunch the first way I thought of wasn't very efficient. Some quick tests indicate the second method is significantly more efficient, and on general grounds it should be much more efficient since it doesn't hit a worktable over and over,but the testing wasn't all that rigorous, so pinches of salt and all that.

Alan Burstein SSC Guru Points: 61141 More actions · Answer 2

Jacob Wilkins (11/20/2015)
Perhaps something like this?
CREATE TABLE Query_Test
(
CPU INT ,
ApplicationName VARCHAR(100) ,
LoginName VARCHAR(100)
);
INSERT INTO Query_Test
VALUES ( 1222, '.net', 'User1' );
INSERT INTO Query_Test
VALUES ( 1223, '.net', 'User2' );
INSERT INTO Query_Test
VALUES ( 1224, 'SQLagent123', 'User1' );
INSERT INTO Query_Test
VALUES ( 1225, 'SQLagent1233434', 'User2' );
INSERT INTO Query_Test
VALUES ( 1226, 'SQLagent123222', 'User1' );
INSERT INTO Query_Test
VALUES ( 1227, 'SQLagent1237989', 'User5' );
INSERT INTO Query_Test
VALUES ( 1228, 'SQLagent1230900', 'User1' );
INSERT INTO Query_Test
VALUES ( 1229, 'SQLagent123ABC', 'User34' );
INSERT INTO Query_Test
VALUES ( 12200, '.net', 'User1' );
INSERT INTO Query_Test
VALUES ( 122244, 'SQLagent1233467', 'User1' );
INSERT INTO Query_Test
VALUES ( 1222455, 'SQLagent123rtrtet', 'User1' );
INSERT INTO Query_Test
VALUES ( 1222, 'SQLagent123aeerer', 'User1' );
INSERT INTO Query_Test
VALUES ( 1222, 'SQLagent123werwerqwe', 'User1' );
INSERT INTO Query_Test
VALUES ( 1222, 'SQLagent1235778', 'User1' );
INSERT INTO Query_Test
VALUES ( 1222, 'SQLagent123ABCNDDRTR', 'User1' );
--First solution I thought of. Based on some quick tests not nearly as efficient as my second idea.
WITH CTE
AS ( SELECT CASE WHEN ApplicationName LIKE 'SQLagent%' THEN 1
ELSE 0
END AS IsAgent ,
CPU ,
SUM(CPU) OVER ( PARTITION BY NULL ) AS TotalCPU
FROM dbo.Query_Test
)
SELECT SUM(CPU) AS TotalAgentCPU ,
SUM(CPU * 1.0 / TotalCPU) AS PercentAgentCPU
FROM CTE
WHERE CTE.IsAgent = 1;
--My second, seemingly better idea
SELECT TotalAgentCPU = SUM(CASE WHEN ApplicationName LIKE 'SQLagent%'
THEN CPU
ELSE 0
END) ,
PercentAgentCPU = ( 1.0
* SUM(CASE WHEN ApplicationName LIKE 'SQLagent%'
THEN CPU
ELSE 0
END) ) / SUM(CPU)
FROM dbo.Query_Test;
DROP TABLE dbo.Query_Test;
Cheers!
EDIT: I added a second solution, because I had a hunch the first way I thought of wasn't very efficient. Some quick tests indicate the second method is significantly more efficient, and on general grounds it should be much more efficient since it doesn't hit a worktable over and over,but the testing wasn't all that rigorous, so pinches of salt and all that.

Excellent solution sir. I would suggest avoiding Window Aggregate functions at all costs though, Dwain Camps wrote a good article:

The Performance of the T-SQL Window Functions[/url] and Window Aggregate Functions are a bummer.

Below is a comparison that looks at the I/O of a similar solution that does not use Window Aggregates:

SET NOCOUNT ON;

SET STATISTICS IO ON;

PRINT 'Solution without Window Aggregate Functions';

WITH TACPU AS

(

SELECT CPU = (SELECT SUM(CPU) AS TotalCPU FROM dbo.Query_Test)

)

SELECT TotalAgentCPU, PercentAgentCPU = (1.*TotalAgentCPU)/CPU

FROM TACPU

CROSS APPLY

(

SELECT SUM(CPU) AS TotalAgentCPU

FROM dbo.Query_Test

WHERE ApplicationName LIKE 'SQLagent%'

) agent ;

PRINT CHAR(13)+'Solution with Window Aggregate Function';

WITH CTE

AS ( SELECT CASE WHEN ApplicationName LIKE 'SQLagent%' THEN 1

ELSE 0

END AS IsAgent ,

CPU ,

SUM(CPU) OVER ( PARTITION BY NULL ) AS TotalCPU

FROM dbo.Query_Test

)

SELECT SUM(CPU) AS TotalAgentCPU ,

SUM(CPU * 1.0 / TotalCPU) AS PercentAgentCPU

FROM CTE

WHERE CTE.IsAgent = 1;

SET STATISTICS IO OFF

Results:

Solution without Window Aggregate Functions

Table 'Query_Test'. Scan count 2, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Solution with Window Aggregate Function

Table 'Worktable'. Scan count 3, logical reads 35, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Query_Test'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

The window aggregate function requires a work table in the temp DB. Going with an old-school GROUP by with a CROSS JOIN we removed the worktable, reduced the scans from 4 to 2, and went from 36 reads to 2. Cudo's to Dwain for his article. 😎

Edit: Looks like you beat me to it and came up with a better solution, notably better than what I posted reducing the scans to 1 and reads to 1. I need to remember the CROSS TAB approach next time.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001