Eric,

I have been given a Power Builder application. I have been asked to allocate space for temp DB and SQL Srvr config. for best performance. How should I go about?

Thanks. Kothans.

-- Anonymous, January 17, 1999

Answers

Kothans,

tempdb is used for two things: (1) the server kernel uses it as temporary scratch space for work tables associated with some joins or order bys, etc.; and (2) to store temporary tables (#tables) created by users or stored procedures.

Without knowing your application, I could offer the following guidelines:

1) If your application does not create #tables then I recommend 25% of the total size of the application's database. You might want to set aside a disk partition big enough to take the 25%, create a device that uses it all. If your application does create #tables then you might want to use a combination of 25% plus an estimate of the size #tables created at any one time.

2) During load/stress testing you can look to see how much space is actually being utilized in tempdb every so many seconds and use that data to size the production environment.

3) Make sure you place tempdb on a separate controller/disk drive to get that IO away from your other devices.

4) Deallocate the initial 2M of tempdb allocated to the master device as part of the installation process so that all the IO is on the dedicated tempdb device.

Hope this helps,

Eric

-- Anonymous, January 19, 1999

Kothans,

You also asked about configuration for best performance.

The obvious suggestions are to have lots of RAM and spread your data out onto lots of disk drive spindles.

A less obvious suggestion is to avoid setting tempdb in ram.

To set the memory size, follow this suggestion from the Administrator's Companion, "To determine the optimal memory for your system, subtract the memory required for Windows NT (and other system uses, if the machine is not wholly dedicated to SQL Server) from the total physical memory. Ideally, you want to allocate as much memory as possible to SQL Server without causing the system to page. You can use SQL Performance Monitor to help determine what the threshold is for your system: the Page Faults/sec counter of the Memory Object indicates whether you are generating any page faults. If so, SQL Server is running with too much memory. The threshold varies depending on your system. For example, on a 32-MB system, 16 MB might be appropriate for SQL Server; on a 64-MB system, 40 MB might be appropriate."

In addition, here are the sp_configure settings on our servers (if the same setting is used for both the production and test servers a single number is given, otherwise they are separated by a /, production listed first): name run_value (both or production/test) --------------------- ---------------------------------- affinity mask 0 allow updates 0/1 backup buffer size 1 backup threads 5 cursor threshold -1 database size 2 default language 0 default sortorder id 43 fill factor 0 free buffers 5580/10240 hash buckets 7993 language in cache 3 LE threshold maximum 200 LE threshold minimum 20 LE threshold percent 0 locks 300000/40000 LogLRU buffers 0 logwrite sleep (ms) 0 max async IO 8 max lazywrite IO 8 max text repl size 65536 max worker threads 255 media retention 0 memory 111616/204800 nested triggers 1 network packet size 4096 open databases 50/200 open objects 10000/120000 priority boost 1/0 procedure cache 30 Protection cache size 15 RA cache hit limit 4 RA cache miss limit 3 RA delay 15 RA pre-fetches 3 RA slots per thread 5 RA worker threads 3 recovery flags 0 recovery interval 5/1 remote access 1 remote conn timeout 10 remote login timeout 5 remote proc trans 0 remote query timeout 0 remote sites 10 resource timeout 10 set working set size 0 show advanced options 1 SMP concurrency 0 sort pages 64 spin counter 10000 tempdb in ram (MB) 0 time slice 100 user connections 256/200 user options 0

Hope this helps,

Eric

-- Anonymous, January 21, 1999