SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Suspecting Disk I/O is slow
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

denis_the_thief
Aged Yak Warrior

Canada
581 Posts

Posted - 02/04/2013 :  10:18:20  Show Profile  Reply with Quote
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)

USA
7020 Posts

Posted - 02/04/2013 :  10:58:38  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
1908 Posts

Posted - 02/05/2013 :  04:53:03  Show Profile  Visit jackv's Homepage  Reply with Quote
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 - 02/06/2013 :  09:04:18  Show Profile  Reply with Quote
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

324 Posts

Posted - 02/08/2013 :  11:13:47  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
1908 Posts

Posted - 02/09/2013 :  06:32:59  Show Profile  Visit jackv's Homepage  Reply with Quote
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

Canada
581 Posts

Posted - 02/16/2013 :  10:13:33  Show Profile  Reply with Quote
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

Canada
581 Posts

Posted - 02/16/2013 :  11:17:08  Show Profile  Reply with Quote
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.

Edited by - denis_the_thief on 02/16/2013 12:25:35
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
1908 Posts

Posted - 02/17/2013 :  01:08:05  Show Profile  Visit jackv's Homepage  Reply with Quote
@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

324 Posts

Posted - 02/19/2013 :  19:06:11  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
1908 Posts

Posted - 02/20/2013 :  08:16:03  Show Profile  Visit jackv's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000