Geek Sync | 3 Secret Configuration Changes to Make Your Existing Indexes Run Faster

Most DBAs and Developers know how indexes work and how to write efficient queries. However, the secrets of efficient indexes and queries are actually hidden in behind-the-scene configurations. When these essential configurations are not set correctly, database application performance goes downhill. SQL Server Optimizer Engine produces many different possibilities and helps build an optimal execution plan for any query.

Join IDERA and Pinal Dave on Wednesday, August 15 at 11 AM CT as he walks through three of the most important settings that help SQL Server Optimizer Engine write better execution plans with indexes. This session will not discuss query rewriting or index modification. Instead, Pinal will discuss the secret configurations which are often ignored or overlooked by SQL experts. This session is for everyone who wants to improve the performance of their system without making any code changes.

You can view Pinal's slides here. This Geek Sync will be recorded and available in our Resource Center.  

You can view Pinal's scripts below.

01-stats.sql
USE [SQLAuthority]
GO
-- Solution 3: Server Settings 
ALTER DATABASE [SQLAuthority] 
SET AUTO_CREATE_STATISTICS ON WITH NO_WAIT
GO
ALTER DATABASE [SQLAuthority] 
SET AUTO_UPDATE_STATISTICS ON WITH NO_WAIT
GO

02-Compatibility Level.sql
USE WideWorldImporters
GO
-- SQL Server 2017
ALTER DATABASE WideWorldImporters 
SET COMPATIBILITY_LEVEL = 140
GO

03-OptimizeFG.sql
-- Create Default Database
USE master
GO
CREATE DATABASE [OptimizeDB] ON
( NAME = N'OptimizeDB', FILENAME = N'e:\data\OptimizeDB.mdf' , 
		SIZE = 102400KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB )
 LOG ON 
( NAME = N'OptimizeDB_log', FILENAME = N'e:\data\OptimizeDB_log.LDF' , 
		SIZE = 102400KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB )
GO