The below are the generic points needs to be considered while doing performance tuning.
1. If the stats are up to date then estimated rows and estimated execution will be approximately same in the execution plan. If there is huge difference then stats are outdated and required update.
2. Rebuild or re-organize the indexes and also create if the indexes are not available.
3. If update statistics or rebuilding the indexes doesn't help you bringing down the CPU then tune the query one by one.
3.
If the procedure is causing the CPU spike then
a.
Use SET NOCOUNT ON to disable no of effected rows message. It is required only to test or debug the code.
b.
Use schema name with the object name if multiple schemas exist in the database. This will helpful in directly finding the compiled plan instead of searching for the object in other schema. This process of searching schema for an object leads to COMPILE lock on SP and decreases the SP's performance. So always its better to refer the objects with the qualified name in the SP.
c.
Do not use the prefix "sp_" in the stored procedure name . If you use then it will search in the master database. Searching in the master database causes extra over head and also there are changes to get wrong resulyt if the same SP found in the master database.
d.
Use IF EXISTS (SELECT 1) instead of (SELECT * ) to check the existence of a record in another table. Hence EXIST will use True or False.
e.
If the query which is spiking linked server query try changing the security of linked server to ensure liked server user has ddl_admin or dba/sysadmin on the remote server.
f.
Try to avoid using the SQL Server cursors when ever possible and use while loop to process the records one by one.
g.
Keep the transaction as short as possible - The length of transaction affects blocking and deadlocking.Exclusive lock is not released until the end of transaction. For faster execution and less blocking the transaction should be kept as short as possible.
h.
Use Try-Catch for error handling it will help full to easily debug and fix the issues in case of big portion of code.
i. Return only the Rows and Columns needed.
j. Avoid expensive operators such as Not Like , != etc.
k.If you are checking the existance then use only IF EXISTS or IF NOT EXISTS instead <> or NOT LIKE or != .
k. Transaction usage should be :
Avoid long-running transactions.
Avoid transactions that require user input to commit.
Access heavily used data at the end of the transactions.
Try to access resources in the same order.
Use isolation level hints to minimize the locks.
Ensure that explicit transactions commit or roll back.
l . Avoid interleaving DDL and DML in Stored Procedure
Interleaving DDL and DML in stored procedures is one of the most common causes of store procedure recompiles. A common scenario is to create a temporary table , to insert data into that table , to create index and then to select the data from the table. This sequence of events typically causes a recompile . To avoid recompiles put all the DDL at the beginning of the stored procedures and put the DML after the DDL.
m. If you are using embedded SQL then follow the below steps to tune the queries .
a. Use table joins in place of sub query.
Ex: If A , B is many to one or one to one relationship.
Replace
SELECT *
FROM A
WHERE a.city IN (
SELECT b.city
FROM B
)
With
SELECT A.*
FROM A ,B
WHERE A.city = B.City
b. Replace Outer Join with Union
Replace
SELECT A.City ,B.City
FROM A ,B
WHERE A.STATE = B.STATE (+)
With
SELECT A.City ,B.CITY
FROM A ,B
WHERE A.STATE = B.STATE
UNION
SELECT NULL ,B.City
FROM B
WHERE NOT EXISTS (
SELECT 'X'
FROM A
WHERE A.STATE = B.STATE
)
n. If you are trying to filter number values then use >0 instead of null in where clause.
o. At SQL Query Level
Avoid
* Cross Join.
* Co-Related Subquery.
* Don't use distinct use Group by.
* Avoid in equality queries like
Select * from emp where empno<>30 instead of it write
Select * from emp where empno in (10,20,40 ...etc)
* Use Exist clause instead of IN clause.
* Create Indexes on source table so that data retrieved fastly.
* Use column names instead of * in Select clause.
* Don't use built-in functions unnecessarily like IsNull after where clause if you want you can
use it along with Select statement.
