分析数据库表和使用情况
我刚进一家新公司，我的任务是优化数据库性能。一种可能的（也是建议的）方法是使用多台服务器而不是一台。由于有很多可能的方法可以做到这一点，我需要首先分析数据库。有没有一种工具可以测量每个表执行了多少插入/更新和删除？
I just got into a new company and my task is to optimize the Database performance. One possible (and suggested) way would be to use multiple servers instead of one. As there are many possible ways to do that, i need to analyse the DB first. Is there a tool with which i can measure how many Inserts/Updates and Deletes are performed for each table?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我同意 Surfer513 的观点，即 DMV 将比 CDC 好得多。添加CDC相当复杂，并且会增加系统的负载。 （请参阅我的文章 此处用于统计。）
我建议首先设置一个 SQL Server Trace 以查看哪些命令长时间运行。
如果您的系统大量使用存储过程（希望如此），还请查看 sys.dm_exec_procedure_stats。这将帮助您专注于最常用的过程/表/视图。查看execution_count 和total_worker_time。
关键是您想要确定系统的哪些部分速度较慢（使用 Trace），以便您知道把时间花在哪里。
I agree with Surfer513 that the DMV is going to be much better than CDC. Adding CDC is fairly complex and will add a load to the system. (See my article here for statistics.)
I suggest first setting up a SQL Server Trace to see which commands are long-running.
If your system makes heavy use of stored procedures (which hopefully it does), also check out sys.dm_exec_procedure_stats. That will help you to concentrate on the procedures/tables/views that are being used most-often. Look at execution_count and total_worker_time.
The point is that you want to determine which parts of your system are slow (using Trace) so that you know where to spend your time.
一种方法是利用更改数据捕获 (CDC) 或更改跟踪 。不确定你要寻找的深度有多深，但还有其他更简单的方法来获得粗略的估计（看起来你不需要精确的数据，只是大概的数字......？）。
假设您的表上有索引，您可以查询 sys.dm_db_index_operational_stats 来获取影响索引的插入/更新/删除数据。再次强调，这是一个粗略的估计，但它会给您一个不错的想法。
One way would be to utilize Change Data Capture (CDC) or Change Tracking. Not sure how in depth you are looking for with this, but there are other simpler ways to get a rough estimate (doesn't look like you want exacts, just ballpark figures..?).
Assuming that there are indexes on your tables, you can query
sys.dm_db_index_operational_statsto get data on inserts/updates/deletes that affect the indexes. Again, this is a rough estimate but it'll give you a decent idea.