Detaching a database

[Picha Pie]

I want to move the log files of a database to another drive to get more performance on our sql server. I'm about to detach the database but need to know if I need to mark the Update statistics prior to detach.

[rmiao]

Not necessary.

[Picha Pie]

Done. Now what's the best way to see the improvements of moving the ldf files in Performance monitor. Which one do I need to monitor?

[skhanal]

You can compare the Disk Queue length, writes/sec, reads/sec for the disk.

[Picha Pie]

I'm running the monitor right now. Do you also suggest defraging the server, I don't remember running since we started using this server in 2001.

[skhanal]

I hardly do that myself as my servers are dedicated to sql server and data file/log file drives don't anything except sql files. In that case there is no fragmentation on those disks.

Your situation may be different.

[Picha Pie]

I've asked around about the server and what other functions it did in the past before I got here but they said its been dedicated as a sql server and nothing else. in that case i don't need to defrag it as well. i'll take their word for it. tks.

[rmiao]

You may need check table fragmentation with 'dbcc showcontig' instead, defrag them by rebuilding clustered index if necessary.

[Picha Pie]

I'll try to run this now. What would be an acceptable Scan Density..I've read that anything below 90% will benefit from defragmentation.

[rmiao]

Should as close to 100% as possible.

[Picha Pie]

I can see a lot of the tables from master and tempdb to have low scan density 12-15%, is this by design?

[rmiao]

Don't worry system dbs.

[Picha Pie]

so i just need to worry on the results from the company databases then?

[rmiao]

That's right.

[skhanal]

Master and msdb tables fragmentation percentage will be higher because of their size. If a table only has two pages and if they are in non contiguous location you gor 50% fragmentation.