Archive for the ‘Technology’ Category

Excel Tip: Month Name Sorting in Excel July 14th, 2014

Vinod Kumar

In my previous article Excel Tip: Month Name Sorting with PowerPivot / PowerView, I wrote about sorting of month name inside PowerPivot. I got a number of ping about the same functionality inside standard Excel sheets. I thought this would be the easiest and known to many – but to my surprise, not all know about the capability of Excel. So in this post, let me take you through the same process for Excel tables.

So let me prep you with the data first. I have two columns Month Name and Sales. When I try to sort by the Month Name, you can see how the sorting happens for A-Z and for Z-A. It is nowhere near to what one would expect to sort as month.

That bring us to an interesting option. It has been there all along, select the “Sort by Color” –> “Custom Sort…”.

This comes up with a small dialog which I am sure most of you would have used. There right at the order dropdown, is a hidden gem called as “Custom List…”. Select this to see the magic.

This bring a standard set of list or feel free to define your own New List for sorting and build the table.

Click on “OK” and see. This is awesome because now the list is sorted automatically back in your table. Now if we sort A-Z and Z-A to see the difference. So how easy and cool is it? Have you ever used this option before, let me know.

Continue reading...


Excel Tip: Month Name Sorting with PowerPivot / PowerView June 25th, 2014

Vinod Kumar

I do a lot of presentation on Excel related topics to customers as part of my BI conversations at MTC, Bangalore. Recently one of the developer when looking at the PowerView demo said, he had an unique problem. He said that the PowerView was not ordering the month names in the correct order of Jan, Feb, Mar and so on but was sorting based on Alphabets. I repeatedly asked him if they had enabled the proper sort order. He was little clueless and wanted to see it in action. Here is the 30 seconds demo that I showed how this can be achieved.

In our PowerPivot dataset we have the following. It is a list of MonthNames and sales marked against them.

When I use this dataset and create a PowerView report, I select MonthName and Sales. This comes up with an report like this irrespective of the sort I do. This is the problem that the customer was mentioning.

The solution is simple. Get back to PowerPivot Tab, under Home –> Sort by Column –> Select “Sort by Column”.

We will be presented by the following dialog. Here I am mentioning, whenever the “Month Name” column is selected, use the “Month” integer column to sort behind the scenes. That is it !!

One the above step is complete, our PowerView will show a dialog of change in Data Model, select OK.

And the change can be seen now. The months seems to be in correct order.

Have you encountered this requirement in your report generation anytime? Do let me know.

Continue reading...


SQL Server: Columnstore Index on Temp Tables June 20th, 2014

Vinod Kumar

Recently I was talking to a customer during performance testing and they had an interesting twist to using Columnstore Index. Since they were on SQL Server 2012, I had suggested them to use Non-Clustered Columnstore index for their workload. He started to quiz me with a number of questions and I thought it would be appropriate to point them to some resources I had already written. So I took the liberty to send them the two articles:

  1. SQL Server 2012: ColumnStore Characteristics
  2. SQL Server 2012: ColumnStore Part II

The fundamentals and concepts have not changed but where worth a mention and read. Having said that, the counter question after about an hour of their research and reading the contents. “Will it work with Temp Tables? We are doing a reporting workload and are using Temp tables extensively.”

I thought, wow, interestingly I have not written on it till date and is worth a mention though. The easy answer is “YES”, it is quite possible. Why didnt you try was my answer :)

Columnstore Index on Temp Tables

To drive home the concept, in the below example I have created a simple temp table called as #Temp_Account. When I query the table, the execution plan shows as “RowStore” under storage as shown below.

The next logical step is to create our Non-Clustered Columnstore Index. I am using the standard syntax as shown below.

If we try to insert into this temp table, it is still readonly because non-clustered columnstore index has been defined in the table.

Msg 35330, Level 15, State 1, Line 19
INSERT statement failed because data cannot be updated in a table that has a nonclustered columnstore index. Consider disabling the columnstore index before issuing the INSERT statement, and then rebuilding the columnstore index after INSERT has completed.

After index creation, if we make a simple query we can see the change in Execution Plan. The storage is now showing as “ColumnStore” as shown in the execution plan below.

The above query is simple in nature and hence the execution mode is “Row”. In future posts I will show how this can be changed to Batch mode.

Continue reading...


Identify SQL Server 2014 Cardinality Estimator June 4th, 2014

Vinod Kumar

SQL Server 2014 released a new cardinality estimator (CE) and more can be learnt from the MSDN documentation on the specifics. In this post I just wanted to call out how you can quickly identify if we are using the old estimator or the new CE. This information stays inside the execution plans or the properties pane.

Recently one of the customers did see me talking about this and complained that his queries run on Adventureworks were never using the new CE and he had to always use a trace flag. This got me interested and I will tell you what I found as part of this learning.

The steps:

  1. Create some random query in samples DB or a DB of your choice. I used the AdventureworksDB for my experiment.
  2. Enable Actual Execution Plan.
  3. Execute the query, and wait for the Execution Plan Tab to appear.
  4. On the Execution Plan tab, right click select “Show Execution Plan XML …”

This now opens up the XML Plan for the query under question. And right at the top we will find the element which says “CardinalityEstimatorModelVersion”, if this is 70 then we are using the old CE and if this reads 120 then we are using the new CE.

We can also find this information in the Properties Pane (F4 shortcut). This is shown in the diagram below.

It was baffling to me why this was always showing as 70 for my environment. So the first place for me to check was the compatibility of the Database. It was at 110 (SQL Server 2012), I changed the same to 120 (SQL Server 2014) and I reran the same query.

As you can see, SQL Server has picked up the new CE and has evaluated the same. There is so much to learn about the new CE that I will reserve it for future posts.

Continue reading...


Checksum on Backup and Restores May 28th, 2014

Vinod Kumar

I am a firm believer of using new features that enhance productivity and life of a DBA. In that category is the feature of CHECKSUM. I have written a number of article on them in the past and are worth a mention:

  1. Torn Page Vs CHECKSUM
  2. CHECKSUM and SQL Server
  3. CHECKSUM and TempDB
  4. CHECKSUM Vs FileStream
  5. SQL Server 2014: Checksum with backups

SQL Server BACKUP and RESTORE statements provide the CHECKSUM option to include checksum protection on the backup stream and trigger the matching validation operations during restore. To achieve a checksum-enabled backup, the BACKUP command must include the CHECKSUM option. Read more syntax of CHECKSUMS from MSDN.

Backup and restore operations that use checksum capabilities increase data integrity protection and also increase CPU usage requirements at the point of backup. A backup or restore with the checksum option requires that each byte be checked as it is streamed, thereby increasing CPU usage. The checksum that is used for backup and restore uses the same algorithm to calculate the checksum value for the backup media as is used for data pages and log blocks.

The following rules apply to the BACKUP and RESTORE command CHECKSUM operations:

  1. By default, SQL Server BACKUP and RESTORE operations maintain backward compatibility (NO_CHECKSUM is the default). This needs to be specified explicitly.
  2. The database’s PAGE_VERIFY setting has no effect on backup and restore operations; only the CHECKSUM setting on the backup or restore command is relevant.
  3. The backup and restore checksum is a single value representing the checksum of the complete stream; it does not represent individual pages or log blocks located in the backup stream. The value is calculated during the backup and stored with the backup. The value is recalculated during the restore and checked against the stored value.
  4. Backup with the CHECKSUM option will not change the pages as it saves them to the backup media; a page’s protection state (NONE, CHECKSUM, or TORN) is maintained as read from the database file. If a checksum was already stored on the data page, it is verified before the page is written to the backup stream.
  5. Restore and Verify commands can be used to validate the CHECKSUM if the backup was created by using the CHECKSUM option. Trying to restore with the CHECKSUM option on a backup without a checksum returns an error as shown below.

Msg 3187, Level 16, State 1, Line 4
RESTORE WITH CHECKSUM cannot be specified because the backup set does not contain checksum information.

From an third-party utilities perspective and CHECKSUM, please read the KB-2656988.

Now that you got some of the fineprints with CHECKSUM and backups, I hope you will use the same in your environments in future atleast if you are not using.

Continue reading...