{"id":88021,"date":"2020-09-01T17:41:03","date_gmt":"2020-09-01T17:41:03","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=88021"},"modified":"2026-03-09T14:36:28","modified_gmt":"2026-03-09T14:36:28","slug":"hands-on-with-columnstore-indexes-part-4-query-patterns","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/hands-on-with-columnstore-indexes-part-4-query-patterns\/","title":{"rendered":"Query Patterns for Columnstore Indexes: Joins & Performance (Part 4)"},"content":{"rendered":"

The series so far:<\/strong><\/p>\n

    \n
  1. Hands-On with Columnstore Indexes: Part 1 Architecture<\/a><\/li>\n
  2. Hands-On with Columnstore Indexes: Part 2 Best Practices and Guidelines<\/a><\/li>\n
  3. Hands-On with Columnstore Indexes: Part 3 Maintenance and Additional Options<\/a><\/li>\n
  4. Hands-On with Columnstore Indexes: Part 4 Query Patterns<\/a><\/li>\n<\/ol>\n\n\n\n\n

    Query performance against a columnstore index depends on controlling the number of segments read. Two mechanisms achieve this: rowgroup elimination (filtering on the ordered column to skip irrelevant rowgroups) and segment elimination (querying fewer columns to reduce the segments accessed per rowgroup).<\/p>\n\n\n\n

    For aggregate queries, SQL Server can use segment metadata (min\/max values) to return results without decompressing the data, making queries across hundreds of millions of rows return in under a second. When joining dimension tables, be cautious: unnecessary JOINs force additional segment reads even when no columns from those tables are used in the output. Pre-selecting dimension keys into temporary tables before joining to the columnstore often outperforms direct multi-table joins.<\/p>\n\n\n\n

    Read also:<\/strong> Part 2: Best Practices and Data Ordering<\/a><\/p>\n\n\n\n

    Introduction<\/h2>\n\n\n\n

    Following a solid dive into the architecture, maintenance, and performance of columnstore indexes, the next logical step is to analyze the data within a columnstore index.<\/p>\n\n\n\n

    Reading data from a highly compressed analytical structure is quite different from the query patterns used on transactional data. By leveraging metadata, data order, segment elimination, and compression, large tables can be quickly read and results returned in seconds (or less!).<\/p>\n\n\n\n

    Taking this further, read queries against columnstore indexes can be further optimized by simplifying queries and providing the query optimizer with the easiest path to the smallest columnstore scans needed to return results.<\/p>\n\n\n\n

    This article explores the most efficient ways to read a columnstore index and produce guidelines and best practices for analytics against large columnstore data structures.<\/p>\n\n\n\n

    Test Data<\/h2>\n\n\n\n

    The test data for this article comes from the table fact_order_BIG_CCI<\/em>, which was introduced in the first article in this series and is a blown-up version of fact_order<\/em><\/a> from the WorldWideImportersDW<\/em> Microsoft demo database. To quickly recap, the table contains 23,141,200 rows of data spanning a date range of 2013-01-01 through 2016-05-31.<\/p>\n\n\n\n

    The table is ordered and partitioned by [Order Date Key], the time dimension for this fact table. The following is a sample of data from the table:<\/p>\n\n\n\n

    \"\"<\/figure>\n\n\n\n

    <\/p>\n\n\n\n

    This table represents a classic data warehouse-style table with narrow columns that reference dimension tables as lookups. The result is data that compresses exceptionally well and is optimal for a columnstore index in SQL Server.<\/p>\n\n\n\n

    This change is a good start, but to illustrate the impact of effective query patterns, the data set can be expanded further:<\/p>\n\n\n\n

    INSERT INTO dbo.fact_order_BIG_CCI\nSELECT\n     [Order Key] + (250000 * ([Day Number] + \n         ([Calendar Month Number] * 31))) AS [Order Key]\n    ,[City Key]\n    ,[Customer Key]\n    ,[Stock Item Key]\n    ,[Order Date Key]\n    ,[Picked Date Key]\n    ,[Salesperson Key]\n    ,[Picker Key]\n    ,[WWI Order ID]\n    ,[WWI Backorder ID]\n    ,[Description]\n    ,[Package]\n    ,[Quantity]\n    ,[Unit Price]\n    ,[Tax Rate]\n    ,[Total Excluding Tax]\n    ,[Tax Amount]\n    ,[Total Including Tax]\n    ,[Lineage Key]\nFROM Fact.[Order]\nCROSS JOIN\nDimension.Date\nWHERE Date.Date > '2013-04-10'\nAND Date.Date <='2014-03-31';\nGO\nDROP INDEX CCI_fact_order_BIG_CCI ON dbo.fact_order_BIG_CCI;\nCREATE CLUSTERED INDEX CCI_fact_order_BIG_CCI \nON dbo.fact_order_BIG_CCI ([Order Date Key]);\nCREATE CLUSTERED COLUMNSTORE INDEX CCI_fact_order_BIG_CCI \nON dbo.fact_order_BIG_CCI WITH (MAXDOP = 1, DROP_EXISTING = ON);<\/pre>\n\n\n\n
    This script inserts more rows into the table and then drops the columnstore index, creates a clustered index, and lastly drops the clustered index in favor of a clustered columnstore index. The added indexing steps ensure a fully ordered (and heftier) data source for us to test on throughout the remainder of this article.<\/p>\n\n\n\n
    Before moving on, a quick query against this data can confirm its size and date range:<\/p>\n\n\n\n
    SELECT\n\tCOUNT(*) AS row_count,\n\tMIN([Order Date Key]) AS min_order_date_key,\n\tMAX([Order Date Key]) AS max_order_date_key\nFROM dbo.fact_order_BIG_CCI;<\/pre>\n\n\n\n
    The results show that we have multiplied our data by a factor of five while retaining the same date range as before:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    Each date contains between 40k and 250k rows. While this is in no way big data, it is large enough to be interesting and forces us to use caution when querying it as a scan of too much data would be quite slow and painful.<\/p>\n\n\n\n
    Controlling Columnstore Index Performance<\/h2>\n\n\n\n
    The key to columnstore index performance is to control the number of segments read. Rowgroup elimination and segment elimination in conjunction with partition elimination allow a large columnstore index to be subdivided very quickly, like cutting a piece of graph paper to remove a small section of squares and discarding the rest.<\/p>\n\n\n\n
    Segment elimination is achieved by reducing the number of columns accessed in a query. Rowgroup elimination is the result of filtering on a column that is well-ordered within the columnstore index. The combination allows for two efficient query mechanisms that are facilitated by a third process:<\/p>\n\n\n\n
    Many Columns, Targeted Date Range<\/h3>\n\n\n\n
    Due to their superb compression, columnstore indexes can service queries across many columns, and does so efficiently so long as the count of rowgroups to be read remains relatively low. A rowgroup represents 220<\/sup> (about 1 million) rows, and therefore each rowgroup read indicates a million rows that need to be read within each referenced segment.<\/p>\n\n\n\n
    Consider the following query:<\/p>\n\n\n\n
    SELECT\n\t*\nFROM dbo.fact_order_BIG_CCI\nWHERE [Order Date Key] = '2015-03-19'<\/pre>\n\n\n\n
    This query returns 135,135 rows with all 19 columns from the table but does so for only a single date. Since our data is ordered by date, all partitions that do not contain this date can be eliminated. Similarly, all rowgroups that do not contain this date can be eliminated.<\/p>\n\n\n\n
    The query takes about a second to process, most of which was returning the data to the client. We can confirm the most effort needed to read a single rowgroup by checking the metadata for all rowgroups in this columnstore index:<\/p>\n\n\n\n
    SELECT\n\tMAX(column_store_row_groups.size_in_bytes) \n             AS max_size_in_bytes,\n\tCOUNT(*) AS rowgroup_count\nFROM sys.column_store_row_groups\nINNER JOIN sys.indexes\nON indexes.index_id = column_store_row_groups.index_id\nAND indexes.object_id = column_store_row_groups.object_id\nINNER JOIN sys.tables\nON tables.object_id = indexes.object_id\nINNER JOIN sys.partitions\nON partitions.partition_number = \n      column_store_row_groups.partition_number\nAND partitions.index_id = indexes.index_id\nAND partitions.object_id = tables.object_id\nWHERE tables.name = 'fact_order_BIG_CCI'<\/pre>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    The results tell us that there is a total of 102 rowgroups in this index, of which the largest is about 4MB. This data remains compressed until needed for a query, at which time it is decompressed for use by the SQL Server execution engine. This is not a lot of data, and therefore can be returned quite quickly! Note that the amount of effort needed by SQL Server to process this query is the same whether we read one row or all rows from the rowgroup as a segment read is a segment read, regardless of how many rows are read.<\/p>\n\n\n\n
    In general, wide queries over few rowgroups perform well due to rowgroup elimination and compression saving the day. Therefore, we can target a columnstore index with OLTP-style read queries and not pay a significant penalty for doing so.<\/p>\n\n\n\n
    Few Columns, Larger Date Range<\/h3>\n\n\n\n
    The use-case most supported and encouraged by columnstore indexes are aggregate queries that span many rowgroups but read few segments. These are queries that scan lots of rows, but only a few columns. Aggregate queries can often take advantage of segment metadata to return needed data, significantly speeding up execution.<\/p>\n\n\n\n
    The following query pulls some quantity metrics for a full year\u2019s worth of data:<\/p>\n\n\n\n
    SELECT\n\tMIN(Quantity) AS min_quantity,\n\tMAX(Quantity) AS max_quantity,\n\tCOUNT(*) AS orders_sampled\nFROM dbo.fact_order_BIG_CCI\nWHERE [Order Date Key] >= '2015-01-01'\nAND [Order Date Key] < '2016-01-01';<\/pre>\n\n\n\n
    This query only requires segments for two columns and can rely on metadata for the MIN, MAX, and COUNT functions. The results are returned sub-second:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    33.2 million rows were \u201cread\u201d, but in actuality, the segment reads were accomplished via metadata, rather than having to actually decompress and read a million rows of segment data per segment:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    The image above is cut in the middle of a much wider series of text so that it fits nicely here on a page. Note that 32 segments are read (the entire partition for the year), which sounds like a lot, but the reads are low \u2013 only 395 LOB reads. This gives the illusion that all rowgroups (and possibly all segments) in the columnstore index were read, but remember that this table is partitioned and the rowgroups for all other years were skipped prior to runtime.<\/p>\n\n\n\n
    Note that all columnstore metadata and data lookups manifest as LOB reads in STATISTICS IO. This is normal and is indicative of how SQL Server stores columnstore index segments within native LOB storage.<\/p>\n\n\n\n
    So long as metadata is mostly being relied on for results, more columns can be queried with minimal additional effort:<\/p>\n\n\n\n
    SELECT\n\tMIN(Quantity) AS min_quantity,\n\tMAX([Unit Price]) AS max_unit_price,\n\tAVG([Tax Rate]) AS avg_tax_rate,\n\tSUM([Tax Amount]) AS total_tax_amount,\n\tSUM([Total Including Tax]) AS total_including_tax,\n\tMIN([Order Date Key]) AS min_order_date_key,\n\tMIN([Picked Date Key]) AS min_picked_date_key,\n\tCOUNT(*) AS orders_sampled\nFROM dbo.fact_order_BIG_CCI\nWHERE [Order Date Key] >= '2015-01-01'\nAND [Order Date Key] < '2016-01-01';<\/pre>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    Results are returned FAST, and the IO stats show more IO, but nothing significant:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    The IO is about four times what it was previously, which makes perfect sense as eight columns are being queried rather than two. Like before, metadata was used instead of data, allowing relatively low IO and a fast execution time given that 33.2 million rows were read.<\/p>\n\n\n\n
    A common analytic need is to group data by time range. For example, the following query returns grouped quantity data for the second half of 2015:<\/p>\n\n\n\n
    SELECT\n\tYEAR([Order Date Key]) AS order_year,\n\tMONTH([Order Date Key]) AS order_month,\n\tMIN(Quantity) AS min_quantity,\n\tMAX(Quantity) AS max_quantity,\n\tCOUNT(*) AS orders_sampled\nFROM dbo.fact_order_BIG_CCI\nWHERE [Order Date Key] >= '2015-07-01'\nAND [Order Date Key] < '2016-01-01'\nGROUP BY YEAR([Order Date Key]), MONTH([Order Date Key])\nORDER BY YEAR([Order Date Key]), MONTH([Order Date Key]);<\/pre>\n\n\n\n
    The results take about a second to be returned, and are as follows:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    Once again, IO is quite low, and since we sampled only half of the year, half of the rowgroups (and hence half of the segments) were skipped:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    No Date Range? Use Metadata!<\/h3>\n\n\n\n
    It is possible with a columnstore index to read absurd numbers of rows relatively quickly, and the key to doing so lies in metadata. If a large number of segments need to be read, and the query can be limited to metrics stored within metadata, then the results can be returned quickly. To test this, the previously executed queries for a single year can be expanded to the entire data set:<\/p>\n\n\n\n
    SELECT\n\tMIN(Quantity) AS min_quantity,\n\tMAX(Quantity) AS max_quantity,\n\tCOUNT(*) AS orders_sampled\nFROM dbo.fact_order_BIG_CCI;\nSELECT\n\tMIN(Quantity) AS min_quantity,\n\tMAX([Unit Price]) AS max_unit_price,\n\tAVG([Tax Rate]) AS avg_tax_rate,\n\tSUM([Tax Amount]) AS total_tax_amount,\n\tSUM([Total Including Tax]) AS total_including_tax,\n\tMIN([Order Date Key]) AS min_order_date_key,\n\tMIN([Picked Date Key]) AS min_picked_date_key,\n\tCOUNT(*) AS orders_sampled\nFROM dbo.fact_order_BIG_CCI;<\/pre>\n\n\n\n
    The queries each sample 105.2 million rows and the total time was under a second. Even with eight columns returned, the query performed well.<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    The IO for each query is as follows:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    The reads required to return this data were not trivial but were significantly lower than would typically be expected for scanning 105 million rows.<\/p>\n\n\n\n
    Joins and Filters on Dimension Tables<\/h3>\n\n\n\n
    Most real-world queries are not as simple as a single table and a handful of metrics. Joining and filtering when consuming a large columnstore index may require some consideration to ensure that even as more dimensions are added, performance on the columnstore does not become significantly worse than what we have seen thus far.<\/p>\n\n\n\n
    In general, data retrieval from a columnstore index is relatively fast so long as metadata can be used and\/or the segment data can remain compressed. For each segment that needs to be read, the time it takes to complete a query will increase. The more segments that are read, the slower queries get.<\/p>\n\n\n\n
    Joins naturally require segments to be read in order to link columns within a columnstore index into separate dimension tables. This query joins a handful of dimensions, but only returns one, along with a SUM:<\/p>\n\n\n\n
    SELECT\n\tCustomer.[Customer Key],\n\tSUM([Total Including Tax]) AS [Total Including Tax]\nFROM dbo.fact_order_BIG_CCI\nINNER JOIN Dimension.City\nON fact_order_BIG_CCI.[City Key] = City.[City Key]\nINNER JOIN Dimension.Customer\nON fact_order_BIG_CCI.[Customer Key] = Customer.[Customer Key]\nINNER JOIN Dimension.Employee\nON fact_order_BIG_CCI.[Salesperson Key] = Employee.[Employee Key]\nWHERE [Order Date Key] >= '2015-01-01'\nAND [Order Date Key] < '2016-01-01'\nGROUP BY Customer.[Customer Key]\nORDER BY Customer.[Customer Key];<\/pre>\n\n\n\n
    Despite having to scan a year of data and churn through all customers in that time frame, the results are returned sub-second:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    The following is an adjustment on the query where the SUM on the [Total Including Tax] column is removed:<\/p>\n\n\n\n
    SELECT\n\tCustomer.[Customer Key]\nFROM dbo.fact_order_BIG_CCI\nINNER JOIN Dimension.City\nON fact_order_BIG_CCI.[City Key] = City.[City Key]\nINNER JOIN Dimension.Customer\nON fact_order_BIG_CCI.[Customer Key] = Customer.[Customer Key]\nINNER JOIN Dimension.Employee\nON fact_order_BIG_CCI.[Salesperson Key] = Employee.[Employee Key]\nWHERE [Order Date Key] >= '2015-01-01'\nAND [Order Date Key] < '2016-01-01'\nGROUP BY Customer.[Customer Key]\nORDER BY Customer.[Customer Key];<\/pre>\n\n\n\n
    At first glance, it would seem that this query should be even faster. Without the need to pull the SUM, all that is left to retrieve is the [Customer Key]. The same results are returned, but a 40-second wait is incurred:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    What happened to cause such terrible performance? In this scenario, SQL Server chose to resolve the joins and aggregate operations prior to the filter being applied. As a result, the entire columnstore index was scanned, resulting in a hefty amount of IO and a long wait.<\/p>\n\n\n\n
    At first glance, it would appear that this performance is unnecessary. SQL Server should be able to determine the minimum effort needed to retrieve data from a large columnstore index first, and properly weigh that against the comparatively tiny dimension tables. This is a common problem and one that can be programmatically solved by avoiding unnecessary joins or leaning MORE on the columnstore index and less on dimension tables. This particular query can be simplified easily to remove this problem:<\/p>\n\n\n\n
    SELECT\n\tfact_order_BIG_CCI.[Customer Key]\nFROM dbo.fact_order_BIG_CCI\nWHERE [Order Date Key] >= '2015-01-01'\nAND [Order Date Key] < '2016-01-01'\nGROUP BY fact_order_BIG_CCI.[Customer Key]\nORDER BY fact_order_BIG_CCI.[Customer Key];<\/pre>\n\n\n\n
    This brings us back to 32 segment reads while returning the same results. Interestingly enough, the poorly performing query earlier can be \u201coptimized\u201d by adding an aggregate:<\/p>\n\n\n\n
    SELECT\n\tCustomer.[Customer Key],\n\tCOUNT(*)\nFROM dbo.fact_order_BIG_CCI\nINNER JOIN Dimension.City\nON fact_order_BIG_CCI.[City Key] = City.[City Key]\nINNER JOIN Dimension.Customer\nON fact_order_BIG_CCI.[Customer Key] = Customer.[Customer Key]\nINNER JOIN Dimension.Employee\nON fact_order_BIG_CCI.[Salesperson Key] = Employee.[Employee Key]\nWHERE [Order Date Key] >= '2015-01-01'\nAND [Order Date Key] < '2016-01-01'\nGROUP BY Customer.[Customer Key]\nORDER BY Customer.[Customer Key];<\/pre>\n\n\n\n
    This method is cringe-worthy and is not a real optimization \ud83d\ude0a It highlights ways in which the optimizer can hit odd breakpoints that tilt it between good decisions and bad decisions.<\/p>\n\n\n\n
    Joined tables can incur unnecessary costs even when not used. Consider this example:<\/p>\n\n\n\n
    SELECT\n\tCustomer.[Customer Key],\n\tCOUNT(*)\nFROM dbo.fact_order_BIG_CCI\nINNER JOIN Dimension.City\nON fact_order_BIG_CCI.[City Key] = City.[City Key]\nINNER JOIN Dimension.Customer\nON fact_order_BIG_CCI.[Customer Key] = Customer.[Customer Key]\nINNER JOIN Dimension.Employee\nON fact_order_BIG_CCI.[Salesperson Key] = Employee.[Employee Key]\nWHERE Customer.[Customer Key] = 0\nGROUP BY Customer.[Customer Key]\nORDER BY Customer.[Customer Key];<\/pre>\n\n\n\n
    The following is the IO for the query:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    No columns are returned from the City table, but it is scanned anyway. This is not as big of a surprise as the last performance bomb that was unearthed above. INNER JOINs to a table force the joined columns to be read, even if not needed elsewhere in the query. This is no different than similar queries against OLTP tables. This can exacerbate performance problems related to columnstore indexes as seen previously.<\/p>\n\n\n\n
    If the City and Employee tables are removed from the 40-second query from earlier, then runtime is reduced to 10 seconds:<\/p>\n\n\n\n
    SELECT\n\tCustomer.[Customer Key]\nFROM dbo.fact_order_BIG_CCI\nINNER JOIN Dimension.Customer\nON fact_order_BIG_CCI.[Customer Key] = Customer.[Customer Key]\nWHERE [Order Date Key] >= '2015-01-01'\nAND [Order Date Key] < '2016-01-01'\nGROUP BY Customer.[Customer Key]\nORDER BY Customer.[Customer Key];<\/pre>\n\n\n\n
    This is still painful, but indicative of how joins (necessary or not) can impact the performance of a columnstore index.<\/p>\n\n\n\n
    Read also:<\/strong>
    Temporary tables for dimension key pre-selection<\/a>
    Subqueries for columnstore filtering<\/a><\/p>\n\n\n\n
    Joins and Lookups: Forcing Good Performance<\/h3>\n\n\n\n
    The easiest workaround to these challenges is to retrieve the data needed from the columnstore index first with as few filters and joins as possible. This methodology is not very ORM or app-friendly but can help make important analysis run exceptionally fast.<\/p>\n\n\n\n
    For example, if there was a need to report on a specific set of customers and the columnstore index query was performing poorly, an easy fix would be to preselect the customer list, similar to this:<\/p>\n\n\n\n
    CREATE TABLE #customer_list\n\t([Customer Key] INT NOT NULL PRIMARY KEY CLUSTERED);\nINSERT INTO #customer_list\n\t([Customer Key])\nSELECT\n\tCustomer.[Customer Key]\nFROM Dimension.Customer\nWHERE [Buying Group] = 'Tailspin Toys'\nAND [Postal Code] BETWEEN '90000' AND '90200'\nSELECT\n\tSUM(Quantity) AS Quantity\nFROM dbo.fact_order_BIG_CCI\nWHERE [Order Date Key] >= '1\/1\/2015'\nAND [Order Date Key] < '1\/1\/2016'\nAND fact_order_BIG_CCI.[Customer Key] IN\n\t(SELECT customer_list.[Customer Key] \n         FROM #customer_list customer_list);\nDROP TABLE #customer_list;<\/pre>\n\n\n\n
    In this example, a very specific filter is applied to the Customer<\/em> dimension. Once applied, the resulting customer keys are stored in a temporary table for use against the columnstore index.<\/p>\n\n\n\n
    Note that this is typically a reactionary approach. If a report or analytic process is exceptionally slow, then moving metadata searches to a separate query and feeding the results into the columnstore index will often improve performance. I would not recommend proactively implementing changes like this as it does add complexity to the script. Instead, save it as a tool to be used when needed.<\/p>\n\n\n\n
    This technique is not unique to the OLAP world and can be useful in complex OLTP queries to reduce the size and complexity of a query and encourage better performance from the query optimizer.<\/p>\n\n\n\n
    Columnstore Indexes and Memory Usage<\/h3>\n\n\n\n
    Like OLTP indexes, all data to satisfy a query needs to be read into the buffer cache prior to being used by the execution engine. Because columnstore indexes are not updated as frequently as OLTP tables, the data that is read into memory can remain there compressed and untouched for an extended period of time. Compounding this, the high compression ratios achieved in columnstore indexes allow more data to reside in the buffer cache for longer, assuming no memory pressure exists.<\/p>\n\n\n\n
    The combination of these columnstore indexes and OLAP attributes is that memory becomes a key component of columnstore index performance. The following are the storage details for the columnstore index used thus far in this article:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    The entire index contains 105 million rows that are squeezed into only 430MB. This is small enough that an entire partition (or even the entire index) could reside in memory for reporting purposes at all times.<\/p>\n\n\n\n
    Real-world columnstore indexes will typically be much larger than this, to the point where the entire index remaining in memory is not feasible, but we can plan appropriately for memory sizing so that an adequate amount is available to service analytic queries.<\/p>\n\n\n\n
    Consider a hypothetical production analytic data store with the following details:<\/p>\n\n\n
    \n