High CPU, Low Reads

Question

Post reply

High CPU, Low Reads

yasubmj

Mr or Mrs. 500

Points: 592
More actions
June 16, 2011 at 11:58 pm

#389759

Hi,
I recently worked on tuning a stored procedure for performance. Here are the perf details of the sproc before tuning:
--CPU 125, Reads 23936, Writes 1, Duration 1656
After tuning:
--CPU 140, Reads 6229, Writes 7, Duration 636
Its strange that the reads have come down and so has the duration but the CPU has gone up.
Both sprocs were run within a minute of each other and have been consistently showing such a difference. Can someone please guide me on this?
PS: In the tuned sproc i have localised the parameters to disable sniffing.
Regards - Yasub

Viewing 11 posts - 1 through 10 (of 10 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Without seeing the code, no.

Was parameter sniffing a problem? If not, you shouldn't disable it.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

yasubmj Mr or Mrs. 500 Points: 592 More actions · Answer 2

Hi,

Thanks for your reply, there was sniffing, had no choice but to disable it within the sproc.

Was working on another perf issue, the reads are down but the duration is still high....

Original Sproc:

--Cpu 234, Reads 9945, Writes 8, Duration 22378

Tuned sproc:

--Cpu 468, Reads 3662, Writes 8, Duration 19708

Again the reads are down but the duration is almost the same and the CPU is up!

Regards - Yasub

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Good luck.

If you want help, you're going to have to post a lot more detail than the performance stats.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

yasubmj Mr or Mrs. 500 Points: 592 More actions · Answer 4

Hi,

Here is the code in the sp which takes a lot of time, around 17 secs, the reads are only 1303,

SELECT

col1, col2, col3....,

(CASE @LocDBSortOrder

WHEN 'DESC' THEN

CASE @LocstrSortExpression

WHEN 'x' THEN ROW_NUMBER() OVER(ORDER BY x DESC)

WHEN 'y' THEN ROW_NUMBER() OVER(ORDER BY y DESC)

WHEN 'z' THEN ROW_NUMBER() OVER(ORDER BY z DESC)

END

WHEN 'ASC' THEN

CASE @LocstrSortExpression

WHEN 'x' THEN ROW_NUMBER() OVER(ORDER BY x DESC)

WHEN 'y' THEN ROW_NUMBER() OVER(ORDER BY y DESC)

WHEN 'z' THEN ROW_NUMBER() OVER(ORDER BY z DESC)

END

END)AS ROWNUM, COUNT(1) OVER() AS TotalNoOfRecords

FROM

table C

INNER JOIN #tblTemp2 t2 ON t2.td = C.id

WHERE

C.Status IN (2,4)

AND C.IsDeleted=0

There are no scans in the plan for this particular query in the sproc, the sorting cost is also minimal.

Regards - Yasub

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

yasubmj (6/17/2011)
Hi,
Here is the code in the sp which takes a lot of time, around 17 secs, the reads are only 1303,
SELECT
col1, col2, col3....,
(CASE @LocDBSortOrder
WHEN 'DESC' THEN
CASE @LocstrSortExpression
WHEN 'x' THEN ROW_NUMBER() OVER(ORDER BY x DESC)
WHEN 'y' THEN ROW_NUMBER() OVER(ORDER BY y DESC)
WHEN 'z' THEN ROW_NUMBER() OVER(ORDER BY z DESC)
END
WHEN [font="Arial Black"]'ASC' [/font]THEN
CASE @LocstrSortExpression
WHEN 'x' THEN ROW_NUMBER() OVER(ORDER BY x [font="Arial Black"]DESC[/font])
WHEN 'y' THEN ROW_NUMBER() OVER(ORDER BY y [font="Arial Black"]DESC[/font])
WHEN 'z' THEN ROW_NUMBER() OVER(ORDER BY z [font="Arial Black"]DESC[/font])
END
END)AS ROWNUM, COUNT(1) OVER() AS TotalNoOfRecords
FROM
table C
INNER JOIN #tblTemp2 t2 ON t2.td = C.id
WHERE
C.Status IN (2,4)
AND C.IsDeleted=0
There are no scans in the plan for this particular query in the sproc, the sorting cost is also minimal.
Regards - Yasub
Regards - Yasub

I could certainly be wrong, but it would appear that script has a much larger functional problem. Please see where I highlighted above in Red.

Also, the use of a PARTITION clause in the ROW_NUMBER() OVER's may eliminate the need for all but one CASE statement.

How many rows does this table have?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

yasubmj Mr or Mrs. 500 Points: 592 More actions · Answer 7

Hi Jeff,

Thanks for your reply. Sorry about the code, its a copy paste error, when i was trying to make the dummy code for this post, please ignore that functional defect, its fine in the sproc.

The table has around 300,000 rows and there is a NC on the joining column. The index is upto date with statistics and there is minimal fragmentation (<5%).

We have event notification setup in case of blocking and there no such thing going on.

Regards - Yasub

yasubmj Mr or Mrs. 500 Points: 592 More actions · Answer 8

Hi Jeff,

You said that adding 'partition by' will reduce it only to one case statement, Not able to translate that into a query, can you please help?

Regards - Yasub

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 9

Yasub,

My apologies... :blush: I didn't read your original query quite right. Adding PARTION BY will buy us nothing in this case.

As a bit of a side bar, it is possible that converting this query to dynamic SQL (with consideration for SQL Injection attacks) will make the code run faster but you'd have to try it.

Either way, keep in mind that returning a large number of rows to the display is the "great equalizer" and can make even great code seem sluggish.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 10

I agree with Gail. If you really want us to help you understand what you observed you need to give us details about before and after versions of the sproc (and perhaps indexing if you changed that) and how you got from point A to point B. My guess is you switched from some loop joins to hash joins. That would have reads go way down but CPU not.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service