Today junior Application Developer asked me what are the causes of poor performance queries in SQL Server? This is a very open ended question and there could be a lot if reasons behind the poor performance of a query. But I try to put list together to answer to his question. I will be updating this list regularly. Performance enhancement of SQL Server queries can be a complex task, as performance depends on many factors both hardware and software.
.
1. Poorly designed queries
2. Too many complicated joins
3. Too many indexes on a table
4. Too few or no indexes on a table
5. Missing clustered index
6. Index fragmentation
7. Missing or out of date statistics
8. Too low or too high FILLFACTOR
9. Not normalized Databases design
10. Too many normalised tables
11. Transaction logs and data sharing the same drive
12. Too many constraints on a table
13. Lack of disk space
14. Wrong memory configuration
15. Lack of physical memory in the server
16. Lack of CPU power
17. Table scans in execution plan.
18. Excessive usage of CURSORS
19. Too many nested CURSORS
20. Not setting NOCOUNT when you have large tables
21. Excessive usage of transaction isolation level
22. Unnecessary linked server queries
23. Selecting too much data (Select * From table1)
24. Excessive query recompilations
25. Excessive use of temporary tables.
26. Lock and deadlocks
27. Network issues
28. Wrongly selected table and query hints
29. OS architecture
30. Excessive use of correlated subqueries
Leave a Comment