How To Split Non-Empty Partitions When A Clustered Columnstore Index Exists on The Table

Another pithily titled blog post, but this one revisits the feature of SQL Server 2014 that piqued my interest the most: Clustered Columnstore Indexes. I’ve been working with Clustered Columnstore Indexes for the past few months and recently a colleague had to split a non empty partition, only for the error message below to appear.

Msg 35346, Level 15, State 1, Line 390
SPLIT clause of ALTER PARTITION statement failed because the partition is not empty. Only empty partitions can be split in when a columnstore index exists on the table. Consider disabling the columnstore index before issuing the ALTER PARTITION statement, then rebuilding the columnstore index after ALTER PARTITION is complete.

Ok, so it looks like we cannot split non empty partitions with a Clustered Columnstore Index on the table, or at least an enabled clustered columnstore index. So the topic for todays post is to find out how can we split a non empty partition when a clustered columnstore index exists on the table. We can create a test database/tables/Clustered Columnstore Indexes and disable the indexes before we split to see if that is the solution, as suggested in the error that it might be. Let’s start at the beginning using the database I created for my previous post on SQL Server 2014 Clustered Columnstore Indexes and Partitioning. The script below will create the database and the objects necessary. I’m going to state the obvious, but read through the scripts first before you run them; things like file locations on boxes are always different: Continue reading

Attach and Detach Cubes Using AMO

goneDrinking

 

Before I start this post, I want to mention a few things:

  1. I’m (finally!) moving house next week. So I won’t have any internet for a couple of weeks. I’ve been so busy with everything else I have not been able to write any posts that will publish during my time off. So output will be down for the next month or so.
  2. If anyone can point me in the direction of a site that helps with giving blog posts good titles let me know; I really do suck at coming up with decent blog post titles and need all the help I can get.

All that being said, let’s get on with today’s topic, using AMO to detach/attach cubes! Writing about using PowerShell to manage SSAS via AMO was the whole reason I created this blog before it expanded to sharing a whole lot more, so I always enjoy coming back to this subject as much as I enjoy writing the scripts.

Continue reading

Range Values of DATETIME vs DATETIME2

Came across an error today generated by Entity Framework:

‘The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value.’

Typically a datetime2 type can be inserted into a datetime type column provided the value of the datetime2 is between the minimum and maximum values of the datetime type. Continue reading

Database Project Entropy

With every new release of a software product, some features are altered at the request of the majority of users with the aim to improve the product. And generally these changes are correct. Most, but not all. Some are short sighted at best. And if there was one feature change which Microsoft acquiesced to which I just cannot abide by was to remove the automatic creation of the folder structure in Visual Studio database projects. Continue reading

PRINT and SELECT Character Limits

trying something a little different today… writing a post that is the examples. A simple one to start off this method of blogging. Enjoy!


		SET NOCOUNT ON
		 
		DECLARE @SQL NVARCHAR (max)
		SET @SQL = 
		'There is a character limit set when outputting the query results to text to 256 character. This message will not print out fully. So the question is:
		"How do i change this?" Well it is quite simplereally: what you need to do is go to go to the menus at the top of the screen, and click on tools ---> 
		options. Then, on the left-hand side, Expand Query Results, and then expand the SQL Server, and highlight "Results to Text". On the right hand side 
		there is an option called "maximum number of characters displayed in each column:. Increase this number (I usually increase to 4000, which is the character 
		limit for PRINT statements... although worth noting that results to grid are capped at 65535 for non-xml data and 2mb for XML) and click ''OK''. You will need 
		to re-open your query windows for this change to take affect.'

		select 'Hello. ' + @SQL

		SET @SQL =  
		'note that this character limit does not apply to a PRINT statement. As stated before, a PRINT statement can be longer. For example, this sentence is pretty long*
		and yet it prints out no issues. 
		
		
		
		( * In fact, the total length of this sentence is actually: '

		DECLARE @SQLLEN nvarchar (3) = LEN (@sql)

		PRINT 'Hello again. ' + @SQL + @SQLLEN +')' +CHAR (10)

		SET NOCOUNT OFF

What You Need to Know About SSAS Processor Affinity

I’ve been looking into using processor affinity on our SSAS instances to help control the workload on our servers. Sometimes a process can run which will grab as much CPU resource as it can, which means that queries on the cubes cannot run, grinding the front end to a halt. Unlike the db engine, there is no resource governor for Analysis Services. Sure you can set the min/max memory simple enough, but setting limits on IO and CPU is a more complex task. Continue reading

Creating Snapshots of a Database Script

Creating snapshots for databases is one of those features that is reserved for the Enterprise edition of SQL Server. A database snapshot is a read only transactionally-consistent copy of a database taken from a point-in-time. To create a snapshot of a database you need to execute a T-SQL statement; there is not an option through the UI. It is much the same as a CREATE DATABASE statement, but with a few differences:

  • you do not need to specify the log file
  • the files that you do create do not need to be of any particular file type (they are sparse files, a feature of NTFS file systems)
  • the database snapshot needs to reside on the same instance of SQL as the original database.

Continue reading