Post reply

Table with high number of pages

  • May 24, 2010 at 12:48 pm

    #235394

    What's the disadvantage of having a table with high number of pages.....Does that decrease the performance of my server???

    Thanks & Regards,

    Sandeep

  • May 24, 2010 at 1:46 pm

    #1171741

    Not necessarily. A table with a lot of pages typically indicates that you have a lot of data in that table.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • May 25, 2010 at 8:45 am

    #1172115

    It is obvious to me that more pages equals lower performance in every equivalent scenario I can think of. If you are scanning you are a) having more IO and b) forcing other stuff out of the buffer pool and c) causing more locking. If you are doing index seeks the index depth will be greater for larger amounts of data thus increasing the cost of each seek/bookmark lookup.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • May 25, 2010 at 9:05 am

    #1172149

    Is there any way to decrease the number of pages other than increasing the fill factor??

  • May 25, 2010 at 9:08 am

    #1172153

    Different fill factor will make a change in number of pages.

    Removing data will also make a difference in number of pages.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • May 25, 2010 at 11:09 am

    #1172248

    For a table with a clustered index, if it is fragmented then doing a defrag operation (such as rebuilding the clustered index) could make the total number of pages lower. If a heap, then building a clustered index could do the same.

    I really have to ask why you are so focused on the total number of pages...

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • May 25, 2010 at 11:22 am

    #1172260

    We use a third party tool called precise i3....It gave me an alert message that a particular table has high number of pages(based upon the pre-defined threshold value).....that's why i was working on this issue(whether decreasing the number of pages increases the performance)....

    Thanks for all your valuable suggestion

  • May 25, 2010 at 12:20 pm

    #1172280

    You are welcome.

    I think I would change the alert on that app in regards to the number of pages being an alert.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • May 25, 2010 at 1:40 pm

    #1172321

    As pointed out , a high number of pages could cause performance issues, but without evidence to the contrary , ie Query performance metrics, that it actually is, i would not view it as a problem.


    Clear Sky SQL
    My Blog[/url]

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply