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
64GB RAM
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.
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
64GB RAM
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 ...
http://www.itechs-systems.com/optimize/Optimizing_Your_Operating_System_For_Use_With_an_SSD_Drive.htm
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.