May 24, 2003 at 12:00 am
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sjones/wheredoiwanttogotodayrealtimedefragging.asp
August 11, 2003 at 7:19 am
Hi Steve
I cant see this happening really. I not a mathmatician or a statistician, but I cant see the real benefits for this sort of technology. There are so many levels of fragmentation its not funny, at the OS, db pages in the file-group, buffer cache, ram, you name it, all managed by complex data structures to speed/cache/buffer this inevitability. There is not doubt its important, but not to the level you mention in the article. Even in busy (normal) oltp systems, it would be hard pressed to deal with the DML dynamics of the dbms and its structures, let alone finding free cpu time to move pages around. In this case, I would say the investment other data structures, the optimizer, the recommendation to the DBA on what is really happening with the user interaction with the data store more relative to building and maintaining the dbms.
Either way, interesting thoughts 🙂
I tell you what we are missing in SQL Server, buffer cache analysis of some nature to really tell the DBA what the hot spots are, perhaos stats in obj "thrashing" etc.
Cheers
Ck
Chris Kempster
Author of "SQL Server 2k for the Oracle DBA"
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
August 11, 2003 at 12:45 pm
The major issue I see is the higher potential for blocking processes. Some systems just have too many transactions to be feasible and those that don't so few there is really no need. The fact now you can UNFRAG as opposed to REBUILD indexes is a major plus and moves toward smoothing this out but again I don't see feasible.
August 17, 2003 at 7:36 pm
Apologies for the delay, was on vacation.
I have to disagree. A low priority process, that moved minimal data in real time, could be a real boost to performance. How often is your CPU pegged? If it's running at 60% constantly, it's busy and you might want to look at an upgrade, but another 5% to move data isn't much more of a load. Also, this would be mostly IO, and if you minimzed the amount then it shouldn't be an impact. I have to say that the majority of the time, meaning of the 24 hours each day, the IO is pretty low on my SQL boxes. It jumps up at times, but not substantially enough that I think a few pages being constantly moved would be an issue.
Plus there could even be a limiter to suspend this if the CPU goes above some value, like 75%.
Steve Jones
September 10, 2003 at 1:57 am
I've seen an article somewhere describing how existing file systems such as FAT & NTFS are going to be replaced by databases. The argument was that the existing file systems are straining to cope with todays multi GB/TB storage and that the use of DBMS would be the next logical step.
Wouldn't this mean that a defragger would almost have to be built into the DBMS whether it was automatic or not.
The comment I would make on auto-defragging would be that it has to be an option that can be switched on or off by the DBA.
September 10, 2003 at 9:43 am
Agree with that, but for most systems, most people, sparing a few constant I/Os along with 5% of the CPU might be a big benefit.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
September 11, 2003 at 1:40 am
The ability to prioritise the work that SQL Server does is what is really required for Defragmenting and other maintenance tasks.
The existing INDEXDEFRAG doesn't cause blocking, but generates vast amounts of I/O, slowing down the rest of the system. If this could be prioritised such that it used 'spare' I/O bandwidth and/or CPU, continuous defrags would be possible.
The ability to prioritise could also be used for other background tasks e.g. Update Statistics, or to prioritise OLTP transactions over Reporting etc etc.
The existing ability to run a SQL Agent job when the CPU is idle doesn't really help in a long running task like defragmenting, by the time its under way, the CPU may no longer be idle.
August 19, 2004 at 5:33 am
I think this would have to be a natural progression from the existing IDLE status jobs - basically set an ON point and an OFF point for your resources (monitor I/O, CPU, MEM etc)
you could then have a histerysis situation where the jobs start if resources fall to idle a specified time, and then continue to run unless things start getting hot (not using the same threshold for off and on)
the ability to break defrag jobs down into smaller chunks would then fit nicely into this scheme by allowing the process to stop mid table.
in this way you'd get a constant controllable defrag process that reduces the need for massive defrag exercises
MVDBA
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply