Metadata query slow on one specific database
0
votes
1
answer
95
views
I have quite a weird problem (who doesn't).
Our ETL Software scans source databases to claim object data. It extracts information from INFORMATION_SCHEMA about objects (tables and views)
For comparison this is what is looks like for a normal database:
Myself, i'm out of option. I've tried purging proc and system cache (with corresponding DBCC) and updating statistics on the whole database but it (as expected) did no good.
Any help is appreciated!
Kind regards
EDIT: I've added the query plans to compare with a database with about the same size and structure on the same server (which doesn't have the performance problem: https://file.io/ODajcfrzvGTP
It contains 2 files:
_fast is from the database *without* any problems
_slow is from the database *with* problems
SELECT
t.[TABLE_SCHEMA],
t.[TABLE_NAME],
c.[COLUMN_NAME],
c.[DATA_TYPE],
c.[CHARACTER_MAXIMUM_LENGTH],
c.[NUMERIC_PRECISION],
c.[NUMERIC_SCALE],
c.[DATETIME_PRECISION],
c.[ORDINAL_POSITION],
c.[IS_NULLABLE],
prim.[CONSTRAINT_NAME] AS [PRIMARY_KEY_NAME],
t.[TABLE_TYPE]
FROM [INFORMATION_SCHEMA].[TABLES] t
INNER JOIN [INFORMATION_SCHEMA].[COLUMNS] c
ON t.[TABLE_CATALOG] = c.[TABLE_CATALOG]
AND t.[TABLE_SCHEMA] = c.[TABLE_SCHEMA]
AND t.[TABLE_NAME] = c.[TABLE_NAME]
LEFT OUTER JOIN (
SELECT tc.[CONSTRAINT_NAME], tc.[CONSTRAINT_SCHEMA], tc.[CONSTRAINT_CATALOG], cc.[TABLE_NAME], cc.[COLUMN_NAME]
FROM [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS] tc
INNER JOIN [INFORMATION_SCHEMA].[CONSTRAINT_COLUMN_USAGE] cc
ON tc.[CONSTRAINT_NAME] = cc.[CONSTRAINT_NAME]
AND tc.[CONSTRAINT_SCHEMA] = cc.[CONSTRAINT_SCHEMA]
AND tc.[CONSTRAINT_CATALOG] = cc.[CONSTRAINT_CATALOG]
AND tc.[TABLE_NAME] = cc.[TABLE_NAME]
AND tc.[TABLE_CATALOG] = cc.[TABLE_CATALOG]
AND tc.[TABLE_SCHEMA] = cc.[TABLE_SCHEMA]
WHERE tc.[CONSTRAINT_TYPE] = 'PRIMARY KEY') prim
ON t.[TABLE_CATALOG] = prim.[CONSTRAINT_CATALOG]
AND t.[TABLE_SCHEMA] = prim.[CONSTRAINT_SCHEMA]
AND t.[TABLE_NAME] = prim.[TABLE_NAME]
AND c.[COLUMN_NAME] = prim.[COLUMN_NAME]
ORDER BY t.[TABLE_SCHEMA], t.[TABLE_NAME], c.[ORDINAL_POSITION]
For most databases (depending on the amount of objects and columns) this takes one minute to complete. For one specific database this takes between 10 and 35 minutes! I can't seem to figure out why. I cannot changes the above query (since it's embedded in the software) so i'm looking for a way to improve performance on this one database
The query outputs about the same amount of records. I've checked database configuration and those are exactly the same. When i tested with 'SET STATISTICS IO ON' it was clear that there was a lot going on in the less-performing database than there was in the other databases. I've put it in www.statisticsparser.com and this is what it looks like for the less-performing:


Asked by LonelyDBA92
(5 rep)
Mar 12, 2024, 01:20 PM
Last activity: Mar 22, 2024, 01:32 PM
Last activity: Mar 22, 2024, 01:32 PM