Sql-server – How accurate is the sys.partition.rows column

countpartitioningsql serversql-server-2008-r2sql-server-2012

The system view sys.partitions has a column "rows" that is the total number of rows in a given partition. For a table that is not partitioned (or only has one partition depending on how you look at it) this column gives the number of rows in the table.

I'm curious how accurate this column is and if I can use it instead of a SELECT COUNT(1) FROM TableName. I've done some experiments where create a table and add a few thousand rows, delete a few hundred, add a few thousand more etc and the count has always been dead on. However I have one table with about 700 mil rows and several indexes. The row in sys.partitions for the clustered index is again dead on, however the other indexes show some slight variations (+-20k).

Does anyone know how this row is calculated and if it is as accurate as it appears?

Best Answer

Books Online states that the rows field "indicates the approximate number of rows in this partition." I would therefore expect it to be close, but not 100% accurate, 100% of the time.

Michael Zilberstein reports an example of sys.partitions being wildly incorrect in For want of a nail. Not saying it is a common occurrence, but it is possible.

sys.dm_db_index_physical_stats contains a record_count field that appears to be more accurate, although be aware running the DMV may result in a REDO blocking issue if you run it on an instance hosting an AlwaysOn Readable Secondary Replica.

The explanation for the record_count field shows the following info:

Total number of records.

For an index, total number of records applies to the current level of the b-tree in the IN_ROW_DATA allocation unit.

For a heap, the total number of records in the IN_ROW_DATA allocation unit.

For a heap, the number of records returned from this function might not match the number of rows that are returned by running a SELECT COUNT(*) against the heap. This is because a row may contain multiple records. For example, under some update situations, a single heap row may have a forwarding record and a forwarded record as a result of the update operation. Also, most large LOB rows are split into multiple records in LOB_DATA storage. For LOB_DATA or ROW_OVERFLOW_DATA allocation units, the total number of records in the complete allocation unit.

See also Martin Smith's answer to a similar question on Stack Overflow.

Related Solutions

Sql-server – Where has sysindex.rows gone

It is still there in the compatibility view sys.sysindexes.

You can also use

SELECT OBJECT_NAME(p.object_id), SUM(rows)
FROM sys.partitions p 
WHERE index_id < 2 and OBJECTPROPERTYEX (object_id ,'IsUserTable' ) = 1
GROUP BY p.object_id

This should not be taken as in any way guaranteed accurate. It includes the effects of uncommitted transactions, forwarding pointers on a heap and can be manually updated with some undocumented parameters to UPDATE STATISTICS.

Sql-server – Large Fact table and partitioning key dilemma

I hate to state the obvious but I'd say test both scenarios and run your production queries against all 3 scenarios (scenario 1 being the current non partitioned one). The reason I state this is because I don't know what your code is querying. Do they actually have a benefit of being sorted in the base table by the identity column as opposed to the other column? For example, are your queries actually looking for the row ID often? If you're not sure, you might be surprised by getting some performance benefits. The general idea of using ID as the clustered key was for range scans, but in our case, we scan by customerID and Date, so it worked out perfectly for us, and perhaps you. Check out this article by Kim Tripp: http://www.sqlskills.com/blogs/kimberly/post/The-Clustered-Index-Debate-Continues.aspx

"What is often cited as the “reason” for IDENTITY PRIMARY KEY clustered index definitions is its monotonic nature, thus minimizing page splits. However, I argue that this is the only “reason” for defining the clustered index as such, and is the poorest reason in the list. Page Splits are managed by proper FILLFACTOR not increasing INSERTS. Range Scans are the most important “reason” when evaluating clustered index key definitions and IDENTITies do not solve this problem.Moreover, although clustering the IDENTITY surrogate key will minimize page splits and logical fragmentation due to its monotonic nature, it will not reduce EXTENT FRAGMENTATION, which can cause just as problematic query performance as page splitting. In short, the argument runs shallow "

Typically you want your clustered index to be as narrow as possible, unique, and non nullable as it get's carried into all other indexes. I have a table with roughly 10 billion rows and we partition off the datetime column, which has worked out great.

Best Answer

Related Solutions

Sql-server – Where has sysindex.rows gone

Sql-server – Large Fact table and partitioning key dilemma

Related Question