The Art of SQL Server Database Administration, Development, and Career Skills for the Technically Minded
Ray, when someone asks if your a God – you say YES!! – Ghostbusters. It’s day 4 – we’ve talked about hidden tricks, hotkeys, and ways to use the windows to your best advantage. Today let’s talk about really utilizing SSMS to it’s fullest.
Entering the gates of T-Sql Valhalla requires that you move past simple hotkeys and window placement. To really establish a legacy as a God like T-Sql coder you need to move into using SSMS features such as the Estimated Execution Plan window, Actual Execution Plan window and Client Statistics window. You also need to produce high quality code.
Entire books have been written on the topics of query plans, statistics, and understanding the query optimizer. If you’re reading this you either know exactly what they are or haven’t ever used them. Querying like a God requires that you use them – so I highly recommend you get to learning them now. Whether you are a DBA or a database developer; understanding query plans and the optimizer is key.
It’s important to note the source of this information is your SQL Server and the T-SQL Language not SSMS its self. These SSMS features simply provide a way to view and analyze the data easily. Why is that important? It’s helpful to know you could still obtain this information in the absence of SSMS. It just wouldn’t necessarily be as easy.
Back to SSMS. Gaining access to these SSMS viewers can be accomplished directly from the toolbar or under the Query menu item – look for the same icons on your toolbar as you see under the Query menu.
The estimated execution plan looks at your query and provides it’s best estimate as to how the query optimizer will actually process the query to obtain your results. It gives you incredible insight into optimizing your query for the best possible performance via a graphical map.
The actual execution plan button will display what it really did after the query runs. Usually estimated and actual plans are pretty close if not identical.
There is so much great content out on the web that does an excellent job of presenting the topic of understanding query plans that I will refer you elsewhere for a deep dive into that topic. Tim Ford, for example, has a great couple posts on execution plans. Jason Kassay also has a great intro level presentation on it. If you have the chance to catch it at a local SQL Saturday I highly recommend.
The query optimizer is also a fascinating and deeply related topic. Ben Nevarez is one of the leading experts on what is really going on in there. If you want to really geek out on this stuff – look into his books and presentations on the topic.
Finally, enabling Client Statistics is another great way to gain insight into the impact of your queries. When enabled this window will appear alongside your results. A typical use is to quantify how code modifications affect performance from one run to the next.
Really writing queries like a God requires far more than knowing SSMS inside and out. You need to know the T-Sql language inside and out. That is beyond the scope of this simple series on SSMS, but once you have those raw T-Sql skills – a great way to enhance them is via 3rd party tools.
3rd Party Tools
I’m not usually one for bling. My cars don’t have spinners. I never built a computer case with an internal gold fish tank or nitrogen cooled processors. Other than a wedding ring I don’t wear jewelry.
I mod up my query environment like a 1970s pimp’s wardrobe though. I love 3rd party tools. Red Gate SQL Prompt and SQL Format are indispensable in my toolbox. Apex SQL, SQLSentry, Idera, the SSMS Toolpack, Embarcadero – there are a ton of good 3rd party tools out there to enhance your query and professional database experience.
Ever had an idea for an integrated SSMS tool of your own? Stay tuned. Later on this month we’ll spend a few days doing just that. It’s on my calendar for Day 27 at the moment. That might change though.
Tomorrow we’ll talk about generating scripts. Turns out there is precious little you have to write completely from scratch any more. That will take us into the topic of leveraging pre-written code where we will cover templates and snippets.