December 5, 2019 at 8:41 pm
I'm dealing with a strange problem that started today. A simple query that will return 5 rows from a table with about 61,000 total rows takes over 20 seconds to complete. At other times, the query will take less than one second. Queries to every other table in this same database respond in less than one second. I understand that there are variations in the # of records and complexity of the tables, but it's just this one table that I'm having trouble with, and not all of the time.
This is on my development/test environment so I'm the only user accessing SQL, so it's not a network/data traffic issue.
Any ideas on what I can do to diagnose and correct this problem? My fear is that if this is happening in my development environment it could also happen in the production environment which would not be good.
Any help would be greatly appreciated!
December 5, 2019 at 8:54 pm
I suggest that you collect the execution plans for the slow and fast versions and compare them. The comparison should provide clues about what's going on and how to fix it.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 6, 2019 at 10:57 am
also have a look at using sp_updatestats
if your stats are out of date then you will have bad plans... but with only 61 thousand records i'm not banking on the fact that i'm correct
the only thing i can suggest is ... is it a stored proc? could it be parameter sniffing?
MVDBA
December 6, 2019 at 11:13 am
Could it be blocking? I know you said you're the only one doing anything, but could there be background processes or jobs that hold locks on the table you're querying?
John
December 6, 2019 at 4:57 pm
Thanks for all of the replies.
At the present time, there is no latency on the Query and everything's running fine. I'll check the execution plan the next time I see it (if I see it).
This is not a stored procedure, so no parameter issues.
It's a single-user situation so I don't think there were any locks in place for the table. It would only be accessed by my SQL client query window.
Anyway, I'll post again if it happens and the execution plan does not tell me anything.
December 9, 2019 at 9:40 am
if it's that small a recordset and you aren't using procs, then i'm guessing it's an access client? - if it is then I think I might know the issue
MVDBA
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply