Operand type clash - usp_sqlwatch_logger_index_histogram

[DiHo78]

Did you check DOCS to make sure there is no workaround? https://sqlwatch.io/docs/

Describe the bug The job SQLWATCH-LOGGER-INDEXES fails with the last step SQLWATCH-LOGGER-INDEXES When executing the procedure usp_sqlwatch_logger_index_histogram manually the error becomes quite clear: Operand type clash: timestamp is incompatible with sql_variant

From my observation this happens when there is an index on a timestamp column. The error will occur even before writing to the destination tables. The proc fails when writing to the temp table #stats

To Reproduce Steps to reproduce the behavior:

1. create a dummy database and table:

   
   create database sqlwatchplayground;
   USE [sqlwatchplayground]
   GO

/** Object: Table [dbo].[dummytable] Script Date: 11/21/2019 9:09:32 PM **/ SET ANSI_NULLS ON GO

SET QUOTED_IDENTIFIER ON GO

CREATE TABLE [dbo].[dummytable]( [test] varchar NULL, [test2] [int] NOT NULL, [test3] [timestamp] NULL ) ON [PRIMARY] GO

/** Object: Index [NonClusteredIndex-20191121-210914] Script Date: 11/21/2019 9:09:40 PM **/ CREATE NONCLUSTERED INDEX [NonClusteredIndex-20191121-210914] ON [dbo].[dummytable] ( [test3] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO

2. Click this..
Run the job SQLWATCH-LOGGER-INDEXES or the procs in appropriate order

3. To dig deeper into the error, just run the code snippet from proc  (already modified to hit the right DB):
```SQL
use SQLWATCH;

set xact_abort on
set nocount on
begin tran
declare @snapshot_type tinyint = 14
declare @database_name sysname
declare @sql varchar(max)
declare @object_id int
declare @index_name sysname
declare @index_id int
declare @object_name nvarchar(256)
declare @sqlwatch_database_id smallint
declare @sqlwatch_table_id int
declare @sqlwatch_index_id int
declare @indextype as table (
    is_index_hierarchical bit
)
create table #stats_hierarchical (
    [database_name] sysname default 'fe92qw0fa_dummy',
    [object_name] sysname default 'fe92qw0fa_dummy',
    index_name sysname default 'fe92qw0fa_dummy',
    index_id int,
    RANGE_HI_KEY hierarchyid,
    RANGE_ROWS real,
    EQ_ROWS real,
    DISTINCT_RANGE_ROWS real,
    AVG_RANGE_ROWS real,
    [collection_time] datetime,
    [sqlwatch_database_id] smallint,
    [sqlwatch_table_id] int,
    [sqlwatch_index_id] int
)
create table #stats (
    [database_name] sysname default 'fe92qw0fa_dummy',
    [object_name] sysname default 'fe92qw0fa_dummy',
    index_name sysname default 'fe92qw0fa_dummy',
    index_id int,
    RANGE_HI_KEY sql_variant,
    RANGE_ROWS real,
    EQ_ROWS real,
    DISTINCT_RANGE_ROWS real,
    AVG_RANGE_ROWS real,
    [collection_time] datetime,
    [sqlwatch_database_id] smallint,
    [sqlwatch_table_id] int,
    [sqlwatch_index_id] int
)
declare @is_index_hierarchical bit
set @snapshot_type = 15
declare c_index cursor for
select md.[database_name], table_name=mt.table_name , index_name = mi.index_name, mi.index_id, mi.sqlwatch_database_id, mi.sqlwatch_table_id, mi.sqlwatch_index_id
from [dbo].[sqlwatch_meta_index] mi
    inner join [dbo].[sqlwatch_meta_table] mt
        on mt.sqlwatch_database_id = mi.sqlwatch_database_id
        and mt.sql_instance = mi.sql_instance
        and mt.sqlwatch_table_id = mi.sqlwatch_table_id
    inner join [dbo].[sqlwatch_meta_database] md
        on md.sql_instance = mi.sql_instance
        and md.sqlwatch_database_id = mi.sqlwatch_database_id
    inner join sys.databases sdb
        on sdb.name = md.database_name collate database_default
        and sdb.create_date = md.database_create_date

    --begin hadr aware and db online
       left join sys.dm_hadr_availability_replica_states hars
            on sdb.replica_id = hars.replica_id
       left join sys.availability_replicas ar
            on sdb.replica_id = ar.replica_id
    --end hadr aware and db online ?
where mi.[sql_instance] = @@SERVERNAME
and mi.date_deleted is null
--begin hadr aware and db online ?
--and database_id > 4
--DEBUG
and state_desc = 'ONLINE'
and (  
        (hars.role_desc = 'PRIMARY' or hars.role_desc is null)
     or (hars.role_desc = 'SECONDARY' and ar.secondary_role_allow_connections_desc IN ('READ_ONLY','ALL'))
     )
and [name]  like '%playground%'
--and [table_name] like '%_sim_feature_license%'
--end hadr aware and db online ?

open c_index
fetch next from c_index
into @database_name, @object_name, @index_name, @index_id, @sqlwatch_database_id, @sqlwatch_table_id, @sqlwatch_index_id
while @@FETCH_STATUS = 0
    begin
        delete from @indextype
        set @sql = 'use [' + @database_name + '];
            select case when tp.name = ''hierarchyid'' then 1 else 0 end
            from sys.schemas s
            inner join sys.tables t
                on s.schema_id = t.schema_id
            inner join sys.indexes i
                on i.object_id = t.object_id
            inner join sys.index_columns ic
                on ic.index_id = i.index_id
                and ic.object_id = i.object_id
                /* only the leading column is used to build histogram
                   https://dba.stackexchange.com/a/182250 */
                and ic.index_column_id = 1
            inner join sys.columns c
                on c.column_id = ic.column_id
                and c.object_id = ic.object_id
            inner join sys.types tp
                on tp.system_type_id = c.system_type_id
                and tp.user_type_id = c.user_type_id
            where i.name = ''' + @index_name + '''
            and s.name + ''.'' + t.name = ''' + @object_name + ''''
        --print (@sql)
        insert into @indextype(is_index_hierarchical)
        exec (@sql)
        select @is_index_hierarchical = is_index_hierarchical from @indextype
        set @is_index_hierarchical  = isnull(@is_index_hierarchical ,0)

        --set @object_name = object_schema_name(@object_id) + '.' + object_name(@object_id)
        set @sql = 'use [' + @database_name + '];
--extra check if the table and index still exist. since we are collecting histogram for indexes already collected in sqlwatch,
--there could be a situation where index was deleted from Sql Server before SQLWATCH was upated and the below would have thrown an error.
if exists (
        select *
        from sys.indexes
        where object_id = object_id(''' + @object_name + ''')
        and name=''' + @index_name + ''')
    begin
        dbcc show_statistics (''' + @object_name + ''',''' + @index_name + ''') with  HISTOGRAM
        Print ''['' + convert(varchar(23),getdate(),121) + ''] Collecting index histogram for index: ' + @index_name + '''
    end'
    print @is_index_hierarchical
    print (@sql)
        if @is_index_hierarchical = 1
            begin
                insert into #stats_hierarchical (RANGE_HI_KEY,RANGE_ROWS,EQ_ROWS,DISTINCT_RANGE_ROWS,AVG_RANGE_ROWS)
                exec (@sql)

                update #stats_hierarchical
                    set [database_name] = @database_name
                        , [object_name] = @object_name
                        , index_name = @index_name
                        , index_id = @index_id
                        , [collection_time] = getutcdate()
                        , [sqlwatch_database_id] = @sqlwatch_database_id
                        , [sqlwatch_table_id] = @sqlwatch_table_id
                        , [sqlwatch_index_id] = @sqlwatch_index_id
                where index_name = 'fe92qw0fa_dummy'
            end
        else
            begin
                insert into #stats (RANGE_HI_KEY,RANGE_ROWS,EQ_ROWS,DISTINCT_RANGE_ROWS,AVG_RANGE_ROWS)
                exec (@sql)
                update #stats
                    set [database_name] = @database_name
                        , [object_name] = @object_name
                        , index_name = @index_name
                        , index_id = @index_id
                        , [collection_time] = getutcdate()
                        , [sqlwatch_database_id] = @sqlwatch_database_id
                        , [sqlwatch_table_id] = @sqlwatch_table_id
                        , [sqlwatch_index_id] = @sqlwatch_index_id
                where index_name = 'fe92qw0fa_dummy'
            end
            select * from #stats
select * from #stats_hierarchical
        fetch next from c_index
        into @database_name, @object_name, @index_name, @index_id, @sqlwatch_database_id, @sqlwatch_table_id, @sqlwatch_index_id
    end
close c_index
deallocate c_index
select * from #stats
select * from #stats_hierarchical
/*
    declare @snapshot_time datetime = getutcdate();
    insert into [dbo].[sqlwatch_logger_snapshot_header] (snapshot_time, snapshot_type_id)
    values (@snapshot_time, @snapshot_type)
    insert into [dbo].[sqlwatch_logger_index_usage_stats_histogram](
            [sqlwatch_database_id], [sqlwatch_table_id], [sqlwatch_index_id],
        RANGE_HI_KEY, RANGE_ROWS, EQ_ROWS, DISTINCT_RANGE_ROWS, AVG_RANGE_ROWS,
        [snapshot_time], [snapshot_type_id], [collection_time])
    select
        st.[sqlwatch_database_id],
        st.[sqlwatch_table_id],
        st.[sqlwatch_index_id],
        convert(nvarchar(max),st.RANGE_HI_KEY),
        RANGE_ROWS = convert(real,st.RANGE_ROWS),
        EQ_ROWS = convert(real,st.EQ_ROWS),
        DISTINCT_RANGE_ROWS = convert(real,st.DISTINCT_RANGE_ROWS),
        AVG_RANGE_ROWS = convert(real,st.AVG_RANGE_ROWS),
        [snapshot_time] = @snapshot_time,
        [snapshot_type_id] = @snapshot_type,
        [collection_time]
    from #stats st
    insert into [dbo].[sqlwatch_logger_index_usage_stats_histogram](
            [sqlwatch_database_id], [sqlwatch_table_id], [sqlwatch_index_id],
        RANGE_HI_KEY, RANGE_ROWS, EQ_ROWS, DISTINCT_RANGE_ROWS, AVG_RANGE_ROWS,
        [snapshot_time], [snapshot_type_id], [collection_time])
    select
        st.[sqlwatch_database_id],
        st.[sqlwatch_table_id],
        st.[sqlwatch_index_id],
        convert(nvarchar(max),st.RANGE_HI_KEY),
        RANGE_ROWS = convert(real,st.RANGE_ROWS),
        EQ_ROWS = convert(real,st.EQ_ROWS),
        DISTINCT_RANGE_ROWS = convert(real,st.DISTINCT_RANGE_ROWS),
        AVG_RANGE_ROWS = convert(real,st.AVG_RANGE_ROWS),
        [snapshot_time] = @snapshot_time,
        [snapshot_type_id] = @snapshot_type,
        [collection_time]
    from #stats_hierarchical st
    */
commit tran

Expected behavior Procedure should run without any problem

Screenshots If applicable, add screenshots to help explain your problem.

Windows Server (please complete the following information):

• OS Version: [e.g. Windows 2012 R2]

SQL Server (please complete the following information):

• SQL Version: [SQL Server 2016 SP1]
• SQL Edition: [Enterprise]

SQL Server Management Studio (SSMS -> about -> copy info):

• paste "about" info here:

SQLWATCH version (from DACPAC or from sysinstances)

• latest beta release installed via dbatools -prerelease

[DiHo78]

As I've said on twitter: it might be an edge case. I stumbled across this behaviour when gathering the idx information for a 3rd party database (Blackberry UEM). 1st time I have come across an index on a timestamp column.

[DiHo78]

Ah... I think now I know a possible solution for this. You mentioned on twitter "it will need another explicit conversion same as hierarchical index"

So, creating a new section in the proc with a temp table #stats_timestamp. This time, for Range_High_key datatype timestamp will be set. Also a check if we have the data type timestamp. And in the last part of the proc there will be a conversion again...

I am not a TSQL programmer, just a little DBA, so I still have to think about why you're using sql_variant instead of varchar for the temp table and do the converation afterwards for the target table. Maybe I get an idea overnight :)

That was my quick and dirty way to get it work:

create table #stats (
    [database_name] sysname default 'fe92qw0fa_dummy',
    [object_name] sysname default 'fe92qw0fa_dummy',
    index_name sysname default 'fe92qw0fa_dummy',
    index_id int,
    RANGE_HI_KEY varchar(max),
    RANGE_ROWS real,
    EQ_ROWS real,
    DISTINCT_RANGE_ROWS real,
    AVG_RANGE_ROWS real,
    [collection_time] datetime,
    [sqlwatch_database_id] smallint,
    [sqlwatch_table_id] int,
    [sqlwatch_index_id] int
)

Otherwise I can try to find the time tomorrow and extend the proc in the same manner as for hierarchyid.

[DiHo78]

Was too excited to give it a try: https://github.com/DiHo78/sqlwatch/blob/2_2_beta/SQLWATCHDB/dbo/Procedures/usp_sqlwatch_logger_index_histogram.sql

Just tested the code on one SQL 2016 Will do some more tests tomorrow.

[marcingminski]

Your assumption is correct, we need to proceed same way as with the hierarchical indexes and do explicit conversion to varchar.

Btw, Index on timestamp column is unusual. As you say, Timestamp has nothing to do with date and is a binary type that changes every time the row changes in order for the application to make sure the row has not been modified since it was pulled from the database. You'd likely never search by timestamp, always by PK (assuming the application is modifying the data). Once the application has pulled the row, it will pull it again before saving and if timestamp is different throw an error back to the user. Alternativey it will do something like udpate table x set field y = 1 where PK = ID and timestamp = @timestamp and throw error if no rows modified. I can't think of a scenario where index on a timestamp column would be useful and it will incur quite some update penalty. But then I do not know everything so...

[marcingminski]

In fact, just had a look at your fix RANGE_HI_KEY datetime,, did you mean timestamp as in rowversion https://docs.microsoft.com/en-us/sql/t-sql/data-types/rowversion-transact-sql?view=sql-server-ver15 or an actual datetime timestamp?

if the latter ignore second part of my comment above and if the first, I don't think this fix will work as timestamp is varbinary I'd say the RANGE_HI_KEY will have to be varchar(max) and then convert(varchar(max),[timestamp_column])

I am actually thinking to scrap the sql_variant and only do hierarchy and varchar. that would perhaps solve your issue.

[DiHo78]

I can post the create table and idx statements of the affected object tomorrow. Since there is no data at all in the table and because it is 3rd Party, I cannot really tell. Due to the name of the indexed column I suppose the vendor created a db model which can also run under Oracle. Can't remember the name of the column right now, but it was something like ora_something...

So,regarding my fix range_hi_key datetime: maybe we go for varchar(max)?

[DiHo78]

Too late for me Did not recognize the last part of your comment regarding varchar.... :) because I replied via email and in the meantime there was an edit from your side...

Using varchar would be the easiest I suppose.

[marcingminski]

Let me play and test it, ill get back to you soon.

On Thu, 21 Nov 2019 at 23:17, Dirk Hondong notifications@github.com wrote:

I can post the create table and idx statements of the affected object tomorrow. Since there is no data at all in the table and because it is 3rd Party, I cannot really tell. Due to the name of the indexed column I suppose the vendor created a db model which can also run under Oracle. Can't remember the name of the column right now, but it was something like ora_something...

So,regarding my fix range_hi_key datetime: maybe we go for varchar(max)?

Am 22. November 2019 00:01:27 MEZ schrieb Marcin Gminski < notifications@github.com>:

In fact, just had a look at your fix RANGE_HI_KEY datetime,, did you mean timestamp as in rowversion

https://docs.microsoft.com/en-us/sql/t-sql/data-types/rowversion-transact-sql?view=sql-server-ver15 or an actual datetime timestamp?

if the latter ignore second part of my comment above :)

-- You are receiving this because you authored the thread. Reply to this email directly or view it on GitHub:

https://github.com/marcingminski/sqlwatch/issues/119#issuecomment-557311291

-- Diese Nachricht wurde von meinem Kindle Fire mit K-9 Mail gesendet.

— You are receiving this because you were assigned. Reply to this email directly, view it on GitHub https://github.com/marcingminski/sqlwatch/issues/119?email_source=notifications&email_token=AB7HQTJTGJZPXJ4RCIAIN43QU4JKDA5CNFSM4JQIBN4KYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEE37NZQ#issuecomment-557315814, or unsubscribe https://github.com/notifications/unsubscribe-auth/AB7HQTJUWLCCJPRYVZSEQW3QU4JKDANCNFSM4JQIBN4A .

[marcingminski]

:)

On Thu, 21 Nov 2019 at 23:42, Dirk Hondong notifications@github.com wrote:

Too late for me Did not recognize the last part of your comment regarding varchar.... :) because I replied via email and in the meantime there was an edit from your side...

Using varchar would be the easiest I suppose.

— You are receiving this because you were assigned.

Reply to this email directly, view it on GitHub https://github.com/marcingminski/sqlwatch/issues/119?email_source=notifications&email_token=AB7HQTKFMXNOQCIWG3LIA53QU4MF7A5CNFSM4JQIBN4KYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEE4BCOQ#issuecomment-557322554, or unsubscribe https://github.com/notifications/unsubscribe-auth/AB7HQTND62BBALW3C7EKIH3QU4MF7ANCNFSM4JQIBN4A .

[DiHo78]

Hi Marcin, just to come back to our discussion. Here is the create table statement of the affected table:

/****** Object:  Table [dbo].[obj_sim_feature_license]    Script Date: 22.11.2019 11:38:50 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[obj_sim_feature_license](
    [id_sim_feature_license] [int] IDENTITY(1,1) NOT NULL,
    [id_user_device] [int] NOT NULL,
    [individual_liable] [bit] NOT NULL,
    [corporate_liable] [bit] NOT NULL,
    [base_universal] [bit] NOT NULL,
    [base_third_party] [bit] NOT NULL,
    [secure_container] [bit] NOT NULL,
    [ora_rowscn] [timestamp] NOT NULL,
 CONSTRAINT [PK_sim_feature_license] PRIMARY KEY CLUSTERED 
(
    [id_sim_feature_license] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [AK_sim_feature_license] UNIQUE NONCLUSTERED 
(
    [id_user_device] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[obj_sim_feature_license] ADD  CONSTRAINT [DF_objSimFeatLic_ind_liab]  DEFAULT ((0)) FOR [individual_liable]
GO

ALTER TABLE [dbo].[obj_sim_feature_license] ADD  CONSTRAINT [DF_objSimFeatLic_corp_liab]  DEFAULT ((0)) FOR [corporate_liable]
GO

ALTER TABLE [dbo].[obj_sim_feature_license] ADD  CONSTRAINT [DF_objSimFeatLic_base_univ]  DEFAULT ((0)) FOR [base_universal]
GO

ALTER TABLE [dbo].[obj_sim_feature_license] ADD  CONSTRAINT [DF_objSimFeatLic_base_3rdparty]  DEFAULT ((0)) FOR [base_third_party]
GO

ALTER TABLE [dbo].[obj_sim_feature_license] ADD  CONSTRAINT [DF_objSimFeatLic_sec_cont]  DEFAULT ((0)) FOR [secure_container]
GO

ALTER TABLE [dbo].[obj_sim_feature_license]  WITH CHECK ADD  CONSTRAINT [FK_sim_feat_lic_devic_id_devce] FOREIGN KEY([id_user_device])
REFERENCES [dbo].[obj_user_device] ([id_user_device])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[obj_sim_feature_license] CHECK CONSTRAINT [FK_sim_feat_lic_devic_id_devce]
GO

and the index:

/****** Object:  Index [IX_obj_sim_feature_license_ora]    Script Date: 22.11.2019 11:40:07 ******/
CREATE NONCLUSTERED INDEX [IX_obj_sim_feature_license_ora] ON [dbo].[obj_sim_feature_license]
(
    [ora_rowscn] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

The name of the colum is ora_rowscn And if you search for this term, you'll find https://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns007.htm

So maybe the vendor just wanted to be prepared for multi platform support and yes, then it is more like a rowversion thing.

BTW: I've checked this DB 1st and all Tables with timestamp columns are emtpy at all... (weird).

Best regards Dirk