Search

Thursday, July 28, 2011

Code to list usage per database in one instance

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