My Experience

Have ever wondered/concerned what columns are being used mostly. Which tables are queried mostly? whats the number of hits on table? and other concerns related to performance of server.

When it comes to performance tuning we all think of Clustered Index, table partitions, Non Clustered Index, Data retention policy, relationships. In addition to above concern below is list of information I have found in my way of performance tuning.

By reading articles from internet that there are two ways of improving performance of Production server.

1) Creating TempDB file for each core processor on server

2) Don't change default configuration of Database options, unless knowing what you are doing.

3) Below script from Microsoft is handy in identifying indexed in already setup and running production environment.

 

SELECT migs.group_handle,Migs.user_seeks,migs.avg_user_impact, mid.*

FROM sys.dm_db_missing_index_group_stats AS migs

INNER JOIN sys.dm_db_missing_index_groups AS mig

    ON (migs.group_handle = mig.index_group_handle)

INNER JOIN sys.dm_db_missing_index_details AS mid

    ON (mig.index_handle = mid.index_handle) order by user_seeks desc

4) Using Activity Monitor in SQL Server 2008 give fair information on Server load, CPU Usage.
5) Standard reports like server dashboard provides information on Server utilization, but not expensive queries.

In course of time there is one flaw, all queries are not visible in Activity monitor. Using DMV helps in this case. I still need to work on query using DMV to list all active queries.

6) One of Database option is Auto Update Statistics, its good to leave this option enabled. Based on frequency and status of stats it would get updated.
    Sp that helps to update all statistics. I recomend running it after creating tempdb files if you have not got one.
 

Update Statistics in Database

EXEC sp_updatestats