OPTION (SHRINKDB PLAN)

Question

OPTION (SHRINKDB PLAN)

andrewkane17

SSCertifiable

Points: 7529
More actions
May 10, 2011 at 10:53 am

#250041

I am currently shrinking a DB after compressing a bunch of big tables, I know the shrink will cause fragmentation and will schedule reindex(s) when it finishes.
What is weird though is that I am seeing the below query in cache as the biggest hitter. I saw the %%BMK%% peice while doing the compression and assumed it was something internal to that. But I now see the hint: OPTION (SHRINKDB PLAN) I am just curious if anyone has any ideas on this, did the usual interwebs search and didn't find anaything?
UPDATE [dbo].[MyTable] WITH (PAGLOCK)
SET @p = 1
WHERE %%BMK%% IN (@p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15, @p16,
@p17, @p18, @p19, @p20, @p21, @p22, @p23, @p24, @p25, @p26, @p27, @p28, @p29, @p30, @p31, @p32, @p33, @p34,
@p35, @p36, @p37, @p38, @p39, @p40, @p41, @p42, @p43, @p44, @p45, @p46, @p47, @p48, @p49, @p50, @p51, @p52,
@p53, @p54, @p55, @p56, @p57, @p58, @p59, @p60, @p61, @p62, @p63, @p64, @p65, @p66, @p67, @p68, @p69, @p70,
@p71, @p72, @p73, @P74, @p75, @p76, @p77, @p78, @p79, @p80, @p81, @p82, @p83, @p84, @p85, @p86, @p87, @p88,
@p89, @p90, @p91, @p92, @p93, @p94, @p95, @p96, @p97, @p98, @p99, @p100)
OPTION (SHRINKDB PLAN)
Andrew

Viewing 3 posts - 1 through 2 (of 2 total)

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

Scott H Old Hand Points: 332 More actions · Answer 1

Morning Andrew,

This is something I'm curious about as well, as I encountered exactly the same thing after another DBA tried to shrink an unused DB before backing it up for archiving.

Although I've never seen the OPTION (SHRINKDB PLAN) query hint before, there's not much on the net about it either, I think it somehow suppresses the execution plan from being displayed or recorded (perhaps to prevent it from revealing too much about the inner workings of SQL server?).

Interestingly, when tested it appears that this command is only designed to work with DBCC commands, as I got the following error when I ran the code below.

Msg 1003, Level 15, State 2, Line 3

Line 3: SHRINKDB clause allowed only for DBCC.

CREATE TABLE #temp

( col1 int )

INSERT INTO #temp VALUES (1),(2),(3)

SELECT *

FROM #temp

OPTION (SHRINKDB PLAN)

UPDATE #temp

SET col1 = 4

WHERE col1 = 1

OPTION (SHRINKDB PLAN)

Additionally, from what I've seen/read/searched on the net, everything points to the code actually being part of the shrink mechanism used by SQL server, and that the code itself tries to get an page-level exclusive lock sequentially on every object (otherwise wouldn't it just get a DB-Level lock, rather than the page-level lock noted in the SQL above?)

Out of curiosity, how frequently have you seen this error appear? Today was the first time I've noticed it, but that doesn't mean it's not happened before.

Thanks

[Edit]

My colleague Rhys Campbell just got this response from Paul Randall re OPTION (SHRINKDB PLAN)...

It's internal use only syntax to help when shrink has to move heap records around

andrewkane17 SSCertifiable Points: 7529 More actions · Answer 2

Scott,

Thanks for the reply. I saw this when I was doing a shrink, I was just curious as to what it was, I had an idea it was an internal thing not documented.

Andrew