Different techniques to speed up Query and Stored Procedure in SQL Server

Different techniques to speed up Query and Stored Procedure in SQL Server

1.  Create NONClustered Index idx_IndexID ON TableName(FieldName)

22. UPDATE sTATISTICS TABLENAME  WITH FULLSCAN 

The Update Statistics task ensures the query optimizer has up-to-date information

about the distribution of data values in the tables.

This allows the optimizer to make better judgments about data access strategies.

-- Create a job for statistics update using maintenance wizard plan in management

                1.In Wizard select seperate schedules foreach task

                2.then select update statistic in check    

or

--Run 'sp_UpdateStats' in every Database

33.       Remove Order by from queries

44. sp_recompile SPName

sp_recompile [ @objname = ] 'object'

e.g.
sp_recompile nc_hse_sp_VehicleSubForm

55. Drop and create SP - DROP PROCEDURE spName

Drop SP and Re Create Same SP again to speed up. Dropping SP will remove Execution plan and speed up SP Statements.

like DROP PROCEDURE spName

6. SHRINK DATABASE by right click on database then task and shrink

7. Memory optimization

1.Right Click on Server name

2.Then Select Memory then Check 'Use AWE to allocate Memory'

3.Set Min and Max Memory as per need

8. Index fragmentation Rebuild

 GO TO TABLE and INDEX and select REBUILD