Powerpoint - SQL Saturday

SQL Server 2016 Performance
Enhancements
It Just Runs Faster
Fabio Gentile / André César Rodrigues
Premier Field Engineers (PFE)/ Microsoft
[email protected] / [email protected]
PATROCINADORES
Quem somos nós...
André César Rodrigues
 Quase 7 anos trabalhando com SQL Server
 MBA Administrando Bancos de Dados IBTA
 SQL Server PFE na Microsoft desde Janeiro deste ano
Fabio Gentile
 Mais de 18 anos trabalhando com o SQL Server nas suas
diversas versões
 Bacharel em Ciência da Computação pela UFSCAR
 SQL Server PFE na Microsoft desde 2007
Introducão
“SQL Server 2016 running on the same hardware
as SQL Server 2014, 2012, 2008, 2008 R2 or 2005
uses fewer resources and executes a wide range of
workloads faster...”
Bob Dorr, Principle Engineer SQL Server Support
SQL Server
2005 is out...
Windows
Server 2012
ou >
HW suporta
Windows
Server 2012?
High Performance Workloads
 Até o SQL Server 2014, vários ajustes eram
necessários para um melhor desempenho:




Ativar o Instant File Initialization
Configurar o tempdb
Ativar trace flags
Mudar parâmetros de configuração
Recommended updates and configuration options for SQL
Server 2012 and SQL Server 2014 with high-performance
workloads (2964518)
High Performance Workloads
 Com o SQL Server 2016, a maior parte do
comportamento desejado tornou-se padrão
 Melhorias no engenho aumentaram
escalabilidade e throughput
 Muitas configurações podem ser feitas no
nível do banco de dados
Trace flag 4199 Servicing Model
 TF 4199 ainda será usado para proteger
futuros hotfixes do otimizador
SQL Server query optimizer hotfix trace flag 4199
servicing model (974006)
 Hotfixes protegidos pelo TF 4199 e
anteriores ao SQL Server 2016 habilitados
automaticamente no CL 130
Trace flag 4199 Servicing Model
Trace flag 2371
 Atualização automática de estatísticas é mais
agressiva em tabelas grandes no CL 130
 Mesmo comportamento do TF 2371, que sob
o SQL Server 2016 não precisa ser ativado
SQL Server
2008 R2 SP1
ou >
Instant File Initialization
 Acelera a criação e expansão dos arquivos
de dados
 Requer o privilégio ‘Perform Volume
Maintenance Tasks’
 Privilégio habilitado por padrão apenas para
administradores locais
Instant File Initialization
 Setup do SQL 2016 permite habilitar o
‘Perform Volume Maintenance Tasks’
Tempdb Configuration
 Artigos, KBs e blogs discutem a
reconfiguração do tempdb:
 Drive dedicado
 Multiplos arquivos de dados, mesmo tamanho e
auto-growth
 TF 1117
 TF 1118
Tempdb Configuration
 Setup com nova guia para configurar o
tempdb:
 Cálculo automático
de nº de arquivos
 Initial Size = 8 MB
Auto-growth = 64 MB
 TFs 1117 e 1118
ativados por padrão
User databases
 TF 1118 ativado por padrão, controlado com
nova opção no ALTER DATABASE:
ALTER DATABASE <dbname> SET
MIXED_PAGE_ALLOCATION OFF
Não funciona
no tempdb
 TF 1117 desativado por padrão, configurado no
nível do FILEGROUP:
ALTER DATABASE <dbname> MODIFY FILEGROUP
<filegroup> {AUTOGROW_ALL_FILES |
AUTOGROW_SINGLE_FILE}
User databases
 Tamanho inicial dos arquivos é 8 MB, autogrowth para dados e log é 64 MB
 Configurações opcionais no nível dos bancos
de dados: Database Scoped Configurations
Database Scoped Configurations





Limpar procedure cache
Configurar MAXDOP
Mudar cardinality estimation model
Controlar parameter sniffing
Controlar TF 4199
Database Scoped Configurations
Dynamic Memory Object Partitioning
 CMEMTHREAD pode ser ponto de
contenção com múltiplos cores
 TF 8048 converte particionamento de NUMA
para CPU, novas contenções exigem hotfixes
Dynamic Memory Object Partitioning
 No SQL Server 2016, contenções por
CMEMTHREAD são detectadas e tratadas
dinamicamente
SQL Server
2014 SP2
backport
 Particionamento dos objetos é perdido com o
restart da instância
Dynamic Memory Object Partitioning
Dynamic Memory Object Partitioning
sys.dm_os_memory_objects
Updated Scheduling Algorithms
 Até o SQL Server 2014, apenas load
factor e NUMA nodes eram considerados
 Diferentes demandas por CPU podiam
não ser balanceadas
 SQL Server 2016 monitora a utilização de
CPU, permitindo tratamento balanceado
para as workers
Updated Scheduling Algorithms
SQL Server 2014
SQL Server 2016
Automatic Soft NUMA
 Até o SQL Server 2005, 8 CPUs por
NUMA node eram sistemas high-end
 Com 18 cores por NUMA, gargalos
semelhantes ao SMP são observados
 SQL Server 2016 configura Soft-NUMA
em sistemas com 8 ou mais CPUs por
NUMA node
Automatic Soft NUMA
Soft NUMA
Soft NUMA
Soft NUMA
Soft NUMA
Other Performance Improvements
 E tem muito mais! Explore outras novidades
neste link:
CSS SQL Server Engineers Blog
SQL SAT – PÓS-EVENTO (09/10)
FABIANO AMORIM – 150 DICAS DE TUNING
R$150,00
LOCAL : MICROSOFT - SP