1：查看sql server代理中作业的运行状况的脚本
-- descr : a simple sql script to view sql server jobs run status
-- last_run_status 1:success 0:fail
select category = jc.name,
category_id = jc.category_id,
job_name = j.name,
job_enabled = j.enabled,
last_run_time = cast(js.last_run_date as varchar(10)) + '-' + cast(js.last_run_time as varchar(10)),
last_run_duration = js.last_run_duration,
last_run_status = js.last_run_outcome,
last_run_msg = js.last_outcome_message + cast(nullif(js.last_run_outcome,1) as varchar(2)),
job_created = j.date_created,
job_modified = j.date_modified
from msdb.dbo.sysjobs j
inner join msdb.dbo.sysjobservers js
on j.job_id = js.job_id
inner join msdb.dbo.syscategories jc
on j.category_id = jc.category_id
where j.enabled = 1
and js.last_run_outcome in (0,1,3,5) -- 0:Fail 1:Succ 3:Cancel 5:First run
and jc.category_id not between 10 and 20 -- repl
--and js.last_run_outcome = 0 只用于查看失败的作业
或者(查看语句的执行计划以及执行时间）
DECLARE @WeekDays TABLE
(
freq_interval INT,
weekdays NVARCHAR(120) ) INSERT INTO @WeekDays
SELECT 1 ,N'星期日 ' UNION ALL
SELECT 2 ,N'星期一 ' UNION ALL
SELECT 4 ,N'星期二 ' UNION ALL
SELECT 8 ,N'星期三 ' UNION ALL
SELECT 16 ,N'星期四 ' UNION ALL
SELECT 32 ,N'星期五 ' UNION ALL
SELECT 118 ,N'星期一,二,四,五,六 ' UNION ALL
SELECT 122 ,N'星期一,三,四,五,六 ' UNION ALL
SELECT 124 ,N'星期二,三,四,五,六 ' UNION ALL
SELECT 63 ,N'星期日,一,二,三,四,五' UNION ALL
SELECT 95 ,N'星期日,一,二,三,四,六' UNION ALL
SELECT 111 ,N'星期日,一,二,三,五,六' UNION ALL
SELECT 119 ,N'星期日,一,二,四,五,六' UNION ALL
SELECT 123 ,N'星期日,一,三,四,五,六' UNION ALL
SELECT 125 ,N'星期日,二,三,四,五,六' UNION ALL
SELECT 126 ,N'星期一,二,三,四,五,六' UNION ALL
SELECT 127 ,N'星期日,一,二,三,四,五,六'; SELECT d.job_id AS job_id ,
j.name AS job_name ,
CASE WHEN j.enabled =1 THEN N'启用'
ELSE N'禁用' END AS job_staus,
CASE WHEN freq_type=1 THEN N'运行一次'
WHEN freq_type=4 THEN N'每天执行'
WHEN freq_type=8 THEN N'每周执行'
WHEN freq_type=16 THEN N'每月执行'
WHEN freq_type=32 THEN N'每月执行'
WHEN freq_type=64 THEN N'代理服务启动时运行'
WHEN freq_type=128 THEN N'在计算机空闲时运行'
END AS freq_type,
CASE WHEN freq_type=1 THEN N'选项无意义'
WHEN freq_type=4 THEN
(
CASE WHEN freq_subday_type=4
THEN N'每隔' + CONVERT(NVARCHAR(4),freq_subday_interval) +N'分钟执行一次'
WHEN freq_subday_type=8
THEN N'每隔' + CONVERT(NVARCHAR(4),freq_subday_interval) +N'小时执行一次'
ELSE N'每天执行'+ CONVERT(NVARCHAR(4),freq_interval) + N'次'
END
)
WHEN freq_type=8 THEN
( SELECT w.weekdays FROM @WeekDays w WHERE w.freq_interval= s.freq_interval
) WHEN freq_type=16 THEN N'每月' + CONVERT(NVARCHAR(4),freq_interval) + N'号执行'
WHEN freq_type=32 THEN N'每月星期' + CONVERT(NVARCHAR(4),freq_interval) + N'执行'
END AS freq_relative_interval,
CASE WHEN freq_subday_type =1 THEN N'指定时间点执行一次'
WHEN freq_subday_type =2 THEN N'每隔：' + CAST(freq_subday_interval AS VARCHAR(2)) + N'秒执行一次'
WHEN freq_subday_type =4 THEN N'每隔：' + CAST(freq_subday_interval AS VARCHAR(2)) + N'分执行一次'
WHEN freq_subday_type =8 THEN N'每隔：' + CAST(freq_subday_interval AS VARCHAR(2)) + N'小时执行一次'
END AS freq_subday_type,
CASE WHEN freq_subday_type =1 THEN N'开始时间点：'
+ CAST(active_start_time / 10000 AS VARCHAR(2)) + N'点'
+ CAST(active_start_time%10000/100 AS VARCHAR(2)) + N'分'
WHEN freq_subday_type =2 THEN N'开始时间点：'
+ CAST(active_start_time / 10000 AS VARCHAR(2)) + N'点'
+ CAST(active_start_time%10000/100 AS VARCHAR(2)) + N'分'
WHEN freq_subday_type =4 THEN N'开始时间点：'
+ CAST(active_start_time / 10000 AS VARCHAR(2)) + N'点'
+ CAST(active_start_time%10000/100 AS VARCHAR(2)) + N'分'
WHEN freq_subday_type =8 THEN N'开始时间点：'
+ CAST(active_start_time / 10000 AS VARCHAR(2)) + N'点'
+ CAST(active_start_time%10000/100 AS VARCHAR(2)) + N'分'
END AS job_start_time,
CASE WHEN freq_subday_type =1 THEN N'结束时间点：'
+ CAST(active_end_time / 10000 AS VARCHAR(2)) + N'点'
+ CAST(active_end_time%10000/100 AS VARCHAR(2)) + N'分'
WHEN freq_subday_type =2 THEN N'结束时间点：'
+ CAST(active_end_time / 10000 AS VARCHAR(2)) + N'点'
+ CAST(active_end_time%10000/100 AS VARCHAR(2)) + N'分'
WHEN freq_subday_type =4 THEN N'结束时间点：'
+ CAST(active_end_time / 10000 AS VARCHAR(2)) + N'点'
+ CAST(active_end_time%10000/100 AS VARCHAR(2)) + N'分'
WHEN freq_subday_type =8 THEN N'结束时间点：'
+ CAST(active_end_time / 10000 AS VARCHAR(2)) + N'点'
+ CAST(active_end_time%10000/100 AS VARCHAR(2)) + N'分'
END AS job_end_time,
freq_type,
freq_interval,
freq_subday_type,
freq_subday_interval,
active_start_date,
active_start_time
FROM msdb.dbo.sysschedules s
INNER JOIN msdb.dbo.sysjobschedules d ON s.schedule_id=d.schedule_id
INNER JOIN msdb.dbo.sysjobs j ON d.job_id = j.job_id
ORDER BY j.name
或者（可以查看作业当前的运行状态）
SELECT
[job].[name] AS '作业名称'
, CASE [jobh].[run_status]
WHEN 0 THEN '失败'
WHEN 1 THEN '成功'
WHEN 2 THEN '重试'
WHEN 3 THEN '取消'
WHEN 4 THEN '正在运行'
END AS '最后执行状态'
,[jobh].[message] AS '最后运行状态信息'
FROM [msdb].[dbo].[sysjobs] AS [job]
LEFT JOIN (
SELECT
[job_id]
, MIN ([next_run_date]) AS [NextRunDate]
, MIN ([next_run_time]) AS [NextRunTime]
FROM [msdb].[dbo].[sysjobschedules]
GROUP BY [job_id]
) AS [jsch]
ON [job].[job_id] = [jsch].[job_id]
LEFT JOIN (
SELECT
[job_id]
,[run_date]
,[run_time]
,[run_status]
,[run_duration]
,[message]
,ROW_NUMBER() OVER ( PARTITION BY [job_id] ORDER BY [run_date] DESC , [run_time] DESC ) AS RowNumber
FROM [msdb].[dbo].[sysjobhistory]
WHERE [step_id] = 0
) AS [jobh]
ON [job].[job_id] = [jobh].[job_id]
AND [jobh].[RowNumber] = 1
ORDER BY [job].[name]
启动作业的存储过程（执行权限默认授予 msdb 数据库中的 public 角色。可执行此存储过程而且是 sysadmin 固定角色成员的用户可以启动任何作业。不是 sysadmin 角色成员的用户使用 sp_start_job 只能启动他/她所拥有的作业。）
--下例启动名为 Nightly Backup 的作业。 USE msdb
EXEC sp_start_job @job_name = 'Nightly Backup' --下例删除名为Nightly Backup的作业
EXEC sp_delete_job @job_name ='Nightly Backup'
返回代码值
0（成功）或 1（失败）
语法结构：
sp_start_job [@job_name =] 'job_name' | [@job_id =] job_id
[,[@error_flag =] error_flag]
[,[@server_name =] 'server_name']
[,[@step_name =] 'step_name']
[,[@output_flag =] output_flag]
--查看作业名（开启关闭状态）
use msdb
go
select name from sysjobs where enabled = 1
--更改作业属性（开启关闭，作业名等）
exec sp_update_job @job_name = N'DBA_StopTraceEveryDay' ,@enabled = 1
2、查看always on 同步状态（队列情况）
SELECT ar.replica_server_name AS [副本名称] ,
ar.availability_mode_desc as [同步模式],
DB_NAME(dbr.database_id) AS [数据库名称] ,
dbr.database_state_desc AS [数据库状态],
dbr.synchronization_state_desc AS [同步状态],
dbr.synchronization_health_desc AS [同步健康状态],
ISNULL(CASE dbr.redo_rate
WHEN 0 THEN -1
ELSE CAST(dbr.redo_queue_size AS FLOAT) / dbr.redo_rate
END, -1) AS [Redo延迟(秒)] ,
ISNULL(CASE dbr.log_send_rate
WHEN 0 THEN -1
ELSE CAST(dbr.log_send_queue_size AS FLOAT)
/ dbr.log_send_rate
END, -1) AS [Log传送延迟(秒)] ,
dbr.redo_queue_size AS [Redo等待队列(KB)] ,
dbr.redo_rate AS [Redo速率(KB/S)] ,
dbr.log_send_queue_size AS [Log传送等待队列(KB)] ,
dbr.log_send_rate AS [Log传送速率(KB\S)]
FROM [master].sys.availability_replicas AS AR
INNER JOIN [master].sys.dm_hadr_database_replica_states AS dbr
ON ar.replica_id = dbr.replica_id
WHERE dbr.redo_queue_size IS NOT NULL
order by DB_NAME(dbr.database_id)
3、查询当前数据库的脚本（语句）的运行情况(dbcc freeproccache 之后执行次数会再次刷新)：
SELECT creation_time N'语句编译时间'
,last_execution_time N'上次执行时间'
,execution_count N'执行次数'
,case datediff(ss,creation_time,last_execution_time) when 0 then 0
else execution_count/datediff(ss,creation_time,last_execution_time) end N'每秒执行次数'
,total_physical_reads N'物理读取总次数'
,total_logical_reads/execution_count N'每次逻辑读次数'
,total_logical_reads N'逻辑读取总次数'
,total_logical_writes 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'执行语句'
,db_name(st.dbid) as dbname,st.objectid
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 execution_count DESC;
4、查询当前数据库正在执行的语句：
SELECT [Spid] = session_Id
,ecid
,[Database] = DB_NAME(sp.dbid)
,[User] = nt_username
,[Status] = er.STATUS
,[Wait] = 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)
,[Parent Query] = qt.TEXT
,Program = program_name
,Hostname
,hostprocess
,loginame
,kpid
,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.*/
5、查询阻塞情况：
SELECT wt.blocking_session_id AS BlockingSessesionId
,sp.program_name AS ProgramName
,COALESCE(sp.LOGINAME, sp.nt_username) AS HostName
,ec1.client_net_address AS ClientIpAddress
,db.name AS DatabaseName
,wt.wait_type AS WaitType
,ec1.connect_time AS BlockingStartTime
,wt.WAIT_DURATION_MS/1000 AS WaitDuration
,ec1.session_id AS BlockedSessionId
,h1.TEXT AS BlockedSQLText
,h2.TEXT AS BlockingSQLText
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
LEFT OUTER JOIN master.dbo.sysprocesses sp ON SP.spid = wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
--====================================
--查看正在执行的SQL的阻塞情况
SELECT
R.session_id,
R.command,
R.blocking_session_id,
R.wait_type,
R.wait_resource
FROM sys.dm_exec_requests AS R
WHERE R.session_id>55
AND R.session_id<>@@SPID
--=================================================================
--查看阻塞链
WITH T1 AS (
SELECT S.session_id ,
ISNULL(RS.blocking_session_id , 0) AS blocking_session_id ,
CAST('' AS NVARCHAR(200)) AS BlockStep ,
AS BlockNum
FROM [sys].[dm_exec_sessions] AS S WITH ( NOLOCK )
LEFT JOIN [sys].[dm_exec_requests] RS WITH ( NOLOCK )
ON S.session_id = RS.session_id
WHERE S.session_id IN (
SELECT RS1.blocking_session_id
FROM [sys].[dm_exec_requests] RS1 )
AND ISNULL(RS.blocking_session_id , 0) = 0
UNION ALL
SELECT RS.session_id ,
RS.blocking_session_id ,
CAST(( '-->'
+ CAST(RS.blocking_session_id AS NVARCHAR(200))
+ T1.BlockStep ) AS NVARCHAR(200)) AS BlockStep ,
+ T1.BlockNum AS BlockNum
FROM [sys].[dm_exec_requests] RS
INNER JOIN T1
ON RS.blocking_session_id = T1.session_id
)
SELECT session_id ,
blocking_session_id ,
( CASE WHEN T1.BlockStep = ''
THEN 'KILL ' + CAST(T1.session_id AS NVARCHAR(200))
ELSE T1.BlockStep
END ) AS BlockStep ,
BlockNum
FROM T1
6：查询数据库日志文件的大小：
dbcc sqlperf(logspace)
| 列名 | 定义 |
|---|---|
|
Database Name |
数据库名称，为该数据库显示日志统计信息。 |
|
Log Size (MB) |
分配给日志的当前大小。该大小始终小于最初为日志空间分配的大小，因为 SQL Server 2005 数据库引擎保留了一小部分磁盘空间用于存放内部标头信息。 |
|
Log Space Used (%) |
事务日志信息当前所占用的日志文件的百分比。 |
|
Status |
日志文件的状态。始终为 0。 |
例：
Database Name Log Size (MB) Log Space Used (%) Status
-------------- ------------------------ ------------------------ -----------
master 2.2421875 32.600174 0
tempdb 0.4921875 39.285713 0
model 0.4921875 41.07143 0
msdb 2.2421875 30.901567 0
pubs 0.7421875 49.934212 0
Northwind 0.9921875 34.940945 0
--如果要保存SQL日志文件大小，则需要先创建一个数据表，然后动态执行dbcc sqlperf(logspace)命令
create table dbo.LogSize
(
dbname nvarchar(50) not null
,logsize decimal(8,2) not null
,logused decimal(5,2) not null
,status int null
)
insert into dbo.LogSize
execute('dbcc sqlperf(logspace) with no_infomsgs')
7、迁移登录用户
select 'create login [' + p.name + '] ' +
case when p.type in('U','G') then 'from windows ' else '' end +
'with ' +
case when p.type = 'S' then 'password = ' + master.sys.fn_varbintohexstr(l.password_hash) +
' hashed, ' + 'sid = ' + master.sys.fn_varbintohexstr(l.sid) + ', check_expiration = ' +
case when l.is_expiration_checked > 0 then 'ON, ' else 'OFF, ' end + 'check_policy = ' +
case when l.is_policy_checked > 0 then 'ON, ' else 'OFF, ' end +
case when l.credential_id > 0 then 'credential = ' + c.name + ', ' else '' end
else '' end +
'default_database = ' + p.default_database_name +
case when len(p.default_language_name) > 0
then ', default_language = "' + p.default_language_name +'"' else '''' end
from sys.server_principals p
left join sys.sql_logins l on p.principal_id = l.principal_id
left join sys.credentials c on l.credential_id = c.credential_id
where p.type in('S','U','G') and p.name <> 'sa'
8、检查文件空间的方法：
使用 exec sp_spaceused 查看的时候，结果比较笼统，不过也是可行的；
建议同时运行下面两条命令：
use<数据库>
go
dbcc showfilestats
go
dbcc sqlperf(logspace)
go
其中
“dbcc showfilestats”命令会以Extent为单位，统计当前数据库下所有数据文件里有多少个Extent，其中有多少个被使用过了，一个Extent是64K，乘一下即可得到数据文件大小，该命令直接从系统分配页面上面读取区分配信息，能够快速准确地计算出一个数据库数据文件区的总数和已使用过的区的数目，而系统分配页上的信息永远是实时更 新的，所以这种统计方法比较准确可靠。在服务器负载很高的情况下也能安全执行，不会增加额外系统负担；
“dbcc sqlperf(logspace)”命令的输出非常浅显易懂。它返回SQL里所有数据库的日志文件当前使用量，该命令的输出也非常快速准确，使用安全。
运行以下查询，可以得到具体库中表的具体空间使用信息：
use xxxxDB --需要查询的库名
SELECT o.name as name ,
SUM (p.reserved_page_count) as reserved_page_count,
SUM (p.used_page_count) as used_page_count ,
SUM ( CASE WHEN (p.index_id <2) THEN (p.in_row_data_page_count + p.lob_used_page_count + p.row_overflow_used_page_count) ELSE p.lob_used_page_count + p.row_overflow_used_page_count END ) as DataPages,
SUM ( CASE WHEN (p.index_id <2) THEN row_count ELSE 0 END ) as rowCounts
FROM sys.dm_db_partition_stats as p join sys.objects as o
on p.object_id = o.object_id
group by o.name
order by reserved_page_count desc
输出结果
第一列name是每个表的名字。
SQL Server在使用数据页的时候，为了提高速度，会先把一些页面一次预留”reserve”给表格，然后真正有数据插入的时候，再使用。所以这里有两 列，Reserved_page_count和Used_page_count。两列的结果相差一般不会很多。所以粗略来 讲，Reserved_page_count*8K，就是这张表格占用的空间大小。
DataPages是这张表数据本身占有的空间。因此，（Used_page_count – DataPages）就是索引所占有的空间。索引的个数越多，需要的空间也会越多。
RowCounts，是现在这个表里有多少行数据。
9、查看sqlserver中表、存储过程、视图等创建以及修改时间：
a）、直接通过对象资源管理详细信息查看（选中相关项，按F7即可）。
b）、直接通过sql语句查看
--查看表的结构
select column_name,data_type from information_schema.columns where table_name = '表名'
--查看用户表的创建时间以及修改时间
select name,create_date ,modify_date from sys.all_objects where type_desc in ('USER_TABLE') --查看存储过程的创建时间以及修改时间
select name,create_date ,modify_date from sys.all_objects where type_desc in ('SQL_STORED_PROCEDURE')
--查看视图的创建时间以及修改时间
select name,create_date ,modify_date from sys.all_objects where type_desc in ('VIEW')
10、查看数据库具体表上的索引的使用情况：
use xxx --改成要查看的数据库 select db_name(database_id) as N'数据库名称',
object_name(a.object_id) as N'表名',
b.name N'索引名称',
user_seeks N'用户索引查找次数',
user_scans N'用户索引扫描次数',
last_user_seek N'最后查找时间',
last_user_scan N'最后扫描时间',
rows as N'表中的行数'
from sys.dm_db_index_usage_stats a join
sys.indexes b
on a.index_id = b.index_id
and a.object_id = b.object_id
join sysindexes c
on c.id = b.object_id
where database_id=db_id('xxx') ---改成要查看的数据库
and object_name(a.object_id) not like 'sys%'
and object_name(a.object_id) = 'xxx' --改成要查看的表名，此步可省略（即查看某库所有索引）
order by user_scans desc
11、查看某个表的具体的增长情况：
--每十分钟统计某表的数据变动情况（副本库查询）
--通过该char(15)可改变统计时间间隔，时间列如 2016-05-17 15:09:53.627
use xxxxDB
select convert(char(15),时间列,120),count(1)
from 表名 with (nolock)
where 自增列 > (select max(自增列)-条目数 from 表名 )
group by convert(char(15),时间列,120)
12、查询指定数据的每月的备份文件增长情况：
select AVG(backup_size)/1024/1024 as "backup_size MB" ,convert(char(7),backup_finish_date,120) as backup_date
FROM msdb.dbo.backupset
WHERE
[database_name] = N'xxxxxDB'
and server_name = 'yyyyy'
group by convert(char(7),backup_finish_date,120)
order by convert(char(7),backup_finish_date,120)
-- 按月统计指定数据库的增长
-- xxxxxDB 数据库
-- yyyyy 数据库所在服务器的主机名
13、查询得到1-100的连续数字（区间可改）：
select number from master..spt_values where type = 'p' and number between 1 and 100
14、查询具体表的行数：
SELECT rows FROM sysindexes WHERE id =OBJECT_ID('table_name') AND indid <2
15、查看数据库服务器上所有的IO负载的排名（IO负载分布状况）：
-- Get I/O utilization by database (Query 31) (IO Usage By Database)
WITH Aggregate_IO_Statistics
AS
(SELECT DB_NAME(database_id) AS [Database Name],
CAST(SUM(num_of_bytes_read + num_of_bytes_written)/1048576 AS DECIMAL(12, 2)) AS io_in_mb
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS [DM_IO_STATS]
GROUP BY database_id)
SELECT ROW_NUMBER() OVER(ORDER BY io_in_mb DESC) AS [I/O Rank], [Database Name], io_in_mb AS [Total I/O (MB)],
CAST(io_in_mb/ SUM(io_in_mb) OVER() * 100.0 AS DECIMAL(5,2)) AS [I/O Percent]
FROM Aggregate_IO_Statistics
ORDER BY [I/O Rank] OPTION (RECOMPILE);
-- Helps determine which database is using the most I/O resources on the instance
16、明确的数据库服务器上所有数据库的高读和写延迟（数据库文件的IO延迟）：
--下面的查询返回哪个数据库文件有最大的I/O延迟：
-- Calculates average stalls per read, per write,
-- and per total input/output for each database file.
SELECT DB_NAME(fs.database_id) AS [Database Name], mf.physical_name,
io_stall_read_ms, num_of_reads,
CAST(io_stall_read_ms/(1.0 + num_of_reads) AS NUMERIC(10,1)) AS
[avg_read_stall_ms],io_stall_write_ms,
num_of_writes,CAST(io_stall_write_ms/(1.0+num_of_writes) AS NUMERIC(10,1)) AS
[avg_write_stall_ms],
io_stall_read_ms + io_stall_write_ms AS [io_stalls], num_of_reads + num_of_writes
AS [total_io],
CAST((io_stall_read_ms + io_stall_write_ms)/(1.0 + num_of_reads + num_of_writes) AS
NUMERIC(10,1))
AS [avg_io_stall_ms]
FROM sys.dm_io_virtual_file_stats(null,null) AS fs
INNER JOIN sys.master_files AS mf WITH (NOLOCK)
ON fs.database_id = mf.database_id
AND fs.[file_id] = mf.[file_id]
ORDER BY avg_io_stall_ms DESC OPTION (RECOMPILE);
-- Helps determine which database files on
-- the entire instance have the most I/O bottlenecks
17、查询指定数据库中总物理读最高的查询以及相关信息（排名前十的）：
SELECT TOP 10
t.text ,
execution_count ,
statement_start_offset AS stmt_start_offset ,
sql_handle ,
plan_handle ,
total_logical_reads / execution_count AS avg_logical_reads ,
total_logical_writes / execution_count AS avg_logical_writes ,
total_physical_reads / execution_count AS avg_physical_reads,t.dbid
FROM sys.dm_exec_query_stats AS s
CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS t
--WHERE DB_NAME(t.dbid) = 'AdventureWorks2008R2'
ORDER BY avg_physical_reads DESC;
18、将所有数据库的恢复模式设置成简单模式（系统数据库除外）：
USE [master]
GO
--alter database model set recovery simple with no_wait
declare
@sqltext varchar(1000),
@dbname varchar(500)
declare mycursor cursor for
select name from sys.databases where database_id >4
open mycursor
fetch next from mycursor into @dbname
while @@FETCH_STATUS=0
begin
print @dbname
set @sqltext='ALTER DATABASE ['+@dbname+'] SET RECOVERY SIMPLE WITH NO_WAIT'
exec (@sqltext)
--print @sqltext fetch next from mycursor into @dbname
end close mycursor
deallocate mycursor
19、查看文件组信息
--查看具体指定数据库的文件组信息
use xxxDB
SELECT df.[name], df.physical_name, df.[size], df.growth, fg.[name]
[filegroup], fg.is_default
FROM sys.database_files df
JOIN sys.filegroups fg
ON df.data_space_id = fg.data_space_id
20、判断表是堆表还是聚集索引表
--通过判断index_id的值，区分表的类型
--当index_id为0时，则为堆表
--当index_id为1时，则为聚集索引表 select object_name(a.object_id),
case a.index_id when 0 then '堆表'
when 1 then '聚集索引表'
end table_type
from sys.partitions a
where a.index_id<2
group by a.object_id,a.index_id
21、如何正确判断何时使用堆表和聚集索引表
我们有很多理由去创建一个聚集索引表，而非堆表。那么最大的理由可能就是：当一个非聚集索引包含的列不能完全符合一条查询（select）时，执行计划可通过聚集索引查找，而非通过表扫描的方式。 那么我们为什么会选择堆表，原因大致就如下2点：
1. 堆表没有聚集索引，因此堆表可节省索引的磁盘空间
2. 堆表没有聚集索引，且数据组织是无序的，节省了排序操作，写入操作更快。 特别注意：在聚集索引索引表上创建分区时，务必检查sql脚本。若设置的分区函数指定的列不是聚集索引列，将会导致聚集索引的变化（删除与重建），最终导致表的类型转换
22、查看当前实例运行的事务（事务的回话id，运行时长，事务类型等等）
SELECT ST.session_id AS spid,
ST.transaction_id AS TransactionID ,
DB_NAME(DT.database_id) AS DatabaseName ,
AT.transaction_begin_time AS TransactionStartTime ,
DATEDIFF(SECOND, AT.transaction_begin_time, GETDATE()) AS TransactionDuration ,
CASE AT.transaction_type
WHEN 1 THEN 'Read/Write Transaction'
WHEN 2 THEN 'Read-Only Transaction'
WHEN 3 THEN 'System Transaction'
WHEN 4 THEN 'Distributed Transaction'
END AS TransactionType ,
CASE AT.transaction_state
WHEN 0 THEN 'Transaction Not Initialized'
WHEN 1 THEN 'Transaction Initialized & Not Started'
WHEN 2 THEN 'Active Transaction'
WHEN 3 THEN 'Transaction Ended'
WHEN 4 THEN 'Distributed Transaction Initiated Commit Process'
WHEN 5 THEN 'Transaction in Prepared State & Waiting Resolution'
WHEN 6 THEN 'Transaction Committed'
WHEN 7 THEN 'Transaction Rolling Back'
WHEN 8 THEN 'Transaction Rolled Back'
END AS TransactionState
FROM sys.dm_tran_session_transactions AS ST
INNER JOIN sys.dm_tran_active_transactions AS AT ON ST.transaction_id = AT.transaction_id
INNER JOIN sys.dm_tran_database_transactions AS DT ON ST.transaction_id = DT.transaction_id
where ST.is_user_transaction=1 ORDER BY TransactionStartTime
go
23、批量删除事务（或者联系22中事务查看方法进行删除）
declare @sessionid int
,@sqltxt varchar(max)
declare mycursor cursor for
select session_id
from sys.dm_tran_session_transactions
where is_user_transaction=1 open mycursor
fetch next from mycursor into @sessionid
while @@FETCH_STATUS = 0 begin --print @sessionid
set @sqltxt = 'kill '+convert(varchar(5),@sessionid)
print @sqltxt
exec (@sqltxt)
fetch next from mycursor into @sessionid
end
close mycursor
deallocate mycursor
24:查看sqlserver日志具体报错信息（根据错误编号以及严重性等级）
select * from sys.messages where message_id = 17836 and language_id = 2052 ---在网络数据包负载中指定的长度与读取的字节数不匹配；该连接已关闭。请与客户端库的供应商联系。%1!
25：查看sqlserver指定表的索引信息
SELECT
索引名称=a.name
,表名=c.name
,索引字段名=d.name
,索引字段位置=d.colid
,c.status
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.xtype='U' --and c.status>0 --查所有用户表
AND c.name='GamePay' --查指定表
ORDER BY c.name,a.name,d.name;
以上有部分摘录于大神们的博客，在此便于查看学习分享，仅此而已。