SQL server slow performance
#1: Bad Schema Designing
Poor Normalization – Flat wide tables or over normalization
Redundant data in databases
Bad referential integrity (foreign keys and constraints)
Wide composite primary keys (and clustered indexes)
No stress testing of schema robustness adhering growth patterns
#2: Inefficient T-SQL Queries
Using NOT IN or IN instead of NOT EXISTS or EXISTS
Using cursors or white loop instead of INSERT…SELECT or SELECT…INTO TABLE
Using SELECT * instead of only necessary column names
Nesting of subqueries creating a complex execution plan
Using functions on Indexed Column in WHERE clause
Datatype mismatch in predicates (where condition or joins)
Interchanging usage of UNION vs UNION ALL
Unnecessary using DISTINCT everywhere
Dynamic SQL
#3: Poor Indexing Strategies
Indexing every single foreign key
Indexing every column in the table
Many single-column indexes
Preferring Heap Table over the Clustered index
Underindexing your table
Not maintaining your index
#4: Incorrect Server settings
Keeping Maximum Degree of Parallelism to 0
Not setting the index level to fill factor
Lower Filegrwoth
Single TempDB files
Hosting SQL Server data and log files together
Running antivirus on SQL Server files
Incorrect value in Max Memory Configuration
High latency for your log files
#5: Hardware issue
Why is my SQL Server Query Suddenly Slow?
1. Look for most expensive queries running in SQL Server, over the period of slowdown
2. Check the query plan and query execution statistics and wait types for those query
3. Review the Query history over the period to see where performance changed
4. Check usage over the periods of “normal” and “bad” performance, and see what changed.
5. Diagnosis and query tuning
Index scan vs Index seek
Index scan means it retrieves all the rows from the table and index seek means it retrieves selective rows from the table. INDEX SCAN: Index Scan touches every row in the table it is qualified or not, the cost is proportional to the total number of rows in the table.
