Does Compressing A Heap/Clustered Index Rebuild Other Indexes?

No; data compression only targets what you specify.

Things to be aware of:

  • Compression locks the index, unless you have Enterprise Edition and then you can use the ONLINE option.
  • The compression process will consume space in the Data file, and it also generates transaction log records as it is a rebuild operation. The space required is only for the index being compressed so work on the index size as a guide for the Data file space being required to compress.

Heap test:

/* create table */
CREATE TABLE test_compression_heap (c1 int IDENTITY (1,1), c2 float);

/* insert some records */
INSERT INTO test_compression_heap (c2) SELECT RAND();
GO 10000

/* create non-clustered index */
CREATE NONCLUSTERED INDEX NCI_c2 ON test_compression_heap(c2);

/* view compression */
SELECT 
 OBJECT_NAME(a.object_id) AS table_name
,CASE WHEN a.index_id = 0 THEN 'heap' ELSE b.[name] END AS index_name
,b.type_desc
,a.data_compression_desc
FROM sys.partitions a, sys.indexes b
WHERE 1=1
AND OBJECT_NAME(a.[object_id]) = 'test_compression_heap'
AND a.[object_id] = b.[object_id]
AND a.index_id = b.index_id
ORDER BY index_name;

/* compress heap */
SET STATISTICS IO ON;
ALTER TABLE test_compression_heap REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE);
SET STATISTICS IO OFF;

/* view compression */
SELECT 
 OBJECT_NAME(a.object_id) AS table_name
,CASE WHEN a.index_id = 0 THEN 'heap' ELSE b.[name] END AS index_name
,b.type_desc
,a.data_compression_desc
FROM sys.partitions a, sys.indexes b
WHERE 1=1
AND OBJECT_NAME(a.[object_id]) = 'test_compression_heap'
AND a.[object_id] = b.[object_id]
AND a.index_id = b.index_id
ORDER BY index_name;

/* remove heap compression */
SET STATISTICS IO ON;
ALTER TABLE test_compression_heap REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);
SET STATISTICS IO OFF;

/* view compression */
SELECT 
 OBJECT_NAME(a.object_id) AS table_name
,CASE WHEN a.index_id = 0 THEN 'heap' ELSE b.[name] END AS index_name
,b.type_desc
,a.data_compression_desc
FROM sys.partitions a, sys.indexes b
WHERE 1=1
AND OBJECT_NAME(a.[object_id]) = 'test_compression_heap'
AND a.[object_id] = b.[object_id]
AND a.index_id = b.index_id
ORDER BY index_name;

/* compress non-clustered index */
SET STATISTICS IO ON;
ALTER INDEX NCI_c2 ON test_compression_heap REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE);
SET STATISTICS IO OFF;

/* view compression */
SELECT 
 OBJECT_NAME(a.object_id) AS table_name
,CASE WHEN a.index_id = 0 THEN 'heap' ELSE b.[name] END AS index_name
,b.type_desc
,a.data_compression_desc
FROM sys.partitions a, sys.indexes b
WHERE 1=1
AND OBJECT_NAME(a.[object_id]) = 'test_compression_heap'
AND a.[object_id] = b.[object_id]
AND a.index_id = b.index_id
ORDER BY index_name;

/* drop test table */
DROP TABLE test_compression_heap;

Clustered index test:

/* create table */
CREATE TABLE test_compression_clustered (c1 int IDENTITY (1,1), c2 float);

/* insert some records */
INSERT INTO test_compression_clustered (c2) SELECT RAND();
GO 10000

/* create clustered index */
CREATE CLUSTERED INDEX CI_c1 ON test_compression_clustered(c1);

/* create non-clustered index */
CREATE NONCLUSTERED INDEX NCI_c2 ON test_compression_clustered(c2);

/* view compression */
SELECT 
 OBJECT_NAME(a.object_id) AS table_name
,CASE WHEN a.index_id = 0 THEN 'heap' ELSE b.[name] END AS index_name
,b.type_desc
,a.data_compression_desc
FROM sys.partitions a, sys.indexes b
WHERE 1=1
AND OBJECT_NAME(a.[object_id]) = 'test_compression_clustered'
AND a.[object_id] = b.[object_id]
AND a.index_id = b.index_id
ORDER BY index_name;

/* compress clustered index */
SET STATISTICS IO ON;
ALTER INDEX CI_c1 ON test_compression_clustered REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE);
SET STATISTICS IO OFF;

/* view compression */
SELECT 
 OBJECT_NAME(a.object_id) AS table_name
,CASE WHEN a.index_id = 0 THEN 'heap' ELSE b.[name] END AS index_name
,b.type_desc
,a.data_compression_desc
FROM sys.partitions a, sys.indexes b
WHERE 1=1
AND OBJECT_NAME(a.[object_id]) = 'test_compression_clustered'
AND a.[object_id] = b.[object_id]
AND a.index_id = b.index_id
ORDER BY index_name;

/* remove heap compression */
SET STATISTICS IO ON;
ALTER INDEX CI_c1 ON test_compression_clustered REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);
SET STATISTICS IO OFF;

/* view compression */
SELECT 
 OBJECT_NAME(a.object_id) AS table_name
,CASE WHEN a.index_id = 0 THEN 'heap' ELSE b.[name] END AS index_name
,b.type_desc
,a.data_compression_desc
FROM sys.partitions a, sys.indexes b
WHERE 1=1
AND OBJECT_NAME(a.[object_id]) = 'test_compression_clustered'
AND a.[object_id] = b.[object_id]
AND a.index_id = b.index_id
ORDER BY index_name;

/* compress non-clustered index */
SET STATISTICS IO ON;
ALTER INDEX NCI_c2 ON test_compression_clustered REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE);
SET STATISTICS IO OFF;

/* view compression */
SELECT 
 OBJECT_NAME(a.object_id) AS table_name
,CASE WHEN a.index_id = 0 THEN 'heap' ELSE b.[name] END AS index_name
,b.type_desc
,a.data_compression_desc
FROM sys.partitions a, sys.indexes b
WHERE 1=1
AND OBJECT_NAME(a.[object_id]) = 'test_compression_clustered'
AND a.[object_id] = b.[object_id]
AND a.index_id = b.index_id
ORDER BY index_name;

/* drop test table */
DROP TABLE test_compression_clustered;

Below is a query to help you identify candidates for compressing.

Run in the database you would like to check.

NOTE: sp_estimate_data_compression_savings samples (up to 5000 used pages) the source object and loads this data into an equivalent table (called #sample_tableDBA05385A6FF40F888204D05C7D56D2B) and index created (called sample_indexDBA05385A6FF40F888204D05C7D56D2B) in tempdb. The table or index create in tempdb is then compressed to the requested setting and the estimated compression savings is computed.

SELECT 'EXEC sp_estimate_data_compression_savings @schema_name=''' +b.[name] + ''',@object_name=''' + a.[name] + ''',@index_id=' + CAST(c.index_id AS varchar(10)) +',@partition_number=' + CAST(c.partition_number AS varchar(10)) +',@data_compression=''PAGE'';/*indexname=' + CASE WHEN d.[name] IS NULL THEN 'heap' ELSE d.[name] END + ',size_gb=' + CAST(CAST(((d.dpages * 8) / 1024 / 1024.) AS decimal(20,2)) AS varchar(100)) + '*/'
FROM sys.tables a, sys.schemas b, sys.partitions c, sysindexes d
WHERE 1=1
AND a.[schema_id] = b.[schema_id]
AND a.[object_id] = c.[object_id]
AND a.[object_id] = d.[id]
AND c.[index_id]= d.[indid]
AND ((d.dpages * 8) / 1024 / 1024.) > 1 /* larger than 1GB */
AND c.data_compression_desc = 'NONE'
ORDER BY ((d.dpages * 8) / 1024 / 1024.) DESC;

Changing A Database Owner

A database owner is typically set to ‘sa’ to mitigate having orphaned owners when the person who created the database leaves the business.

The legacy way of changing a database owner was:

USE [databasehavingnamechanged]
GO
EXEC sp_changedbowner 'sa';
GO

The recommended SQL Server 2008 and newer way is:

USE [master]
GO
ALTER AUTHORIZATION ON DATABASE::[databasehavingnamechanged] TO [sa];
GO

Standard SQL Server Agent Alerts

The following alerts are typically setup at a minimum to keep an eye on your SQL Server.

USE [msdb]
GO
EXEC msdb.dbo.sp_add_operator @name=N'sql_alerts', 
		@enabled=1, 
		@weekday_pager_start_time=90000, 
		@weekday_pager_end_time=180000, 
		@saturday_pager_start_time=90000, 
		@saturday_pager_end_time=180000, 
		@sunday_pager_start_time=90000, 
		@sunday_pager_end_time=180000, 
		@pager_days=0, 
		@email_address=N'destinationemailaddress1@yourcompany.com;destinationemailaddress2@yourcompany.com', 
		@category_name=N'[Uncategorized]'
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev_14', 
		@message_id=0, 
		@severity=14, 
		@enabled=1, 
		@delay_between_responses=300, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Sev_14', @operator_name=N'sql_alerts', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev_15', 
		@message_id=0, 
		@severity=15, 
		@enabled=1, 
		@delay_between_responses=300, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Sev_15', @operator_name=N'sql_alerts', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev_16', 
		@message_id=0, 
		@severity=16, 
		@enabled=1, 
		@delay_between_responses=300, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Sev_16', @operator_name=N'sql_alerts', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev_17', 
		@message_id=0, 
		@severity=17, 
		@enabled=1, 
		@delay_between_responses=300, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Sev_17', @operator_name=N'sql_alerts', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev_18', 
		@message_id=0, 
		@severity=18, 
		@enabled=1, 
		@delay_between_responses=300, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Sev_18', @operator_name=N'sql_alerts', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev_19', 
		@message_id=0, 
		@severity=19, 
		@enabled=1, 
		@delay_between_responses=300, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Sev_19', @operator_name=N'sql_alerts', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev_20', 
		@message_id=0, 
		@severity=20, 
		@enabled=1, 
		@delay_between_responses=300, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Sev_20', @operator_name=N'sql_alerts', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev_21', 
		@message_id=0, 
		@severity=21, 
		@enabled=1, 
		@delay_between_responses=300, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Sev_21', @operator_name=N'sql_alerts', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev_22', 
		@message_id=0, 
		@severity=22, 
		@enabled=1, 
		@delay_between_responses=300, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Sev_22', @operator_name=N'sql_alerts', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev_23', 
		@message_id=0, 
		@severity=23, 
		@enabled=1, 
		@delay_between_responses=300, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Sev_23', @operator_name=N'sql_alerts', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev_24', 
		@message_id=0, 
		@severity=24, 
		@enabled=1, 
		@delay_between_responses=300, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Sev_24', @operator_name=N'sql_alerts', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev_25', 
		@message_id=0, 
		@severity=25, 
		@enabled=1, 
		@delay_between_responses=300, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Sev_25', @operator_name=N'sql_alerts', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'Error_823',
		@message_id=823,
		@severity=0,
		@enabled=1,
		@delay_between_responses=300,
		@include_event_description_in=1,
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Error_823', @operator_name=N'sql_alerts', @notification_method = 1;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Error_824',
		@message_id=824,
		@severity=0,
		@enabled=1,
		@delay_between_responses=300,
		@include_event_description_in=1,
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Error_824', @operator_name=N'sql_alerts', @notification_method = 1;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Error_825',
		@message_id=825,
		@severity=0,
		@enabled=1,
		@delay_between_responses=300,
		@include_event_description_in=1,
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Error_825', @operator_name=N'sql_alerts', @notification_method = 1;
GO

Identity Column Reseed Test

It’s quite common for an Identity column to be configured as an INT data type and the business is impacted when it fills-up.

Ideally the column would be changed to the BIGINT data type and the issue is resolved; however, sometimes this change is not quickly possible due to other objects using the column and/or resource constraints i.e. indexes, available disk space etc., and you have to stick with INT and transition to BIGINT at a later stage when possible.

If you need to stay with INT, the common solution is to start the reseed at -2,147,483,648 and let it work back to 0.

A test example for you is shown below:

/* create test table */
CREATE TABLE test_ident (c1 int IDENTITY (1,1), c2 float);

/* insert some records */
INSERT INTO test_ident (c2) SELECT RAND();
GO 5

/* view records */
SELECT * FROM test_ident;

/* reseed from -2,147,483,648 working back to 0*/
DBCC CHECKIDENT ('test_ident', RESEED, -2147483648); 

/* insert some records */
INSERT INTO test_ident (c2) SELECT RAND();
GO 5

/* view records */
SELECT * FROM test_ident;

/* drop test table */
DROP TABLE test_ident;

Below is an example of changing the Identity column to the BIGINT data type, if suitable for your scenario.

NOTE: A schema modify (SCH-M) lock is required on the table while the ALTER command runs.

/* create test table */
CREATE TABLE test_ident (c1 int IDENTITY (2147483647,1), c2 float);

/* try insert some records and only 1 will succeed*/
INSERT INTO test_ident (c2) SELECT RAND();
GO 5

/* view record */
SELECT * FROM test_ident

/* change indentity column to bigint */
ALTER TABLE test_ident ALTER COLUMN c1 bigint;

/* insert some records */
INSERT INTO test_ident (c2) SELECT RAND();
GO 5

/* view records */
SELECT * FROM test_ident

/* drop test table */
DROP TABLE test_ident;

NOTE: If you use SSMS to change the column type, it will drop and re-create the table. Therefore, rather use the ALTER TABLE command.

Another consideration is the impact that the reseed will have on your sort order.

Below demonstrates the effect of using a negative increasing reseed vs a negative decreasing reseed and if you need to emulate the BIGINT data type i.e. show values larger than 2,147,483,647.

/* reseed starting from -2147483648 and increasing */
DECLARE @t1 TABLE (ident int, inserted int);
INSERT INTO @t1 (ident,inserted) VALUES (1,1);
INSERT INTO @t1 (ident,inserted) VALUES (2,2);
INSERT INTO @t1 (ident,inserted) VALUES (3,3);
INSERT INTO @t1 (ident,inserted) VALUES (-2147483648,4);
INSERT INTO @t1 (ident,inserted) VALUES (-2147483647,5);
INSERT INTO @t1 (ident,inserted) VALUES (-2147483646,6);
SELECT
 ident
,inserted AS reseed_increasing
,CASE WHEN ident < 0 THEN ((2147483648 * 2) + CAST(ident AS bigint)) ELSE ident END AS emulate_bigint
FROM @t1 ORDER BY ident ASC;

/* reseed starting from -1 and decreasing */
DECLARE @t2 TABLE (ident int, inserted int);
INSERT INTO @t2 (ident,inserted) VALUES (1,1);
INSERT INTO @t2 (ident,inserted) VALUES (2,2);
INSERT INTO @t2 (ident,inserted) VALUES (3,3);
INSERT INTO @t2 (ident,inserted) VALUES (-1,4);
INSERT INTO @t2 (ident,inserted) VALUES (-2,5);
INSERT INTO @t2 (ident,inserted) VALUES (-3,6);
SELECT
 ident
,inserted AS reseed_decreasing
,CASE WHEN ident < 0 THEN 2147483647 - CAST(ident AS bigint) ELSE ident END AS emulate_bigint
FROM @t2 ORDER BY ident ASC;

Deadlock Basics

Below attempts to help with understanding deadlocks:

Objective

  • Collect both books in the order given.

Rules

  • Players cannot enter the room at the same time.
  • There are only 2 books available.
  • When a player picks up a book, they have to hold it for 1 minute before proceeding.
  • If a player cannot obtain a book without waiting, the player who entered the room last has to drop their book and restart.
Player 1Player 2
Time (HH:MM:SS)Objective: Obtain Book A and then Book B.Objective: Obtain Book B and then Book A.
10:00:00Enters the room.
10:00:01Enters the room.
10:00:05Finds Book A and picks it up.
10:00:15 Finds Book B and picks it up.
10:01:011 minute has passed. Sees that Book B is held by Player 2 and waits.
10:01:151 minute has passed. See that Player 1 has Book A. As per the rules; Player 2 acknowledges that they cannot obtain Book A without waiting and as Player 1 had entered the room first, Player 2 drops their book and exits the room (Player 2 is the DEADLOCK VICTIM).
10:01:16 Picks it up Book B.
10:01:17 Enters the room.
10:01:18 Sees that Book B is held by Player 1 and waits.
10:02:16 1 minute has passed and the objective have been achieved. Drops both books and exits the room.
10:02:17Sees that Book B is available and picks it up.
10:03:171 minute has passed and picks up Book A.
10:04:171 minute has passed and the objective has been achieved. Drops both books and exits the room.

If we now think of this in terms of SQL Server, a typical deadlock occurs as follows:

Session 1Session 2
Time (HH:MM:SS.nnn)
10:00:00.000Begins Transaction 1.
10:00:00.001 Begins Transaction 2.
10:00:00.002Starts a statement updating Table A.
10:00:00.003 Starts a statement updating Table B.
10:00:00.005Finished updating Table A and waits to update Table B.
10:00:00.007 Finished updating Table B. Needs to update Table A but cannot as it is still held by the Session 1 Transaction 1. Deadlocks, rolls back its changes and exits Transaction 2.
10:00:00.008 Starts a statement updating Table B.
10:00:00.009Session reconnects and begins Transaction 3. Sees that Table B is held by Session 1 and waits.
10:00:00.101Finished updating Table B and ends Transaction 1.
10:00:00.102 Starts a statement updating Table B.
10:00.00.106 Finished updating Table B. Starts a statement updating Table A.
10:00.00.108 Finished updating Table A and ends Transaction 3.

Essentially:

  • Multiple change Statements are operating in a single Transaction.
  • These Statements are changing more than one object.
  • While the Transaction is active, the changed objects are locked to allow for a possible rollback of the Statements.
  • Transactions with multiple change Statements are also accessing and locking objects in a different order to the other multiple Statement Transactions.

What you can try do to help reduce Deadlocks:

  • Use 1 Statement per Transaction.
  • If using multiple Statements per Transaction, try ensure that the Statements in the offending Transactions access the objects in the same order, so you end up with normal waits/blocking instead of Deadlocks.

 

Common XML Characters

When working with XML text the following common conversions are good to know.

XML Character
&amp; &
&lt; <
&gt; >
&quot;
&apos;
&#xD; Carriage Return (CR)
&#xA; Line Feed (LF)

To cast a text column which contains XML to human friendly text you could use the REPLACE function e.g.

DECLARE @xml_text nvarchar(MAX);
SET @xml_text = '&amp;   &lt;   &gt;   &quot;   &apos;   &#xD;&#xA;';

SELECT @xml_text AS xml_text;

SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@xml_text
, '&amp;' , '&')
, '&lt;'  , '<')
, '&gt;'  , '>')
, '&quot;', '"')
, '&apos;', '''')
, '&#xD;' , ' ')
, '&#xA;' , ' ') 
AS converted_text;

Windows Firewall Install Rule

Which Windows Defender Firewall profile causes the Windows Firewall rule warning?

It is the Public Profile.

If this profile is disabled, the warning does not show.

So the next question; when should the Windows firewall be disabled?

Answer: Never; unless you have other another firewall solution. Rather add an appropriate access rule.