Results 1 to 15 of 15
  1. #1
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696

    Unanswered: defrag table with no clustered idx

    Is there any easy way to defrag a table with no clustered idx other than bcp out then back in ? (SQL 2000)

    Actually I have a 100GB db that I deleted data from other tables, about 10GB worth, but access to the table in question (38GB) seems to have slowed down dramatically, Or does the entire disk need to be defragmented and a bcp out/in would be a waste of time on this table ?

    I run an index defrag every night, reindex weekly.

    Thanks.
    Last edited by PMASchmed; 08-22-07 at 14:38.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Do you run

    DBCC INDEXDEFRAG

    ????

    If so what messages do you get?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Run it every night on all indexes, it runs via an step in an agent job with no reported errors, so I assumed it defragged all indexes successfully

    I have sprocs that run the defrag and reindex jobs.

    they are the isp_DBCC_DBREINDEX and isp_DBCC_INDEXDEFRAG sprocs that are available pretty much everywhere.
    Last edited by PMASchmed; 08-22-07 at 15:03.

  4. #4
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    the Disk Defragmenter shows a lot of defragmented files on the drive that contains the db mdf files, so I was assuming an o/s level defragment must be performed to resolve this issue. Am I correct in assuming so ?

  5. #5
    Join Date
    Aug 2007
    Posts
    5

    Defrag - Twice

    Totally correct dude. You need to defrag your disk drive FIRST (very important), THEN defrag your databases. If you don't do both of them, AND do them both in this order, you can suffer badly degraded performance, as the indexes will get rebuilt in order, then scattered across the disk by the disk defrag, effectively randomising their physical order on the disk surface, which can massively increase track-to-track seek times, the enemy of every DBA.

    You can do all this manually, but it gets to be a real chore with dozens of servers - fine if you're a developer with a single box, but for the rest of us it's a no-no. If you have the cash, I recommend you save yourself a heap of time by using the server version of Diskeeper (http://www.diskeeper.com/) to run the file level disk defrag once a month ( you can reduce the need for this by sizing/growing your databases so that they're not continually growing, as this causes file fragmentation). Then run Visual Defrag (http://www.visualdefrag.com/) against all your indexes once a week to check them and keep them ordered and running efficiently - this tool's cool as it only defrags the indexes that need doing instead of all of them, which can take an age if you have loads of big indexes. Both decent tools with good scheduling automation (did I mention you probably want to do disk and database defrags at night when no one's using the system and no jobs are running), not too expensive, and both companies are solid on their technical support. Obviously you'll need to adjust the frequency that you run jobs on a per server/database basis according to the usage patterns, but these tools will at least analyze your server and tell you if it needs doing without having to plough through loads of info.
    Last edited by Indexer; 08-23-07 at 04:56.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    There's no point running a SQL Server defrag on a heap. Logical fragmentation is when the pages are out of logical order. There is no logical order to a heap. There are other sorts of fragmentation (such as page denisity) but heaps don't page split either, although you do get off-page pointers.

    There is no good reason I know for not having a clustered index. How come you don't have one?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Aug 2007
    Posts
    5
    "There's no point running a SQL Server defrag on a heap. Logical fragmentation is when the pages are out of logical order"

    Just to clarify that, when we're talking about the logical fragmentation, we're refering to the index pages being out of logical order, which in the case of a clustered index also happens to be the data pages, as the clustered index is formed by re-ordering the pages that contain the data (which is why you can only have one per table)

    As for running a defrag on a heap (table with no indexes), totally correct - it's won't take very long as there's nothing to defrag ! As pootle flump said, there's rarely a good reason not to have a clustered index - the PK is often a good candidate if it is used in queries and increments sequentially, go for that as you won't suffer page splits as nothing will ever be inserted between records in a page that's already ordered.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    A free alternative for reorganising\ rebuilding indexes is to code it yourself e.g. http://weblogs.sqlteam.com/tarad/arc.../17/60176.aspx
    This is how we do it in our shop.

    Indexer - we do have some issues with physical fragmentation - is diskkeeper specifically for SQL boxes? For example will it handle defragging the mdf & ldf files automajically (for example with an option to stop & start the service)?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Aug 2007
    Posts
    5

    Diskeeper

    No, it's not specifically for SQL boxes, but as you're fundamentally just defragging another Windows file, which is what Diskeeper does, it does the job. It doesn't control service start/stop events, however, although SQL Server does lock the database files to other applications, Diskeeper will defrag them in-situ without stopping and re-starting the SQL Server boxes. Personally, I prefer to stop and start the db engine, you can do this with a Windows scheduled task (or an AT task), using Service Control Manager - just type sc /? at a command prompt and take it from there.
    The only thing you have to be aware of with Diskeeper is that it'll obviously impact disk I/O big time (this is unavoidable), so avoid busy SQL Server periods when you do it.

    The info at sqlteam is useful, but the only problem is it can't work on SQL Server 2000, which is what the majority of boxes out there still are That's where Visual Defrag (or Idera's SQL Defrag Manager) comes into its own, as they'll handle both SQL Server versions, as well as SQL Server 2008, I believe.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ah - then you just want a version using the old syntax. This is supported, though deprecated, on 2005. The same principle - just different syntax. I have not checked 2008 out yet.
    http://weblogs.sqlteam.com/tarad/arc...1/04/3933.aspx

    Ta for the info re diskeeper.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Aug 2007
    Posts
    5
    No problem Yeah, your problem there is that although you can still do the defrag, there is no old syntax in 2000 for getting the info to check if the indexes need defragmenting in the first place, as there are no DMVs in 2000 - you're back to horrible old console commands, which are a pain in the ass to trawl through if you've got more than a dozen tables. That's the main reason for me using third party tools, as the SQL Server output is slow to go through each time you decide to defrag.

    You also need to be aware that the DMVs aren't always current, and if you want the most up to date info rather than a snapshot from the last time SQL Server checked, you need to specify it in the DMV parameters, which will slow down the server - it's effectively scanning the leaf levels of the index structures to update the DMV, just as showcontig used to.

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Indexer
    Yeah, your problem there is that although you can still do the defrag, there is no old syntax in 2000 for getting the info to check if the indexes need defragmenting in the first place, as there are no DMVs in 2000 - you're back to horrible old console commands, which are a pain in the ass to trawl through if you've got more than a dozen tables. That's the main reason for me using third party tools, as the SQL Server output is slow to go through each time you decide to defrag.
    What do the third party tools use?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by pootle flump
    There's no point running a SQL Server defrag on a heap. Logical fragmentation is when the pages are out of logical order. There is no logical order to a heap. There are other sorts of fragmentation (such as page denisity) but heaps don't page split either, although you do get off-page pointers.

    There is no good reason I know for not having a clustered index. How come you don't have one?
    This is a 38GB table with 4K record size and 9+million rows. I was thinking of adding an identity column as a PK. This "Heap" (perfect term for this table) existed before I started at this godforsaken place.

    Performance is much better today for some reason. I need to stage this table, add the ID/PK and see if it does anything to degrade performance. Outside of an ID PK, the natural PK may be a ridiculously sized composite index.

    I've bcp'd out/in this table before and performance has improved 2-3 fold, it just a long operation and I really have no window to do so, except on a Sunday, and you know how we all love to work on Sundays.

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Just in case you thought I made it up - a Heap is the proper name for a table with no clustered index. It is an excellent name though

    4K record sizes huh? You defo need a clustered index. Remember - clustered index is not the same as a PK. Is this a heavy insert and\ or update table? In particular do records tend to get inserted quite small and then have lots of data updated in them to swell them up to 4k? Heavy reads? You know what - fancy posting the DDL?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by pootle flump
    Just in case you thought I made it up - a Heap is the proper name for a table with no clustered index. It is an excellent name though

    4K record sizes huh? You defo need a clustered index. Remember - clustered index is not the same as a PK. Is this a heavy insert and\ or update table? In particular do records tend to get inserted quite small and then have lots of data updated in them to swell them up to 4k? Heavy reads? You know what - fancy posting the DDL?

    36K rows a day inserted in the wee hours, then many updates. It is used for DSS, but 2-3 batch loads a day from our securitymaster with virtually no window for maintenance, except our backup processes. I know about the heap definition, just fits this in another way.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •