Tips to improve your index more healthy


1- Do not use more index on your table.

2- Drop unused Index .

3- Drop Duplicated index .

4- Create Clustered indexes when necessary .

5- Don’t index every column in a table .

6- primary key constrain is the best than for clustered index in more cases .

7- Order of included column does not matter .

8- use filtered index when well defined subset of results are part of select statement .

How to find an idle session with an open transaction


This Script is helpful to Developers and DBA

Let’s’s begin with my scenario

1- Create Database IO_Performance

CREATE DATABASE IO_Performance
GO

2- Create Table

USE IO_Performance

CREATE TABLE [dbo].[tblCountry](
[CountryId] [int] IDENTITY(1,1) NOT NULL,
[Code] [char](3) NOT NULL,
[Description] [varchar](50) NOT NULL)

3- Insert Data

USE IO_Performance

BEGIN TRANSACTION

INSERT INTO [dbo].[tblCountry] ([Code], [Description])
VALUES(‘ENG’, ‘ENGLAND’)

Note : My Session ID = 56

Io_Performance

 

Now open new session then run this query to now what is the idle session with an open TRAN

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT es.session_id, es.login_name, es.host_name, est.text
, cn.last_read, cn.last_write, es.program_name
FROM sys.dm_exec_sessions es
INNER JOIN sys.dm_tran_session_transactions st
ON es.session_id = st.session_id
INNER JOIN sys.dm_exec_connections cn
ON es.session_id = cn.session_id
CROSS APPLY sys.dm_exec_sql_text(cn.most_recent_sql_handle) est
LEFT OUTER JOIN sys.dm_exec_requests er
ON st.session_id = er.session_id
AND er.session_id IS NULL

io_performance_2

 

The Result is Session ID No ((56))

LOCK vs Block vs Deadlock in SQL Server


Question :

What is the main difference between Lock , Block and Deadlock in SQL Server ?

Answer :

The Meaning of lock is :

Lock is a done by database when any connection access a same piece of data concurrently. One connection need to access Piece of data .

 

The Meaning of Block :

It occurs when two connections need access to same piece of data concurrently and the meanwhile another is blocked because at a particular time, only one connection can have access. SQL knows that once the blocking process finishes the resource will be available and so the blocked process will wait (until it times out), but it won’t be killed.

 

The Meaning of Deadlock :

Deadlock occurs when one connection is blocked and waiting for a second to complete its work, and this situation is again with another process as it waiting for first connection to release the lock. Hence deadlock occurs.

Example :

i have 2 processes. P1 & P2 trying to get to 2 resources R1 & R2.
P1 gets a lock on R1
and
P2 gets a lock on R2
THEN
P1 tries to get a lock on R2 but can’t because it is locked by P2
and
P2 tries to get a lock on R1 but can’t because it is locked by P1

in this point no process can finish because they are waiting on locked resources.  they are deadlocked. One of them must be killed to allow either of them to finish.

How to Enable and disable Trace file using policy


Hello guys today i will explain how we can do policy on our SQL Server Mangment studio to mange the trace file from the Enable and Disable status

i do it Video so check it :

Disable or Enable Trace File by T-SQL


T0 Disable or Enable Trace File 0 = Disable , 1 = Enable 

 

EXEC master.dbo.sp_configure ‘allow updates’, 1;
GO
EXEC master.dbo.sp_configure ‘show advanced options’, 0;  *********************** Change here
GO
EXEC master.dbo.sp_configure ‘default trace enabled’, 0;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC master.dbo.sp_configure ‘show advanced options’, 1;
GO
EXEC master.dbo.sp_configure ‘allow updates’, 0;
GO