我对 Azure SQL 数据库的 WaitStats 分析显示 WRITELOG 和 HADR_SYNC_COMMIT 作为计数器的最高等待。这是一个 Premium 250 eDTU 资源。
脚本
SELECT TOP (10) wait_type,
CAST (([wait_time_ms] / 1000.0) AS DECIMAL (16, 2)) AS [WaitS],
CAST (100.0 * [wait_time_ms] / SUM([wait_time_ms]) OVER () AS DECIMAL (16, 2)) AS [Percentage]
FROM sys.dm_db_wait_stats
ORDER BY [Percentage] DESC;
我在 AZure SQL 数据库中找不到很多关于如何解决这个问题的信息。
任何帮助表示赞赏。
谢谢
我试图了解 SQL Server 在将记录插入堆表时的行为。根据关于 sql Server的博客文章，第 2 段“如果页面大小超过页面大小的 20%（8,060 字节 * 0.2 = 1,612 字节），SQL Server 将不会向页面添加新行。 ”
如果我正确理解了该语句，那么如果该表有一条大小为 2025 字节的记录，则第二条记录应该放在另一页上。但是，我的测试不同意
设置脚本
环境：SQL 2019 x64 开发者版。
create table dbo.Heap(id INT, Val varchar(8000) not null);
GO
insert into dbo.heap(ID, val) values (1,replicate('*',2010));
insert into dbo.heap(ID, val) values (2,replicate('*',2010));
insert into dbo.heap(ID, val) values (3,REPLICATE('*',2010));
insert into dbo.heap(ID, val) values (4,REPLICATE('*',2010));
并运行以下代码
SELECT PARSENAME(REPLACE(REPLACE(REPLACE(CAST(sys.fn_PhysLocFormatter (%%physloc%%) AS varchar),')',''),'(',''),':','.'),2)PageNo,
sys.fn_PhysLocFormatter (%%physloc%%) AS [Location],* FROM heap ORDER BY [ID]
上述 SELECT 返回以下结果，与 20% page size 语句不符。
我尝试在插入后查看 DBCC PAGE 结果，但 DBCC PAGE 结果并没有增加插入记录的大小。
设置脚本
DROP TABLE IF EXISTS heap
go
create table dbo.Heap(id INT, Val varchar(8000) not null);
GO
insert into dbo.heap(ID, val) values (1,replicate('*',2010));
go
dbcc traceon(3604);
dbcc page
(
'demodb' -- Database name
,1 -- File ID
,416 -- Page ID
,1 -- Output mode: 3 - display page header and row details
);
记录大小仅为数据页的 25%，但 PFS 显示 50% 已满，为什么？
发布第二个插入并查看 PAGE
insert into dbo.heap(ID, val) values (2,replicate('*',2010));
dbcc page
(
'demodb' -- Database name
,1 -- File ID
,416 -- Page ID
,1 -- Output mode: 3 - display page header and row details
);
我试图了解 SQL Server 如何从聚集索引访问数据。我的理解是，当表有聚集索引时，SQL 应该能够使用 seek 谓词搜索保存记录的单个页面。
但是，当执行查询时，我的测试显示它会加载更多数据页。
CREATE TABLE t2(id INT IDENTITY PRIMARY KEY CLUSTERED,col2 VARCHAR(500),col3 VARCHAR(500));
INSERT INTO [dbo].[t2]([col2],[col3])
SELECT TOP 10010 REPLICATE('z',490),REPLICATE('*',490)
FROM sys.all_columns c1,
sys.all_columns c2
以下查询
select *
from sys.dm_db_index_physical_stats(db_id(),object_id(N'dbo.t2'),DEFAULT,null,'DETAILED');
将输出显示为
然后我清除了缓存
CHECKPOINT
GO
DBCC DROPCLEANBUFFERS
SELECT并在一行中运行以下搜索
SELECT [fplc].*,[t2].[col3] FROM [dbo].[t2] AS [t2]
CROSS APPLY sys.[fn_PhysLocCracker](%%physloc%%) AS [fplc]
WHERE id=4582
上面的查询告诉我记录位于页面 1061
我使用下面的代码检查已将多少页加载到缓冲区中以获取我的 SELECT 的结果
SELECT buffers.* FROM sys.dm_os_buffer_descriptors buffers
INNER JOIN sys.allocation_units AS au
ON au.[allocation_unit_id] = buffers.[allocation_unit_id]
INNER JOIN sys.partitions AS p
ON au.[container_id] = p.[partition_id]
INNER JOIN sys.indexes AS i
ON i.[index_id] = p.[index_id] AND p.[object_id] = i.[object_id]
WHERE p.[object_id] > 100
and [database_id] = DB_ID () AND i.[object_id]=OBJECT_ID('t2')
ORDER BY [page_level] desc
为什么 SQL 正在加载所有标记为绿色的页面？页面是否由 READ AHEAD 加载？
Windows PowerShell Desired State Configuration (DSC) 新手在这里。MOF 文件生成失败并显示：
PSDesiredStateConfiguration\Node : The argument is null or empty. Provide an argument that is not null or empty, and then try the command again. At line:13 char:5 + Node localhost + ~~~~ + CategoryInfo : MetadataError: (:) [PSDesiredStateConfiguration\node], ParentContainsErrorRecordException + FullyQualifiedErrorId : ArgumentIsNull,PSDesiredStateConfiguration\node Compilation errors occurred while processing configuration 'SQLConfig'. Please review the errors reported in error stream and modify your configuration code appropriately. At C:\windows\system32\WindowsPowerShell\v1.0\Modules\PSDesiredStateConfiguration\PSDesiredStateConfiguration.psm1:3917 char:5 + throw $ErrorRecord + ~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (SQLConfig:String) [], InvalidOperationException + FullyQualifiedErrorId : FailToProcessConfiguration
Configuration SQLConfig
{
param(
[Parameter(Mandatory =$true)][string[]]$serviceConfig,
[Parameter(Mandatory =$true)][string]$DataDrive,
[Parameter(Mandatory =$true)][string]$LogDrive
)
Import-DscResource -ModuleName SqlServerDsc
Import-DscResource -ModuleName PSDesiredStateConfiguration
Node localhost
{
WindowsFeature Net35
{
Name = 'NET-Framework-Core'
Ensure = 'Present'
}
WindowsFeature Net45
{
Name = 'NET-Framework-45-Core'
Ensure = 'Present'
}
WindowsFeature Cluster
{
Name = 'RSAT-Clustering'
Ensure = 'Present'
}
File datadrive
{
Type = 'Directory'
DestinationPath = $DataDrive
Ensure ='Present'
}
File logdrive
{
Type = 'Directory'
DestinationPath = $LogDrive
Ensure ='Present'
}
SqlDatabaseDefaultLocation dataPath
{
InstanceName = 'MSSQLSERVER'
Path = $DataDrive
ServerName = 'localhost'
Type = 'Data'
DependsOn = '[File]datadrive'
}
SqlDatabaseDefaultLocation logPath
{
InstanceName = 'MSSQLSERVER'
Path = $LogDrive
ServerName = 'localhost'
Type = 'Log'
DependsOn = '[File]logdrive'
}
foreach ($service in $serviceConfig) {
ServiceSet $service
{
Name = $service.ServiceName
State = $service.State
StartupType = $service.StartupType
Ensure = $service.Ensure
}
}
}
}
#region create configuration data
$serviceConfig=(
@{ServiceName='MSSQLSERVER';State='Running';StartupType='Automatic';Ensure='Present'},
@{ServiceName='SQLSERVERAGENT';State='Running';StartupType='Automatic';Ensure='Present'},
@{ServiceName='SQLBrowser';State='Ignore';StartupType='Disabled';Ensure='Present'}
)
SQLConfig -serviceConfig $serviceConfig -DataDrive "F:\Data" -LogDrive "H:\Log" -OutputPath "C:\dump"
我们正在 Azure VM 中构建一个新的 SQL Server 2017 永远在线的集群。VM的数据盘布局如下
TempDb : 2 Azure Disk Read Cache
TempDBLog : 1 Azure Disk No Cache
UserDB : 3 Azure Disk Read Cache
UserDBLog : 2 Azure Disk No Cache
我的计划是创建一个多存储池，即带有 2 个 TempDB 磁盘的 TempDB 存储池，带有 3 个 User db Azure 磁盘的 UserDB 存储池。
但是，根据Microsoft关于 Azure VM 中 SQL Server 的文档：
如果您使用磁盘条带化技术，例如存储空间，您可以通过两个池来实现最佳性能，一个用于日志文件，另一个用于数据文件。但是，如果您计划使用 SQL Server 故障转移群集实例 (FCI)，则必须配置一个池。
这适用于 Always-On 还是仅适用于共享存储 FCI。请指导我，请分享好的文章/文档以供参考。
谢谢
希望有人能帮助我了解SQL Server 如何使用stats 来估计记录数。
测试脚本
USE [tempdb]
GO
CREATE TABLE t1
(
a INT NOT NULL,
b INT NOT NULL,
c INT CHECK (c between 1 and 50),
CONSTRAINT pk_a primary key(a)
);
GO
INSERT INTO t1(a,b,c)
SELECT number, number%1000+1, number%50+1
FROM master..spt_values
WHERE type = 'P' AND number BETWEEN 1 and 1000;
GO
CREATE STATISTICS s_b ON t1(b);
CREATE STATISTICS s_c ON t1(c);
GO
示例查询
DECLARE @c INT=300
SELECT * FROM t1 WHERE b>@c
SELECT * FROM t1 WHERE b>300
查询 1
估计行数 300
查询 2
估计行数 700
问题
我正在尝试学习表分区。但是，我很难理解为什么 SQL Server 会执行一个简单查询的阶段。
/* --------------------------------------------------
-- Create helper function GetNums by Itzik Ben-Gan
-- http://sqlmag.com/sql-server/virtual-auxiliary-table-numbers
-- GetNums is used to insert test data
-------------------------------------------------- */
-- Drop helper function if it already exists
IF OBJECT_ID('GetNums') IS NOT NULL
DROP FUNCTION GetNums;
GO
-- Create helper function
CREATE FUNCTION GetNums(@n AS BIGINT) RETURNS TABLE AS RETURN
WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
L4 AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
L5 AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)
SELECT TOP (@n) n FROM Nums ORDER BY n;
GO
/* ------------------------------------------------------------
-- Create example Partitioned Table (Heap)
-- The Partition Column is a DATE column
-- The Partition Function is RANGE RIGHT
-- The Partition Scheme maps all partitions to [PRIMARY]
------------------------------------------------------------ */
-- Drop objects if they already exist
IF EXISTS (SELECT * FROM sys.tables WHERE name = N'Sales')
DROP TABLE Sales;
IF EXISTS (SELECT * FROM sys.partition_schemes WHERE name = N'psSales')
DROP PARTITION SCHEME psSales;
IF EXISTS (SELECT * FROM sys.partition_functions WHERE name = N'pfSales')
DROP PARTITION FUNCTION pfSales;
-- Create the Partition Function
CREATE PARTITION FUNCTION pfSales (DATE)
AS RANGE right FOR VALUES
('2013-01-01', '2014-01-01', '2015-01-01');
-- Create the Partition Scheme
CREATE PARTITION SCHEME psSales
AS PARTITION pfSales
ALL TO ([Primary]);
-- Create the Partitioned Table (Heap) on the Partition Scheme
CREATE TABLE Sales
(
SalesDate DATE constraint ck_date check(SalesDate<'2016-01-01'),
Quantity INT
) ON psSales(SalesDate);
-- Insert test data
INSERT INTO Sales(SalesDate, Quantity)
SELECT DATEADD(DAY,dates.n-1,'2012-01-01') AS SalesDate, qty.n AS Quantity
FROM GetNums(DATEDIFF(DD,'2012-01-01','2016-01-01')) dates
CROSS JOIN GetNums(1000) AS qty;
查询
select count(1) from sales where SalesDate>'20160101'
请看下面的执行计划。
问题
非常感谢
为什么一个 SQL Server 表不能有多个标识列？
CREATE TABLE t(id INT IDENTITY, id2 INT IDENTITY)
消息 2744，级别 16，状态 2，第 5 行为
表“t”指定了多个标识列。
每个表只允许一个标识列。
我知道我们可以使用计算列来解决它。
根据产品文档，一个表不能有多个标识列。但为什么？其背后的真正原因是什么？