Tag: Performance

Here’s a transact-SQL to show you a comparison of a compressed and uncomopressed backup from your SQL server instance.
Backup compression is a new feature in SQL Server 2008 that can help provide smaller sized backups and reduce backup time.
(Reference)

SELECT
  b.database_name 'Database Name',
  CONVERT (BIGINT, b.backup_size / 1048576 ) 'UnCompressed Backup Size (MB)',
  CONVERT (BIGINT, b.compressed_backup_size / 1048576 ) 'Compressed Backup Size (MB)',
  CONVERT (NUMERIC (20,2), (CONVERT (FLOAT, b.backup_size) /
  CONVERT (FLOAT, b.compressed_backup_size))) 'Compression Ratio',
  DATEDIFF (SECOND, b.backup_start_date, b.backup_finish_date) 'Backup Elapsed Time (sec)'
FROM
  msdb.dbo.backupset b
WHERE
  DATEDIFF (SECOND, b.backup_start_date, b.backup_finish_date) > 0
  AND b.backup_size > 0
ORDER BY
  b.backup_finish_date DESC

You can also find out compression ratio with this transact-SQL code (Reference):

SELECT backup_size/compressed_backup_size FROM msdb..backupset;

Determine if you have backup compression enabled on a database:

USE AdventureWorks2012 ;
GO
SELECT value
FROM sys.configurations
WHERE name = 'backup compression default' ;
GO

Configure backup compression and enable it on a database:

USE AdventureWorks2012;
GO
EXEC sp_configure 'backup compression default', 1 ;
RECONFIGURE WITH OVERRIDE ;
GO

Alternatively, you can use the WITH NO_COMPRESSION or WITH COMPRESSION in your BACKUP statement to override the database default.

I came across something that I found useful today when troubleshooting some query performance reports.
I won’t re-post the entire article, but here’s what I found useful and I tweaked it to my needs by ordering by avg_fragmentation_in_percent as well as only displaying results with IPS.page_counts greater than 1000 and avg_fragmentation_in_percent greater than 75%.

--To Find out fragmentation level of a given database
--This query will give DETAILED information
--CAUTION : It may take very long time, depending on the number of tables in the DB
USE foodb
GO
SELECT object_name(IPS.object_id) AS [TableName],
   SI.name AS [IndexName],
   IPS.Index_type_desc,
   IPS.avg_fragmentation_in_percent,
   IPS.avg_fragment_size_in_pages,
   IPS.avg_page_space_used_in_percent,
   IPS.record_count,
   IPS.ghost_record_count,
   IPS.fragment_count,
   IPS.avg_fragment_size_in_pages,
   IPS.page_count
FROM sys.dm_db_index_physical_stats(db_id(N'foodb'), NULL, NULL, NULL , 'DETAILED') IPS
   JOIN sys.tables ST WITH (nolock) ON IPS.object_id = ST.object_id
   JOIN sys.indexes SI WITH (nolock) ON IPS.object_id = SI.object_id AND IPS.index_id = SI.index_id
WHERE ST.is_ms_shipped = 0 and IPS.page_count > 1000 and avg_fragmentation_in_percent > 75
ORDER BY avg_fragmentation_in_percent desc
GO