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.

Windows I/O Tracing

I am creating this post out of order (I was planning to demonstrate I/O when various queries are applied with and without indexes – but that will have to wait for a later time. I will end up using XPERF to analyze the I/O).

XPERF.EXE is the holy grail of server performance analysis. It allows software developers to track what each and every process running on a computer is doing. Powerful stuff…

This power comes at a cost – analysis files grow quickly!

WARNING – I suggest only running this tool in a test environment or a lab. It puts extra load on the tested platform and makes simply huge capture files!

Windows Performance Analysis Tools

The Windows Performance Analysis Toolkit, or WPT, ships with the Windows SDK.

Supported Platforms:

• Windows Vista SP1
• Windows Server 2008
• Windows Server 2008R2
• Windows 7
• Windows XP SP2 – Special Install Required
• Windows Server 2003 SP1 – Special Install Required

Installation Instructions

The Windows 7 SDK (also used for Windows server 2008 and 2008R2) requires .Net 4.0 (it will be installed through the web based install). Download the Windows 7 SDK from:

http://msdn.microsoft.com/en-us/windows/bb980924

This post covers the basics for installing Windows SDK 7.1.

Kick off the web based install (it also can be downloaded as an .ISO file).

Agree to the license terms.

Select the appropriate Install Location.

At a minimum the Windows SDK 7.1 will install the application verifier and debugging tools for Windows. Ensure that the Windows Performance toolkit is selected.

Installing on Windows XP or Server 2003

From the MSDN WPA article:

Windows XP

Windows Performance Analyzer can be used on Windows XP SP2 and Windows Server 2003 SP1 to gather trace information. All operations that require trace decoding must be done on Vista or Windows Server 2008. This includes viewing traces in the Windows Performance Analyzer tool (Xperfview.exe).

In order to capture trace information on Windows XP SP2 or Windows Server 2003 SP1 take the following steps:

1.From the "Windows Performance Analyzer" directory on a Windows Vista or Windows Server 2008 machine copy xperf.exe and perfctrl.dll to a directory that is in the PATH of the Windows XP SP2 or Windows Server 2003 target system.

2.On the Windows XP SP2 or Windows Server 2003 target system, run the trace using standard WPA syntax.

3.Copy the "etl" files to a Windows Vista or Windows Server 2008 system that has a full installation of WPA.

WPT Tools

The WPT tools are made up of:

• Xperf.exe – Captures traces, post-processes traces.
• Xperfview.exe – Presents trace content in the form of interactive graphs and summary tables.
• Xbootmgr.exe – Automates on/off state transitions and captures traces during these transitions.
• Perfctrl.dll – DLL file used by xperf.exe to complete traces.

Collect Data

The WPT tools are designed to produce in-depth performance profiles of Windows operating systems and applications. Xperf.exe is used to both start and stop tracing activates. Xperf.exe is designed to log Kernel Groups. The specific kernel group dealing with Disk calls is DISK_IO.

Execute a DISK_IO trace by executing the following command:

xperf -on DISK_IO

Note that xperf traces can become extremely large. The xperf tool by default records all tracing information to a file called kernel.etl. The file location defaults to C:\. To configure a different logging file location use the –f <filename> parameter.

    Xperf –on DISK_IO –f

The trace may be stopped by executing:

    Xperf –stop

Once the trace is stopped it can be merged with other files. The –d flag will merge a stopped trace. If the –d flag is used on a running trace the –stop command is implied. The trace will be stopped and merged in one step. Define the location of the ETL file using the <filename>.etl parameter. The processed .ETL file will be written to the directory that xperf –d is executed from.

xperf -d test.etl

Analyzing Results

View graph results by opening the ETL file (or use the Windows Performance Analyzer GUI from the programs menu).

xperf test.etl

This will open the Xperfview.exe graph viewing tool. WPA defaults to the Disk I/O and Disk Utilization graphs.

The tool defaulted to use the Disk I/O and Disk Utilization graphs (this was due to the fact that only Disk I/O was traced). The charts can be customized to display only desired data.

Clicking on the Graphs tab will allow the user to add or remove graphs from the analysis.

The most useful Xperf feature is the Disk I/O Detail Graph. To view the detailed Disk I/O detail chart right-click a region on the Disk I/O or Disk utilization chart and click Detail Graph.

The detail chart will display a graphical representation of all offsets (offsets are also called Logical Block Numbers), and offset accesses. This view will immediately identify random and sequential disk access. Red lines denote forced flushes. Hovering over each data point will detail the type of I/O operation, the start time, end time, location, and the calling process.

Note: Only capturing the DISK_IO counter will not allow Xperf.exe to determine the calling process. Using the DiagEasy flag on startup will capture DISK_IO, Process information, and kernel information. Unfortunately the use of DiagEasy will significantly increase the size of any trace.

More Information

By design this section only covers basic WPA features relating to disk access. WPA is an extremely powerful performance analysis tool.

Detailed WPA features and documents can be accessed via MSDN:

http://msdn.microsoft.com/en-us/library/ff191077.aspx

As you can see this tool provides the same data as Process Explorer, but you don't need to run through configuration steps, or fire up Microsoft Excel to graph the results.

In the coming weeks I will be using XPERF to analyze different queries, the benefits of indexes, and outline a process for integrating performance analysis into an engineering life cycle. Enjoy tinkering with XPERF!

Detailed Windows I/O: Process Monitor Part 2

Today's post is the second in a long series. Part one covered the Microsoft Process Monitor tool. Part will cover interpreting Process Monitor results. In my lab I have a TPC-H "Like" environment (If testing is not sanctioned by the TPC committee then you are legally obliged to call it TPCH Like - http://www.tpc.org/tpch/). The environment consists of a 300GB data base with clustered and Non-Unique Indexes (depending on the table and keys). The data is generated by the TCPH utility called DBGEN.EXE (you can download the source files and documentation at http://www.tpc.org/tpch/).

Here is the database structure:

For the purposes of these examples I am using the Supplier table. It consists of 3000000 rows.

I fired up two different queries and ran Process Monitor to track the I/O (http://technet.microsoft.com/en-us/sysinternals/bb896645.aspx - Version 2.95 as of this writing).

The first query simply counts the rows in the table:

    Select COUNT(*) as "row count"

FROM (select * FROM [TPCH300R10Thk].[dbo].[H_Supplier]) as t

The query returns a value of 3000000. It scans the .mdf file with 64K and larger I/O (a total of 209 reads averaging 165K each). For quick setup I only created one data file and one log file.

I set up Process Monitor to track only sqlservr.exe events (see Part 1 http://sqlvelocity.typepad.com/blog/2011/04/detailed-windows-io-process-monitor.html). I saved all of the events that generated the query values as a .csv file:

Next, open the .csv file in Excel. We first need to separate the detail column into usable parts. Each detail records the offset, length and I/O flags:

Offset: 1,245,184, Length: 65,536, I/O Flags: Non-cached, Priority: Normal

I use the text to columns feature of Excel to separate the values into columns (Data tab then Text to Columns):

I use a Space as the delimiter of choice (using comma does not work as commas are used as both a 1000's separator and a value Delimiter):

Finally I use step 3 of the text to column wizard to clean up the import by skipping the text (as I don't want it for this analysis).

I am left with an offset column and a length column:

1,245,184,    65,536,

As a final step I clean up the data by removing the commas and converting the columns to number with no decimal places.

I inserted a Scatter graph (Scatter with Markers only) and plotted all of the offsets (Insert tab – charts – scatter). This creates a visual representation of the query:

This graph shows that the row count query created a nice sequential access pattern. In addition to the access pattern we can also look at the actual size of each I/O. In this example SQL server issued 30 64K I/O requests. As the SQL engine detected sequential access it began to size up the I/O request to 128, 192 and 256K.

Note: I wanted to specifically point out that the offset range is localized. If the data set was more varied, for instance starting at 0 and going all the way up to 361,380,839,424 the graph will appear to show only sequential data access. It is important to look at the data ranges before plotting to ensure the graph shows the appropriate data. In other words, zoom…

Excluding the first 6 I/O's and re-creating the chart will result in a more detailed data view:

The graph reveals that the initial I/O requests were purely sequential and extremely small. SQL Server realized the sequentially of the data and cranked up the request size. Realize that this is a 2TB volume with approximately 600GB of data (this is why the offsets values are so large). This query is accessing 463MB of data space with 33MB of index.

The second query lists the supplier's phone numbers that are from PERU (Country 17 is PERU – I skipped the join to simplify the I/O access):

    SELECT s_phone

FROM [TPCH300R10Thk].[dbo].[H_Supplier]

Where s_nationkey =17

The query caused a more random I/O access pattern that is localized to the specific table and the I/O sizes were highly varied (from 16K all the way up to 512K).

As you can see, tying procmon.exe with Microsoft Excel results in an extremely detailed view of each I/O request. We can view the size of each request and exactly how the request accessed the disk. Using procmon.exe when testing new queries can lead to code optimization that is simply not possible with other tools. Perfmon is a great tool for looking at long term trends and finding problems but it is not granular enough to really diagnose specific problems.

Please remember to always experiment with these tools. Never run Process Monitor in production unless it is an emergency and you take precautions to limit system impact (as noted in the last post; this tool is tracing EVERYTHING in memory. Tracing SQLServer.exe as it scans a 100TB data warehouse will result in a runaway application and a system reboot). View the Process Monitor help file for notes on scripting etc…

Enjoy!

EMC World 2011: Protect and scale Microsoft SQL Server using Symmetrix replication technology

Thanks to everyone that attended the presentation.

Protect Microsoft SQL Server

The Microsoft SQL Server and SQL Server Customer Advisory Teams are hard at work on SQL Server codename Denali.

Stay tuned to http://www.microsoft.com/sqlserver/en/us/product-info/future-editions.aspx and http://sqlcat.com/Default.aspx for more information.

Scale Microsoft SQL Server

This blog has more information about Scalable Shared Database in a five part series:

http://sqlvelocity.typepad.com/blog/2010/09/scalable-shared-data-base-part-1.html

SQL Server Tech Book

The definitive guide to running Microsoft SQL Server on EMC Symmetrix arrays is located at:

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

Slides

In case you missed the presentation, or simply can't get enough; here is the presentation along with my speakers notes. Some of the slides were animated – the notes reflect the animation steps (turns out power point does not create animated slides).

Slide 1

 

Slide 2

 

Slide 3

 

Slide 4

 

RPO – how much data are you going to lose?

Example: SQL servers backed up by tape every 24 hours; the Recovery Point Objective was 24 hours.

Recovery Time Objective; as soon as a spare server could be beg borrowed or stolen.

If the building fell over then the tapes were moved offsite once a week. RPO = 1 week.

RTO was the time it took to acquire a new building, order gear, etc…

Slide 5

 

SRDF provides trusted failover; it is used by banks and brokerages worldwide.

SRDF allows EMC Symmetrix arrays to replicate data between storage arrays.

SRDF has many operational modes:

• SRDF/Synchronous - used locally
• SRDF/A – Some chance of Data Loss – can replicate worldwide
• SRDF/Star – Advanced Multi Site

SRDF/S – Recovery Point Objective of zero or no data loss. SRDF/S is used within a metropolitan area network or single data center.

Companies often run SRDF/S and SRDF/A – this hybrid solution mitigates risk.

Banks will host data and fail over across the Hudson River. They will have a bunker site at a "stable" region of the country or world.

Let's explore how SRDF/S works:

• We have our source server and our backup server.
• The source server will write data to cache.
• The data will be mirrored by SRDF/S to the target Symmetrix
• The arrays back the cache by battery, in the event of failure the cache data will be preserved.
• The remote array will acknowledge the write and respond to the source array with a success code
• The source array will respond to the server with a success code.
• Both arrays will then commit data to disk.
• As you can see – this configuration is extremely sensitive to distance. The further apart these arrays are located, the longer write operations will take.

Slide 6

 

SRDF/A, or Asynchronous replication – allows replication across the world.

SRDF/A Does not impair the source server's write latency.

This lack of performance impact comes at a price; It cannot offer a Recovery Point Objective of 0.

The amount of unrecoverable data is affected by:

• The rate of data change
• Distance between sites
• Network quality and bandwidth

When SRDF/A absorbs a failure, any data that has been un-replicated is lost.

In the case of Microsoft SQL server, the remote database will simply enter recovery mode to recover the data base and enter normal operation.

In the event of a failure SRDF/synchronous and SRDF Asynchronous allow businesses to limit or eliminate data loss.

Slide 7

 

SRDF/Cluster Enabler is the technology that ties Windows, SQL, and EMC SRDF together to reduce Recovery Time.

In partner engineering I get to work closely with the Microsoft SQL Customer Advisory Team – the world's foremost SQL experts! The CAT team maintains a Customer lab where customer's prototype custom SQL Server based solutions. These solutions often push the boundaries of Computer Science.

The environment depicted mirrors a system deployed by EMC Partner engineering for the Microsoft SQL CAT team.

VMAX arrays in the Customer Advisory Team lab Linked through Gig E networks and a latency generator to an array in the SQL Performance lab.

The VMAX arrays replicated Data and Log volumes.

To setup up this environment we:

• Configured the storage
• Set up SRDF
• Installed Microsoft Failover clustering
• Installed Microsoft SQL Server
• Finally we installed SRDF/CE.

• SRDF/ Cluster Enabler is a Cluster Resource Dll that becomes a dependency of the Physical Disk resources.
• Failover Clustering Checks with SRDF/CE when failing over. SRDF/CE in turn checks the state of replication on the arrays.
• SRDF/CE will manage the SRDF replication operations to ensure a smooth failover and failback

Today SQL Server, Including SQL Server 2008R2, does not support Multi Subnet failover. Windows Server 2008 came out with the failover clustering feature of multi subnet failover. All servers must be on the same subnet.

Announced last November, at the SQL PASS conference in Seattle, SQL Server Codename Denali will change all of that. It adds support for SQL running on multiple subnets. This will greatly simplify the deployment of GEO-clusters.

Slide 8

 

This slide represents a local server failure event.

• Server Failure.
• SRDF/CE detects storage replication state. In this case the failover operation will not result in an array failover. SRDF/CE is checked by Microsoft Failover Clustering and simply allows the failover operation to continue.
• Failover Clustering executes local physical disk migration

 

Slide 9

 

This slide investigates a remote array failover:

• Site failure.
• SRDF/CE detects that the storage that Failover Clustering wants to access is actually the remote side of the SRDF link. SRDF/CE will stop the replication and set the storage to read/write enabled.
• Failover Clustering mounts the physical disk resources.
• If the original source site is online SRDF/CE will reverse the replication.

Caveat – In the event of a site or communication failure SRDF/CE will not automatically fail over. User interaction is required to bring up the cluster in the event of a site failure. SRDF/CE has a automatic failover mode; it is a feature that must be enabled by the user.

Slide 10

 

SRDF/ Cluster Enabler makes Windows failover clustering extremely Smart

 

• SRDF/CE integrates seamlessly with Failover Clustering.
• The setup interface closely mirrors the Failover Clustering MMC snap in.
• SRDF/CE uses the Symmetrix API to directly interface with, and control array operations.
• Once the environment is setup – it is managed via the Failover Clustering Microsoft Management Console
  • Simply move the resource group and SRDF/CE will ensure appropriate storage configurations
  • This allows DBA's to manage the environment with minimal impact to other teams

Slide 11

 

Most businesses I work with are highly compartmentalized

• Teams interact with tickets.
• The storage team handles storage
• Network team handles networking
• Server team handles servers
• Windows team handles Server OS and Failover Clustering
• Database Team handles DB's
• Application team owns the overall system, but depends on all other teams for support.
• In the Event of a failover all teams must engage. Automation is usually broken out by support team.
• If the customer deploys SRDF then the RPO is a few minutes.
• RTO can be extremely long
  • SRDF/CE simplifies the communications structure between teams, allowing the DBA to control or automate all failover activities.
  • No 3am calls to admins.
  • The real intelligence here is to implement a Site concept to Windows Failover Clustering

Slide 12

 

Slide 13

 

Traditionally SQL has been a scale up solution.

Only Data Warehousing systems have been built to scale out.

Scale out means a distributed system – think of Bing and Google.

OLTP workloads, where data calculations must be performed holistically, are a scale up computer science problem.

The following Scalable Shared Database example covers SQL Server Analysis Services, Microsoft SQL Server can also be used in a Scalable Shared database solution.

Slide 14

 

Microsoft has created a technology Scalable shared Database that ties scale up processing operations with scale out reporting.

Scalable shared database allows SQL Servers to present a read only point in time data copy to multiple reporting nodes.

Scalable shared database is supported by the listed platforms.

Slide 15

 

EMC TimeFinder enables Scalable shared database to be used in massive data processing operations.

Array-based software

• Snapshots: Space-saving views of production information
  • A snapshot is a point in time copy of data
  • The snapshot keeps the original data while the source LUN is updated
  • Because the snapshot only reflects changed data, it minimizes space used.
  • When the snapshot is used, let's say for reporting, the reporting operation will affect the source LUN as well.
  • Think of a snapshot as a Database view
• Clones: Full copies of data
  • A clone is a point in time full copy of data.
  • The clone will consume as much physical space as the source volume (this also depends on RAID type)
  • The clone isolates performance – reporting operations on a clone volume do not affect the host.

TimeFinder has been used for years to provide easy to use and restore backups.

 

We are extending its use as a highly efficient data movement solution.

 

The storage array does all the work; no extra load is placed on the host or the network.

Slide 16

 

Windows and SQL Server TimeFinder Integration

SQL Server first writes data to the SQL log file. This data is then written into the database. Remember that the database resides in the server memory and on disk. SQL server will first write data to server memory, then it "Flushes" dirty, or unwritten, data pages to disk.

TimeFinder replicates storage data, we call this block data.

Block data is simply data that does not have a file structure applied. The host maintains a mapping of file names to block locations. This is known as file data.

The host has no knowledge of block replication. If we simply create a point in time copy – without notifying the host – we run the risk of having inconsistent data.

To provide a consistent Point in time copy:

Microsoft SQL Server utilizes Microsoft

• Volume Shadow Copy Service (VSS)
• SQL Virtual Backup Device Interface (VDI)

• VSS and VDI freeze incoming writes and flush data residing in memory to be written to disk.
• Note: VSS and VDI have a 10 second operational timer. Large numbers of pending writes will usually fail to be written within the 10 second timer.
  • Ensure that automated SQL jobs, such as a bulk load, or re-index, are completed prior to executing a VSS or VDI Freeze and Thaw.

VSS and VDI use a Storage Provider.

• The storage provider acts as a service broker, coordinating the freeze and thaw operations with storage device copy and split operations.

EMC offers two Storage Providers for the Symmetrix array:

• Both offerings utilize the Symmetrix Application Programming Interface
  • TimeFinder Symmetrix Integration Module
    • This is an API
  • Replication Manager
    • This is a backup management environment designed for TimeFinder

• Microsoft SQL Server Analysis Services does not integrate with VSS or VDI.
• Analysis Services writes XML files directly to the NTFS Windows file system.
• To ensure data integrity, I recommend detaching Analysis Services cubes prior to creating clone devices, or snapshots.

Slide 17

 

Microsoft adCenter is the advertising system that powers the Microsoft Bing search engine.

When you complete a Search on Bing Advertising will render at the top and side of the search results.

These keyword ads are powered by adCenter.

adCenter provides really rich Business Intelligence and analytics to their customers; the advertisers.

All of the adCenter systems have really strict SLAs wrapped around system performance. All data from ads must be rendered by the BI system within two hours. In addition all web reports must continue to rapidly refresh, all while the system is processing customer driven custom queries.

Internal business reporting put excessive load on the customer facing systems. The answer was SQL Server Analysis Services.

The Analysis services solution became so successful that it was soon overloaded. adCenter first upgraded the Analysis Services system to SQL Server 2005, and again to SQL Server 2008. Both upgrades resulted in measured performance gains. Even with these software and server upgrades the system was again tasked beyond its limits.

In cooperation with the SQL Customer Advisory Team and EMC Partner Engineering the adCenter analytics and Business Reporting team extended Analysis services using Scalable shared database and Symmetrix time finder.

This solution is an overview of what is currently in use by Microsoft adCenter to provide Business reporting information.

• The data is fed by SQL server Data Warehouses
• Data is loaded into the SQL Server analysis services Factory server. The factory presently runs a 24 hour data load and processing cycle.
• The data is stored on a Symmetrix factory volume.
• The Symmetrix VMAX array, utilizing TimeFinder Clone, makes a full point in time copy every 24 hours.
• Reporting servers all point to the same read only reporting volume.
• The reporting servers are fronted by the Microsoft Windows Network Load Balancing Service.
• Clients, SharePoint dashboards made using Silverlight, Microsoft Excel, and Microsoft power pivot transverse the NLB system to retrieve data from the reporting servers.

Slide 18

 

Data is collected from:

• Billing
• Customer DB's
• Business Intelligence data
• Traffic analysis services
• Web Servers

Data is loaded into a staging data warehouse.

The data is processed by ETL (Extract, Transform, and Load)

Extract Data from other computer systems

Transform it into a format usable by the data warehousing system (basically this means counting and adding – think of a giant spreadsheet)

Load the data into the data warehouse (or in this case Analysis Services).

– In this case the ETL is SSIS (SQL Server Integration Services). SSIS replaced Microsoft DTS. It is used to extract, manipulate, and load data within the Microsoft SQL Server Environment.

The data is loaded from the staging data warehouse into the OLAP (look up what this means too) cube server.

Slide 19

 

Data is processed by the Factory server.

This really demonstrates the power of SQL Server Analysis services:

The hyper cubes store a smaller subset of the entire data warehouse.

Developers can customize the entire BI system using the Microsoft SQL Server Business Intelligence BI Development Studio or BIDS (looks just like visual studio – but dedicated to BI). This system provides easy to use wizards for building integration, reporting, and analytic solutions.

The cube can pre-aggregate the data.

If a BI Developer guesses wrong about the processed data the cubes will process data on the fly, and store the answers in memory.

Think of an Analysis Services cube as a business report.

This entire suite, SQL Server Data Warehouse, Analysis Services, SSIS, Excel, Power Pivot, SharePoint, and Silverlight / .Net as the SQL Server 2008 managed self-service business intelligence system.

The adCenter Self Service BI system takes the processed data and loads it into the Reporting servers.

The Silverlight and Excel Clients are able to perform data queries. These queries are usually constructed using data connected pivot tables.

This system is extremely powerful!

Slide 20

 

Setup – The scalable shared database environment is configured and data is initially replicated.

• Using the Symmetrix API a Time Finder control group is created.
• Both the factory and the reporting volumes are added to the control group.
• Initial data copy is started. This copy is performed within the array. The host has no idea that the copy is happening.
• The control group can be queried as to the copy status. As soon as data is 100% synchronized the volumes are ready to be split.
  • The adCenter system is based on Analysis Services – No VSS/VDI integration.
  • To maintain data consistency we detach the cube. This ensures that all dirty memory pages are written to disk
• As an added precaution; The NTFS file system is flushed to disk using the Symmetrix API.
• The Factory and Reporting volumes are split. The reporting volume is a 100% full copy of data as of the split time.
• The reporting volume is attached to the first reporting server. The first server sets the NTFS read only flag. The other servers will respect the file system read only flag.
• Because the volumes are read only Analysis services (and SQL Server) will mount the volumes read only.

Data Consistency is guaranteed by mounting and un-mounting the cube.

Slide 21

 

The adCenter environment has been completely automated:

• C# application
• Starts and Stops Analysis Services processing.
• Mounts and Unmounts Cubes.
• Makes calls to the Symmetrix API.
• Mounts and Unmounts LUNS
• Controls Cloning
• The system uses part of the Symmetrix API called Symmetrix NT Control (symntctl.exe)

Automation can also be completed using EMC Replication Manager.

*Note: RM does not support mounting clones or snaps to more than one host. If automating with Replication manager first make a clone volume, and then create a Snapshot for each reporting server.

Slide 22

 

Daily Data Refresh

The adCenter system repeats this cycle every 24 hours.

 

• Data Load – The Data is loaded into the factory cube via SSIS from the Staging Data Warehouse
• Process Data – The Factory Server Processes data.
• Drain Stop Clients – NLB is used to drain stop clients. Any new connections are turned away. Existing connections are allowed to complete existing queries.
• Detach Reporting Cubes – Reporting cubes and any open files are closed. This step is extremely important. If any files are left open when the volume is un-mounted the clone volume will not successfully remount without restarting the server. This is due to the NTFS lazy reader (the lazy writer does not come into play as the volume is set read only in NTFS).
• Detach Shared Reporting Clone – The clone is detached using Symntctl.exe – this step is necessary to ensure that NTFS is updated with any changes. If the system is not un-mounted and re-mounted, the file system may not reflect changes.
• Detach Factory Cube – This step is taken because Analysis Services does not integrate with VSS or VDI. To ensure all data is updated and consistent the cube is detached, thus forcing all dirty memory pages to be committed to disk. If SQL Server is used then this step can be performed using the EMC VSS and or VDI providers.
• Flush NTFS – NTFS is flushed using SYMNTCTL.EXE. This step is only necessary for Analysis Services. SQL Server can be made consistent using VSS and or VDI.
• Refresh Clone – The array is told to update the clone. The array tracks all changes using a bitmap. The array chooses to either update only changed blocks or perform a full refresh. The refresh method is chosen based on efficiency. For the duration of the refresh the Clone target is marked not ready (this means it cannot be accessed by any hosts).
• Split Cone – After all changes have been replicated to the reporting clone it is split from the Factory LUN. The array sets the clone back to read write enabled. It can now be accessed by the hosts.
• Mount Factory Volume – the Factory volume is again accessed by the host (this is really a logical step as access was controlled from an application perspective)
• Attach Factory Cube – the factory cube is attached and the factory is now ready to load data.
• Mount Shared Read only clone – this is a multi-step process. The clone is mounted by a single server. This server uses symntctl.exe to set the NTFS file system to read only. The other nodes can then mount the volume and note its read only file system state. The array can set the LUN to read only; Windows has no way to see that the volume is read only, attempting to attach an Analysis Services Cube or SQL Server database will fail. SQL and Analysis Services specifically check the NTFS volume for the read only flag.
• Attach Reporting Cubes – attach the reporting cubes.
• Connect Clients – Use NLB to allow the clients to again access the reporting servers.

This system is really powerful. We can set up a series of parallel clones or snapshots that will allow only some of the reporting servers to be updated at a time. This allows a system that has virtually zero downtime. One caveat; Excel and most application are connection oriented. This means that when they connect to a cube or data warehouse they will fail the connection if that specific server is removed. This can be solved using web services for SQL.

Slide 23

 

Performance Impacts

Network

Network Load Balancing is an installable option for Windows Server. It works by spoofing a network MAC address. All nodes always have this MAC address active so every node receives all network traffic. This has the effect of limiting the achievable network bandwidth. If the system interconnect uses a gigabit Ethernet network, only a gigabit of bandwidth is available to the entire cluster; every note receives all traffic. Normally a network switch would only forward traffic specific to a single node. If extra bandwidth is needed, the entire network should be upgraded to a higher speed.

Hardware Load Balancers – Many manufactures provide hardware load balancing devices. Please note that these devices often do not support database systems. The hardware load balancer is designed to handle web traffic, TCP port 80 and 443. Web traffic is characterized by short connection times. A SQL decision support query may take several minutes to complete. As a protection against denial of service attacks, most hardware load balancers have an extremely short connection time out value set. Please contact the device manufacturer for information on supporting SQL server or Analysis services.

Processing

The Factory server is a single server. This server, by necessity, must be scaled up. If more processing power is needed, more powerful processors and more memory must be added. The reporting servers can scale out. The only limiting factors are the Ethernet bandwidth and storage configuration.

SQL server uses an extremely elegant I/O engine. SQL Server will dynamically scale up I/O size when sequential I/O is detected. The SQL I/O will vary from sequential to random depending on the data and query. Analysis services uses many files residing on NTFS. AS creates many XML files, Fact, Dimension, and Map files (Map files are used to store a mapping of frequently used data. Think of this as simple de-duplication). The Analysis Services workload is extremely random. The application will need to look up data in the map, fact, and possibly dimension files.

Rules of thumb for the storage:

Isolate the Factory server with dedicated performance. This will ensure that the factory has dedicated I/O that is not affected by reporting activities.

The reporting servers are putting load on a single volume. The volume must have enough performance to support all reporting servers accessing the volume simultaneously. If one server needs 10,000 random IOPS; 3 servers must have a single LUN that supports 30,000 random IOPS.

Slide 24

 

I executed a simple I/O test using the IOMETER I/O test tool. I used my laptop hard disk with a moderately sized data file.

The first test executed two 64K random reads at the same time. This test produced about 230 random I/O's at a 9 millisecond response time.

The second test executed two 64K sequential reads at the same time. Notice that the performance was about half – down to 120 IOPS at double the response time.

Slide 25

 

This multi sequential access pattern is called large block random I/O.

This rendering shows a typical hard disk. The blue and green circles represent two tracks. The orange and silver object is a read write head.

The drive has three performance modes:

• Sequential – this is the drives best performance. A typical 15K hard disk can perform about 1400 IOPS.
• Random – the same drive will only support about 220 IOPS when it is randomly executed. This is defined by drive manufactures as average seek time.
• Large block random – also caused excessive head seeking. This performance metric is tracked by drive manufactures as maximum seek time. From the previous slide this can be up to two times slower than associated random I/O.

Slide 26

 

Smart and Powerful – the adCenter solution deployed Enterprise Flash Drives.

Note the graph.

• A single server running on a LUN configured from 80 400GB 10K Drives completed 20 user queries in 10000 seconds.
• Three servers running on the same 80 drive LUN completed 20 user queries in 6500 seconds.
• Moving these servers to the same size LUN running only 9 400GB EFD's resulted in 20 user queries executing in 4000 seconds.

Migrating to just 9 EFDs not only increases performance, it drastically reduces TCO – especially power usage and physical footprint in the data center.

Slide 27

 

To summarize:

• Implementing a solution based on SRDF and SRDF/CE can reduce data loss to at or near zero.
• The recovery process can be automated and reduced from days or hours to minutes.
• These solutions utilize the Microsoft solutions already in use at most businesses.
• Adding SQL Server codename "Denali" can further simplify the deployment of geo clusters.
• Deploying a solution based on Symmetrix TimeFinder and SQL Server Scalable Shared Database can drasticly increase reporting system performance for data warehouse and business intelligence systems.

Slide 28

 

Slide 29

 

Slide 30

 

Slide 31

 

Slide 32

 

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…

Great Article on SQL Server DW Data Loads

My Friend James Roland Jones, EMC SQL Server MVP from the UK, has an excellent post that details how SQL Server loads data using the -E flag.

http://consultingblogs.emc.com/jamesrowlandjones/archive/2010/04/25/focus-on-fast-track-understanding-the-e-startup-parameter.aspx

He makes great use of the Internals Viewer for SQL Server:

http://internalsviewer.codeplex.com/

At some point I will need to conduct a write up on the SQL internals viewer.  

In the meantime - be sure to read all of the SQL Fast Track posts at Claypole's World.

 

-Mike

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

 

Shameless Self Promotion

 

Prior to my present employment with EMC I worked for 10 years at Microsoft; the last 5 years at adCenter. The adCenter is a truly amazing team! They managed to take an advertising platform from concept to the second biggest search advertising platform in just 5 short years.

The backend system is really mind blowing. It is run entirely on Microsoft products, and it represents what is truly possible with Microsoft SQL Server. Did I mention that adCenter also runs on EMC Symmetrix VMAX arrays?

I recently worked with ESG (Enterprise Strategy Group - http://www.enterprisestrategygroup.com/) and Microsoft on an ESG IT audit. The fruits of this partnership are available:

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

In PDF from EMC.Com: http://www.emc.com/collateral/analyst-reports/10499-esg-wp-ems-2010-it.pdf

Finally, the audit is a white paper at SQL Server magazine: http://www.sqlmag.com/resource/it-audit-microsoft-adcenter-bi-infrastructure.aspx?code=EPDedEMCJan11WP01030211

Enjoy!

Windows Disk Alignment

A quasi authoritative guide to improving the performance of Microsoft SQL Server running on EMC Symmetrix arrays

Overview

While attending the SQL PASS Summit in Seattle this last November, http://www.sqlpass.org/summit/na2010/, I was asked an interesting question. A DBA approached me after attending a session featuring Jimmy May from the Microsoft SQL CAT Team, http://blogs.msdn.com/b/jimmymay/. The DBA knew that he should align his Windows 2003 partitions, but his Storage Administrator stated that the Symmetrix array alleviated the need to perform partition alignment.

His question: "How do I convince the SAN and Windows administrators to align my partitions and improve performance?"

My first thought was shock. I have been dealing with performance issues due to partition alignment since the days of Windows 2000. As I was thinking about how to answer the question I realized that a non-windows administrator could easily come to the conclusion that an advanced disk array would fix anything bad the operating system threw at it.

The simple answer to this loaded question:

• When you create a new partition in Windows Server 2008, the operating system aligns the partition for you.
• If you upgrade Windows to Server 2008 (or R2), or move a partition from an earlier version of Windows, you need to partition align it.
• If you create a new partition using Windows 2000, or Windows 2003, you need to partition align it.

The Problem

Now for the good part; "Mr. Smarty pants blogger, why the heck do I need to do that?" That gentle reader is why we are here today!

There are a ton of great documents from both EMC and Microsoft about the topic of alignment, so I shall not elaborate on the topic, rather; I am going to go over a quick summation and give you the links.

A quick overview of the Symmetrix system;

• Symmetrix uses a Fixed Block Architecture (FBA) when it presents storage to Microsoft Windows. For today's discussion the important notes about FBA are:
  • DMX-2 and earlier systems use a 32K track
  • DMX-3 and later, including V-Max systems, use a 64K track

Windows versions prior to 2008 assume that the physical hard disk uses a 63K track when it presents storage to windows. Windows creates a 63K hidden sector that holds all the information needed to read that partition (including things like the Master Boot Record, and the Disk Signature).

This creates windows NTFS file structures that are not aligned to the underlying V-Max FBA tracks. Assuming a 4K standard allocation unit for the Windows partition, every 16^th 4K I/O will create an extra I/O. This can turn into a huge performance hit when Microsoft SQL server reads 64K extents. Each 64K I/O will cause the storage system to generate an extra I/O request.

The Storage administrator's belief that cache would fix this is not correct because the simple fact is; extra I/O is, well, extra I/O. Each extra I/O sent to the array consumes some amount of compute power, bandwidth, and memory to process the I/O. A properly sized Symmetrix array may not show any performance degradation, but why add extra unnecessary overhead? Taken at scale, many Windows systems, that are all unaligned, will create massive performance degradation.

A Windows Volume that is aligned to the 64K Symmetrix track will not generate the extra I/O. This, ladies and gentlemen, can result in a real cost savings. Jimmy completed a recent Blog entry directly tying business cost to misalignment at scale: http://blogs.msdn.com/b/jimmymay/archive/2009/05/08/disk-partition-alignment-sector-alignment-make-the-case-with-this-template.aspx.

The Solution

For those folks that are not interested in full enlightenment from these great papers listed at the end of the post, use the diskpart.exe cliff notes below to align a new partition:

Note: You should really read these papers, If you break something because you did not read up on the subject, it is not my fault. Really – read them. People worked really hard on them. I WORKED REALLY HARD ON ONE OF THEM!

WARNING: Aligning a Windows Partition is data DESTRUCTIVE. If you have a Windows 2000 or 2003 mis-aligned disk you need to migrate onto an aligned disk. Running Dikspart.exe or Diskpar.exe on a disk with existing data WILL DESTRY THE DATA!

Note: Diskpart.exe is available in Windows Server 2003 SP1 – prior to SP1 Diskpar.exe is used. See the EMC white papers at the end of the post for more information.

This code (taken from Jimmy's white paper) is a huge time saver:

Diskpart

list disk

select disk <DiskNumber>

create partition primary align=<Offset_in_KB>

assign letter=<DriveLetter>

format fs=ntfs unit=64K label="<label>" nowait

First use either the Power Path GUI, or Powermt.exe to map devices from the Symmetrix array to a Windows Disk number:

powermt.exe display dev=all

This command shows that Windows disk 3 is really Symmetrix Device 0057 from the 1111 V-Max array:

Pseudo name=harddisk3

Symmetrix ID=000111111111

Logical device ID=0057

Looking in Diskpart.exe we find, create, offset, and format device 0057 as F$ with an alignment offset of 1024 (that is 1024 blocks or, 64K) and an NTFS allocation unit of 64K (useful for great SQL Server data file performance – don't use this for SQL Server Analysis services volumes. For Analysis Services 4K is a better allocation unit):

C:\>diskpart

Microsoft DiskPart version 6.0.6001

Copyright (C) 1999-2007 Microsoft Corporation.

On computer: MYTESTBOX

DISKPART> list disk

Disk ### Status Size Free Dyn GPT

-------- ---------- ------- ------- --- ---

Disk 0 Online 186 GB 0 B

Disk 1 Online 100 GB 0 B

Disk 2 Online 120 GB 0 B

Disk 3 Online 150 GB 150 GB

DISKPART> select disk 3

Disk 3 is now the selected disk.

DISKPART> create partition primary align=1024

DiskPart succeeded in creating the specified partition.

DISKPART> assign letter=F

DiskPart successfully assigned the drive letter or mount point.

DISKPART> Format FS=ntfs unit=64K label="F$_Data1" nowait

If you need to run this on a lot of disks at the same time you can use the diskpart.exe scripting engine:

Diskpart.exe /s <script>

A working example of a single volume Diskpart.exe script file (this script assumes you are going to format a SQL server data file volume with a 64K NTFS allocation unit):

select disk 3

create partition primary align=1024

assign letter=F

format fs=ntfs unit=64K label=" F$_Data1" nowait

Resources

Please read the following white papers for more information:

• If you are an EMC customer you can search Powerlink.EMC.Com (requires a customer or partner logon to access content):
  • Part number: 300-004-075; Aligning GPT Basic and Dynamic Disks for Microsoft Windows 2003
  • White paper: Using diskpar and diskpart to Align Partitions on Windows Basic and Dynamic Disks
• Anyone can download the Windows server on Symmetrix white paper from (Pages 25 through Page 27 cover alignment):
  • http://www.emc.com/collateral/hardware/white-papers/h6665-symtrx-microsoft-windows-server-wp.pdf
• SQL CAT White Paper on Windows Disk Alignment for SQL Server (note the great technical reviewer <cough>).
  • http://sqlcat.com/whitepapers/archive/2009/05/11/disk-partition-alignment-best-practices-for-sql-server.aspx
  • http://msdn.microsoft.com/en-us/library/dd758814.aspx

Scalable Shared Database Part 5

Performance

This post is an extension of the performance section of: http://www.emc.com/collateral/hardware/white-papers/h8129-performance-scaling-sql-wp.pdf. Writing a white paper requires some brevity lest it turn into unreadable minutia. Thank goodness for blogs.

Scalable shared database presents a really interesting lesson in I/O performance. As performance demands increase the DB architect has numerous tools available in the performance toolkit. Humans like to file things into boxes, Database workloads are no exception. We like to separate database systems into one of two categories:

• OLTP
• Data warehouse.

An OLTP workload is understood to create a random I/O pattern. Data Warehouse workloads are understood to be sequential. Life has a way of crushing pre-conceived notions. Allowing a group of analysts to query an OLTP server can cause a normally random workload to become a combined random and sequential workload. This increase in workload, especially a different type of workload, can overwhelm the basic OLTP functions of the database.

The most basic way to ensure that the OLTP system can continue to function as it was originally designed is to split the reporting functions onto a separate dedicated reporting server. Over the years I often used Log shipping to keep this reporting server in sync with the master OLTP database server. Unfortunately this required us to take the Reporting server offline whenever updates were applied.

The analysts do not like their reports to go offline. Using scalable shared database, we can use the replication functions of the storage array to keep reporting servers in sync, and load balancing technology to obscure refresh operations from the reporting system users.

Note: When speaking of scalable shared database performance requirements remember that by allowing the use of a single transaction server, and many reporting servers, we are drastically increasing the performance load placed on the storage array.

As described in my earlier post, http://sqlvelocity.typepad.com/blog/2010/08/windows-performance-counters-for-windows.html, the servers and storage array form a closed performance system with measurable performance characteristics. Little's law gives us an algorithm to predict the performance of a closed system. A single database server can exert three different types of I/O patterns:

• Random
• Sequential
• Large Block Random (I have also called this excessive head seeking).

The majority of deployed storage uses spinning disks. This presents an interesting problem. Spinning disks react differently to each of the major performance regimens. Understanding this performance is the key to designing a storage system that will achieve given performance requirements.

Modeling Disk Performance

To simply calculate Random performance, divide the rotational speed of the drive by 60 seconds. A 10,000 RPM drive rotates 166 times every second. A 15,000 RPM drive rotates 250 times a second. Because the drive head takes some time to move between disk tracks then we should also account for the time this movement (or seek) takes. Luckily this can be tested and both the average and maximum seek time are provided by drive manufactures.

Modeling disk performance becomes viable with the proper algorithm. The following example will help to calculate the random read performance of a spinning hard disk.

• Start by looking up the average seek time of a given disk drive model.
• Calculate the Maximum rotational latency (MRS) by dividing 60,000 (milliseconds per minute) by the drives rotational speed (revolutions per minute); 60,000/RPM.
• To calculate the number of I/O's per second (IOPS) divide 1000 by the drives Average read seek time (ARST) plus the Maximum rotational latency divided by 2.
• 1000/(ARST+(MRS/2))

For example a 10K RPM drive with a 7 millisecond seek time will produce 100 I/O's Per second;

• 1000/(7 + (6/2)) = 100 IOPS

A 15K drive with an 4 ms seek time will produce 166 I/O's Per Second;

• 1000/(4 + (4/2)) = 166 IOPS

Finally a 7200 RPM 146GB SATA drive with an 11 ms seek time will produce 66 I/O's per second.

• 1000/(11 + (8.3/2) = 66 IOPS

To keep the math really easy for my models I estimate that a 10K drive will produce 100 I/O's per second and a 15K Drive will produce 150 IOPS. This is an extremely conservative calculation that assumes that only one I/O is being issued at a time. As more I/O requests are issued performance will increase at the cost of latency. A good rule of thumb is to issue between 1 and 4 I/O's per second to each physical disk.

To increase Random I/O then we need to add more physical disks. If one 10K drive will provide 100 I/O's per second and we need 8,000 I/O's per second then 80 physical disks will be needed. If 80 disks worth of space are not needed then the space is not going to be used. Space will need to be traded for performance.

Sequential disk performance is much faster than random drive performance. Instead of being restricted by the time it takes to move the head between sectors, sequential performance is limited by the system interconnects between the disk and the server.

If we take a look at the 7200RPM 146GB SATA drive listed in the Random performance section above it will only perform 66 random IOPS. When tested sequentially it will perform 1311 IOPS. This is almost 20 times more performance from the same device. When tested with 64KB reads this 7200RPM drive can only generate 4 Mb per second when accessed randomly. A sequential access pattern will generate 81MB per second.

The only way to increase random performance is to aggregate disks. When a single drive is able to perform faster than 10 randomly accessed disks it is possible to decrease the number of disks in a system.

Scalable shared database performance

Planning a scalable shared database deployment will result in two distinct I/O performance pools. The primary database system, called the factory, will generate the first I/O profile. The reporting servers will generate the second I/O performance profile. As described earlier, the factory server will create a random, a sequential, or a large block random performance profile. Estimating this workload is tricky as the exact workload is often a variance of the three workload types, and it often changes over time. I like to plan for the worst case; large block random I/O. Unfortunately, large block random operations perform far worse than a standard random workload. Take this chart as an example:

The experiment compared a standard random workload with many sequential I/O's. As more work is generated the random workload performance increases. Compare this with many sequential workloads forming a now large block random workload. As the workloads increase the large block random workload continues to decrease overall performance. Quickly the random performance will eclipse that of the large block random workloads.

As stated in the paper; http://www.emc.com/collateral/hardware/white-papers/h8129-performance-scaling-sql-wp.pdf the best solution to this performance profile is to use Enterprise Flash Drives (EFD's) utilizing SLC NAND Flash technology (Head over to Wikipedia for an excellent description of flash technology: http://en.wikipedia.org/wiki/Flash_memory). If you are not able to utilize flash technology then plan for the absolute worst case scenario.

Using the equation: 1000/(ARST+(MRS/2)) replace Average read seek time with Maximum read seek time (you can find Maximum read seek time from the drive manufacturer – it is also called a Full stroke access time).

If we look at a Seagate 15K 450GB FC drive it has an average seek time of 3.4ms and a full stroke seek time of 6.6ms.

1000/(4 + (3.4/2)) = 174 IOPS Random Read

1000/(4 + (6.6/2)) = 136 IOPS Maximum Seek Reads

As you can see the performance threshold for a 15K drive will be closer to that of a 10K drive. Plan appropriately when configuring storage for the shared read only volumes. Remember that more than one server will be sending I/O to the shared volume, so it needs to have performance that can handle the load generated by all of the servers combined.

That wraps up the series on Scalable Shared Data Base. Please Post questions.

-Michael