asked
would it be wrong to run freesystemcache
We are a company who have recently started using sql server as its main stay for databases. We are occasionally suffering from performance issues. its mainly writelog wait type and buffer i/o. I have checked the databases and optimised all these. All indexes are uptodate and hardly any fragmentation. I currently use a fill factor of 95%. Its all been running smoothly. Now the main problem I have got is that Our production and development server is the same. So I am trying to read as much as possible to try and sort this issue out. Writelog wait goes from being 1-5 to 300 and there is a very visible lag on doing inserts. Now I am leaning more towards memory pressure??? am i wrong? Any other help would be greatly appreciated. We are running sql server 2008 standard with 14 gb of RAM and 2 x 2 core processors. Our transaction level was relatively low but now its 4 -8 inserts per sec and 4-8 seeks of the index per/sec. One thing I noticed this morning is that we have a very large number of stolen buffer pages. ![alt text][1]. I am assuming thats because of large number of ad-hoc queries. Any pointers will be greatly appreciated. Waiting in anticipation. Regards, Waqar [1]: /storage/temp/4616-capture.jpg
I was having a look at 'stolen pages' and came across this article:
https://www.red-gate.com/simple-talk/blogs/stolen-pages-ad-hoc-queries-and-the-sins-of-dynamic-sql-in-the-application/ It lines up with what you say about memory pressures and contains some queries that you could run to have a look at the causes. Sorry I cant be of more help!
Hi WRBI. Already read it. I am not super advanced and would like some guidance on it :)
Do you have 'optimize for ad hoc workloads' enabled? `exec sys.sp_configure 'show advanced options', 1;reconfigure;exec sp_configure 'optimize for ad hoc workloads';`
its currently set to current value is 0 and run value is also zero. So its a no. I have not used this option ever before, if i enable this, what are the likely side effects of this to the server please?
I cant post my data in comments I dont know why. But please see below in the answer section if the data could be the cause I got this data by using select * from sys.dm_os_performance_counters
Could the following data have the answer? object_name counter_name cntr_value cntr_type SQLServer:Buffer Manager Buffer cache hit ratio 495 537003264 SQLServer:Buffer Manager Buffer cache hit ratio base 495 1073939712 SQLServer:Buffer Manager Page lookups/sec 2743535352 272696576 SQLServer:Buffer Manager Free list stalls/sec 1 272696576 SQLServer:Buffer Manager Free pages 3368 65792 SQLServer:Buffer Manager Total pages 1310720 65792 SQLServer:Buffer Manager Target pages 1310720 65792 SQLServer:Buffer Manager Database pages 1182008 65792 SQLServer:Buffer Manager Reserved pages 0 65792 SQLServer:Buffer Manager Stolen pages 125344 65792 SQLServer:Buffer Manager Lazy writes/sec 5120 272696576 SQLServer:Buffer Manager Readahead pages/sec 1651919 272696576 SQLServer:Buffer Manager Page reads/sec 1883347 272696576 SQLServer:Buffer Manager Page writes/sec 9387113 272696576 SQLServer:Buffer Manager Checkpoint pages/sec 669521 272696576 SQLServer:Buffer Manager AWE lookup maps/sec 0 272696576 SQLServer:Buffer Manager AWE stolen maps/sec 0 272696576 SQLServer:Buffer Manager AWE write maps/sec 0 272696576 SQLServer:Buffer Manager AWE unmap calls/sec 0 272696576 SQLServer:Buffer Manager AWE unmap pages/sec 0 272696576 SQLServer:Buffer Manager Page life expectancy 31076 65792
If you share the same box for dev and prod, you need to configure max server memory on both instances so that they don't both allocate too much memory. Save at least 3GB for the operating system, and then share the rest between the two instances.
Hi Magus, Thats one of the first things I did when I took over almost 1.5 years ago. Reserved 3GB for operating system.
18 People are following this question.
