sql server 常见问题笔记

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   

以上转自：http://blog.csdn.net/fengyarongaa/article/details/8315693

7、让热备数据库联机

日志传送的后备数据库是只读备用状态，数据库 处于热备用状态。热备用数据库是只读的。restore database db_name with recovery

8、sql server数据导入导出

使用 BCP 导入导出数据，参考http://database.51cto.com/art/200701/37924.htm

C:\Users\Administrator>bcp cs.dbo.lhxq_sq out c:\12.txt -U sa -P sa -S 192.168.0.122  -c -t
开始复制...

已复制 36 行。
网络数据包大小(字节): 4096
总时钟时间(毫秒)     : 47     平均值: (每秒 765.96 行。)
C:\Users\Administrator>bcp cs2.dbo.lhxq_sq in c:\12.txt -U sa -P sa -S 192.168.0.122  -c -t

开始复制...

已复制 36 行。
网络数据包大小(字节): 4096
总时钟时间(毫秒)     : 842    平均值: (每秒 42.76 行。)

 

bcp "select * from cs.dbo.ps_manager_grid WHERE WORKCODE like '440310009Z0%'" queryout f:\temp\20161101\grid.txt -c -U sa -P sa -S 192.168.1.212

 参考：http://blog.sina.com.cn/s/blog_63f3e0060102vjcw.html

9、sp_helptext 获取的存储过程去掉空格

create table #temp_table([text] NVARCHAR(MAX))


insert into #temp_table([text]) exec sp_helptext 'p_oa_document_send_ins'


SELECT * FROM #temp_table
WHERE LEN(TEXT)<>2

 10、sqlserver附加数据库

EXEC SP_ATTACH_DB [ty_pmi_eo], 'F:\ty_data\ty_pmi_eo.mdf','F:\ty_data_log\ty_pmi_eo.ldf','F:\ty_data_idx\ty_pmi_eo.ndf'

 11、sqlserver删除重复记录

delete from t_gps_pos_curr_status 
where   c_client_key in (select c_client_key    from t_gps_pos_curr_status group by c_client_key 
     having count(c_client_key) > 1) 
and   fid not in (select min(fid) from t_gps_pos_curr_status group by c_client_key   
  having count(c_client_key)>1) 

 12、查看所有数据库数据文件路径

select * from master.sys.master_files

 13、删除重复数据

Delete T From
 (Select Row_Number() Over(Partition By [Name] order By [ID]) As RowNumber,* From Student)T
 Where T.RowNumber > 1