0

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

12
  • I'm not sure this is a time to complete maybe it's time to get the result on the client, just execute it from SSMS as select ... into #t from... this will esclude getting to the client or just use set statistics time on and look at that time
    – sepupic
    Commented Mar 12 at 13:39
  • You still have options to try. Even if you cannot change the query, you can try reducing it to see which part is causing problems. Remove ORDER BY and see, if it is still slow. Remove one table and see how the runtime changes and so on. Finally you can check the execution plan. Even if these are system tables, the principles are the same. Finally if you want more substantial help you should add the ACTUAL execution plan to your question.
    – rois
    Commented Mar 12 at 13:47
  • @sepupic Interesting suggestion. Just tried and problem is still showing the same problem unfortunately... Commented Mar 12 at 13:50
  • @rois i actual know what the problem is. The subquery that finds all constraints and column constraints. When this is left out, the query runs in seconds. What i don't understand is, what could by in the system base tables that is not in another database. If you look at the statistics IO of the query it hits some tables way more as opposed to the beter performing database. I'm looking for a hint in that direction. Commented Mar 12 at 13:58
  • Please add actual execution plan as was suggested. How many database schemas do you have?
    – sepupic
    Commented Mar 12 at 14:05

1 Answer 1

-2

Answer was given by @DanGuzman and @Sepupic.

Mainly updating system object statistics helped improving the duration of the query as explained on this site: https://www.dbdelta.com/sql-server-system-table-statistics-update/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.