Press "Enter" to skip to content

Day: July 18, 2017

Collecting Processes During High-CPU Events

Laerte Junior has a job which captures processes when server CPU is high:

Whatever the query you prefer to use, the big question will be how to do it in real time when the problem is actually happening, and log whatever information you need, even on the unattended server. There are plenty of times you need to do this, especially if you don’t have a full-time DBA or if you are running in the cloud and needs some support from the cloud provider. You can help the support Engineer by sending him the queries that are breaking your system. In AWS, this kind of service is out of scope of support, but if you have luck to find an Engineer that knows SQL Server and is willing to help you, as I was, it will, help him or her to help you to tune the queries. You just need to leave the solution and then get the CSV log with the queries.

If you can’t get a monitoring solution in place and have to roll your own, this is a very good piece of the puzzle.

Comments closed

Apache Drill Interface For R

Bob Rudis announces a new package on CRAN:

I’m extremely pleased to announce that the sergeant package is now on CRAN or will be hitting your local CRAN mirror soon.

sergeant provides JDBC, DBI and dplyr/dbplyr interfaces to Apache Drill. I’ve also wrapped a few goodies into the dplyr custom functions that work with Drill and if you have Drill UDFs that don’t work “out of the box” with sergeant‘s dplyr interface, file an issue and I’ll make a special one for it in the package.

Seems quite useful if you’re working with MapR.  H/T R-bloggers

Comments closed

Thinking About Databases At Scale

Chris Adkin has a great post explaining some of the hardware and query principles behind scale issues:

All execution plans iterators that require memory grants have two fundamental code paths, one path for when the memory grant is blown and memory spills out into tempdb and one for when the memory grant is correct or under-estimated. Perhaps the database engine team may at some point include a third option, which is for when the grant can be accommodated inside the CPU cache.

As an example, if you run a log record generation intensive workload on the same CPU socket as the log writer, usually socket 0, this will run in a shorter time compared to running the exact same workload in a different socket

This is the type of post where I catch just enough of it to know that I need to dig deeper and learn more.

Comments closed

Tracking Database Restoration-Related Errors

Adrian Buckman has a script which tracks error messages related to database restorations:

In one of my previous posts I went over a scenario where an Auto restore job was logging Restore errors to a table and the error that was being inserted was ‘3013 – RESTORE LOG is terminating abnormally’ and this was due to SQL Server only providing the Last most error produced which is stored within ERROR_NUMBER() and ERROR_MESSAGE() at point of error.

I found this error less than useful so I set out to try and log something more meaningful , which I ended up doing for the specific error (4305) which was being encountered at the time, but I wanted to make this better and less specific to the 4305 error.

This is a very interesting post and a good example of using built-in error handling functionality to help automate your processes.

Comments closed

BimlExpress 2017

Cathrine Wilhelmsen alerts us to a new version of BimlExpress:

.net intellisense

Wait, what? .NET intellisense? Does that mean C# and VB intellisense? You bet it does!

Now, let’s start with a small disclaimer. The release notes say: “Added preview mode of .NET intellisense for early adopters”. That means that this feature is not available out of the box for everyone, it has to be enabled per product key by Varigence. So how do you get it? It’s very simple: E-mail Varigence. Help them out by providing feedback and suggestions for improvements. If you want to go crazy, you may even mention BimlExpress in social media, blog posts or if you’re presenting somewhere. But that’s not a requirement. Just e-mail Varigence and ask. They’re nice guys 🙂

Cathrine also has a webinar coming up tomorrow on the topic.

Comments closed

Using Sqoop To Move Data To Hadoop

The folks at Redglue have a few hints on using Sqoop to move data from a relational database to Hadoop:

  • “Data gets updated” problem

Data gets updated many times and loading data with Sqoop is not a single event as data that you are importing can be updated (INSERTed, DELETed or UPDATed). What is important here, is that, HDFS is an “append-only filesystem” (exceptions made to HBase and Hive with ACID, but they are mostly tricks) and the options are pretty simple: replace the dataset, add data to dataset (partition for example) or merge datasets between old and new data.

If the data that you are loading is a small dataset, don’t think twice, replace and overwrite it.

If the data that you are loading is a big data set, a “incremental” load is recommended. This can be a little tricky as Sqoop needs to know what modification were done since the last incremental or full import.

I’m not a huge fan of Sqoop and prefer to use my own ingest mechanisms, but it’s an easy way to get started.

Comments closed

Basics Of Database Properties

Grant Fritchey explains some of the more important database properties:

Recovery Model

The Recovery Model option is something that you will manipulate constantly as you create databases. A full overview of what the different recovery models are and why you would choose each one will be covered in detail in a blog post later when we talk about database backups. Just to introduce the concept, if you set Recovery to Full, you will need to set up backups for your log. If you set Recovery to Simple then the logs will clean up on their own. There’s a lot more to the topic than just that, but that’s the simple part.

Read on for more.

Comments closed

SQL Server 2017 RC1 Now Available

The SQL Server team announces RC1 of SQL Server 2017:

In SQL Server 2017 RC1, there were several feature enhancements of note:

  • SQL Server on Linux Active Directory integration – With RC1, SQL Server on Linux supports Active Directory Authentication, which enables domain-joined clients on either Windows or Linux to authenticate to SQL Server using their domain credentials and the Kerberos protocol. Check out the getting started instructions.

  • Transport Layer Security (TLS) to encrypt data – SQL Server on Linux can use TLS to encrypt data that is transmitted across a network between a client application and an instance of SQL Server. SQL Server on Linux supports the following TLS protocols: TLS 1.2, 1.1, and 1.0. Check out the getting started instructions.

  • Machine Learning Services enhancements – In RC1, we add more model management capabilities for R Services on Windows Server, including External Library Management. The new release also supports Native Scoring.

  • SQL Server Analysis Services (SSAS)  In addition to the enhancements to SSAS from previous CTPs of SQL Server 2017, RC1 adds additional Dynamic Management Views, enabling dependency analysis and reporting. See the Analysis Services blog for more information.

  • SQL Server Integration Services (SSIS) on Linux  The preview of SQL Server Integration Services on Linux now adds support for any Unicode ODBC driver, if it follows ODBC specifications. (ANSI ODBC driver is not supported.)

  • SQL Server Integration Services (SSIS) on Windows Server  RC1 adds support for SSIS scale out in highly available environments. Customers can now enable Always On for SSIS, setting up Windows Server failover clustering for the scale out master.

Linux AD support is big.

Comments closed