DevCheats – How to Write Better SQL Queries

In the first episode of DevCheats, CEO and Founder of Stackify, Matt Watson, demonstrates some tips, tricks and free tools to help write better SQL queries.

Topics for Writing Better SQL Queries can be found at:

0:35 Using SQL Statistics
0:54 Finding high query reads
1:30 CPU time vs Elapsed Time
2:18 Execution Plans
2:45 Index Seek vs Index Scan
3:34 Supratimas
4:45 ALT-F1 Shortcut
5:24 SQL Search tool

View on YouTube

Transcription:

Hello everybody this is Matt with Stackify. Now I’m not a DBA but I’ve been a developer for a long time and I’ve pretended to be a DBA many times. Today I’m going to tell you some of my favorite tips and tricks about how to write better database queries, just using some simple tools that you already have access to or free tools that you should know about.

So right off the bat here, I’m looking at a basic stored procedure. Returns a couple record sets. What I want to show you first is this SET STATISTICS IO ON and SET STATISTICS TIME ON. These are definitely things that you should know about. All you have to do is run this command and then rerun your stored procedure, or any kind of database query for that matter, then you can jump over to the messages tab here. It’ll show you all the tables that your query hit and give you some idea of how many reads it did.

So in this example, I can see my store procedure is doing 191,000 logical reads, that’s a lot of logical reads. I probably need to do some kind of database tuning. Now even if you’re not a DBA, this might be enough information for you to go and see, do I need a better index? Am I not quitting the index right way? Do I need to maybe go to my DBA and have them help me? This just gives you some basic information, is your query good or bad, at least a high level.

The SET STATISTICS TIME ON is also a really good one. Notice on the bottom right of the screen here says my query took four seconds to execute. It’s important to understand that that’s not necessarily how much CPU was used. When I enable the SET STATISTICS ON of the time. I can come in here and I can see CPU time. I can get an idea of how much actual CPU time was spent on the database, not the total elapsed time. So here you can see it shows elapsed time as well and then I can see that four seconds from SQL management studio.

A lot of times when you’re looking at reporting of slow queries from SQL server, it’s based on CPU time, not the total time. So you have to think if your query returns a lot of data, it’s going to have a bigger impact to your user than just the time name that the CPU spent, always keep that in mind. Of course, you have execution plans available to you, you can enable the execution plan and then run your query. You get a nice little break down here. This does three statements. Immediately, it tells me I’m missing an index, that’s probably why I had that over a 191,000 reads that was reporting. I could easily go create that index, rerun the query, see if I see some improvement.

The other thing that you definitely need to know about when it comes to indexes, is the difference between INDEX SEEK an INDEX SCAN. The problem I have here is this table already actually has several indexes. I’ll show you what they are a minute, but from the query plan here, I can see that it’s doing a seek, which is good. That’s what we want, but I can also see it’s doing a scan. This scan is more akin to doing like a table scan. It probably is reading every record in the table. That’s why it’s doing that hundred thousand reads. You want to avoid index scans so whenever you’re looking at execution plans, that’s one of the things to look for. Are you doing a seek or scan? If you’re using your index correctly and to the best of the performance you can, you’ll be doing a seek.

Now, sometimes execution plans are kind of hard to look at and deal with, so I recommend another tool. There’s a free tool called Supratimas and if you right click in here, you can do show execution plan as XML. You can grab this copy it and go over to their website and only have to do is is paste it in here, and it will open this this plan up. I had done this already. They also have a plug-in for SQL management studio that you can use that’s real handy. Now in Supratimas, I can easily see my three queries. Here, it it highlights you know ninety-eight percent of time was this one. I see the same thing. Right away I can a scan in here i can see the CPU time how much that was attributed to the scan. I can see my data heavy operations down here how it’s doing all these records and and how much data and had to go through. It’s just a different view. you may find this view to be easier to use then the view within SQL management studios. Just a great tool that I recommend that can be very useful for you.

So back to those indexes for a second. From our example, one of the other tricks that somebody showed me a long time ago that I’d never seen, that kind of blew my mind when I first saw it, was if you highlight a table name and SQL management studio. If you hold down alt on your keyboard and hit f1, it will show you a bunch of details about that table including all of the column names and your indexes and some other information. From this table, I can see it’s got a lot of different indexes. So maybe I just need to change my query a little bit to hit one of those indexes, or I don’t know what’s going on, but I can dig in deeper than trying to figure it out. So this alt f1 is an amazing little hidden trick in SQL management studio. If you’ve never done it, all you do is highlight the text and hit alt f1. It works perfectly now.
One of my other favorite tools is Red Gate’s SQL Search it’s a free tool from a gate they offer a lot of amazing things but the sequel searches is really nice because I can come in here and search for anything and it would go find every table stored procedure everything that has this word in it. But usually comes up and immediately it’s fast it’s amazing for trying to figure out where some certain column name is used or some text is used again that’s a free tool and Red Gate offers a lot of other great SQL tools but that SQL search, specifically the free one, and it’s awesome. Thank you for checking out How to Write Better SQL Queries. I’m Matt Watson with Stackify and I hope you’ll join me for more Dev Cheats.

Author

Alexandra

More articles by Alexandra