Índices SQL e a sua importância na performance do Microsoft Dynamics CRM
Como em toda a base de dados Microsoft SQL Server, também a do Microsoft Dynamics CRM tem a sua boa performance associada ao nível de optimização da mesma, nomeadamente no que respeita a índices apropriados.
Aquando da criação de uma organização no Microsoft Dynamics CRM, vários índices são também criados nas várias tabelas da aplicação. Estes não deverão, de forma alguma ser modificados o removidos, pois além de serem utilizados pelos vários componentes/serviços da aplicação, poderão sofrer actualizações aquando do lançamento de um novo Update Rollup. A sua modificação ou remoção é, por esta razão considerada não suportada.
Naturalmente, e uma vez que o Microsoft Dynamics CRM é um produto que permite, através da sua interface de customização, a criação de novas entidades e campos, estes vão ser reflectidos em novas tabelas e campos na base de dados da Organização. Esta criação e posterior utilização pela aplicação levará, também naturalmente a um rendimento não-óptimo destas mesmas, uma vez que não existirão índices apropriados que cubram a execução das consultas (queries) a estas tabelas e campos.
Por esta razão e, de uma forma coerente com qualquer outra base de dados de Microsoft SQL Server, é aceitável e até recomendável, a criação de índices apropriados nas tabelas que o necessitem.
Uma das formas mais comuns de identificação de índices em falta, mas que exige algum esforço e elevado conhecimento técnico, consiste em identificar a consulta (query), executando-a directamente no Microsoft SQL Server Management Studio, com a opção de “Display Actual Execution Plan”, quer permitirá, de uma forma detalhada e gráfica identificar os pontos de melhora da execução da mesma consulta (query).
Exemplo 1 :
A existência de “Scan”, quer de “Index”, quer de “Table”, indiciam a falta de índice que cubra as necessidades de execução da consulta nessa tabela. Uma vez criado de forma adequada, e, seguindo o mesmo exercício anterior, a mesma consulta (query) deverá apresentar resultados de execução do tipo “Seek” ao invés de “Scan”.
O Microsoft SQL Server regista, desde que arranca o seu serviço, toda a sua activade e estatísticas relacionas. Esta informação poderá ser consultada através das DMVs (Dynamic Management Views). Ter em conta que as mesmas serão limpas e começarão sempre vazias aquando do reinicio do mesmo serviço/instância.
Entre outras formas, a consulta esquematizada destas consultas, permite de uma forma mais sumária obter uma boa visão da lista total de índices considerados “em falta”, de acordo com as estatísticas de execução do Microsoft SQL Server.
Aplicando o seguinte bloco de código T-SQL na base de dados da organização do Microsoft Dynamics CRM, é possível obter uma listagem de índices potenciais para criação, uma vez que, de acordo com o Microsoft SQL Server, teriam sido utilizados e melhorado a performance de execução da mesma consulta.
====
select o.name as Table_Name
, REPLACE(d.equality_columns,', ','¦') as Equality_Columns
, REPLACE(d.inequality_columns,', ','¦') as Inequality_Columns
, REPLACE(d.included_columns,', ','¦') as Included_Columns
, s.avg_user_impact
, s.user_seeks
from sys.dm_db_missing_index_group_stats s
,sys.dm_db_missing_index_groups g
,sys.dm_db_missing_index_details d
,sys.objects o
where s.group_handle = g.index_group_handle
and d.index_handle = g.index_handle
and o.object_id = d.object_id
and database_id = db_id()
and avg_user_impact > 80
order by s.user_seeks desc
go
====
User seeks indicará o número de vezes que o mesmo teria sido, potencialmente, utilizado se existisse.
Avg_user_impact representa a redução de custo na execução e respectivo impacto do mesmo índice na experiência do utilizador.
Quando mais altos forem os valores das colunas anteriores, maior será a necessidade que o Microsoft Dynamics CRM terá de optimização da mesma tabela.
No entanto, será de ter em conta que nem todos os índices constantes nesta lista deverão ser criados! De uma forma racional, deverá ser feita a análise, apenas focando a primeira atenção nos de topo (com maior número de user_seeks), realizando a sua criação e posterior teste de performance através da utilização da aplicação ou do método anterior descrito (caso a consulta tenha sido identificada).
Tal como outras bases de dados aplicacionais que se baseiam em Microsoft SQL Server, a existência de índices deve ser ponderada e avaliada para garantir que nunca se caí na situação nem de “índices em falta” nem de “índices a mais”.
Para ajudar a fazer esta análise ponderada sobre a base de dados da organização do Microsoft Dynamics CRM, existem alguns relatórios do Microsoft SQL Server que ajudam. São eles o “Disk Usage” e o “Disk Usage by Top Table”. Os mesmos poderão ser executados clicando com o botão direito na base de dados e escolhendo a opção “Reports”.
Exemplo de relatório “Disk Usage”:
Exemplo de relatório “Disk Usage by Top Table”:
Em suma, a criação de índices numa base de dados do Microsoft Dynamics CRM é aceitável e até recomendada de acordo com as melhores práticas de optimização de tabelas do Microsoft SQL Server. A modificação ou remoção dos índices fornecidos pela aplicação aquando da sua instalação (criação da organização) não é considerada suportada.
Melhores Cumprimentos,
Gonçalo Antunes
Referências:
-- https://msdn.microsoft.com/en-us/library/ms345421(SQL.90).aspx