Snippet from the Microsoft Biztalk whitepaper on lock preallocation. https://msdn.microsoft.com/en-us/library/Bb743398(v=BTS.10).aspx#BKMK_SQLSvrTuning

Pre-allocating SQL Server memory has many advantages. The overhead of dynamic management, particularly under high stress growth, is avoided. Pre-allocated memory...

Yes, but that is instantaneous, I want to aggregate the impact over time. And yes I could insert dm_exec_reguests...

Thanks for the quick response!

I was thinking if I wanted to try a bit more sophistication on a per filegroup basis: allocate a new filegroup, sized correctly based on sp_spaceused...

So that's exactly what I'm trying to sort out. The inline function had 2000 page reads so appears to be RBAR, but less CPU. Harder to tell with...

Apologies, I gathered these around midnight local time, and this simple idea of a single file completely escaped...

Scripts attached. Did you want ALL indexes for EACH monthly shard as well? They should all be identical. I have only included JUL2013 indexes, but here are...

Got it, thanks for clarifying. I was missing the fact my simple test is not actually reproducing the problem behavior.

I think we're now saying the same thing, and admittedly I am probably abusing the word "logical", but here's what I mean.

If I open a new query window and run...

I think I have it figured out. I needed to understand that sys.dm_tran_active_transactions shows logical transactions, and sys.dm_tran_database_transactions is showing actual "physical presence" of transactions. As such I...

Thanks! So that would explain my busted testing methodology.

Maybe I've made some faulty assumptions about the root cause of the lack of log truncation. What I was seeing...

TheSQLGuru (7/16/2013)

---

I think another of the root causes is that DPVs just never got any real loving from the product team and haven't had much attention in many years I...

The optimizer is completing with FULL optimization, and the plan hashes match on subsequent runs.

I think the FORCESEEK hint is the what got me over the hump and is of...

UPDATE: For anyone that may encounter a similar issue.

I've dug a bit deeper, and I think what is happening is that sometimes the generated query plan undergoes partition elimination...

I appreciate the response, and yes I don't expect someone to volunteer for a remote DBA session and solve my problem for free. I just tried to flesh out...

Syed Jahanzaib Bin hassan (4/20/2011)

---

MetaData should be MS SQL Server 2005 or Later because SQL Server 2000 is not a schema base database

and use this new JDBC driver 2.0 by...