locked
How do you find the last uniquifier that was used for "null" values in a clustered index column? RRS feed

  • Question

  • We have had a couple of sites now that have had the error message basically stating we had run out of uniquifiers on this one table's clustered index.

    In this table there is a tremendous number of "null" values generated on the clustered index column that are eventually deleted from the table leading to exhaustion of the uniquifiers over time. 

    I did some testing on a small table and used the dbcc ind and dbcc page to find out how uniquifers worked. I still haven't figured out nor have my web searches been able to find a way to find the last used "uniquifier which we need to find in order to prevent this error from occurring at other sites.  From my testing I understand how uniquifiers work and I realize that on this table's column I would have more than "one" last uniquifier because the column allows non-unique values other than just nulls.  I only need to find the last uniquifier for the "null" values in the column. 

    I would appreciate any help to find the last used uniquifier. 

    Thanks


    Sue

    Sunday, September 29, 2019 8:40 PM

Answers

  • One method is to use the undocumented %%physloc%% virtual column to return the file, page, and slot of the last row with the specified key for use in DBCC PAGE. The query below filters for a NULL key value using a deliberately non-sargable expression, which I found to resulted in a backwards scan in my test and essential to get the highest uniqueifier value. Check your execution plan to verify an ordered backwards scan of the clustered index is used.

    SELECT TOP (1) *, %%physloc%%
    FROM dbo.YourTable
    WHERE ISNULL(YourKeyColumn, NULL) IS NULL
    ORDER BY YourKeyColumn DESC;
    Running out of uniqueifiers indicates a possible design problem. 


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com



    Sunday, September 29, 2019 10:31 PM

All replies

  • One method is to use the undocumented %%physloc%% virtual column to return the file, page, and slot of the last row with the specified key for use in DBCC PAGE. The query below filters for a NULL key value using a deliberately non-sargable expression, which I found to resulted in a backwards scan in my test and essential to get the highest uniqueifier value. Check your execution plan to verify an ordered backwards scan of the clustered index is used.

    SELECT TOP (1) *, %%physloc%%
    FROM dbo.YourTable
    WHERE ISNULL(YourKeyColumn, NULL) IS NULL
    ORDER BY YourKeyColumn DESC;
    Running out of uniqueifiers indicates a possible design problem. 


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com



    Sunday, September 29, 2019 10:31 PM
  • Hi Dan,

    Thank you for the reply, I looked up 'physloc' and found an undocumented function that outputs what I need in a human readable format. I also looked at the execution plan and it shows a backward scan. 

    Select top (1) *

    from [TestDB].[dbo].[tblTest]

    CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%) AS FPLC --(undocumented function to get physical location of page based on filter)

    where ISNULL(CreateDate, NULL) IS NULL

    order by  CreateDate  Desc

    Using this query I can get file id, page id, and slot id to use in dbcc page. 

    A few more questions: have you run a query like this on a very large table on a production OLTP system? If yes, what would you need to watch for in terms of resources i.e. cpu, memory, or IO? We were told by MS support that the value of uniquifier could be increased from just positive [int] value but so far neither my testing or searches have proven that. MS support will not supply any details on how this is supposed to work. Do you know if the uniquifier value can be "negative" or "bigint'? Just curious. 

    Yes, the index design of this table is awful and I've tried to get development to take a look at the table indexes before this for other reasons. I proposed using the primary key as the clustered index and making this column a non-clustered index but I heard from someone that development is proposing a filtered index which makes no sense to me. 

    Thanks



    Sue

    Monday, September 30, 2019 3:03 PM
  • have you run a query like this on a very large table on a production OLTP system? If yes, what would you need to watch for in terms of resources i.e. cpu, memory, or IO? We were told by MS support that the value of uniquifier could be increased from just positive [int] value but so far neither my testing or searches have proven that. MS support will not supply any details on how this is supposed to work. Do you know if the uniquifier value can be "negative" or "bigint'? Just curious. 

    I have not run this query against a large table. I would expect a singe-threaded query that scans all non-null value rows until the first NULL is found so the cost will be proportional the number of non-null values. As to impact on OLTP workload concurrency, shared locks with default read committed behavior should be released immediately as pages are read so I would not expect long-term blocking either. That said, this is a case where you could probably get away with read uncommitted or NOLOCK with due to the ordered scan.

    My first-hand experience with an overflowed uniqueifier is limited to a scenario I encountered many years ago under SQL Server 2000 and may no longer apply. In that case, there were more than 2 billion rows with the same key value and there were no apparent problems, although there could have been others I didn't run into. The only symptom was an out-of-sequence key value error reported by DBCC. It seems the database engine treated the uniqueifier as a 32-bit unsigned integer (allowing 4 billion values) but DBCC it reported the error because it compared the value as a signed integer, considering the first bit as a sign bit instead of part of the value.



    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Tuesday, October 1, 2019 10:38 AM
  • Thank you for taking time to answer my last questions. I really appreciate all of your help. 

    Sue

    Wednesday, October 2, 2019 12:40 AM