Discussion Locked

What are your Favorite SQL Server Performance tips?

0 helpful
13 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

As a new SQL Server DBA, I am curious what your "secrets" for performance and monitoring are. One thing I have learned is that this is more of an acquired art than a science. So, I would be interested in knowing how you approach this or tools and tricks you have found to help you enhance and keep your SQL Servers running at peak conditions.

Gary

13 replies

by sentdata

For performance it?s split between the database and server. Never miss an opportunity to tell your infrastructure brothers you need more CPU! ;-) But seriously, the fastest impact you can make is looking at how your primary keys and indexes are. After that monitor your mdf and ldf sizes and shrink when needed. I do maintenance every other Friday. Many times just using the shrink database command from the menu will not work. In that case this will most of the time.

use [myDatabase]
BACKUP LOG [myDatabase] WITH TRUNCATE_ONLY
DBCC SHRINKFILE ('myDatabase_Log', 2)

When that won?t work there is a script I use but it?s too big for this thread. And as always, GET THE BACKUPS!

by tony hopkinson

How often do you do a full back up ?

The only reason you are having to go through the procedure you describe is because you are not backing up your transaction logs.

You should either shift the database(s) onto simple recovery model (from full) in which case you won't have to do what you are doing now, or you should start backing up your transaction logs in between or with full backups.

Note if you shift to a transactional set up
say weekly full back up daily transactional one, you have all sorts of extra recovery options.

On simple recvovery model or doing what you are now (broken full recovery model), it's back to the last full good back up or nowt, anything that has happened since then, kiss it goodbye.

I'd look at this soonest if I were you, if the data is important and brown stuff hits the fan, most of it will land on you.

by sentdata

Your right to a degree. I do full backups every night. The reason we do it this way is because of some legacy systems that can only be backed up once every night. So if they go bad we roll back to the prior midnight. This should all change inside the next year as they go away. At that point I will go to a log backup. Good catch.

by ellyll

What we do is a database backup once a day and a transaction log backup every 3 hours. So in theory the most we can lose is 3 hours.

We use RAID 5 for the filesystems where the databases are stored so that we can survive a single disk failure, and the backups are written to a separate server so we have easy access to them if the database server dies for any reason.

by jd

This isn't really a tip, but Idera makes a handy (and FREE) SQL Server performance monitor called SQLcheck. It gives you a quick look at how your SQL Server is doing and you can run it as a screensaver. www.idera.com/freetools.

by tony hopkinson

It's amazing how many people have backup policies without recovery policies.

Shifting the database over to simple recovery mode would save you the truncate and shrink though.

by skatingzebra

Tony brings up a great point; no backup strategy is complete without periodic recoveries to ensure that the backups are valid. I've worked on large-scale systems with advanced tape backup hardware, offsite storage, disaster recovery, etc., that have failed because the tape backups were invalid. Incorporate monthly recoveries into your backup strategy! Once a month take a backup file from tape, restore it to a standby server, and then restore the backup. If all goes well you know you've got at least one solid backup to rely on, and if it fails you can correct the problem before you run into real issues.

by strangerthanu

I have tied various machines or peripherals to the bumper of my truck and dragged them down local logging roads.

Of course, the only improved performance I get from that is with regard to my own blood pressure.

by david_dba

Its very easy to get bogged down trying to squeeze the last drops of performance out of your system by fanatically creating indexes, changing obscure system settings, re-writing queries to run 0.5% faster, etc.

Take the easy wins first, there will always be a few simple changes you can make that will give the biggest gains. Have automated jobs setup to collect and log various metrics, such as disk space, file and table size, index fragmentation, I/O, etc. Using this info (and some appropriate server-side trace results) you can easily identify your top 'X' most expensive queries, your worst fragmented tables, your largest tables, etc. These will all give immediate boosts if taken care of properly.

I admit this is advice based on larger systems (1/4TB+), but is just as relevent on small databases suffering performance problem.

Collect data, analyse it, identify which "fixes" will return the greatest benefits for the least amount of effort, and do those first. Then sit back and see how it goes. As time passes, you will identify more "easy fixes", which can then be taken care of.

by strangerthanu

Some of the things you mention, I know how to monitor but how do you monitor index fragmentation? That would be very, very helpful for me.

As for trace results, do you simply open the trace and run the queries one at a time or are you monitoring their use by users in your production database?

by nahidmo

Just check out all DBCC command, You will get a big kick of all information that they can provide you about your databases and their details.

This discussion is closed to new replies.