These are major problem that badly affects SQL Server performance. If you already know this problem area then you can design database and queries with better understanding.
- Poor Indexing: Without proper indexing SQL Server needs to retrieve and process much more data when executing. That means more use of disk,memory and CPU, and also increased execution time. That can lead to bloacking and deadlocks.
- Inaccurate Statistics: Accurate data distribution statistics are important for SQL Server so that it can design optimized execution plan accordingly.
- Poor Query Design: SQL queries should be written to make best use of indexes.Poorly designed queries can lead to increased execution time and thus causing blocking and deadlocks.
- Poor Execution Plans: Bad execution plans can result from parameter sniffing or inaccurate statistics. They will be reused and will hurt performance badly.
- Excessive Blocking and Deadloacks: It is critical to properly control the isolation levels and transaction scopes of queries to minimize blocking and deadlocks.
- Non-Set-Based Operations: Excessive use of cursors and loops leads to inefficient queries.Set based operations are much more efficient.
- Poor Database Design: Under-normalized database can result into more redundant data and thus more IO and more blocking. Similarly over-normalized database can result into very complex joi queries.
- Excessive Fragmentation: If the pages containing data are fragmented then that results in more IO and more blocking.
- Non-reusable Execution Plans: Its take considerable time to create an execution plan for a query. So we need to design query in such a way that there is no need to create execution plan on each execution. we can reuse execution plan.
- Frequent Recompilation of Queries: Queries that recreate execution plans frequently consumes more server resources.
- Improper use of cursors: Excessive use of cursors slows down the server.