I have an issue where I need physical proof to convince the Development manager about SQL Server 2005 Standard SP2 (yes they are still needing to upgrade to SP3) 64bit memory capping on our production server.
The server has 32GB RAM and before it was capped and after the dev team applied a huge bulk load on one of the dbs on that server SQL greedily took all the memory so that the OS almost refused to work. Obviously not much proof needed here, however I want to run three tests that will show how SQL utilizes memory.
I am proposing to run a three tests so during the same bulk data load:
1) SQL memory not capped (SQL will take it all, so not much proof needed here)
2) SQL memory capped to 24GB
3) SQL memory capped to 29GB
I know I can use Performance monitor eg SQL Memory Manager/SQL Buffer Manager) and some SQL DMV's but what else would show what is exactly happening to the RAM and how SQL uses it? Are there any other dianostics/tools that I could use?
Thanks
qh
[font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]