Transaction Log File Size and VLF

This article by Kimberly Tripp is very interesting. Simply put, she says you want the initial size of your transaction logs set to 8 GB, with auto growth set to 8 GB. This should help keep your Virtual Lof File (VLF) sizes below 512 MB, improve performance, and make maintenance during backups much faster.

VLF Image

The article, in part, reads:

First, here’s how the log is divided into VLFs. Each “chunk” that is added, is divided into VLFs at the time the log growth (regardless of whether this is a manual or auto-grow addition) and it’s all dependant on the size that is ADDED not the size of the log itself. So, take a 10MB log that is extended to 50MB, here a 40MB chunk is being added. This 40MB chunk will be divided into 4 VLFs. Here’s the breakdown for chunksize:

chunks less than 64MB and up to 64MB = 4 VLFs
chunks larger than 64MB and up to 1GB = 8 VLFs
chunks larger than 1GB = 16 VLFs

And, what this translates into is that a transaction log of 64GB would have 16 VLFs of 4GB each. As a result, the transaction log could only clear at more than 4GB of log information AND that only when it’s completely inactive. To have a more ideally sized VLF, consider creating the transaction log in 8GB chunks (8GB, then extend it to 16GB, then extend it to 24GB and so forth) so that the number (and size) of your VLFs is more reasonable (in this case 512MB).

You should visit Kimberly’s blog entry for more information. You can also get more information about Virtual Log Files here.

Advertisements

The Year in Hadoop – 2013

Interesting year-in-review video about Hadoop from Jeremiah Peschka at Brent Ozar.

Hadoop Video

PowerShell Commands in SQL Server

You can use PowerShell to issue commands in SQL Server, including the ability to select a database, query tables, show users, etc. This is a very helpful feature now that you can install SQL Server on the Windows Core, without a GUI interface, starting with SQL Server 2012.

As a basic entry into this interface, I’m going to show you how to get into the PowerShell interface from the Management Console, select a database, then query a table to see the basic results. You can start Windows PowerShell sessions from Object Explorer in SQL Server Management Studio. Management Studio launches Windows PowerShell, loads the “sqlps” module, and sets the path context to the associated node in the Object Explorer tree.

When you specify running PowerShell for an object in Object Explorer, SQL Server Management Studio starts a Windows PowerShell session in which the SQL Server PowerShell snap-ins have been loaded and registered. The path for the session is preset to the location of the object you right clicked in Object Explorer. For example, if you right-click on a sample database named “SampleDatabase” database object in Object Explorer and select Start PowerShell, the Windows PowerShell path is set to the following:

SQLSERVER:\SQL\MyComputer\MyInstance\Databases\SampleDatabase>

When opened from Management Studio, PowerShell does not run with Administrator privileges which may prevent some activities such as calls to WMI.

You can now use basic navigation commands to select the proper object. You can enter cd commands to change the current directory to navigate the data structure in SQL Server much like you would for the Windows directory structure. Issuing a “cd ..” command and then the “dir” command from the command line shown above will list all the databases on the current instance.

SQLSERVER:\SQL\MyComputer\MyInstance\Databases\SampleDatabase> cd ..
SQLSERVER:\SQL\MyComputer\MyInstance\Databases\> dir

You can then issue the “cd SampleDatabase” to get back tot the original starting location. This works much like the existing Windows command line we are used to using from DOS.

SQLSERVER:\SQL\MyComputer\MyInstance\Databases\> cd SampleDatabase
SQLSERVER:\SQL\MyComputer\MyInstance\Databases\SampleDatabase>

You can then change directory “cd tables” to allow you access to the list of available tables using the “dir” command. You can now run a query against that table.

SQLSERVER:\SQL\MyComputer\MyInstance\Databases\SampleDatabase> Invoke-Sqlcmd -Query "Select * FROM SampleTable">

The command line will show the returned records much like the query analyzer would in the normal SQL Server GUI.

This has been an effort to show you a very basic example of how to use the PowerShell to connect to a user database, navigate to the proper table, and run a basic query. If you want more information and examples, Microsoft has some detailed information on the subject.

Delete Data from All Tables

For any number of reasons you might need to delete all rows of data from all tables in a SQL Server database. If you have many tables in a database this can become quite an effort if performed manually. Additionally, triggers and constraints which enforce referential integrity must be considered if DELETE or TRUNCATE statements are to succeed. This solution provides a stored procedure (works with SQL 2000/2005/2008) which will delete All data from All tables in the current database.

Please use with caution.

This can be very useful when you want to copy a database into a test or development environment but you don’t want to copy the data because of compliance or size constraints.

 
--EXEC DeleteAllData
CREATE PROCEDURE DeleteAllData
AS
BEGIN
DECLARE @SQL nvarchar(2000), @CurrentTable sysname, @CurrentSchema sysname

--Grab the server version for any statements which need to be modified based upon the server version
DECLARE @ServerVersion int
SET @ServerVersion = (SELECT CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS varchar(50)), CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS varchar(50))) - 1) AS int))

--This solution entails a cursor.  Alternatively, it could be done with
--the undocumented stored procedure sp_msforeachtable, or with loop logic.
	DECLARE TableCursor SCROLL CURSOR FOR
	SELECT QUOTENAME(TABLE_SCHEMA) AS schemaname, QUOTENAME(TABLE_NAME) AS name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @CurrentSchema, @CurrentTable

--Disable all triggers first
WHILE @@FETCH_STATUS = 0
BEGIN
	--Create a TSQL string to disable triggers on the current table
	SET @SQL = 
	(SELECT	'ALTER TABLE '+ @CurrentSchema + '.' + @CurrentTable + ' DISABLE TRIGGER ALL')
	EXECUTE sp_ExecuteSQL @SQL;

	--Print a success or failure message, depending upon whether or not an error was raised.
	IF @@ERROR = 0
		BEGIN
			PRINT 'Triggers successfully disabled on ' + @CurrentSchema + '.' + @CurrentTable
		END
	ELSE
		BEGIN
			PRINT 'An error has occured while disabling triggers on ' + @CurrentSchema + '.' + @CurrentTable
		END

	--Create a TSQL string to disable constraints on the current table
	SET @SQL = 
	(SELECT	'ALTER TABLE '+ @CurrentSchema + '.' + @CurrentTable + ' NOCHECK CONSTRAINT ALL')
	EXECUTE sp_ExecuteSQL @SQL;

	--Print a success or failure message, depending upon whether or not an error was raised.
	IF @@ERROR = 0
		BEGIN
			PRINT 'Constraints successfully disabled on ' + @CurrentSchema + '.' + @CurrentTable
		END
	ELSE
		BEGIN
			PRINT 'An error has occured while disabling constraints on ' + @CurrentSchema + '.' + @CurrentTable
		END

	--Fetch the next table from the cursor
	FETCH NEXT FROM TableCursor INTO @CurrentSchema, @CurrentTable
END

--Move back to the first table in the cursor
FETCH FIRST FROM TableCursor INTO @CurrentSchema, @CurrentTable

WHILE @@FETCH_STATUS = 0
BEGIN

	IF @ServerVersion >= 9  --IF we're on SQL 2005 or greater, we can use Try/Catch.
		BEGIN
		SET @SQL = (SELECT	'BEGIN TRY 
		TRUNCATE TABLE ' + @CurrentSchema + '.' + @CurrentTable + ' 
		PRINT ''Data successfully truncated from ' + @CurrentSchema + '.' + @CurrentTable + ''' 
		END TRY 
		BEGIN CATCH 
		DELETE FROM ' + @CurrentSchema + '.' + @CurrentTable + '
		IF EXISTS(SELECT ''A'' FROM information_schema.columns 
			WHERE COLUMNPROPERTY(OBJECT_ID(''' + @CurrentSchema + '.' + @CurrentTable + '''), 
			column_name,''IsIdentity'')=1 AND QUOTENAME(TABLE_SCHEMA) = ''' + @CurrentSchema + '''
			AND QUOTENAME(TABLE_NAME) = ''' + @CurrentTable + ''')
			BEGIN
				DBCC CHECKIDENT(''' + @CurrentSchema + '.' + @CurrentTable + ''', RESEED, 0) 
			END
		PRINT ''Data successfully deleted from ' + @CurrentSchema + '.' + @CurrentTable + ''' 
		END CATCH')
		END
	ELSE  --We're on SQL 2000, so we need to check for foreign key existence first.
		BEGIN
		SET @SQL = (SELECT	'IF OBJECTPROPERTY(OBJECT_ID(''' + @CurrentSchema + '.' + @CurrentTable + '''), ''TableHasForeignRef'') <> 1
			BEGIN
			TRUNCATE TABLE ' + @CurrentSchema + '.' + @CurrentTable + ' 
			PRINT ''Data successfully truncated from ' + @CurrentSchema + '.' + @CurrentTable + ''' 
			END
		ELSE
			BEGIN
			DELETE FROM ' + @CurrentSchema + '.' + @CurrentTable + '
			IF EXISTS(SELECT ''A'' FROM information_schema.columns 
				WHERE COLUMNPROPERTY(OBJECT_ID(''' + @CurrentSchema + '.' + @CurrentTable + '''), 
				column_name,''IsIdentity'')=1 AND QUOTENAME(TABLE_SCHEMA) = ''' + @CurrentSchema + '''
				AND QUOTENAME(TABLE_NAME) = ''' + @CurrentTable + ''')
				BEGIN
					DBCC CHECKIDENT(''' + @CurrentSchema + '.' + @CurrentTable + ''', RESEED, 0) 
				END
			PRINT ''Data successfully deleted from ' + @CurrentSchema + '.' + @CurrentTable + ''' 
			END')
		END

	EXECUTE sp_ExecuteSQL @SQL;

	--Fetch the next table from the cursor		
	FETCH NEXT FROM TableCursor INTO @CurrentSchema, @CurrentTable
END

--Move back to the first table in the cursor
FETCH FIRST FROM TableCursor INTO @CurrentSchema, @CurrentTable

WHILE @@FETCH_STATUS = 0
BEGIN
	--Reenable triggers
	SET @SQL = (SELECT	'ALTER TABLE ' + @CurrentSchema + '.' + @CurrentTable + ' ENABLE TRIGGER ALL')
	EXECUTE sp_ExecuteSQL @SQL;

	--Print a success or failure message, depending upon whether or not an error was raised.
	IF @@ERROR = 0
		BEGIN
			PRINT 'Triggers successfully reenabled on ' + @CurrentSchema + '.' + @CurrentTable
		END
	ELSE
		BEGIN
			PRINT 'An error has occured while reenabling triggers on ' + @CurrentSchema + '.' + @CurrentTable
		END

	--Now reenable constraints
	SET @SQL = (SELECT	'ALTER TABLE ' + @CurrentSchema + '.' + @CurrentTable + ' CHECK CONSTRAINT ALL')
	EXECUTE sp_ExecuteSQL @SQL;

	--Print a success or failure message, depending upon whether or not an error was raised.
	IF @@ERROR = 0
		BEGIN
			PRINT 'Constraints successfully disabled on ' + @CurrentTable
		END
	ELSE
		BEGIN
			PRINT 'An error has occured while disabling constraints on ' + @CurrentTable
		END

	--Fetch the next table from the cursor		
	FETCH NEXT FROM TableCursor INTO @CurrentSchema, @CurrentTable
END

--CLOSE and DEALLOCATE our cursor
CLOSE TableCursor
DEALLOCATE TableCursor

END

Top 10 New Features in SQL Server 2012

Microsoft introduced SQL Server 2012 to a while back, but many users and administrators haven’t embraced this new version yet. Some can’t because of constraints imposed by corporate requirements, and some just haven’t had time to look at this recent version. This article will list 10 new features you might want to consider as this upgrade to this highly anticipated version of SQL Server is already a couple of years old.

1. Windows Server Core Support Windows Server Core is the GUI-less version of Windows that uses DOS and PowerShell for user interaction. It has a much smaller footprint (up to 50% less memory and disk space utilization), requires fewer patches and hot fixes, and is more secure (because it exposes fewer features to potential hackers) than the full Windows install we have used in the past. Starting with SQL Server 2012, it is supported for SQL Server installs. Just remember, to support this type of install it will take some education on your part to support the lack of GUI interface using just PowerShell and batch commands along with remote administration.

2. Columnstore Indexes — This a new feature that is completely unique to SQL Server. They are special type of read-only index designed to be use with Data Warehouse queries. Basically, data is grouped and stored in a flat, compressed column index, greatly reducing I/O and memory utilization on large queries.

3. User-Defined Server Roles — Database Administrators have always had the ability to create custom database roles, but never server wide. For example, if the Database Administrator wanted to give a development team member read/write access to every database on a shared server, traditionally the only ways to do it were either manually, or using undocumented procedures. Neither of which were good solutions. Now, the Database Administrator can create a role, which has read/write access on every DB on the server, or any other custom server wide role. This should greatly simplify administration.

4. Enhanced Auditing Features — Audit is now available in all editions of SQL Server. Additionally, users can define custom audit specifications to write custom events into the audit log. New filtering features give greater flexibility in choosing which events to write to the log.

5. BI Semantic Model — The BI Semantic Model is replacing the Analysis Services Unified Dimensional Model (or cubes most people referred to them). It’s a hybrid model that allows one data model will support all BI experiences in SQL Server. Additionally, this will allow for some really interesting text infographics

6. Sequence Objects — For those folks who have worked with Oracle, this has been a long requested feature. A sequence is just an object that is a counter — a good example of it’s use would be to increment values in a table, based a trigger. SQL has always had similar functionality with identity columns, but now this is a discrete object.

7. Enhanced PowerShell Support — Windows and SQL Server administrators should definitely start brushing up on their PowerShell scripting skills. Microsoft is driving a lot of development effort into including PowerShell in all of their server-based products. SQL Server 2008 gave Database Administrators some exposure to it, but there are many more in cmdlets in SQL Server 2012.

8. Distributed Replay — Once again this is answer to a feature that Oracle released (Real Application Testing). However the real value proposition of SQL Server is this feature is available in the Enterprise edition and in Oracle it is a (very expensive) cost option to Enterprise Edition. With SQL Server when you buy your licenses for Enterprise Edition, you get everything. Distributed replay allows you to capture a workload on a production server, and replay it on another machine. This way changes in underlying schemas, support packs, or hardware changes can be tested under production conditions. This is very helpful for development and testing.

9. PowerView — You may have heard of this under the name “Project Crescent” it is a fairly powerful self-service BI toolkit that allows users to create mash ups of BI reports from all over the Enterprise, from Excel.

10. Big Data Support — This may be the biggest new feature, introduced at the PASS (Professional Association for SQL Server) conference in 2011, Microsoft announced a partnership with Hadoop provider Cloudera. One part of this involves MS releasing a ODBC driver for SQL Server that will run on a Linux platform. Additionally, Microsoft is building connectors for Hadoop, which is an extremely popular NoSQL platform. With this announcement, Microsoft has made a clear move into this very rapidly growing space. If you haven’t done so already, you should take the time to understand Hadoop.

SQL Server 2012 is a big step forward for Microsoft. They are positioning itself to be a leader in availability and in the growing area of big data. As a database professional, I look forward to using SQL Server 2012 to develop new database solutions, and so should you.

Troubleshooting Slow SQL Servers

When your users report a performance problem when using SQL Server, there are some basic troubleshooting techniques you can use to determine the cause of performance issues in a database instance. You can look at several causes to determine the source of the issue. Most performance issues are limited to some basic causes:

  • Wrong Technology
  • Missing Indexes
  • Index Design
  • Database Schema
  • Storage Subsystem
  • Buffer Pool Size
  • Slow network

Wrong Technology

Some database performance problems can be traced back to simply using the wrong technology to get the job done. Usually this revolves around running large reports against production OLTP databases or databases that have an OLTP schema. Many of these reports end up doing large, complex aggregations. Although SQL Server can do the aggregations, it isn’t the best technology to use for them. For large, complex aggregations, SQL Server Analysis Services (SSAS) is the appropriate tool because SSAS pre-aggregates the data when the data is loaded. As a result, when a query asks for aggregated data, it has already been aggregated and the results can simply be returned instead of being processed. If you store the data in the same format it will be used on your reports, the reports will run much faster.

Missing Indexes

Disk performance problems and an increased number of deadlocks are some of the potential indicators that you might have indexes missing. One way to easily determine if there are missing indexes is to use two dynamic management views (DMVs): sys.dm_db_missing_index_details and sys.dm_db_missing_index_columns. Using these two DMVs you can easily identify many of the indexes that need to be created to improve performance.

However, the system doesn’t understand that indexes already in existence might only need another column added to their included columns list. Your SQL Server instance might recommend that entirely new index be created when all that’s needed is a new column in the existing index. Because of this, you shouldn’t just take the information from the DMVs and create all the specified indexes. The suggested indexes should be compared against the indexes that already exist in the database to ensure that duplicate indexes aren’t being created. Duplicate indexes can lead to additional writes, updates, and deletes, causing performance problems as well as using too much precious disk drive space.

A better approach is to use a query like that in Listing 1 to identify the indexes that are missing from the database on which the query is being run. This query uses the db_id() system function to restrict the output to that from the current database. So, to run it in your database, simply specify that database in the Available Databases drop-down box in SQL Server Management Studio (SSMS) or add a USE statement before the query.

 Listing 1: Query to Identify Missing Indexes
SELECT TOP 25
dm_mid.database_id AS DatabaseID,
dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
dm_migs.last_user_seek AS Last_User_Seek,
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') +
CASE
WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN '_'
ELSE ''
END
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
+ ']'
+ ' ON ' + dm_mid.statement
+ ' (' + ISNULL (dm_mid.equality_columns,'')
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ',' ELSE
'' END
+ ISNULL (dm_mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
ORDER BY Avg_Estimated_Impact DESC
GO

When reviewing the output from the query in Listing 1, look for queries that have a high value in the second column. The higher the number, the more savings that will be seen by adding an index.

Index Design

Poor index designs for database tables will usually manifest as slow running queries and queries that have a high execution plan cost. The easiest way to identity these problems is to query the procedure cache because queries’ execution plans remain in the cache as long as there is enough memory to store them. Listing 2 shows a sample query that looks for queries with a high total cost. When reviewing the output from Listing 2, look at the StatementSubTreeCost column. The higher the number, the more expensive the execution plan.

Listing 2: Query to Identify Poorly Designed Indexes
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
WITH XMLNAMESPACES
(DEFAULT
  'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
   query_plan AS CompleteQueryPlan,
   n.value('(@StatementSubTreeCost)[1]', 'VARCHAR(128)')
    AS StatementSubTreeCost,
   dm_ecp.usecounts
FROM sys.dm_exec_cached_plans AS dm_ecp
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS dm_eqp
CROSS APPLY query_plan.nodes
  ('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple')
  AS qp(n)
ORDER BY n.value('(@StatementSubTreeCost)[1]',
  'VARCHAR(128)') DESC
GO

In SQL Server 2012, you can set up an Extended Events session to capture the execution plans for queries. However, it isn’t recommended that Extended Events be used to capture execution plans in high-load systems as doing so could overload the system by 21% or more by capturing the showplan_xml column.

Database Schema

One of the biggest killers of database performance is a poorly designed database schema. Evidence of a poorly designed database schema can be seen when lots of I/O requests occur while queries are being run. The easiest way to look for this evidence is to use SQL Server Profiler to capture the queries that have the highest I/O load, then run those queries in SSMS with SET STATISTICS IO enabled. Between the table I/O outputs and the execution plans, you’ll get a good indication of which table is having a performance problem.

Storage Subsystem

The most obvious way to determine whether a SQL Server database instance is having storage performance problems is to look in the SQL Server ERRORLOG file. A message about slow I/O in the ERRORLOG file is a good indicator that there’s something wrong with the storage I/O subsystem.

However, not seeing any errors about slow I/O in the ERRORLOG file doesn’t rule out that your storage I/O subsystem isn’t the cause of a performance bottleneck. Not seeing these errors just means that your storage isn’t ridiculously overtasked and not keeping up.

Another way to look for I/O problems is to query the sys.dm_os_wait_stats DMV with code such as

SELECT *

FROM sys.dm_os_wait_stats

ORDER BY wait_time_ms desc

If the first few rows returned from this query include some I/O wait types, then a slow I/O subsystem might be the problem.

You might have noticed that I said that these wait types might indicate a slow I/O problem and not that they do indicate a slow I/O problem. That’s because a poorly indexed database server can also report I/O problems when the I/O subsystem is perfectly sized for the application, but you’re asking the I/O subsystem to do a lot more work than it should be doing. In other words, the I/O problem is actually the result of an index problem. To resolve it, you need to add indexes to the system. Because of this, whenever I look at a new system in which the client wants to upgrade the hard drives, I always start with the indexes because that might very well fix the problem.

After the problem has been identified as actually being a storage I/O subsystem problem, there are a few directions that you can go to fix it. For large reporting databases, table partitioning might help. There are two different techniques that you can consider: partitioning for partition elimination and partitioning for CAPEX savings. No matter which approach is taken, a solid understanding of the data and how it’s used is required.

Buffer Pool

The buffer pool is where SQL Server stores data pages that it has read or written to recently. A small buffer pool will generally cause performance problems, because it will put too much pressure on the disk subsystem. There are a couple of different performance monitor counters that you can look at to see if the buffer pool size is healthy. Both counters are located within the SQLServer:Buffer Manager performance counter object.

The first counter to look at is Database Pages. This counter tells you how much memory is currently allocated to the buffer pool. This counter is in pages, so to get it into gigabytes, multiply the value by 8, then divide by 1,048,576.

The second counter to look at is Page Life Expectancy. This counter tells you how long, in seconds, SQL Server expects that you’ll be keeping the data in memory before releasing the page so that additional data can be loaded into memory. There is no magic number that says that the buffer pool is healthy.

Fixing a small buffer pool requires adding more memory to the server and configuring SQL Server to use the additional memory. The only other way to increase the amount of data that SQL Server can load from one database into the buffer pool is to move databases from one server to another so that the remaining databases have access to a higher percentage of the database engine’s buffer pool.

Slow Network

Another potential server bottleneck is the network. The easiest way to identify this problem is to look at the wait statistics on the system. A wait type of ASYNC_NETWORK_IO indicates that the network between SQL Server and the client computers is having a performance problem. If the client computers are located at a remote site, this could be the cause of the ASYNC_NETWORK_IO wait types.

If all the client computers are local, ASYNC_NETWORK_IO wait types might be the result of an older version of SQL Server or an older network switch being used. A slow or misconfigured network port could also be the cause of ASYNC_NETWORK_IO wait types. For example, having a network port configured for 10/half duplex could cause performance problems. In addition, there could be performance problems if a network port is configured to auto-detect the network speed, but the network switch and server don’t successfully negotiate the fastest possible network speed.

Fixing a slow network isn’t something that a DBA will be able to handle alone. It will require a coordinated effort between the network administrator, the network provider if there’s a WAN involved, and the DBA.

New Feature in VMware 5.5 for Virtual Clustered SQL Servers

While there are plenty of people who have SQL Server clusters running under VMware, one of the issues has been the MPIO configuration. The reason for this problem is that VMware requires that you use a fixed path to route the data between the physical server and the storage array when you are using Raw Device Mappings or RDMs.

Because you are using a fixed path if the path gets slow because a port or switch on the SAN fabric gets busy, there’s nothing that the server can do about it. It isn’t able to switch to another path to send that data down until the path that it is currently using fails. The other big problem that people see with having their guest servers setup on a fixed path is that the HBA in the server can fill to capacity and there’s nothing that can be done about it.

Starting with VMware 5.5 this finally changes. With VMware 5.5 you now have the ability to use a Round-Robin path system, which is basically what you would be using with a physical SQL Server cluster. Probably the biggest benefit that you will see is the ability for your virtual SQL Server to be able to spread the workload across multiple HBAs within the VMware host. I’m sure you will be looking forward to this feature making it into production if you want to better support clusters.

You can read the pdf from VMWare on the subject here. General information on enhancements to VMWare 5.5 is here.