SQL Server Environmental Diagnostics Guide – OS

July 10, 2017January 31, 2018 Jeff Mlakar5 Comments

Diagnostics and the optimization of configurations are important for any SQL Server instance. However, it is not only the database server which need attention. The operating system, Windows Server, needs consideration as well. There are various general configurations to consider optimizing on any Windows Server hosting a SQL Server instance.

NOTE: this deals only with Windows Server. I know that Linux is now recently an option but this article will deal only with Windows Server.

This is part 2 of a 3 part series about SQL Server Environmental Diagnostics. Part 1 can be read here.

SQL Server Environmental Diagnostics Guide

June 18, 2017January 31, 2018 Jeff Mlakar12 Comments

Supporting an application as a DBA means you have encountered complaints about performance. The investigation starts and the root cause must be determined. Often times the end result is not a bug in the code but rather something specific to the environment. Environmental problems are usually the culprit.

Performance problems for a SQL Server based application are likely to be caused by environmental factors and not buggy code.

Whether it is a configuration you can change in SQL Server, Windows Server, VMware, or the network it is likely the first course of action is to perform a quick assessment of the environment. This is where understanding the various configurations and best practices are key. Knowing what to look for can save tons of time.

A mistake I often see is a performance issue is passed off to someone else (more senior) and that engineer assumes a lot of things without checking. People are going to relay the problem as they see it – not as it actually is. This leads to skipping over some elementary checks which can save time and frustration from tracking down imaginary bugs.

Start troubleshooting with a quick environmental check.

Below are common environmental mishaps I see when troubleshooting SQL Server performance complaints. Consider these 1st line of action before getting into execution plans, statistics, indexing, and code refactoring.

Nuances of Null – Using IsNull, Coalesce, Concat, and String Concatenation

April 23, 2017October 17, 2017 Jeff Mlakar1 Comment

Null marks are a fact of life in relational databases. Sometimes it is good to revisit the basics of how they are treated in basic system functions and T-SQL because they can be tricky. Look at the following scenarios of how null marks are treated different than the unsuspecting eye might see.

SQL Saturday – Raleigh 2017 – Amazing Professional Community

March 13, 2017October 2, 2017 Jeff Mlakar2 Comments

This Saturday was SQL Saturday for Raleigh. Attendance was strong, classrooms were filled, and the event was a success. When I attend these events it amazes me how strong the SQL community is. Speakers travel from states away, volunteers setup and plan the event, vendors sponsor the event and meet at their booths with participants, and people come to learn on an Saturday.

That really says something about SELECT individuals who INNER JOIN with each other ON a Saturday WHERE the goal is to learn from each other as a GROUP BY sharing knowledge we are lucky enough HAVING such a great community. I’ll stop now in ORDER to show some pictures of day!

SHA-1: When 2 Message Digests Collide

March 6, 2017March 12, 2017 Jeff Mlakar1 Comment

A SHA-1 vulnerability discussed 10 years ago has now been exploited. On February 23, researchers at Google and the CWI Institute in Amsterdam announced they have demonstrated its 1^st known hash collision.

Introvert at Work – How to Survive and Be Successful as a Software Engineer

January 19, 2017January 31, 2018 Jeff Mlakar9 Comments

Calling all introverts! This post is for you if:

People drain your energy and you gain it back from being alone
You sit at your desk with huge air traffic controller headphones on most of the day
You took a personality test and it said you are an introvert

If you work with software, chances are you are an introvert. There are literally dozens of us! Yet we often wear our introversion as a Scarlet Letter instead of the champion belt that it is. Keep reading to find out how to use your introversion to your advantage at work.

DRM Security Concerns | Who does the IoT Obey?

January 8, 2017March 12, 2017 Jeff Mlakar1 Comment

I saw this video of a keynote speech given at the O`Reilly Security Conference in October 2016. Cory Doctorow gives a compelling talk about the thorny issues surrounding DRM and security research, privacy violations, and private property abuses.

Watch the video (32 min):

Hour of Code 2016 | Expose, Inspire, Teach

December 31, 2016July 20, 2017 Jeff Mlakar1 Comment

I have volunteered in the Hour of Code^TM for the second time this year. This year and last I went to 2 schools and worked with elementary school children on the basics of code.

Using T-SQL to Insert, Update, Delete Millions of Rows

October 15, 2016October 3, 2019 Jeff Mlakar4 Comments

Challenges of Large Scale DML using T-SQL

Using T-SQL to insert, update, or delete large amounts of data from a table will results in some unexpected difficulties if you’ve never taken it to task.

Let’s say you have a table in which you want to delete millions of records. If the goal was to remove all then we could simply use TRUNCATE. However, if we want to remove records which meet some certain criteria then executing something like this will cause more trouble that it is worth.

Why not run the following in a production environment?

DELETE 
FROM myTable
WHERE columnA = 'whatever';

DELETE

FROM myTable

WHERE columnA = 'whatever';

Transaction log growth – expect a large transaction like this to cause the transaction log to expand. Calls to expand the database files are some of the most expensive operations SQL Server makes to the OS. Hopefully the initial sizing was good and the growth settings are reasonable.
Blocking – concurrency is going to increase the likelihood of lock escalation. That will lead to the possibility of excessive blocking.
Rollbacks – if the SPID is killed then the transaction will enter a rollback state. For very large transactions this can take a lot of time. Don’t even think about killing or stopping SQL Services during a rollback unless you want a corrupt database!

What can we do to improve this?

We follow an important maxim of computer science – break large problems down into smaller problems and solve them.

We break up the transaction into smaller batches to get the job done. Each of the above points can be relived in this manner. Let’s take a look at some examples.

SQL Server 2005 – The End of an Era

October 6, 2016October 2, 2017 Jeff Mlakar1 Comment

This year marks the end of life for Microsoft SQL Server 2005. Remember Query Analyzer? Enterprise Manager? In the dark times before BIDS and the Visual Studio shell we called Management Studio emerged, we had to manage and develop the database using a crude form of an MMC snap-in and a text editor whose power rivaled that of WordPad.