SQL 2014 new feature (Updatable columnstore)

One of the new features introduced on SQL server 2012 was a memory optimized columnstore index. This feature not only reduces the amount of storage but also the speed of the query. During my test, I found that the speed of a query performance before and after columnstore index was in magnitude of 15 times faster. Wow, that was very impressive. As being new feature it have its own drawbacks. You are not able to update the data, hum. The workaround was very cumbersome and not suitable for some of data warehouse solutions. If you want to update data you need to drop the columnstore index update the data and recreate it.

If you have already upgraded to 2012 to make use of the columnstore index then you may have to upgrade to the new SQL server 2014. SQL 2014 doesn’t require the workaround of updating data. The new release enhanced the columnstore to be a pure columnar store. Bang on!!

Data warehouse is much happier place to be with SQL 2014. Not only you will be able to save space but also increase query performance.

When is SQL server installed

I have been asked may times how to find when a SQL server is installed. Mostly I told them to find the installation log. But, what I didn't realized is that you can find out by querying sys.syslogin database.To my surprise, quiet an easy statement. You can use

SELECT  createdate as Sql_Server_Install_Date, *
FROM    sys.syslogins
where   sid = 0x010100000000000512000000 

or

SELECT  createdate as Sql_Server_Install_Date
FROM    sys.syslogins
where  loginname = 'NT AUTHORITY\SYSTEM'

One of the above will do  as long as default language is English

Flushing the cache for one database

DECLARE @DBID int
 SET @DBID = ( SELECT dbid FROM master.dbo.sysdatabases WHERE name = 'db')
--Flush procedure on the db
DBCC FLUSHPROCINDB (@intDBID)

Execution plan puzzle

I recently come accross a query that used to run fine on production server but suddenly
Execution plans can be affected by different statistics, parallelism due to number of processors, the amount of available RAM in the server, different service packs, different server configuration settings, and the current load on the server. I imagine one or more of these are causing what you are seeing.