Page fullness in SQL server: Is higher always better?

sql sql-server fragmentation
Biztalk Amateur picture Biztalk Amateur · May 15, 2014 · Viewed 15.2k times · Source

So I've got a very frequently-run query on my SQL server instance that's generating a lot of wait time. On examining the Plan, I was pointed in the direction of a clustered index seek that's accountable for 93% of the cost of the whole operation.

Examining the clustered index, I discovered that while it has 0% fragmentation listed, it has a page fullness value of only 23%. From all the research I've done, I can't really find any indication of why you would want page fullness to be low, and am anticipating that I'll want to do an index reorganize operation to set the value to something more like 90%. (This table is very frequently read from and written to, and I am led to believe too high a page fullness value creates slowdown during write operations, hence why I don't set it to something like 99% or 100%.)

My question is this: Is there any reason I shouldn't reorganize the index and set the page fill factor to 90% or so? Any downside to doing that? I need this query, and the SQL text itself has already been optimized through Lync.

Answer

Related questions

How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More
OR is not supported with CASE Statement in SQL Server

The OR operator in the WHEN clause of a CASE statement is not supported. How can I do this? CASE ebv.db_no WHEN 22978 OR 23218 OR 23219 THEN 'WECS 9500' ELSE 'WECS 9520' END as wecs_system

Read More