Improving SQL Server Performance

True or False? SQL Server produces execution plans that minimize overall resource use to improve system wide performance.

False. Sql server minimizes the time it takes to return results to the client, if multiple cpus are available it will run parts of the query in parallel even though a single cpu solution would minimize overall resource use.

When dealing with database performance problems, many database professionals will look at a variety of metrics, queue sizes, cache hit ratios, etc. However, when users use your database the only important metric, how long it takes for the database system to return results. System performance metrics are a lot different than the user's perceived performance, at the end of the
day only the latter matters.

The storage engine optimizes execution plans with this strategy; Solid Quality Learning's query tuning methodology revolves around the same concept. Taking a top down approach allows you to spend your time fixing the worst bottlenecks.

Methodology:

1. Analyze waits at the instance level
2. Correlate waits with queues
3. Determine a course of action
4. Drill down to the database/file level
5. Drill down to the process level
6. Tune indexes and queries

So go: SELECT * FROM sys.dm_os_wait_stats

More true/false cause I was bored:

Queries that never change in which query data in tables that never changes, always produces the same result. False. Not all queries have a distinct/unique correct logical result. Sql server execution plans may change based on external factors, the execution plan may change as operations are reordered

Where clause are always evaluated after the join clauses. False. Where clauses are only logically evaluated after the joins however the query optimizer adjusts the physical evaluation of the query result by filtering before the joins for increased efficiency.