Database Simplified Headline Animator

Database Simplified

Sunday 10 June 2012

Bad Performance Of View In SQL Server

In this post I am going to describe the problem I encountered in a view, which may affect your view performance badly. Create tables from following script

If(OBJECT_ID('PerfTest1') is Not null)
Drop Table PerfTest1 Create Table PerfTest1(Id int Identity(1,1),Code Int,CodeName Char(30)) Insert Into PerfTest1 Values(1,'Archo'),(2,'Halie'),(3,'Raiye'),(4,'Amity')
Go 300 If(OBJECT_ID('PerfTest2') is Not null)
Drop Table PerfTest2 Create Table PerfTest2(Id int Identity(1,1),Code Int,CodeName Char(40)) Insert Into PerfTest2 Values(120,'LIVE'),(99,'ACTION'),(100,'SLOW'),(200,'PROCESS'),(200,'OUTDOOR')
Go 300

Create Following View

Create View Vw_PerfUnion
As
Select CodeName From PerfTest1
Union
Select CodeName From PerfTest2
Go

Run Following Query and Check the Execution Plan

Select * from Vw_PerfUnion Where CodeName='Live'

image

Its obvious that table scan will take since no index has been create on tables. Now lets create index on these tables.

 

Create NonClustered Index IX_PerfTest1 On PerfTest1(CodeName)
Go Create NonClustered Index IX_PerfTest2 On PerfTest2(CodeName)
Go

Now Run Query Again and Check Execution Plan

Select * from Vw_PerfUnion Where CodeName='Live'

image

As you can see in above screen shot even after creating index on table PerfTest1 Table Scan is taking Place. This is due to the Datatype mismatch between Two Query in the view. Check the datatype of PerfTest1(CodeName) and PerfTest1(CodeName) Fields they are Char(30) and Char(40) respectively. Now Lets alter the Datatype of PerfTest1 Table. To alter field we need to drop index we have created on that field.

Drop Index PerfTest1.IX_PerfTest1
Go
Alter Table PerfTest1 Alter Column CodeName Varchar(40)
Go
Create NonClustered Index IX_PerfTest1 On PerfTest1(CodeName)
Go

Now run the Query and Check Execution Plan

image

Sunday 3 June 2012

Restore Database From Snapshot

This is another way of restoring database to a time when snapshot was taken but it has certain restrictions lets see what are those ?

  • Database should have only one snapshot.
  • Cannot restore files removed after taking snapshot.
  • Transaction log backup chain is broken So make sure you take full backup before restore.
  • Restore From snapshot will drop all full text catalogs.

You must understand that restoring database from snapshot is meant to bring your database to stage when snapshot was taken it cannot fix your data corruption issues.

Note : If you have multiple snapshot then you  must delete all the snapshot except one you want to revert to.

Syntax :

USE Master
Go RESTORE DATABASE DBName from
DATABASE_SNAPSHOT = 'SnapShot_DBName'
GO

During restore operation both actual database and snapshot data will be unavailable (i.e. Restoring Mode).