I'm using the file stat query from the previous post (I found it in an article that I can't locate).
I'm getting negative numbers in several of my result columns.
E.g.
Number of reads: 10
MBs read: 0.11
IoStallReadMS: -1301
....
IoStallMS: -959
File Name: Scrabble_log.ldf
Does this make sense?
Andrew J. Kelly wrote:
Sorry thanks for pointing that out Kevin.
05-Jan-10
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,
Previous Posts In This Thread:
On Wednesday, December 30, 2009 9:29 PM
Jay wrote:
disk I/O tool
What is the name of that disk I/O tool?
On Wednesday, December 30, 2009 9:51 PM
Jay wrote:
SQLIOsim, which the spell checker wants to change to Solipsism
SQLIOsim, which the spell checker wants to change to Solipsism
On Wednesday, December 30, 2009 10:53 PM
TheSQLGuru wrote:
There is also SQLIO and IOMeter.--Kevin G. BolesIndicium Resources, Inc.
There is also SQLIO and IOMeter.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
On Wednesday, December 30, 2009 11:12 PM
Abba wrote:
LOL
LOL
On Thursday, December 31, 2009 9:04 AM
Andrew J. Kelly wrote:
It depends on what you want to accomplish as there are several.
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
On Thursday, December 31, 2009 6:48 PM
Jay wrote:
SQLIO++ will do.
SQLIO++ will do.
I am in a discussion where a guy is saying msdb is so busy, that local SCSI
drives do not 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
On Friday, January 01, 2010 9:19 AM
Andrew J. Kelly wrote:
Why would MSDB be that busy?
Why would MSDB be that busy? If it really is you might want to see why
because it should not 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
On Friday, January 01, 2010 2:06 PM
Jay wrote:
Sigh, that has basically been my argument.
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 are not 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 does not 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.
On Friday, January 01, 2010 3:30 PM
Andrew J. Kelly wrote:
Your best option to begin with is to look at the virtual file stats to seehow
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 does not 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
On Saturday, January 02, 2010 7:53 AM
Jay wrote:
Do you know of a Microsoft whitepaper that talks about SAN's being slow
Do you know of a Microsoft whitepaper that talks about SAN's being slow on
small & frequent writes?
On Saturday, January 02, 2010 9:45 AM
Andrew J. Kelly wrote:
I do not think you will find an MS whitepaper on something like that.
I do not 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 do not 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
On Saturday, January 02, 2010 2:52 PM
Jay wrote:
I will check the link out later, for now, I am looking at:select db_name(mf.
I will check the link out later, for now, I am 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 are refering to, I would like to be sure
I am reading the output right. Unfortunatly, I am having a little trouble. But
that may be because I only have my home system to look at right now.
On Saturday, January 02, 2010 3:53 PM
Jay wrote:
Just got though some of the links, OH MY!
Just got though some of the links, OH MY!
Just one question, when building a cluster, is there an alternative to a
SAN?
On Sunday, January 03, 2010 1:37 PM
TheSQLGuru wrote:
You need shared disk of some sort for a cluster.
You need shared disk of some sort for a cluster. IIRC HP's MSA series are
certified for this kind of implementation, as are other
'semi-direct-attached' enclosures.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
On Sunday, January 03, 2010 1:39 PM
TheSQLGuru wrote:
What I recommend is doing a differential analysis of the io stalls.
What I recommend is doing a differential analysis of the io stalls. They
are cumulative, and thus can mask real problems if those are of an
intermittent nature (which they almost always are).
Put your values in a temp table, waitfor delay 'someperiod', now join
current io stall output to the temp table and do a diff based on the time
spread. gets you actual period-based values which are much more meaningful
because you can run for say 5 or 10 minutes during a period you know to be
high-activity.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
On Sunday, January 03, 2010 5:39 PM
Andrew J. Kelly wrote:
As Kevin mentioned for the most part you need a SAN although there are
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
On Sunday, January 03, 2010 5:41 PM
Andrew J. Kelly wrote:
You need a delta to get anything useful out of it.
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
On Sunday, January 03, 2010 8:31 PM
Jay wrote:
How do you tell which have the slower transfer rates? Reviews?
How do you tell which have the slower transfer rates? Reviews?
On Sunday, January 03, 2010 10:47 PM
Andrew J. Kelly wrote:
You can look at the vendors specs but the bottom line is the actualthroughput
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
On Monday, January 04, 2010 5:22 PM
TheSQLGuru wrote:
Did you mean to paste in the waitstats analysis code Andy?
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
On Monday, January 04, 2010 5:23 PM
TheSQLGuru wrote:
I think it is time to recommend you get a perf tuning professional onboardto
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
On Tuesday, January 05, 2010 11:28 AM
Andrew J. Kelly wrote:
Sorry thanks for pointing that out Kevin.
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,
Submitted via EggHeadCafe - Software Developer Portal of Choice
Win a Free License of SandRibbon for Silverlight
http://www.eggheadcafe.com/tutorials/aspnet/cb3912a1-be37-4032-a71a-59...e18c0d7 >> Stay informed about: disk I/O tool 
FAQ
Search
Profile
Private Messages
Log in
