3

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.

enter image description here

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

enter image description here

Improve this question
9
  • hey i answered but didn't see you already reindexed. Can you run the query found here and double check your fragmentation count is low?
    – Ali Razeghi - AWS
    Nov 21, 2016 at 20:29
  • Ali, thanks for the quick response. Max avg_fragmentation_in_percent is 95.74, page_count 94
    – Shahdat
    Nov 21, 2016 at 20:34
  • 2
    If the scale on the left is cpm, calls per minute, hasn't performance actually increased?
    – Philᵀᴹ
    Nov 22, 2016 at 0:30
  • 3
    Are you concerned about the database response times or about the newrelic alerts? Do you have any reason to believe that a .1 ms response time change triggers the alerts? You seem to have spikes in your response time chart that don't match up with newrelic alerts too.
    – Tom V
    Nov 22, 2016 at 14:38
  • 2
    You say you rebuilt the indexes? How? Did you also update statistis?
    – Tom V
    Nov 22, 2016 at 15:38

2 Answers 2

Reset to default
2

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
Improve this answer
2
  • So, removing and recreating indexes (with high fragmentation) will help?
    – Shahdat
    Nov 21, 2016 at 21:02
  • if performance degrades after shrinking, reindexing tables with high fragmentation is a very common solution, unless there are other issues present as well.
    – Ali Razeghi - AWS
    Nov 22, 2016 at 17:06
0

When you shrink your database, you are fragmenting the data file. Any database I have ever managed, always grows. So lets say I shrink a database file, not something else at the Operating System (OS) uses the disk sectors that were freed up. Now when the database has to grow, the operating system can't give the database file contiguous disk sectors anymore, so it gives it what it can.

SUMMARY: Never shrink a database file. Sure free up space, but don't shrink it.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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