Monday, 26 April 2010

How to understand SQL Server execution plans

SQL Server Execution Plans by Grant Fritchey

Query Execution Plans are something that a DBA or TSQL developer will get to encounter pretty soon after starting to work with SQL Server. They are found in a number of guises - graphical, text or even XML. In either format they are a daunting array of information that seems really important but also pretty complex and technical. With Grant's book by your side you will soon be reading the execution plans and relating them to the query you have written and then be able to alter your script or adjust parts of the database to improve the efficiency of your queries.

The book starts of in a basic way, with worked examples that you can follow on any version of SQL Server that has an AdventureWorks database. You will work your way from the simplest of queries up in complexity to examples using JOINS and GROUP BY and then examining what indexes are being used (or possibly more importantly, why they are not being used).

The book also covers how to see what the execution plans are and also how to control the way the execution plan is created with HINTS so that it is compiled the way you want it if you have a preference for a certain method.

This book is something that I will be referring to regularly as I build up my understanding of SQL Server Execution Plans. An excellent cover-to-cover read and a great reference book.

There are various versions of the book:
electronic version available from Simple Talk Publishing - SQL Server Execution Plans - eVersion
in print from - SQL Server Execution Plans- in print
and it should be available soon via I will put a link here when it is.

Please be sure to read the code disclaimer page before you use any code in this blog.

Sunday, 18 April 2010


Date: Friday 16th April 2010
Location: Church House Westminster
Event: SQLBits VI

Having been to one SQLBits previously I was very keen to get registered and attend this event. This time I was accompanied by my wife (she's also a DBA/Developer so it wasn't a case of having to fund a shopping trip too!) and travelling from Devon meant that we stayed over in London for the previous night. The accommodation as recommended by SQLBits at The Grange Rochester was excellent, I had hoped it was a spelling mistake and we were at The Dorchester but sadly that wasnt the case!

We had a relaxed start to the day and walked for little more than 10 minutes to get to the venue. We easily found our way to the registration area and got our goody bags (more on them later) and then went on towards the exhibition hall. We had plenty of time to grab a coffee & sausage sandwich, meet with some people from last SQLBits and check out a couple of sponsors stands before the first session of the day.

Our session plan for the day was:
Monitoring and Tuning Parallel Query Execution - R Meyyappan - link
Inside the SQL Server Query Optimizer - C Cunnigham - link
Performance and Scalability through normalisation and set theory - T Rogerson - link
Lunch (inc sponsored session)
Denormalisation - Having your cake and eating it - M Whitehorn and Y Ahmed - link
Optimising Tempdb and temporary object use - C Bolton - link
High Performance Functions - S Sabin - link

Monitoring and Tuning Parallel Query Execution - R Meyyappan - link
Ramesh was on fine form again and we really enjoyed his presentation and picked up some neat tips and techniques to handle situations where the query optimiser recommends (or uses) parallelism in a non-optimal way. Sadly the session was a bit rushed as the first few minutes were taken up by announcements about the day ahead and changes to sessions following the issues with flights being cancelled after the Icelandic volcano ash closed British airspace. The DVD handed out at the end of the session should fill in the gaps though.

Inside the SQL Server Query Optimizer - C Cunnigham - link
Conor was a real treat to listen to, hearing his accounts of how the Query Optimiser was built to work and how they managed to account for so many different possibilities of executing a query and then pick the best. Well, not actually the very best but it picks a pretty good one, pretty much every time - its a case of getting as close as possible in a reasonable amount of time so that actually executing the query isnt held up too long. Oh, he also mentioned that he worked on the data management views framework so make sure you take a minute to thank Conor when you next use one.

Performance and Scalability through normalisation and set theory - T Rogerson - link
Tony started out showing us what he had in mind for his talk - it filled the screen with about 30 different headings. We covered a good number of them, based around normalising data and how it can benefit the integrity of the data stored and 

Lunch (inc sponsored session - Improving database development with source control)
We attended the Red Gate sponsored lunch session where Stephanie Herr and David Simner took us through the way that all of Red Gate's SQL Toolbelt applications work together to ease the work of a DBA and provide continuous integration. We had a preview of the new SQL Source Control application that maintains database versioning details while you work in SSMS. All the scripting is done in the background and it supports SVN or TFS source control. A really neat product and they are looking for EAP volunteers, get a copy of SQL Source Control here and help them develop the application the way you want it to work.

Denormalisation - Having your cake and eating it - M Whitehorn and Y Ahmed - link
We attended this session hoping to get some tips and ideas on what would be a good policy for denormalising data and how to go about it in a structured and well ordered way. Sadly it was a session that dealt almost wholly in theory and apart from hearing an interesting discussion about what qualifies as 1NF, 2NF, 3NF and so forth we didnt get as much as we had hoped from this session.

Optimising Tempdb and temporary object use - C Bolton - link
Christian Bolton gave us a peak into the mind of a SQL Server Master with his talk about the way to optimise your server with various tempdb settings, when to take action like this and also when it is not advisable. We had details about Latches, what Trace 1118 used to do and why its no longer needed, the sizing of tempdb and how many files to have and so on.

High Performance Functions - S Sabin - link
Simon's session on using functions was the last one in the day and it started with creating a SSMS keyboard shortcut (Tools|Options|Environment|Keyboard and adding sp_helptext in the Query Shortcuts grid). Next time you highlight an object name in your script and press Ctrl + 3 - hey presto the results window opens with the results of sp_helptext for that object. Nice time saver. We then heard about how Scalar functions suck and how its better to replace them with table values functions wherever possible with the usual selection of clear examples from Simon.

When we got back to the main conference lobby the exhibitors stands had been dismantled and there was beer and pizza to keep us going while we played air hockey, giant jenga, Xbox, giant connect4 or just chatted . A short prize giving was done and then people started to drift off home.

Another great SQLBits, I would like to thank all of the organisers, speakers and sponsors for their not inconsiderable contributions to make this event happen and run as smoothly as it did.

Please be sure to read the code disclaimer page before you use any code in this blog.

Friday, 9 April 2010

Testing a database connection or Who the hell (UDL) are you?

If you work with software and databases then it wont be long before you encounter  problems connecting to SQL server (or any other data source) and there are often many reasons why you would be presented with  problems. A good way of starting to analyse your problems with no need for any specific software on a  PC is to use a UDL file.

Create a new text file on the desktop of the computer you are using. Rename this file with a udl extension, something like test.udl will be ideal. All you need do is double click this and a Data Link Properties dialog opens. For some reason this always opens on the wrong tab so make sure you select to the Provider tab and then choose the OLE DB provider applicable to your problem. Next move to the Connection tab and move through the required steps, entering a data source name, whether you want to use Windows or specify SQL login details and finally the database name you wish to connect to. Once this is complete press the Test Connection button and see what results you get.

You may get a 'test succeeded' message in which case there is no reason why any other means of connecting from this computer wont work and if they do then its something wrong with the settings in the other application you are using. Alternatively you may get a message detailing why the connection failed. This is the key, you get a message detailing the issue and this lets you investigate and resolve your problem.

Remember to delete the udl file once you have used it as, if you have entered a username and password then this is saved in plain text with no security and could pose a security risk. All that is required is for the udl file to be renamed to txt and the username and password can be accessed by notepad or any other text reading software.

Please be sure to read the code disclaimer page before you use any code in this blog.


Bookmark and Share
Google Analytics Alternative