none
Excessive query compile time through automated statistics creation on filestream columns

    Question

  • Hello all,

    I'm currently having a major issue with a couple of queries on a SQL Server 2012 running against the following table:

    CREATE TABLE [dbo].[Measurements] ( [SqlId] INT IDENTITY (1, 1) NOT NULL, [Id] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL, [QIec] FLOAT (53) NOT NULL, [QIecMax] FLOAT (53) NOT NULL, ... [Prpd] VARBINARY (MAX) FILESTREAM NULL )

    In my current sample the table has roughly 100.000 entries and a simple query like 

    SELECT COUNT(*) FROM dbo.Measurements WHERE Prpd IS NOT NULL

    will take a couple of minutes to execute. Actually its the query's compile time that is taking so long, more precise, SQL Server starts to create statistics for the Prpd column.

    Once the statistics are created, the queries runs fine.

    Unfortunately user defined statistics on filestream columns are not supported, you will just get:

    Msg 1978, Level 16, State 1, Line 1
    Column 'Prpd' in table 'dbo.Measurements' is of a type that is invalid for use as a key column in an index or statistics.
    

    Also tried a persisted computed column [HasPrpd], but it gave the same results.

    Any one seen this before and knows how to handle this?

    Thx,

    Micha

    Thursday, June 27, 2013 3:32 PM

All replies

  • Hi Micha,

    I suggest adding another column “[Flag]” as bit, and then add a filtered index on that column.

    alter table dbo.Measurements 
       add [Flag] bit
    
    
    create index idx_nc_Flag
    on dbo.Measurements([Flag])
    where [Flag] is not null;
    

    With the current data row, we need to update the Flag column according to the [Prpd] column. This steps will cause some time, so please do this during off-peak hours.

    update dbo.Measurements 
    set [Flag] = 1
    where [Prpd] is not null;
    

    With the new result, we can update the Flag to 1 if [Prpd] is not null, and we can use the following codes to find the not null [Prpd] column account:

    select count(*) from dbo.Measurements
       where [Flag] is not null;
    

    If you have any feedback on our support, please click here.



    Allen Li
    TechNet Community Support

    Saturday, June 29, 2013 2:46 AM
    Moderator
  • Hi Allen,

    thought about this too, but I wouldn't like to have dependent columns like that actually. Furthermore this is a 24/7 monitoring system with no real off-peak hours.

    - Do you think it would be feasible to update your suggested [Flag] column through a trigger?

    - Since SQL Server is obviously able to create statistics on his own, isn't there a way of creating them manually and would that be an option?

    Regards,

    Micha

    Tuesday, July 02, 2013 6:57 AM