Por: Ahmad Yaseen | Atualizado em: 2018-03-30 | Comentários (3) | Relacionados: Mais > Otimização de Consulta
Livre MSSQLTips Webinar: a Indexação de Desempenho: Consulta Estratégias para Maximizar Seus Índices do SQL Server
Algumas coisas que influenciam a velocidade e o desempenho das instruções SQL no SQL Server como máximo índices. Esta sessão irá ajudá-lo a compreender os fundamentos do desempenho da consulta em relação aos índices na sua base de dados de produção. Vamos ensinar-lhe como determinar se está a usar boas estratégias de indexação e o impacto directo que a sua estratégia de indexação tem nas consultas.
Problema
eu sei que o SQL Server pode executar consultas em paralelo, mas como eu posso dizer que um queryuses um plano de execução em paralelo e como posso obter informações sobre o número ofCPUs usada quando uma consulta é executada em paralelo? Veja esta dica para aprender como.
Solução
O paralelismo conceito vem de dividir uma grande tarefa em tarefas menores, whereeach pequena tarefa é atribuída a uma pessoa específica, ou processador, no caso de uma SQLServer consulta, para fazer parte da tarefa principal. Por último, os resultados parciais de cada pequena tarefa serão combinados num resultado final. Você pode imaginar o quanto mais rápido isso pode ser feito e o ganho de desempenho que você canget de executar várias tarefas em série ao mesmo tempo em paralelo, em comparação queimando uma grande tarefa serial!
Antes de passar pela ponta, é benéfico compreender uma série de aspectos técnicos relacionados com o conceito de paralelismo no servidor SQL:
- o Scheduler é o processador físico ou lógico responsável por agendar a execução dos threads do servidor SQL.
- o trabalhador é o fio que está ligado a um escalonador para formar uma tarefa específica.
- o grau de paralelismo é o número de Trabalhadores, ou o número de processadores, que são designados para o plano paralelo de realização da tarefa do trabalhador.
- o grau máximo de paralelismo (MAXDOP) é uma opção de nível de servidor,banco de dados ou consulta que é usada para limitar o número de processores que o plano paralelo pode usar. O valor padrão do MAXDOP é 0, no qual o motor do servidor theSQL pode usar todos os processadores disponíveis, até 64, na consulta parallelexecution. Configurar a opção MAXDOP para 1 irá impedir o uso de mais de um processorin executando a consulta, o que significa que o motor do servidor SQL irá usar um plano serial para executar a consulta. A opção MAXDOP pode ter valor até 32767, onde o motor do servidor SQL utilizará todos os processadores de servidor disponíveis na execução do plano paralelo se o valor MAXDOP exceder o número de processadores disponíveis no servidor. Se o servidor SQL estiver instalado em um único processador, o valor do MAXDOP será ignorado.
- a tarefa é um pequeno trabalho que é atribuído a um trabalhador específico.
- o contexto de execução é o limite no qual cada máscara única é executada dentro.
- o fornecedor de páginas paralelas é uma parte do Motor SQL ServerStorage que distribui os conjuntos de dados solicitados pela consulta nos trabalhadores participantes.
- a troca é o componente que ligará os diferentes contextos de execução envolvidos no plano paralelo de consulta, para obter o resultado final.
a decisão de usar um plano paralelo para executar a consulta ou não depende de fatores múltiplos. Por exemplo, o servidor SQL deve ser instalado em um multi-processorserver, o número solicitado de threads deve estar disponível para ser satisfeito, o grau máximo de opção de paralelismo não é definido para 1 e o custo da consulta excede o limiar de custo previamente configurado para o Parallelismvalue.
o objetivo desta dica, é recuperar a informação da consulta paralela threads do plano de execução real da consulta. Para forçar o Motor do SQL Server toexecute a consulta enviada usando um plano paralelo, vamos definir o Custo Thresholdfor Paralelismo valor 0, certifique-se de que em todos os casos a consulta de custo será exceedthe Limite de Custo para o Paralelismo de valor e vamos manter o Grau Máximo ofParallelism opção com o seu padrão de valor 0, para permitir que o Mecanismo do SQL Server para useall processadores disponíveis durante a execução de nossa consulta, que são 4 processadores na minha máquina utilizada para esta demonstração.
o limiar de custo para o valor do paralelismo pode ser definido usando o SQL Server ManagementStudio, ligando-se à instância do servidor SQL, Carregando com o botão direito no nome do instancename e escolhendo a opção Propriedades. A partir da página avançada da janela do servidor, desloque-se para a secção do paralelismo, de onde poderá sobrepor o valor por omissão do limiar de custo da opção do paralelismo, que é 5, ou o grau máximo de paralelismo, se necessário, como é mostrado na imagem abaixo:
pode também sobrepor o valor por omissão do limiar de custo para o paralelismo usando sp_configure. Para ser capaz de alterar o Limite de Custos para o Paralelismo de valor,você deve usar sp_configure para activar a Opção Mostrar Avançado em primeiro lugar, como mostrado no script abaixo:
EXEC sp_configure 'show advanced options', 1;RECONFIGURE;GOEXEC sp_configure 'cost threshold for parallelism', 0;RECONFIGURE;GOEXEC sp_configure 'show advanced options', 0;RECONFIGURE;
Demonstração do Plano de Execução Paralela
O servidor agora está configurado para a demonstração. Vamos criar uma nova tabela usando a instrução tabela de recriar T-SQL como mostrado abaixo:
USE MSSQLTipsDemoGOCREATE TABLE ParallelDemo( ID INT IDENTITY (1,1) PRIMARY KEY, FirstName NVARCHAR (200), LastName NVARCHAR (200), PhoneNumber VARCHAR(50), BirthDate DATETIME, Address NVARCHAR(MAX) )
uma Vez criada a tabela, vamos preencher a tabela com 100 mil registros usando theINSERT EM declarações abaixo:
INSERT INTO ParallelDemo VALUES ('John','Horold','+96255889955','1987-01-08','Jordan - Amman - Mecca Street - Building 77')GO 50000 INSERT INTO ParallelDemo VALUES ('Michel','Anglo','+96255776655','1985-06-02','Jordan - Amman - Mecca Street - Building 74')GO 50000
Agora queremos ativar a opção “Incluir Plano de Execução Real” e thenrun abaixo MARQUE consulta:
SELECT , , , , , FROM .. WHERE Address LIKE '%AMM%' ORDER BY BirthDate desc
Depois de concluído este executado, podemos verificar o plano de execução gerado fromexecuting a consulta, você vai ver que o Mecanismo do SQL Server decide usar um parallelplan para executar a consulta, devido ao fato de que o custo da consulta excede theCost Limite para o Paralelismo de valor.
podemos ver abaixo que o plano de execução é um plano paralelo, por causa do Parallelismoperador e também os círculos amarelos com duas setas sob cada operador que executou com paralelismo.
The execution plan is not just a graph, it contains valuable information thatdescribes the execution process of the submitted query. Para ver esta informação,carregue com o botão direito no nó seleccionado no plano de execução e escolha a opção Propriedades. Na janela Propriedades, você pode ferver o número de processadores que são utilizados para processar a consulta enviada a partir de theDegree de Paralelismo atributo, sob Diversos sectionas mostrado abaixo:
a Partir desta mesma janela, informações sobre a consulta paralela threads são alsodisplayed sob a ThreadStat seção. Nessa seção, você cancheck a consulta paralela Ramos atributo, que mostra o número de vezes que a execução simultânea caminhos dentro do plano de execução de consulta, o Nó de IDENTIFICAÇÃO, o número de threads paralelas reservado para o especificado NUMAnode sob a ReservedThreads atributo e o número de threadsused ao executar a consulta, sob a UsedThreads atributo.
da janela de propriedades, Você pode derivar que o motor do servidor SQL usou 4 processadores para executar a consulta. Além disso, o plano de execução da consulta tem um executionpath, no qual 4 threads são reservados e usados para executar a consulta submetida usuma planta paralela.
a mesma informação também pode ser derivada do plano de execução XML, olhando para a secção ThreadStat, como mostrado abaixo:
Você também pode mergulhar mais fundo para verificar os recursos de I / O e CPU consumidos por eachthread. Carregue com o botão direito no nó de pesquisa de índice agrupado no plano de execução e escolha as propriedades. A partir da janela de propriedades mostrada, você pode expandir o nó de estatísticas de E/S real para verificar o número de leituras lógicas e físicas realizadas por cada thread ao executar a consulta. Além disso, você também pode verificar o número de linhas que são recuperadas de cada thread, o tempo de CPU fantasiado por cada thread e, finalmente, o tempo de execução que é decorrido por cada thread para realizar a sua tarefa, como mostrado abaixo:
Os resultados corresponder-se com o nó janela de Propriedades, onde há are4 threads de trabalho que participaram na consulta tarefas de execução e um coordinatorthread que coordenadas entre os threads de trabalho.
tome em consideração que a informação anterior só pode ser obtida a partir do plano de execução real que é gerado após a consulta. Se você tentar procurar por essa informação a partir do Plano de Execução Estimado, sem executar a consulta, nenhuma informação será exibida como a consulta precisa para executar, a fim de especificar o número de processorsthat será atribuído a consulta de processadores disponíveis durante o queryexecution como mostrado a seguir para o Plano de Execução Estimado.
Fazer Mais do SQL Server Paralelismo Informações
Enquanto a consulta está sendo executado, você pode simplesmente executar o comando sp_who2 com o querysession ID para obter informações sobre as threads que são usados para executar o submittedquery durante a execução da consulta, como mostrado abaixo:
No plano de execução, cada operador em que o plano tinha um número atribuído a itand um programador que é utilizado para executá-lo. Esta informação pode ser recuperada usando o sistema.dm_os_tasks sistema de exibição de catálogo e juntar-se a ela com sys.dm_os_workersand sys.dm_exec_query_profiles sistema de catálogo de pontos de vista, como no script T-SQL abaixo:
SELECT OSTSK.scheduler_id, qp.node_id, qp.physical_operator_nameFROM sys.dm_os_tasks OSTSKLEFT JOIN sys.dm_os_workers OSWRK on OSTSK.worker_address=OSWRK.worker_addressLEFT JOIN sys.dm_exec_query_profiles qp on OSWRK.task_address=qp.task_addressWHERE OSTSK.session_id=58ORDER BY scheduler_id, node_id;
Executar o script anterior ao executar a instrução SELECT com a ActualExecution Plano de habilitado, o resultado nos mostra que as três operadoras de planos de: Ordenar,Filtrar e Verificação de Índice de Cluster com o ID de cada operador mostrado ao lado. Isto é executado quatro vezes, usando os quatro schedulers. O Paralelismo operatorwith ID igual a 0 será executada uma vez por quarto programador para se conectar allexecution contextos juntos na última etapa, como mostrado abaixo:
Abaixo eu adicionei o Nó de valores que correspondem com a consulta acima resultswith o plano de execução para ajudar a ilustrar ainda mais.
Next Steps
- Check out the following resources:
- Specifying Max grau of Parallelism in SQL Server for a Query.
- qual a configuração do MAXDOP a utilizar para o servidor SQL.
- como forçar um plano de execução paralelo no servidor SQL 2016.
- SQL Server 2016 DBCC CHECKDB com MAXDOP
Atualizada: 2018-03-30
Sobre o autor
Ahmad Yaseen é um DBA SQL Server com um diploma de bacharel em engenharia da computação, bem como .LÍQUIDO experiência de desenvolvimento.ver todas as minhas dicas
recursos relacionados
- mais dicas de desenvolvimento de bases de dados…
