question

skyline avatar image
skyline 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?
sql-server-2005performance-monitor
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks to all the links are excellent to understand the whole issues . Tool recommended seems fine....will let you know after try it
0 Likes 0 ·
Thanks all for such a good responses @denisrichi SQL Storage Manager seems good tool for performance monitoring @indexer I like those point form answers too any other answers will also be welcomed
0 Likes 0 ·
anthony.green avatar image
anthony.green answered
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

That should be an answer, not a comment. Fixed it!
0 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered
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...
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
1 Like 1 ·
Grant Fritchey avatar image
Grant Fritchey answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

denisrichi avatar image
denisrichi answered
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered
The database schema may be poorly designed. You might just have a lot of data.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

indexer avatar image
indexer answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.