Author: fransiscuss Page 7 of 19

Below is the sample in how to reproduce a recursive parent-child relationship and how to find out which records are the troublesome one

CREATE TABLE RecursiveSample
(
REF INT NOT NULL,
PARENT_REF INT NOT NULL,
NAME VARCHAR(255)
)
GO
/*Inserting the parents*/
INSERT INTO RecursiveSample(REF, PARENT_REF, NAME) VALUES(1, 0, ‘Parent 1’)
GO
INSERT INTO RecursiveSample(REF, PARENT_REF, NAME) VALUES(2, 0, ‘Parent 2’)
GO
INSERT INTO RecursiveSample(REF, PARENT_REF, NAME) VALUES(3, 0, ‘Parent 3’)
GO
/*Inserting the child*/
INSERT INTO RecursiveSample(REF, PARENT_REF, NAME) VALUES(4, 1, ‘Child Parent 1’)
GO
INSERT INTO RecursiveSample(REF, PARENT_REF, NAME) VALUES(5, 2, ‘Child Parent 2’)
GO
INSERT INTO RecursiveSample(REF, PARENT_REF, NAME) VALUES(6, 3, ‘Child Parent 3’)
GO
/*Inserting the grand child*/
INSERT INTO RecursiveSample(REF, PARENT_REF, NAME) VALUES(7, 4, ‘Grand Child Parent 1’)
GO
INSERT INTO RecursiveSample(REF, PARENT_REF, NAME) VALUES(8, 5, ‘Grand Child Parent 2’)
GO
INSERT INTO RecursiveSample(REF, PARENT_REF, NAME) VALUES(9, 6, ‘Grand Child Parent 3’)
GO
/*Let’s update the record to make it recursive*/
UPDATE RecursiveSample SET PARENT_REF = 7 WHERE REF = 4
GO
/*Run this query to find the Parent child structure*/
;WITH CTE
AS(
SELECT *,[PATH]=CAST(REF AS NVARCHAR(1000))FROM RecursiveSample WHERE PARENT_REF =0
UNION ALL
SELECT A.*,[PATH]=CAST(C.[PATH]+’/’+RTRIM(C.REF) AS NVARCHAR(1000))
FROM CTE C
INNER JOIN RecursiveSample A ON C.REF=A.PARENT_REF AND PATINDEX(‘/’+RTRIM(A.REF)+’/%’,’/’+C.[PATH]+’/’)=0
)
SELECT * FROM CTE
/*
REF PARENT_REF NAME PATH

How to simulate Deadlock in SQL Server, pretty simple

1. Create a table

CREATE TABLE MyDeadlockTable
(
REF INT PRIMARY KEY NOT NULL,
NAME VARCHAR(255)
)
GO

2. Populate a table with the record
INSERT INTO MyDeadlockTable(REF, NAME) VALUES(1, ‘Record 1’)
GO
INSERT INTO MyDeadlockTable(REF, NAME) VALUES(2, ‘Record 2’)
GO

3. On the first window let’s run this
BEGIN TRAN
UPDATE MyDeadlockTable set NAME=NAME WHERE REF = 1;
WAITFOR DELAY ’00:00:15′
UPDATE MyDeadlockTable set NAME=NAME WHERE REF = 2;
COMMIT

4. Open a 2nd window let’s run this
BEGIN TRAN
UPDATE MyDeadlockTable SET NAME=NAME WHERE REF = 2;
WAITFOR DELAY ’00:00:15′
UPDATE MyDeadlockTable SET NAME=NAME WHERE REF = 1;
COMMIT

I have a web app that recently timing out and the timing out exception is actually coming from the SQL Server. When I run the execution plan I found that there is one item called Table Spool (Lazy Spool) which is costing about 20%. I thought it was caused by my full text search but when I drilled down further more is because of DISTINCT keyword. So I decided to change to use GROUP BY instead. In my case it is only a single column so It won’t make any difference at all. Once I’ve changed that my web application running fast and no more timeout

I got this explanation from this website

Explain Distinct:
3) We do an all-AMPs RETRIEVE step from … by way of an
all-rows scan with no residual conditions into Spool x
(group_amps), which is redistributed by hash code to all AMPs.
Then we do a SORT to order Spool 1 by the sort key in spool field1
eliminating duplicate rows.

First there’s a redistribution, then duplicate rows are removed:
Efficient, if there are just a few rows per value [per AMP].
Spool size is always about the same, but may be extremely skewed → 2646: No more Spool Space

Explain Group By:
3) We do an all-AMPs SUM step to aggregate from … by way
of an all-rows scan with no residual conditions, and the grouping
identifier in field 1025. Aggregate Intermediate Results are
computed globally, then placed in Spool x.

First each AMP removes duplicate rows locally (first aggregate) and hashes/redistributes the resulting
rows, then there’s a second aggregation to remove duplicate rows:
Efficient, if there are lots of rows per value [per AMP].
Large number of rows per value Spool → small spool size
Small number of rows per value Spool → large spool size
Spool is never skewed.

Other interesting fact quoted from this article/discussion

http://www.simple-talk.com/sql/learn-sql-server/showplan-operator-of-the-week—lazy-spool/

http://www.sql-server-performance.com/forum/threads/table-spool-lazy-spool.15647/

INDEXING: Take a look at your indices to make sure that they’re all covering the columns that you’re selecting out of the tables. You’ll want to aim to get all the columns included in JOINs and WHERE clauses within the indices. All other columns that are in the SELECT statements should be INCLUDEd, or covered, by the index.

OPERATORS: See if you can get rid of the not equals (“<>”) operators, in favor of a single greater than or less than operator. Can this statement and T.CurrentHorizon <> 0 be changed to this and T.CurrentHorizon > 0?

JOINS: Get rid of the subqueries that are JOINing to tables outside of themselves. For instance, this line and FV2.elementId = FV.elementID might be causing some problems. There’s no reason you can’t move that out of a subquery and into a JOIN to dbo.aowCollectedFact FV, given that you’re GROUPing (DISTINCT) in the main query already.

DISTINCT: Change it to a GROUP BY. I’ve got no reason other than, because it’s good practice and takes two minutes.

LAST NOTE: The exception to all the above might be to leave the final subquery, the IF NOT EXISTS, as a subquery. If you change it to a JOIN, it’ll have to be a LEFT JOIN...WHERE NULL statement, which can actually cause spooling operations. No great way to get around that one.