About Me

My photo
Mumbai, Maharastra, India
He has more than 7.6 years of experience in the software development. He has spent most of the times in web/desktop application development. He has sound knowledge in various database concepts. You can reach him at viki.keshari@gmail.com https://www.linkedin.com/in/vikrammahapatra/ https://twitter.com/VikramMahapatra http://www.facebook.com/viki.keshari

Search This Blog

Showing posts with label Locks in SQL Server. Show all posts
Showing posts with label Locks in SQL Server. Show all posts

Monday, January 28, 2013

Cluster Index physically stores data… Myth? Whether true, let’s do the practical!!!


I came across many people saying cluster index physically sort the data in the table with the cluster key. Is it correct, let’s see doing a practical session… 

Lets create a table ClusterIndexOrderDemo with cluster index ie with primary key and with two column sno and fname.

create table ClusterIndexOrderDemo
(
      sno int not null primary key,
      fname varchar(10) not null
)
Command(s) completed successfully.

Now we will be inserting few rows… see the data for sno field, it is not in order.

insert into ClusterIndexOrderDemo
select 15, 'Vaidik' UNION ALL
select 25, 'Aakshat' UNION ALL
select 05, 'Dhanya'

Trace Flag 3604 forces the output to the client (in our case SSMS result pane) and DBCC Page gives you the page details

dbcc TraceOn(3604)
DBCC execution completed.

Now we will be firing DBCC IND – used to know the page details of tables & indexes.
Syntax :

 DBCC IND ( {dbname}, {table_name},{index_id} )Were,
·         dbname : your database;
·         table_name: your table name
·         index_id: for cluster index 1 , for non cluster index its 2


dbcc IND ('testDemo', 'ClusterIndexOrderDemo', 1)

PageFID PagePID     IAMFID IAMPID      ObjectID    IndexID     PartitionNumber PartitionID          iam_chain_type       PageType IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID
------- ----------- ------ ----------- ----------- ----------- --------------- -------------------- -------------------- -------- ---------- ----------- ----------- ----------- -----------
1       71          NULL   NULL        309576141   1           1               72057594039828480    In-row data          10       NULL       0           0           0           0
1       70          1      71          309576141   1           1               72057594039828480    In-row data          1        0          0           0           0           0

(2 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

You will be getting two rows, check for Page Type column where it is 1 see and note the corresponding page number and file number. Here in our case I am getting  page number as 70 and file id as 1.

Now I can run DBCC Page command. So whats the syntax ?
Dbcc page ({'dbname'|dbid}, filenum, pagenum [,printopt={0|1|2|3}]

Here in our case the database is testDemo, fileId is 1, page id is 70 and print option we are providing as 2 which means all page info dump.

DBCC PAGE ('testDemo',1,70,2)

Pick the offset table from output

OFFSET TABLE:

Row - Offset                        
2 (0x2) - 117 (0x75) (Akshat)               
1 (0x1) - 96 (0x60)  (Vaidik)               
0 (0x0) - 139 (0x8b) (Dhanya)  

Here the 117, 96, 139 are location where as the 2,1,0 are offset.            

Now we will be entering few more record and then will check the location and the offset.

insert into ClusterIndexOrderDemo
select 20, 'Radhika' UNION ALL
select 30, 'Silpa' UNION ALL
select 10, 'Sanjana'

dbcc page ('testDemo',1,70,2)

OFFSET TABLE:

Row - Offset                        
5 (0x5) - 182 (0xb6)  (Silpa)              
4 (0x4) - 117 (0x75)  (Akshat)              
3 (0x3) - 160 (0xa0)  (Radhika)              
2 (0x2) - 96 (0x60)   (Vaidik)              
1 (0x1) - 202 (0xca)  (Sanjana)              
0 (0x0) - 139 (0x8b)  (Dhanya)

Conclusion: So we can see here that the place where record got added that location is not changed at all. Instead based on the clustered index key it maintains the correct order within this Row Offset table.

So Clustered Index physically orders the data within a table? NO

Code award energy, No boost up drink required 


Post Reference: Vikram Aristocratic Elfin Share

Wednesday, January 23, 2013

Is it possible to create INDEX on computed column? Let’s try out!!!


It is always good to have computed column to improve the performance in specific scenarios. But in order to improve performance one need to implement INDEX on computed, but creating index on computed column is not as simple as on regular column. There are certain requirements for creating INDEX on computed column.

Let’s table a practical scenario to explain the way to create index on computed column:
The below is the UDF which the computed column will take in use while table creation.

create function sp_calulatePay(@basicPay int, @hra int)
returns int
with schemabinding
as
begin
      return (@basicPay + @hra)
end

Let’s create table with two computed column, the totalPay column will be using UDF sp_calulatePay.

create table ComputedIndexTesting
(name varchar(10),
dob datetime,
dobPlusOneYear as (dateadd(year,1,dob)) persisted,
basicPay int,
hra int,
totalPay as dbo.sp_calulatePay(basicPay,hra)
)
Command(s) completed successfully.

Let’s have some sample data inserted to this table

insert into ComputedIndexTesting (name,dob,basicPay,hra)
select 'Radhika','05/02/1986',1000,500 union all
select 'Vedika','05/03/1986',1000,500 union all
select 'Shyam','05/04/1986',1000,500
(3 row(s) affected)

Lets find out the values of computed column by firing the below select query

select * from ComputedIndexTesting

name       dob                     dobPlusOneYear          basicPay    hra         totalPay
---------- ----------------------- ----------------------- ----------- ----------- -----------
Radhika    1986-05-02 00:00:00.000 1987-05-02 00:00:00.000 1000        500         1500
Vedika     1986-05-03 00:00:00.000 1987-05-03 00:00:00.000 1000        500         1500
Shyam      1986-05-04 00:00:00.000 1987-05-04 00:00:00.000 1000        500         1500

(3 row(s) affected)

Now let’s check our computed columns for index creation using COLUMNPROPERTY ISINDEXABLE

select 
(select
case COLUMNPROPERTY( OBJECT_ID('ComputedIndexTesting'), 'dobPlusOneYear','IsIndexable')
when 0 then 'No'
when 1 then 'Yes'
end)
as 'dobPlusOneYear is Indexable ?',

(select
case COLUMNPROPERTY( OBJECT_ID('ComputedIndexTesting'), 'totalPay','IsIndexable')
when 0 then 'No'
when 1 then 'Yes'
end)
as 'totalPay is Indexable ?'
//OUTPUT

dobPlusOneYear is Indexable ? totalPay is Indexable ?
----------------------------- -----------------------
Yes                           Yes

(1 row(s) affected)

The result shows both the column are indexable.
Now here our task starts, there are two important requirements that may need planning and analysis while creating indexes on computed columns in SQL Server
  • ·         Determinism requirements
  • ·         Precision requirements


Determinism : we have to make sure that the expression of our computed column is always the same for specific inputs. This can be checked by COLUMNPROPERTY function and IsDeterministic

select 
(select
case COLUMNPROPERTY( OBJECT_ID('ComputedIndexTesting'), 'dobPlusOneYear','IsDeterministic')
when 0 then 'No'
when 1 then 'Yes'
end)
as 'dobPlusOneYear is Deterministic ?',

(select
case COLUMNPROPERTY( OBJECT_ID('ComputedIndexTesting'), 'totalPay','IsDeterministic')
when 0 then 'No'
when 1 then 'Yes'
end)
as 'totalPay is Deterministic ?'

dobPlusOneYear is Deterministic ? totalPay is Deterministic ?
--------------------------------- ---------------------------
Yes                               Yes

(1 row(s) affected)
This show both the computed columns are deterministic.

We can also check our UDF for deterministic by:

SELECT objectproperty(object_id('dbo.sp_calulatePay'),
'IsDeterministic') IsUDFDeterministic
IsUDFDeterministic
------------------
1

(1 row(s) affected)

So we can see UDF is deterministic as well.

NOTE: You need to create UDF with SCHEMA BINDING to make it deterministic. And since we have created our UDF with schema binding, it shows it is deterministic in nature.

Precision requirements: This is solely depends on the data type that are involved in your expression or in UDF for deriving values for your computed column. You can use non-precise value in your computed column like float, real but then you can not create index on such computed column.
Now lets see, whether our computed column are Precise in nature.

select 
(select
case COLUMNPROPERTY( OBJECT_ID('ComputedIndexTesting'), 'dobPlusOneYear','IsPrecise')
when 0 then 'No'
when 1 then 'Yes'
end)
as 'dobPlusOneYear is IsPrecise ?',

(select
case COLUMNPROPERTY( OBJECT_ID('ComputedIndexTesting'), 'totalPay','IsPrecise')
when 0 then 'No'
when 1 then 'Yes'
end)
as 'totalPay is IsPrecise ?'
dobPlusOneYear is IsPrecise ? totalPay is IsPrecise ?
----------------------------- -----------------------
Yes                           Yes

(1 row(s) affected)

So now we are confirmed that both the computed column are Precise as well as Deterministic, so we can create index on computed column, let create the index on it.

create index index_dobPlusOneYear
on dbo.ComputedIndexTesting
(
dobPlusOneYear
)
Command(s) completed successfully.

create index index_totalPay
on dbo.ComputedIndexTesting
(
totalPay
)
Command(s) completed successfully.

Your index is now created on computed column.

Conclusion: For creating index on computed column, two condition should meet first computed column should be:     
  • ·         Deterministic
  • ·         Precise


Coding: the straightforward among all SDLC  

Post Reference: Vikram Aristocratic Elfin Share

Saturday, July 2, 2011

Lock up your transaction...

Lock types

There are three main types of locks that SQL Server 7.0/2000 uses:
·  Shared locks
·  Update locks
·  Exclusive locks

Shared locks are used for operations that do not change or update data, such as a SELECT statement.

Update locks are used when SQL Server intends to modify a page, and later promotes the update page lock to an exclusive page lock before actually making the changes.

Exclusive locks are used for the data modification operations, such as UPDATE, INSERT, or DELETE.

Shared locks are compatible with other Shared locks or Update locks.

Update locks are compatible with Shared locks only.

Exclusive locks are not compatible with other lock types.

Let me to describe it on the real example. There are four processes, which attempt to lock the same page of the same table. These processes start one after another, so Process1 is the first process, Process2 is the second process and so on.

Process1 : SELECT
Process2 : SELECT
Process3 : UPDATE
Process4 : SELECT

Process1 sets the Shared lock on the page, because there are no another locks on this page.
Process2 sets the Shared lock on the page, because Shared locks are compatible with other Shared locks.
Process3 wants to modify data and wants to set Exclusive lock, but it cannot make it before Process1 and Process2 will be finished, because Exclusive lock is not compatible with other lock types. So, Process3 sets Update lock.
Process4 cannot set Shared lock on the page before Process3 will be finished. So, there is no Lock starvation. Lock starvation occurs when read transactions can monopolize a table or page, forcing a write transaction to wait indefinitely. So, Process4 waits before Process3 will be finished.
After Process1 and Process2 were finished, Process3 transfer Update lock into Exclusive lock to modify data. After Process3 was finished, Process4 sets the Shared lock on the page to select data.