MS SQL Server - Generation of estimated execution plan takes a long time

Author

Generation of estimated execution plan takes a long time

imarchenko

2005-10-25, 8:23 pm

Hello,

I am puzzled by following problem. One of our tables has more than 12
billion rows. Usually, query plan and query itself is executed almost
immediately but occasionally, it take about 2 minutes to generate estimated
query execution plan for following query:

SELECT MAX(qwEventIx) FROM FTE_STREAM_EVENT (NOLOCK)

There is a PK clustered index defined on qwEventIx field. I do not observer
any blocking on this object when it takes a long time to generate estimated
query plan. There is a plan cached in syscacheobjects for this query.
I was wondering if anyone can explain why it takes such a long time to
generate estimated query plan on some occasions.

Thanks,
Igor

Jerry Spivey

2005-10-25, 8:23 pm

Igor,

Are the statistics current and up-to-date?

HTH

Jerry
"imarchenko" < igormarchenko@hotmai
l.com> wrote in message
news:uIwoFzZ2FHA.4076@TK2MSFTNGP09.phx.gbl...
> Hello,
>
> I am puzzled by following problem. One of our tables has more than 12
> billion rows. Usually, query plan and query itself is executed almost
> immediately but occasionally, it take about 2 minutes to generate
> estimated query execution plan for following query:
>
> SELECT MAX(qwEventIx) FROM FTE_STREAM_EVENT (NOLOCK)
>
> There is a PK clustered index defined on qwEventIx field. I do not
> observer any blocking on this object when it takes a long time to
> generate estimated query plan. There is a plan cached in syscacheobjects
> for this query.
> I was wondering if anyone can explain why it takes such a long time to
> generate estimated query plan on some occasions.
>
> Thanks,
> Igor
>

Tibor Karaszi

2005-10-27, 7:27 am

It could be that this is when auto-update of statistics occurs. You can catch that in Profiler using
Object, Auto Stats.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
Blog: http:// solidqualitylearning
.com/blogs/tibor/

"imarchenko" < igormarchenko@hotmai
l.com> wrote in message
news:uIwoFzZ2FHA.4076@TK2MSFTNGP09.phx.gbl...
> Hello,
>
> I am puzzled by following problem. One of our tables has more than 12 billion rows. Usually, query
> plan and query itself is executed almost immediately but occasionally, it take about 2 minutes to
> generate estimated query execution plan for following query:
>
> SELECT MAX(qwEventIx) FROM FTE_STREAM_EVENT (NOLOCK)
>
> There is a PK clustered index defined on qwEventIx field. I do not observer any blocking on this
> object when it takes a long time to generate estimated query plan. There is a plan cached in
> syscacheobjects for this query.
> I was wondering if anyone can explain why it takes such a long time to generate estimated query
> plan on some occasions.
>
> Thanks,
> Igor
>

imarchenko

2005-10-28, 1:23 pm

Tibor,

You were absolutely correct! SQL Server was updating statistics on 12
billion rows table. It was taking over 2 minutes. I suppose it might make
sense to disable AUTOSTATS on this table with sp_autostats and update
statistics manually during off-peak hours. Would you recommend this
approach?

Thanks,
Igor

"Tibor Karaszi" <tibor_please.no. email_karaszi@hotmai
l.nomail.com> wrote in
message news:%2311fWFg2FHA.956@TK2MSFTNGP10.phx.gbl...
> It could be that this is when auto-update of statistics occurs. You can
> catch that in Profiler using Object, Auto Stats.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www. solidqualitylearning
.com/
> Blog: http:// solidqualitylearning
.com/blogs/tibor/
>
>
> "imarchenko" < igormarchenko@hotmai
l.com> wrote in message
> news:uIwoFzZ2FHA.4076@TK2MSFTNGP09.phx.gbl...
>

imarchenko

2005-10-28, 8:23 pm

Jerry,

It is indeed statistics related.

Thanks,
Igor

"Jerry Spivey" <jspivey@vestas-awt.com> wrote in message
news:uQmyqVa2FHA.2364@TK2MSFTNGP12.phx.gbl...
> Igor,
>
> Are the statistics current and up-to-date?
>
> HTH
>
> Jerry
> "imarchenko" < igormarchenko@hotmai
l.com> wrote in message
> news:uIwoFzZ2FHA.4076@TK2MSFTNGP09.phx.gbl...
>
>

Tibor Karaszi

2005-10-28, 8:23 pm

Yes, with that long auto.stats time, disabling autostats for that table is reasonable. Make sure you
schedule manual update statistics with a reasonable sample and at a good time (like not before some
big batch, but after :-) ).

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
Blog: http:// solidqualitylearning
.com/blogs/tibor/

"imarchenko" < igormarchenko@hotmai
l.com> wrote in message
news:e1KGDu%232FHA.3420@TK2MSFTNGP15.phx.gbl...
> Tibor,
>
> You were absolutely correct! SQL Server was updating statistics on 12 billion rows table. It
> was taking over 2 minutes. I suppose it might make sense to disable AUTOSTATS on this table with
> sp_autostats and update statistics manually during off-peak hours. Would you recommend this
> approach?
>
> Thanks,
> Igor
>
>
> "Tibor Karaszi" <tibor_please.no. email_karaszi@hotmai
l.nomail.com> wrote in message
> news:%2311fWFg2FHA.956@TK2MSFTNGP10.phx.gbl...
>
>

Sponsored Links