Press "Enter" to skip to content

Category: T-SQL Tuesday

Delayed Durability

Published 2016-04-13 by Kevin Feasel

Kenneth Fisher talks about Delayed Durability as his favorite SQL Server feature:

In the end I decided it would be fun to post about one of the newer features;Delayed Durability. To understand DD (delayed durability) you have to understand how SQL Server implements durability. To start with there are two pieces of any data change. The actual data page and any log records that are created. In order to keep SQL Server as fast as possible the data pages are changed in memory and then written to disk as part of a background process. Unfortunately this means that you could lose those changes if the server crashes or shuts down before they are written to disk. In order to avoid this, and keep the changes durable, the transaction can not complete until the log information is actually written to disk. This means that one of the big bottlenecks in any given transaction is writing that log record to disk. The DD option basically changes that write from a synchronous to an asynchronous operation. The log writes are bundled together in 60k chunks before being written to disk. This can (depending on your work load) provide a huge increase in speed. I’m not going to bother discussing that part of it since Aaron Bertrand (b/t) and Melissa Connors (b/t) wrote great posts on just that (Aaron’s and Melissa’s).

What I want to discuss is the actual risk of data loss.

My philosophy on this is simple:  if you need delayed durability and you can afford the data loss, then maybe there’s a better data platform.  I want my SQL Server like I want my Grateful Dead concert:  ACID compliant.

Comments closed

Replication

Published 2016-04-13 by Kevin Feasel

Jon Morisi loves him some replication:

Seriously though, replication has been around since the beginning and it’s not going anywhere.  I can’t think of any other feature more prolific than replication.  Name another SQL Server HA/DR technology that is as extensible as replication.  Replication has gotten a bad rap over the years mostly on anecdotal comments that it “breaks all the time” or “it takes too much time to manage”.  I’ve worked in many environments and have setup dozens and dozens of instances of log shipping, mirroring, clusters, availability groups, and replication.  From my anecdotal experience, I can tell you I’ve had more trouble with availability groups than I have with replication.  If you have a good DBA that understands replication, uses it correctly, and is provided the correct tools (read $ for hardware/infrastructure) replication works just fine.  I have setup replication in a global environment in which multiple databases, publications, subscriptions, and agents ran around the clock and without issue.

Replication is very powerful, I agree…but it still breaks.  A lot.  I’m grateful for its existence and also for the fact that I’m not the one maintaining it…

Comments closed

Forms Of Text

Published 2016-03-11 by Kevin Feasel

Kenneth Fisher explains his blog header, which shows different ways to manipulate text in SQL Server:

When I started this blog a friend of mine suggested I write a really complicated query as a header. Now I’m not sure how complicated it really is, but I find it fairly amusing, and the whole point of it is to manipulate some text to generate a different set of text. So this seems like a good time to go through it and explain what’s going on.

There’s a bit to unpack, but it’s a fun experiment.

Comments closed

Text Search

Published 2016-03-09 by Kevin Feasel

Anders Pedersen discusses one method he used to implement fast text search in SQL Server:

Looking into what was needed, I quickly realized there was a LOT of data, guess 50+ years of news broadcasts will do this.  Consider this was in the early 2000s, some innovation was needed from anything I had coded before.  Obviously LIKE searches was out of the question, full text search was not available.  So what to do?

Basically I decided to break down each broadcast to words into a separate table, the entire application fit in 2 tables: Story and Words.

This is a case in which thinking about the grain of data helps solve an otherwise-intractable problem.

Comments closed

Making Text Search Faster

Published 2016-03-08 by Kevin Feasel

Rob Farley looks at binary collations and specifically hash functions on aggregation:

If there’s an index on the column you’re grouping, then changing the collation is going to hurt a bit. Grouping could take advantage of a Stream Aggregate under our indexed collation, but changing the column is like throwing it away the index order (ORDER BY doesn’t get handled well by changing the collation) means a Hash is required. But comparing two query plans that both use Hash Match (Aggregate), one on a case-insensitive collation and one on a binary collation, then I found the latter was slightly faster. Not as drastic a change as searching, but still 10-30% better. One would run in about 12 seconds, and one in about 10.

Be sure to check out his comments for more details.

Comments closed

Power BI Roundup

Published 2016-02-23 by Kevin Feasel

Jorge Segarra has a roundup of this month’s T-SQL Tuesday:

This month I challenged the blogging community to share their own creations in Power BI. We got a ton of great entries this month, thank you everyone who participated! My overarching goal for this month’s topic was to get folks who may not normally play in the BI space to use this fantastic solution and maybe get some ideas flowing on how they may be able to apply it in their everyday work.

The part I like most about T-SQL Tuesday is that it introduces you to a whole new set of bloggers and a whole new set of perspectives on any particular topic.

Comments closed

Power BI With SSAS

Published 2016-02-19 by Kevin Feasel

Jens Vestergaard’s T-SQL Tuesday entry involves Power BI feeding from Analysis Services:

My story with this half-baked product (the Dashboard you are about to see), is that I needed some way of tracking performance on a couple of Analysis Services (SSAS) query servers. There are a lot of good posts and talks about how to collect and store performance counters and SSAS logs out there, and I suggest you look into this, this or that, if you need inspiration.

The current data set is about 200K rows, as I am sampling each server every 5th minute.

Both of these are valuable tools in a Microsoft BI environment.

Comments closed

Power BI Plus Fitbit

Published 2016-02-16 by Kevin Feasel

Rob Farley visualizes his Fitbit data with Power BI:

I added a column: RollinAvgSteps = AVERAGEX(FILTER(fitbit_export_20160214, EARLIER(fitbit_export_20160214[Date])>=fitbit_export_20160214[Date]),fitbit_export_20160214[Steps])

…which takes the average of my steps to date. There are a bunch of ways to achieve this, but this is the way that I chose. And you can see that the average line is (happily) improving! Oh, and because I pulled down the extract on the 14th, there’s a dip at the end. My numbers were much healthier by the end of the day, and despite spending way too long NOT walking, I did about 7244 steps that day.

You can see the result at http://bit.ly/RobFitbit

I like the rolling average that Rob added in.

Comments closed

T-SQL Tuesday Roundup

Published 2016-01-21 by Kevin Feasel

Robert Davis hosted T-SQL Tuesday, and he has a nice roundup of posts:

Thanks to everybody that participated in this month’s T-SQL Tuesday. A big thanks to everyone who wrote a participating blog post, and a really huge thanks to everyone who read the posts shared by this month’s participants. If you follow one of the links on this round-up page, I will kindly ask that you leave a comment on a blog post that you read if it teaches you something, gives you a new perspective, or makes you think. A blog post is just a bunch of words until somebody reads it, and blog writers love it when they hear that their post resonated with someone on some level.

Thanks to Robert for hosting T-SQL Tuesday #74.

Comments closed