Desire2Learn: Improving Performance

Recently I reviewed some public posts on Desire2Learn SQL Server Performance and extracted some recommendations of common issues:

Often, I have seen an increase of CPUs without increasing RAM or the number of TempDB files. Each CPU (or core on a CPU) creates a "mini-PC". A configuration with 4 GB RAM and 1 CPU may perform worst with a six-core CPU and 4 GB of RAM in some situations. A few simple rule of thumb may help you here:

• Add up the number of cores (not the number of CPU sockets)  and then divide this into the total RAM. If you find that you have 1 GB/core, then you may have just identify a performance problem.
  • For more information on CPU versus Core. see  Right sizing ISV installations for the best bang for the buck!
• The Microsoft recommendation is to have one TempDB file for every core. Each core uses TempDB to store transistory information (especially when there is not enough RAM). If you have 24 cores and just one TempDB file, then the cores may be lined up for this file similar to how 24 PCs in a lab may have their print jobs waiting for a single printer (without print spooling) to process all of the student term papers.
  • Some folks recommend 1 TempDB file for every two cores if you have more than 12 cores.
• Improving the disk storage mechanism
  • Faster Drives
  • More Drives

 For those that are interest in reading experiences more, here are a few of the items that I reviewed:

• Minnesota State Colleges and Universities system D2L Implementation Project
• University of Wisconsin (at Madison) Site Visit Report Executive Summary

Determing how many Cores are being used

There are many ways to determine this. One simple way is to:

1. Select Start/ All Programs/ Accessories / System Tools/ Resource Monitor
   
2. The Resource Monitor Application will now appear:
   
3. Scroll down the right side to see the number of cores you have (the "CPU" are cores and not sockets).

Technical details: Intel hyperthreads will appear as CPUs but should not be count as CPUs. A common SQL Server practice is to disable the hyperthreading on Intel Chips (for more information).

Determing how many TempDB files are being used

To determine this:

1. Open SQL Server Management Studio
   
2. Select the TempDB database
   
3. Right click on Properties
   
4. The Database Properties - tempdb dialog will appear
   
5. Click on the [Files] Page, and the files will now appear:
   
6. Count the number of files marked "Row Data".
   1. The recommended number is one file per CPU
   2. The minimum number that should be consider is one file for every two CPUs
   3. Ideally, each file would be on a different physical spindle.
7. If you do not have enough files, click [ADD]
   1. All of the files should have the same Initial Size.