Simple SQL (SET) statements taking time in our recent trace file

Question

Simple SQL (SET) statements taking time in our recent trace file

Suresh Arumugam

Hall of Fame

Points: 3370
More actions
November 11, 2011 at 3:28 pm

#144507

Hi All,
Please find the attached screen grab to see the simple SET statements in our production trace taking close to one second. Could any one of you guys please help me to understand why these statements could be taking time?
Thanks,
Suresh
Regards,
Suresh Arumugam

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

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

bkubicek SSChampion Points: 10948 More actions · Answer 1

This is just a wild guess, but if the variable you are trying to set is a decimal type perhaps a numeric(18,6) or something like that you should actually set it to 0.0 instead of 0. Otherwise you can get a performance hit on trying to convert an integer "0" to decimal "0.0"

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

Did you catch the wait types when they were running?

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

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 3

My guess would be that there is more code on other lines within the TextData column that is not showing in the grid view.

Try looking at the TextData in a text view mode.

Suresh Arumugam Hall of Fame Points: 3370 More actions · Answer 4

Thanks for the suggestions.

Could you please clarify how to check for the wait types? Sorry i am not sure of that.

Thanks,

Suresh

Regards,
Suresh Arumugam

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

I'd start with, is there more code here?

1 sec for this is extreme.

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

Ninja's_RGR'us (11/14/2011)
I'd start with, is there more code here?
1 sec for this is extreme.

However note that the CPU time is 0.

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

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

Suresh Kumar-284278 (11/14/2011)
Thanks for the suggestions.
Could you please clarify how to check for the wait types? Sorry i am not sure of that.
Thanks,
Suresh

Check sys.dm_exec_requests while they are running.

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

GilaMonster (11/14/2011)
Ninja's_RGR'us (11/14/2011)
I'd start with, is there more code here?
1 sec for this is extreme.
However note that the CPU time is 0.

Wow, I need to find myself a busy bank to learn that stuff. :Whistling:

Tx, again.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 9

GilaMonster (11/14/2011)
Ninja's_RGR'us (11/14/2011)
I'd start with, is there more code here?
1 sec for this is extreme.
However note that the CPU time is 0.

Maybe the hidden code is:

waitfor delay '00:00:01.000'

🙂

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

Michael Valentine Jones (11/14/2011)
GilaMonster (11/14/2011)
Ninja's_RGR'us (11/14/2011)
I'd start with, is there more code here?
1 sec for this is extreme.
However note that the CPU time is 0.
Maybe the hidden code is:
waitfor delay '00:00:01.000'
🙂

Not the most obvious option, but sure :hehe:.

And AFAIK, waitfor is not 100% precise to the ms so that could be possible (unless there's an unknown factor I don't know of on an dev machine with single user.)