SQL Query Suddenly Slow – How to Test Network or Hardware Issues

sql server

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?

Best Answer

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.

Related Solutions

Sql-server – Slow SQL Server query using a DB2 linked server in virtual environment

Thanks to Jon Seigel, it seems like the issue in part due to the CPU affinity setting in SQL.

We deployed this VM with SQL from an image which causes a known issue as listed here (scenario #3):

IBM - Slow performance in Controller due to inability to use available CPU cores in SQL server

Removing the automatic affinity option and setting the affinity mask option to use all the CPUs available resolved the issue.

Affinity Mask Option

sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'affinity mask', 15;
RECONFIGURE;
GO
sp_configure 'show advanced options', 0;
RECONFIGURE;
GO

sql server affinity GUI

We have updated our image to include this option. Thanks everyone for the help in this issue.

Please note, this option is deprecated in SQL Server 2012 and is not recommended for use. Additionally, it is related to processor thread handling in Windows 2000 and Windows 2003 operating systems.

Affinity Mask Option - SQL Server 2012

SQL Server – Troubleshooting Slow SQL Queries

When a query that used to run fast suddenly starts running slowly in the middle of the night and nothing else is affected except for this one query, how do I troubleshoot it...?

You can start by checking if the execution plan is still in the cache. Check sys.dm_exec_query_stats, sys.dm_exec_procedure_stats and sys.dm_exec_cached_plans. If the bad execution plan is still cached you can analyze it, and you can also check the execution stats. The execution stats will contain information as logical reads, CPU time and execution time. These can give strong indications what the problem is (eg. large scan vs. blocking). See Identifying problem queries for an explanation how to interpret the data.

Also, this is not a problem with parameter sniffing. I've seen that before, and this is not it, since even when I hard-code the varaibles in SSMS, I still get slow performance.

I'm not convinced. Hard-coding variables in SSMS does not prove that the past bad execution plan was not compiled against a skewed input. Please read Parameter Sniffing, Embedding, and the RECOMPILE Options for a very good article on the topic. Slow in the Application, Fast in SSMS? Understanding Performance Mysteries is another excellent reference.

I've concluded (perhaps incorrectly) from these little experiments that the reason for the slow-down is due to how SQL's cached execution plan is set up -- when the query is a little different, it has to create a new execution plan.

This can be easily tested. SET STATISTICS TIME ON will show you the compile vs. execution time. SQL Server:Statistics performance counters will also reveal whether compilation is an issue (frankly, I find it unlikely).

However, there is something similar that you may hit: the query grant gate. Read Understanding SQL server memory grant for details. If your query requests a large grant at a moment no memory is available, it will have to wait, and it will all look as 'slow execution' to the application. Analyzing wait info stats will reveal if this is the case.

For a more general discussion about what to measure and what to look for, see How to analyse SQL Server performance

Best Answer

Related Solutions

Sql-server – Slow SQL Server query using a DB2 linked server in virtual environment

SQL Server – Troubleshooting Slow SQL Queries

Related Question