Press "Enter" to skip to content

Month: April 2019

T-SQL Tuesday 113 Roundup

Published 2019-04-17 by Kevin Feasel

Todd Kleinhans takes us through T-SQL Tuesday #113:

Wow, we had a variety of responses to the April 2019 topic of “What Do YOU Use Databases For?”

I think the overall response to the question and the theme is both mixed and varied.

I have been struggling with the personal use of databases for a long time. Things I wish would have been easier but seems to just get more complicated over time. Ever heard of GDPR? Although we think we have absolute control and access to data about ourselves, we really do not. The right to be forgotten is NOT the same as having access to all of the data about ourselves in all of the systems before they disappear. Sometimes companies will delete your data about you before you ask.

Todd starts out with an essay and then moves on to the roundup.

Comments closed

When Scans are Superior to Seeks

Published 2019-04-17 by Kevin Feasel

Brent Ozar shows that index seeks are not always better than index scans:

Somewhere along the way in your career, you were told that:
– Index seeks are quick, lightweight operations
– Table scans are ugly, slow operations

And ever since, you’ve kept an eye on your execution plans looking for those performance-intensive clustered index scans. When you see ’em, you go root ’em out, believing you’ve got a performance problem.

Thing is, … they lied to you. Seeks aren’t necessarily good, nor are scans necessarily bad. To straighten you out, we’re going to walk through a series of demos.

The rule of thumb I like to use is: if you need to go through more than 20% of the data, you’re generally better off scanning. If you need to go through less than 0.5% of the data, you’re generally better off seeking. Everything in between is the “it depends” zone.

Comments closed

Clustered Columnstore Index Memory Timeouts

Published 2019-04-17 by Kevin Feasel

Joe Obbish takes a deep look at a clustered columnstore index insertion scenario:

Why should we care about memory grant timeouts for CCI insert queries? Simply put, lots of bad things can happen when those queries can time out, both for serial and for parallel inserts. For serial insert queries, I’ve observed deadlocks, extremely poor performance along with long SLEEP_TASK waits, and extremely long rollbacks. For parallel insert queries, I’ve observed queries that run seemingly forever, poor performance of the SELECT part, and error 8645. You probably don’t want any of that occurring in production. It would be very helpful if it was possible to extend the 25 second time-out for queries that insert into columnstore tables.

Read through as Joe learns the true meaning of Christmas a KB article.

Comments closed

Reviewing the Stack Overflow Developer Survey

Published 2019-04-16 by Kevin Feasel

Michael Toth looks at the recently-released 2019 Stack Overflow Developer Survey:

Since 2011, Stack Overflow has been surveying their users each year to answer questions about the technologies they use, their work experience, their compensation, and their satisfaction at work. Given Stack Overflow’s place in the broader programming world, they are able to draw quite the audience for their annual surveys.

This year, nearly 90,000 developers participated in the survey! There’s a lot in this survey, and I recommend reviewing it yourself, but I wanted to surface some of the key findings that I thought were particularly relevant to data professionals here.

Stack Overflow says they will be releasing the underlying data for this survey in the coming weeks, so I hope to return to this for a deeper analysis once that’s made available. For now, let’s get into the results!

Michael’s lede involves R versus Python in terms of salaries, but for me, the top line is that functional programmers make more money. Clojure, F#, Scala, Elixir, and Erlang make the top 10 on the global list, including positions 1, 2, 4, and 5. Within the US, Scala, Clojure, Erlang, Kotlin, F#, and Elixir make the top 10, including positions 1, 2, and 4. H/T R-Bloggers

Comments closed

Understanding DNS for Developers

Published 2019-04-16 by Kevin Feasel

RJ Zaworski explains DNS for web developers:

DNS can use a similar TCP/IP stack, but being parts of a simple system, most DNS operations can also travel the wire on the Internet’s favorite Roulette wheel: the User Datagram Protocol, UDP.

On a good day, UDP is fast, simple, and stripped bare of unnecessary niceties like delivery guarantees and congestion management. But a UDP message may also never be delivered, or it may be delivered twice. It may never get a response, which makes for fun client design–particularly coming from the relatively safe and well-adjusted world of HTTP. With TCP, you get an established connection and all kinds of accommodations when Things Inevitably Go Wrong. UDP? “Best effort” delivery. Which means a packet thrown over the fence with a prayer for a soft landing.

It’s a good read if you’re new to DNS.

Comments closed

Automating Command Line Sessions with Powershell

Published 2019-04-16 by Kevin Feasel

James Livingston shows us how to redirect the standard in flow with .NET, using Powershell as the example language:

Command Line Interface (CLI) tools can be very useful for interacting with certain applications. However, some CLIs do not let a user pass in parameters which makes it difficult to automate. Instead, they lock a user into an interactive session and force the user to enter commands. Fortunately, some programming languages allow for a redirection of the standard input, output, and error of an running process. A developer co-worker of mine has been very successful doing this in Java, which got me thinking… And turns out, using the .NET libraries, we can implement this functionality for any CLI in PowerShell.

Automating interactions with CLI programs is often not ideal but when you’re stuck, it does the trick.

Comments closed

Micro Modules in Powershell

Published 2019-04-16 by Kevin Feasel

Kevin Marquette shows how to create a micro module and explains why you might want one:

A micro module is very small in scope and often has a single function. Building a micro module is about getting back to the basics and keeping everything as simple as possible.

There is a lot of good advice out there on how to build a module. That guidance is there to assist you as your module grows in size. If we know that our module will not grow and we will not add any functions, we can take a different approach even though it may not conform fully to the community best practices.

There are a few things which differ from standard module best practices.

Comments closed

Error Messages Related to Temporal Tables

Published 2019-04-16 by Kevin Feasel

Mala Mahadevan digs into temporal tables:

Last month I was fortunate to have my first ever article published on Simple-Talk, among the best quality website for sql server articles ever. During the process of writing this article I ran into several errors related to temporal tables that I have not seen before. Some of these are documented by Microsoft, some are fairly obvious to understand and others are not. Below I summarize the list of errors you can possibly run into if you are using this really cool feature.

Click through for the list.

Comments closed

Defining Downtime Down

Published 2019-04-16 by Kevin Feasel

Andy Mallon takes us through the notion of downtime:

There’s a lot of discussion about preventing downtime. As a DBA and IT professional, it’s my sworn duty to prevent downtime. I usually describe my job as DBA something along the lines of, “to make sure data is always available to the people and applications that need it, and never available to the people and applications that shouldn’t have it.” Preventing downtime is certainly important for that first part–but how the heck do you define downtime?

Andy asks more questions than provides answers, but these are the types of questions which the technical side and the business side can get together on to define what constitutes downtime.

Comments closed