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

WithPDF_LinkGUIDCopy_Anonymized.pesession (4.5 kB)

By LOliveira 1 asked Jun 30, 2015 at 10:01 AM

(comments are locked)

LOliveira Jun 30, 2015 at 10:06 AM

Additional information: in Management Studio, there were PREEMPTIVE_OS_PIPEOPS waits

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?

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.

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:

the wait I mentioned came from a different query, which is executing SSIS packages with "xp_cmdshell";
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

0 answers:

Be the first one to answer this question

ask a question

Where to?

We are Moving!

Follow this question

Topics:

query plan x691
plan explorer pro upload x166
poor performance x116

asked: Jun 30, 2015 at 10:01 AM

Seen: 1133 times

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

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

Where to?

We are Moving!

Follow this question

Related Questions