Last week we organized the 24 Hours of PASS event, and we watched together 24 professional webcasts about SQL Server (well, not all 24). It was great to meet people from the community and to watch the webcasts together. There was good food, great company and wonderful atmosphere.
The webcasts themselves were quite disappointing, though. Most of them were basic level. But there were still some great sessions. My favorite was Brent Ozar’s session – “SAN Basics for DBAs”. Brent did a great job explaining what are the actual components behind the storage box using a simple language that even I could understand.
In addition, I learned two new things about execution plans in this event. One of them is how to monitor actual execution plan. I always looked at the “Showplan XML” event in Profiler, which shows the estimated execution plan. I have never understood why Profiler gives me the estimated execution plan, if the event is fired at run-time. In her session – “Extracting Execution Plans” – Gail Shaw explained very clearly the various ways one can extract execution plans, including Profiler. Now I know that the “Showplan XML” event retrieves the estimated execution plan, and the “Showplan XML Statistics Profile” event retrieves the actual execution plan. Monitoring the actual execution plans and looking for differences between the estimated number of rows and the actual number of rows is extremely useful for troubleshooting slow running queries.
The problem is that, for some reason, the XML retrieved by the “Showplan XML Statistics Profile” event doesn’t contain the statement text. It contains everything else, including run-time information, but not the statement text. This is strange, because when you use the “SET STATISTICS XML ON” statement in SSMS (or click the “Include Actual Execution Plan” button), the XML returned does contain the statement text. Not having the statement text makes it almost impossible to relate the execution plan to the statement it represents, which makes the whole idea of monitoring actual execution plans quite useless. I would like to thank Noam Brezis for showing me this. Noam also opened a bug in Connect about it. Please vote.
Another thing I learned about actual execution plans was that when you look at the properties of the statement operator (the left-most operator in the graphical execution plan), there is a property called “Parameter List”, which contains all the parameters in the query, either explicit or implicit, including the compiled value and the runtime value of each parameter. This information can be really useful when troubleshooting a stored procedure that performs bad for most parameter values just because it was compiled using an exceptional parameter value. I learned that from Grant Fritchey in his session – “All About Execution Plans: Reading Execution Plans”. This session was between 02:00 and 03:00 early in the morning. It wasn’t easy to stay awake for this session, but it was worth it. Thanks, Grant!
All in all, I enjoyed this event very much. I hope those of you who attended enjoyed it as well.