From: TheSQLGuru on
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
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


First  |  Prev  | 
Pages: 1 2 3 4 5
Prev: Create Account
Next: sys.dm_exec_query_stats