Primeiro o Otimizador de Consultas faz o “Parsing<\/em><\/strong>“<\/em> do comando para validar se a sintaxe<\/strong> do comando est\u00e1 correta. Estando o c\u00f3digo correto, \u00e9 gerado o que chamamos de parser tree<\/em><\/strong>, que cont\u00e9m os passos l\u00f3gicos<\/strong> para a execu\u00e7\u00e3o da consulta SQL.<\/p>\n O segundo passo \u00e9 o que chamamos de Algebrizer<\/strong>, esta etapa \u00e9 que faz a normaliza\u00e7\u00e3o do comando que recebe do parser tree<\/em><\/strong>, verifica a exist\u00eancia dos objetos<\/strong> utilizados na consulta (tabelas, colunas etc), al\u00e9m de outras tarefas como validar se o usu\u00e1rio que est\u00e1 executando tem permiss\u00e3o de acesso, valida as constraints e etc… a sa\u00edda deste passo \u00e9 um metadado com as informa\u00e7\u00f5es.<\/p>\n O terceiro passo \u00e9 o que chamamos de Optimizer<\/strong>, que vai fazer a leitura do metadado recebidos da fase anterior e vai come\u00e7ar a analisar os operadores f\u00edsicos e l\u00f3gicos<\/strong> que ele vai utilizar, tamb\u00e9m vai analisar os hints<\/strong> que est\u00e3o na query e os operadores de JOIN. Ap\u00f3s isso, ele vai come\u00e7ar a gerar v\u00e1rios planos de execu\u00e7\u00e3o. Dentre os planos gerados ele vai escolher o que for classificado como bom o suficiente<\/strong> “good enough<\/strong>“. Para ele escolher esse plano ele leva em considera\u00e7\u00e3o o custo de cada opera\u00e7\u00e3o al\u00e9m do custo de recursos como IO e CPU.<\/p>\n Uma vez que o otimizador de consultas gera o plano de execu\u00e7\u00e3o, ele \u00e9 ent\u00e3o armazenado em uma \u00e1rea de mem\u00f3ria denominada plan cache<\/em><\/strong>, que \u00e9 uma \u00e1rea onde ficam armazenados todos os planos de execu\u00e7\u00e3o do SQL Server.<\/p>\n Conclu\u00eddo o terceiro passo, agora que o nosso plano de execu\u00e7\u00e3o est\u00e1 gerado o SQL Server sabe exatamente como chegar nos dados, ent\u00e3o ele \u00e9 colocado na fila de execu\u00e7\u00e3o que \u00e9 o passo Execution<\/strong> ou Exec Plan.<\/strong><\/p>\n 1) Plano de Execu\u00e7\u00e3o<\/strong><\/span><\/p>\n Tiago o que seria o plano de execu\u00e7\u00e3o?<\/strong><\/p>\n Apesar de quase 100% das pessoas que trabalham com SQL Server saber o que \u00e9 o plano de execu\u00e7\u00e3o e para que ele serve, se voc\u00ea est\u00e1 come\u00e7ando a trabalhar com banco de dados agora, vou dar uma resumida. O primeiro ponto \u00e9 que o Plano de Execu\u00e7\u00e3o n\u00e3o \u00e9 exclusivo do SQL Server, todos os SGBD que eu conhe\u00e7o tem planos de execu\u00e7\u00e3o (Oracle, MySQL, Postgree…), que nada mais \u00e9 que uma representa\u00e7\u00e3o gr\u00e1fica, textual ou em XML que mostra quais operadores e opera\u00e7\u00f5es que o otimizador de consultas fez para retornar os dados da sua query e tamb\u00e9m o custo de cada opera\u00e7\u00e3o realizada.<\/p>\n Com esse recurso voc\u00ea consegue ver as etapas de execu\u00e7\u00e3o da sua query e assim verificar o que pode ser melhorado.<\/p>\n Para gerar um plano de execu\u00e7\u00e3o de uma instru\u00e7\u00e3o voc\u00ea pode simplesmente utilizar o atalho “CTRL+M<\/em><\/strong>” ou ent\u00e3o clicar no \u00edcone:<\/p>\n O plano de execu\u00e7\u00e3o tamb\u00e9m pode nos dar informa\u00e7\u00e3o de sugest\u00e3o de \u00edndices do SQL Server.<\/p>\n Mas o que seria essas sugest\u00f5es de cria\u00e7\u00e3o de \u00edndices?<\/strong><\/p>\n Por exemplo, quando executamos uma consulta o otimizador de consultas utiliza as estat\u00edsticas e identifica que a query que voc\u00ea est\u00e1 executando teria um desempenho melhor se a tabela X tivesse um \u00edndice na coluna Y.<\/p>\n Como podemos observar na imagem acima, o otimizador de consultas est\u00e1 nos sugerindo um \u00edndice na tabela “Sales.Customer<\/em><\/strong>“, que vai gerar um impacto de “97%<\/strong>” na execu\u00e7\u00e3o da consulta.<\/p>\n Para fazer a leitura do plano de execu\u00e7\u00e3o as literaturas recomendam que devemos come\u00e7ar da esquerda para a direita e de cima para baixo, uma vez que facilita a nossa interpreta\u00e7\u00e3o. Por\u00e9m, o Otimizador de Consultas constr\u00f3i os planos de execu\u00e7\u00e3o da direita para a esquerda e de cima para baixo<\/strong>. Tamb\u00e9m precisamos aprender o que significa cada operador ou pelo menos os principais deles.<\/p>\n Para consultar os operadores podemos consultar o Books Online (BOL<\/a><\/u>).<\/p>\n Vamos entender melhor com um plano de execu\u00e7\u00e3o um pouco mais complexo. Vamos exibir o plano de execu\u00e7\u00e3o da query abaixo:<\/p>\n Para entender o plano vamos utilizar a recomenda\u00e7\u00e3o liter\u00e1ria que fica mais f\u00e1cil o entendimento.<\/p>\n 1 – Otimizador de consulta executou uma opera\u00e7\u00e3o de “Cluster Index Scan<\/em>”<\/p>\n 3 – Realizou outra opera\u00e7\u00e3o de “Cluster Index Scan<\/em>”<\/p>\n 4 – Fez uma agrega\u00e7\u00e3o dos dados utilizando o operador “Stream Aggregate<\/em>“, essa primeira agrega\u00e7\u00e3o \u00e9 referente a fun\u00e7\u00e3o MAX<\/strong> (SELECT MAX(OrderDate) FROM Sales.SalesOrderHeader<\/strong>)<\/p>\n Dica:<\/strong> Quando clicamos com o bot\u00e3o direito -> propriedades<\/em> do operador, temos as informa\u00e7\u00f5es do que o operador est\u00e1 fazendo.<\/p>\n 5 – Realizou outra opera\u00e7\u00e3o de “Cluster Index Scan<\/em>”<\/p>\n 6 – Fez o join dos dados atrav\u00e9s do operador “Nested Loops<\/em>”<\/p>\n 7 – Agora fez uma opera\u00e7\u00e3o de “Cluster Index Seek”<\/em><\/p>\n 8 – Realizou o join entre os resultados dos passos (4,5,6 e 7) com o operador “Nested Loops<\/em>”<\/p>\n 9 – Realizou a ordena\u00e7\u00e3o dos dados utilizando o operador de “Sort<\/em>”<\/p>\n 10 – Fez uma nova agrega\u00e7\u00e3o dos dados por causa do “Group By” – (<\/em>GROUP BY ProductID<\/strong>)<\/em><\/p>\n 2 – Agora com a segunda parte dos dados da query j\u00e1 retornados, o otimizador de consultas vai realizar o join utilizando o operador “Merge Join<\/em>“.<\/p>\n 11 – Vai realizar mais uma ordena\u00e7\u00e3o utilizando o operador “Sort” <\/em>ordenando os dados da CTE (ORDER BY LastDayQuantity DESC;<\/strong>)<\/p>\n 12 – Finalmente vai retornar os dados do Select.<\/p>\n Agora vamos ver como que o Otimizador de Consulta monta o plano de execu\u00e7\u00e3o, como dito anteriormente, ele monta o plano da direita para a esquerda.<\/p>\n Como explicado anteriormente, quando executamos uma query ela passa por algumas fases e na fase do Optimizer<\/strong> que ser\u00e1 gerado o plano de execu\u00e7\u00e3o. Com isso, a engine sabe exatamente o caminho, ordem e quais operadores ela vai utilizar para alcan\u00e7ar o objetivo. Por esse motivo, ele gerar os planos sempre da direita para esquerda.<\/p>\n 1 – Select<\/p>\n 2 – Sort<\/p>\n 3 – Merge Join<\/p>\n 4 – Cluster Index Scan<\/p>\n 5 – Stream Aggregate<\/p>\n 6 – Sort<\/p>\n 7 – Nested Loops<\/p>\n 8 – Nested Loops<\/p>\n 9 – Stream Aggregate<\/p>\n 10 – Cluster Index Scan<\/p>\n 11 – Cluster Index Scan<\/p>\n 12 – Cluster Index Seek<\/p>\n Para voc\u00ea identificar qual \u00e9 a ordem que o plano est\u00e1 sendo executado, voc\u00ea consegue essa informa\u00e7\u00e3o nas propriedades de cada operador, na propriedade Node ID lembrando que a contagem come\u00e7a no Node ID: 0.<\/p>\n Outra op\u00e7\u00e3o que nos ajuda a entender a ordem que o SQL Server est\u00e1 executando a nossa query \u00e9 habilitando a op\u00e7\u00e3o “set statistics profile on<\/strong>“.<\/p>\n O retorno dessas informa\u00e7\u00f5es nos ajuda a identificar a ordem dos operadores e tamb\u00e9m quais s\u00e3o os operadores f\u00edsicos e quais s\u00e3o os operadores l\u00f3gicos, al\u00e9m de outras informa\u00e7\u00f5es.<\/p>\n Como falamos no come\u00e7o do post, outro ponto muito importante a ser observado s\u00e3o as estat\u00edsticas da query, quantas opera\u00e7\u00f5es de leituras f\u00edsicas, l\u00f3gicas e consumo de CPU o SQL Server efetuou para executar a sua consulta.<\/p>\n Para obter essas informa\u00e7\u00f5es basta habilitar as estat\u00edsticas antes de executar o comando, “set statistics io,time on<\/em><\/strong>” .<\/em><\/p>\n Como podemos ver nas informa\u00e7\u00f5es estat\u00edsticas, a opera\u00e7\u00e3o mais custosa para a execu\u00e7\u00e3o da consulta foi a leitura na tabela “SalesOrderHeader<\/em><\/strong>” e a nossa consulta teve um custo de 9 ms<\/strong>.<\/p>\n Apenas uma observa\u00e7\u00e3o para quem n\u00e3o conhece a opera\u00e7\u00e3o de logical reads<\/strong>, pois n\u00e3o \u00e9 uma leitura dos dados no disco e sim na mem\u00f3ria. Quando existe leitura em disco o valor que ser\u00e1 mostrado \u00e9 a o physical reads <\/strong>em alguns casos quando os dados ainda n\u00e3o est\u00e3o em mem\u00f3ria o SQL pode fazer leitura f\u00edsica, mas na pr\u00f3xima execu\u00e7\u00e3o da consulta os valores estejam zerado, isso vai depender de como estiver o seu PLE (Page Life Expectancy – <\/strong>Expectativa de vida em segundos de uma p\u00e1gina na mem\u00f3ria do SQL Server, bom para monitorar se voc\u00ea est\u00e1 mantendo dados em cache por muito tempo, evitando acessos a disco<\/em>).<\/strong><\/p>\n Existe uma ferramenta que utilizo bastante que \u00e9 a SentryOne Plan Explorer<\/a><\/u>. Ela \u00e9 FREE<\/strong> e nos ajuda bastante quando vamos realizar um tuning.<\/p>\n Quando executamos uma query nessa ferramenta, ela retorna os planos de execu\u00e7\u00e3o, estat\u00edsticas, qual opera\u00e7\u00e3o foi mais custosa para o SQL Serve executar… Costumo dizer que \u00e9 uma ferramenta fant\u00e1stica, nos prints abaixo vamos poder ver as informa\u00e7\u00f5es que ela nos retorna.<\/p>\n Quando vamos utilizar o plan explorer<\/strong> devemos clicar em “New Plan Explorer Session<\/em>“<\/strong>.<\/p>\n Depois de escrever a sua query, voc\u00ea dever\u00e1 clicar em “Get Actual Plan”<\/em><\/strong> e preencher as informa\u00e7\u00f5es de acesso ao banco como inst\u00e2ncia, base de dados e login\/senha.<\/p>\n Como podemos observar, a nossa primeira execu\u00e7\u00e3o da query foi com o tempo de 24 ms<\/strong>, tamb\u00e9m podemos ver as informa\u00e7\u00f5es de “Table I\/O”<\/em><\/strong>, <\/em>que s\u00e3o as informa\u00e7\u00f5es de I\/O que a nossa consulta executou. Essa informa\u00e7\u00e3o \u00e9 a mesma que temos quando executamos o “set statistics io”, <\/em>no Management Studio.<\/p>\n Na aba “Query Columns”<\/em><\/strong> podemos<\/em> ter informa\u00e7\u00f5es sobre quais colunas e tabelas que a nossa query acessou e qual o custo de cada acesso.<\/p>\n Na aba “Plan Diagram”<\/em><\/strong>, <\/em>n\u00f3s temos o mesmo plano de execu\u00e7\u00e3o que conseguimos quando executamos a consulta no Management Studio. Por\u00e9m no Plan Explorer ele nos mostra quais foram as tops opera\u00e7\u00f5es.<\/p>\n Na aba “Join Diagram”<\/em><\/strong>, <\/em>n\u00f3s temos um diagrama dos relacionamentos das tabelas da query.<\/p>\n Semelhante ao “set statistics profile<\/em>” no Management Studio, a aba “Plan Tree”<\/em><\/strong> nos informa a ordem do steps que o SQL Server fez para executar a nossa query.<\/p>\n A aba “Top Operations”<\/em><\/strong>, <\/em>retorna uma tabela com os custos de cada opera\u00e7\u00e3o do plano de execu\u00e7\u00e3o bem como as estimativas, se o operador utilizou paralelismo para executar entre outras.<\/p>\n Por fim, a aba “Index Analysis<\/em>“<\/strong> faz uma an\u00e1lise de cada \u00edndice que foi utilizado na nossa consulta, inclusive mostrando o ganho que ter\u00edamos ao utilizar o \u00edndice sugerido pelo SQL Server. Tamb\u00e9m podemos ver que algumas estat\u00edsticas est\u00e3o desatualizadas.<\/p>\n O legal da ferramenta \u00e9 que podemos atualizar as estat\u00edsticas e criar o \u00edndice direto por ela sem precisar de conectar no Management Studio.<\/p>\n Para atualizar as estat\u00edsticas no pr\u00f3prio SentryOne, basta clicar no gr\u00e1fico que vai abrir uma janela para voc\u00ea configurar as op\u00e7\u00f5es de atualiza\u00e7\u00e3o de estat\u00edsticas, lembrando que se voc\u00ea n\u00e3o tiver permiss\u00e3o<\/strong> para atualizar estat\u00edsticas vai dar erro.<\/p>\n D\u00e1 mesma forma, podemos criar um \u00edndice sugerido pelo SQL Server. Para isso, basta clicar no “<s>”<\/em><\/strong> que o Plan Explorer vai mostrar o script de cria\u00e7\u00e3o do \u00edndice e caso voc\u00ea ache necess\u00e1rio acrescentar alguma coluna ou alguma op\u00e7\u00e3o de cria\u00e7\u00e3o basta editar o script.<\/p>\n Aten\u00e7\u00e3o:<\/strong><\/span><\/p>\n “Criar \u00edndices e atualizar estatisticas em ambiente de produ\u00e7\u00e3o durante o hor\u00e1rio comercial pode impactar a performance do servidor e gerar locks.”<\/strong><\/em><\/span><\/p>\n![\"\"](\"https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2018\/11\/query_processing_schema.png?ssl=1\")
<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\nWITH\nLastDayOrderIDs AS\n(\nSELECT SalesOrderID FROM Sales.SalesOrderHeader\nWHERE OrderDate = (SELECT MAX(OrderDate) FROM Sales.SalesOrderHeader)\n),\nLastDayProductQuantities AS\n(\nSELECT ProductID, SUM(OrderQty) AS LastDayQuantity\nFROM Sales.SalesOrderDetail\nJOIN LastDayOrderIDs\nON Sales.SalesOrderDetail.SalesOrderID = LastDayOrderIDs.SalesOrderID\nGROUP BY ProductID\n),\n\nLastDayProductDetails AS\n(\nSELECT\nProduct.ProductID,\nProduct.Name,\nLastDayQuantity\nFROM Production.Product\nJOIN LastDayProductQuantities\nON Product.ProductID = LastDayProductQuantities.ProductID\n)\n\nSELECT * FROM LastDayProductDetails\nORDER BY LastDayQuantity DESC;\n\n<\/pre>\n
<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\nSET STATISTICS PROFILE ON<\/strong>\n\nWITH LastDayOrderIDs\nAS (SELECT SalesOrderID\n FROM Sales.SalesOrderHeader\n WHERE OrderDate =\n (\n SELECT MAX(OrderDate) FROM Sales.SalesOrderHeader\n )),\n LastDayProductQuantities\nAS (SELECT ProductID,\n SUM(OrderQty) AS LastDayQuantity\n FROM Sales.SalesOrderDetail\n JOIN LastDayOrderIDs\n ON Sales.SalesOrderDetail.SalesOrderID = LastDayOrderIDs.SalesOrderID\n GROUP BY ProductID),\n LastDayProductDetails\nAS (SELECT Product.ProductID,\n Product.Name,\n LastDayQuantity\n FROM Production.Product\n JOIN LastDayProductQuantities\n ON Product.ProductID = LastDayProductQuantities.ProductID)\nSELECT *\nFROM LastDayProductDetails\nORDER BY LastDayQuantity DESC<\/pre>\n
<\/a><\/p>\nSET STATISTICS IO,time ON<\/strong>\n\nWITH LastDayOrderIDs\nAS (SELECT SalesOrderID\n FROM Sales.SalesOrderHeader\n WHERE OrderDate =\n (\n SELECT MAX(OrderDate) FROM Sales.SalesOrderHeader\n )),\n LastDayProductQuantities\nAS (SELECT ProductID,\n SUM(OrderQty) AS LastDayQuantity\n FROM Sales.SalesOrderDetail\n JOIN LastDayOrderIDs\n ON Sales.SalesOrderDetail.SalesOrderID = LastDayOrderIDs.SalesOrderID\n GROUP BY ProductID),\n LastDayProductDetails\nAS (SELECT Product.ProductID,\n Product.Name,\n LastDayQuantity\n FROM Production.Product\n JOIN LastDayProductQuantities\n ON Product.ProductID = LastDayProductQuantities.ProductID)\nSELECT *\nFROM LastDayProductDetails\nORDER BY LastDayQuantity DESC<\/pre>\n
<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n