Impact of using local variable in Stored Procedure
Preparing an optimal execution plan for a given SQL query is crucial, and the Query Optimizer’s ability to do so heavily relies on the accuracy and freshness of the statistics…
Read more »Add a NOT NULL column to an existing table – Performance improvement SQL Server 2012
Why alter table add NOT NULL column takes long time? Adding a NOT NULL column to the existing table will run for longer time for high volume tables. Because SQL…
Read more »Identifying cached tables in SQL Server buffer
When a query is submitted it goes through a sequence of phases in SQL Server. Starting from parse, optimization, plan preparation until the execution of the plan. During the execution…
Read more »When did the last statistics update occur?
Statistics are the critical piece of information that SQL Server maintains for a table or index. The query optimization process depends on the statistics information to prepare an execution plan…
Read more »Benefits of including a non-key column in a non-clustered index
SQL Server supports to include non-key column as part of the non-clustered index. Having an index with the included column improves the query performance in several scenarios. This blog covers…
Read more »Currently executing SQL statements
Microsoft SQL Server introduced various dynamic management views and dynamic management functions to view the server and database state and it is very helpful for troubleshooting, performance tuning, monitoring and…
Read more »How to find the physical location of locked records in SQL Server
SQL Server by default locks the table or page or rows when it executes the transact statement. There are different types of locks such as exclusive, shared, schema modification lock…
Read more »Using constraint improves query performance
We know that the constraints are used to enforce the data integrity. A CHECK constraint is used to limit the value being entered into a column, similarly, unique constraint is…
Read more »Compile time of SQL statement
Query recompile and stored procedure recompile is not a good sign for performance for certain situations. The SQL statement by default cached for reuse, if the same query is requested…
Read more »