Passo a passo dos recursos de desempenho do SQL Server em Linux

Aplica-se a: SQL Server - Linux

Se você é um usuário do Linux que não está familiarizado com o SQL Server, as tarefas a seguir descrevem alguns dos recursos de desempenho. Elas não são exclusivas nem específicas do Linux, mas ajudam a dar uma ideia de áreas para posterior investigação. Em cada exemplo, um link é fornecido para a documentação detalhada dessa área.

Criar um índice columnstore

Um índice columnstore é uma tecnologia para armazenar e consultar grandes armazenamentos de dados em um formato de dados colunar, denominado um columnstore.

1. Adicione um índice columnstore à tabela SalesOrderDetail executando os seguintes comandos Transact-SQL:

   CREATE NONCLUSTERED COLUMNSTORE INDEX [IX_SalesOrderDetail_ColumnStore]
      ON Sales.SalesOrderDetail
      (UnitPrice, OrderQty, ProductID);
   GO
   

2. Execute a seguinte consulta que usa o índice columnstore para examinar a tabela:

   SELECT ProductID, SUM(UnitPrice) SumUnitPrice, AVG(UnitPrice) AvgUnitPrice,
      SUM(OrderQty) SumOrderQty, AVG(OrderQty) AvgOrderQty
   FROM Sales.SalesOrderDetail
      GROUP BY ProductID
      ORDER BY ProductID;
   

3. Verifique se o índice columnstore foi usado pesquisando a object_id do índice columnstore e confirmando se ele é exibido nas estatísticas de uso da tabela SalesOrderDetail:

   SELECT * FROM sys.indexes WHERE name = 'IX_SalesOrderDetail_ColumnStore'
   GO
   
   SELECT *
   FROM sys.dm_db_index_usage_stats
      WHERE database_id = DB_ID('AdventureWorks2022')
      AND object_id = OBJECT_ID('AdventureWorks2022.Sales.SalesOrderDetail');
   

Usar OLTP in-memory

O SQL Server fornece recursos de OLTP in-memory que podem melhorar muito o desempenho de sistemas de aplicativos. Esta seção percorre as etapas para criar uma tabela com otimização de memória armazenada na memória e um procedimento armazenado compilado nativamente que pode acessar a tabela sem precisar ser compilado ou interpretado.

Configurar banco de dados para OLTP in-memory

1. É recomendável definir o banco de dados para um nível de compatibilidade de pelo menos 130 para usar o OLTP in-memory. Use a consulta a seguir para verificar o nível de compatibilidade atual doAdventureWorks2022:

   USE AdventureWorks2022;
   GO
   SELECT d.compatibility_level
   FROM sys.databases as d
       WHERE d.name = DB_NAME();
   GO
   

   Se necessário, atualize o nível para 130:

   ALTER DATABASE CURRENT
   SET COMPATIBILITY_LEVEL = 130;
   GO
   

2. Quando uma transação envolve uma tabela baseada em disco e uma tabela com otimização de memória, é essencial que uma parte com otimização de memória da transação opere no nível de isolamento da transação chamado SNAPSHOT. Para impor com segurança este nível para as tabelas com otimização de memória em uma transação entre contêineres, execute o seguinte:

   ALTER DATABASE CURRENT SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON;
   GO
   

3. Para criar uma tabela com otimização de memória, primeiro, você precisa criar um grupo de arquivos otimizado para memória e um contêiner para arquivos de dados:

   ALTER DATABASE AdventureWorks2022
      ADD FILEGROUP AdventureWorks_mod
         CONTAINS memory_optimized_data;
   GO
   ALTER DATABASE AdventureWorks2022
      ADD FILE (NAME='AdventureWorks_mod',
      FILENAME='/var/opt/mssql/data/AdventureWorks_mod')
         TO FILEGROUP AdventureWorks_mod;
   GO
   

Criar uma tabela com otimização de memória

O repositório primário das tabelas com otimização de memória é a memória principal. Portanto, ao contrário das tabelas baseadas em disco, os dados não precisam ser lidos do disco para os buffers de memória. Para criar uma tabela com otimização de memória, use a cláusula MEMORY_OPTIMIZED = ON.

1. Execute a consulta a seguir para criar a tabela com otimização de memória dbo.ShoppingCart. Por padrão, os dados são persistentes em disco para fins de durabilidade (DURABILITY também pode ser definido para persistir apenas o esquema).

   CREATE TABLE dbo.ShoppingCart (
   ShoppingCartId INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
   UserId INT NOT NULL INDEX ix_UserId NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000),
   CreatedDate DATETIME2 NOT NULL,
   TotalPrice MONEY
   ) WITH (MEMORY_OPTIMIZED=ON);
   GO
   

2. Insira alguns recursos na tabela:

   INSERT dbo.ShoppingCart VALUES (8798, SYSDATETIME(), NULL);
   INSERT dbo.ShoppingCart VALUES (23, SYSDATETIME(), 45.4);
   INSERT dbo.ShoppingCart VALUES (80, SYSDATETIME(), NULL);
   INSERT dbo.ShoppingCart VALUES (342, SYSDATETIME(), 65.4);
   

Procedimentos armazenados e compilados de modo nativo

O SQL Server dá suporte a procedimentos armazenados nativamente compilados que acessam tabelas com otimização de memória. As instruções T-SQL são compiladas no código do computador e armazenadas como DLLs nativas, permitindo um acesso a dados mais rápido e uma execução de consulta mais eficiente do que o T-SQL tradicional. Os procedimentos armazenados que são marcados com NATIVE_COMPILATION são compilados nativamente.

1. Execute o script a seguir para criar um procedimento armazenado compilado nativamente que insere um grande número de registros na tabela ShoppingCart:

   CREATE PROCEDURE dbo.usp_InsertSampleCarts @InsertCount INT
       WITH NATIVE_COMPILATION, SCHEMABINDING AS
   BEGIN ATOMIC
       WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
   
   DECLARE @i INT = 0
   
   WHILE @i < @InsertCount
      BEGIN
          INSERT INTO dbo.ShoppingCart VALUES (1, SYSDATETIME(), NULL)
   
          SET @i += 1
      END
   END
   

2. Insira 1 milhão de linhas:

   EXEC usp_InsertSampleCarts 1000000;
   

3. Verifique se as linhas foram inseridas:

   SELECT COUNT(*) FROM dbo.ShoppingCart;
   

Usar o Repositório de Consultas

O Repositório de Consultas coleta informações de desempenho detalhadas sobre consultas, planos de execução e estatísticas de runtime.

Antes do SQL Server 2022 (16.x), o Repositório de Consultas não estava habilitado por padrão e podia ser habilitado com ALTER DATABASE:

ALTER DATABASE AdventureWorks2022 SET QUERY_STORE = ON;

Execute a consulta a seguir para retornar informações sobre consultas e planos no repositório de consultas:

SELECT Txt.query_text_id, Txt.query_sql_text, Pl.plan_id, Qry.*
FROM sys.query_store_plan AS Pl
   JOIN sys.query_store_query AS Qry
      ON Pl.query_id = Qry.query_id
   JOIN sys.query_store_query_text AS Txt
      ON Qry.query_text_id = Txt.query_text_id;

Consultar exibições de gerenciamento dinâmico

As exibições de gerenciamento dinâmico retornam informações do estado do servidor que podem ser usadas para monitorar a integridade de uma instância do servidor, diagnosticar problemas e ajustar o desempenho.

Para consultar a exibição de gerenciamento dinâmico de estatísticas dm_os_wait:

SELECT wait_type, wait_time_ms
FROM sys.dm_os_wait_stats;

Confira também

• Início rápido 1: tecnologias do OLTP in-memory para um desempenho mais rápido do Transact-SQL
• Migrando para OLTP na memória
• Tabela temporária e variável de tabela mais rápidas usando a otimização de memória
• Monitorar e solucionar problemas de uso da memória
• OLTP na memória (otimização na memória)

Conteúdo relacionado

• Ferramentas para monitoramento e ajuste de desempenho
• Práticas recomendadas de desempenho e diretrizes de configuração para o SQL Server em Linux
• Início Rápido: Instalar o SQL Server e criar um banco de dados no Red Hat