September 29, 2015 — Go inside PASS Summit 2015 session in this Q&A series with our presenters. In this interview, Guy Glantser takes us inside his session “How to Use Parameters like a Pro and Boost Performance”.
Q: Who is your favorite super-hero, and which SQL Server or BI super-power do you hope your session will give attendees?
Guy: My favorite super-hero is, of course, Superman, and throughout my career I always strive to be a Super DBA. My session is going to be all about DBA and developer super-powers. I’m going to show attendees how to save the day and become heroes. I’m even going to wear my own Superman costume for this session, and I’m going to demonstrate my super powers live in front of the audience!
Q: What’s your origin story? How did you become interested in working with data, and how did you take that initial interest to the expert level?
Guy: Twenty years ago, when I was in the army, we were still using a mainframe system for handling inventory. I’m talking about the days of SQL Server 6.5, before ERP systems were common. The process of managing inventory stock levels took place once a year, and it was completely manual. It involved printing tons of paper containing all the stock levels from all the locations for a list of around 10,000 items. We’re talking about something like 700,000 rows printed on paper (very small font, lots of paper). A team of five people then had to manually go over the list and calculate the total stock level for each item. Then, by applying all kinds of rules (manually), they produced a list of items for purchasing. And then, they generated purchase requests in the purchasing system (manually, of course). The whole process took around 3 weeks. I figured that this process could probably be more efficient. So I created a database in SQL Server, designed a few tables, wrote a program to load the inventory data into the database, wrote a procedure to aggregate the data, apply the business rules and produce the purchasing items, and wrote another program to automatically push them to the purchasing system. This process ran automatically once per week, and it took about 2 minutes of machine work instead of 4 months of man work. It was also much more accurate, and it saved a lot of money, not only because we freed those five people to do more important work, but also because we dramatically reduced inventory levels by adjusting weekly rather than once a year. This is when I realized the power of data, and this is when I decided that I wanted to devote my career to this and become a data super-hero.
Q: What’s your favorite data solution’s secret power—the biggest strength that most people don’t really know about or use to full advantage?
Guy: There are so many secret powers in SQL Server, and it’s really hard to choose my favorite. But if I need to choose one, then I choose Extended Events. This is a very powerful monitoring platform, much better than Profiler or SQL Trace in so many ways. You can set up event sessions for a broad range of scenarios quite easily and efficiently, and once you get used to it, the sky is the limit for what you can do with this tool. One of the things I love the most about Extended Events is the different targets that provide different functionalities, such as the histogram and the event pairing targets. But unfortunately, most SQL Server DBAs still use Profiler or SQL Trace, simply because they are not familiar with Extended Events and its secret power.
Q: What about data’s biggest kryptonite or nemesis--the biggest mistake you see data professionals make?
Guy: This is actually something I’m going to talk about and demonstrate in my session. One of the common mistakes that developers make is to change the values of parameters inside a stored procedure. For example, the application might execute a stored procedure with the value NULL in parameter @X. Then, the code inside the stored procedures performs a calculation and sets @X to something else. And eventually @X is used in a query somewhere in the stored procedure. The problem is that the optimizer generates an execution plan for the query based on the value NULL and not the actual run-time value. This mistake can kill performance, and I see it happen so many times. In my session, I’m going to show a few other alternatives for doing it wrong (which are also quite common), and a few alternative for doing it right. In many cases, by applying the techniques I’m going to show in the session, I was able to improve performance dramatically.
Q: What still excites you or trips you up in the real world when working with SQL Server or BI?
Guy: I love helping other people. I love solving complex problems. And when it comes to SQL Server, I love doing performance tuning, because in many cases it allows me to solve complex problems and help other people. I have been doing it for almost 20 years, and I’m still excited when I’m given a slow running query and asked to improve its performance. Asking me to tune a query is like giving a new toy to a kid. Whether it’s by adding a missing index, rewriting the code, or redesigning the whole process, I love that feeling of eventually reducing the query duration from 2 minutes to less than a second. I truly feels like Superman in those glory moments.
Q: What do you see as the next step after attending your session?
Guy: There are going to be a lot of takeaways from my session. Whenever I talk about this topic, people tell me, “Oh my god, I have so many things to do when I go back to the office!” So the next step after my session will be to take a rest and enjoy the week in Seattle—because there’s a good chance that you’ll have a lot of work when you get back to the office.
Find Guy on his blog at http://www.madeiradata.com/author/guyglantser/ or on Twitter @guy_glantser, and check out our other Q&As with PASS Summit 2015 speakers.