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

  • Artigo

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.

Observação

O exemplo a seguir usa o banco de dados de amostra AdventureWorks2022. Para obter instruções sobre como obter e instalar esse banco de dados de exemplo, confira Restaurar um banco de dados do SQL Server do Windows para o Linux.

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

Conteúdo relacionado