免费A级毛片无码专区网站-成人国产精品视频一区二区-啊 日出水了 用力乖乖在线-国产黑色丝袜在线观看下-天天操美女夜夜操美女-日韩网站在线观看中文字幕-AV高清hd片XXX国产-亚洲av中文字字幕乱码综合-搬开女人下面使劲插视频

  1. 首頁(yè) > 生活 > >

.NET應(yīng)用開(kāi)發(fā)之SQLServer常見(jiàn)問(wèn)題分析

生活百科

日常我們開(kāi)發(fā).NET應(yīng)用時(shí)會(huì)使用SQLServer數(shù)據(jù)庫(kù) ， 對(duì)于SQLServer數(shù)據(jù)庫(kù)的日常開(kāi)發(fā)有一些技能和工具 ， 準(zhǔn)備給大家分享一下 。
一、場(chǎng)景1：SQLServer死鎖分析
 執(zhí)行以下SQL ， 啟用SQLServer死鎖日志輸出EXEC sp_altermessage 1205, 'WITH_LOG', 'true'DBCC TRACEON(1204,-1)DBCC TRACEON(1222,-1)運(yùn)行一段時(shí)間后 ， 系統(tǒng)出現(xiàn)死鎖問(wèn)題 ， 可以從以下位置查看SQLServer異常日志的死鎖信息
Program Files\Microsoft SQL Server\MSSQL. n \MSSQL\LOG\ERRORLOG
【.NET應(yīng)用開(kāi)發(fā)之SQLServer常見(jiàn)問(wèn)題分析】二、場(chǎng)景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三、場(chǎng)景3：SQLServer重復(fù)索引分析
/* 執(zhí)行這個(gè)腳本后 ， 索引將會(huì)以三個(gè)報(bào)表的實(shí)行展現(xiàn)出來(lái)1.列出所有索引和約束的關(guān)鍵信息2.列出表潛在的冗余索引3.列出表潛在的反向索引*/-- 創(chuàng)建一個(gè)存放索引信息的表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)--加載索引信息到下面語(yǔ)句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'

經(jīng)驗(yàn)總結(jié)擴(kuò)展閱讀