SQL Server数据优化技术 [英] SQL Server Data Optimization Techniques

问题描述

我希望在我的SQL数据库上实现一种数据优化技术。
我更喜欢先创建索引，我使用了Tuning Advisor。
调优顾问向我推荐了许多非聚集索引。

我应该创建所有这些索引来提高性能吗？或者可以创建只有较小尺寸的那些？

请建议我......我对这个区域很新..

解决方案

关于实施索引的问题，您必须知道，监控和智能响应索引使用需求的第一步是收集和维护简单易用使用索引使用指标的数据集。

此数据集应该允许您快速搜索可能考虑删除或更改索引的常见方案： / p>

• 未使用的索引


• 最少使用的索引


• 索引写得比阅读时多得多


• 经常扫描的索引，但很少是搜索目标


• 索引非常相似且可以合并

SQL Server提供跟踪所有索引使用情况的动态管理视图： sys.dm_db_index_usage_stats 。

此视图是针对索引的累计操作总数，并在重新启动SQL Server服务时重置。

要了解更多信息，请参阅此文 https://www.sqlshack.com/sql-server-index-performance-tuning-using-built-in-index-utilization-metrics/

I want implement one of the data optimization techniques on my SQL database. I preferred to go for creating indexes first, for which i used Tuning Advisor. Tuning advisor recommended me many of the Non-Clustered Indexes.

Shall I create all these indexes to improve performance ? Or Can create those only having less in size?

Please suggest me..as I am very new to this area ..

解决方案

As for your question in implementing indexes, you must know that the first step towards monitoring, and intelligently responding to index usage needs, is to collect and maintain a simple and easy-to-use data set of index usage metrics.

This data set should allow you to quickly search for common scenarios in which you might consider removing or altering an index:

• Unused indexes
• Minimally used indexes
• Indexes that are written to significantly more than they are read
• Indexes that are scanned often, but rarely the target of seeks
• Indexes that are very similar and can be combined

SQL Server provides a dynamic management view that tracks all index usage: sys.dm_db_index_usage_stats.

This view is a cumulative total of operations against indexes and is reset when SQL Server services are restarted.

To learn more, please refer to this article https://www.sqlshack.com/sql-server-index-performance-tuning-using-built-in-index-utilization-metrics/

这篇关于SQL Server数据优化技术的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！