Press "Enter" to skip to content

Author: Kevin Feasel

Things You Might Not Need in SQL Server

Erik Darling has two posts of a similar theme. First up is that you might not need to offload reads:

Duplicating data for reporting, outside of moving it to a data warehouse where there’s some transformations involved, can be an expensive and perilous task.

Your options come down to a native solution like AGs, Replication, or Log Shipping. You can brew something up yourself that relies on native stuff too, like Change Data Capture, Change Tracking, Temporal Tables, or triggers.

Erik’s suggestion here is that appropriate query tuning (and I’ll add proper database design!) does more for you than scaling out.

Then, Erik takes it one step further and recommends against certain features in SQL Server:

Consulting gives you a lot of opportunities to talk to a lot of people and deal with interesting issues.

Recently it occurred to me that a lot of people seem to confer magic button status to a lot of things that always seem to be If-I-Could-Only-Do-This features that would solve all their problems, and similarly a Thing-That-Solved-One-Problem-Once turned into something that got used everywhere.

I do agree with Erik on partitioning (which makes administration easier but usually only helps with read queries on columnstore indexes with huge numbers of rows), In-Memory OLTP (which could have been an incredible feature if it worked as I’d hoped), dirty reads, and over-use of recompilation. For sufficiently busy environments, I disagree with Erik’s take on fill factor, having been convinced by Jeff Moden that there’s a lot of value in well-thought-out fill factor settings, but to make the most of it requires more knowledge of the data than DBAs typically take the time to learn.

Comments closed

Long-Term Storage in Gold

Alex Woodie discusses a new storage solution:

In the hunt for the optimal long-term storage, media comes and media goes. From cave paintings and daguerreotypes to tape and optical disk, humans continue to search for new ways to preserve information for use by subsequent generations. Now an Upstate New York company is touting a new type of archive that leverages an element with the ultimate in longevity: gold.

Totenpass today announced the launch of its novel storage solution that’s made of gold. Data is written directly to the surface of the gold cartridge (which features some nickel) using a laser etching process. The data takes the form of shrunken down human-readable images or documents, or machine-readable data that can be encrypted and read with a smartphone app (which is still in development).

This makes perfect sense to me. If you write it in metal, that means Ruin can’t read or modify it.

Comments closed

Measuring the Performance Impact of TDE

Manvendra Singh does the math:

Transparent Data Encryption (TDE) encrypts database files to secure your data. It also encrypts the tempdb database to secure your data in a temporary space. The process of encryption and decryption adds additional overhead to the database system. Even non-encrypted databases hosted on the same SQL Server instance would have some performance degradation because of tempdb encryption. Today I will show you performance impact analysis using few simple T-SQL statements by comparing their stats gathered before and after enabling TDE.

I will execute T-SQL statements (INSERT, UPDATE, SELECT, BACKUP DATABASE) before and after enabling encryption (Transparent Data Encryption) and gather their performance statistics during each execution. Finally, once we will have performance stats taken before and after enabling encryption then we will compare them to understand the performance impact analysis. I have used DBCC DROPCLEANBUFFERS before executing each query to clean the buffer cache.

Click through for the results. My rule of thumb is about a 5% performance overhead, but Manvendra shows us some of the particulars of what that means.

Comments closed

Organizing a Power BI Workspace: The Checklist

Melissa Coates has a plan:

How to organize workspaces in Power BI is one of those topics that comes up a lot. On one hand, it’s really easy to quickly create a workspace and keep moving. At the same time, it’s also really useful to have a strategy for how you scope your workspaces so they don’t get out of hand over time.

In this post & video we’re going to cover 4 sets of criteria to consider when planning for workspaces in the Power BI Service.

Click through for the video, as well as a post with the details.

Comments closed

Visual Studio Code, Markdown, and Snippets

Robert Cain takes us through Markdown and snippets in Visual Studio Code:

Seriously though, I do find this documentation language very useful, and easy to use. With just a few commands I can produce a nicely formatted document that can be displayed in my code editor, as well as on platforms like GitHub. I’ve even begun authoring these blog posts in Markdown.

A big reason for me is the ability to integrate it into my projects. VSCode, as well as the full blow Visual Studio, support Markdown (with of course the proper extensions installed). When I create a new PowerShell project in VSCode, I can store the projects documentation in Markdown format right alongside the PowerShell code.

By the way, two VS Code extensions I can highly recommend for their Markdown support are Markdown All in One and markdownlint.

Comments closed

Generating Markdown from SQL Server Metadata

Thomas Williams has built a documentation engine:

Sometimes there’s a need to create database documentation – an old fashioned list of tables, columns, views etc. – as part of delivering a project, or for analysts, project managers and end users.

In these situations I tend to automate. SQL Server is very “meta” in that it contains tables and records that describe, well, other tables and records. Coupled with the MS_Description extended property, it’s a powerful way to keep up-to-date documentation in close proximity to the code itself.

The script below will generate Markdown for database tables, views, stored procedures and functions. Column names and data types are listed for tables. View definitions are output. For stored procedures and functions, only the MS_Description extended property will be output.

Click through for a big disclaimer and a bigger script.

Comments closed

Fundamentals of Inline TVFs

Itzik Ben-Gan explains Inline Table-Valued Functions:

Compared to the previously covered named table expressions, iTVFs resemble mostly views. Like views, iTVFs are created as a permanent object in the database, and therefore are reusable by users who have permissions to interact with them. The main advantage iTVFs have compared to views is the fact that they support input parameters. So, the easiest way to describe an iTVF is as a parameterized view, although technically you create it with a CREATE FUNCTION statement and not with a CREATE VIEW statement.

It’s important not to confuse iTVFs with multi-statement table-valued functions (MSTVFs). The former is an inlinable named table expression based on a single query similar to a view and is the focus of this article. The latter is a programmatic module that returns a table variable as its output, with multi-statement flow in its body whose purpose is to fill the returned table variable with data.

Now that we have that sorted, click through to see examples and dive into performance ramifications.

Comments closed

Understanding Support Vector Machines

Luis Valencia takes us through the algorithm for support vector machines:

A support vector machine (SVM) is a supervised machine learning model that uses classification algorithms for two-group classification problem. Compared to newer algorithms like neural networks, they have two main advantages: higher speed and better performance with a limited number of samples (in the thousands).

Pepperidge Farms remembers when we used genetic algorithms to solve problems because support vector machines were too slow.

Comments closed