Press "Enter" to skip to content

Author: Kevin Feasel

Database Scoped Configuration

Published 2016-05-03 by Kevin Feasel

Mirek Sztajno introduces us to new database-scoped configurations in SQL Server 2016:

  • Enable or disable PARAMETER_SNIFFING at the database level. Disable this option to instruct the query optimizer to use statistical data instead of the initial values for all local variables and parameters when the query is compiled and optimized. This is equivalent toTrace Flag 4136 or the OPTIMIZE FOR UNKNOWN query hint

  • Enable or disable QUERY_OPTIMIZER_HOTFIXES at the database level, to take advantage of the latest query optimizer hotfixes, regardless of the compatibility level of the database. This is equivalent to Trace Flag 4199

  • CLEAR PROCEDURE_CACHE which allows to clear procedure cache at the database level without impacting other databases and without requiring sysadmin permission. This command can be executed using ALTER ANY DATABASE SCOPE CONFIGURATION permission on the database, and the operation can be executed on the primary and/or the secondary

This is an early implementation of functionality, but I think this is a step in the right direction.  Getting finer-grained and database-level configuration settings gets us one step closer to that 2012 dream of containerized databases.

Comments closed

Management Studio Trello Board

Published 2016-05-03 by Kevin Feasel

Aaron Nelson has set up a Trello board for Management Studio collaboration:

A couple weeks ago I mentioned that we are using Trello to help the community collaborate about what we want next in SQLPS before we submit Connect items to Microsoft.

That effort is going very well.  It’s going so well in fact that when the topic of getting some new improvements into SSMS was brought up, the SQL Tools team suggested that a Trello board to collaborate and prioritize what people want improved in SSMS would be very helpful to them.  Ultimately Microsoft needs Connect items filed but using Trello helps folks to debate and combine ideas.

The cynic in me says “this is what Connect is supposed to do” but Aaron and Chrissy LeMaire had a great deal of success working with the SQLPS team, so here’s hoping they get traction here as well.

Comments closed

Deploying Dacpacs

Published 2016-05-03 by Kevin Feasel

Richie Lee shows two different methods of automating Dacpac deployment:

DacFx, or to give it it’s full title, the Data-tier Application Framework “is a component which provides application lifecycle services for database development and management for Microsoft SQL Server and Microsoft Azure SQL Databases“. Essentially, it is another method we can use to manage our Dacpacs. However instead of using the external process SQLPackage and initiating it via cmdline you can use C# or PowerShell to manage Dacpacs. In fact, SQLPackage uses the “Microsoft.SqlServer.Dac.dll” itself. You can verify this by going and deleting the dll and trying to run sqlpackage via command line…. or you can just take my word for it.

Read on for the Powershell script Richie uses.

Comments closed

Lambda Architecture

Published 2016-05-03 by Kevin Feasel

Koos van Strien looks at lambda architecture and asks if it works for data warehouses:

The Lambda Architecture is pretty well documented – online1 as well as in the book I just mentioned2. For a quick overview, Lambda Architecture is basically a system where the raw data is always stored, and never thrown away. All information that’s derived from this raw data is always recomputed – often stated as query = function(all data). This provides for a fool-proof architecture that’s rigorously simple (compared to classic RDBMS solutions), made up of three layers:

Admittedly, about half of this went over my head, but there are some good book and webpage recommendations to learn more about lambda architecture and Data Vault.

Comments closed

“Talk Me Through This Query”

Published 2016-05-03 by Kevin Feasel

Brent Ozar is working on a series of SQL interview questions.  Today’s is “talk me through this query:”

Last month’s post “For Technical Interviews, Don’t Ask Questions, Show Screenshots” was a surprise hit, and lots of folks asked for more details about the types of screenshots I’d show. Over the next few weeks, I’ll share a few more.

Normally I’d show this query as a screenshot, but for easier copy/pasting into comments, I’m showing it as code here.

I’d say to the job candidate, “You’ve been asked to take a quick look at this code as part of a deployment. Explain what the business purpose of the code is, and tell me if there’s anything that concerns you.”

I like where Brent is going with this series and plan to incorporate some of these into my in-person interviews.

Comments closed

SQL Server 2016 Release Date: June 1

Published 2016-05-02 by Kevin Feasel

Microsoft has made it official:

  • SQL Server 2016 will be generally available on June 1, 2016. This will allow you to build mission-critical, and business critical intelligent applications with the most secure database1, the highest performance data warehouse2, end-to-end mobile BI on any device, in-database advanced analytics, in-memory capabilities optimized for all workloads, and a consistent experience from on-premises to cloud. These capabilities are built-in to SQL Server for industry-leading low cost of ownership.

They also announced what will be in each edition.

Interesting Standard versus Enterprise bits for me:

  1. Polybase Compute nodes may be Standard edition but head nodes must be Enterprise
  2. Row-level security and data masking appear to be Standard edition features
  3. R support will be in all editions, but “full” parallelism requires Enterprise edition
Comments closed

Analyze Transaction Log Hierarchy

Published 2016-05-02 by Kevin Feasel

Paul Randal has written code to determine how much log an operation has generated:

Over the weekend there was a discussion on the MVP distribution list about the sys.dm_tran_database_transactions DMV and how one cannot use it to accurately determine how much log an operation has generated because it doesn’t provide a roll-up of the sub-transaction metrics to the outer transaction. This makes the output somewhat non-intuitive.

The discussion prompted me to write some code I’ve been meaning to do since 2012, when SQL Server 2012 introduced a field in LOP_BEGIN_XACT log records that tracks the transaction ID of the parent transaction, allowing the hierarchy of transactions to be investigated.

The actual code is at the bottom of the article, and is available in a zip file here.

It provides two stored procs, sp_SQLskillsAnalyzeLog and sp_SQLskillsAnalyzeLogInner, with the former making use of the latter, and the latter calling itself recursively.

I’d consider this squarely in the advanced troubleshooting realm.  Definitely read the whole thing and Paul’s code.

Comments closed

Architecting Semi-Structured Data Solutions

Published 2016-05-02 by Kevin Feasel

James Serra gives four architectural scenarios for handling large quantities of semi-structured data:

An evolution of the three previous scenarios that provides multiple options for the various technologies.  Data may be harmonized and analyzed in the data lake or moved out to a EDW when more quality and performance is needed, or when users simply want control.  ELT is usually used instead of ETL (see Difference between ETL and ELT).  The goal of this scenario is to support any future data needs no matter what the variety, volume, or velocity of the data.

Hub-and-spoke should be your ultimate goal.  See Why use a data lake? for more details on the various tools and technologies that can be used for the modern data warehouse.

Check it out for a high-level architectural view of contemporary warehousing choices.  I prefer having both systems in play:  the EDW answers known business questions and gives you back report information relatively quickly; whereas the Hadoop cluster allows you to do spelunking, data cleansing, and answer unanticipated business questions.

Comments closed

The Joy Of Trello

Published 2016-05-02 by Kevin Feasel

Chrissy LeMaire praises Trello:

The SQLPS Trello board worked out so well, that when the topic of getting overdue some improvements into SSMS was brought up, Microsoft suggested that setting up another board for SQL Server Management Studio might work well too.

If you use either of these products, I strongly suggest you join us and give Microsoft your feedback. They actively participate on both boards which is just amazing.

I’ve never been a huge Trello fan, but I do admit that I’m looking for a good tool for personal planning, so maybe I’ll give it another try.

Comments closed

Fibonacci Series Calculation

Published 2016-05-02 by Kevin Feasel

Daniel Hutmacher shows a few methods for calculating Fibonacci sequences in T-SQL:

There’s a really nice mathematical way that I found on Stack Overflow, using the golden ratio (from Wikipedia). And it’s actually set-based. I don’t have the requisite mathematical skills to evaluate the correctness or precision. Note that it returns afloat value result, the upside of which is that you can calculate much higher values. The downside is the loss of precision that comes with float.

This is a bit of a brain teaser but if you learn the techniques, they can definitely come in handy in the future.  I like the third solution because it’s a reminder that writing code is just as much about understanding the domain as it is understanding the syntax.

Comments closed