SQL sp_executesql versus EXECUTE

Question: Could you explain the difference between sql sp_executesql and EXECUTE (EXEC) ?

Answer:  There are a number of similarities and differences of SQL sp_executesql and EXEC. Let’s start by looking at the SQL Server BOL definitions:

Sp_executesql = “Executes a Transact-SQL statement or batch that can be reused many times, or one that has been built dynamically. The Transact-SQL statement or batch can contain embedded parameters”

EXECUTE = “Executes a command string or character string within a Transact-SQL batch, or one of the following modules: system stored procedure, user-defined stored procedure, scalar-valued user-defined function, or extended stored procedure.

 Notes on sp_executesql

1) The ad-hoc T-SQL statements are paramaterized.

2) The SQL Server Optimizer reuses the execution plan generated as the T-SQL appears static to the SQL Server Query Optimizer , while only the parameters are changing. This is an advantage ,helping to decrease recompilation cost and improve execution plan reuse. The disadvantage is if a “bad” plan is created , it continues to be reused at every execution

3) SQL Injection Protection - Strong typed parameters assist in defending against SQL Injection attacks.

 Notes on EXECUTE

1) Executes any statement construct. Mainly used for stored procedures , but also other character strings

2) EXECUTE does not have strong typed parameters

3) Execution Plans generated by EXECUTE aren’t always cached.

Switching from sql sp_executesql to EXECUTE is not a guarantee of improved sql performance. I’ve seen plenty of situations where the first execution created a “bad” plan. Testing and monitoring is important to optimize response times . I find wrapping up sp_executesql  in a stored procedure is an easier method to manage the  variable options, as well as offering futher benefits to be maintained as part of  a consistent code base

Read More

SQL Server - Stored Procedures versus ad-hoc paramaterized queries.Which is faster?

SQL Server - Stored Procedure Checklist - SQL Server DBA

SQL Server - How to improve Execution Plan Reuse - SQL Server DBA

SQL Server – SQL Execution plan and SET SHOWPLAN_XML – some basic tips

Since SQL Server 2005 , the SET SHOWPLAN_XML is the recommended method to gather SQL Execution Plan information. Analysing the Execution Plan offers great clues for troubleshooting and improving query performance time. 

The SET SHOWPLAN_XML  according to SQL Server BOL “Causes SQL Server not to execute Transact-SQL statements. Instead, SQL Server returns detailed information about how the statements will be  to be executed in the form of a well-defined XML document”

Things to look for:

1) Use an XML Editor – such as IE , which allows you to expand and collapse the hierarchy

2) Clustered Index Scans – Focus on estimated rows . Why does Query Optimizer choose a Clustered Index Scan?

3) Missing Indexes – allows some useful information. Basically, the optimizer suggests that the suggested index will improve performance – with an impact attribute percentage figure. Don’t  automatically create the Missing Index. First , commit a update statistics, rerun query – has anything changed?

 Second , look at the suggested index – weigh up , how often is this query used versus the  maintenance overhead . 

4) Information such as the CachedPlanSize will help you estimate if your query exceeds one page in length. This impacts how the cached plan is managed in memory. More of this topic in a future post

 

SQL Execution Plan in text

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