I have a SQL2005 SP3 x64 Std edition server with 32 Gb memory. I have allocated 25Gb to SQL. I'm not seeing any error messages in the logs, but I am getting paging out to disk. This was at one point extremely high yesterday, but has seemed down come down alot over night and through this morning.
I understand that in this edition 'lock pages in memory' is not supported and to be able to do this I would have to apply CU4.
Would this really help? Is there an 'acceptable' level of paging?
Many Thanks M
asked May 25 '10 at 07:23 AM in Default
Paging to disk is a normal operation. You do want to minimize it as much as possible, but you can't eliminate it. The question isn't whether or not you have paging, the question is, what paging do you have? What's causing it? Further, is that paging causing your system to slow down?
I'd suggest, instead of worrying about the paging, focus on gathering statistics about the waits and queues. Knowing what is causing the system to slow down and wait and then identifying ways to address is are some of the best methods for troubleshooting performance. The dynamic management view sys.dm_os_wait_stats will get you started. It's accumulative, so you'll need to sample it a few times and then compare results to see which waits are growing. You can also see the waits in real time by looking at the DMV sys.dm_exec_requests to see the latest wait causes of execution queries.
answered May 25 '10 at 08:19 AM
Grant Fritchey ♦♦