Tom V's questions

No Dynamics AX existe um mecanismo de cache onde as tabelas podem ser configuradas para serem carregadas na memória e armazenadas em cache. Esse cache é limitado a uma certa quantidade de KB para evitar problemas de memória. A configuração de que estou falando é chamada entiretablecachee carrega toda a tabela na memória assim que um único registro é solicitado.

Até recentemente contamos com alguns scripts para verificar o tamanho das tabelas que possuem essa configuração para ver se o tamanho da tabela está acima desse limite.

Agora, no entanto, a compactação entra em ação e coisas como sp_spaceused ou sys.allocation_units parecem relatar o espaço realmente usado pelos dados compactados.

Obviamente, o servidor de aplicativos está trabalhando com dados não compactados, portanto, o tamanho dos dados no disco no SQL Server é irrelevante. Eu preciso do tamanho real que os dados descompactados terão.

Conheço sp_estimate_data_compression_savings , mas como o nome diz, isso é apenas uma estimativa.
Eu preferiria ter o tamanho o mais correto possível.

A única maneira que eu conseguia pensar era algum SQL dinâmico complicado criando tabelas não compactadas com a mesma estrutura das tabelas compactadas, inserindo os dados compactados nessa tabela sombra e, em seguida, verificando o tamanho dessa tabela sombra.
Desnecessário dizer que isso é um pouco tedioso e demora um pouco para ser executado em um banco de dados de várias centenas de GB.

O Powershell pode ser uma opção, mas eu não gostaria de iterar em todas as tabelas para executar um select *nelas para verificar o tamanho no script, pois isso apenas inundaria o cache e provavelmente levaria muito tempo também.

Em suma, preciso de uma maneira de obter o tamanho de cada tabela, pois ela será descompactada e com fragmentação fora da equação conforme apresentada ao aplicativo, se for possível. Estou aberto a diferentes abordagens, T-SQL é o preferido, mas não me oponho ao Powershell ou outras abordagens criativas.

Suponha que o buffer no aplicativo seja o tamanho dos dados. Um bigint é sempre do tamanho de um bigint e um tipo de dados de caractere é de 2 bytes por caractere (unicode). Os dados BLOB também assumem o tamanho dos dados, um enum é basicamente um int e os dados numéricos são numéricos (38,12), datetime é o tamanho de um datetime. Além disso, não há NULLvalores, eles são armazenados como uma string vazia 1900-01-01ou zero.

Não há documentação sobre como isso é implementado, mas as suposições são baseadas em alguns testes e nos scripts usados ​​pelos PFEs e pela equipe de suporte (que também ignoram a compactação aparentemente, já que a verificação é construída no aplicativo e o aplicativo não pode dizer se os dados subjacentes estiverem compactados) que também verificam os tamanhos das tabelas. Este link, por exemplo, afirma:

Evite usar caches de tabela inteira para tabelas grandes (no AX 2009 com mais de 128 KB ou 16 páginas, no AX 2012 com a configuração do aplicativo 'tamanho do cache de tabela inteira' [padrão: 32 KB ou 4 páginas]) – mude para o cache de registro.

Estou tendo um problema de desempenho com uma consulta que não consigo entender.

Tirei a consulta de uma definição de cursor.

Esta consulta leva segundos para ser executada

SELECT A.JOBTYPE
FROM PRODROUTEJOB A
WHERE ((A.DATAAREAID=N'IW')
AND ((A.CALCTIMEHOURS<>0)
AND (A.JOBTYPE<>3)))
AND EXISTS (SELECT 'X'
FROM PRODROUTE B
WHERE ((B.DATAAREAID=N'IW')
AND (((((B.PRODID=A.PRODID)
AND ((B.PROPERTYID=N'PR1526157') OR (B.PRODID=N'PR1526157')))
AND (B.OPRNUM=A.OPRNUM))
AND (B.OPRPRIORITY=A.OPRPRIORITY))
AND (B.OPRID=N'GRIJZEN')))
AND NOT EXISTS (SELECT 'X'
FROM ADUSHOPFLOORROUTE C
WHERE ((C.DATAAREAID=N'IW')
AND ((((((C.WRKCTRID=A.WRKCTRID)
AND (C.PRODID=B.PRODID))
AND (C.OPRID=B.OPRID))
AND (C.JOBTYPE=A.JOBTYPE))
AND (C.FROMDATE>{TS '1900-01-01 00:00:00.000'}))
AND ((C.TODATE={TS '1900-01-01 00:00:00.000'}))))))
GROUP BY A.JOBTYPE
ORDER BY A.JOBTYPE

O plano de execução real se parece com isso.

Percebendo que a configuração de todo o servidor foi definida como MaxDOP 1, tentei brincar com as configurações de maxdop.

Adicionar OPTION (MAXDOP 0)à consulta ou alterar as configurações do servidor resulta em um desempenho muito melhor e neste plano de consulta.

No entanto, o aplicativo em questão (Dynamics AX) não executa consultas como esta, ele usa cursores.

O código real capturado é este.

declare @p1 int
set @p1=189527589
declare @p3 int
set @p3=16
declare @p4 int
set @p4=1
declare @p5 int
set @p5=2
exec sp_cursoropen @p1 output,N'SELECT A.JOBTYPE FROM PRODROUTEJOB A WHERE ((A.DATAAREAID=N''IW'') AND ((A.CALCTIMEHOURS<>0) AND (A.JOBTYPE<>3))) AND EXISTS (SELECT ''X'' FROM PRODROUTE B WHERE ((B.DATAAREAID=N''IW'') AND (((((B.PRODID=A.PRODID) AND ((B.PROPERTYID=N''PR1526157'') OR (B.PRODID=N''PR1526157''))) AND (B.OPRNUM=A.OPRNUM)) AND (B.OPRPRIORITY=A.OPRPRIORITY)) AND (B.OPRID=N''GRIJZEN''))) AND NOT EXISTS (SELECT ''X'' FROM ADUSHOPFLOORROUTE C WHERE ((C.DATAAREAID=N''IW'') AND ((((((C.WRKCTRID=A.WRKCTRID) AND (C.PRODID=B.PRODID)) AND (C.OPRID=B.OPRID)) AND (C.JOBTYPE=A.JOBTYPE)) AND (C.FROMDATE>{TS ''1900-01-01 00:00:00.000''})) AND ((C.TODATE={TS ''1900-01-01 00:00:00.000''})))))) GROUP BY A.JOBTYPE ORDER BY A.JOBTYPE ',@p3 output,@p4 output,@p5 output
select @p1, @p3, @p4, @p5

resultando neste plano de execução (e, infelizmente, os mesmos tempos de execução de vários segundos).

Eu tentei várias coisas, como descartar planos em cache, adicionar opções na consulta dentro da definição do cursor, ... Mas nenhum deles parece me dar um plano paralelo.

Também pesquisei bastante no google procurando por limitações de paralelismo de cursores, mas não consigo encontrar nenhuma limitação.

Estou perdendo algo óbvio aqui?

A compilação real do SQL é SQL Server 2008 (SP1) - 10.0.2573.0 (X64)que eu percebo não ser suportada, mas não posso atualizar esta instância como considero adequado. Eu precisaria transferir o banco de dados para outro servidor e isso significaria obter um backup descompactado bastante grande em uma WAN lenta.

O sinalizador de rastreamento 4199 não faz diferença, nem OPTION (RECOMPILE).

As propriedades do cursor são:

API | Fast_Forward | Read Only | Global (0)

Estou trabalhando com um aplicativo de 3 camadas, Microsoft Dynamics AX, onde a camada intermediária mantém conexões com um SQL Server. Vários clientes se conectam a esse servidor de camada intermediária.

O servidor de camada intermediária normalmente tem várias conexões abertas com o SQL Server, então tenho certeza de que elas estão sendo agrupadas, no entanto, não há documentação disponível sobre como isso é implementado.

Normalmente não podemos relacionar SPIDs a usuários ou aplicações clientes, mas existe uma opção onde podemos definir uma chave de registro (específica do Microsoft Dynamics AX) que disponibiliza esta informação no context_infocampo sys.dm_exec_sessions.

Novamente, não há documentação sobre como isso é implementado. A única informação que temos sobre isso é uma vaga entrada de blog no MSDN.

A postagem menciona

Adicionar essas informações tem uma pequena sobrecarga de desempenho.

Portanto, como não sabemos nenhum dos detalhes da implementação, como:

1. As informações estão de alguma forma incluídas na string de conexão ou isso é feito por SET CONTEXT_INFO?
2. Quando as conexões são reutilizadas?
3. Que impacto exato pode ser esperado

Existe alguma maneira de determinar do lado do servidor como o pool de conexões está funcionando e qual é o impacto do context_info?

atualização:
Usando esta consulta daqui

SELECT des.program_name,
       des.login_name,
       des.host_name,
--       der.database_id,
       COUNT(des.session_id) AS [Connections]
FROM sys.dm_exec_sessions des
INNER JOIN sys.dm_exec_connections DEC
        ON des.session_id = DEC.session_id
WHERE des.is_user_process = 1
--AND des.status <> 'running'
GROUP BY des.program_name,
         des.login_name,
         des.host_name
--         ,der.database_id
HAVING COUNT(des.session_id) > 2
ORDER BY COUNT(des.session_id) DESC

Eu posso ver que o pool de conexões é usado.