Let me say it outloud: SQL Server Execution Plan SUCKS!
In essence, SSEP (I'm too lazy to keep writing the whole name) is supposed to help you analyze how the SQL Engine performed a query (more specifically what optimization decisions were taken) in order to either alter the query and/or the tables to improve performance. As such, SSEP provides you with a nice graphical interface, that shows every element of the execution along with it's relative cost. You can then see more properties for each element, which supposedly will help you better understand it.
SSEP in SQL2005 has gone through very few changes since SQL2000, and it's a pitty. With a small query, SSEP can help you out - you can easily find the most costly element, analyze it and kill the culprit. With long and complicated queries, however, it's a nightmare. The most prominent issue here is USER EXPERIENCE!!! Let me explain:
In most cases, the first thing you do when you run SSEP is to search for the most costly sub-query and in that you look for the most costly query element. When you have a long and complicated query (which sometimes is inevitable), you can find yourself "surfing" the execution plan for many long minutes, just searching for the elements of interests, never being actually certain you got them all. You just keep scrolling up/down left/right and back again, trying to get a grasp of what's going on. There is a zooming feature, but it just isn't enough! There is no easy way to navigate through the data and search for specific elements. I mean - come on, would it have been so difficult to add, for example, a list of elements with the main properties (say object name, cost and physical and logical operation), allowing you to sort it and jump from that list to the relevant element in the execution plan?
Another USER EXPERIENCE issue is that of the data that comes along with each element. Granted, SSEP is aimed at advanced users (usually DBA level), but would it hurt someone to provide some more explanative descriptions?
Lastly, if you really want to improve the USER EXPERIENCE, you should guide the user (without forcing her hand) to find the real problem as fast as possible. For instance, you could use colors for the elements, such that costly operations would stand out (color table scan in red, index seek in green). Note that these colors should also stand out in the list mentioned above. You could even propose some solutions ad-hoc. For instance, if you see a very costly Bookmark (used to link between indexes and actual data in the tables), you can propose to add an index that will include all the fields required, effectively removing the need for a bookmark (because the engine will read the data directly from the index). There are tons of things you could do to make the user's life easier, and help her finish her job faster, so why not?!?
No comments:
Post a Comment