Press "Enter" to skip to content

Author: Kevin Feasel

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

Sentiment Analysis

Published 2015-12-24 by Kevin Feasel

Dustin Ryan and Patrick Leblanc used Azure ML and Power BI to do sentiment analysis:

Using Azure ML and a free subscription to the Text Analytics API, I’m going to show you how to perform sentiment analysis and key phrase extraction on tweets with the hashtag #Colts (after this past Sunday’s 51-16 beat down of the Colts at the hands of the Jacksonville Jaguars, I’m bathing in the tears of Colts fans. Watch the highlights! ). Although my example here is somewhat humorous, the steps can be used to perform sentiment analysis and key phrase extraction on any text data as long as you can get the data into Power Query.

This is a fantastic example of how Azure ML can be used.  Read the whole thing.

Comments closed

Powershell To Modify Team Foundation Version Control

Published 2015-12-24 by Kevin Feasel

Kevin Eckart makes use of TFS commands to maintain source:

In our environment, changes made in the Test branch have to travel through the Main branch and into the Release branch to be deployed into production. Sometimes changes need to move through quickly without regard to other changes, especially in an environment where there may be a single coder. Note: the following code will merge all checked in code regardless of who checked it in. Be careful in multi-coder environments.

This is your daily public service announcement saying that if you don’t have your database code in source control, you really should get your database code into source control.

Comments closed

Incrementing All Sequences

Published 2015-12-24 by Kevin Feasel

Mark Broadbent had to increment all of his sequences by 10,000.  Here’s how he did it:

The only problem with this approach is that our database was configured (rightly or wrongly) with approximately 250 sequences! Since we could not be sure which sequences would ultimately cause us problems we decided to increment each one by 10,000.

Not being someone who likes performing monotonous tasks and also recognising the fact that this task would probably need to be performed again in the future I decided to attempt to programmatically solve this problem.

The script isn’t too difficult to understand but let me reiterate his warning:  read the script before you run it, and know exactly what it’s doing before you run it.

Comments closed

CISL

Published 2015-12-24 by Kevin Feasel

Niko Neugebauer talks about the Columnstore Indexed Scripts Library:

Around 3.5 Months ago in September of 2015, I have announced the first public release of the CISL – Columnstore Indexes Scripts Library, which allows to have a deeper insight into the database that uses or can use Columnstore Indexes.
Since that, I have released 4 more “point releases” with bug fixes and new features, I have greatly expanded the support of SQL Server with inclusion of SQL Server 2012, SQL Server 2016 and Azure SQLDatabase.

If you use columnstore indexes, you absolutely want to get this.  Also, there’s a brand new update out.

Comments closed

Finding Login Permissions

Published 2015-12-23 by Kevin Feasel

Andy Galbraith has a new permissions script:

I recently was tasked with this ticket:

Please add new login Domain\Bob to server MyServer.  Grant the login the same permissions as Domain\Mary.

On the face of it, this seems relatively straightforward, right?  It is the kind of request that we all get from time to time, whether as an ad-hoc task or as part of a larger project, such as a migration.

The catch of course is that it isn’t that easy – how do you know what permissions Mary has?

Andy’s script looks good.  For bonus points, compare it to fn_my_permissions.

Comments closed

Tuning SQL Server Backups

Published 2015-12-23 by Kevin Feasel

Derik Hammer has a post on tuning SQL Server backups:

Finally, do not forget about your memory. To backup or restore a database you have to load data pages into memory. We will talk more about memory below and how the internal buffer pool comes into play and can cause operating system paging or out of memory conditions.

Derik shows the various knobs and switches available, and I want to emphasize one thing:  optimizing backup statements involves testing different scenarios.  You can make good guesses as to the appropriate MAX_TRANSFER_SIZE or BUFFERCOUNT, but even then, test different combinations and find what works best for each database.

Comments closed

FOR JSON WITHOUT_ARRAY_WRAPPER

Published 2015-12-23 by Kevin Feasel

Jovan Popvic introduces us to the WITHOUT_ARRAY_WRAPPER clause:

This option enables you to remove square brackets [ and ] that surround JSON text generated by FOR JSON clause. I will use the following example:

SELECT 2015 as year, 12 as month, 15 as day
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

This query will return:

{ "year":2015, "month":12, "day":15 }

However, without this option, following text would be returned:

[{ "year":2015, "month":12, "day":15 }]

Jovan also points out important changes between 3.1 and 3.2 with FOR JSON output.

Comments closed

Restoring CDC-Enabled Databases

Published 2015-12-23 by Kevin Feasel

Mark Broadbent shows us how to restore databases with Change Data Capture enabled:

This automatically poses the question of how it is possible to restore a backup chain with CDC? On a database restore, in order to apply differential backups and transaction logs the NORECOVERY clause is required to prevent SQL Server from performing database recovery.

If this option is required but KEEP_CDC in conjunction with it is incompatible, surely this means point in time restores are not possible for restores that require CDC tables to be retained?

-Wrong!

The answer is a bit surprising, and my guess is that most database administrators are totally unaware of this restoration quirk.

Comments closed