Here are some of the typical things that all SQL Server DBAs and Database developers should know about performance and how to identify the causes of poor performance. These should get you out of trouble the majority of the time. Also included is a list of the most common mistakes developers make that cause performance issues.
Want to develop your SQL Server Database with SSW? Check SSW's Databases consulting page.
Identifying CPU Pressure
When looking at SQL Server, you often get performance issues, but how can you figure out what might be the cause?
When looking at Azure SQL Dataabase, you often get performance issues, but how can you figure out what might be the cause?
So you've identified that your SQL Server is under CPU pressure. What can you do about it?
So you've identified that your SQL Server is under memory pressure. What can you do about it?
So you've identified that your SQL Server is under IO pressure. What can you do about it?
Reading data from smaller tables is much faster. How can you keep the amount of data stored down?
It can be expensive retrieving all the columns of a table. Find out why.
So you've created some indexes on your database tables. How can you tell if they are being used by your queries?
Specifying the wrong data types in SQL queries can make the server scan your whole table. That can take ages.
It's very inefficient to loop in database queries. You should avoid it wherever possible.
- Identifying the cause of SQL Server performance problems
- Identifying the cause of Azure SQL Database performance problems
- What to do about SQL Server CPU Pressure?
- What to do about SQL Server Memory Pressure?
- What to do about SQL Server IO Pressure?
- Do you keep database tables small?
- Do you only SELECT the columns you require?
- Do you know how to verify SQL indexes are being used?
- Do you avoid implicit data type conversions in SQL queries?
- Do you avoid looping in database queries?
- Do you know that AND is much more efficient than OR in a WHERE clause?
- Do you avoid large database writes?
- Do you avoid avoid using wildcards at the start of a string?
- Do you use JOIN instead of WHERE?
- Do you avoid joining too many tables?
- Do you use TOP for sampling in your queries?