Polybase error in SQL Server 2016 : Row size exceeds the defined Maximum DMS row size, larger than the limit of [32768 bytes]
I got an email form one of my reader regarding issues while working with SQL Server 2016 and Polybase, and it is as follows:
I am able to successfully install SQL with Polybase and able to query data in Azure storage but for a table I am getting error.
I am trying to pull data by creating External Data Source connection in SQL enabled Polybase features. I am getting below error as:
Cannot execute the query “Remote Query” against OLE DB provider “SQLNCLI11” for linked server “(null)”. 107093;Row size exceeds the defined Maximum DMS row size: [40174 bytes] is larger than the limit of [32768 bytes]
With the error description its quiet evident that the External tables does not support row size more than 32768 bytes. But still I take a look online and found in Azure Documentation that this is a limitation right now with Polybase. The Azure document mentions:
Wide rows support is not supported yet, “If you are using Polybase to load your tables, define your tables so that the maximum possible row size, including the full length of variable length columns, does not exceed 32,767 bytes. While you can define a row with variable length data that can exceed this figure, and load rows with BCP, you will not be be able to use Polybase to load this data quite yet. Polybase support for wide rows will be added soon. Also, try to limit the size of your variable length columns for even better throughput for running queries.”
SQL Server 2016 was released as the Community Technology Preview (CTP) 2 just yesterday (27th May 2015). And in my [previous post] I discussed about it with the Registration and Direct Download link.
After downloading the bits I installed it on my machine and the setup process was very smooth with few changes, check the YouTube video or go through the post below:
2. This will open up a new Setup window and takes you to the Setup Role page which this gives you 2 options, 1st Feature (custom) install and 2nd Default. I chose the first option to select only the features I want. Click Next.
3. Now on the Feature Selection page: you can select what exactly you want to work on. You can see here one more option PolyBase Query Service for External Data. Just select the features and click Next:
4. I got stuck at the the Feature Rule page: with this error Oracle JRE 7 Update 51 or higher is required, Failed. Just click on the Failed link and it will give you the URL from where you can download the JRE or Java Runtime Environment, download it from [here].
… the above Page also shows the limitation of Polybase that it will run only in one Instance per computer.
Re-run the rules and it will be Passed this time, click Next.
5. You will come to Instance Config page: If its is first SQL Server install on your PC then select as Default Instance, otherwise if you had installed SQL Server earlier you will have to choose a Named Instance and Click Next.
6. In the Database Engine Config page: you will see an extra option to set number of TempDB files. The label below mentions “The default value is 8 or the number of cores, whichever is lower. This value can be increased up to the number of cores”.
Update: Now with CTP 2.4 release the installer provides a separate tab for tempdb files configuration.
7. on Ready to Install page just click Install, and it will take some time to finish the installation.
What’s new in SSMS:
You will see three Databases present by default:
… some of these tables in these databases contains pdw prefix, these tables & databases are related to Polybase feature. I will blog about this feature in my coming posts as in when I get more information on this.
– – On expanding Tables you can see 2 new table options:
1. System Tables
2. External Tables – for Polybase to query Non-Relational data (also relational)
– – There is a new category External Resources (for Polybase), on expanding you get:
1. Data Sources
2. File Formats
– – And as you expand Stored Procedures folder you can see a SP present by default with following name under pdw schema:
– pdw.instpdw, with one parameter @DatabaseName NVARCHAR(MAX).
–> There is one hidden feature in SSMS, i.e. Query Store, check this [blog post] on how to enable it fr a database.
Finally the wait is over to get your hands dirty on SQL Server 2014 as the first Community Test Preview (CTP1) is released and is available for download form the Microsoft Official Site.
–> Download the SQL Server 2014 CTP1 [here].
You will need to login with your Windows Live ID to go to the download page.
–> Microsoft has released following Installation Limitations for SQL Server 2014 Community Technology Preview 1 (CTP1):
- Microsoft SQL Server 2014 CTP1 is pre-release software and should be installed only on a clean machine. It does not support upgrade from or side-by-side installations with any previous version of SQL Server, including SQL Server 2012. In addition it does not work with or support side by side installations with any client redistributables of SQL Server such as feature pack components, Visual Studio 2012 or earlier versions, SQL Server Data Tools, Data Tier Application Framework, etc.
- To administer Microsoft SQL Server 2014 CTP1 use only SQL Server Management Studio that ships as a part of Microsoft SQL Server 2014 CTP1.
- The Microsoft SQL Server 2014 CTP1 release is NOT supported by Microsoft Customer Services and Support (CSS).
- The Microsoft SQL Server 2014 CTP1 release is only available in the X64 architecture.
- The Microsoft SQL Server 2014 CTP1 release is a NON-PRODUCTION release and should not be installed and used in production environments.
- The Microsoft SQL Server 2014 CTP1 release does NOT have an associated Microsoft SQL Server 2014 CTP1 Feature Pack.
- The Microsoft SQL Server 2014 CTP1 release on Windows Azure VM is only intended for functional development and testing, and not for performance testing.
- The Microsoft SQL Server 2014 CTP1 release contains branding and versioning from older Microsoft SQL Server releases.
–> System Requirements to install SQL Server 2014 CTP1: [here]
- Minimum Windows 8 or Windows Server 2012
- X64 architecture only
- MS.Net Framework 3.5 SP1
–> Product Detail Page, [link].
Imp Note: As the first limitation point says to install it on a Clean Machine, this means the machine should not have any Pre-Releases of SQL Server and/or Visual Studio Installed.
So, to start with SQL Server 2014 CTP1 you need to get a clean machine, or a new machine with a freshly installed Windows OS.
Check more on SQL Server 2014 CTP1, here: http://www.microsoft.com/en-us/sqlserver/sql-server-2014.aspx
This year’s PASS Summit Microsoft announced lot of new features coming up for “SQL Server 2012” and version vNext.
2. Column Store Indexes: will be extended to be created with Clustered Indexes as well.
3. Hekaton & Polybase: will be major ingredients in SQL Server vNext by 2014-15.
4. SQL Server version Next: will use Hekaton for its OLTP database to take database objects into in-memory and “memory optimize” tables, thus challenging SAP-Hana and Oracle much hyped Exadata soluition.
5. SQL Server 2012 PDW: (Parallel Data Warehouse) will be using Polybase to interact between PDW and Hadoop clusters.
… I’ll be discussing more about these things in next posts, so keep tuned!!!