Alocyte's questions

com referência a: Discos Gerenciados do Azure

SQL Server 2019; Windows 2016

C: OS mapeia para 128 GB (S10 @ 60mb/s).

Até agora tudo bem. Tenho drives para Data, Index, TempDB, Log e Backups Locais.

Dados, índice, padrão de disco TempDB: leitura/gravação; Backups de log e locais: (principalmente) gravações sequenciais.

Agora aqui fica duvidoso:

Prefiro ter vários discos do Azure (por exemplo, 1 x P10 @ 100 mb/s [128 GB], 3 x P20 @ 150 mb/s [512 GB])

OU eu vou maior (com maior taxa de transferência) e armazeno tudo no mesmo disco (por exemplo, P40 @250mb/s [2 TB])?

Isso permite que o SO seja apresentado com uma unidade de dados de 512 GB e uma unidade de índice de 512 GB e uma unidade TempDB de 128 GB. Log SQL e Backup de 512 GB.

Esta pergunta fala sobre o SQL Server acessando arquivos de banco de dados através do sistema operacional e a infraestrutura virtual expondo discos virtuais ...

Existe uma capacidade de 250 mb/s para cada encadeamento direcionado ao disco P40? Se eu tiver esse arquivo VHD de 2 TB alocado como 4 discos lógicos no sistema operacional da VM... ele teria mais desempenho do que 3 discos de camada inferior alocados como unidades individuais?

Requisito simples: exclua arquivos no caminho remoto com mais de 20 dias.

PS C:\Users\Administrator> Get-ChildItem `
    -Path \\FileServer\SQLBackups\SQLServer\ `
    -File -Recurse -Force | 
  Where-Object {
    ($_.LastWriteTime -lt (Get-Date).AddDays(-20))
} | Remove-Item

O comando acima é bem-sucedido (backticks de vaidade e quebras de linha adicionados para legibilidade).

O trabalho relevante do SQL 2016 (backticks e quebras de linha não adicionados, mas o texto do comando é o mesmo)

@subsystem=N'PowerShell',
@command=N'Get-ChildItem -Path \\FileServer\SQLBackups\SQLServer\ -File -Recurse -Force | Where-Object {($_.LastWriteTime -lt (Get-Date).AddDays(-20))}|Remove-Item', 
@proxy_name=N'XPProxy'

As informações de erro retornadas pelo PowerShell são:

'Não foi encontrado um parâmetro que corresponda ao nome do parâmetro 'Arquivo'.

Sem problemas de permissões (consulte proxy_name); Versão do SQL Powershell:

Major  Minor  Build  Revision

-----  -----  -----  --------
4      0      -1     -1      

SQL @@VERSION:

Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64) 28 de outubro de 2016 18:17:30 Copyright (c) Microsoft Corporation Standard Edition (64 bits) no Windows Server 2012 R2 Standard 6.3 (Build 9600: ) (Hipervisor)

Abaixo está a lista de parâmetros real da Get-ChildItemfunção . Por que o SQL não pode usar o -Fileparâmetro?

Get-ChildItem 
   [[-Path] <String[]>] 
   [[-Filter] <String>] 
   [-Attributes {ReadOnly | Hidden | System | Directory | Archive | Device | Normal | Temporary | SparseFile | ReparsePoint | Compressed | Offline | NotContentIndexed | Encrypted | IntegrityStream | NoScrubData}] 
   [-Depth <UInt32>] 
   [-Directory] 
   [-Exclude <String[]>] 
   [-File] 
   [-Force] 
   [-Hidden] 
   [-Include <String[]>] 
   [-Name] 
   [-ReadOnly] 
   [-Recurse] 
   [-System] 
   [-UseTransaction] 
   [<CommonParameters>]

Desempenho de wrt de ambiguidade SAN LUN

O MSDN diz para dividir as operações de leitura/gravação em uma unidade, gravações sequenciais em outra unidade. (O grupo de arquivos PRIMARY normalmente usa dados com atividade de leitura, atualização e gravação, enquanto o grupo de arquivos LOG é principalmente de gravação sequencial) A documentação é anterior às redes de área de armazenamento.

Eu recebo que o armazenamento de agrupamento para atividade de leitura/gravação otimizaria as cabeças no disco - minimizaria o thrashing do disco, tipo de coisa ... muitos discos?

O "gargalo" de E/S é então o controlador de E/S na SAN e controla o fluxo de dados dos dados em grupos de discos distribuídos? Ou seja, LUN1 exposto ao SO Server1 como 2 unidades de 500 GB, cada uma dividida em 2 unidades lógicas D:, E:; F:,G: de 250 GB cada: Um banco de dados com uma tabela de 750 GB criada em um grupo de arquivos de 4 arquivos (D:\f1.mdf, E:\f2.ndf, F:\f3.ndf, G:\f4.ndf ) terá paralelismo em todas as unidades lógicas (driver de arquivo do SO otimizado?) e o SQL pode usar mais threads de manipulador de arquivos? mas todo o IO ainda passa pelo controlador LUN com um máximo de 1 GB/s.

Ou a separação do DISK está implícita em divisões de nível de LUN? Significando LUN1 250GB, LUN2 250GB, LUN3 250, LUN4 250... cada um exposto ao SO como uma unidade física separada, mapeado para uma unidade lógica cada, e a MESMA divisão do GRUPO DE ARQUIVOS SOMENTE perceberia o benefício da taxa de transferência otimizada de IO? Acho que minha pergunta depende se o controlador de armazenamento SAN pode sustentar uma taxa de transferência de 1 GB/s em cada LUN.

Ou significa um controlador de armazenamento para cada LUN?

Alguém mais experiente pode aconselhar se a pegadinha de alinhar o striping ao tamanho do bloco NTFS é participante desta discussão? Blocos de tamanho de alocação do SO NTFS... oh! e o tamanho do pacote de rede - isso também é um fator de influência em termos de transferência de dados para o cliente? IPv6 vs IPv4 - isso também é um fator?

Gostaria de saber como calcular a distribuição dos dados consumidos por arquivo em um grupo de arquivos, voltando ao índice (HEAP, CLUSTERED, NONCLUSTERED) que o armazena. Minha intenção é definir qual I/O vai para onde no disco.

Chego ao data_space_idnível de sys.indexes, mostrando as páginas usadas, alocadas; e data_space_idtamanho de sys.filegroups. Portanto, chego onde o algoritmo ponderado (por proporção de espaço livre?) Para armazenar dados em arquivos dentro do grupo de arquivos entra em vigor. Eu posso juntar-me a sys.database_filesusar data_space_id.

De sys.dm_allocation_units (juntado a índices por object_Ide index_Id) obtenho partition_ID; juntando com sys.dm_partitionsavisa a contagem de linhas, páginas usadas e alocadas, permitindo um cálculo para mostrar também o que está livre por partição. Não é possível obter a partição para arquivo...?

Eu tenho uma consulta que distribuo DATA para FILE com base na proporção de páginas usadas por arquivo em um grupo de arquivos, aplicando essa proporção aos dados de índice armazenados no grupo de arquivos ao qual os arquivos pertencem.

Existe uma maneira melhor de detalhar os dados da tabela/índice para a alocação no nível do arquivo? (Medir em vez de calcular?)

Para indid = 0 ou indid = 1, dpages é a contagem de páginas de dados usadas.
Para indid > 1, dpages é a contagem de páginas de índice usadas.

Para indid = 0 ou indid = 1, usado é a contagem do total de páginas usadas para todos os dados de índice e tabela.
Para indid > 1, used é a contagem de páginas usadas para o índice.

Para indid = 0 ou indid = 1, reservado é a contagem de páginas alocadas para todos os índices e dados da tabela.
Para indid > 1, reservado é a contagem de páginas alocadas para o índice.

SQL:

Select  T.Name TableName,
        ISNULL(SI.Name, SI.type_desc) IndexName, 
    SI.index_id, 
    SI.type_desc, 
    SI.data_space_id,
    S.rows Rows,
    S.rowmodctr,
    PIx.avg_fragmentation_in_percent,
    PIx.fragment_count,
    PIx.avg_fragment_size_in_pages,
    CASE SI.Index_ID
    WHEN 0 THEN S.dpages
    WHEN 1 THEN S.dpages
    END DataPages,
    CASE 
    WHEN SI.index_id > 1
        THEN S.dpages
    END IndexPages,
    FileUsageRatio,
    S.dpages*FileUsageRatio RationedDataPagesToFile,
    physical_name,
    CASE SI.Index_ID
    WHEN 0 THEN S.Used 
    WHEN 1 THEN S.Used
    END UsedTableDataPages,
    CASE 
    WHEN SI.index_id > 1
        THEN S.Used
    END UsedIndexPages,
    CASE SI.Index_ID
    WHEN 0 THEN S.reserved 
    WHEN 1 THEN S.reserved
    END ReservedTableDataPages,
    CASE 
    WHEN SI.index_id > 1
        THEN S.Reserved
    END ReservedIndexPages,
    FG.name FileGroupName,
    FG_SpaceUsage.FG_AllocatePages,
    FG_SpaceUsage.FG_UsedPages,
    FG_SpaceUsage.FG_FreePages,
    FG.type_desc FileGroup_Type_desc,
    OIx.singleton_lookup_count,
    OIx.range_scan_count,
    OIx.page_io_latch_wait_count,
    OIx.page_io_latch_wait_in_ms,
    OIx.page_latch_wait_count,
    OIx.page_latch_wait_in_ms,
    OIx.row_lock_count,
    OIx.row_lock_wait_count,
    OIx.row_lock_wait_in_ms,
    OIx.page_lock_count,
    OIx.page_lock_wait_count,
    OIx.page_lock_wait_in_ms
from SYS.tables T
JOIN sys.indexes SI
ON T.Object_ID = SI.Object_ID
JOIN sys.filegroups FG
ON FG.Data_Space_ID = SI.Data_Space_ID
JOIN sys.partitions P
ON P.index_id = SI.index_id
AND P.object_id = SI.object_id
JOIN sys.allocation_units AU
ON AU.allocation_unit_id = P.partition_id
JOIN sys.dm_db_index_operational_stats(db_id(),NULL,NULL,NULL) OIx
ON OIx.index_id = P.index_id
AND OIx.object_id = P.object_id
JOIN sys.dm_db_index_physical_stats(db_id(),NULL,NULL,NULL,NULL) PIx
ON PIx.index_id = P.index_id
AND PIx.object_id = P.object_id
JOIN sys.sysindexes S
ON S.IndID = SI.Index_ID
AND S.ID = SI.Object_ID
JOIN (
SELECT Data_Space_ID, SUM(SIZE) FG_AllocatePages, SUM(UsedPages) FG_UsedPages,   SUM   (SIZE) - SUM(UsedPages) FG_FreePages
FROM SYS.database_files DBF
    CROSS APPLY (   SELECT FILEPROPERTY(DBF.name, 'SpaceUsed') AS UsedPages) PagesUsed
GROUP BY DBF.data_space_id
) FG_SpaceUsage
ON FG_SpaceUsage.data_space_id = FG.data_space_id
JOIN 
(
 SELECT CTE.data_space_id, CTE.file_ID, CTE.physical_name,      CTE.UsedPages*1.0/AGGREGATED.Total_UsedPages FileUsageRatio
 FROM (
 SELECT data_space_Id, File_ID, Physical_Name, Size, PagesUsed.UsedPages, Size-     PagesUsed.UsedPages FreePages
 FROM sys.database_Files DBF
 CROSS APPLY (  SELECT FILEPROPERTY(DBF.name, 'SpaceUsed') AS UsedPages) PagesUsed
 ) CTE
JOIN
    (SELECT Data_Space_ID, SUM(UsedPages) Total_UsedPages
     FROM (
            SELECT data_space_Id, File_ID, Physical_Name, Size, PagesUsed.UsedPages, Size-PagesUsed.UsedPages FreePages
            FROM sys.database_Files DBF
            CROSS APPLY (   SELECT FILEPROPERTY(DBF.name, 'SpaceUsed') AS UsedPages) PagesUsed
            ) CTE
     GROUP BY Data_Space_ID
     ) AGGREGATED
ON CTE.data_space_id = AGGREGATED.data_space_id
) DataUsageRatio
ON DataUsageRatio.data_space_id = FG.data_space_id

ORDER BY TableName, Index_ID