Three Easy Ways to Improve Performance in Any SQL Server Database

Home > Blogs > SQL Server > Three Easy Ways to Improve Performance in Any SQL Server Database

Three Easy Ways to Improve Performance in Any SQL Server Database

Like This Blog 0 Don Jones
Added by May 3, 2013

The boss is yelling. Users are grumpy. SQL Server’s performance is tanking, and it’s making all of your organization’s line-of-business applications slow. And it’s your problem, just because you’re the “Microsoft person” in your environment.

What’s a “reluctant DBA” to do?

There are three straightforward things you can do to improve performance on almost any SQL Server. Some of these involve some compromises, so you’ll need to think about what’s more important. Others may involve some expenditure, but you can always remind the boss how much the company is saving by not hiring a “real” DBA to take care of these things. None of these things involve software changes, meaning you can do them without worrying about breaking your applications. For that reason, some of these approaches might be less-than-optimal, meaning they’re just hiding the real cause of the performance problem. But if you’re in a hurry, or have no ability to modify the software, then they’re all worth considering.

  1. Upgrade. It’s really rare to run into a SQL Server performance problem that can’t be at least mitigated by bigger hardware. Spend some time looking at Windows’ and SQL Server’s performance counters as a starting point. You’ll need to determine where your most immediate problem is: Memory, disk throughput, or processor. Be aware that one can sometimes disguise another: A busy disk subsystem may be covering for insufficient memory by using the Windows page file, for example. Checking a few additional counters to examine swap file activity is a good way to eliminate or confirm that possibility.
  2. Run the Database Engine Tuning Advisor, Index Tuning Wizard, or whatever it’s called in your version of SQL Server. Ideally, feed it some real-world query traffic that you’ve first captured using SQL Server Profiler. These tuning tools take a look at your query traffic, examine the existing indexes that are helping to execute those queries, and suggest index modifications that can improve performance. Modifying indexes is generally safe to do, even if you can’t modify any other parts of an application or database, but of course you should always take a full backup of SQL Server just in case you need to “roll back” your changes. You can also carefully document the existing indexes before making any changes, enabling you to re-create those same indexes without affecting the underlying data. That’s an important lesson: Indexes don’t impact the actual data in your database. Like a phone book, they just point to that data. Changing indexes will always have some impact on performance, but those changes won’t ever impact the underlying data in the database.
  3. Perform some maintenance. Sometimes, performance can be significantly improved just by doing some basic maintenance on the database. Back it up first, of course, but then consider defragmenting the database, rebuilding the database’s indexes, and even temporarily shutting down SQL Server and letting Windows defragment the disk drives. All of these tasks get your data “lined up” so that SQL Server can read and write more effectively.
  4. Here’s a bonus tip: Give SQL Server some breathing room. Specifically, look for other, unnecessary services on the server and shut them down (after checking to make sure they’re not used, of course). If SQL Server is sharing a machine with other critical services, migrate it to its own server.

I’m also a big fan of third-party SQL Server performance management tools. These often incorporate some of the functionality of the native tuning wizards, but usually go further by offering long-term performance trend analysis, the ability to automatically implement fixes, and other conveniences. Use your favorite search engine to look for “sql server performance tool” and you should turn up several useful possibilities.

Poor SQL Server performance is certainly a pain in the neck, but it’s not an unsolvable problem. Use these tips to start making a dent in your performance problems, and you’ll have SQL Server humming along in no time.

Don Jones
PowerShell and SQL Instructor – Interface Technical Training
Phoenix, AZ

Videos You May Like

A Simple Introduction to Cisco CML2

0 3859 0

Mark Jacob, Cisco Instructor, presents an introduction to Cisco Modeling Labs 2.0 or CML2.0, an upgrade to Cisco’s VIRL Personal Edition. Mark demonstrates Terminal Emulator access to console, as well as console access from within the CML2.0 product. Hello, I’m Mark Jacob, a Cisco Instructor and Network Instructor at Interface Technical Training. I’ve been using … Continue reading A Simple Introduction to Cisco CML2

Cable Testers and How to Use them in Network Environments

0 713 1

This content is from our CompTIA Network + Video Certification Training Course. Start training today! In this video, CompTIA Network + instructor Rick Trader demonstrates how to use cable testers in network environments. Let’s look at some tools that we can use to test our different cables in our environment. Cable Testers Properly Wired Connectivity … Continue reading Cable Testers and How to Use them in Network Environments

Government Edition – Encrypting a USB Flash Drive in Windows 10

0 272 2

In this video, Security Instructor Mike Danseglio demonstrates how to use BitLocker in Window 10 to secure files on a USB Flash drive that adhere to stricter data protection requirements as found inside Government entities. BitLocker 2-day instructor-led training is now available at Interface: BITLOCK: Planning and Deploying BitLocker Drive Encryption Training Video Transcription: Hi. … Continue reading Government Edition – Encrypting a USB Flash Drive in Windows 10

Write a Comment

Share your thoughts...

Please fill out the comment form below to post a reply.