How To Improve Database Performance Without Changing Code

I hear it all the time. It’s a familiar refrain. Most of the time it is innocent enough, and understandable. But there are times when hearing those words are like hearing the screech of fingernails on a chalkboard:

“We can’t change the code.”

There you are, listening to someone complain to you about the database server, and wanting you to fix the problem. You examine the situation and can see an easy code change that would improve performance immediately only to be told “no code changes allowed”. Your heart sinks. You get frustrated that they won’t make any code changes. The user gets frustrated that you don’t have any other options to offer. You ask about changing a stored procedure, or rewriting a view definition, or helping to redesign a few tables from the ‘database’ that is nothing more than an Excel spreadsheet. “No”, “no”, and “no” you are told, along with “stop blaming the code, you always blame the code!”

You are at an impasse. You wish you had a handy list of things to try.

And now you do.

Here are six ways you can improve database performance without changing code, listed in no particular order. They have different levels of difficulty but they all have something in common: no code changes necessary. And your end user will be happy that you can provide them some options.

1. Utilize a proper indexing strategy

I know I said “no code changes”, but indexes are more of a schema change than a code change. And unless you have code that utilizes index hints, you shouldn’t have to worry too much about needing to change code to avoid using an index.

There are more than a handful of blogs out there that talk about missing or unused indexes. I will point you to the MSDN article on how to find missing indexes, as that is likely to be the most useful for you. I will also give you this caveat: adding indexes is not always the right thing to do! You need to be aware of all the activity hitting the underlying table. If you decide to create an index to improve a specific query run monthly to generate a report, you could be doing a disservice to the hourly DUIs (Deletes, Updates, Inserts) that are happening. Be aware of the overall usage before you just start adding indexes for performance.

As for eliminating unused indexes, I would caution you there to make certain you know that the index is truly not being used. If you have been looking at index usage for the past week and find a few hundred that haven’t been touched, don’t go around dropping them because they could very well be used on a monthly basis. Again, know the system and workloads a bit before making these changes. One thing you are likely to be able to do right away is to remove duplicate indexes.

A proper indexing strategy would involve the periodic investigation into finding indexes that are missing, that are duplicates, and are unused.

2. Add memory

Everything SQL Server does *has* to go through memory. Every query you run *has* to read/write pages from/to memory. So why not make certain you have enough memory on your server in order to store as many pages as possible? No, I can’t think of any reason either.

Read more: SqlServerPedia
QR:

Posted via email from Jasper-net