TEMPDB in SQL IaaS
On Azure SQL IaaS, there is a recommendation that we have to move TEMPDB on Temporary Drive (D:) to improve SQL Server performance.
TempDB and Log Files May 11, 2012
http://www.sqlskills.com/BLOGS/PAUL/post/Understanding-data-vs-log-usage-in-tempdb.aspx
One of the things to consider about tempdb is that logging in tempdb is ultra-efficient. Log records for updates in tempdb, for instance, only log the before image of the data instead of logging both before and after images. There is no need to log the after image – as that is only used for the REDO portion of crash recovery. As tempdb never gets crash-recovered, REDO never occurs. The before image *is* necessary, however, because transactions can be rolled back in tempdb, just like other databases, and so the before image of an update must be available to be able to successfully roll back the update.
SQL Tempdb Move and Multiple Files February 1, 2011
http://msdn.microsoft.com/en-us/library/ms175527.aspx
Configuration for resize and location.
SQL Peformance Urban Legends June 16, 2010
http://blogs.msdn.com/b/psssql/archive/2007/02/21/sql-server-urban-legends-discussed.aspx
Tempdb is the database with the highest level of create and drop actions and under high stress the allocation pages, syscolumns and sysobjects can become bottlenecks. SQL Server 2005 reduces contention with the ‘cached temp table’ feature and allocation contention skip ahead actions.
When multiple workers are attempting to create or drop objects in tempdb you can decrease the bottleneck by having
- As many files in tempdb as schedulers
- All files sizes are equal
- Uniform allocations enabled (-T1118)
The key for SQL Server is the I/O response times. Start by looking at the Average Disk Seconds Per Transfer for the same I/O path. Then look at the SQL Server sysprocesses and waitstats for buffer based I/O wait information. You want to see evidence from SQL Server that the disk queue length is related to an I/O bottleneck.
SQL Tempdb Performance May 16, 2010
Take tempdb:
If there is lot of write activity, tempdb suffers from allocation bottleneck as these system pages PFS, GAM, SGAM will be accessed. One way to find this allocation bottleneck is below.
The answer is, it depends. Answering that question is extremely difficult and not the same for everyone.
You need to know where the bottleneck of performance is located. In SQL Server the bottleneck for queries is CPU, RAM and disk access, in that order. The disk is the slowest part of the computer, but can be offset by huge amounts of cache, either in the controller, server, OS or SQL cache. If you are not maxing out the bandwidth of the hard drives, then creating 2 (or more) data files per LUN does not hurt your performance. But, you want to minimize the time the OS is waiting on the hard drives.
It also depends on your usage. If most of your database is “static” data and read many times, then you will benefit from a huge data cache (more RAM). However, if your database is transactional and updated all the time and you need to read those new records all the time, then hard drive speed is probably your bottleneck.
In addition, with newer caching controllers/SANs the RAID level has very little effect on the performance of the actual read/write time. I have done testing on IBM DS4800 using all RAID levels, and they are so close as to be insignificant. IMO, the reliablity of having RAID 5 and spreading the reads/writes between many drives outweights the performance hit of the RAID 5. Even using Windows software “stripping” between several RAID 5 arrays (using a huge server) had almost zero affect on performance.
Bottom line, you will have to try and find out how it works.
select session_id, wait_duration_ms, resource_description
from sys.dm_os_waiting_tasks
where wait_type like ‘PAGE%LATCH_%’ and
resource_description like ’2:%
or
SELECT *
FROM sys.sysprocesses
WHERE lastwaittype like ‘PAGE%LATCH_%’ AND waitresource like ’2:%’
To avoid this allocation bottleneck, it is recommended to
1) To create multiple files as upto the number of cpu’s avaialable and its important that these files to be of EQUAL size to make use of the proportional fill. These files don’t necessary be on different disks unless you have facing IO bottleneck too. If you have the option go for it.
If tempdb is going to be on its own disk/array/whatever…give it a decent size to start and review regularly.
AFAIK, I don’t think there is a huge downside to making it too large to begin with.
It is recreated every time SQL Server starts, but with ‘instant’ file initialization, that should not be a problem.
Start with something like 32GB. If you have four cores, create four files of 8GB each. Eight cores = 8 files of 4GB. And so on.
That’s just to give you somewhere to start. Go large!
http://msdn.microsoft.com/en-us/library/dd758814.aspx
Disk alignment.
Stripe size is the size of one entire stripe spread across all the disks in a RAID-0, RAID-10, RAID-5, or RAID-6 disk group. Stripe unit size is the size of each element of the stripe, as stored on each member of the disk group. Stripe size is a product of the stripe unit size and the number of disks in a RAID group. Stripe unit size is the attribute of a RAID disk group that can be configured by administrators. A stripe unit is the collection of bits on each disk exactly equal to the stripe unit size.
In the absence of an explicit vendor recommendation, use a partition offset that complies with the correlation discussed in the section “Essential Correlations: Partition Offset, File Allocation Unit Size, and Stripe Unit Size”. 64 KB is a common, valid starting partition offset because it correlates well with fundamental physical boundaries in disks, controllers, and cache. Other valid starting partition offsets exist. The Windows Server 2008 default is 1024 KB. For more information, see the “Valid Starting Partition Offsets” section later in this paper.
The performance benefit of disk partition alignment requires configuration of valid starting partition offsets.
Windows Server 2008 partition alignment defaults to 1024 KB (that is, 1,048,576 bytes). This value provides a durable solution. It correlates well (as described later) with common stripe unit sizes such as 64 KB, 128 KB, and 256 KB as well as the less frequently used values of 512 KB and 1024 KB. Also, the value virtually guarantees hidden structures allocated by storage hardware are skipped.
Essential Correlations: Partition Offset, File Allocation Unit Size, and Stripe Unit Size
Use the information in this section to confirm the integrity of disk partition alignment configuration for existing partitions and new implementations.
There are two correlations which when satisfied are a fundamental precondition for optimal disk I/O performance. The results of the following calculations must result in an integer value:
Partition_Offset ÷ Stripe_Unit_Size
Stripe_Unit_Size ÷ File_Allocation_Unit_Size
Of the two, the first is by far the most important for optimal performance. The following demonstrates a common misalignment scenario: Given a starting partition offset for 32,256 bytes (31.5 KB) and stripe unit size of 65,536 bytes (64 KB), the result is 0.4921875. This is not an integer; therefore the offset & strip unit size are not correlated. This is consistent with misalignment.
However, a starting partition offset of 1,048,576 bytes (1 MB) and a stripe unit size of 65,536 bytes produces a result of exactly 8, an exact integer, which is consistent with alignment.
Note that file allocation unit (cluster) size commonly correlates with common stripe unit sizes. The performance question here is usually not one of correlation per the formula, but whether the cluster size is the NTFS default of 4,096 bytes or has been explicitly defined at 64 KB, which is a best practice for SQL Server.
http://en.wikipedia.org/wiki/RAID#RAID_1
RAID Types
http://www.pcguide.com/ref/hdd/perf/raid/levels/singleLevel5-c.html
RAID 5
http://www.sql-server-performance.com/faq/raid_1_raid_5_p1.aspx
RAID 1
Both hardware RAID 1 (mirroring or duplexing) or RAID 5 (striping with parity) offer good data redundancy should a single hard disk in a RAID array fail. And as you might expect, there is some differences in performance between the two.
The major difference in performance between RAID 1 and RAID 5 is that RAID 5 experiences heavy write overhead because additional parity data has to be created and written to the array. This is overhead that RAID 1 does not experience. Because of this, write performance on RAID 1 is generally much faster than RAID 5.
Read performance, on the other hand, is generally better for RAID 5 than RAID 1. This is especially true if the RAID 5 arrays have more than three disk drives in them. RAID 5 read performance increases as more disks are added to the array because the more disks there are, the more read/write heads there are, and RAID 5 arrays have the ability to read simultaneously from all the drives, speeding read access. Since RAID 1 only has two disk drives, and RAID 5 has three or more disk drives, RAID 5 has the read performance advantage.
