Direct Seeding Availability Group Replicas

Mike Fal is excited about direct seeding of Availability Groups in SQL Server 2016:

In SQL Server 2012 and 2014, creating an Availability Group could take a significant amount of work. One of the more tedious tasks is setting up your replica databases. This is because that you need to restore your database to your replica node in a state close enough to the primary to allow synchronization to happen. It can take several log backup restores to hit that magic window where you can join the database on the secondary node. Then, you get to do it again on the next replica!

Enter direct seeding in 2016. With this feature you no longer have to do any of the restores. You simply create your replicas with direct seeding enabled, then when you add a database to the AG, SQL Server will directly seed the database to your replica nodes. It’s surprisingly simple.

This sounds pretty interesting.

Compressing LOB Data

Andy Mallon wants to compress LOB data:

The COMPRESS() function takes an input of string or binary data, and applies the gzip algorithm to it. It returns a value of type varbinary(max). In essence, instead of storing string or binary data, you can gzip it up and store it in a varbinary(max) column. There’s also a DECOMPRESS() function for when you are reading the data and need to unzip it.

This costs some CPU, but gzip can save quite a bit of space. How much space, and whether it’s worth the CPU cost will vary depending on your data and workload. In this blog post, we’ll take a look at one table. We’ll look at the space savings we get out of using COMPRESS(), and we’ll look at the effort necessary to implement it.

Read on for Andy’s test and thoughts.

Creating R Code

Ginger Grant introduces us to Microsoft R:

Microsoft has not one version of R, they have two but two. These two different versions are needed because they have two different purposes in mind. Microsoft R Open, is open source and fully R compatible and is faster than open source R because they rewrote a number of the algorithms to include multi-threaded math libraries. If you want to run R code on SQL Server, this is the not the version you want to use. You want to use the non-open source version designed to run on R Server, which is included with SQL Server 2016, Microsoft RRE Open. This version will run R code not only in memory but swap to disk, to create code which can access SQL Server data without needing to create a file, and can run code on the server from the client. The version of RRE Open which is included in SQL Server 2016 is 8.0.3.

She follows this up with a demo program to pull data from a SQL Server table and generate a histogram.  If you have zero R experience, there’s no time like the present to get started.

Waiting For SP1

Guy Glantser hates “wait for SP1” advice:

Historical Facts

Throughout my career I have never seen an RTM version that was substantially less stable then the following SP1. Sure, there were bugs and issues. Sometimes there were critical bugs and issues. But there were just as much bugs and issues in SP1 and in SP2, and so on. I haven’t conducted a thorough research, so I don’t have a statistical proof, but these are the facts, at least from my experience.

I’d add one more thing:  pre-release versions of SQL Server run in production as part of Microsoft TAP (older link, and I think RDP and TAP have merged together at this point, but I don’t have those inside details).  These are real companies with real workloads running pre-RTM versions of SQL Server.  I work for a company which is in the program, and we were running our data warehouse on CTP 3 and then RCs.  By the time RTM hits the shelves, there’s already been a good deal of burn-in.

T-SQL Tuesday: SQL 2016

Michael J. Swart is hosting this month’s T-SQL Tuesday:

SQL Server 2016 went RTM this week and so naturally, we’re going to write about it. Here are a few writing prompts for you:

  • Check out what’s new. Microsoft has written a lot about their new features. Thomas Larock has written a really nice landing page for those posts, SQL Server 2016: It Just Runs Faster – Thomas Larock. Look through those links. Do you feel optimistic about 2016? Or maybe a bit disappointed? Let us know either way

  • Haven’t had time to download the bits, install them, explore and form thoughts on 2016 yet? Have no fear, check out Microsoft’s Virtual Labs. It lets you explore features without worrying about all the setup. In minutes you’ll be typing SELECT 'hello world';

Get writing!

RID Lookup Or Key Lookup?

Aaron Bertrand asks which is faster, RID lookups or key lookups?

I’ve seen multiple people state that a heap can be better than a clustered index for certain scenarios. I cannot disagree with that. One of the interesting reasons I’ve seen stated, though, is that a RID Lookup is faster than a Key Lookup. I’m a big fan of clustered indexes and not a huge fan of heaps, so I felt this needed some testing.

So, let’s test it!

I thought it would be good to create a database with two tables, identical except that one had a clustered primary key, and the other had a non-clustered primary key. I would time loading some rows into the table, updating a bunch of rows in a loop, and selecting from an index (forcing either a Key or RID Lookup).

It looks like RID lookups are slightly faster than key lookups.  But check out the comments:  this is a best-case scenario.

Custom Power BI Visuals

Rob Farley looks into custom Power BI visuals:

I hadn’t explored much in the way of custom visuals in Power BI until a while back, even though I was very much aware of the competition that was held in September. It had been on my list to explore some of what was possible. And this month, the T-SQL Tuesday topic (hosted by Wendy Pastrick@wendy_dance) was to learn something new and to blog about it. So it seemed a good idea to learn how to make my own custom visualisation!

Now, creativity isn’t exactly my thing. I find it really hard to write songs, for example. I know how to do it – but I quickly become self-critical and get stuck. Writing is easier, because it feels less ‘creative’, and appeals more to the teacher / preacher in me (and I know that takes creativity, especially if you’ve ever seen me present, but it’s different). So sitting down and coming up with a new way of visualising data wasn’t something I was going to do.

For more info, check out more info on creating visuals and using R to visualize data in Power BI.

DB File Space Usage

Andy Mallon looks into sys.dm_db_file_space_usage:

SQL Server 2016 is coming out next month, and it’s chock full of new features that I can learn. Row-level security is interesting. Dynamic data masking sounds fun.

If you’re a regular reader of my blog, you probably know I try to approach questions from a unique angle. Instead of blogging about something cutting edge or sexy, I decided to scroll through the list of system views until I found one I didn’t recognize.

sys.dm_db_file_space_usage

The name is pretty self-explanatory, but I never noticed this existed until now. Seems like the type of DMV that I should have known about, but I didn’t. Quick look at BOL, and I got the verbose description from Microsoft:

Andy goes on to compare the outputs from this DMV to methods he’s historically used.

T-SQL Tuesday Roundup

Jens Vestergaard has his round-up of favorite SQL Server features:

This week, I have been looking forward to the time where I got to read through all the contributions to my#TSQL2SDAY invitation: Favorite SQL Server Feature. Very happy to see this many. I have added a short description of each blog post, as well as my own personal key take-away(s).

So, in no particular order, I give you the round-up:

I like T-SQL Tuesday for several reasons; one of the more selfish reasons is that each month, I get to expand my blogroll a little bit further.  This was a particularly good one, so check out the entrants.

Partition Switching

James Anderson gives his favorite feature in SQL Server:

The good news is that the SWITCH command works on regular tables and in any edition. This means I can quickly transfer all of the data from one table to another in Standard Edition.

In reality, I have found few uses for the regular table to regular table switch; the trick to add the IDENTITY property to a column with existing data is the most recent. SWITCH is most useful when partitioned tables are involved. Sorry, Standard Edition users.

Partition switching is a fascinating solution for a difficult technical problem.

Categories

May 2017
MTWTFSS
« Apr  
1234567
891011121314
15161718192021
22232425262728
293031