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

Avatar of undefined
Last Comment
David

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
mastoo

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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 ...

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.
Your help has saved me hundreds of hours of internet surfing.
fblack61