I am using TDP 3.4 and one of my data connections is to a SQL Server 2012 Express database. When I connect to the database, the connection is established very fast, but the background query used to populate the table information in the object explorer takes more time to complete than it used to about 6 months ago. It used to finish in 1 to 2 seconds, but now takes upwards of 15 seconds to finish. Does anyone know what might be causing this? Could this be a maintenance issue on the SQL Express side? I do somewhat regular rebuilds/reorganizations of the table indexes, but maybe I’m not running my scripts frequently enough for this? Any thoughts are appreciated.
Hi Andy,
Thanks for your feedback ,I’ve tried this ,Loading database is really slower than other version of SQL Server. So if you have a lot of database, it will cost more time.
I’ve create the issue TSS-740 for it and our developer is trying to find the reasons, I will send you the update when he works out.
Cathy.
Hi Andy,
I want to know how about if you use the SQL Server Management Studio? slower too? i found yes.so i think it is the SQL Server 2012‘s Engine is solw, no mater what client.
Of course i will continue to search for more information,and please try SQL Server Management Studio is solw or not.thanks.
Wiki.
i found the same sql sentement (get database list on SQL Server 2008)
SELECT
sd.name AS [Name],
ISNULL(SUSER_SNAME(sd.owner_sid), N'') AS [Owner],
--CAST(1 AS bit) AS [HasAccess],
CAST(HAS_DBACCESS(sd.name) AS bit) AS [HasAccess],
ISNULL(CAST(DATABASEPROPERTYEX(sd.name, 'Status') AS nvarchar(128)), N'') AS [Status],
CAST(sd.is_in_standby AS bit) AS [InStandBy],
sd.user_access_desc AS [AccessMode],
CAST(sd.is_read_only AS bit) AS [ReadOnly],
CAST(CASE
WHEN sd.name IN ('master', 'model', 'msdb', 'tempdb')
THEN 1
ELSE 0
END AS bit) AS [IsSystemDatabase],
NULL AS [LastBackupDate], --NULL AS [LastBackupDate],
sd.source_database_id AS [SourceDbId],
NULL AS [Containment],
is_broker_enabled as [IsBrokerEnabled]
FROM sys.databases sd
ORDER BY [Name] ASC
execute it on SQL Server 2008 spent less than a second.
but execute it on SQL Server 2012 need more than 5 seconds.
Thanks. Just for fun I installed a new instance on my machine and loaded all of the tables that I have in the other server. The connection to the new server runs really fast - around 1 second. The original server probably has twice as much data, but I also have a lot of stored procs there. The new one only has around 70 tables. I’m wondering if it is a combination of bad maintenance and more data? I don’t know if the stored procs would cause any slowness. I’ll keep an eye on this new instance as I build it up.
I also deleted some unused databases and the background query does run a little faster now.
good luck!