Table with high number of pages

Question

Table with high number of pages

Sandy2704

SSCrazy

Points: 2156
More actions
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

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

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

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

Sandy2704 SSCrazy Points: 2156 More actions · Answer 3

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

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

Sandy2704 SSCrazy Points: 2156 More actions · Answer 6

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

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

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 8

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]