SQL Server Performance Forum – Threads Archive

Problem with temp tables

We are experiencing problems with a stored procedure that uses two temp tables. Records are read from one table and then after some processing entered into the other table. This procedure has run without any trouble for over a year but has recently become dramatically slow without any good reason This seems to be caused by the fact that all read and write operations in the temp tables are done from / to disk instead of in memory (although I am not entirely sure it did do this in memory in the past) We noticed that the server seems to use less memory as where it is entitled too. The server has 1GB RAM but SQL-server uses less than 200 MB while processing the stored procedure.
The server is set to manage memory dynamically with a max. of 1 GB. Any ideas that could help? Jaap Kramer

Any other applications or services running on the server? What is the SQL Server Buffer Cache Hit Ratio counter like? If it is <90% consistently you are starved of RAM. You need to find out what else is using all the RAM. Monitor (by logging) PerfMon -> Process -> Private Bytes for all processes and figure out which one is hogging all the RAM. Tom Pullen
DBA, Oxfam GB

There is still plenty of unused RAM available. That is what makes it so strange. The SQL-server does not seem to see the need to claim the extra memory even though plenty is available and it has a big job at hand. Jaap Kramer

Did you have a lot of small extensions of tempdb? What are your tempdb file size and growth settings?

How often you update statistics on the involved tables?
How abotu schedule of DBCC DBREINDEX? Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

I suggest try forcing SQL Server to have, say 700 or 800 MB RAM (fixed amount, not dynamically allocated) and see how you get on. Needs careful mointoring to check other things (e.g. backup software, SQL Server Agent, etc) isn’t getting starved of RAM as a result. Tom Pullen
DBA, Oxfam GB

… in order to get correct results PERFMON (SYSMON) will help to capture the memory, disk, SQL Memory and processor related counters for better assessment. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>