/* Database+Disk+Performance™: Disk Performance Problem Isolation Techniques: Simple Tools */ Database+Disk+Performance™ /* */ /* -- -- */ /* Footer ----------------------------------------------- */ #footer { width:660px; clear:both; margin:0 auto; } #footer hr { display:none; } #footer p { margin:0; padding-top:15px; font:78%/1.6em "Trebuchet MS",Trebuchet,Verdana,Sans-serif; text-transform:uppercase; letter-spacing:.1em; }

Monday, June 26, 2006

Disk Performance Problem Isolation Techniques: Simple Tools

Introduction: “I told you SQL Server couldn’t scale…”


The usual conversation goes like this… “I told you SQL Server couldn’t scale…” and so the finger pointing begins. As a student of philosophy in college, I learned about rhetoric and baiting techniques. As a DBA, I had to learn to fight back and not be on the defensive from such attacks. From personal experience, I have managed many databases with tens of thousands of users that were multi-terabyte and mission critical… so I knew that these statements were spurious.


To me, the issue wasn’t whether the database could scale, but whether the customer architected a scalable architecture that could handle the load and meet the business SLA.

In a database server running an enterprise application, there are many moving main moving parts. Server, network, OS, app servers, web servers, batch servers, software, HBAs, fabric switches, application customization, etc… All can lead to issues. Regrettably, it’s usually the database that gets the blame.


This note will outline tools and techniques to eliminate the database software as the bottleneck and help the team start looking for the real issue, which for database scalability is usually the disk sub system.


Tools & Usage


The first objective is to test outside the SQL Server environment. The reason for this is to eliminate the database as an issue. If you get poor performance outside of SQL Server, then it’s not the database, it’s something else… usually the disk configuration.


Disk Copy


The easiest test to run is a file copy. Simply find a large file, for example, 2G in size and then copy it from one disk to another. You can create this test file by making a test database with a data file of 2G. Detach the database files using sp_detach_db. Then xcopy the files from your drive F: to drive G:.


CREATE DATABASE test

ON

( NAME = test_dat,

FILENAME = 'c:\tmp\test_dat.mdf',

SIZE = 2000MB)

LOG ON

( NAME = test_log,

FILENAME = 'c:\tmp\test_log.ldf',

SIZE = 1MB )

go

sp_detach_db test

go



Another way to create a large file is to use the Windows NT Resource utility "creatfil.exe" which can create a large fixed size file.

Then go to a DOS command prompt and run the following batch file:

time /t

xcopy F:\temp\test_dat.mdf G:\temp\test_dat.mdf

time /t


At this point there are few things to remember:

  1. Make sure you are copying data to physical different drives. Please remember that different LUNs can still share the same physical drives and controller.
  2. Run a PERFMON trace and look at the logical transfer rates. This is outlined in the previous note on latency. If you are seeing times greater than 10 ms, you probably have a configuration issue of your SAN. You may need more spindles.
  3. Look at how many MB/sec you are pushing through. Take the size of the data file (ex. 100MB) and divide it by how many seconds it took to copy it. To get an idea of throughput, the following table gives a broad idea to compare your results with:

Laptop

34 MB/sec

Poor SAN

35 MB/sec

Good SAN

180 MB/sec

Great SAN

412 MB/sec

Lastly, run the same test on another server that is running “well”. Compare the results.


SQLIOSTRESS


SQLIOSTRESS is a free tool that Microsoft offers to create a disk load that simulates SQL Server activity. Please note, you do not have to have SQL Server installed on the server to run it. Hence, it makes a good tool to run prior to creating your database and installing a lot of software that you may have to redo in case you have to flatten and rebuild your SAN to get adequate numbers.


SQLIOSTRESS can be obtained at this location:

http://support.microsoft.com/kb/q231619/


The tool will give the DBA plenty of information. What I like to do is run the tool and then monitor the IO patterns with PERFMON.


IOMETER


IOMETER is another tool that creates a large workload on the disk drive sub system. The tool is not database specific and allows the user to configure the type of workload and IO patterns that need to be run against the disk This tool is also free. It can be downloaded from:

http://www.iometer.org/doc/downloads.html


Again, use PERMON to watch while the tool is running. Look at the output reports from IOMETER.


SQLIO


Lastly, another Microsoft tool that can be downloaded is SQLIO. The tool is located at:

http://download.microsoft.com/download/f/3/f/f3f92f8b-b24e-4c2e-9e86-d66df1f6f83b/SQLIO.msi


This tool will generate data and give an output report. A sample run on a small file from my laptop looks like this:


C:\Program Files\SQLIO>sqlio -kR -s360 -fsequential -o8 -b64 -LS -dE -Fparameter.txt
sqlio v1.5.SG
using system counter for latency timings, -1302407296 counts per second
parameter file used: parameter.txt
file C:\TestFile\TestFile.dat with 16 threads (0-15) using mask 0x0 (0)
16 threads reading for 360 secs from file C
:\testdir\test.dat
using 64KB sequential IOs
enabling multiple I/Os per thread with 8 outstanding
using specified size: 100 MB for file: C:\testdir\test.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 6755.07
MBs/sec: 422.19
latency metrics:
Min_Latency(ms): 2
Avg_Latency(ms): 18
Max_Latency(ms): 47


Summary


Secure a good baseline by running some of the above tools and processes. Then compare the numbers to other “good” systems you have running and to other published data. Work with your drive vendor if the throughput numbers are not like you would suspect. Keep in mind, you get what you pay for. If you spend little, don’t expect 500 MB/sec throughput!


After running the above types of test, I’ll often run a BACKUP to a NUL: device and then compare a database operation to a non-database run. For example, what is the MB/sec throughput of a BACKUP to NUL: versus an XCOPY. If they are the same, then you know the database is not the issue and it’s the disk that is probably the bottleneck.


The key idea in problem isolation and resolution is figure out which part of the equation is causing the problem To do that, you need to break up the variables and test them independently. The above tools give you mechanisms in which to do this.


Addendum:


Microsoft TechNet Article: Ruling Out Disk-Bound Problems

Focuses on perfmon counters. Covers Exchange and some database.


http://www.microsoft.com/technet/prodtechnol/exchange/guides/TrblshtE2k3Perf/5bcdd349-dcc6-43eb-9dc3-54175f7061ad.mspx?mfr=true


IMPORTANT NOTE: When running these tests, make sure that the data used is greater than the size of the SAN cache. The reason for this is the SAN cache will buffer the file and distort the results. For example, if the SAN cache is 1G in size, then make the file used in XCOPY or SQLIO to be 2G in size.





Powered By Qumana

3 Comments:

Blogger sexy said...

情趣用品,情趣用品,情趣用品,情趣用品,情趣用品,情趣用品,情趣用品,情趣用品,情趣,情趣,情趣,情趣,情趣,情趣,情趣,情趣,A片,視訊聊天室,聊天室,視訊,視訊聊天室,080苗栗人聊天室,上班族聊天室,成人聊天室,中部人聊天室,一夜情聊天室,情色聊天室,視訊交友網

免費A片,AV女優,美女視訊,情色交友,免費AV,色情網站,辣妹視訊,美女交友,色情影片,成人影片,成人網站,A片,H漫,18成人,成人圖片,成人漫畫,情色網,日本A片,免費A片下載,性愛

A片,色情,成人,做愛,情色文學,A片下載,色情遊戲,色情影片,色情聊天室,情色電影,免費視訊,免費視訊聊天,免費視訊聊天室,一葉情貼圖片區,情色,情色視訊,免費成人影片,視訊交友,視訊聊天,視訊聊天室,言情小說,愛情小說,AIO,AV片,A漫,avdvd,聊天室,自拍,情色論壇,視訊美女,AV成人網,色情A片,SEX,成人論壇

情趣用品,A片,免費A片,AV女優,美女視訊,情色交友,色情網站,免費AV,辣妹視訊,美女交友,色情影片,成人網站,H漫,18成人,成人圖片,成人漫畫,成人影片,情色網


情趣用品,A片,免費A片,日本A片,A片下載,線上A片,成人電影,嘟嘟成人網,成人,成人貼圖,成人交友,成人圖片,18成人,成人小說,成人圖片區,微風成人區,成人文章,成人影城,情色,情色貼圖,色情聊天室,情色視訊,情色文學,色情小說,情色小說,臺灣情色網,色情,情色電影,色情遊戲,嘟嘟情人色網,麗的色遊戲,情色論壇,色情網站,一葉情貼圖片區,做愛,性愛,美女視訊,辣妹視訊,視訊聊天室,視訊交友網,免費視訊聊天,美女交友,做愛影片

av,情趣用品,a片,成人電影,微風成人,嘟嘟成人網,成人,成人貼圖,成人交友,成人圖片,18成人,成人小說,成人圖片區,成人文章,成人影城,愛情公寓,情色,情色貼圖,色情聊天室,情色視訊,情色文學,色情小說,情色小說,色情,寄情築園小遊戲,情色電影,aio,av女優,AV,免費A片,日本a片,美女視訊,辣妹視訊,聊天室,美女交友,成人光碟

情趣用品.A片,情色,情色貼圖,色情聊天室,情色視訊,情色文學,色情小說,情色小說,色情,寄情築園小遊戲,情色電影,色情遊戲,色情網站,聊天室,ut聊天室,豆豆聊天室,美女視訊,辣妹視訊,視訊聊天室,視訊交友網,免費視訊聊天,免費A片,日本a片,a片下載,線上a片,av女優,av,成人電影,成人,成人貼圖,成人交友,成人圖片,18成人,成人小說,成人圖片區,成人文章,成人影城,成人網站,自拍,尋夢園聊天室

2:42 AM  
Blogger  said...

A片,A片,成人網站,成人漫畫,色情,情色網,情色,AV,AV女優,成人影城,成人,色情A片,日本AV,免費成人影片,成人影片,SEX,免費A片,A片下載,免費A片下載,做愛,情色A片,色情影片,H漫,A漫,18成人

a片,色情影片,情色電影,a片,色情,情色網,情色,av,av女優,成人影城,成人,色情a片,日本av,免費成人影片,成人影片,情色a片,sex,免費a片,a片下載,免費a片下載

情趣用品,情趣用品,情趣,情趣,情趣用品,情趣用品,情趣,情趣,情趣用品,情趣用品,情趣,情趣

A片,A片,A片下載,做愛,成人電影,.18成人,日本A片,情色小說,情色電影,成人影城,自拍,情色論壇,成人論壇,情色貼圖,情色,免費A片,成人,成人網站,成人圖片,AV女優,成人光碟,色情,色情影片,免費A片下載,SEX,AV,色情網站,本土自拍,性愛,成人影片,情色文學,成人文章,成人圖片區,成人貼圖

情色,AV女優,UT聊天室,聊天室,A片,視訊聊天室

6:58 AM  
Blogger freefun0616 said...

酒店經紀人,
菲梵酒店經紀,
酒店經紀,
禮服酒店上班,
酒店小姐兼職,
便服酒店經紀,
酒店打工經紀,
制服酒店工作,
專業酒店經紀,
合法酒店經紀,
酒店暑假打工,
酒店寒假打工,
酒店經紀人,
菲梵酒店經紀,
酒店經紀,
禮服酒店上班,
酒店經紀人,
菲梵酒店經紀,
酒店經紀,
禮服酒店上班,
酒店小姐兼職,
便服酒店工作,
酒店打工經紀,
制服酒店經紀,
專業酒店經紀,
合法酒店經紀,
酒店暑假打工,
酒店寒假打工,
酒店經紀人,
菲梵酒店經紀,
酒店經紀,
禮服酒店上班,
酒店小姐兼職,
便服酒店工作,
酒店打工經紀,
制服酒店經紀,
酒店經紀,

,

1:14 PM  

Post a Comment

<< Home