SQL server 日常运维命令

26 阅读 0 评论 0 点赞

一、基础命令

查看当前数据库的版本

SELECT @@VERSION;

查看服务器部分特殊信息

select SERVERPROPERTY(N'edition') as Edition     --数据版本，如企业版、开发版等
    ,SERVERPROPERTY(N'collation') as Collation   --数据库字符集
    ,SERVERPROPERTY(N'servername') as ServerName --服务名
    ,@@VERSION as Version   --数据库版本号
    ,@@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 [Connection Count] 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.0 Mb                              
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()                              
group by OBJECT_NAME(object_id)                           
order by 2 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()
group by 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;

查询用户角色

select SrvRole = 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 by 1, 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 by 1, 2
go

查询当前用户所有用户表

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

查看所有的数据库

Select Name 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) as Nums
from sys.sysprocesses
group by loginame
order by 2 desc
select spid,ecid,status,loginame,hostname,cmd,request_id 
from sys.sysprocesses where loginame='' and hostname=''

查看当前活动的实例

SELECT CURRENT_USER AS [Current User], SESSION_USER AS [Session User];

查看当前活动进程

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 [Database Name], 
    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 [Database Name], 
    CAST(size * 8 / 1024.0 AS DECIMAL(10,2)) AS [Log File 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'
    AND sys.master_files.type = 1;

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

SELECT TABLE_NAME AS [Table/View Name], 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 ?'

数据备份与恢复

备份
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  
     表名       = Case When A.colorder=1 Then D.name Else '' End,  
     表说明     = Case When A.colorder=1 Then isnull(F.value,'') Else '' End,  
     字段序号   = A.colorder,  
     字段名     = A.name,  
     字段说明   = isnull(G.[value],''),  
     标识       = Case When COLUMNPROPERTY( A.id,A.name,'IsIdentity')=1 Then '√'Else '' End,  
     主键       = Case When 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),  
     允许空     = Case When A.isnullable=1 Then '√'Else '' End,  
     默认值     = isnull(E.Text,'')  
 FROM  
     syscolumns A  
 Left Join  
     systypes B  
 On  
     A.xusertype=B.xusertype  
 Inner Join  
     sysobjects D  
 On  
     A.id=D.id  and D.xtype='U' and  D.name<>'dtproperties'  
 Left Join  
     syscomments E  
 on  
     A.cdefault=E.id  
 Left Join  
 sys.extended_properties  G  
 on  
     A.id=G.major_id and A.colid=G.minor_id  
 Left Join  

 sys.extended_properties F  
 On  
     D.id=F.major_id and F.minor_id=0  
     --where d.name='OrderInfo'    --如果只查询指定表,加上此条件  
 Order By  
     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

本站资源均来自互联网，仅供研究学习，禁止违法使用和商用，产生法律纠纷本站概不负责！如果侵犯了您的权益请与我们联系！

转载请注明出处：免费源码网-免费的源码资源网站 » SQL server 日常运维命令

点赞(0) 打赏

本文分类：文章资讯
本文标签：SQL server 日常运维命令
浏览次数：26 次浏览
本文链接：https://freeymw.com/article/27693.html

评论列表共有 0 条评论

暂无评论

SQL server 日常运维命令

一、基础命令

查看当前数据库的版本

查看服务器部分特殊信息

获取数据库当前时间

查看数据库启动的参数

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

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

查看有多少个端口

查看当前的连接数

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

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

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

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

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

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

查看用户的权限

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

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

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

查看具体某个用户的权限

查看注册时的实例名

查询用户角色

看服务器角色

查询当前用户所有用户表

查看所有的数据库

查看服务器角色相关信息

查看数据库角色相关信息

查看用户相关信息

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

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

查询当前最大的连接数

设置修改连接数

查询当前会话超时时间

查询每个用户的连接数

查看当前活动的实例

查看当前活动进程

查看所有数据库的大小

查看某个数据库的大小

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

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

查询表结构信息

二、运维小技巧

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

数据备份与恢复

完整数据库备份

差异备份

事务日志备份

还原数据库

创建账户及数据库用户

SQL优化相关、执行时间

查看job运行持续时间

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

查询、解除死锁

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

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

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

压缩数据库、文件、日志

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

数据库缓存清理

三、日常运维操作

数据库用户、权限操作

评论列表 共有 0 条评论

发表评论 取消回复

评论列表共有 0 条评论

发表评论取消回复