What could cause this query to increase the execution time from 10 seconds to 2h30?

LOliveira 2015-06-30 10:01:32

Hi,

Without any changes in the database schema or the query, the execution time went from 10 seconds to about 2h30. The table has 1.04 million rows.
Additional info: if executing a "SELECT *" against this table, it executed in around 15 seconds; if executing a "SELECT ColX", the execution time went up to 1 minute.

Updated the statistics, rebuilt the clustered PK, but no changes in the performance were seen.

The query was improved, and this version is no longer in use, but I still would like to know what was the cause of this huge performance degradation.

The server is a SQL Server 2008R2.

Thanks,
Luis

LOliveira 2015-06-30 10:06:24
Additional information: in Management Studio, there were PREEMPTIVE_OS_PIPEOPS waits
Aaron Bertrand 2015-06-30 14:08:50
Are you sure these waits are coming from the same SELECT that is taking a long time? You might need to generate a dump and contact support. This wait type is encountered when doing something outside of SQL Server – are you using filestream or filetable or something that would trigger invoking xp_cmdshell or other file system operations?
Aaron Bertrand 2015-06-30 14:04:33
10 seconds -> 2.5 hours doesn't sound like a query efficiency problem, it sounds like either blocking or a serious underlying resource issue.
LOliveira 2015-06-30 15:58:17
Hi Aaron,

Thanks for your comments.
After reading them, I went back to test this query and look closer to the outputs.

In fact:

  1. the wait I mentioned came from a different query, which is executing SSIS packages with "xp_cmdshell";
  2. this query is being executed by one of the packages, and it causes CXPACKET waits (e_WaitPipeGetRow). Two different nodes are visible in these waits (0 and 5).

Thanks,
Luis