A Fine Slice Of SQL Server
One of the announcements of what is new in CTP 3.2 was that SQL Server now includes Azul System’sZulu Embedded right out of the box for all scenarios where we use Java in SQL Server, including Java extensibility.
So, in this post, we look at the impact, (if any), this has to how we use the Java extensibility framework in SQL Server 2019.
This also affects PolyBase.
Comments closedArun Sirpal takes us through online clustered columnstore index creation in Azure SQL Database:
What tier do you need to create one of these things? Let’s see.
CREATECLUSTERED COLUMNSTORE INDEXcciSales ON[SalesLT].[ProductModelProductDescription] WITH( ONLINE = ON)But I get this message, Msg 40536, Level 16, State 32, Line 1
‘COLUMNSTORE’ is not supported in this service tier of the database. See Books Online for more details on feature support in different service tiers of Windows Azure SQL Database.
Read on to see the minimum tier which allows online creation of clustered columnstore indexes.
Comments closedBartosz Ratajczyk continues a quest to draw SSIS packages as SVGs:
To get the
ValueandExpressionproperties I need to find the precedence constraint in the.dtsxfile during the XSL transformations. It requires three changes in thepackage2svg.xsl:– I have to pass the name of the
.dtsxfile
– I have to read the XML from the.dtsxfile
– I have to use theDTSnamespace because it’s the namespace of the .dtsx file
Read on for more. Bartosz to this point has covered the control flow.
Comments closedBert Wagner gives us some tips for reading execution plans in SQL Server:
Execution plans show the steps SQL Server takes to execute your query. Each icon in the graphical execution plan is known as an operator, and the most common way to read a plan is by starting with the top right most operator and following the arrows to the left.
When you reach a join or concatenation operator where multiple branches merge into one operator, you can proceed to the right-most operator of one of the lower branches and start the process of reading right to left again. In general, this can be summed up as reading a plan right to left, top to bottom.
Right-to-left, top-to-bottom gives you the flow of information and that’s quite important. To understand how the engine works, though, you also need to read left-to-right, as Brad Schulz’s outstanding one-act play demonstrates.
Comments closedHasan Savran revises some indexing recommendations based on changes to Cosmos DB:
Lazy indexing used to be an option. It’s not in any CosmosDB documentation anymore. By using Lazy indexing, you could save 20 to 30 percent for Request Units. Just like anything else in life, you get what you pay for when it comes to Lazy indexing. By selecting Lazy indexing, you are saying that eventually Indexes will be updated. If Indexes are not updated, that means your queries might not return all the data since all data might not be indexed yet. Lazy indexing is still an option, nobody talks about it for a good reason. In my opinion, it should be listed as obsolete feature or it should have a better documentation about how it works or why it might not be a good option for your solutions.
If you use Lazy Indexing to reduce Request Units in your solution, change it to consistent now unless you have a really good reason!
Read on for more advice in this vein.
Comments closedPretty recently hit very interesting problem of how SQL Server concatenates strings.
The very same SELECT clause in some cases produced VARCHAR(MAX), but in same cases it cut the result to VARCHAR(8000) ( or NVARCHAR(4000) )
After a research I’ve discovered following:
Read on to see what Slava has discovered.
Comments closedGilbert Quevauvilliers takes us through version control with PBIX files:
In the second part of my blog post I am going to detail how to use the version control with Power BI Desktop files.
This will include adding files, checking files in and out, viewing previous versions and reverting to previous versions.
If this is the first time you are reading this blog post, I would highly suggest reading Setting up Version Control for my Power BI Desktop Files (PBIX) with no additional Cost * | Part 1
In short, Gilbert treats PBIX file as any other data file. These can get kind of beefy, though, so I’ve also saved them as templates—that way, you get the structure without pulling in all of the data.
Comments closedLeo Furlong shows how we can load an Azure SQL Data Warehouse dimension with Databricks:
Ingesting data into the Data Lake occurs in steps 1 and 2 in our architecture. Azure Data Factory (ADF) provides an excellent mechanism for loading data from source applications into a Data Lake stored in Azure Data Lake Store Gen2. In fact, Microsoft offers a template in the ADF Template gallery which provides a metadata driven approach for doing so. The template comes with a control table example in a SQL Server Database, a data source dataset and a data destination dataset. More on this template can be found here in the official documentation.
I appreciate that this is a full walkthrough of the process, not just one step.
Comments closedSatish Sharma wraps up a series on Kafka and Kafka Streams:
Consider a hypothetical fleet management company that needs a dashboard to get the insight of its day to day activities related to vehicles. Each vehicle in this fleet management company is fitted with a GPS based geolocation emitter, which emits location data containing the following information
1. Vehicle Id: A unique id is given to each vehicle on registration with the company.
2. Latitude and Longitude: geolocation information of vehicle.
3. Availability: The value of this field signifies whether the vehicle is available to take a booking or not. Current Status (Online/Offline) denotes whether the vehicle is on duty or not.
Read through the article and then check out Satish’s GitHub repo for more.
Comments closedErik Darling explains that abstraction can be the cause of performance woes in SQL Server:
There’s no “caching” of steps in a query. If you nest a view however-many-levels-deep, each step isn’t magically materialized.
Same goes for CTEs. If you string a bunch together and reference them multiple times, you’ll start to see some very repetitive branches in your query plans.
Now, there are tricks you can play to get what happens inside of one of these steps “fenced off”, but not to get the result set fully materialized.
It’s a logical separation, not a physical one.
In addition, as your query gets more and more complex, the optimizer eventually gives up and gives you what will likely be an ugly version of its implementation because there are too many potential solutions.
Comments closed