Press "Enter" to skip to content

Curated SQL Posts

Auto-Deploying Documentation

Published 2015-12-28 by Kevin Feasel

Steph Locke has more on documentation auto-deployment (and the original Curated SQL entry):

So I went through and converted everything in my Rtraining to this and realised it messed up my slide decks – it’s been so long since I had built a pure knitr solution that I forgot that rmarkdown::render != knitr::knit. For my slidedecks, if I wanted the ioslides_presentation format, I needed to use rmarkdown::render. The problem with that has been the relative references to the CSS and the logo.

To solve this I read about the custom render formats capability and created afunction that produces an ioslides_presentation but with my CSS preloaded by default. This now means that I can produce slides with better file referencing.

Steph has put up all of her R-related presentations and documentation as well, so check that out.

Comments closed

DocumentDB

Published 2015-12-28 by Kevin Feasel

Robert Sheldon walks us through DocumentDB:

DocumentDB organizes documents into collections, with each database capable of hosting one or more collection. Because DocumentDB is a cloud service, it offers quick and easy implementations, while delivering the flexibility and scalability necessary to meet the demands of todays web and mobile applications.

DocumentDB integrates JSON and JavaScript right into the database engine. JSON, short for JavaScript Object Notation, is a widely implemented lightweight format for exchanging data between different source types, similar to how XML can be used to exchange data. JSON is based on a subset of the JavaScript programming language and is easy for computers to parse and generate, as well as being human readable.

Read the whole thing if you’re interested in Microsoft’s competitor to MongoDB.

Comments closed

Monitoring For Suspect Pages

Published 2015-12-28 by Kevin Feasel

John Martin shows us about dbo.suspect_pages:

dbo.suspect_pages is a table that resides in the MSDB database and is where SQL Server logs information about corrupt database pages (limited to 1,000 rows) that it encounters, not just when DBCC CHECKB is run but during normal querying of the database. So if you have a DML operation that accesses a corrupt page, it will be logged here, this means that you have a chance of identifying a corruption in your database outside of the normal DBCC CHECKDB routine.

This is a nice tool we can use to check for corruption.

Comments closed

Fraud Detection With R And Azure

Published 2015-12-28 by Kevin Feasel

David Smith shows us an online fraud detection template:

Detecting fraudulent transactions is a key applucation of statistical modeling, especially in an age of online transactions. R of course has many functions and packages suited to this purpose, including binary classification techniques such as logistic regression.

If you’d like to implement a fraud-detection application, the Cortana Analytics gallery features an Online Fraud Detection Template. This is a step-by step guide to building a web-service which will score transactions by likelihood of fraud, created in five steps

Read through for the five follow-up articles.  This is a fantastic series and I plan to walk through it step by step myself.

Comments closed

Running Out Of In-Memory Disk Space

Published 2015-12-25 by Kevin Feasel

Jack Li shows us what happens when we run out of disk space for checkpoint files on a memory-optimized table:

The question is what happens if the disk that host the In-Memory checkpoint files runs out of disk space?  So I decided to do some testing and document the symptoms and recovery steps here in case you run into such issue.  With our Azure, test was really easy.  All I had to do was to spawn a VM and attach a very small disk to simulate out of disk space condition.

If your disk runs out of space, you will see various errors below though your database stays online

It looks like you can work your way out of a full drive scenario.  Hopefully, however, you won’t get into this scenario too often.

Comments closed

Indexing JSON

Published 2015-12-25 by Kevin Feasel

Jovan Popovic answers a question which has been on my mind:  how are we supposed to index JSON data in SQL Server 2016?

In this post I will show how you can add indexes on JSON properties in product catalog. In SQL Server 2016, you can use two type of indexes on JSON text:

  1. Index on computed column that index some specific properties in JSON.
  2. Full text search index that can index all key:value pairs in JSON objects.

This is the downside to JSON not being an official type:  indexing is somewhat limited.  In comparison, you could create XML indexes which were specially-designed to do the job of searching for text within an XML field.

Comments closed

Transaction Log Is Full

Published 2015-12-25 by Kevin Feasel

Andy Galbraith diagnoses an “ACTIVE_BACKUP_OR_RESTORE” issue:

The way FULL backups work in SQL Server, the transaction log is not released for re-use during a FULL backup, even if regular LOG backups are occurring or the database is in SIMPLE recovery.  This is due to the fact that the portion of the LOG that is used during the FULL has to be persisted during the FULL in order to be backed up at the end of the FULL – that is, the FULL backup includes the data at the start of the FULL (23:30) *plus* the LOG used until the end of the FULL (in the case of the 12/04-12/05 backup, the LOG used from 23:30 to 07:11).  This is the meaning of the ACTIVE_BACKUP_OR_RESTORE message – the LOG is waiting for the end of the active FULL backup before it can be released for re-use, which in this case was causing the LOG/LDF file to grow to fill its mount point.

This is interesting analysis and a reminder that even though you’re in Simple recovery mode, SQL Server still uses a transaction log and it’s just as important.

Comments closed

Parameterizing Procedures

Published 2015-12-25 by Kevin Feasel

Monica Rathbun shows how to combine several report queries:

I try to parameterize as many stored procedures as possible. This not only minimizes the amount of procedures I need to maintain, it in my opinion is a much cleaner way to code. It disturbs me when I see multiple stored procedures that pull the exact same data, but may have slight differences between them. Whether it be a sort, a where clause, or even just an extra field or two that makes it different, some developers think you need a different procedure for each one . Why not consolidate and parameterize?

The next step might be using dynamic SQL to build a query if there’s as much overlap as we see in Monica’s example.

Comments closed

Network Load Testing

Published 2015-12-25 by Kevin Feasel

Tim Radney uses iperf to perform network load testing:

Poor network performance can be a silent killer for application performance and my personal experience has shown this to be the case on many occasions. Often an application would start having performance issues and the application engineer would say that the application server looks good and starts to point their finger at the database. I would get a call to look at the database server and all indications showed that the database server was in good health (and this is where monitoring for key performance indicators and having a baseline helps!). Since the application and database teams were saying everything was good, we would ask the network team to check things out. The network team would look at a few things and give the all clear on their side as well. Each team troubleshooting and reviewing their respective systems took time, meanwhile the application performance was still suffering. The issue would then get escalated until all the teams would be asked to join a conference bridge to troubleshoot together. Eventually someone would start a deeper network test and determine that we either had a port saturation, routing, or some other complex networking issue. A few clicks or changing something on their end would eventually resolve the application slowness.

iperf is a nice tool for checking to see if your network throughput looks reasonable.

Comments closed