Friday 4 March 2016

What are the steps to take to improve performance of a poor performing query in SQL Server 2016?

Dear DBAs and Developers,
Please find below steps to take to improve performance of SQL Server queries:-


1)      Use indexes efficiently.

2)      Create partition on the big tables

3)      Run Update statistics for out of date statistics

4)      Check CUP utilization

5)      RAM Utilization

6)      Fully qualify database objects.

7)      Resolving Blocking issues

8)      Reduce the too much normalization

9)      SPs and Triggers are with Set NOCOUNT On

10)  Use locking and isolation level hints to minimize locking.

11)  Create all primary and foreign keys and relationships among tables.

12)  Avoid using cursors

13)  Triggers are with Set NOCOUNT On

14)  Avoid using Select*, rather mention the needed columns and narrow the resultset as needed.

15)  Reduce the too much normalization

16)  Use partitioned views

17)  Use temporary tables and table variables

18)  Using Temporary tables instead of Cursors

19)  Reduce joins and heavy clauses like GROUP BY if not needed

20)  Unnecessary  Distinct and Order by Clause

21)  Implement queries as stored procedures.

22)  Have a WHERE Clause in all SELECT queries.

23)  Use data types wisely

24)  Instead of NULLS use string values such as N/A

25)  Avoid expensive operators such as NOT LIKE.

26)  Add space to DB files or TempDB, if that are not having enough space

27)  Reduce the too much normalization

28)  SPs and Triggers are with Set NOCOUNT On

29)  Avoid Unnecessarily complicated joins

30)  Remove data type conversation is occurring

Please comment below if you know more way to imporve performance of query- Jainendra Verma

No comments:

Post a Comment