none
If IX_Table and PK_Table of the same table are on different database file, would it affect overall performance?

    Question

  • If IX_Table and PK_Table of the same table are on different database file, would it affect overall performance?
    Tuesday, April 10, 2012 9:07 PM

Answers

  • Are you talking about separation of clustered and non-clustered indexes to the different filegroups?

    It depends. It could improve the performance in case, if physical files/filegroups placement reduces IO contention. On the other hand you need to consider possible implications for DR and piecemeal restore. That separation can add additional complexity to that subject.

    Let's say it in that way - while that separation can help fine tuning the system in some specific cases, this is for sure not the kind of optimization I would do at the beginning (especially not during system architecture stage) nor for the small databases. You need to have enough data and enough load to the system to make educated decision about the subject and even then you need to consider all possible implications/extra complexity that solution provides.


    Thank you!

    My blog: http://aboutsqlserver.com

    • Proposed as answer by Naomi NEditor Tuesday, April 10, 2012 9:41 PM
    • Marked as answer by Peja TaoEditor Tuesday, April 17, 2012 6:23 AM
    Tuesday, April 10, 2012 9:38 PM
  • In addition to what has been stated (and the answer I gave you on the other thread), be careful with these stats.  Depending on when you restarted your server last, you may be getting stall data that includes long running processes that skew numbers.  I use the following to check the stall in a short period of time, and you could expand it for longer time periods with a permanent table.

    It is pretty long, but it basically takes a baseline of some of these values, then returns the data aggregated by disk and by database file.  I use this to run while processes are running to see the immediate impact. One of the biggest things missing from your listing is what drive each of these files are on.  If it is one disk, and it isn't super optimized, then you probably will see some issues with io stall.

    In the code, just change the waitfor delay statement to whatever time period you want, and the resetbaseline basically determines if you are deleting the table or using it, so you can use this to watch io over an hour..

    --This view returns I/O statistics for data and log files [MDF and LDF file]. This view 
    --is one of the commonly used views and will help you to identify I/O file level. This 
    --will return information’s like:-
    declare @resetbaseLine bit
    set @resetbaseLine = 0
    
    set nocount on
    if @resetBaseLine = 1 or object_id('tempdb..#baseline') is null
        begin
            if object_id('tempdb..#baseline') is not null
                drop table #baseline
            --initialize the baseline table
            select db_name(mf.database_id) as databaseName, mf.physical_name, 
                        num_of_reads, num_of_bytes_read, io_stall_read_ms, num_of_writes ,
                        num_of_bytes_written, io_stall_write_ms, io_stall,size_on_disk_bytes,
                        getdate() as baselineDate
            into #baseline
            from sys.dm_io_virtual_file_stats(null,null) as divfs
                        join sys.master_files as mf
                            on mf.database_id = divfs.database_id
                                 and mf.file_id = divfs.file_id
            waitfor delay '0:00:10'
        end 
    
     
    declare @temptable table (databeName sysname, drive char(1), physical_name  varchar(255),
    		seconds  int, minutes  int, io_stall_ms bigint, io_stall_read_ms bigint, io_stall_write_ms bigint,
    		num_of_reads  bigint, num_of_bytes_read bigint, num_of_mbbytes_read numeric(12,4),
    		num_of_writes  bigint, num_of_bytes_written bigint, num_of_mbbytes_written numeric(12,4),
    		size_change_on_disk_in_mb numeric(12,4))
    
    insert into @temptable 
    --output the values, subtracting the baseline from the "currentLine" values :)
    select currentLine.databaseName, left(currentLine.physical_name,1) as drive,
            currentLine.physical_name
            , dateDiff(second,#baseline.baselineDate,currentLine.baselineDate) as seconds
            , dateDiff(minute,#baseline.baselineDate,currentLine.baselineDate) as minutes
            ,currentLine.io_stall - #baseline.io_stall as io_stall_ms
            ,currentLine.io_stall_read_ms - #baseline.io_stall_read_ms as io_stall_read_ms
            ,currentLine.io_stall_write_ms - #baseline.io_stall_write_ms as io_stall_write_ms
    
            ,currentLine.num_of_reads - #baseline.num_of_reads as num_of_reads
            ,currentLine.num_of_bytes_read - #baseline.num_of_bytes_read as num_of_bytes_read
            ,(currentLine.num_of_bytes_read - #baseline.num_of_bytes_read) / 1024.0 /1024.0 as num_of_mbbytes_read
    
    
            ,currentLine.num_of_writes - #baseline.num_of_writes as num_of_writes
            ,currentLine.num_of_bytes_written - #baseline.num_of_bytes_written as num_of_bytes_written
            ,(currentLine.num_of_bytes_written - #baseline.num_of_bytes_written) / 1024.0 /1024.0 as num_of_mbbytes_written
    
            ,(currentLine.size_on_disk_bytes - #baseline.size_on_disk_bytes) / 1024.0 /1024.0 as size_change_on_disk_in_mb
    
    
    from (
                select  db_name(mf.database_id) as databaseName, mf.physical_name, 
                        num_of_reads, num_of_bytes_read, io_stall_read_ms, num_of_writes, 
                        num_of_bytes_written, io_stall_write_ms, io_stall,size_on_disk_bytes,
                        getdate() as baselineDate
                from sys.dm_io_virtual_file_stats(null,null) as divfs
                            join sys.master_files as mf
                                    on mf.database_id = divfs.database_id
                                        and mf.file_id = divfs.file_id) as currentLine
                            join #baseline
                                    on #baseLine.databaseName = currentLine.databaseName
                                        and #baseLine.physical_name = currentLine.physical_name
    --          order by currentLine.io_stall - #baseline.io_stall desc
    order by (currentLine.num_of_bytes_written - #baseline.num_of_bytes_written) + 
            (currentLine.num_of_bytes_read - #baseline.num_of_bytes_read) desc
    
    select drive, max(seconds) as seconds, max(minutes) as minutes
    		, sum(io_stall_ms) as io_stall_ms, sum(io_stall_read_ms) as io_stall_read_ms
    		, sum(io_stall_write_ms) as io_stall_write_ms, sum(num_of_reads) as num_of_reads
    		, sum(num_of_bytes_read) as num_of_bytes_read, sum(num_of_mbbytes_read) as num_of_mbbytes_read
    		,sum(num_of_writes) as num_of_writes, sum(num_of_bytes_written) as num_of_bytes_written
    		,sum(num_of_mbbytes_written) as num_of_mbbytes_written
    		,sum(size_change_on_disk_in_mb) as size_change_on_disk_in_mb
    from @tempTable
    group by drive
    order by io_stall_ms desc
    
    select *
    from   @tempTable
    order by io_stall_ms desc
    
    
     select  db_name(mf.database_id) as databaseName, mf.physical_name, 
                        num_of_reads, num_of_bytes_read, io_stall_read_ms, num_of_writes, 
                        num_of_bytes_written, io_stall_write_ms, io_stall,size_on_disk_bytes,
                        getdate() as baselineDate
                from sys.dm_io_virtual_file_stats(null,null) as divfs
                            join sys.master_files as mf
                                    on mf.database_id = divfs.database_id
                                        and mf.file_id = divfs.file_id
    order by num_of_bytes_read + num_of_bytes_written desc
    


    Louis

    Wednesday, April 11, 2012 3:53 AM

All replies

  • Are you talking about separation of clustered and non-clustered indexes to the different filegroups?

    It depends. It could improve the performance in case, if physical files/filegroups placement reduces IO contention. On the other hand you need to consider possible implications for DR and piecemeal restore. That separation can add additional complexity to that subject.

    Let's say it in that way - while that separation can help fine tuning the system in some specific cases, this is for sure not the kind of optimization I would do at the beginning (especially not during system architecture stage) nor for the small databases. You need to have enough data and enough load to the system to make educated decision about the subject and even then you need to consider all possible implications/extra complexity that solution provides.


    Thank you!

    My blog: http://aboutsqlserver.com

    • Proposed as answer by Naomi NEditor Tuesday, April 10, 2012 9:41 PM
    • Marked as answer by Peja TaoEditor Tuesday, April 17, 2012 6:23 AM
    Tuesday, April 10, 2012 9:38 PM
  • Database Name file_id io_stall_read_ms num_of_reads avg_read_stall_ms io_stall_write_ms num_of_writes avg_write_stall_ms io_stalls total_io avg_io_stall_ms
    MAIN_DB 1 34891279036.00 46260684 754.2 108244877 17900276 6 34999523913 64160960 545.5
    MAIN_DB 3 158474480918.00 379595331 417.5 22867659 917944 24.9 1.58497E+11 380513275 416.5
    MAIN_DB 6 38057218281.00 79261133 480.1 84614018 12842849 6.6 38141832299 92103982 414.1
    MAIN_DB 5 15137725482.00 27309340 554.3 359258297 19155498 18.8 15496983779 46464838 333.5
    msdb 1 46386841.00 726954 63.8 1396533 427035 3.3 47783374 1153989 41.4
    tempdb 5 889813807.00 67392638 13.2 2558243549 70364738 36.4 3448057356 137757376 25
    tempdb 4 880862128.00 66937978 13.2 2507930640 69836798 35.9 3388792768 136774776 24.8
    tempdb 3 954823207.00 65451424 14.6 1626322408 68443013 23.8 2581145615 133894437 19.3
    tempdb 6 972533161.00 66419466 14.6 1652889167 69483356 23.8 2625422328 135902822 19.3
    tempdb 1 246503527.00 33146250 7.4 977297517 34568209 28.3 1223801044 67714459 18.1
    Passport 1 341638.00 26783 12.8 300981 13358 22.5 642619 40141 16
    ReportServer 1 4063615.00 296707 13.7 398725 33252 12 4462340 329959 13.5
    RGRE_IF_Archive 1 69844498.00 5354649 13 465906 30417 15.3 70310404 5385066 13.1
    MAIN_DB 4 11397110.00 952943 12 510098 115022 4.4 11907208 1067965 11.1
    master 1 47897.00 5683 8.4 8091 1039 7.8 55988 6722 8.3
    RGRE_IF_Archive 2 8213.00 1594 5.1 780253 106755 7.3 788466 108349 7.3
    Passport 2 2007.00 267 7.5 211693 41283 5.1 213700 41550 5.1
    ReportServerTempDB 1 76571.00 14232 5.4 506995 119817 4.2 583566 134049 4.4
    ReportServerTempDB 4 37093.00 10190 3.6 353948 80776 4.4 391041 90966 4.3
    master 2 1109.00 125 8.8 8993 2213 4.1 10102 2338 4.3
    ReportServerTempDB 3 34063.00 9505 3.6 326321 76284 4.3 360384 85789 4.2
    tempdb 2 1468.00 332 4.4 1897783 464688 4.1 1899251 465020 4.1
    ReportServerTempDB 2 150259.00 28855 5.2 2650164 662783 4 2800423 691638 4
    ReportServer 2 2570.00 437 5.9 737484 204611 3.6 740054 205048 3.6
    model 1 314.00 101 3.1 2 2 0.7 316 103 3
    msdb 2 1924.00 286 6.7 4564312 1752466 2.6 4566236 1752752 2.6
    model 2 24.00 7 3 7 7 0.9 31 14 2.1
    MAIN_DB 2 29052008.00 299092 97.1 216918143 147879655 1.5 245970151 148178747 1.7
    Tuesday, April 10, 2012 9:55 PM
  • I'm suspecting that we are experiencing IO problems. Where do I start?

    Tuesday, April 10, 2012 10:01 PM
  • Well, you need to look at the system in general. First of all check that sql server, OS and IO are configured correctly, there are no other processes oveloading IO and especially that you do follow IO/SAN best practices (http://blogs.msdn.com/b/jimmymay/archive/2009/03/01/disk-i-o-microsoft-sql-server-on-san-best-practices-from-sql-cat-s-mike-ruthruff-prem-mehra.aspx    http://sqlcat.com/sqlcat/b/top10lists/archive/2007/11/21/storage-top-10-best-practices.aspx ). Make sure that you have decent amount of RAM available to SQL Server. When you are sure that there are no common misconfiguration issues, start looking at the server.

    Cross check virtual file start with wait stats. And start with query_stats after that - find non-optimized queries and fix them.

    SELECT TOP 50 
    	SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
    		((
    			CASE qs.statement_end_offset
    				WHEN -1 THEN DATALENGTH(qt.TEXT)
    				ELSE qs.statement_end_offset
    			END - qs.statement_start_offset)/2)+1),
    	qs.execution_count,
    	(qs.total_logical_reads + qs.total_logical_writes) / qs.execution_count as [Avg IO],
    	qp.query_plan,
    	qs.total_logical_reads, qs.last_logical_reads,
    	qs.total_logical_writes, qs.last_logical_writes,
    	qs.total_worker_time,
    	qs.last_worker_time,
    	qs.total_elapsed_time/1000 total_elapsed_time_in_ms,
    	qs.last_elapsed_time/1000 last_elapsed_time_in_ms,
    	qs.last_execution_time
    FROM 
    	sys.dm_exec_query_stats qs
    		CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
    		OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
    ORDER BY 
    	[Avg IO] DESC
    go	


    Thank you!

    My blog: http://aboutsqlserver.com

    Wednesday, April 11, 2012 12:09 AM
  • In addition to what has been stated (and the answer I gave you on the other thread), be careful with these stats.  Depending on when you restarted your server last, you may be getting stall data that includes long running processes that skew numbers.  I use the following to check the stall in a short period of time, and you could expand it for longer time periods with a permanent table.

    It is pretty long, but it basically takes a baseline of some of these values, then returns the data aggregated by disk and by database file.  I use this to run while processes are running to see the immediate impact. One of the biggest things missing from your listing is what drive each of these files are on.  If it is one disk, and it isn't super optimized, then you probably will see some issues with io stall.

    In the code, just change the waitfor delay statement to whatever time period you want, and the resetbaseline basically determines if you are deleting the table or using it, so you can use this to watch io over an hour..

    --This view returns I/O statistics for data and log files [MDF and LDF file]. This view 
    --is one of the commonly used views and will help you to identify I/O file level. This 
    --will return information’s like:-
    declare @resetbaseLine bit
    set @resetbaseLine = 0
    
    set nocount on
    if @resetBaseLine = 1 or object_id('tempdb..#baseline') is null
        begin
            if object_id('tempdb..#baseline') is not null
                drop table #baseline
            --initialize the baseline table
            select db_name(mf.database_id) as databaseName, mf.physical_name, 
                        num_of_reads, num_of_bytes_read, io_stall_read_ms, num_of_writes ,
                        num_of_bytes_written, io_stall_write_ms, io_stall,size_on_disk_bytes,
                        getdate() as baselineDate
            into #baseline
            from sys.dm_io_virtual_file_stats(null,null) as divfs
                        join sys.master_files as mf
                            on mf.database_id = divfs.database_id
                                 and mf.file_id = divfs.file_id
            waitfor delay '0:00:10'
        end 
    
     
    declare @temptable table (databeName sysname, drive char(1), physical_name  varchar(255),
    		seconds  int, minutes  int, io_stall_ms bigint, io_stall_read_ms bigint, io_stall_write_ms bigint,
    		num_of_reads  bigint, num_of_bytes_read bigint, num_of_mbbytes_read numeric(12,4),
    		num_of_writes  bigint, num_of_bytes_written bigint, num_of_mbbytes_written numeric(12,4),
    		size_change_on_disk_in_mb numeric(12,4))
    
    insert into @temptable 
    --output the values, subtracting the baseline from the "currentLine" values :)
    select currentLine.databaseName, left(currentLine.physical_name,1) as drive,
            currentLine.physical_name
            , dateDiff(second,#baseline.baselineDate,currentLine.baselineDate) as seconds
            , dateDiff(minute,#baseline.baselineDate,currentLine.baselineDate) as minutes
            ,currentLine.io_stall - #baseline.io_stall as io_stall_ms
            ,currentLine.io_stall_read_ms - #baseline.io_stall_read_ms as io_stall_read_ms
            ,currentLine.io_stall_write_ms - #baseline.io_stall_write_ms as io_stall_write_ms
    
            ,currentLine.num_of_reads - #baseline.num_of_reads as num_of_reads
            ,currentLine.num_of_bytes_read - #baseline.num_of_bytes_read as num_of_bytes_read
            ,(currentLine.num_of_bytes_read - #baseline.num_of_bytes_read) / 1024.0 /1024.0 as num_of_mbbytes_read
    
    
            ,currentLine.num_of_writes - #baseline.num_of_writes as num_of_writes
            ,currentLine.num_of_bytes_written - #baseline.num_of_bytes_written as num_of_bytes_written
            ,(currentLine.num_of_bytes_written - #baseline.num_of_bytes_written) / 1024.0 /1024.0 as num_of_mbbytes_written
    
            ,(currentLine.size_on_disk_bytes - #baseline.size_on_disk_bytes) / 1024.0 /1024.0 as size_change_on_disk_in_mb
    
    
    from (
                select  db_name(mf.database_id) as databaseName, mf.physical_name, 
                        num_of_reads, num_of_bytes_read, io_stall_read_ms, num_of_writes, 
                        num_of_bytes_written, io_stall_write_ms, io_stall,size_on_disk_bytes,
                        getdate() as baselineDate
                from sys.dm_io_virtual_file_stats(null,null) as divfs
                            join sys.master_files as mf
                                    on mf.database_id = divfs.database_id
                                        and mf.file_id = divfs.file_id) as currentLine
                            join #baseline
                                    on #baseLine.databaseName = currentLine.databaseName
                                        and #baseLine.physical_name = currentLine.physical_name
    --          order by currentLine.io_stall - #baseline.io_stall desc
    order by (currentLine.num_of_bytes_written - #baseline.num_of_bytes_written) + 
            (currentLine.num_of_bytes_read - #baseline.num_of_bytes_read) desc
    
    select drive, max(seconds) as seconds, max(minutes) as minutes
    		, sum(io_stall_ms) as io_stall_ms, sum(io_stall_read_ms) as io_stall_read_ms
    		, sum(io_stall_write_ms) as io_stall_write_ms, sum(num_of_reads) as num_of_reads
    		, sum(num_of_bytes_read) as num_of_bytes_read, sum(num_of_mbbytes_read) as num_of_mbbytes_read
    		,sum(num_of_writes) as num_of_writes, sum(num_of_bytes_written) as num_of_bytes_written
    		,sum(num_of_mbbytes_written) as num_of_mbbytes_written
    		,sum(size_change_on_disk_in_mb) as size_change_on_disk_in_mb
    from @tempTable
    group by drive
    order by io_stall_ms desc
    
    select *
    from   @tempTable
    order by io_stall_ms desc
    
    
     select  db_name(mf.database_id) as databaseName, mf.physical_name, 
                        num_of_reads, num_of_bytes_read, io_stall_read_ms, num_of_writes, 
                        num_of_bytes_written, io_stall_write_ms, io_stall,size_on_disk_bytes,
                        getdate() as baselineDate
                from sys.dm_io_virtual_file_stats(null,null) as divfs
                            join sys.master_files as mf
                                    on mf.database_id = divfs.database_id
                                        and mf.file_id = divfs.file_id
    order by num_of_bytes_read + num_of_bytes_written desc
    


    Louis

    Wednesday, April 11, 2012 3:53 AM