Which strategy performs best? In the SSMS query pane, I hit Ctrl-A to select the whole batch. The only way to know for sure is to test all the alternatives. You’re wondering whether these hints were worth pursuing, or whether it makes more sense to create an index. The HASH GROUP hint forces it to use a Hash Match aggregation (which is hash based), and ORDER GROUP a Stream Aggregate (which is order based). You’ve read Grant Fritchey’s account of the HASH | ORDER GROUP query hints, in his Execution Plans book, which forces SQL Server to choose a particular aggregation mechanism for all aggregations in the query that are caused by GROUP BY or DISTINCT. Let’s say you have an aggregation query that is not performing as hoped. I’m not Billy Two-Brains, so I’m happy to get help where I can. To get these, I need to use Extended Events, and if I can keep the complexity at bay by means of snippets or templates, it is an easier decision to make. However, when the elapsed time is longer than the users can tolerate, or is erratic, and I can’t find an obvious fault in the logic, then I like to see the server-based I/O metrics, and to check the execution plans. It is a solid metric that is the same as the experience of the user. As a developer, I’m more at home thinking of performance in simple terms, as being the lapsed time, from the perspective of the application. It’s hypnotic, and I know it is lovely, but I don’t pretend to understand it. Why bother setting it up this way? When wrestling with XML and extended events, I sometimes feel like a goldfish staring at a television set. With a few clicks to invoke and execute the snippet, we get the execution plan for any batch, along with the SQL text and query execution statistics for every SQL statement in the batch. Behind the scenes, the snippet creates and starts an Extended Events event session, inserts and runs the text of the batch of SQL you’ve selected in SSMS, and uses XQuery on the XML event data, to return it in digestible, tabular form. This article is all about using a SQL Prompt snippet to create and run a test harness on a batch of SQL code, to provide performance data. He is a regular contributor to Simple Talk and SQLServerCentral. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.ĭespite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

Redgate sql prompt formatter batch