SQL Server Query Execution flow

Question

I am looking for information about how SQL Server actually handles query execution in finer details (like what data is kept in buffer/memory and how does it decide to get fresh data even if there is an update change in only one column of a table involved in a query etc)

If anyone knows sources please let me know?

---

We have an web application using sql server 2000, it has heavy frequent reads almost 70% of the tables(dashboard) every 30 seconds. and at the same time lot of writes are happening.

Please let me know any tips for optimization of above scenario?

Answer 1

You should examine the execution plan.

Press Ctrl-L in the SSMS or issue SET SHOWPLAN_TEXT ON before executing the query.

This will give you detailed information of what indexes are used, which join algorithms applied etc.

You may also want to see the statistics:

SET STATISTICS TIME ON
SET STATISTICS IO ON

, which will give you the information on how many reads were done from actual tables, cache etc., and how much time (actual and CPU time) did every query take.

Answer 2

It would take a lot of posts to answer the internal's type questions, I suggest you start reading some of the white papers / blogs and some books.

For 2000 Ken Henderson's SQL Server Architecture will give you deep internal details, for the more recent editions of SQL, he did a 2005 practical trouble shooting which isn't bad, and Kalen Delaney's SQL 2008 Internals book is very good.

Answer 3

About SQL Server 2008 buffer management: http://msdn.microsoft.com/en-ca/library/aa337525.aspx

Then you can browse with the left sidebar for information on other subjects.

Answer 4

As far as external sources go, MSDN has a comprehensive resource on optimising your SQL Server 2000 installation, particularly the Patterns & Practices paper on performance and scalability.

If you know where to start looking, take a bottom-up approach with examining SQL profiles and query plans as has been mentioned. Otherwise, start from the top down and learn about the costs involved in recompiling queries, how to index effectively, and how the query optimiser uses statistics.

Answer 5

No one is going to be able to give you an answer on how to solve your optimization problem. This requires access to your database server. To solve your problems you need to understand roughly how the database works, and for this you need to do some reading.

On-line resources are OK, however the following three books are priceless. The first two books have very detailed information about how SQL Server works. The last ones is a guide of how to write queries but with a discussion on how the engine views queries as well.

1. Kalen Daleney: Sql Server 2008 Internals
2. Sql Server 2005: Practical Troubleshooting:
3. Ben Gan et al: Inside SQL Server 2008: T-SQL Querying