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: Yesterday @ 7:18 AM
Points: 1,247, Visits: 9,875
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: Saturday, September 13, 2014 8:01 AM
Points: 11,194, Visits: 11,165
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
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 1:51 AM
Points: 25, Visits: 1,284
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: Saturday, September 13, 2014 8:01 AM
Points: 11,194, Visits: 11,165
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
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 1:51 AM
Points: 25, Visits: 1,284
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: Saturday, September 13, 2014 8:01 AM
Points: 11,194, Visits: 11,165
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: Today @ 5:18 AM
Points: 42,995, Visits: 36,152
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
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 1:51 AM
Points: 25, Visits: 1,284
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