Press "Enter" to skip to content

Author: Kevin Feasel

Saving DBCC Results

Published 2016-11-17 by Kevin Feasel

Wayne Sheffield shows how to save CHECKDB results to table:

Okay, let’s try this out. At the above BOL links, there are two DBCC commands that are documented to use the TABLERESULTS option: OPENTRAN and SHOWCONTIG. Testing all of the other DBCC commands shows that this option can also be used on the CHECKALLOC, CHECKDB, CHECKFILEGROUP and CHECKTABLE commands. I’m going to continue this post with using DBCC CHECKDB. If you check BOL, it does not mention the TABLERESULTS option.

As Wayne notes, you can do this for other DBCC commands as well.

Comments closed

Graphing Row Counts With R

Published 2016-11-17 by Kevin Feasel

I look at one use of R for DBAs:

I have a client data warehouse which holds daily rollups of revenue and cost for customers.  We’ve had some issues with the warehouse lately where data was not getting loaded due to system errors and timeouts, and our services team gave me a list of some customers who had gaps in their data due to persistent processing failures.  I figured out the root cause behind this (which will show up as tomorrow’s post), but I wanted to make sure that we filled in all of the gaps.

My obvious solution is to write a T-SQL query, getting some basic information by day for each customer.  I could scan through that result set, but the problem is that people aren’t great at reading tables of numbers; they do much better looking at pictures.  This is where R comes into play.

Click through for the code and a walkthrough of what each line is doing.

Comments closed

Visualizing SQL Server Agent Jobs

Published 2016-11-17 by Kevin Feasel

Daniel Hutmacher shows how to visualize SQL Server Agent job runtimes using spatial data types:

If all you have is a hammer, everything will eventually start looking like a nail. This is generally known as Maslow’s hammer and refers to the fact that you use the tools you know to solve any problem, regardless if that’s what the problem actually needs. With that said, I frequently need a way to visualize the load distribution of scheduled jobs over a day or week, but I could never be bothered to set up a web server, learn a procedural programming language or build custom visualizations in PowerBI.

So here’s how to do that without leaving Management Studio.

Click through for discussion and link to the code.

Comments closed

New SSMS And SSDT

Published 2016-11-17 by Kevin Feasel

In this blast of new things, Andy Leonard makes mention that there are new versions of Management Studio and Data Tools available:

It’s Release Day! 🙂

New versions of SQL Server Data Tools (SSDT) are available here. SSDT 16.5 and 17.0 (RC1) are available. Also available are Data-Tier Application Framework (DacFx) versions 16.5 and 17.0 (RC1).

New versions of SQL Server Management Studio (SSMS) are available here. SSMS 16.5 and 17.0 (RC1) versions are available for SSMS.

It’s going to be a busy couple of days for some people…

Comments closed

New Features In 2016 SP1

Published 2016-11-17 by Kevin Feasel

Niko Neugebauer looks at new functionality released as part of SQL Server 2016 SP1:

– CREATE OR ALTER. I almost cried when I found out that it was implemented. I was asking, begging, threatening, crying for years to get this in the SQL Server. Now, I can finally have future project deployments of those who are not using SSDT running with much less problems.
Now we can modify and deploy objects like Stored Procedures, Triggers, User-Defined Functions, and Views without any fear. Just “Make it so!”

Yeah, they’re not as big as “every Enterprise Edition development feature over the past decade is now available to anybody” but there are some nice additions here.

Comments closed

Ola’s Jobs On Linux

Published 2016-11-17 by Kevin Feasel

Joey D’Antoni gives a few pointers around SQL Server on Linux, including running Ola Hallengren’s scripts on a system without a SQL Server Agent:

Aside from a couple of DMVs that show you Linux specific performance information, everything in SQL Server on Linux is the same. Some of the HA and DR functionality is not complete, and the SQL Agent is not done, however you can use cron (and if you’re familiar with Linux, you should learn about cron—I’ll have another post on that next week).

These are some good notes, so check it out.

Comments closed

Installing SQL Server On Ubuntu

Published 2016-11-17 by Kevin Feasel

James Anderson installs SQL Server on Ubuntu:

’m installing Ubuntu 16 on a Hyper-V VM. I’ll be using a generation 1 Hyper-V VM as I’ve had much better luck installing Linux VMs on these. I’ve had boot and performance issues with Linux on Generation 2 VMs.

After installing Ubuntu I followed the instructions in the SQL Server Linux documentation and recorded my progress below.

For Ubuntu we need to register the SQL Server repository before we can use the apt-get package manager to download and install SQL Server.

This step-by-step walkthrough shows that installing SQL Server on Linux is pretty easy:  it’s just a single package (RPM or DEB depending upon your flavor of Linux).

Comments closed

SQL Server 2016 SP1

Published 2016-11-16 by Kevin Feasel

Parikshit Savjani notes that SQL Server 2016 SP1 is available:

The following table compares the list of features which were only available in Enterprise edition which are now enabled in Standard, Web, Express, and LocalDB editions with SQL Server 2016 SP1. This consistent programmatically surface area allows developers and ISVs to develop and build applications leveraging the following features which can be deployed against any edition of SQL Server installed in the customer environment. The scale and high availability limits do not change, and remain as–is for lower editions as documented in this MSDN article.

This is huge.  With SQL Server 2016 SP1, you can get data compression, In-Memory OLTP, partitioning, database snapshots, Polybase, Always Encrypted, and a lot more in Standard edition.  If you’re on Standard Edition today, this is a must-upgrade—some of these have been Enterprise-only features for nearly a decade and they were a huge part of the appeal for paying for Enterprise.  My question is, what are they going to announce to make people want to keep buying Enterprise Edition?

Comments closed

Preemptive Scheduling

Published 2016-11-16 by Kevin Feasel

Ewald Cress looks at preemptive scheduling:

Cooperative scheduling is a relay race: you simply don’t stop without passing over the baton. If you write code which reaches a point where it may have to wait to acquire a resource, this waiting behaviour must be implemented by registering your desire with the resource, and then passing over control to a sibling worker. Once the resource becomes available, it or its proxy lets the scheduler know that you aren’t waiting anymore, and in due course a sibling worker (as the outgoing bearer of the scheduler’s soul) will hand the baton back to you.

This is complicated stuff, and not something that just happens by accident. The textbook scenario for such cooperative waiting is the traditional storage engine’s asynchronous disk I/O behaviour, mediated by page latches. Notionally, if a page isn’t in buffer cache, you want to call some form of Read() method on a database file, a method which only returns once the page has been read from disk. The issue is that other useful work could be getting done during this wait.

Read on for a detailed example looking at xp_cmdshell.

Comments closed