| Author |
Topic |
|
Lumbago
Norsk Yak Master
3271 Posts |
 Posted - 2011-05-16 : 04:44:32
|
| Hi,do you do _file_ defrags of your database files on occasion? If so, how do you prefer to do it?I'm working with a client that has a 50GB database which has had 1MB unrestricted filegrowth running since 2004. I haven't actually checked the fragmentation level yet but I think it's safe to say that it's fairly fragmented. I guess a backup / delete database / restore would do the trick but do you regard it as necessary? Index defrag/rebuild is running like it should...- LumbagoMy blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/ |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-05-16 : 06:28:49
|
| Is it causing an issue?I would check the fragmentation first.I have never done this as a mnatter of course - but then anything big usually has dedicated disks or san. 50GB I usually wouldn't worry about the hardware too much.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-05-16 : 06:38:27
|
quote:
Originally posted by nigelrivett
Is it causing an issue?
To be honest I have no idea. I know how to check the fragmentation for a single file (using Contig.exe from sysinternals) but not if it's causing an issue. The problem is that this database is having all sorts of issues and I'm trying to figure out how to increase performance without stepping on the 3rd party vendors toes. I've already stepped on them a few times pointing out that transferring 200k rows to another sql server *usually* doesn't require a 2 day time frame. But they're busy doing something else so my advice falls to deaf ears  - LumbagoMy blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/ |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2011-05-16 : 07:39:22
|
Well, if the vendor do regulary shrinks, I would be bothered with fragmentation.I use contig on a near daily basis for some databases. Within a 2 day timeframe a 10GB database file gets fragmented into 3k fragments.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2011-05-16 : 09:32:04
|
| It sounds like the database file could have as many as 50,000 fragments, so it probably wouldn’t hurt to do a physical file defrag if you have enough space on the disk and time to do it.It would at least eliminate that as a possible cause for poor performance.CODO ERGO SUM |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-05-16 : 09:48:49
|
quote:
Originally posted by Michael Valentine Jones
It sounds like the database file could have as many as 50,000 fragments, so it probably wouldn’t hurt to do a physical file defrag if you have enough space on the disk and time to do it.It would at least eliminate that as a possible cause for poor performance.
This is more or less my thoughts as well. Unplugging the production database to do this defrag will be a tough one to get through though. Hmm...- LumbagoMy blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/ |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2011-05-16 : 10:56:57
|
quote:
Originally posted by Lumbago
quote:
Originally posted by Michael Valentine Jones
It sounds like the database file could have as many as 50,000 fragments, so it probably wouldn’t hurt to do a physical file defrag if you have enough space on the disk and time to do it.It would at least eliminate that as a possible cause for poor performance.
This is more or less my thoughts as well. Unplugging the production database to do this defrag will be a tough one to get through though. Hmm...- LumbagoMy blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/
I believe the Diskeeper server versions will defragment a database file while it is online, but they do cost money, $350 to $600 US, depending on edition:http://www.diskeeper.com/business/diskeeper/CODO ERGO SUM |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2011-05-16 : 15:59:40
|
Contig also can defrag online.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-05-18 : 02:11:38
|
| I did a contig defrag on my local test database while it was online but I didn't test connectivity and such during the operation. And the database showed no sign of problems when querying afterwards.Peso: you said you do this on a near daily basis; is this on a live production system? Can you share some of your experiences? And do you know if corruption can/is prone to happen if you defrag while the db is running?- LumbagoMy blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/ |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2011-05-18 : 04:28:58
|
I trust contig.exe since it is written by Mark Russinovich. He was later employed by Microsoft.The Contig is absolutely failsafe since it relocates the block first and verify it BEFORE reassigning the cluster chain pointer.I have noticed a about 15% CPU overhead during the defragmentation. And a mere 5 percent slower response from database.But only when quering the table that is under defragmentation. All tables not currently defragmented are not affected.This is on a live production system. As I wrote earlier, the vendor does SHRINK all over the place. AFAIK after every major query.The database is heavily severe fragmented on a daily basis.What the end users have noticed is a minimum 200% faster reponse from the database after I have started to defrag the database.Some queries are now 15 times as fast compared to before. I realize this is an extreme but their (end users) satisfaction is enormous.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2011-05-18 : 04:30:45
|
Oh, I forgot to write that there is no mystery in the contig.exe.It uses only documented Windows API calls.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-05-18 : 04:35:03
|
| Wow, this is fantastic feedback Peso! I'm gonna recommend a contig defrag on the basis of your experiences :)And btw: "...the vendor does SHRINK all over the place. AFAIK after every major query". Sounds like a perfect candidate for the "That darn vendor"-thread - LumbagoMy blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/ |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2011-05-18 : 09:04:42
|
quote:
Originally posted by Peso
Contig also can defrag online.
N 56°04'39.26"E 12°55'05.63"
How do you schedule it to run? Or does someone do it manually?CODO ERGO SUM |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-05-18 : 09:08:43
|
quote:
Originally posted by Lumbago
Wow, this is fantastic feedback Peso! I'm gonna recommend a contig defrag on the basis of your experiences :)And btw: "...the vendor does SHRINK all over the place. AFAIK after every major query". Sounds like a perfect candidate for the "That darn vendor"-thread - LumbagoMy blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/
Lol - had a db2 system where the developers did the equivalent of an an update statistics after every update query and again at the end of SPs. It could take half an hour on multi terrabyte tables. They wouldn't belive me when I said it was causing an issue (like locking system tables).==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-05-18 : 09:22:10
|
quote:
Originally posted by Michael Valentine Jones
How do you schedule it to run? Or does someone do it manually?
It's a simple 100k .exe-program that takes the filename(s) as a parameter (it supports wildcards). Scheduling is done like any other task; with sql server agent or scheduled tasks in windows. I'd do it manually though.- LumbagoMy blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/ |
|
|
|
|
File fragmentation