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.
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.
To understand why this is the case it takes a little bit of thought.
It can be very expensive to write large blocks of data into databases. What can you do instead?
Using a wildcard at the start of a string match can cause performance problems with queries. Find out more.
It's better to join tables together using a JOIN clause instead of a WHERE clause.
Joining too many tables in a single query can cause issues. Find out why.
Top is an efficient way of identifying what a tables structure and data look like.