Вы можете использовать счетчик
Data File(s) Size (KB) ( Размер файла данных) Монитора производительности (
Performance Monitor ), связанный с экземпляром
tempdb для объекта производительности базы данных. Использование этого счетчика - хороший способ узнать, когда файл
tempdb начинает расти. Вы можете также получить доступ к этим данным непосредственно из SQL Server , выполнив запрос к таблице
master ..
sysperfinfo , которая материализует все счетчики монитора производительности, экспортируемые SQL Server . Во-вторых, Вы можете периодически проверять вывод
sp_spaceused на
tempdb . И, наконец, Вы можете использовать функцию
fn_virtualfilestats () , чтобы отследить ввод/вывод, который SQL Server записывает в файлы базы данных, которые использует
tempdb . Если
tempdb растет, вероятной причиной является физический ввод/вывод, связанный с определенными запросами. В этом случае контроль данных
fn_virtualfilestats поможет уменьшить список «подозрительных» запросов, вызывающих рост
tempdb . Любой из этих методов позволяет отслеживать рост tempdb в течение конкретного периода времени.
Чтобы найти связь роста tempdb с запросами его вызывающими, Вы можете запустить трассу SQL Server Profiler в процессе слежения за ростом tempdb . Нет никакого непосредственного способа узнать, какой именно запрос вызывает тот или иной прирост tempdb , но Вы можете сделать некоторые предположения, которые помогут Вам отыскать запросы, потенциально являющиеся виновниками этого роста. Основное предположение состоит в том, что любой запрос, который может заставить tempdb расти большими объемами, например, такими, о которых сообщал выше администратор баз данных, будет выполняться значительно дольше по сравнению с типичными запросами к данной базе. Эти запросы, вероятно, покажут также относительно высокое значение в столбце данных WRITES трассы в Profiler . Обнаружение операторов SPOOL в плане выполнения запроса является еще одним признаком того, что SQL Server выполняет большое количество операций записи в tempdb . Оператор SPOOL означает, что SQL Server записывает промежуточный результирующий набор во вторичную память (tempdb) , чтобы впоследствии воспользоваться этими данными для реализации плана.
Если Вы получили данные, которые помогли выяснить, когда файлы tempdb росли или наблюдался интенсивный ввод/вывод, а также Вы получили список запросов, выполняемых в тот же самый период времени, Вам следует проанализировать запросы из этого списка. Ищите запросы, время начала или окончания которых согласуется с периодом высокого роста, ограничиваясь далее теми запросами, которые потребляли много времени при исполнении (по крайней мере, несколько сотен миллисекунд) и демонстрировали относительно высокое количество операций ввода/вывода (по сравнению с другими запросами, выполнявшимися в то же время). Этот анализ даст Вам короткий список запросов, которые могли вызывать чрезмерный рост tempdb .
Источник
ps если сообщения являются ответом на ваш вопрос, отмечайте его как ответ(это касается не только этого треда)
