Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Location
    Belgium
    Posts
    25

    Red face Unanswered: creation of indexes

    Hi,

    We have here some ASE servers configured in pages of 4 K.
    the creation of non clustered index takes around 3 hours for a table containing +/- 735 000 000 recors.
    We have seen that when we execute a query plan on a select on this table, the I/O size is 32 K for data pages.
    Now, when we execute a create index on the same table, the I/O size is 4 K for data pages.
    I think that if we can use an I/O size of 32 K for data pages, the creation of the index will be quick.

    Is it possible to configure the server to use an I/O size of 32 K for data pages during the creation of the index ?

    If yes, which parameter we need to modify ?

    Thank you in advance,

    Xavier.

  2. #2
    Join Date
    Jun 2003
    Posts
    140

    Re: creation of indexes

    Try this

    - > Create a named cache and create 16k pool in the cache, bind the table to the cache

    -> sp_sysmon begin_sample
    create index ...
    sp_sysmon end_sample

    in sysmon output check for
    -> The “Sample Interval,” for the total time taken to create the index
    -> Cache statistics for the cache used by the table
    -> Check the value for “Buffer Grabs” for the 2K and 16K pools to
    determine the effectiveness of large I/O.
    -> Check the value “Dirty Buffer Grabs,” If this value is nonzero, set
    the wash size in the pool higher and/or increase the pool size,
    using sp_poolconfig.

    am not sure this will solve u r prob ,but try to give it a go

  3. #3
    Join Date
    Nov 2003
    Location
    Belgium
    Posts
    25

    Re: creation of indexes

    I will try this and I will inform you about the result.

    Originally posted by perl
    Try this

    - > Create a named cache and create 16k pool in the cache, bind the table to the cache

    -> sp_sysmon begin_sample
    create index ...
    sp_sysmon end_sample

    in sysmon output check for
    -> The “Sample Interval,” for the total time taken to create the index
    -> Cache statistics for the cache used by the table
    -> Check the value for “Buffer Grabs” for the 2K and 16K pools to
    determine the effectiveness of large I/O.
    -> Check the value “Dirty Buffer Grabs,” If this value is nonzero, set
    the wash size in the pool higher and/or increase the pool size,
    using sp_poolconfig.

    am not sure this will solve u r prob ,but try to give it a go

Posting Permissions

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