Sql-server – Ways to Reduce Database Size and possibly improve performace. Untouched enviroment

index-tuningperformancesql serversql-server-2008-r2

I am new to an environment. The databases are virtually untouched by a seasoned DBA. I am looking for ways to reduce DB size and possibly improve performance.

Unfortunately, I only have Web edition SQL Server 2008 so compression is unavailable. What are some things that I can do, before I ask for SQL and HW upgrades?

I'm hosting multiple sites, they all have the same schema structure, but may vary from customer to customer based on use. I want to reduce the size of the MDF files and the overall usage of the data drive they are on.

Best Answer

Zerothly, you need to test your backups and have a recovery plan if your not doing that.

Let's talk about some quick win performance first of all. Here are some options. Google can help flesh these out more.

  • Are their multiple named instances on the box?
  • Have you set max memory?
  • Have you set the number of files for tempdb?
  • If it's at all possible, put the data files and log files (usually with the extensions MDF and LDF respectively) on different physical disks. Not just logical drive letters on the same disk drive.
  • Determine your index fragmentation levels.
  • Maintain your indexes. You have two good free options:
    • Ola Hallengren's solution is something I like on servers I have to maintain because it has a very verbose full manual mode for running from SSMS. It can also be automated.
    • Minion Reindex is going to scale better for you actual automated reindexing.
  • This will require reformatting the drive with the SQL Server databases, but setting your disk partitions correctly is important.
  • Enable Instant File Initialization.
  • Set your file growth options sensibly.

Space-wise:

  • If you delete data or indexes from the database, the MDFs don't shrink. You have to shrink them. Shrinking is generally bad, but on rare ocassions it makes sense.
  • Are you doing FULL or SIMPLE recovery mode? If Simple, do your clients not need point in time data? If your doing full recovery are you properly managing your transaction log backups?
  • What are you options for archiving data, dropping unused indexes and altering schema?
Related Question