Press "Enter" to skip to content

Author: Kevin Feasel

Getting The Last Row Per Group

Published 2016-04-12 by Kevin Feasel

Daniel Hutmacher wants to get the last element in each group (for example, the current records in a type-two dimension):

The CROSS APPLY and the old-school solutions are by far the best choice for dense indexes, i.e. when the first column has a low degree of uniqueness. The old-school solution is only that fast because the optimizer short-circuits the query plan.

LEAD() and the old school strategy are best for selective indexes, i.e. when the first column is highly unique.

There’s a nice set of options available so if one doesn’t work well with your particular data set, try out some of the others and see if they work for you.

Comments closed

Transaction Log Analysis

Published 2016-04-12 by Kevin Feasel

Michael Swart shows how to dig into the transaction log to trace down those WRITELOG waits:

WRITELOG waits are a scalability challenge for OLTP workloads under load. Chris Adkin has a lot of experience tuning SQL Server for high-volume OLTP workloads. So I’m going to follow his advice when he writes we should minimize the amount logging generated. And because I can’t improve something if I can’t measure it, I wonder what’s generating the most logging? OLTP workloads are characterized by frequent tiny transactions so I want to measure that activity without filters, but I want to have as little impact to the system as I can. That’s my challenge.

Check out the entire post, as this is a good exercise in investigating busy transactional systems.

Comments closed

Extended Events In Azure SQL Database

Published 2016-04-12 by Kevin Feasel

Julie Koesmarno walks through Extended Events in Azure SQL Database (currently in preview):

Extended Event (XEvent) feature is available as public preview in Azure SQL Database as announcedhere. XEvent supports 3 types of targets – File Target (writes to Azure Blob Storage), Ring Buffer and Event Counter. Once we’ve created an event session, how do we inspect the event session target properties? This blog post describes how to do this in 2 ways: using the User Interface in SSMS and using T-SQL.

It’s nice to see Extended Events making their way into Azure SQL Database.

Comments closed

SQL Server JDBC Driver Update

Published 2016-04-11 by Kevin Feasel

Microsoft is releasing a preview of their JDBC driver:

We are committed to continuously updating the JDBC driver to bring more feature support for connecting to SQL Server, Azure SQL Database, and Azure SQL DW. Please stay tuned for upcoming releases that will have additional feature support. This applies to our wide range of client drivers including PHP 7.0, Node.js, ODBC, and ADO.NET which are already available.

Don’t forget Hadoop integration (e.g., via Sqoop) while you’re at it…

Comments closed

Loading Large Data Sources Into Power BI

Published 2016-04-11 by Kevin Feasel

Reza Rad shows how to get beyond the 10 GB data limitation in Power BI:

Fortunately Power Query still is available with Live Connection. This gives you ability to join tables, flatten them if you require, apply data transformation and prepare the data as you want. Power Query can also set the data types in a way that be more familiar for the Power BI model to understand. If you want to learn more about Power Query read Power Query sections of Power BI online book.

Reza shows some techniques and also the negative repercussions to using Live Connection.  This is a good read if you’re getting into Power BI.

Comments closed

New Powershell Cmdlets Proposed

Published 2016-04-11 by Kevin Feasel

Aaron Nelson has proposed breaking up SQLPackage.exe into at least three cmdlets:

SQLPackage.exe – Needs to be made into at least 3 cmdlets

SQLPackage.exe – Needs to be made into at least 3 cmdlets (and possibly more; we have added ideas for additional cmdlets below). The first 3 cmdlets that need to be made into are:

  • Export-SqlDatabase

  • Import-SqlDatabase

  • Compare-SqlDatabase

This seems reasonable and would help maintain databases.

Comments closed

SQLCover

Published 2016-04-11 by Kevin Feasel

Ed Elliott has upgraded his T-SQL code coverage tool:

What is code coverage?

Code coverage is a way to see how many statements in your database code have been executed when you ran your tests(s). It is a way to see how well covered with tests a particular area is – the better covered with tests, the less likely you will miss issues when you make changes in those areas.

What is code coverage for SQL Server?

SQL Server gives us a great tracing mechanism, either profiler or extended events which details exactly which statements have been run. SQL Server doesn’t do so well at telling us what statements we could possibly run in some code but the Transact Sql Script Dom that is part of the DacFx does give us the ability to break T-SQL code into statements so combining the two we have the ability to take a stored procedure such as:

This is pretty snazzy.

Comments closed

CLR Turned Off In Azure SQL Database

Published 2016-04-11 by Kevin Feasel

Brent Ozar reports that Azure SQL Database’s CLR will be turned off:

Details are still coming in, but in the Reddit AMA for the Azure database teams (going on as we speak), it’s one of the users reports that they got an email that SQL CLR will be shut off in one week due to a security issue.

The cloud: at the end of the day, it’s just someone else’s server, and they can – and will – take tough actions to protect their product, their users, their security, and their profits.

I’m curious for more details.  I’d like to know if this is particular to Azure or affect on-prem installations as well.

Comments closed

Keep Check Constraints Simple

Published 2016-04-08 by Kevin Feasel

Erik Darling shows performance implications around having scalar UDFs in check constraints:

Really. Every single time. It started off kind of funny. Scalar functions in queries: no parallelism. Scalar functions in computed columns: no parallelism, even if you’re not selecting the computed column. Every time I think of a place where someone could stick a scalar function into some SQL, it ends up killing parallelism. Now it’s just sad.

This is (hopefully. HOPEFULLY.) a less common scenario, since uh… I know most of you aren’t actually using any constraints. So there’s that! Developer laziness might be a saving grace here. But if you read the title, you know what’s coming. Here’s a quick example.

Yeah, UDFs in check constraints is a pretty bad idea most of the time.

Comments closed

Power BI Pivoting

Published 2016-04-08 by Kevin Feasel

Reza Rad shows how to pivot and unpivot using Power BI:

So Pivot is easy and simple to do, but you have to be careful about the nature and quality of source data set. If it is normal to have a name repeated in the source data, then an aggregation needs to be set properly. if you expect each name to appear once, then setting it as Do Not Aggregate works better because you can use error handling mechanism in Power Query to handle error somehow.

This is a good sight easier than writing a bunch of SUM(CASE) statements or using the PIVOT operator in T-SQL.

Comments closed