Sql Server 常用操作
--DDL触发器
CREATE TRIGGER [TR_create_drop_alter_Table]
ON DATABASE
FOR CREATE_TABLE,DROP_table,ALTER_TABLE
AS
IF( SUSER_SNAME() <>'sa' )
BEGIN
PRINT '您无Create/Alter/Drop TABLE 的权限！有需要请与管理员联系 。'
PRINT '***********************************************************'
PRINT '您有对 临时表(#) 进行 Create/Alter/Drop TABLE 操作的权限。'
ROLLBACK
END
-----------------------------------------------------------------------------------------------------------------------------------------------
--IP登录限制
CREATE TRIGGER [Trig_ConnectLimit]
ON ALL SERVER WITH EXECUTE AS 'SA' ---执行用户
FOR LOGON
AS
BEGIN
IF (SELECT EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]', 'NVARCHAR(15)'))
NOT IN('<local machine>','192.168.1.1','192.168.1.2') --IP地址
BEGIN
IF HOST_NAME() NOT IN('CHEN','AF') --机器名
BEGIN
ROLLBACK;
END
END
END
-----------------------------------------------------------------------------------------------------------------------------------------------
--释放TempDb占用内存
USE [tempdb]
GO
dbcc freesystemcache('ALL')
Go
DBCC SHRINKfile(N'tempdev' ,8) --收缩到2MB
GO
------------------------------------------------------------------------------------------------------------------------------------------------
--死锁相关
USE [master]
GO
create procedure [dbo].[sp_who_lock]
as
BEGIN
declare @spid int,@bl int
DECLARE s_cur CURSOR FOR
select 0 ,blocked
from (select * from sysprocesses where blocked>0 ) a
where not exists(select * from (select * from sysprocesses where blocked>0 ) b
where a.blocked=spid)
union select spid,blocked from sysprocesses where blocked>0
OPEN s_cur
FETCH NEXT FROM s_cur INTO @spid,@bl
WHILE @@FETCH_STATUS = 0
begin
if @spid =0
select '引起数据库死锁的是:
'+ CAST(@bl AS VARCHAR(10)) + '进程号,其执行的SQL语法如下'
else
select '进程号SPID：'+ CAST(@spid AS VARCHAR(10))+ '被' + '
进程号SPID：'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下'
DBCC INPUTBUFFER (@bl )
FETCH NEXT FROM s_cur INTO @spid,@bl
end
CLOSE s_cur
DEALLOCATE s_cur
END
GO
--查看死锁
-- EXEC [dbo].[sp_who_lock]
--解除死锁
-- KILL SPID
----------------------------------------------------------------------------------------------------------------------------------------------------
--查找出什么语句读 内存很高
SELECT SS.SUM_EXECUTION_COUNT, T.TEXT, SS.SUM_TOTAL_ELAPSED_TIME, SS.SUM_TOTAL_WORKER_TIME, SS.SUM_TOTAL_LOGICAL_READS, SS.SUM_TOTAL_LOGICAL_WRITES
FROM (SELECT S.PLAN_HANDLE, SUM(S.EXECUTION_COUNT)SUM_EXECUTION_COUNT, SUM(S.TOTAL_ELAPSED_TIME)SUM_TOTAL_ELAPSED_TIME, SUM(S.TOTAL_WORKER_TIME)SUM_TOTAL_WORKER_TIME,
SUM(S.TOTAL_LOGICAL_READS)SUM_TOTAL_LOGICAL_READS, SUM(S.TOTAL_LOGICAL_WRITES)SUM_TOTAL_LOGICAL_WRITES
FROM SYS.DM_EXEC_QUERY_STATS S
GROUP BY S.PLAN_HANDLE ) AS SS
CROSS APPLY SYS.dm_exec_sql_text(SS.PLAN_HANDLE)T
ORDER BY SUM_TOTAL_LOGICAL_READS DESC
--------------------------------------------------------------------------------------------------------------------------------------
--数据库占用内存情况
select database_id, case DATABASE_id when 32767 then 'resourcedb' else DB_NAME(database_id) end AS DbName ,COUNT(*)*8.0/1024 as 'MemorySize(MB)'
from sys.dm_os_buffer_descriptors
group by DB_NAME(database_id),database_id
--------------------------------------------------------------------------------------------------------------------------------------
--查看数据字典
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,
标识=case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,
主键=case when exists(SELECT 1 FROM sysobjects where xtype='PK' 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,''),
字段说明=isnull(g.[value],'')
FROM syscolumns a
left join systypes b on a.xtype=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='要查询的表' --如果只查询指定表,加上此条件
order by a.id,a.colorder
------------------------------------------------------------------------------------------------------------------------------------------------
--查询当前数据库索引情况
SELECT A.DBNAME ,A.TABELNAME ,B.name , D.RowCnt ,C.[%] ,user_seeks,user_scans,user_lookups,user_updates,last_user_seek,last_user_scan,last_user_lookup,last_user_update
FROM (SELECT DB_NAME(database_id) DBNAME ,ISNULL((OBJECT_NAME(OBJECT_ID)),OBJECT_ID) TABELNAME ,index_id ,user_seeks,user_scans,user_lookups
,user_updates,last_user_seek,last_user_scan,last_user_lookup,last_user_update FROM sys.dm_db_index_usage_stats WHERE database_id>6 AND DB_NAME(database_id) NOT IN ('distribution') AND OBJECT_NAME(OBJECT_ID) NOT LIKE 'sys%'AND database_id=DB_ID()
) A LEFT JOIN (SELECT OBJECT_NAME(OBJECT_ID) TABELNAME ,name ,index_id FROM SYS.indexes ) B ON A.TABELNAME=B.TABELNAME AND A.index_id=B.index_id LEFT JOIN (select ROUND(avg_fragmentation_in_percent,2)[%],DB_NAME(database_id)DBNAME, ISNULL((OBJECT_NAME(OBJECT_ID)),OBJECT_ID) TABELNAME ,index_id
from sys.dm_db_index_physical_stats(db_id(),null, null, null, null))C ON A.DBNAME=C.DBNAME AND A.TABELNAME=C.TABELNAME AND A.index_id=C.index_id LEFT JOIN (SELECT object_name (i.id) TableName,rows as RowCnt FROM sysindexes i INNER JOIN sysObjects o ON (o.id = i.id AND o.xType = 'U ')
WHERE indid < 2 ) D ON A.TABELNAME=D.TableName
WHERE B.name IS NOT NULL
AND C.[%]>15
--AND user_seeks>0
--AND last_user_seek>(GETDATE()-10)
AND B.name IS NOT NULL
ORDER BY A.TABELNAME ,name
--生成语句
SELECT 'ALTER INDEX ' ,B.name ,' ON ',A.TABELNAME ,' REBUILD PARTITION = ALL WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF ) '
FROM (SELECT DB_NAME(database_id) DBNAME ,ISNULL((OBJECT_NAME(OBJECT_ID)),OBJECT_ID) TABELNAME ,index_id ,user_seeks,user_scans,user_lookups
,user_updates,last_user_seek,last_user_scan,last_user_lookup,last_user_update FROM sys.dm_db_index_usage_stats WHERE database_id>6 AND DB_NAME(database_id) NOT IN ('distribution') AND OBJECT_NAME(OBJECT_ID) NOT LIKE 'sys%'AND database_id=DB_ID()
) A LEFT JOIN (SELECT OBJECT_NAME(OBJECT_ID) TABELNAME ,name ,index_id FROM SYS.indexes ) B ON A.TABELNAME=B.TABELNAME AND A.index_id=B.index_id LEFT JOIN (select ROUND(avg_fragmentation_in_percent,2)[%],DB_NAME(database_id)DBNAME, ISNULL((OBJECT_NAME(OBJECT_ID)),OBJECT_ID) TABELNAME ,index_id
from sys.dm_db_index_physical_stats(db_id(),null, null, null, null))C ON A.DBNAME=C.DBNAME AND A.TABELNAME=C.TABELNAME AND A.index_id=C.index_id LEFT JOIN (SELECT object_name (i.id) TableName,rows as RowCnt FROM sysindexes i INNER JOIN sysObjects o ON (o.id = i.id AND o.xType = 'U ')
WHERE indid < 2 ) D ON A.TABELNAME=D.TableName
WHERE C.[%]>20
AND user_seeks>user_scans
--AND user_seeks>500
AND last_user_seek>(GETDATE()-2)
AND B.name IS NOT NULL
AND RowCnt>1000
ORDER BY A.TABELNAME ,name
--丢失索引
SELECT user_seeks * avg_total_user_cost *( avg_user_impact *0.01 ) AS [index_advantage] ,
dbmigs.last_user_seek ,
dbmid.[statement] AS [Database.Schema.Table],
dbmid.equality_columns ,
dbmid.inequality_columns ,
dbmid.included_columns ,
dbmigs.unique_compiles ,
dbmigs.user_seeks ,
dbmigs.avg_total_user_cost ,
dbmigs.avg_user_impact
FROM sys.dm_db_missing_index_group_stats AS dbmigs WITH ( NOLOCK )
INNER JOIN sys.dm_db_missing_index_groups AS dbmig WITH ( NOLOCK ) ON dbmigs.group_handle = dbmig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS dbmid WITH ( NOLOCK ) ON dbmig.index_handle = dbmid.index_handle
WHERE dbmid.[database_id] = DB_ID()
ORDER BY index_advantage DESC;
---------------------------------------------------------------------------------------------------------------------------------------------
--查看账号权限
select DB_NAME(db_id()) DBNAME,b.name as TableName,c.name as UserName , CASE b.type WHEN'U' THEN 'Table' WHEN 'P' THEN 'SP' ELSE 'Other' END AS Type
, CASE WHEN a.ACTION = 26 AND a.PROTECTTYPE = 205 THEN '√' ELSE '' END AS 'References'
, CASE WHEN a.ACTION = 193 AND a.PROTECTTYPE = 205 THEN '√' ELSE '' END AS 'Select'
, CASE WHEN a.ACTION = 195 AND a.PROTECTTYPE = 205 THEN '√' ELSE '' END AS 'Insert'
, CASE WHEN a.ACTION = 197 AND a.PROTECTTYPE = 205 THEN '√' ELSE '' END AS 'Update'
, CASE WHEN a.ACTION = 196 AND a.PROTECTTYPE = 205 THEN '√' ELSE '' END AS 'Delete'
, CASE WHEN a.ACTION = 224 AND a.PROTECTTYPE = 205 THEN '√' ELSE '' END AS 'Execute'
, CASE a.PROTECTTYPE WHEN 204 THEN 'Grant_W_Grant ' WHEN 205 THEN 'Grant' WHEN 206 THEN 'Deny' ELSE 'Other' END AS Protecttype
INTO #TEMPPRI
from sysprotects a inner join sysobjects b on a.id = b.id inner join sysusers c on a.uid = c.uid
SELECT A.*,ISNULL(B.[References],'')[References],ISNULL(C.[Select],'')[Select],ISNULL(D.[Insert],'')[Insert],ISNULL(E.[Update],'')[Update],ISNULL(F.[Delete],'')[Delete],ISNULL(G.[Execute],'')[Execute]
FROM
(SELECT DISTINCT DBNAME ,TableName ,UserName ,TYPE FROM #TEMPPRI) A
LEFT JOIN (SELECT * FROM #TEMPPRI WHERE [References] LIKE '_%' ) B ON A.DBNAME=B.DBNAME AND A.TableName=B.TableName AND A.UserName=B.UserName
LEFT JOIN (SELECT * FROM #TEMPPRI WHERE [Select] LIKE '_%') C ON A.DBNAME=C.DBNAME AND A.TableName=C.TableName AND A.UserName=C.UserName
LEFT JOIN (SELECT * FROM #TEMPPRI WHERE [Insert]LIKE '_%') D ON A.DBNAME=D.DBNAME AND A.TableName=D.TableName AND A.UserName=D.UserName
LEFT JOIN (SELECT * FROM #TEMPPRI WHERE [Update] LIKE '_%') E ON A.DBNAME=E.DBNAME AND A.TableName=E.TableName AND A.UserName=E.UserName
LEFT JOIN (SELECT * FROM #TEMPPRI WHERE [Delete] LIKE '_%') F ON A.DBNAME=F.DBNAME AND A.TableName=F.TableName AND A.UserName=F.UserName
LEFT JOIN (SELECT * FROM #TEMPPRI WHERE [Execute] LIKE '_%') G ON A.DBNAME=G.DBNAME AND A.TableName=G.TableName AND A.UserName=G.UserName
DROP TABLE #TEMPPRI
-----------------------------------------------------------------------------------------------------------------------------------------------
--数据库备份情况
select @@servername ServerName , A.database_name as dbName ,type ,A.BackupFinishDate ,A.[BackupSize(MB)] ,A.BackupName ,B.physical_device_name AS PhysicalDeviceName
from (
SELECT media_set_id , database_name ,backup_size/1024/1024 AS [BackupSize(MB)] ,Backup_finish_date BackupFinishDate,type ,name BackupName
FROM MSDB..backupset where backup_finish_date>(GETDATE()-1)
) AS A LEFT JOIN
( SELECT media_set_id ,physical_device_name FROM MSDB..backupmediafamily
) B ON A.media_set_id=B.media_set_id
----------------------------------------------------------------------------------------------------------------------------------------------
--表的使用情况
CREATE PROCEDURE [dbo].[sys_viewTableSpace]
AS
BEGIN
SET NOCOUNT ON;
CREATE TABLE [dbo].#tableinfo(
表名 [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
记录数 [int] NULL,
预留空间 [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
使用空间 [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
索引占用空间 [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
未用空间 [varchar](50) COLLATE Chinese_PRC_CI_AS NULL
)
insert into #tableinfo(表名, 记录数, 预留空间, 使用空间, 索引占用空间, 未用空间)
exec sp_MSforeachtable "exec sp_spaceused '?'"
select * from #tableinfo
order by 记录数 desc
drop table #tableinfo
END
使用的时候直接 ：exec sys_viewtablespace
-----------------------------------------------------------------------------------------------------------------------------------------------
--查看错误日志
--日志编号（0：当前使用的）
DECLARE @FileNum INT ;SET @FileNum=0
--查询类型(1:Sql Server 日志 ; 2:代理错误日志)
DECLARE @TYPE INT ;SET @TYPE=1
--日志查询起始时间
DECLARE @StartTime DATETIME ;SET @StartTime=GETDATE()-1.5
--日志查询结束时间
DECLARE @endTime DATETIME ;SET @endTime=GETDATE()
--时间排序(ASC 或 DESC)
DECLARE @ORDER VARCHAR(10) ;SET @ORDER='DESC'
exec xp_readerrorlog @FileNum,@Type,NULL,NULL,@StartTime,@EndTime,@order
--切换 SQL Server 错误日志文件 存档（执行7次会清除全部数据）
-- EXEC sp_cycle_errorlog
-- GO
-----------------------------------------------------------------------------------------------------------------------------------------------
--最耗费CPU的前个查询以及它们的执行计划
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
WITH TEMP AS(
SELECT CAST((qs.total_worker_time) / 1000000.0 AS DECIMAL(28,2)) AS TotalSecondsForCPUTime
,CAST(qs.total_worker_time* 100.0 / qs.total_elapsed_time AS DECIMAL(28,2)) AS CPUPersent
,CAST((qs.total_elapsed_time- qs.total_worker_time)* 100.0 / qs.total_elapsed_time AS DECIMAL(28, 2)) AS WaitingPersent
,qs.execution_count ExecutionCount
,CAST((qs.total_worker_time)/ 1000000.0 / qs.execution_count AS DECIMAL(28, 2)) AS AvgSecondsForCPUTime
,SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,
((CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS IndividualQuery
,qt.text AS ParentQuery
,DB_NAME(qt.dbid) AS DatabaseName
,qp.query_plan QueryPlan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qs.total_elapsed_time > 0 )
SELECT TOP(20)* FROM TEMP
ORDER BY TEMP.TotalSecondsForCPUTime DESC
-----------------------------------------------------------------------------------------------------------------------------------------------
--最占IO的前个查询以及它们的执行计划
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
WITH TEMP AS(
SELECT (qs.total_logical_reads + qs.total_logical_writes) AS TotalIO
,(qs.total_logical_reads+ qs.total_logical_writes) / qs.execution_count AS AvgIO
,qs.execution_count AS ExecutionCount
,SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,
((CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset
END- qs.statement_start_offset)/2) + 1) AS IndividualQuery
,qt.text AS ParentQuery
, DB_NAME(qt.dbid) AS DatabaseName
,qp.query_plan AS QueryPlan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp)
SELECT TOP(20)* FROM TEMP
ORDER BY TEMP.TotalIO DESC
