SP_whoisactive duration vs Profiler Duration for SP_prepexec statements

Question

SP_whoisactive duration vs Profiler Duration for SP_prepexec statements

Alexp012

Old Hand

Points: 369
More actions
October 24, 2022 at 9:10 am

#4103511

Hi
I have an application that executes statements using sp_prepexec and an sp_unprepare after each statement.
In profiler a common statement will take 5 reads, no CPU and sub millisecond. When I capture the same statement in Sp_whoIsActive, it gives a duration of 4 seconds, and sometimes hundreds of thousand of reads.
Does anyone know why that happens?
BTW am I right in thinking that if you have sp_prepexec - statement - sp_unprepare, you effectively create a new execution plan each time?
Thanks
Alex
November 4, 2022 at 6:26 am

This was removed by the editor as SPAM

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

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

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 1

Sp_whoisactive is querying the DMVs, and, depending on how you run it, an aggregation of multiple runs. Profiler is capturing through trace events, one execution at a time, providing more detail. You will see discrepancies between the two, especially if you're getting an aggregation. I actually set out to measure which of these methods was the most accurate, most of them are about the same, within a few percentage points. I would strongly advocate that you use Extended Events if you want detail with the lightest load on the system.

Assuming the prepared statements are using common parameters, no, you should see plan reuse. However, depending on how they're generating their code, you might be seeing varchar(3) for 'cat' and varchar(5) for 'horse' when the actual column is varchar(50) (or whatever). In that case, yeah, you're going to see tons of different plans. Just make sure everyone instantiates their parameters to the column data type, not the data length of the input (which is the "hello world" example for many ORM tools).

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Alexp012 Old Hand Points: 369 More actions · Answer 2

Thanks for the reply Grant.

I read here, that sp_unprepare would discard the execution plan.

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-unprepare-transact-sql?view=sql-server-ver16

In profiler I see an sp_unprepare run after every sp_prepexec statement won't that mean there isn't a plan to reuse, or have I misunderstood sp_unprepare?

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

Oh! Missed that. Sorry. Yeah, you're hammering your CPU with that. I'd strongly suggest stopping.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning