SqlServer日常维护中，常用SQL备份
1、–查看数据库版本信息 1
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY('edition');
--查看数据库版本信息 2
SELECT @@VERSION;
--查看系统信息
exec master..xp_msver;
结果如下图：
2、–查看全部数据库信息，如日志模式：
SELECT NAME, database_id,create_date 创建时间,is_auto_shrink_on 自动收缩, state_desc 状态,
recovery_model_desc 恢复模式, is_published 是否发布库,log_reuse_wait_desc 日志重用状态
FROM sys.databases ;
结果如图，对于下图中，如果恢复模式为FULL的，可以改成SIMPLE来节约日志空间占用：
--修改数据库日志模式：Simple,Full,BULK_LOGGED
ALTER DATABASE [数据库名] SET RECOVERY SIMPLE WITH NO_WAIT
--设置自动收缩
ALTER DATABASE [数据库名] SET AUTO_SHRINK ON WITH NO_WAIT
3、– 获取当前数据库空间占用 和 空闲空间情况，size以8 KB 为单位，所以除128就是MB
SELECT name [文件组名],
data_space_id [文件组id],
size/128 [文件大小(兆)],
FILEPROPERTY(name, 'SpaceUsed')/128 [已用空间(兆)],
size/128 - FILEPROPERTY(name, 'SpaceUsed')/128 [未用空间(兆)],
FILEPROPERTY(name, 'SpaceUsed')*100.0/size [使用率(%)],
max_size/128 [最大值(兆)],
case is_percent_growth when 0 then cast(growth/128 as nvarchar) + '兆' else cast(growth as nvarchar) + '%' end [增长值],
physical_name [物理路径]
FROM sys.database_files a ORDER BY a.[name]
结果如下图，未用空间表示 文件占用的大小中有一部分是没释放的空间，可以给后续的数据使用：
注：Sql2005建议使用sys.database_files，sysfiles是为了兼容Sql2000才有的
前者表定义参考：http://msdn.microsoft.com/zh-cn/library/ms174397.aspx
后者兼容表定义参考：http://msdn.microsoft.com/zh-cn/library/ms178009.aspx
4、– 获取表的行数，以及每个表占用的索引空间和数据空间
SELECT top 111 a.id,b.name,a.rowcnt AS [行数],
ISNULL((select v.low/1024.0 * SUM(a.used_pages - CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)
FROM sys.indexes as i
JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id
JOIN sys.allocation_units as a ON a.container_id = p.partition_id
where i.object_id = b.object_id),0.0)
AS [索引空间(KB)],
ISNULL((select v.low/1024.0 * SUM(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)
FROM sys.indexes as i
JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id
JOIN sys.allocation_units as a ON a.container_id = p.partition_id
where i.object_id = b.object_id),0.0)
AS [数据空间(KB)]
FROM sys.tables AS b,sysindexes a,master.dbo.spt_values v
WHERE a.id = b.[object_id] AND a.indid <=1 AND v.number=1 and v.type='E' ORDER BY b.[name]
结果如下图：
5、– 获取所有表结构和行号
SELECT b.colorder,a.name table_name,b.name column_name,d.value comments, -- 字段注释e.name data_type, -- 字段类型b.length char_length, -- 指示nvarchar的长度(不是表定义里的长度)b.scale data_scale, -- 指示number小数的长度b.isnullable, -- 字段是否允许为空值,1允许,0不允许f.text column_default -- 字段默认值
FROM sys.sysobjects a
INNER JOIN sys.syscolumns b ON a.id = b.idLEFT JOIN sys.extended_properties d on d.major_id = b.id and d.minor_id = b.colid
INNER JOIN systypes e ON e.xusertype = b.xusertypeLEFT JOIN syscomments f ON b.cdefault = f.id
ORDER BY a.name, b.colorder结果如下图：
6、– 获取所有表的索引和包含的字段：
select tb.name tablename, idx.name idxname,tp.name coltype,idxcol.index_column_id colIdx, col.name colname, idx.type_descfrom sys.columns col, sys.index_columns idxcol,sys.indexes idx,sys.tables tb,sys.types tp
where col.column_id=idxcol.column_id and idxcol.index_id=idx.index_id and idx.object_id=tb.object_id and idxcol.object_id=idx.object_idand col.object_id=idx.object_id and tp.user_type_id=col.user_type_id
order by tb.name, idx.name, idxcol.index_column_id结果如下图：
7、查看所有 数据库的备份历史记录（注：一个备份可能有多个文件）
SELECT database_name,backup_start_date 开始,backup_finish_date 结束,expiration_date 过期,
floor(backup_size/1024/1024) [数据(兆)],floor(compressed_backup_size/1024/1024) [备份文件(兆)],
case [type] when 'l' then '日志备份' when 'd' then '全量备份' when 'i' then '差异备份' else '未知' end baktype, b.physical_device_name
FROM msdb.dbo.backupset a, msdb.dbo.backupmediafamily b
where a.media_set_id=b.media_set_id
ORDER BY backup_finish_date desc结果如下：
8、按CPU占用排序，取前100条sql（注：也可以按AvgIO排序）：
SELECT TOP 100rank() OVER ( ORDER BY ( total_worker_time + 0.0 ) / execution_count DESC, sql_handle, statement_start_offset ) AS row_no ,( rank() OVER ( ORDER BY ( total_worker_time + 0.0 ) / execution_count DESC, sql_handle, statement_start_offset ) )% 2 AS l1 ,creation_time ,last_execution_time ,( total_worker_time + 0.0 ) / 1000 AS total_worker_time ,( total_worker_time + 0.0 ) / ( execution_count * 1000 ) AS [AvgCPUTime] ,total_logical_reads AS [LogicalReads] ,total_logical_writes AS [LogicalWrites] ,execution_count ,total_logical_reads + total_logical_writes AS [AggIO] ,( total_logical_reads + total_logical_writes ) / ( execution_count+ 0.0 ) AS [AvgIO] ,CASE WHEN sql_handle IS NULL THEN ''ELSE ( SUBSTRING(st.text, ( qs.statement_start_offset + 2 ) / 2,( CASE WHEN qs.statement_end_offset = -1THEN LEN(CONVERT(NVARCHAR(MAX), st.text))* 2ELSE qs.statement_end_offsetEND - qs.statement_start_offset ) / 2) )END AS query_text ,DB_NAME(st.dbid) AS db_name ,st.objectid AS object_id
FROM sys.dm_exec_query_stats qsCROSS APPLY sys.dm_exec_sql_text(sql_handle) st
WHERE total_worker_time > 0
ORDER BY [AvgCPUTime] DESC结果如下图：
9、查看表和索引碎片情况：
-- 大概运行1分钟左右，最后一个参数null表示LIMITED，可改为 SAMPLED 或 DETAILED，查询更多细节，但是更慢
select b.name,a.*
from sys.dm_db_index_physical_stats(db_id(),null,null,null,null) a, sys.sysobjects b
where a.object_id=b.id 文档：https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2008-r2/ms188917(v=sql.105)
查询结果字段含义：
database_id/object_id/index_id：数据库id/表或视图id/索引id；
partition_number：分区号，1为未分区的索引或堆；
index_type_desc：索引类型描述；
alloc_unit_type_desc：分配单元类型，IN_ROW_DATA表示LOB大型数据以外的数据存储空间，
- LOB_DATA表示有text/image/varchar(max)类型的字段，单独分配空间，
- ROW_OVERFLOW_DATA表示超过 8,060 字节行大小限制的 varchar等可变长度数据。
参考：https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2008-r2/ms189051(v=sql.105)
https://blog.csdn.net/kk185800961/article/details/48177907
index_depth：索引级别数；
index_level：查询参数为DETAILED时有效，索引当前层级，0为叶子级别；
avg_fragmentation_in_percent：索引碎片率，此值越小越好（LOB_DATA 和 ROW_OVERFLOW_DATA为0）；
fragment_count：总的碎片数；
avg_fragment_size_in_pages：每个碎片包含的平均页数，当然越大越好，跟avg_fragmentation_in_percent相反；
page_count：索引或数据页总数；
avg_page_space_used_in_percent：数据页空间使用率，参考概念：填充因子、页拆分、页填充率；
record_count：总纪录数（不精确，准确值要用 SELECT COUNT(*) ）；
min_record_size_in_bytes/max_record_size_in_bytes/avg_record_size_in_bytes：最小/最大/平均记录字节数；
forwarded_record_count：页拆分的记录数目
关键在avg_fragmentation_in_percent这个字段，如果碎片率超过10%，要考虑整理碎片，微软的建议是：
５％＜碎片率<30% 用索引重组进行整理： ALTER INDEX [索引名] on [表名] REORGANIZE
碎片率大于30%，应该重建索引：ALTER INDEX [索引名] on [表名] REBUILD
也可以用DBCC（下面的SQL）查看单个表的碎片情况：
DBCC SHOWCONTIG ('表名') WITH ALL_INDEXES
-- 执行结果：
表: 'Tickets' (309576141)；索引 ID: 1，数据库 ID: 14
已执行 TABLE 级别的扫描。
- 扫描页数................................: 2525
- 扫描区数..............................: 319
- 区切换次数..............................: 2520
- 每个区的平均页数........................: 7.9
- 扫描密度 [最佳计数:实际计数].......: 12.53% [316:2521]
- 逻辑扫描碎片 ..................: 98.77%
- 区扫描碎片 ..................: 12.54%
- 每页的平均可用字节数.....................: 2732.1
- 平均页密度(满).....................: 66.25%（1）扫描页数/扫描区数：页是数据管理的最小单位，大小8KB；区是磁盘上连续的8个页
（2）区切换次数：遍历表或索引的页时，DBCC 语句从一个区移动到另一个区的次数。
（3）扫描密度 [最佳计数:实际计数]：
最佳计数：所有内容连续时的区更改理想数量。
实际计数：遍历表或索引的页时，区更改实际数量。
扫描密度：如果所有内容都是连续的，则值为 100；如果小于100，则存在碎片。
（4）逻辑扫描碎片：扫描索引的叶级页时返回的出错页的百分比。
此数与堆无关。 对于出错页，分配给索引的下一个物理页不是由当前叶级页中的“下一页”指针所指向的页。
实际上，逻辑碎片是在索引的叶级别中次序混乱页面的百分比。
（5）区扫描碎片：扫描索引的叶级页时出错区所占的百分比。 解释同上。
（6）每页的平均可用字节数：此数字越大，则页的填充程度越低。如果索引不会有很多随机插入，则数字越小越好。
此数字还受行大小影响：行越大，此数字就越大。
（7）平均页密度）：页的平均密度，以百分比表示。该值会考虑行大小。因此，该值可以更准确地指示页的填充程度。百分比越大越好。
本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！