Monday, 29 May 2017

Top 10 expensive queries in SQL

Top 10 expensive queries in SQL

Often, DBA gets this message from the application developers and clients that their system or applications are running deadly slow. There could be several reasons for a Query or System is running slow. A few common reasons can include a temporary blocking, a network slowness or sometimes even an issue from the application end. To verify if it is a temporary blocking we can simply use this system stored-procedure- sp_who2. Upon execution of this system stored procedure we are presented with all the granular level details of the system and user processes which are running behind the scene in SQL Server. I have pasted a sample output of the execution below to get a fair idea of how the output will look like-
SPID  Status Login BlkBy DBName Command CPUTime DiskIO
1 BACKGROUND                     sa   . NULL UNKNOWN TOKEN    0 0
2 BACKGROUND                     sa   . NULL UNKNOWN TOKEN    0 0
3 BACKGROUND                     sa   . NULL UNKNOWN TOKEN    328 0
4 BACKGROUND                     sa   . NULL LOG WRITER       125 0
5 BACKGROUND                     sa   . NULL RECOVERY WRITER  78 0
6 BACKGROUND                     sa   . NULL LAZY WRITER      9765 0
7 BACKGROUND                     sa   . NULL LOCK MONITOR     15 0
8 BACKGROUND                     sa   . master SIGNAL HANDLER   0 0
9 BACKGROUND                     sa   . master BRKR TASK        0 0
10 BACKGROUND                     sa   . NULL XE DISPATCHER    31 0
11 BACKGROUND                     sa   . NULL RESOURCE MONITOR 503875 0
12 BACKGROUND                     sa   . NULL XE TIMER         1562 0
13 sleeping                       sa   . master TASK MANAGER     0 0
14 BACKGROUND                     sa   . master TRACE QUEUE TASK 31 0

The process ids which have their SPID lesser than 50 are system processes and others are user processes. If there is a block then you can see the same in blk by column of the above output. Blocking is intermittent and generally resolves in sometime once the resource is set free by the process but if it does not then we have to identify the cause and try to resolve it.

While there could be a blocking issue sometimes but most of the times the culprit will be poorly created queries. Such queries can eat up all the resources and make the system severely slow.In order to tune up those queries we first need to identify them.

Below is the Query that can help you find out the TOP 10 expensive queries in SQL Server by CPU-

SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
--ORDER BY qs.total_logical_reads DESC -- logical reads
 --ORDER BY qs.total_logical_writes DESC -- logical writes
ORDER BY qs.total_worker_time DESC -- CPU time

Once you extract the Top 10 expensive queries in SQL Server you can then work on them and tune them which in turn will boost the overall performance of your SQL Server System.

Tuesday, 23 May 2017

How to take Managed Backup in SQL Server Azure

Managed Backup in SQL Server Azure

Credential Creation in Azure portal For SQL URL backup

Using SQL Server Management Studio

1.     In Object Explorer, expand the Security folder.
2.     Right-click the Credentials folder and select New Credential….
3.     In the New Credential dialog box, in the Credential Name box, type a name for the credential.
4.     In the Identity box, type the name of the account used for outgoing connections (when leaving the context of SQL Server). Typically, this will be a Windows user account, but the identity can be an account of another type.
Alternately, click the ellipsis (…) to open the Select User or Group dialog box.
5.     In the Password and Confirm password boxes, type the password of the account specified in the Identity box. If Identity is a Windows user account, this is the Windows password. The Password can be blank, if no password is required.
6.     Click OK.

  1. Credential creation Using GUI.

Managed Backup in SQL Server Azure

  1. Using Transact-SQL

To create a credential
  1. In Object Explorer, connect to an instance of Database Engine.
  2. On the Standard bar, click New Query.
  3. Copy and paste the following example into the query window and click Execute.
 -- Creates the credential called "AlterEgo.".  
 -- The credential contains the Windows user "Mary5" and a password.   CREATE CREDENTIAL AlterEgo WITH IDENTITY = 'Mary5',  
SECRET = '<EnterStrongPasswordHere>';

1.       Script

Managed Backup in SQL Server Azure

CREATE CREDENTIAL rahul  WITH IDENTITY = 'nresourcegroupdiag929',  
   SECRET = 'YTweRUSFwydfopwd6M6t707g/D3WLFopOULICiNRz7PEnITSSgwkoRBzlsArvR+y+8tv++8xABGpA6Oxz2CpWQ==';

2.       Insert Storage Name


3.       Storage Key


Below is the snap for Storage name and Storage Key

Managed Backup in SQL Server Azure

How to add container in Sql server management studio

  1. Click on Connect tab
  2. Click on Azure Storage tab

Managed Backup in SQL Server Azure

Managed Backup in SQL Server Azure

Backup Query

Go to through below process.

Take Full Backup of Required DB using below Query.


Test container name
HR-Full Database Backup your Backup name
Note:- Change Highlited Information.

Backup Restoration Query

In below query With Move Steps are needed which we are performing in Point in Time while restoring backup.

Managed Backup in SQL Server Azure

--Restore Database

USE [master]
move 'Test' to     'F:\Test1\Test.mdf',
move 'Test_log' to       'F:\Test1\Test_log.ldf' ,CREDENTIAL = N'rahul' ,  FILE = 1,  NOUNLOAD,  STATS = 5

Note:- Change Highlited Information.

Note:- If in case while backing up database if it goes in Single User mode then use below quey to get database in Multi user mode

--Get database SINGAL USER mode to MULTI USER mode

use master
alter database Test set multi_user with rollback immediate

--To check Physical and Logical Files

restore filelistonly from url=''

Hence We have seen that Managed Backup is much easier if done step by step.