disk I/O tool
in [Server Configuration]
|
Prev: Create Account
Next: sys.dm_exec_query_stats
From: Andrew J. Kelly on 3 Jan 2010 17:39 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 3 Jan 2010 17:41 You need a delta to get anything useful out of it. Here are some sps that should get you started. IF EXISTS (SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[gather_wait_stats_2005]') and OBJECTPROPERTY([object_id], N'IsProcedure') = 1) DROP PROCEDURE [dbo].[gather_wait_stats_2005] ; go CREATE PROCEDURE [dbo].[gather_wait_stats_2005] (@Clear INT = 0) AS SET NOCOUNT ON ; DECLARE @DT DATETIME ; SET @DT = GETDATE() ; IF OBJECT_ID(N'[dbo].[wait_stats]',N'U') IS NULL CREATE TABLE [dbo].[wait_stats] ([wait_type] nvarchar(60) not null, [waiting_tasks_count] bigint not null, [wait_time_ms] bigint not null, [max_wait_time_ms] bigint not null, [signal_wait_time_ms] bigint not null, [capture_time] datetime not null default getdate()) ; -- If 1 the clear out the wait_stats counters & the table IF @Clear = 1 BEGIN DBCC SQLPERF([sys.dm_os_wait_stats],clear) WITH no_infomsgs ; TRUNCATE TABLE [dbo].[wait_stats] ; END INSERT INTO [dbo].[wait_stats] ([wait_type], [waiting_tasks_count], [wait_time_ms], [max_wait_time_ms], [signal_wait_time_ms], [capture_time]) SELECT [wait_type], [waiting_tasks_count], [wait_time_ms], [max_wait_time_ms], [signal_wait_time_ms], @DT FROM sys.dm_os_wait_stats ; GO IF EXISTS (SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[report_wait_stats_2005]') and OBJECTPROPERTY([object_id], N'IsProcedure') = 1) DROP PROCEDURE [dbo].[report_wait_stats_2005] ; GO CREATE PROCEDURE [dbo].[report_wait_stats_2005] (@First_Time DATETIME = NULL ,@Last_Time DATETIME = NULL ,@UseOLEDB INT = 0) /* -- Date & time of the last sample to use -- 0 = Dont include OLEDB waits, 1 = Include OLEDB waits */ AS SET NOCOUNT ON ; IF OBJECT_ID( N'[dbo].[wait_stats]',N'U') IS NULL BEGIN RAISERROR('Error [dbo].[wait_stats] table does not exist', 16, 1) WITH NOWAIT ; RETURN ; END DECLARE @Total_Wait numeric(20,1), @Total_SignalWait numeric(20,1), @Total_ResourceWait numeric(20,1) ,@EndTime datetime, @Total_Requests Bigint ; DECLARE @Waits TABLE ([wait_type] nvarchar(60) not null, [waiting_tasks_count] bigint not null, [wait_time_ms] bigint not null, [max_wait_time_ms] bigint not null, [signal_wait_time_ms] bigint not null, [capture_time] datetime not null) ; -- If no First time was specified then use the First sample IF @First_Time IS NULL SET @First_Time = (SELECT MIN([capture_time]) FROM [dbo].[wait_stats]) ; ELSE BEGIN -- If the time was not specified exactly find the closest one IF NOT EXISTS(SELECT * FROM [dbo].[wait_stats] WHERE [capture_time] = @First_Time) BEGIN DECLARE @FT DATETIME ; SET @FT = @First_Time ; SET @First_Time = (SELECT MIN([capture_time]) FROM [dbo].[wait_stats] WHERE [capture_time] <= @FT) ; IF @First_Time IS NULL SET @First_Time = (SELECT MIN([capture_time]) FROM [dbo].[wait_stats] WHERE [capture_time] >= @FT) ; END END -- If no Last time was specified then use the latest sample IF @Last_Time IS NULL SET @Last_Time = (SELECT MAX([capture_time]) FROM [dbo].[wait_stats]) ; ELSE BEGIN -- If the time was not specified exactly find the closest one IF NOT EXISTS(SELECT * FROM [dbo].[wait_stats] WHERE [capture_time] = @Last_Time) BEGIN DECLARE @LT DATETIME ; SET @LT = @Last_Time ; SET @Last_Time = (SELECT MAX([capture_time]) FROM [dbo].[wait_stats] WHERE [capture_time] <= @LT) ; IF @Last_Time IS NULL SET @Last_Time = (SELECT MIN([capture_time]) FROM [dbo].[wait_stats] WHERE [capture_time] >= @LT) ; END END -- Get the relevant waits INSERT INTO @Waits ([wait_type], [waiting_tasks_count], [wait_time_ms], [max_wait_time_ms], [signal_wait_time_ms], [capture_time]) SELECT [wait_type], [waiting_tasks_count], [wait_time_ms], [max_wait_time_ms], [signal_wait_time_ms], [capture_time] FROM [dbo].[wait_stats] WHERE [capture_time] = @Last_Time ; IF @@ROWCOUNT = 0 BEGIN RAISERROR('Error, there are no waits for the specified DateTime', 16, 1) WITH NOWAIT ; RETURN ; END -- Delete some of the misc types of waits and OLEDB if called for IF @UseOLEDB = 0 DELETE FROM @Waits WHERE [wait_type] IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK','SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH','WAITFOR', 'BROKER_TASK_STOP', 'BROKER_RECEIVE_WAITFOR', 'OLEDB','CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT' ) ; ELSE DELETE FROM @Waits WHERE [wait_type] IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK','SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH','WAITFOR', 'BROKER_TASK_STOP', 'BROKER_RECEIVE_WAITFOR','CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT' ) ; ---- 'EXCHANGE', ----'REQUEST_FOR_DEADLOCK_SEARCH', ----'KSOURCE_WAKEUP', ----'BROKER_TRANSMITTER', 'BROKER_EVENTHANDLER', 'BROKER_RECEIVE_WAITFOR', ----'BROKER_TASK_STOP', 'ONDEMAND_TASK_QUEUE', 'CHKPT', 'DBMIRROR_WORKER_QUEUE', 'DBMIRRORING_CMD', ----'SLEEP_TASK', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT' -- Get the delta UPDATE a SET a.[waiting_tasks_count] = (a.[waiting_tasks_count] - b.[waiting_tasks_count]) ,a.[wait_time_ms] = (a.[wait_time_ms] - b.[wait_time_ms]) ,a.[signal_wait_time_ms] = (a.[signal_wait_time_ms] - b.[signal_wait_time_ms]) FROM @Waits AS a INNER JOIN [dbo].[wait_stats] AS b ON a.[wait_type] = b.[wait_type] AND b.[capture_time] = @First_Time ; -- Get the totals SELECT @Total_Wait = SUM([wait_time_ms]) + 1, @Total_SignalWait = SUM([signal_wait_time_ms]) + 1 FROM @Waits ; SET @Total_ResourceWait = (1 + @Total_Wait) - @Total_SignalWait ; SET @Total_Requests = (SELECT SUM([waiting_tasks_count]) FROM @Waits) ; INSERT INTO @Waits ([wait_type], [waiting_tasks_count], [wait_time_ms], [max_wait_time_ms], [signal_wait_time_ms], [capture_time]) SELECT '***Total***',@Total_Requests,@Total_Wait,0,@Total_SignalWait,@Last_Time ; -- Display the results SELECT @First_Time AS [Start Time], @Last_Time AS [End Time] ,CONVERT(varchar(50),@Last_Time - @First_Time,14) AS [Duration (hh:mm:ss:ms)] ; select [wait_type] AS [Wait Type] ,[waiting_tasks_count] AS [Requests] ,[wait_time_ms] AS [Total Wait Time (ms)] ,[max_wait_time_ms] AS [Max Wait Time (ms)] ,CAST(100 * [wait_time_ms] / @Total_Wait as numeric(20,1)) AS [% Waits] ,[wait_time_ms] - [signal_wait_time_ms] AS [Resource Waits (ms)] ,CAST(100 * ([wait_time_ms] - [signal_wait_time_ms]) / @Total_ResourceWait as numeric(20,1)) AS [% Res Waits] ,[signal_wait_time_ms] AS [Signal Waits (ms)] ,CAST(100*[signal_wait_time_ms] / @Total_SignalWait as numeric(20,1)) AS [% Signal Waits] FROM @Waits ORDER BY [Total Wait Time (ms)] DESC, [Wait Type] ; GO -- Andrew J. Kelly SQL MVP Solid Quality Mentors "Jay" <spam(a)nospam.org> wrote in message news:OMgkFU#iKHA.2132(a)TK2MSFTNGP05.phx.gbl... > I'll check the link out later, for now, I'm looking at: > > select db_name(mf.database_id) as databaseName, divfs.file_id, > 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 > from sys.dm_io_virtual_file_stats(null,null) as divfs > inner join sys.master_files as mf > on mf.database_id = divfs.database_id > and mf.file_id = divfs.file_id > > While it is clear this is what you're refering to, I would like to be sure > I'm reading the output right. Unfortunatly, I'm having a little trouble. > But that may be because I only have my home system to look at right now. >
From: Jay on 3 Jan 2010 20:31 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 3 Jan 2010 22:47 You can look at the vendors specs but the bottom line is the actual throughput depends mainly on configuration and total load. -- Andrew J. Kelly SQL MVP Solid Quality Mentors "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: TheSQLGuru on 4 Jan 2010 17:22
Did you mean to paste in the waitstats analysis code Andy? :-) -- Kevin G. Boles Indicium Resources, Inc. SQL Server MVP kgboles a earthlink dt net "Andrew J. Kelly" <sqlmvpnooospam(a)shadhawk.com> wrote in message news:udaWkXMjKHA.5052(a)TK2MSFTNGP04.phx.gbl... > You need a delta to get anything useful out of it. Here are some sps that > should get you started. > > IF EXISTS (SELECT * FROM sys.objects WHERE [object_id] = > OBJECT_ID(N'[dbo].[gather_wait_stats_2005]') and > OBJECTPROPERTY([object_id], N'IsProcedure') = 1) > DROP PROCEDURE [dbo].[gather_wait_stats_2005] ; > go > CREATE PROCEDURE [dbo].[gather_wait_stats_2005] (@Clear INT = 0) > > AS > > SET NOCOUNT ON ; > > DECLARE @DT DATETIME ; > SET @DT = GETDATE() ; > > IF OBJECT_ID(N'[dbo].[wait_stats]',N'U') IS NULL > CREATE TABLE [dbo].[wait_stats] > ([wait_type] nvarchar(60) not null, > [waiting_tasks_count] bigint not null, > [wait_time_ms] bigint not null, > [max_wait_time_ms] bigint not null, > [signal_wait_time_ms] bigint not null, > [capture_time] datetime not null default getdate()) ; > > -- If 1 the clear out the wait_stats counters & the table > IF @Clear = 1 > BEGIN > DBCC SQLPERF([sys.dm_os_wait_stats],clear) WITH no_infomsgs ; > TRUNCATE TABLE [dbo].[wait_stats] ; > END > > > INSERT INTO [dbo].[wait_stats] ([wait_type], [waiting_tasks_count], > [wait_time_ms], [max_wait_time_ms], [signal_wait_time_ms], [capture_time]) > SELECT [wait_type], [waiting_tasks_count], [wait_time_ms], > [max_wait_time_ms], [signal_wait_time_ms], @DT > FROM sys.dm_os_wait_stats ; > > GO > > > IF EXISTS (SELECT * FROM sys.objects WHERE [object_id] = > OBJECT_ID(N'[dbo].[report_wait_stats_2005]') and > OBJECTPROPERTY([object_id], N'IsProcedure') = 1) > DROP PROCEDURE [dbo].[report_wait_stats_2005] ; > GO > CREATE PROCEDURE [dbo].[report_wait_stats_2005] > (@First_Time DATETIME = NULL > ,@Last_Time DATETIME = NULL > ,@UseOLEDB INT = 0) > /* > -- Date & time of the last sample to use > -- 0 = Dont include OLEDB waits, 1 = Include OLEDB waits > */ > AS > > SET NOCOUNT ON ; > > IF OBJECT_ID( N'[dbo].[wait_stats]',N'U') IS NULL > BEGIN > RAISERROR('Error [dbo].[wait_stats] table does not exist', 16, 1) WITH > NOWAIT ; > RETURN ; > END > > DECLARE @Total_Wait numeric(20,1), @Total_SignalWait numeric(20,1), > @Total_ResourceWait numeric(20,1) > ,@EndTime datetime, @Total_Requests Bigint ; > > DECLARE @Waits TABLE ([wait_type] nvarchar(60) not null, > [waiting_tasks_count] bigint not null, > [wait_time_ms] bigint not null, > [max_wait_time_ms] bigint not null, > [signal_wait_time_ms] bigint not null, > [capture_time] datetime not null) ; > > -- If no First time was specified then use the First sample > IF @First_Time IS NULL > SET @First_Time = (SELECT MIN([capture_time]) FROM [dbo].[wait_stats]) > ; > ELSE > BEGIN > -- If the time was not specified exactly find the closest one > IF NOT EXISTS(SELECT * FROM [dbo].[wait_stats] WHERE [capture_time] = > @First_Time) > BEGIN > DECLARE @FT DATETIME ; > SET @FT = @First_Time ; > > SET @First_Time = (SELECT MIN([capture_time]) FROM > [dbo].[wait_stats] WHERE [capture_time] <= @FT) ; > IF @First_Time IS NULL > SET @First_Time = (SELECT MIN([capture_time]) FROM > [dbo].[wait_stats] WHERE [capture_time] >= @FT) ; > END > END > > -- If no Last time was specified then use the latest sample > IF @Last_Time IS NULL > SET @Last_Time = (SELECT MAX([capture_time]) FROM [dbo].[wait_stats]) ; > ELSE > BEGIN > -- If the time was not specified exactly find the closest one > IF NOT EXISTS(SELECT * FROM [dbo].[wait_stats] WHERE [capture_time] = > @Last_Time) > BEGIN > DECLARE @LT DATETIME ; > SET @LT = @Last_Time ; > > SET @Last_Time = (SELECT MAX([capture_time]) FROM > [dbo].[wait_stats] WHERE [capture_time] <= @LT) ; > IF @Last_Time IS NULL > SET @Last_Time = (SELECT MIN([capture_time]) FROM > [dbo].[wait_stats] WHERE [capture_time] >= @LT) ; > END > END > > > -- Get the relevant waits > INSERT INTO @Waits ([wait_type], [waiting_tasks_count], [wait_time_ms], > [max_wait_time_ms], [signal_wait_time_ms], [capture_time]) > SELECT [wait_type], [waiting_tasks_count], [wait_time_ms], > [max_wait_time_ms], [signal_wait_time_ms], [capture_time] > FROM [dbo].[wait_stats] WHERE [capture_time] = @Last_Time ; > > IF @@ROWCOUNT = 0 > BEGIN > RAISERROR('Error, there are no waits for the specified DateTime', 16, > 1) WITH NOWAIT ; > RETURN ; > END > > > -- Delete some of the misc types of waits and OLEDB if called for > IF @UseOLEDB = 0 > DELETE FROM @Waits > WHERE [wait_type] IN > ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK','SLEEP_SYSTEMTASK', > 'SQLTRACE_BUFFER_FLUSH','WAITFOR', 'BROKER_TASK_STOP', > 'BROKER_RECEIVE_WAITFOR', 'OLEDB','CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT' ) ; > ELSE > DELETE FROM @Waits > WHERE [wait_type] IN > ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK','SLEEP_SYSTEMTASK', > 'SQLTRACE_BUFFER_FLUSH','WAITFOR', 'BROKER_TASK_STOP', > 'BROKER_RECEIVE_WAITFOR','CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT' ) ; > > ---- 'EXCHANGE', > ----'REQUEST_FOR_DEADLOCK_SEARCH', > ----'KSOURCE_WAKEUP', > ----'BROKER_TRANSMITTER', 'BROKER_EVENTHANDLER', 'BROKER_RECEIVE_WAITFOR', > ----'BROKER_TASK_STOP', 'ONDEMAND_TASK_QUEUE', 'CHKPT', > 'DBMIRROR_WORKER_QUEUE', 'DBMIRRORING_CMD', > ----'SLEEP_TASK', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT' > > > -- Get the delta > UPDATE a SET a.[waiting_tasks_count] = (a.[waiting_tasks_count] - > b.[waiting_tasks_count]) > ,a.[wait_time_ms] = (a.[wait_time_ms] - b.[wait_time_ms]) > ,a.[signal_wait_time_ms] = (a.[signal_wait_time_ms] - > b.[signal_wait_time_ms]) > FROM @Waits AS a INNER JOIN [dbo].[wait_stats] AS b ON a.[wait_type] = > b.[wait_type] > AND b.[capture_time] = @First_Time ; > > > -- Get the totals > SELECT @Total_Wait = SUM([wait_time_ms]) + 1, @Total_SignalWait = > SUM([signal_wait_time_ms]) + 1 > FROM @Waits ; > > SET @Total_ResourceWait = (1 + @Total_Wait) - @Total_SignalWait ; > > SET @Total_Requests = (SELECT SUM([waiting_tasks_count]) FROM @Waits) ; > > INSERT INTO @Waits ([wait_type], [waiting_tasks_count], [wait_time_ms], > [max_wait_time_ms], [signal_wait_time_ms], [capture_time]) > SELECT > '***Total***',@Total_Requests,@Total_Wait,0,@Total_SignalWait,@Last_Time ; > > > -- Display the results > SELECT @First_Time AS [Start Time], @Last_Time AS [End Time] > ,CONVERT(varchar(50),@Last_Time - @First_Time,14) AS [Duration > (hh:mm:ss:ms)] ; > > select [wait_type] AS [Wait Type] > ,[waiting_tasks_count] AS [Requests] > ,[wait_time_ms] AS [Total Wait Time (ms)] > ,[max_wait_time_ms] AS [Max Wait Time (ms)] > ,CAST(100 * [wait_time_ms] / @Total_Wait as numeric(20,1)) AS [% Waits] > ,[wait_time_ms] - [signal_wait_time_ms] AS [Resource Waits (ms)] > ,CAST(100 * ([wait_time_ms] - [signal_wait_time_ms]) / @Total_ResourceWait > as numeric(20,1)) AS [% Res Waits] > ,[signal_wait_time_ms] AS [Signal Waits (ms)] > ,CAST(100*[signal_wait_time_ms] / @Total_SignalWait as numeric(20,1)) AS > [% Signal Waits] > FROM @Waits > ORDER BY [Total Wait Time (ms)] DESC, [Wait Type] ; > > > GO > > -- > > Andrew J. Kelly SQL MVP > Solid Quality Mentors > > "Jay" <spam(a)nospam.org> wrote in message > news:OMgkFU#iKHA.2132(a)TK2MSFTNGP05.phx.gbl... >> I'll check the link out later, for now, I'm looking at: >> >> select db_name(mf.database_id) as databaseName, divfs.file_id, >> 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 >> from sys.dm_io_virtual_file_stats(null,null) as divfs >> inner join sys.master_files as mf >> on mf.database_id = divfs.database_id >> and mf.file_id = divfs.file_id >> >> While it is clear this is what you're refering to, I would like to be >> sure I'm reading the output right. Unfortunatly, I'm having a little >> trouble. But that may be because I only have my home system to look at >> right now. >> > > |