DiggerSite

Нагрузка на базы данных сервера

Периодически возникают вопрос, мол, какая база на сервере используется более активно, сколь активно используется tempdb. Очень простой скрипт для ответа на все эти вопросы

 
— Нагрузка на базы данных сервера, в процентах. Отдельно данные и журнал транзакций
— Dmitry Gorchilin 20160822 digger.dp.ua

select
sum(bytesread) as bytesread,
sum(bytesWritten) as bytesWritten,
sum(iostallreadms) as iostallreadms,
sum(iostallwritems) as iostallwritems,
isnull(db_name(dbid),») as DbName,
type_desc
into #db_rw
from sys.master_files mf
inner join fn_virtualfilestats(NULL,NULL) vf on mf.file_id=vf.fileid and mf.database_id=vf.Dbid
group by isnull(db_name(dbid),»),type_desc

;with sum_io as (select sum(bytesread) as bytesread,
sum(bytesWritten) as bytesWritten,
sum(iostallreadms) as iostallreadms,
sum(iostallwritems) as iostallwritems,
type_desc
from #db_rw group by type_desc)
select @@servername,dbname,#db_rw.type_desc,
100.*#db_rw.bytesread/sum_io.bytesread as ‘%bytesread’,
100.*#db_rw.bytesWritten/sum_io.bytesWritten as ‘%bytesWritten’,
100.*#db_rw.iostallreadms/sum_io.iostallreadms as ‘%iostallreadms’,
100.*#db_rw.iostallwritems/sum_io.iostallwritems as ‘%iostallwritems’
from #db_rw inner join sum_io on sum_io.type_desc=#db_rw.type_desc
order by #db_rw.type_desc,4 DESC,5 DESC

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *