SQL Velocity

Windows Performance Counters for SQL Server

It was recently brought to my attention that I neglected an important piece of information in my first post (Thanks Lance!).

I get a lot of questions about the appropriate Windows Performance counters to capture for SQL server analysis. This post is cheating; from now on I get to send out a simple link!

LogicalDisk Perfmon Object

• Average Disk sec/Read
• Average Disk sec/Write
• Disk Reads/sec
• Disk Writes/sec
• Disk Read Bytes/Sec
• Disk Write Bytes/Sec

SQL Server Buffer Manager Perfmon Object

• Checkpoint pages/sec
• Page Reads/sec
• Readahead pages/sec

Processor

• % Processor Time
• Sytem: Processor Queue Length

Memory

• Available Mbytes 

 

Table 1: Windows Perfmon Counters

LogicalDisk Perfmon Object

The Average Disk Sec/Read and Write counters measure the time it takes for an input output (I/O) operation to get from the calling application (in this case SQL) to the storage system and back. This measure is the single biggest indicator of I/O system health. These counters are a demonstration of Little's Law; as a systems capacity reaches its maximum performance its response time approaches infinity.  

An optimal system design will balance latency with performance.  The optimal performance region is called the knee of the curve.  The knee demonstrates the maximum throughput that can be achieved with acceptable application latency.

Note:  I will have an entire series on I/O performance benchmarking, stay tuned! 

Figure 1: Little's Law

The latency counters are measured in milliseconds. A reading of 0.001 is one millisecond, 0.010 is ten ms, and .100 is a measure of 100 ms. Good and bad latency changes depending on many factors (these factors are way beyond the scope of this post). A good rule of thumb is latency over 25ms (or .025) is bad.

Throughput is measured as the number of requests and physical amount of data flowing through the I/O subsystem. The counters Disk Reads and writes per second show how many requests are being made of the system. Disk read and write Bytes per second record how much physical data is being moved.

To calculate average I/O sizes simply divide Bytes per second by the number of operations per second. Knowing the size of the I/O can show application behavior. For instance a SQL data base will write 8K data pages and read 64K data extents. When SQL server 2008 needs large volumes of contiguous data it will start reading 128 or 256K I/Os. This can greatly reduce overhead and increase performance. It will also show empirical evidence that the I/O patterns are sequential.

The Disk Queue Length count is affirmation of a busy system. If the system demonstrates a high Queue and low latency then things are great! If the Queue is high and the latency is high then the I/O system may be exceeding its maximum available performance or it may have an issue.

SQL Server Buffer Manager Perfmon Object

Each SQL Server instance measures Buffer Manager Activity. These counters help the operator distinguish scan from seek activity. Scan activity is a sequential operation associated with high throughput and seek operations are usually random. The random operations are associated with lower throughput.

Checkpoint pages/sec measures the number of 8K DB pages written per second when checkpoints are issued.

Page Reads/Sec measures the number of page read operations issued by the SQL engine every second.

Readahead pages/Sec measures how many read ahead operations issued by SQL server. SQL 2005 and newer use a read ahead engine that varies I/O sizes from 8K to 512K in size. More sequential operations will usually be larger in size.

Processor

The % Processor time counter shows the processor load on the server. Busy processors are not bad (quite the opposite). Busy processors with bad SQL performance with low memory use and fast I/O are an indication that the processors are a bottleneck.

Note: On Virtual Servers (Thanks to Brent Ozar for this Tip) % Processor time does not reflect what is actually happening on the server.

Use System: Processor Queue Length to determine how many tasks are waiting on the CPU.

Memory

The Available Mbytes counter shows how much memory is in use. High memory use is not in and of itself a bad thing (especially if SQL server is configured to use a lot of RAM). High memory use can indicate a bottleneck. Use the memory counters along with the other counters listed here to identify bottlenecks.

Other Resources

The Microsoft SQL Cat team has an excellent white paper detailing SQL performance: http://sqlcat.com/whitepapers/archive/2010/05/10/analyzing-i-o-characteristics-and-sizing-storage-systems-for-sql-server-database-applications.aspx

If you ever get the chance to see Mike Ruthruff speak seize the opportunity! Besides being a co-author of this paper he is an absolute expert in SQL Server performance tuning.

Detailed Windows I/O: Process Monitor

In previous posts I outlined performance counters that can be used to view windows I/O:

http://sqlvelocity.typepad.com/blog/2010/08/windows-performance-counters-for-windows.html

Perfmon is a great tool for analyzing how busy a given system is. Unfortunately Perfmon needs to sample data over time and by necessity performs data smoothing. If a tool only captures data every 5 seconds it is not going to be absolutely accurate in how it reports specific problems. Think of Perfmon like an MRI. An MRI shows you an approximation of what is going on inside the human body. If you need to verify specific problems then you need a scalpel and a surgeon.

Enter the Windows Performance Analysis Toolkit (XPERF) and Process Monitor. I will relate Process Monitor to a surgical scope and XPERF to an intrusive scalpel. Process Monitor is extremely lightweight and lacks the full suite of diagnostic tools included with XPERF.

Over the coming weeks I will cover both tools in detail.

Process Monitor

Process Monitor is created by the Windows Sysinternals team. Mark Russinovich and Bryce Cogswell created the Sysinternals web site back in 1996 to highlight advanced system utilities and technical information.

Note: Mark Russinovich is a Microsoft Technical Fellow who is a co-author of Windows Internals: http://technet.microsoft.com/en-us/sysinternals/bb963901.aspx - Let's just call this book the definitive guide to Windows. By definitive guide I mean the book folks in Microsoft PSS turn to when they need to complete Kernel mode debugging. Good read if you want to stop Sudoku puzzles for a while and really grow your brain power.

Useful tools referencing disks:

Microsoft Sysinternals Disk and file utilities: http://technet.microsoft.com/en-us/sysinternals/bb545046

Lets start our exercise by Downloading the ProcessMonitor.zip file from: http://technet.microsoft.com/en-us/sysinternals/bb896645

. The .zip file contains three files:

• Eula.txt – Lawyer speak
• procmon.chm – help file
• Procmon.exe – The tool!

Run Process Monitor by executing the Procmon.exe application:

The first time Procmon.exe is executed it will prompt the user to accept its license terms. This will only appear the first time the application is executed. Procmon.exe will write your acceptance to the system registry. Other than that the tool is run from the .exe file.

Process Monitor opens and shows all running processes:

Process Monitor will by default show:

• Registry activity
• File system activity
• Network activity
• Process and thread activity
• Not shown by default; profiling events

The reader should notice that this is a SQL Server I/O performance blog. As such I am going to turn off everything monitored by this tool other than SQL Server I/O. To view SQL Server disk activity we only want to see file activity (I also like to see what the process and thread activity are doing, for the remainder of this post I am leaving it turned off). Turn off features by toggling the "Show activity" icons until only "show file system activity" is toggled on.

Process Monitor is now configured to show every file system access. For the purposes of viewing SQL server we are going to turn off all processes other than SQLServer.exe. Further pare down what process monitor is displaying by excluding processes. Right click the process to exclude (in this case everything other than SQLServer.exe):

Repeat these steps until only SQLServer.exe is listed (I am doing this to simplify this post. You will probably want to see how many processes interact with the file system and the server. This is a powerful tool!).

For this article I ran a TCP-H like workload. The actual workload runs on a 300GB data file (600GB after indexes are applied).

Process Monitor is showing the operation (in this case read file – my test is running table scans), the accessed file, the result, details, and the Process ID (PID). For this exercise we are primarily interested in the Detail section. Windows is accessing a disk Offset and a length of data.

What is an offset you ask? An offset is an integer that defines location within a data structure http://en.wikipedia.org/wiki/Offset_(computer_science) In this case an offset maps to a Logical Block Address http://en.wikipedia.org/wiki/Logical_block_addressing. Logical Block Address (LBA) is a scheme used by disk drives and storage arrays to map storage sectors on a disk. Here is a (ahem, great – had to teach myself 3D rendering to create this) diagram of a disk.

SQL server stores data within pages and extents (http://msdn.microsoft.com/en-us/library/ms190969(v=sql.100).aspx). My Process Monitor example is showing all of us how SQL server is actually addressing the .mdf files.

Length denotes the size of the I/O request. A length of 524,288 bytes is a 512 KB I/O request (524,288 / 1024 = 512).

I can see in my example that my queries are issuing relatively sequential series of 512K I/O's:

Offset: 62,904,336,384, Length: 524,288, I/O Flags: Non-cached, Priority: Normal

Offset: 63,134,171,136, Length: 524,288, I/O Flags: Non-cached, Priority: Normal

Offset: 63,505,760,256, Length: 524,288, I/O Flags: Non-cached, Priority: Normal

Offset: 62,577,311,744, Length: 524,288, I/O Flags: Non-cached, Priority: Normal

Keep in mind that the offset is a representation of a location on the disks (in this case a 2TB volume from a VMAX array that resides on about 80 physical disks) and length is the size of the request. The offsets will not increment by the length.

An example of a join operation pulling data from different tables (mixed 128K and 256K I/O's):

Offset: 366,849,884,160, Length: 131,072, I/O Flags: Non-cached, Priority: Normal

Offset: 78,508,130,304, Length: 262,144, I/O Flags: Non-cached, Priority: Normal

Offset: 369,155,465,216, Length: 106,496, I/O Flags: Non-cached, Priority: Normal

Offset: 78,512,586,752, Length: 262,144, I/O Flags: Non-cached, Priority: Normal

Offset: 369,364,566,016, Length: 262,144, I/O Flags: Non-cached, Priority: Normal

Finally a great example of Random requests (256K, 512K, 80K, 464K, and 384K):

Offset: 15,746,727,936, Length: 262,144, I/O Flags: Non-cached, Priority: Normal

Offset: 13,209,829,376, Length: 524,288, I/O Flags: Non-cached, Priority: Normal

Offset: 14,578,548,736, Length: 524,288, I/O Flags: Non-cached, Priority: Normal

Offset: 13,221,363,712, Length: 524,288, I/O Flags: Non-cached, Priority: Normal

Offset: 14,580,645,888, Length: 524,288, I/O Flags: Non-cached, Priority: Normal

Offset: 13,225,558,016, Length: 81,920, I/O Flags: Non-cached, Priority: Normal

Offset: 14,583,267,328, Length: 475,136, I/O Flags: Non-cached, Priority: Normal

Offset: 10,122,952,704, Length: 393,216, I/O Flags: Non-cached, Priority: Normal

Offset: 10,125,967,360, Length: 524,288, I/O Flags: Non-cached, Priority: Normal

WARNING: Process Monitor can capture a huge amount of data. Be cautious running this tool on production systems. DO NOT let it run in perpetuity! I managed to kill TS on my test machine because I ran a 10 hour test, logging all the while. I performed a 5 minute SQLSERVER.exe file activity capture. The CSV export file is 1.33GB! Use Caution, you have been warned!

This is an amazing tool. I like to use it to give me a detailed snapshot of I/O on my servers. When I detect a performance anomaly with Perfmon I can look in detail at what caused it. Always remember, this tool is a scalpel.

Be sure to get comfortable with Process Monitor on a test system before running it in production.

Note: I need to rant on the beauty and elegance of the SQL engine for a second. SQL server detected that my example test is largely sequential. Instead of grabbing 64K data pages, it is dynamically grabbing 512K I/O. This is a lot more efficient than grabbing a ton of smaller I/O. Please realize that this has its limits. 512K is about the maximum I/O you want to grab from a storage system. If you start getting into the megabyte I/O size you will actually see overall performance degrade and latency will skyrocket. Storage was designed to handle smaller I/O. 256 to 512K I/O's are just about perfect. End of rant!

A great way to do this is to run SQLIO.exe with a known performance profile. Capture Perfmon data, and use Procmon.exe to get a super detailed view of what the tool is actually doing to the storage system. Mike Ruthruff has an excellent article on SQL I/O at: http://technet.microsoft.com/en-us/library/cc966412.aspx.

Part 2 will cover how to export the data into Microsoft Excel to perform some math and graphing magic; let's call it a poor man's XPERF (that is a tool for a later post as well).

Part 3 will demo what random and sequential I/O generated by SQLIO.exe looks like.

Enjoy! Post your questions…

Webcast - Architecting a Massively Scalable BI Platform

Wednesday, April 6, 2011 at 12:00p.m. ET/ 9:00a.m. PT

I am speaking with two Microsoft colleagues and the Enterprise Strategy Group at a SQL server Magazine webcast on the topic of massive Business Intelligence scale out using Microsoft SQL Server 2008 and VMAX.

I get to present with Akshaya Arora (arguably the most knowledgeable SQL Server DBA I have ever met - gauntlet at the feet for the rest of you) and Guru Kirthigavasan (PM for adCenter BI).

Please register (at a minimum head into the chat room to heckle) at:

https://www.vconferenceonline.com/event/regeventweb.aspx?id=243&cid=eventscentral

There is a companion white paper on ESG's web site:

http://www.enterprisestrategygroup.com/2010/11/microsoft-adcenter-business-intelligence-infrastructure-powered-by-emc-symmetrix-vmax-and-microsoft-sql-server-2008/

And a PDF version at EMC.com:

http://www.emc.com/collateral/analyst-reports/10499-esg-wp-ems-2010-it.pdf

-Mike

 

Scalable Shared Data Base Part 4

Directing Traffic

Scalable shared data base enables the merging of a scale up computer science problem (the loading and reporting of data) with a scale out solution. The scale out solution is made possible because the data is synchronized across nodes as a read only copy. This same scale out approach has been used for years in large web sites.

Each web server in a farm contains the same set of read only data. A technology called load balancing allows the web site to display a single name, say EMC.COM, and direct user connections to one of many web servers hosting the web site. Advanced load balancers can direct new users based on the number of existing connections maintained by each web server. Web servers with low numbers of users receive incoming users until each server in the site is balanced. To ensure a great user experience, the load balancer will return existing users new requests to the same server they have already been using.

Load balancing technology used for web sites is based on network connections. A web browser opens a connection to the load balancing device or service. The load balancer forwards this request to a web server based on appropriate parameters. Simple load balancers rely on a round robin approach:

• Request 1 is directed to server A
• Request 2 is directed to server B
• Request 3 is directed to server A
• Request 4 is directed to server B

These network load balancers can be either a software service, such as a DNS server that sends requests to different addresses, or a hardware appliance. Both software services and hardware appliances can offer advanced features.

• Least Busy – This technology tracks parameters on the server and directs incoming connections based on usage. The most common parameter is the number of network connections already open on a given server.
• Drain stop – Drain stop allows an administrator to gracefully remove servers from the site. When the drain stop is initiated, new connections are not sent to the draining server. Any existing connections are allowed to be closed by the user. As soon as all connections are closed the server can then be maintained without affecting the overall web site.

Wikipedia has an interesting article detailing network load balancers that covers the topic in great detail: http://en.wikipedia.org/wiki/Load_balancing_%28computing%29

The reader should note that I have described load balancing and Web sites, but the article is about integrating SQL server and SQL Server Analysis Services with load balancing. Most hardware and software load balancers are designed to run large web sites. These systems work extremely well! Unfortunately they are not designed with long running queries in mind.

As a security measure, many load balancers limit the time that a given request can process on a given host. An average SQL or Analysis Services query can exceed these timeout values. For this reason I have often used load balancer service from Microsoft called Network Load Balancer (NLB). NLB is easily adaptable to SQL server and Analysis Services. This is not to say that another load balancer won't work, however, configuration specifics for any load balancer other than NLB are beyond the scope of this post. Check with the manufacture of your load balancer for recommended SQL configuration settings.

In the white paper; http://www.emc.com/collateral/hardware/white-papers/h8129-performance-scaling-sql-wp.pdf I described an Analysis services system that uses NLB to load balance three servers that share a single read only reporting volume.

Once these first servers are load balanced then we can really start to scale the solution. Because we are using the storage to replicate changes in the data we can create more than one shared volume. If we attach half of the servers to one shared read only volume (update pair 1) and the second set to a second read only volume (update pair 2) we can update half of the cluster at a time. This will allow the creation of a solution with absolute minimum downtime for data updates.

The solution implementation would look like:

• Perform a data refresh on the factory node.
• Remove update pair 1 from service
  • All clients will now connect only to update pair 2
• Detach the reporting volume from update pair 1 and sync the changes.
• Attach reporting volume 1 to update pair 1 and put update pair 1 into service.
• Now take update pair 2 out of service
  • All clients now connect only to update pair 1 – hitting the latest version of data.
• Detach the reporting volume from update pair 2 and sync the changes.
• Attach reporting volume 2 to update pair 2 and put update pair 2 into service.
• Now all the data has been refreshed. As clients connect – they are directed to constantly refreshed data with minimal or no downtime.

In addition to network based load balancers, a developer can create what I will call an Application Metadata Redirector (AMR). Application based load balancers can take into account the application state of a given server. Why is this important? If a single user creates a large workload on a given server, this single user is only using a single network connection. If a load balancer only accounts for the number of network connections, then a load balancer can inadvertently direct traffic to a server that is already extremely busy!

An AMR can take into account the load that is being placed on the processors and memory of a server, re-directing clients accordingly. Remember that one of the great features of Analysis Services is the cache. Make sure returning clients get re-directed back to servers they have already executed queries against (unless you have executed a data refresh – then it does not matter as the cache will be reset).

 

Unfortunately I am not aware of a commercially available product that will perform redirection for either SQL server or Analysis Services. If anyone knows of such a product please post in the comments.

If you decide to take on the task of creating a customer Application metadata redirector then the following blog post will be a great starting point (thanks Denny!): http://bloggingabout.net/blogs/mglaser/archive/2008/08/15/configuring-http-access-to-sql-server-2008-analysis-services-on-microsoft-windows-server-2008.aspx

It is far easier to build a redirector using HTTP as the access method, as opposed to direct connections from EXCEL.

For more information on setting up NLB:

Windows Server 2008 NLB Deployment guide: http://technet.microsoft.com/en-us/library/cc754833(WS.10).aspx

Choice and Confidence for SQL Server

A quick note!

My team here at EMC has a brand new page dedicated to Microsoft SQL Server:

http://www.emc.com/campaign/global/sql-server/index.htm

There is an excellent best practices guide to SQL Server running on EMC Symmetrix Storage Systems:

http://www.emc.com/collateral/software/solution-overview/h2203-ms-sql-svr-symm-ldv.pdf

We work extremely hard to provide guides that help you implement excellent systems.  Be sure to post any topics you would like to see us write about, or any issues you are having!

Happy Reading,

Mike

SQL Data Layout Best Practices

Hello and welcome to the first installment of SQL Velocity!

 

Over the coming weeks and months I will be documenting best practices for high performance database installations.  The focus of this material will be running SQL server on EMC Symmetrix arrays. 

 

This first installment covers separating SQL Functions to optimize storage I/O.  For the purposes of this installment we will separate databases into three basic types:

• Online transaction Processing (OLTP)
• Data Warehousing
• Analysis Services (OLAP Cubes)

These database types are separated because they generally access their data files in very different ways. 

• Online Transaction Processing systems are assumed to issue random read/write I/O to their data devices.
• Data warehousing systems (often called Business Intelligence systems) often load data into the data warehouse using sequential writes, and access the data using Sequential read I/O’s
• SQL Services Analysis Services (SSAS) utilizes the NTFS file system directly.  SSAS reads and writes these many files in a highly random pattern.  Many times SSAS will randomly read many files while creating new files sequentially.  SSAS can be an excellent candidate for high performance Enterprise Flash Drives.

Each database utilizes four distinct file types.  Each file type has its own data access pattern:

• Data - Each different type of database performs either random or sequential I/O access patterns, as noted above.
• Log - Log access is mostly sequential.  The database writes data to the log, and then writes the data into the database.  Note that SSAS does not use Log files.
• Temporary database (TempDB) - TempDB is used when the system needs to perform temporary data base operations.  TempDB access is assumed to be highly Random.  SSAS does not use a TempDB device.  As a side note, TempDB can often benefit from high performance Enterprise Flash Drives (EFDs).
• Backup - Database backup files should be stored on a separate backup volume.  Backup devices are accessed sequentially.

Best practices:

• Aggregate Random I/O:  Random workload disk drive performance is based on rotational latency and seek times.  For example, a 10,000 RPM drive spins 166 times per second.  It takes some time to physically move the drives read/write head (this is measured as seek time) therefore I conservatively use 100 I/O’s per second (IOPS) to calculate how many drives are needed to support a given work load.  If I need to perform 1000 IOPS then I need 10 drives (Note:  There are a significant number of variables in these calculations; that data is beyond the scope of this post).  You will find that issuing I/O serially will not be able to achieve this performance threshold.  In order to reach optimal performance many I/O’s should be issued to the RAID set (the number of I/O’s the system can sustain is based on many factors, again beyond our scope today).
• Separate Sequential I/O:  A single physical hard disk can perform about 10 times the number of sequential IOPS as random IOPS.  Sending two sequential data streams to the same drive becomes a random operation thus conceivably reducing performance by a factor of ten.  By isolating sequential operations, fewer drives are needed.  Using the same 10 drives listed above; sequential access will yield 10,000 IOPS vs. 1,000 random IOPS, based purely on performance requirements. Separating two sequential files onto 4 total drives will yield 4,000 IOPS vs. only 400 IOPS when the sequential operations are combined.

Based on these rules the log and backup devices should be isolated. 

That sounds really easy.  Dedicate a few drives to a small DB log file and a whole lot of drives to the backup drive, ensure that nothing else shares these drives and you are home free.

Note:  There is one other really good reason to separate database functions and that is Business Continuance.  If all data base functions are placed on the same physical disks then one disaster can render all data lost.  If, on the other hand, the backup and log files are on separate physical media there is a better chance at recovery. 

Imagine that someone manages to drive a forklift through your storage array.  If you separate the backup and log files then there is an excellent chance of recovering all data.  Since the SQL Server instance writes to the transaction log before updating the database, keeping the log files along with the backup files will enable a full data recovery.  Shame on the fork lift driver – lucky for you – you planned for that.  Planning for your datacenter falling into the ocean, catching fire, or losing power; well you should implement remote storage replication, but that is a topic for another time!

This is where things get a bit complicated.

One easy way to separate I/O should be to simply create different storage devices (called Logical Units or LUNS), and on each of those LUNs create a single separate NTFS volume, such as the following:

• E$ - Backup
• H$ - Data
• O$ - Log
• T$ - Temp DB

A Symmetrix array has two technologies that greatly enhance performance:

• Hyper Volumes
• Virtual Storage (often called virtual provisioning).

While these technologies will enhance performance they can make it more challenging for the average user to clearly identify where their storage actually resides.

The Symmetrix array splits each physical disk into pieces called Hyper Volumes.  The hyper volume offers an excellent performance boost over using a single physical disk.  Using 400GB physical disks a single volume of 1 terabyte would only utilize 8 physical disks (assuming that the disks are configured using RAID mirroring).  Utilizing 50GB hyper volumes this same 1TB volume can be spread across 40 mirrored volumes.  This offers 5 times the performance when compared to utilizing the basic 8 disks.

Figure 1: Hyper volumes

Understanding a specific volume layout is important.  An administrator may separate data files onto unique volumes, only to later discover that the underlying storage is amalgamated.

Figure 2 shows an example of SQL server sharing all Physical Disks.  This configuration will induce a condition known as excessive head seek.  This causes the disk read write heads to move all over the disk surface whenever an operation is initiated.  A worst case example would be running a production backup.  The backup operation will cause the disk heads to move rapidly, back and forth, as data needs to be read from the Data and Log volumes and written to the Backup volume.  This head seeking will cause I/O that is much slower than random I/O.

Figure 2: Poorly configured SQL layout

Whenever possible do not allow a SQL data file to “wrap” back onto the same physical disk.  Figure 3 is an example of a configuration that will cause excessive head seeking.  Ensure that enough physical disks are allocated to each volume so that it can support the necessary I/O load.  Balance highly utilized hyper volumes with less frequently accessed data.

 

Figure 3: Data volume wrapping

Many Symmetrix deployments will take advantage of Virtual Provisioning, also known as Thin Provisioning.  A thin data pool can greatly improve storage utilization and work significantly increase performance.  Ideally the administrator may create one pool for data and temporary data base files, and a second pool for log and backup files. 

Best Practices

·     Utilize SQL best practices to separate data files.  Visit www.SQLCAT.com for best practices from the Microsoft SQL Customer Advisory team and www.EMC.com/collateral/software/solution-overview/h2203-ms-sql-svr-symm-ldv.pdf for our EMC SQL server best practices tech book.

·     Using Standard Hyper Volumes:

o   Ensure that heavily used SQL volumes are paired with less frequently accessed data (such as an archive). 

o   This will dedicate maximum performance to the SQL workload, while ensuring storage capacity is well utilized.

·     Using Virtual Provisioning:

o   When possible create enough thin pools to ensure that backup and log data are separated from the database files.  This should boost performance and add an extra layer of reliability to the design.

o   Size the thin pool for performance and capacity.  Add enough physical disk resources to a thin pool to absorb the entire peak IOPS load that will be generated.  I like to use a worst case scenario, assuming all I/O is random, to determine the number of physical disks that are needed in every pool.

o   Balance data files that have high performance requirements with less frequently accessed data.

·     For the Symmetrix array:

o   Ensure that workloads are balanced across

§  Physical Disks

§  Disk Groups

§  Front End Ports

Figure 4: Well-balanced configuration

I hope you enjoyed installment number one.  Please feel free to post feedback, good or bad!

-Michael