SQL Hints: NOLOCK and NOWAIT Explained

As we know SQL Server uses pessimistic locking to protect data inconsistency. As part of its pessimistic locking strategy, it ensures data can be accessed safely, without the risk of data corruption, dirty reads or unrepeatable reads using locks. e.g. when you update any row within a transaction, locks are applied at row level which...

Pagination in SQL Server 2012 and above

Prior to SQL Server 2012, implementing pagination with millions of records was a big challenge for any developer. A commonly used workaround to implement pagination in SQL Server  was the ROW_NUMBER() function. The ROW_NUMBER function, like many of its siblings (i.e. RANK etc.), is a window function that returns a sequential integer value per row...

Stored Procedure Performance Optimisation Tips- Part 1

SQL Server gives us several ways to write a query and it's up to us, how are we using them effectively. The essential thing is "always check execution plan" if you are writing any code. Sometimes we write a stored procedure smartly which works perfectly as far as functional outcome is concerned but may impact...

Capture Compiled parameter values from cached execution plans

I am sure we all must have faced performance issues where queries/procedures some days run great, but on other days run much slower. There can be many factor causing the slowness however in most of the cases, this is caused by query execution plans being compiled with non-optimal parameter values. Basically when a query executes...

Performance Stats Analysis with Statistics Parser

I have seen a lot of people using STATISTICS IO and STATISTICS TIME commands to measure query performance and these are generally very useful and effective for measuring or comparing the performance stats. However it gets tricky when you start using these to troubleshoot your lengthy script that references multiple tables resulting in a very...

Understanding SQL Server indexes- Heap table vs Clustered Index

 We often hear about Heap, Clustered Indexes in our day to day job. I will try to explain them here in a simpler way. Heap Table:  What we know about heap table is "A heap is a table without a clustered index". Or you can say "without any clustered index our data gets stored in a table in non-specific...