This is the first in a series of blog posts about the pre-con sessions that we are running ahead of
SQL Saturday 467 in Southampton in December 2015. Here we will be focusing on Dave Ballantyne and his
Query Performance and Internals pre-con, so, over to a few words from Dave.
Q: Dave, can we have a quick background on who you are and what you do?
A: I've been in IT for all of my working life and I guess like a lot of people in the SQL Server field almost came to it by accident.
I was working at a software company that had a database independent product and happened to be the one in the office when SQL server 6 was delivered. Fast forward a good twenty plus years and I'm still learning about the product, more specifically the optimizer, and applying those discoveries to aid my clients to streamline operations and user experiences.
Q: What do you enjoy most about training days like the one that you are delivering?
A: Along the route I've taken, there has been many “light bulb” moments, that's something that's particularly satisfying as an individual, but to see that happen in other people is amazing. Take, for instance, parameter sniffing : Its a great cost saving optimization, but when it ‘goes wrong’ it can be disastrous for a system… As you take people through the steps how and why that happens, you can almost see in their faces that they are mentally reliving problems where they know they dump the procedure cache and it solves the problem , but know they know why the problem happens.
Q: You authored a chapter on T-SQL Windowing Functions in SQL Server, how useful do you find these functions in your everyday work?
A: I've lost track of the amount of queries I've had to write that have a calculation based upon the value in a previous row, last days sales , last quarter sales etc… and tbh , before windowing functions there was not a good way of doing that in TSQL, if you *had* to do it in TSQL then there would be hideous triangular joins or a cursor, even a quirky update, now you have succinct TSQL statement that gets the job done. They are not perfect, there are lots of opportunities for the function to spool and spill to disk, but, like a lot of SQL Server, once you have visibility to the problems they can be solved, mostly
.
Q: What will be one of the key things that the attendees will take away from your session?
A: That SQL Server is not magic, its just code. To develop/design/debug/fix a system so that it performs ( and scales) well, the internals of how the SQL Server works have to be understood. For me, the most important part of that puzzle is the optimizer, you can have the best highly available SAN system in the world driven by magical unicorns that make each page of data available in a nanosecond, but if the optimizer decides to scan a 50TB table rather than use an appropriate index, the hardware magic is all for nowt. I'm often asked which stream my session sit in , DBA, DEV, BI , the answer is Yes, all of the above. If you write SELECT statements then you will gain knowledge from understanding the impact that has on the system.
So just what is in the session;
When faced with performance issues there are many lines of attack. Tuning the hardware and infrastructure can get you so far, however for maximum effect you need to understand how the engine translates your submitted SQL statements into performable actions. This is not a simple task, the number of permutations of logical operations can be immense and to perform optimally the engine has to select the ‘best one’. If you like the sounds of this then you can read the full abstract along with registration details by clicking on the button below.
Hope to see you there.
John
50628d9d-93ca-46c8-a2f3-fb6610794bb4|0|.0|96d5b379-7e1d-4dac-a6ba-1e50db561b04