Sql-server – Performance degraded after shrinking

performanceperformance-tuningshrinksql serversql-server-2012

I was cleaning up database (SQL Server 2012 SE) by deleting old data and truncating some unnecessary tables. After deleting and truncating data performance improved and had 67% free spaces out of 16 GB database. Then run following command to shrink database with 10% free space.

DBCC SHRINKDATABASE (databasename, 10);
GO

After that database reduced to 7GB but having performance problem, see that attach screenshot. By the way, I also rebuild indexes but no luck. Getting low application performance index (Apdex) alert from New Relic app monitoring tool.

Look at the DMV query results, at least tweenty queries taking more then 1000ms! I had only 2 queries that exceed 1000ms limit.

Best Answer

edit: Will delete this shortly or update based on answer to question. Realized OP mentioned he DID rebuild indexes already.

Shrinking a SQL Server database causes the data to get massively fragmented due to how the algorithm works. You'll want to usually presize so you don't have to shrink but don't have to worry about growths too much either. Always easier to grow a live DB with IFI than to shrink and reindex.

Paul Randal has a great post about this in depth here. You will want to reindex and check your fragmentation prior and after using this script ripped off here:

SELECT dbschemas.[name] as 'Schema', 
dbtables.[name] as 'Table', 
dbindexes.[name] as 'Index',
indexstats.alloc_unit_type_desc,
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc

Related Solutions

Sql-server – OPTION FORCE ORDER improves performance until rows are deleted

Is this normal for a forced join order to make the query estimates to be completely inaccurate (and thus query times unpredictable)?

The use of FORCE ORDER isn't making estimates inaccurate, the deletion of rows did. Forcing an update of statistics on the table may improve the estimation accuracy.

Should I just expect that I'll have to either accept sub-optimal query performance, or watch it like a hawk and frequently manually edit query hints? Or maybe hint every join as well? .3s to 2s is a big hit to take.

Preferable would be to ensure the optimiser is given the information it needs to generate the best plan, without using the FORCE ORDER hint. By doing so, it should cope better with changes to the underlying data distribution without requiring manual intervention. That said, if the nature of the data is such that cardinality could vary significantly hour by hour or day by hour, consider using a plan guide to ensure the plan is fixed.

Is it obvious why the optimizer blew up after deleting rows? For example, "yes, it took a sample scan, and because I archived most of the rows earlier in the data history the sample yielded sparse results, so it underestimated the need for a sorted hash operation"?

You didn't mention the row counts in the problem tables but it's likely that the deletions either:

didn't remove enough rows to to trigger a statistics update. This should occur when 20% of rows have been modified but there is the option to use trace flag 2371 to enable a dynamic threshold.
did trigger a statistics update but the sample gathered wasn't representative. Correct this by running a manual update WITH FULLSCAN.

You could also be running into good old fashioned parameter sniffing problems, for which there are myriad options to work around. WITH RECOMPILE might be an expensive option to specify with a query this large but it's worth investigating at both procedure and statement level.

Sql-server – UDF performance suddenly degraded

3 things I would do.

Use #temporary tables instead of @table variables. Both get materialized in tempdb, but using #temporary tables gives you greater flexibility and better information to the Query Optimizer.
Perform the COUNT in a classic GROUP BY query prior rather than using it as a windowing function. These are known to work terribly in SQL Server for certain conditions. Feel free to compare the plans generated.
Drop the usage of the functions. The expressions are very simple and can be directly included in the query

e.g.

SELECT  a.[DET_NUMBERA] ,
        a.[Name] ,
        a.[Branch] ,
        G.RowsForThisEmployee ,
        CONVERT(CHAR(10), a.[Date] +6 - (DATEPART(dw, a.[Date]) + @@DATEFIRST -2) % 7, 103) [WeekEnding],
        a.[Date] ,
        a.[Start Time] ,
        a.[End Time] ,
        a.[Duration] ,
        RIGHT(a.[Duration]/60, 10) + 'h ' + RIGHT(a.[Duration]%60,10) + 'm' [DurationF], 
        a.[EntryCount] ,
        a.[EntryColour] ,
        a.[DurationColour] ,
        a.[DurationComment]
FROM    #CompletedData a
JOIN    (SELECT   [DET_NUMBERA], COUNT(*) RowsForThisEmployee
         FROM     #CompletedData
         WHERE    a.[EntryCount] = 0
         GROUP BY [DET_NUMBERA]) G ON a.[DET_NUMBERA] = G.[DET_NUMBERA]
WHERE    a.[EntryCount] = 0
ORDER BY a.[DET_NUMBERA] ,
         a.[Date] ,
         a.[Start Time];

Best Answer

Related Solutions

Sql-server – OPTION FORCE ORDER improves performance until rows are deleted

Sql-server – UDF performance suddenly degraded

Related Question