Vojtěch Dohnal's questions

Preciso atualizar o painel de mensagens no SSMS com mais frequência. Existe uma maneira de fazer isso sem instruções GO?

Minha consulta é:

 DECLARE @Deleted_Rows INT @Deleted_Rows = 1;
 DECLARE @Deleted_Rows_Total bigINT = 0;
 DECLARE @t1 DATETIME;
 DECLARE @t2 DATETIME;

 WHILE (@Deleted_Rows > 0)
   BEGIN
      SET @t1 = GETDATE();
      DELETE TOP (100000)  FROM  PenData WHERE Cas < '20200101'
      SET @Deleted_Rows = @@ROWCOUNT;
      SET @t2 = GETDATE();

      SET @Deleted_Rows_Total = @Deleted_Rows_Total +@Deleted_Rows
      PRINT DATEDIFF(SECOND,@t1,@t2)
      PRINT @Deleted_Rows_Total

      --SELECT @Deleted_Rows AS smazano, @Deleted_Rows_Total AS celkem, DATEDIFF(SECOND,@t1,@t2) AS elapsed_s, GETDATE() as CAS
 END

Eu tenho uma tabela com dados históricos sobre carros AutoDatacom chave agrupada combinada Cas(DateTime) + GCom(Car ID). Um registro contém vários indicadores, como nível de combustível, estado do veículo etc.

Os intervalos entre os registros individuais de um carro na AutoDatatabela são irregulares, às vezes são 120 segundos, às vezes poucos segundos, às vezes horas etc. Preciso normalizar os registros para visualização, para que mostre um registro a cada 30 segundos.

Tenho o seguinte script:

DECLARE @GCom int = 2563,
    @Od DateTime2(0) = '20170210', 
    @Do DateTime2(0) = '20170224'    

--Create a table with intervals by 30 seconds
declare @temp Table ([cas] datetime2(0))
INSERT @temp([cas])
SELECT d
FROM
(
  SELECT
      d = DATEADD(SECOND, (rn - 1)*30, @Od)
  FROM 
  (
      SELECT TOP (DATEDIFF(MINUTE, @Od, @Do)*2) 
          rn = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
      FROM
          sys.all_objects AS s1
      CROSS JOIN
          sys.all_objects AS s2
      ORDER BY
          s1.[object_id]
  ) AS x
) AS y;

--Create temp table
CREATE TABLE #AutoData (
    [Cas] [datetime2](0) NOT NULL PRIMARY KEY,
    [IDProvozniRezim] [tinyint] NOT NULL,
    [IDRidic] [smallint] NULL,
    [Stav] [tinyint] NOT NULL,
    [Klicek] [bit] NOT NULL,
    [Alarm] [bit] NOT NULL,
    [MAlarm] [tinyint] NOT NULL,
    [DAlarm] [bit] NOT NULL,
    [Bypass] [bit] NOT NULL,
    [Lat] [real] NULL,
    [Lon] [real] NULL,
    [ObjemAktualni] [real] NOT NULL,
    [RychlostMaxV1] [real] NOT NULL,
    [RychlostV2] [real] NOT NULL,
    [Otacky] [smallint] NOT NULL,
    [Nadspotreba] [real] NOT NULL,
    [Vzdalenost] [real] NOT NULL,
    [Motor] [smallint] NOT NULL
)

--Populate the temp table selecting only relevant AutoData records
INSERT INTO #AutoData
SELECT [Cas]
      ,[IDProvozniRezim]
      ,[IDRidic]
      ,[Stav]
      ,[Klicek]
      ,[Alarm]
      ,[MAlarm]
      ,[DAlarm]
      ,[Bypass]
      ,[Lat]
      ,[Lon]
      ,[ObjemAktualni]
      ,[RychlostMaxV1]
      ,[RychlostV2]
      ,[Otacky]
      ,[Nadspotreba]
      ,[Vzdalenost]
      ,[Motor]
FROM AutoData a 
WHERE a.GCom = @GCom AND a.cas BETWEEN @Od AND @do

--Select final data
SELECT t.cas, ad.malarm, ad.IDProvoznirezim, ad.Otacky, ad.motor, ad.objemAktualni, ad.Nadspotreba 
FROM @temp t
OUTER APPLY (
SELECT TOP 1 stav, malarm, otacky,motor, objemAktualni, Nadspotreba, IDProvoznirezim  FROM #AutoData a
                     WHERE DATEDIFF(SECOND, a.cas, t.cas)<=CASE WHEN Motor>120 THEN Motor ELSE 120 END 
                     AND DATEDIFF(SECOND,  a.cas, t.cas)>-30 
                     ORDER BY CASE WHEN DATEDIFF(SECOND, a.cas, t.cas)>0 THEN DATEDIFF(SECOND, a.cas, t.cas) ELSE (DATEDIFF(SECOND, a.cas, t.cas)*-1) +120 END
) ad

DROP TABLE #AutoData

A princípio tentei escrever o script com apenas uma variável de tabela @temp colocando a condição WHERE a.GCom = @GCom AND a.cas BETWEEN @Od AND @dona última seleção. O script levou 39 segundos para ser executado.

Quando eu usei #AutoDataa tabela temporária para pré-carregar o subconjunto de dados em uma tabela temporária como é mostrado no script acima, ele caiu para 5 segundos.

Então eu tentei usar uma variável de tabela @AutoDataem vez de #AutoData- mas levou muito mais tempo - 22 segundos.

@temptable tem 40320 registros e #AutoDatatable tem 1904 registros para este exemplo. Mas, surpreendentemente, apenas usar #temptabela em vez de @tempvariável tornou a execução lenta novamente.

Fiquei surpreso ao ver essas diferenças usando ou não a tabela/variável temporária. Aparentemente, o SQL Server não pôde por si só otimizar o interior da cláusula OUTER APPLY.

Mas por que há uma diferença tão grande usando variáveis ​​de tabela versus tabelas temporárias? Existe alguma outra maneira de saber, o que usar e não apenas tentar?

Plano de execução com tabela temporária #AutoData:

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

Plano de execução com a variável @AutoData:

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

Estou ficando sem ideias sobre este pequeno problema:

Eu preciso executar uma consulta em um banco de dados LocalDB diretamente no computador do usuário. Não quero instalar nada lá e há apenas .NET (4.5.2) e SQL Server 2014 Express LocalDB instalados.

Não quero copiar o arquivo do banco de dados para o meu computador e vice-versa com uma pequena modificação, preciso executar apenas uma ou duas consultas diretamente nessa máquina.

Existe alguma ferramenta de linha de comando portátil que me permita fazer isso? O SQLCMD não é utilizável, pois requer a instalação do driver ODBC, o SSMS também requer instalação.

Modifiquei um plano de manutenção no SQL Server 2008 usando o SSMS 2014, particularmente a tarefa Reorganizar índice - adicionei outros dois bancos de dados para esta tarefa.

Quando eu queria verificar se o T-SQL está OK, o View T-SQLbotão mostra o erro:

propriedade desconhecida IsMemoryOptimized

Isso poderia (deveria?) ser consertado de alguma forma?

Em uma máquina com o SQL Server LocalDB 2014 instalado, estou procurando o arquivo SQLCMD.EXE, mas não consigo encontrá-lo em nenhum lugar. Existe uma maneira de instalá-lo separadamente?

Infelizmente não sou administrador local da máquina, então prefiro sem MSI...

Encontrei este link https://www.microsoft.com/en-us/download/confirmation.aspx?id=36433 , mas não pode usar MSI... Talvez copiar arquivos executáveis ​​e dlls possa ajudar.

Depois de copiar os arquivos SQLCMD.EXE, BATCHPARSER.DLL e SQLCMD.RLL C:\Program Files\Microsoft SQL Server\110\Tools\Binn de outro computador com o SSMS instalado, ainda recebo o erro:

Microsoft SQL Server Native Client 11.0: nome da fonte de dados não encontrado e nenhum driver padrão especificado.

correndo sqlcmd -S np:\\.\pipe\LOCALDB#F8040C8F\tsql\query.

eu preciso correr

ALTER TABLE ... 
ALTER COLUMN ... COLLATE Czech_CI_AS ...

para várias colunas em um banco de dados. Todas essas tabelas possuem TIMESTAMPcolunas que são usadas para sincronização do banco de dados.

O ALTER TABLEcomando que muda apenas COLLATIONde uma coluna e nada mais incrementa valores de uma TIMESTAMPcoluna no SQL Server 2008 R2 Std.?

Com base nessas perguntas e nas respostas dadas:

SQL 2008 Server - perda de desempenho possivelmente relacionada a uma tabela muito grande

Tabela grande com dados históricos aloca muito do SQL Server 2008 Std. memória - perda de desempenho para outros bancos de dados

Eu tenho uma tabela em um banco de dados SupervisionP definida assim:

CREATE TABLE [dbo].[PenData](
    [IDUkazatel] [smallint] NOT NULL,
    [Cas] [datetime2](0) NOT NULL,
    [Hodnota] [real] NULL,
    [HodnotaMax] [real] NULL,
    [HodnotaMin] [real] NULL,
 CONSTRAINT [PK_Data] PRIMARY KEY CLUSTERED 
(
    [IDUkazatel] ASC,
    [Cas] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[PenData]  WITH NOCHECK ADD  CONSTRAINT [FK_Data_Ukazatel] FOREIGN KEY([IDUkazatel])
REFERENCES [dbo].[Ukazatel] ([IDUkazatel])

ALTER TABLE [dbo].[PenData] CHECK CONSTRAINT [FK_Data_Ukazatel]

Ele contém cerca de 211 milhões de linhas.

Eu corro a seguinte declaração:

DECLARE @t1 DATETIME;
DECLARE @t2 DATETIME;

SET @t1 = GETDATE();
SELECT min(cas) from PenData p WHERE IDUkazatel=24
SELECT min(cas) from PenData p WHERE IDUkazatel=25
SET @t2 = GETDATE();
SELECT DATEDIFF(millisecond,@t1,@t2) AS elapsed_ms;


SET @t1 = GETDATE();
SELECT min(cas) from PenData p WHERE IDUkazatel=24 OR IDUkazatel=25 
SET @t2 = GETDATE();
SELECT DATEDIFF(millisecond,@t1,@t2) AS elapsed_ms;

O resultado é mostrado aqui:

O terceiro SELECT também carrega muito mais dados no cache de memória do SQL Server.

Por que o terceiro SELECT é muito mais lento (8,5 s) do que os dois primeiros SELECTs (16 ms)? Como posso melhorar o desempenho do terceiro select com OR? Eu quero executar o seguinte comando SQL, mas parece-me que criar cursor e executar consultas separadas é muito mais rápido do que uma única seleção neste caso.

 SELECT MIN(cas) from PenData p WHERE IDUkazatel IN (SELECT IDUkazatel FROM  ...)

EDITAR

Como David sugeriu, passei o mouse sobre a seta gorda:

Com base nesta pergunta e nas respostas dadas:

SQL 2008 Server - perda de desempenho possivelmente relacionada a uma tabela muito grande

Eu descobri um problema de alocação de memória com o SQL Server 2008.

Basicamente, temos 3 bancos de dados (EkDB cca 300 MB, SupervisionP cca 8 GB e Tarmac42 cca 42 GB) em 1 SQL Server e uma tabela muito grande fica na memória. O consumo de memória é assim:

Quase 6 GB são alocados por apenas uma tabela PenData com cerca de 211 milhões de linhas.

Esta tabela é definida da seguinte forma:

CREATE TABLE [dbo].[PenData](
    [IDUkazatel] [smallint] NOT NULL,
    [Cas] [datetime2](0) NOT NULL,
    [Hodnota] [real] NULL,
    [HodnotaMax] [real] NULL,
    [HodnotaMin] [real] NULL,
 CONSTRAINT [PK_Data] PRIMARY KEY CLUSTERED 
(
    [IDUkazatel] ASC,
    [Cas] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[PenData]  WITH NOCHECK ADD  CONSTRAINT [FK_Data_Ukazatel] FOREIGN KEY([IDUkazatel])
REFERENCES [dbo].[Ukazatel] ([IDUkazatel])

ALTER TABLE [dbo].[PenData] CHECK CONSTRAINT [FK_Data_Ukazatel]

Esta tabela contém muitos registros históricos que não são usados ​​ou usados ​​muito raramente e os dados são sempre acessados ​​usando condições como esta

 WHERE IDUkazatel=@a AND Cas BETWEEN @b AND @c

nunca sem esta condição. Os dados estão sendo inseridos a cada hora em uma série de inserções em massa, o banco de dados cresce cerca de 10 MB por dia.

Tentei excluir alguns registros (83 milhões) e, em seguida, executar o DBCC SHRINKDATABASE - parecia promissor no momento, mas no dia seguinte o SupervisionP ocupou novamente 5-6 GB.

Não consigo encontrar nenhuma razão para o SQL Server alocar tanta memória para esta tabela. O problema é que os dados armazenados em cache dos outros bancos de dados são descarregados regularmente e as consultas neles estão sendo executadas mais lentamente antes que os dados sejam carregados na memória novamente.

EDITAR

Esqueci de mencionar que é a edição Standard do SQL Server... Portanto, o particionamento não é possível. Eu considerei isso, mas não observei que é apenas para edição corporativa.

EDITAR 2

Eu examinei todos os procedimentos armazenados e devo admitir que existem vários comandos SQL que podem acessar mais linhas do que eu supunha, ou seja

SELECT @minCas=MIN(cas) FROM PenData WHERE IDUkazatel=@IDUkazatel 

SELECT @StazenoDoReal=MAX(cas) from PenData p INNER JOIN Ukazatel u ON u.IDUkazatel=p.IDUkazatel WHERE u.IDZapisovac=@IDZapisovac 

SELECT  TOP 365   DATEADD(dd, 0, DATEDIFF(dd, 0, dbo.PenData.Cas)) AS Den, MAX(dbo.PenData.Hodnota) AS MaxHodnota
FROM         dbo.Zapisovac INNER JOIN
                      dbo.VyrobniLinka ON dbo.Zapisovac.IDVyrobniLinka = dbo.VyrobniLinka.IDVyrobniLinka INNER JOIN
                      dbo.Ukazatel ON dbo.Zapisovac.IDZapisovac = dbo.Ukazatel.IDZapisovac INNER JOIN
                      dbo.PenData ON dbo.Ukazatel.IDUkazatel = dbo.PenData.IDUkazatel
WHERE     /*(dbo.PenData.Cas >= @Od) AND (dbo.PenData.Cas <= @Do) AND*/ (dbo.Zapisovac.IDVyrobniLinka = @IDVyrobniLinka AND dbo.Zapisovac.IDTypZapisovace!=4)
GROUP BY DATEADD(dd, 0, DATEDIFF(dd, 0, dbo.PenData.Cas)), ISNULL(dbo.Ukazatel.MinHodnotaProvoz, 10)
HAVING MAX(dbo.PenData.Hodnota)>ISNULL(dbo.Ukazatel.MinHodnotaProvoz, 10) OR MAX(dbo.PenData.Hodnota) IS NULL
ORDER BY Den DESC

O SELECT MIN de todos os registros precisa ler todos os registros o tempo todo? Acho que sim, talvez uma pergunta boba...

EDITAR 3

Então fiz o seguinte teste. Eu restaurei o banco de dados SupervisionP no servidor de teste. Após a restauração, ocupou apenas 1 MB no cache. Então eu executo esta consulta simples:

  SELECT MIN(cas) FROM PenData

A chave primária em cluster para PenData é um composto como mostrado acima - contendo Time + IndicatorID. Portanto, encontrar o primeiro ou o último registro em um índice clusterizado pode ser uma tarefa relativamente fácil, suponho, para o SQL Server. Mas o uso de memória cresceu muito abruptamente para 1285 MB (o servidor de teste tem 3,5 GB de memória total utilizável). A consulta está fazendo o Clustered Index Scan.

Quando eu faço:

 SELECT MIN(cas)  FROM PenData WHERE IDUkazatel=10

então é Clustered Index Seek e a memória permanece em 1 MB - é muito rápido e OK.

Quando eu corro:

 SELECT MIN(cas) from PenData p WHERE IDUkazatel IN (SELECT IDUkazatel FROM Ukazatel WHERE IDZapisovac=10)

ou

 SELECT min(cas) from PenData p WHERE IDUkazatel=24 OR IDUkazatel=25 OR IDUkazatel=26 OR IDUkazatel=97 OR IDUkazatel=97 OR IDUkazatel=98

então leva muito tempo e a alocação de memória sobe abruptamente. No entanto, os seguintes comandos, que fazem a mesma coisa, são executados em um instante e sem alocação de memória!!

SELECT min(cas) from PenData p WHERE IDUkazatel=24 
SELECT min(cas) from PenData p WHERE IDUkazatel=25
...

EDITAR 5

Abri uma nova pergunta, pois parece que esse comando é o motivo do problema

SELECT @StazenoDoReal=MAX(cas) from PenData p INNER JOIN Ukazatel u ON u.IDUkazatel=p.IDUkazatel WHERE u.IDZapisovac=@IDZapisovac 

Busca de índice muito mais lenta com condição OR em comparação com SELECTs separados

Temos o SQL Server 2008 com 3 bancos de dados ativos em execução.

• DB1 - cca 400 MB de tamanho
• DB2 - tamanho cca de 8 GB
• DB3 - tamanho de cca 42 GB - mas a maioria dos registros não é usada

No DB2 temos esta tabela

CREATE TABLE [dbo].[PenData](
    [IndicatorID] [smallint] NOT NULL,
    [Time] [datetime2](0) NOT NULL,
    [Value] [real] NULL,
    [ValueMax] [real] NULL,
    [ValueMin] [real] NULL,
 CONSTRAINT [PK_Data] PRIMARY KEY CLUSTERED 
(
    [IndicatorID] ASC,
    [Time] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Esta tabela por si só ocupa cerca de 8 GB e possui 283.029.812 registros. A grande maioria dos registros nesta tabela são registros históricos e são acessados ​​muito raramente ou nunca. Mas uma pequena parte dos registros recentes é bastante utilizada e a cada hora muitos novos registros são inseridos nesta tabela.

O problema é que recentemente observamos problemas de desempenho no DB3. Embora o desempenho do DB2 e PenData esteja OK.

Minha pergunta é:

1. o tamanho da tabela PenData pode ser um fator importante para o desempenho geral do servidor? Como esses muitos registros de tabela não utilizados afetam a memória alocada pelo servidor?

2. Posso obter um ganho significativo de performance no servidor (em DB3) se deletar metade dos registros da tabela muito grande PenData?

3. E existem ferramentas para monitorar o desempenho quando não tenho permissões para acessar o Monitor de atividades?

EDITAR

Fiquei bastante apavorado ao ver (usando scripts fornecidos nas respostas) que a tabela PenData ocupava 60-70% de toda a memória do SQL Server (que é relativamente baixa, cca 6 GB). Não sei por que, já que este é o aplicativo que eu mesmo programei e não vejo nenhum motivo, por que tantas linhas desta tabela devem permanecer armazenadas em cache na memória. Também foi um erro meu executar SELECT COUNT(*) FROM PenData antes de tentar ver quanto de PenData permaneceu armazenado em cache na memória.

Omiti uma chave estrangeira que tenho nesta tabela, então a apresento aqui:

ALTER TABLE [dbo].[PenData]  WITH NOCHECK ADD  CONSTRAINT [FK_Data_Indicator] FOREIGN    KEY([IndicatorID])
REFERENCES [dbo].[Indicator] ([IndicatorID])

Eu deletei milhões de registros em lotes de 100.000 registros usando SET RECORDCOUNT 100000 de PenData. Agora ele tem 211 120 425 registros. Executei o DBCC SHRINKDATABASE (PenData, 20) - somente depois disso o consumo de memória do PenData diminuiu significativamente.

O desempenho e o consumo de memória de outros bancos de dados melhoraram.

Mas depois de um dia a tabela PenData ocupa novamente quase toda a memória...

EDITAR

Alterei um único comando SQL em um único procedimento armazenado e agora tudo está perfeito, o banco de dados SupervisionP ocupa apenas 184 MB no cache! Veja detalhes aqui

Busca de índice muito mais lenta com condição OR em comparação com SELECTs separados

Obrigado pela ajuda.