Fza's questions

Como parte de um programa de correção herdado, migramos os bancos de dados do aplicativo de uma instância antiga do SQL Server 2000 em execução no Windows 2003 para uma nova instância do SQL Server 2022 em execução no Windows 2022. A camada do aplicativo tem vários servidores Web usando IIS 6.0 no Windows 2003 e quando tentam testar a conexão com o novo SQL Server, obtêm o erro de segurança SSL abaixo. Alguma ideia do que poderia ser o problema está aqui, é provável que seja o driver (a captura de tela abaixo mostra os drivers instalados nos servidores web)? Ou é provável que esteja relacionado a quais versões de SSL/TLS estão habilitadas em ambos os lados?

Eu tenho uma instância do SQL Server 2017 Enterprise Edition em que um procedimento armazenado estava demorando aprox. cinco minutos para executar. Depois de revisar o código do procedimento armazenado, pude ver que havia um UDF escalar embutido referenciado várias vezes na lista SELECT e também na cláusula WHERE do predicado do corpo do procedimento armazenado.

Aconselhei a equipe de aplicativos proprietária do código que eles deveriam refatorar seu proc armazenado para não usar uma UDF em linha que eles incorporaram e substituíram por um TVF. Enquanto eles estavam fazendo isso, notei que o banco de dados do aplicativo ainda tinha nível de compatibilidade de banco de dados 100, então elevei isso para o nível mais recente de 140 depois de executar o banco de dados por meio do Data Migration Assistant para verificar se há recursos obsoletos e alterações importantes.

Após a substituição do UDF por um TVF e aumentando o nível de compatibilidade do banco de dados de 100 para 140, o desempenho aumentou muito e o proc armazenado agora é executado em menos de um minuto, mas o desempenho ainda não está onde eu gostaria. Espero que alguém possa aconselhar sobre algo óbvio que estou perdendo ou me apontar na direção certa de qualquer outra coisa que eu possa fazer para otimizar ainda mais o código ou obter um melhor desempenho? O plano de execução está aqui: https://www.brentozar.com/pastetheplan/?id=ByrsEdRpr

O código para o procedimento armazenado e função são como abaixo e o procedimento armazenado é chamado pelo aplicativo como tal: "EXEC dbo.CAOT_GetApplicationQueue;1"

/****** Object: StoredProcedure [dbo].[CAOT_GetApplicationQueue] ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[CAOT_GetApplicationQueue]
(@userID VARCHAR(50)='', @showComplete CHAR(1)='N', @JustMyQueue BIT=0, @ChannelId VARCHAR(10) = NULL )
AS
BEGIN
SELECT App.pkApplication ,
COALESCE(ApplicationReference, AlternateApplicationReference) AS ApplicationReference ,
ApplicationDate ,
Name ,
Telephone ,
[Address] ,
Email ,
CIN ,
Dob ,
CreatedDate ,
BusinessPhone ,
PostCode ,
MobilePhone ,
[Action] ,
ActionStatus ,
branchNumber ,
AccountNumber ,
AccountType ,
act.accountDescription,
IsNull( appstatus.DESCRIPTION ,'-- CREATED --') As LastStatus,
IsNull(appstatus.DAYS,'0') DaysSinceLastStatus ,
DATEDIFF(d,ApplicationDate, GETDATE()) DaysCreated,
InitialUserID,
IsNull(appstatus.STATUS,'-- MADE --') APPLICATIONSTATUS
FROM dbo.CAOT_Application (NOLOCK) app
LEFT OUTER JOIN dbo.CAOT_AccountType (NOLOCK) act
ON app.AccountType = act.AccountTypecode
LEFT OUTER JOIN [CAOT_GetAllApplicationStatus]() appstatus
ON app.pkApplication = appstatus.[PKAPPLICATION]
WHERE (IsNull(appstatus.STATUSCODE,'MADE') NOT IN ('CANCELLED','DECLINED','COMPLETE','EXPIRED')
OR @showComplete='Y') AND
(@JustMyQueue = 0 OR InitialUserID = @userID) AND
(@ChannelId IS NULL OR ChannelID = @ChannelId OR (@ChannelId = 'CBU' AND ChannelID IS NULL AND isCAO='N'))
ORDER BY CASE WHEN InitialUserID = @userid THEN 10 ELSE 900 END, ApplicationDate DESC
END
GO

/****** Object: UserDefinedFunction [dbo].[CAOT_GetAllApplicationStatus] ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[CAOT_GetAllApplicationStatus]() RETURNS
@return TABLE
(
[PKAPPLICATION] [int] NOT NULL,
[PKAPPLICATIONEVENT] INT,
[EVENTCREATEDDATE] [DATETIME] NULL,
[KEY] VARCHAR(12) NULL,
[DESCRIPTION] VARCHAR(200) NULL,
[CODE] VARCHAR(20) NULL,
[DAYS] VARCHAR(20) NULL,
[STATUS] VARCHAR(200) NULL,
[STATUSCODE] VARCHAR(50) NULL
)

AS
BEGIN

Declare @AppStatus table
(
[PKAPPLICATION] [int] NOT NULL,
[PKAPPLICATIONEVENT] INT,
[EVENTCREATEDDATE] [DATETIME] NULL,
[KEY] VARCHAR(12) NULL,
[DESCRIPTION] VARCHAR(200) NULL,
[CODE] VARCHAR(20) NULL,
[DAYS] VARCHAR(20) NULL,
[STATUS] VARCHAR(200) NULL,
[STATUSCODE] VARCHAR(50) NULL
)

INSERT INTO @AppStatus

SELECT
fkApplication,
ev.pkApplicationEvent As pkApplicationEvent,
ev.CreateDate As 'EventCreatedDate',
CONVERT(VARCHAR(12), evt.fkApplicationStatus) As 'KEY',
evt.EventDescription As 'DESCRIPTION',
evt.EventCode As 'CODE' ,
CONVERT(VARCHAR(20), DATEDIFF(d, ev.createdate, GETDATE()) ) As 'DAYS',
apps.StatusDescription As 'STATUS' ,
apps.StatusCode As 'STATUSCODE'
FROM dbo.CAOT_ApplicationEvent (NOLOCK) ev
INNER JOIN dbo.CAOT_EventType (NOLOCK) evt ON ev.fkEventType = evt.pkEventType
INNER JOIN dbo.CAOT_ApplicationStatus (NOLOCK) apps ON evt.fkApplicationStatus = apps.pkApplicationStatus

ORDER BY ev.CreateDate DESC, ev.pkApplicationEvent DESC

INSERT INTO @return
Select * from @AppStatus AllStatus
Where AllStatus.EVENTCREATEDDATE = ( Select Max(LatestAppStatus.EVENTCREATEDDATE) from @AppStatus LatestAppStatus where LatestAppStatus.PKAPPLICATION =AllStatus.PKAPPLICATION ) --Z On X.PKAPPLICATION = Z.PKAPPLICATION

RETURN
END

GO

Estou tentando ajustar a consulta abaixo, que leva de 15 a 16 segundos, independentemente do valor passado como parâmetro, a consulta é:

select distinct d.documentpath as path, d.documentname as name, d.datecreated as created, pc.DateProcessed
from datagatheringruntime dgr 
 inner join processentitymapping pem on pem.entityid = dgr.entityid 
 inner join document d on d.entityid = pem.entityid or d.unitofworkid = pem.processid
 left join PendingCorrespondence pc on pc.PendingCorrespondenceId = d.PendingCorrespondenceId
 where rootid = @P0 and dgr.name in('cust_pn', 'case_pn')
OPTION(RECOMPILE)

Eu atualizei as estatísticas para todas as tabelas tocadas pela consulta (excluindo a DataGatheringRuntimetabela que é bastante grande em ~ 100GB) e tentei refatorar a consulta usando um CTEmas obtive o mesmo plano de execução e preciso de alguma assistência.

O plano de execução real pode ser encontrado aqui:

https://www.brentozar.com/pastetheplan/?id=ByUVIqlFE

Está claro no plano de execução que o problema está na entrada externa nested loop joinespecificamente com o lazy table spoolseguinte do índice scannão clusterizado na tabela, mas não tenho ideia de como resolver esse problema e gostaria de receber qualquer orientação.IX_Camunda_1Document

Eu tenho um banco de dados relativamente grande de 550 GB em uma instância do SQL Server 2016 EE que tem um limite máximo de memória de 112 GB do total de 128 GB de RAM disponível para o sistema operacional. O banco de dados está no nível de compatibilidade mais recente de 130. Os desenvolvedores reclamaram da consulta abaixo que é executada dentro de um tempo aceitável de 30 segundos quando executada isoladamente, mas quando executam seus processos em escala, a mesma consulta é executada várias vezes simultaneamente em vários encadeamentos e é quando eles observam que o tempo de execução sofre e o desempenho/taxa de transferência cai. O T-SQL problemático é:

select distinct dg.entityId, et.EntityName, dg.Version
                     from DataGathering dg with(nolock) 
                     inner join entity e with(nolock) 
                           on e.EntityId = dg.EntityId
                     inner join entitytype et with(nolock)   
                         on et.EntityTypeID = e.EntityTypeID  
                         and et.EntityName = 'Account_Third_Party_Details' 
                     inner join entitymapping em with(nolock)   
                         on em.ChildEntityId = dg.EntityId  
                         and em.ParentEntityId = -1  
                     where dg.EntityId = dg.RootId  

    union all

select distinct dg1.EntityId, et.EntityName, dg1.version
                     from datagathering dg1 with(nolock)  
                     inner join entity e with(nolock)   
                         on e.EntityId = dg1.EntityId 
                     inner join entitytype et with(nolock)   
                         on et.EntityTypeID = e.EntityTypeID 
                         and et.EntityName = 'TIN_Details' 
                     where dg1.EntityId = dg1.RootId  
                     and dg1.EntityId not in (  
                         select distinct ChildEntityId   
                         from entitymapping  
                         where ChildEntityId = dg1.EntityId 
                         and ParentEntityId = -1)

O plano de execução real mostra o aviso de concessão de memória abaixo:

O plano gráfico de execução pode ser encontrado aqui:

https://www.brentozar.com/pastetheplan/?id=r18ZtCidN

Abaixo estão as contagens de linhas e tamanhos das tabelas tocadas por esta consulta. O operador mais caro é uma varredura de índice de um índice não clusterizado na tabela DataGathering, o que faz sentido considerando o tamanho da tabela em comparação com as outras. Eu entendo por que/como a concessão de memória é necessária, o que acredito ser devido à forma como a consulta é escrita, o que requer vários tipos e operadores de hash. O que eu preciso de conselho / orientação é como evitar as concessões de memória, T-SQL e código de refatoração não é meu ponto forte, existe uma maneira de reescrever essa consulta para que ela tenha mais desempenho? Se eu puder ajustar a consulta para ser executada mais rapidamente isoladamente, espero que os benefícios sejam transferidos para quando ela for executada em escala, que é quando o desempenho começa a sofrer. Feliz em fornecer mais informações e esperando aprender algo com isso!

Após atualizar as estatísticas em 3 das tabelas:

UPDATE STATISTICS Entity WITH FULLSCAN; 
UPDATE STATISTICS EntityMapping WITH FULLSCAN; 
UPDATE STATISTICS EntityType WITH FULLSCAN;

...o plano de execução melhorou um pouco:

https://www.brentozar.com/pastetheplan/?id=rkVmdkh_4

Infelizmente, o aviso "Excessive Grant" ainda está lá.

Josh Darnell gentilmente sugeriu refatorar a consulta para o abaixo para evitar que o paralelismo seja inibido que ele detectou em um determinado operador. A consulta refatorada gera o erro "Msg 4104, Level 16, State 1, Line 7 O identificador de várias partes "et.EntityName" não pôde ser vinculado." Como faço para contornar isso?

DECLARE @tinDetailsId int;

SELECT @tinDetailsId = et.EntityTypeID 
FROM entitytype et 
WHERE et.EntityName = 'TIN_Details';

select distinct dg1.EntityId, et.EntityName, dg1.version
                     from datagathering dg1 with(nolock)  
                     inner join entity e with(nolock)   
                         on e.EntityId = dg1.EntityId
                     where dg1.EntityId = dg1.RootId  
                     and e.EntityTypeID = @tinDetailsId
                     and dg1.EntityId not in (  
                         select distinct ChildEntityId   
                         from entitymapping  
                         where ChildEntityId = dg1.EntityId 
                         and ParentEntityId = -1)

            UNION ALL

select distinct dg.entityId, et.EntityName, dg.Version
                     from DataGathering dg with(nolock) 
                     inner join entity e with(nolock) 
                           on e.EntityId = dg.EntityId
                     inner join entitytype et with(nolock)   
                         on et.EntityTypeID = e.EntityTypeID  
                         and et.EntityName = 'Account_Third_Party_Details' 
                     inner join entitymapping em with(nolock)   
                         on em.ChildEntityId = dg.EntityId  
                         and em.ParentEntityId = -1  
                     where dg.EntityId = dg.RootId  

Estou fazendo alguns testes em atualizações automáticas de estatísticas sincronizadas e assíncronas. Eu gostaria de invalidar rapidamente todos os objetos estatísticos (cabeçalhos, vetores de densidade e histogramas) para garantir que na próxima vez que a estatística for usada, ela será atualizada .

Estou tentando simular uma atualização automática de estatísticas, não uma criação automática.

Idealmente, não quero alterar a contagem de linhas, por isso descartei INSERT/DELETEas operações. Idealmente, também não quero alterar nenhum valor de dados, considerei o uso UPDATEde instruções, mas acho que isso pode demorar muito em algumas das minhas tabelas maiores.

Eu tinha olhado, UPDATE STATISTICS WITH ROWCOUNT, PAGECOUNTmas não acho que é isso que estou procurando. Eu esperava que houvesse talvez um sinalizador de rastreamento ou comando não documentado que invalidasse as estatísticas.

Existe uma maneira rápida e eficiente de fazer o que quero alcançar que não considerei?

Estou testando no SQL Server 2016.

Quais bloqueios são obtidos durante as várias operações estatísticas abaixo no SQL Server, assumindo que as opções padrão de estatísticas de criação automática e estatísticas de atualização automática estão definidas como verdadeiras?

1. Criação automática de um objeto estatístico
2. Atualização automática de um objeto estatístico
3. Criação manual de um objeto estatístico
4. Atualização manual de um objeto estatístico

Existe alguma diferença nos bloqueios obtidos nos cenários 1 e 2 acima quando as estatísticas são definidas para atualizar de forma assíncrona?

Fiquei com a impressão de que, ao usar o LIKEoperador em todas as otimizações para cenários desconhecidos, tanto o legado quanto os novos CEs usam uma estimativa de 9% (supondo que estatísticas relevantes estejam disponíveis e o otimizador de consulta não precise recorrer a suposições de seletividade).

Ao executar a consulta abaixo no banco de dados de crédito, obtenho diferentes estimativas nos diferentes CEs. Sob o novo CE, recebo uma estimativa de 900 linhas que eu esperava, sob o CE herdado, recebo uma estimativa de 241,416 e não consigo descobrir como essa estimativa é derivada. Alguém é capaz de lançar alguma luz?

-- New CE (Estimate = 900)
DECLARE @LastName VARCHAR(15) = 'BA%'
SELECT * FROM [Credit].[dbo].[member]
WHERE [lastname] LIKE @LastName;

-- Forcing Legacy CE (Estimate = 241.416)
DECLARE @LastName VARCHAR(15) = 'BA%'
SELECT * FROM [Credit].[dbo].[member]
WHERE [lastname] LIKE @LastName
OPTION (
QUERYTRACEON 9481,
QUERYTRACEON 9292,
QUERYTRACEON 9204,
QUERYTRACEON 3604
);

No meu cenário, já tenho o banco de dados de crédito definido para o nível de compatibilidade 120, por isso na segunda consulta estou usando sinalizadores de rastreamento para forçar o CE herdado e também para fornecer informações sobre quais estatísticas são usadas/consideradas pelo otimizador de consulta. Posso ver que as estatísticas da coluna 'sobrenome' estão sendo usadas, mas ainda não consigo descobrir como a estimativa de 241,416 é derivada.

Não consegui encontrar nada on-line além deste artigo de Itzik Ben-Gan , que afirma "Ao usar o predicado LIKE em todas as otimizações para cenários desconhecidos, tanto o legado quanto os novos CEs usam uma estimativa de 9 por cento". As informações nesse post parecem estar incorretas.

Há muitas informações excelentes disponíveis sobre certas abordagens que podem ser usadas para mitigar o sniffing de parâmetro ruim, mas não há muitas informações sobre como identificar um problema de sniffing de parâmetro ruim.

Supondo que você não esteja usando o SQL Server 2016, portanto, não pode aproveitar o Query Store e não há usuários reclamando de um procedimento armazenado problemático que apresenta desempenho ruim de forma intermitente, quais métodos podem ser usados ​​para identificar proativamente um problema de detecção de parâmetro incorreto?

Utilizar Eventos Estendidos, Utilitários RML e simplesmente consultar/pesquisar os DMVs parecem abordagens viáveis.

Estou usando a consulta T-SQL abaixo para determinar a data do último backup completo do banco de dados e também retornar o tamanho e o local do arquivo de backup. Meu problema é que ele não retornará nenhum dado para bancos de dados que não tiveram backups ou para os quais não há histórico de backup. Idealmente, eu gostaria de modificar a consulta para que todos os bancos de dados fossem retornados, independentemente de haver ou não histórico de backup para eles. Alguém pode aconselhar como a consulta abaixo pode ser modificada para acomodar isso?

WITH LastBackUp AS
(
SELECT  bs.database_name,
        bs.backup_size,
        bs.backup_start_date,
        bmf.physical_device_name,
        Position = ROW_NUMBER() OVER( PARTITION BY bs.database_name ORDER BY bs.backup_start_date DESC )
FROM  msdb.dbo.backupmediafamily bmf
JOIN msdb.dbo.backupmediaset bms ON bmf.media_set_id = bms.media_set_id
JOIN msdb.dbo.backupset bs ON bms.media_set_id = bs.media_set_id
WHERE   bs.[type] = 'D'
AND bs.is_copy_only = 0
)
SELECT 
        database_name AS [Database],
        CAST(backup_size / 1048576 AS DECIMAL(10, 2) ) AS [BackupSizeMB],
        backup_start_date AS [Last Full DB Backup Date],
        physical_device_name AS [Backup File Location]
FROM LastBackUp
WHERE Position = 1
ORDER BY [Database];

Este é um acompanhamento da pergunta: AlwaysON Availability Groups - alteração do endereço IP do nó de réplica secundária

Mesmo cenário, Grupo de Disponibilidade no modo de confirmação síncrona, uma réplica primária e uma réplica secundária em uma configuração de várias sub-redes. Os nós do cluster são máquinas físicas. Devido à manutenção de hardware, o servidor usado pelo nó do cluster de réplica secundária está sendo movido, portanto, ficará off-line e voltará a ficar on-line com um endereço IP diferente em uma sub -rede diferente . Como alguém abordaria isso?

Meus pensamentos iniciais são: Se possível, adicione a nova interface de rede ao nó secundário. Configure a nova interface de rede com o novo endereço IP que está em uma sub-rede diferente. O cluster deve construir automaticamente as rotas internas e registrar a nova interface de rede.

No FCM (Failover Cluster Manager), uma nova rede de cluster aparecerá na guia 'Redes'. Nos recursos principais do cluster, adicione a nova rede e crie um novo endereço IP estático para o VNN do cluster. Aplique as alterações e, em seguida, volte e adicione uma dependência OR no novo endereço IP.

Antes da interrupção da movimentação do servidor em que o antigo endereço IP/interface de rede será removido, no FCM, volte aos recursos principais do cluster, acesse as propriedades do VNN do cluster e remova o antigo endereço IP de rede/estático e remova quaisquer dependências em isto. Quando o servidor for colocado off-line e voltar a ficar on-line, a interface de rede antiga não ficará visível para o servidor e não aparecerá na guia de redes no FCM. Não deve haver nenhum problema com nenhum recurso em cluster, pois todas as dependências do antigo IP/sub-rede/rede foram removidas.

Há algo mais que deve ser levado em consideração? Como os nós do cluster são físicos, isso complica as coisas em relação às interfaces de rede? Com o teste em um ambiente virtual, é obviamente simplificado, pois é fácil remover e conectar switches de rede virtual.

Eu tenho um AlwaysON AG de várias sub-redes com uma réplica primária e uma secundária no modo de confirmação síncrona. Devido à manutenção do hardware, o endereço IP do nó secundário será alterado.

Existe alguma ação que eu deva tomar ou algo que eu deva estar ciente de uma perspectiva do SQL Server, Grupos de Disponibilidade AlwaysON e WSFC?

Eu tenho um AlwaysON AG com uma réplica primária e uma secundária no modo de confirmação síncrona. Todos os bancos de dados no AG têm crescimento automático desabilitado (política padrão dentro da organização). Há uma manutenção de hardware planejada no servidor que hospeda a réplica secundária que fará com que o servidor fique offline por 4 a 8 horas. Meu entendimento é que, quando isso acontecer, o estado conectado da réplica secundária mudará para DISCONNECTED, a fila de envio dos bancos de dados na réplica primária acumulará registros de log de transações não enviados. Enquanto a réplica secundária permanecer offline, todos os registros de log atuais permanecerão ativos (o crescimento automático está desabilitado), os backups de log de transações não truncarão o log com o potencial de preenchimento do log. Alguém por favor pode confirmar se isso está correto? Existe alguma maneira de mitigar isso, ie.

Esse foi o meu entendimento, mas depois de ler este artigo msdn https://msdn.microsoft.com/en-us/library/ff877931%28v=sql.110%29.aspx?f=255&MSPPError=-2147217396 Estou confuso. Há uma observação que afirma: "Se o período de tempo limite da sessão primária for excedido por uma réplica secundária, a réplica primária mudará temporariamente para o modo de confirmação assíncrona para essa réplica secundária. modo de confirmação."

Isso se aplica à minha situação descrita acima e, em caso afirmativo, isso significa que o potencial de preenchimento da transação é mitigado por design? Essa mudança temporária no modo de confirmação pode ser vista ao consultar sys.availabilty_replicas ou por meio de qualquer evento estendido ou é uma mudança puramente oculta?