Understanding I/O Performance for SQL Server on Amazon EC2 / AWS.

Having run SQL Server on EC2, be advised EC2 is a very stable platform, however you are required to pay for the performance you need. I have done several repeated tests, and my experience is that Amazon gives you exactly what you pay for.

If you are having a performance problem, you need to look at your entire infrastructure and ensure you have not over provisioned one aspect of the system, while under-provisioning another.

The AWS blog tells the Dedicated network throughput for each instance size. I have chosen to expand on this to show you the best choices you have for EBS volume configuration for these instance types. If your only objective is maxing out I/O performance the table below should be sufficient. If however you need large amounts of space, you may choose to increase your drive count, and reduce your Provisioned IOPs per volume.

 

There are several key things, I have learned and wanted to state for you:

1) Your instance size determines Guaranteed Network Throughput

2) Everything that goes off your server traverses that single NIC ( Disk I/O, Network I/O, everything)

3) 1000 IOPS is the approximately 16 MB per second on AWS, the block size is 16K.

4)  If you are running SQL with Terabytes of Data you most likely need an instance size is rated as High for Network Performance and potentially one of the newer instances which promise 10Gigabit throughput.

5) Please do configure your server to be EBS optimized.

6) EBS volumes are currently limited to 1TB in size, so to create larger disks use Software RAID in your Operating System. (p.s. Amazon will deliver the IOPS of all volumes in the RAID Array — just remember you cannot exceed the Dedicated Throughput of your NIC).

7) If your backups are running terribly long and you can’t seem to figure out why… you most likely have an I/O bottleneck related to your server configuration. Amazon is NOT the problem.

Below is the table, hope it works for you..

 

Any questions / comments that may assist in improving this post are appreciated.

 

 

Instance Type Dedicated Throughput Dedicated Through Put (MB/second)  Max IOPs Through Put for EBS Purchasing   Most Optimized Purchase Size   Min Drive Size (GB) @ MAX IOPS   Min Drive Count to Get Maximum IOPS 
m1.large 500 Mbps 62.5                3,906.25                    4,000                             400                   1
 m1.xlarge 1000 Mbps 125                7,812.50                    8,000                             800                   2
 m2.2xlarge (new) 500 Mbps 62.5                3,906.25                    4,000                             400                   1
 m2.4xlarge 1000 Mbps 125                7,812.50                    8,000                             800                   2
 m3.xlarge (new) 500 Mbps 62.5                3,906.25                    4,000                             400                   1
 m3.2xlarge (new) 1000 Mbps 125                7,812.50                    8,000                             800                   2
 c1.xlarge (new) 1000 Mbps 125                7,812.50                    8,000                             800                   2
Instance Type Dedicated Throughput Dedicated Through Put (MB/second)  Max IOPs Through Put for EBS Purchasing   Most Optimized Purchase Size   Min Drive Size (GB) @ MAX IOPS   Min Drive Count to Get Maximum IOPS 
m1.large 500 Mbps 62.5                3,906.25                    4,000                             400                   1
 m1.xlarge 1000 Mbps 125                7,812.50                    8,000                             800                   2
 m2.2xlarge (new) 500 Mbps 62.5                3,906.25                    4,000                             400                   1
 m2.4xlarge 1000 Mbps 125                7,812.50                    8,000                             800                   2
 m3.xlarge (new) 500 Mbps 62.5                3,906.25                    4,000                             400                   1
 m3.2xlarge (new) 1000 Mbps 125                7,812.50                    8,000                             800                   2
 c1.xlarge (new) 1000 Mbps 125                7,812.50                    8,000                             800                   2

SSRS Identifying Reports to Tune – Top 10 Offenders

To discover reports which are candidates for tuning I use the following query to identify the reports which take the longest time to perform their data retrieval tasks.

The query I use is

DECLARE @ReportPath VARCHAR(MAX)
SET @ReportPath = '%'


SELECT TOP 10
c.[Path],c.[Name]
,[ReportID]
,COUNT(1) as UseCount
,SUM([TimeDataRetrieval]) as [TimeDataRetrieval]
,SUM([TimeProcessing]) as [TimeProcessing]
,SUM([TimeRendering]) as [TimeRendering]
,(SUM([TimeDataRetrieval])/(SUM([TimeDataRetrieval]+[TimeProcessing]+[TimeRendering])*1.00)) *100 as TimeDataRetrieval_TotalTime
,(SUM([TimeProcessing])   /(SUM([TimeDataRetrieval]+[TimeProcessing]+[TimeRendering])*1.00)) *100 as TimeProcessing_TotalTime
,(SUM([TimeRendering])    /(SUM([TimeDataRetrieval]+[TimeProcessing]+[TimeRendering])*1.00)) *100 as TimeRendering_TotalTime
,SUM([TimeDataRetrieval]+[TimeProcessing]+[TimeRendering]) as TotalTime
,SUM([ByteCount]) as [ByteCount]
,SUM([RowCount]) as [RowCount]
,MIN([TimeStart]) as FirstUsed
,MAX([TimeStart]) as LastUsed
,AVG([TimeDataRetrieval]*1.00)  as [Avg_TimeDataRetrieval]
,AVG([TimeProcessing]*1.00)        as [Avg_TimeProcessing]
,AVG([TimeRendering]*1.00)        as [Avg_TimeRendering]
,AVG([RowCount])                as [Avg_RowCount]
,AVG([TimeDataRetrieval]/(case when [RowCount] = 0 then 0.01 else [RowCount] end *1.00))  as [Avg_TimeDataRetrieval_Per_Row]  -- rudimentary hack to prevent divide by 0 and also geta number even when no data returned.
FROM
[dbo].[catalog] c WITH(NOLOCK)
LEFT OUTER JOIN [dbo].[ExecutionLog] el  WITH(NOLOCK) ON (c.ItemID = el.ReportID)
WHERE
1=1
-- AND c.[Path] LIKE @ReportPath
GROUP BY c.[Path],c.[Name],[ReportID]
HAVING COUNT(1) > 5
ORDER BY
SUM([TimeDataRetrieval]+[TimeProcessing]+[TimeRendering]) DESC,AVG([TimeDataRetrieval]) DESC

This gives me a list of the reports on the server in the order of longest time taken for data retrieval. Once I have this list, I then look at the reports and see what I can do to make the retrieval of each data set faster / more efficient by reviewing execution plans, and re-writing the queries / stored procedures.

SSIS 2005 Description: Connect to SSIS Service on machine failed: Library not registered. Could not load package because of error 0xC00160AA.

After Applying Service Pack 4 to SQL Server 2005, I was unable to connect to SSIS from Management Studio.
I was also unable to run packages from the command line using DTEXEC.

Upon further investigation it see

c:\windows\system32\regsvr32 “C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTS.dll”

c:\windows\system32\regsvr32 “C:\Program Files\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvrUtil.dll”

  • The SQL Server 2005 Integration Services hotfix package is installed. However, the SQL Server 2005 Tools hotfix package is not installed.
  • You install the SQL Server 2005 Tools hotfix package before you install the SQL Server 2005 Integration Services hotfix package.
  • You are running two instances of SQL Server 2005 on the computer. Additionally, the versions of both instances of SQL Server are earlier versions than Microsoft SQL Server 2005 Service Pack 2 (SP2).
  • The computer is running an instance of SQL Server 2005 with SP2. Additionally, you install a post-SP2 hotfix on this instance. Then you install a second instance of SQL Server 2005. On the second instance, you install SQL Server 2005 SP2.

The issue is described on Microsoft site in the following KB http://support.microsoft.com/kb/919224

Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005

Check if the RetainSameConnection Option is set to true or false for your Database Connection.
I was attempting to Update data by using a Data Flow Transformation
OLE DB Source –> OLE DB Command

When the RetainSameConnection was set to TRUE, I encountered errors.
When the RetainSameConnection was set to FALSE, the package functions as expected.
Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: “Microsoft SQL Server Native Client 10.0” Hresult: 0x80004005 Description: “Syntax error, permission violation, or other nonspecific error”.

http://connect.microsoft.com/SQLServer/feedback/details/682902/ssis-failing-pre-execution-when-retainsameconnection-is-set-to-true

 

 

ERROR [IM014] [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application

Did you create a DSN, but your application cannot access the DSN?

I created a DSN, but could not connect via SSIS. It seems that the DSN I created was 64-bit, while the application I was using (SSIS) could only connect to 32-bit DSNs.

After some research I have realized that there are two different ODBC Administrator runtimes on a 64-bit machine.

The first ODBC Manager is used to manage 64-bit data sources, while the second is used to manage 32-bit data sources.

If you are running a 32-bit Operating System, you will have only 32 bit drivers installed. If you are using a 64 bit machine, the default ODBC Manager will be for 64-bit data sources.

Use this ODBC Manager to Review 64-Bit Data Source Names

c:\windows\system32\odbcad32.exe

Use this ODBC Manager to Review 32-Bit Data Source Names

c:\windows\sysWOW64\odbcad32.exe

If you are trying to access a DSN in your application and receive the error “ERROR [IM014] [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application” check to see if you have a DSN configured for the architecture of your application ( 32-bit / 64-bit ).

I create my DSNs twice – once for 32 bit architecture and once for 64 bit architecture. My naming convention is as follows:

  • DSN_NAME_64 created using c:\windows\system32\odbcad32.exe
  • DSN_NAME_32 created using c:\windows\sysWOW64\odbcad32.exe
Good Luck

restore database and automatically drop database snapshot transact sql

To facilitate quick testing of changes on my development and test environments I leverage database snapshots greatly.  The benefit of snapshots is that I can revert data to its previous state without having testers wait 30 – 40 minutes to perform another database restore, only to repeat tests again. Also, I can do a join on the data in the snapshot database, to compare data.

Snapshots are a great help. However, I still need to refresh the source database every now and then from the production source to get up to date data. The following methodology allows me to drop all existing snapshots and restore database in one step, without having to do a lot of work manually checking for snapshots, then dropping each one individually, then performing my restore.

Once I have the script define for a database I put in svn so that my entire team has access to the script and can restore a database unattended.

e.g. http://svn/database/servername/database_restore_scripts/mydatabase.sql

Please note however that

  1. If you run this script you drop and lose all your snapshots on a database permanently.
  2. If you do not have backups of your test databases, make sure that all your users are ok with you refreshing the environment before doing your data refresh from production.

My process is to

  1. drop any existing database snapshots
  2. offline the database
  3. online the database
  4. restore the database
  5. fix users


-- DROP DATABASE SNAPSHOTS IF THEY EXIST
DECLARE @emailMessage NVARCHAR(4000)
DECLARE @database VARCHAR(100)
DECLARE @sql NVARCHAR(4000)
DECLARE cur_db CURSOR
FOR
select name
from sys.databases
where source_database_id IN (select database_id from sys.databases where name LIKE 'MyDatabase')
ORDER BY create_date DESC
FOR READ ONLY
OPEN cur_db
FETCH NEXT FROM cur_db INTO @database
WHILE (@@FETCH_STATUS = 0 )
BEGIN

SET @sql = 'ALTER DATABASE ['+@database+'] SET OFFLINE WITH ROLLBACK IMMEDIATE;'
EXEC sp_executesql @sql
SET @sql = 'ALTER DATABASE ['+@database+'] SET ONLINE;'
EXEC sp_executesql @sql
SET @sql = 'DROP DATABASE ['+@database+'];'
-- PRINT @sql
EXEC sp_executesql @sql
FETCH NEXT FROM cur_db INTO @database
END
CLOSE cur_db
DEALLOCATE cur_db
GO
-- OFFLINE THE MAIN DATABASE TO FORCE USERS OFF SYSTEM
ALTER DATABASE  [MyDatabase] SET OFFLINE WITH ROLLBACK IMMEDIATE;
GO
-- ONLINE THE DATABASE
ALTER DATABASE  [MyDatabase] SET ONLINE;
GO

-- BEGIN RESTORING DATABASE.
DECLARE @exitcode int, @sqlerrorcode int;
EXEC MASTER..sqlbackup N'-SQL "RESTORE DATABASE [MyDatabase]
FROM DISK=''\\BACKUP\MyDatabase.sqb''
WITH REPLACE
, DISCONNECT_EXISTING
, RECOVERY"', @exitcode OUTPUT, @sqlerrorcode OUTPUT;
IF (@exitcode <> 0) OR (@sqlerrorcode <> 0)
BEGIN
SET @emailMessage = 'FAILED DATABASE RESTORE | ' + @@SERVERNAME + ' | ' + SUSER_SNAME() + ' | '+ 'MyDatabase' + ' | ' + CONVERT(VARCHAR(20),getdate(),120)
EXEC msdb.dbo.sp_send_dbmail
@recipients='dbas@mydomain.com;',
@subject= @emailMessage,
@body= @emailMessage
END
ELSE
BEGIN
SET @emailMessage = 'SUCCESSFUL DATABASE RESTORE | ' + @@SERVERNAME + ' | ' + SUSER_SNAME() + ' | '+ 'MyDatabase' + ' | ' + CONVERT(VARCHAR(20),getdate(),120)
EXEC msdb.dbo.sp_send_dbmail
@recipients='dbas@mydomain.com;',
@subject= @emailMessage,
@body= @emailMessage
END
GO
-- RUN CODE TO RESOLVE MISMATCHED SIDS and USER ACCOUNTS WITH LOGINS
EXEC [MyDatabase].dbo.sp_fixusers
GO