.NET应用开发之SQLServer常见问题分析 _生活百科

日常我们开发.NET应用时会使用SQLServer数据库，对于SQLServer数据库的日常开发有一些技能和工具，准备给大家分享一下。
一、场景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_countFROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPSINNER JOIN sys.tables T on T.object_id = DDIPS.object_idINNER JOIN sys.schemas S on T.schema_id = S.schema_idINNER JOIN sys.indexes I ON I.object_id = DDIPS.object_idAND DDIPS.index_id = I.index_idWHERE DDIPS.database_id = DB_ID()and I.name is not nullAND DDIPS.avg_fragmentation_in_percent > 0ORDER 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) ENDAS [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'

.NET应用开发之SQLServer常见问题分析

推荐阅读

小男孩的乳名大全2022，小男孩的乳名大全2020

丁香花需要天天浇水吗

不落地的折纸滑翔机怎么折不落地的折纸滑翔机

只恋爱不结婚是哪个星座的爱情宗旨？

网易亲时光可以添加两个相册吗

2020年11月05日白羊座事业爱情运势

京东618红包在哪里领取的京东618红包在哪里领取

快手怎么解除禁言

安居电视剧马倩大结局电视剧里的拆迁你还记得么？

中国石油大学分数线中国石油大学研究生分数线

最可爱的女生网名最可爱的女生网名二字

刀下一壶酒指是什么酒

我是处女座女孩真的很讨厌很讨厌双子男

奔驰车用什么机油奔驰车用什么机油保养好

清凉油怎么打开盖子

红旗汽车生产基地在哪里

猪年本命年给女生买什么礼物好女生送猪铃铛说明什么

孩子学习不好如何和老师沟通

东张西望到处看优柔寡断难成事东张西望到处看优柔寡断难成事打一肖

由柑指的是什么关于由柑的介绍