indexes

SQL Server 2014 Feature – Non-Clustered Indexes For Table Variables

by
Facebooktwitterredditpinterestlinkedinmail

This is truly an amazing feature to add to SQL Server 2014. I use table variables all the time and this was the only thing I didn’t like about them. Up to this point, SQL Server did not support having non-clustered indexes on table variables (the one with the name that starts with the @). If you wanted to do this, you had to create/use a temp table (the one with the name that starts with #).

With SQL Server 2014, they changed this and now allow the table variables to have non-clustered indexes as well. To do this, we simply add a little bit of extra code after the column declaration.

DECLARE	@AnimalTableVar	TABLE
( AnimalID		INT,
  AnimalName	VARCHAR(50)	INDEX IX_AnimalTempTable_AnimalName
)

In the sample above, we create a table variable called @AnimalTableVar. Then when we declare the column AnimalName, we add an index called IX_AnimalTempTable_AnimalName.

Here is how selecting from this table shows up in the execution plan!
Non-Clustered Indexes For Table Variables

Reference: http://msdn.microsoft.com/en-us/library/ms188927(v=sql.120).aspx

SQL Server 2014 Feature – Timeout For Online Index Rebuilding

by
Facebooktwitterredditpinterestlinkedinmail

SQL Server 2014 comes with a new argument for how to handle the blocking issues that come from online index rebuilds.

 
Before SQL Server 2014

Before the existence of SQL Server 2014, if you were doing an online index rebuild… SQL Server would wait until the index rebuild could get the locks that it needs for rebuilding the index.  This can cause long running maintenance plans on very active tables.

 
SQL Server 2014 Online Index Rebuild Argument

The new functionality available in SQL Server 2014 tags on to the original index/table rebuild syntax.  It allows you to customize how you want to handle the blocking that occurs on the indexes/tables that are being rebuilt.  Here is a sample of the new syntax for managing the blocking on the index rebuild.

ALTER	INDEX ALL
ON		Animal
REBUILD	WITH
(
	SORT_IN_TEMPDB = ON,
	STATISTICS_NORECOMPUTE = OFF,
	ONLINE = ON ( WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 4 MINUTES,
									     ABORT_AFTER_WAIT = BLOCKERS ) )
)

Notice in the statement above, the new WAIT_AT_LOW_PRIORITY flag that gets added to the ONLINE argument.  When you rebuild an index in SQL Server with the WAIT_AT_LOW_PRIORITY flag turned on, it will allow other operations to proceed while the index build waits for low priority locks.  This argument comes with 2 parameters.

 
MAX_DURATION

The MAX_DURATION parameter is how long you want to wait in minutes.  It must always be in minutes.

 
ABORT_AFTER_WAIT

This parameter comes with 3 options.  This is basically who loses if the rebuild process cannot get the locks that it needs.  The connection that you choose will be terminated after the timeout period.

  • NONE – Continue waiting for the lock
  • SELF – The rebuild operation will fail
  • BLOCKERS – All transactions that are blocking the rebuild will be killed

 
If you do not specify WAIT_AT_LOW_PRIORITY with you index rebuild, then SQL Server will automatically set it to the default of 0 minutes and NONE.

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

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

 

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)