STATEMENT LEVEL RECOMPILATION

Question

Post reply

STATEMENT LEVEL RECOMPILATION

Anu-923127

SSC-Addicted

Points: 499
More actions
March 24, 2010 at 1:23 am

#218484

HELLO TEAM, I need a help on statement level recompile (syntax level) that can implement in sql server 2005/2008

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

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

HowardW One Orange Chip Points: 29109 More actions · Answer 1

Hi,

Probably best off looking up Query Hints in BOL:

http://msdn.microsoft.com/en-us/library/ms181714.aspx

It's pretty straight forward

Paul White SSC Guru Points: 150467 More actions · Answer 2

Everything anyone ever needs to know about compilation and recompilation:

http://msdn.microsoft.com/en-us/library/cc966425.aspx#XSLTsection127121120120

That link will take you to the section on statement-level recompilation.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Anu-923127 SSC-Addicted Points: 499 More actions · Answer 3

Thanks!!!

Still I could n't the thread of Statement Level Recompilation in the specified link

r.dragoi-1095738 SSCrazy Points: 2022 More actions · Answer 4

Maybe you are looking for something like this:

SELECT TOP (10) *

FROM table

OPTION (HASH Join , recompile)

-- or just simple: OPTION (recompile)

Paul White SSC Guru Points: 150467 More actions · Answer 5

r.dragoi-1095738 (3/24/2010)
Maybe you are looking for something like this:
SELECT TOP (10) *
FROM table
OPTION (HASH Join , recompile)
-- or just simple: OPTION (recompile)

What's the HASH JOIN hint for? There's no join.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

r.dragoi-1095738 SSCrazy Points: 2022 More actions · Answer 6

In my own query I had a few more tables, but it can used just like I said OPTION (Recompile)

Paul White SSC Guru Points: 150467 More actions · Answer 7

Anu-923127 (3/24/2010)
Still I could n't the thread of Statement Level Recompilation in the specified link

The link takes you straight to it...? :unsure:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

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

r.dragoi-1095738 (3/24/2010)
In my own query I had a few more tables, but it can used just like I said OPTION (Recompile)

Still begs the question, why are you forcing hash joins?

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

r.dragoi-1095738 SSCrazy Points: 2022 More actions · Answer 9

We have some data warehouse applications and some tables which are not indexed etc , in such cases we use it.

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

r.dragoi-1095738 (3/24/2010)
We have some data warehouse applications and some tables which are not indexed etc , in such cases we use it.

Um..... ok......

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