Sunday, March 18, 2018

Webinar on Adaptive Query Processing for Data Platform Geeks

My session on Adaptive Query Processing has been finding significant interest in Data Platform events :) Delivered 2 webinars for Data Platform Geeks community, one of them at American time and other with Indian time. Indian time webinar gave me massive attendance with 100 enthusiasts joining in. Recording of one of the webinars shared below for people who couldn't join in.

Data Platform Geeks have quite a few webinars going on. Do check out their website over here and join them.  

Friday, February 9, 2018

Data Channel Interview - 10 - Dinesh Priyankara on Azure Data Lake Analytics

Here we go for the 10th Interview of Data Channel. In this interview, Dinesh Priyankara, a 12-time MVP from Sri Lanka, demystifies Azure Data Lake and ADL Analytics. This interview is an excellent starting point if you looking to learn Azure Data Lake Analytics. Personally, Imo, one of my best performances as an interviewer asking the right questions. Happy Learning!!!

Sunday, January 21, 2018

Configuration Manager Missing - Windows 10

SQL Server Configuration Manager is one of the fundamental tools for a DBA to do basic tasks like starting or stopping SQL Service, knowing how many instances and what components of SQL Server are been installed on the machine.

Imagine this, suddenly you find your configuration manager missing. Pretty annoying and can be one of those things which can give that 15 minutes of extreme irritating moments. Not sure, why it happens ( Windows update perhaps ? ) but have seen this in couple of machines now. You can find it at "C:\Windows\SysWOW64\" directory

For SQL Server 2017 it is : C:\Windows\SysWOW64\SQLServerManager14.msc 
For SQL Server 2016/2014 it is : C:\Windows\SysWOW64\SQLServerManager13.msc / SQLServerManager12.msc

To add it back to the start menu, copy it from the original location provided above and paste it to

C:\ProgramData\Microsoft\Windows\Start Menu\Programs\Microsoft SQL Server 2017\Configuration Tools\

This would put back the configuration manager under start menu. Hope it helps!!!

Sunday, December 31, 2017

Azure SQL Database - Backup

Most of you must be knowing that backup in Azure SQL Database fully automatic. After the database is created and the database is automatically backed up by Azure. One can restore to any point in time for a period of 7 days for the basic subscription and 35 days for standard and premium.

Most of the above are already known to you. But, what can be little surprising for someone learning Azure SQL Database is, one can never access backup files used for recovering the database. Meaning, you can only restore the database as another azure sql database but you can never download the automatic backup files like you would do when you configure backups on your on-premise database.

So, what do you do when you need the backup file of Azure SQL Database for reference?

Create a bacpac file backup (similar to your full back up on - premise database ) of Azure SQL Database

1) Connect to the Azure SQL Database via SSMS
2) Right click tasks-> Export -Data-Tier Application. Follow the screenshots below to manually take a backup of Azure SQL Database

So, if you need an older backup of the azure database (yesterday 8:00 am backup), then recover the older version of the database using the Azure portal to a different azure sql database and extract the bacpac file using the steps explained above.

Saturday, December 23, 2017

My Interview at Data Platform Geeks 2017

Dear All,

Earlier this year in August, I had the privilege of speaking at Data Platform Summit 2017 at Bangalore. As always a terrific experience to hangout, interact and learn from the best in the business. Here is my interview to DPS Team about my experience at the summit

Adaptive Query Processing - Adaptive Joins - Plan resue - 3

Continuing on the series on Adaptive Query Processing covered over here, this post would cover what happens to adaptive joins when plans are reused.

To give a little bit of context to the post, the earlier posts in the series explained how SQL Server 2017's Adaptive joins made it possible for SQL Server to switch between nested loop join and hash join in query plan during runtime depending upon the number of rows returned by one of the joining tables. "Adaptive join threshold" is a row count threshold used to decide whether to go for Nested loop join or hash join.
So, the question is what happens when the query plan is reused? Does the Query plan dynamically switch between the nested loop and hash join when the query plan is reused or does it just go with the last choice? What is the role of "Adaptive join threshold" when the plan is reused? To check it out, let's test it



EXECUTE sp_executesql

         N'SELECT  [fo].[Order Key], [si].[Lead Time Days], [fo].[Quantity]

FROM    [Fact].[Order] AS [fo]

INNER  JOIN [Dimension].[Stock Item] AS [si]

       ON [fo].[Stock Item Key] = [si].[Stock Item Key]

WHERE   [fo].[Quantity] = @quantity', N'@quantity int',@quantity = 360

EXECUTE sp_executesql

         N'SELECT  [fo].[Order Key], [si].[Lead Time Days], [fo].[Quantity]

FROM    [Fact].[Order] AS [fo]

INNER  JOIN [Dimension].[Stock Item] AS [si]

       ON [fo].[Stock Item Key] = [si].[Stock Item Key]

WHERE   [fo].[Quantity] = @quantity', N'@quantity int',@quantity = 130

Step 1: Clearing the cache
Step 2: Run the query using sp_executesql and pass the variable, so that plan is reused
Step 3: Run the same query with a different parameter value, so that the plan is reused with a different value.

Don't forget to turn on the Show query plan option to see the query plan.
Query plans for both the queries provided below

Both use Adaptive Joins which is a good news.
Now, let's compare the properties of Adaptive Join operator to understand more

First observation: Actual join type for both executions are different. So, Adaptive Join switches the join operator dynamically even when the plan is reused
 Second Observation: Adaptive threshold of rows remains the same and this implies it is fixed at the compile time or when the plan was first generated. Hence "Estimated Join Type" also remains the same.

 So, to summarize, Adaptive Join threshold is computed at the compile time. So, when the plans are reused, the same adaptive join threshold is used for all executions. However, Adaptive join still switches the join operator at each execution, using the adaptive join threshold calculated at the first time the plan was generated.

Saturday, December 2, 2017

Data Channel Interview - 09 - Mr.Madhivanan on Best Practices in TSQL Programming

Dear All,

Very happy to share the 9th interview of #DataChannel. Mr.Madhivanan Ramachandran, 10 time Microsoft MVP Award winner and a Data Platform expert from India discusses "Best Practices for TSQL Programming".  Mr.Madhivanan demystifies quite a few concepts with his to the point answers in this interview. Happy Learning!!!