Optimizing SQL Server Database Performance and Overcoming Unpredictable Challenges

This is a guest post from Tony Branson at ScaleArc.

With the explosion of digital data, achieving optimum database performance has become the primary concern of every database professional. For improving efficiency when managing a complex IT environment, DBAs must stay one step ahead consistently and learn about the best practices, proven strategies, and innovative approaches being applied to different DBA processes. Here are 5 key areas to consider for driving database efficiency even with an exponential increase in data:

1. Knowing What Needs Your Focus

It is important to have a good fundamental understanding of your IT infrastructure as a DBA. It’s critical to understand what’s working well and what’s not performing within the database infrastructure itself – e.g., if you’re having memory issues vs. I/O issues. It’s also critical that you understand how the database is reached – what network issues, application issues, VM issues could be impacting database availability or performance. Ensure your perspective is broad enough to understand the parts of the technology stack that need your attention.

2. Performing Periodic Health Checks

Database corruption hits without warning and has a devastating impact on your data if you are unprepared. Backups are essential but if you are backing up corrupt data, all your efforts are going down the drain. To prevent such a scenario it is important to perform health checks periodically using a standardized process. As a rule of thumb, DBAs should check and validate the consistency and integrity of a database frequently to make sure there is an accurate, valid backup always available in case the need arises.

3. Fine-tuning SQL Server Performance

The biggest challenge facing any DBA is how to improvise, optimize and maintain SQL Server database performance. When tuning a busy system, considering the full range of KPIs can get downright overwhelming. Use online guides to identify the metrics that actually matter and make improvements accordingly. For example, if you see a sudden fall in page life expectancy, it reflects an increase in your I/O requirements, which means you should be checking the processes running at that time.

4. Staying Compliant

Compliance can take a toll on compute resources, giving rise to on-going stress. While it may seem tempting to monitor every single transaction, it can kill your performance because it would need a large amount of storage space.

It is important to have an audit strategy in place with well-defined data and events before you can start. This approach will help you make any necessary adjustments over the time and track all the results for quarterly and annual audits.

5. Leveraging a Modern Database

The emergence of new generation applications that require both scale and speed to function at peak efficiency has exposed the flaws and gaps in existing database technologies. Scale up has reached full capacity, but scale out has historically been really tough. Modern databases support key features that can boost app performance and improve uptime, but taking advantage of these capabilities has required substantial application recoding.

Database load balancing software makes SQL Server management easy, avoiding the need for code changes to support features at the application tier. It enables geo-aware load balancing, supports app-transparent failover, transparently delivers read/write split, enables query routing, and performs multiplexing and connection pooling, enabling DBAs to tackle the challenges of an ever-growing pool of database servers. Deploying database load balancing lets you harness all the capabilities of SQL Server databases. By deploying database load balancing software, DBAs can efficiently address all the issues and problems that impact their ability to manage and optimize SQL Server databases effectively.

young man portrait, isolated on whiteAbout the author: A self-proclaimed tech geek, with a passion for ScaleArc’s disruptive technology innovation in database load balancing. Tony has a passion for dissecting tech topics such as transparent failover, centralized control, ACID compliance, database scalability and downtime effects. On his days off, he can be found watching sci-fi movies, rock climbing or volunteering.

Disclaimer: This post is not an advertisement. The owner of this blog has received no compensation for the placement of this guest post.

What happened to Save-AzureRmProfile?

I’ve been working a lot in the Azure PowerShell area of late. One thing I wanted to be able to do is have my scripts login automatically to Azure. In many examples the cmdlet Save-AzureRmProfile was used to save your Azure credentials, then later you could use Import-AzureRmProfile to import them.

But, when I attempted to run Save-AzureRmProfile I got the error ‘Save-AzureRmProfile is not recognized as the name of a cmdlet, function, script file, or operable program’.  Huh? I checked the docs, and it does include a listing for Save-AzureRmProfile.


This is a case of the PowerShell AzureRM module getting ahead of the docs. After beating my head against the wall, I found the cmdlets had been replaced with the new noun of AzureRmContext.

To use them, first login to Azure manually. Then, use the new Save-AzureRmContext to save your information to a file.

# Setup – First login manually per previous section

# Now save your context locally (Force will overwrite if there)
$path = "C:\Azure\PS\ProfileContext.ctx’
Save-AzureRmContext -Path $path -Force

Once that’s done, from then on you can use the Import-AzureRmContext to automate the login.

# Once the above two steps are done, you can simply import
$path = C:\Azure\PS\ProfileContext.ctx’
Import-AzureRmContext -Path $path

Be warned, this does present a security issue. If someone were to steal your context file, they could then login as you. You need to be sure your context file is stored in a safe location no one can get to.

Converting (Remapping) Caps Lock to CTRL

ThinkpadKeyboardI’ve always been a fan of the Lenovo Thinkpad keyboards. The have a good feel, and I especially love the Trackpoint. The Trackpoint is that little red nub between G and H, and acts as a mouse. With it I can do many mouse activities without removing my hands from the keyboard. With most it is a love / hate thing, either they love it or the are mentally deficient and hate it.

I actually have two, one is the USB version which I have plugged into my KVM (Keyboard Video Monitor) switch. The other is a Bluetooth model I use with my Surface Pro 3 when I’m away from home. They are quiet, easy to hold in your lap, and take up very little desk space.

The only complaint I have is with the CTRL key, something we all hit many times a day. Its position makes it difficult to reach with my huge hands. Fortunately there is an easy fix: remapping the seldom used Caps Lock key to turn it into another CTRL key.

While there are many ways and apps to do this, by far the one I trust the most comes from Microsoft in the form of its SysInternals utility Ctrl2Cap v2.0. It’s dirt simple, just follow the instructions and after the next reboot your Caps Lock will function as a CTRL key.

While my Thinkpad keyboards were the reason I needed to do this, Ctrl2Cap will work with any keyboard. If you could use an extra CTRL key, give the utility a try.

Note, it doesn’t swap CTRL and Caps Lock, it just converts Caps Lock to CTRL. All of your existing CTRL keys will continue to work normally. Thus, if you do this, you’ll lose the ability to do Caps Lock. (Don’t say I didn’t warn you.) Not a big deal for me, I rarely “unleash the fury” as they say, needing to type in all caps. It’s not permanent either, if you want to reverse it, Ctrl2Cap has an easy to use uninstall option.

Hope this tip helps, if nothing else this post will help me in the future when I need to setup a new PC.

IT ops and news talk–Episode 3 Secure that Jump Server

I owe everyone an apology, I missed blogging about this at the time it occurred. Last December I was a guest on Don Box’s podcast, “IT ops and news talk”. I appeared on Episode 3, Secure that Jump Server.

In the podcast we discuss the testing of PowerShell code with Pester. After that we got into an interesting discussion on the current state of DevOps. Give it a listen I think you’ll enjoy, it’s about half an hour in length. Don is a great interviewer it was a lot of fun.

As a follow on to the discussion, you might want to learn more about Pester. I have a complete course on the subject in my Pluralsight course Testing PowerShell with Pester. If you don’t know anything about PowerShell, but want to learn, then I’d suggest my Beginning PowerShell Scripting for Developers. Note that even though it says “…for Developers” we don’t mean programmers (although it could), instead it refers to people who wish to develop scripts in PowerShell.

What? What was that? You in the back row waving your hand? You say you don’t have a Pluralsight subscription? Hey, no problem. Just email me, free <at> arcanetc.com and I can send you a code good for 30 days of free access to Pluralsight, with which you can watch not just my courses, but any course from Pluralsight’s library of over 5,000 courses.

Troy Hunt: A data breach investigation blow-by-blow

This is a fascinating read from security researcher Troy Hunt. In it he describes the process by which he investigates a data breach. Well worth the read.


SQL Server Reporting Services Playbook now live on Pluralsight!

My newest course, the SQL Server Reporting Services Playbook, is now live on Pluralsight! If you are looking to get up to speed fast writing reports in SQL Server Reporting Services, this is the course for you. In roughly two hours you will have enough information to start cranking out reports for your organization.

Playbooks though are a bit different than traditional Pluralsight courses. Playbooks are specifically designed so you can watch just one segment to gather that piece of information you need right now, and not have to sit through the entire course. For example, if you were comfortable with generating line item reports, but need a refresher on how to use charts in a report, you could watch just the 15 minute module on charts.

The playbook format is also great for times when, after watching a course, you need to come back and refresh yourself on one piece. The organization makes it easy to find the specific piece of information you need.

You’ll find the course at: https://www.pluralsight.com/courses/sql-server-reporting-playbook

After watching this course, you may wish to go watch my previous course, What’s New in SQL Server 2016 Reporting Services. ( https://www.pluralsight.com/courses/sql-server-2016-reporting-services ). This course is another brief one which assumes you are comfortable with Reporting Services and want to get up to speed quickly on the new features in 2016.

I for one really appreciate this style of course, as it doesn’t waste time teaching me what I already know about SSRS, and instead just focuses on the things that have changed since the previous version. And let me assure you, a lot has changed in SSRS 2016!

What’s that you say? You don’t have a Pluralsight subscription but really want to view this course? Well no problem my friend, just email me <free at arcanetc.com> and I’ll be more than happy to send you a code that will give you 30 days free at Pluralsight to watch mine or anyone’s courses.

By the way, this is my 13th Pluralsight course, you’ll find the whole list at https://www.pluralsight.com/authors/robert-cain . My courses cover a variety of topics, Reporting Services of course, but also PowerShell, SQL Server Integration Services, and a nifty course introducing you to the concepts of Data Warehousing and Business Intelligence.

What’s New in SQL Server 2016 Reporting Services–Now live at Pluralsight

So you want to see what’s new in SQL Server 2016 Reporting Services? Well I’ve got just the thing for you. My 12th course just went live on Pluralsight, named appropriately “What’s New in SQL Server 2016 Reporting Services”.

This course is designed specifically for those who are already familiar with SQL Server Reporting Services, and just need to come up to speed with the new features in the 2016 release. Even better you can do it during a long lunch (or two short ones), the course is just under an hour and a half so you can learn quickly.

The course starts with a look at the brand new Report Portal, the replacement for Report Manager. You’ll see how to create KPIs right in the portal, improvements to subscriptions, and how to custom brand the Report Portal so it can integrate right into your companies websites. After that you’ll learn about the new HTML 5 compatibility, something that makes SSRS integrate even better into your web apps and sites.

Some time is then spent on improvements to what Microsoft calls “Paginated Reports”, essentially the same reports you’ve grown to know and love. You’ll see improvements for embedded reports, as well as the two new chart types, sunburst and treemap. Finally, the long awaited ability to arrange parameters is covered.

Last, but certainly not least, the course covers what I consider to be the most exciting piece of the 2016 release, the Mobile Report Publisher. You’ll see how to use it to generate reports, and how they can be used on the web as well as mobile devices such as phones and tablets.

What’s that you say? You want to see it, but don’t have a Pluralsight subscription? Hey, no problem. Just shoot me an email, free at arcanetc.com. I can send you a code good for 30 days during which you can watch this and any of the over 5,000 (yes, FIVE THOUSAND) courses.