Sql Server 常用操作

最新推荐文章于 2024-11-09 21:51:58 发布

weixin_30680385

最新推荐文章于 2024-11-09 21:51:58 发布

阅读量71

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/chenqs/p/6179931.html

版权

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