{"id":184,"date":"2012-01-10T23:24:20","date_gmt":"2012-01-11T05:24:20","guid":{"rendered":"https:\/\/jackdonnell.com\/?p=184"},"modified":"2012-02-17T19:54:02","modified_gmt":"2012-02-18T01:54:02","slug":"disk-space-sqlio-and-stuff","status":"publish","type":"post","link":"https:\/\/jackdonnell.com\/?p=184","title":{"rendered":"Disk Space – Finding I\/O Bottlenecks(SQLIO)"},"content":{"rendered":"

As a production DBA ,\u00a0I get to start\u00a0diving into the internals\u00a0of the SQL Server environment. Over the last year, we had an opportunity\u00a0to ‘let loose’ on a new clustered environment with a dedicated SAN.\u00a0 Zoom… Zoom…Zoom!\u00a0It was like performing sea trials or a shakedown tour in a new ship. Much of the testing centered around the execution of\u00a0existing SQL code and processes. We were able to set-up a series of tests using\u00a0the \u00a0Microsoft\u00a0‘s \u00a0SQLIO Disk Subsystem Benchmark Tool<\/a>\u00a0to saturate the disk subsystems.<\/p>\n

\u00a0The tool does not actually test your SQL Server configuration , but provides the means to script out and execute\u00a0a\u00a0controlled\u00a0load against the disk\u00a0subsystem.\u00a0Scripts can be executed to create\u00a0a variety of read and write operations. Parameters allow the load to\u00a0use a variety of file sizes, number of threads, latency, outstanding requests in random or sequential\u00a0reads\/writes for specific durations.\u00a0\u00a0 I am<\/span> not going to get into the <\/span>specifics of the scripts we used. The\u00a0links below provide a much better\u00a0examples\u00a0than I could tap out in this post.<\/p>\n

<\/p>\n

The testing was\u00a0conducted in two main iterations. The first was to run a script against one drive at a time with increasing load. \u00a0Next,\u00a0 executed the scripts all at the same time against all the drives.\u00a0 Establishing times with our SAN group, we were able to capture activity with SAN monitoring tools.\u00a0 Another neat find was\u00a0how to import the SQLIO output to SQL server. The output is a bit cryptic, but the import cleaned up the information\u00a0for analysis. We had a limited window to test. I wish\u00a0wish we had added a few more iterations with multiple scripts on the same drive at one time.<\/p>\n

Below are the links I used to setup the tests:<\/p>\n

SQLServerpedia.com<\/span> – SAN Performance Tuning with SQLIO<\/a><\/p>\n

Probably the best collection of information and application of the tool.\u00a0There is a How-to One on\u00a0importing the output into the\u00a0SQL Server.<\/p>\n

 <\/p>\n

Brent Ozar<\/span><\/span> – SQLIO Tutorial: How to Test Disk Performance<\/a><\/p>\n

The above link has information and even videos by Brent, but this link does a deeper dive into the tool.<\/p>\n

 <\/p>\n

SQLCrunch<\/span>.com<\/span><\/a>\u00a0 has some great articles on monitoring and\u00a0understanding <\/span>\u00a0I\/O bottlenecks.<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

As a production DBA ,\u00a0I get to start\u00a0diving into the internals\u00a0of the SQL Server environment. Over the last year, we had an opportunity\u00a0to ‘let loose’ on a new clustered environment with a dedicated SAN.\u00a0 Zoom… Zoom…Zoom!\u00a0It was like performing sea …<\/p>\n

Read more »<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"footnotes":""},"categories":[273,266,22],"tags":[277,303,274,248,275,295,276],"class_list":["post-184","post","type-post","status-publish","format-standard","hentry","category-administration","category-dba","category-sql-server","tag-database","tag-dba","tag-io","tag-performance","tag-san","tag-sql-server","tag-testing"],"_links":{"self":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts\/184","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=184"}],"version-history":[{"count":7,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts\/184\/revisions"}],"predecessor-version":[{"id":188,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts\/184\/revisions\/188"}],"wp:attachment":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=184"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=184"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=184"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}