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?
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...
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]. It doesn't answer every possible question, but it answers quite a few of them. :
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