sql server 纪实回忆录 SQL Server数据库表索引碎片整理

• 查询索引的缺失 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

 

区分堆表与聚集索引表:

方法一:

--通过判断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; 

 

 

转载于:https://www.cnblogs.com/niceletter/p/11152225.html