SQL File System Questions

I did a presentation on the SQL File system Tuesday for Training on the Ts
The answers to questions asked during the presentation follow:

Questions Asked by Attendee

Q: Are there any changes in the file internals for Sql 2014? Does Sql Azure have the same file architecture? Does it expose the same data and log files as on premise Sql Server versions? A: I'm not aware of any changes to the filesystem in 2014.  There are always performance improvement in each release but the basic file structure that we talked about hasn't changed for several releases.  SQL Azure uses a very similar physical structure but a Azure database is a logical database and the physical structure of the database isn't exposed to the user.

Q: Back in the day, we used to try and always allocate our DBs in allocations & growth sizes that correlated (fit) onto 8k pages (size of physical disk page). What is the physical size these days, 64bit etc? A: Good point.  SQL Server space is allocated in 64k chunks so if the file size isn't evenly divisible by  64k there will be some unused space at the end of the file.

Q: can u please share the sqlserver capacity Planning templates or URL's A: Unfortunately, I haven't ever run into a good SQL Server capacity planning system.  I generally come up with a  solution tailored to the particular application.  Here are some of the links I use for   information:  http://msdn.microsoft.com/en-us/library/ms978394.aspx     http://technet.microsoft.com/en-us/library/cc298801.aspx  http://technet.microsoft.com/en-us/library/ms345368(v=SQL.105).aspx

Q: how allocation is different, when allocation unit set to 64k A: Not sure what you're asking.  Allocation is always 64k.  Are you talking about NTFS file system block sizes?  If so, an NTFS block size of 64k is recommended for SQL Server

Q: I heard from another DBA that he creates 8 files automaticaly for TempDB, no matter the   server's setup. What are you comments from this? A: Not a bad thing to do.  I don't see any reason why that would cause a problem on a 4 core box.  The only down side is a little extra complexity.

Q: If temp space is requested by an application, does SQL allow it to use only one of the 8 files allocated to tempdb or can it use multiple? A: SQL Server allocates space from the available files in a round robbin fashion if the files are the same size.  If the files are different sizes SQL uses something called proportional fill to allocate more space from the larger files so they stay approximately the same percent full.    This is why all files for a filegroup should be the same size.  If not the big file is a hot spot.

Q: is anyone else having audio issues? A: Sorry about that - maybe there was a network issue

Q: is it recommended to run disk defrag where SQL mdfs and ndfs are located? A: Yes but it's only necessary if the disk is fragmented.  If your files don't autogrow the disk   volume shouldn't get fragmented.

Q: Is it worthwhile to update an existing database minimum file size after the database is created and in use? A: Yes, if the data size is increasing you can prevent further fragmentation.

Q: To Defrag the Windows filegroup, don't we have to thake the DB files offline, defrag   windows file, then bring SQL file online and Defrag it? A: Yes Q: Best Practice we employ - use create table for tempDB's otherwise it locks all the Allocation Maps until the entire insert into is completed A: True.  Good point

Q: Very good presentation.  Thank you so very much. A: --unanswered-- Q: How can I defrag the SQL file system after doing a windows file system defrag. A: The SQL file system doesn't get fragmented but SQL indexes do.  Rebuilding the indexes willl remove that fragmentation

Q: When does truncating a table reallocate space to be reused? A: First, I want to be sure you're not talking about the Windows file size decreasing when tables are truncated.  The windows file doesn't ever shrink unless you shrink the database which I definitely don't recommend.  If you are asking when the pages allocated to the table are freed up after a trancate table, they are freed after the next log file backup.  There' a misconception that truncate is not logged.  It is actually minimally   logged - the list of pages allocated to the table is written to the log and in the next log backup, those pages are written to the backup.

Q: with today's SANs, how can you control fragmentation? A: SAN volumes can be defragmented with the defrage tool of your choice.

Q: Would multiple files on user databases help reduce deadlocks? A: Unfortunately probably not.  Allocation contention doesn't generally cause deadlocks.  It's worth doing anyway but I haven't ever run into a deadlock caused by allocation contention.