Curated SQL – Page 862 – A Fine Slice Of SQL Server

As noted in the previous post in this series, memory in SQL Server is generally divided between query plans in the plan cache, and data in the buffer pool (other uses for memory in SQL Server are listed later in this post).
The official documentation tells us:
[T]he default setting for max server memory is 2,147,483,647 megabytes (MB).
Look carefully at that number. It’s 2 billion megabytes. In other words, we might think of it as either 2 million gigabytes, 2,048 terabytes, or 2 petabytes.

Randolph is writing this like we don’t all have multiple petabytes of RAM on each machine.

Comments closed

Bash Script Introductions

Published 2019-05-20 by Kevin Feasel

Kellyn Pot’vin-Gorman continues a series on Bash scripting:

For Part II, we’ll start with the BASH script “introduction”.
The introduction in a BASH script should begin the same in all scripts.
1. Set the shell to be used for the script
2. Set the response to failure on any steps, (exit or ignore)
3. Add in a step for testing, but comment out or remove when in production
For our scripts, we’ll keep to the BASH format that is used by the template scripts, ensuring a repeatable and easy to identify introduction.

Click through to see what that entails.

Comments closed

Multi-Line Powershell Comments

Published 2019-05-20 by Kevin Feasel

Jess Pomfret shows how you can build out Powershell comments with multiple lines of code in them:

You can see above the first example looks good, however in the second example the first two lines should both have a prompt to show they are code. I spent a little while Googling this without much avail. I then figured, somewhere within dbatools there must be an example with two lines of code. Sure enough I found my answer, and it’s pretty straightforward.

Click through for the answer, as well as one of the most important Powershell cmdlets you’ll ever find on the Internet.

Comments closed

Making Corporate Color Palettes Palatable

Published 2019-05-20 by Kevin Feasel

Meagan Longoria takes us through a corporate coloring problem:

Last week, I had a conversation on twitter about dealing with corporate color palettes that don’t work well for data visualization. Usually, this happens because corporate palettes are designed with websites and/or marketing collateral in mind rather than information graphic design. This often results in colors being too bright, dark, or dull to be used together in a report. Sometimes the colors aren’t easily distinguishable from each other. Other times, the colors needed for various situations (main color, ancillary colors, highlight color, error color, KPIs, text, borders) aren’t available in the corporate palette.
You can still stay on brand and create a consistent user experience with a color palette optimized for data visualization. But you may not be using the exact hex values as defined in the corporate palette. I like to say the data viz color palette is “inspired by” the marketing color palette.

Click through for lots of goodies, including a link to a really interesting color tester.

Comments closed

Minimal Logging into Empty Clustered Indexes

Published 2019-05-20 by Kevin Feasel

Paul White explains how to perform minimal logging when using the INSERT..SELECT pattern to insert into an empty table with a clustered index:

The summary top row suggests that all inserts to an empty clustered index will be minimally logged as long as TABLOCK and ORDER hints are specified. The TABLOCK hint is required to enable the RowSetBulk facility as used for heap table bulk loads. An ORDER hint is required to ensure rows arrive at the Clustered Index Insert plan operator in target index key order. Without this guarantee, SQL Server might add index rows that are not sorted correctly, which would not be good.
Unlike other bulk loading methods, it is not possible to specify the required ORDER hint on an INSERT...SELECT statement. This hint is not the same as using an ORDER BY clause on the INSERT...SELECT statement. An ORDER BY clause on an INSERTonly guarantees the way any identity values are assigned, not row insert order.

Read on to see what you can do.

Comments closed

SQL Server and Recent Security Patches

Published 2019-05-20 by Kevin Feasel

Allan Hirt takes us through recent security updates and how they pertain to SQL Server:

After Spectre and Meltdown a few months back (which I cover in this blog post from January 4), another round of processor issues has hit the chipmaker. This one is for MDS (also known as a ZombieLoad) This one comprises the following security issues: CVE-2019-11091, CVE-2018-12126, CVE-2018-12127, and CVE-2018-12130. Whew! Fun fact: CVE stands for “Common Vulnerabilities and Exposures”.
As of now, this is only known to be an Intel, not AMD, issue. That is an important distinction here. The official Intel page on this issue can be found at this link. This issue does not exist in select 8th and 9th generation Intel Core processors as well as the 2nd generation Xeon Scalable processor family. (read: the latest stuff)

Be sure to read through all of this. Most of the notes are for non-SQL Server items which have an impact rather than bugs in SQL Server itself, but that doesn’t make patching any less important.

Comments closed

Automating Grafana Dashboard Creation

Published 2019-05-16 by Kevin Feasel

Rishi Khandelwal walks us through automating Grafana dashboard and alert creation:

We have already discussed the creation of Grafana dashboards and alerts in my previous blogs. We were doing that manually. But think of, if we need to do that in more than 10 environments then we need to repeat that manual process again and again and sometimes we get frustrated by doing all these repetitive stuff.
We should have some automated process for doing this. So let’s discuss that.

Read on for an example.

Comments closed

Temporal Tables with Flink

Published 2019-05-16 by Kevin Feasel

Marta Paes shows off a new feature in Apache Flink:

In the 1.7 release, Flink has introduced the concept of temporal tables into its streaming SQL and Table API: parameterized views on append-only tables — or, any table that only allows records to be inserted, never updated or deleted — that are interpreted as a changelog and keep data closely tied to time context, so that it can be interpreted as valid only within a specific period of time. Transforming a stream into a temporal table requires:
– Defining a primary key and a versioning field that can be used to keep track of the changes that happen over time;
– Exposing the stream as a temporal table function that maps each point in time to a static relation.

It looks pretty good.

Comments closed

Converting Existing SSIS Packages to Biml

Published 2019-05-16 by Kevin Feasel

David Stein shows off a conversion tool built into BimlExpress:

BimlExpress is a free Visual Studio add-in created by the good folks at Varigence. Its a full featured Biml editor which allows you to dynamically create SSIS packages. It was first released back in 2017, and the latest version is 2019 (of course). The current version supports Visual Studio 2010 through 2019 as well as SQL Server 2005 through 2019.
Prior to it’s release, Biml was written with Bids Helper, now known as BI Developer Extensions. While BI Developer Extensions has many nice features, you should no longer use it to work with Biml as it is no longer being updated/supported.

I’m pleasantly surprised by this. It used to be limited to BimlStudio (nee Mist) and BimlOnline.

Comments closed

PolyBase — SQL to SQL

Published 2019-05-16 by Kevin Feasel

I have a post covering PolyBase from SQL Server to SQL Server:

Historically, PolyBase has three separate external entities: external data sources, external file formats, and external tables. External data sources tell SQL Server where the remote data is stored. External file formats tell SQL Server what the shape of that data looks like—in other words, CSV, tab-separated, Parquet, ORC, etc. External tables tell SQL Server the structure of some data of a particular external file format at a particular external data source.
With PolyBase V2—connectivity with SQL Server, Cosmos DB, Oracle, Spark, Hive, and a boatload of other external data sources—we no longer need external file formats because we ingest structured data. Therefore, we only need an external data source and an external table. You will need SQL Server 2019 to play along and I’d recommend keeping up on CTPs—PolyBase is under active development so being a CTP behind may mean hitting bugs which have subsequently been fixed.

I want this to get even better, to the point where external tables are a no-brainer over linked servers in terms of performance.

Comments closed

Curated SQL Posts