Posted in Admin

Performance Tuning Part -11 – Calculations in conditions

Avoid using calculations on the table columns in conditions (where clause or Join predicates). A calculation on the left side of where clause or join…

Continue Reading... Performance Tuning Part -11 – Calculations in conditions
Posted in Admin

Performance Tuning Part -10 -NoCount

Use ‘SET NOCOUNT ON’ in procedures. SQL server writes the number of rows affected to the client by default, which is not useful for application…

Continue Reading... Performance Tuning Part -10 -NoCount
Posted in Admin

Performance Tuning part -9 – Views

Summary: We should use view if and only if it is really necessary. Use of the view causes performance problem because it still fetches the…

Continue Reading... Performance Tuning part -9 – Views
Posted in Admin

Performance Tuning Part-8-Temporary tables

Tuning Tip: Always create the index after dumping the data in to temporary tables. Loading the data after index creation takes more time and causes…

Continue Reading... Performance Tuning Part-8-Temporary tables
Posted in Admin

Performance Tuning part -7-Print Statement

Do not use the print statement in production (If required, use variable to capture the message for logging). Developers often use print statement to debug…

Continue Reading... Performance Tuning part -7-Print Statement
Posted in Admin

Performance tuning Part -6-correlated sub-queries

Avoid correlated sub-queries. Correlated sub-queries cause more reads and increase the overall execution time. Demo Note: AdventureWorks2017 database has been used for this demo First…

Continue Reading... Performance tuning Part -6-correlated sub-queries
Posted in Admin

Performance Tuning Part 5- Variable Declaration

Define variable with the proper data type. Improper data type may cause conversions which is not good for performance. Execution plan As you can see,…

Continue Reading... Performance Tuning Part 5- Variable Declaration
Posted in Admin

Performance Tuning Part -4 (Constant Scan, Compute Scalar, Parameter sniffing and Temp DB spill)

Scripts: CREATE OR ALTER PROCEDURE Proc_SalesDetails (@UnitPrice FLOAT,@TaxAmt FLOAT) AS BEGIN Select A.SalesOrderID,A.CarrierTrackingNumber,C.TerritoryID ,B.SalesOrderNumber FROM Sales.SalesOrderDetail A INNER JOIN Sales.SalesOrderHeader B ON A.SalesOrderID=B.SalesOrderID INNER JOIN…

Continue Reading... Performance Tuning Part -4 (Constant Scan, Compute Scalar, Parameter sniffing and Temp DB spill)
Posted in Admin

Simple tips to create effective indexes

Continue Reading... Simple tips to create effective indexes
Posted in Admin T-SQL

Performance Tuning Tip#2

Tip:┬áDo not use Order by unless the output is not required in sorted format. If ‘Order by’ is required then include only required columns in…

Continue Reading... Performance Tuning Tip#2
Posted in Admin

Memory bottleneck in SQL Server

How to Identify: SQL Server is Designed to consume all the memory on the server. High memory utilization is often considered as normal in SQL…

Continue Reading... Memory bottleneck in SQL Server