Waiting for response from datasource... on the server!

SQL Server Administration
1

Hi folks !

Of late, I've been noticing that queries seem to run slow every once in a while. I figured that it might be a lack of resources or something like that. But today while on my standalone workstation, I ran a Select * query on a tiny 8 column, 50 row table. To my astonishment, I got a "Waiting for response from datasource" and had to wait a good 5-seconds or so to get the results. I checked to see what might be interfering but no process was running other than SQL Server and there is plenty of available memory on the system (I5 8th gen, SSD, 16G RAM). Afterwards, everything seemed to run smoothly again. So, in essence, it looks like the engine just sputtered for a moment but then came back to life.

I can't say that I'm very diligent/knowledgeable when it comes to maintenance but I run a ALTER INDEX ALL REBUILD every 15 days on all tables in all DBs. Right now, it seems that I've got 3 bad injectors on a 6-cylinder system.

Any thoughts would be appreciated! Thanks!

2

You need to check, what was the wait type on SQL server that occured when the query was running.

3

You should take a look at the Ola Hallengren script:
SQL Server Index and Statistics Maintenance (hallengren.com)

4

Can you check to see if your databases have auto close set ON?

SELECT name, is_auto_close_on FROM sys.databases

If that returns 1 for any database, you'll want to turn it off:

ALTER DATABASE myDatabase SET AUTO_CLOSE OFF;

5

I ran the following query to check for wait blockages and it came back clean:

SELECT
owt.session_id,
owt.exec_context_id,
owt.wait_duration_ms,
owt.wait_type,
owt.blocking_session_id,
owt.resource_description,
est.text,
es.program_name,
eqp.query_plan,
es.cpu_time,
es.memory_usage
FROM
sys.dm_os_waiting_tasks owt
INNER JOIN sys.dm_exec_sessions es ON 
owt.session_id = es.session_id
INNER JOIN sys.dm_exec_requests er
ON es.session_id = er.session_id
OUTER APPLY sys.dm_exec_sql_text (er.sql_handle) est
OUTER APPLY sys.dm_exec_query_plan (er.plan_handle) eqp
WHERE es.is_user_process = 1
ORDER BY owt.session_id, owt.exec_context_id
6

I don't get it !?! The databases all had AutoClose ON even though I have a loop which runs on every software update (every 6 months or so) which specifically makes sure that AutoClose is OFF !!! I don't know if this will specifically resolve my root issue but it really irks me to see this. What the heck would make it so that AutoClose just gets turned on again?

That said, thanks Robert for latching on.

7

Check to see if model database has auto close on, and turn it off. Any new database created will inherit from model, unless you specifically change settings.

Any deployment process that uses Database Projects and/or DACFX with a DB project will include database settings in that project, and will create a deployment script to change those settings to match what's in the project. If your dev team/devops team are doing those deployments, you'll need to check their DB projects and get the setting changed, or even better, have them modify their deployment process to skip all database settings when deploying.

If they're using Flyway or another migration-based tool, the same may apply, although it's probably a one-time setting in a migration script, it's just run every time they deploy.

If you want to catch what's causing it, a DDL trigger should do, or a server or database audit. You'll be able to capture login, machine name, and program name, and you can filter for ALTER DATABASE events.

edit: changed AUDIT to ALTER

2 Likes