Sample Header Ad - 728x90

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)
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: statistics parsed bad database For comparison this is what is looks like for a normal database: statistics parsed good 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
Asked by LonelyDBA92 (5 rep)
Mar 12, 2024, 01:20 PM
Last activity: Mar 22, 2024, 01:32 PM