Clustered Columnstore Index Hotfix Released

A little under a week ago on the 25th of September a hotfix (2999809) was released that pertained to poor query performance when a query contains table joins in SQL Server 2014.

The poor performance may show up if all the following apply:

  • You query contains an inner join
  • both tables have a Clustered Columnstore Index
  • There is a hash join executing in batch mode
  • the plan decides that a bitmap filter will be selective enough to be useful
  • the bitmap filter used is a complex bitmap filter (filter is made up of multiple columns and data types, whilst simple is a single column filter)

Continue reading

Compressing Open Row Groups

If you’re using, or at least read up on Clustered Columnstore Indexes then you’ll know that the data is stored in segments. You’ll also know that there are 4 states the segments can be in at any one time:

  1. OPEN
  2. CLOSED
  3. INVISIBLE
  4. COMPRESSED

When segments are in the OPEN/CLOSED states they are delta-stores. Delta stores are immediately up-datable and are currently uncompressed. So at this stage the delta stores will not have the space saving advantages of columnstore. The db engine creates a new delta store whenever it needs one to handle inserts, closes them when full (have 1048576 rows). OPEN and CLOSED delta stores can be directly updated and deleted, unlike COMPRESSED segments (see below). You can have more than one OPEN delta store at any one time. Continue reading

Is Your Server Virtual or Physical?

Ever wondered whether an instance of SQL is hosted on a virtual or physical server? This query will tell you:

SELECT
CASE virtual_machine_type
WHEN 1 THEN 'VIRTUAL'
ELSE 'PHYSICAL'
END as [ServerType]
from sys.dm_os_sys_info

You can also determine whether your server is clustered or not via T-SQL:


SELECT
CASE SERVERPROPERTY ('IsClustered')
WHEN 1 THEN 'YES' WHEN 0
THEN 'NO'
END as [CLUSTERED]

These are just a few of the queries I run when I am handed over a server that I have not been responsible for. You can see more of these queries in this post.

SQL Functions: QUOTENAME

Hey all, today I am going to show a quick demo on QUOTENAME. When I first discovered this string function, it was one I wished I had learnt long ago as it helps simplify writing out dynamic SQL.

To quote books online, QUOTENAME “Returns a Unicode string with the delimiters added to make the input string a valid SQL Server delimited identifier.” So basically it formats an identifier with delimiters so that in the event that an identifier does not comply with formatting.

The example below returns all the tables in AdventureWorks 2014 with the different types of delimiters that can be added using QUOTENAME. Continue reading