Sql-Server

Recently I published a series of blog posts that provided scripts for rebuilding clustered columnstore indexes.

Niko Neugebauer sent me a suggested update to the latest version of those scripts and it makes sense. The change will help to filter out row groups that are not compressed and Niko noted that as Microsoft have started to include more objects (like Tombstone values)  into sys.column_store_row_groups in Azure SQLDatabase, he believes that will help to prevent future SQL versions having a problem with the scripts.

2015-01-31

I’m always telling people that the thing I love about BI projects is that they tend to appeal to the people who pay the bills. This is a good place to be doing project work.

When I look at large companies like Amazon, I can imagine that the people who do the IT work that relates to order processing, invoicing, shipping, etc. do a great job. But I’ll bet that their life is full of budget cutbacks, headcounts, increased pressure to improve productivity, etc. By comparison, I’ll bet the projects that fund parts of their site that make recommendations for possible purchases, etc. are funded at an entirely different level. And that’s because these projects can more directly impact the profitability of the organization, and do so in a more visible way.

2015-01-28

When I attend events like TechEd, like many people I usually find the networking time more valuable than the session time. There is a pretty tight limit on the number of sessions you can attend, no matter how hard you try. So I often watch the sessions later when I can. At one of the earliest TechEd Australia events that I attended, they gave us CDs of the TechEd USA sessions. That was great because that TechEd had around 250 sessions, and there were over 150 that I would have loved to attend. Clearly that wasn’t possible.

2015-01-09

Earlier, I posted a maintenance script that could be used with clustered columnstore indexes to try to determine when they should be rebuilt. Reorganize is not a very useful operation for these indexes as it basically just forces a close of the existing delta store, ready for compression.

One of the problems with a clustered columnstore index is that it doesn’t work as well if the segments have been created at less than the maximum potential size of 1048576 rows per segment. Over use of reorganize options could cause this, but more likely it will be to do with loading the index with small batches of data, rather than loading large batches in BULK INSERT operations.

2015-01-09

For general index maintenance, our friend Old Hallengren has an awesome solution for most people: https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

We’ve started to have customers using clustered columnstore indexes in SQL Server 2014, and they need to be treated differently. I checked Ola’s latest scripts today to see what happens with columnstore indexes. It appears that the code ignores nonclustered columnstore indexes (ie: index type of 6), which makes sense as we need to rebuild them whenever the data changes, and in the meantime, the table is read-only. So that makes lots of sense.

2015-01-09

At a client site today and they asked me about a warning that they got every time they manually failed over their SQL Server availability group.

It said: “The current WSFC cluster quorum vote configuration is not recommended for the availability group.” They were puzzled by this as they had a valid quorum configuration. In their case, they had a two node cluster using MNS (majority node set) and a fileshare witness.

2014-12-22

I’ve been hoping this would happen for a while and now it’s here (in beta).

Whenever I take a certification exam, I find it removes my ability to work for most of a day, so I tend to schedule myself for two or three exams in a day, to avoid the overhead. It also means that I tend to limit the number of exams that I would take.

Online proctoring of exams changes all that for me. If I can just schedule an exam for lunch time or night, or weekend from my own office, I’ll be much more inclined to take more certification exams.

2014-12-22

I’ve liked the Telerik tools for a long time. I see many of my clients using either their controls for Webforms, and many using KendoUI for newer MVC development. I suppose it’s a challenge for such vendors to work out how to best market their products, but also a challenge to show developers how to best use them.

With KendoUI, I particularly liked the Kendo Dojo idea, where you could just interactively learn to use the framework directly from inside your own browser without having to install anything locally.

2014-12-19

A question came up from a developer yesterday. He could see how to set a transaction isolation level but didn’t know how to determine the current transaction isolation level. That detail is available in the sys.dm_exec_sessions DMV.

Here’s an example:

And if you are running SQL Server 2012, you could always use CHOOSE instead:

2014-12-18

I spend a lot of time working with software houses, helping them to make their applications work well with SQL Server. One thing that I’ve heard loud and clear over the years is that most software houses won’t write a single line of code that will only run on the enterprise edition of SQL Server, because they are not prepared to limit their potential pool of customers to those running enterprise edition.

2014-12-12