Multi-statement table valued function and inefficient Excecution Plan

Working with Multi-statement table valued function can cause the SQL Optimizer to not estimate accurately the amount of rows. The SQL Optimizer cannot \ doesn’t know the amount of rows ,  building the execution plan on guesswork .    This can result in an  inefficient execution plan causing the Estimated number of rows = 1

Similar problems are experienced when working with Table Variables or Recursive CTEs. Read up on SQL Table variable and temporary table

This basic example illustrates the problem. Below the code , I’ve included the execution plan from Test 1 (using the select statement calling function)  and Test 2 ( a straightforward SELECT statement)

In Test 1 – the estimated Rows are 1 and in Test 2 – the estimated rows are 999. 

Avoid using Multi-statement table valued function  in these situations. Focus on creating code which allows the SQL Optimizer to make an accurate estimate .

 

use master
go 
--create a table to store the values
create table jvtest
(ID INT, 
values_insert nvarchar(100)
) 
--add rows to the table
declare @cnt INT
SET @cnt = 0 
while @cnt < 1000
begin 
  INSERT INTO jvtest 
  select @cnt, @cnt
set @cnt = @cnt + 1
end 

--create a multi statement table valued function
CREATE FUNCTION dbo.estimatedRows(@ID INT)
RETURNS @IDS TABLE
(ID    INT         NOT NULL,
 values_insert     NVARCHAR(100)         NOT NULL
)
AS
BEGIN
    INSERT INTO @IDS
    select ID,values_insert from jvtest where ID > @ID
    RETURN
END
GO


--Test 1 : execute select using the multi table valued function
select * from dbo.estimatedRows(0)

--Test 2 : execute a standard select statement
select ID,values_insert from jvtest where ID > 0


--drop the table
drop table jvtest

 

Read More

 

 

SQL Execution Plan - Comparing Estimated Rows and Actual Rows

Performance Tuning - Save hundreds of hours

How to troubleshoot a slow running query - SQL Server DBA

 

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

How to troubleshoot a slow running query in SQL Server

 “ I have a slow running query , what steps can I take to speed up the query and achieve an optimised execution plan?” .

Troubleshooting database server performance is an in-depth topic. A methodical  and repeatable approach solving the root cause is the perfect situation for any DBA.   There are different approaches to SQL Server Tuning. In reality , the root cause may not be a SQL query issue , it may be SAN  , SQL Server configuration issue or workload has suddenly increased.

But of course, you’re under pressure and the query is causing  delays for the end users.The steps below expand on earlier post :SQL Server Rapid Tuning, providing a very simple emergency approach.

The steps below are a straightforward approach to troubleshooting sub optimal SQL Execution Plans. Follow the steps and repeat as necessary.

Step 1)       Statistics – Is Auto Create and Update Statistics Enabled? If Auto Create statistics is disabled , this may indicate out of date statistics. If Disabled, than proceed to  Update Statistics – use sp_updatestats to update all statistics on the database. Run the query and check if any improvement.

Step 2)       If  Auto Create and Update Statistics is Enabled, Identify the longest running queries or highest impact queries .( If the queries have high CPU usage go to step 6). Long duration and highest IO should be a priority.

Step 3)       Place every query in the SSMS and analyse the execution plan. First – check for tables or index scans.  If large table \ index scans are occurring – progress with Query Analysis.  The Query Analysis should ask questions such as : Are all JOINS valid ? Are the JOINS returning excessive data ? Search argument validity?   Functions in predicate? 

Step 4)       If no table or index scans exist and  the query is complex , for example a large transaction managing a booking process –  check for : excessive joins, temp tables, DDL changes, sub – queries , no set based approach to writing the queries. Review  the query ,  break it down into smaller parts or analyse the JOINS to invoke a new execution plan , ensuring a similar transaction integrity is retained

Step 5)       If the query is simple and no no index \table scans exists and executing in  SSMS responds with acceptable  performance - analyse the Application and how it processes the resultset.   Ask the right question a) are just relevant results returned?  Talk to the application developers.

Step  6)       If the query is no faster in SSMS , more complex query tuning is required,  research other methods or contact a performance tuning expert

Step 7)       If in Step  2 you identified queries  with high CPU usage , analyse in SSMS for Hash Joins, Sorts, Filters.  If any of these exists , progress with Query Analysis.

Step 8)       Repeat until the problem disappears.

 

Read More

SQL Server – AUTO CREATE STATISTICS - SQL Server DBA

SQL Server – Find high impact queries with sys.dm_exec_query_stats

SQL Server execution plan as text - SQL Server DBA

SQL Server Query Optimizer and Statistics - SQL Server DBA

SQL Performance tuning - Asking the right question

SQL Server – Download the Perfstats script

Troubleshooting SQL Server environments , I need to gather data and troubleshoot SQL Server performance problems as time critical. One of the tools I use is the Perfstats Script. The scripts originally came from the SQK Server support group at Microsoft. One of main benefits is using this script is the templates capture data from different sources: SQL Trace, Blocking information, Event Logs, SQL Diag and Perfmon.

Download the scripts and begin to experiment. Once you’ve used them a few times and begin to understand which template to use -   focus on interpreting the data.

Step 1 – Navigate to http://www.codeplex.com/sqlnexus

Step 2-  Under the Feature highlights section ,  go to point 4. Click on : SQL 2005 Perf Stats Script or SQL 2008 Perf Stats

Step 3 -  Click Page Info on the navigation bar and go to the File Attachments section . Download the relevant version

 Once you’ve extracted the files – you’ll dicover a number files . predefined for usage with SQLDiag.exe

The standard templates deal with  - Performance Statistics, Blocking, Latch contention

 Before deciding which of the Perfstats Templates to use:

SQLDiagPerfStats_Trace.XML

SQLDiagPerfStats_Detailed_Trace.XML

SQLDiagReplay.xml

 

Consider what is the aim of gathering this data?

 Related Posts

 SQL Server Performance Killers

 SQL Server Performance ,memory pressure and memory usage

SQL Server Performance Checklist

SQL server – 5 things SQL Server developers should know about SQL Server

SQL Server developers and SQL Server DBAs meet somewhere in the middle of the development life cycle.  That’s the theory. In reality , with time and cost pressures , the DBA sometimes can be the last to know code is about to go live. There is a responsibility for the Developer to develop code with some understanding of the SQL Database Engine.

 The way in which Developers approach coding has a big impact on the success of the project. Scalable code should be a central principle in coding. The code may work efficiently with a controlled set of data – but how does it scale with 10x the amount of users, or how does it deal with adding a new column a after the go-live date. This is where a good DBA advises and works with Developers to apply tactics for performant SQL Server.

SQL Tuning Advisor

The SQL Tuning Advisor promises much but can also lead to problems.  No in-depth expertise is required, therefore tempting for non DBAs to run a sample workload. The Advisor typically recommends indexes and statistics. Do not just add them without considering the consequences , for example, a) Adding new indexes has an extra storage space requirement b) Extra maintenance is required . How will this impact the maintenance window? 

SQL Server TempDB – Does not retain permanent data

Tempdb is cleared out everytime there is a SQL Server restart.  Think of Tempdb as a temporary data store, for Application data and Internal operations. There is only one TempDB per SQL Server Instance – it is possible a query hitting one database supporting one application can adversely impact another application using another database on the same Instance.

TempDB performance and strategy checklist

Temporary Tables versus Table Variables

 

SQL Query Optimizer is cost based

The SQL Query Optimizer works on a cost-based model . There doesn’t appear to be any obvious measure of acceptance , apart from a comparison to other potential Execution Plans. From a developers perspective , the key to maximising the process is to write efficient code. This means not only focusing on Set- Based principles , but also working with a DBA to study  the Execution Plan .   Ensure Statistics are kept up to date – as Statistics remain a fundamental part of Optimization.

Query Hints are a powerful tool, but be wary of using them. Generally , if the SQL Server is maintained properly , it will choose  a suitable Execution Plan. Generally , I don’t recommend them as there is always potential to create new problems.

 SQL Server Query Optimizer and Statistics

Transact-SQL and the xQuery language

Transact-SQL (T-SQL) is fundamental in using SQL Server. All types of applications use SQL server , but they all use T-SQL to communicate.

Querying xml data requires using the XQuery language. XQuery is used for Structured and semi structured xml data.

Avoid using XML for storing and retrieving data.  Based on experience the XML data type is very slow.

Love your DBA

A good DBA will work hard to monitor and maintain the Database Server. In the background , they will usually be fixing many small bugs and issues that can topple the server .  Database Servers are part of a large IT ecosystem – when you consider SAN storage, backups, high availability, monitoring, networks etc , many things can go wrong.

As the developer cultivate a good relationship with the DBA , for example , set up a review process of new code going into Production. Try not to rush code into Production because a manager is shouting . It’s in everyones interest to maintained optimized code. Contact me for any questions

Related Posts

SQL Server – DBA: Daily Routine

SQL Index Usage

SQL Server Performance Killers

DBA supertech . Specialise or generalise?

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