The only way to truly optimize performance is to monitor (using perfmon/profiler) and test.
You need someone with the expertise to do this, or someone who can learn the rudimentary tools and give it a shot. But, I would recommend either hiring a consultant (I can give some recommendations) or maybe using Microsoft’s “health checkup” services (from there support organization).
You could start with an analysis of the missing indexes. Your can find some great article out on the web about this. All ot the data for this is on a set of DMV’s (sys.dm_db_missing_index*).
The next place to look is your storage layout – to make sure you have the right number of and type of disks in you storage framework (local RAID, NAS or SAN). One of the biggest problems that people face is only looking at the amount of space, and not taking the time to characterize the IO behavior and needs and match them with the right number of disk heads to make sure you are not IO constrained. Contact your storage vendor (EMC or NetApps?) and convince them that you want to evaluate your storage – and they may have a service where they will take some perfmon output from your system and recommend the right IO configuration.
As you can probably see from my answer, this is not a simple task, and it will take time, understanding of your systems behavior, how SQL Server works, and detailed analysis.
Here is a missing index query I use. The base code was borrowed from someone else (I wish I could remeber to give them credit):
SELECT S.AVG_TOTAL_USER_COST * (S.AVG_USER_IMPACT / 100.0) * (S.USER_SEEKS + S.USER_SCANS) AS EST_IMPROVE,
LEFT (PARSENAME(D.STATEMENT, 1), 32) TABLE_NAME,
ISNULL (D.EQUALITY_COLUMNS,”) + CASE WHEN D.EQUALITY_COLUMNS IS NOT NULL AND D.INEQUALITY_COLUMNS IS NOT NULL THEN ‘,’ ELSE ” END + ISNULL (D.INEQUALITY_COLUMNS, ”) COLUMN_NAMES,
ISNULL (D.INCLUDED_COLUMNS , ”) AS INCLUDE_COLUMNS,
S.UNIQUE_COMPILES, S.USER_SEEKS, S.USER_SCANS, S.LAST_USER_SEEK, S.LAST_USER_SCAN, S.AVG_TOTAL_USER_COST, S.SYSTEM_SEEKS, S.SYSTEM_SCANS, S.LAST_SYSTEM_SEEK, S.LAST_SYSTEM_SCAN, S.AVG_TOTAL_SYSTEM_COST, S.AVG_SYSTEM_IMPACT
FROM SYS.DM_DB_MISSING_INDEX_GROUPS G
INNER JOIN SYS.DM_DB_MISSING_INDEX_GROUP_STATS S ON S.GROUP_HANDLE = G.INDEX_GROUP_HANDLE
INNER JOIN SYS.DM_DB_MISSING_INDEX_DETAILS D ON G.INDEX_HANDLE = D.INDEX_HANDLE
–WHERE S.AVG_TOTAL_USER_COST * (S.AVG_USER_IMPACT / 100.0) * (S.USER_SEEKS + S.USER_SCANS) > 10
ORDER BY EST_IMPROVE DESC