June 2, 2009 at 9:00 am
Hello everyone,
We have a feed server thats used to extract our product information and send it to partners. The feed process does some logic first to find all the products to send and then either dump them to a permanent feed table, or write to xml file.
The process uses lots of tempdb. And currently the counter pages/sec is averages about 200, and it can peak to more than 1000. The feed process is getting slower and slower and sometimes times out.
I did a couple of things:
-- add tempdb data files so it has 8 files, because we have 8 cpus. And make sure they have the same initial size, which is 2GB to start with.
-- change the database to simple recovery
I am not sure what else to do to reduce paging. Any help would be greatly appreciated!
Thanks!
Kathleen
June 2, 2009 at 9:13 am
Also, I also made page file the same size as memory (16GB), half on one drive and half on the other drive.
June 3, 2009 at 7:57 am
To add to my thread, two counters I monitored yesterday:
buffer cache hit ratio -- between 95%-100%
sql buffer manager:page life expectancy-- 1800 seconds
neither of them seem to indicate a memory issue.
Not sure where the paging is from and why the feed process is so slow.
June 5, 2009 at 7:32 am
I just found out that the vb application running on the server runs every minute and it opens and closes lots of connections to the db. I know its expensive to open/close connections, but can it be that bad?
June 5, 2009 at 9:09 am
is it 32 bit or 64 bit?
how much ram you have in the box?
June 5, 2009 at 10:55 am
It is 64 bit, 16GB RAM.
June 5, 2009 at 11:35 am
Assuming this is SQL Server 2005?
Do you have your max server memory set? If so, at what?
June 5, 2009 at 11:54 am
Yes, sql 2005 standard. The max memory for sql is set to be 14.5GB.
June 5, 2009 at 11:59 am
KATHLEEN Y ZHANG (6/5/2009)
Yes, sql 2005 standard. The max memory for sql is set to be 14.5GB.
Might want to consider lowering that a bit ... If you're running other apps on the server, you're only leaving 1.5GB for the O/S (which should have 2-4) and anything else. This is a bit of a gray area for me, but once SQL has hit it's max, it won't give it up until other processes need it (unless LPIM is set) so you're going to cause your other apps (point in case this process) to hit the page file. I'd say release some more memory and see what happens.
June 5, 2009 at 12:06 pm
That makes sense. I will try it. Thanks!
June 5, 2009 at 2:45 pm
I actually set max memory for sql to 13GB, and have been seeing memory available on the server being 1.5GB, should I give more memory back to sql server?
June 6, 2009 at 4:26 am
cap max server memory to 12GB and apply sp3 CU4 which will be coming soon and add lock pages in memory
it should help
June 8, 2009 at 8:26 am
Over the weekend, I set the max memory for sql at 13.5 GB. Paging increased (avg 200/sec) compared with max memory at 13GB (avg 20/sec). But still a lot less than the original setting ( at 14.5GB, where avg paging was 500/sec).
On the sql side, the feed process did not seem to improve. It seems to me paging is caused by the feed process itself. Whenever there was a big feed process running, paging peaked. In that sense, it seems more memory needs to be allocated to sql. Is there any other counters that can tell if the server is performing better?
I will install service pack 3. Not sure what lock pages in memory means, but I will do some research.
Thanks a lot!
June 8, 2009 at 4:18 pm
lock pages in memory
http://blogs.technet.com/askperf/archive/2008/03/25/lock-pages-in-memory-do-you-really-need-it.aspx
In standard they will enable on SP3 CU4
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply