I had a session
about trace flags this year on the SQL Day 2014 conference that was held in
Wroclaw at the end of April. The session topic is important to most of DBA's
and the reason I did it was that I sometimes forget about various trace flags
:). So I decided to prepare a presentation but I think it is a good idea to
write posts about trace flags, too.
Let's start then
- today I will describe the TF 1117. I assume that we all know how to setup a
TF using starting parameters or registry or in the session or on the query
level. I will always write if a trace flag is local or global to make sure we
know how to use it.
Why do we need
this trace flag? Let’s create a test database first. This is quite ordinary
database as it has two data files (4 MB each) and a log file that has 1MB. The
data files are able to expand by 1 MB and the log file grows by 10%:
USE [master]
GO
CREATE DATABASE [TF1117]
ON PRIMARY
( NAME = N'TF1117',
FILENAME = N'C:\Program Files\Microsoft SQL
Server\MSSQL12.SQL2014\MSSQL\DATA\TF1117.mdf' ,
SIZE = 4096KB ,
MAXSIZE = UNLIMITED,
FILEGROWTH
= 1024KB
),
( NAME = N'TF1117_1',
FILENAME = N'C:\Program Files\Microsoft SQL
Server\MSSQL12.SQL2014\MSSQL\DATA\TF1117_1.ndf' ,
SIZE = 4096KB ,
MAXSIZE = UNLIMITED,
FILEGROWTH
= 1024KB
)
LOG ON
( NAME = N'TF1117_log',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\TF1117_log.ldf'
,
SIZE = 1024KB ,
MAXSIZE = 2048GB ,
FILEGROWTH
= 10%
)
GO
Without the TF
1117 turned on the data files don’t grow all up at once. When a first file is
full the SQL Server expands it but the other file is not expanded until is
full. Why is that so important? The SQL Server proportional fill algorithm will
direct new extent allocations to the file with the most available space so new
extents will be written to the file that was just expanded. When the TF 1117 is
enabled it will cause all files to auto grow by their specified increment. That
means all files will have the same percent of free space so we still have the
benefit of evenly distributed IO. The TF 1117 is global flag so it affects all
databases on the instance. Of course if a filegroup contains only one file the
TF does not have any effect on it.
Now let’s do a
simple test. First let’s create a table in which every row will fit to a single
page: The table definition is pretty simple as it has two integer columns and
one character column of fixed size 8000 bytes:
create table TF1117Tab
(
col1 int,
col2 int,
col3 char (8000)
)
go
Now I load some
data to the table to make sure that one of the data file must grow:
declare @i int
select @i = 1
while (@i < 800)
begin
insert into TF1117Tab values (@i, @i+1000, 'hello')
select @i= @i + 1
end
I can check the
actual file size in the sys.database_files DMV:
SELECT name, (size*8)/1024 'Size in MB'
FROM sys.database_files
GO
As you can see only the
first data file was expanded and the
other has still the initial size:
name Size
in MB
--------------------- -----------
TF1117 5
TF1117_log 1
TF1117_1 4
There is also
other methods of looking at the events of file autogrows. One possibility is to
create an Extended Events session and the other is to look into the default
trace file:
DECLARE @path NVARCHAR(260);
SELECT @path = REVERSE(SUBSTRING(REVERSE([path]),
CHARINDEX('\', REVERSE([path])), 260)) + N'log.trc'
FROM sys.traces
WHERE
is_default = 1;
SELECT
DatabaseName,
[FileName],
SPID,
Duration,
StartTime,
EndTime,
FileType
=
CASE EventClass
WHEN 92 THEN 'Data'
WHEN 93 THEN 'Log'
END
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE
EventClass IN (92,93)
AND StartTime >'2014-07-12' AND
DatabaseName = N'TF1117'
ORDER BY StartTime DESC;
After running the
query I can see the file was expanded and how long did the process take which
might be useful from the performance perspective.
Now it’s time to
turn on the flag 1117.
DBCC TRACEON(1117)
I dropped the
database and recreated it once again. Then I ran the queries and observed the
results. After loading the records I see that both files were evenly expanded:
name Size
in MB
--------------------- -----------
TF1117 5
TF1117_log 1
TF1117_1 5
I found also
information in the default trace. The query returned three rows. The last one
is connected to my first experiment when the TF was turned off. The two rows shows that first file was
expanded by 1MB and right after that operation the second file was expanded,
too. This is what is this TF all about J
