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