We purchased new hardware (HP 980) with 32 cores, 50 GB RAM with multiple FusionIO cards (to house database files) to run SQL 2008 R2 Enterprise. We’re leveraging SQL mirroring. This physical server is the mirror node.
Long story short, after many rebuilds from the OS up, SQL performance remained degraded. Systematically, we began to rule out what was causing the issue.
At the end of the day, we added trace flag –T834 (causes SQL Server to use Microsoft Windows large-page allocations for the memory that is allocated for the buffer pool). Lock pages in memory had already been configured.
Well, long story short, the performance of the SQL server immediately began to improve.
Interesting fact, we have dozens of other high performance SQL instances (clustered, stand-alone, etc.), (SAN storage, other FusionIO cards) and have NOT had to set the –T834 trace flag on any additional SQL instances.
If you’ve had to set the T834 flag, please let me know.