Posted in Performance Tuning, Uncategorized

SQL Server Execution Plan Operator – Table Scan

In my previous blog we discussed about the different format of SQL Server execution plan. Now in upcoming blogs we will discussed about the most commonly seen operators we see when execution plan generates. In this post I will start the easy one that is Table Scan operator.

As with the name of operator Table Scan it indicates that it returns the rows after scanning every row of a table.

When this operator appears in execution plan?

When there is no index present on the table then query optimizer has to scan every row of table to return the result set.

tablescan

In the returned result set data is retrieved after traversing data row by row.

When a table is having fewer rows and returning maximum rows then it’s faster for query optimizer to scan all rows rather than checking indexes.

That’s all for the day !!!

 

Advertisements
Posted in Development, Performance Tuning, Uncategorized

SQL Server Execution Plan formats

Execution plan helps in knowing how a query gets executed and troubleshooting a query which is performing poor.

In SQL Server execution plan can be of 2 types:

  1. Estimated Execution plan
  2. Actual Execution plan

Execution plan can be viewed in 3 different formats:

  • Text format
  • XML format
  • Graphical format

We can choose the format on depending what level of details we want to see.

Let’s discuss these format one by one.

  1. Text format

Execution plan in text format can be seen using these 3 methods.

  • Showplan_ALL
  • Showplan_Text
  • Statistics Profile

Showplan_ALL : Its shows the estimated execution plan of the query. To enable it we need to execute the query:

SET SHOWPLAN_ALL ON;

1

Once we analyze our query plan we can turn off this option by executing below query so that our subsequent queries will not get affected from this.

SET SOWPLAN_ALL OFF;

Showplan_Text: It’s also works with the estimated execution plan of the query. To enable it we need to execute the query:

SET SHOWPLAN_TEXT ON;

2

SET SHOWPLAN_TEXT OFF;

Statistics Profile: It works with the actual execution plan. We need to execute the below query to enable it:

SET STATISTICS PROFILE ON;

3

SET STATISTICS PROFILE OFF;

2 XML format plans

Execution plan can be seen in XML format by using following two methods:

Showplan XML

Statistics XML

Statistics XML: It is used to generate the execution plan in XML format. After clicking on XML link a graphical execution plan gets opened in a new window. We can save that graphical plan to our local disk with extension .sqlplan.

4

Showplan XML: It’s also generates the XML plan and only one result set appears in outcome with XML link.

5

3. Graphical Plan

This is the most easiest and readable option to generate the execution plan. It also has two option for generating execution plan:

Estimated execution plan

Actual execution plan

To enable the estimated execution plan we can press the shortcut key Ctrl +L. Also, we can navigate to Query tab and select Display Estimated Execution Plan.

Estimated execution plan is beneficial when we have complex query and not aware how much it will take for complete execution in such situation we can use this as this doesn’t execute the query to generate the plan.

6

Similarly, actual execution plan we can be enabled by using shortcut key Ctrl+M.

We can also activate it by selecting option Include Actual Execution Plan from Query menu tab.

That’s all for the day folks.

 

Posted in Development

SQL Server – Rollback transaction completely in case of error

Hi friends, I recently found an issue while inserting data into table during transactions that transaction will not completely rollback if we use multiple insert statement in transaction.

Let me give you a demo on this how to handle such situation.

Create table Test_tran

(

              ID Int Primary key,

                Name varchar(10)

)

Now let’s try to insert some data into this table in a transaction.

BEGIN TRAN

INSERT INTO Test_Tran ( ID, Name) VALUES (1,’Amit’)

INSERT INTO Test_Tran ( ID, Name) VALUES (2,’Kapil’)

INSERT INTO Test_Tran ( ID, Name) VALUES (1,’Aditya’)

COMMIT TRAN

As we can see that ID column has primary key defined on it so ID column can contain unique values only. But here we are trying to insert duplicate value in ID column in our third INSERT statement and it should got failed.

Let’s execute the query and see what will happen.

1

See the third statement has thrown the error and we cannot insert duplicate key in column ID.

But what happen with the first two statement will they got inserted or rollback.

We now check the data in the table test_tran.

SELECT * FROM test_tran

2

We can see that even when the transaction got failed records got inserted which is not the correct way. To avoid such situation we need to make the transaction atomic which means that either all the statements in the transaction executed successfully or none of them if any of the statement got failed.

Here now I am defining two methods to achieve the atomicity of transaction.

  1. Using Try/CATCH block
  2. Using XACT_ABORT

 

  1. TRY/CATCH block

 

We will rewrite the query using Try/Catch block

 

First I will delete all the records from the table

TRUNCATE TABLE Test_tran

 

BEGIN TRY

       BEGIN TRAN

       INSERT INTO Test_Tran ( ID, Name) VALUES (1,’Amit’)

       INSERT INTO Test_Tran ( ID, Name) VALUES (2,’Kapil’)

       INSERT INTO Test_Tran ( ID, Name) VALUES (1,’Aditya’)

       COMMIT TRAN

END TRY

BEGIN CATCH

       ROLLBACK TRAN

END CATCH

 

After executing above query we can see that no rows will get inserted into the table as it got rollback when an error occurred and we have achieved atomicity by using try/catch block.

 

  1. SET XACT_ABORT

 

We can also achieve the atomicity by setting XACT_ABORT to ON. By setting XACT_ABORT to ON we can rollback all the statement inside a transaction when an error occurred.

 

So we rewrite the code again in this manner.

SET XACT_ABORT ON

BEGIN TRAN

       INSERT INTO Test_Tran ( ID, Name) VALUES (1,’Amit’)

       INSERT INTO Test_Tran ( ID, Name) VALUES (2,’Kapil’)

       INSERT INTO Test_Tran ( ID, Name) VALUES (1,’Aditya’)

COMMIT TRAN

It will also rollback the transaction when error got occurred in third statement.

So friends we can use these two method to rollback the transaction completely and achieved atomicity.

Have a happy learning 🙂

 

Posted in Development

SQL Server – Conditions when value of @@error changes

Hi friends, when we do error handling in our SQL code we use different methods like TRY/CATCH, RAISERROR etc. to return the error messages. We also use global variable @@error to print the error value of the last T-SQL statement executed. But many folks doesn’t know that value of @@error gets reset with every execution of T-SQL statement. Today I will show you how @@error value gets changes automatically with every execution.

Let’s executed the below code to see what error value will return in output.

SELECT ‘Print error’

SELECT 1/0

Print @@error

1

In output we can see error value 8134 below the error message. This values comes from the system table sys.sysmessages

SELECT error as Error_Number, Severity, Description, msglangid as Language_Code

FROM sys.sysmessages

WHERE error = 8134  and msglangid = 1033

2.jpg

If we want to return our own custom error message then SQL has provide us facility to do that and we can use RAISERROR command for that.

Now let me add another SQL statement between that and re execute the query to see whether I am getting same value or not.

SELECT ‘Print error’

SELECT 1/0

Print @@error

SELECT ‘error’

PRINT @@error

As most of us still expecting the same value 8134 for the second print statement also.

What will be the error value for the second print statement?

3

Second print statement will return 0.

Why?

As we know that @@error return the error value of the last statement executed and it returns 0 if statement gets executed successfully.

Here SELECT ‘error’ is the last statement for the second Print @error statement and it’s always gets executed successfully so it returns 0.

That’s all for the day folks 🙂

Posted in Development

SQL Server – Columnstore Index creation error: A columnstore index cannot have INCLUDED columns

Hi friends, in my previous blog we learnt that sparse column cannot be part of columnstore index. In this blog we will discuss another limitation of columnstore index which is INCLUDE clause is not allowed while creating columnstore index.

Now, let’s create a test table and then we will try to create a nonclustered columnstore index using INCLUDE clause:

CREATE TABLE Test_Include

(

                ID INT,

                FirstName VARCHAR(10) NOT NULL,

                MiddleName VARCHAR(10),

                LastName VARCHAR(10) NOT NULL

)

Now let’s create a nonclustered columnstore index on this table using below script:

CREATE NONCLUSTERED COLUMNSTORE INDEX ix_TestInclude

ON Test_Include (ID, FirstName, LastName)

INCLUDE (MiddleName)

When we execute the above script it gives us error:

CREATE INDEX statement failed because a columnstore index cannot have included column. Create the columnstore index on the desired columns without specifying any included columns.’

 CS_2

Also, from the error message itself we come to know that included columns are not allowed in columnstore index.

Drop the table –

DROP TABLE Test_Include

So friends, this is another one of the limitation of nonclustered columnstore index. Hope you like it 🙂

Posted in Development

SQL Server – Columnstore Index creation error: A columnstore index cannot be created on a SPARSE column

Hi friends, as we know that in SQL Server 2012 a new feature Columnstore index was introduced which made a great change in area of performance optimization like reducing I/O. From SQL server 2012 to 2016 there were many changes made in columnstore index. In SQL Server 2012 only nonclustered columnstore index were introduced.

In the next few blogs we will discuss some limitations of columnstore index.

What is columnstore index?

From BOL: columnstore index is a technology for storing, retrieving and managing data by using a columnar data format, called a columnstore.

Now, let’s create a test table with a SPARSE column property in it and then we will try to create a nonclustered columnstore index including that column:

CREATE TABLE Test_Sparse

(

                ID INT,

                FirstName VARCHAR(10) NOT NULL,

                MiddleName VARCHAR(10) SPARSE,

                LastName VARCHAR(10) NOT NULL

)

Sparse column is used to handle NULL or ZERO values as it doesn’t take any space.

Now let’s create a nonclustered columnstore index on this table using below script:

CREATE NONCLUSTERED COLUMNSTORE INDEX ix_TestSparse

ON Test_Sparse (ID, FirstName, MiddleName, LastName)

When we execute the above script it gives us error:

CREATE INDEX statement failed because a columnstore index cannot be created on a sparse column. Consider creating a nonclustered columnstore index on a subset of column that does not include any sparse column.’

CS_1

From the error message itself we come to know that sparse column cannot be part of columnstore index.

Drop the table –

DROP TABLE Test_Sparse

So friends, this is one of the limitation of nonclustered columnstore. We will learn some more limitations of columnstore index in my next blog.

Hope you like it J

Posted in Development

SQL Server – Extended Events in SQL Server 2012

Hi friends, today we will discuss about new performance monitoring system Extended Events which gets introduced in version SQL Server 2012.In previous versions we were using SQL Trace for capturing events and SQL Profiler for managing and creating SQL trace.  SQL Server profile, SQL Trace deprecated in future version of SQL version so it suggested you guys  to start using Extended events in near future which is more lightweight.

In this blog we will create an extended event session following below steps:

  1. Open SSMS and connect to your SQL Server instance.
  2. Select database name from available database list. I have selected TSQL2012 database for this demo.
  3. Write following query in query window :

SELECT PP.productid, PP.productname, PC.categoryname

FROM Production.Products PP

INNER JOIN Production.Categories PC ON PP.categoryid = PC.categoryid

  1. In SSMS, expand Management folder Right click on the Session folder and Select New Session Wizard.

ExtendedEvent_1

5. On Introduction page, read the information and click Next.

ExtendedEvent_2

6. On Set Session Properties page, name the session ‘TestEvent’. Click Next.

ExtendedEvent_3

7. On Choose Template page, Select Do Not Use a Template and click Next.

ExtendedEvent_4

8. On Select Events To Capture page, type string sql in Event Library textbox and select sql_statement_completed. Move it to Selected events box and click Next.

ExtendedEvent_5

9. On Capture Global Field page, select the fields which you want to capture in this session. Click Next. I have selected few fields like CPU_id, database_id, database_name.

ExtendedEvent_6

10. On Set Session Event Filter page, create a filter to limit the data you want to capture. I have created filter for database name and sql text. In Field tab select value database_name and Value to be equal to ‘TSQL2012’ with operator value ‘=’. Also, create another filter with Field value sqlserver.sql_text and Value equals to ‘SELECT PP.Productid%’ with operator value ‘like_i_sql_unicode_string’. Click Next.

ExtendedEvent_7

11. On Specify Session Data Storage page specify the way you want to collect the data for analysis.Select option Work with only the most recent data (ring_buffer_target) and click Next.

ExtendedEvent_8

12. On Summary page, verify your selections that you made for this event session. Click Finish.

ExtendedEvent_9

13. On last page of Create Event Session page, select both checkboxes: Start the event session immediately after session creation and Watch live data on screen as it is captured. Click Close.

ExtendedEvent_10

14. Extended Events Live Window gets opened in new window.

15. You can check the new event session in the session folder. Right click on the newly created session ‘TestEvent’.

16. Execute the above query in query window and see event information in Live Data Window.

ExtendedEvent_12

Extended Events is a more lightweight monitoring system in comparison to SQL Trace. So friends start making use of extended events as SQL Trace will get deprecated in future versions.

That’s all folks for the day. Hope you like this post.