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?
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;
TechNet Community Support
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?