{"id":81814,"date":"2018-11-29T14:42:29","date_gmt":"2018-11-29T14:42:29","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=81814"},"modified":"2022-04-24T21:26:04","modified_gmt":"2022-04-24T21:26:04","slug":"there-is-a-new-count-in-town","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/there-is-a-new-count-in-town\/","title":{"rendered":"There is a New COUNT in Town"},"content":{"rendered":"

We have all been using the COUNT(DISTINCT)<\/code> function to return row counts for a distinct list of column values in a table for ages. With the introduction of SQL Server 2019, there is a new way to get an estimate of distinct row values and counts for a table. It is by using the new APPROX_COUNT_DISTINCT()<\/code> function. This new function doesn\u2019t return the actual number of rows for each distinct value in a table, but instead returns an approximate count for each distinct value. This new function uses fewer resources than the tried and true COUNT(DISTINCT)<\/code> function. Let\u2019s take a little closer look at this new function.<\/p>\n

What Problem is APPROX_COUNT_DISTINCT() Trying to Solve?<\/h2>\n

The new APPROX_COUNT DISTINCT()<\/code> function is trying to solve the \u201cCount Distinct Problem.\u201d More information about this problem can be found here<\/a>. Basically, the problem is that counting distinct values requires more and more memory as the number of distinct values increases. At some point, SQL Server can no longer manage to maintain the count of distinct values in memory and must spill to tempdb<\/em>. Spilling to tempdb <\/em>causes overhead and leads to increased execution time.<\/p>\n

The implementation of APPROX_COUNT_DISTINCT()<\/code> has a much smaller memory requirement than the COUNT(DISTINCT)<\/code> function. The algorithm used for this new function is HyperLogLog<\/a>. This algorithm can estimate the number of distinct values of greater than 1,000,000,000, where the accuracy of the calculated approximate distinct count value is within 2% of the actual distinct count value. It can do this while using less than 1.5 KB of memory.<\/p>\n

More Information About the APPROX_COUNT_DISTINCT Function<\/h2>\n

The APPROX_COUNT_DISTINCT<\/code> function has been available in other vendor software packages for a while now, but it is new in SQL Server. It first became available in Azure SQL Database, and now has been released for on premises use with SQL Server 2019 Community Technical Preview (CTP) 2.0. This new function is design to quickly return the approximate number of unique non-null values in a group, when your data set has millions, or billions of rows, and many distinct values.<\/p>\n

This APPROX_COUNT_DISTINCT()<\/code> function is designed to give you the approximate aggregated counts more quickly than using the COUNT (DISTINCT)<\/code> method. It does this by using the HyperLogLog algorithm, which reduces the memory footprint required to maintain a list of distinct values. Because less memory is needed, APPROX_COUNT(DISTINCT)<\/code> is, therefore, less likely to spill to tempdb<\/em> when aggregating millions or billions of rows of data. This equates to shorter execution times compared to using the COUNT(DISTINCT)<\/code> method.<\/p>\n

The documentation states that this new function \u201cguarantees up to a 2% error rate within a 97% accurac<\/em>y.\u201d Therefore, if speed is more important than absolute accuracy of the distinct count, then you might want to consider using this new function.<\/p>\n

Invoking this function is similar to using the COUNT(DISTINCT)<\/code> function. Here is the syntax:<\/p>\n

APPROX_COUNT_DISTINCT ( expression )<\/pre>\n
The expression<\/em> can be any type, except for image<\/code>, sql_variant<\/code>, ntext<\/code>, or text <\/code>value.<\/p>\n
Testing APPROX_COUNT_DISTINCT() for Performance<\/h2>\n
In order to test out the performance of this new function, I\u2019ll use a specific use case to determine out how fast APPROX_COUNT_DISTINCT()<\/code> runs as compared to COUNT(DISTINCT<\/code>. In addition to verifying the speed, I\u2019ll also look to see if I can detect if this new function uses a smaller memory footprint than the old COUNT(DISTINCT)<\/code> standby.<\/p>\n
To gather and compare these metrics, I\u2019m going to use this specific hypothetical use case and situation:<\/p>\n
Use Case:<\/em><\/p>\n
Develop a dashboard indicator that shows whether the number of unique IP addresses that have accessed our site for the current month is more, less or the same as the prior month.<\/p>\n
Situation:<\/em><\/p>\n
I work at an internet search engine, and I am responsible for building queries to run against some of the data we have collected. One of the things we track are the IP addresses for every visit to our site. We collect billions of rows of data every month. My boss has asked me to develop a query that can be used as a dashboard trend indicator that will show whether the number of unique IP Addresses coming to our site has gone up (+) or down (-) in the current month, as compared to the prior month. My boss requires this new query to run as fast as possible across our IP Address tracking table that contains billions, and billions of rows. My boss is willing to accept some level of lesser precision in the accuracy of the number, provide the indicator and percentage of change can be produced more quickly than a method that has absolute accuracy.<\/p>\n
To create a testing environment, I created a database and populated a table named Visits2<\/em> that has a billion rows of randomly generated data. You can find the code I used to create my sample database in Listing 3, at the end of this article.<\/p>\n
First, I want to test how fast the APPROX_COUNT_DISTINCT()<\/code> function runs as compared to the COUNT(DISTINCT) <\/code>function. For this test, I ran two SELECT<\/code> queries that supported my use case. The first SELECT<\/code> statement uses the COUNT(DISTINCT)<\/code>function, and the second SELECT<\/code> statement uses the APPROX_COUNT_DISTINCT()<\/code> function. This code, found in Listing 1, generates a count of the number of unique IP Address that have accessed our site for July and August. You can also turn on \u201clive query statistics\u201d if you wish to monitor the progress of each SELECT<\/code> statement while they are running.<\/p>\n
SET STATISTICS TIME ON;\r\nGO\r\nSELECT COUNT(DISTINCT IP_Address) AS NumOfIP_Addresses, \r\n    DATEPART (month,VisitDate) AS MonthNum \r\nFROM [dbo].[Visits2] \r\nWHERE DATEPART (month,VisitDate) IN (7, 8)\r\nGROUP BY DATEPART (month,VisitDate);\r\nGO\r\nSELECT APPROX_COUNT_DISTINCT(IP_Address) AS NumOfIP_Addresses, \r\n    DATEPART (month,VisitDate) AS MonthNum \r\nFROM [dbo].[Visits2] \r\nWHERE DATEPART (month,VisitDate) IN (7, 8) \r\nGROUP BY DATEPART (month,VisitDate);<\/pre>\n
Listing 1: Code to compare COUNT(DISTINCT) and APPROX_COUNT_DISTINCT() functions<\/p>\n
This code turns on time statistics and runs each SELECT<\/code> statement. I will use the time statistics produced by running this code to determine how much CPU and elapsed time it takes to get distinct IP address counts for July and August. In Results 1, you can see the CPU and elapsed times I got when I ran the code in Listing 1.<\/p>\n
<\/p>\n
Results 1: The timing results when running the code in Listing 1<\/p>\n
In Results 1, I show the timing results of the output produced by setting the STATISTICS TIME ON<\/code>, for the two SELECT<\/code> statements. The first set of times are from the COUNT(DISTINCT)<\/code> query, whereas the second set of times are from the APPROX_COUNT_DISTINCT<\/code> query. As you can see, APPROX_COUNT_DISTINCT<\/code> used less CPU and elapsed time compared to the COUNT(DISTINCT)<\/code>query. The APPROX_COUNT_DISTINCT()<\/code> function ran a little over 6% faster than the COUNT(DISTINCT)<\/code> function and uses almost 24% less CPU.<\/p>\n
To determine the number of memory grants used by each SELECT statement, I used the output of the execution plan. In Results 2 you can see the execution plan from these two SELECT queries.<\/p>\n\n\n\n
\n
<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Results 2: Execution Plan code in Listing 1<\/p>\n
The first thing you might notice when comparing these two executions is that the Cost<\/em> of the Hash Match<\/em> operation for the COUNT(DISTINCT)<\/code> function takes more than twice as long to run as the Hash Match<\/em> operation for the APPROX_COUNT_DISTINCT()<\/code> function.<\/p>\n
When I hover over the Hash Match <\/em>operator for the COUNT(DISTINCT)<\/code> function, I can see it spilled to disk (see Results 3), but when I hover over the Hash Match <\/em>operator for the APPROX_COUNT_DISTINCT<\/code> function, it did not spill to disk (see Results 4).<\/p>\n\n\n\n
\n
<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Results 3: Details of Hash Match operation for COUNT(DISTINCT) function<\/p>\n\n\n\n
\n
<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Results 4: Details of Hash Match operation for APPROX_COUNT_DISTINCT function<\/p>\n
To display the number of memory grants between the two different functions, I hover over the SELECT icon in the execution plan. When I hover over the COUNT(DISTINCT) SELECT icon I get the results in Results 5, and when I hover over the APPROX_COUNT_DISTINCT SELECT icon I get the results in Result 6.<\/p>\n\n\n\n
\n
<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Results 5: Memory Grants for COUNT(DISTINCT)<\/p>\n\n\n\n
\n
<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Results 6: Memory Grants for APPROX_COUNT_DISTINCT()<\/p>\n
As you can see, the COUNT(DISTINCT)<\/code> function required 1,847,736 memory grants, whereas the APPROX_COUNT_DISTINCT()<\/code> function only required 24,776 memory grants. This means the COUNT(DISTINCT)<\/code> took 74 times as many memory grants over the new APPROX_COUNT_DISTINCT()<\/code> function. Based on this, you can see the APPROX_COUNT_DISTINCT()<\/code> function had a significantly smaller number of additional memory grants required versus the COUNT(DISTINCT)<\/code> function.<\/p>\n
Testing APPROX_COUNT_DISTINCT() for Accuracy<\/h2>\n
In addition to performance testing, I also wanted to test the accuracy\/precision of distinct values produced between the COUNT(DISTINCT)<\/code> and the APPROX_COUNT_DISTINCT()<\/code> functions. To determine the precision difference between the two functions, I compared the number of distinct values produced by the two different SELECT<\/code> statements in Listing 1. The COUNT(DISTINCT)<\/code> function returned a count of 16,581,375 distinct IP address for July, whereas the APPROX_COUNT_DISTINCT()<\/code> function returns a count of 17,075,480 for July. By comparing these two numbers, I can see that the APPROX_COUNT_DISTINCT()<\/code> function is off by a little over 2.97%. Since this number was not within 2% error rate as documented, I contact Microsoft about this.<\/p>\n
An engineer at Microsoft told me that the SQL Server engine uses a \u201csophisticated analytic sampling method\u201d to produce an estimated count, and because of this, it is possible to build a contrived table that throws off the sampling method and produces dramatically inaccurate results.<\/p>\n
From this first test, I can see that the APPROX_COUNT_DISTINCT()<\/code> function does, in fact, run faster and requires fewer resources over the COUNT(DISTINCT)<\/code> function. The only problem I ran across with this first test, was that I had more than a 2% precision error when generating the approximate count, over the real distinct count value. In my case, I found the precision error was 2.97%, which according to documentation can occur.<\/p>\n
Since my initial testing found the APPROX_COUNT_DISTINCT()<\/code> function returned a distinct count that was more than 2% different than the actual distinct count, I decided to run some additional precision testing. The additional SELECT<\/code> statements I will be testing can be found in Listing 2.<\/p>\n
SELECT COUNT(DISTINCT ID)           AS COUNT_ID,\r\n\t   COUNT(DISTINCT I100)      AS COUNT_I100,      \r\n\t   COUNT(DISTINCT I1000)     AS COUNT_I1000,      \r\n\t   COUNT(DISTINCT I10000)    AS COUNT_I10000,    \r\n\t   COUNT(DISTINCT I1000000)  AS COUNT_I1000000,  \r\n\t   COUNT(DISTINCT I10000000) AS COUNT_I10000000  \r\nFROM Visits2;\r\nGO\r\nSELECT APPROX_COUNT_DISTINCT(ID)           AS COUNT_ID,\r\n\t   APPROX_COUNT_DISTINCT(I100)      AS COUNT_I100,      \r\n\t   APPROX_COUNT_DISTINCT(I1000)     AS COUNT_I1000,      \r\n\t   APPROX_COUNT_DISTINCT(I10000)    AS COUNT_I10000,    \r\n\t   APPROX_COUNT_DISTINCT(I1000000)  AS COUNT_I1000000,  \r\n\t   APPROX_COUNT_DISTINCT(I10000000) AS COUNT_I10000000  \r\nFROM Visits2;<\/pre>\n
Listing 2: Additional Testing Queries<\/p>\n
The code in Listing 2 will identity the number of unique counts for columns that have a different number of unique values. I will use this code in Listing 2 to determine the number of different values it takes for the APPROX_COUNT_DISTINCT<\/code> function to produce a count which is different than the actual number of unique values produced by the COUNT(DISTINCT)<\/code> function. The code from Listing 2 produces the output found in Results 7.<\/p>\n\n\n\n
\n
<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Results 7: Results from running code in Listing 2<\/p>\n
As you can see by looking at Results 7, the APPROX_COUNT_DISTINCT()<\/code> function produces the same number as compared to the COUNT(DISTINCT) function when the number of unique values is low. In my case less than 1,000 unique values. But for each column that had more than 100 values, the APPROX_COUNT_DISTINCT()<\/code> function produced an approximate number was more or less than the actual number generated by the COUNT(DISTINCT)<\/code> function. This test shows that when the actual number of distinct values in a table increases, the APPROX_COUNT_DISTINCT()<\/code> functions don\u2019t produce the same number of distinct values as the COUNT() function, however, it is close. Therefore, if your table contains a large number of distinct values, then expect this new function to produce a count value that is somewhat different than the actual number of distinct values.<\/p>\n
Conclusions Based on My Initial Testing<\/h2>\n
From the few simple tests I ran here against my generated sample data, I have made the following observations:<\/p>\n
    \n
  • The APPROX_COUNT_DISTINCT()<\/code> function does return a distinct count number faster than the COUNT(DISTINCT)<\/code> function.<\/li>\n
  • The COUNT(DISTINCT)<\/code> function requires more memory grants than the APPROX_COUNT_DISTINCT()<\/code> function.<\/li>\n
  • In one situation, I able to returns a unique count value that had more than a 2% error rate.<\/li>\n
  • If the number of distinct values for a given column is low, then the APPROX_COUNT_DISTINCT()<\/code> function returns the same count value as the COUNT(DISTINCT)<\/code> function.<\/li>\n<\/ul>\n
    Should you start using the APPROX_COUNT_DISTINCT()<\/code>function? That\u2019s a good question. I suppose it depends on your situation. I was pleasantly surprised at how much I was able to improve the runtime and reduce the memory footprint using the APPROX_COUNT_DISTINCT()<\/code> function over the COUNT(DISTINCT)<\/code> function. But I was little concerned how easy it was for me to retrieve an aggregated count value that had a precision error of more than 2%. My Visits2 <\/em>table was very contrived but did expose the improvement and drawbacks of using the APPROX_COUNT_DISTINCT()<\/code> function. Therefore, before using this new function in a production environment, I would suggest developing a more exhaustive set of tests against real data to verify that any precision errors are within an acceptable range for each situation. As with any new feature, I suggest you identify what the new feature does and then test the heck out of it to verify it provides the improvements you desire in your environment.<\/p>\n
    Code Used to Create Sample Test Data<\/h2>\n
    In Listing 3, you will find the code I used to create my contrived sample database (SampleData)<\/em> and the table (Visits2<\/em>) I used to support my testing. Keep in mind that the code in this listing is not very efficient at creating my sample data. It took over an hour on my laptop to create the billion-row table I used for testing.<\/p>\n
    -- Create Sample DB with SIMPLE recovery\r\nCREATE DATABASE SampleData\r\n CONTAINMENT = NONE\r\n ON  PRIMARY \r\n( NAME = N'SampleData', FILENAME = N'D:\\SQL Server 2019\\SampleData.mdf', \r\n  SIZE = 65GB, FILEGROWTH = 5GB)\r\n LOG ON \r\n( NAME = N'SampleData_log', \r\n  FILENAME = N'D:\\SQL Server 2019\\SampleData_log.ldf' , \r\n  SIZE = 5GB, FILEGROWTH = 5GB);\r\nGO\r\nALTER DATABASE SampleData SET RECOVERY SIMPLE;\r\nGO\r\n-- Set database context\r\nUSE SampleData;\r\nGO\r\n-- Create table to hold sample data\r\nCREATE TABLE Visits2\r\n(\r\n\tID         INT, \r\n\tI100       INT, \r\n\tI1000      INT, \r\n\tI10000     INT, \r\n\tI100000    INT,\r\n\tI1000000   INT, \r\n\tI10000000  INT,\r\n\tIP_Address VARCHAR(15),\r\n\tVisitDate  DATE\r\n);\r\nGO\r\n-- Create Tally Table\r\nGO\r\nCREATE VIEW vw_Tally AS \r\n   --Itzik style tally table\r\n   WITH lv0 AS (SELECT 0 g UNION ALL SELECT 0)\r\n     ,lv1 AS (SELECT 0 g FROM lv0 a CROSS JOIN lv0 b) -- 4\r\n     ,lv2 AS (SELECT 0 g FROM lv1 a CROSS JOIN lv1 b) -- 16\r\n     ,lv3 AS (SELECT 0 g FROM lv2 a CROSS JOIN lv2 b) -- 256\r\n     ,lv4 AS (SELECT 0 g FROM lv3 a CROSS JOIN lv3 b) -- 65,536\r\n     ,lv5 AS (SELECT 0 g FROM lv4 a CROSS JOIN lv4 b) -- 4,294,967,296\r\n     ,Tally (n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) \r\n     FROM lv5)\r\n   SELECT TOP (1000000) n\r\n   FROM Tally\r\n   ORDER BY n;\r\n\t\r\nGO\t\r\n-- Populate Visits2 with sample data\r\nSET NOCOUNT ON;\r\nDECLARE @Max bigint = (select ISNULL(max(ID),0) From Visits2);\r\nWHILE @Max < 1000000000 BEGIN \r\n\t\r\n    WITH TallyTable AS (\r\n\r\n      SELECT n + @Max as N, \r\n\tCAST(RAND(CHECKSUM(NEWID())) * 255 as INT) + 1 AS A4,\r\n\tCAST(RAND(CHECKSUM(NEWID())) * 255 as INT) + 1 AS A3,\r\n\tCAST(RAND(CHECKSUM(NEWID())) * 255 as INT) + 1 AS A2, \r\n\t1.0 + floor(1 * RAND(convert(varbinary, newid()))) AS A1,\r\n\tDATEADD(DD, 1.0 + floor(62 * \r\n         RAND(convert(varbinary, newid()))),'2018-07-01') \r\n                     AS VisitDate\r\n\tFROM vw_Tally)\r\n     INSERT INTO Visits2 (ID, I100, I1000, I10000,\tI100000,\r\n        \tI1000000, I10000000, IP_Address, VisitDate)\r\n     SELECT\tn,n%100, n%1000, n%10000,n%100000,\t\r\n                n%1000000, n%10000000, \r\n\t\tCAST(A1 AS VARCHAR) + '.' + CAST(A2 AS VARCHAR) + \r\n                 '.' +  CAST(A3 AS VARCHAR) + \r\n                 '.' +  CAST(A4 AS VARCHAR), VisitDate\r\n     FROM TallyTable \r\n     set @Max = (select ISNULL(max(ID),0) From Visits2);\r\nEND\r\n-- CREATE UNIQUE CLUSTERED INDEX\r\nCREATE CLUSTERED INDEX IX_SampleData_Visits2_ID ON dbo.Visits2(ID);   \r\nGO  <\/pre>\n
    Listing 3: Script to create database and Visit2 table<\/p>\n","protected":false},"excerpt":{"rendered":"
    Now that SQL Server 2019 is on the way, it\u2019s time to start learning about the new capabilities. In this article, Greg Larson tests the new APPROX_COUNT_DISTINCT function for performance and accuracy.…<\/p>\n","protected":false},"author":78478,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143529,143531],"tags":[5134],"coauthors":[11330],"class_list":["post-81814","post","type-post","status-publish","format-standard","hentry","category-performance-sql-server","category-t-sql-programming-sql-server","tag-sql-prompt"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/81814","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/78478"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=81814"}],"version-history":[{"count":14,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/81814\/revisions"}],"predecessor-version":[{"id":81846,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/81814\/revisions\/81846"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=81814"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=81814"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=81814"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=81814"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}