Forum
Bildirimler
Hepsini Temizle
SQL Server
4
Yazılar
3
Üyeler
0
Reactions
633
Görüntüleme
Konu başlatıcı
Merhaba,
Ben Sql server 2005 express'in;
Diskte en çok yer kaplayan tablolar.
Ram'de en çok yer kaplayan tablolar.
Cpu'ya ne kadar yük bindirdigi.
Bunları nasıl kontrol edebilirim, konu ile ilgili yardımlarınızı rica ediyorum.
Teşekkürler,
Gönderildi : 14/06/2013 21:02
CPU için Task Manager en iyisi bence.
Disk için aşağıdaki scripti kullanabilirsin. Tabi SQL Server Management Toollu yüklemen lazım.
create table #tmp1 (
Servername SysName,
DBName SysName,
object_id int,
SchemaName sysName,
TableName sysName,
TableSize_KB int,
row_count int
)
create table #tmp2 (
Servername SysName,
DBName SysName,
object_id int,
SchemaName sysName,
TableName sysName,
row_count int
)
exec sp_msforeachdb 'use ?;
if DB_ID()>4 begin
insert #tmp1
select @@ServerName,DB_Name(),ps.object_id,
schema_name(o.schema_id) as SchemaName,
o.name as TableName,
SUM(ps.reserved_page_count)*8 as TableSize_KB,
0 as row_count
from sys.dm_db_partition_stats ps
join sys.objects o on o.object_id = ps.object_id
where o.type=''U''
group by ps.object_id,o.schema_id, o.name
insert #tmp2
select @@ServerName,DB_Name(),ps.object_id,
schema_name(o.schema_id) as SchemaName,
o.name as TableName,
sum(ps.row_count) as row_count
from sys.dm_db_partition_stats ps
join sys.objects o on o.object_id = ps.object_id
where o.type=''U'' and ps.index_id in (0,1)
group by ps.object_id,o.schema_id, o.name
end
';
update t1
set t1.row_count=t2.row_count
from #tmp1 t1
left join #tmp2 t2 on
t2.ServerName=t1.ServerName and
t2.DBName=t1.DBName and
t2.object_id=t1.object_id
select Servername , DBName , convert(char(8),getdate () , 112 )Tarih ,object_id , SchemaName , TableName , TableSize_KB , row_count from #tmp1
order by TableSize_KB desc
drop table #tmp1
drop table #tmp2
Gönderildi : 18/06/2013 13:35
Buda ram için olan script
SELECT COUNT(*) AS cached_pages_count ,
name AS BaseTableName, IndexName,
IndexTypeDesc
FROM sys.dm_os_buffer_descriptors AS
bd
INNER JOIN
(
SELECT
s_obj.name,
s_obj.index_id,
s_obj.allocation_unit_id, s_obj.OBJECT_ID,
i.name
IndexName, i.type_desc
IndexTypeDesc
FROM
(
SELECT OBJECT_NAME(OBJECT_ID) AS name,
index_id ,allocation_unit_id, OBJECT_ID
FROM sys.allocation_units AS
au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND (au.type = 1 OR au.type = 3)
UNION ALL
SELECT OBJECT_NAME(OBJECT_ID) AS name,
index_id, allocation_unit_id,
OBJECT_ID
FROM sys.allocation_units AS
au
INNER JOIN sys.partitions AS p
ON au.container_id = p.partition_id
AND au.type = 2
) AS s_obj
LEFT JOIN sys.indexes i ON i.index_id = s_obj.index_id
AND i.OBJECT_ID = s_obj.OBJECT_ID ) AS obj
ON bd.allocation_unit_id =
obj.allocation_unit_id
WHERE
database_id = DB_ID()
GROUP BY name, index_id, IndexName,
IndexTypeDesc
ORDER BY cached_pages_count DESC;
GO
Gönderildi : 18/06/2013 13:47
Sql 2005 için performans dashboard reportsu http://www.microsoft.com/en-us/download/details.aspx?id=22602 indirip rapor alabiliyorsunuz.
Gönderildi : 07/07/2013 00:10
