Wednesday, April 02, 2014

Analysis Services 2012 and 2014 Performance Guide

Microsoft has released an Analysis Services 2012 and 2014 Performance Guide, 111 pages of multidimensional performance tuning goodness.

Is there a future for the multidimensional cube at Microsoft?  Sounds like it may stick around for awhile longer.... watch for improvements in SQL vNext.

Performance is important, and not just for improving business processes. Licensing rules for SQL 2014  will probably cause server administrators to try and keep their server utilization at full capacity.  Do more with less.

A similar whitepaper exists for PowerPivot and the tabular model in SQL 2012.

Tuesday, April 01, 2014

SQL 2014 Released, so what's the big deal?

SQL 2014 is released, in 2014 no less!  You can download the bits from Microsoft or get your free Azure trial (with a cap to ensure you don't get charged anything) to try it out.  Perhaps SQL Spade will help you to install it.  Or just browse the new features on Virtual Labs.

Microsoft has officially killed support for SQL 2005 compatibility mode, so before upgrading from prior versions be sure you've upgraded the compatibility mode of your databases.

The database engine has various improvements, fixes and tweaks to query plan optimization and partitioning.  Hopefully the query engine is tuned for the better.

There are some additional options for impersonation and separation of duties within the database.

Reporting Services, Analysis Services, and Integration Services, Data Quality Services, Master Data Management Services are mostly left in the dust.  Sigh....

Some of the cloud integration stories have improved dramatically.  Backup to URL.  Migrate to VM.  Hybrid blob storage.  AlwaysOn to Azure.  Azure's ExpressRoute functionality to move the public cloud to a private direct connection is now available.  This should improve adoption for those not wanting to transfer data over the public internet.  And provide companies with a secure form of offsite backup, DRP and replication to Azure.

Perhaps the biggest story with SQL 2014 is its In-Memory (formerly Hekaton) engine.  Or what I like to call DBCC PINTABLE+++.  This, along with its ability to use solid state storage for the SQL Buffer Pool, should provide for much faster query times when it's used correctly.  Think columnstore indexes but for an entire table, and updateable, and not a separately stored object.  Columnstore indexes in SQL 2014 can now be created as updateable, clustered objects that contain the data itself, in addition to the indexes.  In Enterprise Edition, anyway...

Note for SQL Server 2005:
This (DBCC Pintable) functionality was introduced for performance in SQL Server version 6.5. DBCC PINTABLE has highly unwanted side-effects. These include the potential to damage the buffer pool. DBCC PINTABLE is not required and has been removed to prevent additional problems. The syntax for this command still works but does not affect the server.


Ahhh, memories of data corruption, and arguments with other technology experts that there is no such feature post-SQL 2000 for pinning tables in memory.  "But I typed DBCC Pintable in and it came back with no errors!"   Whenever I want to do something that bypasses the internal workings of a database, I like to visit Ask Tom.   Even if you're not using Oracle, his site is a very fun an informative read.

Now back to SQL and the fine print.

Some of the licensing challenges to be aware of.  One challenge, where can you get some budget for 128 GB of memory for your SQL Server Standard boxes, and 512GB of flash memory for your buffer pool?  How about 4 terabytes on Datacentre with Enterprise edition, and some of those larger tables pinned in memory?

Fun times ahead with in-memory databases.