What can cause bad SQL server performance?

Question

Every time I find out that the performance of data retrieval from my database is slow. I try to figure out which part of my SQL query has the problem and I try to optimize it and also add some indexes to the table. But this does not always solve the problem.

My question is :

Are there any other tricks to make SQL server performance better?

What are the other reason which can make SQL server performance worse?

Answer 1

Take a look at this blog post. It's pretty much the answer to your question.

Answer 2

Maybe start here?

http://msdn.microsoft.com/en-us/library/ms979196.aspx

Answer 3

• Inefficient query design
• Auto-growing files
• Too many indexes to be maintained on a table
• Too few indexes on a table
• Not properly choosing your clustered index
• Index fragmentation due to poor maintenance
• Heap fragmentation due to no clustered index
• Too high FILLFACTORs used on indexes, causing excessive page splitting
• Too low of a FILLFACTOR used on indexes, causing excessive space usage and increased scanning time
• Not using covered indexes where appropriate
• Non-selective indexes being used
• Improper maintenance of statistics (out of date statistics)
• Databases not normalized properly
• Transaction logs and data sharing the same drive spindles
• The wrong memory configuration
• Too little memory
• Too little CPU
• Slow hard drives
• Failing hard drives or other hardware
• A 3D screensaver on your database server chewing up your CPU
• Sharing the database server with other processes which compete for CPU and memory
• Lock contention between queries
• Queries which scan entire large tables
• Front end code which searches data in an inefficent manner (nested loops, row by row)
• CURSORS which are not necessary and/or are not FAST_FORWARD
• Not setting NOCOUNT when you have large tables being cursored through.
• Using a transaction isolation level which is too high (such as using SERIALIZABLE when it's not necessary)
• Too many round trips between the client and the SQL Server (a chatty interface)
• An unnecessary linked server query
• A linked server query which targets a table on a remote server with no primary or candidate key defined
• Selecting too much data
• Excessive query recompilations

oh and there might be some others, too.

Answer 4

If you're new to the database and you have access to the database engine tuning advisor, you can heuristically tune your database.

You basically capture the SQL queries being run against your DB in the SQL Profiler, then feed those to DETA. DETA effectively runs the queries (without altering your data) and then works out what information your database is missing (views, indexes, partitions, statistics etc.) to do the queries better.

It can then apply them for you and monitor them in the future. I'm not saying to assume that DETA is always right or to do things without understanding, but I've found that it's definately a good way to see what your queries are doing, how long they take, and how you can index the DB appropriately.

PS: With all that said, it's much better to invest in a good DBA at the start of a project so that you have good structures and indexing to start with. But thats not the position that you're in right now...

Answer 5

When I talk to new developers that have this problem I usually find that it is because of one of two problems. Both of them are fixed if you follow these 2 rules.

First, don’t retrieve any data that you don’t need. For example, if you are doing paging then don’t bring back 100 rows and then calculate which ones belong on the page. Have the stored proc figure it out and only retrieve the 10 you need.

Second, nothing is faster than work you don’t do. For example, I worked on a system where the full roles and rights for a user were retrieved with every page requested – this was 100’s of rows for some users. Even just saving this to session state on the first request and then using it from there for subsequent requests took a meaningful weight off of the database.

Answer 6

Suggest you get a good book on Performance tuning for the database you use (this is very much database specific). This is an extremely complex subject and cannot really be answered other than in generalities on the web.

For instance, Dave markle tell you inefficient queries can cause the problem and there are many many ways to write inefficient queries and many more ways to fix them.

Answer 7

What are the other reason which can make SQL server performance worse?

It's early in the evening and all the techs are on the Unreal Tournament/SQL Server at the same time.