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

 

区分堆表与聚集索引表:

方法一:

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

 

posted @ 2019-07-08 16:50  超级飞猪猪侠  阅读(413)  评论(0)  编辑  收藏  举报