存档
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.
请参考：
aix与solaris中mount一个iso文件的方法
Linux系统在使用光盘、软盘或U盘时，必须先执行挂载（mount）命令。挂载命令会将这些存储介质指定成系统中的某个目录，以后直接访问相应目录即可读写存储介质上的数据。
1.挂载光盘
挂载光盘的命令如下：
# mount -t is09660 /dev/cdrom /mnt/cdrom
该命令将光盘挂载到/mnt/cdrom目录，使用“ls /mnt/cdrom”命令即可显示光盘中数据和文件。
卸载光盘的命令如下：
# umount /mnt/cdrom
2.挂载软盘
将软盘挂载到/mnt/floppy目录的命令如下：
# mount /dev/fd0 /mnt/floppy
卸载软盘的命令如下：
#umount /mnt/floppy
3.挂载U盘挂载U盘相对复杂一些。
首先使用“fdisk -l”命令查看外挂闪存的设备号，一般为/dev/sda1。然后用“mkdir /mnt/usb”命令建立一个挂载U盘用的目录。之后使用如下命令挂载FAT格式的U盘：
# mount -t msdos /dev/sda1 /mnt/usb
使用如下命令挂载FAT32格式的U盘：
# mount -t vfat /dev/sda1 /mnt/usb
4.挂载外挂硬盘分区
挂载外挂硬盘分区（FAT32格式）同样需要先用“fdisk -1”查看外挂的硬盘分区设备号，假设为/dev/hda1。建立/mnt/vfat挂载目录后，使用如下命令进行挂载：
# mount -t vfat /dev/hda1 /mnt/vfat
注意，默认情况下Linux只允许root用户执行mount命令。如果想让一般用户也能挂载，并且希望在系统启动时自动挂载光盘或软盘，需要修改/etc/fstab配置文件，加入以下内容：
LABEL=/ / ext3 defaults 11
/dev/cdrom/mnt/cdrom iSo9660 auto,owner,kudzu,ro,user 00
/dev/fdo /mnt/floppy auto auto,owner,kudzu,ro,user 00
其中，“user”表示将mount命令赋予一般用户使用。
/etc/fstab文件在Linux的帮助手册中讲得很详细，读者不妨看一看。
光盘的使用方法
mount命令用于物理设备中的文件系统挂在到linux文件系统的某个目录中，mount命令不使用任何选项和参数时将显示当前linux系统中已经挂载的文件系统信息。
#ls -l /media/cdrom/(/media/cdrom/是linux系统默认的挂载点，只有当默认挂载点为total 0,时才能进行挂载。)
#mount -t iso9660 /dev/cdrom /media/cdrom/(光盘挂载)
#ls /media/cdrom/(读取光盘内容)
#cp /dev/cdrom backup.iso(制作光盘镜像)
#umount /media/cdrom/(卸载光盘)
#eject(光盘驱动托盘弹出)
#eject -t(收回)
#mount -o loop -t iso9660 backup.iso /media/cdrom/(光盘镜像的挂载；backup.iso为光盘的镜像文件。)
#umount /media/cdrom/(镜像的卸载)
--------------------------------------------------------------------------
在虚拟机上redhat 5.4 64bit上面，光驱的设备名为：./dev/hdc
挂载用：
mkdir -p /mnt/cdrom
mount /dev/hdc /mnt/cdrom 或者： mount -t iso9660 /dev/hdc /mnt/cdrom
卸载用：
unmount /mnt/cdrom
前日误删除了C:\Documents and Settings\Administrator\.virtualbox目录，导致virtualbox上配置的redhat64bit虚拟机不能启动了。
试图拷贝一个其它虚拟机的xml文件修改一下，但是不可以。提示物理盘的uuid不匹配。那个虚拟机的xml文件丢失了，那么此虚拟机vdi文件上记录的uuid就无从得知了。
难道此redhat64bit虚拟机就不能用了？文件好几个G呢，删除了又太可惜，重装一遍很麻烦。
最后，在网上找到一个解决的办法：
利用virtualbox自带的命令行工具VBoxManage.exe 对残留的vdi虚拟机文件进行克隆，最后会重新生成一个uuid。
D:\Program Files\Oracle\VirtualBox>VBoxManage.exe clonehd /?
Oracle VM VirtualBox Command Line Management Interface Version 3.2.6
(C) 2005-2010 Oracle Corporation
All rights reserved.
Usage:
VBoxManage clonehd <uuid>|<filename> <outputfile>
[--format VDI|VMDK|VHD|RAW|<other>]
[--variant Standard,Fixed,Split2G,Stream,ESX]
[--type normal|writethrough|immutable]
[--remember] [--existing]
Syntax error: Mandatory output file parameter missing
步骤一：
VBoxManage.exe clonehd D:\virtual-machine\linux5_64bit\linux5_64bit.vdi D:\virtual-machine\linux5_64bit\linux5.vdi
耐心等待一会之后，会在命令行的下面显示一个重新生成的uuid，此uuid和D:\virtual-machine\linux5_64bit\linux5.vdi中的磁盘物理uuid是一致的。
步骤二：
在VirtualBox.xml文件中有：
<MachineRegistry>
<MachineEntry uuid="{014d4435-25a9-4ce3-9aa6-ea18cc407698 }" src="Machines\linux5_64bit\linux5.xml "/>
<MachineEntry uuid="{4d307441-f7a7-4196-8dee-d4d6910b0bae}" src="Machines\VMWIN7\VMWIN7.xml"/>
<MachineEntry uuid="{8046d767-abf4-4eb5-bb73-451b54fbba38}" src="D:\virtual-machine\redhat64\redhat64.xml"/>
</MachineRegistry>
对应本机安装的所有的虚拟机，记录各个虚拟机的配置xml文件的位置，以及每个虚拟机对应的机器id（machine uuid）。
步骤三：
找到redhat64bit虚拟机的配置文件Machines\linux5_64bit\linux5.xml，其中
<StorageController name="SATA 控制器" type="AHCI" PortCount="1" useHostIOCache="false" IDE0MasterEmulationPort="0" IDE0SlaveEmulationPort="1" IDE1MasterEmulationPort="2" IDE1SlaveEmulationPort="3">
<AttachedDevice type="HardDisk" port="0" device="0">
<Image uuid="{4a0544d6-e496-4b21-af8c-e432410ea0a7 }"/>
</AttachedDevice>
</StorageController>
上面的image uuid就是通过VBoxManage.exe clonehd重新生成的硬盘id。
步骤四：
linux5.xml中
<Machine uuid="{014d4435-25a9-4ce3-9aa6-ea18cc407698 }" name="linux5" OSType="RedHat_64" lastStateChange="2010-08-16T10:41:07Z">
的014d4435-25a9-4ce3-9aa6-ea18cc407698 要和VirtualBox.xml中的MachineEntry uuid 保持一致。
这样基本上重新打开以下virtualbox虚拟机，就能使用redhat64bit虚拟机了。呵呵。
参考：http://www.modhul.com/2009/06/17/how-to-clone-or-copy-a-virtualbox-virtual-disk/
————————————————————————————————-
—- 本文为andkylee个人原创，请在尊重作者劳动成果的前提下进行转载；
—- 转载务必注明原始出处 : http://www.dbainfo.net
—- 关键字：virtualbox xml hard disk uuid VBoxManage.exe clonehd
————————————————————————————————-
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
window7程序兼容性助手基本上来说是没有用，国内的大部分软件都提示不兼容，但实际是兼容的，弄个这个只是多了麻烦浪费时间而已。
下面介绍4种关闭方法：
一 ：打开 运行（热键：win+R）输入 gpedit.msc 打开 用户配置→ 管理模板→Windows组件→应用程序兼容性→ 关闭程序兼容性助理，设置成“已启用”
二 ：计算机→管理→服务→ 禁用Program Compatibility Assistant Service服务
三 ：点击“开始”，然后在“搜索框”中输入services.msc并回车。 现在滚动滚动条找到 Program Compatibility Assistant Service (程序兼容性助手服务)，点击停止它。
或者执行：
sc stop PcsSvc
sc config PcsSvc start= disabled
四 ： 若要禁用程序兼容性助手警告，使用测试计算机来确定要创建注册表项。 即可使用这些注册表项以禁用程序兼容性助手警告其他计算机上。 要这样做，请按下列步骤操作：
- 在测试计算机上运行在受影响的程序。
- 当收到程序兼容性助手警告消息时，单击以选中 不显示此消息再次 复选框。
请注意 此操作将以下注册表子项中创建一个注册表项：
HKEY_CURRENT_USER\SOFTWARE\Microsoft\Windows NT\CurrentVersion\AppCompatFlags项的名称是代表程序项 appcompat 数据库中的 GUID。 条目类型是 DWORD，并且该条目的值为 0x77。
请注意 程序兼容性助手运行仅为不会被阻止的程序出现警告。
- 退出程序。
- 请注意在步骤 3 中创建的注册表项。
- 在安装脚本包含在安装开始之前添加注册表项的步骤。
再次强调，我们不建议家庭用户禁用PCA，因为这会使得系统不稳定。然后，作为高级用户和管理员，将PCA关闭可以节约时间、避免懊恼的情况发生。
————————————————————————————————-
—- 本文为andkylee个人原创，请在尊重作者劳动成果的前提下进行转载；
—- 转载务必注明原始出处 : http://www.dbainfo.net
—- 关键字：win7 兼容性 禁止 AppCompatFlags PcsSvc
————————————————————————————————-
命令：
==适用于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
以下操作均由sybase用户来做：
新建一个脚本文件，譬如/sybase/script/dump.sh
类容如下
$ cat dump.sh
/sybase/OCS-12_5/bin/isql -Usa -P******* -S******* -i /sybase/script/dump.sql -o /sybse/script/dump.log
mv /caiwubackup/backup/cwbase1.bak /caiwubackup/backup/cwbase1_`date +%Y%m%d`.bak
mv /caiwubackup/backup/cwbase2.bak /caiwubackup/backup/cwbase2_`date +%Y%m%d`.bak
mv /caiwubackup/backup/cwbase3.bak /caiwubackup/backup/cwbase3_`date +%Y%m%d`.bak
mv /caiwubackup/backup/cwmaster.bak /caiwubackup/backup/cwmaster_`date +%Y%m%d`.bak
mv /caiwubackup/backup/master.bak /caiwubackup/backup/master_`date +%Y%m%d`.bak
（注意：`号是在键盘左上角和波浪线在一起的那个键上，不是单引号）
在同一个目录下新建脚本dump.sql
类容如下
$ cat dump.sql
dump database cwbase1 to "/caiwubackup/backup/cwbase1.bak"
go
dump database cwbase2 to "/caiwubackup/backup/cwbase2.bak"
go
dump database cwbase3 to "/caiwubackup/backup/cwbase3.bak"
go
dump database cwmaster to "/caiwubackup/backup/cwmaster.bak"
go
dump database master to "/caiwubackup/backup/master.bak"
go
制定一个定时计划，每天晚上23点30开始备份数据库
$crontab -e
30 23 * * * /sybase/script/dump.sh
根据你本机的实际情况，修改相关数据库名称和路径。