3

The unthinkable happened. One of my colleagues mixed up servers and dropped my database by mistake. Then we found out that there were no backups.

I had it scripted some time ago, so most of the work wasn't lost. Data was not a problem - it was a data warehouse, so all could be restored from sources using stored procedures I had scripted previously. And here is where the problems start. Obviously, it wasn't latest version so there was a lot of small changes missing and I recovered them as best as I could from notes and from memory. Some stored procedures were saved literally minutes before disaster so they were the latest versions available.

The one used as a benchmark is inserting about 1.6M rows into a table with less than 20 columns and 3 indexes (including one clustered unique). Where previously it would take about 5 minutes (it's also updating said 1.6M rows after insert) now it takes 65 minutes (after all my performance improving tricks brought it down from 105 minutes)

Stats were updated, indexes ditto. Added or removed indexes where execution plan showed issues. Got rid of those and biggest percentage I have is 9% relative to the total exec time.

But this is where it's getting interesting: I had several bottlenecks (40+% like eager spool before index insert), which I got rid of by adding (or removing) indexes. So I was going in places of the whole query from 40+% to 0%, but elapsed time stays the same. I have now a lot of 10% clustered index scans, sorts and hash matches all over the place. But as it did not help at all I'm not sure I should spend time hammering them flat if last day did not help at all.

Can't share execution plans. Proprietary, signed NDA and all sorts of threats of prosecution between the lines. Censored query in Paste The Plan link at the bottom.

What is going on? It is literally the same query I used before, table I'm inserting into is as close to previous version as possible, sources are of the same structure, but there is 20 times worse performance.

When looking at Activity Monitor when executing query I'm seeing it suspended at CXPACKET and waiting for itself A LOT.

This got me thinking... So I've changed the ISOLATION LEVEL to READ UNCOMMITTED in query settings and execution time dropped to pre-restore, that is under 4 min.

I've added also stats read:

Table 'Pr'. Scan count 66, logical reads 11372, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Pa'. Scan count 33, logical reads 3095, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'O'. Scan count 66, logical reads 80413, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 3650075, logical reads 14106722, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PrIt'. Scan count 33, logical reads 118686, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'dwot'. Scan count 1, logical reads 1728693, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'days'. Scan count 1956045, logical reads 81317683, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TCO'. Scan count 0, logical reads 230300, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tC'. Scan count 0, logical reads 352791, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tO'. Scan count 0, logical reads 352905, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DW_CS'. Scan count 64, logical reads 1089745172, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Warning: Null value is eliminated by an aggregate or other SET operation.

Numbers in some rows are staggering, especially in case of DW_CS table, which contains only one row but has 1 BILLION logical reads...

https://www.brentozar.com/pastetheplan/?id=B1KsZOX0x

I've removed the index from the table, so 44% Table Spool is no longer there, but query performance did not change - still 56 mins.

1
  • I have not personally used this file recovery utility, but I found several positive reviews of this product which might be worth a try in recovering your deleted mdf, ldf, etc. files - piriform.com/recuva/download – Scott Hodgin - Retired Apr 12 '17 at 12:42
2

Try running this query and check if there is any performance improvements.

Added index to temp table as the rows inserted are quite high.

CREATE TABLE #tempTbl
(
    OID     INT,
    PID     INT,
    ic      INT
)

insert into #tempTbl (OID, PID, IC)
SELECT o.OID
    ,p.PID
    ,SUM(CASE 
            WHEN (pit.AC = 0)
                AND (pit.CQ > 0)
                THEN pit.CQ
            WHEN (pit.AC = 0)
                THEN 1
            ELSE pit.CQ
            END) as ic
FROM [p].[dbo].o o with (nolock) 
JOIN [p].[dbo].pr p  with (nolock) 
    ON o.orderid = p.orderid
JOIN [p].[dbo].prit pit  with (nolock) 
    ON p.pid = pit.pid
GROUP BY o.OID
    ,p.PID
order by 1

CREATE CLUSTERED INDEX IDX_tempTbl_OID ON #tempTbl(OID);
CREATE INDEX IDX_tempTbl_PID ON #Users(PID);

INSERT INTO [dbo].[dwot] (
    [OID]
    ,[OType]
    ,[CDate]
    ,[TV]
    ,[ItemCount]
    ,[PID]
    ,[StatID]
    ,[NRSID]
    ,[SysID]
    ,[CatID]
    ,[DivID]
    ,[OpDate]
    ,[EtDDate]
    ,[DDate]
    ,[OSource]
    ,[Subcat]
    )
SELECT DISTINCT 
    o.[OID]
    ,99
    ,o.[CDate]
    ,o.[OV]
    ,oic.itemcount
    ,oic.[PID]
    ,o.[StatId]
    ,pa.[NRSID]
    ,1
    ,1
    ,CASE 
        WHEN o.TPID = 17 THEN 2
        WHEN (p.PTypeId = 'UID1' OR p.PTypeId = 'UID2')
            AND (o.TPID <> 17 OR o.TPID IS NULL) THEN 4
        WHEN (p.PTypeId = 'uid3') OR (p.PTypeId = 'UID4') OR (p.PTypeId = 'UID5') THEN 1
        ELSE 3
        END
    ,CASE 
        WHEN Cast(o.[CDate] AS TIME) > '16:00:00'
            THEN (
                    SELECT TOP 1 [PKDate]
                    FROM [calendar].[dbo].days with (nolock)
                    WHERE is_weekend <> 1
                        AND is_holiday <> 1
                        AND PKDate > cast(o.[CDate] AS DATE)
                    ORDER BY PKDate ASC
                    )
        ELSE (
                SELECT TOP 1 [PKDate]
                FROM [calendar].[dbo].days with (nolock)
                WHERE is_weekend <> 1
                    AND is_holiday <> 1
                    AND PKDate >= Cast(o.[CDate] AS DATE)
                ORDER BY PKDate ASC
                )
        END
    ,CASE 
        WHEN Cast(o.[Date] AS TIME) > '16:00:00'
            THEN (
                    SELECT TOP 1 [PKDate]
                    FROM [calendar].[dbo].days with (nolock) 
                    WHERE is_weekend <> 1
                        AND is_holiday <> 1
                        AND PKDate > dateadd(day, isnull((
                                    SELECT TOP 1 [odays]
                                    FROM [dbo].[DW_CS] with (nolock)
                                    ), 2) + 1, Cast(o.[CDate] AS DATE))
                    ORDER BY PKDate ASC
                    )
        ELSE (
                SELECT TOP 1 [PKDate]
                FROM [calendar].[dbo].days with (nolock)
                WHERE is_weekend <> 1
                    AND is_holiday <> 1
                    AND PKDate > dateadd(day, isnull((
                                SELECT TOP 1 [odays]
                                FROM [dbo].[DW_CS] with (nolock)
                                ), 2), Cast(o.[CDate] AS DATE))
                ORDER BY PKDate ASC
                )
        END
    ,(SELECT dateadd(day, 3, o.[CDate]))
    ,'OSource'
    ,CASE 
        WHEN o.tpoid IS NOT NULL
            AND o.TPID = 17
            THEN (
                    SELECT TOP 1 tco.[Title]
                    FROM [PD].[dbo].[tor] tor with (nolock)
                    JOIN [PD].[dbo].[tc] tc with (nolock)
                        ON tor.[TCID] = tc.TCID
                    JOIN [PD].[dbo].[TCO] tco with (nolock)
                        ON tc.TCID = tco.TCOID
                    WHERE tor.OID = o.TPOID
                        AND o.TPID = 17
                    ORDER BY tor.DTCreated DESC
                    )
        ELSE 'N/A'
        END
FROM [P].[dbo].[O] o with (nolock)
LEFT JOIN #tempTbl oic 
    ON oic.OID = o.OID
LEFT JOIN [P].[dbo].pr p with (nolock)
    ON o.oid = p.oid
LEFT JOIN [P].[dbo].pa pa with (nolock)
    ON pa.pid = oic.pid
WHERE OID NOT IN (
        SELECT OID
        FROM [dbo].dwot dwo2 with (nolock)
        WHERE dwo2.OID = o.ID
        )
DROP TABLE #tempTbl
0

Not the answer you're looking for? Browse other questions tagged or ask your own question.