none
Proof of concept by Monitoring the Data in MDF and NDF Files

    General discussion

  • Hi All:

    As per the below statement from Microsoft:

    SQL Server files can grow automatically from their originally specified size. When you define a file, you can specify a specific growth increment. Every time the file is filled, it increases its size by the growth increment. If there are multiple files in a filegroup, they will not autogrow until all the files are full. Growth then occurs in a round-robin fashion.

    What we are looking for:

    We have a primary group, on both MDF and NDF files, how can we monitor the data is segregating to MDF and NDF files as per the statement (round-robin fashion) above.

    Please share me with your ideas to monitor.

    Thanks,
    Satish Kumar.

    Thanks, Satish Kumar. Please mark as this post as answered if my anser helps you to resolves your issue :)


    Wednesday, December 04, 2013 10:49 AM

All replies



  • What we are looking for:

    We have a primary group, on both MDF and NDF files, how can we monitor the data is segregating to MDF and NDF files as per the statement (round-robin fashion) above.

    Please share me with your ideas to monitor.

    Thanks,
    Satish Kumar.

    Thanks, Satish Kumar. Please mark as this post as answered if my anser helps you to resolves your issue :)


    AFAIK I dont think there is a way out to monitor the round robin data distribution.

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Wednesday, December 04, 2013 10:53 AM
  • You can report file autogrow events using a query like the one below against the default trace.  I'm not sure why you want to verify the statement in the Books Online other than as an academic exercise.  Generally, it's best to size all files within a filegroup equally for the anticipated space needs and allow autogrow only as a safety net.

    SELECT 
    	te.name
    	,tt.StartTime
    	,DatabaseName
    	,FileName
    FROM sys.traces AS t
    CROSS APPLY fn_trace_gettable(
    	REVERSE('crt.gol' + SUBSTRING(REVERSE(t.path), CHARINDEX('\', REVERSE(t.path)), 128)), default) AS tt
    JOIN sys.trace_events AS te ON
    	te.trace_event_id = tt.EventClass
    WHERE
    	t.is_default = 1
    	AND te.name = N'Data File Auto Grow'
    ORDER BY
    	tt.StartTime;


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Wednesday, December 04, 2013 12:45 PM
    Moderator
  • Hello Dan,

    As per OP 'We have a primary group, on both MDF and NDF files, how can we monitor the data is segregating to MDF and NDF files as per the statement (round-robin fashion) above.

    But can we really see round robin fashion.I think this was the OP's question.Your query just giving time when Auto growth triggered



    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Thursday, December 05, 2013 10:13 AM
  • Since autogrow is triggered only when the file is full, I think one can infer that data are distributed in a round robin fashion if the trace shows auto grow events occurring evenly over the files in the filegroup. 

    CREATE DATABASE AutoGrow
    	ON
    		 (NAME='PRIMARY_1', FILENAME='c:\temp\AutoGrow_PRIMARY_1.mdf', SIZE=10MB, FILEGROWTH=1MB)
    		,(NAME='PRIMARY_2', FILENAME='c:\temp\AutoGrow_PRIMARY_2.mdf', SIZE=5MB, FILEGROWTH=1MB)
    	LOG ON(NAME='LOG_1', FILENAME='c:\temp\LOG_1.ldf')
    GO
    
    USE AutoGrow;
    GO
    
    CREATE TABLE dbo.AutoGrow(
    	TestData char(8000)
    	) ON [PRIMARY];
    GO
    
    WITH 
    	t4 AS (SELECT n FROM (VALUES(0),(0),(0),(0)) t(n))
    	,t256 AS (SELECT 0 n FROM t4 a CROSS JOIN t4 b CROSS JOIN t4 c CROSS JOIN t4 d)
    	,t16M AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS num FROM t256 a CROSS JOIN t256 b CROSS JOIN t256 c)
    INSERT INTO dbo.AutoGrow WITH (TABLOCKX)
    SELECT ''
    FROM t16M
    WHERE num <= 10000;
    GO
    
    SELECT 
    	 te.name EventName
    	,tt.StartTime
    	,tt.DatabaseName
    	,tt.FileName
    FROM sys.traces AS t
    CROSS APPLY fn_trace_gettable(
    	REVERSE('crt.gol' + SUBSTRING(REVERSE(t.path), CHARINDEX('\', REVERSE(t.path)), 128)), default) AS tt
    JOIN sys.trace_events AS te ON
    	te.trace_event_id = tt.EventClass
    WHERE
    	t.is_default = 1
    	AND te.name = N'Data File Auto Grow'
    	AND tt.DatabaseName = N'AutoGrow'
    ORDER BY
    	tt.StartTime;
    

    At any given time, I would expect to following query to show roughly the same percentage used for each file if the growth increment is among the files is proportional.

    SELECT
    	 name
    	,sum(CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)*100.0/size) PerecentUsed
    FROM sys.database_files  
    WHERE type_desc = N'Rows'
    GROUP BY name;
    


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Thursday, December 05, 2013 1:22 PM
    Moderator
  • The term "round robin" is not how pages are allocated, it is how the files are grown during autogrowth.  You have no control or visiblity into how the database engine allocates pages within the multiple files.

    Please see:

    http://www.patrickkeisler.com/2013/03/t-sql-tuesday-40-proportional-fill.html

    Thursday, December 05, 2013 1:30 PM
    Moderator
  • Hi Guys:

    Thanks for your replies on my query...

    I m collecting the database sizes on files daily once by using "DBCC SHOWFILESTATS". Based on the sizes I can prove to my client to show that data is pushing to all files.

    Please let me know how frequent we can collect...

    the following query I m using...

    DECLARE @Database varchar(100)
    DECLARE @SQL nvarchar(255)

    if exists ( select * from tempdb..sysobjects where name LIKE
    '#FileStats__%' ) drop table #FileStats
    if exists ( select * from tempdb..sysobjects where name LIKE
    '#logdetails__%' ) drop table #logdetails
    CREATE TABLE #FileStats(
    [FileId] INT,
    [FileGroup] INT,
    [TotalExtents] INT,
    [UsedExtents] INT,
    [Name] varchar(100),
    [Filename] varchar(255)
    )

    DECLARE @FileStats TABLE (
    [FileId] INT,
    [FileGroup] INT,
    [TotalExtents] INT,
    [UsedExtents] INT,
    [Name] varchar(100),
    [Filename] varchar(255)
    )

    DECLARE cDatabases CURSOR FOR
    SELECT quotename(sdb.name)
    FROM master.dbo.sysdatabases sdb
    WHERE sdb.name in ('DB's')

    OPEN cDatabases
    FETCH FROM cDatabases INTO @Database
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
    DELETE FROM #FileStats
    SET @SQL = 'USE ' + @Database + '; INSERT INTO #FileStats EXEC (''DBCC
    SHOWFILESTATS'')'
    EXEC (@SQL)


    UPDATE #FileStats SET Name = @Database
    INSERT INTO @FileStats SELECT * FROM #FileStats

    FETCH FROM cDatabases INTO @Database
    END
    CLOSE cDatabases
    DEALLOCATE cDatabases

    Create table #logdetails(name varchar(100),lsize int,lspace int,stat int)
    declare @logdetails table(name varchar(100),lsize int,lspace int,stat int)
    DECLARE @SQL1 nvarchar(255)
    SET @SQL1 = 'USE master ; INSERT INTO #LOGDETAILS EXEC (''DBCC
    SQLPERF(LOGSPACE)'')'
    EXEC (@SQL1)
    insert into @logdetails select * from #logdetails
    SELECT getdate() AS Executed_date,
    b.name as [Name]
    ,[TotalExtents]*64/1024 AS TotalSizeInMB
    ,[UsedExtents]*64/1024 AS UsedSizeInMB
    ,([TotalExtents] - [UsedExtents]) / 16. AS UnAllocSizeInMB --* 64 / 1024. AS UnAllocExtInMB
    FROM @FileStats a inner join @logdetails b on a.name=quotename(b.name)
    ORDER BY b.name

    drop table #FileStats
    drop table #logdetails


    Thanks, Satish Kumar. Please mark as this post as answered if my anser helps you to resolves your issue :)

    Thursday, December 05, 2013 4:05 PM
  • Hello,

    If you want to be extremely nit-picky you can create a procedure to run at intervals that consumes the output of DBCC IND and tell your client exactly what allocations are where. That seems a bit overkill to me, but if it's what they want....

    -Sean


    Sean Gallardy | Blog | Twitter

    Saturday, December 07, 2013 5:16 PM
    Answerer
  • That's great, but can you use sys.database_files instead of dbcc?

    Josh

    Sunday, December 08, 2013 1:48 AM
  • Josh,

    To get that level of detail, no that DMV can't be used. I'm not advocating the use of it, I'm saying purely academic it could be done that way if he really wanted to know which files and how much per file each part of each index is living. In my opinion, again, I think that's extremely overkill but that's alas the answer to the question.

    One other thing to consider which isn't what the OP hasn't pointed out is that while SQL Server does indeed use round-robin for it's allocations from files, it also uses proportional fill which will change how *often* allocations happen to that file. In his has he might mistake the fact that a nearly full file with other not full files may result in it *looking* as though round-robin is not being used when in fact it is but proportional fill is also being used... in other words a false negative.


    Sean Gallardy | Blog | Twitter

    Sunday, December 08, 2013 2:52 AM
    Answerer
  • No need for DMVs or DBCC IND. But it helps to have a heap. Then you can use the special column %%LOCKRES%% (which is undocumented and thus unsupported etc). For a heap the lock resource is file:page:recordno. Look at the repro below.

    CREATE DATABASE multifile ON (NAME = 'multifile1', FILENAME = 'F:\MSSQL\DefInstance\multifile1.mdf'),
    (NAME = 'multifile2', FILENAME = 'F:\MSSQL\DefInstance\multifile2.ndf'),
    (NAME = 'multifile3', FILENAME = 'F:\MSSQL\DefInstance\multifile3.ndf'),
    (NAME = 'multifile4', FILENAME = 'F:\MSSQL\DefInstance\multifile4.ndf')
    LOG ON (NAME = 'multifile_log', FILENAME = 'F:\MSSQL\DefInstance\multifile.ldf')
    go
    USE multifile
    go
    CREATE TABLE inflated (object_id int NOT NULL,
                           name      sysname NOT NULL,
                           filler    char(4000) NOT NULL DEFAULT ' ',
                           PRIMARY KEY NONCLUSTERED (object_id, name)
    )
    INSERT inflated (object_id, name)
       SELECT object_id, name
       FROM   sys.columns
    go
    SELECT object_id, name, %%LOCKRES%%
    FROM   inflated
    go
    USE tempdb
    go
    DROP DATABASE multifile


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, December 08, 2013 11:53 AM
  • Erland,

    That will help find where the data is that satisfies the result.

    Assume you had multiple nonclustered indexes, you would have to do that for each nonclustered index on the table (specifying it as a hint).

    -Sean


    Sean Gallardy | Blog | Twitter

    Sunday, December 08, 2013 2:51 PM
    Answerer
  • Assume you had multiple nonclustered indexes, you would have to do that for each nonclustered index on the table (specifying it as a hint).

    Not really, but you may have to specify

       WITH (INDEX = 0)

    to specify the data pages explicitly. If the query plan uses an index, the lock resource is a hash of the key value, which I don't think you easily can translate to a page.

    Then again, there is also %%PHYSLOC%% (equally undocumented and unsupported) which gives you a binary value. I don't know the exact structure by heart (but no doubt you should find it in Kalen Deleaney's books). However, when I try it on the repro I posted,  i see values like this:

       0x1D00000003000100

    And it is clear that the 3 here is the datafile.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, December 08, 2013 3:38 PM
  • Erland, as I read it he just wants to watch the round-robin, not drill down to the table or index level.

    Josh

    Sunday, December 08, 2013 5:00 PM
  • Erland,

    You are correct, but that's just for the base table data or indexes used to satisfy the query (which is why I pointed IND [also undocumented] as it gives everything [if specified correctly]). Wouldn't he also want to see that if there were, say, 15 nonclustered indexes on the table that those also were being equally distributed across the files?

    I do agree though that the undocumented inline functions are most certainly useful in cases like this as well.

    -Sean


    Sean Gallardy | Blog | Twitter

    Sunday, December 08, 2013 5:46 PM
    Answerer