asked
what is actually causing the performance of SQL SERVER
I find out every time 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. What are the other reason which can make SQL server performance worse?
Couple of links Gail always posts on finding poor performing SQL Poor Performing SQL Part 1 -
http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/ Poor Performing SQL Part 2 -
http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/
A few potential causes: Insufficient memory, non-performant storage, other stuff running on the server other than just SQL Server, dodgy network connection, lack of CPU power, lots of work going on on the SQL Server itself, temporary blocking of your query, slow network, badly written queries...
And your answer shows why being a DBA is a career and not just a job. The breadth of knowledge required to excel can be intimidating at times.
The number one problem is generally the code, the queries. You can have functions on the columns in WHERE clauses and JOINs. You might not be using the entire key on indexes in the columns you select. You might have multi-statement table valued user defined functions. You might be nesting views. You could have cursors. There are just tons and tons of issues in and around T-SQL performance. The trick is to gather metrics in order to understand what's going on within your system and within your queries. Once you've identified the primary poor performers, you have to fix them. This can be as easy as spotting that function on a column, or, you may have to dive into the execution plans to understand why something is going wrong. Another frequent problem is that the statistics, even though they're automatically maintained, are out of date. Simply updating these can make a huge difference. I suggest taking a look at [my book on query tuning][1]. It doesn't answer every possible question, but it answers quite a few of them. [1]:
http://www.amazon.com/Server-2012-Query-Performance-Tuning/dp/1430242035/ref=sr_1_1?ie=UTF8&qid=1360757385&sr=8-1&keywords=fritchey
Follow the bellow article [
http://stackoverflow.com/questions/1252795/what-can-cause-bad-sql-server-performance][1] [
http://www.brentozar.com/archive/2012/06/sql-server-poor-performance-checklist/][2] You can try a tool to improve performance like this [
http://www.sqlservermanagement.net/][3] [1]:
http://stackoverflow.com/questions/1252795/what-can-cause-bad-sql-server-performance [2]:
http://www.brentozar.com/archive/2012/06/sql-server-poor-performance-checklist/ [3]:
http://www.sqlservermanagement.net/
The database schema may be poorly designed. You might just have a lot of data.
Main causes of poor performance 1. Inefficient query design 2. Auto-growing files 3. Too many indexes to be maintained on a table 4. Too few indexes on a table 5. Not properly choosing your clustered index 6. Index fragmentation due to poor maintenance 7. Heap fragmentation due to no clustered index 8. Improper maintenance of statistics (out of date statistics) 9. Databases not normalized properly 10. Transaction logs and data sharing the same drive spindles 11. Too little CPU 12. Slow hard drives 13. Failing hard drives or other hardware 14. A 3D screensaver on your database server chewing up your CPU 15. Sharing the database server with other processes which compete for CPU and memory 16. Lock contention between queries 17. Queries which scan entire large tables
18 People are following this question.
