我正在寻找有关 SQL Server 实际上如何更详细地处理查询执行的信息（例如缓冲区/内存中保存哪些数据，以及即使涉及的表中仅一列发生更新更改，它如何决定获取新数据）在查询等）
如果有人知道来源请告诉我？
我们有一个使用 SQL Server 2000 的 Web 应用程序，它每 30 秒频繁读取近 70% 的表（仪表板）。同时发生大量写入。
请告诉我对上述场景的优化有什么建议吗？
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?
发布评论
评论（5）
没有人能够为您提供如何解决优化问题的答案。这需要访问您的数据库服务器。为了解决您的问题，您需要大致了解数据库的工作原理，为此您需要阅读一些内容。
网上资源还可以，但以下三本书是无价的。前两本书包含有关 SQL Server 如何工作的非常详细的信息。最后一个是如何编写查询的指南，但也讨论了引擎如何查看查询。
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.
回答内部类型的问题需要很多帖子，我建议您开始阅读一些白皮书/博客和一些书籍。
对于 2000 年，Ken Henderson 的 SQL Server Architecture 将为您提供深入的内部细节，对于 SQL 的最新版本，他做了 2005 年的实际故障排除，这还不错，而 Kalen Delaney 的 SQL 2008 Internals 书非常好。
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.
您应该检查执行计划。
在执行查询之前，请在
SSMS中按Ctrl-L或发出SET SHOWPLAN_TEXT ON。这将为您提供使用哪些索引、应用哪些连接算法等的详细信息。
您可能还想查看统计信息：
，这将为您提供有关从实际表、缓存等进行了多少次读取以及如何读取的信息每个查询花费了很多时间（实际时间和
CPU时间）。You should examine the execution plan.
Press
Ctrl-Lin theSSMSor issueSET SHOWPLAN_TEXT ONbefore 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:
, which will give you the information on how many reads were done from actual tables, cache etc., and how much time (actual and
CPUtime) did every query take.关于 SQL Server 2008 缓冲区管理： http://msdn.microsoft.com/en -ca/library/aa337525.aspx
然后您可以使用左侧边栏浏览有关其他主题的信息。
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.
就外部资源而言，MSDN 拥有有关优化的全面资源您的 SQL Server 2000 安装，特别是 Patterns &关于性能和可扩展性的实践论文。
如果您知道从哪里开始查找，请采用自下而上的方法来检查 SQL 配置文件和查询计划，如前所述。否则，从上到下开始，了解重新编译查询所涉及的成本、如何有效地建立索引以及查询优化器如何使用统计信息。
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.