0

I'm not a general IT expert. Sometimes with SQL queries ... they will take 10 seconds about 95% of the time.

Every so often, the exact same query will take 5-10 minutes. As will others. The constant is the host computer and current time --- not the query itself.

I'm just wondering if there's any way for me to test or verify what the problem is that is slowing down the query (and that it is, indeed, slowing the query). Would I remote into the host machine and check the memory usage/ processes? The host machine is also in a foreign country --- USA to Germany. Again, 95% of the time, this doesn't matter. I'm wondering if there are temporary 'network spike' issues. I want to know so I can actually complain to IT there ... but I want to pinpoint exactly what's causing the query issues. Maybe this is out of scope of this board ... I know a showplan wouldn't really indicate anything ... or would it? Would network or memory issues slow all steps the same duration? Is there an easier way to troubleshoot this?

|improve this question
  • Did you compare the plan between 10 second runtime and 5-10 minute runtime? Did you look at the compiled vs runtime value for the parameters during both run? – SqlWorldWide Mar 7 '18 at 1:48
  • You should look at the sys.dm_os_waiting_tasks to find out waits of your spid. You'll clearly see if it's network, locks, IO or smth else. If you just want to understand if it's network issue instead of pulling the result to the client, make your select to local variables, i.e. instead of doing "SELECT col1, col2,... from..." declare appropriate variables and rewrite your select this way: SELECT @var1 = col1, @var2 = col2... this way nothing goes to client, all happens on the server – sepupic Mar 7 '18 at 7:53
  • Which version of SQL Server? – gareth Mar 7 '18 at 15:16
1

There is not just a DBA but also a SysAdmin question. Although, there are some basic DBA tools you should have in use to help you out.

  • Slow query monitoring: most databases have tools that allow you to monitor SERVER SIDE for slow queries. Your system could be indexing, re-allocating memory and a myriad of other server side functions that could interfere with the speed of your queries. Slow query monitoring will help you start drilling down and isolate.
  • Basic logging: Make sure your server date is correlated with your local time (as in Germany +8 from US PST) to make it easy to look up your logs. I often just (I use Linux based systems) run a tail -f on appropriate logs until the slow queries surface or note times of slow queries and then run through the logs.
  • Basic network network monitoring: Setup a constant ping to your server(there are also plenty of bandwidth monitoring tools) that can be setup to alert you for slow traffic between two points as well as longer ping times.

There are different indicators based on your operating system that will show memory issues such as page faults. Memtester for Linux will stress test your memory and all of it when you direct it to. Memory issues can be sporadic as not all memory is used at all times so access to bad memory can be intermittent.

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.