RSS

Category Archives: Performance MSSQL

SQL SERVER – Fundamentals of Columnstore Index

WoooooooooooooooooooooooooW New Feature in SQL Server 2012 ((columnstore index))
*****************************************************************
by this new future we can improve the enhancement of the query to be more more faster really must be all Developer know more info about this new Feature by this option we can move the logical reads (I/O) from 152723 (very Expensive Amount logical Read ) to 707 when we select 266 Row from 100000 Rows
Do you have a data warehouse? Do you wish your queries would run faster? If your answers are yes, check out the new columnstore index (aka Project “Apollo”) in SQL Server Code Name “Denali” today!

 
Leave a comment

Posted by on May 14, 2013 in Index

 

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 .

 
Leave a comment

Posted by on May 13, 2013 in Index

 

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))

 
Leave a comment

Posted by on May 10, 2013 in Performance MSSQL

 

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.

 
2 Comments

Posted by on May 8, 2013 in Performance MSSQL

 

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 :

 
Leave a comment

Posted by on May 8, 2013 in Performance MSSQL

 

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

 

Check Trace File information

by this Script you will Check the trace file information :

SELECT *

FROM fn_trace_getinfo(default);
GO

Result Set Description
Traceid Unique identifier for the trace
Property = 1 Configured trace options
Property = 2 Trace file name
Property = 3 Max file size for the *.trc file
Property = 4 Stop time for the trace session
Property = 5 Current trace status (1 = On and 0 = Off)
Value Current value for the traceid\property combination
*/

 

–Return that information for all traces running on the system
SELECT traceid, value FROM [fn_trace_getinfo](NULL)
WHERE [property] = 2;
SELECT *
FROM [fn_trace_gettable](‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\log_33.trc’, DEFAULT)
ORDER BY StartTime;

 
Leave a comment

Posted by on May 8, 2013 in Performance MSSQL