TL/DR；

查看数据库中的第一个 DCM 页时（哪些文档记录了哪些范围已被修改，以便 DIFF 备份过程不必扫描整个数据库以查找更改，但可以跳转到范围来扫描已更改的页面）下一个 DCM 页的位置记录在哪里？

我原本以为它会被记录在m_nextPage页眉的属性中。但检查后发现情况似乎并非如此。

查找 DCM 页面

我正在指导我们的学徒差异备份包含什么以及 DBMS 如何记录各个页面以及合并 DCM 页面中的更改。

参考： 页面和范围体系结构指南(Microsoft Learn | SQL)

然后我开始处理一个非常大的数据库并启动以下命令：

DBCC TRACEON (3604);
DBCC PAGE ( 6, 1, 0, 3 );       -- File Header 

这产生了以下输出：

PAGE: (1:0)


BUFFER:


BUF @0x000002CC7AC80780

bpage = 0x000002CC2D11E000          bhash = 0x0000000000000000          bpageno = (1:0)
bdbid = 6                           breferences = 0                     bcputicks = 0
bsampleCount = 0                    bUse1 = 48700                       bstat = 0x9
blog = 0x15a                        bnext = 0x0000000000000000          bDirtyContext = 0x0000000000000000
bstat2 = 0x0                        

PAGE HEADER:


Page @0x000002CC2D11E000

m_pageId = (1:0)                    m_headerVersion = 1                 m_type = 15
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x208
m_objId (AllocUnitId.idObj) = 99    m_indexId (AllocUnitId.idInd) = 0   Metadata: AllocUnitId = 6488064
Metadata: PartitionId = 0           Metadata: IndexId = 0               Metadata: ObjectId = 99
m_prevPage = (0:0)                  m_nextPage = (0:0)                  pminlen = 0
m_slotCnt = 1                       m_freeCnt = 7019                    m_freeData = 3321
m_reservedCnt = 0                   m_lsn = (2501578:318516:1)          m_xactReserved = 0
m_xdesId = (0:0)                    m_ghostRecCnt = 0                   m_tornBits = 1668847350
DB Frag ID = 1                      

Allocation Status

GAM (1:2) = ALLOCATED               SGAM (1:3) = NOT ALLOCATED          PFS (1:1) = 0x44 ALLOCATED 100_PCT_FULL
DIFF (1:6) = CHANGED                ML (1:7) = NOT MIN_LOGGED 

阅读输出后，我注意到底部附近的信息，并（正确地）假设 DCM 页面位于第 6 页（嗯，实际上是第 7 页，但是......）。

让我们来看看：

DBCC PAGE ( 6, 1, 6, 0 );       -- 1. DCM Page

是的，这似乎是第一个 DCM 页面：

PAGE: (1:6)


BUFFER:


BUF @0x000002CC7AC80300

bpage = 0x000002CC2D112000          bhash = 0x0000000000000000          bpageno = (1:6)
bdbid = 6                           breferences = 1                     bcputicks = 0
bsampleCount = 0                    bUse1 = 48800                       bstat = 0x9
blog = 0x7a7a7a7a                   bnext = 0x0000000000000000          bDirtyContext = 0x0000000000000000
bstat2 = 0x0                        

PAGE HEADER:


Page @0x000002CC2D112000

m_pageId = (1:6)                    m_headerVersion = 1                 m_type = 16
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x200
m_objId (AllocUnitId.idObj) = 99    m_indexId (AllocUnitId.idInd) = 0   Metadata: AllocUnitId = 6488064
Metadata: PartitionId = 0           Metadata: IndexId = 0               Metadata: ObjectId = 99
m_prevPage = (0:0)                  m_nextPage = (0:0)                  pminlen = 90
m_slotCnt = 2                       m_freeCnt = 6                       m_freeData = 8182
m_reservedCnt = 0                   m_lsn = (2501605:3132423:42)        m_xactReserved = 0
m_xdesId = (0:0)                    m_ghostRecCnt = 0                   m_tornBits = -431908416
DB Frag ID = 1                      

Allocation Status

GAM (1:2) = ALLOCATED               SGAM (1:3) = NOT ALLOCATED          PFS (1:1) = 0x44 ALLOCATED 100_PCT_FULL
DIFF (1:6) = CHANGED                ML (1:7) = NOT MIN_LOGGED           

DIFF_MAP: Header @0x000000B60C7FA064 Slot 0, Offset 96

status = 0x0                        

DIFF_MAP: Extent Alloc Status @0x000000B60C7FA0C2

(1:0)        - (1:24)       =     CHANGED                                
(1:32)       - (1:40)       = NOT CHANGED                                
(1:48)       -              =     CHANGED                                
(1:56)       - (1:80)       = NOT CHANGED                                
(1:88)       -              =     CHANGED                                
(1:96)       - (1:104)      = NOT CHANGED                                
(1:112)      - (1:128)      =     CHANGED                                
(1:136)      - (1:152)      = NOT CHANGED                                
(1:160)      - (1:168)      =     CHANGED                                
(1:176)      -              = NOT CHANGED                                
(1:184)      - (1:192)      =     CHANGED                                
(1:200)      - (1:208)      = NOT CHANGED   
...

但是，查找下一个 DCM 页面需要反复试验。我在已有的 6 页基础上添加了 4'096'000 页（这是错误的，应该只有 512'000 页。感谢Martin Smith指出这一点），但得到的页码略高于下一个 DCM 页面。下一个 DCM 页面可以在标题信息中找到。

GAM (1:4089856) = ALLOCATED         SGAM (1:4089857) = NOT ALLOCATED    PFS (1:4092528) = 0x0   0_PCT_FULL
DIFF (1:4089862) = NOT CHANGED      ML (1:4089863) = NOT MIN_LOGGED    

下一个 DCM 页面未记录在标题信息中，因此我假设它应该位于第一个 DCM 页面的m_nextPage末尾。bit

问题

1. 我的假设是否正确，即 SQL Server 将下一个 DCM 页检测为第一个 DCM 页的最后一位？或者 SQL Server 是否有其他方法来查找第二、第三、第四等 DCM 页？

2. m_nextPage在第一个 DCM 页的标题信息中记录下一个 DCM 页不是更有效率吗？

介绍

最近在更新应用程序时，我收到以下错误消息：

Msg 5074 Level 16 State 1 Line 1
The statistics 'BreakingStuff' is dependent on column 'TaskText'.
Msg 4922 Level 16 State 9 Line 1
ALTER TABLE ALTER COLUMN TaskText failed because one or more objects access this column.

^{基于分析的错误消息示例}

这是针对表中的列手动创建的统计信息。实际上，有多个手动创建的统计数据引用了相关列。

解决方法

删除表的统计信息，运行更新，然后重新创建统计信息后，一切都运行良好。

分析

我首先对 db<>fiddle 进行了一个简单的模拟，看看是否可以重现该问题。经过一番摆弄（双关语），我发现只有当我减小列的大小时才会触发错误消息。

例如varchar(50)-->varchar (40)

这是db<>fiddle。

我在 Microsoft Learn 上找到的唯一警告是：

1. 当现有数据转换为新类型时，修改已包含数据的列的数据类型可能会导致数据永久丢失。此外，依赖于已修改列的代码和应用程​​序可能会失败。其中包括查询、视图、存储过程、用户定义函数和客户端应用程序。请注意，这些故障会级联。例如，调用依赖于已修改列的用户定义函数的存储过程可能会失败。在对列进行任何更改之前，请仔细考虑。

   ^{参考： 修改列(Microsoft Learn | SQL)}

我知道减少列大小可能会导致数据丢失，但事实并非如此，因为数据都短于varchar(40).

问题

1. 为什么由于手动创建的统计信息而减少列的大小会导致错误？
2. 为什么增加列的大小不会导致手动创建的统计信息出现错误？

我的数据库中有以下表格。

姓氏表

CREATE TABLE [dbo].[LastNames](
    [LastNameID] [int] IDENTITY(1,1) NOT NULL,
    [LastName] [varchar](50) NOT NULL
) ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX [CIX_LastNames_LastName] ON [dbo].[LastNames]
(
    [LastName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

名字表

CREATE TABLE [dbo].[FirstNames](
    [FirstNameID] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [varchar](50) NOT NULL
) ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX [CIX_FirstNames_FirstName] ON [dbo].[FirstNames]
(
    [FirstName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

人员匿名表

CREATE TABLE [dbo].[PersonsAnon](
    [PersonID] [int] IDENTITY(1,1) NOT NULL,
    [LastNameID] [int] NOT NULL,
    [FirstNameID] [int] NOT NULL,
    [Info1] [bit] NULL,
    [Info2] [char](1) NULL,
    [Info3] [nchar](50) NULL,
    [AdressID] [int] NULL
) ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX [CIX_PersonsAnon_PersonID_LastNameID_FirstNameID] ON [dbo].[PersonsAnon]
(
    [PersonID] ASC,
    [LastNameID] ASC,
    [FirstNameID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [NIX_PersonsAnon_LastNameID_FirstNameID] ON [dbo].[PersonsAnon]
(
    [LastNameID] ASC,
    [FirstNameID] ASC
)
INCLUDE([PersonID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

数据生成

对于那些拥有 AdventureWorks2014 数据库的人，我使用以下语句来填充表：

INSERT INTO dbo.LastNames (LastName) SELECT DISTINCT app.LastName FROM AdventureWorks2016.Person.Person AS app

INSERT INTO dbo.FirstNames (FirstName) SELECT DISTINCT app.FirstName FROM AdventureWorks2016.Person.Person AS app

INSERT INTO dbo.PersonsAnon (LastNameID, FirstNameID)
SELECT ln.LastNameID, fn.FirstNameID FROM LastNames ln CROSS APPLY FirstNames fn 

该声明

使用以下语句来查看将创建什么样的执行计划：

SELECT pa.PersonID,
       fn.FirstName,
       ln.LastName,
       pa.Info2
FROM   PersonsAnon AS pa
       JOIN LastNames AS ln
            ON  pa.LastNameID = ln.LastNameID
       JOIN FirstNames AS fn
            ON  pa.FirstNameID = fn.FirstNameID
WHERE  ln.LastName LIKE 'Pet%'
       AND fn.FirstName LIKE 'John%'
ORDER BY
       ln.LastName,
       fn.FirstName;

查询执行计划

粘贴计划

问题

为什么即使没有 JOIN 谓词，也要在 LastNames 和 FirstNames 之间执行 JOIN？是什么导致 QO JOIN 这两个表？

图解情况

它看起来是这样的：

细节显示：

已查阅的文章

• 为什么说没有连接谓词？
• SQL Server 中“无连接谓词”到底是什么意思？
• 我应该对这个 NO JOIN PREDICATE 警告感到震惊吗？
• 为什么说没有连接谓词？

我最近正在查询我们的内部数据库库存工具以获取服务器、实例和数据库的列表，并向每个服务器、实例和数据库添加相应的状态。

关系图

Server ˂-- 1 : n --˃ Instance ˂-- 1 : n --˃ Database
   ˄                    ˄                      ˄
   |                    |                      |
   |                  1 : 1                    |
   |                    |                      |
   |                    ˅                      |
   +-- 1 : 1 --˃     Status        ˂-- 1 : 1 --+

读作：
...一个服务器可以有多个实例
...一个实例可以有多个数据库
...一个服务器、一个实例和一个数据库可以有一个状态

设置

状态表

CREATE TABLE [Status]
(
  StatusID int,
  StatusName char(20),
  );

状态数据

INSERT INTO [Status] (StatusID, StatusName)
VALUES
(1,'Productive'),
(2,'Prod ACC'),
(3,'Prod APP'),
(4,'Test'),
(5,'Test ACC'),
(6,'Test APP'),
(7,'OFFLINE'),
(8,'Reserved'),
(9,'Decommisioned');

服务器表

CREATE TABLE [Server]
(
  ServerID int,
  ServerName char(20),
  ServerStatusID int
  );

服务器数据

INSERT INTO [Server] (ServerID, ServerName, ServerStatusID)
VALUES
(1,'FirstServer',1),
(2,'SecondServer',2),
(3,'ThirdServer',5),
(4,'FourthServer',8),
(5,'FifthServer',8);

实例表

CREATE TABLE [Instance]
(
  InstanceID int,
  ServerID int,
  InstanceName char(30),
  InstanceStatusID int
  );

实例数据

INSERT INTO [Instance] 
  (InstanceID, ServerID, InstanceName, InstanceStatusID)
VALUES
(1,1,'GENERAL',1),
(2,1,'TAXES',1),
(3,2,'GENERAL',9),
(4,2,'SOCIAL',2),
(5,3,'GENERAL',5),
(6,3,'FBI',8),
(7,5,'COMINGSOON',8);

数据库表

CREATE TABLE [Database]
(
  DatabaseID int,
  InstanceID int,
  DatabaseName char(30),
  DatabaseStatusID int
  );

数据库数据

INSERT INTO [Database]
(DatabaseID, InstanceID, DatabaseName, DatabaseStatusID)
VALUES
(1,1,'master',1),
(2,1,'model',1),
(3,1,'msdb',1),
(4,1,'UserDB1',1),
(5,2,'master',1),
(6,2,'model',1),
(7,2,'msdb',1),
(8,2,'TaxesDB',1),
(9,4,'master',2),
(10,4,'model',2),
(11,4,'msdb',2),
(12,4,'HealthCareDB',2),
(13,5,'master',5),
(14,5,'model',5),
(15,5,'msdb',5),
(16,5,'GeneralUserDB',5),
(17,6,'master',8),
(18,6,'model',8),
(19,6,'msdb',8),
(20,6,'CriminalDB',8);

不涉及状态表的 SELECT 语句

初始 SELECT 语句仅涉及连接三个表：服务器、实例、数据库，如下所示：

-- Simple SELECT to get all information on Servers, Instances and Databases
-- The status of the server, instance or database is not returned
SELECT 
  ServerName, 
  InstanceName,
  DatabaseName 
  FROM [Server] as srv
    LEFT JOIN [Instance] as ins
      ON srv.ServerID = ins.ServerID
    LEFT JOIN [Database] as dbs
      ON ins.InstanceID = dbs.InstanceID;

1. 声明的结果

请注意...

• 有一台没有实例和数据库的服务器
• 有一个实例没有数据库

涉及 Status 表的 SELECT 语句

在下一个语句中，我决定将状态添加到每个元素（服务器、实例、数据库），并JOIN使用表编辑每个表，Status如下所示：

-- Advanced SELECT to get all information on Servers, Instances and Databases 
-- including their status
SELECT 
  ServerName, 
  srvst.StatusName,
  InstanceName,
  insst.StatusName,
  DatabaseName,
  dbsst.StatusName
  FROM [Server] as srv
    JOIN [Status] as srvst
      ON srv.ServerStatusID = srvst.StatusID
    LEFT JOIN [Instance] as ins
      ON srv.ServerID = ins.ServerID
    JOIN [Status] as insst
      ON ins.InstanceStatusID = insst.StatusID
    LEFT JOIN [Database] as dbs
      ON ins.InstanceID = dbs.InstanceID
    JOIN [Status] as dbsst
      ON dbs.DatabaseStatusID = dbsst.StatusID
  ;

2. 声明结果

令我惊讶的是，没有实例和数据库的服务器以及有实例但没有数据库的服务器不再列出：

调查结果/解决方案

通过反复试验的方法检查了各种选项后，我发现必须将表JOIN上的Status更改为 a ，LEFT JOIN以允许该语句显示没有实例或数据库的服务器，并显示没有数据库的实例：

-- Advanced SELECT to get all information on Servers, Instances and Databases 
-- including their status
SELECT 
  ServerName, 
  srvst.StatusName,
  InstanceName,
  insst.StatusName,
  DatabaseName,
  dbsst.StatusName
  FROM [Server] as srv
    LEFT JOIN [Status] as srvst
      ON srv.ServerStatusID = srvst.StatusID
    LEFT JOIN [Instance] as ins
      ON srv.ServerID = ins.ServerID
    LEFT JOIN [Status] as insst
      ON ins.InstanceStatusID = insst.StatusID
    LEFT JOIN [Database] as dbs
      ON ins.InstanceID = dbs.InstanceID
    LEFT JOIN [Status] as dbsst
      ON dbs.DatabaseStatusID = dbsst.StatusID;

3. 声明的结果

参考资料

这里有一个db<>fiddle的链接来重现我的发现。

问题

为什么 SQL Server 需要在表LEFT JOIN中Status显示不存在的子项以及查询来显示这些项？

情况

当使用包含一组已定义列的 SELECT 语句查询数据库时，大约 21 秒内会收到结果。

, *如果在定义的列集列表末尾有一个额外的星号 ( )，则查询将在 2 秒内返回结果。

查询执行计划

执行计划有很大不同。

您可以使用 PasteThePlan 中的链接找到好的实际查询执行计划和坏的实际查询执行计划。

列列表中包含 , * 的语句（末尾）

            SELECT    -- DISTINCT -- 27.04.2020
                'SchuelerKlasse' AS EcoQuery,
                VX_PERSON.PER_MAN_ID, VX_PERSON.PER_ID, VX_PERSON.PER_NAME, VX_PERSON.PER_VORNAME, VX_PERSON.PER_LB_PER_ID, 
                VX_PERSON.PER_GESCHLECHT, VX_PERSON.PER_GEBURTSDATUM, VX_PERSON.PER_TELP, VX_PERSON.PER_MAILP, VX_PERSON.PER_NATP, VX_PERSON.PER_VERSICHERTENNUMMER, VX_PERSON.PER_LAND,
                VX_ADRESSE.ADR_STRASSE, VX_ADRESSE.ADR_PLZ, VX_ADRESSE.ADR_ORT,
                VX_KLASSE.KL_CODE, VX_KLASSE.KL_BEZEICHNUNG,
                VX_KLASSEABSCHNITTSCHUELER.KAS_ANMELDE_STATUS, 
                VX_KLASSEABSCHNITTSCHUELER.KAS_ANMELDETYP, VX_KLASSEABSCHNITTSCHUELER.KAS_ABSCHNITTSNR,
                VX_KLASSE_ZEITRAUM.KLZ_IS_ABSCHLUSSKLASSE, VX_KLASSE_ZEITRAUM.KLZ_ZR_NR,
                VX_ZEITRAUM.ZR_BEGINN, VX_ZEITRAUM.ZR_ENDE
                ,'' AS FA_CODE
                ,'' AS FA_BEZ_STP, '' AS FA_BEZ_STP_LANG
                , '' AS EcoOrig_FA_CODE, '' AS EcoOrig_FA_BEZ_STP, '' AS EcoOrig_FA_BEZ_STP_LANG
                , VX_ANGEBOT.ANG_BEGINN
            
 ,* 

            FROM 
                ECOLST.VX_KLASSE_ZEITRAUM, 
                ECOLST.VX_PERSON, 
                ECOLST.VX_KLASSE, 
                ECOLST.VX_KLASSEABSCHNITTSCHUELER, 
                ECOLST.VX_ZEITRAUM, 
                ECOLST.VX_ADRESSE 
                , ECOSYS.T_KLASSE
                , ECOLST.VX_ANGEBOT

            WHERE  
                    VX_KLASSE_ZEITRAUM.klz_kl_id = VX_KLASSE.kl_id 
                AND VX_KLASSE_ZEITRAUM.klz_zr_id = VX_ZEITRAUM.zr_id 
                AND VX_KLASSEABSCHNITTSCHUELER.kas_ang_id = VX_KLASSE.kl_ang_id 
                AND VX_KLASSEABSCHNITTSCHUELER.kas_zr_id = VX_ZEITRAUM.zr_id 
                AND VX_KLASSEABSCHNITTSCHUELER.kas_per_id = VX_PERSON.per_id 
                AND VX_KLASSEABSCHNITTSCHUELER.kas_kl_id = VX_KLASSE.kl_id 
                AND VX_KLASSEABSCHNITTSCHUELER.KAS_ANMELDE_STATUS LIKE 'De%'  -- LIKE 'Definitiv%'
                AND VX_PERSON.per_id = VX_ADRESSE.adr_per_id 
                AND VX_PERSON.per_man_id = VX_KLASSE.kl_man_id
                AND VX_KLASSE.KL_ANG_ID = VX_ANGEBOT.ANG_ID
                AND VX_KLASSE.KL_MAN_ID = 15 
                AND VX_KLASSE.KL_ID = T_KLASSE.KL_ID
                AND T_KLASSE.KL_STATUS_ID = 491   -- d.h. TS_CODE.CODE_UP_BEZEICHNUNG = 'AKTIV'
            

                AND VX_KLASSE.KL_KLASSENTYP_ID IN (742,743,1235,1926,2075,2076,2078,2079,2080,2081,2086,2103,2118,2119,2122,2152,2252,2308,2416)
        

                AND VX_PERSON.PER_NP = 1   -- Natürliche Person
                AND LEN(LTRIM(RTRIM(VX_PERSON.PER_VORNAME))) > 0        -- TRIM() kann erst ab SQL Server 2017 verwendet werden
                AND LEN(LTRIM(RTRIM(VX_PERSON.PER_NAME))) > 0           -- TRIM() kann erst ab SQL Server 2017 verwendet werden
        
 AND VX_ZEITRAUM.zr_beginn <= CONVERT(DATETIME, '20.05.2023', 104) 
 AND VX_ZEITRAUM.zr_ende   >= CONVERT(DATETIME, '14.02.2023', 104) 
 AND VX_PERSON.per_man_id IN ( 15 ) 

                --AND VX_Person.PER_ID IN  (233777,233779)
        

问题

*一般建议在定义列列表时不要使用，但在我的例子中，, *在末尾添加到列列表，可以显着加快查询速度。（从 21 秒减少到 2 秒）

实际执行计划中没有缺失索引建议。

, *我认为这与在语句中使用时返回的特定列有关，这些列可能包含在查询优化器认为有用的索引中，但我不确定如何查明这些列。

1. 为了说服 SQL Server 运行不包含, *列列表的执行不佳的语句，使用与包含列列表中的附加项的性能语句类似的计划，我必须创建哪些索引？, *

2. 我是否必须分析良好执行计划中使用的所有索引并创建缩减索引（省略某些列）以便查询优化器考虑对语句使用类似的良好, *执行计划而不需要额外的？

根据建议尝试解决方案

1. OPTION (MIN_GRANT_PERCENT = 10, MAX_GRANT_PERCENT = 15)

   应用上述解决方案仅提供了大约 1 小时的临时性能提升。之后查询恢复到错误的执行计划。我不知道为什么...

2. 数据库兼容级别

   ,*使用以下命令将数据库的兼容级别向下更改为 110 (SQL Server 2012)，导致上述查询的性能不断提高，而无需在列列表中添加。

   USE [master]
   GO
   ALTER DATABASE [ECOWEBBSP] SET COMPATIBILITY_LEVEL = 110
   GO
   

   兼容级别为 110 的查询执行计划表明，查询优化器在检索数据时选择了一种完全不同的方法，并且在分配正确的内存量 (110 MB) 方面没有任何问题。

跟进问题

将兼容级别设置为 110 是我唯一的选择吗？

额外反馈

Erik 的回答中提到的Ominous Function是由视图中的fi_kla_is_abschlussklasse列触发的。基础表在检索数据时调用标量值函数。该函数本身返回 0 或 1，具体取决于学生是在毕业班 (1) 还是不在毕业班 (0)。VX_KLASSE_ZEITRAUM.KLZ_IS_ABSCHLUSSKLASSEECOLST.VX_KLASSE_ZEITRAUM

但是，在兼容级别设置为 110 (SQL Server 2012) 的情况下运行时，该函数似乎对查询持续时间的影响不大。有关详细信息，请参阅兼容级别为 110 的查询执行计划。

我目前正在查询sys.dm_db_log_info()DMV 以从数据库中检索 VLF，以确定何时可以缩小、重组和减少 TLOG 文件中的碎片（10 MB VLF）数量。

这样做的原因是，如果事务位于 TLOG 文件的末尾并导致活动的 VLF，则无法缩小 TLOG 文件。类似的情况，如果一个活动事务位于 TLOG 文件的中间，那么您不能缩小到该 VLF。

当前声明

我目前有这个语句来检索MAX(vlf_begin_offset)记录、MIN(vlf_begin_offset)记录和任何具有活动的记录vlf_active = 1：

SELECT ddli.vlf_begin_offset,
       ddli.vlf_sequence_number,
       ddli.vlf_active,
       ddli.vlf_status,
       ddli.vlf_first_lsn
FROM   sys.dm_db_log_info(DB_ID()) AS ddli
WHERE  ddli.vlf_begin_offset = (
           SELECT MIN(ddli2.vlf_begin_offset)
           FROM   sys.dm_db_log_info(DB_ID()) AS ddli2
       )
       OR  ddli.vlf_active = 1
       OR  ddli.vlf_begin_offset = (
               SELECT MAX(ddli3.vlf_begin_offset)
               FROM   sys.dm_db_log_info(DB_ID()) AS ddli3
           )
ORDER BY
       ddli.vlf_begin_offset ASC

返回所有记录后，结果集如下所示：

+------------------+---------------------+------------+------------+------------------------+
| vlf_begin_offset | vlf_sequence_number | vlf_active | vlf_status |     vlf_first_lsn      |
+------------------+---------------------+------------+------------+------------------------+
|             8192 |              253978 |          0 |          0 | 00000000:00000000:0000 |
|           262144 |              253979 |          0 |          0 | 00000000:00000000:0000 |
|           516096 |              253980 |          0 |          0 | 00000000:00000000:0000 |
|           770048 |              253977 |          0 |          0 | 00000000:00000000:0000 |
|          1048576 |              253981 |          0 |          0 | 00000000:00000000:0000 |
|         17563648 |              253982 |          0 |          0 | 00000000:00000000:0000 |
|         34078720 |              253983 |          1 |          2 | 0003e01f:00000010:0001 |
|         50593792 |              253970 |          0 |          0 | 00000000:00000000:0000 |
|         67108864 |              253971 |          0 |          0 | 00000000:00000000:0000 |
|         75497472 |              253972 |          0 |          0 | 00000000:00000000:0000 |
|         83886080 |              253973 |          0 |          0 | 00000000:00000000:0000 |
|         92274688 |              253974 |          0 |          0 | 00000000:00000000:0000 |
|        100663296 |              253975 |          0 |          0 | 00000000:00000000:0000 |
|        109051904 |              253976 |          0 |          0 | 00000000:00000000:0000 |
|        117440512 |                   0 |          0 |          0 | 00000000:00000000:0000 |
|        236978176 |                   0 |          0 |          0 | 00000000:00000000:0000 |
|        356515840 |                   0 |          0 |          0 | 00000000:00000000:0000 |
|        476053504 |                   0 |          0 |          0 | 00000000:00000000:0000 |
|        595591168 |                   0 |          0 |          0 | 00000000:00000000:0000 |
|        715128832 |                   0 |          0 |          0 | 00000000:00000000:0000 |
|        834666496 |                   0 |          0 |          0 | 00000000:00000000:0000 |
|        954204160 |                   0 |          0 |          0 | 00000000:00000000:0000 |
+------------------+---------------------+------------+------------+------------------------+

使用我当前的脚本，我得到：

+------------------+---------------------+------------+------------+------------------------+
| vlf_begin_offset | vlf_sequence_number | vlf_active | vlf_status |     vlf_first_lsn      |
+------------------+---------------------+------------+------------+------------------------+
|             8192 |              253978 |          0 |          0 | 00000000:00000000:0000 |
|         34078720 |              253983 |          1 |          2 | 0003e01f:00000010:0001 |
|        954204160 |                   0 |          0 |          0 | 00000000:00000000:0000 |
+------------------+---------------------+------------+------------+------------------------+

附加信息

可以在不同的vlf_active = 1位置弹出。可以有多个岛屿vlf_active = 1。

问题

在任何活动的 vlf ( ) 之前有一个记录并在之后有一个记录会很好vlf_active = 1。

• 我怎样才能做到这一点？

    +------------------+---------------------+------------+------------+------------------------+
    | vlf_begin_offset | vlf_sequence_number | vlf_active | vlf_status |     vlf_first_lsn      |
    +------------------+---------------------+------------+------------+------------------------+
    |             8192 |              253978 |          0 |          0 | 00000000:00000000:0000 |
    |         17563648 |              253982 |          0 |          0 | 00000000:00000000:0000 |
    |         34078720 |              253983 |          1 |          2 | 0003e01f:00000010:0001 |
    |         50593792 |              253970 |          0 |          0 | 00000000:00000000:0000 |    
    |        954204160 |                   0 |          0 |          0 | 00000000:00000000:0000 |
    +------------------+---------------------+------------+------------+------------------------+
  

• 如果有数百条记录，那么在两者之间需要什么摘要记录？例如这样的：

    +------------------+---------------------+------------+------------+------------------------+
    | vlf_begin_offset | vlf_sequence_number | vlf_active | vlf_status |     vlf_first_lsn      |
    +------------------+---------------------+------------+------------+------------------------+
    |             8192 |              253978 |          0 |          0 | 00000000:00000000:0000 |
    |  (4 res clipped) |                     |            |            |                        |
    |         17563648 |              253982 |          0 |          0 | 00000000:00000000:0000 |
    |         34078720 |              253983 |          1 |          2 | 0003e01f:00000010:0001 |
    |         50593792 |              253970 |          0 |          0 | 00000000:00000000:0000 |    
    | (13 res clipped) |                     |            |            | 00000000:00000000:0000 |    
    |        954204160 |                   0 |          0 |          0 |                        |
    +------------------+---------------------+------------+------------+------------------------+
  

在为第一个实例分配特定 IP 地址时，我在配置多实例 SQL Server 时遇到了问题。

专用 IP 设置

我将单个实例配置为专用 IP 地址的步骤是：

1. 打开 SQL Server 配置管理器 (SSCM)

2. 向下导航到树：

   SQL Server Configuration Manager (local)
   +-- SQL Server Network Configuration
       +-- Protocols for <INSTANCENAME>
   

3. 然后在右窗格中双击TCP/ IP ，在选项卡协议中打开 TCP/IP 的属性

4. 然后我确保Enabled设置为yes并且Listen All的选项设置为No。

5. 我将选项卡切换到IP 地址并确保

   • 对于所有 IPv4 和 IPv6 地址，“已启用”设置为“否”，我要分配给此实例的 IP 地址除外。对于这个 IP 地址，我将设置设置为Yes。
   • 对于所有 IP 地址， TCP 动态端口都是空的。
   • 对于我要分配给此实例的 IP 地址，我分配TCP 端口 1433。

6. 然后我关闭所有窗口，SSCM 显示标准消息：

   ---------------------------
   Warning
   ---------------------------
   Any changes made will be saved; however, they will not take 
   effect until the service is stopped and restarted.
   ---------------------------
   OK   
   ---------------------------
   

7. 我重新启动特定实例，它通常会允许通过专用 IP 地址连接到我的实例。

8. 我测试实例正在侦听给定的 IP 地址和端口：

   c:\> netstat -abon
   

   TCP    10.58.212.112:1433     0.0.0.0:0              LISTENING         2172
   [sqlservr.exe]
   

^{以上结果来自工作服务器}

到目前为止，一切都很好。我已经无数次这样做了，从来没有遇到过任何问题。

案子

两天前，测试服务器上新配置的实例的 SQL Server 服务在为实例配置 10.58.194.5 地址后无法启动。我尝试了以下步骤来修复“损坏”的测试实例：

1. 修复共享工具
2. 修复实例
3. 卸载并重新安装 SQL Server
4. 运行 ProcMon 以找出问题所在。

但到目前为止没有任何帮助。

ERRORLOG 文件的相关输出如下：

2021-12-17 08:00:03.76 spid17s     Error: 26024, Severity: 16, State: 1.
2021-12-17 08:00:03.76 spid17s     Server failed to listen on 10.58.194.5 <ipv4> 1433. Error: 0x2741. 
                                   To proceed, notify your system administrator.
2021-12-17 08:00:03.76 spid17s     Error: 17182, Severity: 16, State: 1.
2021-12-17 08:00:03.76 spid17s     TDSSNIClient initialization failed with error 0x2741, status code 0xa. 
                                   Reason: Unable to initialize the TCP/IP listener. 
2021-12-17 08:00:03.76 spid17s     Error: 17182, Severity: 16, State: 1.
2021-12-17 08:00:03.76 spid17s     TDSSNIClient initialization failed with error 0x2741, status code 0x1. 
                                   Reason: Initialization failed with an infrastructure error. Check for previous errors. 
2021-12-17 08:00:03.76 spid17s     Error: 17826, Severity: 18, State: 3.
2021-12-17 08:00:03.76 spid17s     Could not start the network library because of an internal error in the network library. 
                                   To determine the cause, review the errors immediately preceding this one in the error log.
2021-12-17 08:00:03.76 spid17s     Error: 17120, Severity: 16, State: 1.
2021-12-17 08:00:03.76 spid17s     SQL Server could not spawn FRunCommunicationsManager thread. 
                                   Check the SQL Server error log and the operating system error log for information about possible related problems.

这是我第一次无法将 SQL Server 实例配置/设置为特定 IP 地址。

事件日志条目

尝试启动服务时（最新的优先），Windows 的应用程序事件日志中记录了以下错误：

Level | Date / Time         | Source               | Event ID
Error | 17.12.2021 11:00:45 | MSSQL$<INSTANCENAME> | 17120
Error | 17.12.2021 11:00:45 | MSSQL$<INSTANCENAME> | 17826
Error | 17.12.2021 11:00:45 | MSSQL$<INSTANCENAME> | 17182
Error | 17.12.2021 11:00:45 | MSSQL$<INSTANCENAME> | 17182
Error | 17.12.2021 11:00:45 | MSSQL$<INSTANCENAME> | 26024

这些事件 ID 类似于 ERRORLOG 中的信息。

系统事件日志中的其他条目是：

Level | Date / Time         | Source                  | Event ID
Error | 17.12.2021 11:00:45 | Service Control Manager | 7024 

详情如下：

The service "SQL Server (<INSTANCENAME>)" has been stopped with the following error: 
The requested address is invalid in this context.

XML 详细信息为：

- <Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
- <System>
  <Provider Name="Service Control Manager" Guid="{555908d1-a6d7-4695-8e1e-26931d2012f4}" EventSourceName="Service Control Manager" /> 
  <EventID Qualifiers="49152">7024</EventID> 
  <Version>0</Version> 
  <Level>2</Level> 
  <Task>0</Task> 
  <Opcode>0</Opcode> 
  <Keywords>0x8080000000000000</Keywords> 
  <TimeCreated SystemTime="2021-12-17T10:00:45.876740800Z" /> 
  <EventRecordID>15418</EventRecordID> 
  <Correlation /> 
  <Execution ProcessID="736" ThreadID="372" /> 
  <Channel>System</Channel> 
  <Computer>SERVER.DOMAIN.TLD</Computer> 
  <Security /> 
  </System>
- <EventData>
  <Data Name="param1">SQL Server (<INSTANCENAME>)</Data> 
  <Data Name="param2">%%10049</Data> 
  <Binary>4D005300530051004C00240041004C004C00470045004D00450049004E000000</Binary> 
  </EventData>
  </Event>

网络接口卡配置

测试服务器上只配置了一个网卡。它是一个虚拟的。名称为Ethernet0，类型为Ethernet Adapter for vmxnet3。

网络配置如下：

IP address:        10.58.194.4
Subnet mask:       255.255.255.192
Standard gateway:  10.58.194.2

我们用于 SQL Server 实例的其他 IP 地址是：

10.58.194.5 / 255.255.255.192
10.58.194.6 / 255.255.255.192
10.58.194.7 / 255.255.255.192
10.58.194.28 / 255.255.255.192
10.58.194.29 / 255.255.255.192

单个 NIC 配置为接受六个 IP 地址的连接。

问题

有人知道我该如何解决这个问题吗？

通过评论附加信息

如果我在 TCP/IP 配置中将Listen All选项设置为Yes，则实例将启动。但这不是我想要的。我无法使用别名或 IP 地址连接到实例。然后 sqlserver.exe 进程不再侦听端口 1433。

在考虑我观察到sqlserver.exe 进程不再侦听端口 1433之后，似乎服务器无法将 IP 地址绑定到我的第一个实例的端口 1433。

将Listen All配置为yes并将 IP 地址配置为具有 port1433后，sqlserver.exe不再侦听 port 的任何 IP 地址 ( 0.0.0.0) 1433。这就像端口号输入框的配置设置已损坏。SQL Server 错误日志显示：

2021-12-17 16:27:36.35 spid17s     Error: 26058, Severity: 16, State: 1.   
2021-12-17 16:27:36.35 spid17s     A TCP provider is enabled, but there are no TCP listening ports configured. 
                                   The server cannot accept TCP connections.`

旁注

我能够设置和配置同一天收到的生产服务器，以使用特定 IP 没有任何问题。唯一的区别可能是我收到的生产服务器的六个 IP 地址按如下顺序排列：

10.xx.xxx.111 / 255.255.255.0 -- server
10.xx.xxx.112 / 255.255.255.0 -- instance_1
10.xx.xxx.113 / 255.255.255.0 -- instance_2
10.xx.xxx.114 / 255.255.255.0 --...
10.xx.xxx.115 / 255.255.255.0
10.xx.xxx.116 / 255.255.255.0

概述

我有一个图片数据库，其中包含用相机拍摄的图片的目录信息。该数据库包含一个表，该表pictures有 256 列，其中包含有关已拍摄照片的信息。

一列Comments被格式化为ntext并包含 CR/LF。
还有另外 21 列已配置为ntext.

Tasks | Export Data...我使用SSMS 中的函数将数据库中的数据提取到一个平面文件中。导出的数据随后由外部合作伙伴传输到将在不久的将来使用的新系统。导出文件 (CSV) 包含大约 256 列，其中 21 列可能包含 CR/LF。

问题

Comments字段/列包含许多影响数据分析的CR /LF (SQL: CHAR(13), )。CHAR(10)

我尝试使用REPLACE(expression, value, value)来搜索 CR/LF 并将其替换为并正在考虑在使用SSMS@@的导出期间实现此功能。Export Data

但是，该REPLACE()函数返回一个

消息 8116，级别 16，状态 1，第 4 行
参数数据类型 ntext 对于替换函数的参数 1 无效。

...当我执行以下操作时：

SELECT 'Start *******************', REPLACE(Comment,'
','@@'), ID, '********************End' FROM dbo.pictures
WHERE Comment LIKE '%
%';

来自列注释的示例数据

发表以下声明：

SELECT Comment FROM dbo.Pictures
WHERE Comment like '%
%';

...将检索以下示例Comment记录：

Zwei Fotos von Arenenberg auf einer Seite einer englischen Zeitschrift.
Seite 148 der Zeitung "The Graphic" vom 4. August 1906 = News from Abroad.
"The last stage of all": the retreat for aged actors opened last week near Meaux, in France
1. General view of the home
2. M. Coquelin reciting in the open-air theater

The château of Arenenberg which has been presented by the Empress Eugénie to the canton of Thurgovie
3. View from the chateau [Arenenberg] over Lake Constance
4. The château of Arenenberg
The Empress Eugénie has presented to the Swiss Canton of Thurgovie the historical château of Arenenberg, where Napoleon III. passed several years of his youth. Queen Hortense, on the fall of the first Empire, fled to Switzerland, and in 1817 purchased the castle, which is delightfully situated on the shore of Lake / Constance. The gift includes a priceless collection of paintings, manuscripts, books, old furniture, and tapestries, among the mos important souvenirs being the camp bed of Napoleon III., and the carriage in which he left Sedan after his defeat. When the alterations are complete the château will be opened to the public.

5. The maiden voyage of the new Santos-Dumont flying machine
6. The room in Viborg where the dissolved Duma met
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          .

是的，有一个空行。我自己插入了句号以显示文本的长度。

常用表达

我导出数据，然后运行各种正则表达式以从数据中省略 CR/LF。因为该列Comment位于数据中间，所以我不得不尝试各种正则表达式字符串：

搜索字符串

([a-zA-Z0-9/,.@():;\s]+)(\r\n)([a-zA-Z0-9/,.@():;"\s]+)
([a-zA-Z0-9/,.@():;\s]+)(\r\n\r\n)([a-zA-Z0-9/,.@():;"\s]+)
([a-zA-Z0-9/,.@():;\s]+)(\r\n\r\n\r\n)([a-zA-Z0-9/,.@():;"\s]+)
([a-zA-Z0-9/,.@():;\s]+)(\r\n\r\n\r\n\r\n)([a-zA-Z0-9/,.@():;"\s]+)
([\w/,.@():;\s']+)(\r\n)([\w/,.@():;"\s]+)
(;")(\r\n)(";)
(;")(\r\n)([\w/,.@():;\s']+)
(\w")(\r\n)([\w/,.@():;\s']+)

代替

\1@@\3

这个解决方案不是很有效也不是很有效，因为它需要多次运行和数小时来整理 CSV 文件。

问题

如何在导出到 CSV 平面文件期间替换ntext列中的 CR/LF？@@有比REPLACE我可以使用的不同的选择吗？

限制/观察

• 尚未安装集成服务，因此这不是一个选项。
• ntext 字符串可以包含", 'and “, ”, ‘，’当然还有可选的«and »。
• 我已经使用语句分析了该ntext列中的数据，并收到一条记录包含5'562个字符的反馈。CommentSELECT MAX(DATALENGTH(Comment)) FROM Pictures;

我创建了一个db<>fiddle，其中包含表的基本定义和示例数据以及失败的REPLACE()语句。

问题

如何CAST(event_data as XML)使用以下语句将数据插入到临时表中新添加的列中：

INSERT INTO #Unique_Logins (event_data_xml) SELECT CAST(event_data AS XML) FROM #Unique_Logins

...没有收到以下错误消息：

Msg 515, Level 16, State 2, Line 36
Cannot insert the value NULL into column 'module_guid', table 'tempdb.dbo.#Unique_Logins______________________________________________________________________________________________________00000000000F'; column does not allow nulls. INSERT fails.
The statement has been terminated.

重现步骤

我正在尝试从导入的 XEL 文件中读取一些 XML。在这样做时，我必须创建一个新列来将数据nvarchar(max)从XML.

1. 让我们看看如何导入扩展的事件日志文件

sp_help fn_xe_file_target_read_file

基本信息

Name                        | Owner   | Type            | Created_datetime
----------------------------+---------+-----------------+------------------
fn_xe_file_target_read_file | sys     | inline function | 2014-02-20 20:48:46.370

内联函数的列

Column_name  | Type             | Computed | Length | Prec  | Scale | Nullable | TrimTrailingBlanks | FixedLenNullInSource | Collation
-------------+------------------+----------+--------+-------+-------+----------+--------------------+----------------------+------------------------------
module_guid  | uniqueidentifier | no       | 16     |       |       | no       | (n/a)              | (n/a)                | NULL
package_guid | uniqueidentifier | no       | 16     |       |       | no       | (n/a)              | (n/a)                | NULL
object_name  | nvarchar         | no       | 120    |       |       | no       | (n/a)              | (n/a)                | SQL_Latin1_General_CP1_CI_AS
event_data   | nvarchar         | no       | -1     |       |       | yes      | (n/a)              | (n/a)                | SQL_Latin1_General_CP1_CI_AS
file_name    | nvarchar         | no       | 520    |       |       | no       | (n/a)              | (n/a)                | SQL_Latin1_General_CP1_CI_AS
file_offset  | bigint           | no       | 8      | 19    | 0     | no       | (n/a)              | (n/a)                | NULL

内联函数的参数

Parameter_name     | Type     | Length | Prec | Scale | Param_order | Collation
-------------------+----------+--------+------+-------+-------------+----------------
@path              | nvarchar | 520    | 260  | NULL  | 1           | Latin1_General_CS_AS
@mdpath            | nvarchar | 520    | 260  | NULL  | 2           | Latin1_General_CS_AS
@initial_file_name | nvarchar | 520    | 260  | NULL  | 3           | Latin1_General_CS_AS
@initial_offset    | bigint   | 8      | 19   | 0     | 4           | NULL

2. 将 XEL 文件中的数据插入临时表 #Unique_Logins

select * into #Unique_Logins from sys.fn_xe_file_target_read_file(N'C:\temp\Unique_Logins_0_132175196428210000.xel',NULL,NULL,NULL)

注意：
我导入了一个2 GB XEL 文件，生成了一个24 GB 临时表！

3. 从临时表中选择，看看我们有哪些列

select TOP 2 * from #Unique_Logins

module_guid                          | package_guid                         | object_name | event_data                                                                                                                                                                                                                                                                                            | file_name                                      | file_offset
-------------------------------------+--------------------------------------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------+------------
CE79811F-1A80-40E1-8F5D-7445A3F375E7 | 655FD93F-3364-40D5-B2BA-330F7FFB6491 | login       | <event name="login" package="sqlserver" timestamp="2019-11-06T13:14:02.835Z">...<action name="database_name" package="sqlserver"><value><![CDATA[DatabaseName1]]></value></action>...<action name="server_principal_name" package="sqlserver"><value><![CDATA[DB_USER1]]></value></action>...</event> | C:\temp\Unique_Logins_0_132175196428210000.xel | 6656
CE79811F-1A80-40E1-8F5D-7445A3F375E7 | 655FD93F-3364-40D5-B2BA-330F7FFB6491 | login       | <event name="login" package="sqlserver" timestamp="2019-11-06T13:14:02.894Z">...<action name="database_name" package="sqlserver"><value><![CDATA[DatabaseName2]]></value></action>...<action name="server_principal_name" package="sqlserver"><value><![CDATA[DB_USER2]]></value></action>...</event> | C:\temp\Unique_Logins_0_132175196428210000.xel | 6656

现在数据似乎是 XML。让我们验证它是否已作为 XML 导入。

4.检索临时表属性

USE TEMPDB --caution; temporary table is in tempdb
GO
SELECT sc.name, 
       st.name, 
       st.max_length, 
       st.collation_name, 
       sc.column_id, 
       sc.collation_name, 
       sc.max_length, 
       sc.is_nullable, 
       sc.is_xml_document
FROM sys.columns AS sc JOIN sys.types AS st 
    ON sc.user_type_id = st.user_type_id WHERE sc.object_id = OBJECT_ID('#Unique_Logins')

name         | name             | max_length | collation_name       | column_id | collation_name       | max_length | is_nullable | is_xml_document
-------------+------------------+------------+----------------------+-----------+----------------------+------------+-------------+----------------
module_guid  | uniqueidentifier | 16         | NULL                 | 1         | NULL                 | 16         | 0           | 0
package_guid | uniqueidentifier | 16         | NULL                 | 2         | NULL                 | 16         | 0           | 0
object_name  | nvarchar         | 8000       | Latin1_General_CS_AS | 3         | Latin1_General_CS_AS | 120        | 0           | 0
event_data   | nvarchar         | 8000       | Latin1_General_CS_AS | 4         | Latin1_General_CS_AS | -1         | 1           | 0
file_name    | nvarchar         | 8000       | Latin1_General_CS_AS | 5         | Latin1_General_CS_AS | 520        | 0           | 0
file_offset  | bigint           | 8          | NULL                 | 6         | NULL                 | 8          | 0           | 0

列中的 XML 数据event_data已导入到nvarchar(max)列中。

5. 创建新列作为 XML 并从使用event_data_xml插入列数据event_dataCAST()

ALTER TABLE #Unique_Logins ADD event_data_xml XML
GO
INSERT INTO #Unique_Logins (event_data_xml) SELECT CAST(event_data AS XML) FROM #Unique_Logins

错误信息

Msg 515, Level 16, State 2, Line 36
Cannot insert the value NULL into column 'module_guid', table 'tempdb.dbo.#Unique_Logins______________________________________________________________________________________________________00000000000F'; column does not allow nulls. INSERT fails.
The statement has been terminated.

我看不出module_guid与 INSERT / SELECT 语句有什么关系。

我目前正在研究一个应用程序，该应用程序似乎针对它正在查询的数据库生成 99% 的即席查询计划。我可以通过运行以下语句来检索查询计划缓存中的对象摘要来验证这一点：

_{抱歉无法在 SE 编辑器中输入代码，因此截图}

^{参考：为临时工作负载规划缓存和优化(SQLSkills.com / K. Tripp)，稍作修改}

上述查询结果如下：

CacheType            Total Plans          Total MBs                               Avg Use Count Total MBs - USE Count 1                 Total Plans - USE Count 1
-------------------- -------------------- --------------------------------------- ------------- --------------------------------------- -------------------------
Adhoc                158997               5749.520042                             2             2936.355979                             126087
Prepared             1028                 97.875000                               695           46.187500                               576
Proc                 90                   69.523437                               39659         21.187500                               21
View                 522                  75.921875                               99            0.453125                                3
Rule                 4                    0.093750                                22            0.000000                                0
Trigger              1                    0.070312                                12            0.000000                                0    

在计划缓存中的 158'997 个临时查询中，有 126'087 个查询只执行了一次。

在对即席查询的进一步检查中，我发现有些查询甚至会生成多次。我使用以下查询检查了计划缓存，以检索相同的执行计划：

SELECT SUM(cplan.usecounts) AS [Unique Same Single Plans],
       qtext.text
FROM   sys.dm_exec_cached_plans AS cplan
       CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS qtext
             CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qplan
       JOIN sys.databases AS sdb
            ON  sdb.database_id = qplan.dbid
WHERE  1 = 1
       AND cplan.objtype = 'Adhoc'   -- <-- only Adhoc plans
       AND sdb.name = 'DATABASENAME' -- <-- for a certain database
       AND cplan.usecounts = 1       -- <-- with a usecounts of 1
GROUP BY
       qtext.text having sum(cplan.usecounts) > 1
ORDER BY
       1 DESC --,cplan.objtype, cplan.usecounts   

^{参考：不记得了。让我知道它是否最初是你的，我会归于它。}

这给了我一个即席查询列表，这些查询的查询计划与现有的相同查询计划相同，并且计划缓存中唯一相同的查询计划的总和。

正如您从编辑的 GUID 中看到的那样，有许多独特的即席查询计划已经被创建了多次。

为了证明我正在朝着正确的方向前进，我从上面获取了一个唯一计数为 3 的语句，并将该语句用作我的计划缓存摘要语句中的过滤器来检索语句和查询计划：

SELECT cplan.usecounts,
       qtext.text,
       qplan.query_plan
FROM   sys.dm_exec_cached_plans AS cplan
       CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS qtext
       CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qplan
       JOIN sys.databases AS sdb
            ON  sdb.database_id = qplan.dbid
WHERE  1 = 1
       AND cplan.objtype = 'Adhoc'
       AND sdb.name = 'DATABASENAME'
       AND qtext.text = 
           'SELECT description,id,name,osguid,profil FROM omitted WHERE osguid IN (SELECT osgroupguid FROM omitted WHERE osuserguid=''81C4B8_REMOVED_SOME_9DD2'')'
ORDER BY
       1 DESC

^{参考：不记得了。让我知道它是否最初是你的，我会归于它。}

这给了我一个独特的即席查询列表，这些查询已经创建并存储在计划缓存中：

现在上面屏幕截图中的数字表明一个查询已经被再次重用，因为它的计数为 3。但是，所有查询都是相同的。

现在从我到目前为止所读到的，我假设：

• 即席查询是在其（可能很短的）生命周期中第一次传递给 SQL Server 查询优化器的查询
• 没有参数的语句被认为是唯一的，并将导致在Adhoc计划缓存中创建一个条目
• 即席查询可能很简单，这会导致为每个语句创建单独的查询计划，即使它们是相同的

我同样意识到：

• 打开optimize for ad hoc workloads将导致缓存中查询计划的大小略有减少，用于仅使用一次的临时计划
• 就我而言，跑步ALTER DATABASE [DATABASENAME] SET PARAMETERIZATION FORCED可能是一个好主意，但是...
  • 有限制（见 BrentOzar 的文章）
  • 程序中的参数化会更好

问题

在阅读了所有文章和输入此问题时弹出的一些相关问题后，我有以下两个问题：

1. 在哪些情况下会重用非参数化、非平凡、即席查询计划？
2. 为什么相同语句有多个缓存查询计划？

我意识到我的问题是矛盾的，因为非参数化查询计划被认为是唯一的，但为什么一些非参数化即席查询计划仍然被再次重用？

回应@DenisRubashikin 的评论：

以 XML 格式保存“相同”查询的计划并比较文件，我认为可能存在一些差异（例如，在设置选项中）– Denis Rubashkin 29 分钟前

SET 选项是相同的。整个计划的唯一区别是在和<StatementSetOptions>部分之后的第二行。我在下面粘贴了两个相关部分：CompileTimeCompileCPU

查询计划1.xml

      <StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="false" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />
      <QueryPlan CachedPlanSize="32" CompileTime="4" CompileCPU="4" CompileMemory="472">

查询计划2.xml

      <StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="false" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />
      <QueryPlan CachedPlanSize="32" CompileTime="3" CompileCPU="3" CompileMemory="472">

未发现其他差异。

用于解决此问题的参考资料：

• 针对临时工作负载规划缓存和优化(SQLSkills.com)
• 查询处理架构指南(Microsoft SQL Docs)
• ALTER DATABASE SET 选项 (Transact-SQL) (Microsoft SQL Docs)
• 为什么一个查询的多个计划不好（BrentOzar.com）
• 强制参数化会出错吗？(BrentOzar.com)
• 闪电战结果：强制参数化(Brentoar.com)

是否可以按表格中的元素（如COLUMN LIKE='Value%'）分组PIVOT？我有一个表 [DBT].[Status] 包含各种状态（数据库、实例等），并且不想将所有 PROD 和 TEST 值作为单个值进行透视/查询，而是将它们分组。

例如，我将只有一列包含 和 的值，而不是列用于状态Prod、Prod ACC、 、 ..等。Prod APPName LIKE 'Prod%'Name LIKE 'Test%'

到目前为止我所拥有的：

表定义

CREATE TABLE [DBT].[Status](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Status] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY],
 CONSTRAINT [IX_Status] UNIQUE NONCLUSTERED 
(
    [Name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

GO

表值

INSERT INTO [DBT].[Status]
(
    -- ID -- this column value is auto-generated
    Name
)
VALUES
('Test ACC'),
('Test APP'),
('Test DBA'),
('Prod ACC'),
('Prod APP'),
('Prod DBA'),
('Prod'),
('Test'),
('Migrated'),
('Offline'),
('Reserved')

透视状态表

SELECT 'Database Status' AS [DB Status], 
[1] AS [Test ACC], [2] AS [Test APP], [3] AS [Test DBA], [4] AS [Prod ACC], [5] AS [Prod APP], [6] AS [Prod DBA], [7] AS [Prod], [8] AS [Test], [9] AS [Migrated], [10] AS [Offline], [11] AS [Reserved] 
FROM 
(
    SELECT ID, Name  FROM [DBT].[Status]
) AS Source
PIVOT
(
    COUNT(Name) FOR ID IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11])
) AS PivotTable

到目前为止的输出

DB Status       Test ACC    Test APP    Test DBA    Prod ACC    Prod APP    Prod DBA    Prod        Test        Migrated    Offline     Reserved
--------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
Database Status 1           1           1           1           1           1           1           1           1           1           1

db<>小提琴

到目前为止的dbfiddle 。

问题

我宁愿将它们分组，而不是为各种Test... 和值设置多行，类似于以下内容：Prod....

DB Status       | Test | Prod | Migrated | Offline | Reserved   
--------------- | ---- | ---- | -------- | ------- | --------
Database Status |    4 |    4 |        1 |       1 |        1

我不知道如何解决我的问题。（老实说，经过大量的反复试验，我昨天才掌握了 PIVOT）。

这个问题与我已经问过的如何在多个表上创建分组项目的总和/计数问题松散相关。表 [DBT].[Instance] 和 [DBT].[Database] 包含一个具有 [StatusID] 的列，它对应于我们现在正在查看的表。

简单的问题

SQL Server Quantum (4 ms) 如何与 Server OS Quantum (通常为 187.5 ms) 同步？

简单问题解释

在使用了 184 毫秒的 OS 时间片（对应于 46 个完整的 SQL 时间片）之后，OS 时间片有 3.5 毫秒的时间才能将计划移交给不同的进程。SQL 操作系统启动一个时间片（4 毫秒），3.5 毫秒后，操作系统时间片决定停止当前的 SQL 操作系统线程，该线程在产生调度之前还有 0.5 毫秒。现在会发生什么？

深入了解 OS Quantum

在接下来的几节中，我将写出到目前为止我发现的关于 OS 量子以及如何计算量子持续时间的内容。操作系统“量子”的持续时间基于“滴答”，“滴答”本身的持续时间基于“时钟间隔”，通常为 15.625000 毫秒。但是让我详细说明一下...

打钩

在博客文章Know Thy Tick中，作者 Jim 解释了时钟间隔（又名“滴答声”）的基础知识以及它们的用途。

当我读到“时钟间隔……对于大多数 x86 多处理器来说大约是 15 毫秒”之类的内容时，我不得不确定我的时钟或“滴答”间隔的值。幸运的是，我读到这句话的书，Windows Internals Fourth Edition 提供了帮助我解决痛苦的参考。... 上述书籍的作者 Mark Russinovich 慷慨地在他的网站上提供了实用程序ClockRes 。运行这个实用程序，我能够确定我的 x86 多处理器 PC 上的时钟间隔是 15.625000 毫秒。有趣，但我好奇的心想知道更多。

量子

文章的作者在他的第二篇文章中继续解释那...

当然，滴答间隔重要的真正原因是它影响线程调度。Windows 调度程序在允许具有相同优先级的另一个任务运行之前，给每个线程一个“量子”的执行时间。调度程序分配给线程的量程是滴答间隔的倍数。为特定线程选择的特定量子值有点超出我想在本文中讨论的范围。

好的，所以我知道量子是什么，但不知道量子会运行多长时间。

现在，让我们检查一下 XPe 中前台线程的默认量子值。在这种情况下，Windows 调度程序分配 18 或 6 个滴答间隔。（是的，要将量子转换为滴答间隔，必须除以 3....，但倍数的原因是允许调度程序能够“充电”线程以执行导致其挂起的操作。）

我们现在知道时钟间隔（滴答声）应该在 15.625000 毫秒左右，并且在默认时间片为 18 的 Windows 桌面操作系统上，这将导致 6 个滴答声或 93.750000 毫秒（18 / 3 * 15.625000 毫秒）。

在 Windows Server 操作系统上，默认量程是不同的。“处理器调度”设置设置为“后台服务”

^{可以通过“系统设置|高级（选项卡）|性能（部分）|设置...”找到此设置，这将打开“性能选项|高级（选项卡）|处理器调度”}

然后，默认量程设置为 36（背景）到 36（前景）。量子更大，因此更长。这是 Windows 桌面操作系统上 18（6 滴答）量子前台设置的 93.750000 毫秒的两倍，在为后台服务设置的服务器操作系统上约为 187.500000 毫秒。

观察/解释

当您在服务器或桌面上将设置从“后台服务”更改为“应用程序”时，注册表中的HKLM\SYSTEM\CurrentControlSet\Control\PriorityControl\Win32PrioritySeparation键将从 0x18 更改为 0x02。0x02 的默认量子值是多少？这可以在评论中找到：

值 0x02 表示“Short vs. Long”和“Variable vs. Fixed”字段是操作系统的默认值。

XPe 和 XP Pro 的这些字段的默认值是：Short 和 Variable，这与设置以下位相同：0x24。

将此值与 0x02 进行或运算得到 0x26，您可以在本文的表格中找到该值。

^{参考：对“掌握你的量子”的评论（MSDN 博客）}

解释同一篇文章中的量子设置的表格：

Win32PrioritySeparation   Foreground   Background
0x28, 0x29, 0x2A                  18           18
0x18, 0x19, 0x1A                  36           36
0x24                               6            6
0x25, 0x14                        12            6
0x26                              18            6
0x15                              24            6
0x16                              36            6

OS Quantum 的简短摘要

根据以上信息和文章引用，我们知道量程不是固定大小，而是源自系统属性中的操作系统设置。数量取决于Win32PrioritySeparation注册表中的设置，该设置通常对应于“系统属性”（“后台服务”或“应用程序”）中的设置之一。

操作系统级别的量子是

• 对于“应用程序”设置
  • 前台应用程序为 18（即 6 个滴答声）（93.75 毫秒）
  • 6（即 2 个滴答声）用于后台应用程序（31.25 毫秒）
• 对于“后台服务”设置
  • 36（即 18 个滴答声）用于前台应用程序（187.5 毫秒）
  • 36（即 18 个滴答声）用于后台应用程序（187.5 毫秒）

因此，现在我们知道要针对后台服务进行优化的 Windows Server 设置上的操作系统量是...

36 / 3 * 15.625000 ms = 187.5 ms

SQL OS 量子

本节列出了我在 SQL OS 量子...

SOS_SCHEDULER_YIELD 等待类型

来自 Paul Randall 对 SOS_SCHEDULER_YIELD 等待类型的描述：

这种等待类型是当一个线程能够执行其完整的线程量子（在所有版本的 SQL Server 中为 4 毫秒，不可更改），因此自愿让出调度程序，移动到其调度程序中可运行队列的底部。

^{参考：SOS_SCHEDULER_YIELD（SQLSkills.com 等待类型）}

SQL Server DMV 中的调度程序

在关于 sys.dm_os_schedulers DMV 的 SQL Server DMV 的说明中。

[...] Windows 使用抢占式调度机制，并为每个线程分配一定的 CPU 时间，当一个线程消耗它的时间时，它被发送到队列，其他线程被授予执行。

相反，当线程可以自愿放弃其时间量时，SQL Server 使用协作调度机制（当您具有 SOS_SCHEDULER_YIELD 等待类型时，您可以看到此行为）。这允许 SQL Server 优化 CPU 利用率，因为当一个线程被发出执行信号但尚未准备好运行时，它可以产生有利于其他线程的时间量。

^{参考：了解 SQL Server 调度程序、工作程序和任务(MSSQLTips.com)}

检测 SQL Server CPU 压力

这是一篇关于 SQL Server 中的 CPU 压力的文章的一小部分。

当一个任务自愿让出调度程序以供其他任务执行时发生。在此等待期间，任务正在等待其量程被更新。

^{参考：检测 SQL Server CPU 压力(MSSQLTips.com)}

sys.dm_os_schedulers (Microsoft Docs)

我想下面的引用是我能找到的关于 SQL OS 量子的最重要的信息片段：

quantum_length_us bigint  Identified for informational purposes only. 
                          Not supported. Future compatibility is not guaranteed. 
                          Exposes the scheduler quantum used by SQLOS.

^{参考：sys.dm_os_schedulers (Transact-SQL) (Microsoft | Docs)}

我的难题

Server OS Quantum 规定了授予 SQL Server 服务执行“任务”的时间。SQL Server OS Quantum 定义为 4 ms。如果我将 187.5 毫秒除以 4 毫秒，则剩下 3.5 毫秒。

我们甚至还没有开始讨论何时将时钟间隔设置为默认值 15.625000 毫秒以外的其他值....

简单的问题

SQL Server Quantum (4 ms) 如何与 Server OS Quantum (通常为 187.5 ms) 同步？

简单问题解释

在使用了 184 毫秒的 OS 时间片（对应于 46 个完整的 SQL 时间片）之后，OS 时间片有 3.5 毫秒的时间才能将计划移交给不同的进程。SQL 操作系统启动一个时间片（4 毫秒），3.5 毫秒后，操作系统时间片决定停止当前的 SQL 操作系统线程，该线程在产生调度之前还有 0.5 毫秒。现在会发生什么？

TL;博士

我偶然发现了 SQL Server Management Studio v17.9 中的一个我不知道的功能。它看起来有点像EXPLAIN在 Oracle、PostgreSQL 和 MySQL 中找到的。此功能记录在哪里？

如何打开功能

我正在运行以下查询，并启用了包括实际执行计划 (Ctrl + M)和包括实时查询统计信息的选项：

    选择 sdes.session_id
          ,sdes.[状态]
          ,sdes.login_name
          ,sdes.[主机名]
          ,sder.blocking_session_id
          ,sdb.name
          ,sdes.cpu_time
          ,sdes.logical_reads -- 可选：+ sdes.reads + sdes.writes
          ,sdes.last_request_start_time
          ,sdes.program_name
          ,sdes.session_id
          ,sder.request_id
          ,dest.[文本]
    FROM sys.dm_exec_sessions AS sdes
           左连接 sys.dm_exec_connections AS sdec
                ON sdes.session_id = sdec.session_id
           加入 sys.databases 作为 sdb
                ON sdes.database_id = sdb.database_id
           左连接 sys.dm_exec_requests AS sder
                ON sdes.session_id = sder.session_id
           交叉应用 sys.dm_exec_sql_text(sdec.most_recent_sql_handle) 作为目标
    其中 1=1
    AND sdb.name = ''

结果

这产生了四个结果选项卡：

• 结果
• 留言
• 实时查询统计
• 执行计划

特殊结果选项卡

当我切换到结果选项卡时，我注意到实际结果下方的结果集如下所示：

观察

如果我打开包含实际执行计划 (Ctrl + M)选项或包含实时查询统计信息选项，则不会显示SQL Server Explain 。只有同时打开这两个选项时，才会显示“SQL Server 说明”。

问题

1. 是否可以在不激活包括实际执行计划 (Ctrl + M)选项和包括实时查询统计信息的情况下启用此功能？
2. 这个简洁的小功能记录在哪里？

先决条件

场景中涉及以下组件和/或软件包：

服务器

• 视窗服务器 2016
• SQL Server 2016 SP1

客户

• Windows 10 版本 1807（内部版本 17134）
• SQL Server 管理工作室 v17.9
  • Microsoft SQL Server 管理工作室 14.0.17285.0
  • Microsoft 分析服务客户端工具 14.0.1016.283
  • Microsoft 数据访问组件 (MDAC) 10.0.17134.1
  • 微软 MSXML 3.0 6.0
  • 微软 Internet Explorer 9.11.17134.0
  • 微软 .NET 框架 4.0.30319.42000
  • 操作系统 6.3.17134

SQL Server 管理工作室 v17.9

• 包括实际执行计划 (Ctrl + M )打开
• 包括打开的实时查询统计信息

TL;博士

我想将SQL Server 2014 Analysis Services添加到 SQL Server的特定实例，并以这样的方式对其进行配置，即 Analysis Services (SSAS) 和未来的 Reporting Services (SSRS) 将只接收特定于实例的请求IP，以便让SQL Server Browser Service保持在停止状态。

先决条件

对于运行多个 SQL Server 2014 实例的单个 Windows Server，我有以下先决条件：

基本信息

- 2 个网卡
- 12 个 IP 地址
- 10 个 SQL Server 服务器服务实例（运行中）
- 10 个 SQL Server 代理服务器服务实例（运行中）
- 1 x SQL Server Browser 服务（未运行）

网卡配置

Windows Server NIC 配置如下：

- 1 x NIC（备份网络）
    - 10.2.0.1
- 1 个网卡（局域网）
    - 10.2.0.2（Windows 服务器）
    - 10.2.1.1 (SQL Server "Instance01"/CNAME: servername-ins01)
    - 10.2.1.2 (SQL Server "Instance02"/CNAME: servername-ins02)
    - 10.2.1.3 (SQL Server "Instance03"/CNAME: servername-ins03)
    - 10.2.1.4 (SQL Server "Instance04"/CNAME: servername-ins04)
    - 10.2.1.5 (SQL Server "Instance05"/CNAME: servername-ins05)
    - 10.2.1.6 (SQL Server "Instance06"/CNAME: servername-ins06)
    - 10.2.1.7 (SQL Server "Instance07"/CNAME: servername-ins07)
    - 10.2.1.8 (SQL Server "Instance08"/CNAME: servername-ins08)
    - 10.2.1.9 (SQL Server "Instance09"/CNAME: servername-ins09)
    - 10.2.1.10 (SQL Server "Instance10"/CNAME: servername-ins10)

SQL Server 配置管理器

SQL Server 配置管理器为 SQL Server 的每个“Instance nm ”配置如下：

- 'Instance01' 的协议
    - 共享内存：启用
    - 命名管道：禁用
    - TCP/IP：启用

TCP/IP 属性 'Instance01' - 协议选项卡

- 协议
    - 启用：是
    - 保持活力：30000
    - 全部听：没有

使用上述设置，必须单独配置每个“实例nm ”，以响应配置端口上的 IP 特定调用。（注意：作为一个额外的好处，每个实例将能够监听默认的 SQL Server 端口 1433，这反过来将大大简化防火墙配置，因为您将能够使用端口规则sql-server。只是说。）

TCP/IP 属性 'Instance01' - IP 地址选项卡

对于具体实例IP地址对应的IP n条目，设置如下：

- IPn
    - 主动：是
    - 启用：是
    - IP地址：10.2.1.1
    - TCP 动态端口：
    - TCP 端口：1433
- IP全部
    - TCP 动态端口：
    - TCP 端口：

与实例的特定 IP 地址无关的任何其他IP n条目将配置如下：

- IPn
    - 主动：是
    - 启用：否
    - IP 地址：10.2.1.xx
    - TCP 动态端口：
    - TCP 端口：
 

快速总结

通过上述设置和配置，每个 SQL Server 实例将在标准 SQL Server 端口 1433 上/使用专用 IP 地址运行。每个实例都有自己的 CNAME，允许用户连接到 SQL Server：

• 服务器名-ins01
• 服务器名-ins02
• 服务器名-ins03
• 服务器名-ins04
• 服务器名-ins05
• 服务器名-ins06
• ...

...无需指定端口或实例名称。另一个优点是不必运行SQL Server Browser 服务来分发对 SQL Server 实例的调用，因为每个调用都直接路由到实例特定的 SQL Server（服务）。

将分析服务添加到实例

软件供应商现在希望增加用户体验并提供针对现有数据运行一些分析查询的能力。软件供应商要求我们安装 SQL Server Analysis Services。这是一个特定于实例的功能，在安装过程中，您必须选择要添加该功能的实例。

防火墙配置

要允许连接到 Analysis Services，您必须在防火墙中配置以下规则：

10.2.0.2:2382 (Browser Service)

如果我使用默认实例 (MSSQLSERVER)，那么我可以在防火墙中配置以下规则：

10.2.1.1:2383 (Default Instance)

这是根据以下文档：

SQL Server 2005 Analysis Services (SSAS) 服务器属性（SQL Server 2005，但显然对当前版本仍然有效）

连接到特定于实例的分析服务

安装完成后，您可以使用以下语法连接到特定于实例的 Analysis Services：

servername\Instance01

SQL Server 浏览器服务

配置 SQL Server 实例特定的分析服务后，我注意到 SQL Server Browser 服务现在处于运行状态。

糟糕，这意味着对 Analysis Services 的请求正在通过 Window Server 的 IP 地址 (10.2.0.2) 路由到 SQL Server 特定的 Analysis Service 实例。

如果我停止 SQL Server Browser 服务，则无法再通过servername\Instance01连接到 SQL Server Analysis Services 。

安全注意事项

根据上述文件：

SQL Server Browser 服务在 SQL Server 2005 数据库引擎和 Analysis Services 引擎之间共享。对于数据库引擎，出于安全考虑，建议关闭 SQL Server Browser 服务。这可能会造成两难境地，因为您可能需要将它用于Analysis Services引擎。推荐的做法是在需要时打开服务，否则将其关闭。

哇。

发现

安装 SQL Server Analysis Services 已启动 SQL Server Browser 服务，从而产生安全考虑。

问题

1. 我可以Instance01用 CNAME配置 SQL Server Analysis Servicesservername-ins01来绑定 IP 地址10.2.1.1吗？

2. 我能否以这种方式配置我的命名实例的 SQL Server Analysis Services，以便我可以再次停止SQL Server Browser服务？

SQL Server 数据库页的大小定义为 8192 字节。有一些标头信息据说大小为 96 字节。

如果您曾经尝试创建一个包含超过 8053 字节的列定义的表，那么您将遇到错误消息：

Creating or altering table 'Generated_Data_GUID' failed because the 
minimum row size would be 8061, including 7 bytes of internal overhead. 
This exceeds the maximum allowable table row size of 8060 bytes.

以下是一个示例表 DDL：

CREATE TABLE [dbo].[Generated_Data_GUID](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [GUID] [uniqueidentifier] NOT NULL,
    [SEQGUID] [uniqueidentifier] NOT NULL,
    [Data1] [char](4000) NULL,
    [Data2] [char](4000) NULL,
    [Data3] [char](9) NULL,
    [EntryDate] [datetime2](7) NULL
) ON [PRIMARY]

使用上面的 DDL，如果我将列的列定义更改为Data3，char(10)那么我将遇到错误消息。

每种列类型的字节大小如下：

int               :  4 bytes
uniqueidentifiere : 16 bytes
char(n)           :  n bytes
datetime2(n)      :  6 bytes if n < 3 
                     7 bytes if n = 3 or n = 4
                     8 bytes if n > 4

如果我们做一些简单的数学运算，那么我们最终会得到以下计算：

Page Size         : 8192 bytes
                   -----------
Header            :   96 bytes - 
Internal Overhead :    7 bytes - 
Max Size          : 8053 bytes - 
                   -----------
Missing Data      :   36 bytes
                   ===========

问题

这 36 个字节包含什么？

参考资料

• 页面和范围架构指南（Microsoft 文档）
• 存储引擎内部：页面剖析(SQLSkills.com)

在问题中 MS-SQL 上是否有任何（隐藏的）内置函数来取消引用对象名称？作者正在寻求有关是否存在“取消引用”引用字符串的内部函数的建议。作者McNets注意到，一些内部函数可以处理带引号的（例如[MyTable]）和不带引号MyTable的（例如 ）参数值（例如OBJECT_ID()）。

在David Browne - Microsoft给出的答案 ( 1 )中，引述如下：

... QUOTENAME 的倒数是 PARSENAME，它具有导航多部分名称的额外能力。

我不同意这部分答案，并发表以下评论：

我同意您可以修改传递给的参数PARSENAME()以显示多部分标识符的部分，无论是、SERVER还是部分。但恕我直言，说这与 的相反有点牵强。DATABASESCHEMAOBJECTPARSENAMEQUOTENAME

我现在的问题是：

函数PARSENAME()是相反的QUOTENAME()吗？

在 Microsoft SQL Server 2012 上激活 FILESTREAM 功能后，SQL Server 将在系统上创建一个“隐藏”共享。份额定义如下：

Sharename          FILESTREAM_SHARE
Path               \\?\GLOBALROOT\Device\RsFx0320\<localmachine>\FILESTREAM_SHARE
Remark             SQL Server FILESTREAM share
Maximum users      unlimited
Users Caching      Manual caching of documents 
Permissions        NT-AUTHORITY\Authenticated Users, FULL

该名称是您在SQL Server 配置管理器中最初配置 FILESTREAM 时提供的共享名称。但它是为了什么？

至今

我从以下位置开始通读所有可用的 FILESTREAM 文档：

• 文件流（SQL 服务器）
• 启用和配置 FILESTREAM
• 创建启用 FILESTREAM 的数据库
• 创建用于存储 FILESTREAM 数据的表
• ...以及所有相关的
• FILESTREAM 与其他 SQL Server 功能的兼容性
• FILESTREAM DDL、函数、存储过程和视图
• 使用 OpenSqlFilestream 访问 FILESTREAM 数据
• 论文SQL Server 2008 - SQL Server 2008 中的 FILESTREAM 存储
• 和文章FILESTREAM (OLTP)---a Technical Reference Guide for Designing Mission-Critical OLTP Solutions参考了许多其他材料

...但没有提及份额及其作用或用途。您输入名称，SQL Server 将在后台创建共享。

启用 FILESTREAM 的数据库

当您创建启用 FILESTREAM 的数据库时，该数据库引用一个文件组，该文件组引用一个与共享完全无关的目录（建议在单独的驱动器上），该目录最初是在 FILESTREAM 配置期间创建的。

屏幕截图 启用 FILESTREAM 的数据库脚本

是的，我确实意识到所有路径都在 C:; 这只是一个例子

Paul Randall 等人的白皮书。继续解释...

FILESTREAM 数据存储在文件系统中一组称为数据容器的 NTFS 目录中，这些目录对应于数据库中的特殊文件组。对 FILESTREAM 数据的事务访问由 SQL Server 和作为在 Windows 级别启用 FILESTREAM 的一部分而安装的文件系统筛选器驱动程序控制。使用文件系统过滤器驱动程序还允许通过 UNC 路径远程访问 FILESTREAM 数据。SQL Server 维护从表行到与其关联的 FILESTREAM 文件的排序链接。这意味着直接通过文件系统删除或重命名任何 FILESTREAM 文件将导致数据库损坏。

...在文档（第 14 页）的更下方，他们继续进行...

每个具有 FILESTREAM 数据容器的 NTFS 卷都有一个 FILESTREAM 文件系统筛选器驱动程序，并且每个版本的 SQL Server 都有一个在卷上具有 FILESTREAM 数据容器的驱动程序。每个筛选器驱动程序负责管理该卷的所有 FILESTREAM 数据容器，以及使用特定 SQL Server 版本的所有实例。

例如，托管三个 FILESTREAM 数据容器（三个 SQL Server 2008 实例各一个）的 NTFS 卷将只有一个 SQL Server 2008 FILESTREAM 文件系统筛选器驱动程序。

问题

1. 很高兴知道 SQL Server 的一切都很好并且捆绑在一起，但是这个共享实际上有什么作用呢？它是所谓的“文件系统过滤驱动程序”吗？
2. 看到任何经过身份验证的用户都可以访问“共享”，安全隐患是什么？
3. 设备 RsFx0320 是 Windows Server 2012 引入的弹性文件系统格式的前身吗？

如果您可以提供我的问题的答案，那么如果您可以提供源参考，那就太好了。

免责声明：标题可能具有误导性。

介绍

我已经获得了使我们所有的 SQL Server 实例保持最新状态的出色项目，这意味着我必须确保将适当的 Service Pack 应用到每个实例。

我们有一个数据库工具（缩写为 DBT）应用程序，它将我们的 MySQL、PostgreSQL、Microsoft SQL Server 和 Oracle RDBMS 数据库的所有信息存储在一个位置。

这个 DBT 应用程序将给定的数据库链接到应用程序，数据库链接到实例，实例链接到服务器，当然还有数据库链接到负责人。

数据库将并且可以有很多附加信息（DB 版本、状态、项目经理、数据库经理，...），为了简化解释，我已将这些信息排除在描述之外。

为了让项目继续进行，我想输出一个包含数据库总和并按所有其他相关信息分组的唯一 SQL Server 列表。其想法是对拥有最多数据库和最高复杂性（用户、应用程序、实例）的 SQL Server 进行概览。

长话短说

这是已经汇总的数据示例以及我期望实现的目标

示例结果集

SRV_NAME             INST_NAME            DB_NAME              USER_NAME            APPL_NAME
-------------------- -------------------- -------------------- -------------------- --------------------
SQLSRV_01            ANOTHER              HIS_DB               HIM                  TELLTAIL            
SQLSRV_01            ANOTHER              RZO_P4               YOU                  PSB IZQ             
SQLSRV_01            GENERAL              MY_DB2               ME                   HAL_2000            
SQLSRV_01            GENERAL              MY_DB3               ME                   HAL_2000            
SQLSRV_01            GENERAL              MY_DB4               ME                   HAL_2000            
SQLSRV_01            GENERAL              RZO_6_4              ME                   RZO_6.4             
SQLSRV_01            GENERAL              RZO_6_4_1            ME                   RZO_6.4             
SQLSRV_01            GENERAL              RZO_6_4_2            YOU                  RZO_6.4             
SQLSRV_01            GENERAL              YOUR_DB2             YOU                  HAL_2000            
SQLSRV_01            SECURE               DB1                  ME                   HAL_2000            
SQLSRV_01            SECURE               PURCHGRAV            HER                  PURCHGRAV           
SQLSRV_01            SECURE               TELLTAIL             HER                  TELLTAIL            

进一步分组/排序后的预期结果

SRV_NAME             GRP_CNT_INST_NAME    SUM_DB_NAME          GRP_CNT_USER_NAME    GRP_CNT_APPL_NAME
-------------------- -------------------- -------------------- -------------------- --------------------
SQLSRV_01            3                    12                   4                    5

预期结果说明

示例中的 SQL Server SQLSRV_01具有三 (3) 个唯一实例、总共十二 (12) 个数据库、四 (4) 名负责人和链接到数据库的五 (5) 个应用程序。这是上面示例数据的摘要。

将其应用于整个 DBT 数据库将使我对最复杂的系统有一个概览。

参考资料已查阅

• 如何获取同一张表中不同列的计数
• OVER 子句 (Transact-SQL)
• 使用 PIVOT 和 UNPIVOT
• 对具有多个联接的不同行 求和
• 日期间隔内的滚动总和/计数/平均值

长版

遵循查询中涉及的每个表的数据和定义。最后，我已经完成了这些步骤。

表 [DBT].[服务器]

数据

 ID | SRV_NAME  | ... 
----+-----------+----
  1 | SQLSRV_01 |     
  2 | SQLSRV_11 |     
  3 | SQLSRV_21 |     

定义

CREATE TABLE [DBT].[Server](
    [ID] [int] NOT NULL,
    [SRV_NAME] [nchar](20) NULL
) ON [PRIMARY]
END
GO
ALTER AUTHORIZATION ON [DBT].[Server] TO  SCHEMA OWNER 
GO
SET ANSI_PADDING ON
GO
CREATE UNIQUE CLUSTERED INDEX [CL_UX_Server_ALL] ON [DBT].[Server]
(
    [ID] ASC,
    [SRV_NAME] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

表 [DBT].[实例]

数据

 ID | INST_NAME   | SRV_ID | ...
----+-------------+--------+----
  1 | GENERAL     |      1 |
  2 | SECURE      |      1 |
  3 | ANOTHER     |      1 |
  4 | GENERAL     |      2 |
  5 | MSSQLSRV    |      3 |
  6 | MSSQLSRV    |      2 |
  7 | PRODUCTION  |      2 |
  8 | TESTING     |      3 |
... |             |        | 

定义

CREATE TABLE [DBT].[Instance](
    [ID] [int] NOT NULL,
    [INST_NAME] [nchar](20) NOT NULL,
    [SRV_ID] [int] NOT NULL
) ON [PRIMARY]
END
GO
ALTER AUTHORIZATION ON [DBT].[Instance] TO  SCHEMA OWNER 
GO
SET ANSI_PADDING ON
GO
CREATE UNIQUE CLUSTERED INDEX [CL_UX_Instance_ALL] ON [DBT].[Instance]
(
    [ID] ASC,
    [INST_NAME] ASC,
    [SRV_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

表[DBT].[应用]

数据

 ID | APPL_NAME  | ... 
----+------------+-----
  1 | HAL_2000   |     
  2 | RZO_6.4    |     
  3 | PSB IZQ    |     
  4 | TELLTAIL   |     
  5 | PURCHGRAV  |     
... |            |     

定义

CREATE TABLE [DBT].[Application](
    [ID] [int] NOT NULL,
    [APPL_NAME] [nchar](20) NOT NULL
) ON [PRIMARY]
END
GO
ALTER AUTHORIZATION ON [DBT].[Application] TO  SCHEMA OWNER 
GO
SET ANSI_PADDING ON
GO
CREATE UNIQUE CLUSTERED INDEX [CL_UX_Application_ALL] ON [DBT].[Application]
(
    [ID] ASC,
    [APPL_NAME] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

表 [DBT].[用户]

数据

 ID | USER_NAME  | ... 
----+------------+-----
  1 | ME         |     
  2 | YOU        |     
  3 | HIM        |     
  4 | HER        |     
  5 | THE OTHERS |     
  6 | ALIENS     |     
... |            |     

定义

CREATE TABLE [DBT].[User](
    [ID] [int] NOT NULL,
    [USER_NAME] [nchar](20) NOT NULL
) ON [PRIMARY]
END
GO
ALTER AUTHORIZATION ON [DBT].[User] TO  SCHEMA OWNER 
GO
SET ANSI_PADDING ON
GO
CREATE UNIQUE CLUSTERED INDEX [CL_UX_User_ALL] ON [DBT].[User]
(
    [ID] ASC,
    [USER_NAME] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

表 [DBT].[数据库]

数据

 ID | DB_NAME    | INST_ID | APPL_ID | USER_ID | ...
----+------------+---------+---------+---------+-----
  1 | MY_DB2     |       1 |       1 |       1 | 
  2 | YOUR_DB2   |       1 |       1 |       2 | 
  3 | RZO_6_4    |       1 |       2 |       1 | 
  4 | DB1        |       2 |       1 |       1 | 
  5 | TELLTAIL   |       2 |       4 |       4 | 
  6 | PURCHGRAV  |       2 |       5 |       4 | 
  7 | HIS_DB     |       3 |       4 |       3 | 
  8 | RZO_P4     |       3 |       3 |       2 | 
  9 | PURCH      |       4 |       5 |       2 | 
 10 | YOUR_DB    |       5 |       4 |       2 | 
 11 | HER_DB     |       6 |       4 |       4 | 
 12 | TEST_PURCH |       6 |       5 |       5 | 
 13 | PROD_PURCH |       7 |       5 |       5 | 
 14 | TELLTAIL   |       7 |       4 |       4 | 
 15 | IZQ_TEST   |       8 |       3 |       3 | 
 16 | IZQ_PROD   |       7 |       2 |       2 | 
 17 | HAL_CA1    |       5 |       1 |       3 | 
 18 | MY_DB3     |       1 |       1 |       1 | 
 19 | MY_DB4     |       1 |       1 |       1 | 
 20 | RZO_6_4_1  |       1 |       2 |       1 | 
 21 | RZO_6_4_2  |       1 |       2 |       2 | 
 22 | HAL_CA1_1  |       5 |       1 |       3 | 
 23 | HAL_CA1_2  |       5 |       1 |       6 | 
... |

定义

CREATE TABLE [DBT].[Database](
    [ID] [int] NOT NULL,
    [DB_NAME] [nchar](20) NOT NULL,
    [INST_ID] [int] NOT NULL,
    [APPL_ID] [int] NOT NULL,
    [USER_ID] [int] NOT NULL
) ON [PRIMARY]
END
GO
ALTER AUTHORIZATION ON [DBT].[Database] TO  SCHEMA OWNER 
GO
SET ANSI_PADDING ON
GO
CREATE UNIQUE CLUSTERED INDEX [CL_UX_Database_ID_DB_NAME_INST_ID] ON [DBT].[Database]
(
    [ID] ASC,
    [DB_NAME] ASC,
    [INST_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

目前为止就这样了。

选择所有信息

第一个陈述是我选择基本信息的起点。

SELECT s.[SRV_NAME], i.[INST_NAME], d.[DB_NAME], u.[USER_NAME], a.[APPL_NAME]
FROM   [DBT].[Server]            AS s
       JOIN [DBT].[Instance]     AS i
            ON  s.ID = i.SRV_ID
       JOIN [DBT].[Database]     AS d
            ON  i.[ID] = d.[INST_ID]
       JOIN [DBT].[Application]  AS a
            ON  d.[APPL_ID] = a.[ID]
       JOIN [DBT].[User]         AS u
            ON  u.ID = d.[USER_ID]
ORDER BY 1, 2, 3, 4, 5

这导致返回以下记录，并且是简介中示例数据的长版本：

SRV_NAME             INST_NAME            DB_NAME              USER_NAME            APPL_NAME
-------------------- -------------------- -------------------- -------------------- --------------------
SQLSRV_01            ANOTHER              HIS_DB               HIM                  TELLTAIL            
SQLSRV_01            ANOTHER              RZO_P4               YOU                  PSB IZQ             
SQLSRV_01            GENERAL              MY_DB2               ME                   HAL_2000            
SQLSRV_01            GENERAL              MY_DB3               ME                   HAL_2000            
SQLSRV_01            GENERAL              MY_DB4               ME                   HAL_2000            
SQLSRV_01            GENERAL              RZO_6_4              ME                   RZO_6.4             
SQLSRV_01            GENERAL              RZO_6_4_1            ME                   RZO_6.4             
SQLSRV_01            GENERAL              RZO_6_4_2            YOU                  RZO_6.4             
SQLSRV_01            GENERAL              YOUR_DB2             YOU                  HAL_2000            
SQLSRV_01            SECURE               DB1                  ME                   HAL_2000            
SQLSRV_01            SECURE               PURCHGRAV            HER                  PURCHGRAV           
SQLSRV_01            SECURE               TELLTAIL             HER                  TELLTAIL            
SQLSRV_11            GENERAL              PURCH                YOU                  PURCHGRAV           
SQLSRV_11            MSSQLSRV             HER_DB               HER                  TELLTAIL            
SQLSRV_11            MSSQLSRV             TEST_PURCH           THE OTHERS           PURCHGRAV           
SQLSRV_11            PRODUCTION           IZQ_PROD             YOU                  RZO_6.4             
SQLSRV_11            PRODUCTION           PROD_PURCH           THE OTHERS           PURCHGRAV           
SQLSRV_11            PRODUCTION           TELLTAIL             HER                  TELLTAIL            
SQLSRV_21            MSSQLSRV             HAL_CA1              HIM                  HAL_2000            
SQLSRV_21            MSSQLSRV             HAL_CA1_1            HIM                  HAL_2000            
SQLSRV_21            MSSQLSRV             HAL_CA1_2            ALIENS               HAL_2000            
SQLSRV_21            MSSQLSRV             YOUR_DB              YOU                  TELLTAIL            
SQLSRV_21            TESTING              IZQ_TEST             HIM                  PSB IZQ             

按计数汇总（DB_NAME）

所以我认为按 , 分组SRV_NAME，INST_NAME然后USER_NAME添加APPL_NAME到COUNT(DB_NAME)select 语句是个好主意。

陈述

SELECT s.[SRV_NAME], i.[INST_NAME], count(d.[DB_NAME]) AS SUMDB, u.[USER_NAME], a.[APPL_NAME]
FROM   [DBT].[Server]            AS s
       JOIN [DBT].[Instance]     AS i
            ON  s.ID = i.SRV_ID
       JOIN [DBT].[Database]     AS d
            ON  i.[ID] = d.[INST_ID]
       JOIN [DBT].[Application]  AS a
            ON  d.[APPL_ID] = a.[ID]
       JOIN [DBT].[User]         AS u
            ON  u.ID = d.[USER_ID]

GROUP BY s.[SRV_NAME], i.[INST_NAME], u.[USER_NAME], a.[APPL_NAME]
ORDER BY 1, 2, 3, 4, 5

结果

SRV_NAME             INST_NAME            SUMDB       USER_NAME            APPL_NAME
-------------------- -------------------- ----------- -------------------- --------------------
SQLSRV_01            ANOTHER              1           HIM                  TELLTAIL            
SQLSRV_01            ANOTHER              1           YOU                  PSB IZQ             
SQLSRV_01            GENERAL              1           YOU                  HAL_2000            
SQLSRV_01            GENERAL              1           YOU                  RZO_6.4             
SQLSRV_01            GENERAL              2           ME                   RZO_6.4             
SQLSRV_01            GENERAL              3           ME                   HAL_2000            
SQLSRV_01            SECURE               1           HER                  PURCHGRAV           
SQLSRV_01            SECURE               1           HER                  TELLTAIL            
SQLSRV_01            SECURE               1           ME                   HAL_2000            
SQLSRV_11            GENERAL              1           YOU                  PURCHGRAV           
SQLSRV_11            MSSQLSRV             1           HER                  TELLTAIL            
SQLSRV_11            MSSQLSRV             1           THE OTHERS           PURCHGRAV           
SQLSRV_11            PRODUCTION           1           HER                  TELLTAIL            
SQLSRV_11            PRODUCTION           1           THE OTHERS           PURCHGRAV           
SQLSRV_11            PRODUCTION           1           YOU                  RZO_6.4             
SQLSRV_21            MSSQLSRV             1           ALIENS               HAL_2000            
SQLSRV_21            MSSQLSRV             1           YOU                  TELLTAIL            
SQLSRV_21            MSSQLSRV             2           HIM                  HAL_2000            
SQLSRV_21            TESTING              1           HIM                  PSB IZQ        

正如您从结果中看到的那样，还有进一步总结（分组？）的潜力，例如，通过,INST_NAME来概述最复杂的系统。USER_NAMEAPPL_NAME

对 INST_NAME、USER_NAME 和 APPL_NAME 进行分组

所以基本上我想根据介绍中解释的服务器对每个唯一（子）项目进行总结：

SRV_NAME             GRP_CNT_INST_NAME    SUM_DB_NAME          GRP_CNT_USER_NAME    GRP_CNT_APPL_NAME
-------------------- -------------------- -------------------- -------------------- --------------------
SQLSRV_01            3                    12                   4                    5

嗯。查看在线书籍我可以选择OVER 子句 (Transact-SQL)并在相关列上进行分区。但是我可能会误解描述。

陈述

SELECT s.[SRV_NAME], 
COUNT(i.[INST_NAME]) OVER (PARTITION by i.[INST_NAME]) as GRP_CNT_INST_NAME, 
COUNT(d.[DB_NAME]) AS SUMDB, 
COUNT(u.[USER_NAME]) OVER (PARTITION by u.[USER_NAME]) as GRP_CNT_USER_NAME, 
COUNT(a.[APPL_NAME]) OVER (PARTITION by a.[APPL_NAME]) as GRP_CNT_APPL_NAME
FROM   [DBT].[Server]            AS s
       JOIN [DBT].[Instance]     AS i
            ON  s.ID = i.SRV_ID
       JOIN [DBT].[Database]     AS d
            ON  i.[ID] = d.[INST_ID]
       JOIN [DBT].[Application]  AS a
            ON  d.[APPL_ID] = a.[ID]
       JOIN [DBT].[User]         AS u
            ON  u.ID = d.[USER_ID]

GROUP BY s.[SRV_NAME]--, i.[INST_NAME], u.[USER_NAME], a.[APPL_NAME]
ORDER BY 1, 2, 3, 4, 5

结果

SRV_NAME             GRP_CNT_INST_NAME SUMDB       GRP_CNT_USER_NAME GRP_CNT_APPL_NAME
-------------------- ----------------- ----------- ----------------- -----------------
SQLSRV_01            2                 1           3                 5
SQLSRV_01            2                 1           6                 2
SQLSRV_01            3                 1           3                 5
SQLSRV_01            3                 1           4                 4
SQLSRV_01            3                 1           4                 5
SQLSRV_01            5                 1           6                 3
SQLSRV_01            5                 1           6                 5
SQLSRV_01            5                 2           3                 3
SQLSRV_01            5                 3           3                 5
...
...

That doesn't look like what I was expecting to achieve. But then again, I might need a totally different approach.

Question

I'm still trying to find the right way to summarise each sub-item so as to have an overview of the most complex systems. What is a possible solution to my problem?

SSMS 登录框的有效连接字符串是什么？

每隔一段时间，我就必须连接到具有某些特定配置设置的 SQL Server 实例，或者连接到没有可用主机名的 DMZ 中，或者我必须通过 SSMS 或 SQLCMD 连接到专用管理员连接 (DAC) . 因为我很少通过特定端口或使用 DAC 连接到 SQL Server，所以我往往会忘记建立和运行连接的确切语法选项。然后，我将不得不无数次再次搜索互联网以检索确切的连接字符串，并且经常会因为找不到所需的信息而感到沮丧。

研究

我将阅读之前发布在 Serverfault、Stackoverflow 和 DBA 上的所有问题和答案。

服务器故障

[1] DAC 连接端口 SQL Server 2005 SP3
[2]远程 SQL server 连接失败
[3] SQL Server 2008 R2 远程连接

堆栈溢出

[4]如何在 SQL Server 连接字符串中指定端口号？

数据库管理员

[5] DAC连接错误

微软

此外，我将在 Microsoft 网站上搜索相关文章：

[6]如何配置 SQL Server 监听特定端口
[7]配置 Windows 防火墙以允许 SQL Server 访问
[8]外围应用配置
[9]如何配置 SQL Server 监听不同 IP 地址的不同端口？
[10]将服务器配置为侦听特定 TCP 端口（SQL Server 配置管理器）
[11]数据库管理员的诊断连接

布伦特奥扎

Kendra Little 有一篇关于 DAC 一些有趣方面的文章。
[12]专用管理员连接：为什么需要它、何时需要它以及如何判断谁在使用它

附带说明：有时 DAC 连接也称为 ADMIN 连接。

我发现最后从来没有一个简单的连接字符串的实际摘要输入到 SSMS 登录框，也没有添加到 SQLCMD 的内容。

这个问题和以下答案旨在总结我迄今为止发现的内容，并将信息放入一篇知识库文章中。

实际问题

给定 SQL Server Standard Edition 的以下设置：

Servername.....:    SERVERNAME
IP address.....:    123.1.2.3

1st instance (default instance):
CNAME/Alias....:    SERVERNAME-I01
Name...........:    MSSQLSERVER
Port...........:    1433 (TCP)
IP address.....:    123.1.2.3
DAC Port.......:    1434 (TCP)

2nd instance (default ip & port configuration according to [7]):
CNAME/Alias....:    SERVERNAME-I02
Name...........:    Instance2
Port...........:    dynamic (TCP)
IP address.....:    123.1.2.3
DAC port.......:    dynamic (TCP)

3rd instance (dedicated IP and port):
CNAME/Alias....:    SERVERNAME-I03
Name...........:    Instance3
Port...........:    1433 (TCP)
IP address.....:    123.1.2.4
DAC port.......:    dynamic (TCP)

Browser Service:    1434 (UDP)
Browser service:    ON

...我可以在 SSMS 登录框中输入的每个实例的有效连接字符串是什么

1. ...与主机名？
2. ...别名（CNAME）？
3. ...有一个IP地址？
4. ...对于“专用管理员连接”（DAC aka ADMIN :)？

假设

1. 根据6、7或8中提到的 Microsoft 文档，服务器防火墙被禁用或配置。对于 LAN 内的任何网络防火墙（包括与 DMZ 服务器的连接）也是如此。
2. 除默认实例外，其他实例已手动配置为根据9侦听不同的 IP 地址，在此示例中已配置为侦听固定端口10。
3. 在某些情况下，DAC 将根据11和12启用。

我试图弄清楚 PostgreSQL 周围的各种事情，以及备份应该如何与 WAL 和 Commvault Simpana 一起工作。Simpana 告诉我一切正常，但文件仍留在 WAL 存档目录中。

让旅程开始吧。

环境

PostgreSQL 和操作系统版本

PostgreSQL 9.3 在 Ubuntu 14.04.3 LTS 服务器上运行。

Postgres WAL 配置

postgres.conf 文件为 WAL 归档设置如下。

#------------------------------------------------------------------------------
# WRITE AHEAD LOG
#------------------------------------------------------------------------------
# - Settings -
#wal_level = minimal                    # minimal, archive, or hot_standby
wal_level = archive

[...]

# - Archiving -
archive_mode = on
#archive_mode = off             # allows archiving to be done
                                # (change requires restart)
archive_command = 'cp %p /pgsql-backup/archive/postgres1/%f'
                                # command to use to archive a logfile segment
                                # archive_command = ''           
                                # command to use to archive a logfile segment
                                # placeholders: %p = path of file to archive
                                #               %f = file name only
                                # e.g. 'test ! -f /mnt/server/archivedir/%f    && cp %p /mnt/server/archivedir/%f'
#archive_timeout = 0            # force a logfile segment switch after this
                                # number of seconds; 0 disables

如果test ...零件留在其中，archive_command它会破坏 Simpana 备份，这就是我们省略它的原因。

上面的配置应该会导致 WAL 文件从/pg_xlog/目录复制到/pgsql-backup/archive/postgres1/目录，当...

1. 不再需要，因为 pg_basebackup
2. WAL 文件已满（默认为 16MB）且不再使用

Commvault Simpana

客户端计算机已配置为备份存档日志目录中的 PostgreSQL 数据库/实例和 WAL 文件。不再需要时应删除 WAL 文件，因为已为 PostgreSQL 客户端设置了 Simpana 选项“删除存档”。

预期行为

因为 Simpana 正在使用 PostgreSQL 本机命令执行备份，所以我希望当 Simpana 完成完整备份或 WAL 备份时，/pgsql-backup/archive/postgres1/目录中的文件将被删除。

有效行为

当我/pgsql-backup/archive/postgres1/在 Simpana 执行备份后检查目录时，目录中还会有一个带有0000000300000037000000nn.mmmmmmmm.backup语法的文件。这暗示 Simpana 正在使用本机 PostgreSQL 命令执行备份，因为0000000300000037000000nn.mmmmmmmm.backup只有在使用pg_basebackup. 这只是我在阅读 PostgreSQL 9.3 的文档后得出的结论。

以下是目录内容的示例：

[...]
00000003000000370000007A
00000003000000370000007B.00000028.backup
000000030000003700000091.00000028.backup
000000030000003700000093.00000028.backup
000000030000003700000095.00000028.backup
000000030000003700000097.00000028.backup
000000030000003700000099.00000028.backup
00000003000000370000009B.00000028.backup

PostgreSQL 文档

官方文档指出

要使用备份，您需要保留在文件系统备份期间和之后生成的所有 WAL 段文件。为了帮助您执行此操作，基本备份过程会创建一个备份历史文件，该文件会立即存储到 WAL 存档区域中。该文件以文件系统备份所需的第一个 WAL 段文件命名。例如，如果起始 WAL 文件是 0000000100001234000055CD，则备份历史文件将命名为 0000000100001234000055CD.007C9330.backup。（文件名的第二部分代表 WAL 文件中的确切位置，通常可以忽略。）一旦您安全地归档了文件系统备份和备份期间使用的 WAL 段文件（如备份历史记录中指定的那样）文件），不再需要名称数字较小的所有已归档 WAL 段来恢复文件系统备份，并且可以将其删除。但是，您应该考虑保留几个备份集，以绝对确定您可以恢复数据。

这破坏了我的结论，即 Simpana 正在使用本机 PostgreSQL 命令来备份目录中的数据库/实例及其 WAL 存档日志文件/pgsql-backup/archive/postgres1/。

根据文档，nnnnnnnnnnnnnnnnnnnnnn.mmmmmmmm.backup 文件是指向成功前滚恢复所需的最早 WAL 文件的指针。存档日志目录中的任何旧 WAL 文件都可以删除并且不再需要。

让我吃惊的是，Archive Log 目录中有一个 WAL 文件，没有对应的 *.mmmmmmmm.backup 指针文件。

问题

1. 如果我不使用 Simpana 进行备份，谁会（必须）删除 WAL 存档目录中的 *.mmmmmmmm.backup 文件？pg_archivecleanup命令？
2. 为什么存档日志目录中仍然有一个完整的 WAL 文件，而它应该像存档日志目录中的所有其他 WAL 文件一样被删除？
3. 为什么存档日志目录中没有00000003000000370000007A.mmmmmmmm.backup仍然存在的WAL 文件的文件？00000003000000370000007A

我期待您的回复，并希望有人在某个地方有类似的 Simpana 和 PostgreSQL 配置。