推荐点击下面图片，通过本站淘宝优惠价购买:
1、关于复制类型
快照发布:
发布服务器按预定的时间间隔向订阅服务器发送已发布数据的快照。
事务发布:
在订阅服务器收到已发布数据的初始快照后，发布服务器将事务流式传输到订阅服务器。
对等发布:
对等发布支持多主复制。发布服务器将事务流式传输到拓扑中的所有对等方。所有对等节点可以读取和写入更改，且所有更改将传播到拓扑中的所有节点。
合并发布:
在订阅服务器收到已发布数据的初始快照后，发布服务器和订阅服务器可以独立更新已发布数据。更改会定期合并。Microsoft SQL Server Compact Edition 只能订阅合并发布。
2、数据库服务器安装sql server后修改机器名导致发布失败解决方法
-- 检查SQL Server中的“服务器名[/命名实例名]”, 和当前真实的“计算机名[/命名实例名]”。如果修改了计算机名，则这两者即会不一致。
select @@serverName, serverproperty('serverName')
-- 将"服务器名", 修改为正确的计算机名
EXEC sp_dropserver '服务器名[/命名实例名]'; -- 即旧的计算机名
GO
EXEC sp_addserver '计算机名[/命名实例名]', 'local'; -- 即新的计算机名
go
-- 重启SQL Server
3、判断无用的索引
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 30
DB_NAME() AS DatabaseName ,
'[' + SCHEMA_NAME(o.Schema_ID) + ']' + '.' + '['
+ OBJECT_NAME(s.[object_id]) + ']' AS TableName ,
i.name AS IndexName ,
i.type AS IndexType ,
s.user_updates ,
s.system_seeks + s.system_scans + s.system_lookups AS [System_usage]
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
INNER JOIN sys.objects o ON i.object_id = O.object_id
WHERE s.database_id = DB_ID()
AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0
AND s.user_seeks = 0
AND s.user_scans = 0
AND s.user_lookups = 0
AND i.name IS NOT NULL
ORDER BY s.user_updates DESC
4、判断 哪些索引缺失
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 30
ROUND(s.avg_total_user_cost * s.avg_user_impact * ( s.user_seeks
+ s.user_scans ),
0) AS [Total Cost] ,
s.avg_total_user_cost * ( s.avg_user_impact / 100.0 ) * ( s.user_seeks
+ s.user_scans ) AS Improvement_Measure ,
DB_NAME() AS DatabaseName ,
d.[statement] AS [Table Name] ,
equality_columns ,
inequality_columns ,
included_columns
FROM sys.dm_db_missing_index_groups g
INNER JOIN sys.dm_db_missing_index_group_stats s ON s.group_handle = g.index_group_handle
INNER JOIN sys.dm_db_missing_index_details d ON d.index_handle = g.index_handle
WHERE s.avg_total_user_cost * ( s.avg_user_impact / 100.0 ) * ( s.user_seeks
+ s.user_scans ) > 10
ORDER BY [Total Cost] DESC ,
s.avg_total_user_cost * s.avg_user_impact * ( s.user_seeks
+ s.user_scans ) DESC
5、看看那些索引维护成本很高 通俗的说就是更新次数大于使用这个索引的次数
SELECT TOP 20
DB_NAME() AS DatabaseName ,
'[' + SCHEMA_NAME(o.Schema_ID) + ']' + '.' + '['
+ OBJECT_NAME(s.[object_id]) + ']' AS TableName ,
i.name AS IndexName ,
i.type AS IndexType ,
( s.user_updates ) AS update_usage ,
( s.user_seeks + s.user_scans + s.user_lookups ) AS retrieval_usage ,
( s.user_updates ) - ( s.user_seeks + user_scans + s.user_lookups ) AS maintenance_cost ,
s.system_seeks + s.system_scans + s.system_lookups AS system_usage ,
s.last_user_seek ,
s.last_user_scan ,
s.last_user_lookup
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
INNER JOIN sys.objects o ON i.object_id = O.object_id
WHERE s.database_id = DB_ID('{0}')
AND i.name IS NOT NULL
AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0
AND ( s.user_seeks + s.user_scans + s.user_lookups ) > 0
ORDER BY maintenance_cost DESC
6、常常使用的索引查看 看看你常用使用的索引是否建立的合理
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 20
DB_NAME() AS DatabaseName
, '['+SCHEMA_NAME(o.Schema_ID)+']'+'.'+'['+OBJECT_NAME(s.[object_id]) +']'AS TableName
, i.name AS IndexName
, i.type as IndexType
, (s.user_seeks + s.user_scans + s.user_lookups) AS Usage
, s.user_updates
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
INNER JOIN sys.objects o ON i.object_id = O.object_id
WHERE s.database_id = DB_ID()
AND i.name IS NOT NULL
AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0
ORDER BY Usage DESC
发表评论:
◎欢迎参与讨论，请在这里发表您的看法、交流您的观点。
