posted on Thursday, April 21, 2005 8:01 AM
by
amachanic
New white paper posted: Processing XML Showplans
This is really cool -- this white paper, Processing XML Showplans Using SQLCLR in SQL Server 2005, shows how to analyze XML showplan data before sending the actual query, in order to catch expensive queries before they bog down the server.
I can see immediate use for this in reporting scenarios, in which ad hoc queries are allowed. Instead of submitting the query and destroying server performance (as well as testing the users' patience), the data access code can now be smart enough to reject the query and return an error to the user ("go re-write your query!" -- I'm sure that will make lots of users happy). Or, perhaps, more expensive queries could be put in a queue for later, serialized execution. Many options are available -- this technique will be quite useful.
Upon further reflection (and before hitting the "Post" button), I'm forced to ask: Why is this any better than using SET SHOWPLAN_ALL ON and evaluating the estimated cost it returns? What does using XML for this task give us that using a result set doesn't?
I don't have an answer to that question. Any ideas?