(English version at the bottom)
Tive o prazer de bater um papo com Luiz Vitor Lima, formado em ciência da computação, consultor SQL Server e especialista em otimizar bancos de dados. Conversamos sobre suas experiências e técnicas de melhoria de desempenho de bancos de dados.
Trabalhando há mais de dez anos com Microsoft SQL Server (MS SQL), Luiz observou uma disrupção na indústria com o aumento de servidores na nuvem. Uma das vantagens ao hospedar um banco de dados na nuvem é a terceirização das operações de infraestrutura e manutenção. Nesse caso, quem fica responsável pela segurança e acesso ao local do servidor, pelo controle de temperatura com ar-condicionado, pela proteção contra quedas de energia, entre outros, é o fornecedor do serviço Database-as-a-Service (DBaaS). Além disso, existem vantagens relacionadas ao hardware, como CPU e memória, os quais podem ser escalados facilmente conforme as necessidades da aplicação por meio de máquinas virtuais. Dessa forma, o cliente pode focar suas energias nas ações que agregam diretamente valor ao seu negócio.
No entanto, ao migrar um banco de dados para nuvem, os profissionais devem estar atentos aos eventuais problemas de alta latência da aplicação. Dependendo da distância física entre os servidores rodando as aplicações e os usuários, o tempo das consultas aos bancos de dados pode aumentar significativamente, sobretudo em um país com grandes dimensões e infraestrutura em telecomunicações deficitária como o Brasil.
Luiz recomenda, se for viável para o cliente, utilizar um servidor dedicado unicamente para a aplicação do banco de dados. Dessa forma, o servidor SQL poderá utilizar todos os recursos disponíveis de processamento, como CPU e memória, e rodar de forma mais rápida. Além disso, Luiz ressaltou um ponto importante sobre a configuração do MS SQL em relação ao uso de núcleos da CPU por consulta (MAXDOP - Max Degree of Parallelism). A configuração padrão é paralelizar o processamento de uma consulta usando todos os núcleos da CPU disponíveis, por exemplo, todos os dezesseis núcleos. No entanto, se uma consulta travar, ou necessitar de um tempo maior para execução, um gargalo será criado no fluxo de processamento do sistema, pois todos os núcleos da CPU estarão ocupados nessa operação, prejudicando as próximas consultas. Luiz sugere, portanto, limitar os núcleos por consulta nas configurações, escolhendo, por exemplo, somente quatro núcleos da CPU.
Grande parte da otimização do desempenho de um banco de dados pode ser feita via software. Alguns exemplos simples são: evitar o uso do asterisco nas consultas, de modo a restringir a quantidade dos dados, usar UNIONs de consultas menores ao invés do operador OR no filtro WHERE de uma grande consulta, e aplicar uma técnica de filtragem com tabelas temporárias ao invés de criar uma consulta complexa. Essas dicas contribuem não somente para melhorar o desempenho do banco de dados, mas também para aprimorar a arquitetura do código escrito, que fica mais modular e limpo, e mais fácil de debugar e manter.
Ao utilizar a técnica de filtragem dos dados, restringindo o número de colunas e salvando o resultado intermediário de uma consulta em uma tabela temporária, para somente em um segundo momento buscar as demais informações necessárias, Luiz conseguiu reduzir significativamente o tempo de execução de uma consulta. Nesse exemplo, o tempo da operação baixou de trinta segundos para menos de um segundo.
-- ANTES
Table 'Worktable'. Scan count 0, logical reads 10972, physical reads 0, read-ahead reads 142
Table 'SA1700'. Scan count 4097, logical reads 209080
Table 'SCJ700'. Scan count 4103, logical reads 20550
Table 'SB1700'. Scan count 2, logical reads 619558
Table 'SCK700'. Scan count 1, logical reads 19909
SQL Server Execution Times:
CPU time = 29704 ms, elapsed time = 29787 ms.
-- DEPOIS
Table 'SCJ700'. Scan count 205, logical reads 1021
Table 'SB1700'. Scan count 257, logical reads 1451
Table 'SCK700'. Scan count 4, logical reads 797
Table '#TEMP_SA1700_00000013EF28'. Scan count 1, logical reads 1
SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 857 ms.
Como a famosa frase do estatístico William Deming resume, “o que não pode ser medido, não pode ser gerenciado”, é fundamental monitorar alguns indicadores para acompanhar o desempenho de um sistema. Para isso, no MS SQL, Luiz recomenda ativar os indicadores abaixo em uma sessão específica para monitorar o consumo de leituras e escrituras e os tempos das consultas, de modo a encontrar possíveis gargalos. Outra dica é usar a procedura “sp_whoisactive” para identificar as consultas em execução, com informações importantes tais como: login, hostname, cpu e logical reads. Uma funcionalidade do MS SQL prática para análise dos tempos e dos motivos de espera das consultas (WAITs) é a DMV (Dynamic Management Views) “sys.dm_os_wait_stats”.
> SET STATISTICS IO, TIME ON
> sp_whoisactive @get_plans = 1, @get_additional_info = 1
> SELECT * FROM sys.dm_os_wait_stats
Existem muito mais dicas e vias de otimização e alguns materiais para se aprofundar são os sites [1] e [2]. No entanto, a estratégia para a resolução começa com a identificação dos pontos de gargalo do sistema, que pode ser iniciada com as dicas e técnicas mostradas acima.
Recentemente Luiz realizou um projeto de melhoria de desempenho significativo. Antes de ser contatado, o cliente reclamava da demora de até três dias para a execução de uma determinada consulta. Após uma análise profunda e diversas melhorias, usando algumas das técnicas citadas anteriormente, Luiz otimizou o sistema, reduzindo o tempo de execução da consulta para apenas alguns minutos. Obviamente esse nível de otimização não acontece por acaso, mas havendo um conhecimento profundo das tecnologias além de anos de experiência acumulada na área.
Luiz faz parte da comunidade de MS SQL no Brasil e também tem canais ativos na internet focados em dicas sobre SQL. Destacam-se o seu blog e o recém-lançado canal no YouTube.
-----
I had the pleasure of having a chat with Luiz Vitor Lima, a computer science graduate, SQL Server consultant and database optimization expert. We talked about his experiences and techniques for improving database performance.
Working for over ten years with Microsoft SQL Server (MS SQL), Luiz has seen a disruption in the industry with the increase of servers in the cloud. One of the advantages of hosting a database in the cloud is outsourcing infrastructure operations and maintenance. In this case, the Database-as-a-Service (DBaaS) provider is responsible for security and access to the server location, for temperature control with air conditioning, for protection against power outages, among others. In addition, there are advantages related to hardware, such as CPU and memory, which can easily be scaled as needed by means of virtual machines. This way, the customer can focus his energies on actions that directly add value to his business.
However, when migrating a database to the cloud, professionals should be aware of the potential problems of high latency of the application. Depending on the physical distance between the servers running the applications and the users, database query times can increase significantly, especially in a country with large dimensions and poor telecommunications infrastructure such as Brazil.
Luiz recommends, if it is feasible for the customer, to use a dedicated server solely for the database application. This way, the SQL server can utilize all available processing resources, such as CPU and memory, and run faster. In addition, Luiz made an important point about the configuration of MS SQL with respect to the use of CPU cores per query (MAXDOP - Max Degree of Parallelism). The default setting is to parallelize the processing of a query using all available CPU cores, for example, all sixteen cores. However, if a query hangs, or requires a longer execution time, a bottleneck will be created in the system's processing flow, because all CPU cores will be busy in this operation, hindering the next queries. Luiz therefore suggests limiting the number of cores per query in the configurations, choosing, for example, only four CPU cores.
Much of the performance optimization of a database can be done via software. Some simple examples are: avoiding the use of the asterisk in queries to restrict the amount of data, using UNIONs of smaller queries instead of the OR operator in the WHERE filter of a large query, and applying a filtering technique with temporary tables instead of creating a complex query. These tips contribute not only to improving the performance of the database, but also to improving the architecture of the written code, which becomes more modular and cleaner, and easier to debug and maintain.
By using the technique of filtering the data, restricting the number of columns and saving the intermediate result of a query in a temporary table, Luiz managed to significantly reduce the execution time of a query. In this example, the operation time was reduced from thirty seconds to less than one second.
-- BEFORE
Table 'Worktable'. Scan count 0, logical reads 10972, physical reads 0, read-ahead reads 142
Table 'SA1700'. Scan count 4097, logical reads 209080
Table 'SCJ700'. Scan count 4103, logical reads 20550
Table 'SB1700'. Scan count 2, logical reads 619558
Table 'SCK700'. Scan count 1, logical reads 19909
SQL Server Execution Times:
CPU time = 29704 ms, elapsed time = 29787 ms.
-- AFTER
Table 'SCJ700'. Scan count 205, logical reads 1021
Table 'SB1700'. Scan count 257, logical reads 1451
Table 'SCK700'. Scan count 4, logical reads 797
Table '#TEMP_SA1700_00000013EF28'. Scan count 1, logical reads 1
SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 857 ms.
As the famous quote from statistician William Deming summarizes, “what cannot be measured, cannot be managed”, it is critical to monitor some indicators to track a system's performance. To this end, in MS SQL, Luiz recommends activating the indicators below in a specific session to monitor read and write consumption and query times in order to find possible bottlenecks. Another tip is to use the procedure “sp_whoisactive” to identify the running queries, with important information such as login, hostname, cpu and logical reads. A practical MS SQL feature for analyzing query wait times and reasons (WAITs) is the DMV (Dynamic Management Views) "sys.dm_os_wait_stats".
> SET STATISTICS IO, TIME ON
> sp_whoisactive @get_plans = 1, @get_additional_info = 1
> SELECT * FROM sys.dm_os_wait_stats
There are many more optimization tips and routes, and some materials to delve into are the sites [1] and [2]. However, the strategy for resolution begins with identifying the bottlenecks in the system, which can be started with the tips and techniques shown above.
Recently, Luiz undertook a significant performance improvement project. Before he was contacted, the customer was complaining about a query taking up to three days to execute. After a deep analysis and several improvements, using some of the techniques mentioned above, Luiz optimized the system, reducing the query execution time to just a few minutes. Obviously, this level of optimization does not happen by chance, but with a deep knowledge of the technologies and years of accumulated experience in the area.
Luiz is part of the MS SQL community in Brazil and also has active channels on the internet focused on SQL tips. Highlights include his blog and the recently launched YouTube channel.
Further readings:
[1] https://use-the-index-luke.com/
[2] https://ola.hallengren.com/ [3] https://dev.mysql.com/doc/refman/8.0/en/optimization.html
Videos:
No comments:
Post a Comment