Yes we do have optimize for ad hoc workloads on. If it was up to me, I'd hire you and Gail Shaw today. I did pass both...

There are certainly enough things to get frustrated about -- the advice to not try and influence the optimizer is just one of them. Another is, even today, article...

Just read and re read what I wrote okay so the plan handle is used once to create the plan guide from that it gets the sequel statement.. then...

Creating a plan guide when your sql statements are very complicated is proving to be difficult but I found the store procedure that will create a plan guide from a...

More testing was showing simple parameterization alone would yield significant performance improvements, without using hints like option(recompile) that would require code changes.

When I switched to sql statements as captured in...

I'm now playing with plan guides and turning off forced parameterization in QA. So far it isn't using the plan guide -- that's a tough query to get exactly right...

Comment from a co-worker dba. We probably shouldn't have turned on forced parameterization years ago, since all application sql is parmameterized and only prod support add hoc work isn't,...

Forgot to mention, when you have huge variances in estimated/actual rows ( e.g. 600 estimated, 1.4 million actual) how likely is it that some of the work dumps...

Yes, yes and yes. And correct me if I'm wrong, but once the front end of a plan ( right side as viewed in an execution plan )...

The main question would be: Am I nuts for thinking this way? And, since for the time being we are stuck with our design and ORM-approach, has anyone "tweaked"...

Ours seem to be a mix. Sometimes I'll see all parameters defined as varchar(8000), other times there are varchar set at the string length ( varchar(23) ...

ORMs work, up until they don't. Too many tables joined, sometimes with outer joins, too many values in "IN" statements ( e.g. 50+ ), parameters declared as...

I've emailed my manager about the possibility of bringing in a consultant.

Can't seem to paste Excel in here so attached the info requested on Waits, sql configurations, etc.

namevalue_in_use

cost threshold for parallelism40

max degree of parallelism8

max server memory (MB) 458,752

min server memory (MB) 356,352

optimize for ad hoc workloads1

][font="Arial"]WaitType Wait_SResource_SSignal_SWaitCountPercentageAvgWait_SAvgRes_SAvgSig_S

CXPACKET17341361.0916919887.22421473.86133025910855.480.01300.01270.0003

PAGEIOLATCH_SH4728690.064716703.3811986.6828037161315.130.01690.01680.0000

WRITELOG2040599.381975409.1165190.272366001496.530.00860.00830.0003

LCK_M_IS 1837891.571837834.7956.77481775.8838.148738.14760.0012

LCK_M_U 1712299.881704071.818228.07276175805.480.06200.06170.0003

PAGEIOLATCH_EX941725.85940379.731346.11608315603.010.01550.01550.0000

LCK_M_IX520778.58520768.769.82185121.6728.131928.13140.0005

IO_COMPLETION392239.67388572.053667.62651472321.250.00600.00600.0001

SOS_SCHEDULER_YIELD377693.255320.11372373.1410257554441.210.00040.00000.0004[/font]

isolation etc

[font="Arial"]DBnamesnapshot_isolation_statesnapshot_isolation_state_descis_read_committed_snapshot_onrecovery_modelrecovery_model_descpage_verify_optionpage_verify_option_descis_auto_create_stats_onis_auto_update_stats_onis_auto_update_stats_async_onis_ansi_null_default_onis_ansi_nulls_onis_ansi_padding_onlog_reuse_waitlog_reuse_wait_descis_date_correlation_on

COLLATERALMANAGER0OFF11FULL2CHECKSUM1110000NOTHING0

ASPState0OFF03SIMPLE2CHECKSUM1110001CHECKPOINT0

Messaging0OFF01FULL2CHECKSUM1110002LOG_BACKUP0

Reporting0OFF11FULL2CHECKSUM1110000NOTHING0[/font]