DISK ACTIVITY ON SAN - SQL Server Forums

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 2000 Forums

SQL Server Administration (2000)

DISK ACTIVITY ON SAN

Author

Topic

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-02-08 : 19:06:40

i was reading about fn_virtualstats and it is since the server sql has been rebooted..
is there a way to figure out how many bytes of data is being sent over the network to the SAN and how long it is taking......i see disk length is high but i want to do measurements on this

Any advice is greatly appreciated.

I not sure if our san are powerful enough and want to measure this.

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-02-08 : 19:33:05

I'm not sure what other statistics you can gather that aren't already in the physical disk stats area of perfmon.

If you want to get some numbers that are easier to deal with, you might want to use the approach Joe Chang details in this thread http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=16995 over at sql-server-performance.com.

We have run this benchmark on most of our environments to get a good idea of our baseline disk performance. It is also useful when configuring disk layouts to determine which is most advantageous.

-ec

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-02-09 : 08:14:12

some of the commands use
DBCC DROPCLEANBUFFERS

Should u do this on a production server?

I'm not sure what other statistics you can gather that aren't already in the physical disk stats area of perfmon.
(Where do i see this in perfmon if i ca get it with this that be useful)

Looking to see how much data is passed through the CPU to the SAN and then how long it takes to get back?

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-02-09 : 11:43:16

you would certainly want to run these tests on a test environment connected to the same SAN infrastructure (with similar disk layout), or during a maintenance window on your prod environment.

-ec

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-02-09 : 19:39:47

Never actual issued that command on production server should i do this from time to time

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-02-09 : 20:55:41

quote:
Originally posted by TRACEYSQL

Never actual issued that command on production server should i do this from time to time

not really. That command clears flushes the buffer cache.

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-02-10 : 10:21:55

I got time to read the entire thing again today.

Im not sure where to start....All i know is the disk queue length on our system goes up to 70 most days.....and always about 5-10 seconds

Im trying to figure out how many bytes of data is read and written to the disk and whether our san is adequate or not.

I do not have 32 gig free...to run the scripts.

Is there something i can do with the fn_virtualstats to run over the next few weeks.

How would i know the specs of our SAN disks how much mb per second it can handle.
And how to figure out when some one is doing a 18 gb table scan.

john.burns
Posting Yak Master

100 Posts

Posted - 2007-02-12 : 09:11:36

To your original question.
Using perfmon .. within Network Interface there is a counter called bytes total/second.

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2007-02-12 : 09:31:34

Our SAN was mapped to two drive letters. One for th data and the second for the log. I used PerfMon and monitored Read Bytes/Sec and Write Bytes/Sec on the Physical Disk object for those two drives.

I found Reads/Sec and Writes/Sec to be a better guide for disk performance. SQL Server is issuing I/O requests that range from 8KB up to much larger requests. A modern high-end SCSI drive can handle 150-180 I/O's per second. (It might even be higher these days). The cache in the SAN will tend to smooth out large write spikes. If SQL Server is issuing more I/O's per second than the SAN can keep with you're going to see latency.

I would also look at the DBCC SQLPERF (WAITSTATS) command. That will tell you what SQL Server is actually waiting on. Here's an article on SQL IO basics:

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-02-12 : 09:43:08

Thanks i got some counters
Counters....i set up everything and then digest them all

Thanks alot everyone.

PHYSICAL DISK
Disk Write Bytes /Sec
Disk Read Bytes/Sec
Avg Disk Bytes Read
Avg Disk Bytes Write
Avg. Disk sec/Transfer
Split IO/Sec
%Disk Read Time
%Disk Write Time
Average Disk Queue Length
LOGICAL DISK
Disk Write Bytes /Sec
Disk Read Bytes/Sec
Avg Disk Bytes Read
Avg Disk Bytes Write
Avg. Disk sec/Transfer
Split IO/Sec
%Disk Read Time
%Disk Write Time

Current Disk Queue Length
MEMORY
Pages/Sec
Available Bytes
Page Faults Sec
Transition Faults Sec
% Committed Bytes
Cache Faults/Sec
Network Interface
Bytes Total/sec
Network Segment
% Network Utilization
CACHE
Data Maps Hits %
MDL Read Hits %
PROCESSOR
%Processor Time
Interrupts / Sec
Queue Length
Context Switches/sec
SQL Server : ACCESS METHODS
Page Splits/Sec
Full Scans / sec
able Lock Escalations/sec
SQL SERVER: BUFFER MANAGER
Page Reads/sec and Page Writes/sec counters
Lazy Writes/Sec
Checkpoint Pages/Sec
Buffer Manager Buffer Cache Hit Ratio
Database pages
Procedure cache pages
Stolen Pages
Cache Hit Ratio
SQL Server Databases
Active Transactions
Log growths
Transactions/sec
SQL Server:General Statistics
User Connections
SQL Server:Locks
Lock Requests /Sec
Average Wait Time
Number of Deadlocks / Sec
SQLServer: SQL Statistics:
Batch Requests/Sec
SQL Compilations/sec
SQL Re-Compilations/sec
SQLServer:Memory
Target Server Memory (KB).
Total Server Memory (KB).
Optimizer Memory
Connection Memory
SQL Server Latches
Latches
System
Processor Queue Length

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-02-12 : 09:56:48

DBCC SQLPERF (WAITSTATS)
(Is this since server shutdown or actually current)

MISCELLANEOUS 8.0 0.0 0.0
LCK_M_SCH_S 0.0 0.0 0.0

What is the measurements in? seconds.

Can this be run in a loop and recorded at the same time as the permon aswell.

Thank

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources