How can I migrate the default four system DB into another partitions?

Question

Why can't I see any detach / attach command in the 4 SQL Server system DB objects? I want to move it into separate partition for each database files and the log files for greater performance.

Am I supposed to do that or just leave it on C:\Program Files\Microsoft SQL Server\MSSQL10.(dbinstance)\MSSQL\DATA?

Answer 1

You can't use attach/detach on the system databases but rather you use alter database statements coupled with changing the startup parameters pointing to the new location.

Here is a guide from MS on how to move the system databases:

http://msdn.microsoft.com/en-us/library/ms345408.aspx

Answer 2

It's not likely that the location of the system databases is causing or will cause performance problems.

You should answer 2 questions before undertaking this task:

1. Is there a performance problem?

2. Is the location of the system databases the cause of said performance problem?

If you can't answer yes to both questions then my suggestion is to leave things as they are.

Answer 3

To complement the above 2 already answers, the only database that you should be concerned is the tempdb. The below query will get the amount of IO per database and its a good indicator if tempdb is doing more work when compared to others. If Yes, moving tempdb to separate spindles will help. You should also check if you have allocation bottleneck in tempdb using WAIT STATISTICS (query below) and use TF 1118 and multiple tempdb data files as necessary. So you might get good responses if you add more details. You can tweak the first query a bit to look at the IO stats per file level also to get more granularity.

--Per database write cost with running total
With g as
(select db_name(mf.database_id) as database_name, mf.physical_name, 
left(mf.physical_name, 1) as drive_letter, 
vfs.num_of_writes, 
vfs.num_of_bytes_written as BYTESWRITTEN, 
vfs.io_stall_write_ms, 
mf.type_desc, vfs.num_of_reads, vfs.num_of_bytes_read, vfs.io_stall_read_ms,
vfs.io_stall, vfs.size_on_disk_bytes
from sys.master_files mf
join sys.dm_io_virtual_file_stats(NULL, NULL) vfs
on mf.database_id=vfs.database_id and mf.file_id=vfs.file_id
)
select 
    database_name
    , sum(BYTESWRITTEN) as BYTES_WRITTEN
    , Percentage = RTRIM(CONVERT(DECIMAL(5,3),sum(BYTESWRITTEN)*100.0/(SELECT SUM(BYTESWRITTEN) FROM g)))+'%'
from g 
group by database_name
order by BYTES_WRITTEN desc

-- Isolate top waits for server instance since last restart or statistics clear
WITH Waits AS
(SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,
    100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
    ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
 FROM sys.dm_os_wait_stats
 WHERE wait_type NOT IN( 'BROKER_TASK_STOP', 'BROKER_TRANSMITTER',
  'SQLTRACE_BUFFER_FLUSH', 'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT', 'BROKER_EVENTHANDLER',
    'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT',
  'LAZYWRITER_SLEEP', 'SQLTRACE_WAIT_ENTRIES')) -- filter out additional irrelevant waits
SELECT W1.wait_type, 
  CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
  CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
  CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM Waits AS W1
INNER JOIN Waits AS W2
ON W2.rn <= W1.rn
GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
HAVING SUM(W2.pct) - W1.pct < 95; -- percentage threshold