首页 > 数据库 >【转】SQL Server 常用运维语句

【转】SQL Server 常用运维语句

时间：2023-08-13 16:44:46浏览次数：39  
标签：index name 运维 INDEX Server COL SQL NULL id

来自：微信公众号  DotNet工控上位机编程

链接地址：https://mp.weixin.qq.com/s/wyyuuRMMAgM_4QEvaAdQMw

 

开发.NET应用时可能会使用SQLServer数据库，对于SQLServer数据库的日常开发，给大家分享一些运维SQL语句。

一、场景1：SQLServer死锁分析

执行以下SQL，启用SQLServer死锁日志输出

EXEC sp_altermessage 1205, 'WITH_LOG', 'true'
DBCC TRACEON(1204,-1)
DBCC TRACEON(1222,-1)

运行一段时间后，系统出现死锁问题，可以从以下位置查看SQLServer异常日志的死锁信息

Program Files\Microsoft SQL Server\MSSQL. n \MSSQL\LOG\ERRORLOG

 

二、场景2：SQLServer索引锁片收集

SELECT S.name as 'Schema',
T.name as 'Table',
I.name as 'Index',
DDIPS.avg_fragmentation_in_percent,
DDIPS.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS
INNER JOIN sys.tables T on T.object_id = DDIPS.object_id
INNER JOIN sys.schemas S on T.schema_id = S.schema_id
INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id
AND DDIPS.index_id = I.index_id
WHERE DDIPS.database_id = DB_ID()
and I.name is not null
AND DDIPS.avg_fragmentation_in_percent > 0
ORDER BY DDIPS.avg_fragmentation_in_percent desc

三、场景3：SQLServer重复索引分析

/* 执行这个脚本后，索引将会以三个报表的实行展现出来
1.  列出所有索引和约束的关键信息
2.  列出表潜在的冗余索引
3.  列出表潜在的反向索引
*/
-- 创建一个存放索引信息的表
DECLARE @AllIndexes TABLE (
 [Table ID] [int] NOT NULL,
 [Schema] [sysname] NOT NULL,
 [Table Name] [sysname] NOT NULL,
 [Index ID] [int] NULL,
 [Index Name] [nvarchar](128) NULL,
 [Index Type] [varchar](12) NOT NULL,
 [Constraint Type] [varchar](11) NOT NULL,
 [Object Type] [varchar](10) NOT NULL,
 [AllColName] [nvarchar](2078) NULL,
 [ColName1] [nvarchar](128) NULL,
 [ColName2] [nvarchar](128) NULL,
 [ColName3] [nvarchar](128) NULL,
 [ColName4] [nvarchar](128) NULL,
 [ColName5] [nvarchar](128) NULL,
 [ColName6] [nvarchar](128) NULL,
 [ColName7] [nvarchar](128) NULL,
 [ColName8] [nvarchar](128) NULL,
 [ColName9] [nvarchar](128) NULL,
 [ColName10] [nvarchar](128) NULL
)

--  加载索引信息到下面语句
INSERT INTO @AllIndexes
 ([Table ID],[Schema],[Table Name],[Index ID],[Index Name],[Index Type],[Constraint Type],[Object Type]
 ,[AllColName],[ColName1],[ColName2],[ColName3],[ColName4],[ColName5],[ColName6],[ColName7],[ColName8],
 [ColName9],[ColName10])
SELECT o.[object_id] AS [Table ID] ,u.[name] AS [Schema],o.[name] AS [Table Name],
 i.[index_id] AS [Index ID]
 , CASE i.[name]
 WHEN o.[name] THEN '** Same as Table Name **'
 ELSE i.[name] END AS [Index Name],
 CASE i.[type]
 WHEN 1 THEN 'CLUSTERED'
 WHEN 0 THEN 'HEAP'
 WHEN 2 THEN 'NONCLUSTERED'
 WHEN 3 THEN 'XML'
 ELSE 'UNKNOWN' END AS [Index Type],
 CASE
 WHEN (i.[is_primary_key]) = 1 THEN 'PRIMARY KEY'
 WHEN (i.[is_unique]) = 1 THEN 'UNIQUE'
 ELSE '' END AS [Constraint Type],
 CASE
 WHEN (i.[is_unique_constraint]) = 1
 OR (i.[is_primary_key]) = 1
 THEN 'CONSTRAINT'
 WHEN i.[type] = 0 THEN 'HEAP'
 WHEN i.[type] = 3 THEN 'XML INDEX'
 ELSE 'INDEX' END AS [Object Type],
 (SELECT COALESCE(c1.[name],'') FROM [sys].[columns] AS c1 INNER JOIN [sys].[index_columns] AS ic1
 ON c1.[object_id] = ic1.[object_id] AND c1.[column_id] = ic1.[column_id] AND ic1.[key_ordinal] = 1
 WHERE ic1.[object_id] = i.[object_id] AND ic1.[index_id] = i.[index_id]) +
 CASE
 WHEN INDEX_COL('[' + u.[name] + '].['+ o.[name] + ']', i.[index_id], 2) IS NULL THEN ''
 ELSE ', '+INDEX_COL('[' + u.[name] + '].['+ o.[name] + ']', i.[index_id],2) END +
 CASE
 WHEN INDEX_COL('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id], 3) IS NULL THEN ''
 ELSE ', '+INDEX_COL('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id],3) END +
 CASE
 WHEN INDEX_COL('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id], 4) IS NULL THEN ''
 ELSE ', '+INDEX_COL('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id],4) END +
 CASE
 WHEN INDEX_COL('[' + u.[name] + '].['+ o.[name] + ']', i.[index_id], 5) IS NULL THEN ''
 ELSE ', '+INDEX_COL('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id],5) END  +
 CASE
 WHEN INDEX_COL('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id], 6) IS NULL THEN ''
 ELSE ', '+INDEX_COL('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id],6) END +
 CASE
 WHEN INDEX_COL('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id], 7) IS NULL THEN ''
 ELSE ', '+INDEX_COL('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id], 7) END +
 CASE
 WHEN INDEX_COL('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id],8) IS NULL THEN ''
 ELSE ', '+INDEX_COL('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id],8) END +
 CASE
 WHEN INDEX_COL('[' + u.[name] + '].['+ o.[name] + ']', i.[index_id], 9) IS NULL THEN ''
 ELSE ', '+INDEX_COL('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id],9) END +
 CASE
 WHEN INDEX_COL('[' + u.[name] + '].['+ o.[name] + ']', i.[index_id], 10) IS NULL THEN ''
 ELSE ', '+INDEX_COL('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id],10) END  AS [AllColName],
 (SELECT COALESCE(c1.[name],'') FROM [sys].[columns] AS c1 INNER JOIN [sys].[index_columns] AS ic1
 ON c1.[object_id] = ic1.[object_id] AND c1.[column_id] = ic1.[column_id] AND ic1.[key_ordinal] = 1
 WHERE ic1.[object_id] = i.[object_id] AND ic1.[index_id] = i.[index_id])   AS [ColName1],
 CASE
 WHEN INDEX_COL('[' + u.[name] + '].['+ o.[name] + ']', i.[index_id], 2) IS NULL THEN ''
 ELSE INDEX_COL('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id],2) END AS [ColName2],
 CASE
 WHEN INDEX_COL('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id], 3) IS NULL THEN ''
 ELSE INDEX_COL('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id],3) END AS [ColName3],
 CASE
 WHEN INDEX_COL('[' + u.[name] + '].['+ o.[name] + ']', i.[index_id], 4) IS NULL THEN ''
 ELSE INDEX_COL('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id],4) END AS [ColName4],
 CASE
 WHEN INDEX_COL('[' + u.[name] + '].['+ o.[name] + ']', i.[index_id], 5) IS NULL THEN ''
 ELSE INDEX_COL('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id],5) END AS [ColName5],
 CASE
 WHEN INDEX_COL('[' + u.[name] + '].['+ o.[name] + ']', i.[index_id], 6) IS NULL THEN ''
 ELSE INDEX_COL('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id],6) END AS [ColName6],
 CASE
 WHEN INDEX_COL('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id], 7) IS NULL THEN ''
 ELSE INDEX_COL('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id],7) END AS [ColName7],
 CASE
 WHEN INDEX_COL('[' + u.[name] + '].['+ o.[name] + ']', i.[index_id],8) IS NULL THEN ''
 ELSE INDEX_COL('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id],8) END AS [ColName8],
 CASE
 WHEN INDEX_COL('[' + u.[name] + '].['+ o.[name] + ']', i.[index_id], 9) IS NULL THEN ''
 ELSE INDEX_COL('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id],9) END AS [ColName9],
 CASE
 WHEN INDEX_COL('[' + u.[name] + '].['+ o.[name] + ']', i.[index_id], 10) IS NULL THEN ''
 ELSE INDEX_COL('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id],10) END AS [ColName10]
FROM [sys].[objects] AS o WITH (NOLOCK)
 LEFT OUTER JOIN [sys].[indexes] AS i WITH (NOLOCK)
 ON o.[object_id] = i.[object_id]
 JOIN [sys].[schemas] AS u WITH (NOLOCK)
 ON o.[schema_id] = u.[schema_id]
WHERE o.[type] = 'U' --AND i.[index_id] < 255
 AND o.[name] NOT IN ('dtproperties')
 AND i.[name] NOT LIKE '_WA_Sys_%'

-----------
SELECT 'Listing All Indexes' AS [Comments]

SELECT I.*
 FROM @AllIndexes AS I
 ORDER BY [Table Name]

-----------
SELECT 'Listing Possible Redundant Index keys' AS [Comments]

SELECT DISTINCT I.[Table Name], I.[Index Name] ,I.[Index Type],  I.[Constraint Type], I.[AllColName]
 FROM @AllIndexes AS I
 JOIN @AllIndexes AS I2
 ON I.[Table ID] = I2.[Table ID]
 AND I.[ColName1] = I2.[ColName1]
 AND I.[Index Name] <> I2.[Index Name]
 AND I.[Index Type] <> 'XML'
 ORDER BY I.[Table Name], I.[AllColName]

----------
SELECT 'Listing Possible Reverse Index keys' AS [Comments]

SELECT DISTINCT I.[Table Name], I.[Index Name], I.[Index Type],  I.[Constraint Type], I.[AllColName]
 FROM @AllIndexes AS I
 JOIN @AllIndexes AS I2
 ON I.[Table ID] = I2.[Table ID]
 AND I.[ColName1] = I2.[ColName2]
 AND I.[ColName2] = I2.[ColName1]
 AND I.[Index Name] <> I2.[Index Name]
 AND I.[Index Type] <> 'XML'

以上SQL语句，比较常用，分享给大家。

转自：Eric zhou

链接：cnblogs.com/tianqing/p/16887038.html

三、场景3：SQLServer重复索引分析

标签：index,name,运维,INDEX,Server,COL,SQL,NULL,id
From： https://www.cnblogs.com/z5337/p/17626761.html

相关文章

  • mysql在开启group_replication后，状态显示为RECOVERING，告警日志报错MY-013117、MY-0115
    问题描述：mysql在开启group_replication后，状态显示为RECOVERING，告警日志报错MY-013117、MY-011582、MY-011583，如下所示：数据库：MySQL8.0.27系统：rhel7.364位1、问题重现Slave02[(none)]>select*fromperformance_schema.replication_group_members;+-----------------------......
  • mysql在开启group_replication后，报错ERROR 3092，This member has more executed transa
    问题描述：mysql在开启group_replication后，报错ERROR3092，Thismemberhasmoreexecutedtransactionsthanthosepresentinthegroup，如下所示：数据库：MySQL8.0.27系统：rhel7.31、异常重现Slave01[(none)]>startgroup_replication;ERROR3092(HY000):Theserverisnotc......
  • mysql在安装group_replication插件时，报错"version libcrypto.so.10 not defined in fi
    问题描述：mysql在安装group_replication插件时，报错"versionlibcrypto.so.10notdefinedinfilelibcrypto.so"，如下所示：数据库：mysql8.0.27系统：rhel7.364位1、异常重现mysql>installplugingroup_replicationsoname'group_replication.so';ERROR1126(HY000):......
  • SQL 语句创建数据库表时列字段的初始化值
    在SQL中，创建数据库表时可以指定每个列字段的初始值，这称为"默认值"（DefaultValue）。默认值是在插入新记录时，如果没有显式提供该列的值，则自动应用的值。当插入新行时，如果未提供该列的值，则数据库会使用默认值来填充该列。默认值对于确保数据完整性和提供默认选项非常有用。当插入新行......
  • 自动获取 DNS server address
    "ObtainDNSserveraddressautomatically"是一种网络配置选项，它允许计算机或其他网络设备通过动态主机配置协议（DHCP）从网络中的路由器或服务器自动获取DNS（域名系统）服务器的地址。DNS服务器的作用是将域名转换为相应的IP地址，以便在互联网上定位和连接到特定的网站或资源。在这......
  • SAP UI5 中的 sap-fe-mockserver
    SAPUI5是一种用于构建现代Web应用程序的开发框架，它是SAP(Systems,Applications,andProductsinDataProcessing)公司推出的一种前端技术。它提供了丰富的控件库、MVC(Model-View-Controller)架构模式、数据绑定、主题定制等功能，帮助开发人员构建直观、高度可定制且易......
  • 一个mysql dba的成长之旅--第零章 绝处逢生：意外收到dba offer
    （本故事纯属虚构，如有雷同实属巧合）2018年的一个秋天的下午，江南理工大学图书馆一楼的宣讲会大厅人头攒动，充满了期待的氛围。这里正在举办一场国内知名互联网公司的宣讲会，吸引了众多毕业生前来倾听。小李身穿一套整洁的求职西装，手里拿着整齐的彩色简历，坐在室友旁边，全神贯注地聆听着台......
  • SQL:打印表索引脚本
    SQLSERVER 打印索引脚本declareC1cursorfor--表索引，不包含主约束索引。SELECTOBJ.OBJECT_IDAS[TABLE_ID],OBJ.NAMEAS[TABLE_NAME],--PK.nameASCONSTRAIN_NAME,idx.nameasindex_name,--索引类型：聚集索引、非聚集索引caseWHEN......
  • Go语言gorm框架MySQL实践
    gorm是一个使用Go语言编写的ORM框架。文档齐全，对开发者友好，支持主流数据库。我最近在补齐Go语言各类基础的框架和操作库的知识，终于进展到了数据库阶段，搜资料的时候基本都是推荐这个框架，可见其之流行程度。在不断尝试练习之后，总结了一些经验和使用方式，供初学者参考。在之前使用Jav......
  • SQL:索引表索引
    SQLSERVER2008R2 查询数据库表索引，不包含主约束索引--表索引，不包含主约束索引。SELECTOBJ.OBJECT_IDAS[TABLE_ID],OBJ.NAMEAS[TABLE_NAME],--PK.nameASCONSTRAIN_NAME,idx.nameasindex_name,--索引类型：聚集索引、非聚集索引caseW......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99