RSS

Category Archives: TSQL

Table spools operator used in SQL server query plans

Table spools in SQL server are temporary result sets used by SQL server query optimizer. You might see this operator in SQL server query execution plans. Let’s see this with an example. We can create a simple test table with following script.

IF (OBJECT_ID('test') IS NOT NULL)
BEGIN
DROP TABLE test
END
GO

--Create test table 
CREATE TABLE test    
( Id INT IDENTITY(1, 1) , 
[Name] CHAR(1000) DEFAULT 'test value')          
GO   
-- insert 1000 test records 

INSERT INTO dbo.test 
DEFAULT VALUES  
GO 100

CREATE CLUSTERED INDEX IX_test_Name ON dbo.test(Name ASC)
GO

If we do an update to the test table by including actual execution plan (graphical in this case) using following query we will get following sort of graphical execution plan

-- run the update query with include actual execution plan
UPDATE dbo.test set [Name] = NEWID()

table_spool
We can see that there is a table spool in the middle of the table update. Why SQL server creates table spool here is noteworthy to understand since it has 39% cost which seems like unnecessary additional cost but actually it is a sort of protection mechanism pushed by SQL server for common problem known as “Halloween Protection”. When an update query is being executed it has two operations. One is done by read cursor which does the reading of rows to be updated and the other half is write cursor which actually does the row changes. Let’s understand this with above example, since my test table has clustered index on “Name” column and clustered index enforces physical order of the table updating the clustered index column with new values might change physical order of the table so if the read cursor and write cursor in SQL server went simultaneously write cursor might do the update more than once while traversing the physical structure of the clustered index. So to prevent that SQL server creates a temporary result set in tempdb know as table spool with the result set of the read cursor(which is eager Spool since it will block parent operation until all rows are being read) . That result set will be updated so this is the “Halloween Protection” enforced by SQL server. This issue is identified by IBM researchers more than 30 years ago.
If you drop the clustered index and run the update query again you will NOT see the table spool since you are not updating physically ordered clustered index hence no threat of “Halloween Issue”. We can confirm this with Query plan as following.

--drop the clustered index
DROP INDEX IX_test_Name ON dbo.test
GO
-- run the update query again and see execution plan
UPDATE dbo.test set [Name] = NEWID()

with no clustered index no table spool

Advertisements
 
Leave a comment

Posted by on April 25, 2013 in MS SQL Server, SQL, TSQL

 

Using SQL Fiddle to debug test SQL scriptlets

Many web developers use online web parsers like JSFiddle(http://jsfiddle.net/) to test, debug, demonstrate etc JavaScript code. Recently I had to show some simple SQL code to a student who didn’t have any DBMS system installed in his machine. I had to search similar tool to quickly demonstrate the SQL code. There is similar SQL code runner called “SQL Fiddle” in http://sqlfiddle.com/. However it might be much easier to test your SQL code in your real DBMS than running in this web tool but there might be occasions where you find this tool helpful. 

 
Leave a comment

Posted by on April 16, 2013 in SQL, TSQL

 

Truncate table command in SQL server is a efficiently logged operation

There is common misconception between developers that truncate table command is a non logged operation or minimally logged operation. Actually it is not. It is a fully logged but also efficiently logged operation. Those who are not familiar with these terms here is a quick explanation.
In SQL server, minimally logged operation is a database operation that only generates log records for data page level allocations but not for all row level changes for the selected pages. i.e. Rebuilding a index would generate log records for page level allocations in the transactions log but not row level changes. whereas in fully logged operation it will record all row level changes in addition to page level changes(happens with delete command) in after images and before images in transaction log records. When you run Truncate table command it will deallocate all the pages associated with the selected table. Pages and the extents are deallocated which are associated with the table. But still operation is logged in transaction log only the data pages deallocation happen instantly,so it might be faster at some situations. Truncate table command is a efficiently logged operation. However if it is a large table there might be a need to get large amount of locks for the pages and hence it might be slower if you are using SQL server 2000 SP2 or earlier. This has been fixed with SQL Server 2000 SP3 and upper with deferred drop mechanism where truncate table command runs as separate steps. this solves the issue of running out of extent locks. You can experiment with the deallocations as following scripts.

--Create test table 
CREATE TABLE test
    (
      Id INT IDENTITY(1, 1) ,
      [Name] CHAR(1000) DEFAULT 'test value'
    )
    
    
GO 


-- insert 1000 test records

INSERT into dbo.test

DEFAULT VALUES 

GO 1000


-- Clear the Transaction log so wee can see later easily
CHECKPOINT
GO

-- Run truncate table 

TRUNCATE TABLE dbo.test
GO

-- See the transaction log records

SELECT * FROM  fn_dblog(NULL,NULL)

transactionlog

 
Leave a comment

Posted by on December 17, 2012 in ASP.NET, MS SQL Server, SQL, TSQL

 

Tags: ,

Are sql server table variables not being created in tempdb where as temp tables do?

Some TSQL developers and DBAs think that in SQL server table variables are created in SQL server memory where as only temporary tables created in tempdb. It is actually both these kind of temp tables are being created in tempdb. Actually you could do experiments to find out this.



SELECT  *
FROM    sys.dm_db_session_space_usage
WHERE   session_id = ( SELECT   @@SPID
                     )
GO
-- get the current date time before table creation
DECLARE @DTBeforeCreation DATETIME

SET @DTBeforeCreation = GETDATE()

--create tamp Table with 1000000 recoreds
CREATE TABLE #TestTempTable ( Id INT )

INSERT  #TestTempTable
        SELECT TOP 1000000
                ROW_NUMBER() OVER ( ORDER BY a.name ) AS Alias
        FROM    sys.objects a
                CROSS JOIN sys.objects b 
                
--see that temp table created in temp db                
SELECT  *
FROM    tempdb.sys.objects
WHERE   type = N'U'
        AND create_date >= @DTBeforeCreation               


GO
--see how much pages being in the database
SELECT  *
FROM    sys.dm_db_session_space_usage
WHERE   session_id = ( SELECT   @@SPID
                     ) 
GO 
-- get the current date time before table creation
DECLARE @DTBeforeCreation DATETIME

SET @DTBeforeCreation = GETDATE()

SELECT  @DTBeforeCreation
                     
                     
--create table variable with same amount of records                     
DECLARE @TestTableVar TABLE ( Id INT )

INSERT  @TestTableVar
        SELECT TOP 1000000
                ROW_NUMBER() OVER ( ORDER BY a.name ) AS Alias
        FROM    sys.objects a
                CROSS JOIN sys.objects b 
--see that table variable created in temp db database
SELECT  *
FROM    tempdb.sys.objects
WHERE   type = N'U'
        AND create_date >= @DTBeforeCreation  
GO                 
/*
see how much pages being in the database due to table variable creation
it proves that same amount of pages are being incremented used as in temp table creation
*/
SELECT  *
FROM    sys.dm_db_session_space_usage
WHERE   session_id = ( SELECT   @@SPID
                     )    
   
                                  
                
/*Cleanup*/
DROP TABLE #TestTempTable

Here I am checking page allocation before and after temp table and table variable creation. It proves that in both occasions same amount of pages being increased since we inserted same amount of records to the two tables. I am also recording current date time before temp table and table variable creation and checking sys.objects in tempdb database to find tables that have been created after the time record. In both occasions make sure you are the only one connected to the target test database.
We can see that even after the table variable creation it is being recorded in tempdb database.

 
Leave a comment

Posted by on September 23, 2012 in MS SQL Server, SQL, TSQL

 

Tags: ,

Using recursive query to generate incremental integer field in SQL server

There are number of ways to generate incremental integer id field in SQL server using TSQL for temp tables, DDL operations etc. Recently for research purpose I needed to generate incremental integer column for a table. I used a recursive query to do it for research purpose although there are many other trivial ways to do it too.(eg.ROW_NUMBER() function, IDENTITY column temp table method etc.)

;WITH    cte
AS ( SELECT   1 AS n
UNION ALL
SELECT   n + 1
FROM     cte /*this is the recursive call which starts the recursion*/
WHERE    n < 21 /*Terminating condition*/
)
SELECT  *
FROM    cte

Recursion in SQL server is frequently achieved by using common table expressions(there are other approaches too SQL CLR, Recursife UDF calls etc). How ever maximum recursion level in SQL server is by default 100 so if you try to get more than that you will be popped up with error saying

Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

In order to overcome that use following kind of query hint which also specify an aditional option(which is actually the query hint in this case)

;WITH    cte
AS ( SELECT   1 AS n
UNION ALL
SELECT   n + 1
FROM     cte /*this is the recursive call which starts the recursion*/
WHERE    n < 12222 /*Terminating condition*/
)
SELECT  *
FROM    cte OPTION( MAXRECURSION 32767)

Actually if you specify 0 as MAXRECURSION you can virtually expand the limit to much higher level as in following query.

;WITH    cte
AS ( SELECT   1 AS n
UNION ALL
SELECT   n + 1
FROM     cte /*this is the recursive call which starts the recursion*/
WHERE    n < 12222 /*Terminating condition*/
)
SELECT  *
FROM    cte OPTION( MAXRECURSION 0) -- specifying maxrecursion to 0

 
Leave a comment

Posted by on June 24, 2012 in MS SQL Server, SQL, TSQL

 

Tags: , ,

How to prevent table locks in sql server for long running DML commands

Every statement in SQL server by default considered as read committed transaction unless you have specified specific transaction isolation mode. So if you have to for example delete hundreds of thousands  records from a table it might put locks in your tables for long time. The way to go here is to do the long running query in separate units. Consider the following script.


CREATE TABLE #temp ( Id INT )

--this will insert dummy records for test
INSERT  #temp
( ID )
VALUES  ( 1 )
GO  10000

essentially above script will insert 10000 records in to #temp table.Lets say you need to delete all those rows .At first glance it seems very trivial to write just one

delete from #temp

table statement however this might be long running query depending your server resources much better approach in situations like these is to use followin method. (This can be applied to any DML statement) If you are using SQL server 2000 you could use following script.


-- if you are using SQL server 2000

WHILE ( EXISTS ( SELECT *
FROM   #temp ) )
BEGIN
SET ROWCOUNT 4

DELETE  FROM #temp

SET ROWCOUNT 0
END

If you are using SQL Server 2005+ you could use more sophisticated following method which uses “Top” TSQL keyword for DML statements which is not allowed in SQL server 2000.

-- if you are using SQL server 2005+
--Method 1
WHILE ( EXISTS ( SELECT *
FROM   #temp ) )
BEGIN

DELETE TOP ( 4 )
FROM    #temp

END

--Method 2
DECLARE @limit INT
SET @limit = 4

again:

DELETE TOP ( @limit )
FROM   #temp

IF @@ROWCOUNT != 0
GOTO again ;

So as you can see above methods partition the DML statements into chunks which will help other users to minimize locks.

 
Leave a comment

Posted by on June 3, 2012 in MS SQL Server, TSQL

 

Tags: ,