Tempdb переполнен при запросе различного количества всех таблиц

Question

ОРИГИНАЛЬНАЯ ПРОБЛЕМА

Я создал собственный скрипт для извлечения данных с удаленного сервера SQL в нашу локальную копию в нашем офисе. У меня были некоторые проблемы со сценарием, когда в выбранные таблицы вставлялось несколько данных, что создавало дубликаты. Я знаю, что для всех таблиц во всех базах данных не должно быть дубликатов.

Эта проблема сделала меня параноиком, что другие таблицы могли иметь эту проблему исторически, и поэтому я хотел бы проверить это.

РЕШЕНИЕ

Я создал сценарий SQL для вставки счетчика и отдельного счетчика всех столбцов в таблицу для всех баз данных на нашем сервере (кроме 4 системных баз данных):

DECLARE     @TableFullName AS NVARCHAR(MAX)
DECLARE     @SQLQuery AS NVARCHAR(MAX)
DECLARE     @TableHasDuplicates AS BIT
DECLARE     @TempTableRowCount AS INT
DECLARE     @ResultsTable TABLE ([CompleteTableName] NVARCHAR(200), [CountAll] INT, [CountDistinct] INT)
DECLARE     @CountAll INT
DECLARE     @CountDistinct INT

SET NOCOUNT ON
DECLARE @AllTables TABLE ([CompleteTableName] NVARCHAR(200))
INSERT INTO @AllTables ([CompleteTableName])
EXEC sp_msforeachdb 'SELECT ''['' + [TABLE_CATALOG] + ''].['' + [TABLE_SCHEMA] + ''].['' + [TABLE_NAME] + '']'' FROM [?].INFORMATION_SCHEMA.TABLES'
SET NOCOUNT OFF;

DECLARE     [table_cursor] CURSOR FOR 
(SELECT     *
FROM        @AllTables
WHERE       [CompleteTableName] NOT LIKE '%master%' AND [CompleteTableName] NOT LIKE '%msdb%' AND [CompleteTableName] NOT LIKE '%tempdb%' AND [CompleteTableName] NOT LIKE '%model%');

OPEN [table_cursor]

PRINT N'There were ' + CAST(@CountAll AS NVARCHAR(10)) + ' tables with potential duplicate data'

FETCH NEXT FROM [table_cursor]
INTO @TableFullName

WHILE @@FETCH_STATUS = 0
    BEGIN
        SET         @SQLQuery = 'SELECT @CntAll = COUNT(*) FROM ' + @TableFullName + ' SELECT @CntDistinct = COUNT(*) FROM  (SELECT DISTINCT * FROM ' + @TableFullName + ') AS [sq] IF @CntAll > @CntDistinct SELECT @BitResult=1 ELSE SELECT @BitResult=0';

        EXEC        sp_executesql @SQLQuery, N'@BitResult BIT OUTPUT, @CntAll INT OUTPUT, @CntDistinct INT OUTPUT', @BitResult = @TableHasDuplicates OUTPUT, @CntAll = @CountAll OUTPUT, @CntDistinct = @CountDistinct OUTPUT;

        IF @TableHasDuplicates = 1
            BEGIN
                INSERT INTO @ResultsTable ([CompleteTableName], [CountAll], [CountDistinct])
                SELECT @TableFullName, @CountAll, @CountDistinct
            END;

        FETCH NEXT FROM [table_cursor]
        INTO @TableFullName
    END 
CLOSE [table_cursor];
DEALLOCATE [table_cursor];

SELECT *
FROM @ResultsTable

Обзор того, как это работает, заключается в том, что табличная переменная @AllTables использует sp_msforeachdb с INFORMATION_SCHEMA.TABLES для вывода списка всех таблиц во всех базах данных (имеется 16537 таблиц). Табличный курсор используется для хранения всех несистемных записей, а затем я использую динамический SQL для проведения подсчета и отдельного подсчета, который хранится в другой табличной переменной @ResultsTable.

ПРОБЛЕМА С НАСТОЯЩИМ РЕШЕНИЕМ

Когда я запускаю этот запрос, он будет работать в течение примерно 3 минут, а затем выдаст ошибку о том, что файловая группа tempdb PRIMARY заполнена:

Я - мой собственный администратор баз данных, и я использовал руководство Брента Озара по настройке своего экземпляра SQL-сервера, и моя база данных tempdb настроена на 8 x 3 ГБ файлов mdf/ndf (сервер имеет 8 ядер):

Эти файлы отображаются как имеющие 23997 МБ, доступные в свойствах "Общие".

МОИ ВОПРОСЫ

1. Если у меня есть около 24 ГБ свободного места в базе данных tempdb, почему в этом относительно простом запросе не хватает места в базе данных tempdb?
2. Есть ли лучший / более эффективный способ получения подсчета и четкого подсчета всех таблиц во всех базах данных?

Answer 1

Вы должны всегда учитывать конфликт до добавления файла TempDb. Добавление 7 дополнительных файлов TempDb действительно не поможет.

Если у меня есть около 24 ГБ свободного места в базе данных tempdb, почему в этом относительно простом запросе не хватает места в базе данных tempdb?

Нет, не должно. Но вы уверены, что не имеете дело с большим объемом данных или у вас нет другого процесса, работающего на SQL? Курсоры, таблицы Temp и даже переменные таблиц широко используют TempDb. Проверьте, какой объект занимает больше места в TempDb:

SELECT
    SUM (user_object_reserved_page_count)*8 as usr_obj_kb,
    SUM (internal_object_reserved_page_count)*8 as internal_obj_kb,
    SUM (version_store_reserved_page_count)*8  as version_store_kb,
    SUM (unallocated_extent_page_count)*8 as freespace_kb,
    SUM (mixed_extent_page_count)*8 as mixedextent_kb
FROM sys.dm_db_file_space_usage

Таким образом, если ваши пользовательские и внутренние объекты больше, это явно означает, что у вас мало пространства TempDb из-за курсоров и внутреннего использования SQL Server (например: промежуточные таблицы, хэш-соединения, агрегация хеша и т. Д.)

Есть ли лучший / более эффективный способ получения подсчета и четкого подсчета всех таблиц во всех базах данных?

Вы можете использовать код ниже, чтобы получить количество всех таблиц во всех базах данных

  DECLARE @Stats TABLE (DBNAME VARCHAR(40), NAME varchar(200), Rows INT)
INSERT INTO @Stats
EXECUTE sp_MSForEachDB 
        'USE ?; SELECT DB_NAME()AS DBName, 
        sysobjects.Name
    , sysindexes.Rows
FROM
    sysobjects
    INNER JOIN sysindexes
    ON sysobjects.id = sysindexes.id 
WHERE
    type = ''U''
    AND sysindexes.IndId < 2'

    SELECT * FROM @Stats

Я написал статью о рекомендации TempDb; Я хотел бы предложить вам прочитать это, чтобы понять объекты, которые могут повлиять на TempDb и как решить общие проблемы с ним. В идеале, ваш общий размер TempDb должен быть рассчитан на основе наблюдений, которые в вашем случае> 24 ГБ.

** Редактировать 1**

Если вы не уверены в обновлении статистики, используйте приведенный ниже запрос для подсчета всех таблиц. Примечание. Замените базы данных, для которых вам не нужна статистика.

    DECLARE @ServerStats TABLE (DatabaseName varchar(200), TableName varchar(200), RowsCount INT)
INSERT INTO @ServerStats
exec sp_msforeachdb @command1='
use #;
if ''#'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'',''ReportServer'')
begin
print ''#''
exec sp_MSforeachtable @command1=''
SELECT ''''#'''' AS DATABASENAME, ''''?'''' AS TABLENAME, COUNT(*)  FROM ? ;
''
end 
', @replacechar = '#'

SELECT * FROM @ServerStats

Точно так же вы можете взять разные во всех таблицах для всех баз данных с запросом ниже

    DECLARE @ServerStatsDistinct TABLE (DatabaseName varchar(200), TableName varchar(200), RowsCount INT)
INSERT INTO @ServerStatsDistinct
exec sp_msforeachdb @command1='
use #;
if ''#''  NOT IN (''master'', ''model'', ''msdb'', ''tempdb'',''ReportServer'')
begin
print ''#''
exec sp_MSforeachtable @command1=''
SELECT ''''#'''' AS DATABASENAME, ''''?'''' AS TABLENAME, COUNT(*)  FROM  (
    SELECT DISTINCT *
    FROM ?
) a  ;
''
end 
', @replacechar = '#'

SELECT * FROM @ServerStatsDistinct