Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

STATEMENT LEVEL RECOMPILATION Expand / Collapse
Author
Message
Posted Wednesday, March 24, 2010 1:23 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 21, 2010 5:56 AM
Points: 21, Visits: 73
HELLO TEAM, I need a help on statement level recompile (syntax level) that can implement in sql server 2005/2008
Post #888720
Posted Wednesday, March 24, 2010 3:43 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 10:09 AM
Points: 1,207, Visits: 9,333
Hi,

Probably best off looking up Query Hints in BOL:

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

It's pretty straight forward
Post #888774
Posted Wednesday, March 24, 2010 5:34 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 5:59 AM
Points: 11,168, Visits: 10,931
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #888829
Posted Wednesday, March 24, 2010 5:56 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 21, 2010 5:56 AM
Points: 21, Visits: 73
Thanks!!!

Still I could n't the thread of Statement Level Recompilation in the specified link
Post #888839
Posted Wednesday, March 24, 2010 7:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 2:58 AM
Points: 24, Visits: 1,176
Maybe you are looking for something like this:

SELECT TOP (10) *
FROM table
OPTION (HASH Join , recompile)
-- or just simple: OPTION (recompile)

Post #888913
Posted Wednesday, March 24, 2010 7:16 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 5:59 AM
Points: 11,168, Visits: 10,931
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #888934
Posted Wednesday, March 24, 2010 7:23 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 2:58 AM
Points: 24, Visits: 1,176
In my own query I had a few more tables, but it can used just like I said OPTION (Recompile)
Post #888941
Posted Wednesday, March 24, 2010 7:30 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 5:59 AM
Points: 11,168, Visits: 10,931
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...?




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #888952
Posted Wednesday, March 24, 2010 7:34 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 12:20 PM
Points: 41,529, Visits: 34,445
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 2008, MVP
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

Post #888959
Posted Wednesday, March 24, 2010 7:49 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 2:58 AM
Points: 24, Visits: 1,176
We have some data warehouse applications and some tables which are not indexed etc , in such cases we use it.
Post #888972
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse