A Fine Slice Of SQL Server
Steve Jones looks at one Powershell function inside dbatools:
I like using PoSh for some tasks, especially when I don’t have an easy way to do something in SSMS or want to run a task across a variety of instances. In this case, as I glanced through the September updates, I found a good one.
Get-DbaTcpPort
I don’t love the mixed naming, and I’ll get used to it, but I do love the autocomplete in PoSh.
Steve has lots of screenshots walking you through this function.
Comments closedI look at what the deal is with Polybase and Tar files:
The select statement returned 3104 records, exactly 4 shy of the 3108 I would have expected (777 * 4 = 3108). In each case, the missing row was the first, meaning when I search for LastName = ‘Turgeon’ (the first player in my data set), I get zero rows. When I search for another second basemen in the set, I get back four rows, exactly as I would have expected.
What’s really interesting is the result I get back from Wireshark when I run a query without pushdown: it does actually return the row for Casey Turgeon.
This isn’t an ideal scenario, but it did seem to be consistent in my limited testing.
Comments closedRoi Ravhon shows how to take down an Elasticsearch instance:
Cardinality aggregation is used to count distinct values in a data set. For example, if you want to know the number of IPs used in your system, you can use this aggregation on an IP field and then count the results.
Despite the usefulness, cardinality can also be a touchy Elasticsearch feature to use. Performing a unique count on a field with a multitude of possible values when configuring a visualization, for example, can bring Elasticsearch to a halt.
Most of it comes down to writing good queries. But if you don’t know what good Elasticsearch queries look like, read on.
Comments closedKenneth Fisher looks to see when his indexes were created (or at least updated):
SQL Server stores a create date and a change date for each object in the sys.objects system view.
Unfortunately while tables, views and even constraints are objects, indexes are not. Or at least they aren’t stored in the sys.objects system view. And the sys.indexes system view doesn’t have any dates associated with it. So how do we get the create/update date on an index? Well, short answer is you don’t. Long answer is that in some cases you can get some information.
These aren’t ideal answers, but they can be better than nothing.
Comments closedDevin Knight’s Power BI custom visuals series continues:
The Scroller the values move from left to right on an animated scroll bar.
The settings allow you to customize the speed, size, status indicator and colors used.
If you’d like to see the status indicator then you must have a deviation value.
Maybe it’s just me, but I’m not a fan of tickers. They’re too visually noisy for my taste.
Comments closedWhat I do want to bring to your attention is the differences between the two when it comes to redirecting error rows, specifically rows that are truncated. One of the beauties of SSIS is the ability to output rows that fail to import through the error pipeline and push them into an error table for example. With fast load there is a downside to this, the whole batch will be output even if there is only 1 row that fails, there are ways to handle this and a tried and tested method is to push those rows into another OLE DB Destination where you can run them either in smaller batches and keep getting smaller or simply push that batch to run in row-by-row to eventually output the 1 error you want. Take a look at Marco Schreuder’s blog for how this can be done.
One of the issues we have exerienced in the past is that any truncation of a column’s data in fast load will not force the package to fail. What? So a package can succeed when in fact the data itself could potentially not be complete!?! Yes this is certainly the case, lets take a quick look with an example.
Read on for details and potential workarounds.
Comments closedPedro Lopes discusses new properties which show up on execution plans:
This caused some friction, and so we are happy to announce that in the latest (October) release of SSMS, these attributes are now readily available in the Properties window of an Actual Execution Plan, in the scope of each operator.
Here’s an example with the same query as the March post, where we can see the Actual Number of Rows returned by each of the two threads executing the index seek.
Click through for more details.
Comments closed