Gerenciamento de carga de trabalho com classes de recursos no SQL Data Warehouse do AzureWorkload management with resource classes in Azure SQL Data Warehouse

Diretrizes para usar classes de recursos para gerenciar a memória e simultaneidade para consultas no SQL Data Warehouse do Azure.Guidance for using resource classes to manage memory and concurrency for queries in your Azure SQL Data Warehouse.

O que são classes de recursoWhat are resource classes

A capacidade de desempenho de uma consulta é determinada pela classe de recurso do usuário.The performance capacity of a query is determined by the user's resource class. Classes de recursos são limites de recursos predeterminados no Azure SQL Data Warehouse que controlam recursos de computação e simultaneidade para execução da consulta.Resource classes are pre-determined resource limits in Azure SQL Data Warehouse that govern compute resources and concurrency for query execution. As classes de recursos podem ajudá-lo a configurar recursos para suas consultas definindo limites no número de consultas executadas simultaneamente e nos recursos de computação atribuídos a cada consulta.Resource classes can help you configure resources for your queries by setting limits on the number of queries that run concurrently and on the compute-resources assigned to each query. Há uma compensação entre a memória e a simultaneidade.There's a trade-off between memory and concurrency.

  • Classes de recursos menores reduzem a memória máxima por consulta, mas aumentam a simultaneidade.Smaller resource classes reduce the maximum memory per query, but increase concurrency.
  • Classes de recursos maiores aumentam a memória máxima por consulta, mas reduzem a simultaneidade.Larger resource classes increase the maximum memory per query, but reduce concurrency.

Há dois tipos de classes de recursos:There are two types of resource classes:

  • Classes de recursos estáticos, que são adequadas para aprimoramento de simultaneidade em um tamanho de conjunto de dados é fixa.Static resources classes, which are well suited for increased concurrency on a data set size that is fixed.
  • Classes de recursos dinâmicos, que são adequadas para conjuntos de dados que estão crescendo em tamanho e precisam de aumento de desempenho conforme o nível de serviço é escalado verticalmente.Dynamic resource classes, which are well suited for data sets that are growing in size and need increased performance as the service level is scaled up.

As classes de recursos usam slots de simultaneidade para medir o consumo de recursos.Resource classes use concurrency slots to measure resource consumption. Os slots de simultaneidade são explicados mais adiante neste artigo.Concurrency slots are explained later in this article.

Classes de recursos estáticosStatic resource classes

Classes de recursos estáticos alocam a mesma quantidade de memória, independentemente do nível de serviço atual, que é medido em unidades do data warehouse.Static resource classes allocate the same amount of memory regardless of the current performance level, which is measured in data warehouse units. Já que as consultas obtêm a mesma alocação de memória, independentemente do nível de desempenho, dimensionar o data warehouse permite que mais consultas sejam executadas em uma classe de recurso.Since queries get the same memory allocation regardless of the performance level, scaling out the data warehouse allows more queries to run within a resource class. Classes de recursos estáticos são ideais se o volume de dados é conhecido e constante.Static resource classes are ideal if the data volume is known and constant.

As classes de recursos estáticos são implementadas com essas funções de banco de dados predefinidos:The static resource classes are implemented with these pre-defined database roles:

  • staticrc10staticrc10
  • staticrc20staticrc20
  • staticrc30staticrc30
  • staticrc40staticrc40
  • staticrc50staticrc50
  • staticrc60staticrc60
  • staticrc70staticrc70
  • staticrc80staticrc80

Classes de recursos dinâmicosDynamic resource classes

Classes de recursos dinâmicos alocam uma quantidade variável de memória dependendo do nível de serviço atual.Dynamic Resource Classes allocate a variable amount of memory depending on the current service level. Embora as classes de recursos estáticos são benéficas para maior simultaneidade e volumes de dados estáticos, classes de recursos dinâmicos são mais adequados para um volume crescente ou variável de dados.While static resource classes are beneficial for higher concurrency and static data volumes, dynamic resource classes are better suited for a growing or variable amount of data. Quando você aumenta para um nível de serviço maior, as consultas automaticamente recebem mais memória.When you scale up to a larger service level, your queries automatically get more memory.

As classes de recursos dinâmicos são implementadas com essas funções de banco de dados predefinidos:The dynamic resource classes are implemented with these pre-defined database roles:

  • smallrcsmallrc
  • mediumrcmediumrc
  • largerclargerc
  • xlargercxlargerc

A alocação de memória para cada classe de recurso é a seguinte.The memory allocation for each resource class is as follows.

Nível do serviçoService Level smallrcsmallrc mediumrcmediumrc largerclargerc xlargercxlargerc
DW100cDW100c 25%25% 25%25% 25%25% 70%70%
DW200cDW200c 12,5%12.5% 12,5%12.5% 22%22% 70%70%
DW300cDW300c 8%8% 10%10% 22%22% 70%70%
DW400cDW400c 6,25%6.25% 10%10% 22%22% 70%70%
DW500cDW500c 20%20% 10%10% 22%22% 70%70%
DW1000c paraDW1000c to
DW30000cDW30000c
3%3% 10%10% 22%22% 70%70%

Classe de recurso padrãoDefault resource class

Por padrão, cada usuário é um membro da classe de recursos dinâmicos smallrc.By default, each user is a member of the dynamic resource class smallrc.

A classe de recurso do administrador de serviços é fixada em smallrc e não pode ser alterada.The resource class of the service administrator is fixed at smallrc and cannot be changed. O administrador de serviços é o usuário criado durante o processo de provisionamento.The service administrator is the user created during the provisioning process. O administrador de serviços nesse contexto é o logon especificado para o "Logon de administrador do servidor" ao criar uma instância do SQL Data Warehouse com um novo servidor.The service administrator in this context is the login specified for the "Server admin login" when creating a new SQL Data Warehouse instance with a new server.

Observação

Usuários ou grupos definidos como administrador do Active Directory também são administradores de serviços.Users or groups defined as Active Directory admin are also service administrators.

Operações de classe de recursoResource class operations

As classes de recursos são projetadas para melhorar o desempenho para atividades de gerenciamento e manipulação de dados.Resource classes are designed to improve performance for data management and manipulation activities. As consultas complexas também podem se beneficiar de ser executado em uma grande classe de recurso.Complex queries can also benefit from running under a large resource class. Por exemplo, a consulta de desempenho para grandes junções e classificações podem aumentar quando a classe de recurso é grande o suficiente para habilitar a consulta a ser executada na memória.For example, query performance for large joins and sorts can improve when the resource class is large enough to enable the query to execute in memory.

Operações governadas por classes de recursoOperations governed by resource classes

Essas operações são governadas por classes de recurso:These operations are governed by resource classes:

  • INSERT-SELECT, UPDATE, DELETEINSERT-SELECT, UPDATE, DELETE
  • SELECT (ao consultar tabelas de usuário)SELECT (when querying user tables)
  • ALTER INDEX - REBUILD ou REORGANIZEALTER INDEX - REBUILD or REORGANIZE
  • ALTER TABLE REBUILDALTER TABLE REBUILD
  • CREATE INDEXCREATE INDEX
  • CREATE CLUSTERED COLUMNSTORE INDEXCREATE CLUSTERED COLUMNSTORE INDEX
  • CREATE TABLE AS SELECT (CTAS)CREATE TABLE AS SELECT (CTAS)
  • Carregamento de dadosData loading
  • Operações de movimentação de dados realizadas pelo Serviço de Movimentação de Dados (DMS)Data movement operations conducted by the Data Movement Service (DMS)

Observação

Instruções SELECT nas DMVs (exibições de gerenciamento dinâmico) ou em outras exibições do sistema não são regidas por nenhum dos limites de simultaneidade.SELECT statements on dynamic management views (DMVs) or other system views are not governed by any of the concurrency limits. Você pode monitorar o sistema independentemente do número de consultas em execução nele.You can monitor the system regardless of the number of queries executing on it.

Operações não governadas por classes de recursoOperations not governed by resource classes

Algumas consultas sempre são executadas na classe de recurso smallrc, mesmo se o usuário for um membro de uma classe de recursos maior.Some queries always run in the smallrc resource class even though the user is a member of a larger resource class. Essas consultas isentas não são consideradas no limite de simultaneidade.These exempt queries do not count towards the concurrency limit. Por exemplo, se o limite de simultaneidade for 16, muitos usuários poderão estar selecionando de exibições do sistema sem afetar os slots de simultaneidade disponíveis.For example, if the concurrency limit is 16, many users can be selecting from system views without impacting the available concurrency slots.

As instruções a seguir estão isentas das classes de recursos e sempre são executadas em smallrc:The following statements are exempt from resource classes and always run in smallrc:

  • CREATE ou DROP TABLECREATE or DROP TABLE
  • ALTERAR TABELA... COMUTAdor, divisão ou MESCLAr partiçãoALTER TABLE ... SWITCH, SPLIT, or MERGE PARTITION
  • ALTER INDEX DISABLEALTER INDEX DISABLE
  • DROP INDEXDROP INDEX
  • CREATE, UPDATE ou DROP STATISTICSCREATE, UPDATE, or DROP STATISTICS
  • TRUNCATE TABLETRUNCATE TABLE
  • ALTER AUTHORIZATIONALTER AUTHORIZATION
  • CREATE LOGINCREATE LOGIN
  • CREATE, ALTER ou DROP USERCREATE, ALTER, or DROP USER
  • CREATE, ALTER ou DROP PROCEDURECREATE, ALTER, or DROP PROCEDURE
  • CREATE ou DROP VIEWCREATE or DROP VIEW
  • INSERT VALUESINSERT VALUES
  • SELECT de exibições do sistema e DMVsSELECT from system views and DMVs
  • EXPLAINEXPLAIN
  • DBCCDBCC

Slots de simultaneidadeConcurrency slots

Slots de simultaneidade são uma maneira conveniente para controlar os recursos disponíveis para execução da consulta.Concurrency slots are a convenient way to track the resources available for query execution. Eles são como tíquetes que você compra para reservar assentos em um concerto, pois a capacidade é limitada.They are like tickets that you purchase to reserve seats at a concert because seating is limited. O número total de slots de simultaneidade por data warehouse é determinado pelo nível de serviço.The total number of concurrency slots per data warehouse is determined by the service level. Antes de iniciar a execução de uma consulta, ela deve ser capaz de reserva slots de simultaneidade suficientes.Before a query can start executing, it must be able to reserve enough concurrency slots. Quando uma consulta for concluída, ela libera seus slots de simultaneidade.When a query finishes, it releases its concurrency slots.

  • Uma consulta em execução com 10 slots de simultaneidade pode acessar 5 vezes mais recursos de computação que uma consulta em execução com 2 slots de simultaneidade.A query running with 10 concurrency slots can access 5 times more compute resources than a query running with 2 concurrency slots.
  • Se cada consulta exige 10 slots de simultaneidade e houver 40 slots de simultaneidade, então, apenas 4 consultas podem ser executados simultaneamente.If each query requires 10 concurrency slots and there are 40 concurrency slots, then only 4 queries can run concurrently.

Apenas consultas governadas por recurso consomem slots de simultaneidade.Only resource governed queries consume concurrency slots. As consultas do sistema e algumas consultas triviais não consomem nenhum slot.System queries and some trivial queries don't consume any slots. O número exato de Slots de simultaneidade consumidos é determinado pela classe de recurso da consulta.The exact number of concurrency slots consumed is determined by the query's resource class.

Exibir as classes de recursosView the resource classes

As classes de recursos são implementadas como funções de banco de dados predefinidos.Resource classes are implemented as pre-defined database roles. Há dois tipos de classes de recursos: dinâmico e estático.There are two types of resource classes: dynamic and static. Para exibir uma lista das classes de recursos, use a seguinte consulta:To view a list of the resource classes, use the following query:

SELECT name
FROM   sys.database_principals
WHERE  name LIKE '%rc%' AND type_desc = 'DATABASE_ROLE';

Alterar uma classe de recursos de usuárioChange a user's resource class

Classes de recursos são implementadas atribuindo usuários a funções de banco de dados.Resource classes are implemented by assigning users to database roles. Quando um usuário executa uma consulta, a consulta é executada com a classe de recurso do usuário.When a user runs a query, the query runs with the user's resource class. Por exemplo, se um usuário for membro da função de banco de dados staticrc10, suas consultas serão executadas com pequenas quantidades de memória.For example, if a user is a member of the staticrc10 database role, their queries run with small amounts of memory. Se um usuário de banco de dados for membro das funções de banco de dados xlargerc ou staticrc80, suas consultas serão executadas com grandes quantidades de memória.If a database user is a member of the xlargerc or staticrc80 database roles, their queries run with large amounts of memory.

Para aumentar a classe de recurso de um usuário, use sp_addrolemember para adicionar o usuário a uma função de banco de dados de uma classe de recurso grande.To increase a user's resource class, use sp_addrolemember to add the user to a database role of a large resource class. O código abaixo adiciona um usuário à função de banco de dados largerc.The below code adds a user to the largerc database role. Cada solicitação Obtém 22% da memória do sistema.Each request gets 22% of the system memory.

EXEC sp_addrolemember 'largerc', 'loaduser';

Para diminuir a classe de recurso, use sp_droprolemember.To decrease the resource class, use sp_droprolemember. Se ' loaduser ' não for um membro ou outras classes de recurso, eles entrarão na classe de recurso smallrc padrão com uma concessão de memória de 3%.If 'loaduser' is not a member or any other resource classes, they go into the default smallrc resource class with a 3% memory grant.

EXEC sp_droprolemember 'largerc', 'loaduser';

Precedência de classe de recursoResource class precedence

Os usuários podem ser membros de várias classes de recursos.Users can be members of multiple resource classes. Quando um usuário pertence a mais de uma classe de recurso:When a user belongs to more than one resource class:

  • Classes de recursos dinâmicos têm precedência sobre classes de recursos estáticos.Dynamic resource classes take precedence over static resource classes. Por exemplo, se um usuário for membro de mediumrc (dinâmico) e staticrc80 (estático), as consultas serão executadas com mediumrc.For example, if a user is a member of both mediumrc(dynamic) and staticrc80 (static), queries run with mediumrc.
  • Classes de recursos maiores têm precedência sobre classes de recursos menores.Larger resource classes take precedence over smaller resource classes. Por exemplo, se um usuário for membro de mediumrc e largerc, as consultas serão executadas com largerc.For example, if a user is a member of mediumrc and largerc, queries run with largerc. Da mesma forma, se um usuário for membro de staticrc20 e statirc80, as consultas serão executadas com alocações de recursos staticrc80.Likewise, if a user is a member of both staticrc20 and statirc80, queries run with staticrc80 resource allocations.

RecomendaçõesRecommendations

Observação

Considere aproveitar os recursos de gerenciamento de carga de trabalho (isolamento de carga de trabalho, classificação e importância) para obter mais controle sobre sua carga de trabalho e desempenho previsívelConsider leveraging workload management capabilities (workload isolation, classification and importance) for more control over your workload and predictable performance.

É recomendável criar um usuário dedicado à execução de um tipo específico de consulta ou operação de carregamento.We recommend creating a user that is dedicated to running a specific type of query or load operation. Dê a esse usuário uma classe de recurso permanente em vez de alterar a classe de recurso com frequência.Give that user a permanent resource class instead of changing the resource class on a frequent basis. As classes de recursos estáticos oferecem maior controle geral na carga de trabalho, então sugerimos o uso de classes de recursos estáticos antes de considerar as classes de recursos dinâmicos.Static resource classes afford greater overall control on the workload, so we suggest using static resource classes before considering dynamic resource classes.

Classes de recursos para usuários de cargaResource classes for load users

O CREATE TABLE usa índices columnstore clusterizados por padrão.CREATE TABLE uses clustered columnstore indexes by default. A compactação de dados em um índice columnstore é uma operação de uso intensivo de memória e a pressão na memória pode reduzir a qualidade do índice.Compressing data into a columnstore index is a memory-intensive operation, and memory pressure can reduce the index quality. A pressão de memória pode levar à necessidade de uma classe de recursos mais alta ao carregar dados.Memory pressure can lead to needing a higher resource class when loading data. Para garantir que as cargas tenham memória suficiente, você pode criar um usuário que seja designado para que execute cargas e atribuir esse usuário a uma classe de recurso maior.To ensure loads have enough memory, you can create a user that is designated for running loads and assign that user to a higher resource class.

A memória necessária para processar cargas com eficiência depende da natureza da tabela carregada e do tamanho dos dados.The memory needed to process loads efficiently depends on the nature of the table loaded and the data size. Para obter mais informações sobre requisitos de memória, consulte Maximizando a qualidade do grupo de linhas.For more information on memory requirements, see Maximizing rowgroup quality.

Depois de determinar o requisito de memória, escolha se deseja atribuir o usuário de carga a uma classe de recurso estático ou dinâmico.Once you have determined the memory requirement, choose whether to assign the load user to a static or dynamic resource class.

  • Use uma classe de recurso estático quando os requisitos de memória de tabela estiverem em um intervalo específico.Use a static resource class when table memory requirements fall within a specific range. As cargas são executadas com memória apropriada.Loads run with appropriate memory. Quando você dimensiona o data warehouse, as cargas não precisam de mais memória.When you scale the data warehouse, the loads do not need more memory. Ao usar uma classe de recurso estático, as alocações de memória permanecem constantes.By using a static resource class, the memory allocations stay constant. Essa consistência economiza a memória e permite que mais consultas sejam executadas simultaneamente.This consistency conserves memory and allows more queries to run concurrently. É recomendável que novas soluções usem as classes de recursos estáticos primeiro, pois eles fornecem maior controle.We recommend that new solutions use the static resource classes first as these provide greater control.
  • Use uma classe de recursos dinâmicos quando os requisitos de memória de tabela variarem muito.Use a dynamic resource class when table memory requirements vary widely. As cargas podem exigir mais memória do que a DWU atual ou que o nível de cDWU fornece.Loads might require more memory than the current DWU or cDWU level provides. O dimensionamento da data warehouse adiciona mais memória às operações de carregamento, o que permite que as cargas sejam executadas mais rapidamente.Scaling the data warehouse adds more memory to load operations, which allows loads to perform faster.

Classes de recursos para consultasResource classes for queries

Algumas consultas são de computação intensiva e outras não.Some queries are compute-intensive and some aren't.

  • Escolha uma classe de recurso dinâmico quando as consultas forem complexas, mas não precisarem de alta simultaneidade.Choose a dynamic resource class when queries are complex, but don't need high concurrency. Por exemplo, a geração de relatórios diários ou semanais é uma necessidade ocasional para recursos.For example, generating daily or weekly reports is an occasional need for resources. Se os relatórios estiverem processando grandes quantidades de dados, dimensionar o data warehouse fornece mais memória à classe de recurso existente do usuário.If the reports are processing large amounts of data, scaling the data warehouse provides more memory to the user's existing resource class.
  • Escolha uma classe de recurso estático quando as expectativas de recursos variarem ao longo do dia.Choose a static resource class when resource expectations vary throughout the day. Por exemplo, uma classe de recurso estático funciona bem quando o data warehouse é consultado por várias pessoas.For example, a static resource class works well when the data warehouse is queried by many people. Ao dimensionar o data warehouse, a quantidade de memória alocada para o usuário não é alterada.When scaling the data warehouse, the amount of memory allocated to the user doesn't change. Consequentemente, mais consultas podem ser executadas em paralelo no sistema.Consequently, more queries can be executed in parallel on the system.

As concessões de memória adequadas dependem de vários fatores, como a quantidade de dados consultados, a natureza dos esquemas de tabela e vários predicados de junções, de seleção e de grupo.Proper memory grants depend on many factors, such as the amount of data queried, the nature of the table schemas, and various joins, select, and group predicates. Em geral, alocar mais memória permitirá que consultas sejam concluídas mais rapidamente, mas reduzirá a simultaneidade geral.In general, allocating more memory allows queries to complete faster, but reduces the overall concurrency. Se a simultaneidade não for um problema, a alocação excessiva de memória não prejudicará a taxa de transferência.If concurrency is not an issue, over-allocating memory does not harm throughput.

Para ajustar o desempenho, use classes de recursos diferentes.To tune performance, use different resource classes. A próxima seção fornece um procedimento armazenado que ajuda você a descobrir a melhor classe de recurso.The next section gives a stored procedure that helps you figure out the best resource class.

Exemplo de código para encontrar a melhor classe de recursoExample code for finding the best resource class

Você pode usar o seguinte procedimento armazenado especificado para descobrir a simultaneidade e a concessão de memória por classe de recurso em um determinado SLO e a melhor classe de recursos para operações de CCI com uso intensivo de memória em uma tabela CCI não particionada em uma determinada classe de recurso:You can use the following specified stored procedure to figure out concurrency and memory grant per resource class at a given SLO and the best resource class for memory intensive CCI operations on non-partitioned CCI table at a given resource class:

Aqui está a finalidade deste procedimento armazenado:Here's the purpose of this stored procedure:

  1. Visualizar a simultaneidade e a concessão de memória por classe de recursos em um determinado SLO.To see the concurrency and memory grant per resource class at a given SLO. O usuário precisa fornecer NULL para o esquema e tablename, conforme mostrado neste exemplo.User needs to provide NULL for both schema and tablename as shown in this example.
  2. Para ver a melhor classe de recurso para as operações de CCI com uso intensivo de memória (carregar, copiar tabela, recompilar índice, etc.) na tabela CCI não particionada em uma determinada classe de recurso.To see the best resource class for the memory-intensive CCI operations (load, copy table, rebuild index, etc.) on non-partitioned CCI table at a given resource class. O procedimento armazenado usa o esquema da tabela para descobrir a concessão de memória necessária.The stored proc uses table schema to find out the required memory grant.

Dependências & restriçõesDependencies & Restrictions

  • Esse procedimento armazenado não foi projetado para calcular o requisito de memória para uma tabela CCI particionada.This stored procedure isn't designed to calculate the memory requirement for a partitioned cci table.
  • Esse procedimento armazenado não leva em conta os requisitos de memória para a parte SELECT de CTAS/INSERT-SELECT e pressupõe que é uma seleção.This stored procedure doesn't take memory requirements into account for the SELECT part of CTAS/INSERT-SELECT and assumes it's a SELECT.
  • Esse procedimento armazenado usa uma tabela temporária que está disponível na sessão em que esse procedimento armazenado foi criado.This stored procedure uses a temp table, which is available in the session where this stored procedure was created.
  • Esse procedimento armazenado depende das ofertas atuais (por exemplo, configuração de hardware, config do DMS) e, se qualquer uma dessas alterações for alterada, esse procedimento armazenado não funcionará corretamente.This stored procedure depends on the current offerings (for example, hardware configuration, DMS config), and if any of that changes then this stored proc won't work correctly.
  • Esse procedimento armazenado depende das ofertas de limite de simultaneidade existentes e, se essa alteração for alterada, esse procedimento armazenado não funcionará corretamente.This stored procedure depends on existing concurrency limit offerings and if these change then this stored procedure won't work correctly.
  • Esse procedimento armazenado depende das ofertas de classe de recurso existentes e, se essas alterações forem alteradas, esse procedimento armazenado não funcionará corretamente.This stored procedure depends on existing resource class offerings and if these change then this stored procedure won't work correctly.

Observação

Se você não estiver obtendo a saída após executar o procedimento armazenado com parâmetros fornecidos, então poderá ser dois casos.If you are not getting output after executing stored procedure with parameters provided, then there could be two cases.

  1. Um parâmetro de DW contém um valor inválido de SLOEither DW Parameter contains an invalid SLO value
  2. Ou, não há nenhuma classe de recurso correspondente para a operação CCI na tabela.Or, there is no matching resource class for the CCI operation on the table.

Por exemplo, em DW100c, a concessão de memória mais alta disponível é de 1 GB e se o esquema de tabela for grande o suficiente para cruzar o requisito de 1 GB.For example, at DW100c, the highest memory grant available is 1 GB, and if table schema is wide enough to cross the requirement of 1 GB.

Exemplo de usoUsage example

Sintaxe:Syntax:
EXEC dbo.prc_workload_management_by_DWU @DWU VARCHAR(7), @SCHEMA_NAME VARCHAR(128), @TABLE_NAME VARCHAR(128)

  1. @DWU: fornecer um parâmetro nulo para extrair o DWU atual do banco de BD DW ou fornecer qualquer DWU com suporte na forma de ' DW100c '@DWU: Either provide a NULL parameter to extract the current DWU from the DW DB or provide any supported DWU in the form of 'DW100c'
  2. @SCHEMA_NAME: Forneça um nome de esquema da tabela@SCHEMA_NAME: Provide a schema name of the table
  3. @TABLE_NAME: Forneça um nome de tabela dos juros@TABLE_NAME: Provide a table name of the interest

Exemplos executando esse procedimento armazenado:Examples executing this stored proc:

EXEC dbo.prc_workload_management_by_DWU 'DW2000c', 'dbo', 'Table1';  
EXEC dbo.prc_workload_management_by_DWU NULL, 'dbo', 'Table1';  
EXEC dbo.prc_workload_management_by_DWU 'DW6000c', NULL, NULL;  
EXEC dbo.prc_workload_management_by_DWU NULL, NULL, NULL;  

A instrução a seguir cria Table1 que é usada nos exemplos anteriores.The following statement creates Table1 that is used in the preceding examples. CREATE TABLE Table1 (a int, b varchar(50), c decimal (18,10), d char(10), e varbinary(15), f float, g datetime, h date);

Definição do procedimento armazenadoStored procedure definition

-------------------------------------------------------------------------------
-- Dropping prc_workload_management_by_DWU procedure if it exists.
-------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'prc_workload_management_by_DWU')
DROP PROCEDURE dbo.prc_workload_management_by_DWU
GO

-------------------------------------------------------------------------------
-- Creating prc_workload_management_by_DWU.
-------------------------------------------------------------------------------
CREATE PROCEDURE dbo.prc_workload_management_by_DWU
(@DWU VARCHAR(7),
 @SCHEMA_NAME VARCHAR(128),
 @TABLE_NAME VARCHAR(128)
)
AS

IF @DWU IS NULL
BEGIN
-- Selecting proper DWU for the current DB if not specified.

SELECT @DWU = 'DW'+ CAST(CASE WHEN Mem> 4 THEN Nodes*500 
  ELSE Mem*100 
  END AS VARCHAR(10)) +'c'
    FROM (
      SELECT Nodes=count(distinct n.pdw_node_id), Mem=max(i.committed_target_kb/1000/1000/60)
        FROM sys.dm_pdw_nodes n
        CROSS APPLY sys.dm_pdw_nodes_os_sys_info i
        WHERE type = 'COMPUTE')A
END

-- Dropping temp table if exists.
IF OBJECT_ID('tempdb..#ref') IS NOT NULL
BEGIN
  DROP TABLE #ref;
END;

-- Creating ref. temp table (CTAS) to hold mapping info.
CREATE TABLE #ref
WITH (DISTRIBUTION = ROUND_ROBIN)
AS
WITH
-- Creating concurrency slots mapping for various DWUs.
alloc
AS
(
SELECT 'DW100c' AS DWU,4 AS max_queries,4 AS max_slots,1 AS slots_used_smallrc,1 AS slots_used_mediumrc,2 AS slots_used_largerc,4 AS slots_used_xlargerc,1 AS slots_used_staticrc10,2 AS slots_used_staticrc20,4 AS slots_used_staticrc30,4 AS slots_used_staticrc40,4 AS slots_used_staticrc50,4 AS slots_used_staticrc60,4 AS slots_used_staticrc70,4 AS slots_used_staticrc80
  UNION ALL
   SELECT 'DW200c',8,8,1,2,4,8,1,2,4,8,8,8,8,8
  UNION ALL
   SELECT 'DW300c',12,12,1,2,4,8,1,2,4,8,8,8,8,8
  UNION ALL
   SELECT 'DW400c',16,16,1,4,8,16,1,2,4,8,16,16,16,16
  UNION ALL
   SELECT 'DW500c',20,20,1,4,8,16,1,2,4,8,16,16,16,16
  UNION ALL
   SELECT 'DW1000c',32,40,1,4,8,28,1,2,4,8,16,32,32,32
  UNION ALL
   SELECT 'DW1500c',32,60,1,6,13,42,1,2,4,8,16,32,32,32
  UNION ALL
   SELECT 'DW2000c',48,80,2,8,17,56,1,2,4,8,16,32,64,64
  UNION ALL
   SELECT 'DW2500c',48,100,3,10,22,70,1,2,4,8,16,32,64,64
  UNION ALL
   SELECT 'DW3000c',64,120,3,12,26,84,1,2,4,8,16,32,64,64
  UNION ALL
   SELECT 'DW5000c',64,200,6,20,44,140,1,2,4,8,16,32,64,128
  UNION ALL
   SELECT 'DW6000c',128,240,7,24,52,168,1,2,4,8,16,32,64,128
  UNION ALL
   SELECT 'DW7500c',128,300,9,30,66,210,1,2,4,8,16,32,64,128
  UNION ALL
   SELECT 'DW10000c',128,400,12,40,88,280,1,2,4,8,16,32,64,128
  UNION ALL
   SELECT 'DW15000c',128,600,18,60,132,420,1,2,4,8,16,32,64,128
  UNION ALL
   SELECT 'DW30000c',128,1200,36,120,264,840,1,2,4,8,16,32,64,128
)
-- Creating workload mapping to their corresponding slot consumption and default memory grant.
,map  
AS
(
  SELECT CONVERT(varchar(20), 'SloDWGroupSmall') AS wg_name, slots_used_smallrc AS slots_used FROM alloc WHERE DWU = @DWU
UNION ALL
  SELECT CONVERT(varchar(20), 'SloDWGroupMedium') AS wg_name, slots_used_mediumrc AS slots_used FROM alloc WHERE DWU = @DWU
UNION ALL
  SELECT CONVERT(varchar(20), 'SloDWGroupLarge') AS wg_name, slots_used_largerc AS slots_used FROM alloc WHERE DWU = @DWU
UNION ALL
  SELECT CONVERT(varchar(20), 'SloDWGroupXLarge') AS wg_name, slots_used_xlargerc AS slots_used FROM alloc WHERE DWU = @DWU
  UNION ALL
  SELECT 'SloDWGroupC00',1
  UNION ALL
    SELECT 'SloDWGroupC01',2
  UNION ALL
    SELECT 'SloDWGroupC02',4
  UNION ALL
    SELECT 'SloDWGroupC03',8
  UNION ALL
    SELECT 'SloDWGroupC04',16
  UNION ALL
    SELECT 'SloDWGroupC05',32
  UNION ALL
    SELECT 'SloDWGroupC06',64
  UNION ALL
    SELECT 'SloDWGroupC07',128
)

-- Creating ref based on current / asked DWU.
, ref
AS
(
  SELECT  a1.*
  ,       m1.wg_name          AS wg_name_smallrc
  ,       m1.slots_used * 250 AS tgt_mem_grant_MB_smallrc
  ,       m2.wg_name          AS wg_name_mediumrc
  ,       m2.slots_used * 250 AS tgt_mem_grant_MB_mediumrc
  ,       m3.wg_name          AS wg_name_largerc
  ,       m3.slots_used * 250 AS tgt_mem_grant_MB_largerc
  ,       m4.wg_name          AS wg_name_xlargerc
  ,       m4.slots_used * 250 AS tgt_mem_grant_MB_xlargerc
  ,       m5.wg_name          AS wg_name_staticrc10
  ,       m5.slots_used * 250 AS tgt_mem_grant_MB_staticrc10
  ,       m6.wg_name          AS wg_name_staticrc20
  ,       m6.slots_used * 250 AS tgt_mem_grant_MB_staticrc20
  ,       m7.wg_name          AS wg_name_staticrc30
  ,       m7.slots_used * 250 AS tgt_mem_grant_MB_staticrc30
  ,       m8.wg_name          AS wg_name_staticrc40
  ,       m8.slots_used * 250 AS tgt_mem_grant_MB_staticrc40
  ,       m9.wg_name          AS wg_name_staticrc50
  ,       m9.slots_used * 250 AS tgt_mem_grant_MB_staticrc50
  ,       m10.wg_name          AS wg_name_staticrc60
  ,       m10.slots_used * 250 AS tgt_mem_grant_MB_staticrc60
  ,       m11.wg_name          AS wg_name_staticrc70
  ,       m11.slots_used * 250 AS tgt_mem_grant_MB_staticrc70
  ,       m12.wg_name          AS wg_name_staticrc80
  ,       m12.slots_used * 250 AS tgt_mem_grant_MB_staticrc80
  FROM alloc a1
  JOIN map   m1  ON a1.slots_used_smallrc     = m1.slots_used and m1.wg_name = 'SloDWGroupSmall'
  JOIN map   m2  ON a1.slots_used_mediumrc    = m2.slots_used and m2.wg_name = 'SloDWGroupMedium'
  JOIN map   m3  ON a1.slots_used_largerc     = m3.slots_used and m3.wg_name = 'SloDWGroupLarge'
  JOIN map   m4  ON a1.slots_used_xlargerc    = m4.slots_used and m4.wg_name = 'SloDWGroupXLarge'
  JOIN map   m5  ON a1.slots_used_staticrc10    = m5.slots_used and m5.wg_name NOT IN ('SloDWGroupSmall','SloDWGroupMedium','SloDWGroupLarge','SloDWGroupXLarge')
  JOIN map   m6  ON a1.slots_used_staticrc20    = m6.slots_used and m6.wg_name NOT IN ('SloDWGroupSmall','SloDWGroupMedium','SloDWGroupLarge','SloDWGroupXLarge')
  JOIN map   m7  ON a1.slots_used_staticrc30    = m7.slots_used and m7.wg_name NOT IN ('SloDWGroupSmall','SloDWGroupMedium','SloDWGroupLarge','SloDWGroupXLarge')
  JOIN map   m8  ON a1.slots_used_staticrc40    = m8.slots_used and m8.wg_name NOT IN ('SloDWGroupSmall','SloDWGroupMedium','SloDWGroupLarge','SloDWGroupXLarge')
  JOIN map   m9  ON a1.slots_used_staticrc50    = m9.slots_used and m9.wg_name NOT IN ('SloDWGroupSmall','SloDWGroupMedium','SloDWGroupLarge','SloDWGroupXLarge')
  JOIN map   m10  ON a1.slots_used_staticrc60    = m10.slots_used and m10.wg_name NOT IN ('SloDWGroupSmall','SloDWGroupMedium','SloDWGroupLarge','SloDWGroupXLarge')
  JOIN map   m11  ON a1.slots_used_staticrc70    = m11.slots_used and m11.wg_name NOT IN ('SloDWGroupSmall','SloDWGroupMedium','SloDWGroupLarge','SloDWGroupXLarge')
  JOIN map   m12  ON a1.slots_used_staticrc80    = m12.slots_used and m12.wg_name NOT IN ('SloDWGroupSmall','SloDWGroupMedium','SloDWGroupLarge','SloDWGroupXLarge')
  WHERE   a1.DWU = @DWU
)
SELECT  DWU
,       max_queries
,       max_slots
,       slots_used
,       wg_name
,       tgt_mem_grant_MB
,       up1 as rc
,       (ROW_NUMBER() OVER(PARTITION BY DWU ORDER BY DWU)) as rc_id
FROM
(
    SELECT  DWU
    ,       max_queries
    ,       max_slots
    ,       slots_used
    ,       wg_name
    ,       tgt_mem_grant_MB
    ,       REVERSE(SUBSTRING(REVERSE(wg_names),1,CHARINDEX('_',REVERSE(wg_names),1)-1)) as up1
    ,       REVERSE(SUBSTRING(REVERSE(tgt_mem_grant_MBs),1,CHARINDEX('_',REVERSE(tgt_mem_grant_MBs),1)-1)) as up2
    ,       REVERSE(SUBSTRING(REVERSE(slots_used_all),1,CHARINDEX('_',REVERSE(slots_used_all),1)-1)) as up3
    FROM    ref AS r1
    UNPIVOT
    (
        wg_name FOR wg_names IN (wg_name_smallrc,wg_name_mediumrc,wg_name_largerc,wg_name_xlargerc,
        wg_name_staticrc10, wg_name_staticrc20, wg_name_staticrc30, wg_name_staticrc40, wg_name_staticrc50,
        wg_name_staticrc60, wg_name_staticrc70, wg_name_staticrc80)
    ) AS r2
    UNPIVOT
    (
        tgt_mem_grant_MB FOR tgt_mem_grant_MBs IN (tgt_mem_grant_MB_smallrc,tgt_mem_grant_MB_mediumrc,
        tgt_mem_grant_MB_largerc,tgt_mem_grant_MB_xlargerc, tgt_mem_grant_MB_staticrc10, tgt_mem_grant_MB_staticrc20,
        tgt_mem_grant_MB_staticrc30, tgt_mem_grant_MB_staticrc40, tgt_mem_grant_MB_staticrc50,
        tgt_mem_grant_MB_staticrc60, tgt_mem_grant_MB_staticrc70, tgt_mem_grant_MB_staticrc80)
    ) AS r3
    UNPIVOT
    (
        slots_used FOR slots_used_all IN (slots_used_smallrc,slots_used_mediumrc,slots_used_largerc,
        slots_used_xlargerc, slots_used_staticrc10, slots_used_staticrc20, slots_used_staticrc30,
        slots_used_staticrc40, slots_used_staticrc50, slots_used_staticrc60, slots_used_staticrc70,
        slots_used_staticrc80)
    ) AS r4
) a
WHERE   up1 = up2
AND     up1 = up3
;

-- Getting current info about workload groups.
WITH  
dmv  
AS  
(
  SELECT
          rp.name                                           AS rp_name
  ,       rp.max_memory_kb*1.0/1048576                      AS rp_max_mem_GB
  ,       (rp.max_memory_kb*1.0/1024)
          *(request_max_memory_grant_percent/100)           AS max_memory_grant_MB
  ,       (rp.max_memory_kb*1.0/1048576)
          *(request_max_memory_grant_percent/100)           AS max_memory_grant_GB
  ,       wg.name                                           AS wg_name
  ,       wg.importance                                     AS importance
  ,       wg.request_max_memory_grant_percent               AS request_max_memory_grant_percent
  FROM    sys.dm_pdw_nodes_resource_governor_workload_groups wg
  JOIN    sys.dm_pdw_nodes_resource_governor_resource_pools rp    ON  wg.pdw_node_id  = rp.pdw_node_id
                                                                  AND wg.pool_id      = rp.pool_id
  WHERE   rp.name = 'SloDWPool'
  GROUP BY
          rp.name
  ,       rp.max_memory_kb
  ,       wg.name
  ,       wg.importance
  ,       wg.request_max_memory_grant_percent
)
-- Creating resource class name mapping.
,names
AS
(
  SELECT 'smallrc' as resource_class, 1 as rc_id
  UNION ALL
    SELECT 'mediumrc', 2
  UNION ALL
    SELECT 'largerc', 3
  UNION ALL
    SELECT 'xlargerc', 4
  UNION ALL
    SELECT 'staticrc10', 5
  UNION ALL
    SELECT 'staticrc20', 6
  UNION ALL
    SELECT 'staticrc30', 7
  UNION ALL
    SELECT 'staticrc40', 8
  UNION ALL
    SELECT 'staticrc50', 9
  UNION ALL
    SELECT 'staticrc60', 10
  UNION ALL
    SELECT 'staticrc70', 11
  UNION ALL
    SELECT 'staticrc80', 12
)
,base AS
(   SELECT  schema_name
    ,       table_name
    ,       SUM(column_count)                   AS column_count
    ,       ISNULL(SUM(short_string_column_count),0)   AS short_string_column_count
    ,       ISNULL(SUM(long_string_column_count),0)    AS long_string_column_count
    FROM    (   SELECT  sm.name                                             AS schema_name
                ,       tb.name                                             AS table_name
                ,       COUNT(co.column_id)                                 AS column_count
                           ,       CASE    WHEN co.system_type_id IN (36,43,106,108,165,167,173,175,231,239)
                                AND  co.max_length <= 32
                                THEN COUNT(co.column_id)
                        END                                                 AS short_string_column_count
                ,       CASE    WHEN co.system_type_id IN (165,167,173,175,231,239)
                                AND  co.max_length > 32 and co.max_length <=8000
                                THEN COUNT(co.column_id)
                        END                                                 AS long_string_column_count
                FROM    sys.schemas AS sm
                JOIN    sys.tables  AS tb   on sm.[schema_id] = tb.[schema_id]
                JOIN    sys.columns AS co   ON tb.[object_id] = co.[object_id]
                           WHERE tb.name = @TABLE_NAME AND sm.name = @SCHEMA_NAME
                GROUP BY sm.name
                ,        tb.name
                ,        co.system_type_id
                ,        co.max_length            ) a
GROUP BY schema_name
,        table_name
)
, size AS
(
SELECT  schema_name
,       table_name
,       75497472                                            AS table_overhead

,       column_count*1048576*8                              AS column_size
,       short_string_column_count*1048576*32                       AS short_string_size,       (long_string_column_count*16777216) AS long_string_size
FROM    base
UNION
SELECT CASE WHEN COUNT(*) = 0 THEN 'EMPTY' END as schema_name
         ,CASE WHEN COUNT(*) = 0 THEN 'EMPTY' END as table_name
         ,CASE WHEN COUNT(*) = 0 THEN 0 END as table_overhead
         ,CASE WHEN COUNT(*) = 0 THEN 0 END as column_size
         ,CASE WHEN COUNT(*) = 0 THEN 0 END as short_string_size

,CASE WHEN COUNT(*) = 0 THEN 0 END as long_string_size
FROM   base
)
, load_multiplier as
(
SELECT  CASE
          WHEN FLOOR(8 * (CAST (CAST(REPLACE(REPLACE(@DWU,'DW',''),'c','') AS INT) AS FLOAT)/6000)) > 0
            AND CHARINDEX(@DWU,'c')=0
          THEN FLOOR(8 * (CAST (CAST(REPLACE(REPLACE(@DWU,'DW',''),'c','') AS INT) AS FLOAT)/6000))
          ELSE 1
        END AS multiplication_factor
)
       SELECT  r1.DWU
       , schema_name
       , table_name
       , rc.resource_class as closest_rc_in_increasing_order
       , max_queries_at_this_rc = CASE
             WHEN (r1.max_slots / r1.slots_used > r1.max_queries)
                  THEN r1.max_queries
             ELSE r1.max_slots / r1.slots_used
                  END
       , r1.max_slots as max_concurrency_slots
       , r1.slots_used as required_slots_for_the_rc
       , r1.tgt_mem_grant_MB  as rc_mem_grant_MB
       , CAST((table_overhead*1.0+column_size+short_string_size+long_string_size)*multiplication_factor/1048576    AS DECIMAL(18,2)) AS est_mem_grant_required_for_cci_operation_MB
       FROM    size
       , load_multiplier
       , #ref r1, names  rc
       WHERE r1.rc_id=rc.rc_id
                     AND CAST((table_overhead*1.0+column_size+short_string_size+long_string_size)*multiplication_factor/1048576    AS DECIMAL(18,2)) < r1.tgt_mem_grant_MB
       ORDER BY ABS(CAST((table_overhead*1.0+column_size+short_string_size+long_string_size)*multiplication_factor/1048576    AS DECIMAL(18,2)) - r1.tgt_mem_grant_MB)
GO

Próximos passosNext steps

Para obter mais informações sobre como gerenciar usuários de banco de dados e segurança, confira Proteger um banco de dados no SQL Data Warehouse.For more information about managing database users and security, see Secure a database in SQL Data Warehouse. Para obter mais informações sobre como classes de recursos maiores podem melhorar a qualidade do índice columnstore clusterizado, consulte Otimizações de memória para compressão de columnstore.For more information about how larger resource classes can improve clustered columnstore index quality, see Memory optimizations for columnstore compression.