Duty Free!!

SQL Write-Ahead Overhead…

Concurrency enhancements for the tempdb database

Here is a great link that describes Concurrency enhancements for the tempdb database. This also helps understand the way an object gets a page in the memory. 

“During object creation, two (2) pages must be allocated from a mixed extent and assigned to the new object. One page is for the Index Allocation Map (IAM), and the second is for the first page for the object. SQL Server tracks mixed extents by using the Shared Global Allocation Map (SGAM) page. Each SGAM page tracks about 4 gigabytes of data. 
As part of allocating a page from the mixed extent, SQL Server must scan the Page Free Space (PFS) page to find out which mixed page is free to be allocated. The PFS page keeps track of free space available on every page, and each PFS page tracks about 8000 pages. Appropriate synchronization is maintained to make changes to the PFS and SGAM pages; and that can stall other modifiers for short periods. 

When SQL Server searches for a mixed page to allocate, it always starts the scan on the same file and SGAM page. This results in intense contention on the SGAM page when several mixed page allocations are underway, which can cause the problems documented in the “Symptoms” section of this article. ”

A single GAM page for 4GB of Tempdb file means there is heavy contention for that page. This is the same with the PFS as well where the contention is doubled with allocation and deallocation of pages.  This is main reason to have multiple Tempdb files and with the same size. IF any single Tempdb file is  bigger again it can be frequently contended for during the round-robin algorithm for allocating a page.

Also, “The auto-grow of tempdb data files can also interfere with the proportional fill algorithm. Therefore, it may be a good idea to turn off the auto-grow feature for the tempdb data files. If the auto-grow option is turned off, you must make sure to create the data files so that they are large enough to prevent the server from experiencing a lack of disk space with tempdb.”


January 20, 2009 Posted by | SQL Server | Leave a comment

Trace Flags in SQL Server 2008.

A brief description of Traceflags in SQL 2008.

260 – Prints versioning for DLLs for XPs.

1204 – Resources involved in Deadlocks and also Victim details.

1211 – No lock escalation at all.

1222 – Resources involved in Deadlock in XML format and also victims details.

1224 – No lock escalation unless severe memory pressure.

2528 – No parallellization of DBCC Commands.

3205 – No compressed dumps to tapes though file compression is enabled on Tapes.

3226 – No messages on Backups in the Errorlog.

3608 – Doesnt start any DB than Master on Startup.

3625 – Limits error message 

4616 – Server level metadata visible to App roles.

6527 – No memory dumps in CLR integration on first error occurance.

7806 – Enable DAC on SQL Express.

Enable/Disable traceflag using DBCC Traceon and DBCC Traceoff. While Server startup use the -T startup option to SQLSrvr.exe. Use DBCC Tracestatus to find the traces enabled on the server. 

Dont confuse the functions involving Profiler Traces with those of these Traceflags.  The ones that involve Traceflags are DBCC commands.

Books Online link

January 20, 2009 Posted by | SQL Server | Leave a comment

Problems with Mixed Extents.

SQL Server (2005 and 2008) Trace Flag 1118 (-T1118) Usage

Trace flag 1118 forces uniform extent allocations instead of mixed page allocations.   The trace flag is commonly used to assist in TEMPDB scalability by avoiding SGAM and other allocation contention points.



SQL Server 2008 optimized mixed extent allocation behavior reducing the need for trace flag 1118 and the contention on SGAM(s).   The logic was also added to SQL Server 2005 in a CU release, KB article 936185.



The MORE INFORMATION section of the article states: “The hotfix that this article describes affects only the tempdb database. Additionally, you do not have to have trace flag 1118 after you install this hotfix. However, you can still use trace flag 1118.”



Some have interpreted this to mean the 1118 trace flag has been deprecated.   This is NOT the case and I have asked for the text to be updated.  Trace flag 1118 may still be needed along with the other suggestions in section “Troubleshooting contention caused by to DML operations” ofhttp://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx.


The blog that discusses this is here

January 20, 2009 Posted by | SQL Server | Leave a comment

SQL Server on Compressed Filesystems.

Many of us know that the filesystem on which the SQL Datafiles exist shouldnt be Compressed. The main reason that every one knows – it takes more effort to get the IO operations completed. Added to that the Windows  OS doesnt entertain any async IO operations on a Compressed volume. This makes a huge difference when the application, most probably SQL Server, can give a better performance calling the async IO calls. 

More over here by Ken Henderson’s Why you shouldn’t compress SQL Server data and log files

January 20, 2009 Posted by | SQL Server | Leave a comment

Lightweight Thread Pooling / Fibre Mode

One of the rare tweaks that one would do on a SQl Server when excessive Context switches are found on SQLservr.exe, is to apply the Fibre mode. In this Fibre mode there is a higher probability that SQl Server gets hung or some applications doesnt work as they are supposed to. These applications might not be fibre-safe. Infact, though  SQL Server runs in fibre-mode, at the OS level still the process called SQL Server executes in thread mode. It is just that internal to the process the thread is finely used as a set of fibres that are light weight, meaning – not heavy in terms of their possessions so the context switching between fibres doesnt eat much of the resources. 

An application thats using datastructures pertaining to a thread like Thread  Local Storage, shall not be called “fibre-safe” and are prone to errors in fibre mode. 

Ken Henderson’s blog on msdn gives an insight into this in particular detail. One can find it here.

January 20, 2009 Posted by | SQL Server | Leave a comment