Measuring Query Execution Time: What Is Most Accurate

SQL Server, SQL Server 2016, SQL Server 2017, T-SQL, Tools
Probably the single most important factor when deciding which query to tune, or actively tuning a query, is how you go about measuring query execution time. SQL Server provides a number of different mechanisms (really, maybe too many) to get this done. However, all measures are not created equally. In fact, they frequently disagree with one another. Let's take a look at this odd phenomenon. Measuring Query Execution Time Before we get into all the choices and compare them, let's baseline on methodology and a query to use. Not sure why, but many people give me blow back when I say "on average, this query runs in X amount of time." The feedback goes "You can't say that. What if it was just blocking or resources or..." I get it.…
Read More

SQL Clone

Tools
Today is the launch of SQL Clone, a great new tool that helps you quickly and easily provision SQL Server databases for development and testing. Oh god, that sounds like marketing speak. To heck with that. Let me tell you why I'm so excited about SQL Clone and why I think you're going to be excited too. Once Upon a Time... Almost two years ago one of the developers here at Redgate called me over. He wanted to show off this neat trick he'd figured out. What I saw was a good-sized database, about 200gb, created on his local instance of SQL Server in about 10 seconds. Now, that's fast. Further, he showed me the files and disk space on his machine, and it was only taking up a few…
Read More

Toshiba Portege Z301-A Review

Tools
Last year I purchased a Lenovo W530. Nightmares then ensued. Nice laptop if you just want to use it as a laptop, and only a laptop. Horror show if you need to present from it. So, I replaced it with an HP... which also gave me some troubles (try presenting with your laptop bricked in front of your boss AND 100 people), but that's OK because I dropped it the next day (totally by accident, it had just worked great all day, I was happy with it, mostly). So there I was, laptop free and in need of something that would let me work, present, and be an all round road warrior. PLUS, I had just broke a laptop and was feeling bad that company would be replacing it. So…
Read More

Database in Source Control

SQL Server, Tools
Many years ago, I was working with a great DBA. Seriously, a very smart and capable guy. He told me, "We need to put the database into source control, just like app code." And I just laughed. Not because I disagreed with him. I knew he was right, but I had tried, several times, to do just that. See, I'm not really a DBA. I'm a developer. I knew that code (and all the T-SQL that describes databases is code) needed to be versioned, sourced, tracked and audited. But great googly moogly, it was not an easy thing to do. I first tried just exporting the entire database into a script and then occasionally checking that script into source control. Yay! Mission Accomplished... Well, I had a database in source…
Read More

24 Hours of PASS, Fall 2012

PASS, SQL Server, T-SQL, Tools
It's time to get your learn on again. The schedule for the Fall 24 Hours of PASS is up and ready for registration. This is the Summit preview session, so many (most, all) of the speakers are showing off some of what you can learn at their sessions at the PASS Summit 2012 itself. It looks like a pretty exciting bunch of topics given by some of the best professionals in the industry. I'll be presenting Three Ways to Identify Slow Running Queries on September 20th, 1400 GMT. This is just a sub-set of the information that I'll be presenting during my all day pre-conference seminar, Query Performance Tuning: Start to Finish. The full seminar I talk about how to measure the performance of your systems, identify which queries are…
Read More

SQL In The City: London 2012, Recap

Professional Development, Redgate Software, T-SQL, Tools
Wow! How's that for a recap? The concept for the SQL in the City events is pretty simple. Put on a free event that instructs people on SQL Server, Azure, and related technologies along with a healthy smattering of Red Gate tools. All teaching is done by some of the best people in the business (and me). This was the second event in London. The concept was launched there last year and succeeded quite well. This year the event filled it's registrations so quickly that Red Gate felt obligated to have a second day, which almost completely filled up too. There were more than 350 people in attendance on Friday, and then, on Saturday, a day off, another 250+ people showed up. That's well over 600 attendees over the two…
Read More

I’ve got 99 Problems, but a disk ain’t one

nHibernate, Object Relational Mapping, SQL Server, T-SQL, Tools
Tom LaRock has a new meme for Meme Monday. It’s all about the problems caused in your system other than disks. Thankfully, despite the title, I don’t have to list 99 separate things, only 9, but you know what, 99 is possible. I’m going to present the problems. You find the solutions on your own today. Let’s go. Recompiles I’ve seen queries so big that they take more than three minutes to compile. That’s the edge case, but as an edge case it is educational. The most important thing to remember about recompiles is that they are driven by data changes. Once a threshold is reached on any given set of statistics, all queries referencing that set of statistics gets marked for recompile. The key words and tricky phrase here…
Read More

Communication

Object Relational Mapping, SQL Server, T-SQL, Tools, Visual Studio
It sure seems like there’s a lot of miscommunication between developers and database specialists. In fact, the communication can become so poor that outright hostility between the groups is common. At the end of the day we are all working towards a common goal, to add value to whatever organization we are working for. It's a shame that we all lose sight of this commonality and create such a false dichotomy between the groups. I think there are some ways that we, as database specialists, can use to attempt to cross that gap. Prior to being suborned to the dark side, I was a developer. I had a little over 10 years experience working in VB, Java & C#. I remember, distinctly, cursing our database team for being so problematic…
Read More

Powershell to Automate SQL Compare

Tools
A question came up on the SQL Server Central Forums, how could you use Red Gate SQL Compare to automate creating scripts for all your databases at once. What’s the first thing that popped into my head? Yep, Powershell. SQL Compare Pro comes with a command line utility that allows you to automate just about everything you can do with the GUI. I like to use it when I’m setting up deployment mechanisms for systems because it makes one click deployments possible. I won’t go into all the methods of that type of automation here. For a lot more information on that topic, check out the Team-Based Development book. If you’re interested in exploring how you can use the command line, just type this: [crayon-5b75a23da4af4794644042/] Now is a good time…
Read More

Life/Work Balance

SQL Server, Tools
Technology, especially information technology, is the greatest thing to ever happen to mankind, freeing us from toil and drudgery. Technology, especially information technology, is a pernicious evil taking over our lives forcing us to work harder and longer. Depending on the time of day, the day of the week, my mood, my wife’s mood, or the direction the wind is blowing, either of these statements could be true. The fact is, I love technology and I do have to wrestle with keeping it from taking over my life, but only because I have so much fun with the toys that technology brings. You want to know how much I love toys, ask me about my Droid sometime. Pull up a chair. We’re going to be here a while. The trick…
Read More