SQL Server Data Files and File Groups Maintenance

• One SQL Server Instance can have Upto 32,767 Databases and
• Each Database can have upto 32,767 files (Log + Data).
• MDF -  Main/Primary Data File.
• NDF - Secondary Data File
• LDF - Log Data File. (We only need one log file)
•  File Groups
• Every database have one file group (Primary).
• Multiple Secondary file group can be added.
• We can have multiple file groups for 
• Manageability
• Performace
• Availability
•  With multiple files are there, two allocation algorithms kick-in
• Round-robin Allocation
• Proportional Fill
• Allocations are made from each data file during round robin proportional to amount of free space in each file.
• Weightings recalculated afyer every 8192 allocations or after every file add/drop/restore
• Buffer Pool/Cache
• Buffer pool is a block of memory used to hold in-memory copies of pages from the data file.
• Pages are read into buffer pool when they are requested and are not already in memory.
• When a page is read from disk, this is a physical I/O.
• When a page is already in memory, this is a logical I/O.
• All I/O start as logical requests and may become physical.
• Page lifetime in cache depends on
• Two last access times.
• When SQL Server falls short of memory, the least recently used pages are tossed out of the buffer pool to make room for new pages.

• Hash tables are maintained to quickly find page images in memory. 

• Creating Or Growing Data Files
• Whenever a data file is created or grown, it's 'high-water mark' must be set in NTFS
• This is the point in the file upto which NTFS knows the data is trustworthy and will allow it to be read.
• In SQL Server 2000 and by default in later versions, this  is done by zerowing out new/additional space
• This is done by SQL server by issuing successive writes of zeros to file.
• The new/additional space cannot be used until the process completes and the allocation that triggered it, will pause until it is done.
• In SQL Server 2005+ this process can be skipped by enabling instant file initialization(aka instant initialization)
• Only applies to data files.
• Implementing Instant File Initialization
• Can't be done from within SQL Server.
• Requires
• Windows XP Sp2, Windows 2003 Or Later Version
• NTFS Volumes
• SQL Server 2005 Or later, any edition
• "Perform Volume maintenance Tasks" (aka SE_MANAGE-VOLUME_NAME) security permissions granted to SQL server service account.
• Use Local Security Policy Editor to grant the permission
• Administrative Tools - Local Security Policy and then Local Policies - User Rights assignment
• Defaults to Local Administrators Group
• Grant this to lower priveledge accounts.

• Instant file initialization happens automatically once SQL Server is restarted after granting the permission.
• Trace Flag 3004 & 3604 can be used to monitor file growth to see if Instant File Initialization is enabled or not.

•  Auto Grow
• When a file full, it will grow to prove more space.
• By default new space is zero initialized, which takes time 
• Allocation pauses while the grow takes place.
• Default value for autogrowth is
• 1 MB in SQL Server 2005 onwards, 10% in previous versions.
• 10% for log files.
• Auto Shrink
• Not at all recommended.
• Introduces index fragmentation within data files.
• We can't control when it kicks in and affects performance.
• The database is most likely to auto-grow again and then auto-shrink.

•  When to Use Data File Shrink
• Causes index fragmentation.
• Can be used in following situations
• Emptying a file before removing it.
• When a large amount of data has been deleted AND the space won't be reused.
• Moving a file/filegroup/database to read-only.
• How to Shrink a Data File?
• Create new filegroup and move all indexes into it.
• Use CREATE INDES...WITH DROP_EXISTING and specify location to new filegroup
• can be performed online.
•  If any table are heaps, use shrink to move them
• but will be very slow in 2005+
• Drop old filegroup
•  We can also shrink data file by index reorganize, don't do index rebuild because this will only increase data file.

• TempDB Uses
• User Objects
• Any user Created Table
• Table variables
• If they get large enough to sill to disk.
• #Temp tables/Indexes
• Global ##Temp Tables/Indexes

• Internal Objects
• Worktables(e.g Sort, hash joins, order by, ...)  from memory spills to disk.
• Intermediate sort results from sort.
• Intermediate sort results from index create/reduilds
• Onlu if SORT_IN_TempDB is specified.
• Intermediate DBCC CheckDB results
• Version Store
• The Common version store, which underpins
• Snapshot isolation/read-committed snap shot isolation.
• DML triggers(for inserted and deleted tables)
• MARS(Multiple Actie Result Sets)
• The Online index operations version store.

• Internal Objects
• Temporary LOB storage
• LOB data used as parameters, variables.

 

• Contention in TempDB
• TempDB is very susceptible to contention issues because there's only one tempdb per instance to support all user database operations.
• Contention can be
• PAGEIOLATCH
• thread waiting for a page to be read from disk.
• This can be alleviated by 
• Creating multiple data files, and/or
• Multiple threads competing for access to an in-memory page.
• PAGELATCH
• Multiple threads competing for access to an in-memory page.
• Trace Flag 1118
• Turns OFF use of mixed extents(Single-page allocations) in tempdb only.
• Removes SGAM Contention.
• Creating Multiple data files reduces contention.

 

• Queries for  troubleshooting
• What Tasks are taking up space right now.
• SELECT
      t1.session_id
      ,t1.request_id
      ,t1.Task_Alloc
      ,t1.Task_Dealloc
      ,t2.sql_handle
      ,t2.statement_start_offset
      ,t2.statement_end_offset
      ,t2.plan_handle
  FROM
  (SELECT
       session_id
      ,request_id
      ,sum(internal_objects_alloc_page_count) as Task_Alloc
      ,sum(internal_objects_dealloc_page_count) as Task_Dealloc
  FROM sys.dm_db_task_space_usage
  GROUP BY session_id ,request_id) AS t1,
  sys.dm_exec_requests t2
  WHERE t1.session_id = t2.session_id AND
        (t1.request_id = t2.request_id) AND
        (t1.Task_Alloc + t1.Task_Dealloc > 0)
  ORDER BY t1.Task_Alloc DESC;                           

• What the Task Doing
• SELECT * FROM sys.dm_exec_sql_text(<TaskID>);
• SELECT * FROM sys.dm_exec_query_plan(<PlanID>)