SQLPackage Timeout on SQL Server 2014 with TF 4199

hi all,

since have migrated sql 2014 sp1, figured out our application experiencing cardinality estimator bug.(see link)

after applying tf, sqlpackage experiencing timeout on query:

select * (  select           schema_name([o].[schema_id])    [schemaname],          [si].[object_id]                [columnsourceid],          [o].[name]                      [columnsourcename],          [o].[type]                      [columnsourcetype],          [ic].[column_id]                [columnid],          [c].[name]                      [columnname],          [si].[index_id]                 [indexid],          [si].[name]                     [indexname],          [ds].[type]                     [dataspacetype],          [ds].[data_space_id]            [dataspaceid],          [ds].[name]                     [dataspacename],          [si].[fill_factor]              [fillfactor],          [si].[is_padded]                [ispadded],          [si].[is_disabled]              [isdisabled],          [si].[allow_page_locks]         [doallowpagelocks],          [si].[allow_row_locks]          [doallowrowlocks],          [sit].[cells_per_object]        [cellsperobject],          [sit].[bounding_box_xmin]       [xmin],          [sit].[bounding_box_xmax]       [xmax],          [sit].[bounding_box_ymin]       [ymin],          [sit].[bounding_box_ymax]       [ymax],          [sit].[level_1_grid]            [level1grid],          [sit].[level_2_grid]            [level2grid],          [sit].[level_3_grid]            [level3grid],          [sit].[level_4_grid]            [level4grid],          [sit].[tessellation_scheme]     [tessellationscheme],          [s].[no_recompute]              [norecomputestatistics],          [p].[data_compression]          [datacompressionid],          convert(bit, case when [ti].[data_space_id] = [ds].[data_space_id] 1 else 0 end)                                          [equalsparentdataspace]           [sys].[spatial_indexes]          [si] (nolock)          inner join [sys].[objects]       [o] (nolock) on [si].[object_id] = [o].[object_id]          inner join [sys].[spatial_index_tessellations] [sit] (nolock) on [si].[object_id] = [sit].[object_id] , [si].[index_id] = [sit].[index_id]          inner join [sys].[data_spaces]   [ds] (nolock) on [ds].[data_space_id] = [si].[data_space_id]           inner join [sys].[index_columns] [ic] (nolock) on [si].[object_id] = [ic].[object_id] , [si].[index_id] = [ic].[index_id]          inner join [sys].[columns]       [c] (nolock) on [si].[object_id] = [c].[object_id] , [ic].[column_id] = [c].[column_id]          inner join [sys].[objects]       [o2] (nolock) on [o2].[parent_object_id] = [si].[object_id]          inner join [sys].[stats]         [s] (nolock) on [o2].[object_id] = [s].[object_id] , [s].[name] = [si].[name]          inner join [sys].[partitions]    [p] (nolock) on [p].[object_id] = [o2].[object_id] , [p].[partition_number] = 1          left  join [sys].[indexes]       [ti] (nolock) on [o].[object_id] = [ti].[object_id]          left join [sys].[tables]         [t] (nolock) on [t].[object_id] = [si].[object_id]  [si].[is_hypothetical] = 0          , [ti].[index_id] < 2          , objectproperty([o].[object_id], n'issystemtable') = 0          , ([t].[is_filetable] = 0 or [t].[is_filetable] null)          , ([o].[is_ms_shipped] = 0 , not exists (select *                                          [sys].[extended_properties]                                              [major_id] = [o].[object_id]                                                , [minor_id] = 0                                                , [class] = 1                                                , [name] = n'microsoft_database_tools_support'                                         ))  ) [_results]

does of have experienced issue also? , how have corrected it?

cheers,

jeremy

and have same query generated sqlpackage.exe

select * (  select          [db].[is_ansi_null_default_on]          [isansinulldefaulton],          
[db].[is_ansi_nulls_on]                 [isansinullson],          [db].[is_ansi_padding_on]               [isansipaddingon],          
[db].[is_ansi_warnings_on]              [isansiwarningson],          [db].[is_arithabort_on]                 [isarithaborton],          
[db].[is_auto_close_on]                 [isautocloseon],          [db].[is_auto_create_stats_on]          [isautocreatestatisticson],          
[db].[is_auto_shrink_on]                [isautoshrinkon],          [db].[is_auto_update_stats_on]          [isautoupdatestatisticson],          [db].[is_cursor_close_on_commit_on]     [iscursorcloseoncommiton],          [db].[is_fulltext_enabled]              [isfulltextenabled],          [db].[is_in_standby]                    [isinstandby],          [db].[is_local_cursor_default]          [islocalcursorsdefault],          [db].[is_merge_published]               [ismergepublished],          [db].[is_concat_null_yields_null_on]    [isconcatnullyieldsnullon],          [db].[is_numeric_roundabort_on]         [isnumericroundaborton],          [db].[is_published]                     [ispublished],          [db].[is_quoted_identifier_on]          [isquotedidentifieron],          [db].[is_recursive_triggers_on]         [isrecursivetriggerson],  
        [db].[is_subscribed]   
...
  , [name] = n'microsoft_database_tools_support'                                         )))  ; 

it runs in 90s 10 times slower sql 2012 or if disable tf 4199 or run database in 110 compatibility mode.

 

SQL Server  >  SQL Server Database Engine