SQL Centre of Excellence

A new MP for SQL for SCOM 2007 has been released. This looks pretty much a no brainer to deploy. Key points that I like are:

  • The old MP doesn't collect all metrics unless “DMO” is installed. DMO does not ship with SQL 2008 so most SQL 2008 boxes are not properly monitored without additional work.
  • Support for SQL 2008 R2 et al.
  • Improved Views for Database Free Space and SQL Performance
  • Free space monitoring improved.
  • Better support for SQL job discovery
  • Better monitoring of SQL events and Key Performance Metrics

There are lots of other misc improvements and the download is here

http://www.microsoft.com/downloads/details.aspx?FamilyId=8C0F970E-C653-4C15-9E51-6A6CADFCA363&displaylang=en&displaylang=en

From what I see there are no decent MDX training course in Ireland as being a smaller country we don’t have any world class “gurus” in the MDX training business. If we do, drop me an email  ;-)

I’ve helped organize a once off MDX training course with Chris Webb on 16th/17th of September. If you are working with Analysis Services design, or report writing against cubes this course is a must. Chris is one of the most prolific MDX guys around and will be sure to answer any tricky questions and help you skill up on one of the hardest,  rarest and sometimes useful skillets in Business Intelligence.

The course is over two days and hands on lab based so you will get to write plenty of MDX queries and get to grips with multi dimensional concepts and functions.

This really is an opportunity to either solidify your MDX expertise or a chance to become the “goto” person on BI projects. Its shocking to me how many BI projects are implemented with teams who do not have any MDX skills. Would you implement an OLTP system without any TSQL skills!

The link for more information and registration is below:

http://www.prodata.ie/events/MDX2010.aspx

SQL IO Characteristics

by Bob Duffy 8. August 2010 05:20

This is fairly well documented in various books and white papers, but can be tricky to find out on the Internet so I am summarising it here in this blog.

The key questions is “what are SQL Server’s I/O Characteristics”. This helps answer questions like why is a 1MB block size bad; why don’t we often care about random I/O for log files; what extra benefit for I/O does enterprise edition have; and why do we mainly test random I/O for data files in OLTP systems

The table below shows general I/O characteristics for the storage engine that will be important to most OLTP workloads. If you “really” know your workload you can optimise your storage. For example:

  • if you know that most of your workload is index seeks, you may benefit from a 8k block size on the SAN - good luck convincing your SAN team of this ;-)
  • If you are doing mostly very large table scans and you have enterprise edition, then it is possible that a 1MB block size may be most performant.
  • If you are not running enterprise edition, then a 1MB block size will never be optimal as the maximum IO size SQL Server will issue is 256k, so a minimum of three quarters of your I/O will be wasted.

Operation

Random / Sequential

Read / Write

Size Range

OLTP – Log

Sequential

Write

Sector Aligned Up to 60K

OLTP – Log

Sequential

Read

Sector Aligned Up to 120K

OLTP – Data (Index Seeks)

Random

Read

8K

OLTP - Lazy Writer (scatter gather)

Random

Write

Any multiple of 8K up to 256K

OLTP – Checkpoint (scatter gather)

Random

Write

Any multiple of 8K up to 256K

Read Ahead (DSS, Index/Table Scans)

Sequential

Read

Any multiple of 8KB up to 256K (1024 Enterprise Edition)

Bulk Insert

Sequential

Write

Any multiple of 8K up to 128K

 

This secondary table is useful as trivia, but we rarely optimise storage for these operations. One  observation is that backup and restore can issue up to 4MB IO sizes, and the importance of instant file initialisation in helping create database performance (or file growth).

Operation

Random / Sequential

Read / Write

Size Range

CREATE DATABASE (or file growth)

Sequential

Write

512KB (SQL 2000) , Up to 4MB (SQL2005+)

(Only log file is initialized in SQL Server 2005+ if instant file initialisation is enabled)

BACKUP

Sequential

Read/Write

Multiple of 64K (up to 4MB)

RESTORE

Sequential

Read/Write

Multiple of 64K (up to 4MB)

DBCC – CHECKDB

Sequential

Read

8K – 64K

ALTER INDEX REBUILD - replaces DBREINDEX
(Read Phase)

Sequential

Read

Any multiple of 8KB up to 256K

ALTER INDEX REBUILD - replaces DBREINDEX
(Write Phase)

Sequential

Write

Any multiple of 8K up to 128K

DBCC – SHOWCONTIG (deprecated, use sys.dm_db_index_physical_stats)

Sequential

Read

8K – 64K

 

Further References:

Hitachi – Tuning SL Server 2005 Performance http://www.servicesorientedstorage.com/assets/pdf/tuning-microsoft-sql-server-2005-performance-wp.pdf

High Availability Best Practices: I/O Subsystem

http://download.microsoft.com/download/f/e/3/fe32de98-8be3-4212-9cf9-be75fc699df8/SQL_Server_Always_On_Tec_IO.ppt

SQL Server 2000 I/O Basics (Old but still mostly relevant today)

http://technet.microsoft.com/en-us/library/cc966500.aspx

Page List

Page List