I recommend an optimization theory that uses top-down. This approach begins by analyzing the instance-level wait time, which is continuously refined through a series of steps to find out which processes/components are causing a lot of waiting in the system. Once you find these annoying processes, you can focus on optimizing them, which is not the main step of this methodology: 1. Parsing instance-level waits 2. Associating waits and Queues 3. Identify scenario 4. Refine to database/file level. 5. Refine to process level 6. Optimize indexes/Queries SQL Server 2008 introduces a component called the Data Collector that collects different data (performance data or other data) from multiple sources. These performance information is stored in the relational database of the appellation Management Data Warehouse (manangement. Warehouse). The Data collector installs three system data collect sets, each of which collects disk usage, server activity, and query statistics, and the new data collection platform can help you automatically collect performance and other information, or you can use preconfigured reports The data is analyzed in the form of an icon.
SQL Technology Insider -12 Introduction to SQL Optimization methodology
