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]. I am assuming thats because of large number of ad-hoc queries. Any pointers will be greatly appreciated. Waiting in anticipation. Regards, Waqar : /storage/temp/4616-capture.jpg
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.