m__'s questions

Eu tenho uma grande tabela de série temporal particionada onde cada partição é armazenada em seu próprio arquivo. Como é um particionamento baseado em séries temporais dividido por semana e estou armazenando apenas dados novos, nunca tenho mais de 2 partições nas quais estou gravando ativamente ao mesmo tempo. Eu tenho um FILLFACTOR definido como 75 no índice, pois os dados estão chegando aleatoriamente.

As antigas partições de dados estão ocupando bastante espaço, utilizando apenas cerca de 60-70% desse espaço. É possível alterar automaticamente o FILLFACTOR nas referidas partições, reconstruir os índices e reduzir os arquivos? Como não estou mais gravando nessas partições, elas não devem crescer novamente.

Estou revisando uma instalação do SQL Server 2008R2 com grandes problemas de desempenho. Atualmente, estou analisando seus índices e chaves estrangeiras e descobri que nenhuma das tabelas possui chaves estrangeiras especificadas. Ou o aplicativo cuida de toda a lógica de negócios sozinho ou a instalação está errada.

Quanto as chaves estrangeiras afetam o desempenho? Ou eles existem apenas para lógica de negócios? Existem casos em que não ter chaves estrangeiras é uma vantagem? (por exemplo, este sistema suporta SQL Server, Oracle e DB2.)

Fundo

Tenho alguns dispositivos, cada um com alguns sensores. Eu os registro de vez em quando e os armazeno em uma tabela descrita abaixo. Quando alguém solicita uma página da Web, busco alguns desses valores (o último registrado) um por um e os mostro ao usuário. Mas atualmente isso leva muito tempo porque há muitos valores que precisam ser buscados, a busca leva cerca de 8 ms por valor e, no total, falamos de cerca de 300 ms de aumento no tempo total de carregamento da página - para uma página relativamente boa.

CREATE TABLE [dbo].[SensorValues](
  [DeviceId] [int] NOT NULL,
  [SensorId] [int] NOT NULL,
  [SensorValue] [int] NOT NULL,
  [Date] [int] NOT NULL, --- stored as unixtime
CONSTRAINT [PK_SensorValues] PRIMARY KEY CLUSTERED 
(
  [DeviceId] ASC,
  [SensorId] ASC,
  [Date] DESC
);

A tabela é particionada semanalmente na coluna Data.

O que faço agora

Então, o que eu faço é o seguinte. Eu seleciono o maior valor em cada partição que está antes da data/hora atual. e escolha o maior valor.

SELECT TOP (1) ca.SensorValue, ca.Date
  FROM sys.partitions AS p
  CROSS APPLY
  (
  SELECT TOP (1) v.Date, v.SensorValue
    FROM SensorValue AS v
    WHERE $PARTITION.SensorValues_Date_PF(v.Date) = p.[partition_number]
    AND v.DeviceId = @fDeviceId
    AND v.SensorId = @fSensorId
    AND v.Date <= @fDate
    ORDER BY v.Date DESC
  ) AS ca
  WHERE p.[partition_number] <= $PARTITION.SensorValues_Date_PF(@fDate)
  AND p.[object_id] = OBJECT_ID(N'dbo.SensorValues', N'U')
  AND p.index_id = 1
  ORDER BY p.[partition_number] DESC, ca.Date DESC;

O que eu quero fazer

Eu quero selecionar todos os valores em uma consulta. Por exemplo, selecione o valor mais recente para DeviceId=1 e SensorId=1,2,3,4,5. Eu criei o seguinte até agora, onde seleciono com a palavra-chave IN para obter valores para vários sensores. No entanto, ainda preciso agrupá-los e resolver aquele com a data mais alta. Estou pensando em adicionar uma cláusula GROUP BY, mas não sei como acertar (as que tentei até agora falharam).

SELECT ca.SensorValue, ca.Date
  FROM sys.partitions AS p
  CROSS APPLY
  (
  SELECT TOP (1) v.Date, v.SensorValue
    FROM SensorValue AS v
    WHERE $PARTITION.SensorValues_Date_PF(v.Date) = p.[partition_number]
    AND v.DeviceId = @fDeviceId
    AND v.SensorId IN (@fSensorId1, @fSensorId2, @fSensorId3)
    AND v.Date <= @fDate
    ORDER BY v.Date DESC
  ) AS ca
  WHERE p.[partition_number] <= $PARTITION.SensorValues_Date_PF(@fDate)
  AND p.[object_id] = OBJECT_ID(N'dbo.SensorValues', N'U')
  AND p.index_id = 1
  ORDER BY p.[partition_number] DESC, ca.Date DESC;

Esta é uma pergunta derivada da ordem de classificação especificada na chave primária, mas a classificação é executada em SELECT .

@Catcall diz isso sobre o assunto da ordem de armazenamento (índice clusterizado) e a ordem de saída

Muitas pessoas acreditam que um índice clusterizado garante uma ordem de classificação na saída. Mas não é isso que ele faz; garante uma ordem de armazenamento em disco. Veja, por exemplo, esta postagem no blog .

Li a postagem do blog de Hugo Kornelis e entendo que um índice não garante que o sql server leia os registros em uma ordem específica. No entanto, tenho dificuldade em aceitar que não posso assumir isso para o meu cenário.

CREATE TABLE [dbo].[SensorValues](
  [DeviceId] [int] NOT NULL,
  [SensorId] [int] NOT NULL,
  [SensorValue] [int] NOT NULL,
  [Date] [int] NOT NULL,
CONSTRAINT [PK_SensorValues] PRIMARY KEY CLUSTERED 
(
  [DeviceId] ASC,
  [SensorId] ASC,
  [Date] DESC
) WITH (
    FILLFACTOR=75,
    DATA_COMPRESSION = PAGE,
    PAD_INDEX = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    SORT_IN_TEMPDB = OFF,
    IGNORE_DUP_KEY = OFF,
    ONLINE = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON)
  ON [MyPartitioningScheme]([Date])

Minha consulta original era esta:

SELECT TOP 1 SensorValue
  FROM SensorValues
  WHERE SensorId = 53
    AND DeviceId = 3819
    AND Date < 1339225010
  ORDER BY Date DESC

Mas sugiro que também poderia usar este (leia abaixo minha explicação):

SELECT TOP 1 SensorValue
  FROM SensorValues
  WHERE SensorId = 53
    AND DeviceId = 3819
    AND Date < 1339225010

Como você pode ver, as linhas da minha tabela são pequenas (16 bytes) e tenho apenas um índice, um clusterizado. No meu cenário, a tabela consiste em 100.000.000 registros neste momento (e isso provavelmente aumentará dez vezes).

Quando o servidor de banco de dados consulta esta tabela, ele tem duas maneiras de encontrar minhas linhas: busca a chave primária e, assim, lê e retorna meus valores em desc. ordem de Data, ou tem que fazer uma varredura completa da tabela. Minha conclusão é que uma varredura completa da tabela em todos esses registros será muito lenta e, portanto, o servidor de banco de dados sempre buscará a tabela por meio de sua chave primária e, assim, retornará os valores classificados porDate DESC

Estou armazenando os dados do sensor em uma tabela SensorValues ​​. A tabela e a chave primária são as seguintes:

CREATE TABLE [dbo].[SensorValues](
  [DeviceId] [int] NOT NULL,
  [SensorId] [int] NOT NULL,
  [SensorValue] [int] NOT NULL,
  [Date] [int] NOT NULL,
CONSTRAINT [PK_SensorValues] PRIMARY KEY CLUSTERED 
(
  [DeviceId] ASC,
  [SensorId] ASC,
  [Date] DESC
) WITH (
    FILLFACTOR=75,
    DATA_COMPRESSION = PAGE,
    PAD_INDEX = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    SORT_IN_TEMPDB = OFF,
    IGNORE_DUP_KEY = OFF,
    ONLINE = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON)
  ON [MyPartitioningScheme]([Date])

No entanto, quando seleciono o valor do sensor válido para um tempo específico, o plano de execução me diz que está fazendo uma classificação. Por que é que?

Eu teria pensado que, como armazeno os valores classificados pela coluna Data, a classificação não ocorreria. Ou é porque o índice não é classificado apenas pela coluna Date, ou seja, não pode assumir que o conjunto de resultados está classificado?

SELECT TOP 1 SensorValue
  FROM SensorValues
  WHERE SensorId = 53
    AND DeviceId = 3819
    AND Date < 1339225010
  ORDER BY Date DESC

Editar: posso fazer isso em vez disso?

Como a tabela está ordenada DeviceId, SensorId, Date e eu faço um SELECT especificando apenas um DeviceId e um SensorId , o conjunto de saída já deve estar ordenado por Date DESC . Então, eu me pergunto se a seguinte pergunta produziria o mesmo resultado em todos os casos?

SELECT TOP 1 SensorValue
  FROM SensorValues
  WHERE SensorId = 53
    AND DeviceId = 3819
    AND Date < 1339225010

De acordo com @Catcall abaixo, a ordem de classificação não é igual à ordem de armazenamento. Ou seja, não podemos assumir que os valores retornados já estão em uma ordem de classificação.

Editar: tentei esta solução CROSS APPLY, sem sorte

@Martin Smith sugeriu que eu tentasse OUTER APPLY meu resultado nas partições. Encontrei uma postagem no blog ( Índices não agrupados alinhados na tabela particionada ) descrevendo esse problema semelhante e tentei a solução um tanto semelhante à sugerida por Smith. No entanto, sem sorte aqui, o tempo de execução está no mesmo nível da minha solução original.

WITH Boundaries(boundary_id)
AS
(
  SELECT boundary_id
  FROM sys.partition_functions pf
  JOIN sys.partition_range_values prf ON pf.function_id = prf.function_id
  WHERE pf.name = 'PF'
  AND prf.value <= 1339225010
  UNION ALL
  SELECT max(boundary_id) + 1
  FROM sys.partition_functions pf
  JOIN sys.partition_range_values prf ON pf.function_id = prf.function_id
  WHERE pf.name = 'PF'
  AND prf.value <= 1339225010
),
Top1(SensorValue)
AS
(
  SELECT TOP 1 d.SensorValue
  FROM Boundaries b
  CROSS APPLY
  (
    SELECT TOP 1 SensorValue
      FROM SensorValues
      WHERE  SensorId = 53
        AND DeviceId = 3819
        AND "Date" < 1339225010
        AND $Partition.PF(Date) = b.boundary_id
        ORDER BY Date DESC
  ) d
  ORDER BY d.Date DESC
)
SELECT SensorValue
FROM Top1