Performance issues with Composite Primary keys Index fragmentation

Say you have a table with 10 columns and 3 columns (id, name, phone) as composite keys
when you have lot of updates on Name, Phone ...the index on composite key get fragmented a lot.
better
try to keep ID Primary key and have clustered index
or
keep id name then have composite key

and think of option whether to keep a constraint on Phone

Note:
you should first see how the index is getting fragmented and do some analysis , test thoroughly so that application won't break and then implement in Prod

Thanks 

REBUILD vs RE ORGANIZE INDEX = OPTION ONLINE =ON?

Just a quick note,.

 RE ORGANIZE INDEX with OPTION ONLINE =ON - is not supported
where as we can do REBUILD INDEX

Issues with SQL Database Log Backups

 I have 1 Production Server X, database Auditdb, Enterprise edition, 2014

Full backups run 12am every night(NO Differential)

T log run every 10m minutes, Today someone updated a big table without where clause and i had to restore that data, so I tried do in our UAT Server first and then do a compare or update manually...

so, in UAT when I tried to restore: 12:01:39am Full backup with NO RECOVERY --good

then started to restore all T log backups  starting from 12:10:11am, bases on scripts attached , i was able to to restore with 2 t logs with NO RECOVERY ,but failed on 3 rd t log file..

  i have attached  the error which i got , i tried to look at LSN , file names based on date and did lot of research but no luck,

can someone please take a look at it let me know what im i doing wrong?

and please provide any working script  to restore T logs in order ? 

how can i know the next T log file to restore?after Full, tlog1, tlog2 like that?

you know what i found: 

We have Alwayson Availability groups : Primary replica and  Secondary replica and i have FULL(12am daily)and Log backup(every 10mins) jobs running On primary and secondary replica after checking IF PRIMARY THEN BACKUP using maintenance plan, ELSE EXIT...

So something is happening at 12:31AM everyday on secondary ,  T log backup is being taken using NT Authority/System and also the name of the File is NULL . I looked at  all jobs  to see if there are any T-logs  but found no jobs running. I am going to run a trace to find what NT Authority/System  is doing between 12-13:45 am and also i disabled T-log backup job which i mentioned above on secondary.

Note:

Primary was always primary and no failover happened 

We have Veeam backups which kick start at 1am which does file copy not SQL style backups

No Scheduled Jobs in OS or SQL which could kick start T-log other than mine.

this issue is happening Everyday at same time on secondary replica for all databases 

    
SELECT   top 10 
        [b].[backup_start_date] ,
        [b].[backup_finish_date] ,
        [b].[type] ,
        [b].[first_lsn] ,
        [b].[last_lsn] 
FROM    [msdb].[dbo].[backupset] AS [b] 
     where[b].[backup_start_date] >= '2016-08-23 00:01:00' and database_name like '%Auditdb%'
ORDER BY [b].[backup_start_date];

 backup_start_date backup_finish_date type first_lsn last_lsn
2016-08-23 00:01:18.000 2016-08-23 00:05:06.000 D 13983000002259000001 13983000002262100001
2016-08-23 00:10:07.000 2016-08-23 00:10:07.000 L 13983000002259000001 13983000002264600001
2016-08-23 00:20:14.000 2016-08-23 00:20:14.000 L 13983000002264600001 13983000002265500001
2016-08-23 00:30:05.000 2016-08-23 00:30:05.000 L 13983000002265500001 13983000002266400001
2016-08-23 00:40:05.000 2016-08-23 00:40:05.000 L 13983000002267000001 13983000002268000001
2016-08-23 00:50:06.000 2016-08-23 00:50:06.000 L 13983000002268000001 13983000002268900001
2016-08-23 01:00:06.000 2016-08-23 01:00:06.000 L 13983000002268900001 13983000002287100001
2016-08-23 01:05:05.000 2016-08-23 01:05:05.000 L 13983000002287100001 13983000002290400001
2016-08-23 01:10:05.000 2016-08-23 01:10:05.000 L 13983000002290400001 13983000002291300001
2016-08-23 01:20:05.000 2016-08-23 01:20:05.000 L 13983000002291300001 13983000002292200001

 SECONDARY:

backup_start_date backup_finish_date type first_lsn last_lsn
2016-08-23 00:33:24.000 2016-08-23 00:33:24.000 L 13983000002266400001 13983000002267000001

THEN ::::::::

I found why LSN Chain in SQL Server Database backups is breaking on FPMC at 12:30am and on FPVI at 1am Daily.

On FPMC: 12:30am

I looked at SQL Profiler Trace data which is happening with Login name “NT AUTHORITY\SYSTEM” and  found Veeam Endpoint Backup is running  

       DECLARE @database_name_var NVARCHAR(255) SET @database_name_var = N'AuditDB' BACKUP LOG @database_name_var TO DISK = 'NUL' .

This is happening against all databases, and TRAUNCATING LOG , Breaking LSN (Backup to Nul is TRASH- No location), and should be avoided Immediately in order to have a Point in time recovery.

As per my googling  I found that Veeam does by default backup the log to the filename "nul" before it takes the VSS snapshot

We need to make some modification to Veeam Endpoint Backup and remove the step which is doing back log to nul which we don’t need,  and same thing happening on FPVI-SQL1HA at 1am

https://helpcenter.veeam.com/backup/howtosql/how2_sql_choose_bu_tool.html

https://forums.veeam.com/veeam-endpoint-backup-f33/endpoint-backup-ms-sql-backup-t27878-15.html

Fyi: Veeam Endpoint Backup is Scheduled to run Backups on FPMC at 12:30am and on FPVI at 1am Daily.

I added a (32 bit) DWORD – VSSForceOnlyCopy=1 to the following Registry location on both SQL HA Nodes. – HKLM\SOFTWARE\VEEAM\VEAM ENDPOINT BACKUP

From what I read, that should fix the issue. 

RESTORE MULTIPLE LOG backup files

DECLARE @databaseName sysname
DECLARE @backupStartDate datetime
DECLARE @backup_set_id_start INT
DECLARE @backup_set_id_end INT

-- set database to be used
SET @databaseName = 'databasename'

SELECT @backup_set_id_start = MAX(backup_set_id)
FROM msdb.dbo.backupset
WHERE database_name = @databaseName AND type = 'D'

SELECT @backup_set_id_end = MIN(backup_set_id)
FROM msdb.dbo.backupset
WHERE database_name = @databaseName AND type = 'D'
AND backup_set_id > @backup_set_id_start

IF @backup_set_id_end IS NULL SET @backup_set_id_end = 999999999

SELECT backup_set_id, 'RESTORE DATABASE ' + @databaseName + ' FROM DISK = '''
+ mf.physical_device_name + ''' WITH NORECOVERY'
FROM msdb.dbo.backupset b,
msdb.dbo.backupmediafamily mf
WHERE b.media_set_id = mf.media_set_id
AND b.database_name = @databaseName
AND b.backup_set_id = @backup_set_id_start
UNION
SELECT backup_set_id, 'RESTORE LOG ' + @databaseName + ' FROM DISK = '''
+ mf.physical_device_name + ''' WITH NORECOVERY'
FROM msdb.dbo.backupset b,
msdb.dbo.backupmediafamily mf
WHERE b.media_set_id = mf.media_set_id
AND b.database_name = @databaseName
AND b.backup_set_id >= @backup_set_id_start AND b.backup_set_id < @backup_set_id_end
AND b.type = 'L'
UNION
SELECT 999999999 AS backup_set_id, 'RESTORE DATABASE ' + @databaseName + ' WITH RECOVERY'
ORDER BY backup_set_id

ENABLE INDEX AFTER DISABLED

ALTER INDEX uci_MSpeer_originatorid_history on MSpeer_originatorid_history REBUILD

How to implement Encryption in Sensitive data column

USE master
GO
CREATE DATABASE CellLevelEncryptionDemo
GO
Use   CellLevelEncryptionDemo
GO
--create a database master key (DMK) for the master database
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'F#4*KN^7$';
GO

--create a certificate for use as the database encryption key   (DEK) protector and is protected by the DMK.
CREATE CERTIFICATE CertificateforColumns
WITH SUBJECT = 'Certificate for Column Level encryption';
GO


--Create a symmetric key and encrypt it by using above created   certificate
CREATE SYMMETRIC KEY   EncryptColumn
     WITH ALGORITHM   = AES_256 --Supported encryption algorithms are AES with 128-bit,   192?bit, or 256?bit keys or 3 Key Triple DES
     ENCRYPTION BY   CERTIFICATE CertificateforColumns;
GO


--To decrypt the encrypted data you need to use another inbuilt   function called DecryptByKey
--When you try encrypting or decrypting data without opening the symmetric key, the command will not fail but also, it will not work; for example the command given below will not fail as symmetric is not open but it will return NULLs instead of data:
--It’s very important and essential to take a backup of the keys and certificates in order to restore or attach the encrypted database on another SQL Server instance after restoring these keys\certificates there.
--Please note, enabling encryption at cell level has overhead and performance penalties (as it is a resource intensive operation and salts the data differently, which causes a table scan) as discussed above and hence it’s recommended to first evaluate the need and
--then plan for its implementation.



-- drop table Customer
--go
CREATE TABLE dbo.Customer
(
CustomerID   INT IDENTITY PRIMARY KEY,
FirstName   VARCHAR(100),
LastName   VARCHAR(100),
CreditCardNumber   Varchar(128) NULL
)
GO
 INSERT INTO dbo.Customer(FirstName,   LastName, CreditCardNumber)
VALUES
('Steve', 'Savage','1111-1111-1111-1111'),
('Ranjit', 'Srivastava','2222-2222-2222-2222'),
('Akram', 'Haque', '3333-3333-3333-3333')
GO

SELECT * from Customer
go


-----------FROM HERE Actually Steps Start

ALTER TABLE Customer
ADD DecCreditCardNumber VARBINARY(256)
GO


UPDATE Customer
SET DecCreditCardNumber = ENCRYPTBYKEY(KEY_GUID('EncryptColumn'),CreditCardNumber)
GO

--CHECK DATA
SELECT * from Customer

SELECT CustomerID,FirstName,LastName,CreditCardNumber,DecCreditCardNumber, CONVERT(VARCHAR(50),DECRYPTBYKEY(DecCreditCardNumber)) AS DecryptSecondCol
from Customer

CLOSE SYMMETRIC KEY   EncryptColumn;
GO

 --CHeck any Indexes, FK, Constraints
 --Backups

--Backup Database or table
Alter table Customer
drop Column CreditCardNumber


 Alter table Customer
 --sp_rename 'table_name.old_column_name', 'new_column_name', 'COLUMN';

 sp_RENAME 'Customer.DecCreditCardNumber' , 'CreditCardNumber', 'COLUMN'

 --CHeck any Indexes, FK, Constraints

SELECT * from Customer
go

 SELECT CustomerID,FirstName,LastName,CreditCardNumber, CONVERT(VARCHAR(50),DECRYPTBYKEY(CreditCardNumber)) AS DecryptSecondCol
from Customer








--if you're using cell encryption then you need to call the encryptby or decryptby methods for each insert\select. The app code would need to reflect this
 --Insert method: Joe need to change
Declare @CreditCardNumber Varchar(128)
SET @CreditCardNumber ='123456789087'
INSERT INTO dbo.Customer(FirstName,   LastName, CreditCardNumber)
VALUES
('Kari', 'Singh', EncryptByKey(Key_GUID('EncryptColumn'),@CreditCardNumber ))




OPEN SYMMETRIC KEY EncryptColumn DECRYPTION
BY CERTIFICATE CertificateforColumns
SELECT CustomerID,FirstName,LastName, CreditCardNumber,CONVERT(VARCHAR(50),DECRYPTBYKEY(CreditCardNumber)) AS DecryptSecondCol
from Customer

SELECT *
from Customer
CLOSE SYMMETRIC KEY   EncryptColumn;
GO

How to implement Encryption in Sensitive data column

USE master
GO
CREATE DATABASE CellLevelEncryptionDemo
GO
Use   CellLevelEncryptionDemo
GO
--create a database master key (DMK) for the master database
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'F#4*KN^7$';
GO

--create a certificate for use as the database encryption key   (DEK) protector and is protected by the DMK.
CREATE CERTIFICATE CertificateforColumns
WITH SUBJECT = 'Certificate for Column Level encryption';
GO


--Create a symmetric key and encrypt it by using above created   certificate
CREATE SYMMETRIC KEY   EncryptColumn
     WITH ALGORITHM   = AES_256 --Supported encryption algorithms are AES with 128-bit,   192?bit, or 256?bit keys or 3 Key Triple DES
     ENCRYPTION BY   CERTIFICATE CertificateforColumns;
GO


--To decrypt the encrypted data you need to use another inbuilt   function called DecryptByKey
--When you try encrypting or decrypting data without opening the symmetric key, the command will not fail but also, it will not work; for example the command given below will not fail as symmetric is not open but it will return NULLs instead of data:
--It’s very important and essential to take a backup of the keys and certificates in order to restore or attach the encrypted database on another SQL Server instance after restoring these keys\certificates there.
--Please note, enabling encryption at cell level has overhead and performance penalties (as it is a resource intensive operation and salts the data differently, which causes a table scan) as discussed above and hence it’s recommended to first evaluate the need and
--then plan for its implementation.



-- drop table Customer
--go
CREATE TABLE dbo.Customer
(
CustomerID   INT IDENTITY PRIMARY KEY,
FirstName   VARCHAR(100),
LastName   VARCHAR(100),
CreditCardNumber   Varchar(128) NULL
)
GO
 INSERT INTO dbo.Customer(FirstName,   LastName, CreditCardNumber)
VALUES
('Steve', 'Savage','1111-1111-1111-1111'),
('Ranjit', 'Srivastava','2222-2222-2222-2222'),
('Akram', 'Haque', '3333-3333-3333-3333')
GO

SELECT * from Customer
go


-----------FROM HERE Actually Steps Start

ALTER TABLE Customer
ADD DecCreditCardNumber VARBINARY(256)
GO


UPDATE Customer
SET DecCreditCardNumber = ENCRYPTBYKEY(KEY_GUID('EncryptColumn'),CreditCardNumber)
GO

--CHECK DATA
SELECT * from Customer

SELECT CustomerID,FirstName,LastName,CreditCardNumber,DecCreditCardNumber, CONVERT(VARCHAR(50),DECRYPTBYKEY(DecCreditCardNumber)) AS DecryptSecondCol
from Customer

CLOSE SYMMETRIC KEY   EncryptColumn;
GO

 --CHeck any Indexes, FK, Constraints
 --Backups

--Backup Database or table
Alter table Customer
drop Column CreditCardNumber


 Alter table Customer
 --sp_rename 'table_name.old_column_name', 'new_column_name', 'COLUMN';

 sp_RENAME 'Customer.DecCreditCardNumber' , 'CreditCardNumber', 'COLUMN'

 --CHeck any Indexes, FK, Constraints

SELECT * from Customer
go

 SELECT CustomerID,FirstName,LastName,CreditCardNumber, CONVERT(VARCHAR(50),DECRYPTBYKEY(CreditCardNumber)) AS DecryptSecondCol
from Customer








--if you're using cell encryption then you need to call the encryptby or decryptby methods for each insert\select. The app code would need to reflect this
 --Insert method: Joe need to change
Declare @CreditCardNumber Varchar(128)
SET @CreditCardNumber ='123456789087'
INSERT INTO dbo.Customer(FirstName,   LastName, CreditCardNumber)
VALUES
('Kari', 'Singh', EncryptByKey(Key_GUID('EncryptColumn'),@CreditCardNumber ))




OPEN SYMMETRIC KEY EncryptColumn DECRYPTION
BY CERTIFICATE CertificateforColumns
SELECT CustomerID,FirstName,LastName, CreditCardNumber,CONVERT(VARCHAR(50),DECRYPTBYKEY(CreditCardNumber)) AS DecryptSecondCol
from Customer

SELECT *
from Customer
CLOSE SYMMETRIC KEY   EncryptColumn;
GO

Clearing Wait Stats and Procedure Cache won’t affect Index Stats , tested and confirmed in UAT.

Clearing Wait Stats and Procedure Cache won’t affect Index Stats , tested and confirmed in UAT.

NOLOCK will propagate to the tables used by the view definition

USE AdventureWorks2008R2;
GO
CREATE VIEW EmployeeName AS
SELECT h.BusinessEntityID, p.LastName, p.FirstName
FROM HumanResources.Employee AS h With  
JOIN Person.Person AS p  
ON h.BusinessEntityID = p.BusinessEntityID;
GO

--select from view

SELECT * from EmployeeName WITH (NOLOCK)

 NOLOCK will propagate to the tables used by the view definition 

Finding Implicit Column Conversions in the Plan Cache

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @dbname SYSNAME
SET @dbname = QUOTENAME(DB_NAME());
WITH XMLNAMESPACES
   (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') SELECT
   stmt.value('(@StatementText)[1]', 'varchar(max)'),
   t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'varchar(128)'),
   t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]', 'varchar(128)'),
   t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]', 'varchar(128)'),
   ic.DATA_TYPE AS ConvertFrom,
   ic.CHARACTER_MAXIMUM_LENGTH AS ConvertFromLength,
   t.value('(@DataType)[1]', 'varchar(128)') AS ConvertTo,
   t.value('(@Length)[1]', 'int') AS ConvertToLength,
   query_plan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS batch(stmt)
CROSS APPLY stmt.nodes('.//Convert[@Implicit="1"]') AS n(t) JOIN INFORMATION_SCHEMA.COLUMNS AS ic
   ON QUOTENAME(ic.TABLE_SCHEMA) = t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'varchar(128)')
   AND QUOTENAME(ic.TABLE_NAME) = t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]', 'varchar(128)')
   AND ic.COLUMN_NAME = t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]', 'varchar(128)') WHERE t.exist('ScalarOperator/Identifier/ColumnReference[@Database=sql:variable("@dbname")][@Schema!="[sys]"]') = 1


http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/01/08/finding-implicit-column-conversions-in-the-plan-cache.aspx

Some Possible checks to do if you have any Performance issues

If some one says that some application/process is taking 1-2 days to complete.

I would check:
if that server VM or physical, CPU, memory, DISK ,Tempdb, other databases related checks and

>I/O bottlenecks
>N/W bottlenecks
>Missing, unused indexes, Bad or duplicate indexes
>Stored Procedures count and see if we can tune the SP which is running frequently and taking more time
>READ/Write STATS
>TOP WAIT STATS