Many customer have many databases in one instance. To get the databases with the highest load of reads, writes or iosatll we can use this code:
USE MASTER
DECLARE @DBID integer
create table #Utable (dbid int not null,
FileID int Null,
[Timestamp] bigint null,
NumberReads bigint null,
Numberwrites bigint null,
BytesRead bigint null,
BytesWritten bigint null,
IostallMs bigint null,)
declare Database_cursor cursor
for
select dbid from sysdatabases order by DBID
open Database_cursor
DECLARE @SQLString NVARCHAR(500)
fetch next from Database_cursor into @DBID
while (@@fetch_status <> -1)
begin
SET @SQLString = 'insert into #Utable select * from ::fn_virtualfilestats(' + convert(varchar,@dbid ) + ',1)'
exec(@SQLString)
fetch next from Database_cursor into @DBID
end
close Database_cursor
deallocate Database_cursor
select db_name(dbid)as DatabaseName,numberreads,numberwrites,(bytesread/1024)/1024 as MBytes_Read,(byteswritten/1024)/1024 as MBytes_Written,iostallms from #Utable order by iostallms desc
select db_name(dbid)as DatabaseName,numberreads,numberwrites,(bytesread/1024)/1024 as MBytes_Read,(byteswritten/1024)/1024 as MBytes_Written,iostallms from #Utable order by MBytes_Written desc
select db_name(dbid)as DatabaseName,numberreads,numberwrites,(bytesread/1024)/1024 as MBytes_Read,(byteswritten/1024)/1024 as MBytes_Written,iostallms from #Utable order by MBytes_Read desc
drop table #Utable
No comments:
Post a Comment