Press "Enter" to skip to content

Month: April 2018

Cloud Savings And TCO

Published 2018-04-17 by Kevin Feasel

James Serra argues that moving to the cloud can be a net savings on cost:

I often tell clients that if you have your own on-premise data center, you are in the air conditioning business.  Wouldn’t you rather focus all your efforts on analyzing data?  You could also try to “save money” by doing your own accounting, but wouldn’t it make more sense to off-load that to an accounting company?  Why not also off-load the  costly, up-front investment of hardware, software, and other infrastructure, and the costs of maintaining, updating, and securing an on-premises system?

And when dealing with my favorite topic, data warehousing, a conventional on-premise data warehouse can cost millions of dollars in the following: licensing fees, hardware, and services; the time and expertise required to set up, manage, deploy, and tune the warehouse; and the costs to secure and back up the data.  All items that a cloud solution eliminates or greatly minimizes.

When estimating hardware costs for a data warehouse, consider the costs of servers, additional storage devices, firewalls, networking switches, data center space to house the hardware, a high-speed network (with redundancy) to access the data, and the power and redundant power supplies needed to keep the system up and running.  If your warehouse is mission critical then you need to also add the costs to configure a disaster recovery site, effectively doubling the cost.

I don’t think this story plays quite as well.  For small and mid-sized companies, yes, the cloud is often a net savings.  For companies whose products were designed to be cloud-first and take advantage of burstiness and spot markets, yes, you can drive cost savings that way.  But for most mid-to-large companies, I think the calculus shifts to where sometimes cloud options work better but often they don’t.  Need a few hundred SQL Server instances with microsecond-level latency running SQL Server Enterprise Edition 24/7?  That’s not going to be cheaper.

Comments closed

Something’s Missing: Head Operators In Extended Event-Based Execution Plans

Published 2018-04-17 by Kevin Feasel

Grant Fritchey notices something odd about execution plans grabbed from an Extended Events session:

Notice anything missing? Yeah, the first operator, the SELECT operator (technically, not really an operator, but they don’t have any name or designation in the official documentation, so I’m calling them operators). It’s not there. Why do I care?

Because it’s where all the information about the plan itself is stored. Stuff like, Cached Plan Size, Compile Time, Optimizer Statistics Usage, Reason for Early Termination, is all there, properties and details about the plan itself. Now, the weird thing is, if you look to the XML, as shown here, all that data is available:

Read on for Grant’s best guess as to the root cause of the problem.

Comments closed

When Table Join Order Oughtn’t Matter…But It Sometimes Does

Published 2018-04-17 by Kevin Feasel

Bert Wagner looks at join order in SQL Server:

SQL is a declarative language: you write code that specifies *what* data to get, not *how* to get it.

Basically, the SQL Server query optimizer takes your SQL query and decides on its own how it thinks it should get the data.

It does this by using precalculated statistics on your table sizes and data contents in order to be able to pick a “good enough” plan quickly.

I like this post.  It also lets me push one of my favorite old-time performance tuning books, SQL Tuning by Dan Tow.  95+ percent of the time, you don’t need to think about join order.  But when you do, you want to have a systematic method of figuring the ideal join order out.

Comments closed

SQL Server And STIGs

Published 2018-04-17 by Kevin Feasel

Mohammad Darab has a quick summary of the Department of Defense’s STIG overview for SQL Server 2016:

To make it easier for people in charge of “STIG’ing” their SQL Server 2016 environment, this blog is aimed to go over the newest MS SQL Server 2016 STIG Overview document (Version 1, Release 1) that was released on 09 March 2018. If you want to read through the whole document you can download it here. Otherwise, below is my summation of the relevant sections.

This overview document was developed by both Microsoft and DISA for the Department of Defense.

The entire overview document is 9 pages (including title page, etc.)

Click through for Mohammad’s summary.  Also check out Chris Bell’s sp_woxcompliant.

Comments closed

Auto Soft-NUMA And Scheduler Waits

Published 2018-04-17 by Kevin Feasel

Joe Obbish walks us through a scenario with automatic soft-NUMA leading to poor performance:

Consider a server with soft-NUMA nodes of 8 schedulers with MAXDOP 8. The first parallel query will be sent to numa node 0. The number of active workers matches the number of schedulers exactly so each active worker is assigned to a different scheduler in the NUMA node. The second parallel query will be sent to NUMA node 1. The third parallel query will be sent to NUMA node 2, and so on. Execution of serial queries or creation of sessions does not matter. That advances a counter that’s separate from the “global enumerator” used for parallel query scheduler placement. As far as I can tell the scheduler assigned to execution context 0 does not affect the scheduling of the parallel worker threads, although it can certainly affect parallel query performance.

The scenario described above doesn’t sound so bad. It can work well if the parallel queries take roughly about the same amount of time to complete and query MAXDOPmatches the number of schedulers per soft-NUMA node. Problems can emerge when at least one of those is not true. With the spread selection type it’s possible that the amount of work already assigned to schedulers has no effect on parallel query scheduler placement. Let that sink in. You could have 100 serial queries all assigned to schedulers in numa node 0 but SQL Server may send a parallel query to that NUMA node. It depends on the position of the “global enumerator” as opposed to current work on the server.

Joe offers up some alternatives if you find yourself dealing with this issue.  Definitely a must-read.

Comments closed

Push-Based Alerting With Kafka Streams

Published 2018-04-16 by Kevin Feasel

Robin Moffatt shows how to take syslog data and create a notification app using Python and Kafka Streams:

Now we can query from it and show the aggregate window timestamp alongside the result:

ksql> SELECT ROWTIME, TIMESTAMPTOSTRING(ROWTIME, 'yyyy-MM-dd HH:mm:ss'), \
HOST, INVALID_LOGIN_COUNT \
FROM INVALID_USERS_LOGINS_PER_HOST;
1521644100000 | 2018-03-21 14:55:00 | rpi-03 | 1
1521646620000 | 2018-03-21 15:37:00 | rpi-03 | 2
1521649080000 | 2018-03-21 16:18:00 | rpi-03 | 1
1521649260000 | 2018-03-21 16:21:00 | rpi-03 | 4
1521649320000 | 2018-03-21 16:22:00 | rpi-03 | 2
1521649080000 | 2018-03-21 16:38:00 | rpi-03 | 2

In the above query I’m displaying the aggregate window start time, ROWTIME (which is epoch), and converting it also to a display string, using TIMESTAMPTOSTRING. We can use this to easily query the stream for a given window of interest. For example, for the window beginning at 2018-03-21 16:21:00 we can see there were four invalid user login attempts. We can easily check the source data for this, using the ROWTIME in the above output for the window (16:21 – 16:22) as the bounds for the predicate:

It’s a very interesting use case.

Comments closed

Using The Bot Framework

Published 2018-04-16 by Kevin Feasel

Jakub Kaczmarek demonstrates using the Microsoft Bot Framework:

Before starting a new bot project, you need to consider if it really is a solution for your business case. It’s not recommended to start bot development just because it’s a hot topic. However, in some cases, this kind of software can save a lot of time, money and resources. The following list of bot example use cases might help in making the decision:

  • Answer for typical questions

    • A bot can make use of Q&A knowledge to receive user question and provide an appropriate answer.
    • Questions can be matched to correct answers using a LUIS (language understanding intelligent service) cognitive service.
    • Reduced time can be spent by help desk staff answering typical questions.
    • Example use cases are help chat, contact pages and web stores.

  • Alternative system interface

    • By integrating a bot with external systems (e.g. Outlook, Jira, CRM, SharePoint) a bot can become an alternative interface to work with these systems.
    • A bot can simply ask some questions and gather the answers given by the user to submit data that normally would be filled in on a form.
    • Example use cases are creating support tickets, uploading SharePoint documents, making calendar appointments, and providing translations.

  • Entertainment & education

    • A bot can be also used to entertain and educate its recipients by sending various kinds of content to the user.
    • It’s a good idea to use media types like videos, audio, images and links to knowledge base articles.
    • Example use cases are workout coach, recipes book and product adviser.

  • Notification bot

    • A bot can be scheduled to initialize conversations at appropriate time, notifying the user about some actions or reminding about things he should do.

    • It’s important to remember that sending proactive messages is not always possible – it depends on the channel used for communication.

    • Example use cases are meeting reminders and timesheet reminders.

I try to avoid the term “intelligent bots” because we’re at least 2 or three generations away from that.  But it’s definitely worth getting your hands dirty with them today, at least to learn their limitations.

Comments closed

Migrating Excel Power Pivot Models To SSAS

Published 2018-04-16 by Kevin Feasel

Imke Feldmann has a walkthrough to show how to migrate a Power Pivot model in Excel into SQL Server Analysis Services by way of Power BI:

In Visual Studio there is a wizard to migrate an Excel Power Pivot model to a SSAS model. But this will not bring over the M-queries unfortunately. But there is a workaround to achieve this. It requires SQL Server 2017 or higher.

Click through for a list of operations and a video showing how it’s done.

Comments closed

Checking For Temp Table Existence

Published 2018-04-16 by Kevin Feasel

Wayne Sheffield offers a clinic on temp tables:

I was recently reviewing a newly created T-SQL stored procedure. This procedure was verifying temporary table existence with the following code:

Seeing this takes me back to one of my favorite presentations, where I compare Temporary Tables and Table Variables. In this, I go over several of the methods that I have seen for how code found on the internet actually does this task… and I show why they are all doing it wrong.

Read on to understand why this isn’t the correct answer.

Comments closed

Verifying SSIS Database Connections With ssisUnit

Published 2018-04-16 by Kevin Feasel

Bartosz Ratajczyk shows how to test project-level connections in SQL Server Integration Services with ssisUnit:

Previously we successfully prepared tests for variables and parameters using VariableCommandand and ParameterCommand. Now it’s time to communicate with the database, and for that, I will use connection manager defined on the project level. I know from the ssisUnit tutorials it works perfect with package connection managers, so it’s time to verify it against the projects. I will test the package 10_ProjectCM.dtsx – it is just getting a single value from the table in a database and storing it in a variable. All the packages and unit tests are on my GitHub.

The package contains three SQL Tasks: the first just checks if we can communicate with the database using SELECT 1 statement, the second gets the information from the table, and the third repeats the second on the container level.

Click through for the tests.

Comments closed