SQL SERVER – Video – Performance Improvement in Columnstore Index

I earlier wrote an article about SQL SERVER – Fundamentals of Columnstore Index and it got very well accepted in community. However, one of the suggestion I keep on receiving for that article is that many of the reader wanted to see columnstore index in the action but they were not able to do that. Some of the readers did not install SQL Server 2012 or some did not have good machine to recreate the big table involved in the demo.

For the same reason, I have created small video for that.

I have written two more article on columstore index. Please read them as followup to the video:

SQL SERVER – How to Ignore Columnstore Index Usage in Query

SQL SERVER – Updating Data in A Columnstore Index

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

11 thoughts on “SQL SERVER – Video – Performance Improvement in Columnstore Index

  1. It was a good demo on benefits of ColumnStore index. I have a few queries
    a. Could you also explain when one should go for such an index?
    b. is it only when I have aggregate functions used on my tables (in my stored procedures or queries)? or
    c. does it provide benefit even if I don’tr have aggregate functions used?
    d. Is the order of columns that make up the Index keys important in ColumnStore index also?

    Thanks
    Vijayaraghavan @ Chennai

  2. Greetings Sir,..

    I faced these in interview,but still have confusions, pls let me clear..

    * Will Group function ignore null values?
    * Declaring a column (for which we gonna create index)as not null or using indexed column in queries often,which will improve the performance of an index?
    *its commonly said that,views and synonyms will get dropped after dropping relevant table..but it becomes invalid and not dropped..only constrains,indexes will get dropped..Am i right??

  3. Hi sir,
    can you please guide me the process of index works,in the below is one scenario
    as

    CREATE TABLE DEPT(deptid NUMBER(4) PRIMARY KEY, deptname CHAR(10));
    CREATE INDEX dept_deptname_idx ON DEPT(deptname);
    /* The DEPT table contains 10,000 rows and the .deptname column contains 10 unique values that are evenly distributed. */

    Based on the scenario above, which query needs to use the dept_deptname_idx index?

    SELECT deptid FROM DEPT WHERE deptid 1234;
    SELECT deptid FROM DEPT WHERE deptname = ‘SALES';
    SELECT DISTINCT deptid FROM DEPT;
    SELECT deptid FROM DEPT WHERE deptname ‘SALES';
    SELECT deptid FROM DEPT WHERE deptid < 1234;

    please answer it and give me the how and why is pocessing

    regard

  4. Hi sir,
    Here is one more scenario as

    You have a Table called Employees with following Columns
    EmployeeID INT
    EmployeeName VARCHAR(256)
    Salary MONEY
    DeptID SMALLINT

    The table has following Indexes created on it
    1. Clustered Index on EmployeeID
    2. Non Clustered Index on EmployeeName
    3. Non Clustered Index on DeptID

    The Employees tables has over 10 million rows.
    You want to get the count of Employees, for which you write the following query

    SELECT COUNT(*) FROM Employees

    What will be the likely query plan for above query? Which index will SQL SERVER use in above query? Please give an explanation for your answer.

    please help me in detailed manner

    regard

  5. Pingback: SQL SERVER – Difference between Create Index – Drop Index – Rebuild Index – Quiz – Puzzle – 21 of 31 « SQL Server Journey with SQL Authority

  6. Hi,

    That was really informative messages. Thank you very much sharing.

    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.

    Thanks,

    Santosh

  7. Pingback: SQL SERVER – Identify Columnstore Index Usage from Execution Plan « SQL Server Journey with SQL Authority

  8. Pingback: SQL SERVER – Beginning New Weekly Series – Memory Lane – #002 « SQL Server Journey with SQL Authority

  9. Hi Sir,

    I need your help, here is my query, it is slow first time

    SELECT COUNT(1) AS Results FROM ImportShipmentNew2013
    WHERE CONTAINS(ProductDescription, ‘neem’) –AND (@HSCode=” OR HSCode = @HSCode )
    GROUP BY ForeignCountry

    (69 row(s) affected)
    Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table ‘ImportShipmentNew2013′. Scan count 0, logical reads 721107, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    (1 row(s) affected)

    Please suggest to index create to improve performance

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s