存档
在ASE中统计某个用户表的信息，比如：表中的行数、分配的空间、数据所占空间、索引所占空间以及剩余空间等，我们可以使用系统存储过程sp_spaceused来实现。
系统存储过程sp_spaceused有两个参数：@objname和@list_indices，第一个@objname是待统计的对象名，一般 是表名；第二个@list_indices标志是否单独统计索引信息，@list_indices=1表示单独统计索引信 息，@list_indices=0则不单独统计。
示例：
1> sp_spaceused sysobjects,0
2> go
name rowtotal reserved data index_size unused
---------- -------- -------- ------ ---------- ------
sysobjects 1014 252 KB 132 KB 68 KB 52 KB
(1 row affected)
(return status = 0)
1> sp_spaceused sysobjects,1
2> go
index_name size reserved unused
------------ ----- -------- ------
ncsysobjects 48 KB 64 KB 16 KB
csysobjects 20 KB 32 KB 12 KB
(1 row affected)
name rowtotal reserved data index_size unused
---------- -------- -------- ------ ---------- ------
sysobjects 1014 252 KB 132 KB 68 KB 52 KB
(return status = 0)
1>
但是利用sp_spaceused还不能实现统计某个库里面哪张表的数据记录数最多，哪张表占用的空间最大，哪些表的行数为0，以及哪些表的索引所占空间大于100M等等的问题。
如何实现呢？
方法一：改造sp_spaceused过程语法中的SQL语句。但是，sp_spaceused过程的源代码有560多行，看起来比较累，至少需要一个临时表存储中间临时数据。
方法二：是本博客专门推荐使用的，利用ASE中的代理表来实现。下面是操作步骤：
假设用户数据库名字为：andkylee
先执行：use andkylee
go
步骤一：建立代理表tablespace2
create existing table tablespace2(name varchar(128) null,rowtotal char(15) ,reserved char(15),[data] char(15),index_size char(15),unused char(15),_objname varchar(128) null,_list_indices int null) external procedure at "loopback.andkylee.dbo.sp_spaceused"
代理表的列 有：name,rowtotal,reserved,data,index_size,unused,_objname,_list_indices，其 中name,rowtotal,reserved,data,index_size,unused这几列接收存储过程sp_spaceused返回的结果 数据。
注意name,rowtotal,reserved,data,index_size,unused的各列的数据类型要和sp_spaceused返回的列的类型一致。
_objname,_list_indices 是过程sp_spaceused的参数列。
需要再sysservers中加入一个指向自己的远程服务器loopback。
sp_addserver loopback,null,@@servername
go
如果@@servername为空，则写dsedit中显示服务器名。
步骤二：在代理表tablespace2上建立视图，统计库内所有用户表的存储空间
create view TableSize(name,rowtotal,reserved,data_KBytes,index_size,unused)
as
select name,cast(str_replace(rowtotal,' KB','') as int),cast(str_replace(reserved,' KB','') as int),cast(str_replace(data,' KB','') as int),cast(str_replace(index_size,' KB','') as int),cast(str_replace(unused,' KB','') as int) from tablespace2 where _list_indices = 0
and _objname in
(
select user_name(uid)+"."+name from andkylee.dbo.sysobjects where type='U'
)
go
说明：可以使用其它的条件来仅仅统计某些表，比如：只统计用户test所拥有的表的空间信息，那么改为： where type='U' and uid=user_id("test")
步骤三： 针对视图TableSize，根据要求写SQL语句
比如：统计记录数最多的10个表的表名、行数、分配空间，用如下的SQL：
select top 10 name,rowtotal,reserved from TableSize order by rowtotal desc
go
1> select top 10 name,rowtotal,reserved from TableSize order by rowtotal desc
2> go
name
rowtotal reserved
-------------------------------------------------------------------------------
-------------------------------------------------
----------- -----------
SOS_Y_Hs_Xell
1044911 178308
test3
1008576 21940
test2
1008576 21992
SOS_Y_Wharehouse
380000 31488
SOS_Y_Hs_Xell_copy_1
288097 49596
FLOWREC1
285139 40580
PartitionTestTable
262144 11368
lzftest
262144 7496
SOS_Y_Hs_Xell_Back
236000 40512
SOS_R_Wharehouse
192000 38408
(10 rows affected)
根据自己的要求写针对TableSize的相应的SQL语句，上面的sql在此抛砖引玉。
备注：
1、由于每次统计过程中都要涉及到待统计表的存储空间大小的计算，所以，上面介绍的方法对系统性能会有一点小的影响。只要不是频繁的执行，个人感觉影响可以忽略。
2、另外针对数据库日志也就是系统表syslogs的统计结果和其它表的不一样。
1> sp_spaceused syslogs
2> go
name total_pages free_pages used_pages
reserved_pages
--------------- --------------- --------------- ---------------
---------------
syslogs 102400 101945 455
0
(1 row affected)
(return status = 0)
1>
所以，不要在上面的被统计表中包含syslogs表。
————————————————————————————————-
—- 本文为andkylee个人原创，请在尊重作者劳动成果的前提下进行转载；
—- 转载务必注明原始出处 : http://www.dbainfo.net
—- 关键字：ASE sp_spaceused 代理表 存储过程 统计表的空间大小 行数最多的表的记录数
所占空间最大的表
————————————————————————————————-
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.