存档
演示在用java连接SQL Server数据库。用了一个简单的java程序作试验。
第一步：获得jdbc for SQL Server驱动程序
sqljdbc.jar 和 sqljdbc4.jar sqljdbc_auth.dll
为了支持向后兼容以及可能的升级方案，JDBC Driver 2.0 在每个安装包中都包括 2 个 JAR 类库：sqljdbc.jar 和 sqljdbc4.jar。
| JAR | 说明 |
|---|---|
|
sqljdbc.jar |
sqljdbc.jar 类库提供对 JDBC 3.0 的支持。 sqljdbc.jar 类库要求使用 5.0 版的 Java 运行时环境 (JRE)。连接到数据库时，在 JRE 6.0 上使用 sqljdbc.jar 会引发异常。 注意： JDBC Driver 2.0 不支持 JRE 1.4。使用 JDBC Driver 2.0 时必须将 JRE 1.4 升级至 JRE 5.0 或更高版本。在某些情况下，您可能需要重新编译应用程序，因为它可能与 JDK 5.0 或更高版本不兼容。有关详细信息，请参阅 Sun Microsystems 网站上的文档。
|
|
sqljdbc4.jar |
sqljdbc4.jar 类库提供对 JDBC 4.0 的支持。它不仅包括 sqljdbc.jar 的所有功能，还包括新增的 JDBC 4.0 方法。 sqljdbc4.jar 类库要求使用 6.0 或更高版本的 Java 运行时环境 (JRE)。在 JRE 1.4 或 5.0 上使用 sqljdbc4.jar 会引发异常。 注意： 如果应用程序必须在 JRE 6.0 上运行，即使该应用程序不使用 JDBC 4.0 功能，也应使用 sqljdbc4.jar。
|
请注意，尽管驱动程序旨在与所有主要的 Sun 等效 Java 虚拟机一起工作并由这些虚拟机支持，但却是在 Sun JRE 5.0 或更高版本上进行测试的。
注意：JRE6的要选择sqljdbc4.jar
第二步：设置环境变量CLASSPATH
将下载下来的sqljdbc.jar或sqljdbc4.jar放到目录d:\shared\lib下。将环境变量CLASSPATH设置为：D:\shared\lib\sqljdbc.jar
set CLASSPATH=.;d:\shared\lib\sqljdbc.jar;
(注意：第一个“.”，这个代表当前目录。)
第三步：编写一段简单的java程序。
下面的这段程序使用驱动sqljdbc.jar连接SQL Server 2005数据库，并提取master数据库中的用户表的ID和name两列信息。
SQL Server 2005服务器在本机，端口为1433。登录sa的密码为：secret
执行：select id,name,crdate from dbo.sysobjects where type='U' 这条语句返回当前数据库（这可在url中指定！）中的用户表的id和name列。
以下存储过程能够统计sqlserver数据库内所有用户表所占用的空间，并按照占用空间大小倒序排列。
--------------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE GetAllTableSizes
AS
DECLARE @TableName VARCHAR(100)
DECLARE tableCursor CURSOR FORWARD_ONLY
FOR
select [name] from dbo.sysobjects
where OBJECTPROPERTY(id, N'IsUserTable') = 1
FOR READ ONLY
CREATE TABLE #TempTable
(
tableName varchar(100),
numberofRows varchar(100),
reservedSize varchar(50),
dataSize varchar(50),
indexSize varchar(50),
unusedSize varchar(50)
| 一、使用 Microsoft OLE DB Provider For ODBC 链接MySQL 安装MySQL的ODBC驱动MyODBC 1、为MySQL建立一个ODBC系统数据源，例如:选择数据库为test ,数据源名称为myDSN 2、建立链接数据库 EXEC sp_addlinkedserver @server = ’MySQLTest’, @srvproduct=’MySQL’, @provider = ’MSDASQL’, @datasrc = ’myDSN’ GO EXEC sp_addlinkedsrvlogin @rmtsrvname=’MySqlTest’,@useself=’false’,@locallogin=’sa’,@rmtuser=’mys ql的用户名’,@rmtpassword=’mysql的密码’ 3、查询数据 SELECT * FROM OPENQUERY (MySQLTest ,’select * from 表’ ) 下面这个不行: SELECT * FROM OPENQUERY (MySQLTest ,’表’ ) 注意:不能直接用select * from 链接服务器名.数据库名.用户名.表(或视图) 四部分名称查询数据，可能是个Bug. 二、使用 Microsoft OLE DB Provider For ORACLE 链接ORACLE 1、建立链接数据库 sp_addlinkedserver ’别名’, ’Oracle’, ’MSDAORA’, ’服务名’ GO EXEC sp_addlinkedsrvlogin @rmtsrvname=’别名 ’,@useself=’false’,@locallogin=’sa’,@rmtuser=’oracle用户名 ’,@rmtpassword=’密码’ 2、查询数据 SELECT * FROM 别名..用户名.表(视图) 注意:四部分名称全部用大写 3、执行存储过程 使用OPENQUERY: SELECT * FROM OPENQUERY(别名, ’exec 用户名.存储过程名’) 三、设置链接服务器以访问格式化文本文件 用于 Jet 的 Microsoft OLE DB 提供程序可用于访问并查询文本文件。 若要直接创建访问文本文件的链接服务器而不将文件链接为 Access .mdb 文件中的表，请行 sp_addlinkedserver，如下例所示。 提供程序是 Microsoft.Jet.OLEDB.4.0，提供程序字符串为"Text"。数据源是包含文本文件的目录的完整路径名称。 schema.ini 文件(描述文本文件的结构)必须与此文本文件存在于相同的目录中。有关创建 schema.ini 文件的更多信息，请参见 Jet 数据库引擎文档。 --Create a linked server. EXEC sp_addlinkedserver txtsrv, ’Jet 4.0’, ’Microsoft.Jet.OLEDB.4.0’, ’c:\data\distqry’, NULL, ’Text’ GO --Set up login mappings. EXEC sp_addlinkedsrvlogin txtsrv, FALSE, NULL, Admin, NULL GO --List the tables in the linked server. EXEC sp_tables_ex txtsrv GO --Query one of the tables: file1#txt --using a 4-part name. SELECT * FROM txtsrv...[file1#txt] |
| 四、链接SQL Server服务器: 1、使用 ODBC 的 Microsoft OLE DB 提供程序 EXEC sp_addlinkedserver ’别名’,’’,’MSDASQL’,NULL,NULL,’DRIVER={SQL Server};SERVER=远程名;UID=用户;PWD=密码;’ 如果加上参数@catalog，可以指定数据库 exec sp_addlinkedsrvlogin @rmtsrvname=’别名 ’,@useself=’false’,@locallogin=’sa’,@rmtuser=’sa’,@rmtpassword=’密码’ 2、使用SQL Server 的 Microsoft OLE DB 提供程序 exec sp_addlinkedserver @server=’别名 ’,@provider=’sqloledb’,@srvproduct=’’,@datasrc=’远程服务器名’ exec sp_addlinkedsrvlogin @rmtsrvname=’wzb’,@useself=’false’,@locallogin=’sa’,@rmtuser=’sa’,@rmtp assword=’密码’ 然后你就可以如下: select * from 别名.库名.dbo.表名 insert 库名.dbo.表名 select * from 别名.库名.dbo.表名 select * into 库名.dbo.新表名 from 别名.库名.dbo.表名 go 例1、 此示例在 SQL Server 的实例上创建一台名为 S1_instance1 的链接服务器， 该服务器使用 SQL Server 的 Microsoft OLE DB 提供程序。 EXEC sp_addlinkedserver @server=’S1_instance1’, @srvproduct=’’, @provider=’SQLOLEDB’, @datasrc=’S1\instance1’ 例2、 --建立链接服务器 EXEC sp_addlinkedserver ’xiaoming’,’’,’MSDASQL’,NULL,NULL,’DRIVER={SQL Server};SERVER=192.168.0.1;UID=sa;PWD=123;’ --建立链接服务器登录映射 exec sp_addlinkedsrvlogin @rmtsrvname=’xiaoming’,@useself=’false’,@locallogin=’sa’,@rmtuser=’sa’, @rmtpassword=’123’ go --查询数据 select * from xiaoming.schooladmin.dbo.agent --删除链接服务器登录映射和链接服务器: exec sp_droplinkedsrvlogin ’xiaoming’ ,’sa’ exec sp_dropserver ’xiaoming’ 注意事项: SET IDENTITY_INSERT [ database.[ owner.] ] { table } { ON | OFF } 所以不能通过连接服务器设置此属性 into 也存在这样的问题 select * into xiaoming.northwind.dbo.tt from xiaoming.northwind.dbo.tt |
五、设置链接服务器以访问Access数据库
使用用于 Jet 的 Microsoft OLE DB 提供程序
此示例创建一台名为 test的链接服务器。
说明 本示例假设已经安装 Microsoft Access 和示例 Northwind 数据库，且
Northwind 数据库驻留在 C:\。
USE master
GO
-- To use named parameters:
EXEC sp_addlinkedserver
@server = ’test’,
@provider = ’Microsoft.Jet.OLEDB.4.0’,
@srvproduct = ’OLE DB Provider for Jet’,
@datasrc = ’C:\Northwind.mdb’
GO
-- OR to use no named parameters:
USE master
GO
EXEC sp_addlinkedserver
’test’,
’OLE DB Provider for Jet’,
’Microsoft.Jet.OLEDB.4.0’,
’C:\Northwind.mdb’
GO
使用
select * from test...表名
六、连接SYBASE
--首先,你要在SQL服务器上装上访问sybase的客户端
--创建链接服务器
exec sp_addlinkedserver ’Sybase1’, ’ ’, ’MSDASQL’, NULL, NULL
,’Driver={Sybase System
11};Database=hisdb;Srvr=10.211.135.12;UID=sa;PWD=1111;’
使用:
select * from Sybase1.hisdb.dbo.table1
方法二
使用ODBC
SQL Server到SYBASE连接服务器的实现
作者: CCBZZP
本文的测试环境为:
操作系统: WINDOWS2000 SERVER (繁体系统)
安装数据库: SQLSERVER2000(英文版)和SYBASE8.0客户端(英文版)
具体实现步骤:
1.要求pc机上安装SYBASE8.0客户端软件和sqlserver2000软件。
2.配置windows的ODBC数据源:
开始菜单—》程式集—》系统管理工具—》资料数据源(ODBC)—》进入配置用户DSN或者系统DSN均可以:添加—》选择 ADAPTIVE SERVER ANYWHERE8.0—》自定义数据源名称(随意如: SYBASETEST)—》数据库名称(必选!)—》OK完成。
3. 选择刚才配置的数据源名称, 再选择 配置, 跳出SYBASETEST MESSAGES:
The data source is not connected. Connecting to the data source will
provide useful information during configuration. Would you like to
connect to the data source?
选择YES(OK或确认)即可
进入CONNECT TO SYBASE DATABASE画面:
USER ID: 输入SYBASE DATABASE的用户
PASSWORD: 输入SYBASE DATABASE的用户的密码
CONNECTION MODE: 可以选择默认的SHARE模式
选择OK(确认)即可!
配置和测试ODBC完成!
4.配置sqlserver2000中的连接服务器:
企业管理器—》安全性—》连接服务器—》右键新建连接服务器—》定义连接名称; 选其他数据源; 指定程序名称为:SYBASE ADAPTIVE SERVER ANYWHERE PROVIDER8.0; 产品名称可不填; 数据源指定刚才ODBC中定义好的数据源名称;提供程序字符串按以下格式填写:User ID=username;Password=userpasswd(或者按如下格式:UID=username;PWD=userpasswd)，这里的用户名和密码对应所要连接的SYBASE数据库中的用户名和密码 —》 安全性标签页里:设置用此安全上下文进行，并录入SYBASE的数据库用户名和密码—》服务器选项标签页可默认—》确定。
5.准备工作全部完成!在 sqlserver企业管理器—》安全性—》连接服务器打开刚建好的连接服务器—》点击表，即可在右边窗口看到该SYBASE数据库用户拥有的所有表名，但在这里还并不能查看表的记录，这个需要在sqserver的查询分析器中用具体sql实现!访问表时，使用格式为: [连接服务器名].. [SYBASE用户].[表名]。
oracle的版本为：10gr2，10.2.0.1.0。
利用oracle中的hsodbc连接sqlserver2005数据库。已成功连接。db link的名字为： sql2005fororacle。
执行：select count(*) from sysdatabases@sql2005fororacle; 能够成功执行。
但是，在执行： select count(*) from sysobjects@sql2005fororacle;却导致系统出现了死机症状，无法响应任务的操作，任务管理器看到cpu利用率不高。无奈，只能强制关机。
在alert_<SID>.log文件中发现有这么几句：
Memory Notification: Library Cache Object loaded into SGA
Heap size 2225K exceeds notification threshold (2048K)
KGL object name :XDB.XDbD/PLZ01TcHgNAgAIIegtw==
原来遇到oracle的bug了。上网检索，说 是oracle的一个bug在10g10.2.0.1的各个平台上都出现过。 Oracle10g中，在load较大的对象进library cache中时，会记录以上警告。在版本10.2.0.1中，这个定义大对象的阈值是2M，这是有隐含参数 _kgl_large_heap_warning_threshold 指定的从10.2.0.2起，这个参数的默认值被修改为50M。
解决方法1 ：升级到10.2.0.2或者以上。
解决方法2 ：
SQL> alter system set "_kgl_large_heap_warning_threshold"=20388608 scope=spfile ;
SQL> shutdown immediate
SQL> startup
或者将 2038860调成更大的值。
我是通过调整隐式参数的方式解决的，重启后。
下面就是在oracle中访问sqlserver2005数据库的数据的效果：
SQL> select "name" from sysobjects@sql2005fororacle where "type"='U';
name
--------------------------------------------------------------------------------
spt_fallback_db
spt_fallback_dev
spt_fallback_usg
QuestSoftwareProc
pbcattbl
pbcatcol
pbcatfmt
pbcatvld
pbcatedt
spt_monitor
spt_values
name
--------------------------------------------------------------------------------
MSreplication_options
已选择12行。
SQL> desc sysusers@sql2005fororacle
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
uid NUMBER(5)
status NUMBER(5)
name NOT NULL VARCHAR2(128 CHAR)
sid RAW(85)
roles RAW(2000)
createdate NOT NULL DATE
updatedate NOT NULL DATE
altuid NUMBER(5)
password RAW(256)
gid NUMBER(5)
environ VARCHAR2(32512 CHAR)
hasdbaccess NUMBER(10)
islogin NUMBER(10)
isntname NUMBER(10)
isntgroup NUMBER(10)
isntuser NUMBER(10)
issqluser NUMBER(10)
isaliased NUMBER(10)
issqlrole NUMBER(10)
isapprole NUMBER(10)
SQL>
dbcc writepage是sqlserver 2000 和 2005中未公开的命令，也就是说使用这些未官方支持的命令出现的任何后果与MS无关。
同样，本文仅仅演示dbcc writepage的使用，您在操作时出现的任何后果与本人无关。
dbcc writepage的语法为：
dbcc writepage ({ dbid, ‘dbname’ }, fileid, pageid, offset, length, data)
下面演示dbcc writepage的使用方法：
----------------------------------------------------------------------------
第一步：创建一个测试表
1> create table test (id int not null,name varchar(30) null)
2> go
1> insert into test
2> select 1,'china'
3> go
(1 行受影响)
1> insert into test
2> select 2,'beijing'
3> go
(1 行受影响)
1> select object_id('test')
2> go
-----------
1877581727
(1 行受影响)
表的ID为： 1877581727。
第二步：查看表内数据所在的页面号
1> select * from sysindexes
2> where id = 1877581727
3> go
id status first indid root minlen keycnt groupid dpages
reserved used rowcnt rowmodctr reserved3 reserved4
xmaxlen maxirow OrigFillFactor StatVersion reserved2 FirstIAM impid lockflags
pgmodctr keys
name
statblob
maxlen rows
----------- ----------- -------- ------ -------- ------ ------ ------- ---------
-- ----------- ----------- -------------------- ----------- --------- ---------
------- ------- -------------- ----------- ----------- -------- ------ ---------
----------- -------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------- ----------------
--------------------------------------------------------------------------------
-------------------------------- -----------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------- ----------- -----------
1877581727 0 0x3B4F00 0 0x000000 8 0 1
1 2 2 2 2 0 0
57 0 0 0 0 0x3C4F00 0 0
0 NULL
NULL
NULL
8000 2
(1 行受影响)
查询属于test表的第一个数据页的页号，在sysindexes表中first列表示对象的第一个物理存储页的页号：0x384F00，也就是页号（1：20283）。
第三步：查询修改前的数据内容
下面查询修改前页面（1，20283）上的数据内容。可以看出（1，20283）页上有2行记录，这和插入的2行记录数保持一致。然后，观察到页面的头部有china和beijing的字样，
这和插入的2行记录内容“似乎”一致。
下面我用红色标记了test表中两行测试数据的内容。1,'china' 2,'beijing'
1> dbcc page(9,1,20283,2)
2> go
DATA:
Memory Dump @0x518FC000
518FC000: 01010400 00800001 00000000 00000800 ?................
518FC010: 00000000 00000200 aa000000 721f8a00 ?............r...
518FC020: 3b4f0000 01000000 92010000 75000000 ?;O..........u...
518FC030: 02000000 00000000 00000000 00000000 ?................
518FC040: 01000000 00000000 00000000 00000000 ?................
518FC050: 00000000 00000000 00000000 00000000 ?................
518FC060: 30000800 01000000 0200fc01 00140063 ?0..............c
518FC070: 68696e61 30000800 02000000 0200fc01 ?hina 0...........
518FC080: 00160062 65696a69 6e670000 21212121 ?...beijing ..!!!!
518FC090: 21212121 21212121 21212121 21212121 ?!!!!!!!!!!!!!!!!
518FC0A0: 21212121 21212121 21212121 21212121 ?!!!!!!!!!!!!!!!!
........... 这些是未分配使用的空间
518FDFC0: 21212121 21212121 21212121 21212121 ?!!!!!!!!!!!!!!!!
518FDFD0: 21212121 21212121 21212121 21212121 ?!!!!!!!!!!!!!!!!
518FDFE0: 21212121 21212121 21212121 21212121 ?!!!!!!!!!!!!!!!!
518FDFF0: 21212121 21212121 21212121 74006000 ?!!!!!!!!!!!!t.`.
OFFSET TABLE:
Row - Offset
1 (0x1) - 116 (0x74)
0 (0x0) - 96 (0x60)
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
第四步：修改物理数据页面的内容
通过上面的dbcc page输出结果，仔细数了一下，china这列内容的偏移为111，将china这五个字符改成aaaa。这是最简单的。a的asicc为：97（0x61）。
1> db cc writepage(9,1,20283,111,5,0x6161616161)
2> go
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
第五步：查看修改后的物理页面的内容
1> dbcc page(9,1,20283,2)
2> go
DATA:
Memory Dump @0x5002C000
5002C000: 01010400 00820001 00000000 00000800 ?................
5002C010: 00000000 00000200 aa000000 721f8a00 ?............r...
5002C020: 3b4f0000 01000000 92010000 75000000 ?;O..........u...
5002C030: 02000000 00000000 00000000 8234666e ?.............4fn
5002C040: 00000000 00000000 00000000 00000000 ?................
5002C050: 00000000 00000000 00000000 00000000 ?................
5002C060: 30000800 01000000 0200fc01 00140061 ?0..............a
5002C070: 61616161 30000800 02000000 0200fc01 ?aaaa 0...........
5002C080: 00160062 65696a69 6e670000 21212121 ?...beijing ..!!!!
5002C090: 21212121 21212121 21212121 21212121 ?!!!!!!!!!!!!!!!!
5002C0A0: 21212121 21212121 21212121 21212121 ?!!!!!!!!!!!!!!!!
5002C140: 21212121 21212121 21212121 21212121 ?!!!!!!!!!!!!!!!!
5002C150: 21212121 21212121 21212121 21212121 ?!!!!!!!!!!!!!!!!
..................省略未占用空间
5002DFE0: 21212121 21212121 21212121 21212121 ?!!!!!!!!!!!!!!!!
5002DFF0: 21212121 21212121 21212121 74006000 ?!!!!!!!!!!!!t.`.
OFFSET TABLE:
Row - Offset
1 (0x1) - 116 (0x74)
0 (0x0) - 96 (0x60)
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
第六步：通过客户端工具验证修改后的数据内容
1> select * from test
2> go
id name
----------- ------------------------------
1 aaaaa
2 beijing
(2 行受影响)
可以看到第一行记录的name列由china改为了aaaaa
备注： 以上演示的是仅仅修改某一行某一列的数据，并且修改后的内容和修改前的内容保持长度一致。
如果修改多列或者修改前后的字段长度不一致；进一步修改整行数据，或者修改整页的数据的话，可能会复杂许多。
————————————————————————————————-
—- 本文为andkylee个人原创，请在尊重作者劳动成果的前提下进行转载；
—- 转载务必注明原始出处 : http://www.dbainfo.net
—- 关键字：dbcc writepage undocumented command 修改 物理页面 page
————————————————————————————————-
What does the “I/O request” error below represent?
2008-04-21 13:26:42.480 spid364 Microsoft SQL Server 2005 - 9.00.3177.00 (Intel X86)
2008-04-22 16:30:02.140 spid6s SQL Server has encountered 2 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [F:\sql data files\xxx.MDF] in database [xxx] (5).
2008-04-22 16:32:08.780 spid6s SQL Server has encountered 2 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [H:\sql data files\xxx_data_4.NDF] in database [xxx] (5).
Research and Findings
---------------------------------
These errors may occur as result of “CPU Drift” and can be ignored and disabled, howerver, first verify both SQL DMV sys.dm_io_pending_io_requests and Windows Performance counters don’t indicate any IO delays.
On computers with multiple CPUs, the CPUs are designed to “sleep” during periods of low workload. When CPUs sleep, SQL may not accurately determine CPUs overall workload and incorrectly report this as IO WARNING shown above, however, this does not represent an actual CPU performance problem.
To confirm if the CPUs entered low-power state, SQL Server Escalation Services created RDTSC (Read Time Stamp Counter) utility to report total CPU sleep time. The report confirmed the CPUs were sleeping up to 24 seconds. This would be enough for SQL Server to incorrectly report this a slow IO. Both AMD’s and INTEL’s web sites describe the ability for CPUs to sleep.
RDTSCTest.exe [-md|-mt]
-md Detailed output (default)
-mt CPU speeds in table format
-- Current CPU Speeds --
Runtime CPU ExpectedMHz ActualMHz RDTSCTicks DriftMS
-------------------- ---- ----------- --------- -------------------- ----------------
2008-04-22 17:53:36 0 3502 3503 0x0001564772F87FA72 16123.4
2008-04-22 17:53:36 8 3502 3506 0x00015647D8B7AE21D 23922.5
2008-04-22 17:53:36 16 3502 3507 0x00015647B5FEB4A39 21260.9
For more information on RDTSC can be found at
Starting with SQL 2005 SP2 we’ve included two trace flags to disable the reporting of CPU Drift errors in the SQL Server errorlog.
Disable this error using Trace Flag 8033
The time stamp counter of CPU on scheduler id 1 is not synchronized with other CPUs.
Disable this error using Trace Flag 830
SQL Server has encountered 2 occurrence(s) of I/O requests taking longer than 15 seconds to complete
-------------------------------------------------------------------------------------------------------
In the majority of cases this warning indicates that an I/O operation has taken too long. This article looks at the possible reasons for this and describes what can be done to reduce the likelihood of it recurring.
There is another possible reason that is not I/O related and this is briefly referred to at the end of the article.
Let's start by examining the reason behind it. I/O requests from SQL Server are handled asynchronously by the operating system. This means that when a read or write request is made, the thread making it waits for the I/O to complete.
This frees CPU for use by another thread. Optimally an I/O should take a few ms, but can take considerably more than this. This warning is reported when the time between requesting an I/O and its completion is greater than 15 seconds.
The possible reasons for this are: I/O subsystem problems or misconfiguration, excessive I/O being requested by SQL Server, data files not optimally placed on the disk, and fragmentation.
I/O Subsystem
The first thing to do is to examine the I/O subsystem. Hardware errors are a common cause and you should run diagnostics if you are at all suspicious that this might be the case.
Two common reasons for poor I/O throughput on a SAN are out of date firmware, and insufficient queue length on the HBA. Be aware that if you upgrade SAN firmware you often need to upgrade the HBA drivers at the same time, or the server may fail to access the SAN altogether. Contact the SAN vendor for confirmation and details.
Another common cause of poor I/O performance is if a file system filter driver has been installed. A filter driver intercepts requests before they reach the file system, and performs additional processing such as anti-virus checking and encryption. It goes without saying that this can only have a negative impact on I/O performance.
If you have to have anti-virus software installed on your SQL Server, ensure that mdf, ndf and ldf files are added to the exclusion list. Even better is for realtime virus checking to be disabled completely; schedule a regular scan during quiet times instead.
I/O Load
It is often the case that there are I/O issues at night, when batch jobs are running, but it performs well during the day with no warnings in the logs.
You may find that there are several jobs running at the same time, either through poor scheduling or because one or more jobs have overrun. Provided there is sufficient free time you could reschedule one or more jobs and monitor for a couple of days to see if it has made a difference.
This is more complicated in environments where a SAN is used by several servers, as it may be jobs running on non-SQL servers overloading the SAN and causing your problems. In this case you need to discuss the problem with the system administrators responsible for these servers and agree a schedule that is mutually acceptable.
Fragmentation
Fragmentation may be internal (within tables/indexes) or external (file fragmentation on the disk).
Internal fragmentation is only a problem where in-order scans are being performed, and only on large tables. The warning message that is the subject of this article is unlikely to occur in this situation. Best practise is to monitor logical fragmentation and regularly rebuild or reorganize affected indexes.
Disk fragmentation can also be reduced through best practise, such as presizing data files (capacity planning), and not using autoshrink , or manually shrinking database files.
If you suspect disk fragmentation may be causing performance problems, I can recommend Diskeeper . This runs continuously in the background using idle CPU time only, and is able to defragment SQL Server files while they are in use. Your mileage may vary, but I have seen staggering improvements from running this tool.
I/O Configuration
Other best practise includes splitting tempdb into several files, putting data and log files on separate disks, placing clustered and non-clustered indexes in separate filegroups (on different disks), keeping statistics up-to-date, etc. All these will improve performance and some are described in SQL Server Configuration
.
One Last Possibility
In a minority of cases there may be not be an issue with I/O at all, but a problem caused by CPU drift. This is described in this article .
I stress that this is rare, and you should always examine the possible causes of slow I/O described above.
Summary
This article has described some of the most common reasons for "SQL Server has encountered n occurrence(s) of I/O requests taking longer than 15 seconds to complete on file <filename> in database <dbname>" .
In most cases this message is an accurate description of the problem, and reasons for slow I/O should be investigated.
sql server 2005中增加了动态性能视图，可以查看sys.dm_exec_query_stats获得正在执行的SQL的一些信息。
下面的SQL语句查询当前运行时间最长的10个事务的信息：
SELECT DISTINCT TOP 10
t.TEXT QueryName,
s.execution_count AS ExecutionCount,
s.max_elapsed_time AS MaxElapsedTime,
ISNULL(s.total_elapsed_time / 1000 / NULLIF(s.execution_count, 0), 0) AS AvgElapsedTime,
s.creation_time AS LogCreatedOn,
ISNULL(s.execution_count / 1000 / NULLIF(DATEDIFF(s, s.creation_time, GETDATE()), 0), 0) AS FrequencyPerSec
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
ORDER BY s.max_elapsed_time DESC, ExecutionCount DESC
go