Category Archives: Sql Server Tutorial

Indexes in Sql Server – Part I

101 Performance Tuning Tips and Tricks, Index, Performance Optimization, Sql Server, Sql Server Tutorial, , , , , , , , , , ,

If you need your query to run faster, then you must know about the indexes. In this series of articles on Indexes, I will walk you through Sql Server Indexes with examples and explain how Sql Server Indexes come handy to resolve query performance issue. This series of articles will be helpful for both Sql beginners and advance users. I will try to keep this series of articles on Indexes as simple as possible by keeping in mind the beginner audience.

In this Part-I article of the series of articles on Sql Server Indexes, I will explain how to enable the execution plan, IO and Time statistics. It will also cover HEAP tables and problems while querying data from it.

The main purpose of Sql Server indexes is to facilitate the faster retrieval of the rows from a table. Sql Server indexes are similar to indexes at the end of the book whose purpose is to find a topic quickly.

To understand Sql Server Indexes with example, let us create a Customer table as shown in the below image. Execute the following script to create the Customer table with sample 100,000 records. 

--Create Demo Database
CREATE DATABASE SqlHintsIndexTutorial
GO
USE SqlHintsIndexTutorial
GO
--Create Demo Table Customer
CREATE TABLE dbo.Customer (
    CustomerId INT IDENTITY(1,1) NOT NULL,
    FirstName VARCHAR(50), LastName  VARCHAR(50),
    PhoneNumber VARCHAR(10), EmailAddress VARCHAR(50),
    CreationDate DATETIME
)
GO
--Populate 1 million dummy customer records
INSERT INTO dbo.Customer (FirstName, LastName, PhoneNumber, EmailAddress, CreationDate)
SELECT TOP 1000000 REPLACE(NEWID(),'-','') FirstName, REPLACE(NEWID(),'-','') LastName, 
    CAST( CAST(ROUND(RAND(CHECKSUM(NEWID()))*1000000000+4000000000,0) AS BIGINT) AS VARCHAR(10))
	PhoneNumber,
    REPLACE(NEWID(),'-','') + '@gmail.com' EmailAddress,     
    DATEADD(DAY,CAST(RAND(CHECKSUM(NEWID())) * 3650 as INT) + 1 ,'2006-01-01') CreationDate
FROM sys.all_columns c1
        CROSS JOIN sys.all_columns c2
GO
--Update one customer record with some know values
UPDATE dbo.Customer
SET FirstName = 'Basavaraj', LastName = 'Biradar', 
    PhoneNumber = '4545454545', EmailAddress = 'basav@sqlhints.com'
WHERE CustomerId = 50000
GO

Enable Execution Plan

Enable the display of query execution plan in Sql Server Management Studio (SSMS) by clicking on the Actual Execution Plan option as shown in the below image. You can as well enable/disable the Actual Execution Plan by pressing the key stroke CTRL + M.

Enable Actual Execution Plan

What is execution plan?

Beginners may be wondering what is this execution plan? In Sql Server execution plan is nothing but the visual representation of steps or operations which Sql Server performs internally to execute a query and return the result.

Enable the display of IO and Time Statistics for a Query

Execute the following statements to enable both IO and Time statistics in one statement. 

SET STATISTICS IO,TIME ON

The SET STATISTICS IO ON statement displays the disk activity performed to execute the Sql query. In Sql Server table data is stored in 8 KB data pages on the disk. Whenever we try to read the data from a table, the Sql Server query engine first checks whether data page is already in memory. If the page is already in memory then sql uses that, this operation is shown as logical read in SET STATISTICS IO output of the query. If sql doesn’t find the data page in the memory then it reads it from the disk, this operation is shown as physical read in SET STATISTICS IO output of the query. Both logical and physical reads of a data page is a costly operation. A query should have minimal page reads. The SET STATISTICS TIME ON output of the query shows the time taken by the query to complete the execution. In the following sections, I will explain both these settings results with an example. It will be more clear once you go through the examples in the following sections.

Execute the following query to get the details of a Customer whose CustomerId is 50000.

SELECT * 
FROM dbo.Customer WITH(NOLOCK) 
WHERE CustomerId = 50000

From the above result, we can see that we have one customer record in the Customer table with CustomerId as 50000.

Let us now go to the Execution Plan tab of the result and see the execution plan of the query.

From the above execution plan we can see that Sql Server is doing the table scan of the Customer table. Table scan means Sql server is reading all the data pages and rows of the table to find the records in the customer table. Even after finding the first record with CustomerId = 50000, Sql server will not stop searching till it reads the last row as it doesn’t know that there is only one record with CustomerId = 50000 unless it reads the last row.

Let us now go to the Messages tab and see the IO and Time Statistics.

From the above IO and Time statistics of the query, we can see that it is reading 18,182 data pages. Each data page is of 8KB size, so to get one Customer record Sql server is reading 142 MB of data. And it is taking 250 millisecond CPU time.

From the above result we can observe that Sql Server is doing lot of IO and consuming CPU resource to fetch just one Customer record. If you see such things in your environment on any transactional table then there is something terrible wrong. You should immediately solve such problems. next sections will guide you through how such problems can be solved using Indexes.

HEAP Table

A table without a Clustered Index is called as a Heap Table. Frankly speaking we should never have a HEAP table in an Online Transaction Processing System (OLTP). There are some .01% edge case scenario where we may go for heap table if we need faster DML (INSERT, UPDATE, DELETE) operations. If we see any transactional table without a Clustered index then you can assume the table is badly designed.

The Customer table at the current state is a HEAP table, as it doesn’t have any Clustered index. In the above execution plan we have seen that Sql Server is doing a Table Scan, we see this operation of ” Table Scan” only for the HEAP Table. In case of a Table without a Clustered index , table data is stored in a heap structure. It means data is stored in an unordered way.

What is the Solution for the above query problem where it is reading 142 MB of data and using a quarter of a second to return just one customer record? Clustered Index is the Solution for the above query problems. In the next article in this series of articles on Indexes I will explain how Clustered Index solves these problems. Will post this article on Clustered Index next weekend, till then bye and be safe.

Logon Triggers in Sql Server

Sql Server, Sql Server Tutorial, , , , ,

What is Logon Trigger?

Logon Triggers are special kind of Stored Procedure or an operation that gets executed automatically in response to a LOGON event. They get executed only after the successful authentication but before the user session is established. If authentication fails the logon triggers will not be fired.

Caution! : Logon Triggers are very useful feature, but a small mistake in the Logon Trigger may cause database server un-accessible to any user including SA user. For example in a Logon trigger you are referring to a non existent table or database will allow you to create it. But once it is created then no one will be able to connect to the server as Logon trigger errors out. So it is best advised to check whether you are able to establish a Dedicated Administrator Connection (DAC) with SQL Server Management Studio. Because in case you have created a wrong trigger and not able to to connect, then DAC connection comes for your rescue. As the DAC connection will not trigger the LOGON trigger execution and you will be able to connect to Sql Server and after connecting disable the incorrect trigger.

Uses of Logon Triggers

Below are the some of the usecase scenarios where LOGON triggers will be usefull:

  • To audit and control server sessions
  • Restricting logins to Sql Server
  • Restricting the number of sessions for a specific login
  • Restricting user from logging-in outside permitted hours

Understanding Logon Triggers with extensive list of examples

Create a login TestUser by executing the following script:

-- Create a login 
CREATE LOGIN TestUser WITH PASSWORD = 'TestPass1'

[ALSO READ] Data Manipulation Language (DML) Triggers

EXAMPLE 1: This example Logon trigger blocks the user TestUser from connecting to Sql Server after office hours.

Execute the following script to create a Logon trigger which blocks the TestUser user from connecting to Sql Server after office hours

CREATE TRIGGER LimitConnectionAfterOfficeHours
ON ALL SERVER FOR LOGON 
AS
BEGIN
 IF ORIGINAL_LOGIN() = 'TestUser' AND
  (DATEPART(HOUR, GETDATE()) < 9 OR 
                  DATEPART (HOUR, GETDATE()) > 18)
 BEGIN
  PRINT 'You are not authorized to login after office hours'
  ROLLBACK
 END
END

Now try to connect to Sql Server using the TestUser user after office hours

LogonTriggerExampleAfterHours

From the above result we can see that the Logon Trigger LimitConnectionAfterOfficeHours is blocking the TestUser from connecting to Sql Server after office hours

In case of Logon Triggers the PRINT statement result in the trigger will be logged to the Sql Server Error Log. Let us login as different user and check whether PRINT message in the Logon trigger is logged into the Error Log during the above failure attempt:

LogonTriggerErrorLog

Drop the above Logon Trigger by executing the following statement:

DROP TRIGGER LimitConnectionAfterOfficeHours ON ALL SERVER

[ALSO READ] Data Definition Language (DDL) Triggers

EXAMPLE 2: This example Logon Trigger blocks a user from establishing more than two user sessions at any given point of time

Execute the following script to create a Logon Trigger which blocks the TestUser from establishing more than two user sessions at any given point of time

CREATE TRIGGER LimitMultipleConcurrentConnection
ON ALL SERVER WITH EXECUTE AS 'sa' 
FOR LOGON
AS
BEGIN
 IF ORIGINAL_LOGIN() = 'TestUser' AND
  (SELECT COUNT(*) FROM   sys.dm_exec_sessions
   WHERE  Is_User_Process = 1 
    AND Original_Login_Name = 'TestUser') > 2
 BEGIN
  PRINT 'You are not authorized to login, as you already have two active user sessions'
  ROLLBACK
 END
END

Now try to establish more than two concurrent sessions for the TestUser:

LogonTriggerBlockMultipleConcurrentSessions

From the above image we can see that the TestUser was able to have two concurrent new query sessions, but the attempt to open the third session is blocked by the trigger.

Below is the error logged in the Sql Server Error Log:

LogonTriggerErrorLogMultipleSessions

In the above example trigger you can observe that, during trigger creation I have added the clause EXECUTE AS ‘sa’. The reason for this is only the users with VIEW SERVER STATE permission on the server can see all the executing session on the instance of Sql Server. And the users who don’t have this permission can see only the current session. Because of this I am executing the above trigger as ‘sa’. An alternative option for this is grant TestUser the VIEW SERVER STATE permission using GRANT VIEW SERVER STATE TO TestUser statement.

Drop the above Logon Trigger by executing the following statement:

DROP TRIGGER LimitMultipleConcurrentConnection ON ALL SERVER

[ALSO READ]
Introduction to Triggers
Data Manipulation Language (DML) Triggers
Data Definition Language (DDL) Triggers
INSERTED and DELETED Logical Tables

Data Definition Language (DDL) Triggers in Sql Server

Sql Server, Sql Server Tutorial, , , , , , , , , , , , , , ,

What is DDL Trigger?

Data Definition Language (DDL) Triggers are special kind of Stored Procedure or an operation that gets executed automatically when a DDL Statements like CREATE, ALTER, DROP, GRANT, DENY, REVOKE, and UPDATE STATISTICS statements are executed. Certain system stored procedures that perform DDL like operations can also fire DDL triggers.

Scope of DDL Triggers

DDL triggers can be DATABASE scoped or SERVER scoped. The DDL triggers with Server level scope gets fired in response to a DDL statement with server scope like CREATE DATABASE, CREATE LOGIN, GRANT_SERVER, ALTER DATABASE, ALTER LOGIN etc. Where as DATABASE scoped DDL triggers fire in response to DDL statement with DATABASE SCOPE like CREATE TABLE, CREATE PROCEDURE, CREATE FUNCTION, ALTER TABLE, ALTER PROCEDURE, ALTER FUNCTION etc.

[ALSO READ] Data Manipulation Language (DML) Triggers in Sql Server

Uses of DDL Triggers

DDL triggers will be very handy to audit and control the DDL changes. Below are such example scenarios:

  • Track the DLL changes
    • Track the DDL T-Sql statement which is fired
    • Who has fired the DDL statement. For example we may be interested in identifying who has dropped the table.
    • When the DDL statement is fired etc
  • Block user from doing some DDL changes like DROP TABLE, DROP PROCEDURE etc
  • Allow DDL changes only during specified window (i.e. only during particular hours of the day)

Understand DDL Triggers with extensive list of examples

To understand DDL triggers with extensive list of examples, let us create a demo database with a Table and a Stored Procedure by executing the following Script:

--Create Demo Database
CREATE DATABASE SqlHintsDDLTriggersDemo
GO
USE SqlHintsDDLTriggersDemo
GO
--Create Customer Table
CREATE TABLE Customer 
( CustomerId INT IDENTITY (1, 1) NOT NULL ,
  FirstName NVARCHAR(50), LastName NVARCHAR(50))
GO
--Create a Stored Procedure
CREATE PROCEDURE GetCustomers
AS
BEGIN
	SELECT * FROM Customer
END
GO

EXAMPLE 1: This example DDL trigger blocks user FROM modifying or dropping any Table in the SqlHintsDDLTriggersDemo database

Execute the following statement to create a Trigger which fires in response to ALTER TABLE and DROP TABLE statements

USE SqlHintsDDLTriggersDemo
GO
CREATE TRIGGER DDLTriggerToBlockTableDDL
ON DATABASE
FOR DROP_TABLE,ALTER_TABLE
AS
BEGIN
 PRINT 'Disable trigger DDLTriggerToBlockTableDDL to drop or alter tables'
 ROLLBACK
END

Now try to drop the Customer table by executing the following statement

DROP TABLE Customer

RESULT:

Disable trigger DDLTriggerToBlockTableDDL to drop or alter tables
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.

From the above result we can see that the DDL trigger DDLTriggerToBlockTableDDL is blocking the user from dropping the Customer table

Let us try to execute the following statement to add DOB column to the Customer Table

ALTER TABLE dbo.Customer ADD DOB DATETIME

RESULT:

Disable trigger DDLTriggerToBlockTableDDL to drop or alter tables
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.

From the above result we can see that the DDL trigger DDLTriggerToBlockTableDDL is blocking the user from adding DOB column to the Customer table

In Sql Server we have an option to disable the trigger without dropping it. Execute the below script to disable the trigger DDLTriggerToBlockTableDDL:

DISABLE TRIGGER DDLTriggerToBlockTableDDL ON DATABASE

Now try to re-execute the following statement to add the DOB column to the Customer Table

ALTER TABLE dbo.Customer ADD DOB DATETIME
GO
SELECT * FROM dbo.Customer

RESULT
ALTER TABLE After Disabling Trigger 1

Execute the following statement to enable back the Disabled trigger DDLTriggerToBlockTableDDL

ENABLE TRIGGER DDLTriggerToBlockTableDDL ON DATABASE

Now try to execute the following statement drop the newly added column DOB from the Customer table by executing the following statement

ALTER TABLE dbo.Customer DROP COLUMN DOB

RESULT:

Disable trigger DDLTriggerToBlockTableDDL to drop or alter tables
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.

We can drop the DDLTriggerToBlockTableDDL trigger by executing the following statement

DROP TRIGGER DDLTriggerToBlockTableDDL ON DATABASE

[ALSO READ] Logon Triggers in Sql Server

EXAMPLE 2: This example DDL trigger blocks user from creating, modifying or dropping any Stored Procedures in the SqlHintsDDLTriggersDemo database

USE SqlHintsDDLTriggersDemo
GO
CREATE TRIGGER DDLTriggerToBlockProcedureDDL
ON DATABASE
FOR DDL_PROCEDURE_EVENTS
AS
BEGIN
 PRINT 'Disable trigger DDLTriggerToBlockProcedureDDL to create, alter or drop procedures'
 ROLLBACK
END

After creating the above trigger, try to drop the stored procedure GetCustomers by executing the following statement

DROP PROCEDURE GetCustomers

RESULT:

Disable trigger DDLTriggerToBlockProcedureDDL to create, alter or drop procedures
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.

In the above example trigger, the trigger action DDL_PROCEDURE_EVENTS is an aggregate event name for the individual trigger event names: CREATE_PROCEDURE, ALTER_PROCEDURE and DROP_PROCEDURE. The system stored procedure SYS.TRIGGER_EVENT_TYPES lists out all the trigger events.

If you want to just block dropping of the stored procedure then in the FOR clause just mention the event DROP_PROCEDURE instead of DDL_PROCEDURE_EVENTS

The below query explains that the DDL_PROCEDURE_EVENTS is an aggregate event name for the individual trigger event names: CREATE_PROCEDURE, ALTER_PROCEDURE and DROP_PROCEDURE.

SELECT * FROM SYS.TRIGGER_EVENT_TYPES 
WHERE parent_type = 10024 or type = 10024
RESULT: TriggerEventTypes

We can drop this stored procedure DDL trigger by executing the following statement:

DROP TRIGGER DDLTriggerToBlockProcedureDDL ON DATABASE

EXAMPLE 3: This example server level trigger blocks user from creating, altering or dropping database

USE MASTER
GO
CREATE TRIGGER DDLTriggerToBlockDatabaseDDL
ON ALL SERVER
FOR DDL_DATABASE_EVENTS
AS
BEGIN
 PRINT 'Disable trigger DDLTriggerToBlockDatabaseDDL to create, alter or drop database'
 ROLLBACK
END

Note: Server scoped triggers need to be created in the MASTER database

After creating the above trigger, try to create the database TestDemoDB

CREATE DATABASE TestDemoDB

RESULT:

Disable trigger DDLTriggerToBlockDatabaseDDL to create, alter or drop database
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.

From the above result we can see that the DDL trigger DDLTriggerToBlockDatabaseDDL is blocking the user from Creating the database

In the above example trigger, the trigger action DDL_DATABASE_EVENTS is an aggregate event name for the individual trigger event names: CREATE_DATABASE, ALTER_DATABASE and DROP_DATABASE. The system stored procedure SYS.TRIGGER_EVENT_TYPES lists out all the trigger events.

If you want to just block creating of the new database then in the FOR clause JUST mention the event CREATE_DATABASE instead of DDL_DATABASE_EVENTS

The below query explains that the DDL_DATABASE_EVENTS is an aggregate event name for the individual trigger event names: CREATE_DATABASE, ALTER_DATABASE and DROP_DATABASE.

SELECT * FROM SYS.TRIGGER_EVENT_TYPES
WHERE parent_type = 10004 or type = 10004

RESULT:
DDLTriggerEventTypes

We can drop this DDL trigger by executing the following statement

DROP TRIGGER DDLTriggerToBlockDatabaseDDL ON ALL SERVER

[ALSO READ] INSERTED and DELETED Logical Tables in Sql Server

EXAMPLE 4: This example explains how we can use DDL triggers to log the stored procedure DDL statement, statement execution time, the user who has fired the statement etc

Let us execute the following statement to create a Audit log table: LogDDLEvents, to capture the Stored Procedure DDL statement

CREATE TABLE dbo.LogDDLEvents
( 	
 EventTime DATETIME,
 LoginName VARCHAR(50),
 TSQLCommand NVARCHAR(MAX) 
)

Now execute the following statement to create a SP DDL actions. This SP is using the EVENTDATA() function to capture the triggering SP DDL statement into the audit log table LogDDLEvents

USE SqlHintsDDLTriggersDemo
GO
CREATE TRIGGER DDLTriggerForSPDDL
ON DATABASE
FOR DDL_PROCEDURE_EVENTS
AS
BEGIN
 SET NOCOUNT ON
 DECLARE @EventData XML = EVENTDATA()
 INSERT INTO dbo.LogDDLEvents(EventTime,LoginName, TSQLCommand)	
 SELECT @EventData.value('(/EVENT_INSTANCE/PostTime)[1]'
                                  , 'DATETIME'),
  @EventData.value('(/EVENT_INSTANCE/LoginName)[1]'
                                  , 'VARCHAR(50)'),
 @EventData.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]'
                                  , 'NVARCHAR(MAX)')
END

Now try to create a Stored Procedure by executing the following statement

CREATE PROCEDURE GetAllCustomers
AS
BEGIN
    SELECT * FROM Customers
END

After executing the above statement let us verify the audit table: LogDDLEvents data by executing the following statement

SELECT * FROM dbo.LogDDLEvents

RESULT
DDL Trigger Audit Log Table
Let us check whether apart from adding to the audit log table: LogDDLEvents, the Stored Procedure is created or not by executing the following statement?

SP_HELPTEXT GetAllCustomers

StoredProcedure

We can drop this DDL trigger by executing the following statement

DROP TRIGGER DDLTriggerForSPDDL ON DATABASE

[ALSO READ]
Introduction to Triggers
Data Manipulation Language (DML) Triggers
INSERTED and DELETED Logical Tables
Logon Triggers