locked
Performance issue generating execution plan RRS feed

  • Question

  • I've been looking at some recurring performance problems on a relatively simple query on a SQL 2005 server, and found that the execution plan could take up to 4 minutes to generate, with a large number of pageiolatch_sh waits, which doesn't seem right to me. The query is of the form:

    Select * from view where field1 = '{<Guid Value>}', field2 = <Int Value>, field3 = <Int Value>, description like '%<Some Text>%'

    All search fields are in the same base table. There are non-clustered indexes on field1, field2 and field3. Field2 and field3 are not very selective (~10% and ~5% possible values respectively), but field1 is much more highly selective (returning at most ~0.1% of data on an equality match). The description field is nvarchar(max) and is not indexed, nor does it have statistics. Statistics exist for all indexes (15 on the table); they have not been updated for a couple of months, but the data distribution won't have changed significantly in that time. The are around 150 000 records in the table, with 140 MB index space, and 4 GB data space

    The view joins to 4 other tables, but only on their respective primary keys (all clustered indexes). The plan produced is clearly appropriate (a seek using the index on field1, nested loops for the joins), but I can't see the reason for the excess time and IO activity (processor utilisation is not high) when generating the plan.

    Any ideas what could cause the IO activity ? My best guess is that it may be ignoring or regenerating the statistics - is there any way I can tell if that's the case ?
    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk
    • Moved by Tom Phillips Monday, August 10, 2009 1:48 PM (From:SQL Server Database Engine)
    Monday, August 10, 2009 11:37 AM

Answers


  • Do you know if AUTO_UPDATE_STATISTICS_ASYNC applies to creation of statistics, or just the update ?

    Good question. I think your take on the documentation is correct, as that is my interpretation of it too. I do not know for sure if this is correct though so perhaps a fellow poster can confirm.

    Once the initial creation of the naughty statistic is done however, it should from then on no longer hold off query execution (if an update to the stat was required) provided the database property AUTO_UPDATE_STATISTICS_ASYNC ON was in play. You could perhaps create the statistic manually yourself ahead of time in order to avoid any delay?



    John Sansom | Calling out all SQL Server Experts, be sure to take part in the SQL Server Guru Challenge
    Monday, August 10, 2009 1:12 PM

All replies

  • I think I've partly answered my question, in that it looks like statistics where created/updated on the description field when the execution plan was generated, which is probably the reason for the time the query took - I hadn't expected SQL to create statistics on a field when using the like operator starting with a wildcard, but it looks like this is one of the 'improvements' in SQL 2005, as is allowing statistics on nvarchar(max) fields.

    So, assuming the above analysis is correct, is there an effective way to stop SQL Server creating statistics on a field when I know that the statistics are not useful, and can cause such an excessive overhead on generation ? I could turn off Auto_Create_Statistics at the database level, but I'd prefer to keep it on for all other tables.


    Microsoft CRM MVP - http://mscrmuk.blogspot.com  http://www.excitation.co.uk
    Monday, August 10, 2009 12:23 PM
  • Hi,

    Perhaps it would be benificial to set the database option Auto Update Statistics Asynchronously to true?

    This way when an execution plan requests for statistics to be updated, the plan generation does not need to wait for the stats to be generated but can continue by using the already existing stats.

    See the following reference for further details.

    http://msdn.microsoft.com/en-us/library/ms188124.aspx
    John Sansom | Calling out all SQL Server Experts, be sure to take part in the SQL Server Guru Challenge
    Monday, August 10, 2009 12:35 PM
  • Consider turning on the database AUTO_UPDATE_STATISTICS_ASYNC option so the query doesn't wait while stats are updated asynchronously:
     
    ALTER DATABASE foo
    SET AUTO_UPDATE_STATISTICS_ASYNC ON;

    --
    Hope this helps.
     
    Dan Guzman
    SQL Server MVP
    http://weblogs.sqlteam.com/dang /
    Monday, August 10, 2009 12:42 PM
  • John / Dan

    Thanks for the speedy replies. At the moment it seems like my best option is to have
    AUTO_CREATE_STATISTICS OFF
    AUTO_UPDATE_STATISTICS ON
    AUTO_UPDATE_STATISTICS_ASYNC ON

    Do you know if AUTO_UPDATE_STATISTICS_ASYNC applies to creation of statistics, or just the update ? My interpetation of the Books Online documentation is that it only applies to updates, but it would be helpful to be sure.

    In this specific case it looked like the query optimiser was trying to create statistics, as there weren't any, but was taking so long that the client application timeout was exceeded, so the query was cancelled, and so the statistics weren't created, and then the cycle repeated the next time someone tried. As I see it, the AUTO_UPDATE_STATISTICS_ASYNC option would only help if it applied to statistic creation, and was as updates
    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk
    Monday, August 10, 2009 12:48 PM

  • Do you know if AUTO_UPDATE_STATISTICS_ASYNC applies to creation of statistics, or just the update ?

    Good question. I think your take on the documentation is correct, as that is my interpretation of it too. I do not know for sure if this is correct though so perhaps a fellow poster can confirm.

    Once the initial creation of the naughty statistic is done however, it should from then on no longer hold off query execution (if an update to the stat was required) provided the database property AUTO_UPDATE_STATISTICS_ASYNC ON was in play. You could perhaps create the statistic manually yourself ahead of time in order to avoid any delay?



    John Sansom | Calling out all SQL Server Experts, be sure to take part in the SQL Server Guru Challenge
    Monday, August 10, 2009 1:12 PM