Press "Enter" to skip to content

Curated SQL Posts

Understanding Azure Billing

Published 2017-07-20 by Kevin Feasel

Denny Cherry explains some of the oddities behind Azure billing:

Some things stand out to me when looking at this bill. First is the service that is being billed. It doesn’t say how many DWs we’re being billed more (more on that later).

Something else odd is that the SQL DW shows being billed for 1220 hours. Now in theory the most number of hours for any single service you can be billed for in a single month is 744 as that’s 24 (hours) *31 (days), but here we’re being billed for 1220 hours. Now we know for a fact that this SQL DW is powered off at times, so how are we using this resource for more than 31 days in a month?

The last oddity is the rate. $1.46 an hour. This points us to the reason for all of these strange numbers. That’s the rate to run a SQL DW at DW100 for one hour.

Click through for Denny’s explanation.

Comments closed

When CHECKDB Doesn’t Catch Corruption

Published 2017-07-20 by Kevin Feasel

Paul Randal explains one condition when DBCC CHECKDB misses corruption:

An interesting situation was discussed online recently which prompted me to write this post. A fellow MVP was seeing periodic corruption messages in his error log, but DBCC CHECKDB on all databases didn’t find any corruptions. A subsequent restart of the instance caused the problem to go away.

My diagnosis? Memory corruption. Something had corrupted a page in memory – maybe it was bad memory chips or a memory scribbler (something that writes into SQL Server’s buffer pool, like a poorly-written extended stored procedure), or maybe a SQL Server bug. Whatever it was, restarting the instance wiped the buffer pool clean, removing the corrupt page.

So why didn’t DBCC CHECKDB encounter the corrupt page?

Read on for the rest of the story.

Comments closed

Dealing With Newlines In Reports

Published 2017-07-20 by Kevin Feasel

Shane O’Neill shows how to deal with newlines in data:

I have mentioned before that we can use CHAR(10) and CHAR(13) for new lines and carriage returns in SQL Server so I’ll leave it up to an exercise to the reader to create a table with these “troublesome” bits of information in them (plus if you came here from Google, I assume you already have a table with them in it).

For me, I’ve just created a single table dbo.NewLineNotes that has a single entry with a new line in it.

Read on for more.

Comments closed

2016 Best Practices By Default

Published 2017-07-20 by Kevin Feasel

Wayne Sheffield shows how SQL Server 2016 helps you follow best practices:

tempdb

Ahh, tempdb. In SQL Server, that database does so much… it’s where temporary tables and table variables are stored. It’s where row version activity is stored. It’s where running queries will spill out temporary workspace needed for the query. And on, and on, and on. On busy systems, having this database running optimally is of vital importance. And there are several best practices that need to be observed here.

Read on for several tips.

Comments closed

Powershell Pattern Matching

Published 2017-07-19 by Kevin Feasel

Klaas Vandenberghe shows some of the pattern matching and regular epxression functionality within Powershell:

This is like grep if you ever encountered that. The Select-String cmdlet finds a -pattern in text.
It has an -allmatches switch to … guess what 🙂

Get-ChildItem D:\Myscripts -File -Recurse *.sql | Select-String -pattern "drop\s+[?(database|table|login)"

will get us all our sql scripts in which we drop a database, table or login.
The returned MatchInfo object holds the matching parts of the script text, and also the name of the file and the line number where the match was found.

Get-ChildItem | Select-String is my most frequently used Powershell pipeline.

Comments closed

Neural Networks From Scratch

Published 2017-07-19 by Kevin Feasel

Ilia Karmanov explains neural nets and shows how to build one in R:

Hence, my motivation for this post is two-fold:

  1. Understanding (by writing from scratch) the leaky abstractions behind neural-networks dramatically shifted my focus to elements whose importance I initially overlooked. If my model is not learning I have a better idea of what to address rather than blindly wasting time switching optimisers (or even frameworks).
  2. A deep-neural-network (DNN), once taken apart into lego blocks, is no longer a black-box that is inaccessible to other disciplines outside of AI. It’s a combination of many topics that are very familiar to most people with a basic knowledge of statistics. I believe they need to cover very little (just the glue that holds the blocks together) to get an insight into a whole new realm.

Starting from a linear regression we will work through the maths and the code all the way to a deep-neural-network (DNN) in the accompanying R-notebooks. Hopefully to show that very little is actually new information.

This is pretty detailed.  Karmanov mentions Andrej Karpathy, whose Hacker’s guide to Neural Networks is also a must-read on the topic.

Comments closed

Schema-Only Optimized Tables Can Still Roll Back

Published 2017-07-19 by Kevin Feasel

Chris Adkin investigates whether schema-only memory-optimized tables are logged and whether they support transactions the way other tables do:

The statement “There is zero logging when DURABILITY=SCHEMA_ONLY” is not factually correct, its more like a minimally logged operation. What is surprising is the fact that logged as advertised for the in-memory engine should result in far fewer log records than the equivalent workload for the legacy engine, clearly this is not the case in this particular example and something I need to dig into somewhat deeper. Also note that the version of SQL Server being used is SQL Server 2016 SP1 CU3, which should be stable. One final point, in order to make sure that fn_dblog and fn_dblog_xtp produced clean results for me each time, I took the quick and dirty option of re-creating my test database each time.

This post definitely ranks in the “Microsoft did this right” category.

Comments closed

Running Totals With Window Functions

Published 2017-07-19 by Kevin Feasel

Bert Wagner shows the best method to calculate a running total in SQL Server 2012 or later:

Before SQL Server 2012, the solution to generating a running total involved cursors, CTEs, nested subqueries, or cross applies. This StackOverflow thread has a variety of solutions if you need to solve this problem in an older version of SQL Server.

However, SQL Server 2012’s introduction of window functions makes creating a running total incredibly easy.

Enhanced window functions was one of 2012’s killer features on the T-SQL developer side.  Bert’s post doesn’t cover window ranges and sizes, as the defaults work for him, but Steve Stedman has a good post on the topic if you want more details.

Comments closed

SUM() Or SUMX()

Published 2017-07-19 by Kevin Feasel

Matt Allington explains when to use SUM() or SUMX() in DAX:

Example: Total Sales SUMX = SUMX(Sales,Sales[Qty] * Sales[Price Per Unit])

SUMX() will iterate through a table specified in the first parameter, one row at a time, and complete a calculation specified in the second parameter, eg Quantity x Price Per Unit as shown in the example above for the current filter context.  Once it has done this for every row in the specified table in current filter context, it then adds up the total of all of the row by row calculations to get the total.   It is this total that is returned as the result.

This is a really good explanation of the topic.

Comments closed