A Fine Slice Of SQL Server
Kenneth Fisher wanted your non-SQL Server tips and tricks:
I was thrilled to host TSQL Tuesday this month (#127) and I my prompt was non-SQL tips and tricks. Now I have to admit when I said that I was thinking specifically technical stuff, but I didn’t specify and I’ll be honest I’m really glad I didn’t.
Click through for the set of answers.
Leave a CommentMikey Bronowski walks through an introduction to the Graphviz diagramming language:
I came across Graphviz which is an open-source graph visualization software initiated by AT&T Labs Research. It can process the graphs that are written in the DOT language.
What is the DOT language?
In short, it is a graph description language that has few keywords like graph, digraph, node, edge. You cannot miss it has something to do with graphs.
I’ve used the R implementation of this as well. It doesn’t create beautiful diagrams, but it is fast, easy, and the output makes sense.
Comments closedGlenn Berry wanted to know how we’ve been keeping busy lately:
Back on May 5, 2020, I posted the invitation for T-SQL Tuesday #126 – [email protected], which was due on Tuesday, May 12, 2020. I ended up getting seventeen blog post responses that I am aware of. Thank you to everyone who participated! This post will be my T-SQL Tuesday #126 Recap.
Click through for the rundown.
Comments closedJess Pomfret walks us through the basics of tSQLt:
Getting started with tSQLt is really easy- you download a zip file, unzip the contents and then run the tSQLt.class.sql script against your development database.
There are a couple of requirements, including CLR integration must be enabled and your database must be set to trustworthy. These could open up some security considerations, but for my development environment it’s no issue.
This is where I’d say putting the database in a container would be extremely helpful, as then you can destroy it afterward. I’m not sure if that’d work, as SQL Server on Linux doesn’t support unsafe or external access assemblies (and I’m not sure what tSQLt requires there).
Comments closedChris Johnson shares some thoughts on unit testing database code:
This is a topic that is quite close to me heart. I don’t come from a computing background before I started working with SQL Server, so I was quite ignorant when it came to a lot of best practices that other developers who have worked with other languages are aware of. Because of this, I had no idea about unit testing until I attended a talk at a SQL Saturday all about tSQLt. If anyone isn’t aware (as I wasn’t) tSQLt is a free open source unit testing framework for use in SQL Server databases. It is the basis of Redgate’s SQL Test software, and is the most used framework for writing unit tests in SQL Server.
Since then I’ve worked to try and get employers to adopt this as part of a standard development life cycle, with mixed success at best. My current employer is quite keen, but there are two major problems. First, we have a huge amount of legacy code that obviously has no unit tests in place; and second, the way people code is not conducive to unit testing.
Click through for additional thoughts on writing good tests and an example of modularizing code to make it more testable. I’m still in the camp of “test what you can, but you can’t test everything” with databases. There’s just too much state dependency.
Comments closedRob Farley talks about a couple of reasons why database unit testing can be difficult to do:
Hamish wants to develop a conversation about unit testing within database because he recognises that the lack of unit testing is a significant problem. It’s quite commonplace in the world of iterative code, of C#, Java, and those kinds of languages, but a lot less commonplace in the world of data. I’m going to look at two of the reasons why I think this is.
Read Rob’s thoughts in their entirety. I fully agree that we need to test, but get wishy-washy on the topic of automated testing. The reason for that is that tooling is quite limited, and many of those limitations are inherent limits in the database platform itself. For the types of things you most need to test (like hefty stored procedures), the number of test cases spirals out of control quickly. And unlike functional or structured programming languages, T-SQL performance gets markedly worse as you modularize, which makes it so difficult to get down to an easily testable block of code.
Comments closedTracy Boggiano asked about Query Store and got some good responses:
Happy St. Patrick’s Day (are you wearing something green)! For March I asked everyone to blog something related to Query Store, here is a summary of in alphabetical order of the blog posts we received although Rob Farley gets credit for being first.
To be fair, Rob does live in the future.
Comments closedBrent Ozar has some tips if you use RECOMPILE hints frequently:
The first query’s plan stuck around in memory, so it now shows 2 executions, and 2 total rows returned. Its row metrics are correct through the life of the stored procedure’s time in cache.
However, the second query – the one with the recompile hint – has a brand new plan in the cache, but also new metrics. You’re not just recompiling the execution plan, but you’re also not getting query plan metrics here. (That’s fine, and that part I was also kinda aware of.)
But the part that I keep forgetting is that when I’m looking at the stored procedure’s totals in sp_BlitzCache, the total, min, and max values are useless:
If the plan cache isn’t going to help, what will? Brent tells you exactly what.
Comments closedJason Brimhall takes us through some of the internals of Query Store as exposed by Extended Events:
One of my favorite questions to ask during some of presentations on XE is “What was the first version of SQL Server to have Query Store?” You can imagine the wide array of answers but what is interesting is how often the correct answer is always missed. I hear lots of answers for 2012, some answers for 2017 and somewhere in between for 2016. But never does the correct answer pop up.
Right now, I hope you are scratching your head at that last statement. You see, the question is somewhat of a trick question. The first version of SQL Server that has QDS is SQL Server 2014. However, the first version where you can actually use it is SQL Server 2016. This fun fact is visible when we start exploring SQL Server from the realm of XE. Let’s take a look.
Read the whole thing.
Comments closedJohn Sterrett shows us an unorthodox use of Query Store:
Today, I wanted to talk about my least favorite part of replaying workloads. It’s having an extended event or server-side trace running during a workload replay only so we can compare the results at a query-level when the replay is finished. Now, this might seem like a trivial thing but when you have workloads over 10k batch requests/sec this can consume terabytes of data quickly. The worst part is waiting to read all the data, slice and dice the data for analysis.
Starting with SQL Server 2016 there is a better and faster way to go! You can replace your extended event or server-side trace with Query Store captured data. Today, I will show you how to use the Query Store for the same purpose.
Click through for the solution.
Comments closed