Friday 24 August 2012

Index of Materialized View Not Working

Introduction
I got a mail from one of my closed friends related to "Materialized view Index not working". I am little bit confused after getting this mail. When I am getting closer look at the problem that I understand what he mean by "Materialized view Index not working" This article is related to Why in Materialized view Index is not working.
Point to focus
1.    Understanding the case scenario
2.    Finding the rood of the problem
3.    How we fix it.
4.    Related Tropics

In this article I am not going to describe related to Materialize View as we all know that how important it is in case of performance boost up. If have you any confusion visit my previous article. You can find it in related tropic options of this article.
Understanding the case scenario
Here in this article, I cannot provide the exact scenario due to some restrictions but I can create a scenario related to it. So let's see what the scenario is.

Step-1 [ Creating the Base Table ]
IF OBJECT_ID('my_BaseTable') IS NOT NULL
   BEGIN
        DROP TABLE my_BaseTable
   END
GO

CREATE TABLE my_BaseTable
       (ID      INT         NOT NULL IDENTITY(1,1) PRIMARY KEY,
        EMPNAME VARCHAR(50) NOT NULL,
        GRADE   VARCHAR(1)  NOT NULL)
GO
Step-2 [ Now Insert Some records on our Base Table ]
INSERT INTO my_BaseTable
       (EMPNAME, GRADE)  
VALUES ('Mr. Sudip Das', 'A'),
       ('Mr. Joydeep Das', 'B'),
       ('Mr. Sukamal Jana', 'B'),           
       ('Mr. Tuhin Kr. Shinah', 'C'),
       ('Mr. Sangram Jit', 'C'),
       ('Mr. Sourav Sarkar', 'D');
GO    

Step-3 [ Display the Index Information ]
sp_helpindex my_BaseTable
index_name
index_description
index_keys
PK__my_BaseT__3214EC2766979D65
clustered, unique, primary key located on PRIMARY
ID


Here the UNIQUE CLUSTERED index is created.

Step-4 [ Optional -- Display the Records of the Base Table ]
ID    EMPNAME                 GRADE
1     Mr. Sudip Das           A
2     Mr. Joydeep Das         B
3     Mr. Sukamal Jana        B
4     Mr. Tuhin Kr. Shinah    C
5     Mr. Sangram Jit         C
6     Mr. Sourav Sarkar       D

Step-5 [ Create the Materialize View ]
IF OBJECT_ID('View_my_BaseTable') IS NOT NULL
   BEGIN
        DROP VIEW View_my_BaseTable
   END
  
GO

CREATE VIEW View_my_BaseTable
WITH SCHEMABINDING
AS
SELECT ID, EMPNAME, GRADE
FROM   dbo.my_BaseTable;
Step-6 [ Create the Clustered index on the View ]

CREATE UNIQUE CLUSTERED INDEX IX_View_my_BaseTable
ON View_my_BaseTable (ID);

Step-7 [ Display the Index Information ]
sp_helpindex View_my_BaseTable
index_name
index_description
index_keys
IX_View_my_BaseTable
clustered, unique located on PRIMARY
ID


Here the UNIQUE CLUSTERED index is created.
Step-7 [ Display the Actual Execution Plan ]



In this execution plan if we see it carefully we find that the Index named "PK__my_BaseT__3214EC2766979D65" is used which is the Base Table Index (The Index of the Primary Key). The Index that is created on the View is not used.
"And this is the Problem. Why the Index of the View is not used by the MS SQL Query optimizer? "

Finding the rood of the problem
The cost of using the indexed view may exceed the cost of getting the data from the base tables, or the query is so simple that a query against the base tables is fast and easy to find. This often happens when the indexed view is defined on small tables.

How we fix it
We can use the NOEXPAND hint if we want to force the query processor to use the indexed view.
SELECT * FROM View_my_BaseTable(NOEXPAND)



Related Tropics
1.    Materialize view and the performance issue


Hope you like it.


Posted by: MR. JOYDEEP DAS

Saturday 18 August 2012

Fill Factor


Introduction

In my previous article, I am trying to discuss about the MS SQL Server Index and how the Index Boost up the performance of SQL Server Query.

Fill Factors is directly related to Index and Index is related to performance. So without the proper knowledge of Fill Factor, understanding of Index is not completed.  So I in this article I am trying to discuss related to Fill Factors.

 Point in Focus

1.    What is Fill Factor
2.    How it Effects
3.    Measurement of Fill Factors % Value
4.    How to Set Fill Factor
5.    Is There anything New in MS SQL 2008/2012
6.    Related tropics

What is Fill Factor

Before going to Fill Factor we have to understand the architecture of Index. In the MS SQL Server the Indexes are organised by B-Tree structure.

Please go to my previous article "Related to Cluster Index" to find the details of it.

In the MS SQL Server the smallest unit is the Page (8K) where one on more rows stores depends on the size of the rows.

Fill Factors is the value that determines the percentage of the spaces in each leaf-level pages to be field by data.

The range of the Fill Factor starts from 0% to 100% and the default value is 100%. Here the 0% and 100% means the same

 How it Effects

I think the above example of Fill Factor is quite complex to understand to make it simple, let's take an example.

Index is fragmented due to DML operation on the table objects. Defragmented index is causes to bad performance of query in MS SQL Server.

 Stuation-1

 In this example we are taking an Index with Fill Factor of 0 or 100 % and the Leaf-level data pages are completely full. Now we are trying to update a row which is located in the same pages. The size of the new value that is updated is bigger than the previous one.



So In this situation the page size cannot support the new value as the page is full.  So it create a new leaf-level pages and the 50% of the data moves to the new page and in the previous pages 50% data exists so the page filling of the old page is only 50% and the Index is fragmented.  To find the desired rows SQL Server have to move multi pages as the Index is fragmented by Page splitting.

Situation-2

Now take above example with FILL FACTOR vale is between 0 to 100%. For example we are taking the FILL FACTOR Value = 70%.



Now for the above example (Situation-1) for the new updated value, MS SQL Server try to adjust the new data within 30% of the leaf-level pages as the 70% of the pages is full and the rest 40% is blank pages and the page is not split.

Measurement of Fill Factors % Value

It is hard to say that what percentage of fill factor that we use. It depends on situations to situations or we can say it how often the DML operation is done on the table objects.
Here I am trying to explain some situations and there fill factor percentage

Situation for Fill Factor
Fill Factor %
Static Table – The value of the table never changed
100
Tables Updated Less Often
95
Frequently Updated Table
70 to 90

If a table containing clustered Index with Primary key and use IDENTITY columns than we can use the Higher percentage vale of the Fill factor.

 If the table containing clustered Index with  Primary key and the value of the Primary key is NOT sequential like Auto incremental value than, we can use the Lower Percentage Value of the Fill Factor.

In case of OLTP systems we can take the lower percentage value of Fill Factor. But in case of OLAP system we can take the higher percentage value of Fill Factor.
Don't set the Fill Factor Value to 50% others your index is fragmented.

How to Set Fill Factor

To Set the Default Fill Factor

[A]  MS SQL Server Enterprise Manager.


1.    In the MS SQL Server console tree, right-click on your server.

2.    From the shortcut menu, choose Properties.

3.    Select the Database Settings tab.

4.    Change the fill factor by choosing Fixed below Settings, and then specify the fill factor percentage by using the slider bar.

[B]  By T-SQL

To set the Fill Factor 90%

sp_configure 'show advanced options', 1
GO
--Here is the message:
Configuration option 'show advanced options' changed from 0 to 1.
Run the RECONFIGURE command to install.

RECONFIGURE
GO
sp_configure 'fill factor', 90
GO
RECONFIGURE
GO


Specify Fill Factor of an Existing Index

USE AdventureWorks2012;
GO
-- Rebuilds the IX_Employee_OrganizationLevel_OrganizationNode index
-- with a fill factor of 80 on the HumanResources.Employee table.

ALTER INDEX IX_Employee_OrganizationLevel_OrganizationNode 
ON HumanResources.Employee
REBUILD WITH (FILLFACTOR = 80);

GO

Another way to specify a fill factor in an index

USE AdventureWorks2012;
GO
/*
 Drops and re-creates the IX_Employee_OrganizationLevel_OrganizationNode index on
the HumanResources.Employee table with a fill factor of 80.
*/

CREATE INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources.Employee
   (OrganizationLevel, OrganizationNode)
WITH (DROP_EXISTING = ON, FILLFACTOR = 80);
GO

Is There anything New in MS SQL 2008/2012

I don't find anything new on MS SQL Server 2005 and MS SQL Server 2012. If you have anything please give me some notes related to it.

Related tropics

1.    Index defragmentation


Hope you like it.




Posted by: MR. JOYDEEP DAS

Sunday 12 August 2012

Transaction Log and VLF


Introductions

Transaction log is used for data recovery purpose of the database. A proper understanding of transaction log is very important to managing the transaction log.
Point in focus

In this article I am trying focus on the points are mentioned bellow.

1.    The Physical architecture
2.    What's the Problem
3.    Finding the numbers of VLF in a Database
4.    How we fix it

The Physical architecture

The transaction log in a database maps over one or more physical files. Internally a transaction log consists of multiple blocks, called virtual log files. Every time a transaction log grows, it is done by adding additional virtual log files. The Database Engine maintains the size of the virtual log files dynamically while it is creating or extending log files. The Database Engine tries to maintain a small number of virtual files. The size of the virtual files after a log file has been extended is the sum of the size of the existing log and the size of the new file increment.

Note that, the database administrator cannot configure the size and the number of the virtual log file.

If we make a look of the transaction log file it is a wrap-around file. To understand it properly, in our example we are taking a database contains one physical log file which is divided into four virtual log file. When our database is created the logical log file begins at the start point of the physical log file.  If needed the new log are added at the end of the logical log and expand towards the end of the physical log.  Log truncation frees any virtual logs whose records all appear in front of the minimum recovery log sequence number (MinLSN). The MinLSN is the log sequence number of the oldest log record that is required for a successful database-wide rollback. When the end of the logical log reaches the end of the physical log file, the new log records wrap around to the start of the physical log file.



The log file content is logically categorized into three different categories:
  • Used portion: This contains log records that are written to the transaction log but can be removed
  • Active portion: This is the part of the transaction log which is defined by the oldest active transaction. This information cannot be removed while the transaction is still active
  • Unused portion: This is empty space

What's the Problem

When the transaction log file grows very repeatedly, it generates the thousands of virtual log files. The multiple virtual file may be scattered over the disks resulting in a seek every time a write crosses from one file to the next.

At the time the log is growing, each Chunk that is added is divided into Virtual Log File. Taking an example,  if the 10 MB of Log that is extended to the 50 MB, so the 40 MB chunk is added and it divided into 4 Virtual Log File.

Here demonstrating a simple table to understand it.

Chunks
VLF
Chunks <64MB
4
Chunks>=64MB AND <=1GB
8
Chunks>1GB
16

Finding the numbers of VLF in a Database

 Ideally any count of VLF less than 50 is considered as good number, anything beyond 50 might affect the performance.
The bellow scripts helps to find the number of VLF in our entire database.

CREATE TABLE #MyTracker
       (FileID          INT,
            FileSize    BIGINT,
            StartOffset BIGINT,
            FSeqNo            BIGINT,
            [Status]    BIGINT,
            Parity            BIGINT,
            CreateLSN   NUMERIC(38)
);
CREATE TABLE #MyResult
       (Database_Name  sysname,
        VLF_count      INT,
        Log_File_count INT
);

EXEC sp_MSforeachdb N'Use [?];
                      INSERT INTO #MyTracker
                      EXEC sp_executeSQL N''DBCC LogInfo(?)'';
                      INSERT INTO #MyResult
                      SELECT DB_Name(), 
                             Count(*), 
                             Count(Distinct FileID)
                      FROM   #MyTracker;
                      TRUNCATE TABLE #MyTracker;'
SELECT   *
FROM     #MyResult
ORDER BY VLF_count DESC;

Result set are mentioned bellow.

Database_Name              VLF_count          Log_File_count
msdb                                     10                            1
master                                    5                             1
Test                                       4                              1
model                                    3                              1
tempdb                                  2                              1

How we fix it

If you come across a database with more than 50-100 VLFs then we should look to take action in order to increase the transaction log throughput. Here I am taking the example of "AdventureWorks" Database.

Step -1 [ Backup the Transaction Log ]

BACKUP LOG [AdventureWorks]
TO  DISK = N'E:\db.bak'
GO

Step-2 [ Shrink the Transaction Log ]

USE [AdventureWorks]
GO
DBCC SHRINKFILE ('AdventureWorks_Log', TRUNCATEONLY)
GO
Step-3 [ Alter the Database to modify the size of transaction
            log and configure Auto growth ]

USE [master]
GO
ALTER DATABASE [AdventureWorks]
      MODIFY FILE ( NAME = N'AdventureWorks_Log',
                    SIZE = 1024000KB,
                    FILEGROWTH = 1024000KB)
GO


Hope you like it.


Posted by: MR. JOYDEEP DAS

Thursday 9 August 2012

Table Variable with Dynamic SQL

Introductions
The table variable gives us the high degree of flexibility to work with T-SQL construct. Most of the developer uses the table variable to find the desired solutions due to its simplicities. Here in this article I am not discussing related to table variable. I already discuss it in my previous article (Please refer to the related tropics portions of the article). 
Here I am trying to discuss about the Table variable with dynamic SQL constructs and how to use the table variable in the dynamic SQL.
Before writing this article, I saw several article related to it, but they are discussing about a specific example in mind. Here in this article I am trying to cover all the aspects related to Table variable and dynamic SQL.
Although I do not prefer any dynamic SQL constructs but depending on the criteria and implementations in mind we have to go to choose these options. With the dynamic SQL and Table variable, developers are facing lot of common problem. In this article I am trying to figure out those problems and try to explain the solution of it.
Focused area
The area that we focused in this article is mentioned bellow.
1.    Table variable and Performance aspects
2.    The Common problem that we faced
3.    An Example of the Problem
4.    How we can solve it

Table variable and Performance aspects
When we are making a SQL constructs we must think about the performance aspects of the query. So we must understand that the using Table variable is good for performance boost up or not. Before using Table variable, we must know some facts related to it.
ü  MS SQL Server will not use parallelism when modifying a table variable. 
    The query used to insert rows into the table variable will not use parallelism.
ü  MS SQL Server does not create any statistics, like histogram, for table variables, so 
    the estimates on your second query will be a rough guess.


ü  Use table variables for really low number of rows, and if possible use OPTION 
    (RECOMPILE) in our query so the query optimizer could know the number of rows in 
    the table variable by the time it executes the query.


ü  If the number of rows is high than using temporary table is a good idea for 
    performance boost up as the temporary table use the Parallelism.
What is Parallelism?
MS SQL server can performs a query or index operations in parallel by using several operating system threats, the operation can be completed quickly and efficiently.
For more information related to Parallelism, follow the MSDN link.

The Common problem that we faced
Here I am mentioning an error message that most of the developer found if they made mistake when working with Table variable and dynamic SQL constructs.
Msg 137, Level 16, State 1, Procedure my_proc, Line 13
Must declare the scalar variable "@TBLVAR".
OR
Msg 1087, Level 15, State 2, Line 1
Must declare the table variable "@TBLVAR" 

An Example of the Problem
Here is the example from where the Error message is generated.
Here is a simple SQL example where the Error occurred.
Example-1
DECLARE @my_TABLE VARCHAR(50)='my_STUDENT'   
EXEC('SELECT * FROM  @my_TABLE')
-- Error Messages
Msg 1087, Level 15, State 2, Line 1
Must declare the table variable "@my_TABLE".
Example-2
In this example, the error message is generated when the stored procedure is compiled.
-- Base Table
IF OBJECT_ID('my_STUDENT') IS NOT NULL
   BEGIN
      DROP TABLE my_STUDENT;
   END
GO  
CREATE TABLE my_STUDENT
       (ROLL  INT          NOT NULL IDENTITY(1,1) PRIMARY KEY,
        SNAME VARCHAR(50)  NOT NULL,
        CLASS INT          NOT NULL);
      
GO
-- Insert Records
INSERT INTO my_STUDENT
       (SNAME, CLASS)
VALUES ('Joydeep', 1),
       ('Sukamal', 2),
       ('Sandip', 3),
       ('Samgram jit', 4)
GO 
-- SP Example
IF OBJECT_ID('my_proc') IS NOT NULL
   BEGIN
      DROP PROCEDURE my_proc;
   END                     
GO  
CREATE PROCEDURE my_proc
   (
      @p_OpeningMood   INT = 4
   )
AS
DECLARE @SQLSTRING VARCHAR(100)
DECLARE @TBLVAR TABLE
        (ROLL   INT,
         SNAME  VARCHAR(50))
BEGIN
    IF @p_OpeningMood = 4
       BEGIN
          SET @SQLSTRING = 'INSERT INTO '+ @TBLVAR
                           +' SELECT * FROM my_STUDENT'
                          
         EXEC (@SQLSTRING);                           
       END
    SELECT * FROM @TBLVAR;  
END     
GO
-- Error Messages
Msg 137, Level 16, State 1, Procedure my_proc, Line 13
Must declare the scalar variable "@TBLVAR".
In this example, the Error message is generated when the Stored Procedure is executed.
 -- SP Example [Another Variations]
IF OBJECT_ID('my_proc') IS NOT NULL
   BEGIN
      DROP PROCEDURE my_proc;
   END                     
GO  
CREATE PROCEDURE my_proc
   (
      @p_OpeningMood   INT = 4
   )
AS
DECLARE @SQLSTRING VARCHAR(2000)
DECLARE @TBLVAR TABLE
        (ROLL   INT,
         SNAME  VARCHAR(50))
BEGIN
    IF @p_OpeningMood = 4
       BEGIN
          SET @SQLSTRING = 'INSERT INTO  @TBLVAR
                           SELECT * FROM my_STUDENT'
                          
         EXEC (@SQLSTRING);                           
       END
     SELECT * FROM @TBLVAR;  
END     
GO
-- Messages
Command(s) completed successfully.

-- Executing the Stored Proc
EXEC my_proc
     @p_OpeningMood=
    
-- Error Messages    
Msg 1087, Level 15, State 2, Line 1
Must declare the table variable "@TBLVAR"

How we can solve it
We have to know, how to write the Dynamic SQL with Table variable to solve the problem.
First we look at the Example-1 and solve it (Passing table variable into dynamic SQL).
Please remember that the solutions of Example-1 is worked on MS SQL Server 2008 only
IF TYPE_ID('my_STUDENT') IS NOT NULL
   BEGIN
     DROP TYPE my_STUDENT
   END
GO  
CREATE TYPE my_STUDENT AS TABLE
(ROLL  INT    NOT NULL IDENTITY(1,1) PRIMARY KEY,
 SNAME VARCHAR(50)  NOT NULL,
 CLASS INT    NOT NULL);
GO

BEGIN
      DECLARE @my_TABLE my_STUDENT
      INSERT @my_TABLE VALUES ('Joydeep', 1),
                              ('Sukamal', 2),
                              ('Sandip', 3),
                              ('Samgram jit', 4)

      EXECUTE sp_executesql N'SELECT * FROM @my_TABLE',
                            N'@my_TABLE my_STUDENT READONLY',
                            @my_TABLE
    
END
GO

To passing the Table variable to a dynamic SQL please follow the gideline mentioned bellow.

[A] Defining the Table Type:

In my example, first I check that the type name exists or not. If exists than drop the type name.

IF TYPE_ID('my_STUDENT') IS NOT NULL
   BEGIN
     DROP TYPE my_STUDENT
   END
GO  

CREATE TYPE my_STUDENT AS TABLE
      (ROLL  INT    NOT NULL IDENTITY(1,1) PRIMARY KEY,
       SNAME VARCHAR(50)  NOT NULL,
       CLASS INT    NOT NULL);
GO

[B] Now declare a Variable by this Type name:

Here in this example, we are also filling some records in it.

DECLARE @my_TABLE my_STUDENT
INSERT @my_TABLE VALUES ('Joydeep', 1),
                        ('Sukamal', 2),
                        ('Sandip', 3),
                        ('Samgram jit', 4)

In case of stored procedure
CREATE PROCEDURE my_proc
           (
              @my_TABLE my_STUDENT READONLY
           )
AS
BEGIN

END
GO
[C] Passing the variable into Dynamic statement:
In my example
EXECUTE sp_executesql N'SELECT * FROM @my_TABLE',
                      N'@my_TABLE my_STUDENT READONLY',
                        @my_TABLE

For the stored procedure we can
DECLARE @DataTable AS my_STUDENT
INSERT INTO @DataTable SELECT * FROM (Some data)

-- Call the stored proc
EXECUTE my_proc @my_TABLE = @DataTable
Now we are going to solve the Example-2
We can solve it by
IF OBJECT_ID('my_proc') IS NOT NULL
   BEGIN
      DROP PROCEDURE my_proc;
   END                     
GO  
CREATE PROCEDURE my_proc
   (
      @p_OpeningMood   INT = 4
   )
AS
DECLARE @SQLSTRING VARCHAR(2000)

BEGIN
    IF @p_OpeningMood = 4
       BEGIN
         
         SET @SQLSTRING = 'DECLARE @TBLVAR TABLE
                                 (ROLL   INT,
                                  SNAME  VARCHAR(50))
                           INSERT INTO @TBLVAR
                           SELECT ROLL, SNAME FROM my_STUDENT'
                          
         EXEC (@SQLSTRING);                           
       END
END     
GO
The Example works fine but the table variable is not accessible from outside the dynamic SQL. The reason is the dynamic SQL is not part of the procedure, but constitutes a scope of its own. So if we go to the simple solutions we must use the temporary table (#, ##) to solve this problem.
Or if we stick to the table variable we must go to the hybrid solutions for it.
IF OBJECT_ID('my_proc') IS NOT NULL
   BEGIN
      DROP PROCEDURE my_proc;
   END                     
GO  
CREATE PROCEDURE my_proc
   (
      @p_OpeningMood   INT = 4
   )
AS
DECLARE @SQLSTRING VARCHAR(2000)
DECLARE @TBLVAR TABLE
        (ROLL   INT,
         SNAME  VARCHAR(50))
BEGIN
    IF @p_OpeningMood = 4
       BEGIN
         
         SET @SQLSTRING = 'SELECT ROLL, SNAME FROM my_STUDENT'
                          
         INSERT INTO @TBLVAR                 
            EXEC (@SQLSTRING);                           
       END
    SELECT * FROM @TBLVAR;  
END     
GO
-- Executing the Stored Proc
EXEC my_proc
     @p_OpeningMood=4

-- Output

ROLL       SNAME
1              Joydeep
2              Sukamal
3              Sandip
4              Samgram jit    

The above example works fine and we can get the desired result.
Related tropics
1.    Table-Valued Parameters
2.    Temporary table and Table variable
3.    How to TABLE or TABLE variable to SP
4.    Dynamic SQL With sp_executesql()


Hope you like it.

Posted by: MR. JOYDEEP DAS