Column store Index mechanism: RRS feed

  • Question

  • Hi,

    I've created NonClustered Index (IDX_x) and COLUMNSTORE INDEX (idx_csi).

    Once I run SELECT query with NonClustered Index, the query takes 30 secs to execute and COLUMNSTORE INDEX takes around 2 seconds to execute.

    But, if I try to run the NonClustered Index once again, surprisingly it takes only 2-3 seconds to run the query. So my question is once COLUMNSTORE INDEX is created and ran, is there any mechanism which stores the searched data in memory and will be used later if required. Let me know how it works.



    Friday, February 17, 2012 4:58 AM

All replies

  • Without knowledge of what you did, it's difficult to say for sure, but there is a general mechanism: the buffer cache. Presumably, the first time you ran the query, the data was not in the cache, so SQL Server had to read from disk. On subsequent executions data was in the cache, and SQL Server did not need to go to disk.

    This is an important thing to understand when benchmarking queries. Make sure that you either run all tests with data on disk (by using DBCC DROPCLEANBUFFER before each run) each time, or run all tests with data in cache. (Make sure that the data you query is way below your memory, and run a warm-up query first.)

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, February 17, 2012 10:32 PM
  • Thank you very much Erland for the response.

    Yes, even I assumed there is a caching mechanism is happening. But, even if I desconnect and connect, the caching mechanism still works? And if I don't want caching mechanism to work, what should be done? (should I delete the Index and again)



    Tuesday, February 21, 2012 2:14 AM
  • Your question indicates that you have a misconception in the mindset. SQL Server is a server application, and you connect as a client. Server and client are different processes, often at different machines. The server serves many clients, and the buffer cache is shared by everyone. Who knows, 10 seconds after you ran your query, maybe another user runs something similar.

    Data stays in cache, until the spaces is needed for something else. Which means that for data which is read often that the data stays there virtually forever.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, February 21, 2012 9:29 PM