Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 1 down vote accepted

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:

share|improve this answer

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.

share|improve this answer
yes you are right, there is no performance problem now but I just want to prevent such thing before it happens in the future. – Server System Specialist Apr 18 '11 at 3:40

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_bytes_written as BYTESWRITTEN, 
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
from g 
group by database_name
order by BYTES_WRITTEN desc

-- Isolate top waits for server instance since last restart or statistics clear
(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
  '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
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
share|improve this answer
cool, thanks for sharing the script Sankar ! – Server System Specialist Apr 18 '11 at 3:41

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.