SQL数据库超时过期问题解决方案.doc

SQL数据库超时过期问题的解决方案
减小字体 增大字体
如果SQL的数据库越来越多，有时候会遇到读取超时，死锁等一大堆问题，按经验来说，数据结构设计不合理，经常使用视图等原因都有，那些怎么解决呢？下面看文章
1.由于数据库设计问题造成SQL数据库新增数据时超时
　症状:
　Microsoft OLE DB Provider for SQL Server 错误 '80040e31' （[ODBC SQL Server Driver]超时已过期）;
　服务器上看CPU、内存占用率很低;
　事件日志中提示: 数据库 '*********' 中文件 '***********' 的自动增长在 453 毫秒后已取消或出现超时。使用 ALTER DATABASE 设置更小的 FILEGROWTH 或设置新的大小。
　原因:
　数据库设置时,[文件增长]按百分比来增长,当数据库文件很大时(1G以上),新增操作都会报超时，而这时候其实CPU、内存占用率都非常非常的低。
　解决方法:
　把上述的文件增长这里设置为一个更低的百分比或者直接指定增加多少兆字节。
2.SQL Server数据库超时设置
　修改客户端的连接超时设置。默认情况下，通过企业管理器注册另外一台SQL Server的超时设置是 4 秒，而查询分析器是 15 秒。
　企业管理器中的设置:
　　A、在企业管理器中，选择菜单上的"工具"，再选择"选项"；
　　B、在弹出的"SQL Server企业管理器属性"窗口中，点击"高级"选项卡；
　　C、在"连接设置"下的"登录超时（秒）"右边的框中输入一个比较大的数字，如 30。
　查询分析器中的设置:
　　单击“工具”->"选项"->"连接"; 将登录超时设置为一个较大的数字，连接超时改为0。
3.查询语句时超时
　　原因分析:
　　查询超时一般来说首先要从sql语句和数据表的结构上找原因，优化sql语句和为数据库的查询字段建索引是最常用的办法。　
　　另外，数据库的查询超时设置一般是sqlserver自己维护的（在你没有修改query　wait配置前），只有当你的实际查询时间超过估计查询时间的25倍时，才会超时。
　　而造成超出估计值那么多的原因有两种可能:
　　一是估计时间不准确；
　　二是sql语句涉及到大量占用内存的查询（如排序和哈希操作），内存不够，需要排队等待资源造成的。
　　解决办法:
　　A.优化语句,创建\使用合适的索引;
　　B.解决第一个问题的方法,更新要查询表的索引分发统计，保证估计时间的正确性，UPDATE　STATISTICS　表名;
　　C.增加内存
　　如果想手动设置查询超时，可以使用以下语句：　
　　sp_configure　'show　advanced　options',　1　
　　GO　
　　RECONFIGURE　
　　GO　
　　sp_configure　'query　wait',　2147483647　
　　GO　
　　RECONFIGURE　
　　GO
4.应用程序连接失败
故障:
　在应用程序中我们也会遇到类似的错误信息，例如：
　Microsoft OLE DB Provider for ODBC Drivers 错误 '80004005'. [Microsoft][ODBC SQL Server Driver]超时已过期.
解决方法:
　A.如果遇到连接超时的错误，我们可以在程序中修改 Connection 对象的超时设置，再打开该连接。例如：
<%
Set Conn = Server.CreateObject("ADODB.Connection")
DSNtest="DRIVER={SQL Server};SERVER=ServerName;UID=USER;PWD=password;DATABASE=mydatabase"
Conn. Properties("Connect Timeout") = 15 '以秒为单位
Conn.open DSNtest
%>　
　B.　 如果遇到查询超时的错误，我们可以在程序中修改 Recordset 对象的超时设置，再打开结果集。例如：
Dim cn As New ADODB.Connection
Dim rs As ADODB.Recordset
. . .
cmd1 = txtQuery.Text
Set rs = New ADODB.Recordset
rs.Properties("Command Time Out") = 300
'同样以秒为单位，如果设置为 0 表示无限制
rs.Open cmd1, cn
rs.MoveFirst
. . .
另外,一些硬件及网络方面的原因也可能造成SQL数据库连接超时.

呵呵，这个问题很有趣不是吗？
上面的同志们只是给出一些建议，以我的经验来看（oracle），
如果数据量较大，索引的重复量尽量避免，最好的方式是建立非业务id（最好使用自增或是序列），把这个id建立索引。
你的最大的问题就是，建立了索引后，索引列必须出现在where中，否则索引就白白建立了，比如你的id是从1一直到383000，那么你的语句可以写成
select * from hr_worktime where id>-1
还有就是，where条件中避免出现!=,or,between,等东西，否则索引实效。

SQL查询速度慢的原因
查询速度慢的原因很多，常见如下几种：              1、没有索引或者没有用到索引(这是查询慢最常见的问题，是程序设计的缺陷) 　　2、I/O吞吐量小，形成了瓶颈效应。　　3、没有创建计算列导致查询不优化。　　4、内存不足　　5、网络速度慢　　6、查询出的数据量过大(可以采用多次查询，其他的方法降低数据量) 　　7、锁或者死锁(这也是查询慢最常见的问题，是程序设计的缺陷) 　　8、sp_lock,sp_who,活动的用户查看,原因是读写竞争资源。　　9、返回了不必要的行和列　　10、查询语句不好，没有优化 ●可以通过如下方法来优化查询 : 　　1、把数据、日志、索引放到不同的I/O设备上，增加读取速度，以前可以将Tempdb应放在RAID0上，SQL2000不在支持。数据量(尺寸)越大，提高I/O越重要. 　　2、纵向、横向分割表，减少表的尺寸(sp_spaceuse) 　　3、升级硬件　　4、根据查询条件,建立索引,优化索引、优化访问方式，限制结果集的数据量。注意填充因子要适当(最好是使用默认值0)。索引应该尽量小，使用字节数小的 ...
CREATE INDEX
为给定表或视图创建索引。

只有表或视图的所有者才能为表创建索引。表或视图的所有者可以随时创建索引，无论表中是否有数据。可以通过指定限定的数据库名称，为另一个数据库中的表或视图创建索引。

语法
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
ON { table | view } ( column [ ASC | DESC ] [ ,...n ] )
[ WITH < index_option > [ ,...n] ]
[ ON filegroup ]

< index_option > ::=
{ PAD_INDEX |
FILLFACTOR = fillfactor |
IGNORE_DUP_KEY |
DROP_EXISTING |
STATISTICS_NORECOMPUTE |
SORT_IN_TEMPDB
}
--这是基本语法，建立索引，只针对查询和一些更新和删除的速度，像性别一列，如果表里面有1000行，如果只有1行是男，这样用索引的话肯定高，如果有990行是男，那么它不如直接扫描了，这是选择性

CREATE [UNIQUE] [CLUSTERED│NONCLUSTERED]INDEX index_name ON {table│view} (column [ASC│DESC] [,…n])
　　例1：为表jbxx创建一个非聚集索引，索引字段为employee_name，索引名为i_employeename
　　create index i_employeename on jbxx(employee_name)
　　例2：新建一个表，名称为temp，为此表创建一个惟一聚集索引，索引字段为temp_number，索引名为i_temp_number。
　　use student
　　Create table t_temp
　　(temp_number int,
　　temp_name char(10),
　　temp_age int)
　　create unique clustered index i_temp_number
　　on t_temp(temp_number)
　　例3：为表s创建一个复合索引，使用sex和birthday字段。
　　Use student
　　Create index i_s on s(sex,birthday)
　　(一) 使用企业管理器查看、修改和删除索引的操作
　　在企业管理器中，展开指定的服务器和数据库，右击要创建索引的表，从弹出的快捷菜单中依次选择“所有任务|管理索引”选项，在出现的管理索引对话框中，选择要查看或修改的索引，单击“编辑”按钮，出现“编辑现有索引”对话框。在该对话框中，可以修改索引的大部分设置，还可以直接修改其SQL脚本，只需单击“编辑SQL”按钮，即可出现“编辑Transact_SQl脚本”对话框，在此可以编辑、分析、执行索引的Transact_SQl脚本。
　　要在企业管理器中修改索引的名称，需要在表的“属性”对话框中进行。在企业管理器中，右击要修改名称的表，从弹出的快捷菜单中选择“设计表”选项，在打开的设计表的窗口中，打开表的“属性”对话框，选择“索引/键”选项卡，在此对话框中，先选定要修改索引名称的索引，然后直接在“索引名”文本框中输入心得索引名称替换原来的索引名称。
　　要删除索引，可以在“管理索引”对话框中或表的“属性”对话框中，选择要删除的索引，单击“删除”按钮，即可删除索引。
　　(二) 使用T_SQL查看、修改和删除索引的操作
　　使用系统存储过程查看索引信息，语法如下：
　　sp_helpindex [@objname=] ‘name’
　　例1：查看jbxx表的索引信息
　　sp_helpindex jbxx
　　使用系统存储过程修改索引名称，语法如下:
　　sp_rename[@objname=] ‘object_name’,[@newname=] ‘new_name’
　　[,[@objtype=] ‘object_type’]
　　例2：将s表中的索引i_s的名称改为i_s_sexandbirth
　　use student
　　sp_rename ‘s.i_s’,’i_s_ sexandbirth’,’index’
　　删除索引句法：
　　drop index ‘table.index│view.index’[,…n]
　　例3：删除表s中的索引i_s_sexandbirth