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

 

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 Uncategorized

Importance of Database Backup In SQL Server

Overview

To save any organisation from any big future loss, one needs make a proper strategy or disaster plan that will save them in their hard time. When working with critical data it becomes very necessary to backup organisation data. In any organisation, for an administrator it is very important task to make a disaster plan because the risk of loosing data due to hardware failures, human error, natural catastrophes, network intrusions is always there.

When the SQL server met with disaster, admin needs a plan or a way to recover their precious data.

To safeguard SQL server databases there is SQL server backup and restore component that protects data from any catastrophic data loss.

Why backup a Database In SQL Server ?

In order to save data from such damages, user needs to backup the database regular basis that will also preserve data from modifications, table corruption, etc. there should be a proper backup and restore, that will safeguard organisation data against data loss caused due to various factors. In any organisation there are number of systems having crucial information in them, virus intrusions in one of the system can cause huge loss entire network. By keeping data in backup storage, one can smooth perform restore their data after even the damage.

Hardware Failure/Media Failure: It includes reasons like bad sector, hard drive crash, etc.

User Error: sometimes people unintentionally deletes data or make any alteration on data. For example- when user drop a table by mistake.

Natural disaster: one can not save their on-premises data from such natural tragedy, however, SQL server provides a service called Windows azure storage service, in this user can create an off-site backup in a different location and then in on-premises location.

There are also, Other reasons due to which need of backup arises-Power failure, mechanical damage to hardware, viruses and damaging malware.

Backup Strategy Best Practices in SQL Server

  • SQL server perform backup when the user is in active state and transactions are being processed, therefore one can only back when system in use
  • User can shorten their backup period by using data compression feature and also by saving database to disk. However, user must not use same disk for backup that storing database or transaction log.
  • Time to time backup the transaction log, as it covers all activities and keeps it very up-to date.
  • User must backup SQL server system database as it contains all vital information of system configuration and required in the event of complete restore.
  • User must ensure the security of database.

Conclusion

SQL database backup is a very important activity that one can perform to safeguard the data present in the SQL database. Backup is one way with which users of an SQL database can restore their data preventing a catastrophic loss. In case, at the time of disaster, if one doesn’t have any backup strategy, then going for an SQL Database Recovery Software would be a good choice to recover data from disaster without a backup.

Posted in Development, Uncategorized

Limitation of In-Memory OLTP in SQL Server 2016 – Merge not supported with memory tables as target

Hi friends, in this post we will discussed another limitation of In-Memory OLTP that in-memory tables as a Target in Merge statement is not supported.

Let’s first create two table: In-memory and standard table.

CREATE TABLE S_Merge

(

[Name] varchar(32) not null PRIMARY KEY ,

[City] varchar(32) null,

[State_Province] varchar(32) null,

[LastModified] datetime not null,

)

CREATE TABLE T_Merge

(

[Name] varchar(32) not null PRIMARY KEY NONCLUSTERED HASH

WITH (BUCKET_COUNT = 100000),

[City] varchar(32) null,

[State_Province] varchar(32) null,

[LastModified] datetime not null,

) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

GO

Now, I will insert some dummy data in S_Merge table –

INSERT INTO S_Merge (Name, City, State_Province, LastModified)

SELECT ‘A1’, ‘Jaipur’, ‘Rajasthan’, Getdate()

UNION ALL

SELECT ‘A2’, ‘GGN’,’Haryana’, Getdate()

Lim4.jpg

So, now we are ready to execute merge statement scenario.

MERGE T_Merge AS T

USING S_Merge AS S ON (T.Name = S.Name)

WHEN NOT MATCHED THEN

INSERT (Name, City, State_Province, LastModified)

VALUES (S.Name, S.City, S.State_Province, S.LastModified);

Here in this script I used my standard table S_Merge as Source and T_Merge (in-Memory table) as my target table. But when I execute this script it comes out with an error –

The operation ‘MERGE’ is not supported with memory optimized tables.’

Lim4_1

Now when I used it vice versa i.e. using In-memory table as Source and standard table as target using below script –

MERGE S_Merge AS T

USING T_Merge AS S ON (T.Name = S.Name)

WHEN NOT MATCHED THEN

INSERT (Name, City, State_Province, LastModified)

VALUES (S.Name, S.City, S.State_Province, S.LastModified);

It will get executed successfully without any error.

So friends, we came to know one more limitation of In-memory OLTP tables that we cannot use In-memory table as target in MERGE statement.

 

 

 

Posted in Development, Uncategorized

Limitation of In-Memory OLTP in SQL Server 2016 – Seed and Increment value other than 1 not supported

Hi friends, in this blog we will discussed another limitation of In-Memory OLTP that seed and increment value other than 1 while defining identity property to a column is not supported.

Let’s quickly see a demo on this –

First we will create a table defining identity property with seed 1 and incremental value 1.

CREATE TABLE T4

(

ID INT IDENTITY(1,1),

[Name] varchar(32) not null PRIMARY KEY NONCLUSTERED HASH

WITH (BUCKET_COUNT = 100000),

[City] varchar(32)  null,

[State_Province] varchar(32)  null,

) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

When I execute this query, table will created successfully.

Now, let’s change the identity value (1,1) to (1,4) and re run the query.
We will drop the table T4 and re execute the query modifying identity property.

DROP TABLE T4

Recreate the table again –

CREATE TABLE T4

(

ID INT IDENTITY(1,4),

[Name] varchar(32) not null PRIMARY KEY NONCLUSTERED HASH

WITH (BUCKET_COUNT = 100000),

[City] varchar(32)  null,

[State_Province] varchar(32)  null,

) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

Oops we got an error ‘The use of seed and increment values other than 1 is not supported with memory optimized tables.’

Lim3

So, finally we got to know one more limitation of In-memory OLTP tables that no seed and increment value other than 1 is supported with it.

That’s all for the day folks.

Posted in Development, Uncategorized

Limitation of In-Memory OLTP in SQL Server 2016 – TRUNCATE table not supported

Hi friends, another limitation that we will discussed is TRUNCATE table command is not supported with memory optimized tables.

Let’s see a demo on this.

First we will create a memory optimized table.

CREATE TABLE T2

(

[Name] varchar(32) not null PRIMARY KEY NONCLUSTERED HASH

WITH (BUCKET_COUNT = 100000),

[City] varchar(32) not null,

[State_Province] varchar(32) not null,

[LastModified] datetime not null,

INDEX T1_ndx_c2c3 NONCLUSTERED ([City],[State_Province])

) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

 

Once the table gets created, we will try to add one more column with LOB datatype.

TRUNCATE TABLE T2

But as we run this statement it comes up with an error –

‘The statement ‘TRUNCATE TABLE’ is not supported with memory optimized tables.’

Lim2

So, we got to know limitation of In-memory OLTP is that TRUNCATE table command is not supported in In-memory table. Might be this limitation also gets over with future CTP versions of SQL Server 2016.

Have a happy learning.

Posted in Development, Uncategorized

Limitation of In-Memory OLTP in SQL Server 2016 – LOB datatypes not supported

Hi friends, Wish you all a very Happy New Year. This is my first blog of 2016 and starting it with SQL 2016 J . Now a days I am exploring In-Memory features and changes done in SQL 2016 CTP versions. I have installed CTP 3.0 currently and comparing changes done in SQL 2016 from SQL 2014 In-memory features.

For next few blogs I will try to cover the limitation of In-Memory OLTP feature.

First limitation that we will discussed is support of LOB datatypes. LOB datatypes like varchar(max), nvarchar(max), XML etc. are not supported in memory optimized tables.

Let’s see a demo on this.

First we will create a memory optimized table.

CREATE TABLE T2

(

[Name] varchar(32) not null PRIMARY KEY NONCLUSTERED HASH

WITH (BUCKET_COUNT = 100000),

[City] varchar(32) not null,

[State_Province] varchar(32) not null,

[LastModified] datetime not null,

INDEX T1_ndx_c2c3 NONCLUSTERED ([City],[State_Province])

) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

 

Once the table gets created, we will try to add one more column with LOB datatype.

ALTER TABLE T2

ADD Description nvarchar(max) NULL

Alter table T2

ADD Description XML

But the column does not gets added and comes up with an error –

‘The type ‘nvarchar(max)’ is not supported with memory optimized tables.’

Lim1

So, we got to know limitation of In-memory OLTP is that LOB datatypes are not supported in In-memory table. Might be this limitation gets over with future CTP versions of SQL Server 2016.

Have a happy learning.

Posted in Uncategorized

SQL SERVER – Random function in Dynamic Data Masking in SQL Server 2016 CTP 3

Hi friends,

With new CTP version of SQL Server 2016 many new functionality are added and modified. As with introduction SQL 2016 new functionality Dynamic data masking get introduced which helps in protecting the confidential data from users who do not have access to view it. In earlier CTP version data masking were of three types:

  1. Default
  2. Custom
  3. Email

I have already written about these in my earlier blogs here :

http://kapilsqlgeek.com/2015/07/26/sql-server-2016-blogs/

In CTP 3.0 of SQL 2016 one more type gets added with name Random.

Random function can be used for any numeric datatype to mask the value with any random number defined within a specified range.

Random ([start range], [end range])

In this blog I will give you the overview of new Random function with an example.

Let’s first create a table Demo_Masking with some data in it.

Create table Demo_Masking

(

EmployeeId INT,

FirstName varchar(10),

LastName varchar(20),

Salary INT

)

GO

—-INSERT dummy data into Demo_masking table

INSERT INTO Demo_Masking (EmployeeId, FirstName, LastName, Salary)

SELECT 11, ‘Kapil’, ‘Singh’, 50000

UNION ALL

SELECT 12, ‘Ankit’,’Sharma’,35000

UNION ALL

SELECT 13, ‘Aditya’, ‘Sinha’, 45000

M1

Here, I have created an employee with some columns, but I don’t want user to see the salary of other users so I will mask it with any random numbers.

Now, I am altering the table Demo_masking and adding masking function on columns Salary using this syntax:

ALTER TABLE Demo_Masking

ALTER COLUMN Salary ADD MASKED WITH (FUNCTION = ‘random(1,90000)’);

After applying masking, I will create a user ‘Masking’ which will have only read access (SELECT) on table Demo_Masking.

CREATE USER Masking WITHOUT LOGIN

GRANT SELECT ON Demo_Masking to Masking

Now let me execute below query –

SELECT * FROM Demo_Masking

GO

EXECUTE AS USER = ‘Masking’

SELECT * FROM Demo_Masking

REVERT

M2

After executing queries with current user and new user which has only SELECT rights we see that the salary column is showing random values for it and restricting the user to see the original salary of other users. In this way we can use random function to mask the data with any random numbers.

Hope you will like this post.

Posted in Development, Uncategorized

SQL Server – DROP IF EXISTS in SQL Server 2016

Hi friends, in SQL Server 2016 to check the existence of the object a new optional clause IF EXISTS gets introduced which can be used with the existing DROP statement.

Previously, within IF clause we were checking whether the object is exist or not.

Here is the syntax that we were using in previous versions of SQL server:

–Previous version syntax

IF OBJECT_ID(‘dbo.Test_IfExists’,’U’) IS NOT NULL

DROP TABLE Test_IfExists

IF EXISTS (SELECT * FROM sys.objects where name = ‘Test_IfExists’)

DROP TABLE Test_IfExists

Now, in SQL Server 2016 we can rewrite the above statements in this way –

—DROP IF EXISTS 2016

DROP TABLE  IF EXISTS Test_IfExists

Cool, see the length of code is reduced now and looks simpler :).

Syntax of DROP IF EXISTS i.e. DIE is –

DROP object_type IF EXISTS object_name

Some object_type that be included in DROP clause are like –

Tables, Database, Function, Trigger, Stored Procedure, Column, User, View, Schema etc……

Similarly we can drop the column from a table in this manner –

Syntax

ALTER object_type object_name

DROP object_type If EXISTS object_name

Here we drop the column of a table –

ALTER TABLE Test_Ifexists

DROP COLUMN If EXISTS Name

Hope you like this post !!

Posted in Development, Uncategorized

Steps to create SQL database at Azure

Hi friends, in this blog we will learn how we can create the SQL database at Microsoft Azure.

Login into your Azure account and move to portal.

  1. Move to left side pane of portal and click on SQL databases.

1

2. Currently no database is available so we click on create a SQL database link. A new window will pop up like this –

2

Fill the required details in the create database window as Name of the database, collation of the database etc. I kept the rest of the details as default only you can change the settings as per your requirements.

3. Also select the option ‘New SQL database server’ option to create a new database server in SERVER point. Click on Arrow to further proceed to fill new database server details.

3

Fill the details Login name and Password to login into the server. Also, select your region and click on correct sign to complete this window.

4. In the SQL database pane we can see the new entry of the sql database that we created.

4

5. We can also check the server details from the Servers tab.

5

6. After creating the database, we can also get the option to export the database.

6

7. Click on Export link to export the database. A new window of Export Database gets opened.

7

Specify the filename by what name you want to export the database with extension .bacpac.

8. Now, choose the option to create the new storage account. Again, a new window gets opened of Create Storage Account. Fill the required details as URL, new container name and click on OK sign.

8

Once the database gets created we get the option of opening the database in Visual studio.

9. Click on Open In Visual Studio option. You also get the prompt to add the IP address in the firewall setting, allow that and proceed further.

9

10. After creating firewall rule allowing connection from your IP address, click Open.

11

12

After performing all the above steps, a new azure sql database is ready.

Hope you like this post.