disk I/O tool
in [Server Configuration]
|
Prev: Create Account
Next: sys.dm_exec_query_stats
From: TheSQLGuru on 4 Jan 2010 17:23 I think it is time to recommend you get a perf tuning professional onboard to help out. There are a kajillion ways you can screw up an IO subsystem, and it is a darn shame that almost everyone out there does most of the things wrong. -- Kevin G. Boles Indicium Resources, Inc. SQL Server MVP kgboles a earthlink dt net "Jay" <spam(a)nospam.org> wrote in message news:uPBza2NjKHA.1648(a)TK2MSFTNGP05.phx.gbl... > How do you tell which have the slower transfer rates? Reviews? > > "Andrew J. Kelly" <sqlmvpnooospam(a)shadhawk.com> wrote in message > news:OQ0lNWMjKHA.5520(a)TK2MSFTNGP06.phx.gbl... >> As Kevin mentioned for the most part you need a SAN although there are >> some smaller units that will support 2 node clustering from HP and maybe >> a few others. But essentially they act like a SAN vs. direct attached >> storage. >> >> -- >> >> Andrew J. Kelly SQL MVP >> Solid Quality Mentors >> >> "Jay" <spam(a)nospam.org> wrote in message >> news:u#w6h2#iKHA.5608(a)TK2MSFTNGP05.phx.gbl... >>> Just got though some of the links, OH MY! >>> >>> Just one question, when building a cluster, is there an alternative to a >>> SAN? >>> >>> "Andrew J. Kelly" <sqlmvpnooospam(a)shadhawk.com> wrote in message >>> news:unM9wo7iKHA.2188(a)TK2MSFTNGP04.phx.gbl... >>>>I don't think you will find an MS whitepaper on something like that. For >>>>one there are too many variables and performance depends greatly on >>>>configuration and load type. But there is no question that disk for disk >>>>a SAN will never beat the performance of direct attached storage. The >>>>advantages of the SAN are that it is more flexible and scalable in terms >>>>of number of spindles and such. But the biggest down side is that it is >>>>overrated and usually shared with other heavy loads. You might want to >>>>have a look at these: >>>> >>>> http://sqlblogcasts.com/search/SearchResults.aspx?q=san+performance >>>> >>>> But don't underestimate the virtual file stats and what that can give >>>> you. It will tell you if you are waiting or not. >>>> >>>> -- >>>> >>>> Andrew J. Kelly SQL MVP >>>> Solid Quality Mentors >>>> >>>> "Jay" <spam(a)nospam.org> wrote in message >>>> news:#h52Hq6iKHA.2160(a)TK2MSFTNGP02.phx.gbl... >>>>> Do you know of a Microsoft whitepaper that talks about SAN's being >>>>> slow on small & frequent writes? >>>>> >>>>> "Andrew J. Kelly" <sqlmvpnooospam(a)shadhawk.com> wrote in message >>>>> news:ujVlsEyiKHA.5520(a)TK2MSFTNGP06.phx.gbl... >>>>>> Your best option to begin with is to look at the virtual file stats >>>>>> to see how much physical I/O msdb is actually doing over a given time >>>>>> period. It will also tell you how long it is taking to read and write >>>>>> those I/O's and you can see if it is too long or not. I guess he >>>>>> really doesn't understand SAN's very well either because SAN's are >>>>>> notoriously slow for writing small but frequent I/O's and direct >>>>>> attached drives will often outperform them hands down. If you are >>>>>> processing lots of Service Broker messages then that can explain some >>>>>> of the activity. But until you look at the file stats it is hard to >>>>>> say if it is handling it properly or not. >>>>>> >>>>>> -- >>>>>> >>>>>> Andrew J. Kelly SQL MVP >>>>>> Solid Quality Mentors >>>>>> >>>>>> "Jay" <spam(a)nospam.org> wrote in message >>>>>> news:#iwZMWxiKHA.1648(a)TK2MSFTNGP05.phx.gbl... >>>>>>> Sigh, that has basically been my argument. However, the guy has >>>>>>> stuck to his guns that his msdb is so active that the local drives >>>>>>> aren't enough and that it must be on the SAN for performance >>>>>>> reasons. >>>>>>> >>>>>>> The best I could think of was to stress a similar drive (and monitor >>>>>>> it) to show what off the shelf SCSI's can do on an internal >>>>>>> controller and that msdb activity just doesn't come close to the >>>>>>> drive capacity. Perhaps SQLIO, being simpler, would be better. >>>>>>> >>>>>>> His DB seems to have a very large number of databases and each of >>>>>>> these DB's seem to have a lot of SQL Agent jobs that fire >>>>>>> frequently. This combined with heavy Message Broker activity is >>>>>>> supposedly generating log writes, reads and I have no clue what, to >>>>>>> produce the "heavy activity" that requires the SAN - which is RAID 5 >>>>>>> BTW. >>>>>>> >>>>>>> >>>>>>> "Andrew J. Kelly" <sqlmvpnooospam(a)shadhawk.com> wrote in message >>>>>>> news:upeu61uiKHA.1236(a)TK2MSFTNGP04.phx.gbl... >>>>>>>> Why would MSDB be that busy? If it really is you might want to see >>>>>>>> why because it shouldn't be under normal circumstances. SQLIOSIM >>>>>>>> simulates SQL IO patterns but is not really a measurement tool per >>>>>>>> say. It is more to hammer the array and see where it breaks. If you >>>>>>>> want to see how it actually performs in general reads & writes use >>>>>>>> SQLIO. >>>>>>>> >>>>>>>> -- >>>>>>>> >>>>>>>> Andrew J. Kelly SQL MVP >>>>>>>> Solid Quality Mentors >>>>>>>> >>>>>>>> "Jay" <spam(a)nospam.org> wrote in message >>>>>>>> news:OgPyrOniKHA.1536(a)TK2MSFTNGP06.phx.gbl... >>>>>>>>> SQLIO++ will do. >>>>>>>>> >>>>>>>>> I'm in a discussion where a guy is saying msdb is so busy, that >>>>>>>>> local SCSI drives don't have enough throughput and it has to be on >>>>>>>>> the SAN (which is RAID 5 BTW). I was suggesting to him to run >>>>>>>>> SQLIOsim to see what the monitors look like when the drive is >>>>>>>>> hammered and to compare it to what he sees in his production >>>>>>>>> environment. >>>>>>>>> >>>>>>>>> Jay >>>>>>>>> >>>>>>>>> "Andrew J. Kelly" <sqlmvpnooospam(a)shadhawk.com> wrote in message >>>>>>>>> news:OPl$zIiiKHA.5604(a)TK2MSFTNGP04.phx.gbl... >>>>>>>>>> It depends on what you want to accomplish as there are several. >>>>>>>>>> Can you tell us what your goal is? >>>>>>>>>> >>>>>>>>>> -- >>>>>>>>>> >>>>>>>>>> Andrew J. Kelly SQL MVP >>>>>>>>>> Solid Quality Mentors >>>>>>>>>> >>>>>>>>>> "Jay" <spam(a)nospam.org> wrote in message >>>>>>>>>> news:#HfRsQciKHA.1648(a)TK2MSFTNGP05.phx.gbl... >>>>>>>>>>> SQLIOsim, which the spell checker wants to change to Solipsism >>>>>>>>>>> :) >>>>>>>>>>> >>>>>>>>>>> "Jay" <spam(a)nospam.org> wrote in message >>>>>>>>>>> news:OhTGHEciKHA.1648(a)TK2MSFTNGP05.phx.gbl... >>>>>>>>>>>> What is the name of that disk I/O tool? >>>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>> >>>>>>> >>>>> >>>>> >>> >>> > >
From: Andrew J. Kelly on 5 Jan 2010 11:28
Sorry thanks for pointing that out Kevin. While wait stats are good too here is what I meant to post: IF EXISTS (SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[gather_file_stats_2005]') AND OBJECTPROPERTY([object_id], N'IsProcedure') = 1) DROP PROCEDURE [dbo].[gather_file_stats_2005] ; go CREATE PROCEDURE [dbo].[gather_file_stats_2005] (@Clear INT = 0) AS SET NOCOUNT ON ; DECLARE @DT DATETIME ; SET @DT = GETDATE() ; IF OBJECT_ID(N'[dbo].[file_stats]',N'U') IS NULL CREATE TABLE [dbo].[file_stats]( [database_id] [smallint] NOT NULL, [file_id] [smallint] NOT NULL, [num_of_reads] [bigint] NOT NULL, [num_of_bytes_read] [bigint] NOT NULL, [io_stall_read_ms] [bigint] NOT NULL, [num_of_writes] [bigint] NOT NULL, [num_of_bytes_written] [bigint] NOT NULL, [io_stall_write_ms] [bigint] NOT NULL, [io_stall] [bigint] NOT NULL, [size_on_disk_bytes] [bigint] NOT NULL, [capture_time] [datetime] NOT NULL ) ; -- If 1 the clear out the table IF @Clear = 1 BEGIN TRUNCATE TABLE [dbo].[file_stats] ; END INSERT INTO [dbo].[file_stats] ([database_id] ,[file_id] ,[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] ,[capture_time]) SELECT [database_id] ,[file_id] ,[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] ,@DT FROM [sys].dm_io_virtual_file_stats(NULL,NULL) ; GO IF EXISTS (SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[report_file_stats_2005]') and OBJECTPROPERTY([object_id], N'IsProcedure') = 1) DROP PROCEDURE [dbo].[report_file_stats_2005] ; GO CREATE PROCEDURE [dbo].[report_file_stats_2005] ( @EndTime DATETIME = NULL , @BeginTime DATETIME = NULL ) -- Date & time of the last sample to use AS SET NOCOUNT ON ; IF OBJECT_ID( N'[dbo].[file_stats]',N'U') IS NULL BEGIN RAISERROR('Error [dbo].[file_stats] table does not exist', 16, 1) WITH NOWAIT ; RETURN ; END DECLARE @file_stats TABLE ( [database_id] [smallint] NOT NULL, [file_id] [smallint] NOT NULL, [num_of_reads] [bigint] NOT NULL, [num_of_bytes_read] [bigint] NOT NULL, [io_stall_read_ms] [bigint] NOT NULL, [num_of_writes] [bigint] NOT NULL, [num_of_bytes_written] [bigint] NOT NULL, [io_stall_write_ms] [bigint] NOT NULL, [io_stall] [bigint] NOT NULL, [size_on_disk_bytes] [bigint] NOT NULL, [capture_time] [datetime] NOT NULL ) ; -- If no time was specified then use the latest sample minus the first sample IF @BeginTime IS NULL SET @BeginTime = (SELECT MIN([capture_time]) FROM [dbo].[file_stats]) ; ELSE BEGIN -- If the time was not specified exactly find the closest one IF NOT EXISTS(SELECT * FROM [dbo].[file_stats] WHERE [capture_time] = @BeginTime) BEGIN DECLARE @BT DATETIME ; SET @BT = @BeginTime ; SET @BeginTime = (SELECT MIN([capture_time]) FROM [dbo].[file_stats] WHERE [capture_time] >= @BT) ; IF @BeginTime IS NULL SET @BeginTime = (SELECT MAX([capture_time]) FROM [dbo].[file_stats] WHERE [capture_time] <= @BT) ; END END IF @EndTime IS NULL SET @EndTime = (SELECT MAX([capture_time]) FROM [dbo].[file_stats]) ; ELSE BEGIN -- If the time was not specified exactly find the closest one IF NOT EXISTS(SELECT * FROM [dbo].[file_stats] WHERE [capture_time] = @EndTime) BEGIN DECLARE @ET DATETIME ; SET @ET = @EndTime ; SET @EndTime = (SELECT MIN([capture_time]) FROM [dbo].[file_stats] WHERE [capture_time] >= @ET) ; IF @EndTime IS NULL SET @EndTime = (SELECT MAX([capture_time]) FROM [dbo].[file_stats] WHERE [capture_time] <= @ET) ; END END INSERT INTO @file_stats ([database_id],[file_id],[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],[capture_time]) SELECT [database_id],[file_id],[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],[capture_time] FROM [dbo].[file_stats] WHERE [capture_time] = @EndTime ; IF @@ROWCOUNT = 0 BEGIN RAISERROR('Error, there are no waits for the specified DateTime', 16, 1) WITH NOWAIT ; RETURN ; END -- Subtract the starting numbers from the end ones to find the difference for that time period UPDATE fs SET fs.[num_of_reads] = (fs.[num_of_reads] - a.[num_of_reads]) , fs.[num_of_bytes_read] = (fs.[num_of_bytes_read] - a.[num_of_bytes_read]) , fs.[io_stall_read_ms] = (fs.[io_stall_read_ms] - a.[io_stall_read_ms]) , fs.[num_of_writes] = (fs.[num_of_writes] - a.[num_of_writes]) , fs.[num_of_bytes_written] = (fs.[num_of_bytes_written] - a.[num_of_bytes_written]) , fs.[io_stall_write_ms] = (fs.[io_stall_write_ms] - a.[io_stall_write_ms]) , fs.[io_stall] = (fs.[io_stall] - a.[io_stall]) FROM @file_stats AS fs INNER JOIN (SELECT b.[database_id],b.[file_id],b.[num_of_reads],b.[num_of_bytes_read],b.[io_stall_read_ms] ,b.[num_of_writes],b.[num_of_bytes_written],b.[io_stall_write_ms],b.[io_stall] FROM [dbo].[file_stats] AS b WHERE b.[capture_time] = @BeginTime) AS a ON (fs.[database_id] = a.[database_id] AND fs.[file_id] = a.[file_id]) ; SELECT CONVERT(varchar(50),@BeginTime,120) AS [Start Time], CONVERT(varchar(50),@EndTime,120) AS [End Time] ,CONVERT(varchar(50),@EndTime - @BeginTime,108) AS [Duration (hh:mm:ss)] ; SELECT fs.[database_id] AS [Database ID], fs.[file_id] AS [File ID], fs.[num_of_reads] AS [NumberReads], CONVERT(VARCHAR(20),CAST((fs.[num_of_bytes_read] / 1048576.0) AS MONEY),1) AS [MBs Read] ,fs.[io_stall_read_ms] AS [IoStallReadMS] ,fs.[num_of_writes] AS [NumberWrites] ,CONVERT(VARCHAR(20),CAST((fs.[num_of_bytes_written] / 1048576.0) AS MONEY),1) AS [MBs Written] ,fs.[io_stall_write_ms] AS [IoStallWriteMS] ,fs.[io_stall] AS [IoStallMS] ,CONVERT(VARCHAR(20),CAST((fs.[size_on_disk_bytes] / 1048576.0) AS MONEY),1) AS [MBsOnDisk] ,(SELECT c.[name] FROM [master].[sys].[databases] AS c WHERE c.[database_id] = fs.[database_id]) AS [DB Name] ,(SELECT RIGHT(d.[physical_name],CHARINDEX('\',REVERSE(d.[physical_name]))-1) FROM [master].[sys].[master_files] AS d WHERE d.[file_id] = fs.[file_id] AND d.[database_id] = fs.[database_id]) AS [File Name] ,fs.[capture_time] AS [Last Sample] FROM @file_stats AS fs ORDER BY fs.[database_id], fs.[file_id] ; GO |