Tuesday, November 29, 2016

SQL Server 2016 SP1 – Rocking RELEASE!!!

Last week, I did a presentation titled "SQL Server 2016 SP1 – Rocking RELEASE!!!" to Singapore SQL User Group. The Presentation was about the things I covered over here and few more cool features released with SQL Server 2016 SP1. The best part of it was it was recorded :) This is my first presentation that has been recorded and uploaded in public platform. Obviously excited sharing it to all. Special thanks to Paul Lorret and engineers.sg team for recording it. 
On the presentation, it was certainly not my best as I had just 2 days to prepare. But still worth sharing and had fun doing it  :) Link provided below.

Slides and scripts can be downloaded from here

Saturday, November 26, 2016

Data Channel - Launch

Dear All,

I am starting a YouTube channel for SQL Server and Microsoft Data Platform Technologies called "Data Channel". What this channel is going to contain is Interviews with SQL Server and Data Platform experts like MVPs, MCM , Community Leads and Microsoft Professionals too. Interviews are generally 15 to 20 minute long,giving us a quick understanding of the subject discussed. The intention behind starting this channel is over a period of time it would develop into a huge volume of useful technical information for our community. I have shot a quick promo for the same..Take a look at it below.

If you like this idea, please subscribe to the "Data Channel" over here.  Please share your comments and follow this space for more updates on the channel. 


Sunday, November 20, 2016

SQL Server 2016 - SQL Service Doesn't Start after GDR Security update

Recently, after an security fix GDR (https://support.microsoft.com/en-us/kb/3194716) on  SQL Server 2016 RTM, SQL Service refused to start. Though, the patch completed successfully as per 
"C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\Log", the Event viewer under "System" gave the following error

"The SQL Server (I2016) service terminated with the following service-specific error: %%945"
Error didn't make much sense

The eventviewer under "Application" section gave a lot more meaningful error

FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'D:\MSSQL2016Root\MSSQL13.I20161708\MSSQL\Binn
\mssqlsystemresource.ldf'. Diagnose and correct the operating system error, and retry the operation.

From the error it was fairly clear that "mssqlsystemresource.mdf", "mssqlsystemresource.ldf" were missing. A windows search of those two files showed that the files were mysteriously moved to "C:\Program Files\Microsoft SQL Server\130\LocalDB\Binn"

Even though, there is no local DB installed, the security fix had likely moved the "mssqlsystemresource.mdf", "mssqlsystemresource.ldf" to a different folder causing the SQL Server to fail while starting. 

The fix was simple. Copy "mssqlsystemresource.mdf", "mssqlsystemresource.ldf" files from "C:\Program Files\Microsoft SQL Server\130\LocalDB\Binn" to its original location which was "D:\MSSQL2016Root\MSSQL13.I20161708\MSSQL\Binn\". After the file copy SQL Service started as usual. 

The above issue occurred on Windows 10 laptop with SQL Server 2016 Developer edition. Please note that, GDR was applied automatically by windows patch update. Yes, we would not schedule auto update of SQL Security Patches on production machines but suspect the issue would have occurred even had the GDR been manually installed.

 Hope this post would help someone, trying to troubleshoot SQL Service failure issue after applying security fix.

Thursday, November 17, 2016

SQL Server 2016 SP1 Release - Thoughts and Comments!!!

SQL Server 2016 SP1 has released last night and most of the features that were previously part of enterprise edition have been made available in standard edition now!!!! By far, the biggest change any service pack brought about in any version of SQL Server!!!

List of items provided below which moved from enterprise to standard provided below. Read about the announcement over here

  • Change Data Capture
  • Database Snapshot
  • Columnstore Index
  • In-Memory OLTP
  • Row Level Security
  • Dynamic Data Masking
  • Always Encrypted
  • Fine Grained Auditing
  • Data Compression
  • Multiple FILESTREAM Containers
  • Partitioning
  • Polybase
That's quite a change and I am super excited looking at the list of items. Quick comments few of those items.

Column store Index:

A game changer technology especially in data warehouse applications now moves to standard. But, the concern is, it is a memory driven concept known to consume significant amount of memory. The memory limit of 128 GB still holds good on standard edition. To add to that, SQL Server 2016 SP1 sets a hard limit of 25% of maximum memory set for SQL Server for the  memory used by column store index. For example if 48 GB is allocated to SQL Server, column store can use maximum of 12 GB. This implies, theoretically column store index can maximum take only  32 GB of RAM. However, the memory used by Columnstore is not counted within buffer pool's quota of memory.  So it implies, if 48 GB of memory is set for SQL Server, then SQL Server's total memory usage can go up to 60 GB ( 12 GB for column store + 48 GB of buffer pool )

Though the extended memory usage provided for Columnstore does sound useful, it may not be so effective as column store index is at its best only when it operates on large volume of data with sizable amount of memory.

In-Memory OLTP:

In my opinion, one of the best moves of SP1. Reason is even though "In memory" is ideally suitable for systems with few hundred GBs of RAM, it can prove handy for some of the mid size ones too. 25% of max memory hard limit also applies for "In memory" tables (similar to column store). For ex, lets say we have a 64 GB of RAM for SQL Server, and then 16 GB would be available for "In memory" tables.

1) Though the memory available may sound to be small, it can still be useful to place the most important and most accessed tables take advantage of "In memory OLTP". Please note that most important tables of the application may not be the largest table by size.

2) "In memory OLTP" can help applications get rid of locking and latch contentions almost completely.

3) Remember, "In memory" is not just about placing tables in the memory. SQL Server reads and writes to them differently enhancing the overall performance of the application

So, this is an enhancement which I will be pushing my application teams to take advantage of for sure.

Security n Encryption Related Features: - Row Level Security / Dynamic Data Masking / Always Encrypted / Fine Grained Auditing

It is a excellent move to place security related features in standard and other editions. My personal take has always been that security related features should be available at all editions of SQL Server. The reason why security related features needs to be available for all is application developed may not have high performance requirements demanding enterprise edition but would need key security features of SQL Server. For example, a small shopping cart application which just needs SQLExpress, deals with sensitive data ( credit card numbers, account balance etc..) requires  features like "Always Encrypted" or "Row Level security" for developing secure applications.


Another welcome change as Standard edition does deal with terabytes of data and partitioning does make lot of their lives easier

Change Data Capture / Database Snapshot :

Commonly used features in development or staging environments and making them available across all editions makes it better for environments which use different editions for Development and Production.

What remains in Enterprise:

High Availability feature like "Always ON Availability Groups" ( barring basic AG which was made available in standard since SQL Server 2016 RTM ) and "Online Index Rebuild" remain in enterprise.  Capacity limitations of lesser editions ( max 128 GB, 24 core processor limit on standard etc.. ) still remain. This makes absolute sense, as these features are primarily required by applications with extreme performance requirements and they should ideally be on Enterprise editions ( without capacity limits ).

Also notice that the features on enterprise only are more of "Infrastructure" related instead of "Development" related. This also is a move in right direction as one needs to have the same editions at development environment and production environment to make application development effective.

Overall, indeed, one of the best announcements in the entire history of SQL Server. No doubt, it is going to make several applications move to SQL Server 2016 soonest and makes my job of convincing application teams easier :)

Sunday, November 13, 2016

Residual Predicate and "Number of Rows Read" in Execution Plans

What is Residual Predicate?

Lets say one gets a query to fine tune. While checking the execution plan, if there is a Index seek, it is common for DBAs to think that the query performance is good and acceptable. Though in most of the scenarios, the idea of looking for index seeks is acceptable, there are quite a few scenarios where index seeks simply doesn't mean optimal performance. One such scenario is explained below. Consider the following query

FROM [Production].[TransactionHistory]
WHERE Productid = 801
AND TransactionID % 3 = 0

Table has a non clustered index on "ProductID" and clustered Index on "TransactionID".

The picture above as expected indicates an Index seek.

Screenshot of Index Seek Operator's details provided above.  The seek predicate section at the bottom indicates that "Index Seek" operator was used for "ProductID = 801" filter alone.

Observe the section marked in red. "Predicate" section shows
"[Production].[TransactionHistory].TransactionID % 3 = 0 ".
What it implies is the index seek filtered only for "ProductID = 801" filter condition. Additional filtering (outside the index) had to be done  for "TransactionID % 3 = 0", after the Index seek operation. This additional filters for the rows that are extracted from "Index Seek" are termed as "Residual Predicates". If the work done by residual predicate is too high then it implies that the Index is not effective.  
"Number of Rows Read" n "Residual Predicates"

On the last post, I wrote about "Number of Rows Read". Just to recap, "Number of Rows Read" indicates the number of read by the operator. "Actual Number of Rows" is the rows returned by the operator. Observe the section highlighted in Green in picture above.

Number of Rows Read: 519
Actual Number of Rows:171

The above numbers imply that Index seek operator's seek predicate ( "ProductID = 801" ) filtered 519 rows. The additional filter " "TransactionID % 3 = 0" filtered it further to 171 rows.

The difference in "Number of Rows Read" and "Actual Number of Rows" is due to the additional rows filtered for "Residual Predicate". "Number of Rows Read" information on execution plans has made it much easier to track the additional costs incurred due to "Residual Predicates"

Thursday, November 10, 2016

SQL Server 2016 - “Number of Rows Read” on Execution plan

One of the coolest things to release with SQL Server 2016 was “Number of Rows Read” information in execution plan. If you are wondering, what it is look at the picture below

What “Number of Rows Read” operator actually talks about is the total number of rows read by the query plan operator. This is not the same as the total number of rows returned (as output) from the operator.  Total number of rows returned is provided by “Actual Number of Rows”  on the query plan.

For ex:  Let’s say we have the following query

FROM [AdventureWorks2012].[Production].[TransactionHistory]
WHERE  Quantity = 2 

 Assume the above query does a table scan and filters the data. Then the “Number of Rows Read” would be total number of rows in the table.  “Actual Number of Rows” would be the total number of rows filtered by the "where" clause.

Obviously, the operators with significant differences between “Number of Rows Read” and “Actual Number of Rows” are potential areas to watch out for while query tuning. On upcoming posts, will cover scenarios where “Number of Rows Read” can be put to good use.

Sunday, November 6, 2016

Query Store - Part 3 - How it works?

Query Store collects 2 important kinds of data. They are

1) Compile time statistics - Query Plan, Estimated Costs, Estimated number of rows etc..

2) Runtime statistics - Cost of the query, row count, IO cost, memory, Degree of parallelism etc.

How it actually stores is, when plan is loaded to plan cache, its compile time data like query plan, estimated costs are loaded into plan store. Subsequently, as the plan gets reused, the runtime stats like actual cost, memory used, and actual row count are aggregated and stored. Runtime stats are aggregated at a frequency or time period defined by "Statistics Collection Interval" setting explained here. Meaning, if one sets "Statistics Collection Interval" as 10 minutes, then Avg/Min/Max/Std Dev values of runtime stats are grouped for 10 minute intervals and stored in query store. For ex - For each query plan avg running duration, IO, memory for 9:40 to 9:50, 9:50 to 10:00, 10:00 to 10:10 and so on is stored

Runtime stats are recorded asynchronously. Query plans meta data views can be used to view compile time stat and run time stats. They are

Compile time data  / Plan store:

1) Sys.query_store_query_text
2) Sys.query_store_query
3) Sys.query_store_context_settings
4) Sys. query_store_plan

Run time data:

1) query_store_runtime_stats
2) query_store_runtime_stats_interval

How to use these effectively, is coming up in next post

Friday, November 4, 2016

Folders to Check for errors in SQL Patching or Installation

Recently, while applying SQL Server 2012 Service Pack 3, encountered the following error and SQL Patch failed to upgrade.

"The patch installer has failed to update the following instance: MSSQLSERVER. To determine the reason for failure, review the log files"

For troubleshooting the same ( or any patching ), I had to check a sequence of folders to identify the root cause. Thought it would be useful to document the list of files and folders to check if one encounters errors during patching or installation

1) "\SQLVersion number\Setup Bootstrap\Log\"
2) "\SQLVersion number\Setup Bootstrap\Log\Timestamp"

3) "\SQLVersion number\Setup Bootstrap\Log\Timestamp\MSSQLSERVER"

is the installation path - Example "C:\Program Files\Microsoft SQL Server\"

SQLVersion number - 90 for SQL 2005, 100 for 2008, 110 - 2012, 120 - 2014,130 - 2016 

Step 1:
\SQLVersion number\Setup Bootstrap\Log\summary.txt" Look for summary.txt to know the activity was a success or failure. 

On the same folder, locate the folder with the timestamp of your activity.

Open the folder for more details on the error. "Summary_*.txt" would be similar to the file read on log "\SQLVersion number\Setup Bootstrap\Log\summary.txt" . Identify the 
component that failed. It can be "Database Engine" / "Replication" or any other component of SQL Server

Step 2: 

Open \SQLVersion number\Setup Bootstrap\Log\Timestamp\ and look at the following files for more info

1) Detail.txt - Search using keywords like "Error" or "Failed" to quickly get to the issue
2) SQLServer_ERRORLOG_*.txt 
3) Summary_*.txt

Step 3: 

If still no clue, Open \SQLVersion number\Setup Bootstrap\Log\Timestamp\MSSQLSERVER. Open "Detail.txt". Search for keywords like "Error" or "Failed" This should help one figure out what actually caused the error. 

Besides these, "InstallationDir>\SQLVersion number\Setup Bootstrap\Log\Timestamp\MSSQLSERVER" also contains one file for each component patched. you may search thro them if the root cause is still not clear from above files.

In my case, the issue was related to permissions on "C:\Windows\System32\". The error message was 0

Failed to version-copy file '\MSSQL11.MSSQLSERVER\MSSQL\Binn\hadrres.dll' to 'C:\Windows\system32\hadrres.dll'. Exception data is: System.IO.IOException: The requested operation cannot be performed on a file with a user-mapped section open.

The Error message was found from InstallationDir>\SQLVersion number\Setup Bootstrap\Log\Timestamp\MSSQLSERVER\Detail.txt. After granting permissions on the file, issue was fixed and patch was reran and it updated successfully.