SQL Server Performance Forum – Threads Archive
Physical architecture
Hi. Today i’m running Ms SQL Server 2005 where logs are located on one physical disk and the five data-files on a second disk. As the db has grown quite a lot the last months I want to relocate the physical files. The DB today occupies 90 Gb. The 13 biggest tables takes 95% of the space where top of the list is Rank% of database142
213
37
45
55
65
74
84
93
103
112
121
131
Will it be sensible to have logs on one separate disk, indexes on separate disk and then split the tables on two separate disks (four disks altogether)? I guess that attributes like size, how often a table is accessed and which tables are accessed in parallell will decide where to place tables. Is there a tool or script to identify which tables that not should be on the same disk?
Thanks,EB
Forgot to say that this is an OLTP system. Thanks,EB
I don’t think there any tool like this… but you have to check I/O stalls…
and the following script give you list of tables/indexes and its location.. Read the following from BOL..
SQL Server 2005 Books Online
sys.dm_os_wait_stats
http://msdn2.microsoft.com/en-us/library/ms179984.aspx SELECT distinct fi.filename, fg.groupname AS [Filegroup],
so.name AS TableName,
si.name AS [Index Name],
IndexType = case when si.indid = 1 then ‘Clustered index’
when si.indid > 1 and si.indid <255 then ‘Nonclustered’
when si.indid = 255 then ‘Tex/Image Data’
end
FROM sysindexes si
JOIN sysobjects so
ON so.id = si.id
JOIN sysfilegroups fg
ON fg.groupid = si.groupid
JOIN sysfiles fi
ON fg.groupid = fi.groupid
WHERE so.type = ‘U’
and si.name not like ‘_wa_sys%’
order by so.name MohammedU.
Moderator
SQL-Server-Performance.com
Ok, thanks that helps. Any comments on using four disks as I described earlier? Thanks,EB
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=16995 Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
]]>