Press "Enter" to skip to content

Category: T-SQL Tuesday

dbatools: The Swiss Army Knife For DBAs

Published 2018-04-12 by Kevin Feasel

Jess Pomfret uses this T-SQL Tuesday to cover some of her favorite cmdlets in dbatools:

Test-DbaSqlBuild

When I found this command I couldn’t have been more excited. My day-to-day job requires the care and watering of over 100 SQL Server instances of varying versions.  Using this command you can get the current build of all your instances and then compare that to the most recent available.  There are also parameters for how far you want to be from the latest version. Setting the -latest switch means just that, your server will only be seen as compliant if it’s on the latest release, passing in -1CU means that it can be no more than 1 cumulative update behind.

Read on for a few additional useful cmdlets.  Out of a large number of useful cmdlets.

Comments closed

Online Tools For Data Professionals

Published 2018-04-11 by Kevin Feasel

Cathrine Wilhelmsen lists five interesting online tools:

When I need to quickly create smaller sets of test data or dummy data, I use Mockaroo. It is highly configurable with over 140 built-in field types for locations, personal information, product information, technical information and much more. Every field type can be customized, and you can also use your own regular expression to generate data. The data can then be exported to CSV, JSON, SQL, and Excel formats. The interface is simple to use and understand, and you can save your schemas and data sets for later reuse.

I’m fond of Coblis and was aware of the last two, but the first two were new to me.

Comments closed

Tools For Various SQL Server Stacks

Published 2018-04-11 by Kevin Feasel

Warren Estes breaks out some tooling recommendations by stacks—that is, common use cases:

The Admin stack is probably the most important stack here. You still using maintenance tasks via SSMS? stop doing that. Rebuilding indexes every night? Maybe rethink that.

How you keep track of, monitor and do basics DBA tasks?

CMS server 
Ok so this can involve SSMS, but a feature not a lot of people may not use. We use it to keep track of all of our instances and push things..oh baby baaaby!  It also allows me to combine PoSh to do work against instances, gather data (historical, dmv…etc) and do a boat load of admin stuff without pointing and clicking. Heck I don’t even have to open SSMS to use my CMS server at all.

SentryOne SQLSentry
SQLSentry can automatically defrag indexes for you and update stats. You could use this instead of the below choices for this aspect if desired. Although not free, it’s an option we have in our environment and I love me some options.

Ola hallengren/Minionware
Both amazing options for backup, reindexing and checkdb. Although most places i’ve worked use Ola’s scripts by default. HOWEVER…. Minion has some pretty nice options that are FAR more configurable than Ola’s. We have mitigated some large DB issues by rolling our own code on top of Ola’s scripts. We could avoid this by simply using Minionware!

I’m a huge fan of the Minionware suite.  And several other things Warren mentions.

Comments closed

Essential SQL Server Tools

Published 2018-04-11 by Kevin Feasel

Tracy Boggiano has a top 5 list of tools she uses on a day-to-day basis:

This T-SQL Tuesday is brought to us by Jens Vestergaard (b |  t), and we are asked to share our favorite SQL Server tools. Hint Profiler will not be on the list. But where do you start there are so many tools out there. In alphabetical order here are my top 5 tools because I can’t pick which one is better than other.

Click through to see Tracy’s top 5 list.

Comments closed

Meidinger’s Law

Published 2018-03-14 by Kevin Feasel

Eugene Meidinger shares his thoughts on the future:

Since we are prognosticating, I want to take a guess at one of the constraints limiting the future.  I present you with Meidinger’s law:

An industry’s growth is constrained by how much your junior dev can learn in two years.

Let me explain. On my team, one of our developers’ just left for a different company. We also have a college student who will be going full time in May, upon graduation. How long do you think it’s going to take the new guy to get up to speed?

And how long do you think he’s going to stay?

This I think is a useful dictum which explains a pretty good amount of industry movement.

Comments closed

Using Date Types In Warehouses

Published 2018-02-14 by Kevin Feasel

Koen Verbeeck argues that date keys in warehouses should be actual date types:

The worst are by far the string representation, as there is no actual check on the contents. It can literally contain everything. And is ’01/02/2018′ the first of February 2018 (like any sane person would read, because days come before months), or the 2nd of January? So if you have to store dates in your data warehouse, avoid strings at all costs. No excuses.

The integer representation – e.g. 20171208 – is really popular. If I recall Kimball correctly, he said it’s the one exception where you can use smart keys, aka surrogate keys that have a meaning embedded into them. I used them for quite some time, but I believe I have found a better alternative: using the actual date data type.

I bounce back and forth, but I’m sympathetic to Koen’s argument, which you can read by clicking through.

Comments closed

Appropriate Data Types And Unicode

Published 2018-02-14 by Kevin Feasel

Raul Gonzalez on (in)appropriate use of National character strings:

Yes, you have read it… I see dates stored as NVARCHAR(10) and NCHAR(10) on daily basis, please don’t ask me why.

This case is even worse, because DATE takes 3 bytes where NCHAR(10) takes 20 bytes, yes Ladies and Gentlemen more than 6 times more space to store the same data.

But wait! how can you be certain that those ten characters are actually a valid date? You can’t, unless you reinvent the wheel and validate that those dates are obviously valid dates and pay the performance penalty of doing it.

You’d think that picking the right data type for something would be fairly easy and then you find a table with a few dozen NVARCHAR(MAX) columns.

Comments closed