4

We're moving a SQL Server 2005 database to a new server and upgrading to 2008, and I'm looking for some advice and reassurance that our approach is correct.

I inherited this database and it came to me in a bit of a mess truth be told - on top of that I've basically learnt to be a SQL Server developer on this server (no educational background in computing) so I've made plenty of my own mistakes on it. We're underfunded as a department (public sector), and so lacking expertise, pulling ourselves up by our bootstrings. The database started out as a back room thing but has become very important to our organisation and given this the performance is frankly embarrassing.

So we have issues such as

  • history of Autoshrink usage
  • History of undersized storage - never really enough space on the server for the db to grow so I'd guess the files are probably quite internally fragmented.
  • There are multiple collation types (for no good reason),
  • Inappropriate text datatypes all over the place (lots of Nvarchar, nchar, ntext when we have no need of ascii characters, and text where varchar would be more appropriate)

The new server is a 64bit virtual server, storage on RAID.

My plan after installing and upgrading is to:

  • Remove a vast amount of tables we've identified (with confidence) as redundant (about 1/3 of db size!)
  • Normalise collations to 1 type (after some analysis and testing we're certain this won't cause us problems - we have no requirement for case sensitivity)
  • Normalise text data types to varchar/char
  • Set database file sizes to the expected usage for the coming 2 years plus 50%

Is there anything else I should be considering after/before these processes - and do any of decisions here look off mark? For example the file size issue - is this something I need to worry about on RAID?

Improve this question
0

2 Answers 2

Reset to default
5

Shrinking and Storage

Enable "Instant File Initialization"

Disable autoshrink

Size your storage properly (15% free per drive)

RAID what? RAID level matters and SQL Server accesses the files differently. Log file access is generally sequential and Data is generally random.

tempdb

Separate data and log files (see RAID, below) - get a good tempdb strategy. We have tempdb logs on a log drive and our tempdb files (1/4-1/2 the number of cores) on their own drive. While you're at it, make sure that tempdb has an appropriate number of data files, that their initial sizing is exactly the same, and that they have exactly the same growth factor. While you're at it, read up on Trace Flag 1118

Schema Changes

If you're going to be making these changes, make sure you have a good, durable backup. What happens in 9 months when someone actually needed those tables?

Can your application actually withstand the changes from NVARCHAR, NCHAR, and NTEXT? While you're at it, NTEXT is deprecated so moving to TEXT would not be good. Check out VARBINARY or VARCHAR(MAX) instead.

You can change the collation all you want, but it won't affect any of the existing data.

Compatibility

Change the db compatibility levels to 100 if you can.

Tuning Option

Check out some of these trace flags none of them may be appropriate. Some of them might be, "it depends." Also, read about trace flag 4199.

Tooling

I'd also recommend coming in to this install with a good tool set. Create a DBA database for this toolkit. I'd start with sp_WhoIsActive by Adam Machanic, sp_Blitz by Brent Ozar. I'd also look for Kendra Little's work with scheduling and storing the results from sp_WhoIsActive. Finally, I'd start up a way of capturing Waits and Queues so that you have a good history of your server's profile.

Look into getting a monitoring solution.

Finally (and maybe firstly), check out Glenn Berry's excellent series on provisioning a new SQL Server Instance.

Part 1, Part 2, and Part 3

Improve this answer
1
  • This is superb, thanks very much. Autoshrink is long since turned off, but the rest of this is going to be fantastically helpful. We have little direct control over our hardware, but I'll bring the provisioning issues up in discussion with our server team.
    – DanBennett
    Commented Mar 13, 2013 at 16:40
2

I'm glad you have an opportunity to do this cleanup. I wouldn't worry too much about setting the database file sizes. As long as you have enough disk space, SQL Server can fly itself in this regard

In general, however, your issues shouldn't be impacting performance and that may still be an issue after cleanup. The solution would be to find and improve long-running queries. You can use SQL Server Profiler to find the problem queries, and I learned a lot by breaking them down and running them in SQL Server Management Studio, looking at execution times and execution plans. Good luck!

Improve this answer
6
  • thanks! We're planning to make better use of profiler after the move. At the moment the db performs so badly we've been concerned that the overhead of profiler would just make life impossible.
    – DanBennett
    Commented Mar 13, 2013 at 14:03
  • 1
    Yes, I'd wait until after the move. You don't leave the profiler running, incidentally, you just use it to collect a sample of the activity for a little while. As little as ten minutes, if there's enough activity for your slow queries to show up in that sample.
    – criticalfix
    Commented Mar 13, 2013 at 14:52
  • thanks, that's good to know. - I anticipated running it over a week or so to capture representative use; but you'd recommend not?
    – DanBennett
    Commented Mar 13, 2013 at 15:01
  • 1
    I'd certainly start with a small sample and play around with the slow queries you pick up. You will want a larger sample if you want to run the trace through the Database Engine Tuning Advisor and actually believe its index recommendation (I hesitate about that - indexing is often a case of less-is-more and common sense). The other case where I've run Profiler for a day or longer is when I'm trying to catch some rare condition. Also consider using PerfMon, if these are CPU/memory/disk bottlenecks. But I'd focus on the slow queries first.
    – criticalfix
    Commented Mar 13, 2013 at 15:32
  • 1
    Another thought: these performance issues won't differ greatly between SQL Server 2005 and 2008 (though 64 bits and new hardware will help). Do you have a test environment? If not, you might keep your old server around as a test environment to try out ideas (e.g., does adding an index really help this query?) Or else set up a parallel test database on the new server, if you have the disk space for it. You definitely need a test space of some sort.
    – criticalfix
    Commented Mar 13, 2013 at 15:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.