No one knows everything, especially about SQL Server. When you encounter a problem, especially for the first time, you may not know how to solve it. I’ve seen some crazy approaches taken to fix problems – like duct tape on a car. Many times, instead of fixing the root cause of a problem, such as a poorly-performing query, a server-level change is made.

Here are three rolls of duct tape to avoid.

Clearing the Plan Cache Regularly

“We found that the system would be running fine, then a user would run a report with a certain set of parameters, and it would slow down. Running DBCC FREEPROCACHE every day solved that problem.”

When you run DBCC FREEPROCACHE, all the query execution plans in memory are dropped. The next time every query is run, SQL Server will have to compile a plan, and this is expensive. CPU usage will increase. Queries will run slower the first time they are executed. Is it worth dumping the hundreds or thousands of plans that are saved for reuse for the sake of one or two that are bad?

If a query runs acceptably most of the time, but runs very slowly at other times, check into what else is running on the server at the same time, and also investigate statistics and parameter sniffing. Often, you’ll find that a simple change can fix the problem – without taking drastic measures that affect the whole server.

Shrinking Files Regularly

“Tempdb grows from its normal 25-30 GB to 100 GB every Wednesday night. Our drive is only 100 GB in size, so I set up a job to shrink it back down every Thursday morning.”

Or, your log file doubles in size once a week, so you create a job to run DBCC SHRINKFILE weekly, too.

If this happens, there is something running in your database that is causing the data or log file to grow. A large index is rebuilt frequently. A batch job is run to insert a large amount of data all at once, instead of in sections. You need to find the source of the problem and fix it. Constantly shrinking and regrowing files leads to fragmentation, and fragmentation leads to poor performance.

Combining Development and Production Databases on the Same Server

“Our database performance has been really bad the last month. We are testing new features in our development database, which is on the same server as our production database, and I think that might be the problem.”

When you write new code, you need to test it first. You absolutely should have separate development and production databases. You also should keep them on separate servers. When they share a server, they share resources, and poorly performing queries that are being tested in development can bring production to a screeching halt.

You have many options for setting up a development server. Trying to find hardware? Use an older server that has reached the end of its production lifecycle, or, if you have a virtual environment, request a virtual SQL Server. Worried about licensing? For development only, you can purchase a copy of SQL Server Developer Edition for about $50 per user.

Learning from the Past

These are only three of the valuable lessons I’ve learned from working with SQL Server. Finding the root cause of a performance problem and solving it is a much better long-term solution than implementing a short-term workaround.