ktaranov/sqlserver-kit

SSMS Locking up on explorer operations

jercole opened this issue · 8 comments

jercole commented

Do you want to request a feature or report a bug?
Question regarding potential SSMSBoost issue relating to a specific query.

What is the current behavior?
With SSMS Boost installed with SSMS version 17.4, we keep seeing SSMS locking up and a "sp_whoisactive" run shows us that its locked and sitting on this specific query (will be at thee bottom).

The query is essentially the SQL found here below the proc:
https://github.com/ktaranov/sqlserver-kit/blob/master/Scripts/Table_Parameters.sql

I'm trying to figure out if this query is an SSMS thing, or part of SSMSBoost.

If the current behavior is a bug, please provide the steps to reproduce and if possible a minimal demo of the problem via http://sqlfiddle.com/

What is the expected behavior?

Which versions of SQL Server and which OS are affected by this issue? Did this work in previous versions of our procedures?
SQL Server 2016, Windows 10. Not sure about previous versions.

jercole commented

Note:

The query that I'm seeing when SSMS locks up is exactly below:

It looks very much like the query here:
https://github.com/ktaranov/sqlserver-kit/blob/master/Scripts/Table_Parameters.sql

We're trying to determine if it is SSMSBoost that is running this query, and if so, can this behavior be somehow disabled.


<?query --
SELECT
tbl.name AS [Name],
tbl.object_id AS [ID],
tbl.create_date AS [CreateDate],
tbl.modify_date AS [DateLastModified],
ISNULL(stbl.name, N'') AS [Owner],
CAST(case when tbl.principal_id is null then 1 else 0 end AS bit) AS [IsSchemaOwned],
SCHEMA_NAME(tbl.schema_id) AS [Schema],
CAST(
 case 
    when tbl.is_ms_shipped = 1 then 1
    when (
        select 
            major_id 
        from 
            sys.extended_properties 
        where 
            major_id = tbl.object_id and 
            minor_id = 0 and 
            class = 1 and 
            name = N'microsoft_database_tools_support') 
        is not null then 1
    else 0
end          
             AS bit) AS [IsSystemObject],
CAST(OBJECTPROPERTY(tbl.object_id, N'HasAfterTrigger') AS bit) AS [HasAfterTrigger],
CAST(OBJECTPROPERTY(tbl.object_id, N'HasInsertTrigger') AS bit) AS [HasInsertTrigger],
CAST(OBJECTPROPERTY(tbl.object_id, N'HasDeleteTrigger') AS bit) AS [HasDeleteTrigger],
CAST(OBJECTPROPERTY(tbl.object_id, N'HasInsteadOfTrigger') AS bit) AS [HasInsteadOfTrigger],
CAST(OBJECTPROPERTY(tbl.object_id, N'HasUpdateTrigger') AS bit) AS [HasUpdateTrigger],
CAST(OBJECTPROPERTY(tbl.object_id, N'IsIndexed') AS bit) AS [HasIndex],
CAST(OBJECTPROPERTY(tbl.object_id, N'IsIndexable') AS bit) AS [IsIndexable],
CAST(CASE idx.index_id WHEN 1 THEN 1 ELSE 0 END AS bit) AS [HasClusteredIndex],
CAST(ISNULL((select top 1 1 from sys.indexes ind where ind.object_id = tbl.object_id and ind.type > 1 and ind.is_hypothetical = 0 ), 0) AS bit) AS [HasNonClusteredIndex],
CAST(case idx.index_id when 1 then case when (idx.is_primary_key + 2*idx.is_unique_constraint = 1) then 1 else 0 end else 0 end AS bit) AS [HasPrimaryClusteredIndex],
CAST(ISNULL((select top 1 1 from sys.indexes ind where ind.object_id = tbl.object_id and ind.type = 6 and ind.is_hypothetical = 0 ), 0) AS bit) AS [HasNonClusteredColumnStoreIndex],
CAST(ISNULL((select top 1 1 from sys.indexes ind where ind.object_id = tbl.object_id and ind.type = 3 and ind.is_hypothetical = 0 ), 0) AS bit) AS [HasXmlIndex],
CAST(CASE idx.type WHEN 0 THEN 1 ELSE 0 END AS bit) AS [HasHeapIndex],
CAST(ISNULL((select top 1 1 from sys.all_columns as clmns join sys.types as usrt on usrt.user_type_id = clmns.user_type_id where clmns.object_id = tbl.object_id and usrt.name = N'xml'), 0) AS bit) AS [HasXmlData],
CAST(ISNULL((select top 1 1 from sys.all_columns as clmns join sys.types as usrt on usrt.user_type_id = clmns.user_type_id where clmns.object_id = tbl.object_id and usrt.name in (N'geometry', N'geography')), 0) AS bit) AS [HasSpatialData],
tbl.uses_ansi_nulls AS [AnsiNullsStatus],
CAST(ISNULL(OBJECTPROPERTY(tbl.object_id,N'IsQuotedIdentOn'),0) AS bit) AS [QuotedIdentifierStatus],
CAST(0 AS bit) AS [FakeSystemTable],
ISNULL(dstext.name,N'') AS [TextFileGroup],
CAST(tbl.is_memory_optimized AS bit) AS [IsMemoryOptimized],
case when (tbl.durability=1) then 0 else 1 end AS [Durability],
tbl.is_replicated AS [Replicated],
tbl.lock_escalation AS [LockEscalation],
CAST(case when ctt.object_id is null then 0 else 1  end AS bit) AS [ChangeTrackingEnabled],
CAST(ISNULL(ctt.is_track_columns_updated_on,0) AS bit) AS [TrackColumnsUpdatedEnabled],
tbl.is_filetable AS [IsFileTable],
ISNULL(ft.directory_name,N'') AS [FileTableDirectoryName],
ISNULL(ft.filename_collation_name,N'') AS [FileTableNameColumnCollation],
CAST(ISNULL(ft.is_enabled,0) AS bit) AS [FileTableNamespaceEnabled],
CASE WHEN 'PS'=dsidx.type THEN dsidx.name ELSE N'' END AS [PartitionScheme],
CAST(CASE WHEN 'PS'=dsidx.type THEN 1 ELSE 0 END AS bit) AS [IsPartitioned],
CASE WHEN 'FD'=dstbl.type THEN dstbl.name ELSE N'' END AS [FileStreamFileGroup],
CASE WHEN 'PS'=dstbl.type THEN dstbl.name ELSE N'' END AS [FileStreamPartitionScheme],
CAST(CASE idx.type WHEN 5 THEN 1 ELSE 0 END AS bit) AS [HasClusteredColumnStoreIndex],
CAST(CASE tbl.temporal_type WHEN 2 THEN 1 ELSE 0 END AS bit) AS [IsSystemVersioned],
CAST(ISNULL(historyTable.name, N'') AS sysname) AS [HistoryTableName],
CAST(ISNULL(SCHEMA_NAME(historyTable.schema_id), N'') AS sysname) AS [HistoryTableSchema],
CAST(ISNULL(historyTable.object_id, 0) AS int) AS [HistoryTableID],
CAST(CASE WHEN periods.start_column_id IS NULL THEN 0 ELSE 1 END AS bit) AS [HasSystemTimePeriod],
CAST(
      ISNULL((SELECT cols.name
      FROM sys.columns cols
      WHERE periods.object_id = tbl.object_id
      AND cols.object_id = tbl.object_id
      AND cols.column_id = periods.start_column_id), N'')
     AS sysname) AS [SystemTimePeriodStartColumn],
CAST(
      ISNULL((SELECT cols.name
      FROM sys.columns cols
      WHERE periods.object_id = tbl.object_id
      AND cols.object_id = tbl.object_id
      AND cols.column_id = periods.end_column_id), N'')
     AS sysname) AS [SystemTimePeriodEndColumn],
tbl.temporal_type AS [TemporalType],
CAST(tbl.is_remote_data_archive_enabled AS bit) AS [RemoteDataArchiveEnabled],
CAST(
      ISNULL(rdat.migration_state, 0)
     AS tinyint) AS [RemoteDataArchiveDataMigrationState],
CAST(rdat.filter_predicate AS varchar(4000)) AS [RemoteDataArchiveFilterPredicate],
CAST(rdat.remote_table_name AS sysname) AS [RemoteTableName],
CAST(CASE WHEN rdat.remote_table_name IS NULL THEN 0 ELSE 1 END AS bit) AS [RemoteTableProvisioned],
CAST(tbl.is_external AS bit) AS [IsExternal],
eds.name AS [DataSourceName],
ISNULL(eff.name,N'') AS [FileFormatName],
ISNULL(et.location,N'') AS [Location],

      CASE et.reject_type
      WHEN 'VALUE'      THEN 0
      WHEN 'PERCENTAGE' THEN 1
      ELSE 255
      END
     AS [RejectType],
ISNULL(et.reject_value,0) AS [RejectValue],
ISNULL(et.reject_sample_value,-1) AS [RejectSampleValue],
CAST(0 AS bit) AS [IsNode],
CAST(0 AS bit) AS [IsEdge]
FROM
sys.tables AS tbl
LEFT OUTER JOIN sys.database_principals AS stbl ON stbl.principal_id = ISNULL(tbl.principal_id, (OBJECTPROPERTY(tbl.object_id, 'OwnerId')))
INNER JOIN sys.indexes AS idx ON 
        idx.object_id = tbl.object_id and (idx.index_id < @_msparam_0  or (tbl.is_memory_optimized = 1 and idx.index_id = (select min(index_id) from sys.indexes where object_id = tbl.object_id)))
      
LEFT OUTER JOIN sys.data_spaces AS dstext  ON tbl.lob_data_space_id = dstext.data_space_id
LEFT OUTER JOIN sys.change_tracking_tables AS ctt ON ctt.object_id = tbl.object_id 
LEFT OUTER JOIN sys.filetables AS ft ON ft.object_id = tbl.object_id 
LEFT OUTER JOIN sys.data_spaces AS dsidx ON dsidx.data_space_id = idx.data_space_id
LEFT OUTER JOIN sys.tables AS t ON t.object_id = idx.object_id
LEFT OUTER JOIN sys.data_spaces AS dstbl ON dstbl.data_space_id = t.Filestream_data_space_id and (idx.index_id < 2 or (idx.type = 7 and idx.index_id < 3))
LEFT OUTER JOIN sys.tables as historyTable ON historyTable.object_id = tbl.history_table_id
LEFT OUTER JOIN sys.periods as periods ON periods.object_id = tbl.object_id
LEFT OUTER JOIN #tmp_extended_remote_data_archive_tables AS rdat ON rdat.object_id = tbl.object_id
LEFT OUTER JOIN sys.external_tables AS et ON et.object_id = tbl.object_id
LEFT OUTER JOIN sys.external_data_sources AS eds ON eds.data_source_id = et.data_source_id
LEFT OUTER JOIN sys.external_file_formats AS eff ON eff.file_format_id = et.file_format_id
WHERE
(tbl.name=@_msparam_1 and SCHEMA_NAME(tbl.schema_id)=@_msparam_2)
--?>
ktaranov commented

Hi, @jercole. Tomorrow I will try to reproduce your problem (my time zone UTC+3).

ktaranov commented

Hi, @jercole. I updated this script f30fee1 and checked on all our servers - all works fine without any locks.
Could you provide additional information:

  1. SSMSBoost - free or paid version
  2. Could you try this query in SQLOS (https://github.com/Microsoft/sqlopsstudio)?
jercole commented
jercole commented
ktaranov commented

Hi, @jercole.

Should I just point to any table on any database in our system once you are able to answer about SQLOS?
Yes, any table.

But I think that we misunderstood each other. Could you describe what problem do you want to resolve with this query? You need some information about your tables on database?

Why I ask: this query reproduced when you press right button on table name in SSMS (properties), I get it with profiler - very strange that on your server this query can reproduce deadlocks.

jercole commented
ktaranov commented

@jercole , does this issue is actual for you?