.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吉林养老金上调方案最新消息吉林养老金计算公式2022计算器举例

手机容量不足怎么办教你简单的几招

年底出生的女孩取名字冬天女孩取名

画苹果核的方法苹果的果核怎么画

京东拼购1200万赏金大街&ensp;收藏店铺最高可领取250京豆

运筹帷幄词语解释坐筹帷幄名词解释

塔罗未来结婚对象的特征，我想的人心里想我吗

汽车贴膜有气泡怎么办点模糊汽车贴膜有气泡怎么办

告别2019迎接2020抖音句子大全告别2019迎接2020抖音句子

属虎相冲的属相是什么生肖，属虎与什么生肖相冲

荒原雪荒原雪狼稿件

江苏儿童新冠疫苗可以和其它疫苗同时接种吗

关于火棘简述火棘

怎么取消科目一考试预约成功怎么取消科目一考试预约

艾薇儿纸尿裤是什么档次的品牌

黄蚊子是什么蚊子黄蚊子的介绍

雅诗兰黛口红怎么样