Posts Tagged ‘ITPro’

Managed Databases on Cloud July 18th, 2014

Vinod Kumar

Recently my good friend and colleague Govind wrote about this topic on what are customers looking forward to when it comes to Cloud and working with Azure. The fundamental tenants that customers look at for cloud be it PaaS, SaaS or IaaS has been around:

  1. Reduced Maintenance headaches
  2. SLA backed for HA/DR
  3. Performance
  4. Synchronization with on-prem
  5. Security
  6. Backups
  7. No worry about hardware

and a few more. But for most parts the above fits the quizzing we get into. In a recent conversation, I had to outline some of the options when it comes to backup requirements with the customer which I thought is worth a share here. I am looking at this from an Azure standpoint:

For IaaS:

  1. You will need to use SQL Server Agent and build your maintenance plans that can automated. This can be scripted (powershell, TSQL or others) and done for all workloads.
  2. For SQL Server 2008 R2 CU2 onwards, we can use Backup to URL option wherein backups from Azure VM – SQL box we can point backups to a blob storage. I wrote about this a while back and you can try the same –
  3. SQL Server 2014 also supports Encrypted backups to Blob and the same article shows the same.
  4. Also from SQL Server 2014 we have option to use Managed Automated backups configured. This will take backups automatically to  blob on a predefined time or based on workload pattern. Documentation for this can be found at:

For PaaS:

  1. Since we already make sure of consistency in the Azure world, we dont have to worry on this.
  2. For Basic, Standard and Premium editions there are SLA for Point-In-Time recovery which is 7, 14 and 35 days respectively. You can read more about this at: . I highly recommend to use Powershell scripts to automate this, if you plan to use the them.
  3. In the past, I have also seen customers use Database Copy functionality to keep a copy of their database in a ready to use state every couple of days. This gives them an opportunity to go back to that version immediately without any problems. This is also an viable option if you like to use. – Since point-in-time restores are available, I am more inclined to use that for cold standby and restores. Having said that, we can still use that feature for creating a copy for Dev, Test environments from our prod servers for testing.

These are my customer notes and I plan to start publishing these customer notes from time to time here in my blog. Since we are talking about Azure, I am sure some more additional capabilities and SLA’s can change over a period of time. So please keep an eye on the documentation for the latest values.

Continue reading...


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...