Friday 28 March 2014

Difference Between UNIQUE/ PK/CLUSTERED

Introduction

Primary Key (PK) of a table object is containing CLUSTERED INDEX by default. So the confusion arises between PK – CLUSTERED INDEX and UNIQUE CLUATERED INDEX.

In this article we are trying to solve this confusion.

So what is That?
UNIQUE Constraint means it takes the Unique Records on the columns and with this It Supports one NULL value.

UNIQUE Constraint = Unique Records in Columns  +  One NULL Value

CLUSTERED means, How data the is Arranged in the Disk.
 Where have INDEX in different type

1.    UNIQUE CLUSTERED Index
2.    Non UNIQUE Clustered Index
3.    UNIQUE NON CLUSTERED Index
4.    NON UNIQUE NON CLUSTERED Index

If we think about the PRIMARY KEY (PK) by default it takes CLUSTERED Index. So the Primary Key is UNIQUE and NOT Support any NULL VALUES.

Primary Key (PK) = UNIQUE + NOT NULL + CLUSTERED Index (By Default)

Here we must remember that the CLUSTERED Index is not the feature of Primary key. The Clustered Index is created automatically when we create the Primary Key.  We can create Primary Key without any CLUSTERED Index also (Not a good Practice).

So a primary key support only UNIQUE value and NO NULL is allowed here.
For UNIQUE CLUSTERED or NON CLUSTERED Index One NULL value is allowed.

A table object cannot have more than one Primary Key but have multiple Unique Indexes.




Hope you like it.




Posted By: MR. JOYDEEP DAS

Thursday 27 March 2014

Finding Unused Index

Introduction

To increase the performance of our Microsoft SQL Server we have to find the Unused index and Drop it.

How to find it
From MS SQL Server version 2005 introduce two DMV to see the usages status of the INDEX.

sys.dm_db_index_operational_stats

This DMV allows you to see insert, update and delete information for various aspects for an index.  Basically this shows how much effort was used in maintaining the index based on data changes.

SELECT OBJECT_NAME(A.[OBJECT_ID]) AS [TABLE NAME],
       I.[NAME] AS [INDEX NAME],
       A.LEAF_INSERT_COUNT,
       A.LEAF_UPDATE_COUNT,
       A.LEAF_DELETE_COUNT
FROM   SYS.DM_DB_INDEX_OPERATIONAL_STATS (db_id(),NULL,NULL,NULL ) A
       INNER JOIN SYS.INDEXES AS I
         ON I.[OBJECT_ID] = A.[OBJECT_ID]
            AND I.INDEX_ID = A.INDEX_ID
WHERE  OBJECTPROPERTY(A.[OBJECT_ID],'IsUserTable') = 1;

sys.dm_db_index_usage_stats

This DMV shows you how many times the index was used for user queries.

SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [TABLE NAME],
       I.[NAME] AS [INDEX NAME],
       USER_SEEKS,
       USER_SCANS,
       USER_LOOKUPS,
       USER_UPDATES
FROM   SYS.DM_DB_INDEX_USAGE_STATS AS S
       INNER JOIN SYS.INDEXES AS I
          ON I.[OBJECT_ID] = S.[OBJECT_ID] AND I.INDEX_ID = S.INDEX_ID
WHERE  OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1
       AND S.database_id = DB_ID();

How to Decide

Based on the output of the two above query we have to decide.
If we see indexes where there are no seeks, scans or lookups, but there are updates this means that SQL Server has not used the index to satisfy a query but still needs to maintain the index. 



Hope you like it.




Posted By: MR. JOYDEEP DAS

Wednesday 26 March 2014

Fixing the Fragmentation of HEAPS

Introduction
A table objects that not containing any Clustered Index is called Heap. It does not mean that this table has no indexes. The table may contain several non clustered indexes on it.

I personally not recommended any kind of Heap table in database. But sometime we find it as for functional support. I do not understand why we create the Heap table? If we do not have any option to primary key we can use the surrogate key.
The problem lies when a Heap table is highly fragmented.
This article is trying to identify the Heap table fragmentation issue and try to defragment it.

How to Identify Heap table Fragmentation Percent
To identify whether your heap table is fragmented, we need to either run DBCC SHOWCONTIG (2000 or 2005) or use the DMV sys.dm_db_index_physical_stats (2005 and later)

DECLARE @db_id     SMALLINT;
DECLARE @object_id INT;

SET @db_id = DB_ID(N'AdventureWorks2008R2');
SET @object_id = OBJECT_ID(N'AdventureWorks2008R2.dbo.tbl_EMPLOYEE');

IF @object_id IS NULL
  BEGIN
     PRINT N'Invalid object';
  END
ELSE
  BEGIN
      SELECT *
      FROM   sys.dm_db_index_physical_stats(@db_id,
                                            @object_id,
                                            0,
                                            NULL ,
                                            'DETAILED');
  END
GO

The third parameter in sys.dm_db_index_physical_stats is for index_id, and we should use zero (0) when the table is a heap.  If you use zero and the table is not a heap, you will receive an error.

How to Defrag Heap

1.    Using the REBUILD option of ALTER TABLE (Only MS SQL 2008 and higher)

ALTER
TABLE dbo.tbl_EMPLOYEE REBUILD;
GO

2.    We will need to select a column to create the clustered index on; keeping in mind this will reorder the entire table by that key.  Once the clustered index has been created, immediately drop it.

CREATE CLUSTERED INDEX cluIdx1 ON dbo. tbl_EMPLOYEE (col1);
GO
DROP INDEX cluIdx1 ON dbo. tbl_EMPLOYEE;
      GO

3.    By manually moving all data to a new temporary table. 

CREATE TABLE dbo.tbl_EMPLOYEE_Temp(col1 INT,col2 INT);
GO
INSERT dbo.tbl_EMPLOYEE_Temp
SELECT * FROM dbo.tbl_EMPLOYEE;
GO

Next, drop the old table, rename the temporary table to the original name, and then create the original non-clustered indexes.

DROP TABLE dbo.tbl_EMPLOYEE;
GO
EXEC sp_rename 'tbl_EMPLOYEE_Temp','tbl_EMPLOYEE';
GO
CREATE NONCLUSTERED INDEX idx1 ON dbo.tbl_EMPLOYEE(col1);
GO
CREATE NONCLUSTERED INDEX idx2 ON dbo.tbl_EMPLOYEE(col2);
GO


Hope you like it.



Posted By: MR. JOYDEEP DAS

Monday 24 March 2014

JOIN ORDER cans Increase Performance


Introduction


All the developer is very much concern related to performance. If someone say that this increase performance all the developer are running behind it. It is not a bad practice at all. Rather as per my point of view we must span all our effort related improve the performance of query.
“One common question that we find that, if we change the ordering of table join in case of inner join will effect or increase performance”
To understand it lets take a simple example of Inner join. There is two tables named Table-A and Table-B. We can us the Inner Join on both the table.
Like this

FROM [Table-A]  AS a INNER JOIN [Table-B] AS b ON a.IDNO = b.IDNO
OR
FROM [Table-B]  AS a INNER JOIN [Table-A] AS b ON  a.IDNO = b.IDNO

Which one is best for performance?

To answer this question we all know that whenever a SQL Query is executed the MS SQL server create several query plans with different join Order and choose the best one.

That means the Join order that we are writing in the query may not be executed by execution plan. May be different join order is used by the execution plan. In the above case the execution plan decide which Join order he will chose depends on best possible costing of execution.

Here [Table-A] JOIN [Table-B] or [Table-B] JOIN [Table-A], MS SQL Server knows it well that both are same.

To understand it Details Lets take an Example

Step-1 [ Create Base Table and Insert Some Records ]

-- Item Master
IF OBJECT_ID(N'dbo.tbl_ITEMDTLS', N'U')IS NOT NULL
   BEGIN
     DROP TABLE [dbo].[tbl_ITEMDTLS];
   END
GO
CREATE TABLE [dbo].[tbl_ITEMDTLS]
     (
        ITEMCD    INT         NOT NULL IDENTITY PRIMARY KEY,
        ITEMNAME  VARCHAR(50) NOT NULL
     ) 
GO
-- Inserting Records
INSERT INTO [dbo].[tbl_ITEMDTLS]
       (ITEMNAME)
VALUES ('ITEM-1'),('ITEM-2'),('ITEM-3');
      
-- Item UOM Master
IF OBJECT_ID(N'dbo.tbl_UOMDTLS', N'U')IS NOT NULL
   BEGIN
     DROP TABLE [dbo].[tbl_UOMDTLS];
   END
GO
CREATE TABLE [dbo].[tbl_UOMDTLS]
     (
        UOMCD    INT         NOT NULL IDENTITY PRIMARY KEY,
        UOMNAME  VARCHAR(50) NOT NULL
     ) 
GO
-- Inserting Records
INSERT INTO  [dbo].[tbl_UOMDTLS]
       (UOMNAME)
VALUES ('KG'), ('LTR'), ('GRM');
GO  

-- Transaction Table     
IF OBJECT_ID(N'dbo.tbl_SBILL', N'U')IS NOT NULL
   BEGIN
     DROP TABLE [dbo].[tbl_SBILL];
   END
GO
CREATE TABLE [dbo].[tbl_SBILL]
      (
        TRID      INT               NOT NULL IDENTITY PRIMARY KEY,
        ITEMCD    INT               NOT NULL,
        UOMCD     INT               NOT NULL,
        QTY       DECIMAL(18,3) NOT NULL,
        RATE      DECIMAL(18,2) NOT NULL,
        AMOUNT    AS QTY * RATE
      );
GO
-- Foreign Key Constraint
ALTER TABLE  [dbo].[tbl_SBILL]
ADD CONSTRAINT  FK_ITEM_tbl_SBILL FOREIGN KEY(ITEMCD) REFERENCES [dbo].[tbl_ITEMDTLS](ITEMCD);
GO
ALTER TABLE  [dbo].[tbl_SBILL]
ADD CONSTRAINT  FK_UOMCD_tbl_SBILL FOREIGN KEY(UOMCD) REFERENCES [dbo].[tbl_UOMDTLS](UOMCD);

-- Insert Records
INSERT INTO [dbo].[tbl_SBILL]
       (ITEMCD, UOMCD, QTY, RATE)
VALUES (1, 1, 20, 2000),(2, 3, 23, 1400);      

Step-2 [ Now Make Some JOIN  ]

SELECT b.TRID, b.ITEMCD, a.ITEMNAME, b.UOMCD,
       c.UOMNAME, b.QTY, b.RATE, b.AMOUNT
FROM   [dbo].[tbl_ITEMDTLS] AS a
       INNER JOIN  [dbo].[tbl_SBILL] AS b ON a.ITEMCD = b.ITEMCD
       INNER JOIN  [dbo].[tbl_UOMDTLS]  AS c ON b.UOMCD  = c.UOMCD;

Here  [tbl_ITEMDETAILS] JOIN [tbl_SALES] JOIN [tbl_UOMDETAILS]

If we look at the Execution Plan



We find that

[tbl_SALES] JOIN [tbl_ITEMDETAILS] JOIN [tbl_UOMDETAILS]

Step-2 [ Now we need to Force Order Hint to maintain Join Order ]

SELECT b.TRID, b.ITEMCD, a.ITEMNAME, b.UOMCD,
       c.UOMNAME, b.QTY, b.RATE, b.AMOUNT
FROM   [dbo].[tbl_ITEMDTLS] AS a
       INNER JOIN  [dbo].[tbl_SBILL] AS b ON a.ITEMCD = b.ITEMCD
       INNER JOIN  [dbo].[tbl_UOMDTLS]  AS c ON b.UOMCD  = c.UOMCD
OPTION ( QUERYRULEOFF JoinCommute);



For this we need the FORCE ORDER Hint.
The query optimizer uses different rules to evaluate different plan and one of the rules is called JoinCommute. We can turn it off using the undocumented query hint QUERYRULEOFF.



Hope you like it.



Posted By: MR. JOYDEEP DAS

Saturday 22 March 2014

Delayed Transaction Durability in MS SQL 2014(CTP2)

Introduction

When we first learn the Database we all know about the ACID property of database. We are not going to review it again. Just going to the D means the Durability.

Durability means that when a transaction is committed then changes made by the transaction are permanently stored on disk.

Before MS SQL Server 2014 the TRANSACTION of the SQL Server is Fully Durable. That means TRANSACTION commits are synchronous and report a COMMIT as successful and return control to the client only after the log records for the transaction are written to disk.
If the log records are written to disk successfully then the COMMIT process is successful and the control returns to client. In the other words, we can say that if the transaction log entry fails then the entire transaction is ROLLBACK.

MS SQL Server 2014 (CTP2) introduced Delayed Durability.
It helps reduce the IO contention for writing to the transaction log. Transaction commits are asynchronous. In this case, transactions are logged to the transaction log buffer and then control is returned to the application. The log buffers are written to disk later. This feature is also known as Lazy Commit. 

Both Full and Delayed Transaction durability have their own advantage and disadvantage.

When to Use Full Transaction Durability
·         We must use it when our system not tolerates any data loss.
·         The bottleneck is not due to transaction log write latency.


When to Use Delayed Transaction Durability

We can tolerate some data loss

Where the individual records are not critical as long as we have most of the data.

We are experiencing a bottleneck on transaction log writes

If our performance issues are due to latency in transaction log writes, our application will likely benefit from using delayed transaction durability.

We  workloads have a high contention rate
If our system has workloads with a high contention level much time is lost waiting for locks to be released. Delayed transaction durability reduces commit time and thus releases locks faster which results in higher throughput.

Option of the Transaction Durability
We have the following three options to set the value of Delayed Durability:
  • Disabled: The Delayed Durability feature cannot be used by the current database. This is the default setting. This is the same as a fully durable transaction.

  • Allowed: With this option, each transaction's durability is determined by the transaction level (DELAYED_DURABILITY = {OFF | ON}).

  • Forced: With this option, every transaction must follow Delayed Durability. This is very useful when transaction durability is more important for the database.
Syantax

ALTER DATABASE [DatabaseName]
SET DELAYED_DURABILITY = {DISABLED | ALLOWED | FORCED}

Please note that MS SQL Server 2014 also allows us to use various durability levels at the transaction level.

The COMMIT syntax is extended to support force delayed transaction durability. This COMMIT option is ignored when DELAYED_DURABILITY is DISABLED or FORCED at the database level.

COMMIT TRAN
[ transaction name | @transaction name variable ]
[ WITH ( DELAYED_DURABILITY = { OFF | ON })]

Example

BEGIN TRAN
   INSERT INTO tbl_JODT
          (JOINDT)
   SELECT GETDATE()
COMMIT WITH (DELAYED_DURABILITY = ON)

With Procedure Example

CREATE PROCEDURE TESTPROC
WITH NATIVE_COMPILATION, SCHEMABINDING,
EXECUTE AS OWNER
AS
BEGIN
     ATOMIC WITH
     (
          DELAYED_DURABILITY = ON,
          TRANSACTION ISOLATION LEVEL = SNAPSHOT
     )
     <.... Body ...>
END

Forced a transaction log flush

MS SQL Server 2014 has the system Stored Procedure named sp_flush_log that forces a flush of the log records of all preceding committed (in memory).

References


Hope you like it.

Posted By: MR. JOYDEEP DAS

ColumnStore Index in MS SQL 2012 and Extension In MS SQL 2014

Introduction

In this article we are going to discuss about the ColumnStore Index of Microsoft SQL Server 2012. The feature is developed in MS SQL 2012 first and Modification of it came at MS SQL Server 2014. Here we discuss both of them. Hope it will be interesting and useful.

What Technology the ColumnStore used

ColumnStore indexes are based on xVelocity technology. This is formerly known as VertiPaq. It is an advanced storage and compression technology that originated with PowerPivot and Analysis Services but has been adapted to SQL Server 2012 databases.

For Which Purpose it is Created

ColumnStore Index is specially designed for handling large amount data in FACT table of Sql Server Analytical Services (SSAS) Cube.

Understanding ColumnStore Index

At the heart of this model is the columnar structure that groups data by columns rather than rows. To better understand it lets take a simple example.

First of all we create the Base Table

-- Base Tabhle
IF OBJECT_ID(N'dbo.tbl_EMPDTLS', N'U')IS NOT NULL
   BEGIN
      DROP TABLE [dbo].[tbl_EMPDTLS];
   END
GO
CREATE TABLE [dbo].[tbl_EMPDTLS]
   (
     EMPID    INT         NOT NULL IDENTITY PRIMARY KEY,
     EMPFNAME VARCHAR(50) NOT NULL,
     EMPLNAME VARCHAR(50) NOT NULL,
     EMPGRADE CHAR(1)     NOT NULL
   )
GO     

As the table named dbo.tbl_EMPDTLS contains a primary key on the columns EMPID. So a CLUSTERED INDEX is generated automatically on EMPID columns of the table. So it is a ROW store INDEX. Before understanding the ColumnStore Index we need to understand the ROW Store index first and how it’s works.

In the case of a Row Store Index, data from all the columns of the rows are stored together on the same page.



Now we put the query to retrieve data

SELECT EMPID, EMPGRADE FROM [dbo].[tbl_EMPDTLS];

Now we see what happened when the above query fires.



When the database engine processes the above query, it retrieves all three data pages into memory, fetching the entire table.  Even if most of the columns aren’t needed. Than the requested fetched and show us the result.

Now in case of ColumnStore Index store each column data in separate pages. That is column wise fashion, rather than the traditional Row Store Index, which stores data from all the columns of a row together contiguously (row wise fashion). If we query only selects a few columns of the index, it reads less pages as it needs to read data of selected columns only and improves the performance by minimizing the IO cost.



Creating ColumnStore Index

Creating ColumnStore Index is not a hard job. No extra syntax is needed. June one key word named COLUMNSTORE is needed with old Index syntax.

CREATE NONCLUSTERED COLUMNSTORE
INDEX IX_ClStr_tbl_EMPDTLS
ON [dbo].[tbl_EMPDTLS]
(EMPID, EMPFNAME, EMPLNAME, EMPGRADE);

Limitation

·         A table with a ColumnStore Index cannot be updated.
·         A table can have only one ColumnStore Index and hence you should consider including all         columns or at least all those frequently used columns of the table in the index.
·         A ColumnStore Index can only be non cluster and non unique index; you cannot specify       
      ASC/DESC or INCLUDE clauses.
·         The definition of a ColumnStore Index cannot be changed with the ALTER INDEX   
       command; you need to drop and create the index or disable it then rebuild it.
·         A ColumnStore Index cannot be created on view.
·         A ColumnStore Index cannot be created on table which uses features 
      like ReplicationChange TrackingChange Data Capture and Filestream

What’s New in 2014

A new storage engine in SQL Server 2014 overcomes that limitation.
Clustered Columnstore Index, allowing the table to operate normally when it comes to DML operations like INSERT, UPDATE and DELETE.




Hope you like it.




Posted By: MR. JOYDEEP DAS