一、基础命令
查看当前数据库的版本
-
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 ?'
-
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
-
表名 = 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
-
文中的概念来源于网络,如有侵权,请联系我删除。