Looking to resolve SQL Server performance problems caused by using many temporary tables

Disk storage
Microsoft SQL Server 2000
SQL Server performance
I need your advice on the SQL Server performance tuning. The Problem: We use a lot of temporary tables in our application, which is causing a lot of SQL Server performance problems. I read about the following solution on some site. "RamDisk presents a powerful solution to this problem by providing up to 63 GB of virtual disk storage. SQL Server production environments with 8, 16 and 32 GB of system memory have dedicated 4, 12 and 20 GB to the RAM disk, and placed the tempdb file on the virtual disk. In these settings, accelerations in SQL Server application throughput of up to six times have been realized." Can you please guide me on this? I also read that for SQL Server 2000 we cannot use more than 4MB of RAM. Is that right?

Answer Wiki

Thanks. We'll let you know when a new response is added.

Yes, a RAM disk would provide you with an ultra fast tempdb, however there are very few databases which actually need this sort of speed boost. Purchasing enough RAM to be able to create a RAM disk large enough to fit your needs may get very expensive. Once you start needing to use the 4 Gig or 8 Gig memory sticks your costs start to go up very fast.

A Dedicated RAID 10 array with 4 drives is usually fast enough for most databases, and is usually a much less expensive solution.

No that’s not correct that SQL 2000 can only use 4 GB of RAM (you put 4 MB, I assume you meant Gigs). SQL 2000 standard can only use 2 Gigs of RAM. SQL 2000 Enterprise can use has much RAM as the host OS Supports. If you are using Windows 2003 Standard than you are limited to 4 Gigs of RAM. Windows 2003 Enterprise supports up to 64 GB of RAM.

Discuss This Question: 2  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Denny Cherry
    Check out my SQL Server blog "SQL Server with Mr Denny" for more SQL Server information.
    69,125 pointsBadges:
  • MarkWPF2
    You may want to work out ways of removing the reliance on temporary tables as well For example, you can use table variables instead of temporary tables. That only works sometimes though. The other way to get around the problem is down to database usage. One thing that has also worked well for me in past assignments is to take away the need for temporary tables or temporary variables by some redesign. For example, you can prebuild reporting style tables rather than keep generating them as temporary tables - ie have them de-normalised. Then you can query against the prebuilt tables, which is going to be much faster
    40 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: