The last time when I wrote about ColumnsStore FAQ, one of the readers had an interesting question. The question was around how to identify the traits of using a column store inside an execution plan. The very thought got me thinking. I was thinking where to start. I remembered that ShowPlan XML was a great way to see some of these attributes. The very next question that came up, what should one look forward to inside a ShowPlan XML.
Showplan can be used to determine whether a columnstore index was used to process the query and whether batch processing was used. Three additional properties will be displayed when viewing Showplan XML, when viewing the tooltip displayed when hovering over the Index Scan icon in the graphical showplan, or when right clicking the graphical showplan and selecting properties.
Three Properties to Look Out
The other two properties are EstimatedExecutionMode and ActualExecutionMode. Both properties can have two values, either batch or row. The value row indicates the traditional processing, while batch indicates that values for multiple rows are being processed. The values for both determine how the SQL Server engine went about executing the actual query. Below table explains the same in simple words. In addition, the number of batches or rows passing through a given operator will be in statistics XML.
Estimated Vs Actual – Interpretation
| EstimatedExecutionMode | ActualExecutionMode | Interpretation |
| Row | Row | Same as current SQL Server behavior |
| Row | Batch | Not available in SQL Server 2012 |
| Batch | Batch | Query Engine successfully executed in batch mode |
| Batch | Row | Query Engine was unable to process in batch mode and fell back to row mode execution |
I am sure this will be a great start for folks who want to understand the nuances of working with SQL Server and ColumnStore index. The query processing engine is quite intelligent and am sure will evolve and more will get added to this. I have tried to overly simplify a complex topic which will help one and all.
Reference: Pinal Dave (https://blog.sqlauthority.com)
1 Comment. Leave new
dbForg Studio for SQL Server, All-in-One Tool for SQL Database Developers, is Excellent SQL Management, it´s give you all control about everything at your server and data, also give you flexibility to build perfect queries helping choose correct commands with calculate of cost and profile control, giving a nice format.
Besides that, with it you have:
– User-friendly user interface to manage and consult your data with SQL formatting, and you can see diagram of your query, analyze how and where your data has a high cost and how your sub-query and help high performance.
– Manipulate multi Databases (Attach, Backup, Restore, Import and Export), you will not need use SSMS (SQL Server Management Studio) because all you need at your figure tip quickly and without loss focus to change to another Software
– Generate SQL scripts to help you Create, Insert, update your Tables, then if you want to applicate your tables at new Database at another Server another place, just run these scripts and in a few minutes or seconds will have the same Tables with all PK, FK and all Index.
– Compare your data across Scheme and Databases, then if you are developer and want to applicate your changes from Work DB to Live (production) Server, just compare and it will be exact copy of what you plan.
– How about forget one of SQL Syntax or want advanced solution? it´s your chance to have it easily, dbForg SQL Product help you with Right hand window with all SQL commands and syntax.
– You work at team and want to be up-to-date with all changes, or work alone and want to retrieve old change to your SQL Server, all that at SQL Source Control, Tracking changes history and resolve conflicts, control working folders.
– Index Manager, you can be fixing index fragmentation, and make your queries with more performance.
– If you are Developer and want to have Database with testing data, to prevent security of original data of your client, you can use Unit Test tool and have sure of success teste to your coding.
– How much you need to write SQL Script of Create Table and mistakes that can happened? what about Table Designer, can grantee quickly create, set properties, edit, rebuild tables, also can preview changes before modification DB Object.
– I´m wonder with strange result of my query and can’t know when and where that happened during query execution, but with T-SQL Debugger, I solved my problem with no effort and low cost.
– One of the most important you have data and want to Export or import from/to you analyze and statistic reports, now with dbForn SQL Studio, can Export/Import with 10+ widely used data formats like (Excel and Excel 2007 or higher, ODBC, SQL Scripts, DBF, MS-Access and more.
– With Database Administration, could manage, Assign, Grant and revoke with security Manger, and without need to switch to SSMS to do that.
– Server event Profiler is interface is a powerful GUI tool for tracking and monitoring instances of the SQL Server Engine using traces, to Create, Manage and Analyze and saving trace results.
– Watch your data table and see something need to change, without go outside of grid view can Edit the data and commit in seconds, quickly and secure.
– Need to Pivot Table, and don’t leave your dbForn Studio to another software, okay, that´s it, when you are at query result window, go to view option and select Pivot view, with a few clicks you have what you need, with small offer and goateed data result, using group, summarize and filter data.
– SQL Server Reporting offer SQL Report Builder with chart potting in 9 different formats.
– Master-Detail Browser help you view simultaneous data in related tables.
– I compared it with NAVICAT premium, and see a lot of jobs dbForg SQL Studio do with more efficient than NAVICAT, and don´t halt all the time with big amount of data, I tested with 457,000,000 row of one Table, and no halt.
If you search for professional and best SQL Management and Develop, that it´s dbForg SQL Studio, the best cost benefice.