<a cantankerous old woman who insisted that nothing should ever be allowed
Yep.... that is pretty much SQL Server. Very powerful, but definitely a little hard to deal with from time to time. I will even agree that she is female... but, I can't really explain that one. Regardless, I've tried to list varied pointers here that I have learned over the years, pertaining to Performance Tuning. There are many, many ways to do it. Unfortunately, they are not all necessarily the right ways. :-)
- SQL Server Profiler - Very useful, when used properly.
- sys.dm_os_performance_counters - Query the Dynamic Management Views.
- Active SQL Connections - More DMVs
- sys.dm_os_sys_memory - Query memory details from the OS.
- Index Fragmentation - Quick way to check % of fragmentation.
- Aggregate performance statistics Using sys.dm_exec_query_stats
- Forced Parameterization Replacing literal values with parameters.
- sys.dm_os_memory_clerks Where is all of SQL's memory going?
- Instant File Initialization Allow SQL to skip the zero padding.
- Changes to the TempDB Reconfigure TempDB for improved performance.