When I work on improving database performance, I follow three simple steps: measure, measure and measure. Basic tool for that is simple timer. In SQL Server syntax that would read something like this:
CHECKPOINT; DBCC DROPCLEANBUFFERS; DECLARE @StartTime DATETIME = GETDATE(); SELECT SomeField1, SomeField2 FROM SomeTable WHERE (SomeFieldN='Something'); SELECT [Duration] = DATEDIFF(ms, @StartTime, GETDATE());
Although code is pretty much straightforward, first line requires some explanation.
SQL Server is pretty smart tool with lot of caching going around and testing on cached data is not something that gives consistent results. CHECKPOINT statement ensures that all dirty pages are written to disk and DBCC DROPCLEANBUFFERS ensures that cache is cleaned. Those two statements together basically force database to be in worst-case-scenario for performance but in best state for testing.
Of course, not all decisions can be made by this simplest of all tests. However, it will serve as pretty good rule of a thumb.