- 查询索引的缺失 https://www.cnblogs.com/littlewrong/p/8676691.html https://blog.csdn.net/qyx0714/article/details/77428961
- SQL Server 索引维护(1)——如何获取索引使用情况 https://blog.csdn.net/dba_huangzj/article/details/49735941#
- 堆表缺失索引导致死锁 地址:https://www.cnblogs.com/woodytu/p/4693618.html
- SQL Server性能优化(8)堆表结构介绍 地址:https://www.cnblogs.com/ustcyc/p/4524122.html
- SQL Server数据库表索引碎片整理 地址:https://www.cnblogs.com/yangkangIT/p/7613893.html
- Sql Server 检测死锁的SQL语句 地址: https://www.cnblogs.com/OpenCoder/p/5557514.html
- SQL Server 中WITH (NOLOCK)浅析 https://www.cnblogs.com/kerrycode/p/3946268.html
- SQL Server里因丢失索引造成的死锁 https://www.cnblogs.com/woodytu/p/4693618.html
- 解决死锁之路 - 了解常见的锁类型 https://www.aneasystone.com/archives/2017/11/solving-dead-locks-two.html
- 数据库的快照隔离级别(Snapshot Isolation) https://www.cnblogs.com/ljhdo/p/5037033.html
-
SQL Server中的事务日志管理(9/9):监控事务日志 https://www.cnblogs.com/woodytu/p/5122492.html
-
聚集索引与非聚集索引的总结 https://www.cnblogs.com/s-b-b/p/8334593.html
-
SQL Server 堆表与栈表的对比(大表) https://www.cnblogs.com/Apq/p/Table_NonClustered.html
-
重新理解SQL Server的聚集索引表与堆表 https://blog.csdn.net/wstoneh01/article/details/50560037#%E8%81%9A%E9%9B%86%E7%B4%A2%E5%BC%95%E8%A1%A8%E4%B8%8E%E5%A0%86%E8%A1%A8%E7%9A%84%E6%AD%A3%E7%A1%AE%E4%BD%BF%E7%94%A8
-
SQLServer 索引引起大量超时和死锁!https://blog.csdn.net/kk185800961/article/details/50038613
-
Sqlserver 性能优化 https://www.cnblogs.com/chenchao/articles/2121075.html
- SqlServer性能IO SET STATISTCS IO https://www.cnblogs.com/woodytu/p/4535658.html
- 初谈SQL Server逻辑读、物理读、预读 https://www.cnblogs.com/sword-successful/p/5543626.html
区分堆表与聚集索引表:
方法一:
--通过判断index_id的值,区分表的类型 --当index_id为0时,则为堆表 --当index_id为1时,则为聚集索引表 SELECT OBJECT_NAME(s.object_id) talbe_name ,CASE s.index_id WHEN 0 THEN 'heap' WHEN 1 THEN 'clustered table' END table_type FROM sys.partitions s WHERE s.index_id < 2 GROUP BY s.object_id,s.index_id
方法二:
--注意:当表为堆表时,name为NULL SELECT OBJECT_NAME(object_id) table_name, name, type_desc FROM sys.indexes WHERE index_id <2
SqlServer 并发事务:死锁跟踪(一)简单测试 地址: https://blog.csdn.net/kk185800961/article/details/41488215
程序猿是如何解决SQLServer占CPU100%的 地址:https://www.cnblogs.com/marvin/p/ASolutionForSQLServerCauseHighCPU.html
sql server在高并发状态下同时执行查询与更新操作时的死锁问题 地址:https://blog.csdn.net/ajianchina/article/details/46807131
SQLServer性能优化之---数据库级日记监控 地址:https://www.cnblogs.com/dunitian/p/6022967.html
SQL SERVER的锁机制(一)——概述(锁的种类与范围)地址:https://www.cnblogs.com/chillsrc/archive/2013/04/13/3018386.html
设置数据库为SINGLE_USER模式,减少锁定时间
ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE ALTER DATABASE dbname SET ALLOW_SNAPSHOT_ISOLATION ON ALTER DATABASE dbname SET READ_COMMITTED_SNAPSHOT ON ALTER DATABASE dbname SET MULTI_USER
为什么READ_COMMITTED_SNAPSHOT默认不开启?https://cloud.tencent.com/developer/ask/90272
The Potential Dangers of the Read Committed Snapshot Isolation Level http://www.jimmcleod.net/blog/index.php/2009/08/27/the-potential-dangers-of-the-read-committed-snapshot-isolation-level/
查询数据库中所有索引:
SELECT CASE WHEN t.[type] = 'U' THEN '表' WHEN t.[type] = 'V' THEN '视图' END AS '类型', SCHEMA_NAME(t.schema_id) + '.' + t.[name] AS '(表/视图)名称', i.[name] AS 索引名称, SUBSTRING(column_names, 1, LEN(column_names) - 1) AS '列名', CASE WHEN i.[type] = 1 THEN '聚集索引' WHEN i.[type] = 2 THEN '非聚集索引' WHEN i.[type] = 3 THEN 'XML索引' WHEN i.[type] = 4 THEN '空间索引' WHEN i.[type] = 5 THEN '聚簇列存储索引' WHEN i.[type] = 6 THEN '非聚集列存储索引' WHEN i.[type] = 7 THEN '非聚集哈希索引' END AS '索引类型', CASE WHEN i.is_unique = 1 THEN '唯一' ELSE '不唯一' END AS '索引是否唯一' FROM sys.objects t INNER JOIN sys.indexes i ON t.object_id = i.object_id CROSS APPLY ( SELECT col.[name] + ', ' FROM sys.index_columns ic INNER JOIN sys.columns col ON ic.object_id = col.object_id AND ic.column_id = col.column_id WHERE ic.object_id = t.object_id AND ic.index_id = i.index_id ORDER BY col.column_id FOR XML PATH('') ) D(column_names) WHERE t.is_ms_shipped <> 1 AND index_id > 0 ORDER BY t.[name];
查询数据库中表记录数:
SELECT OBJECT_NAME(ii.id) TableName , rows FROM sysindexes ii INNER JOIN sysobjects oo ON ( oo.id = ii.id AND oo.xtype = 'U ') WHERE ii.indid < 2 ORDER BY rows desc;
查询统计信息更新情况:
SELECT DISTINCT OBJECT_NAME(SI.object_id) AS Table_Name, SI.name AS Statistics_Name, STATS_DATE(SI.object_id, SI.index_id) AS Last_Stat_Update_Date, SSI.rowmodctr AS RowModCTR, SP.rows AS Total_Rows_In_Table, 'UPDATE STATISTICS [' + SCHEMA_NAME(SO.schema_id) + '].[' + OBJECT_NAME(SI.object_id) + ']' + SPACE(2) + SI.name AS Update_Stats_Script FROM sys.indexes AS SI ( NOLOCK ) INNER JOIN sys.objects AS SO ( NOLOCK ) ON SI.object_id = SO.object_id INNER JOIN sys.sysindexes SSI ( NOLOCK ) ON SI.object_id = SSI.id AND SI.index_id = SSI.indid INNER JOIN sys.partitions AS SP ON SI.object_id = SP.object_id WHERE SSI.rowmodctr > 0 AND STATS_DATE(SI.object_id, SI.index_id) IS NOT NULL AND SO.type = 'U' ORDER BY RowModCTR DESC
查询表物理空间大小
SELECT SUM(p.TotalSpaceMB)/1024 FROM ( SELECT t.NAME AS TableName, s.Name AS SchemaName, p.rows AS RowCounts, SUM(a.total_pages) * 8 AS TotalSpaceKB, CAST(ROUND(( ( SUM(a.total_pages) * 8 ) / 1024.00 ), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB, SUM(a.used_pages) * 8 AS UsedSpaceKB, CAST(ROUND(( ( SUM(a.used_pages) * 8 ) / 1024.00 ), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, ( SUM(a.total_pages) - SUM(a.used_pages) ) * 8 AS UnusedSpaceKB, CAST(ROUND(( ( SUM(a.total_pages) - SUM(a.used_pages) ) * 8 ) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB FROM sys.tables t INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id WHERE t.NAME NOT LIKE 'dt%' AND t.is_ms_shipped = 0 AND i.OBJECT_ID > 255 GROUP BY t.Name, s.Name, p.Rows) AS p ORDER BY p.TotalSpaceMB DESC