SQL Server trace flags

Normally I’m not a big fan of using trace flags, my advice is only to use these when it is absolutely necessary otherwise don’t.

Here is a list of the documented trace flags that you can use with SQL Server. That is the documented once, besides that there is a list of undocumented trace flags – and it is as you might already have guessed one of these I’ll be blogging about today.

Last week I was visiting a customer, their database is running a very busy OLTP system. Their database is running with multiple files and filegroups. Until now they have been manually growing their datafiles, primarily to avoid the autogrowth and ending up with the IOPS being unevenly spread across the drives.

SQL Server fills the datafiles in a round robin fashion based on percent free space, when autogrow occurs the current datafile grows. There is no guarantee that the smallest datafile will be the next to grow. This behavior corrupts your proportional fill pretty quickly.

If SQL Server is running with Trace flag 1117, the behavior is different. When SQL Server determines that it needs to autogrow one of it’s datafiles, it autogrows all the datafiles at the same time. Read further and have a look at the examples, that demonstrates the behavior.

First of all, I’ll create a database for my tests.

<span style="color:blue;">CREATE DATABASE </span><span style="color:teal;">T1117_NotEnabled
</span><span style="color:blue;">ON
</span><span style="color:gray;">(
    </span><span style="color:teal;">NAME </span><span style="color:gray;">= </span><span style="color:teal;">T1117_01</span><span style="color:gray;">,
    </span><span style="color:blue;">FILENAME </span><span style="color:gray;">= </span><span style="color:red;">'C:T1117T1117_01.mdf'</span><span style="color:gray;">,
    </span><span style="color:teal;">SIZE </span><span style="color:gray;">= </span>3<span style="color:teal;">MB</span><span style="color:gray;">,
    </span><span style="color:teal;">FILEGROWTH </span><span style="color:gray;">= </span>5<span style="color:teal;">MB
</span><span style="color:gray;">),
(
    </span><span style="color:teal;">NAME </span><span style="color:gray;">= </span><span style="color:teal;">T1117_02</span><span style="color:gray;">,
    </span><span style="color:blue;">FILENAME </span><span style="color:gray;">= </span><span style="color:red;">'C:T1117T1117_02.ndf'</span><span style="color:gray;">,
    </span><span style="color:teal;">SIZE </span><span style="color:gray;">= </span>3<span style="color:teal;">MB</span><span style="color:gray;">,
    </span><span style="color:teal;">FILEGROWTH </span><span style="color:gray;">= </span>5<span style="color:teal;">MB
</span><span style="color:gray;">),
(
    </span><span style="color:teal;">NAME </span><span style="color:gray;">= </span><span style="color:teal;">T1117_03</span><span style="color:gray;">,
    </span><span style="color:blue;">FILENAME </span><span style="color:gray;">= </span><span style="color:red;">'C:T1117T1117_03.ndf'</span><span style="color:gray;">,
    </span><span style="color:teal;">SIZE </span><span style="color:gray;">= </span>3<span style="color:teal;">MB</span><span style="color:gray;">,
    </span><span style="color:teal;">FILEGROWTH </span><span style="color:gray;">= </span>5<span style="color:teal;">MB
</span><span style="color:gray;">),
(
    </span><span style="color:teal;">NAME </span><span style="color:gray;">= </span><span style="color:teal;">T1117_04</span><span style="color:gray;">,
    </span><span style="color:blue;">FILENAME </span><span style="color:gray;">= </span><span style="color:red;">'C:T1117T1117_04.ndf'</span><span style="color:gray;">,
    </span><span style="color:teal;">SIZE </span><span style="color:gray;">= </span>3<span style="color:teal;">MB</span><span style="color:gray;">,
    </span><span style="color:teal;">FILEGROWTH </span><span style="color:gray;">= </span>5<span style="color:teal;">MB
</span><span style="color:gray;">)
</span><span style="color:magenta;">LOG </span><span style="color:blue;">ON
</span><span style="color:gray;">( </span><span style="color:teal;">NAME </span><span style="color:gray;">= </span><span style="color:teal;">T1117_log</span><span style="color:gray;">,
    </span><span style="color:blue;">FILENAME </span><span style="color:gray;">= </span><span style="color:red;">'C:T1117T1117_log.ldf'</span><span style="color:gray;">,
    </span><span style="color:teal;">SIZE </span><span style="color:gray;">= </span>1<span style="color:teal;">MB</span><span style="color:gray;">,
    </span><span style="color:teal;">FILEGROWTH </span><span style="color:gray;">= </span>5<span style="color:teal;">MB
</span><span style="color:gray;">)
</span><span style="color:blue;">GO
</span>

Then I’ll create a table and load some data into it

<span style="color:blue;">USE </span><span style="color:teal;">T1117_NotEnabled
</span><span style="color:blue;">GO  

CREATE TABLE </span><span style="color:teal;">T1117_TestData
</span><span style="color:gray;">(
    </span><span style="color:teal;">Id </span><span style="color:blue;">uniqueidentifier default </span><span style="color:magenta;">newid</span><span style="color:gray;">(),
    </span><span style="color:teal;">Created </span><span style="color:blue;">Datetime2 default </span><span style="color:magenta;">sysdatetime</span><span style="color:gray;">(),
    </span><span style="color:teal;">DataValues </span><span style="color:blue;">char</span><span style="color:gray;">(</span>6000<span style="color:gray;">)
)
</span><span style="color:blue;">GO  

INSERT INTO </span><span style="color:teal;">T1117_TestData </span><span style="color:gray;">(</span><span style="color:teal;">DataValues</span><span style="color:gray;">)
</span><span style="color:blue;">VALUES </span><span style="color:gray;">(</span><span style="color:red;">'TESTING 123...'</span><span style="color:gray;">)
</span><span style="color:blue;">GO </span>10000

After running this code, let’s have a look at how SQL Server has expanded the files, this little script helps me with that.

<span style="color:blue;">USE </span><span style="color:teal;">T1117_NotEnabled
</span><span style="color:blue;">GO

SELECT
 </span><span style="color:magenta;">file_id</span><span style="color:gray;">,
 </span><span style="color:teal;">physical_name</span><span style="color:gray;">,
 </span><span style="color:teal;">[file_size_mb] </span><span style="color:gray;">= </span><span style="color:magenta;">CONVERT</span><span style="color:gray;">(</span><span style="color:blue;">DECIMAL</span><span style="color:gray;">(</span>12<span style="color:gray;">,</span>2<span style="color:gray;">),</span><span style="color:magenta;">ROUND</span><span style="color:gray;">(</span><span style="color:teal;">size</span><span style="color:gray;">/</span>128.000<span style="color:gray;">,</span>2<span style="color:gray;">)),
 </span><span style="color:teal;">[space_used_mb] </span><span style="color:gray;">= </span><span style="color:magenta;">CONVERT</span><span style="color:gray;">(</span><span style="color:blue;">DECIMAL</span><span style="color:gray;">(</span>12<span style="color:gray;">,</span>2<span style="color:gray;">),</span><span style="color:magenta;">ROUND</span><span style="color:gray;">(</span><span style="color:magenta;">FILEPROPERTY</span><span style="color:gray;">(</span><span style="color:teal;">name</span><span style="color:gray;">,</span><span style="color:red;">'SPACEUSED'</span><span style="color:gray;">)/</span>128.000<span style="color:gray;">,</span>2<span style="color:gray;">))
</span><span style="color:blue;">FROM </span><span style="color:green;">sys</span><span style="color:gray;">.</span><span style="color:green;">database_files
</span><span style="color:blue;">WHERE </span><span style="color:gray;">(</span><span style="color:teal;">data_space_id </span><span style="color:gray;">= </span>1<span style="color:gray;">)
</span>

And here is the result, when running SQL Server without trace flag 1117

Enable Trace flag 1117
There are a few ways to enable a trace flag, that is not the topic of this blog post, so I’ll just show how I have done it for testing purposes. I open a command prompt and stop the SQL Server service, when that is stopped I start SQL Server in console mode and with the T1117 trace flag.

When the SQL Server service is back online, I rerun my tests. I will not show the code again, only the picture that shows the result. As you can see the result is clear, and just what we expected, the proportional fill works just fine.

Any questions?
Please reach out to info@inspari.dk or +45 70 24 56 55 if you have any questions. We are looking forward to hearing from you.