sql server tips 一些 实用 的sql 查询 提示 query hint

SQL SERVER

表如果已存在则删除

2016开始
DROP TABLE IF EXISTS dbo.Scores
2016之前
IF OBJECT_ID('tempdb..#tempdbname') IS NOT NULL --Remove dbo here
DROP TABLE #tempdbname

通过表锁优化批量插入和删除性能

delete dbo.myTbl with(tablock)
删除时添加表锁，可以确保让删除释放的空间还给数据库。参考
insert into dbo.myTbl with(tablock) select * from aaa
批量插入有时执行计划不会启用并发，添加表锁参数后就能并发，如果大表且有索引的话，并发会快很多

添加了tablock
主键为noclustered，没有聚集索引
数据库兼容级别为130，执行计划仍然无法启动并行查询
原因:开启并行insert，可能需要insert的表的所有索引的填充因子都设置为50%。
我的实际情况表只有一个主键，填充因子没有设置，默认100%，无法启用并行插入。改为50%就能启用并发了。
How to make insert faster in SQL Server

How to use parallel insert in SQL Server 2016 to improve query , In this blog post, you are going to see step by step how to use parallel insert in SQL Server 2016 to improve query performance. Put a fill factor of like 50 on all the indexes. Try an insert. If this work then tune the fill factor and add in index maintenance. Maybe even tune the order of PK to put those with a more common insert sort early - like if one FK is a customerID and you load by customerID then have it early.

[Performance Tuning] Parallel INSERT … SELECT with TABLOCK hint

sql单线执行

有时候涉及表扫描的大批量io查询，默认开启并发查询对物理磁盘读写并不友好，不如单线程读写效率高
maxdop 1

强制全表扫描，不走索引

TABLE HINT( [pub].[wx].[table1], FORCESCAN)

强制启用批量insert模式，避免单条insert

QUERYTRACEON 8790

强制开启并行执行

OPTION(QUERYTRACEON 8649)
参考 https://www.mssqltips.com/sqlservertip/4939/how-to-force-a-parallel-execution-plan-in-sql-server-2016/

强制并行查询计划parallelism

OPTION(USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'))
参考 https://www.mssqltips.com/sqlservertip/4939/how-to-force-a-parallel-execution-plan-in-sql-server-2016/

移动临时库tempdb位置

alter database tempdb modify file (name = tempdev ,filename = '/var/opt/mssql/data/' ,size=256)
有几个文件就敲几条命令，然后 重启数据库

tempdb文件数少可能导致闩锁争抢

sqlserver数据库迁移

使用数据迁移助手 Microsoft® Data Migration Assistant v5.0
https://docs.microsoft.com/zh-cn/sql/dma/dma-migrateonpremsql?view=sql-server-ver15

其他

两种方式设置表的hint

在option中设置

SELECT *
  FROM [pub].[dbo].[table1]
option(maxdop 1,TABLE HINT( [pub].[wx].[table1], FORCESCAN))

在表名后门通过with设置

SELECT *
  FROM [pub].[dbo].[table1]  with ( FORCESCAN)
option(maxdop 1)

https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-2017

© 2020 - 2021, 新之助meow. 原创文章转载请注明： 转载自http://www.xinmeow.com