Rebuild Execution Plan

Facebooktwitterredditpinterestlinkedinmail

One way that SQL Server optimizes stored procedure execution time is by caching the execution plan.  This functionality is built-in to SQL Server and is done every time that you create a stored procedure.

What Is An Execution Plan?

An execution plan in SQL Server is simply the way that SQL Server will go about getting the data for the client.  When a query is executed in SQL Server, it will generate an execution plan.  This execution plan is a set of steps that is the most efficient way to get this data from the database for the user.

How Do You Know If You Need To Rebuild An Execution Plan?

Okay, this is not very scientific.  Usually what I’ve found in the past is that when a stored procedure runs fast one day… and the next day it is running slow… one cause might be that the execution plan needs to be rebuilt.

How To Rebuild An Execution Plan?

If you think that a stored procedure in your database has an execution plan that is not the most efficient, here are a couple easy ways to rebuild the execution plan.  Keep in mind that the following queries do not actually rebuild the execution plan.  They merely force the stored procedure to rebuild the execution plan next time that it is executed.

Rebuild For One Specific Stored Procedure

In the example below, CustOrderHist is the name of the stored procedure we want to rebuild the execution plan for.

EXEC sp_recompile 'CustOrderHist'

Rebuild All Execution Plans For All Stored Procedures On Your Server

NOTE: The WITH NO_INFOMSGS option will suppress a generic message that comes up after executing the FREEPROCCACHE command.

DBCC FREEPROCCACHE WITH NO_INFOMSGS

Why Does This Happen?

As data changes in your database, the data in the index will change as well.  As the statistics for an index change, what used to be the fastest way to pull data may no longer be the fastest way.

Automatic Rebuild Of The Execution Plan

Of course SQL Server does not require you to always rebuild the execution plans manually.  There are triggers in the database that will cause an automatic rebuild of the execution plan.

  • Alterations to the table, view, stored procedure, or indexes used by the stored procedure
  • Updating statistics on the table or index that the stored procedure uses
  • Dropping an index used by the stored procedure
  • A lot of inserts or deletes from a table (causing a large change in the keys)
  • Executing a stored procedure with the WITH RECOMPILE option

Reference: http://technet.microsoft.com/en-us/library/ms181647.aspx  –  sp_recompile (Transact-SQL)
Reference: http://technet.microsoft.com/en-us/library/ms174283(v=sql.105).aspx  –  DBCC FREEPROCCACHE (Transact-SQL)

How To Enable Line Numbering

Facebooktwitterredditpinterestlinkedinmail

Line numbers can be very helpful when writing/debugging code in SQL Server Management Studio. Below are the simple steps to turn on line numbering in the query window.

Before and After

Step 1

From the menu at the top, select Tools –> Options

Select Tools --> Options

Step 2

From the list on the left, choose Text Editor and then All Languages.  Then select the Line numbers check box on the right.  Choose OK to save your settings.

Line Number Options

Delete Using Join

Facebooktwitterredditpinterestlinkedinmail

The DELETE statement is one of the most fundamental statements on SQL Server.  Just about everyone knows how to use the delete statement.  But did you know that you can delete using a join?  It’s actually pretty easy.

Delete Using Join

To delete from a table using a join condition, you need to first format your statement like a regular select statement.  Instead of using a SELECT statement, you will use a DELETE statement.  After the delete command you will specify the table that you want to delete from in your join.

In the example below, we want to delete all orders for this employee.  I have aliased the Orders table with the letter O.  So my delete statement will say “DELETE O”… where O is the alias for the Orders table.  This will only delete data from the Orders table and not from the Employees table.

DELETE	O
FROM	Orders O
INNER	JOIN Employees E ON E.EmployeeID = O.EmployeeID
WHERE	E.LastName = 'Tester'

One thing to note is that you cannot delete from multiple tables this way.

Reference: http://technet.microsoft.com/en-us/library/ms189835.aspx

Sleep Command In Sql Server

Facebooktwitterredditpinterestlinkedinmail

Most programming languages have a command that allows a process to sleep (or wait) before continuing.  SQL Server is no exception.  Since SQL Server 2005 there has support for a sleep command through the use of the WAITFOR command.

Read more

How To Format A Number With Commas

Facebooktwitterredditpinterestlinkedinmail

SQL Server 2012 makes it a lot easier to format a number with commas, but let’s face it… previous versions haven’t been so nice to us.  Here are the easiest ways that I know to format a number with commas.

SQL Server 2012+

SELECT	FORMAT(1234567890, '###,###,###') AS 'FormattedNumber'

SQL Server 2008 And Previous

SELECT	REPLACE(CONVERT(VARCHAR(50), (CAST(1234567890 AS money)), 1), '.00', '') AS FormattedNumber