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)
Despite the fact that end of mainstream support ended for SQl Server 2008 R2 earlier this year, Microsoft have released SQL Server 2008 R2 Service Pack 3. Continue reading
Some weeks back I wrote about the differences between truncating and deleting data from a table. Today I’m going to look into the behaviour of an identity column on a table when you use either a delete or a truncate to clear the data out of the table.
SQL Server 2014 has been available for some time now, yet until now we have not had any sample databases available. I say until now because on August 27th Microsoft released the AdventureWorks 2014 sample databases, available on Codeplex.
Read up on Jimmy May’s blog on how they (eventually) got to us.
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:
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
Ever wondered whether an instance of SQL is hosted on a virtual or physical server? This query will tell you:
WHEN 1 THEN 'VIRTUAL'
END as [ServerType]
You can also determine whether your server is clustered or not via T-SQL:
CASE SERVERPROPERTY ('IsClustered')
WHEN 1 THEN 'YES' WHEN 0
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.
October this year is shaping up to be a month of learning, especially for those of us who live in London. Because not only is SQL Relay taking place on the 30th, there’s also RedGate’s SQL In The City happening a week earlier on the 24th. Continue reading
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