x

SQL2005 SP3 x64 Std edition lock pages in memory & CU 4?

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

more ▼

asked May 25, 2010 at 07:23 AM in Default

5QL53rv3r gravatar image

5QL53rv3r
59 7 7 9

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered May 25, 2010 at 08:19 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
98.7k 19 21 74

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x56

asked: May 25, 2010 at 07:23 AM

Seen: 1177 times

Last Updated: May 25, 2010 at 07:23 AM