The list of databases doesn't load in an instance with many databases about azuredatastudio HOT 9 CLOSED

I believe Leila has a PR for this already - microsoft/sqltoolsservice#547. If so this is our 1st fix for a publicly reported issue so thanks for raising it @danilocbraga

from azuredatastudio.

I could reproduce the bug connecting to a sql instance with 600 databases on it.

from azuredatastudio.

@abist could you take a look at this? The most recent change to this codepath is microsoft/sqltoolsservice@10cb78b

@danilocbraga could you please give us some more information to help reproduce this - for example:

• Can you confirm the issue is when clicking the databases dropdown shown below?
  
• How many databases do you have in that server?
• You mention database mirroring. How many DBs do you have set up that way / could you explain how this impacts this?

The specific query we're using is SELECT name FROM sys.databases WHERE state_desc='ONLINE' ORDER BY name ASC so it's interesting this is slow. If possible:

• How long does executing this against the database you're connected to take? Are there any issues with the query?

from azuredatastudio.

@kevcunnane, the database list you mentioned works just fine.

The following is the one I was talking about.

The instance I'm testing has around 100 databases.

• I don't have any databases with auto-close enabled.
• I also don't have any databases with database mirroring/always on enabled.

Using SQL Profiler, I see the following query running for all databases.
It runs in average of 1.5 seconds on each db. But, If I remove the columns related to the sys.database_mirroring, it returns in 50 ms.

SELECT
ISNULL((case dmi.mirroring_redo_queue_type when N'UNLIMITED' then 0 else dmi.mirroring_redo_queue end),0) AS [MirroringRedoQueueMaxSize],
ISNULL(dmi.mirroring_connection_timeout,0) AS [MirroringTimeout],
ISNULL(dmi.mirroring_partner_name,'') AS [MirroringPartner],
ISNULL(dmi.mirroring_partner_instance,'') AS [MirroringPartnerInstance],
ISNULL(dmi.mirroring_role,0) AS [MirroringRole],
ISNULL(dmi.mirroring_safety_level + 1, 0) AS [MirroringSafetyLevel],
ISNULL(dmi.mirroring_state + 1, 0) AS [MirroringStatus],
ISNULL(dmi.mirroring_witness_name,'') AS [MirroringWitness],
ISNULL(dmi.mirroring_witness_state + 1, 0) AS [MirroringWitnessStatus],
CAST(case when dmi.mirroring_partner_name is null then 0 else 1 end AS bit) AS [IsMirroringEnabled],
ISNULL(dmi.mirroring_guid,'00000000-0000-0000-0000-0000000000000000') AS [MirroringID],
ISNULL(dmi.mirroring_role_sequence,0) AS [MirroringRoleSequence],
ISNULL(dmi.mirroring_safety_sequence,0) AS [MirroringSafetySequence],
ISNULL(dmi.mirroring_failover_lsn,0) AS [MirroringFailoverLogSequenceNumber],
dtb.is_ansi_null_default_on AS [AnsiNullDefault],
dtb.is_ansi_nulls_on AS [AnsiNullsEnabled],
dtb.is_ansi_padding_on AS [AnsiPaddingEnabled],
dtb.is_ansi_warnings_on AS [AnsiWarningsEnabled],
dtb.is_arithabort_on AS [ArithmeticAbortEnabled],
dtb.is_auto_shrink_on AS [AutoShrink],
dtb.is_cursor_close_on_commit_on AS [CloseCursorsOnCommitEnabled],
dtb.is_concat_null_yields_null_on AS [ConcatenateNullYieldsNull],
dtb.is_numeric_roundabort_on AS [NumericRoundAbortEnabled],
dtb.is_quoted_identifier_on AS [QuotedIdentifiersEnabled],
dtb.is_read_only AS [ReadOnly],
dtb.is_recursive_triggers_on AS [RecursiveTriggersEnabled],
dtb.user_access AS [UserAccess],
dtb.is_db_chaining_on AS [DatabaseOwnershipChaining],
dtb.is_auto_update_stats_async_on AS [AutoUpdateStatisticsAsync],
dtb.is_date_correlation_on AS [DateCorrelationOptimization],
dtb.is_trustworthy_on AS [Trustworthy],
dtb.name AS [Name],
dtb.database_id AS [ID],
dtb.create_date AS [CreateDate],
dtb.is_auto_create_stats_on AS [AutoCreateStatisticsEnabled],
dtb.is_auto_update_stats_on AS [AutoUpdateStatisticsEnabled],
dtb.is_parameterization_forced AS [IsParameterizationForced],
dtb.is_read_committed_snapshot_on AS [IsReadCommittedSnapshotOn],
CAST(isnull(dtb.source_database_id, 0) AS bit) AS [IsDatabaseSnapshot],
ISNULL(DB_NAME(dtb.source_database_id), N'') AS [DatabaseSnapshotBaseName],
dtb.is_fulltext_enabled AS [IsFullTextEnabled],
dtb.service_broker_guid AS [ServiceBrokerGuid],
dtb.snapshot_isolation_state AS [SnapshotIsolationState],
(dtb.is_published*1+dtb.is_subscribed*2+dtb.is_merge_published*4) AS [ReplicationOptions],
dtb.is_local_cursor_default AS [LocalCursorsDefault],
dtb.page_verify_option AS [PageVerify],
dtb.recovery_model AS [RecoveryModel],
dtb.is_auto_close_on AS [AutoClose],
dtb.is_broker_enabled AS [BrokerEnabled],
ISNULL(suser_sname(dtb.owner_sid),'') AS [Owner],
ISNULL(dtb.log_reuse_wait,0) AS [LogReuseWaitStatus],
drs.recovery_fork_guid AS [RecoveryForkGuid],
drs.database_guid AS [DatabaseGuid],
CAST((case when drs.last_log_backup_lsn is not null then 1 else 0 end) AS bit) AS [HasFullBackup],
CAST(case when dtb.name in ('master','model','msdb','tempdb') then 1 else dtb.is_distributor end AS bit) AS [IsSystemObject],
CAST(case when ctb.database_id is null then 0 else 1  end AS bit) AS [ChangeTrackingEnabled],
CAST(ISNULL(ctb.is_auto_cleanup_on,0) AS bit) AS [ChangeTrackingAutoCleanUp],
ISNULL(ctb.retention_period,0) AS [ChangeTrackingRetentionPeriod],
CAST(ISNULL(ctb.retention_period_units,0) AS tinyint) AS [ChangeTrackingRetentionPeriodUnits],
dtb.containment AS [ContainmentType],
dtb.default_language_lcid AS [DefaultLanguageLcid],
dtb.default_language_name AS [DefaultLanguageName],
dtb.default_fulltext_language_lcid AS [DefaultFullTextLanguageLcid],
ISNULL(dtb.default_fulltext_language_name,N'') AS [DefaultFullTextLanguageName],
CAST(dtb.is_nested_triggers_on AS bit) AS [NestedTriggersEnabled],
CAST(dtb.is_transform_noise_words_on AS bit) AS [TransformNoiseWords],
dtb.two_digit_year_cutoff AS [TwoDigitYearCutoff],
dtb.target_recovery_time_in_seconds AS [TargetRecoveryTime],
dtb.delayed_durability AS [DelayedDurability],
dtb.is_auto_create_stats_incremental_on AS [AutoCreateIncrementalStatisticsEnabled],

        case
        when dtb.collation_name is null then 0x200
        else 0
        end |
        case
        when 1 = dtb.is_in_standby then 0x40
        else 0
        end |
        case dtb.state
        when 1 then 0x2
        when 2 then 0x8
        when 3 then 0x4
        when 4 then 0x10
        when 5 then 0x100
        when 6 then 0x20
        else 1
        end
       AS [Status],
CAST(( case LOWER(convert( nvarchar(128), DATABASEPROPERTYEX(dtb.name, 'Updateability'))) when 'read_write' then 1 else 0 end) AS bit) AS [IsUpdateable],
CAST(dtb.is_encrypted AS bit) AS [EncryptionEnabled],
CAST(dtb.is_honor_broker_priority_on AS bit) AS [HonorBrokerPriority],
CAST(
        case
        when SERVERPROPERTY('EngineEdition') = 6 then cast(1 as bit)
        else cast(0 as bit)
        end
       AS bit) AS [IsSqlDw],
CAST(has_dbaccess(dtb.name) AS bit) AS [IsAccessible],
ISNULL(fsopt.directory_name , N'') AS [FilestreamDirectoryName],
ISNULL(fsopt.non_transacted_access , 0) AS [FilestreamNonTransactedAccess],
CAST(dtb.is_remote_data_archive_enabled AS bit) AS [RemoteDataArchiveEnabled],
NULL AS [RemoteDataArchiveEndpoint],
NULL AS [RemoteDataArchiveLinkedServer],
NULL AS [RemoteDatabaseName],
0 AS [RemoteDataArchiveUseFederatedServiceAccount],
NULL AS [RemoteDataArchiveCredential],
0 AS [MaxDop],
NULL AS [MaxDopForSecondary],
0 AS [LegacyCardinalityEstimation],
2 AS [LegacyCardinalityEstimationForSecondary],
0 AS [ParameterSniffing],
2 AS [ParameterSniffingForSecondary],
0 AS [QueryOptimizerHotfixes],
2 AS [QueryOptimizerHotfixesForSecondary],
dtb.name AS [DatabaseName2],
dtb.containment AS [ContainmentType2],
dtb.name AS [DatabaseName5],
dtb.name AS [DatabaseName6]
FROM
master.sys.databases AS dtb
LEFT OUTER JOIN sys.database_mirroring AS dmi ON dmi.database_id = dtb.database_id
LEFT OUTER JOIN sys.database_recovery_status AS drs ON drs.database_id = dtb.database_id
LEFT OUTER JOIN sys.change_tracking_databases AS ctb ON ctb.database_id = dtb.database_id 
LEFT OUTER JOIN sys.database_filestream_options AS fsopt ON fsopt.database_id = dtb.database_id
WHERE
(dtb.name=@_msparam_0)

from azuredatastudio.

@danilocbraga thanks for the clarification!
@llali could you help with this? This seems related to our use of the SMO Server.Databases enumeration, correct? Can we figure out whether the fix would be in SMO or if we can work around in the tools service code?

from azuredatastudio.

@danilocbraga the instance I'm testing has 100 databases but I don't see any timeout issue. I definitely see same query running for each db so that can cause performance issue. I have a fix for it in sqltoolsservice.

@kevcunnane the issue is in sqltoolsservice not in SMO and it's actually an easy fix

from azuredatastudio.

I verified the issue with 600 databases and also verified that it's fixed using the change in sqltoolsservice

from azuredatastudio.

Merged the PR: microsoft/sqltoolsservice#547

from azuredatastudio.

I have a client with 1,884 databases and this is causing blocking and causing relatively small databases with small ldf files (500 MB) to take forever to restore.

Microsoft SQL Server 2016 (SP1-CU1) (KB3208177) - 13.0.4411.0 (X64)
Jan 6 2017 14:24:37
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 (Build 9600: )

(@_msparam_0 nvarchar(4000))SELECT
ISNULL((case dmi.mirroring_redo_queue_type when N'UNLIMITED' then 0 else dmi.mirroring_redo_queue end),0) AS [MirroringRedoQueueMaxSize],
ISNULL(dmi.mirroring_connection_timeout,0) AS [MirroringTimeout],
ISNULL(dmi.mirroring_partner_name,'') AS [MirroringPartner],
ISNULL(dmi.mirroring_partner_instance,'') AS [MirroringPartnerInstance],
ISNULL(dmi.mirroring_role,0) AS [MirroringRole],
ISNULL(dmi.mirroring_safety_level + 1, 0) AS [MirroringSafetyLevel],
ISNULL(dmi.mirroring_state + 1, 0) AS [MirroringStatus],
ISNULL(dmi.mirroring_witness_name,'') AS [MirroringWitness],
ISNULL(dmi.mirroring_witness_state + 1, 0) AS [MirroringWitnessStatus],
CAST(case when dmi.mirroring_partner_name is null then 0 else 1 end AS bit) AS [IsMirroringEnabled],
ISNULL(dmi.mirroring_guid,'00000000-0000-0000-0000-0000000000000000') AS [MirroringID],
ISNULL(dmi.mirroring_role_sequence,0) AS [MirroringRoleSequence],
ISNULL(dmi.mirroring_safety_sequence,0) AS [MirroringSafetySequence],
ISNULL(dmi.mirroring_failover_lsn,0) AS [MirroringFailoverLogSequenceNumber],
dtb.is_ansi_null_default_on AS [AnsiNullDefault],
dtb.is_ansi_nulls_on AS [AnsiNullsEnabled],
dtb.is_ansi_padding_on AS [AnsiPaddingEnabled],
dtb.is_ansi_warnings_on AS [AnsiWarningsEnabled],
dtb.is_arithabort_on AS [ArithmeticAbortEnabled],
dtb.is_auto_shrink_on AS [AutoShrink],
dtb.is_cursor_close_on_commit_on AS [CloseCursorsOnCommitEnabled],
dtb.is_concat_null_yields_null_on AS [ConcatenateNullYieldsNull],
dtb.is_numeric_roundabort_on AS [NumericRoundAbortEnabled],
dtb.is_quoted_identifier_on AS [QuotedIdentifiersEnabled],
dtb.is_read_only AS [ReadOnly],
dtb.is_recursive_triggers_on AS [RecursiveTriggersEnabled],
dtb.is_local_cursor_default AS [LocalCursorsDefault],
dtb.page_verify_option AS [PageVerify],
dtb.recovery_model AS [RecoveryModel],
dtb.user_access AS [UserAccess],
dtb.is_db_chaining_on AS [DatabaseOwnershipChaining],
dtb.is_auto_update_stats_async_on AS [AutoUpdateStatisticsAsync],
dtb.is_date_correlation_on AS [DateCorrelationOptimization],
dtb.is_trustworthy_on AS [Trustworthy],
dtb.name AS [Name],
dtb.database_id AS [ID],
dtb.create_date AS [CreateDate],
dtb.is_auto_create_stats_on AS [AutoCreateStatisticsEnabled],
dtb.is_auto_update_stats_on AS [AutoUpdateStatisticsEnabled],
dtb.is_parameterization_forced AS [IsParameterizationForced],
dtb.is_read_committed_snapshot_on AS [IsReadCommittedSnapshotOn],
dtb.is_auto_close_on AS [AutoClose],
dtb.is_broker_enabled AS [BrokerEnabled],
CAST(isnull(dtb.source_database_id, 0) AS bit) AS [IsDatabaseSnapshot],
ISNULL(DB_NAME(dtb.source_database_id), N'') AS [DatabaseSnapshotBaseName],
dtb.is_fulltext_enabled AS [IsFullTextEnabled],
dtb.service_broker_guid AS [ServiceBrokerGuid],
dtb.snapshot_isolation_state AS [SnapshotIsolationState],
(dtb.is_published1+dtb.is_subscribed2+dtb.is_merge_published*4) AS [ReplicationOptions],
suser_sname(dtb.owner_sid) AS [Owner],
ISNULL(dtb.log_reuse_wait,0) AS [LogReuseWaitStatus],
drs.recovery_fork_guid AS [RecoveryForkGuid],
drs.database_guid AS [DatabaseGuid],
CAST((case when drs.last_log_backup_lsn is not null then 1 else 0 end) AS bit) AS [HasFullBackup],
CAST(case when dtb.name in ('master','model','msdb','tempdb') then 1 else dtb.is_distributor end AS bit) AS [IsSystemObject],
CAST(case when ctb.database_id is null then 0 else 1 end AS bit) AS [ChangeTrackingEnabled],
CAST(ISNULL(ctb.is_auto_cleanup_on,0) AS bit) AS [ChangeTrackingAutoCleanUp],
ISNULL(ctb.retention_period,0) AS [ChangeTrackingRetentionPeriod],
CAST(ISNULL(ctb.retention_period_units,0) AS tinyint) AS [ChangeTrackingRetentionPeriodUnits],
dtb.containment AS [ContainmentType],
dtb.default_language_lcid AS [DefaultLanguageLcid],
dtb.default_language_name AS [DefaultLanguageName],
dtb.default_fulltext_language_lcid AS [DefaultFullTextLanguageLcid],
ISNULL(dtb.default_fulltext_language_name,N'') AS [DefaultFullTextLanguageName],
CAST(dtb.is_nested_triggers_on AS bit) AS [NestedTriggersEnabled],
CAST(dtb

(@_msparam_0 nvarchar(4000))
create table #tmp_db_hadr_dbrs (group_database_id uniqueidentifier, synchronization_state tinyint, is_local bit)

    declare @HasViewPermission int
    select @HasViewPermission = HAS_PERMS_BY_NAME(null, null, 'VIEW SERVER STATE')
  


    if (@HasViewPermission = 1)
    begin
    insert into #tmp_db_hadr_dbrs select group_database_id, synchronization_state, is_local from master.sys.dm_hadr_database_replica_states
    end

SELECT
dtb.name AS [Database_Name],
'Server[@name=' + quotename(CAST(
serverproperty(N'Servername')
AS sysname),'''') + ']' + '/Database[@name=' + quotename(dtb.name,'''') + ']' AS [Database_Urn],
dtb.containment AS [Database_ContainmentType],
dtb.recovery_model AS [Database_RecoveryModel],
suser_sname(dtb.owner_sid) AS [Database_Owner],

      case
      when dtb.collation_name is null then 0x200
      else 0
      end |
      case
      when 1 = dtb.is_in_standby then 0x40
      else 0
      end |
      case dtb.state
      when 1 then 0x2
      when 2 then 0x8
      when 3 then 0x4
      when 4 then 0x10
      when 5 then 0x100
      when 6 then 0x20
      else 1
      end
     AS [Database_Status],

dtb.compatibility_level AS [Database_CompatibilityLevel],
ISNULL(dmi.mirroring_role,0) AS [Database_MirroringRole],
ISNULL(dmi.mirroring_state + 1, 0) AS [Database_MirroringStatus],
CAST(has_dbaccess(dtb.name) AS bit) AS [Database_IsAccessible],
dbrs.synchronization_state AS [Database_AvailabilityDatabaseSynchronizationState],
dtb.recovery_model AS [RecoveryModel],
dtb.user_access AS [UserAccess],
dtb.is_read_only AS [ReadOnly],
dtb.name AS [Database_DatabaseName2]
FROM
master.sys.databases AS dtb
LEFT OUTER JOIN sys.database_mirroring AS dmi ON dmi.database_id = dtb.database_id
LEFT OUTER JOIN #tmp_db_hadr_dbrs AS dbrs ON dtb.group_database_id = dbrs.group_database_id and dbrs.is_local = 1

WHERE
(dtb.name=@_msparam_0)
ORDER BY
[Database_Name] ASC

    drop table #tmp_db_hadr_dbrs

from azuredatastudio.