Link to home
Start Free TrialLog in
Avatar of campbell1972

asked on

SQL 2008: Query response pauses every 30 seconds

Currently looking at a problem with query responses that are impacted approximately every 30 seconds. Simple query response (i.e. a SELECT against a table with a single row) is instantaneuous for the most part, then takes around 1.5 seconds every 30 seconds.

No problem with wait stats, waiting tasks (although I will be checking this again), poor execution plans, cache usage, memory or IO pressure. Setting statistics on confirms that all reads are logical and not physical.

Servers are HP ProLiant DL585 G7s running V8.70 of SmartStart (Insight Agents) and configured as follows:

RAID 10 disk arrays
Windows 2008 Enterprise SP1 x64
SQL 2008 Enterprise x64

One database is mirrored and log-shipped (backups @ 1 minute intervals). Selects against the mirrored database and standalone databases show query response similarly affected every 30 seconds.

Previously diagnosed an issue with the HP Insight Storage Agent that was impacting SQL query response every 2 mins that was resolved by disabling the agent.

On the basis that this issue is every 30 seonds approximately and all performance metrics look spot on, anybody have any ideas as to any other process or service that may be responsible? The fact that this is occurring with such frequency suggests that something is polling.
Avatar of mastoo
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of David
Windows and SQL Server aren't real-time operating systems or applications, neither are device drivers nor file systems.  So first, this is expected behavior.  

You can use filemon from the microsoft system internals and it *may* point out a process, but gut feeling this is nothing more than standard I/O flushing.   There are a lot of things that get flushed, but here is a nice article that talks about tuning for SSD. Ignore the tuning for SSD part and just see all the registry stuff and explanations ...

Note the controller also does some internal housekeeping, so you can look at the long list of ACU settings, but I would first just look at what is more easily controllers by reading article.