sql-server-2016

Will Query Store Work in a Read Only Database?

Will Query Store Work in a Read Only Database?

For static databases, it’s quite useful to set SQL Server’s “read only” database property to true. When the database is read-only, it ensures that the last backup you took is still valid… as long as nothing bad happens to that backup file.

Target Recovery Interval and Indirect Checkpoint - New Default of 60 Seconds in SQL Server 2016

Target Recovery Interval and Indirect Checkpoint - New Default of 60 Seconds in SQL Server 2016

Update, 6/21/2016: Be careful using indirect checkpoint with failover clusters if your SQL Server 2014 instance is not fully patched. See KB 3166902. This bug was fixed in SQL Server 2016 prior to RTM.

SQL Server 2016 introduces big new features, but it also includes small improvements as well. Many of these features are described in the “It Just Runs Faster” series of blog posts by Bob Ward and Bob Dorr.

Live Query Statistics Don't Replace Actual Execution Plans

Live Query Statistics Don't Replace Actual Execution Plans

I like SQL Server’s new Live Query Statistics feature a lot for testing and tuning large queries. One of my first questions was whether this could replace using actual execution plans, or if it’s useful to use both during testing.

Finding: Both are useful. And both can impact query performance.

What Resets sys.dm_db_index_usage_stats and Missing Index DMVs?

What Resets sys.dm_db_index_usage_stats and Missing Index DMVs?

Managing indexes got trickier with SQL Server 2012. SQL Server has tracked and reported statistics on how often indexes are used and requested since SQL Server 2005. As of SQL Server 2012, suddenly all that information started getting reset whenever anyone ran ALTER INDEX REBUILD.