GilaMonster (2/5/2013)
SQLRNNR (2/5/2013)
If you have a ton of one off types of queries - this can be helpful. The downside is that you may start seeing parameter sniffing and longer running queries.Optimise for ad-hoc can't cause parameter sniffing where there was none before.
I'll agree on that. Sometimes though the param sniffing isn't made evident until adhoc workload is enabled. Could be a coincidence and I haven't dived deeply into it. I just ran into a case in the last two weeks and then ran into another DBA last week that experienced the same thing.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events