SQL Server Performance Forum – Threads Archive
Physical architectureHi. 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 database
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?
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
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’
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.
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
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.