标签：db

SQL Server 2012 不支持创建连接服务器到 2000 版本，及其替代方案

jojoster / / 技术

翻译自“sqlwithmanoj”的博客

http://sqlwithmanoj.wordpress.com/tag/sqlncli10/

小伙伴们震惊了吧，“SQL Server 2012” 不支持通过连接服务器的方式连接到“SQL Server 2000”了。SQL Server 2012 采用了新版本的SQLNCL11（SQL Server Native Client 11）替代了以前的SQLNCL10，这个客户端只支持连接到2008R2，2008，2005版本的数据库。

笔者加一句：这个SQLNCL11不仅不能通过分布查询连接2000版本数据库，还原操作也不能进行，简而言之，不能读取2000版本的数据了。

问题是这样复现的：

  • 我将我的数据库升级从 2008R2 升级到了2012

  • 将从2008R2 服务器备份的数据库文件还原到了 2012服务器上

  • 在新服务器上执行任务时候，出现了这样的错误

错误信息：

OLE DB provider “SQLNCLI11″ for linked server “NorthWind2000″ returned message “Client unable to establish connection”.
Msg 22, Level 16, State 1, Line 0
SQL Server Native Client 11.0 does not support connections to SQL Server 2000 or earlier versions.
OLE DB provider “SQLNCLI11″ for linked server “NorthWind2000″ returned message “Invalid connection string attribute”.

由于我是升级了数据库，所以我把问题定位到了连接服务器的OLE DB（对象链接嵌入数据库）应用程序接口上，发现了这个SQLNCL11的版本，同时我的服务器上还有SQLNCL10版本。

所以我试着显示使用SQLNCL10去创建连接服务器，但还是出现了如下异常：

Msg 8522, Level 16, State 3, Line 1
Microsoft Distributed Transaction Coordinator (MS DTC) has stopped this transaction.

我贴出我创建连接服务器的脚本，如下：

USE [master]
GO</p>

<p>-- Existing LinkedServer [NorthWind2000]:
EXEC master.dbo.sp_addlinkedserver @server = N'NorthWind2000', @srvproduct=N'MSSQL', @provider=N'SQLNCLI', @provstr=N'PROVIDER=SQLOLEDB;SERVER=NorthWind'</p>

<p>EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'NorthWind2000',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
GO

—>以下是替代的解决方案：

使用ODBC是一个比较好的方案，有两种实现方式

  1. 在操作系统中穿件一个ODBC Data Source (DSN)，在SQLSERVER中只用这个DSN创建连接服务器。
  2. 创建连接服务器时候，直接使用DSN的连接字符串调用ODBC接口。

第一种方式：

首先创建ODBC数据源

  • 打开控制面板，进入管理工具，选择”数据源（ODBC）“

  • 在”ODBC数据源管理器“页签，选取”系统DSN“面板

  • 单击添加创建一个新的数据源

  • 选择”SQL Server“然后点击完成

笔者多嘴2:基本上各大数据库甚至excel都提供了相关接口，都可以连接

  • 在新建数据源页面，建议给数据源适当的命名（比如: NorthWind2000DSN），这个命名会在以后创建练级服务器时候使用到，这里就叫NorthWind，点击下一步

  • 选择验证方式，Windows验证或者Sqlserver都无所谓，进入下一步

  • 选择默认数据库，不选也无所谓，下一步

  • 点击完成，新的DSN就创建了

现在可以使用DSN创建连接服务器了，在@dadasrc参数中使用DSN名称，同时@provider参数中要填写”MSDASQL“.

以下是示例SQL

-- Drop Existing LinkedServer [NorthWind2000]:
EXEC master.dbo.sp_dropserver @server=N'NorthWind2000', @droplogins='droplogins'
GO</p>

<p>-- Re-create LinkedServer [NorthWind2000] by using the ODBC connection:
EXEC master.dbo.sp_addlinkedserver @server = N'NorthWind2000', @srvproduct=N'MSDASQL', @provider=N'MSDASQL', @datasrc = N'NorthWind2000DSN', @location=N'System';</p>

<p>EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'NorthWind2000',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
GO

第二种方式：

直接使用DSN字符串也挺好，在@provstr参数中直接使用DSN连接字符串，请看：

-- Drop Existing LinkedServer [NorthWind2000]:
EXEC master.dbo.sp_dropserver @server=N'NorthWind2000', @droplogins='droplogins'
GO
-- Re-create LinkedServer [NorthWind2000] by using the ODBC connection:
EXEC master.dbo.sp_addlinkedserver @server = N'NorthWind2000', @srvproduct=N'', @provider=N'MSDASQL', @provstr=N'DRIVER={SQL Server};SERVER=NorthWind;Trusted_Connection=yes;'</p>

<p>EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'NorthWind2000',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
GO

使用这种方式可以实现在2012服务器上分布式查询2000版本数据库的数据。

这种方式看似可以直接替代以前的连接服务器，其实效率降低了，如果想实现更有效率的ETL操作，建议升级源数据库的版本，至少到SQL2005。

笔者多嘴3：据不完全统计，ODBC数据源查询效率约为使用SQLNCL查询的1/4。

【迁移】数据库查询调优（SQL 2008）

jojoster / / 技术
  • 数据合并程序调优

关键词：系统问题定位  系统速度调优  消除java程序中循环的数据库操作

背景：程序主要功能是将业务数据定时的从数据库中进行提取，合并，并且导入到接口库中的一个java service程序。随着业务系统以及业务数据的增加，程序执行速度下降显著。由于是数据增加以后出现的这个问题，所以问题定位在数据库操作之上。

调优攻略：

  1. 查询历史执行日志，发现日志使用log4j打出了debug信息，其中包含每一步的操作时间，调查时间间隔比较长的几个步骤。
  2. 查询目标步骤涉及的表数据，发现目标表数据量都很大，有3张表数据达到千万。
  3. 查询其中一张表的全部打印日志，发现操作随着时间变慢，基本都是新增或更新数据的操作。
  4. 根据SQL，对抽取涉及到查询的列创建索引，发现速度基本没有变化。
  5. 由于有源码，查看源码逻辑，发现源码中对每条数据都先进行一次查询有没有的数据库操作，然后才进行一次增加或者更新操作。业务系统每天产生约1W条数据，那么光这一张表就会进行至少2W次操作，并且是在一张1000W大小的表上。
  6. 更改上面逻辑，将更新和新增统一变为先删除，后新增的操作。业务系统无论产生多少数据，只进行2次数据库操作。
  7. 问题解决。

后话：更改以后的操作受到了限制，如果源数据库和目标数据库不在同一台服务器，就会无法完成合并，即：SQL是跨数据库的。但是考虑了实际环境，两个库不可能分开，所以这么写了。

  • 查询统计调优

　　关键词: 统计信息更新  索引创建  执行计划  性能监视器  查询跟踪器

　　背景：之前一个统计分析子系统对数据仓库有大量的查询操作。某一天，用户提出某一个模块查询过慢，导致程序无法正常运行。

　　服务器操作系统：winserver2003

　　数据库：MSSQLServer 2008。

调优攻略：

  1. 使用性能监视器进行监视，开始执行目标操作以后，观察计数器获取线索：
  2. Memory Page Faults/sec  每次执行都会保持很高的值
  3. Memory Page /sec 第一次执行会有值，以后则没有
  4. 说明这个SQL执行过程会进行大量数据的扫描。
  5. 使用SQL Server Profiler开始跟踪，模板选择TSQL，这样会过滤掉一些其他的操作，然后执行目标操作，对SQL进行捕获。
  6. 分析目标SQL，综合索引，查询的表数据量较大，800W左右，但是SQL没有不同，之前查询经过优化，对应分组和过滤的列上也创建有索引。
  7. 对近期客户数据进行比对，发现目标表数据增加了部分，总量的1/20左右，不会对查询造成过大影响。
  8. 偶尔发现查询更改作为参数的查询日期会得出不同的查询速度。分析执行计划，发现执行计划不同。
  9. 查看目标表对应索引的统计信息，发现统计信息不完整，是抽样1.5%得出，只有200行左右。
  10. 目标表每日有合并操作，操作内容是截断表和新增数据，这个操作会引发更新统计信息的操作。
  11. 在合并过程中增加全量更新统计信息操作解决。

深入剖析：

　　问题本质: 过时或者抽样率过小的统计信息会导致效率低下的执行计划.

　　truncate操作以后表的统计信息会过时, 新的统计信息收集会在第一次查询时候进行收集，收集信息策略如下:

　　数据库设置了参数AUTO_CREATE_STATISTICS, 统计信息会自动进行更新具体更新阈值Recompilation threshold (RT)如下:

      1. Permanent table 永久表, 数据量 = n
        • If n <= 500, RT = 500.
        • If n > 500, RT = 500 + 0.20 * n.
      1. Temporary table 临时数据库表 , 数据量=n
        • If n < 6, RT = 6.
        • If 6 <= n <= 500, RT = 500.
        • If n > 500, RT = 500 + 0.20 * n.
      1. Table variable 表变量不进行统计

查看原文

　　对于数据量比较大的表, 自动更新策略会比较缩水,以下是微软的原文：

　　“ 当 AUTO_UPDATE_STATISTICS 数据库选项设置为 ON（默认值）时，查询优化器会在表中的数据发生变化时自动定期更新这些统计信息。每当查询执行计划中使用的统计信息没有通过针对当前统计信息的测试时就会启动统计信息更新。 采样是在各个数据页上随机进行的，取自表或统计信息所需列的最小非聚集索引。 从磁盘读取一个数据页后，该数据页上的所有行都被用来更新统计信息。常规情况是：在大约有 20% 的数据行发生变化时更新统计信息。但是，查询优化器始终确保采样的行数尽量少。对于小于 8 MB 的表，则始终进行完整扫描来收集统计信息。”

这也就是为什么数据的统计信息始终只有很少的原因。

查看原文

ps: 创建索引时候自动创建的统计信息抽样率为100%

ps2: 无论使用什么手动办法更新statistics, 如果不明文指定FULLSCAN, 那么数据库始终使用抽样的方式进行信息收集。╮(╯_╰)╭

调优感想：

  1. 统计信息的查看方式有很多

更多用法

  1.  也可以查询动态视图

  1.  

    更多动态视图用法

    1. 查看统计信息时候要注意，数据库忙的时候，会使用抽样的方式来进行统计信息，此时的统计信息虽然无可厚非，但是对于数据量比较大的表，单靠索引存活，那么很可能导致严重的查询问题。
    2. 这种问题的典型例子就是对于同样的SQL不同的参数，执行计划不同。
    3. 解决办法暂时只是想到了定时进行数据库的统计信息更新，另外，选择TRUNCATE操作一定要慎重。

     通过存储过程

     

    或者通过T-SQL

     

    常用的系统存储过程说明

    sp_who    (sp_who2)报告有关当前 SQL Server 用户和进程的快照信息，包括当前正在执行的语句以及该语句是否被阻塞。

    sp_lock    报告有关锁的快照信息，包括对象 ID、索引 ID、锁的类型以及锁应用于的类型或资源。

    sp_spaceused    显示对表（或整个数据库）所用的当前磁盘空间量的估计。

    sp_monitor    显示统计信息，包括 CPU 使用率、I/O 使用率以及自上次执行 sp_monitor 以来的空闲时间。

    常用的动态管理视图说明

    常规服务器动态管理对象包括：

    dm_db_*：数据库和数据库对象

    dm_exec_*：执行用户代码和关联的连接

    dm_os_*：内存、锁定和时间安排

    dm_tran_*：事务和隔离

    dm_io_*：网络和磁盘的输入/输出

    以下举几个比较重要的动态视图。

    查看原文

    sys.dm_exec_query_stats DMV 提供缓存查询计划的汇总性能统计信息，包括有关物理和逻辑读/写以及查询执行次数的详细信息。它包含从实际 SQL 的父 SQL 中提取实际 SQL 所使用的偏移量。此 DMV 已联接到sys.dm_exec_sql_text DMF，后者包含与 I/O 有关的 SQL 批处理的信息。

    示例1：查询最费IO的查询

     

    示例2：查询最费CPU的查询

     

    示例3：使用 CLR（包括存储过程、函数和触发器）的语句

     

    示例4：最常执行的SQL

     

    示例5：受阻塞影响的查询

     

    sys.dm_db_missing_index_group_stats DMV 记录了 SQL 尝试使用特定缺失索引的次数。sys.dm_db_missing_index_details DMV 详细显示缺失索引的结构，例如查询所需的列。这两个 DMV 通过sys.dm_db_missing_index_groups DMV 联系在一起。缺失索引的开销（总开销列）的计算方法是，用户平均总开销与用户平均影响的积，再乘以用户搜寻次数与用户扫描次数的和。

     以下脚本来确定开销最高的缺失索引。此查询的结果（按“总开销”排序）显示最重要缺失索引的成本以及有关数据库/架构/表和缺失索引中所需列的信息。特别是，此脚本可确定哪些列在相等和不相等 SQL 语句中使用。另外，它还报告应将哪些其他列用作缺失索引中的包含性列。使用包含性列可以在不从基础页获取数据的情况下满足更多的覆盖查询，因而使用的 I/O 操作更少，从而提高性能。

    示例6：高开销的缺失索引

     

    从sys.dm_exec_requests DMV中读取正在运行的请求，以及这个请求的句柄(sql_handle),通过句柄，可以从sys.dm_exec_sql_textDMF得到请求的具体内容。同时，通过(SPID)联接sys.processes这个表，可以得到运行语句的用户、数据库、及应用程序名。

    示例7：查询正在执行的SQL