ScottPletcher (9/26/2016)
Eric M Russell (9/26/2016)
ScottPletcher (9/23/2016)
Eric M Russell (9/23/2016)
Regardless of SQL Server version, performance optimization should start by looking at execution plans.
Not necessarily. If the best clustered indexes are not already in place, you should start by looking at all index stats; you might need to look at "heavy" queries as a part of that, but not always.
You need to look at the execution plan to see what tables are referenced, and then look at statistics on those tables and indexes.
Initially, you could just look at the stats of all significant tables and indexes. And unless you've already reviewed the clustered indexes on all tables, that's what you should do.
Especially when working with 3rd party source databases, we really don't know which tables are "significant" unless we analyze the SQL or execution plans. I know some of us have strong opinions regarding identity vs. natural clustering keys... but let's not rehash that discussion. I think we can all agree that generally speaking database performance optimization boils down more to physical data modeling and basic SQL optimization rather than leveraging new T-SQL features introduced in the latest release.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho