“SQL Server 2017 Query Performance Tuning” – impressões iniciais

p038_capa livro PerformanceMeu foco no SQL Server é a parte de programação T-SQL e em como construir códigos T-SQL otimizados. E, é claro, como otimizar códigos T-SQL existentes, o que me fez adquirir o livro “SQL Server 2017 Query Performance Tuning” após ler sobre o conteúdo dele. E também por causa do autor do livro.

Comprei a versão e-book desse livro no final de 2019 mas raramente o consultei. Não foi falta de interesse mas sim perda de foco de minha parte. Agora, de volta aos trilhos, resolvo ler o livro de cabo a rabo.

O livro aborda várias partes do processo de ajuste no desempenho (tuning), como índices, estatísticas, bloqueios, deadlock, parameter sniffing, query store , extended events etc.

O livro contém 909 páginas (sem contar os itens de abertura e o índice remissivo ao final), em 28 capítulos. O autor do livro é Grant Fritchey, autor do blog Scary DBA e que publica artigos no sítio web Simple Talk.

Espero com a leitura deste livro ampliar meus conhecimentos na atividade de otimização de códigos T-SQL, além de instâncias SQL Server como um todo.

Se você também se interessa por otimização de consultas T-SQL, segue descrição de cada capítulo.

1. SQL Query Performance Tuning

Neste capítulo é tratado de:

  • O processo de ajuste no desempenho;
  • Desempenho versus preço;
  • O patamar (baseline) de desempenho;
  • Onde concentrar os esforços de ajuste;
  • Os 13 principais matadores de desempenho no SQL Server.

2. Memory Performance Analysis

Because all this work is being done within the memory of the system, it’s important that you understand how memory is being managed.

Neste capítulo é tratado de:

  • Noções básicas da ferramenta Performance Monitor;
  • Alguns dos objetos dynamic management utilizados para observar o comportamento do sistema;
  • Como e porque os recursos de hardware podem se tornar gargalos;
  • Métodos de observação e medição da utilização da memória no SQL Server e no Windows;
  • Métodos de observação e medição da utilização da memória no Linux;
  • Possíveis resoluções de gargalos na memória.

3. Disk Performance Analysis

(…) disks are still one of the slowest parts of most systems. This means you’re going to want to be able to monitor your disks to understand their behavior.

Neste capítulo é tratado de:

  • Utilização de contadores de sistema para coletar métricas de desempenho do disco;
  • Utilização de outros mecanismos de coleta do comportamento do disco;
  • Resolução de problemas de desempenho do disco;
  • Diferenças em I/O de disco quando utilizando GNU/Linux.

4. CPU Performance Analysis

This chapter concludes the book’s exploration of the system, with a discussion about CPU, network, and general SQL Server metrics.

Neste capítulo é tratado de:

  • Como coletar métricas sobre o processador;
  • Métricas adicionais disponíveis através de consultas T-SQL;
  • Métodos para a resolução de gargalos do processador.

5. Creating a Baseline

This chapter will cover how to gather your metrics so that you have that baseline for later comparison. (…) A baseline is a fundamental part of understanding system behavior, so you should always have one available.

Neste capítulo é tratado de:

  • Considerações ao monitorar máquinas físicas e máquinas virtuais;
  • Como criar uma coleção automática de métricas do Performance Monitor;
  • Considerações para evitar problemas ao utilizar o Performance Monitor;
  • Patamar para o Azure SQL Database.

6. Query Performance Metrics

(…) it is important to examine the database application workload and identify the SQL queries causing the most stress on system resources.

Neste capítulo é tratado de:

  • Noções básicas de Extended Events;
  • Como analisar a carga de trabalho (workload) do SQL Server e identificar consultas SQL dispendiosas, utilizando Extended Events;
  • Como acompanhar o desempenho da consulta através de objectos dynamic management.

7. Analyzing Query Performance

The previous chapter showed how to gather query performance metrics. This chapter will show how to consume those metrics to identify long-running or frequently called queries.

Neste capítulo é tratado de:

  • Como analisar a estratégia de processamento de uma consulta SQL dispendiosa utilizando o Management Studio;
  • Como analisar os métodos utilizados pelo otimizador de consultas (query optimizer) para uma consulta SQL;
  • Como medir o custo de uma consulta SQL usando comandos T-SQL.

8. Index Architecture and Behavior

The right index on the right column, or columns, is the basis on which query tuning begins. (…) it is extremely important for everyone—not just a DBA—to understand the different indexing techniques that can be used to optimize the database design.

Neste capítulo é tratado de:

  • O que é um índice;
  • As vantagens e desvantagens de um índice;
  • Recomendações gerais para a definição de índices;
  • Comportamento e comparações dos índices agrupado (clustered) e não agrupado (nonclustered);
  • Recomendações para os índices agrupados e não agrupados.

9. Index Analysis

Neste capítulo é tratado de:

  • Técnicas avançadas de indexação;
  • Índices Columnstore
  • Tipos de índices especiais;
  • Características adicionais dos índices.

10. Database Engine Tuning Advisor

The task of deciding upon the correct indexes is complicated by the fact that an index change that benefits one set of queries may be detrimental to another set of queries. To help you through this process, SQL Server provides a tool called the Database Engine Tuning Advisor.

Neste capítulo é tratado de:

  • Como funciona o Database Engine Tuning Advisor (DTA);
  • Como utilizar o Database Engine Tuning Advisor para recomendações de índices em um conjunto de consultas problemáticas;
  • As limitações do Database Engine Tuning Advisor.

11. Query Store

The Query Store provides three pieces of functionality that you’re going to want to take advantage of. First, you get query metrics and execution plans, stored permanently in the database in structures that are easy to access so that you have good, flexible information about the performance of the queries on your system. Second, the Query Store creates a mechanism for directly controlling execution plan behavior in a way we’ve never had before. Finally, the Query Store acts as a safety and reporting mechanism for database upgrades that will enable you to protect your systems in new ways.

Neste capítulo é tratado de:

  • Como funciona o Repositório de Consultas (Query Store) e informações coletadas;
  • Relatórios e mecanismos acessíveis no Management Studio para o comportamento do Repositório de Consultas;
  • Plan forcing, um método para controlar quais planos de execução são utilizados pelo SQL Server e Azure SQL Database;
  • Um método de atualização que o ajuda a proteger o comportamento do seu sistema.

12. Key Lookups and Solutions

A major overhead associated with nonclustered indexes is the cost of excessive lookups, formerly known as bookmark lookups, which are a mechanism to navigate from a nonclustered index row to the corresponding data row in the clustered index or the heap.

Neste capítulo é tratado de:

  • O propósito do Lookup;
  • Os inconvenientes da utilização de Lookup;
  • Análise da causa de Lookup;
  • Técnicas para resolver Lookup.

13. Statistics, Data Distribution, and Cardinality

Finally, and possibly most important, the optimizer must have information about the data that defines an index or a column. That information is referred to as a statistic. Statistics define both the distribution of data and the uniqueness or selectivity of the data.

Neste capítulo é tratado de:

  • O papel das estatísticas na otimização das consultas;
  • A importância das estatísticas nas colunas com índices;
  • A importância das estatísticas nas colunas não indexadas utilizadas nos critérios de junção e de filtragem;
  • Análise de estatísticas de uma e de várias colunas, incluindo o cálculo da seletividade de uma coluna para indexação;
  • Manutenção das estatísticas;
  • Avaliação eficaz das estatísticas utilizadas na execução da consulta.

14. Index Fragmentation

In an OLTP database, data changes continually, causing fragmentation of the indexes. As a result, the number of reads required to return the same number of rows increases over time.

Neste capítulo é tratado de:

  • As causas da fragmentação do índice;
  • Os custos gerais associados à fragmentação;
  • Como analisar a quantidade de fragmentação nos índices;
  • Técnicas utilizadas para resolver a fragmentação;
  • A importância do fillfactor para ajudar a controlar a fragmentação nos índices rowstore;
  • Como automatizar o processo de análise de fragmentação.

15. Execution Plan Generation

The cost incurred when generating the execution plan depends on the process of generating the execution plan, the process of caching the plan, and the reusability of the plan from the plan cache

Neste capítulo é tratado de:

  • Geração e armazenamento de planos de execução;
  • Os componentes do SQL Server utilizados para gerar um plano de execução;
  • Estratégias para otimizar o custo de geração do plano de execução;
  • Fatores que afetam a geração de planos com processamento em paralelo.

16. Execution Plan Cache Behavior

This chapter will walk through how you can monitor the plan cache to see how SQL Server reuses execution plans.

Neste capítulo é tratado de:

  • Como analisar o cache do plano de execução;
  • Query plan hash e query hash como mecanismos de identificação de consultas a ajustar;
  • Formas de melhorar a reusabilidade do cache do plano de execução;
  • Interações entre o Repositório de Consultas (Query Store) e o cache do plano.

17. Parameter Sniffing

One of the best mechanisms for ensuring plan reuse is to parameterize the query, (…) These parameters can be sampled, or sniffed, by the optimizer to use the values contained within when creating the execution plan. When this works well, as it does most of the time, you benefit from more accurate plans. But when it goes wrong and becomes bad parameter sniffing, you can see serious performance issues.

Neste capítulo é tratado de:

  • Os mecanismos úteis por do parameter sniffing;
  • Como o parameter sniffing pode se tornar algo ruim;
  • Mecanismos para lidar com o parameter sniffing.

18. Query Recompilation

Stored procedures and parameterized queries improve the reusability of an execution plan by explicitly converting the variable parts of the queries into parameters. (…) However, sometimes the existing plan may not be optimal, or it may not provide the best processing strategy during reuse. SQL Server resolves this condition by recompiling statements within stored procedures to generate a new execution plan.

Neste capítulo é tratado de:

  • As vantagens e desvantagens da recompilação;
  • Como identificar comandos que provocam a recompilação;
  • Como analisar as causas das recompilações;
  • Formas de evitar recompilações.

19. Query Design Analysis

A database schema may include a number of performance-enhancement features such as indexes, statistics, and stored procedures. But none of these features guarantees good performance if your queries are written badly in the first place. (…) To improve the performance of a database application, it is important to understand the cost associated with varying ways of writing a query.

Neste capítulo é tratado de:

  • Aspectos do projeto da consulta que afetam o desempenho;
  • Como os modelos de consulta utilizam os índices de forma eficaz;
  • O papel das dicas do otimizador no desempenho da consulta;
  • O papel das restrições (constraints) da base de dados no desempenho da consulta.

20. Reduce Query Resource Use

In this chapter, you’ll make sure you’re writing a queries in such a way that they don’t use your resources in inappropriate ways. There are approaches to writing queries that avoid using memory, CPU, and I/O, as well as ways to write the queries that use more of these resources than you really should.

Neste capítulo é tratado de:

  • Modelos de consulta que consomem menos recursos;
  • Modelos de consulta que utilizam o cache de procedimentos de forma eficaz;
  • Modelos de consulta que reduzem a sobrecarga da rede;
  • Técnicas para reduzir o custo de transação de uma consulta.

21. Blocking and Blocked Processes

You would ideally like your database application to scale linearly with the number of database users and the volume of data. However, it is common to find that performance degrades as the number of users increases and as the volume of data grows. One cause for degradation, especially associated with ever-increasing scale, is blocking.

Neste capítulo é tratado de:

  • Os fundamentos do bloqueio de recursos no SQL Server;
  • As propriedades ACID de um banco de dados transacional;
  • Granularidade, escalonamento, modos e compatibilidade do bloqueio;
  • Níveis de isolamento ANSI;
  • O efeito dos índices no bloqueio;
  • As informações necessárias para analisar o bloqueio;
  • Um script SQL para coletar informações de bloqueio;
  • Resoluções e recomendações para evitar bloqueios de recursos;
  • Técnicas para automatizar os processos de detecção de bloqueios e coleta de informações.

22. Causes and Solutions for Deadlocks

Blocking can lead to a special situation referred to as a deadlock, (…) When a deadlock occurs between two or more transactions, SQL Server allows one transaction to complete and terminates the other transaction, rolling back the transaction.

Neste capítulo é tratado de:

  • Fundamentos do deadlock;
  • Manuseio de erros para tratar um deadlock;
  • Maneiras de analisar a causa de um deadlock;
  • Técnicas para resolver um deadlock.

23. Row-by-Row Processing

T-SQL and SQL Server are designed to work best with sets of data, not one row at a time. (…) However, if a cursor must be used, then use a cursor with the least cost.

Neste capítulo é tratado de:

  • Os fundamentos do cursor;
  • Uma análise de custos de diferentes características de cursor;
  • Os benefícios e inconvenientes de um resultado padrão definido sobre cursor;
  • Recomendações para minimizar os custos gerais dos cursor.

24. Memory-Optimized OLTP Tables and Procedures

One of the principal needs for online transaction processing (OLTP) systems is to get as much speed as possible out of the system. (…) The memory-optimized technologies consist of in-­memory tables and natively compiled stored procedures. This set of features is meant for high-end, transaction-intensive, OLTP-focused systems. (…) The memory-­optimized technologies are another tool in the toolbox of query tuning, but they are a highly specialized tool, applicable only to certain applications.

Neste capítulo é tratado de:

  • Noções básicas de como funciona a tabela in-memory;
  • Melhorar o desempenho através da compilação nativa de procedimento;
  • As vantagens e desvantagens de procedimento compilado nativamente e de tabela in-memory;
  • Recomendações para quando usar tabela in-memory.

25. Automated Tuning in Azure SQL Database and SQL Server

While a lot of query performance tuning involves detailed knowledge of the systems, queries, statistics, indexes, and all the rest of the information put forward in this book, certain aspects of query tuning are fairly mechanical in nature. (…) Microsoft has started the process of automating these aspects of query tuning. Further, it is putting other forms of automated mechanisms (…) that will help you by tuning aspects of your queries on the fly.

Neste capítulo é tratado de:

  • Correção automática do plano de execução;
  • Gerenciamento automático de índices do Azure SQL Database;
  • Processamento de consultas adaptativas.

26. Database Performance Testing

Knowing how to identify performance issues and knowing how to fix them are great skills to have. The problem, though, is that you need to be able to demonstrate that the improvements you make are real improvements. (…) You need to have a systematic way to validate performance improvements using the full panoply of queries that are run against your system in a realistic manner. (…) SQL Server provides such a mechanism through its Distributed Replay tool.

Neste capítulo é tratado de:

  • Os conceitos de testes de desempenho em banco de dados;
  • Como criar trace no servidor;
  • Utilizando Distributed Replay para testes de desempenho.

27. Database Workload Optimization

So far, you have learned about a number of aspects that can affect query performance, the tools that you can use to analyze query performance, and the optimization techniques you can use to improve query performance. Next, you will learn how to apply this information to analyze, troubleshoot, and optimize the performance of a database workload.

Neste capítulo é tratado de:

  • As características de uma carga de trabalho (workload) em banco de dados;
  • Os passos envolvidos na otimização da carga de trabalho do banco de dados;
  • Como identificar consultas dispendiosas na carga de trabalho;
  • Como medir o uso de recursos básicos e o desempenho de consultas dispendiosas;
  • Como analisar fatores que afetam o desempenho de consultas dispendiosas;
  • Como aplicar técnicas para otimizar consultas dispendiosas;
  • Como analisar os efeitos da otimização da consulta sobre a carga de trabalho global.

28. SQL Server Optimization Checklist

What I hope to do in this chapter is to provide a performance-monitoring checklist that can serve as a quick reference for database developers and DBAs when in the field. The idea is similar to the notion of tear-off cards of best practices. This chapter does not cover everything, but it does summarize, in one place, some of the major tuning activities that can have a quick and demonstrable impact on the performance of your SQL Server systems.


Gostou deste artigo? Então clica no botão CURTIR aí embaixo.

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Google

Você está comentando utilizando sua conta Google. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s