实用T-SQL收集

抛出错误提示	DECLARE @s  VARCHAR(10); DECLARE @d  INT; SET @s = 'P20100518001269' SET @d = 12; RAISERROR ('订单:%s不存在商品Id:%d' , 16, 1,@s,@d) WITH NOWAIT;
try_cach事务	-- try cath 写法 BEGIN TRANSACTION; BEGIN TRY       /*更新语句*/       COMMIT TRANSACTION; END TRY BEGIN CATCH     ROLLBACK TRANSACTION;     SELECT ERROR_MESSAGE() AS [错误的描述];       END CATCH;
另外一个开事务的方法	/* 当SET XACT_ABORT 为ON 时，如果执行Transact-SQL 语句产生运行时错误，则整个事务将终止并回滚。 当SET XACT_ABORT 为OFF 时，有时只回滚产生错误的Transact-SQL 语句，而事务将继续进行处理。如果错误很严重，那么即使SET XACT_ABORT 为OFF，也可能回滚整个事务。 */ -- 简单写法 SET XACT_ABORT ON BEGIN TRAN /*更新语句*/ COMMIT TRAN
按关键字搜索存储过程、函数。	SELECT definition FROM sys.sql_modules WHERE PATINDEX('%t_orderpackage%',definition) > 0;
查询含有某字段的所有表	SELECT a.[name] AS [表名],b.[name] AS [字段名],b.length AS [字段长度],c.[name] AS [字段类型]  FROM  sys.sysobjects a -- sys.tables JOIN sys.syscolumns  b  ON a.id = b.id  LEFT JOIN sys.systypes c ON b.xusertype = c.xusertype  WHERE a.xtype='u' AND b.[name] LIKE '%CategoryId%';
查看数据库返回的错误信息	SELECT * FROM sys.messages WHERE message_id = 102;
处理自动增长字段	-- 给自动增长字段付值 SET IDENTITY_INSERT [t_test] ON INSERT INTO t_test(id,[Name])VALUES(4,'fan'); SET IDENTITY_INSERT [t_test] OFF
每个表的行数	SELECT OBJECT_NAME([object_id]) AS table_name,b.rows AS 行数  FROM sys.tables AS a  INNER JOIN sys.sysindexes AS b  ON a.[object_id] = b.id  WHERE b.indid IN(0,1) AND b.[rows] > 0;
判断中文	PATINDEX('%[吖-座]%',name)>0
查看某个表的索引情况	-- 查看索引的使用情况、碎片率，采用系统的表值函数 SELECT index_type_desc,avg_fragmentation_in_percent,avg_fragment_size_in_pages, b.[NAME],object_name(a.object_id) FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('CM_ShippingList'),NULL,NULL,NULL) AS a JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id AND b.[NAME] > '';
观察索引的使用情况	/*用语句查看索引的使用情况 sys.dm_db_index_physical_stats sys.dm_db_index_operational_stats*/ SELECT     OBJECT_NAME(a.object_id) AS [表或视图名称],     b.name                   AS [索引名称],     b.type_desc                 AS [索引类型],     a.user_seeks             AS [索引查找次数],     a.user_scans             AS [索引扫描次数],     a.user_lookups              AS [索引书签查找次数],     a.user_updates              AS [索引更新次数],     a.last_user_seek         AS [用户上次查找时间],     a.last_user_scan         AS [用户上次扫描时间],     a.last_user_lookup          AS [用户上次书签查找的时间],     a.last_user_update          AS [用户上次修改索引的时间] FROM sys.dm_db_index_usage_stats -- 索引使用统计 a JOIN sys.indexes b  -- 索引信息 ON a.OBJECT_ID = b.OBJECT_ID and a.index_id = b.index_id JOIN sys.objects c -- 对象信息 ON a.OBJECT_ID = c.OBJECT_ID AND c.type IN('V','U')-- 表或视图 WHERE a.database_id = DB_ID() AND a.index_id > 0 -- DB_ID()为当前库 ORDER BY [表或视图名称],[索引类型];
查出缺失的索引	/*缺失索引功能使用动态管理对象和显示计划， 提供有关可增强SQL Server 查询性能的缺失索引的信息。 但要切记只是为我们创建有效的索引提供参考。是否需要创建还是要根据实际情况。*/ SELECT [statement] AS table_name,     column_id, column_name, column_usage,     mid.equality_columns,mid.included_columns FROM sys.dm_db_missing_index_details AS mid CROSS APPLY sys.dm_db_missing_index_columns (mid.index_handle) INNER JOIN sys.dm_db_missing_index_groups AS mig ON mig.index_handle = mid.index_handle WHERE mid.database_id=DB_ID() ORDER BY mig.index_group_handle, mig.index_handle, column_id; /*[说明] 如果是创建包含索引（覆盖索引）: Equality数据列排在左边 INEquality数据列排在后面 include数据列放在include子句后面   将选择性最高的数据行放在最前面 equality_columns: 这个字段和一个相等运算符在WHERE从句中使用。所以SQL Server告诉我们这将是针对索引的很好的选择。 included_columns: 索引创建时，其它可能被用作内嵌的字段。返回列。 */
获取产生阻塞的语句	-- 获取产生阻塞的源头 DECLARE @sp_id sysname; SELECT @sp_id = spid FROM sys.sysprocesses WHERE spid IN(  SELECT DISTINCT blocked  FROM sys.sysprocesses  WHERE blocked > 0 ) AND blocked = 0; SELECT  DB_NAME(a.[dbid]) AS 所在数据库,  @sp_id AS [产生阻塞原因的spid],  (SELECT [name] FROM sys.all_objects WHERE [object_id] = rt.[objectid]) AS [OBJ],  rt.[text] AS [产生阻塞的相关语句] FROM sys.sysprocesses a CROSS APPLY sys.dm_exec_sql_text(a.[sql_handle]) AS rt WHERE a.spid = @sp_id;
查看最耗I/O的语句	-- 查询最耗I/O资源的SQL语句 SELECT top 5     (total_logical_reads/execution_count) AS 平均逻辑读取次数,     (total_logical_writes/execution_count) AS 平均逻辑写入次数,     (total_physical_reads/execution_count) AS 平均物理读取次数,     execution_count AS 执行次数,     SUBSTRING(qt.text,r.statement_start_offset/2 + 1,     (CASE WHEN r.statement_end_offset = -1     THEN DATALENGTH(qt.text)     ELSE r.statement_end_offset END - r.statement_start_offset)/2 +1) AS 执行语句,     DB_NAME(dbid),[text] FROM sys.dm_exec_query_stats AS r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS qt ORDER BY (total_logical_reads + total_logical_writes) DESC;
查看最耗CPU的语句	/*呈现最耗cpu的前个执行计划*/ SELECT TOP 50     total_worker_time/1000000.0 AS [总耗cpu时间(s)],     execution_count AS [执行次数],     qs.total_worker_time/qs.execution_count/1000000.0 AS [平均耗cpu时间(s)],     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    AS [完整语句],     qt.dbid,     dbname = DB_NAME(qt.dbid),     qt.objectid,     OBJECT_NAME(qt.objectid) AS objectName FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt ORDER BY total_worker_time DESC;
查看最耗时间的语句	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;