Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Suspecting Disk I/O is slow

Author  Topic 

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2013-02-04 : 10:18:20
We set up this new server recently. It has about the same RAM and CPU speed as the original one.

Some of the Stored Procs take 2 times longer as compared to the old Server, even though the Database is an exact copy and we run the same Stored Proc with the same Parameters. What is worse is that if we are running something that is time consuming (let's say over 1 minute), it effects everything else so badly - i.e. even just to log in could take 30 seconds.

Also when I was copying a large file to this Database (about 100G), it seemed to slow everyone down severely.

Whenever I check the CPU usage on the Server, it doesn't look like that is the issue.

2 questions:
1) I think this is due the Disk I/O on the Server not being good enough. Does anyone know how I can prove this?

2) Assuming our performance issue is poor Disk I/O, any ideas what we can do so that when one SQL Process is very slow, that it does not effect everyone so much.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2013-02-04 : 10:58:38
When you setup a new server, you should always test the IO to make sure it is what you are expecting. I usually use SQLIO for this.

Another good test is to run DBCC CHECKDB on the new server, and compare it to the run-time for DBCC CHECKDB on the old server.





CODO ERGO SUM
Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-02-05 : 04:53:03
If you still have access to the old server , you could do a quick comparison .
Here are some notes on SQLIO (suggested above ) and how to use http://www.sqlserver-dba.com/2011/08/disk-io-performance-and-sqlio.html

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

srimami
Posting Yak Master

160 Posts

Posted - 2013-02-06 : 09:04:18
Have you checked for Page memory and cache memory? As per recommended settings page memory should be 1.5 times of RAM and Sql Server Memory should be 90% of RAM. There might be some possibilities of sessions running behind, check firing sp_who2 and kill the sessions if by any chance are they running.
Go to Top of Page

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2013-02-08 : 11:13:47
What value to you get for diskcontention?

Note: Change "5" to your primary database id...

SELECT iostallms/(NumberReads+NumberWrites) as diskcontention ,* FROM :: fn_virtualfilestats(5, 1)

Anything over 20 indicates a possible problem.
Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-02-09 : 06:32:59
If you find that the numbers are consistently high - you're two options are : 1) redistribute the workload onto additional IO channels 2) add more capacity for throughput or increased response time



Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2013-02-16 : 10:13:33
quote:
Originally posted by jackv

If you still have access to the old server , you could do a quick comparison .
Here are some notes on SQLIO (suggested above ) and how to use http://www.sqlserver-dba.com/2011/08/disk-io-performance-and-sqlio.html

Jack Vamvas
--------------------
http://www.sqlserver-dba.com



Thanks, I took your advice and used a tool to compare I/O speeds. But I got the crystaldiskmark tool since it was easier to use.

I did several tests and in all of them, surprise to me, the new Server was faster. About 30% faster.

But I still think something is up with the new server. I was copying a 50G file on this server, from another server, and that made the performance absolutely terrible while the copy was going on.
Go to Top of Page

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2013-02-16 : 11:17:08
quote:
Originally posted by ferrethouse

What value to you get for diskcontention?

Note: Change "5" to your primary database id...

SELECT iostallms/(NumberReads+NumberWrites) as diskcontention ,* FROM :: fn_virtualfilestats(5, 1)

Anything over 20 indicates a possible problem.



Cool query!

I ran this for all Databases. Here is my summary.

Old Server

Small Databases: 6 to 10
Medium/Large Databases: 10 to 35

New Server

Small Databases: 10 to 35
Medium/Large Databases: 35 to 400

Bad?

I'm curious if this query can look at different time intervals. I'm also wondering why we are looking at Number of Reads/Writes rather than Bytes.
Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-02-17 : 01:08:05
@denis_the_thief - are your databases set out in the same layout as the old server , such as logs,data amd tempdb files? Is phyicla memory the same and does the SQL Server have the same amount of memory?
Is this a physical or VM?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2013-02-19 : 19:06:11
quote:
Originally posted by denis_the_thief

quote:
Originally posted by ferrethouse

What value to you get for diskcontention?

Note: Change "5" to your primary database id...

SELECT iostallms/(NumberReads+NumberWrites) as diskcontention ,* FROM :: fn_virtualfilestats(5, 1)

Anything over 20 indicates a possible problem.



Cool query!

I ran this for all Databases. Here is my summary.

Old Server

Small Databases: 6 to 10
Medium/Large Databases: 10 to 35

New Server

Small Databases: 10 to 35
Medium/Large Databases: 35 to 400

Bad?

I'm curious if this query can look at different time intervals. I'm also wondering why we are looking at Number of Reads/Writes rather than Bytes.



Looks bad to me. I can't explain why bytes are excluded. Brent Ozar Ltd. provided me the query. I have seen variations that do make use of bytes.

I found this query that calculates the value over a set interval. I modified it slightly to include the "contention" column...


-- create table
IF NOT EXISTS (SELECT *
FROM sys.objects
WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[filestats]')
AND type IN (N'U'))
BEGIN
CREATE TABLE filestats
(dbname VARCHAR(128),
fName VARCHAR(2048),
timeStart datetime,
timeEnd datetime,
timeDiff bigint,
readsNum1 bigint,
readsNum2 bigint,
readsBytes1 bigint,
readsBytes2 bigint,
readsIoStall1 bigint,
readsIoStall2 bigint,
writesNum1 bigint,
writesNum2 bigint,
writesBytes1 bigint,
writesBytes2 bigint,
writesIoStall1 bigint,
writesIoStall2 bigint,
ioStall1 bigint,
ioStall2 bigint
)
END

-- clear data
TRUNCATE TABLE dbo.filestats

-- insert first segment counters
INSERT INTO dbo.filestats
(dbname,
fName,
TimeStart,
readsNum1,
readsBytes1,
readsIoStall1,
writesNum1,
writesBytes1,
writesIoStall1,
IoStall1
)
SELECT
DB_NAME(a.dbid) AS Database_name,
b.filename,
GETDATE(),
numberReads,
BytesRead,
IoStallReadMS,
NumberWrites,
BytesWritten,
IoStallWriteMS,
IoStallMS
FROM
fn_virtualfilestats(NULL,NULL) a INNER JOIN
sysaltfiles b ON a.dbid = b.dbid AND a.fileid = b.fileid
ORDER BY
Database_Name

/*Delay second read */
WAITFOR DELAY '000:02:00'

-- add second segment counters
UPDATE dbo.filestats
SET
timeEnd = GETDATE(),
readsNum2 = a.numberReads,
readsBytes2 = a.BytesRead,
readsIoStall2 = a.IoStallReadMS ,
writesNum2 = a.NumberWrites,
writesBytes2 = a.BytesWritten,
writesIoStall2 = a.IoStallWriteMS,
IoStall2 = a.IoStallMS,
timeDiff = DATEDIFF(s,timeStart,GETDATE())
FROM
fn_virtualfilestats(NULL,NULL) a INNER JOIN
sysaltfiles b ON a.dbid = b.dbid AND a.fileid = b.fileid
WHERE
fName= b.filename AND dbname=DB_NAME(a.dbid)

-- select data
SELECT
dbname,
fName,
timeDiff,
(ioStall2 - ioStall1)/nullif((readsNum2 - readsNum1)+(writesNum2 - writesNum1),0) contention,
readsNum2 - readsNum1 AS readsNumDiff,
readsBytes2 - readsBytes2 AS readsBytesDiff,
readsIoStall2 - readsIOStall1 AS readsIOStallDiff,
writesNum2 - writesNum1 AS writesNumDiff,
writesBytes2 - writesBytes2 AS writesBytesDiff,
writesIoStall2 - writesIOStall1 AS writesIOStallDiff,
ioStall2 - ioStall1 AS ioStallDiff
FROM dbo.filestats
Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-02-20 : 08:16:03
Do you have an expectation of what you think the IO should be?If you do and have created an IO profile - you can decide on whether you have overloaded the drives.?
How is your disk layout set relative to activity type - data, logs, tempdb?
Are your drives mapped to separated IO channels?
The stored procedures - can you identify the type of delays occuring - re : wait types

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page
   

- Advertisement -