Two great links for Microsoft SQL Server 2000 Index Defragmentation Best Practices and Automatic Reindexing.

These links provides information that you can use to determine whether you should defragment indexes to benefit the workload performance in your production environment. And how...

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

http://www.sqlmag.com/Articles/ArticleID/43783/pg/2/2.html

Use dm_exec_query_stats to find data for all statements in the cache: number of times the query has been executed, the longest query to execute.

Use these tow objects:

sys.dm_exec_query_stats
sys.dm_exec_sql_text

 Run this query to find most frequently executed queries in SQL Server 2005

select* from sys.dm_exec_query_stats as across apply sys.dm_exec_sql_text(a.sql_handle) as b order by execution_count desct

Great SQL Server Monitoring Tool.

WildMetrix says:

"Benefits of Monitoring Microsoft SQL Server with WildMetrix

Helps administrators detect problems in their SQL Server architecture
Quickly diagnose exactly where and what the problems are
Resolves the problems from a single easy-to-use interface
Gain understanding of entire SQL Server system inside and out"

Product

http://www.ascendview.com/wildmetrix/sol_mssqlserver.asp

Demo

http://www.ascendview.com/resources/request.asp?Activity=Eval