A Fine Slice Of SQL Server
Andy Leonard shows how to create an Integration Services ExecuteSQL task using Biml:
You do not yet have an SSIS package. You have the Biml template of a package. I can hear you thinking, “So how does this become an SSIS package, Andy?” I’m glad you asked!
We now need to execute the BimlScript and then build the output.
Follow along with the images.
Comments closedGreg Gonzalez with the announcement of the week:
Which brings me to today, one of the most exciting days in the history of SQL Sentry. I would like to introduce to you the new unified Plan Explorer v3! This edition includes all features of Plan Explorer PRO, plus two new and incredibly powerful features: Index Analysis and Performance Profiling.
These new features were originally going to be part of yet a 3rd edition called Plan Explorer ULTIMATE, and at an even higher price point. However, we ultimately came to our senses and realized that this would be counter to the mission, and that the only way ahead was to make all features of Plan Explorer freely available to everyone.
Andy Mallon goes in-depth with two of the new features:
If you’ve used Live Query Profile in Plan Explorer (or Live Query Statistics in SSMS), then you’re familiar with seeing your query plan play live as it executes. Performance Profiling adds some special sauce to that feature. Its like a DVR for the live query profile, and also displays the performance metrics as a graph below the plan diagram. The graph gives performance metrics by time, rather than by operator. In complex plans, you might have many operators doing work at the same time, and you’ll be able to see that better by looking at the graphs. Adding a time axis to the plan is a pretty ingenious way to give a new way to look at performance.
You can then replay the “DVRed” query, and re-watch the profile without re-running the query. If you save the plan as a .pesession file, the DVR’ed query with performance profile is included in that file. You can even change the speed to watch in slow-mo or fast-forward.
If you don’t already use Plan Explorer, you’re missing out on the single best query tuning add-in.
Comments closedKendra Little discusses unique constraints versus unique indexes:
Unique constraints and unique nonclustered indexes have a lot in common: unique constraints are implemented with a unique index behind the scenes.
While unique nonclustered indexes have a couple of additional features, I can still think of a scenario where it might be best to use unique constraints.
The value I see in unique constraints over unique indexes is semantic: it says to everyone, “I’m supposed to be unique by virtue of my existence.” They’re part of the data model, whereas indexes are performance optimizations to handle common data paths.
Comments closedLet’s answer a different question first. What happens when the plan gets invalidated, when the index being used gets dropped or some other structural change occurs so that the plan is no longer valid? I answered that question in this blog post. The plan being forced, after the object is dropped, becomes invalid, so that plan can no longer be used. The Query Store still attempts to apply the plan during any recompile or compile event of the query in question, but it fails and a proper plan is used. All this means, I think, the Query Store is going to ignore the new index, since a new index doesn’t invalidate an existing plan. A new index just makes new plans possible. However, when I was asked this question, this wasn’t something I had tested, so I gave a speculative, best guess, answer with plenty of caveats and the promise to provide a tested answer ASAP. Here we go.
Read on for the full answer.
Comments closedAlex Woodie reports that Yahoo is open sourcing Pulsar:
Pulsar uses Apache Bookkeeper (committed by Yahoo to open source in 2011) as its durable storage mechanism. “With Bookkeeper, applications can create many independent logs, called ledgers,” Pulsar’s project page on GitHub says. “A ledger is an append-only data structure with a single writer that is assigned to multiple storage nodes (or bookies) and whose entries are replicated to multiple of these nodes.”
Pulsar uses brokers to serve topics. Each topic is assigned to a broker, and an individual broker can serve thousands of topics, Yahoo says. “The broker accepts messages from writers, commits them to a durable store, and dispatches them to readers,” Yahoo says.
My biases would lead me to still go with Kafka over Pulsar, but it’d be interesting to see a good comp between the two.
Comments closedJustin Kestelyn describes the Impala Connector for Power BI Desktop:
Note that the connector currently only supports Import mode, which requires downloading the query output data to the local data model. In future updates, we will enhance the connector with DirectQuery capabilities, as well as with support for refresh scenarios via the Power BI Gateway. [Ed. Note: As of the August 2016 update, the Impala Connector also supports DirectQuery mode, which means you are always viewing the most up-to-date data. The functionality for both periodic refreshes and DirectQuery mode require the Power BI Gateway running either on-premise or in Microsoft Azure.]
Enabling Power BI connectivity to Impala has been a very frequently requested capability from our customers. We encourage you to give it a try and share with us any feedback or issues that you encounter via the “Send a Frown” feature in Power BI Desktop.
Good stuff.
Comments closedKendra Little describes the nested loop operator in execution plans:
One of the first things we often learn when we’re looking at plans is that SQL Server uses estimates. And sometimes, those estimates are wrong. At first glance, this looks really wrong– it estimated 11.5 rows, and actually got 20,825 rows!
Read the whole thing to see how nested loop estimates which seem way off might not actually be.
Comments closedMixing OLTP and data warehouse workload types in the same SQL Server instance is not a good idea. Why? Well, because the workload types don’t mix. As described above, an ideal buffer cache for an OLTP system contains pages used in searches as well as pages for the most used customer/items etc. Running a few data warehouse queries will change the buffer cache to contain (mostly) all dimension data. This will make OLTP operations slow. On the other hand, the ideal buffer cache in a data warehouse will contain all dimension data, which makes aggregations fast because all dimension lookups will be done in memory. OLTP operations might flush out parts of this dimension data from the cache, hurting the performance for data warehouse queries. The two workload types will keep on fighting about the content of the buffer cache, making both systems suffer.
Except in very small systems (where it doesn’t much matter), this is sound advice.
Comments closedWayne Sheffield has a Powershell cmdlet to find sessions matching SQL Saturday topics:
The output to the screen is the Event #, Speaker, Session Title and URL for the presentation.
The generated export file will also include the event name and session abstract. Additionally, the URL will be encased with the Excel HYPERLINK() function. When the export file is opened up with Excel, clicking the URL will open your browser to the session information, where the presentation material can be downloaded.
I hope that you get a lot of use out of this script.
Click through for the script and more details.
Comments closedYou then need to trust the certificate on all the linux hosts
From the IBM article:
Create ‘/etc/pki/ca-trust/source/anchors/activedirectory.pem’ and paste the certificate contents
Trust CA cert: sudo update-ca-trust enable; sudo update-ca-trust extract; sudo update-ca-trust check
Trust CA cert in Java:
mycert=/etc/pki/ca-trust/source/anchors/activedirectory.pem sudo keytool -importcert -noprompt -storepass changeit -file ${mycert} -alias ad -keystore /etc/pki/java/cacerts
And at last, please make sure every node on your cluster has access to the ad host.
LDAP support is a key part of setting up a production Hadoop cluster.
Comments closed