Press "Enter" to skip to content

Author: Kevin Feasel

Poor Man’s More

Published 2017-03-21 by Kevin Feasel

Chris Koester has a quick-and-easy file reader in a few lines of C#:

This post describes one way that you can read the top N rows from large text files with C#. This is very useful when working with giant files that are too big to open, but you need to view a portion of them to determine the schema, data types, etc.

I’ve used PowerShell many times to do this with large csv files, but in this example we’re going to use C# and look at the Wikipedia XML dump of pages and articles. The 3017-03-01 dump is very large and comes in at 59.5 GB.

I’ve had to write something similar before on Windows machines where I didn’t have access to more/less.  It’s really helpful for perusing the first few lines of gigantic log files.

Comments closed

What Will The DBAs Do?

Published 2017-03-21 by Kevin Feasel

Kevin Hill predicts that database administration isn’t going anywhere anytime soon:

There have been a lot of questions, posts, answers, guesses and such floating around the SQL blogs lately…most of which seem to suggest that the DBA is going away.

Hogwash.

The DBA position is not going away.  Ever.  Or at least not before I retire to Utah to spend my days mountain biking 😉

That said, Kevin does point out that you shouldn’t rest on your laurels.

One fun anecdote I have about database administration:  I recall some marketing for some NoSQL product about how, by adopting their software, you can get rid of those stodgy database administrators.  Within a couple of years, said product’s parent company was offering developer training on “advanced” techniques, which included taking backups, tuning queries, implementing disaster recovery, and creating good indexes to help with performance.  But hey, at least they don’t have DBAs!

Comments closed

Cores Visible Offline

Published 2017-03-21 by Kevin Feasel

John Morehouse enlists some assistance to figure out why his SQL Server instance is ignoring 24 cores:

Hidden schedulers are used to process requests that are internal to the engine itself.  Visible schedulers are used to handle end-user requests.  When you run the casual SELECT * query, it will utilize a visible scheduler to process the query.  With this information, if I have a 64 core server and all is well, I should have 64 visible schedulers online to process requests.

However, I discovered that some of the schedulers were set to “VISIBLE OFFLINE”.  This essentially means that those particular schedulers are unavailable to SQL Server for some reason.   How many offline schedulers do I have? A quick query resulted in 24 schedulers currently offline.  24 logical cores means that 12 physical cores are offline.

But why would a scheduler be set to “VISIBLE OFFLINE”?

Read on for the answer, and check the comments for a helpful plug for sp_Blitz.

Comments closed

RevoScaleR With Power BI

Published 2017-03-21 by Kevin Feasel

Tomaz Kastrun looks at several methods for using RevoScaleR packages in a Power BI dashboard:

I was invited to deliver a session for Belgium User Group on SQL Server and R integration. After the session – which we did online using web based Citrix  – I got an interesting question: “Is it possible to use RevoScaleR performance computational functions within Power BI?“. My first answer was,  a sceptical yes. But I said, that I haven’t used it in this manner yet and that there might be some limitations.

The idea of having the scalable environment and the parallel computational package with all the predictive analytical functions in Power BI is absolutely great. But something tells me, that it will not be that straight forward.

Read on for the rest of the story.

Comments closed

What’s In A Name?

Published 2017-03-21 by Kevin Feasel

Kenneth Fisher explains the different parts of an object name in SQL Server:

One Part Name
vIndividualCustomer
This is just the name of the object. This is probably the most common usage and yet the only one I would recommend never using. (I’ll freely admit I’m not great at this myself btw.) When you only use a single name the schema is implied by the default schema of the user running the statement. That means that the same statement run by two different users could have two different meanings. Since most users have a default schema of dbo this would probably hit the object dbo.vIndividualCustomer. Assuming one even existed.

Click through for more.

Comments closed

Compressed Backup Errors

Published 2017-03-20 by Kevin Feasel

Kenneth Fisher explains why you might get “out of space” errors when taking compressed backups:

Compressing your backups has very few downsides. It’s usually faster (the additional time for compression is less than the time saved by reduced IO) and of course, the backups are smaller. I have run into a few issues, however.

On one occasion there was 75gb free on a drive, the last full backup was only 50gb and the database had not grown significantly in size. Interestingly there was an error when we tried to run a backup. Not enough disk space.

Read on for the explanation as well as the solution.

Comments closed

Cost Threshold For Parallelism Testing

Published 2017-03-20 by Kevin Feasel

Tim Peters shows that the Dynamics people are probably right with their assertion regarding Cost Threshold for Parallelism:

These plans are from a SQL Server 2008 R2 SP3 instance that runs a version of Microsoft Dynamics. The MS Dynamics team knows their product issues a bunch of really small queries and recommend a Max Degree of Parallelism of 1. In my case there is one query plan with a cost of 34,402 query bucks that a MAXDOP = 1 can’t afford. Increasing the MAXDOP from 1 while keeping the Cost Threshold at 5 will keep all of my little queries running in their single threaded paradise while allowing some infrequent biguns to spread their lovely wings across a few more threads. My Cost Threshold For Parallelism is set to 5 and I hope to never have to defend my settings in a dark alley with Erik Darling.

Different systems will behave in different ways, so it makes sense that they might require different settings.  In the case of Dynamics, it appears that almost all of the queries are extremely low-cost, so relatively few plans would go parallel and the ones that do, you probably want going parallel.

Comments closed

TDE And Encrypted Backups

Published 2017-03-20 by Kevin Feasel

Arun Sirpal shows that Transparent Data Encryption will also lead to encrypted backups:

Ok great let’s check this backup file using a cool tool (XVI32). I really want the contact number of a guy called SQLDOUBLEG because I need his help tuning my SQL Servers so I go looking for a text string, hopefully the phone number will be close by.

As Arun points out, you don’t need TDE to get encrypted backups, but it does the job.

Comments closed

Testing Backups With dbatools

Published 2017-03-20 by Kevin Feasel

Sander Stad shows how to test database restorations en masse using dbatools:

Testing your backups is a tedious job and it takes a lot of time which I as a DBA don’t have. I don’t have the time to restore a database, run a DBCC command for every database that’s backed up.

There is a solution and it’s called “Test-DbaLastBackup” which is part of the dbatools module.

Rob Sewell also has a recent post on the topic.

Comments closed