Sql Server日常运维看我这篇就够了！

一、基础命令

查看当前数据库的版本

SELECT @@VERSION;

查看服务器部分特殊信息

select SERVERPROPERTY(N'edition')asEdition--数据版本，如企业版、开发版等
,SERVERPROPERTY(N'collation')asCollation--数据库字符集
,SERVERPROPERTY(N'servername')asServerName--服务名
,@@VERSIONasVersion--数据库版本号
,@@LANGUAGE AS Language--数据库使用的语言，如us_english等

获取数据库当前时间

SELECT GETDATE() AS CurrentDateTime;

查看数据库启动的参数

sp_configure

查看所有数据库用户登录信息

sp_helplogins

查看数据库启动时间（最近一次）

select convert(varchar(30),login_time,120)from master..sysprocesses where spid=1

查看有多少个端口

SELECT * FROM sys.dm_tcp_listener_states;

查看当前的连接数

SELECT COUNT(*) AS [ConnectionCount] FROM sys.dm_exec_connections;

查看各个磁盘分区的剩余空间

Exec master.dbo.xp_fixeddrives

查看数据库的磁盘使用情况

Exec sp_spaceused

查看数据库服务器各数据库日志文件的大小及利用率

DBCC SQLPERF(LOGSPACE)

查看当前占用 cpu 资源最高的会话和其中执行的语句

select spid,cmd,cpu,physical_io,memusage,
(select top 1[text]from::fn_get_sql(sql_handle)) sql_text
from master..sysprocesses order by cpu desc,physical_io desc

查看缓存中重用次数少，占用内存大的查询语句（当前缓存中未释放的）

SELECT TOP 100 usecounts, objtype, p.size_in_bytes,[sql].[text]
FROM sys.dm_exec_cached_plans p OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) sql 
ORDER BY usecounts,p.size_in_bytes  desc

看BUFFER POOL中，都缓存了哪些表(当前数据库)的数据

select OBJECT_NAME(object_id)表名,COUNT(*)页数,COUNT(*)*8/1024.0Mb
from   sys.dm_os_buffer_descriptors a,sys.allocation_units b,sys.partitions c                              
where  a.allocation_unit_id=b.allocation_unit_id   
and b.container_id=c.hobt_id             
and database_id=DB_ID()
groupby OBJECT_NAME(object_id)
order by2 desc

查看用户的权限

EXEC sp_helprotect;

查看当前数据库内存使用情况

select*from sys.dm_os_process_memory

查询当前数据库缓存的所有数据页面，哪些数据表，缓存的数据页面数量

--查询当前数据库缓存的所有数据页面，哪些数据表，缓存的数据页面数量
--从这些信息可以看出，系统经常要访问的都是哪些表，有多大？
select p.object_id, object_name=object_name(p.object_id), p.index_id, buffer_pages=count(*)
from sys.allocation_units a,
    sys.dm_os_buffer_descriptors b,
    sys.partitions p 
where a.allocation_unit_id=b.allocation_unit_id 
and a.container_id=p.hobt_id 
and b.database_id=db_id()
groupby p.object_id,p.index_id 
order by buffer_pages desc

查询缓存中具体的执行计划，及对应的SQL

--查询缓存中具体的执行计划，及对应的SQL
--将此结果按照数据表或SQL进行统计，可以作为基线，调整索引时考虑
--查询结果会很大，注意将结果集输出到表或文件中
SELECT  usecounts ,
        refcounts ,
        size_in_bytes ,
        cacheobjtype ,
        objtype ,
        TEXT
FROM    sys.dm_exec_cached_plans cp
        CROSS APPLY sys.dm_exec_sql_text(plan_handle)
ORDER BY objtype DESC ;
GO

查看具体某个用户的权限

SELECT p.class_desc, OBJECT_NAME(p.major_id) AS object_name, p.permission_name, p.state_desc, u.name AS user_name
FROM sys.database_permissions p
JOIN sys.database_principals u ON p.grantee_principal_id = u.principal_id
WHERE u.name = ‘test’

查看注册时的实例名

SELECT * FROM sys.servers;

查询用户角色

selectSrvRole= g.name,MemberName= u.name,MemberSID= u.sid  
from sys.server_principals u, sys.server_principals g, sys.server_role_members m  
where g.principal_id = m.role_principal_id  
and u.principal_id = m.member_principal_id  
order by1,2
go

看服务器角色

select用户名= u.name,管理员权限= g.name,是否在用= u.is_disabled,MemberSID= u.sid  
from sys.server_principals u, sys.server_principals g, sys.server_role_members m  
where g.principal_id = m.role_principal_id  
and u.principal_id = m.member_principal_id  
and g.name ='sysadmin'
order by1,2
go

查询当前用户所有用户表

select name from sysobjects where xtype='u' order by name

查看所有的数据库

SelectName FROM Master..SysDatabases orDER BY Name

查看服务器角色相关信息

SP_HELPSRVROLE 
SP_HELPSRVROLEMEMBER 服务器角色
SP_HELPSRVROLE 服务器角色

查看数据库角色相关信息

SP_HELPROLE 
SP_HELPROLEMEMBER 数据库角色
SP_HELPROLE 数据库角色

查看用户相关信息

SP_HELPUSER 
SP_HELPUSER 数据库用户名

查看上次启动以来尝试的连接数

select@@connections//返回 SQL Server 自上次启动以来尝试的连接数，无论连接是成功还是失败

当前实例允许同时进行的最大用户连接数

select@@max_connections
//返回 SQL Server 实例允许同时进行的最大用户连接数。返回的数值不一定是当前配置的数值

查询当前最大的连接数

SELECT value_in_use
FROM sys.configurations c
WHERE c.name ='user connections';#0表示无限制

设置修改连接数

exec sp_configure 'show advanced options',1
RECONFIGURE WITH OVERRIDE
exec sp_configure 'user connections',300
RECONFIGURE WITH OVERRIDE

查询当前会话超时时间

select@@lock_timeout//返回当前会话的当前锁定超时设置（毫秒）。

查询每个用户的连接数

select loginame,count(1)asNums
from sys.sysprocesses
groupby loginame
order by2 desc
select spid,ecid,status,loginame,hostname,cmd,request_id 
from sys.sysprocesses where loginame=''and hostname=''

查看当前活动的实例

SELECT CURRENT_USER AS [CurrentUser], SESSION_USER AS [SessionUser];

查看当前活动进程

SELECT * FROM sys.dm_exec_requests;

查看所有数据库的大小

SELECT 
    DB_NAME(database_id) AS DatabaseName,
    SUM(size/128.0) AS SizeInMB,
    SUM(size/128.0)/1024 AS SizeInGB
FROM 
    sys.master_files
GROUP BY 
    database_id
ORDER BY 
SizeInMB DESC;

查看某个数据库的大小

SELECT sys.databases.name AS [DatabaseName],
    CAST(SUM(size *8/1024.0) AS DECIMAL(10,2)) AS [Size(MB)]
FROM sys.master_files
INNER JOIN sys.databases ON sys.master_files.database_id = sys.databases.database_id
WHERE sys.databases.name ='master'
GROUP BY sys.databases.name;
#也可以用EXEC sp_spaceused @updateusage = N'TRUE';

查看当前数据库的日志大小

SELECT sys.databases.name AS [DatabaseName],
    CAST(size *8/1024.0 AS DECIMAL(10,2)) AS [LogFileSize(MB)]
FROM sys.master_files
INNER JOIN sys.databases ON sys.master_files.database_id = sys.databases.database_id
WHERE sys.databases.name ='master'
    AND sys.master_files.type =1;

查询当前数据库的表和视图

SELECT TABLE_NAME AS [Table/ViewName], TABLE_TYPE AS [Type]
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE IN ('BASE TABLE','VIEW');

查询表结构信息

sp_help 'test';

二、运维小技巧

一次性清除数据库所有表的数据（高危操作，谨慎）

CREATE PROCEDURE sp_DeleteAllData  
AS  
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
EXEC sp_MSForEachTable 'ALTER TABLE ? DISABLE TRIGGER ALL'
EXEC sp_MSForEachTable 'DELETE FROM ?'
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
EXEC sp_MSForEachTable 'ALTER TABLE ? ENABLE TRIGGER ALL'
EXEC sp_MSFOREACHTABLE 'SELECT * FROM ?'
GO

数据备份与恢复

备份

BACKUP DATABASE test TO DISK ='C:\backup\MyDatabase.bak';

恢复

RESTORE DATABASE MyDatabase FROM DISK ='C:\backup\MyDatabase.bak';

完整数据库备份

BACKUP DATABASE test
TO DISK ='C:\Backup\MyDatabase.bak'
WITH FORMAT, MEDIANAME ='MyDatabase_Full', NAME ='Full Backup';

差异备份

BACKUP DATABASE test
TO DISK ='C:\Backup\MyDatabase_diff.bak'
WITH DIFFERENTIAL, FORMAT, MEDIANAME ='MyDatabase_Diff', NAME ='Differential Backup';

事务日志备份

BACKUP LOG test
TO DISK ='C:\Backup\MyDatabase_log.trn'
WITH NOFORMAT, NOINIT, NAME = N'MyDatabase_LogBackup', SKIP, NOREWIND, NOUNLOAD, STATS =10;

还原数据库

RESTORE DATABASE test
FROM DISK ='C:\Backup\MyDatabase.bak'
WITH FILE =1, NOUNLOAD, REPLACE, STATS =5;

创建账户及数据库用户

#创建账户
CREATE LOGIN test WITH PASSWORD ='123123';
#创建数据库用户并映射到登录名
CREATE USER test FOR LOGIN test;
ALTER ROLE db_datareader ADD MEMBER test;--给予读权限
ALTER ROLE db_datawriter ADD MEMBER test;--给予写权限

SQL优化相关、执行时间

SELECT creation_time  N'语句编译时间'
,last_execution_time  N'上次执行时间'
,total_physical_reads N'物理读取总次数'
,total_logical_reads/execution_count N'每次逻辑读次数'
,total_logical_reads  N'逻辑读取总次数'
,total_logical_writes N'逻辑写入总次数'
,execution_count  N'执行次数'
,total_worker_time/1000 N'所用的CPU总时间ms'
,total_elapsed_time/1000  N'总花费时间ms'
,(total_elapsed_time / execution_count)/1000  N'平均时间ms'
,SUBSTRING(st.text,(qs.statement_start_offset/2)+1,
((CASE statement_end_offset   
          WHEN -1 THEN DATALENGTH(st.text)
          ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2)+1) N'执行语句'
FROM sys.dm_exec_query_stats AS qs  
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st  
WHERE SUBSTRING(st.text,(qs.statement_start_offset/2)+1,
((CASE statement_end_offset   
          WHEN -1 THEN DATALENGTH(st.text)
          ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2)+1) NOT LIKE '%fetch%'
ORDER BY  total_elapsed_time / execution_count DESC;

查看job运行持续时间

SELECT 
[T1].[job_id]
,[T1].[name] AS [job_name]
,[T2].[run_status]
,[T2].[run_date]
,[T2].[run_time]
,[dbo].[agent_datetime]([T2].[run_date],[T2].[run_time]) AS [run_datetime]
,[T2].[run_duration]
,DATEDIFF(SECOND,'1900-01-01', DATEADD(SECOND,31,[dbo].[agent_datetime](19000101,[run_duration]))) AS [run_duration_s]
FROM 
[dbo].[sysjobs] AS T1
    INNER JOIN [dbo].[sysjobhistory] AS T2
        ON [T2].[job_id]=[T1].[job_id]
WHERE 
[T1].[enabled]=1
    AND [T2].[step_id]=0
    AND [T2].[run_duration]>=1
and[T1].[name]='PIMS_CreatePaperCraftParameterAnalysisData'
ORDER BY
[T2].[job_id] ASC
,[T2].[run_date] ASC
GO

从所有缓存中释放所有未使用的缓存条目

DBCC FREESYSTEMCACHE('ALL');

查询、解除死锁

--查询表死锁信息
select object_name(resource_associated_entity_id)as tableName, request_session_id as pid from sys.dm_tran_locks
where resource_type ='OBJECT'
dbcc opentran
--查看死锁的详细信息、执行的sql语句
exec sp_who2 53
--exec sp_who 53
DBCC inputbuffer (53)
--解除死锁
kill 53

查询SQL Server根据CPU消耗列出前5个最差性能的查询

--Worst performing CPU bound queries
SELECT TOP 5
    st.text,
    qp.query_plan,
    qs.*
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY total_worker_time DESC
GO

查询数据库中各数据表大小

--=============================================
--描述：更新查询数据库中各表的大小，结果存储到数据表中
--=============================================
--查询是否存在结果存储表
    IF NOT EXISTS (SELECT * FROM sysobjects where id = OBJECT_ID(N'temp_tableSpaceInfo') AND OBJECTPROPERTY(id, N'IsUserTable')=1)
BEGIN
--不存在则创建
        CREATE TABLE temp_tableSpaceInfo
(name NVARCHAR(128),
        rows char(11),
        reserved VARCHAR(18),
        data VARCHAR(18),
        index_size VARCHAR(18),
        unused VARCHAR(18))
END
--清空数据表
    DELETE FROM temp_tableSpaceInfo
--定义临时变量在遍历时存储表名称
    DECLARE @tablename VARCHAR(255)
--使用游标读取数据库内所有表表名
    DECLARE table_list_cursor CURSOR FOR 
    SELECT name FROM sysobjects 
    WHERE OBJECTPROPERTY(id, N'IsTable')=1 AND name NOT LIKE N'#%%' ORDER BY name
--打开游标
    OPEN table_list_cursor
--读取第一条数据
    FETCH NEXT FROM table_list_cursor INTO @tablename
--遍历查询到的表名
    WHILE @@FETCH_STATUS=0
BEGIN
--检查当前表是否为用户表
        IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(@tablename) AND OBJECTPROPERTY(id, N'IsUserTable')=1)
BEGIN
--当前表则读取其信息插入到表格中
            EXECUTE sp_executesql N'INSERT INTO temp_tableSpaceInfo EXEC sp_spaceused @tbname', N'@tbname varchar(255)',@tbname=@tablename
END
--读取下一条数据
        FETCH NEXT FROM table_list_cursor INTO @tablename
END
--释放游标
    CLOSE table_list_cursor
    DEALLOCATE table_list_cursor
    SELECT *,replace(reserved,'KB','')/1024数据表大小M FROM temp_tableSpaceInfo order by replace(reserved,'KB','')/1024 desc
    drop table temp_tableSpaceInfo

显示如何依据I/O消耗来找出你性能最差的查询

--Worst performing I/O bound queries
SELECT TOP 5
    st.text,
    qp.query_plan,
    qs.*
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY total_logical_reads DESC
GO

压缩数据库、文件、日志

DBCC ShrinkFile(‘数据库名’,  targetsize);/* 收缩数据库文件 */
DBCC ShrinkFile(‘数据库名_log’,  targetsize);/* 收缩日志文件 */
Targetsize：单位为兆，必须为整数，DBCC SHRINKFILE 尝试将文件收缩到指定大小。
DBCC SHRINKFILE 不会将文件收缩到小于“实际使用的空间”大小，例如“分配空间”为10M，“实际使用空间”为6M，当制定targetsize为1时，则将该文件收缩到6M，不会将文件收缩到1M。
--收缩数据库
DBCC SHRINKDATABASE(数据库名，百分比)
百分比：即“收缩后文件中的最大可用空间”，取值范围“大于等于0，小于100%”，实际使用中设为0即可。

查询数据库表字段各项属性信息，便于直接复制导出excel表

SELECT  
表名=CaseWhen A.colorder=1Then D.name Else''End,
表说明=CaseWhen A.colorder=1Then isnull(F.value,'')Else''End,
字段序号= A.colorder,
字段名= A.name,
字段说明= isnull(G.[value],''),
标识=CaseWhen COLUMNPROPERTY( A.id,A.name,'IsIdentity')=1Then'√'Else''End,
主键=CaseWhen exists(SELECT 1 FROM sysobjects Where xtype='PK'and parent_obj=A.id and name in(
                      SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = A.id AND colid=A.colid)))then'√'else''end,
类型= B.name,
占用字节数= A.Length,
长度= COLUMNPROPERTY(A.id,A.name,'PRECISION'),
小数位数= isnull(COLUMNPROPERTY(A.id,A.name,'Scale'),0),
允许空=CaseWhen A.isnullable=1Then'√'Else''End,
默认值= isnull(E.Text,'')
 FROM  
     syscolumns A  
LeftJoin
     systypes B  
On
     A.xusertype=B.xusertype  
InnerJoin
     sysobjects D  
On
     A.id=D.id  and D.xtype='U'and  D.name<>'dtproperties'
LeftJoin
     syscomments E  
 on  
     A.cdefault=E.id  
LeftJoin
 sys.extended_properties  G  
 on  
     A.id=G.major_id and A.colid=G.minor_id  
LeftJoin
 sys.extended_properties F  
On
     D.id=F.major_id and F.minor_id=0
--where d.name='OrderInfo'--如果只查询指定表,加上此条件
OrderBy
     A.id,A.colorder

数据库缓存清理

CREATE PROCEDURE [dbo].ClearMemory
AS   
BEGIN
--清除所有缓存
    DBCC DROPCLEANBUFFERS 
--打开高级配置
exec sp_configure 'show advanced options',1
--设置最大内存值，清除现有缓存空间
exec sp_configure 'max server memory',25600
    EXEC ('RECONFIGURE')
--设置等待时间
    WAITFOR DELAY '00:00:01'
--重新设置最大内存值
    EXEC  sp_configure 'max server memory',40960
    EXEC ('RECONFIGURE')
--关闭高级配置
exec sp_configure 'show advanced options',0
END
GO

三、日常运维操作

数据库用户、权限操作

USE [master]
GO
--待确认账号密码
CREATE LOGIN [NDIT] WITH PASSWORD=N'1', DEFAULT_DATABASE=[PIMS], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE PIMS
go
CREATE USER [NDIT] FOR LOGIN [NDIT]
GO
--大权限，如果是指定的部分表，不执行这个，如果是所有内容都可以读，用此脚本
--EXEC sp_addrolemember N'db_datareader', N'NDIT'
--GO
--指定特定表名赋予新增/更新/查询
DECLARE @Sql NVARCHAR(max)
SET @Sql=''
--table
--SELECT @Sql=@Sql+'GRANT INSERT,UPDATE,SELECT ON ['+a.name+'] TO [NDIT];' FROM sys.tables AS a WHERE name IN ('Tab1','Tab2');
--view
--SELECT @Sql=@Sql+'GRANT INSERT,UPDATE,SELECT ON ['+a.name+'] TO [NDIT];' FROM sys.views AS a WHERE name IN ('view1','view2');
--procedure
--SELECT @Sql=@Sql+'GRANT INSERT,UPDATE,SELECT ON ['+a.name+'] TO [NDIT];' FROM sys.procedures AS a WHERE name IN ('proc1','proc2');
PRINT @Sql
EXEC(@Sql)
go
--禁用登陆帐户
alter login NDIT disable
--启用登陆帐户
alter login NDIT enable
--登陆帐户改名
alter login NDIT with name=dba_tom
--登陆帐户改密码：
alter login NDIT with password='aabb@ccdd'
--数据库用户改名：
alter user NDIT with name=dba_tom
--更改数据库用户 defult_schema：
alter user NDIT with default_schema=sales
--删除数据库用户：
drop user NDIT
--删除 SQL Server登陆帐户：
drop login NDIT

文中的概念来源于网络，如有侵权，请联系我删除。

整理：小周的数据库进阶之路