Adhoc Query Question

Question

Adhoc Query Question

bugg

SSCertifiable

Points: 7567
More actions
October 17, 2018 at 7:09 am

#408985

Why would I get an adhoc query plan and prepared plan at same time for one query?
query:
select id, name from customers where email = 'blah@blah.com'
I get 2 plans in cache, 1 plan is adhoc and 1 is prepared
When I run the same query again adhoc count increments by 1
If i change the email i get another adhoc query but prepared plan increments by 1
bucketid refcounts usecounts size_in_bytes memory_object_address cacheobjtype objtype plan_handle 9492 2 1 16384 0x000000EC0E9D4060 Compiled Plan Adhoc 0x06000C0071351F19305693C1EB00000001000000000000000000000000000000000000000000000000000000 25166 2 1 16384 0x000000FADCBB0060 Compiled Plan Adhoc 0x06000C00CC0EEE28304793C1EB00000001000000000000000000000000000000000000000000000000000000 5446 4 2 73728 0x000000ED858CC060 Compiled Plan Prepared 0x06000C00D8E4F302904D93C1EB00000001000000000000000000000000000000000000000000000000000000

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

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

Just adding a single space to a query will cause a new plan to be generated. It's one of the reasons why it's so very important to parameterize code, especially dynamic SQL.

--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)

bugg SSCertifiable Points: 7567 More actions · Answer 2

Jeff Moden - Wednesday, October 17, 2018 7:55 AM
Just adding a single space to a query will cause a new plan to be generated. It's one of the reasons why it's so very important to parameterize code, especially dynamic SQL.

The SQL is parameterized using PDO params are binded etc.
Could it be a driver issue PHP to MSSQL?

Unsure why one execution creates a prepared plan and also an adhoc one, from one call.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

bugg - Wednesday, October 17, 2018 8:03 AM
Jeff Moden - Wednesday, October 17, 2018 7:55 AM
Just adding a single space to a query will cause a new plan to be generated. It's one of the reasons why it's so very important to parameterize code, especially dynamic SQL.
The SQL is parameterized using PDO params are binded etc.
Could it be a driver issue PHP to MSSQL?
Unsure why one execution creates a prepared plan and also an adhoc one, from one call.

I wouldn't think it to be a driver issue but I don't know for sure.

--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)

bugg SSCertifiable Points: 7567 More actions · Answer 4

On further inspection it doesn't look like one query that goes via PDO has a prepared plan in cache everything is adhoc!