jitbit's questions

我有一个带有邮政编码的字符串NN2 7DG。SQL Server 可以完美找到7DG但不能NN2。

我的停用词列表是空的。

我查看了索引关键字，NN2甚至不存在，7DG而是

-- list indexed keywords
SELECT * FROM sys.dm_fts_index_keywords (DB_ID('MyDatabase'), OBJECT_ID('MyTable'))

建议？

我有一台安装了 SQL Server 2017 的 64GiB Ubuntu 机器。memory.memorylimitmb任何高于 63695 兆字节的设置都将被忽略。将其设置为 67000 MB（大致为 62.3 GiB）仍会导致 SSMS 报告 63695 MB 可用（右键单击服务器，属性 - 常规 - 内存行）。较低的设置反映在那里就好了。

这导致 SQL Server 没有使用所有可用内存，它只使用 59.2，留下超过 4 个演出空。

同样，这是我的“mssql.conf”：

[memory]
memorylimitmb = 67000

但这是 SSMS 报告的内存量：

（注意：这不是“最大内存”设置，这是 SSMS 在“服务器 - 属性 - 常规 - 内存”下简单说明机器上安装了多少内存的地方）

我有一个非常简单的查询：

INSERT INTO #tmptbl
SELECT TOP 50 CommentID --this is primary key
FROM Comments WITH(NOLOCK)
WHERE UserID=@UserID
ORDER BY CommentID DESC

针对此表：

CREATE TABLE [dbo].[Comments] (
    [CommentID] int IDENTITY (1, 1) NOT NULL PRIMARY KEY,
    [CommentDate] datetime NOT NULL DEFAULT (getdate()),
    [UserID] int NULL ,
    [Body] nvarchar(max) NOT NULL,
--a couple of other int and bit cols, no indexes on them
)

我在UserID列上有一个简单的索引（不包括列），一切都运行良好且超快。

但是每 5-8 天我就会在应用程序的那部分看到超时。所以我去调查查询存储，我看到服务器停止使用我的索引并恢复到愚蠢的“集群扫描”。删除临时表没有帮助。

为了解决这个问题——我为这个特定的查询重置了计划缓存（只是为了记录我是怎么做的）

select plan_handle FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text (qs.[sql_handle]) AS qt
where text like '%SELECT TOP 50 CommentID FROM hdComments%'
--blahblahblah skipped some code
DBCC FREEPROCCACHE (@plan_handle)

然后再次开始正常工作。

执行计划：慢 快

我已经挠头好几天了……有什么想法吗？

这是我们的配置：

1. 我们的所有数据都在 PRIMARY 文件组中

2. 我们的全文索引位于另一个 SECONDARY 文件组中，该文件组位于非常快（但非常不可靠）的 SSD 驱动器上。我所说的“不可靠”是指 - 它是亚马逊云服务器临时驱动器（在紧急重启/硬件故障的情况下可能会完全丢失）。

全文索引并不重要，我们已经准备好完全失去它，因为我们显然可以从头开始重建它。

问题是：如果辅助文件组 100% 丢失，有没有办法在不经过完整备份/恢复周期的情况下恢复数据库？就像，简单地删除丢失的文件组，然后重新创建它，然后重建索引。

（我们确实有定期的完整数据库备份、日志备份等等等等，所以我们可以“恢复到时间点”，但这意味着长时间中断）

PS 此外，非常感谢任何关于我们的方法可行性的想法。

查询 1：

select count(UserID)
from users
where UserID IN (select UserID from otherTable)

退货： 178

查询 2: : (简单地IN改为NOT IN)

select count(UserID)
from users
where UserID NOT IN (select UserID from otherTable)

退货： 0

查询 3：（完全删除 IN 子句）

select count(UserID)
from users

回报：（ 1047123超过一百万）

什么？我不能……甚至……

我唯一的解释是我的数据库不一致/损坏，我必须运行 DBCC CHECKDB 或其他东西。

附言。UserID是一个主键，所以 - 没有欺骗。

将 SQL Server 2014 升级到 2016 后，服务器每隔几个小时dm*就会不断重置缓存的执行计划和视图（如dm_exec_query_stats）等

好像有人手动执行（除了没有人执行，它是自动发生的）DBCC FREEPROCCACHE。DBCC DROPCLEANBUFFERS

同一个数据库在 SQL Server 2014 和 Windows Server 2012 上运行良好，但在迁移到 SQL Server 2016（和 Windows Server 2016）之后，事情就变糟了

我检查的事情：数据库没有“自动关闭”标志。SQL 服务器ad hoc optimized设置为true（我认为它会有所帮助，但没有）。“查询存储”是“关闭”的。服务器有 16 GB 内存。

“SQL Server 日志”中也没有任何帮助。只是每周的备份消息...

我还检查了这篇文章https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-set-options（向下滚动到“示例”部分，就在上面它）有一个自动清除计划的情况列表。这些都不适用。

更新：

不幸的是，这些建议都没有帮助。授予 LPIM 权限，检测和修复为同一查询生成大量计划的非参数化查询，降低“最大服务器内存”......计划不断随机重置，从每几个小时到每 5-10 分钟。如果服务器“承受内存压力”，2014 版如何在同一台机器上正常工作。

这是请求的 sp_Blitz 输出

**Priority 10: Performance**:

- Query Store Disabled - The new SQL Server 2016 Query Store feature has not been enabled on this database.

    * xxx


**Priority 50: Server Info**:

- Instant File Initialization Not Enabled  - Consider enabling IFI for faster restores and data file growths.


**Priority 100: Performance**:

- Resource Governor Enabled  - Resource Governor is enabled.  Queries may be throttled.  Make sure you understand how the Classifier Function is configured.


**Priority 120: Query Plans**:

- Implicit Conversion Affecting Cardinality - One of the top resource-intensive queries has an implicit conversion that is affecting cardinality estimation.

    * 

- Missing Index - One of the top resource-intensive queries may be dramatically improved by adding an index.

    * 

- RID or Key Lookups - One of the top resource-intensive queries contains RID or Key Lookups. Try to avoid them by creating covering indexes.

    * 

**Priority 170: File Configuration**:

- System Database on C Drive
    * master - The master database has a file on the C drive.  Putting system databases on the C drive runs the risk of crashing the server when it runs out of space.

    * model - The model database has a file on the C drive.  Putting system databases on the C drive runs the risk of crashing the server when it runs out of space.

    * msdb - The msdb database has a file on the C drive.  Putting system databases on the C drive runs the risk of crashing the server when it runs out of space.


**Priority 200: Backup**:

- MSDB Backup History Not Purged msdb - Database backup history retained back to Jun 10 2017  9:47PM


**Priority 200: Informational**:

- Backup Compression Default Off  - Uncompressed full backups have happened recently, and backup compression is not turned on at the server level. Backup compression is included with SQL Server 2008R2 & newer, even in Standard Edition. We recommend turning backup compression on by default so that ad-hoc backups will get compressed.


**Priority 200: Non-Default Server Config**:

- Agent XPs  - This sp_configure option has been changed.  Its default value is 0 and it has been set to 1.

- max server memory (MB)  - This sp_configure option has been changed.  Its default value is 2147483647 and it has been set to 15000.

- optimize for ad hoc workloads  - This sp_configure option has been changed.  Its default value is 0 and it has been set to 1.

- show advanced options  - This sp_configure option has been changed.  Its default value is 0 and it has been set to 1.

- xp_cmdshell  - This sp_configure option has been changed.  Its default value is 0 and it has been set to 1.


**Priority 200: Performance**:

- Buffer Pool Extensions Enabled  - You have Buffer Pool Extensions enabled, and one lives here: Z:\sql_buffer_pool.BPE. It's currently 60.00000000000 GB. Did you know that BPEs only provide single threaded access 8KB (one page) at a time?

- cost threshold for parallelism  - Set to 5, its default value. Changing this sp_configure setting may reduce CXPACKET waits.

**Priority 240: Wait Stats**:

- No Significant Waits Detected  - This server might be just sitting around idle, or someone may have cleared wait stats recently.

**Priority 250: Informational**:

- SQL Server Agent is running under an NT Service account  - I'm running as NT Service\SQLSERVERAGENT. I wish I had an Active Directory service account instead.

- SQL Server is running under an NT Service account  - I'm running as NT Service\MSSQLSERVER. I wish I had an Active Directory service account instead.

**Priority 250: Server Info**:

- Default Trace Contents  - The default trace holds 125 hours of data between Aug 19 2017 11:55AM and Aug 24 2017  4:59PM. The default trace files are located in: C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log

- Hardware  - Logical processors: 2. Physical memory: 15GB.

- Hardware - NUMA Config  - Node: 0 State: ONLINE Online schedulers: 2 Offline schedulers: 0 Processor Group: 0 Memory node: 0 Memory VAS Reserved GB: 29

- Locked Pages In Memory Enabled  - You currently have 12.02534484863 GB of pages locked in memory.

- Memory Model Unconventional  - Memory Model: LOCK_PAGES

- Server Last Restart  - Aug 20 2017 12:32PM

- Server Name  - xx

- Services
 - Service: SQL Full-text Filter Daemon Launcher (MSSQLSERVER) runs under service account NT Service\MSSQLFDLauncher. Last startup time: not shown.. Startup type: Manual, currently Running.

 - Service: SQL Server (MSSQLSERVER) runs under service account NT Service\MSSQLSERVER. Last startup time: Aug 20 2017 12:32PM. Startup type: Automatic, currently Running.

 - Service: SQL Server Agent (MSSQLSERVER) runs under service account NT Service\SQLSERVERAGENT. Last startup time: not shown.. Startup type: Automatic, currently Running.

- SQL Server Last Restart  - Aug 20 2017 12:33PM

- SQL Server Service  - Version: 13.0.4446.0. Patch Level: SP1. Edition: Enterprise Edition (64-bit). AlwaysOn Enabled: 0. AlwaysOn Mgr Status: 2

- Virtual Server  - Type: (HYPERVISOR)

- Windows Version  - You're running a pretty modern version of Windows: Server 2012R2 era, version 6.3


**Priority 254: Rundate**:

 - Captain's log: stardate something and something...