Tag Archives: SQL Scripts

Script to find out most CPU bound Queries

Execute below query to get TOP 25 completed queries which have the highest cumulative CPU usage

SELECT highest_cpu_queries.plan_handle, 
(highest_cpu_queries.total_worker_time/highest_cpu_queries.execution_count) AS AverageCPU, highest_cpu_queries.execution_count, highest_cpu_queries.total_worker_time, highest_cpu_queries.plan_generation_num, highest_cpu_queries.creation_time, highest_cpu_queries.last_execution_time, 
highest_cpu_queries.last_physical_reads, highest_cpu_queries.min_physical_reads,
q.dbid, q.objectid, q.number, q.encrypted, q.[text]
FROM (SELECT TOP 25 qs.plan_handle, qs.total_worker_time, qs.last_execution_time,
qs.plan_generation_num, qs.creation_time, qs.execution_count, qs.last_physical_reads,
qs.min_physical_reads FROM sys.dm_exec_query_stats qs 
ORDER BY qs.total_worker_time DESC) AS highest_cpu_queries 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q 

Above script will return the queries which are completed. You can check the active sessions and CPU details using below query.

SELECT SPID, CPU, s2.text, open_tran, status, program_name,
net_library, loginame FROM sys.sysprocesses 
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2  
where cpu > 5000 and status = 'runnable' 

How to insert value into IDENTITY column?

If you will try to insert the value into Identity column you will get the one of the below error.

Error 1:
Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table ‘Employee’ when IDENTITY_INSERT is set to OFF.

Error 2:
Error 8101 An explicit value for the identity column in table can only be specified when a column list is used and IDENTITY_INSERT is ON

Write SET IDENTITY_INSERT table name ON before the insert script and SET IDENTITY_INSERT table name Off after insert script.


use db1

create table Employee
	myID int identity(100,1),
	name varchar(20)

insert into Employee(name) values('Jugal')

--if i will try to insert the value into Identity column it will fail
insert into Employee(myID,name) values (101,'DJ')

--you can add the data into identiy column by turning on the IDENTITY_INSERT ON

	insert into Employee(myID,name) values (101,'DJ')

Transparent Data Encryption

Question: Recently I got a question regarding Transparent Data Encryption (TDE) and how it works.
Answer:Microsoft has introduced a new feature TDE (Transparent Data Encryption) from SQL Server 2008. TDE provides real time encryption of data and log files. Data is encrypted before it is written to disk; data is decrypted when it is read from disk.

Main benefit of TDE is that it does not require any changes into existing application. TDE encrypt data using AES and 3DES encryption algorithms without having to change the applications that will be connecting to the database.

With TDE backup files and database files are encrypted and no one can access it without key.

Follow below 4 steps to implement the TDE on the particular database.
• Create a master key
• Create or obtain a certificate protected by the master key
• Create a database encryption key and protect it by the certificate
• Set the database to use encryption

Create a Master Key A master key is a symmetric key that is used to create certificates and asymmetric keys. You can create the Master Key using below script

--create a master key
USE master 

Now next step is you have to create the server-based certificate which will be used to encrypt the database. You can create the certificate using below query.

--create a ceritificate
USE master;
WITH SUBJECT = 'TDE Certificate for SQL_tde database'

Here, I am creating sample database using below script, if you have already any test database you can use it.

--script to create a database
create database sql_tde

use sql_tde

--script to create sample table
create table fnInfo
	Id int primary key identity(1,1),
	CCNo varchar(16) constraint UK1 Unique,
	Name varchar(20)

--enter the sample data
insert into fnInfo values ('456333125','Jugal')
insert into fnInfo values ('1234568484','Sunil')
insert into fnInfo values ('1234789254','Chintan')

select * from fnInfo

Create a Database Encryption Key A database encryption key is required for TDE. Here we will set the encryption for the database by creating a database encryption key and password using the certificate we created in the above step.

use sql_tde
create database encryption key
with algorithm = AES_128
encryption by server certificate sql_tdeDBcert

Now we will check for the backup scenario, whether we are able to restore it without key or not. I have executed below backup script to take the database backup.

----backup database
backup database sql_tde to disk = 'c:\sql_tde.bak'

Now I am restoring the database backup on the different machine without key and certificate.

----backup database
restore database sql_tde from disk = 'c:\sql_tde.bak'

Restore command is failed with the below error on the differnet machine.

Starting up database 'sql_tde'.
Error: 3283, Severity: 16, State: 1.
The file "sql_tde_log" failed to initialize correctly. Examine the error logs for more details.  

To restore the encrypted database backup on different SQL Server instance we have to export the certificate created on the SQL instance on which the encrypted database backup was created.
You can export or transfer the certificate to a file. Use the below query for it.

USE master 
TO FILE = 'C:\sql_tdeDBcert_cert.cer' 
WITH PRIVATE KEY (FILE = 'C:\sql_tdeDBcert_key.pvk' , 

Now move the files to destination server and import the certificate using below command. Please note that the password specified here in the DECRYPTION BY PASSWORD parameter is the same as that which used to export the certificate to a file.

FROM FILE = 'C:\sql_tdeDBcert_cert.cer' 
WITH PRIVATE KEY (FILE = 'C:\sql_tdeDBcert_key.pvk',  

You can restore the database now using below query.

----backup database
restore database sql_tde from disk = 'c:\sql_tde.bak'