sqlserver oracle语句,sqlserver dba常用的sql语句

sqlserver查看实例级别的信息，使用SERVERPROPERTY函数

select SERVERPROPERTY ('propertyname')

查看实例级别的某个参数XX的配置

select * from sys.configurations where name='XX'

更改实例级别的某个参数XX的值

sp_configure 'XX','0'

RECONFIGURE WITH OVERRIDE

sp_configure显示或更改当前服务器的全局配置设置。

RECONFIGURE表示SQL Server不用重新启动就立即生效

使用sp_configure更改设置时，请使用RECONFIGURE语句使更改立即生效，否则更改将在SQL Server重新启动后生效。RECONFIGURE后面加WITH OVERRIDE表示不管这个值是不是符合要求都会生效，比如recovery interval的范围值是10--60对应sys.configurations.minimum是10、sys.configurations.maximum是60，如果sp_configure 'recovery interval', 75设置为75，超过了这个10--60规范，但是要让75生效，则必须加上WITH OVERRIDE

sqlserver没有系统表可以查询所有数据库下面对象

如下只能在当前数据库下面查

select * from sys.all_objects --查询当前数据库的所有架构范围的对象

select * from sys.sysobjects --查询当前数据库的所有对象

--sys.all_objects、sys.sysobjects这种的视图，在每个数据库的系统视图下面都有

select * from sys.databases --在当前数据库下可以查询到所有数据库信息，包含是否on状态

select * from sys.sysdatabases --在当前数据库下可以查询到所有数据库信息，不包含是否on状态，这个系统视图会在后续的版本中删除

--sys.databases、sys.sysdatabases这种的视图，在每个数据库的系统视图下面都有

sys.processes --没有这个视图

select * from sys.sysprocesses --在当前数据库下可以查询所有正在SQL Server 实例上运行的进程的相关信息，也就是所有数据库上的线程，这个系统视图会在后续的版本中删除

全局系统视图、单个数据库系统视图

sys.database_files --每个存储在数据库本身中的数据库文件在表中占用一行。 这是一个基于每个数据库的视图。

sys.master_files --master 数据库中的每个文件对应一行。 这是一个系统范围视图。

--sys.database_files、sys.master_files这种的视图，在每个数据库的系统视图下面都有

一些只存在msdb的系统表，而非系统视图

dbo.backupset

dbo.log_shipping_secondary

dbo.restorehistory

dbo.sysjobs

dbo.sysjobhistory

--这些系统表只存在msdb数据库，使用的时候必须加上msdb前缀

sp_lock、sp_who、sp_who2、sp_helptext等一些系统存储过程存在于每个数据库中

报告有关锁的信息，会显示实例里面的所有数据库的锁信息、堵塞信息

sp_lock

提供有关当前用户、 会话和进程的实例中的信息，可以看到会话的状态running、SUSPENDED、sleeping、rollback，sp_who2通过CPUTime、DiskIO可以判断对应的transaction是否很大

sp_who

sp_who2

sp_who2 active (可选参数LoginName, 或active代表活动会话数)

其中sp_who2除了显示sp_who的输出信息外,，还显示下面的信息：

CPUTime (进程占用的总CPU时间)

DiskIO (进程对磁盘读的总次数)

LastBatch (客户最后一次调用存储过程或者执行查询的时间)

ProgramName (用来初始化连接的应用程序名称，或者主机名)

查看某个存储过程的内容

sp_helptext pro_name

DBCC INPUTBUFFER

显示某个线程号发送到sqlserver数据库的最后一个语句

DBCC INPUTBUFFER (249)

假设查询到249被锁给堵塞了，执行上面的可以查到被堵塞的SQL语句

查看某个数据库中是否存在活动事务，有活动事务就一定会写日志，显示结果为最早的一个事务的ID

DBCC OPENTRAN (dbname)

监视日志空间

DBCC SQLPERF (LOGSPACE)

查找无法重用日志中的空间的原因(日志无法截断导致日志文件越来越大，但是可用空间很小，无法收缩)

select name,log_reuse_wait_desc from sys.databases

查看虚拟日志文件信息

DBCC LOGINFO

结果有多少行，代表有多少虚拟日志文件，活动的虚拟日志文件的状态(status)为2

修复msdb数据库，比如ssms页面sql server agent丢失或看不了job view history等功能，说明msdb坏了，需要修复

dbcc checkdb (msdb);

在您当前连接到的 SQL Server 数据库中生成一个手动检查点

CHECKPOINT [ checkpoint_duration ]

--checkpoint_duration表示以秒为单位指定手动检查点完成所需的时间，一般不使用这个参数，让数据库自己控制

查看数据库各种设置

select name,State,user_access,is_read_only,recovery_model from sys.databases

查看某个数据库中是否存在会话

select DB_NAME(dbid),* from sys.sysprocesses where dbid=db_id('dbname')

查询当前阻塞的所有请求

select * from sys.sysprocesses where blocked>0

或

SELECT t1.resource_type,db_name(t1.resource_database_id),t1.resource_associated_entity_id,t1.request_mode,

t1.request_session_id,t2.blocking_session_id,t2.wait_duration_ms

FROM sys.dm_tran_locks as t1

INNER JOIN sys.dm_os_waiting_tasks as t2

ON t1.lock_owner_address = t2.resource_address;

或

select A.SPID as 被阻塞进程,a.CMD AS 正在执行的操作,b.spid AS 阻塞进程号,b.cmd AS 阻塞进程正在执行的操作

from master..sysprocesses a,master..sysprocesses b

where a.blocked<>0 and a.blocked= b.spid

或

SELECT session_Id,spid,ecid,DB_NAME (sp.dbid),nt_username,er.status,wait_type,

[Individual Query] =SUBSTRING (qt.text,er.statement_start_offset / 2,

( CASE

WHEN er.statement_end_offset = -1

THEN

LEN (CONVERT (NVARCHAR (MAX), qt.text)) * 2

ELSE

er.statement_end_offset

END

- er.statement_start_offset)

/ 2),

qt.text,program_name,Hostname,nt_domain,start_time

FROM sys.dm_exec_requests er

INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid

CROSS APPLY sys.dm_exec_sql_text (er.sql_handle) AS qt

WHERE session_Id > 50 /* Ignore system spids.*/

AND sp.blocked>0 AND session_Id NOT IN (@@SPID)

或

SELECT session_id ,status ,blocking_session_id

,wait_type ,wait_time ,wait_resource

,transaction_id

FROM sys.dm_exec_requests

WHERE status = N'suspended';

--sys.dm_exec_requests返回SQL Server 中正在执行的每个请求的信息

查看哪些表被锁了，以及这些表被哪个进程锁了：

select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableName

from sys.dm_tran_locks where resource_type='OBJECT' ORDER BY request_session_id ASC

查询某个job是否被堵塞

select b.name,a.* from master..sysprocesses a,msdb.dbo.sysjobs b  where SUBSTRING(REPLACE(a.PROGRAM_NAME, 'SQLAgent - TSQL JobStep (Job ', ''), 1, 34)=master.dbo.fn_varbintohexstr(CONVERT(varbinary(16), b.job_id)) and b.name='jobname'

检查SQL Agent是否开启

IF EXISTS (

SELECT TOP 1 1

FROM sys.sysprocesses

WHERE program_name = 'SQLAgent - Generic Refresher'

)

SELECT 'Running'

ELSE

SELECT 'Not Running'

查看活动线程执行的sql语句,并生成批量杀掉的语句

select 'KILL '+CAST(a.spid AS NVARCHAR(100)) AS KillCmd,REPLACE(hostname,' ','') as hostname ,replace(program_name,' ','') as program_name

,REPLACE(loginame, ' ', '') AS loginame, db_name(a.dbid) AS DBname,spid,blocked,waittime/1000 as waittime

,a.status,Replace(b.text,'''','''') as sqlmessage,cpu

from sys.sysprocesses as a with(nolock)

cross apply sys.dm_exec_sql_text(sql_handle) as b

where a.status<>'sleeping' AND a.spid<>@@SPID

查看备份进度

SELECT DB_NAME(database_id) AS Exec_DB

,percent_complete

,CASE WHEN estimated_completion_time < 36000000

THEN '0' ELSE '' END + RTRIM(estimated_completion_time/1000/3600)

+ ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%3600/60), 2)

+ ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%60), 2) AS [Time Remaining]

,b.text as tsql

,*

FROM SYS.DM_EXEC_REQUESTS

cross apply sys.dm_exec_sql_text(sql_handle) as b

WHERE command LIKE 'Backup%' --and database_id=db_id('cardorder')

--OR command LIKE 'RESTORE%'

ORDER BY 2 DESC

查看恢复进度

SELECT DB_NAME(database_id) AS Exec_DB

,percent_complete

,CASE WHEN estimated_completion_time < 36000000

THEN '0' ELSE '' END + RTRIM(estimated_completion_time/1000/3600)

+ ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%3600/60), 2)

+ ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%60), 2) AS [Time Remaining]

,b.text as tsql

,*

FROM SYS.DM_EXEC_REQUESTS

cross apply sys.dm_exec_sql_text(sql_handle) as b

WHERE command LIKE 'RESTORE%' --and database_id=db_id('cardorder')

--OR command LIKE 'RESTORE%'

ORDER BY 2 DESC

查看数据库的最近备份信息

SELECT database_name,type,MAX(backup_finish_date) AS backup_finish_date FROM msdb.dbo.backupset GROUP BY database_name,type ORDER BY database_name,type

备注： D 表示全备份，i 表示差异备份，L 表示日志备份

查询备份信息

SELECT

bs.backup_set_id,

bs.database_name,

bs.backup_start_date,

bs.media_set_id,

bmf.physical_device_name,

bmf.media_family_id,

bs.*

FROM

msdb.dbo.backupmediafamily bmf

INNER JOIN

msdb.dbo.backupset bs ON bs.media_set_id = bmf.media_set_id

INNER JOIN

(

SELECT

database_name,

MAX(backup_start_date) as backup_start_date

FROM

msdb.dbo.backupset bs

WHERE

type = 'd'

GROUP BY

database_name

) de on bs.database_name = de.database_name

and bs.backup_start_date = de.backup_start_date

and bs.type = 'd'

查看数据库的历史备份记录，并生成restore语句

SELECT

CONVERT(CHAR(100),SERVERPROPERTY('Servername'))AS Server,

bs.database_name,

bs.backup_start_date,

bs.backup_finish_date,

bs.expiration_date,

CASE bs.type

WHEN 'D' THEN 'Database'

WHEN 'L' THEN 'Log'

END AS backup_type,

bs.backup_size,

bmf.logical_device_name,

bmf.physical_device_name,

bs.name AS backupset_name,

bs.description,

'RESTORE DATABASE ['+bs.database_name+'] FROM DISK=N'''

+bmf.physical_device_name+ '''WITH NORECOVERY;'

FROM msdb.dbo.backupmediafamily bmf

INNER JOIN msdb.dbo.backupset bs

ON bmf.media_set_id=bs.media_set_id

WHERE bs.backup_start_date>DATEADD(DAY,-1,GETDATE())

ORDER BY bs.backup_finish_date

查询XX库从YYYY-MM-DD日期开始的日志备份记录，并生成restore log的语句

SELECT TOP 1000

S.database_name [Database],

CASE [S].[type]

WHEN 'L'

THEN N'RESTORE LOG ' + QUOTENAME(S.database_name) + N' FROM DISK = ''' + F.physical_device_name + N''' WITH NORECOVERY;'

END [LogRestore],

F.physical_device_name,

S.[Type],

S.backup_start_date,

S.backup_finish_date

FROM msdb.dbo.backupmediafamily F

INNER JOIN msdb.dbo.backupset S

ON S.media_set_id = F.media_set_id

WHERE S.database_name = 'XX' AND

S.type = 'L' AND S.backup_start_date > 'YYYY-MM-DD' ORDER BY S.backup_start_date ASC

查询always on状态是否正常

select dc.database_name, d.synchronization_health_desc, d.synchronization_state_desc, d.database_state_desc from sys.dm_hadr_database_replica_states d join sys.availability_databases_cluster dc on d.group_database_id=dc.group_database_id and d.is_local=1

查看mirror镜像信息

SELECT

db_name(database_id),

mirroring_state_desc,

mirroring_role_desc,

mirroring_partner_name,

mirroring_partner_instance

FROM sys.database_mirroring

查询SSRS Report Subscriptions相关的job

SELECT

b.name AS JobName

, e.name

, e.path

, d.description

, a.SubscriptionID

, laststatus

, eventtype

, LastRunTime

, date_created

, date_modified

FROM

ReportServer.dbo.ReportSchedule a

JOIN msdb.dbo.sysjobs b ON CONVERT(SYSNAME,a.ScheduleID) = b.name

JOIN ReportServer.dbo.ReportSchedule c ON b.name = CONVERT(SYSNAME,c.ScheduleID)

JOIN ReportServer.dbo.Subscriptions d ON c.SubscriptionID = d.SubscriptionID

JOIN ReportServer.dbo.Catalog e ON d.report_oid = e.itemid

WHERE

e.name = 'Report Name Goes Here'

查看某个数据库的数据文件信息，就算是mirror从库的数据文件也可以查到，filestream目录也可以查到

SELECT db_name(database_id),* FROM master.sys.master_files WHERE database_id =DB_ID(N'DBA');

查看某个数据文件信息

select b.name,a.type_desc,a.name,a.physical_name,a.size,a.max_size,a.is_percent_growth,a.growth from sys.master_files a join sys.databases b on a.database_id=b.database_id and a.physical_name like '%DTSWonda_1%'

查询实例的数据文件总大小

SELECT sum(size*8/1024/1024) FROM master.sys.master_files

查询某个目录中数据库使用的总大小

SELECT a.size*8/1024/1024 ,a.* FROM master.sys.master_files a WHERE physical_name like 'G:\DEFAULT.DATA%'

查询某个目录中哪些数据库占用了8G以上容量

SELECT b.name dbname,a.size*8/1024/1024 sum_GB,a.type_desc,a.name datafilename,a.physical_name FROM master.sys.master_files a join sys.sysdatabases b on a.database_id=b.dbid and a.physical_name like 'G:\DEFAULT.DATA%' and a.size*8/1024/1024>8

查询实例上的每个数据库的大小

SELECT

DB_NAME(db.database_id) DatabaseName,

(CAST(mfrows.RowSize AS FLOAT)*8)/1024 RowSizeMB,

(CAST(mflog.LogSize AS FLOAT)*8)/1024 LogSizeMB,

(CAST(mfstream.StreamSize AS FLOAT)*8)/1024 StreamSizeMB,

(CAST(mftext.TextIndexSize AS FLOAT)*8)/1024 TextIndexSizeMB

FROM sys.databases db

LEFT JOIN (SELECT database_id, SUM(size) RowSize FROM sys.master_files WHERE type = 0 GROUP BY database_id, type) mfrows ON mfrows.database_id = db.database_id

LEFT JOIN (SELECT database_id, SUM(size) LogSize FROM sys.master_files WHERE type = 1 GROUP BY database_id, type) mflog ON mflog.database_id = db.database_id

LEFT JOIN (SELECT database_id, SUM(size) StreamSize FROM sys.master_files WHERE type = 2 GROUP BY database_id, type) mfstream ON mfstream.database_id = db.database_id

LEFT JOIN (SELECT database_id, SUM(size) TextIndexSize FROM sys.master_files WHERE type = 4 GROUP BY database_id, type) mftext ON mftext.database_id = db.database_id

查询总耗CPU最多的前3个SQL，且最近5天出现过

SELECT TOP 3

total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数],

qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 时间(ms)],

last_execution_time AS [最后一次执行时间],max_worker_time /1000 AS [最大执行时间(ms)],

SUBSTRING(qt.text,qs.statement_start_offset/2+1,

(CASE WHEN qs.statement_end_offset = -1

THEN DATALENGTH(qt.text)

ELSE qs.statement_end_offset END -qs.statement_start_offset)/2 + 1)

AS [使用CPU的语法], qt.text [完整语法],

qt.dbid, dbname=db_name(qt.dbid),

qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName

FROM sys.dm_exec_query_stats qs WITH(nolock)

CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qt

WHERE execution_count>1 and last_execution_time>dateadd(dd,-5,getdate())

ORDER BY total_worker_time DESC

查询平均耗CPU最多的前3个SQL，且最近5小时出现过

SELECT TOP 3

total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数],

qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 时间(ms)],

last_execution_time AS [最后一次执行时间],min_worker_time /1000 AS [最小执行时间(ms)],

max_worker_time /1000 AS [最大执行时间(ms)],

SUBSTRING(qt.text,qs.statement_start_offset/2+1,

(CASE WHEN qs.statement_end_offset = -1

THEN DATALENGTH(qt.text)

ELSE qs.statement_end_offset END -qs.statement_start_offset)/2 + 1)

AS [使用CPU的语法], qt.text [完整语法],

qt.dbid, dbname=db_name(qt.dbid),

qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName

FROM sys.dm_exec_query_stats qs WITH(nolock)

CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qt

WHERE execution_count>1 and last_execution_time>dateadd(hh,-5,getdate())

ORDER BY (qs.total_worker_time/qs.execution_count/1000) DESC

查看当前最耗资源的10个SQL及其spid

SELECT TOP 10

session_id,request_id,start_time AS '开始时间',status AS '状态',

command AS '命令',d_sql.text AS 'sql语句', DB_NAME(database_id) AS '数据库名',

blocking_session_id AS '正在阻塞其他会话的会话ID',

wait_type AS '等待资源类型',wait_time AS '等待时间',wait_resource AS '等待的资源',

reads AS '物理读次数',writes AS '写次数',logical_reads AS '逻辑读次数',

row_count AS '返回结果行数'

FROM sys.dm_exec_requests AS d_request

CROSS APPLY

sys.dm_exec_sql_text(d_request.sql_handle) AS d_sql

WHERE session_id>50

ORDER BY cpu_time DESC

--前50号session_id一般是系统后台进程，sys.dm_exec_requests的status显示为background

查询某个存储过程被哪些job调用了

SELECT *

FROM msdb.dbo.sysjobs JOB WITH( NOLOCK)

INNER JOIN msdb. dbo.sysjobsteps STP WITH(NOLOCK )

ON STP .job_id = JOB .job_id

WHERE STP .command LIKE N'%sp_name%'

--以上要查询某个job被哪个job调用了，把sp_name存储过程名字改成job_name作业名字即可

命令执行某个job

EXECUTE msdb.dbo.sp_start_job N'job_name'

自增长列相关

查询某表标识列的列名

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME='表名' AND COLUMNPROPERTY(OBJECT_ID('表名'),COLUMN_NAME,'IsIdentity')=1

获取标识列的种子值

SELECT IDENT_SEED ('表名')

获取标识列的递增量

SELECT IDENT_INCR('表名')

获取指定表中最后生成的标识值

SELECT IDENT_CURRENT('表名')

重新设置标识种子值为XX

DBCC CHECKIDENT (表名, RESEED, XX)

升级前，查询服务器名、实例名、版本号

select SERVERPROPERTY('machinename'),@@SERVERNAME,SERVERPROPERTY ('edition'),@@version

用户被grant这样操作赋予的权限

use dbname

exec sp_helprotect @username = 'username'

授予某个用户执行某个数据库的sp的权限

use dbname

grant execute to "username"

always on查看集群各节点的信息，包含节点成员的名称，类型，状态，拥有的投票仲裁数

SELECT * FROM  sys.dm_hadr_cluster_members;

always on查看集群各节点的信息，包含节点成员的名称，节点成员上的sql实例名称

select * from sys.dm_hadr_instance_node_map

always on查看WSFC(windows server故障转移群集)的信息，包含集群名称，仲裁类型，仲裁状态

SELECT * FROM SYS.dm_hadr_cluster;

always on查看AG名称

select * from sys.dm_hadr_name_id_map

always on查看集群各节点的子网信息，包含节点成员的名称，子网段，子网掩码

SELECT * FROM  sys.dm_hadr_cluster_networks;

always on查看侦听ip

select * from sys.availability_group_listeners;

always on查看主从各节点的状态

select d.is_local,dc.database_name, d.synchronization_health_desc,

d.synchronization_state_desc, d.database_state_desc

from sys.dm_hadr_database_replica_states d

join sys.availability_databases_cluster dc

on d.group_database_id=dc.group_database_id;

always on查看辅助副本(传说中的从库)延迟多少M日志量

select db_name(database_id),log_send_queue_size/1024 delay_M

from sys.dm_hadr_database_replica_states where is_primary_replica=0;

select ar.replica_server_name, db_name(drs.database_id),drs.truncation_lsn,

drs.log_send_queue_size,drs.log_send_rate, drs.redo_queue_size,drs.redo_rate

from sys.dm_hadr_database_replica_states drs

join sys.availability_replicas ar on drs.replica_id=ar.replica_id where drs.is_local=0

--log_send_queue_size 主数据库中尚未发送到辅助数据库的日志记录量 (KB)

--log_send_rate 在最后一个活动期间，以千字节 (KB) 的平均主副本发送实例数据的速率/秒

--redo_queue_size 辅助副本的日志文件中尚未重做的日志记录量 (KB)

--redo_rate 平均千字节 (KB) 中的给定辅助数据库做的日志记录速率 / 秒

查询实例的FILESTREAM 使用的DIRECTORY_NAME

SELECT  SERVERPROPERTY('FilestreamShareName')

查询FILETABLE表的数据库对应的DIRECTORY_NAME

select db_name(database_id),* from sys.database_filestream_options

仅仅使用filestream功能时，数据库不需要对应的DIRECTORY_NAME

查询FILETABLE表对应的DIRECTORY_NAME

select object_name(object_id),* from sys.filetables

查询filetable表testdb.dbo.table1中的文件完整路径名称

SELECT FileTableRootPath()+[file_stream].GetFileNamespacePath(),name FROM testdb.dbo.table1

查询所有job的状态是否running

SELECT sj.Name,

CASE

WHEN sja.start_execution_date IS NULL THEN 'Not running'

WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NULL THEN 'Running'

WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NOT NULL THEN 'Not running'

END AS 'RunStatus'

FROM msdb.dbo.sysjobs sj

JOIN msdb.dbo.sysjobactivity sja

ON sj.job_id = sja.job_id

WHERE session_id = (

SELECT MAX(session_id) FROM msdb.dbo.sysjobactivity) order by RunStatus desc;

锁表的四种用法

TABLOCKX

SELECT * FROM table WITH (TABLOCKX)

查询过程中，其他会话无法查询、更新此表，直到查询过程结束

TABLOCK

SELECT * FROM table WITH (TABLOCK)

查询过程中，其他会话可以查询，但是无法更新此表，直到查询过程结束

HOLDLOCK

SELECT * FROM table WITH (HOLDLOCK)

查询过程中，其他会话可以查询，但是无法更新此表，直到查询过程结束

NOLOCK

SELECT * FROM table WITH (NOLOCK)

查询过程中，其他会话可以查询、更新此表

查询某个发布XX，发布的数据库对象的2种方法

1、发布数据库上执行(数据来源这三张表distribution.dbo.MSpublications、distribution.dbo.MSarticles、sysarticlecolumns)

select a.article,a.source_object,a.destination_object,b.colid from

(select article,article_id,source_object,destination_object

from [distribution].[dbo].MSarticles where publication_id in

( select publication_id from

[distribution].[dbo].MSpublications where publication='XX'

)

) a

inner join

(select * from replicate1.dbo.sysarticlecolumns) b

on a.article_id=b.artid order by a.article

2、订阅数据库上执行

select distinct article  from MSreplication_objects where publication='XX'

查询发布信息，发布名称，发布名称对应的发布序号

Select * from distribution.dbo.MSpublications

查询发布名里面的发布对象的信息，包含表、视图、存储过程等

Select * from  distribution.dbo.MSarticles

监控发布订阅是否有异常，执行以下5条语句即可

select * from [distribution].[dbo].[MSlogreader_history] WHERE error_id != 0 AND [time] >= DATEADD(HOUR, -1, GETDATE())

select * from [distribution].[dbo].[MSdistribution_history] WHERE error_id != 0 AND [time] >= DATEADD(HOUR, -1, GETDATE())

select * from [distribution].[dbo].[MSsnapshot_history] WHERE error_id != 0 AND [time] >= DATEADD(HOUR, -1, GETDATE())

select * from [distribution].[dbo].MSrepl_errors order by 2 desc

select * from msdb.dbo.sysreplicationalerts order by 7 desc

查询XX表的索引信息

SELECT a.name index_name,c.name table_name,d.name column_name

FROM sysindexes a JOIN sysindexkeys b

ON a.id=b.id AND a.indid=b.indid

JOIN sysobjects c

ON b.id=c.id

JOIN syscolumns d

ON b.id=d.id= AND b.colid=d.colid

WHERE a.indid NOT IN(0,255) AND c.name in ('XX')

生成sql语句的执行计划(select XXX为例，当然select XXX也可以换成执行存储过程比如exec pro_XXX，都是只生成执行计划，不产生结果集，不会执行存储过程)

SET SHOWPLAN_ALL ON;

GO

select XXX

GO

SET SHOWPLAN_ALL OFF;

GO

或

SET SHOWPLAN_XML ON;

GO

select XXX

GO

SET SHOWPLAN_XML OFF;

GO

查询名称为XXX的job的最后一次运行成功的时间

SELECT TOP 1 CONVERT(DATETIME, RTRIM(run_date))+ ((run_time / 10000 * 3600) + ((run_time % 10000) / 100 * 60) + (run_time % 10000) % 100) / (86399.9964)

FROM msdb.dbo.sysjobhistory jobhis inner join msdb.dbo.sysjobs  jobs

on jobhis.job_id = jobs.job_id AND jobhis.step_id = 0 AND jobhis.run_status = 1

and jobs.name='XXX'

ORDER BY 1 DESC

查询某张分区表的总行数和大小，比如表为crm.EmailLog

exec sp_spaceused 'crm.EmailLog';

查询某张分区表的信息，每个分区有多少行，比如表为crm.EmailLog

select convert(varchar(50), ps.name

) as partition_scheme,

p.partition_number,

convert(varchar(10), ds2.name

) as filegroup,

convert(varchar(19), isnull(v.value, ''), 120) as range_boundary,

str(p.rows, 9) as rows

from sys.indexes i

join sys.partition_schemes ps on i.data_space_id = ps.data_space_id

join sys.destination_data_spaces dds

on ps.data_space_id = dds.partition_scheme_id

join sys.data_spaces ds2 on dds.data_space_id = ds2.data_space_id

join sys.partitions p on dds.destination_id = p.partition_number

and p.object_id = i.object_id and p.index_id = i.index_id

join sys.partition_functions pf on ps.function_id = pf.function_id

LEFT JOIN sys.Partition_Range_values v on pf.function_id = v.function_id

and v.boundary_id = p.partition_number - pf.boundary_value_on_right

WHERE i.object_id = object_id('crm.EmailLog')

and i.index_id in (0, 1)

order by p.partition_number

查询分区函数

select * from sys.partition_functions

查看分区架构

select * from sys.partition_schemes

查询ssis包的信息

select * from msdb.dbo.sysssispackages

查询某张表里的索引的大小,如下示例表为dbo.table1

SELECT

i.name              AS IndexName,

SUM(page_count * 8) AS IndexSizeKB

FROM sys.dm_db_index_physical_stats(

db_id(), object_id('dbo.table1'), NULL, NULL, 'DETAILED') AS s

JOIN sys.indexes AS i

ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id

GROUP BY i.name

ORDER BY i.name

重建表上的所有索引

alter index all on table_name rebuild with (

重建表上的某个索引

alter index index_name on table_name rebuild with (>

重新组织表上的所有索引

alter index all on table_name reorganize

重新组织表上的某个索引

alter index index_name on table_name reorganize

查看数据文件可收缩空间，结果见Availabesize_MB字段值

select name ,size*8/1024 as Totalsize_MB ,CAST(FILEPROPERTY(name,'SpaceUsed') AS int)*8/1024 as Usedsize_MB,

size*8/1024 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)*8/1024 AS Availabesize_MB

from sys.master_files where database_id=db_id(N'DBNAME')

查询某个表中的全部索引的信息

declare @tableName varchar(50) = 'LbaListAlertDetail'

declare @tableId int

select @tableId = object_id

from sys.objects

where name = @tableName

SELECT OBJECT_NAME(IX.OBJECT_ID) Table_Name

,IX.name AS Index_Name

,IX.type_desc Index_Type

,SUM(PS.[used_page_count]) * 8 IndexSizeKB

,IXUS.user_seeks AS NumOfSeeks

,IXUS.user_scans AS NumOfScans

,IXUS.user_lookups AS NumOfLookups

,IXUS.user_updates AS NumOfUpdates

,IXUS.last_user_seek AS LastSeek

,IXUS.last_user_scan AS LastScan

,IXUS.last_user_lookup AS LastLookup

,IXUS.last_user_update AS LastUpdate

FROM sys.indexes IX

INNER JOIN sys.dm_db_index_usage_stats IXUS ON IXUS.index_id = IX.index_id AND IXUS.OBJECT_ID = IX.OBJECT_ID

INNER JOIN sys.dm_db_partition_stats PS on PS.object_id=IX.object_id

WHERE OBJECTPROPERTY(IX.OBJECT_ID,'IsUserTable') = 1

and IX.OBJECT_ID = @tableId

GROUP BY OBJECT_NAME(IX.OBJECT_ID) ,IX.name ,IX.type_desc ,IXUS.user_seeks ,IXUS.user_scans ,IXUS.user_lookups,IXUS.user_updates ,IXUS.last_user_seek ,IXUS.last_user_scan ,IXUS.last_user_lookup ,IXUS.last_user_update

sqlserver中类似oracle的dba_source的视图是sys.sql_modules

查询某个数据库下的表数据占用磁盘容量最大的10张表

use XX

if exists(select 1 from tempdb..sysobjects where id=object_id('tempdb..#tabName') and xtype='u')

drop table #tabName

go

create table #tabName(

table_name varchar(100),

rowsNum varchar(100),

reserved_size varchar(100),

data_size varchar(100),

index_size varchar(100),

unused_size varchar(100)

)

declare @name varchar(100)

declare cur cursor for

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

open cur

fetch next from cur into @name

while @@fetch_status=0

begin

insert into #tabName

exec sp_spaceused @name

fetch next from cur into @name

end

close cur

deallocate cur

select top 10 table_name, data_size,rowsNum ,index_size,unused_size ,reserved_size,convert(int,SUBSTRING(data_size,0,LEN(data_size)-2)) size

from #tabName ORDER BY size desc

或

select top 10 a.tablename,a.SCHEMANAME,sum(a.TotalSpaceMB) TotalSpaceMB,sum(a.RowCounts) RowCounts

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) a

GROUP BY  a.tablename,a.SCHEMANAME

order by sum(a.TotalSpaceMB) desc

--这个比上一个专业

查询某个数据库中是否有create index '+name+ CHAR(10)

select 'use '+name+ CHAR(10) +'select DB_NAME(),OBJECT_NAME(OBJECT_ID),definition from '+name+'.sys.sql_modules

WHERE objectproperty(OBJECT_ID, ''IsProcedure'') = 1

AND definition like ''%online%=%on%'' and definition like ''%index%''' from sys.databases;

根据id号查询某个数据库名

SELECT DB_NAME(18)

根据id号查询某个对象名

SELECT OBJECT_NAME(1769220894)

查看收缩的进度100%，此语句要到指定的数据库下执行

SELECT DB_NAME(database_id) AS Exec_DB

,percent_complete

,CASE WHEN estimated_completion_time < 36000000

THEN '0' ELSE '' END + RTRIM(estimated_completion_time/1000/3600)

+ ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%3600/60), 2)

+ ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%60), 2) AS [Time Remaining]

,b.text as tsql

,*

FROM SYS.DM_EXEC_REQUESTS

cross apply sys.dm_exec_sql_text(sql_handle) as b

WHERE command LIKE 'DbccFilesCompact%' --and database_id=db_id('cardorder')

ORDER BY 2 DESC

查看重新组织索引的100%进度

SELECT DB_NAME(database_id) AS Exec_DB

,percent_complete

,CASE WHEN estimated_completion_time < 36000000

THEN '0' ELSE '' END + RTRIM(estimated_completion_time/1000/3600)

+ ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%3600/60), 2)

+ ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%60), 2) AS [Time Remaining]

,b.text as tsql

,*

FROM SYS.DM_EXEC_REQUESTS

cross apply sys.dm_exec_sql_text(sql_handle) as b

WHERE command LIKE '%REORGANIZE%' --and database_id=db_id('cardorder')

ORDER BY 2 DESC

查看存储过程的执行计划

SELECT

d.object_id ,

DB_NAME(d.database_id) DBName ,

OBJECT_NAME(object_id, database_id) 'SPName' ,

d.cached_time ,

d.last_execution_time ,

d.total_elapsed_time/1000000    AS total_elapsed_time,

d.total_elapsed_time / d.execution_count/1000000

AS [avg_elapsed_time] ,

d.last_elapsed_time/1000000     AS last_elapsed_time,

d.execution_count ,

d.total_physical_reads ,

d.last_physical_reads ,

d.total_logical_writes ,

d.last_logical_reads ,

et.text SQLText ,

eqp.query_plan executionplan

FROM    sys.dm_exec_procedure_stats AS d

CROSS APPLY sys.dm_exec_sql_text(d.sql_handle) et

CROSS APPLY sys.dm_exec_query_plan(d.plan_handle) eqp

WHERE   OBJECT_NAME(object_id, database_id) = 'xxxx'

ORDER BY [total_worker_time] DESC;

查看当前用户

select system_user

查询ddl修改操作的记录

1、执行如下找到trace文件的目录和名称

select * from Sys.traces

2、使用sqlserver profiler工具打开trace文件，就可以查到相关记录

查看XX数据库的隔离级别(数据库隔离级别有read committed和read committed snapshot， 前者表示读已经提交的事务之后的数据，如果一个事务没有提交，则当前事务将被阻塞。后者表示通过row versioning获得旧的数据。所以前者的情况下，如果一个select语句读取的数据正在被修改，但是这个修改还没有提交，那么这个select会被堵塞。)

use XX

DBCC  USEROPTIONS ;

修改XX数据库的隔离级别为READ_COMMITTED_SNAPSHOT

ALTER DATABASE XX SET READ_COMMITTED_SNAPSHOT ON ;

查看某个数据下，哪些用户拥有哪些角色权限

USE DB;

WITH CTE AS

(

SELECT u.name AS UserName,

g.name AS dbRole,

'√' as 'flag'

FROM sys.database_principals u

INNER JOIN sys.database_role_members m ON u.principal_id = m.member_principal_id

INNER JOIN sys.database_principals g ON g.principal_id = m.role_principal_id

)

SELECT * FROM CTE PIVOT(MAX(flag) FOR dbRole IN ([public],

[db_owner],

[db_accessadmin],

[db_securityadmin],

[db_ddladmin],

[db_backupoperator],

[db_datareader],

[db_datawriter],

[db_denydatareader],

[db_denydatawriter])) as rg;

查询实例级别的角色名称

select * from sys.server_principals where type='R'

查询某个数据库拥有的角色名称

select * from sys.database_principals where type='R'

每个数据拥有的角色是不一样的，比如msdb数据库中引入了SQLAgentUserRole、SQLAgentReaderRole、SQLAgentOperatorRole，而其他数据库并没有这几个角色

查询某数据库下，角色拥有的成员信息(比如db_datareader这个角色，哪些登录名拥有了这个角色)

SELECT DP1.name AS DatabaseRoleName,

isnull (DP2.name, 'No members') AS DatabaseUserName

FROM sys.database_role_members AS DRM

RIGHT OUTER JOIN sys.database_principals AS DP1

ON DRM.role_principal_id = DP1.principal_id

LEFT OUTER JOIN sys.database_principals AS DP2

ON DRM.member_principal_id = DP2.principal_id

WHERE DP1.type = 'R'

ORDER BY DP1.name;

查看SQLServer用户哪些权限是使用grant命令操作赋予的

use dbname

exec sp_helprotect @username = 'username'

查询哪些表的哪些字段需要创建索引，并生成创建索引语句

SELECT

mig.index_handle,

mid.database_id,

mid.object_id,

mid.[statement] [object_fullname],

(migs.avg_total_user_cost * (migs.avg_user_impact / 100.0)) * (migs.user_seeks + migs.user_scans) AS improvement_measure,

'CREATE INDEX [' AS [create_index_part1],

'missing_index_' + CONVERT(varchar, mig.index_group_handle) + '_' + CONVERT(varchar, mid.index_handle) + '_' + LEFT(PARSENAME(mid.statement, 1), 32) AS [create_index_name],

']' +

' ON ' + mid.statement + ' (' + ISNULL(mid.equality_columns, '') +

CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END + ISNULL(mid.inequality_columns, '') +

')' + ISNULL(' INCLUDE (' + mid.included_columns + ')', '') + '  WITH (' AS [create_index_part2],

'DATA_COMPRESSION = ROW, AS [create_index_part3],

migs.group_handle,

migs.unique_compiles,

migs.user_seeks,

migs.user_scans,

migs.last_user_seek,

migs.last_user_scan,

migs.avg_total_user_cost,

migs.avg_user_impact,

migs.system_seeks,

migs.system_scans,

migs.last_system_seek,

migs.last_system_scan,

migs.avg_total_system_cost,

migs.avg_system_impact

FROM sys.dm_db_missing_index_groups AS mig

INNER JOIN sys.dm_db_missing_index_group_stats AS migs

ON migs.group_handle = mig.index_group_handle

INNER JOIN sys.dm_db_missing_index_details AS mid

ON mig.index_handle = mid.index_handle

WHERE ((migs.avg_total_user_cost * (migs.avg_user_impact / 100.0)) * (migs.user_seeks + migs.user_scans) > 100000)

ORDER BY (migs.avg_total_user_cost * migs.avg_user_impact) * (migs.user_seeks + migs.user_scans) DESC

删除某个对象或某个sql语句在计划缓存中执行计划

SELECT cp.plan_handle, cp.objtype, cp.usecounts,DB_NAME(st.dbid) AS [DatabaseName],OBJECT_NAME (st.objectid) object_name,st.text

FROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st

WHERE OBJECT_NAME (st.objectid) LIKE N'%对象名%' or text LIKE N'%某个sql语句%' OPTION (RECOMPILE);

DBCC FREEPROCCACHE(0x05000E0044A36826A0FB6F1DEA00000001000000000000000000000000000000000000000000000000000000);

--上述括号中的内容就是sys.dm_exec_cached_plans.plan_handle的值

查询哪些job在某个时间段内启动

如下查询5:00和5:15启动的job

select B.name job_name,C.name job_schedule_name,C.active_start_time job_schedule_start_time from dbo.sysjobschedules A , dbo.sysjobs B,dbo.sysschedules C

where A.job_id=B.job_id and A.schedule_id=c.schedule_id and (

c.active_start_time between 50000 and 50059 or c.active_start_time between 51500 and 51559 )

and B.enabled=1

order by 1

查询某个数据库比如dba，当前产生日志最多的5个会话

select top 5 sdtdt.transaction_id,ss.spid, sd.name dbname,

database_transaction_log_bytes_used/1024/1024 as "log_byte_used(MB)",

database_transaction_log_bytes_reserved/1024/1024 as "log_byte_reseved(MB)",

database_transaction_begin_time,

database_transaction_type,

database_transaction_state,

ss.hostname,

ss.nt_username,

text

from sys.dm_tran_database_transactions sdtdt

inner join sys.databases sd on sdtdt.database_id=sd.database_id

inner join sys.dm_tran_session_transactions sdtst on sdtst.transaction_id=sdtdt.transaction_id

inner join sys.sysprocesses ss on sdtst.session_id= ss.spid

cross apply sys.dm_exec_sql_text(ss.sql_handle)

where sd.name='dba' and sdtdt.database_transaction_state in(4,12)

order by database_transaction_log_bytes_used desc

查询当前产生日志最多的5个且是job的会话

select top 5 sdtdt.transaction_id,ss.spid, sd.name dbname,job.name jobname,

database_transaction_log_bytes_used/1024/1024 as "log_byte_used(MB)",

database_transaction_log_bytes_reserved/1024/1024 as "log_byte_reseved(MB)",

database_transaction_begin_time,

database_transaction_type,

database_transaction_state,

ss.hostname,

ss.nt_username,

text

from sys.dm_tran_database_transactions sdtdt

inner join sys.databases sd on sdtdt.database_id=sd.database_id

inner join sys.dm_tran_session_transactions sdtst on sdtst.transaction_id=sdtdt.transaction_id

inner join sys.sysprocesses ss on sdtst.session_id= ss.spid

INNER JOIN   msdb.dbo.sysjobs job

ON master.dbo.fn_varbintohexstr(CONVERT(varbinary(16), job.job_id)) =

SUBSTRING(REPLACE(ss.PROGRAM_NAME, 'SQLAgent - TSQL JobStep (Job ', ''), 1, 34)

cross apply sys.dm_exec_sql_text(ss.sql_handle)

where sdtdt.database_transaction_state in(4,12)

order by database_transaction_log_bytes_used desc

查看本地服务器是否因为访问linked server慢，从而导致本地服务器的进程慢

select * from sys.sysprocesses where waitresource like '%SPID=%'

查询会话被堵塞blocking了多长时间

select  session_id, blocking_session_id,command,db_name(database_id) dbname,wait_type,wait_time/1000 wait_time_second,wait_resource

from sys.dm_exec_requests where blocking_session_id<>0

查询哪些数据库的哪些表产生了锁升级

SELECT db_name(database_id),object_name(object_id,database_id),index_lock_promotion_count

FROM sys.dm_db_index_operational_stats( NULL, NULL, NULL, NULL) where index_lock_promotion_count > 0;