SQL Unit Testing Using tSQLt : Part 2

Unit Testing Experts In the last post, we went through what unit testing is, at least when discussing it in the context of SQL Server. In addition to that discussed what benefits using the open-source framework tSQLt can bring. Of course, you have skimmed studied that post diligently and now we’re all unit testing experts, right?. In this post, I would like to demonstrate how to create a very basic…

SQL Unit Testing Using tSQLt : Part 1

Unit…What? Whilst those .Net folks have been unit testing their code for many moons now, we SQL folks have been falling behind. Up until about 12 months ago, I hadn’t even heard of the idea that one could unit test SQL code. What will they think of next? Over the past 6 months or so, unit testing within SQL server has become an almost daily occurrence for me as we…

CXPACKET Is Wasting Your Memory

False Advice I will be honest here, at one point I had become a little blind to seeing CXPACKET waits on my SQL instances. As a DBA having to work across lots of different instances, you will see this quite a bit (or at least I have). Countless times had I heard phrases such as “Yeah nothing to worry about there” and “that’s just indicating parallelism is happening”. But, dear…

Indexed Views And The Benefits They Provide

Despite being a DBA for a few years now, only just have I come to appreciate the benefits Index Views can bring. If you have commonly aggregated data sets which are constantly requested, Index Views could be your new best friend. Show Me What You Can Do Let’s take a rather messy query, which has been completely and utterly fabricated by yours truly, purely for this post USE StackOverflow2010; GO…

Improve Query Performance When JOINS Use OR

Sometimes Bad Things Happen Ive seen it many times and im sure i will continue to. But sometimes, people do bad things in SQL Server. JOINS using ORs are one of them. People are inheritently lazy and i certainly fall under this umberella at times. However, being lazy rarely helps the optimiser do its best work. Using OR conditions in your JOINS is bad news bears for the optimiser most…

Batch Mode Fun

  Nuts and Bolts Deep within my little toolbox, is this little trick. Firstly, lets just set the record straight here, I don’t like little tricks, hacks and especially quick wins. However, with that said, sometimes you’re in a bind and you gotta get that query over the finishing line because that line at your desk isn’t getting any shorter (its actually Teams messages, but you get the point). So,…

Extended Events

What are extended events? Extended Events first appeared with the release of SQL Server 2008. As a successor to the bulky and somewhat resource intensive SQL Profiler and SQL Trace tool, Extended Events was a new lightweight tool which can be used as and when required to gather useful metrics and information in a much more lightweight and non intrusive manner due to its architecture. Typical use cases for Extended…

SQL Bits 2019

This year, i was lucky enough to attend SQLBits 2019 in Manchester England. This was my first time at SQL Bits and was keen to see what it had to offer, compared to other SQL events I have been to such as Data Relay. Through work, I signed up for the Thursday training day. The session I chose to attend was the brilliant Erik Darling’s session called Total Server Performance…