CPU Consuming
fabriciolima
Posts: 4 New member
in SQL Prompt
hi,
I have a database with too many objects. So, this query below take five minutes to execute and have large CPU consuming.
Do we have some workaround?
SELECT name, schema_id AS id FROM [BBH3RH_P10].[sys].schemas; SELECT o.schema_id AS owner, o.name, o.type, o.object_id AS id, o.parent_object_id AS parent, CONVERT(BIT, ( CASE WHEN o.is_ms_shipped = 1 THEN 1 WHEN x.major_id IS NOT NULL THEN 1 ELSE 0 END )) AS sysobj FROM [BBH3RH_P10].sys.objects o LEFT JOIN [BBH3RH_P10].sys.extended_properties x ON o.object_id = x.major_id AND x.minor_id = 0 AND x.class = 1 AND x.name = N'microsoft_database_tools_support' WHERE type != 'TR' AND schema_id!=4; SELECT object_id AS id, parent_id AS owner, name FROM [BBH3RH_P10].[sys].triggers; SELECT t.user_type_id AS id, t.name, t.schema_id AS owner, t.system_type_id as systype, t.is_table_type, tt.type_table_object_id, t.is_assembly_type as clr_type FROM [BBH3RH_P10].[sys].types t LEFT JOIN [BBH3RH_P10].[sys].table_types tt ON t.user_type_id = tt.user_type_id; SELECT name, object_id as owner, user_type_id as xusertype, column_id as colid, is_identity, is_computed, max_length as length, is_nullable FROM [BBH3RH_P10].sys.columns ORDER BY object_id, column_id; SELECT name, object_id, user_type_id, parameter_id, is_output, max_length FROM [BBH3RH_P10].sys.parameters ORDER BY object_id, parameter_id; SELECT s.name, s.object_id AS owner, CONVERT(BIT, ( CASE WHEN i.index_id IS NULL THEN 1 ELSE 0 END)) AS stats FROM [BBH3RH_P10].[sys].stats s LEFT JOIN [BBH3RH_P10].[sys].indexes i ON s.object_id = i.object_id AND s.stats_id = i.index_id; SELECT constraint_object_id AS constid, parent_object_id AS fkeyid, referenced_object_id AS rkeyid, parent_column_id AS fkey, referenced_column_id AS rkey FROM [BBH3RH_P10].[sys].foreign_key_columns ; SELECT i.object_id AS owner, ic.column_id AS colid FROM [BBH3RH_P10].[sys].indexes i INNER JOIN [BBH3RH_P10].[sys].index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id WHERE is_primary_key = 1; SELECT name, service_id FROM [BBH3RH_P10].sys.services; SELECT name, service_contract_id FROM [BBH3RH_P10].sys.service_contracts; SELECT name, message_type_id FROM [BBH3RH_P10].sys.service_message_types; SELECT name, route_id FROM [BBH3RH_P10].sys.routes; SELECT name, xml_collection_id, schema_id FROM [BBH3RH_P10].sys.xml_schema_collections; SELECT name FROM [BBH3RH_P10].sys.filegroups; SELECT principal_id AS uid, name FROM [BBH3RH_P10].[sys].database_principals WHERE type='R' OR type='A'; SELECT principal_id AS uid, name FROM [BBH3RH_P10].[sys].database_principals WHERE type='S' OR type='U' OR type='G'; SELECT SCHEMA_NAME(); SELECT object_id, schema_id, name, base_object_name FROM [BBH3RH_P10].[sys].[synonyms] WITH(NOLOCK); SELECT class, major_id, minor_id, value FROM [BBH3RH_P10].[sys].extended_properties WHERE name='MS_Description';
I have a database with too many objects. So, this query below take five minutes to execute and have large CPU consuming.
Do we have some workaround?
SELECT name, schema_id AS id FROM [BBH3RH_P10].[sys].schemas; SELECT o.schema_id AS owner, o.name, o.type, o.object_id AS id, o.parent_object_id AS parent, CONVERT(BIT, ( CASE WHEN o.is_ms_shipped = 1 THEN 1 WHEN x.major_id IS NOT NULL THEN 1 ELSE 0 END )) AS sysobj FROM [BBH3RH_P10].sys.objects o LEFT JOIN [BBH3RH_P10].sys.extended_properties x ON o.object_id = x.major_id AND x.minor_id = 0 AND x.class = 1 AND x.name = N'microsoft_database_tools_support' WHERE type != 'TR' AND schema_id!=4; SELECT object_id AS id, parent_id AS owner, name FROM [BBH3RH_P10].[sys].triggers; SELECT t.user_type_id AS id, t.name, t.schema_id AS owner, t.system_type_id as systype, t.is_table_type, tt.type_table_object_id, t.is_assembly_type as clr_type FROM [BBH3RH_P10].[sys].types t LEFT JOIN [BBH3RH_P10].[sys].table_types tt ON t.user_type_id = tt.user_type_id; SELECT name, object_id as owner, user_type_id as xusertype, column_id as colid, is_identity, is_computed, max_length as length, is_nullable FROM [BBH3RH_P10].sys.columns ORDER BY object_id, column_id; SELECT name, object_id, user_type_id, parameter_id, is_output, max_length FROM [BBH3RH_P10].sys.parameters ORDER BY object_id, parameter_id; SELECT s.name, s.object_id AS owner, CONVERT(BIT, ( CASE WHEN i.index_id IS NULL THEN 1 ELSE 0 END)) AS stats FROM [BBH3RH_P10].[sys].stats s LEFT JOIN [BBH3RH_P10].[sys].indexes i ON s.object_id = i.object_id AND s.stats_id = i.index_id; SELECT constraint_object_id AS constid, parent_object_id AS fkeyid, referenced_object_id AS rkeyid, parent_column_id AS fkey, referenced_column_id AS rkey FROM [BBH3RH_P10].[sys].foreign_key_columns ; SELECT i.object_id AS owner, ic.column_id AS colid FROM [BBH3RH_P10].[sys].indexes i INNER JOIN [BBH3RH_P10].[sys].index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id WHERE is_primary_key = 1; SELECT name, service_id FROM [BBH3RH_P10].sys.services; SELECT name, service_contract_id FROM [BBH3RH_P10].sys.service_contracts; SELECT name, message_type_id FROM [BBH3RH_P10].sys.service_message_types; SELECT name, route_id FROM [BBH3RH_P10].sys.routes; SELECT name, xml_collection_id, schema_id FROM [BBH3RH_P10].sys.xml_schema_collections; SELECT name FROM [BBH3RH_P10].sys.filegroups; SELECT principal_id AS uid, name FROM [BBH3RH_P10].[sys].database_principals WHERE type='R' OR type='A'; SELECT principal_id AS uid, name FROM [BBH3RH_P10].[sys].database_principals WHERE type='S' OR type='U' OR type='G'; SELECT SCHEMA_NAME(); SELECT object_id, schema_id, name, base_object_name FROM [BBH3RH_P10].[sys].[synonyms] WITH(NOLOCK); SELECT class, major_id, minor_id, value FROM [BBH3RH_P10].[sys].extended_properties WHERE name='MS_Description';
Tagged:
Answers