OLTP performance in Denali - Less is more

  • Article

I get the occasional criticism about SQL Server that all then new stuff is related to BI, and there isn’t anything for the traditional or grumpy DBA (aka Colin Leversutch Roberts).  Listening to Tom Casey  (corporate vice president within the Microsoft Business Platform Division)of  at the SQL Social last week, the only way to make SQL Server transactions perform faster on a given bit of kit is to take stuff away.  A good example of this in Denali is the new Always On capability which combines Windows Server clustering technologies and database mirroring to give:

  • Multiple copies of the mirrored* database which might be in remote offices, in Denali parlance these are referred to as secondaries.
  • Availability groups  mean that a group of databases can be mirrored as a unit.
  • The secondaries are readable.

Making these secondaries readable is the key to transaction performance:

  • Any of the secondaries can be used as a source for reporting as it is nearly up to date freeing up the principal to do more transactions.
  • The same applies to backup except that you can only make full backups of a secondary

However there is also one add-in in Denali that might speed up transactions; column based indexes.  As the name suggests these store columns of data in pages rather than in traditional databases where rows get stored in pages.  This offers massive performance benefits and these indexes can be significantly compressed as the values in any column or often similar.  So if a transaction needs to a lookup then these indexes could help, of course you’ll need to testy that and the use of these indexes will just show up as part of the query execution plan like we have in earlier version of SQL Server.     

BTW If you are in Manchester (15th June)  or Leeds (16th June)  I’ll be at the SQL Server User Group meetings  to go into some of this in more detail.

* In mirroring the live database i.e. the one that transactions are first is called principal, the offline copy is called the mirror and in all earlier version up to Denali there can be only be one mirror