Lacking an index to subset the records might result in every record being read and checked (a table scan). This series of posts illustrates how you can identify missing indexes that will improve performance.

Windchill ships with a minimal set of indexes. Different Windchill installations might have significantly different usage patterns that make it difficult to predetermine which indexes will improve performance. Indexes can improve performance by taking less work to find records, and indexes can deteriorate performance by taking longer to do an insert and an update because of the CPU and storage consumed by updating the indexes. Some indexes may result in a significant loss of performance because they use resources to maintain but are never used in queries.

Typically, there are two ways to identify missing indexes:

• You can use t into the performance dashboard. ere. We were talkking SQL Server Database Engine Tuning Advisor together with SQL Server Profiler, which is an in-depth process that takes some time. I'll look at this option in a future post.
• You can use SQL Server 2005 Performance Dashboard, which gets immediate results.

SQL Server 2005 Performance Dashboard was developed as a joint effort between the support and development teams at Microsoft. Their goal was to reduce the amount of time spent discovering the source of a performance problem so that administrators could focus their efforts on resolving the problem.

SQL Server 2005 Performance Dashboard Reports rely exclusively on SQL Server 2005 dynamic management views. Dynamic management views use server and database data that is already captured and always available. Consequently, there is zero performance impact of using the dashboard except when you actually open or refresh a report. Dynamic management views are a feature in SQL Server 2005 that were not available in earlier versions.

How to Install SQL Server 2005 Performance Dashboard

Download SQL Server 2005 Performance Dashboard from the following Microsoft Download Center website:

http://www.microsoft.com/downloads/details.aspx?FamilyId=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en

Install it on your desktop computer and not on the computer that is running Windchill.

After you install SQL Server 2005 Performance Dashboard, connect to the Windchill database, and then open the following transactional .sql file:

C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Performance Dashboard\setup.sql

This file installs the queries that you will use in the Windchill database to generate reports.

How to Install the Reports

To install the reports, follow these steps:

1. Start SQL Server Management Studio.
2. Navigate to the wcadmin database.

   

3. Right-click wcadmin, point to Reports, and then click Custom Reports.

   

4. In the Open File dialog box, navigate to the folder that I mentioned earlier, click performance_dashboard_main.rdl, and then click Open.

    

5. Right-click wcadmin, point to Reports, and then click performance_dashboard_main.

   

6. In the warning message, click to select the Please don't show this warning again check box, and then click Run.

   

How to Find the Index Recommendations

After you install and run the reports, the dashboard is displayed.

A link to the Missing Indexes report is located at the bottom of the dashboard.

The Missing Indexes report shows the recommendation and gives you the index definition.

Notes:

• The analysis of queries and the recommendation derive from the usage since the last time that you started SQL Server. So, run the report at the end of a workday.
• To add the new index, copy and paste the code into SQL Server Management Studio and execute it.

In my next post, Creating a Workload by Using a Trace Log, I'll show you how to find indexes by using Database Engine Tuning Advisor and then how to eliminate unneeded or expensive indexes.

Ken Lassesen is part of the original team that created Dr. GUI of MSDN and specializes in new and resurrected commercial product architecture. He developed architecture for several Microsoft websites, including the original MSDN site and the current Microsoft Partner Network site. He's equally at home with SQL Server, XHTML, Section 508 accessibility standards, globalization, Security Content Automation Protocol (SCAP) security, C#, and ASP.NET server controls. When he is not having fun with technology, he enjoys taking lunch-break hikes in the North Cascades.