Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149

    Cool Unanswered: Index Discussion

    Hi Folks,

    Got a topic open for debate.

    We currently have an archive table - DDL

    CREATE TABLE [dbo].[Audit] (
    [id] [int] identity (1,1) NOT NULL ,
    [col1] [char] (10) NOT NULL ,
    [col2] [char] (15) NOT NULL ,
    [col3] [int] NOT NULL ,
    [col4] [varchar] (50) NOT NULL ,
    [col5] [datetime] NOT NULL ,
    [col6] [varchar] (4000) NULL ,
    [col7] [char] (3) NULL
    )
    GO


    This table grows to about 40 million rows during the course of the month. The table has a clustered index on the id field and a non clustered index on the col2 and col3. The id column is not used in queries. At the moment we run weekly dbcc reindexes on all the indexes. We are running into a space issue on the reindex of the clustered index (copying the whole table out , ordering etc) and are considering dropping the index or changing to a non clustered index. (The DBCC utility that we have built will only rebuilt all the indexes or none at all.)

    I feel this is not a good idea and know my reasons. I would like some input as to why this might prove a bad idea.

    Will it increase page splitting? Will the table performance be impacted even if the queries are not specifically using the clustered index?

    What are the reasons for and against?

    Thanks Folks

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Why do yuo have an [id] column if you don't use it?

    I am IDENTITY, there for I am.....

    Drop the Index

    The when people start screaming, the create a non unique index...
    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
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    The when people start screaming, the create a non unique index... .... they don't scream... simply blame SQL server with no cause.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  4. #4
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149

    Smile

    I never designed the schema. Bag of pish if you ask me.

    Nevertheless before I drop the index :

    Will the table start to split pages if we lose the clustered ?

    The table is inserted into the by date order. There is no index on the datatime field but the id field clustered index maintains the date order of the table. Would go if the clustered index was dropped?

    Do the other non-clustered indexes not use the clustered as a backbone? Will the non-clustered grow if the clustered was dropped?

    Am basing my concerns over dropping this index mainly from the advice on the link below.

    http://www.sql-server-performance.co...ed_indexes.asp

    More conjecture please.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK, first, the order of data in a database has no meaning...

    Second, (and I should have said this earlier), do NO alteration in a prod environment until you tested ANY approach in DEV

    Me telling you to (and off the cuff) to just drop the index was so bad, I had to drink many margaritas to forget it....

    Well, ok, I'm always looking for an excuse....

    And no, indexes are not dependant on each other....

    And keeping an IDENTITY to make sure the dates are in the right order (did I read that right) doesn't make sense to me....

    The big question is...

    CREATE TABLE with clustered index

    Data is meant to be stored in that order...however data will be put on pages where it finds room...how much free space?

    When the table is REORG'ed it will order the data by it...

    Now the question...you drop a cluster, and then reorg....what happens?

    Don't know, I'll have to test it....

    However, I think you have bigger issues....
    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.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You know what? I wanted to do the following, and then reorg the data pages...(it's a db2 term I guess), and realized I don't know how, except to unload and load (OK, another db2 term, bcp out and bcp in)...

    Anyone?

    I've DBCC REINDEX, but it doesn't mention anything about the pages...

    got to be a way...

    Code:
    USE Northwind
    GO
    
    CREATE TABLE myTable99 (
    	  Col1 int NOT NULL
    	, Col2 char(1) NOT NULL
    )
    GO
    
    CREATE UNIQUE CLUSTERED INDEX myTable99_IX1 ON myTable99(Col1)
    CREATE INDEX myTable99_IX2 ON myTable99(Col2)
    GO
    
    sp_help myTable99
    GO
    
    INSERT INTO myTable99(Col1, Col2)
    SELECT 1, 'A' UNION ALL
    SELECT 2, 'B' UNION ALL
    SELECT 3, 'C' UNION ALL
    SELECT 4, 'D'
    GO
    
    SELECT * FROM myTable99
    GO
    
    DROP INDEX myTable99.myTable99_IX1
    GO
    
    sp_help myTable99
    GO
    
    INSERT INTO myTable99(Col1, Col2)
    SELECT 5, 'E' UNION ALL
    SELECT 6, 'F' UNION ALL
    SELECT 7, 'G' UNION ALL
    SELECT 8, 'H'
    GO
    
    SELECT * FROM myTable99
    GO
    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.

  7. #7
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149
    Thanks for the feedback Brett,

    Don't worry - Would never drop an index on a table in prod without fully testing and understanding the implications before doing so

    Hence this thread....

    Going to some testing and get back to you.

    Is there a way in T-SQL you can check the size of a specific index?

    Don't trust EM .....

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by aldo_2003
    Don't trust EM .....
    Good...

    Of a specific index?

    Anyone...

    sp_spaceused myTable99
    GO

    Will tell you the size of all....

    I'll keep looking....

    EDIT: IF this was DB2 I'd have an answer....
    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.

  9. #9
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149
    sysindexes tells you how many pages where used

    can we simply multiply this by 8kb to get the answer?

  10. #10
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    ANytime to get accurate sizes better to DBCC UPDATEUSAGE or use @UPDATEUSAGE='TRUE' in SP_SPACEUSED statements.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  11. #11
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149

    Angry

    Brett, Satya ,

    Have done a bit of testing

    Inserted 6 million rows into this table with all the indexes
    i.e 1 clustered and 2 non clustered

    Did sp_spaceused with DBCC UPDATEUSAGE
    index size = 233278kb

    Then i dropped the clustered index

    Did sp_spaceused with DBCC UPDATEUSAGE

    index size = 284104kb

    Why has the total index size gone up when I have dropped the an index?

    What is going on ??

    "And no, indexes are not dependant on each other...."???

    Anybody ?

  12. #12
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Have you performed DBCC DBREINDEX before and after CLustered Index drop?

    I've bit doubt in this regard after this it should return correct sizes.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  13. #13
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149

    Angry Re: Index Discussion

    Have done this and tested

    Still get the same result -

    i.e increse in overall index size when I drop the clustered index

    has anyone else noticed this behaviour or am I the only one.

    shame SQL Server has no T-SQL to check out the size of specific indexes

  14. #14
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Thats for sure there is no direct deal to get the result.
    http://www.sql-server-performance.com/q&a13.asp - review for information.

    HTH
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  15. #15
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149
    Thanks Satya,

    Still don't know what is happening with my indexes though

Posting Permissions

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