Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

SQL File System Questions

  • 25 September 2013
  • Author: Roger Wolter
  • Number of views: 999
  • 0 Comments

 

 

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.
Print
Categories: SQL Server
Tags:
Rate this article:
5.0

Roger Wolter Roger Wolter

Other posts by Roger Wolter

Please login or register to post comments.