登录查看更多内容

What are the steps to validate your indexing strategy before deploying it?

由人工智能和领英社区提供技术支持

Indexing is a crucial technique to optimize the performance and efficiency of your database queries. However, creating and maintaining indexes can be costly and complex, so you need to have a clear and effective strategy to choose the right indexes for your data and workload. In this article, you will learn the steps to validate your indexing strategy before deploying it, using some common tools and methods.

在这篇协作文章中查找专家回答

由社区从 3 条内容中精选。了解更多

1 Analyze your queries

The first step is to analyze your queries and identify the most frequent, critical, or problematic ones. You can use tools such as SQL Server Profiler, SQL Server Management Studio, or Database Engine Tuning Advisor to capture and analyze the query workload, and generate recommendations for indexes. You can also use the query optimizer to view the execution plans and statistics of your queries, and look for signs of poor performance, such as table scans, high logical reads, or high CPU usage.

添加您的观点

ANUP S.

Staff Database Performance Engineer at Wayfair
举报内容
Analyzing the query is a very critical first step on how effective a solution you can come up with is. There are always multiple ways of solving the same problem with each has its own pros and cons. First understand what your goal is with the query analysis and optimisation. Do you want to improve execution time, memory consumption, CPU consumption, io consumption or something else? Accept that for all queries one may not be able to achieve optimisations at all front. Understand the underlying data and entity relationship and make sure the query is accessing only needed data and not trying to retrieve more than necessary just to filter out that in a later stage.

已翻译

赞

2 Test your indexes

The next step is to test your indexes and measure their impact on your query performance and resource consumption. You can use tools such as SQL Server Data Tools, SQL Server Data Compare, or SQL Server Unit Test to create and run tests on your indexes, and compare the results with the baseline. You can also use the index-related dynamic management views and functions, such as sys.dm_db_index_usage_stats, sys.dm_db_index_operational_stats, or sys.dm_db_index_physical_stats, to monitor the usage, status, and health of your indexes.

添加您的观点

ANUP S.

Staff Database Performance Engineer at Wayfair
举报内容
Everyone would agree that the testing is very important with any production deployment. The question is how and where do you test it? If you have dev env start there.if you have QA use that. If you have UAT env much better definitely test there. Well if you have none of this then you can still test in production but with extreme caution. Select queries do not change data but if it is very expensive than execution even for a short time can have significant business/revenue impact on a busy system. You can run DML queries with BEGIN Transaction and rollback at the end. Remember, testing in production is never recommended so proceed with extreme caution and always keep stakeholders informed. Get the code reviewed by a senior colleague.

已翻译

赞

3 Evaluate your trade-offs

The third step is to evaluate your trade-offs and balance the benefits and costs of your indexes. You need to consider factors such as the size, structure, and distribution of your data, the frequency and type of your queries, the frequency and type of your data modifications, and the available storage and memory space. You also need to weigh the advantages of improving query performance and reducing I/O operations against the disadvantages of increasing index maintenance and storage overhead.

添加您的观点

4 Review your best practices

The fourth step is to review your best practices and follow the guidelines and standards for designing and implementing indexes. You need to adhere to the principles of index normalization, index selectivity, index covering, and index clustering. You also need to avoid common pitfalls and mistakes, such as over-indexing, under-indexing, using inappropriate data types, using unnecessary columns, or creating duplicate or redundant indexes.

添加您的观点

5 Document your strategy

The fifth step is to document your strategy and record the details and rationale of your indexing decisions. You need to create and maintain an index inventory that lists the indexes in your database, their properties, their purpose, and their performance metrics. You also need to document the process and criteria for creating, modifying, or dropping indexes, and the tools and methods for testing and validating them.

添加您的观点

Blessious Phiri

EDB PostgreSQL| Oracle| MSSQL| Trainer| Expert
举报内容
The fifth step is to document your strategy and record the details and rationale of your indexing decisions. You need to create and maintain an index inventory that lists the indexes in your database, their properties, their purpose, and their performance metrics. You also need to document the process and criteria for creating, modifying, or dropping indexes, and the tools and methods for testing and validating them.

已翻译

赞

6 Deploy your strategy

The final step is to deploy your strategy and apply your indexes to your production database. You need to plan and schedule the deployment carefully, and minimize the downtime and disruption to your database operations. You also need to backup your database before and after the deployment, and test the functionality and performance of your queries and applications. Finally, you need to monitor and tune your indexes regularly, and adjust your strategy as needed.

添加您的观点

7 Here’s what else to consider

This is a space to share examples, stories, or insights that don’t fit into any of the previous sections. What else would you like to add?

添加您的观点

Database Administration

+ 关注

给文章评分

我们借助人工智能创建了此文章。您认为这篇文章怎么样？

很棒不太好

举报此文章

查看全部

What are the steps to validate your indexing strategy before deploying it?

1

2

3

4

5

6

7

1 Analyze your queries

2 Test your indexes

3 Evaluate your trade-offs

4 Review your best practices

5 Document your strategy

6 Deploy your strategy

7 Here’s what else to consider

Database Administration

给文章评分

感谢您的反馈

更多Database Administration相关文章

更多相关阅读内容

What are the steps to validate your indexing strategy before deploying it?

1

2

3

4

5

6

7

1 Analyze your queries

2 Test your indexes

3 Evaluate your trade-offs

4 Review your best practices

5 Document your strategy

6 Deploy your strategy

7 Here’s what else to consider

Database Administration

给文章评分

感谢您的反馈

查看其他技能