{"id":820,"date":"2013-08-05T15:38:42","date_gmt":"2013-08-05T22:38:42","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/glenn\/?p=820"},"modified":"2018-11-13T10:52:11","modified_gmt":"2018-11-13T18:52:11","slug":"experiments-with-sql-server-vlfs-part-1","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/glenn\/experiments-with-sql-server-vlfs-part-1\/","title":{"rendered":"Experiments with SQL Server VLFs, Part 1"},"content":{"rendered":"<p>Since SQL Server 2012 is relatively easy to install and get running, using just the default configuration settings, I thought it would be interesting to explore the negative performance effects of some of these default configuration settings, using a simple, repeatable test.<\/p>\n<p>My test machine is a desktop machine with an <a href=\"https:\/\/ark.intel.com\/products\/65523\/\">Intel Core i7-3770K processor<\/a> with 32GB of RAM, and two fast, consumer-level SSDs. The C: drive is a <a href=\"https:\/\/www.newegg.com\/Product\/Product.aspx?Item=N82E16820227793\">512GB OCZ Vertex 4<\/a>, and the L: drive is a faster, <a href=\"https:\/\/www.newegg.com\/Product\/Product.aspx?Item=N82E16820147193\">256GB Samsung 840 Pro<\/a>. Both SSDs are plugged into 6Gbps SATA III ports, and they are performing as I would expect.<\/p>\n<p>For my test database, I downloaded a copy of the 2008 <a href=\"https:\/\/www.sqlskills.com\/sql-server-resources\/sql-server-demos\/\">Credit database<\/a> and restored it to a SQL Server 2012 SP1 CU5 instance. This database has a table called charge, that has 1.6 million rows of data. The database starts out with a 1000MB data file, and a 400MB log file with 15 VLFs. The database is in the SIMPLE recovery model, and both the data and log files have their autogrowth set to 10% (which is not the best choice). I used the MOVE command to place the SQL Server log file for the database on my separate, faster L: drive, so I would get the fastest possible sequential write performance for the log file.<\/p>\n<p>To make this experiment more interesting, I changed the recovery model to FULL, and I changed the autogrowth for both the data and log files to 1MB (<strong>which is a really bad idea<\/strong>). Finally, I took a compressed, full database backup to have a SQL Server 2012 baseline for the experiments, as shown in Listing 1:<\/p>\n<p><strong><span style=\"text-decoration: underline;\"><span style=\"font-size: large;\">Initial Preparation of the Credit Database<\/span><\/span><\/strong><\/p>\n<pre class=\"csharpcode\"><span class=\"kwrd\">USE<\/span> [master];\r\n\r\n<span class=\"rem\">-- Start of initial preparation<\/span>\r\n<span class=\"rem\">-- Restore from the original SQL Server 2008 backup with MOVE if necessary<\/span>\r\n<span class=\"rem\">-- This will upgrade it to SQL Server 2012 format<\/span>\r\n<span class=\"kwrd\">RESTORE<\/span> <span class=\"kwrd\">DATABASE<\/span> [Credit] \r\n<span class=\"kwrd\">FROM<\/span>  <span class=\"kwrd\">DISK<\/span> = N<span class=\"str\">'C:\\SQLBackups\\CreditBackup100.bak'<\/span> <span class=\"kwrd\">WITH<\/span>  <span class=\"kwrd\">FILE<\/span> = 1,  \r\nMOVE N<span class=\"str\">'CreditData'<\/span> <span class=\"kwrd\">TO<\/span> N<span class=\"str\">'C:\\SQLData\\CreditData.mdf'<\/span>,  \r\nMOVE N<span class=\"str\">'CreditLog'<\/span> <span class=\"kwrd\">TO<\/span> N<span class=\"str\">'L:\\SQLLogs\\CreditLog.ldf'<\/span>,  NOUNLOAD,  STATS = 1;\r\n<span class=\"kwrd\">GO<\/span>\r\n\r\n<span class=\"rem\">-- Change compatibility level to 110 (SQL Server 2012)<\/span>\r\n<span class=\"rem\">-- This is different from the database format<\/span>\r\n<span class=\"kwrd\">ALTER<\/span> <span class=\"kwrd\">DATABASE<\/span> [Credit] <span class=\"kwrd\">SET<\/span> COMPATIBILITY_LEVEL = 110;\r\n<span class=\"kwrd\">GO<\/span>\r\n<span class=\"rem\">-- Change recovery model to FULL<\/span>\r\n<span class=\"kwrd\">ALTER<\/span> <span class=\"kwrd\">DATABASE<\/span> [Credit] <span class=\"kwrd\">SET<\/span> RECOVERY <span class=\"kwrd\">FULL<\/span> <span class=\"kwrd\">WITH<\/span> NO_WAIT;\r\n<span class=\"kwrd\">GO<\/span>\r\n<span class=\"rem\">-- Change file growth for data file to 1 MB (This is a bad thing to do!)<\/span>\r\n<span class=\"kwrd\">ALTER<\/span> <span class=\"kwrd\">DATABASE<\/span> [Credit] <span class=\"kwrd\">MODIFY<\/span> <span class=\"kwrd\">FILE<\/span> (NAME = N<span class=\"str\">'CreditData'<\/span>, FILEGROWTH = 1MB);\r\n<span class=\"kwrd\">GO<\/span>\r\n<span class=\"rem\">-- Change file growth for data file to 1 MB (This is a bad thing to do!)<\/span>\r\n<span class=\"kwrd\">ALTER<\/span> <span class=\"kwrd\">DATABASE<\/span> [Credit] <span class=\"kwrd\">MODIFY<\/span> <span class=\"kwrd\">FILE<\/span> (NAME = N<span class=\"str\">'CreditLog'<\/span>, FILEGROWTH = 1MB)\r\n<span class=\"kwrd\">GO<\/span>\r\n\r\n<span class=\"rem\">-- Take a compressed full backup for a baseline<\/span>\r\n<span class=\"kwrd\">BACKUP<\/span> <span class=\"kwrd\">DATABASE<\/span> [Credit] \r\n<span class=\"kwrd\">TO<\/span>  <span class=\"kwrd\">DISK<\/span> = N<span class=\"str\">'C:\\SQLBackups\\CreditBackup110.bak'<\/span> <span class=\"kwrd\">WITH<\/span> NOFORMAT, INIT,  \r\nNAME = N<span class=\"str\">'Credit-Full Database Backup'<\/span>, SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 1;\r\n<span class=\"kwrd\">GO<\/span>\r\n-- <span class=\"kwrd\">End<\/span> <span class=\"kwrd\">of<\/span> initial preparation<\/pre>\n<style type=\"text\/css\"><!--\n.csharpcode, .csharpcode pre\n{\n\tfont-size: small;\n\tcolor: black;\n\tfont-family: consolas, \"Courier New\", courier, monospace;\n\tbackground-color: #ffffff;\n\t\/*white-space: pre;*\/\n}\n.csharpcode pre { margin: 0em; }\n.csharpcode .rem { color: #008000; }\n.csharpcode .kwrd { color: #0000ff; }\n.csharpcode .str { color: #006080; }\n.csharpcode .op { color: #0000c0; }\n.csharpcode .preproc { color: #cc6633; }\n.csharpcode .asp { background-color: #ffff00; }\n.csharpcode .html { color: #800000; }\n.csharpcode .attr { color: #ff0000; }\n.csharpcode .alt \n{\n\tbackground-color: #f4f4f4;\n\twidth: 100%;\n\tmargin: 0em;\n}\n.csharpcode .lnum { color: #606060; }\n--><\/style>\n<p><strong><span style=\"color: #000000;\">Listing 1: Initial Preparation and Backup of Baseline Database<\/span><\/strong><\/p>\n<p><strong><\/strong><\/p>\n<p><span style=\"color: #000000; font-size: large;\"><strong><span style=\"text-decoration: underline;\">Standard Test Details<\/span><\/strong><\/span><\/p>\n<p><span style=\"color: #000000;\">After the initial preparation, I ran a test, where I doubled the number of rows in the charge table every time I went through a loop, with a simple INSERT\u2026SELECT pattern, also incrementing the charge_dt to a later date for each new set of charges. I added some timing information, and gathered some information about cumulative waits, VLFs, and file sizes for each test run. I dropped and then restored the baseline SQL Server 2012 Credit database before each test run. I also restarted the SQL Server Service before each test run after I made my configuration changes. Listing 2 shows the standard test loop that I ran for each configuration. Listing 3 shows the cleanup I did after each test was done.<\/span><\/p>\n<pre class=\"csharpcode\"><span class=\"rem\">-- Start Standard Test<\/span>\r\n<span class=\"kwrd\">USE<\/span> Credit;\r\n<span class=\"kwrd\">GO<\/span>\r\n\r\n<span class=\"rem\">-- Supress done messages<\/span>\r\n<span class=\"kwrd\">SET<\/span> NOCOUNT <span class=\"kwrd\">ON<\/span>;\r\n\r\n<span class=\"rem\">-- Get starting row count and size of Charge table<\/span>\r\n<span class=\"kwrd\">EXEC<\/span> sp_spaceused N<span class=\"str\">'dbo.charge'<\/span>;\r\n\r\n<span class=\"rem\">-- Get intial VLF count<\/span>\r\n<span class=\"kwrd\">DBCC<\/span> LogInfo;\r\n\r\n<span class=\"rem\">-- Individual File Sizes and space available for current database  <\/span>\r\n<span class=\"kwrd\">SELECT<\/span> f.name <span class=\"kwrd\">AS<\/span> [<span class=\"kwrd\">File<\/span> Name] , f.physical_name <span class=\"kwrd\">AS<\/span> [Physical Name], \r\n<span class=\"kwrd\">CAST<\/span>((f.<span class=\"kwrd\">size<\/span>\/128.0) <span class=\"kwrd\">AS<\/span> <span class=\"kwrd\">decimal<\/span>(15,2)) <span class=\"kwrd\">AS<\/span> [Total <span class=\"kwrd\">Size<\/span> <span class=\"kwrd\">in<\/span> MB],\r\n<span class=\"kwrd\">CAST<\/span>(f.<span class=\"kwrd\">size<\/span>\/128.0 - <span class=\"kwrd\">CAST<\/span>(FILEPROPERTY(f.name, <span class=\"str\">'SpaceUsed'<\/span>) <span class=\"kwrd\">AS<\/span> <span class=\"kwrd\">int<\/span>)\/128.0 <span class=\"kwrd\">AS<\/span> <span class=\"kwrd\">decimal<\/span>(15,2)) \r\n<span class=\"kwrd\">AS<\/span> [Available <span class=\"kwrd\">Space<\/span> <span class=\"kwrd\">In<\/span> MB], [file_id], fg.name <span class=\"kwrd\">AS<\/span> [Filegroup Name]\r\n<span class=\"kwrd\">FROM<\/span> sys.database_files <span class=\"kwrd\">AS<\/span> f <span class=\"kwrd\">WITH<\/span> (NOLOCK) \r\n<span class=\"kwrd\">LEFT<\/span> <span class=\"kwrd\">OUTER<\/span> <span class=\"kwrd\">JOIN<\/span> sys.data_spaces <span class=\"kwrd\">AS<\/span> fg <span class=\"kwrd\">WITH<\/span> (NOLOCK) \r\n<span class=\"kwrd\">ON<\/span> f.data_space_id = fg.data_space_id <span class=\"kwrd\">OPTION<\/span> (RECOMPILE);\r\n\r\n<span class=\"rem\">-- Clear Wait Stats <\/span>\r\n<span class=\"kwrd\">DBCC<\/span> SQLPERF(<span class=\"str\">'sys.dm_os_wait_stats'<\/span>, CLEAR);\r\n\r\n<span class=\"rem\">-- Declare and initialize some variables<\/span>\r\n<span class=\"kwrd\">DECLARE<\/span> @LoopCount <span class=\"kwrd\">int<\/span> = 1;\r\n<span class=\"kwrd\">DECLARE<\/span> @DateDiff <span class=\"kwrd\">int<\/span> = 30;\r\n<span class=\"kwrd\">DECLARE<\/span> @<span class=\"kwrd\">Start<\/span> datetime = GETDATE();\r\n<span class=\"kwrd\">DECLARE<\/span> @LoopDone datetime;\r\n<span class=\"kwrd\">DECLARE<\/span> @<span class=\"kwrd\">End<\/span> datetime;\r\n\r\n<span class=\"rem\">-- Double the size of the charge table each time, changing charge_dt to a higher value each loop<\/span>\r\n<span class=\"kwrd\">WHILE<\/span> @LoopCount &lt; 6\r\n    <span class=\"kwrd\">BEGIN<\/span>\r\n        INSERT <span class=\"kwrd\">INTO<\/span> dbo.charge \r\n        (member_no, provider_no, category_no, charge_dt, charge_amt, statement_no, charge_code)\r\n        <span class=\"kwrd\">SELECT<\/span> member_no, provider_no, category_no, charge_dt + @DateDiff, charge_amt, statement_no, charge_code\r\n        <span class=\"kwrd\">FROM<\/span> dbo.charge;\r\n\r\n        <span class=\"rem\">-- Do some timing and housekeeping<\/span>\r\n        <span class=\"kwrd\">SET<\/span> @LoopDone = GETDATE();\r\n        <span class=\"kwrd\">PRINT<\/span> N<span class=\"str\">'Loop '<\/span> + <span class=\"kwrd\">CONVERT<\/span>(NVARCHAR(5), @LoopCount) + N<span class=\"str\">' done in '<\/span> + <span class=\"kwrd\">CONVERT<\/span>(NVARCHAR(5), DATEDIFF(<span class=\"kwrd\">second<\/span>, @<span class=\"kwrd\">Start<\/span>, @LoopDone)) + N<span class=\"str\">' seconds'<\/span>;\r\n        <span class=\"kwrd\">SET<\/span> @DateDiff += 30;\r\n        <span class=\"kwrd\">SET<\/span> @LoopCount += 1;\r\n    <span class=\"kwrd\">END<\/span>\r\n\r\n<span class=\"kwrd\">SET<\/span> @<span class=\"kwrd\">End<\/span> = GETDATE();\r\n\r\n<span class=\"kwrd\">SELECT<\/span> DATEDIFF(<span class=\"kwrd\">second<\/span>, @<span class=\"kwrd\">Start<\/span>, @<span class=\"kwrd\">End<\/span>) <span class=\"kwrd\">AS<\/span> [Total Elapsed <span class=\"kwrd\">Time<\/span> (sec)];\r\n\r\n<span class=\"rem\">-- Isolate top waits for server instance since last restart or statistics clear <\/span>\r\n<span class=\"rem\">-- SQL Server 2012 and newer specific version<\/span>\r\n<span class=\"kwrd\">WITH<\/span> Waits\r\n<span class=\"kwrd\">AS<\/span> (<span class=\"kwrd\">SELECT<\/span> wait_type, <span class=\"kwrd\">CAST<\/span>(wait_time_ms \/ 1000. <span class=\"kwrd\">AS<\/span> <span class=\"kwrd\">DECIMAL<\/span>(12, 2)) <span class=\"kwrd\">AS<\/span> [wait_time_s],\r\n    <span class=\"kwrd\">CAST<\/span>(100. * wait_time_ms \/ <span class=\"kwrd\">SUM<\/span>(wait_time_ms) <span class=\"kwrd\">OVER<\/span> () <span class=\"kwrd\">AS<\/span> <span class=\"kwrd\">decimal<\/span>(12,2)) <span class=\"kwrd\">AS<\/span> [pct],\r\n    ROW_NUMBER() <span class=\"kwrd\">OVER<\/span> (<span class=\"kwrd\">ORDER<\/span> <span class=\"kwrd\">BY<\/span> wait_time_ms <span class=\"kwrd\">DESC<\/span>) <span class=\"kwrd\">AS<\/span> rn\r\n    <span class=\"kwrd\">FROM<\/span> sys.dm_os_wait_stats <span class=\"kwrd\">WITH<\/span> (NOLOCK)\r\n    <span class=\"kwrd\">WHERE<\/span> wait_type <span class=\"kwrd\">NOT<\/span> <span class=\"kwrd\">IN<\/span> (N<span class=\"str\">'CLR_SEMAPHORE'<\/span>, N<span class=\"str\">'LAZYWRITER_SLEEP'<\/span>, N<span class=\"str\">'RESOURCE_QUEUE'<\/span>,N<span class=\"str\">'SLEEP_TASK'<\/span>,\r\n                            N<span class=\"str\">'SLEEP_SYSTEMTASK'<\/span>, N<span class=\"str\">'SQLTRACE_BUFFER_FLUSH'<\/span>, N<span class=\"str\">'WAITFOR'<\/span>, N<span class=\"str\">'LOGMGR_QUEUE'<\/span>,\r\n                            N<span class=\"str\">'CHECKPOINT_QUEUE'<\/span>, N<span class=\"str\">'REQUEST_FOR_DEADLOCK_SEARCH'<\/span>, N<span class=\"str\">'XE_TIMER_EVENT'<\/span>,\r\n                            N<span class=\"str\">'BROKER_TO_FLUSH'<\/span>, N<span class=\"str\">'BROKER_TASK_STOP'<\/span>, N<span class=\"str\">'CLR_MANUAL_EVENT'<\/span>, N<span class=\"str\">'CLR_AUTO_EVENT'<\/span>,\r\n                            N<span class=\"str\">'DISPATCHER_QUEUE_SEMAPHORE'<\/span> ,N<span class=\"str\">'FT_IFTS_SCHEDULER_IDLE_WAIT'<\/span>, N<span class=\"str\">'XE_DISPATCHER_WAIT'<\/span>,\r\n                            N<span class=\"str\">'XE_DISPATCHER_JOIN'<\/span>, N<span class=\"str\">'SQLTRACE_INCREMENTAL_FLUSH_SLEEP'<\/span>, N<span class=\"str\">'ONDEMAND_TASK_QUEUE'<\/span>,\r\n                            N<span class=\"str\">'BROKER_EVENTHANDLER'<\/span>, N<span class=\"str\">'SLEEP_BPOOL_FLUSH'<\/span>, N<span class=\"str\">'SLEEP_DBSTARTUP'<\/span>, N<span class=\"str\">'DIRTY_PAGE_POLL'<\/span>,\r\n                            N<span class=\"str\">'HADR_FILESTREAM_IOMGR_IOCOMPLETION'<\/span>,N<span class=\"str\">'SP_SERVER_DIAGNOSTICS_SLEEP'<\/span>)),\r\nRunning_Waits \r\n<span class=\"kwrd\">AS<\/span> (<span class=\"kwrd\">SELECT<\/span> W1.wait_type, wait_time_s, pct,\r\n    <span class=\"kwrd\">SUM<\/span>(pct) <span class=\"kwrd\">OVER<\/span>(<span class=\"kwrd\">ORDER<\/span> <span class=\"kwrd\">BY<\/span> pct <span class=\"kwrd\">DESC<\/span> <span class=\"kwrd\">ROWS<\/span> UNBOUNDED PRECEDING) <span class=\"kwrd\">AS<\/span> [running_pct]\r\n    <span class=\"kwrd\">FROM<\/span> Waits <span class=\"kwrd\">AS<\/span> W1)\r\n<span class=\"kwrd\">SELECT<\/span> wait_type, wait_time_s, pct, running_pct\r\n<span class=\"kwrd\">FROM<\/span> Running_Waits\r\n<span class=\"kwrd\">WHERE<\/span> running_pct - pct &lt;= 99\r\n<span class=\"kwrd\">ORDER<\/span> <span class=\"kwrd\">BY<\/span> running_pct\r\n<span class=\"kwrd\">OPTION<\/span> (RECOMPILE);\r\n\r\n<span class=\"rem\">-- Get ending VLF count of current database<\/span>\r\n<span class=\"kwrd\">DBCC<\/span> LogInfo;\r\n\r\n<span class=\"rem\">-- Individual File Sizes and space available for current database  <\/span>\r\n<span class=\"kwrd\">SELECT<\/span> f.name <span class=\"kwrd\">AS<\/span> [<span class=\"kwrd\">File<\/span> Name] , f.physical_name <span class=\"kwrd\">AS<\/span> [Physical Name], \r\n<span class=\"kwrd\">CAST<\/span>((f.<span class=\"kwrd\">size<\/span>\/128.0) <span class=\"kwrd\">AS<\/span> <span class=\"kwrd\">decimal<\/span>(15,2)) <span class=\"kwrd\">AS<\/span> [Total <span class=\"kwrd\">Size<\/span> <span class=\"kwrd\">in<\/span> MB],\r\n<span class=\"kwrd\">CAST<\/span>(f.<span class=\"kwrd\">size<\/span>\/128.0 - <span class=\"kwrd\">CAST<\/span>(FILEPROPERTY(f.name, <span class=\"str\">'SpaceUsed'<\/span>) <span class=\"kwrd\">AS<\/span> <span class=\"kwrd\">int<\/span>)\/128.0 <span class=\"kwrd\">AS<\/span> <span class=\"kwrd\">decimal<\/span>(15,2)) \r\n<span class=\"kwrd\">AS<\/span> [Available <span class=\"kwrd\">Space<\/span> <span class=\"kwrd\">In<\/span> MB], [file_id], fg.name <span class=\"kwrd\">AS<\/span> [Filegroup Name]\r\n<span class=\"kwrd\">FROM<\/span> sys.database_files <span class=\"kwrd\">AS<\/span> f <span class=\"kwrd\">WITH<\/span> (NOLOCK) \r\n<span class=\"kwrd\">LEFT<\/span> <span class=\"kwrd\">OUTER<\/span> <span class=\"kwrd\">JOIN<\/span> sys.data_spaces <span class=\"kwrd\">AS<\/span> fg <span class=\"kwrd\">WITH<\/span> (NOLOCK) \r\n<span class=\"kwrd\">ON<\/span> f.data_space_id = fg.data_space_id <span class=\"kwrd\">OPTION<\/span> (RECOMPILE);\r\n\r\n<span class=\"rem\">-- Get ending row count and size of Charge table<\/span>\r\n<span class=\"kwrd\">EXEC<\/span> sp_spaceused N<span class=\"str\">'dbo.charge'<\/span>;\r\n<span class=\"kwrd\">GO<\/span>\r\n-- <span class=\"kwrd\">End<\/span> Standard Test<\/pre>\n<style type=\"text\/css\"><!--\n.csharpcode, .csharpcode pre\n{\n\tfont-size: small;\n\tcolor: black;\n\tfont-family: consolas, \"Courier New\", courier, monospace;\n\tbackground-color: #ffffff;\n\t\/*white-space: pre;*\/\n}\n.csharpcode pre { margin: 0em; }\n.csharpcode .rem { color: #008000; }\n.csharpcode .kwrd { color: #0000ff; }\n.csharpcode .str { color: #006080; }\n.csharpcode .op { color: #0000c0; }\n.csharpcode .preproc { color: #cc6633; }\n.csharpcode .asp { background-color: #ffff00; }\n.csharpcode .html { color: #800000; }\n.csharpcode .attr { color: #ff0000; }\n.csharpcode .alt \n{\n\tbackground-color: #f4f4f4;\n\twidth: 100%;\n\tmargin: 0em;\n}\n.csharpcode .lnum { color: #606060; }\n--><\/style>\n<p><strong><span style=\"color: #000000;\">Listing 2: Standard Test<\/span><\/strong><\/p>\n<pre class=\"csharpcode\"><span class=\"rem\">-- Cleanup after each test run<\/span>\r\n<span class=\"rem\">-- Drop Credit database<\/span>\r\n<span class=\"kwrd\">USE<\/span> [master]\r\n<span class=\"kwrd\">GO<\/span>\r\n<span class=\"kwrd\">ALTER<\/span> <span class=\"kwrd\">DATABASE<\/span> [Credit] <span class=\"kwrd\">SET<\/span>  SINGLE_USER <span class=\"kwrd\">WITH<\/span> <span class=\"kwrd\">ROLLBACK<\/span> <span class=\"kwrd\">IMMEDIATE<\/span>;\r\n<span class=\"kwrd\">GO<\/span>\r\n<span class=\"kwrd\">DROP<\/span> <span class=\"kwrd\">DATABASE<\/span> [Credit];\r\n<span class=\"kwrd\">GO<\/span>\r\n\r\n<span class=\"rem\">-- Restore from our initial baseline SQL Server 2012 full backup<\/span>\r\n<span class=\"kwrd\">RESTORE<\/span> <span class=\"kwrd\">DATABASE<\/span> [Credit] \r\n<span class=\"kwrd\">FROM<\/span>  <span class=\"kwrd\">DISK<\/span> = N<span class=\"str\">'C:\\SQLBackups\\CreditBackup110.bak'<\/span> <span class=\"kwrd\">WITH<\/span>  <span class=\"kwrd\">FILE<\/span> = 1,  \r\nMOVE N<span class=\"str\">'CreditData'<\/span> <span class=\"kwrd\">TO<\/span> N<span class=\"str\">'C:\\SQLData\\CreditData.mdf'<\/span>,  \r\nMOVE N<span class=\"str\">'CreditLog'<\/span> <span class=\"kwrd\">TO<\/span> N<span class=\"str\">'L:\\SQLLogs\\CreditLog.ldf'<\/span>,  NOUNLOAD,  STATS = 1;\r\n<span class=\"kwrd\">GO<\/span>\r\n\r\n<span class=\"rem\">-- Restart SQL Server Service<\/span>\r\n\r\n-- <span class=\"kwrd\">End<\/span> <span class=\"kwrd\">of<\/span> Cleanup<\/pre>\n<style type=\"text\/css\"><!--\n.csharpcode, .csharpcode pre\n{\n\tfont-size: small;\n\tcolor: black;\n\tfont-family: consolas, \"Courier New\", courier, monospace;\n\tbackground-color: #ffffff;\n\t\/*white-space: pre;*\/\n}\n.csharpcode pre { margin: 0em; }\n.csharpcode .rem { color: #008000; }\n.csharpcode .kwrd { color: #0000ff; }\n.csharpcode .str { color: #006080; }\n.csharpcode .op { color: #0000c0; }\n.csharpcode .preproc { color: #cc6633; }\n.csharpcode .asp { background-color: #ffff00; }\n.csharpcode .html { color: #800000; }\n.csharpcode .attr { color: #ff0000; }\n.csharpcode .alt \n{\n\tbackground-color: #f4f4f4;\n\twidth: 100%;\n\tmargin: 0em;\n}\n.csharpcode .lnum { color: #606060; }\n--><\/style>\n<p><strong><span style=\"color: #000000;\">Listing 3: Cleanup After Each Test Run<\/span><\/strong><\/p>\n<p><span style=\"color: #000000;\">After all of this preparation, what did I actually want to test? Each test would measure the elapsed time and ending Virtual Log File (VLF) count after inserting 49.4 million rows into the credit table, in ever larger batches of INSERTs. I also wanted to see what the top cumulative wait statistics were for each test run.<\/span><\/p>\n<p><span style=\"color: #000000; font-size: large;\"><strong><span style=\"text-decoration: underline;\">Test Configurations and Results<\/span><\/strong><\/span><\/p>\n<p><span style=\"color: #000000; font-size: medium;\"><strong><span style=\"text-decoration: underline;\">Test 1<\/span><\/strong><\/span><\/p>\n<p><span style=\"color: #000000;\">Disable Windows Instant File Initialization (IFI), which is the default condition when you install SQL Server, unless you choose to enable it. I used the very bad autogrowth value of 1MB for both the data file and the log file of the database. This is a very bad combination that you should not emulate! I expected this configuration to perform relatively poorly, and I was not surprised by the results. This test took 1074 seconds to complete, and the VLF count went up to 126,399. Table 1 shows the top cumulative wait types during this test run. The PREEMPTIVE_OS_xxx waits are related to the fact that we have such a small file autogrowth increment and that Windows Instant File Initialization is not enabled. <\/span><\/p>\n<p>&nbsp;<\/p>\n<table width=\"600\" border=\"0\" cellspacing=\"0\" cellpadding=\"2\">\n<tbody>\n<tr>\n<td valign=\"top\" width=\"200\"><strong><span style=\"text-decoration: underline;\"><span style=\"font-size: small;\">Wait Type<\/span><\/span><\/strong><\/td>\n<td valign=\"top\" width=\"200\"><strong><span style=\"text-decoration: underline;\"><span style=\"font-size: small;\">Percent<\/span><\/span><\/strong><\/td>\n<td valign=\"top\" width=\"200\"><strong><span style=\"text-decoration: underline;\"><span style=\"font-size: small;\">Running Percent<\/span><\/span><\/strong><\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">CXPACKET<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">72.22<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">72.22<\/span><\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">PREEMPTIVE_OS_FLUSHFILEBUFFERS<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">13.01<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">85.23<\/span><\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">PREEMPTIVE_OS_WRITEFILEGATHER<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">6.52<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">91.75<\/span><\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">WRITELOG<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">2.13<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">93.88<\/span><\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">PREEMPTIVE_OS_FILEOPS<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">1.39<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">95.27<\/span><\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">PAGEIOLATCH_EX<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">0.97<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">96.24<\/span><\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">ASYNC_IO_COMPLETION<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">0.82<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">97.06<\/span><\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">WRITE_COMPLETION<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">0.71<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">97.77<\/span><\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">IO_COMPLETION<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">0.67<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">98.44<\/span><\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">LATCH_SH<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">0.62<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">99.06<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p><span style=\"font-size: small;\"><strong>Table 1: Test 1 Cumulative Wait Types<\/strong><\/span><\/p>\n<p><strong><\/strong><\/p>\n<p><strong><span style=\"text-decoration: underline;\">Test 2<\/span><\/strong><\/p>\n<p><span style=\"color: #000000;\">Enable Windows Instant File Initialization (making sure to restart the SQL Server Service). I used the same bad autogrowth value of 1MB for both the data file and the log file of the database.\u00a0 I expected this configuration to still perform relatively poorly. This test took 1069 seconds to complete, and the VLF count went up to 126,399. The lower elapsed time was really within the margin of error, so just enabling Windows Instant File Initialization was not really helping in a meaningful way (by itself). Keep in mind that IFI only affects the data file, not the log file. Table 2 shows the top cumulative wait types during this test run. The cumulative wait types during Test 2 did not really change in a meaningful way.<\/span><\/p>\n<table width=\"602\" border=\"0\" cellspacing=\"0\" cellpadding=\"2\">\n<tbody>\n<tr>\n<td valign=\"top\" width=\"200\"><strong><span style=\"text-decoration: underline;\"><span style=\"font-size: small;\">Wait Type<\/span><\/span><\/strong><\/td>\n<td valign=\"top\" width=\"200\"><strong><span style=\"text-decoration: underline;\"><span style=\"font-size: small;\">Percent<\/span><\/span><\/strong><\/td>\n<td valign=\"top\" width=\"200\"><strong><span style=\"text-decoration: underline;\"><span style=\"font-size: small;\">Running Percent<\/span><\/span><\/strong><\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">CXPACKET<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">72.88<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">72.88<\/span><\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">PREEMPTIVE_OS_FLUSHFILEBUFFERS<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">13.34<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">86.22<\/span><\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">PREEMPTIVE_OS_WRITEFILEGATHER<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">6.4<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">92.62<\/span><\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">PREEMPTIVE_OS_FILEOPS<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">1.39<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">94.01<\/span><\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">WRITELOG<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">1.12<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">95.13<\/span><\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">WRITE_COMPLETION<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">1.01<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">96.14<\/span><\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">PAGEIOLATCH_EX<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">0.87<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">97.01<\/span><\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">ASYNCH_IO_COMPLETION<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">0.83<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">97.84<\/span><\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">LATCH_SH<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">0.67<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">98.51<\/span><\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">IO_COMPLETION<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">0.65<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">99.16<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p><span style=\"font-size: small;\"><strong>Table 2: Test 2 Cumulative Wait Types<\/strong><\/span><\/p>\n<p><strong><\/strong><\/p>\n<p><span style=\"color: #000000; font-size: medium;\"><strong><span style=\"text-decoration: underline;\">Test 3<\/span><\/strong><\/span><\/p>\n<p><span style=\"color: #000000;\">Enable Windows Instant File Initialization (making sure to restart the SQL Server Service). I changed the autogrowth value to a more reasonable 1024MB for both the data file and the log file of the database.\u00a0 I expected this configuration to perform much better, with a reduced VLF count in the log file. This test took 771 seconds to complete, and the VLF count only went up to 246, which was much better. Table 3 shows the top cumulative wait types during this test run. We can see that PREEMPTIVE_OS_FLUSHFILEBUFFERS drops completely off the list when we have much fewer file growths during the test.<\/span><\/p>\n<table width=\"602\" border=\"0\" cellspacing=\"0\" cellpadding=\"2\">\n<tbody>\n<tr>\n<td valign=\"top\" width=\"200\"><strong><span style=\"text-decoration: underline;\"><span style=\"font-size: small;\">Wait Type<\/span><\/span><\/strong><\/td>\n<td valign=\"top\" width=\"200\"><strong><span style=\"text-decoration: underline;\"><span style=\"font-size: small;\">Percent<\/span><\/span><\/strong><\/td>\n<td valign=\"top\" width=\"200\"><strong><span style=\"text-decoration: underline;\"><span style=\"font-size: small;\">Running Percent<\/span><\/span><\/strong><\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">CXPACKET<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">88.95<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">88.95<\/span><\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">PREEMPTIVE_OS_WRITEFILEGATHER<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">7.48<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">96.43<\/span><\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">ASYNCH_IO_COMPLETION<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">1.11<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">97.54<\/span><\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">PAGEIOLATCH_EX<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">0.89<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">98.43<\/span><\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">LATCH_SH<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">0.88<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">99.31<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p><span style=\"font-size: small;\"><strong>Table 3: Test 3 Cumulative Wait Types<\/strong><\/span><\/p>\n<p><strong><\/strong><\/p>\n<p><span style=\"color: #000000; font-size: medium;\"><strong><span style=\"text-decoration: underline;\">Test 4<\/span><\/strong><\/span><\/p>\n<p><span style=\"color: #000000;\">Enable Windows Instant File Initialization (making sure to restart the SQL Server Service). I changed the autogrowth value to a more reasonable 1024MB for both the data file and the log file of the database.\u00a0 I also pre-grew the log file to 32000MB, in 4000MB increments (to reduce the VLF count and eliminate any log file autogrowths during the test). <\/span><span style=\"color: #000000;\">I expected this configuration to perform a little better than Test 3, with a reduced VLF count in the log file. This test took 711 seconds to complete, and the VLF count only went up to 127, which was even better. Table 4 shows the top cumulative wait types during this test run. Notice that the PREEMPTIVE_OS_WRITEFILEGATHER wait type is greatly reduced by this latest configuration, since we are only seeing a few file growths for the data file and none for the log file.<\/span><\/p>\n<table width=\"602\" border=\"0\" cellspacing=\"0\" cellpadding=\"2\">\n<tbody>\n<tr>\n<td valign=\"top\" width=\"200\"><strong><span style=\"text-decoration: underline;\"><span style=\"font-size: small;\">Wait Type<\/span><\/span><\/strong><\/td>\n<td valign=\"top\" width=\"200\"><strong><span style=\"text-decoration: underline;\"><span style=\"font-size: small;\">Percent<\/span><\/span><\/strong><\/td>\n<td valign=\"top\" width=\"200\"><strong><span style=\"text-decoration: underline;\"><span style=\"font-size: small;\">Running Percent<\/span><\/span><\/strong><\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">CXPACKET<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">94.74<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">94.74<\/span><\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">PREEMPTIVE_OS_WRITEFILEGATHER<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">1.57<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">96.31<\/span><\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">ASYNCH_IO_COMPLETION<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">1.13<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">97.44<\/span><\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">PAGEIOLATCH_EX<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">1.12<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">98.56<\/span><\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">LATCH_SH<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">0.94<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">99.50<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p><span style=\"font-size: small;\"><strong>Table 4: Test 4 Cumulative Wait Types<\/strong><\/span><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #000000; font-size: medium;\"><strong><span style=\"text-decoration: underline;\">Test 5<\/span><\/strong><\/span><\/p>\n<p><span style=\"color: #000000;\">Test 5 used the same configuration steps as Test 4, except that I changed the instance-level MAXDOP setting to 4 (since I had a single, quad-core processor with hyper-threading enabled). This change had no measurable effect, taking 716 seconds to complete, and the VLF count stayed at 127. Since there is only one NUMA node on my desktop machine, I did not expect any improvement here. Table 5 shows the top cumulative wait types during this test run. <\/span><\/p>\n<table width=\"602\" border=\"0\" cellspacing=\"0\" cellpadding=\"2\">\n<tbody>\n<tr>\n<td valign=\"top\" width=\"200\"><strong><span style=\"text-decoration: underline;\"><span style=\"font-size: small;\">Wait Type<\/span><\/span><\/strong><\/td>\n<td valign=\"top\" width=\"200\"><strong><span style=\"text-decoration: underline;\"><span style=\"font-size: small;\">Percent<\/span><\/span><\/strong><\/td>\n<td valign=\"top\" width=\"200\"><strong><span style=\"text-decoration: underline;\"><span style=\"font-size: small;\">Running Percent<\/span><\/span><\/strong><\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">CXPACKET<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">92.69<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">92.69<\/span><\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">PREEMPTIVE_OS_WRITEFILEGATHER<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">2.71<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">95.40<\/span><\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">ASYNCH_IO_COMPLETION<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">1.89<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">97.29<\/span><\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">PAGEIOLATCH_EX<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">1.68<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">98.97<\/span><\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">LATCH_SH<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">0.63<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">99.60<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p><span style=\"font-size: small;\"><strong>Table 5: Test 5 Cumulative Wait Types<\/strong><\/span><\/p>\n<p><span style=\"color: #000000; font-size: medium;\"><strong><span style=\"text-decoration: underline;\">Test 6<\/span><\/strong><\/span><\/p>\n<p><span style=\"color: #000000;\">Test 6 used the same configuration steps as Test 5, except that I changed the tempdb configuration by adding three additional tempdb data files and I made all of the tempdb data files the same initial size (2048MB) with an autogrowth increment of 1024MB. I also made the tempdb log file a larger 1024MB initial size, with an autogrowth increment of 1024MB. <\/span><span style=\"color: #000000;\">This change also had no measurable effect, taking 723 seconds to complete, with the VLF count staying at 127. This was somewhat of a surprise, since I could see the original tempdb data file being hit pretty hard during the test runs. Table 6 shows the top cumulative wait types during this test run. <\/span><\/p>\n<table width=\"602\" border=\"0\" cellspacing=\"0\" cellpadding=\"2\">\n<tbody>\n<tr>\n<td valign=\"top\" width=\"200\"><strong><span style=\"text-decoration: underline;\"><span style=\"font-size: small;\">Wait Type<\/span><\/span><\/strong><\/td>\n<td valign=\"top\" width=\"200\"><strong><span style=\"text-decoration: underline;\"><span style=\"font-size: small;\">Percent<\/span><\/span><\/strong><\/td>\n<td valign=\"top\" width=\"200\"><strong><span style=\"text-decoration: underline;\"><span style=\"font-size: small;\">Running Percent<\/span><\/span><\/strong><\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">CXPACKET<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">94.07<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">94.07<\/span><\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">ASYNCH_IO_COMPLETION<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">1.96<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">96.03<\/span><\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">PREEMPTIVE_OS_WRITEFILEGATHER<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">1.96<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">97.99<\/span><\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">PAGEIOLATCH_EX<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">1.83<\/span><\/td>\n<td valign=\"top\" width=\"200\"><span style=\"font-size: small;\">99.82<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p><span style=\"font-size: small;\"><strong>Table 6: Test 6 Cumulative Wait Types<\/strong><\/span><\/p>\n<p><span style=\"font-size: large;\"><strong><span style=\"text-decoration: underline;\">Part One Conclusions<\/span><\/strong><\/span><\/p>\n<p>We can see from these tests that it is very beneficial to make sure that you use a reasonable autogrowth increment size for both your data file(s) and log file rather than an extremely small autogrowth increment size. It is also very beneficial to manually pre-grow your log file to an appropriate size, in 4000MB increments so that it does not have to autogrow during normal and maintenance operations. This will also keep your VLF count under control.<\/p>\n<p>It is also beneficial to enable Windows Instant File Initialization (IFI), even though it only affects your SQL Server data file(s). More tests in Part 2 will show some of the other advantages of IFI for SQL Server. You can get the complete script and some more detailed results here. Table 7 shows the overall test results for Part 1. Keep in mind that this testing is using relatively high performance, consumer-level SSDs, so the negative effects of lots of small file growths in the data and log files are not as severe as they might otherwise be with magnetic disk storage.<\/p>\n<table width=\"600\" border=\"0\" cellspacing=\"0\" cellpadding=\"2\">\n<tbody>\n<tr>\n<td valign=\"top\" width=\"200\">Test Run<\/td>\n<td valign=\"top\" width=\"200\">Elapsed Time<\/td>\n<td valign=\"top\" width=\"200\">VLF Count<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"200\">Test\u00a0 1<\/td>\n<td valign=\"top\" width=\"200\">1074 seconds<\/td>\n<td valign=\"top\" width=\"200\">126,399<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"200\">Test 2<\/td>\n<td valign=\"top\" width=\"200\">1069 seconds<\/td>\n<td valign=\"top\" width=\"200\">126,399<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"200\">Test 3<\/td>\n<td valign=\"top\" width=\"200\">771 seconds<\/td>\n<td valign=\"top\" width=\"200\">246<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"200\">Test 4<\/td>\n<td valign=\"top\" width=\"200\">711 seconds<\/td>\n<td valign=\"top\" width=\"200\">127<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"200\">Test 5<\/td>\n<td valign=\"top\" width=\"200\">716 seconds<\/td>\n<td valign=\"top\" width=\"200\">127<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"200\">Test 6<\/td>\n<td valign=\"top\" width=\"200\">723 seconds<\/td>\n<td valign=\"top\" width=\"200\">127<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p><span style=\"font-size: small;\"><strong>Table 7: Part One Test Results<\/strong><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Since SQL Server 2012 is relatively easy to install and get running, using just the default configuration settings, I thought it would be interesting to explore the negative performance effects of some of these default configuration settings, using a simple, repeatable test. My test machine is a desktop machine with an Intel Core i7-3770K processor [&hellip;]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[31,175,174],"tags":[339],"class_list":["post-820","post","type-post","status-publish","format-standard","hentry","category-sql-server-2012","category-sql-server-configuration","category-vlf","tag-vlf"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Experiments with SQL Server VLFs, Part 1 - Glenn Berry<\/title>\n<meta name=\"description\" content=\"Describes some experiments with SQL Server VLFs and the negative effect they have on performance\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/glenn\/experiments-with-sql-server-vlfs-part-1\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Experiments with SQL Server VLFs, Part 1 - Glenn Berry\" \/>\n<meta property=\"og:description\" content=\"Describes some experiments with SQL Server VLFs and the negative effect they have on performance\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/glenn\/experiments-with-sql-server-vlfs-part-1\/\" \/>\n<meta property=\"og:site_name\" content=\"Glenn Berry\" \/>\n<meta property=\"article:published_time\" content=\"2013-08-05T22:38:42+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2018-11-13T18:52:11+00:00\" \/>\n<meta name=\"author\" content=\"Glenn Berry\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Glenn Berry\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"12 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/experiments-with-sql-server-vlfs-part-1\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/experiments-with-sql-server-vlfs-part-1\/\",\"name\":\"Experiments with SQL Server VLFs, Part 1 - Glenn Berry\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website\"},\"datePublished\":\"2013-08-05T22:38:42+00:00\",\"dateModified\":\"2018-11-13T18:52:11+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7\"},\"description\":\"Describes some experiments with SQL Server VLFs and the negative effect they have on performance\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/experiments-with-sql-server-vlfs-part-1\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/glenn\/experiments-with-sql-server-vlfs-part-1\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/experiments-with-sql-server-vlfs-part-1\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Experiments with SQL Server VLFs, Part 1\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/\",\"name\":\"Glenn Berry\",\"description\":\"Semi-random musings about SQL Server performance\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7\",\"name\":\"Glenn Berry\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g\",\"caption\":\"Glenn Berry\"},\"sameAs\":[\"https:\/\/www.sqlskills.com\/blogs\/glenn\/\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/author\/glenn\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Experiments with SQL Server VLFs, Part 1 - Glenn Berry","description":"Describes some experiments with SQL Server VLFs and the negative effect they have on performance","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/glenn\/experiments-with-sql-server-vlfs-part-1\/","og_locale":"en_US","og_type":"article","og_title":"Experiments with SQL Server VLFs, Part 1 - Glenn Berry","og_description":"Describes some experiments with SQL Server VLFs and the negative effect they have on performance","og_url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/experiments-with-sql-server-vlfs-part-1\/","og_site_name":"Glenn Berry","article_published_time":"2013-08-05T22:38:42+00:00","article_modified_time":"2018-11-13T18:52:11+00:00","author":"Glenn Berry","twitter_misc":{"Written by":"Glenn Berry","Est. reading time":"12 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/experiments-with-sql-server-vlfs-part-1\/","url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/experiments-with-sql-server-vlfs-part-1\/","name":"Experiments with SQL Server VLFs, Part 1 - Glenn Berry","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website"},"datePublished":"2013-08-05T22:38:42+00:00","dateModified":"2018-11-13T18:52:11+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7"},"description":"Describes some experiments with SQL Server VLFs and the negative effect they have on performance","breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/experiments-with-sql-server-vlfs-part-1\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/glenn\/experiments-with-sql-server-vlfs-part-1\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/experiments-with-sql-server-vlfs-part-1\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/glenn\/"},{"@type":"ListItem","position":2,"name":"Experiments with SQL Server VLFs, Part 1"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/","name":"Glenn Berry","description":"Semi-random musings about SQL Server performance","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/glenn\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7","name":"Glenn Berry","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g","caption":"Glenn Berry"},"sameAs":["https:\/\/www.sqlskills.com\/blogs\/glenn\/"],"url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/author\/glenn\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts\/820","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/comments?post=820"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts\/820\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/media?parent=820"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/categories?post=820"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/tags?post=820"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}