The story begins with a Sql Server upgrade from 2012 to 2016. But the upgrade itself was really just a conduit for new discoveries of our exiating but not understood issues and new options not available in 2012 version.
Learning #1: Vmware CPU hot swap option is bad. Upon first upgrading performance was worse than 2012 overall and some queries seemed to hang without returning rows. Excessive cxpacket waits and blocking thread on the same session indicated a thread issue. I found the vmware best practices for sql server said not to use this option and that all our sql servers were setup using this option. Turning it off and bouncing the severs gave us from 30 to 80% speed increase for parallel operations and the freezing issue became much less frequent.
Learning #2: SSIS basic logging trashes performance. SSIS in 2016 has the option of custom logging. Knowing we dont need or use logging unless there’s an error (we instead rely on the much more useful and efficient BiXpress audit framework), I created a custom log plan (only takes a few minutes) to only log error events and replaced all our basic log assignments in our job steps. This knocked off another 30% or so from all our job steps.
Learning #3: New cardinality estimator can loose its mind with nasty queries. I’ve had a problematic query for sometime that has some implicit datatype conversions. Since it’s generated with dynamic where clause for incremental loads, parameter sniffing has been an issue for performance. I had previously solved this by using the recompile option. But in 2016 the query would unpredictably go serial. And instead of running 3 minutes it would run for an hour and up to 6 hours! The solution? A prefer parallel hint. This took the query down to under 1 minute!
Lesson #4: Updatable clustered columstores! Previously I dropped and recreated non-clustered columnstores…I knew being able to update and not have to spend 3 minutes recreating them would save the 3 minutes but also cost on the update side. Plus the obvious biggy of risk of lock issues with competing queries. Just stopping the drops helped but by converting to clustered helped for all the processes that use the related fact tables. And no noticeable hit on the update side (Testing proved the existing merge update superior to alternatives).
There’s a little known option called Hot Swap CPU. Don’t use it!!!!!!
You find it in the Vmware console in the “Manage” tab for VM Hardware. Open up the x zzzCPU section and you’ll see it. If it is checked, then this is bad…its real bad. If you have <= 8 cores then it confuses Sql Server threads. If you have > 8 cores, it also disables Numa awareness. Then you’ve gone from bad to worse.
The good news is, simply uncheck this option and reboot to fix it. And you can be the immediate hero – tell them it took many hours of hard work to increase the performance of their server.
I made this change and saw between an immediate 30% to 80% improvements in all my queries using parallelism. And the amount of cxpacket waits declined significantly. This was true both for my non-prod 8 core servers as well as the 14 core production server.
If you don’t believe me, believe Vmware…read their best practices guide at SqlServer on Vmware Best Practices Guide.
Review of Melissadata Matchup for SSIS
Review of Wherescape Red
Wherescape Red Review
Review of Microsoft Master Data Services (MDS)
Review of Microsoft SqlServer Integration Services (SSIS)
Or maybe not! Here’s how you can do it.
There are other older and alternative drivers but I found the setup easiest with the latest Oracle and Microsoft software. And the latest version of SSDT can be installed alongside older software as well as develop backwards compatible SSIS and SSRS. Oh and schema and data compare are now built-in! So why are you waiting to upgrade?
Install SSDTSetup.exe file (this is the new 2016 SSDT software that includes visual studio 2015) found at https://msdn.microsoft.com/en-us/library/mt204009.aspx
Install SetupODTforVS2015.exe file found at Oracle’s download site.
Copy your tnsnames.ora and sqlnet.ora files to c:\program files (x86)\oracle developer tools for vs2015\network\admin\
When you start SSDT or just visual studio, to go Tools – Connect to Database – Change the data source to Oracle Database with Data provider of ODP.NET – Next. The Data source name should reflect the databases in your tnsnames.ora file if its setup correction. Just sign in as normal. It will then appear in your server “Server Explorer” tool under Data Connections.
You should end up at this screen with the ability to scroll through your tnsnames aliases and the rest should be obvious…
Problem: Series of child-parent relationship rows where you need to find the topmost parent.
Script to create the situation below…
— Create a Table
drop table party_merge
CREATE TABLE Party_merge(
FromPartyID INT PRIMARY KEY,
— Insert Sample Data
— 3 gets gets 4 children (1, 2, 8, 9) assigned to merge to it and then itself gets merged to id=10
— 2 is the new parent for 4, 5, and 7
— 11 gets 12 assigned. Only parent 12 has only one child (11)
— 14 gets parent assigned as 14 (due to source system issue)
INSERT INTO Party_merge
SELECT 1, 3
SELECT 2, 3
SELECT 14, 14
SELECT 4, 2
SELECT 5, 2
SELECT 7, 2
SELECT 8, 3
SELECT 9, 3
SELECT 3, 10
SELECT 11, 12
— Check the data
ORDER BY FromPartyID, ToPartyID
SELECT p1.FromPartyID, p1.ToPartyId
FROM Party_merge p1
LEFT OUTER JOIN Party_merge p2
ON p1.ToPartyID = p2.FromPartyID
WHERE p1.FromPartyID <> p1.ToPartyID
This is a common error when doing null handling when replacing incoming value with something larger since the datatype defaults based on the incoming value.
You need to not just CAST or CONVERT to a different larger datatype but once but you have do it twice – both on the incoming column and then on the result. This can also happen when doing aggregations as well as it may demand overriding the default decimal or integer size for the resulting value.
Below demonstrates a further complication where CAST doesn’t work but CONVERT does…
Following doesn’t work…
SELECT CAST(ISNULL(CAST([Person_Acxiom_Abilitec_Id] AS VARCHAR(36)), NEWID()) AS VARCHAR(36)) AS Person_Acxiom_Abilitec_Id…
CAST(ISNULL(CONVERT(VARCHAR(36),[Person_Acxiom_Abilitec_Id]), NEWID()) AS VARCHAR(36)) AS Person_Acxiom_Abilitec_Id
The only difference is using CONVERT instead of CAST.