SQL Server 查询超时 [英] SQL Server Query Time Out
问题描述
访问 SQL Server 2012 数据库的第 3 方应用程序在执行约 20 分钟后出现 [Microsoft][ODBC SQL Server Driver]Query timeout expired 错误.
A 3rd party application accessing a SQL Server 2012 database is getting [Microsoft][ODBC SQL Server Driver]Query timeout expired errors after executing for about 20 mins.
这是我在应用程序开始接收错误后在数据库上看到的:
This is what I see on the database after the application starts receiving the errors:
SPID 102 未显示在上述查询中.它是来自同一应用程序的同一进程的另一个连接.我能够在下面的屏幕截图中捕捉到一些细节.它是此屏幕截图中最上面一行的那个.很抱歉截屏时滚动条向右移动了.
SPID 102 is not shown in the query above. It is another connection from the same application for the same process. I was able to capture some details of this in the screenshot below. It is the one in the topmost row in this screenshot. Apologize that the scrollbar was moved to the right when taking the screenshot.
AutoShrink 已设置为 TRUE.
看起来像:
来自稍后启动的第 3 方应用程序的几个连接正在等待 SPID 27 - 这是一个 AUTOSHIRNK 命令
several connections from the 3rd party application which started later on are waiting on SPID 27 - which is an AUTOSHIRNK command
SPID 27 正在等待 SPID 102，这是来自同一个第 3 方应用程序的另一个连接
SPID 27 is waiting on SPID 102 which is another connection from the same 3rd party application
问题:
有什么方法可以判断自数据库上的
AutoShirnk设置为TRUE以来是否创建了与 SPID 27 的连接?
Is there any way to tell if the connection with SPID 27 created since
AutoShirnkwas set toTRUEon the database?
如果是这样，为什么它在 SPID 102 上等待，为什么其他连接(83、85、86、88 和 90)在 27 上等待?
If so, why does it wait on SPID 102 and why are the other connections (83,85,86,88 and 90) waiting on 27?
根据以下链接中的 TechNet 文章，AutoShrink 不应影响数据库上的任何活动
According to the TechNet article in the link below, AutoShrink shouldn't have an impact on any activity on the database
如果数据库的 AUTO_SHRINK 选项设置为 ON，则数据库引擎减少了数据库中文件的大小.本次活动发生在后台并且不会影响其中的任何用户活动数据库.
If a database has the AUTO_SHRINK option set to ON, the Database Engine reduces the size of the files in the database. This activity occurs in the background and does not affect any user activity within the database.
注意事项:
这当前在发生此问题的 SQL Server 2012 SP1 上运行.
This is currently running on SQL Server 2012 SP1 where this issue occurs.
当应用程序在 SQL Server 2008 SP3 上运行数据库时，应用程序中的此过程正常运行.
This process in the application works ok when the application runs off a database on SQL Server 2008 SP3.
推荐答案
AUTO SHRINK 以循环方式工作，如果它看到任何 DB 上的可用空间，它就会释放它.
AUTO SHRINK works on a round robin fashion and if it sees free space on any DB, it deallocates it.
虽然没有像长期阻塞这样的效果，但是确实占用了大量的资源，包括IO和CPU.它还通过缓冲池移动大量数据，因此可能导致热页被推送到磁盘，进一步减慢速度.如果服务器已经在推动 IO 子系统的限制，运行收缩可能会推动它，导致磁盘队列长度过长，并可能导致 IO 超时.
Although it doesn’t have any effect like long-term blocking, it does take up a lot of resources, both IO and CPU. It also moves a lot of data through the buffer pool and so can cause hot pages to be pushed out to disk, slowing things down further. If the server is already pushing the limits of the IO subsystem, running shrink may push it over, causing long disk queue lengths and possibly IO timeouts.
您会在这里得到很多答案和说明 - http://www.sqlskills.com/blogs/paul/auto-shrink-turn-it-off/
You will get a lot of answers and clarifications here - http://www.sqlskills.com/blogs/paul/auto-shrink-turn-it-off/
这篇关于SQL Server 查询超时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
