存档
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
命令：
==适用于sqlserver2005 developer
setup.exe /qb INSTANCENAME=MSSQLSERVER ADDLOCAL=SQL_Engine,Client_Components,Connectivity,SQL_Tools90 SAPWD=andkylee SQLACCOUNT="NT AUTHORITY\SYSTEM" SQLPASSWORD= AGTACCOUNT="NT AUTHORITY\SYSTEM" AGTPASSWORD= SQLBROWSERACCOUNT="NT AUTHORITY\SYSTEM" SQLBROWSERPASSWORD= SECURITYMODE=SQL DISABLENETWORKPROTOCOLS=0 SQLCOLLATION=Chinese_PRC_CI_AS ASCOLLATION=Chinese_PRC_CI_AS
==适用于sqlserver2005 express
start /wait setup.exe /qb INSTANCENAME=MSSQLSERVER ADDLOCAL=SQL_Engine,Client_Components,Connectivity,SDK SAPWD=liuzhenfu SQLACCOUNT="NT AUTHORITY\SYSTEM" SQLPASSWORD= AGTACCOUNT="NT AUTHORITY\SYSTEM" AGTPASSWORD= SQLBROWSERACCOUNT="NT AUTHORITY\SYSTEM" SQLBROWSERPASSWORD= SECURITYMODE=SQL DISABLENETWORKPROTOCOLS=0 SQLCOLLATION=Chinese_PRC_CI_AS ASCOLLATION=Chinese_PRC_CI_AS
在用dbcc checkdb 对数据库进行检查的时候，在数据结果的头部报下面的错误：
扩展盘区 (1:9508664) （属于数据库 ID 7 ）在 GAM 中标记为已分配，但没有 SGAM 或 IAM 分配过该盘区。
消息 8905 ，级别 16 ，状态 1 ，服务器 SERVER ，行 1
扩展盘区 (1:9508672) （属于数据库 ID 7 ）在 GAM 中标记为已分配，但没有 SGAM 或 IAM 分配过该盘区。
消息 8905 ，级别 16 ，状态 1 ，服务器 SERVER ，行 1
扩展盘区 (1:9508680) （属于数据库 ID 7 ）在 GAM 中标记为已分配，但没有 SGAM 或 IAM 分配过该盘区。
消息 8905 ，级别 16 ，状态 1 ，服务器 SERVER ，行 1
扩展盘区 (1:9508688) （属于数据库 ID 7 ）在 GAM 中标记为已分配，但没有 SGAM 或 IAM 分配过该盘区。
消息 8905 ，级别 16 ，状态 1 ，服务器 SERVER ，行 1
扩展盘区 (1:9508696) （属于数据库 ID 7 ）在 GAM 中标记为已分配，但没有 SGAM 或 IAM 分配过该盘区。
消息 8905 ，级别 16 ，状态 1 ，服务器 SERVER ，行 1
扩展盘区 (1:9508704) （属于数据库 ID 7 ）在 GAM 中标记为已分配，但没有 SGAM 或 IAM 分配过该盘区。
消息 8905 ，级别 16 ，状态 1 ，服务器 SERVER ，行 1
扩展盘区 (1:9508712) （属于数据库 ID 7 ）在 GAM 中标记为已分配，但没有 SGAM 或 IAM 分配过该盘区。
消息 8905 ，级别 16 ，状态 1 ，服务器 SERVER ，行 1
扩展盘区 (1:9508720) （属于数据库 ID 7 ）在 GAM 中标记为已分配，但没有 SGAM 或 IAM 分配过该盘区。
消息 8905 ，级别 16 ，状态 1 ，服务器 SERVER ，行 1
扩展盘区 (1:9508728) （属于数据库 ID 7 ）在 GAM 中标记为已分配，但没有 SGAM 或 IAM 分配过该盘区。
消息 8905 ，级别 16 ，状态 1 ，服务器 SERVER ，行 1
扩展盘区 (1:9508736) （属于数据库 ID 7 ）在 GAM 中标记为已分配，但没有 SGAM 或 IAM 分配过该盘区。
消息 8905 ，级别 16 ，状态 1 ，服务器 SERVER ，行 1
扩展盘区 (1:9508744) （属于数据库 ID 7 ）在 GAM 中标记为已分配，但没有 SGAM 或 IAM 分配过该盘区。
消息 8905 ，级别 16 ，状态 1 ，服务器 SERVER ，行 1
扩展盘区 (1:9508760) （属于数据库 ID 7 ）在 GAM 中标记为已分配，但没有 SGAM 或 IAM 分配过该盘区。
消息 8905 ，级别 16 ，状态 1 ，服务器 SERVER ，行 1
扩展盘区 (1:9508776) （属于数据库 ID 7 ）在 GAM 中标记为已分配，但没有 SGAM 或 IAM 分配过该盘区。
消息 8905 ，级别 16 ，状态 1 ，服务器 SERVER ，行 1
扩展盘区 (1:9508792) （属于数据库 ID 7 ）在 GAM 中标记为已分配，但没有 SGAM 或 IAM 分配过该盘区。
消息 8905 ，级别 16 ，状态 1 ，服务器 SERVER ，行 1
扩展盘区 (1:9508800) （属于数据库 ID 7 ）在 GAM 中标记为已分配，但没有 SGAM 或 IAM 分配过该盘区。
消息 8905 ，级别 16 ，状态 1 ，服务器 SERVER ，行 1
扩展盘区 (1:9508808) （属于数据库 ID 7 ）在 GAM 中标记为已分配，但没有 SGAM 或 IAM 分配过该盘区。
消息 8905 ，级别 16 ，状态 1 ，服务器 SERVER ，行 1
扩展盘区 (1:9508816) （属于数据库 ID 7 ）在 GAM 中标记为已分配，但没有 SGAM 或 IAM 分配过该盘区。
DBCC 语句的修复级别导致回避了此修复。
DBCC 语句的修复级别导致回避了此修复。
DBCC 语句的修复级别导致回避了此修复。
DBCC 语句的修复级别导致回避了此修复。
DBCC 语句的修复级别导致回避了此修复。
DBCC 语句的修复级别导致回避了此修复。
DBCC 语句的修复级别导致回避了此修复。
DBCC 语句的修复级别导致回避了此修复。
DBCC 语句的修复级别导致回避了此修复。
DBCC 语句的修复级别导致回避了此修复。
DBCC 语句的修复级别导致回避了此修复。
DBCC 语句的修复级别导致回避了此修复。
DBCC 语句的修复级别导致回避了此修复。
DBCC 语句的修复级别导致回避了此修复。
DBCC 语句的修复级别导致回避了此修复。
DBCC 语句的修复级别导致回避了此修复。
DBCC 语句的修复级别导致回避了此修复。
消息 8905 ，级别 16 ，状态 1 ，服务器 SERVER ，行 1
扩展盘区 (1:9508824) （属于数据库 ID 7 ）在 GAM 中标记为已分配，但没有 SGAM 或 IAM 分配过该盘区。
消息 8905 ，级别 16 ，状态 1 ，服务器 SERVER ，行 1
扩展盘区 (1:9508832) （属于数据库 ID 7 ）在 GAM 中标记为已分配，但没有 SGAM 或 IAM 分配过该盘区。
消息 8905 ，级别 16 ，状态 1 ，服务器 SERVER ，行 1
扩展盘区 (1:9508840) （属于数据库 ID 7 ）在 GAM 中标记为已分配，但没有 SGAM 或 IAM 分配过该盘区。
消息 8905 ，级别 16 ，状态 1 ，服务器 SERVER ，行 1
本文介绍如何识别当前的 Microsoft SQL Server 版本号和相应的产品或 Service Pack 级别。同时介绍如何识别正在使用的 SQL Server 具体版本。
如何确定正在运行的 SQL Server 2008 为哪个版本
若要确定正在运行的 SQL Server 2008 为哪个版本，请使用 SQL Server Management Studio 连接到 SQL Server 2008 ，然后运行下列 Transact-SQL 语句。
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')
运行结果如下：
- 产品版本（例如，10.0.1600.22 ）
- 产品级别（例如，RTM ）
- 版本（例如， Enterprise ）
例如，运行结果可能类似于如下内容。
收起该表格展开该表格
|
10.0.1600.22 |
RTM |
Enterprise Edition |
下表列出了 Sqlservr.exe 版本号。
收起该表格展开该表格
版本
Sqlservr.exe
RTM
2007.100.1600.0
SQL Server 2008 Service Pack 1
2007.100.2531.0
Status bits, some of which can be set by using ALTER DATABASE as noted:
1 = autoclose (ALTER DATABASE)
4 = select into/bulkcopy (ALTER DATABASE using SET RECOVERY)
8 = trunc. log on chkpt (ALTER DATABASE using SET RECOVERY)
16 = torn page detection (ALTER DATABASE)
32 = loading
64 = pre recovery
128 = recovering
256 = not recovered
512 = offline (ALTER DATABASE)
1024 = read only (ALTER DATABASE)
2048 = dbo use only (ALTER DATABASE using SET RESTRICTED_USER)
4096 = single user (ALTER DATABASE)
32768 = emergency mode
4194304 = autoshrink (ALTER DATABASE)
1073741824 = cleanly shutdown
Multiple bits can be ON at the same time.
status2
16384 = ANSI null default (ALTER DATABASE)
65536 = concat null yields null (ALTER DATABASE)
131072 = recursive triggers (ALTER DATABASE)
1048576 = default to local cursor (ALTER DATABASE)
8388608 = quoted identifier (ALTER DATABASE)
33554432 = cursor close on commit (ALTER DATABASE)
67108864 = ANSI nulls (ALTER DATABASE)
268435456 = ANSI warnings (ALTER DATABASE)
536870912 = full text enabled (set by using sp_fulltext_database )
在sql server 2000的错误日志文件中出现如下的错误：
2010-08-05 09:21:51.31 spid11 错误: 823，严重度: 24，状态: 2
2010-08-05 09:21:51.31 spid11 I/O error (torn page) detected during read at offset 0x0000116496a000 in file 'd:\Microsoft SQL Server\MSSQL\data\xxxxxxx_Data.MDF'.
======================================================
Description&Solution:
Torn_page_detection:
This recovery option allows SQL Server to detect incomplete I/O operations caused by power failures or other system outages.
When set to ON, this option causes a bit to be reversed for each 512-byte sector in an 8-kilobyte (KB) database page when the page is written to disk. If a bit is in the wrong state when the page is later read by SQL Server, the page was written incorrectly; a torn page is detected. Torn pages are usually detected during recovery because any page that was written incorrectly is likely to be read by recovery.
Although SQL Server database pages are 8 KB, disks perform I/O operations using a 512-byte sector. Therefore, 16 sectors are written per database page. A torn page can occur if the system fails (for example, due to power failure) between the time the operating system writes the first 512-byte sector to disk and the completion of the 8-KB I/O operation. If the first sector of a database page is successfully written before the failure, the database page on disk will appear as updated, although it may not have succeeded.
Note Using battery-backed disk caches can ensure that data is successfully written to disk or not written at all.
If a torn page is detected, an I/O error is raised and the connection is killed. If the torn page is detected during recovery, the database is also marked suspect. The database backup should be restored, and any transaction log backups applied, because it is physically inconsistent.
By default, TORN_PAGE_DETECTION is ON.