22 June,2012 by Jack Vamvas
Question: I want to send an SQL query Execution Plan in text to the DBA. What is the best way to create the Execution Plan?
Answer: There are three commands in SQL Server I use for SQL Server Query Tuning. They each offer different levels of information and presentation. Read up in BOL about various options such as SET NOEXEC
SET SHOWPLAN_TEXT ON|OFF
SET SHOWPLAN_ALL ON|OFF
SET SET SHOWPLAN_XML ON|OFF
I prefer SET SHOWPLAN_ALL , as it offers extra information , in a tabular format. It makes it easy to spot potential problems. I build scripts to analyse the output.
SET SHOWPLAN_TEXT is easier to read
With some training , it doesn’t take very long to understand the details offered. Typical uses include:
1) SEEK versus SCAN
2) The order of occurrence
3) Comparing query plans – “What is the most cost efficient approach to a query?”
4) Looking for clues – such as Hash Joins , that will allow query perfomance improvement