The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

I know this sounds weird but this is a SELECT query and it only happens on the deployment site, I cannot reproduce it on my local machine.

First time I run the query against database, it takes 25 to 30secs and the second time ~13secs (±2secs), the third time 25secs again, so the cycle goes on. It's exactly the same query every time.

Do you have any idea how or why this could happen? Maybe because of disk fragmentation? Or for some reason the cache "invalidates" itself after the second run? (this was a wild one) Unfortunately I cannot expose the full query but its format is like this:

WITH SubQuery (...) as (SELECT ..., row_number() over (...) FROM ....) WHERE .... 
SELECT ... FROM SubQuery WHERE ...

Note: No other processes were interacting with the db at the time of test.

asked 19 Sep '11, 02:22

henginy's gravatar image

henginy
406151827
accept rate: 0%

edited 19 Sep '11, 02:31


You haven't posted enough details for us to help you. Some ideas:

  • Cache contents may be impacting elapsed time. The server does take the in-cache proportion of table pages into account during optimization, but if you are memory constrained then the proportion cached may be the "wrong" set of pages.
  • A selectivity estimate may be on the threshold of switching from one plan to another, and with each query execution the statistics may be updated that is resulting in hysteresis.

Attaching graphical plans with statistics for the problem query (for both cases) would be a good idea.

permanent link

answered 19 Sep '11, 07:55

Glenn%20Paulley's gravatar image

Glenn Paulley
10.7k568104
accept rate: 43%

Thank you for the answer and pointers. I'll gather more information in this direction and edit my post.

(19 Sep '11, 09:27) henginy

The computer was on the site and offline so it was not possible to collect further data. But looking at other different problems it was most probably due to harddisk or RAID-related.

(18 Jan '12, 10:06) henginy

How does one attach a graphical plan without exposing the query? ...I suppose one could manually edit the XML but that might not be sufficient to satisfy auditors etcetera.

(18 Jan '12, 11:26) Breck Carter
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×238
×95
×13

question asked: 19 Sep '11, 02:22

question was seen: 768 times

last updated: 18 Jan '12, 11:26