optimization

SQL Server Management Studio Keyboard Shortcuts

by
Facebooktwitterredditpinterestlinkedinmail

Although some of these keyboard shortcuts may seem a little odd at first… once you start using them I think that you’ll love them and use them all the time.

Ctrl + R – Show/Hide the results pane

Ctrl + N – New query window

Ctrl + Shift + U – Switch current selection to uppercase

Ctrl + Shift + L – Switch current selection to lowercase

F5 – Execute the currently selected query (or entire query if nothing is selected)

Ctrl + Tab – Switch to the next open tab

Ctrl + Shift + Tab – Switch to the previous open tab

Ctrl + Space – Pops up IntelliSense complete if it is not already open

Ctrl + Shift + R – Refresh local IntelliSense cache

F8 – Open the Object Explorer if not already open

 

 What other SSMS keyboard shortcuts do you use?

Difference Between UNION and UNION All

by
Facebooktwitterredditpinterestlinkedinmail

The UNION operation and the UNION ALL operation perform almost the same operation.  They are both used to combine two result sets in to one result set.  The main difference between the two operations is that the UNION operation will return the unique records in the final result set.  The UNION ALL operation will return any duplicates in the final result set.

 
In the examples below, we will assume we have two tables with the following data.  Notice that the values Cat and Dog exist in both tables.
[table width=”0″ colwidth=”100|100″ colalign=”left|left”]
Table1,Table2
Alligator,Cat
Beaver,Dog
Cat,Eagle
Dog,Frog
[/table]

 
The UNION Operation

SELECT	*
FROM	Table1

UNION

SELECT	*
FROM	Table2

[table width=”0″ colwidth=”100″ colalign=”left”]
Result
Alligator
Beaver
Cat
Dog
Eagle
Frog
[/table]

Notice that the duplicate Cat and Dog values were removed from the result.

 
The UNION ALL Operation

SELECT	*
FROM	Table1

UNION	ALL

SELECT	*
FROM	Table2

[table width=”0″ colwidth=”100″ colalign=”left”]
Result
Alligator
Beaver
Cat
Dog
Cat
Dog
Eagle
Frog
[/table]

Notice that the duplicate Cat and Dog values are left in the result.

 
Speed Considerations

One final difference between UNION and UNION ALL is the speed difference.  Because the UNION operation needs to return a distinct set of values, this will take extra processing time.  A general rule is that if you know that the combined data is going to be unique already… just use the plain UNION ALL operation.  Only use the UNION operation if there are duplicates that you would like to have remove.
NOTE:  The UNION and UNION ALL operations are actually one operation.  The ALL keyword is just an attribute.  Because they are so different, I find it is easier to think of them both as separate operations.

 
Reference:  http://msdn.microsoft.com/en-us/library/ms180026.aspx
 

Show Query Execution Time

by
Facebooktwitterredditpinterestlinkedinmail

One of the limitations of SQL Server Management Studio is that it shows query execution time in seconds.  In most daily activities, this is accurate enough.  But when it comes to query permanence tuning, it is helpful to have a more accurate measurement.

Here are a 3 different ways to show you an accurate query execution time:

 
Statistics On
To have your exact query time show in the separate messages tab, all you need to do is run the following queries.

SET STATISTICS TIME ON

GO

-- Insert query here

GO

SET STATISTICS TIME OFF

GO

Then you can view the time by clicking on the Messages tab.

Show Query Execution Time

 
Include Client Statistics
To get a lot of in-depth information about your query, you can turn on Client Statistics before running your query.  The details of all the results will be shown in the Client Statistics tab after execution was completed.

Turn on client statistics by choosing the Include Client Statistics option from query menu.

show query execution time client stats

You can then view the query time by looking under the Client Statistics tab by your query results

Show Query Execution Time Client Stats

 
Inline Query Time Results
If you would like to just show the execution time in the standard results window, this query allows you to do this.  It will also allow you to show exact time of execution of a specific code segment in a query.

To use this, just place the code that you would like to get the execution time for in the center of the below script.  The exact time will be shown in the results.

DECLARE @Time1 DATETIME

DECLARE @Time2 DATETIME

SET     @Time1 = GETDATE()

-- Insert query here

SET     @Time2 = GETDATE()

SELECT  DATEDIFF(MILLISECOND,@Time1,@Time2) AS Elapsed_MS

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

Option Recompile

by
Facebooktwitterredditpinterestlinkedinmail

Option recompile is one of those hidden gems when it comes to increasing stored procedure performance.  When used in the right spot, this can speed up a never ending stored procedure call to instantaneous.  If you don’t know what option recompile is… it’s just a query hint that can be added to the end of a sql query.

What Option Recompile Does

When option recompile is added to the query hints, it does a couple things.  The first thing that it does is it tells SQL Server to discard any stored execution plan for that query.  This will cause SQL Server to rebuild the execution plan for this query.  The next thing it does is the magical part.  When SQL Server goes to build the execution plan, it will replace the local variables with the actual values in those variables.  This allows SQL Server to pull the data SUPER fast.

Symptoms

Keep in mind that many times the slowdown in a stored procedure call is just one statement inside the stored procedure.

The easiest way that I have found to figure out if this will help a slow stored procedure is to decompose the stored procedure in to separate sql statements.  I then run the sql statements to see what one is the slow one.  If I run a statement that is slow and uses a local variable, this may be a good candidate.  Next I replace the local variables in that statement with the explicit values.  If I rerun the statement and it performs fast, I know that this could use option recompile.

Syntax

DECLARE @CustomerName   VARCHAR(250)

SET     @CustomerName = "Tester"

SELECT  CustomerID,
        CustomerName,
        Address
FROM    Customer
WHERE   CustomerName = @CustomerName
OPTION  (RECOMPILE)

Rebuild Execution Plan

by
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)