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

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

avatar image By LOliveira 1 asked Jun 30, 2015 at 10:01 AM
more ▼
(comments are locked)
avatar image LOliveira Jun 30, 2015 at 10:06 AM

Additional information: in Management Studio, there were PREEMPTIVE_OS_PIPEOPS waits

avatar image Aaron Bertrand ♦ Jun 30, 2015 at 02:08 PM

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?

avatar image Aaron Bertrand ♦ Jun 30, 2015 at 02:04 PM

10 seconds -> 2.5 hours doesn't sound like a query efficiency problem, it sounds like either blocking or a serious underlying resource issue.

avatar image LOliveira Jun 30, 2015 at 03:58 PM

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

10|10000 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question

Topics:

x641
x166
x109

asked: Jun 30, 2015 at 10:01 AM

Seen: 1090 times

Last Updated: Jun 30, 2015 at 03:58 PM