Alocyte's questions

参考：Azure 托管磁盘

SQL Server 2019；视窗 2016

C：操作系统映射到 128 GB (S10 @ 60mb/sec)。

到目前为止，一切都很好。我有用于数据、索引、临时数据库、日志和本地备份的驱动器。

数据、索引、TempDB 磁盘模式：读/写；日志和本地备份：（大部分）顺序写入。

现在这里变得不确定：

我是否更喜欢拥有多个 Azure 磁盘（例如 1 x P10 @ 100mb/s [128 GB]、3 x P20 @150mb/s [512 GB]）

或者我应该做得更大（吞吐量更高）并将所有内容存储在同一个磁盘上（例如 P40 @250mb/s [2 TB]）？

这允许为操作系统提供 512GB 的数据驱动器、512GB 的索引驱动器和 128GB 的​​ TempDB 驱动器。512 GB 的 SQL 日志和备份。

这个问题涉及通过操作系统访问数据库文件的 SQL Server，以及暴露虚拟磁盘的虚拟基础架构......

针对 P40 磁盘的每个线程是否有 250mb/s 的容量？如果我在 VM 的操作系统中将此 2TB VHD 文件分配为 4 个逻辑磁盘......它会比分配为单个驱动器的 3 个较低层磁盘的性能更高吗？

简单的要求：删除远程路径上超过 20 天的文件。

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

上面的命令成功（为了可读性添加了虚反引号和换行符）。

相关的SQL 2016 Job（反引号和换行符没有加，但是命令文本是一样的）

@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'

PowerShell返回的错误信息是：

'找不到与参数名称'文件'匹配的参数。

没有权限问题（参考 proxy_name）；SQL Powershell 版本：

Major  Minor  Build  Revision

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

SQL @@VERSION：

Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64) Oct 28 2016 18:17:30 版权所有 (c) Microsoft Corporation Standard Edition (64-bit) o​​n Windows Server 2012 R2 Standard 6.3 (Build 9600:) （管理程序）

下面是Get-ChildItem函数的实际参数列表。为什么 SQL 不能使用-File参数？

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>]

SAN LUN 歧义 wrt 性能

MSDN 说将读/写操作拆分到一个驱动器中，顺序写入另一个驱动器。（PRIMARY 文件组通常使用具有读取、更新、写入活动的数据，而 LOG 文件组主要是顺序写入）文档早于存储区域网络。

我知道将存储分组到读/写活动将优化磁盘上的磁头 - 最大限度地减少磁盘抖动，类似的事情......但是当操作系统上的“磁盘”分配给从派生的存储池时如何处理许多磁盘？

那么IO的“瓶颈”就是SAN上的IO控制器，它控制来自条带化磁盘组数据的数据流？意思是，LUN1 作为 2 个 500GB 驱动器暴露给 Server1 操作系统，每个驱动器分为 2 个逻辑驱动器 D:、E:；F:,G: 每个 250GB：一个数据库有一个 750GB 的表，在一个包含 4 个文件的文件组上创建（D:\f1.mdf、E:\f2.ndf、F:\f3.ndf、G:\f4.ndf ) 将在所有逻辑驱动器中具有并行性（优化操作系统文件驱动程序？）并且 SQL 可以使用更多文件处理程序线程？但所有 IO 仍然来自 LUN 控制器，最大速度为 1GB/秒。

或者 DISK 分离是否暗示为 LUN 级别拆分？意思是 LUN1 250GB、LUN2 250GB、LUN3 250、LUN4 250...每个都作为单独的物理驱动器暴露给操作系统，每个都映射到一个逻辑驱动器，并且 SAME FILEGROUP 拆分只会实现 IO 优化吞吐量的好处？我想我的问题取决于 SAN 存储控制器是否可以在每个 LUN 上维持 1GB/秒的吞吐量。

或者它是否意味着每个 LUN 都有一个存储控制器？

更有经验的人可以建议将条带对齐到 NTFS 块大小的问题是否参与了这个讨论？操作系统 NTFS 分配大小块...哦！和网络数据包大小——这也是影响客户端数据吞吐量的因素吗？IPv6 与 IPv4 - 这也是一个因素吗？

我想知道如何计算文件组中每个文件消耗的数据分布，返回到存储它的索引（HEAP、CLUSTERED、NONCLUSTERED）。我的意图是定义哪个 I/O 在磁盘上的哪个位置。

我data_space_id从 开始sys.indexes，显示已使用、已分配的页面；和data_space_id大小从sys.filegroups。因此，我到达了用于将数据存储到文件组中的文件的加权（按可用空间比率？）算法生效的位置。我可以加入sys.database_files使用data_space_id.

从s （通过和sys.dm_allocation_unit连接到索引）我得到；加入建议行数、使用和分配的页面，允许计算还显示每个分区的空闲内容。无法分区到文件...？object_Idindex_Idpartition_IDsys.dm_partitions

我有一个查询，我根据文件组中每个文件的已用页面比率将 DATA 分配给 FILE，并将该比率应用于存储在文件所属文件组中的索引数据。

是否有更好的方法将表/索引数据向下钻取到文件级分配？（测量而不是计算？）

对于 indid = 0 或 indid = 1，dpages 是使用的数据页数。
对于 indid > 1，dpages 是使用的索引页数。

对于 indid = 0 或 indid = 1，used 是用于所有索引和表数据的总页数。
对于 indid > 1，used 是用于索引的页数。

对于 indid = 0 或 indid = 1，reserved 是为所有索引和表数据分配的页数。
对于 indid > 1，reserved 是为索引分配的页数。

查询语句：

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