TempDB Improvement in SQL Server 2005
TempDB Improvement in SQL Server 2005
By : Kasim Wirama, MCDBA
In performance tuning area in SQL Server, there are five resources that are potential to contribute performance bottleneck. They are CPU, memory, I/O, tempdb and blocking. I will talk about CPU, memory and blocking in separate article. I would like to look into tempdb database.
SQL Server 2005 makes interesting improvements in tempdb database and let’s see improvements made in SQL Server 2005.
1. Less I/O activity in tempdb log file.
See this script below :
CREATE TABLE #temp1 (ColA CHAR(1000));
INSERT INTO #temp1 VALUES (‘test’);
UPDATE #temp1 SET ColA = ‘test updated’;
Instead of before and after changes are logged into tempdb log file (total 2000 byte), only 1000 byte is written into tempdb log file, so it explains less I/O activity in log file. But it doesn’t apply if the temporary table has clustered index or contains LOB. And the benefit will come for INSERT and UPDATE statement only.
2. Faster growing data file
In SQL Server 2000, file growth takes awhile. And if this activity frequently happens, it will degrade performance. SQL Server 2005 improves it so that file growth happens instantaneously. What is the trick behind this? The trick is that SQL Server 2005 leverages windows API to expand file size without performing I/O activity on new area of the file expansion. This only works if SQL Server 2005 is installed in Windows XP, Windows 2003 or Windows 2008.
3. Scalable concurrent access.
Consider this script below :
CREATE TABLE #temp1 (ColA CHAR(1000));
INSERT INTO #temp1 VALUES (‘test’);
DROP TABLE #temp1;
If large number of user execute this script above, it will create intensive CREATE/DELETE activity. Such a intensive activity will cause read/write contention in page level because each calling for the batch above will make tempdb database doing these activities:
a. Insert new metadata entry into system table.
b. Allocate 2 pages, one for data page, and the other for IAM page. PFS, GAM, and SGAM pages will be scanned and updated to find and allocate the pages.
c. When DROP TABLE is issued, tempdb will free up the 2 pages.
d. Remove related metadata entry in system table.
Better scalability in SQL Server 2005 when dealing with these patterns is that :
a. Change internal page allocation algorithm in allocating new pages so that read/write contention is reduced.
b. Cache metadata creation and deletion in memory cache.
It will make those script will be scalable when executed in terms of concurrent access.
4. Better utilization of multiple tempdb files.
If you have multiple CPU, it is recommended you have proportional number of tempdb files to corresponding to number of CPU. It is done so to avoid page latch because of read/write PFS/GAM/SGAM pages contention. Implement file-level striping to achieve better I/O bandwidth. SQL Server 2005 have better algorithm so that each files will be proportionally filled up. At the end, all files will reach their maximum capacity almost the same time.