Your SQL Server Compact is capable of lightening-fast performance and stunningly efficient service even while handling huge work loads. But without regular SQL sever performance tuning, you can’t expect it to “win the race” any more than a race car could without regular tune ups.
You will have an opportunity to read in-depth information about SQL Server performance if you visit this Stackify page. But for a basic overview of the most important principles for your SQL tune-ups, keep reading right here.
About SQL Server Tuning
It would be nice to think that you could handle SQL performance tuning in a single, end-all strike, but what it’s really going to take is a targeted, involved, and “never-ending” campaign. Slow SQL performance may be caused by only one or two slow but frequently used queries that are eating up all your active memory. It’s a “search and destroy mission.”
Realize that your query processor has to compile, perfect, and generate an execute plan before it can even start an actual performance. Also realize that there may be numerous ways to do any given query, but only one of them is the most efficient. And what works in one scenario won’t work somewhere else. And what worked best last year may be only second-best (at best) this year.
Buckets and Metrics
As you regularly test and retest, record the result and search for of the fastest, least expensive, lowest memory consumption solution, you need to learn to “think in buckets.”
“Buckets” is tech-talk for units of similar data-types. It’s the way you organize things when you are looking for a problem. You ask, “Is the issue in resources, in query structure, in indexing, or somewhere else?” It’s the equivalent to a mechanic asking, “Is the problem with the engine, transmission, electrical system, or the body?”
Next, keep in mind you will be using numerous data metrics as you fine tune your query searches for optimal performance. Your boss will likely be happy with even a slight uptick, just like a track runner shaving seconds off his mile.
The key is to not only understand and have access to great metrics, but to know how to correlate them correctly. You have to be able to deduce where the problem is by comparing various metrics. You look at wait states, CPU, throughput, memory pressure, bandwidth, usage, and more, and “triangulate” to find the issue. Use the Database Performance Analyzer dashboard to make this process much easier!
Track Results Over Time
Again, this is a long-term undertaking, a never-ending war. To win, you need to keep collecting data at regular intervals (we’re talking about days, weeks, months); and then, compare this against last year’s or the last few year’s stats. That gives you perspective on how well you are doing and if you are improving/declining.
Of course, you’ll also want to get information on the industry average and pay attention to any goals your superiors may have set for you (or you may have set for yourself).
Do Regular Maintenance
Maintenance of your database is also key. As you pay attention to maintenance tasks, you both prevent SQL issues and gather the data that can help you solve certain SQL problems should they arise.
Specifically, hour-by-hour transaction log backups plus daily and weekly backups; daily index maintenance (not necessarily rebuilding indexes every day though), statistic updating, and corruption checks should all be a part of your maintenance routine.
