database agnostic - Page 573

Trace flags - TF 1117

- by Damian

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

Search Results

Search found 31293 results on 1252 pages for 'database agnostic'.

Page 573/1252 | < Previous Page | 569 570 571 572 573 574 575 576 577 578 579 580 | Next Page >

- by Damian

- by tonyrogerson

- by joe.huang

- by rickramsey

- by pinaldave

- by Irem Radzik

- by mseika

- by Ken Lovely, MCSE, MCDBA, MCTS

- by Etienne Giust

- by CassandraClark-OTN

- by rituchhibber

- by Tori Wieldt

- by Stephen Tierney

- by Bob Rhubart

- by kanichiro.nishida

- by Dana Singleterry

- by pinaldave

- by CheeseConQueso

- by Tamarick Hill

- by [email protected]

- by SamanthaF-Oracle

- by Guilherme Cardoso

< Previous Page | 569 570 571 572 573 574 575 576 577 578 579 580 | Next Page >