My procedure is taking more CPU time : sys.dm_exec_requests

Question

My procedure is taking more CPU time : sys.dm_exec_requests

kbhanu15

SSCarpal Tunnel

Points: 4493
More actions
February 24, 2014 at 6:57 am

#289867

hi
my procedure is taking more cpu time, how to reduce this.
select r.total_elapsed_time, r.cpu_time, r.logical_reads, r.reads, r.writes
from sys.dm_exec_requests r (nolock)
where r.session_id = @@spid
total_elapsed_timecpu_timelogical_readsreads writes
140371170373451470 53
my question is : how to redece this cpu_time.
please provide the possible optioins for this.

Viewing 6 posts - 1 through 5 (of 5 total)

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

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

Tune the query, tune the indexes. Without more information that's about all that can be said.

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

stormsentinelcammy SSCrazy Points: 2493 More actions · Answer 2

GilaMonster (2/24/2014)
Tune the query, tune the indexes. Without more information that's about all that can be said.

Also, if you have an inline table switch to a temp table so that you can get a true execution plan...gila is right, unless you provide more information there's not much to add...

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

stormsentinelcammy (2/24/2014)
GilaMonster (2/24/2014)
Tune the query, tune the indexes. Without more information that's about all that can be said.
Also, if you have an inline table switch to a temp table so that you can get a true execution plan

No problems with inline tables (derived tables) and execution plans.

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

stormsentinelcammy SSCrazy Points: 2493 More actions · Answer 4

GilaMonster (2/24/2014)
stormsentinelcammy (2/24/2014)
GilaMonster (2/24/2014)
Tune the query, tune the indexes. Without more information that's about all that can be said.
Also, if you have an inline table switch to a temp table so that you can get a true execution plan
No problems with inline tables (derived tables) and execution plans.

apologies, I meant to say table variables 😛

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 5

Check the execution plan and see if it needs any index or maintenance.

--

SQLBuddy