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.
Microsoft SQL Server 2008Server HardwareWindows Server 2008