Timeline for SQL Server 2005 Database move and rationalisation - Recommended maintenance processes
Current License: CC BY-SA 3.0
9 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Mar 13, 2013 at 16:40 | vote | accept | DanBennett | ||
| Mar 13, 2013 at 16:40 | |||||
| Mar 13, 2013 at 16:35 | comment | added | DanBennett | Thanks very much for all this - incredibly helpful stuff and very much appreciated. A test server is maybe a possibility now. I've certainly felt the inability to test tuning options somewhere safe- as have our users occasionally! | |
| Mar 13, 2013 at 15:49 | history | migrated | from stackoverflow.com (revisions) | ||
| Mar 13, 2013 at 15:39 | comment | added | criticalfix | 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. | |
| Mar 13, 2013 at 15:32 | comment | added | criticalfix | 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. | |
| Mar 13, 2013 at 15:01 | comment | added | DanBennett | thanks, that's good to know. - I anticipated running it over a week or so to capture representative use; but you'd recommend not? | |
| Mar 13, 2013 at 14:52 | comment | added | criticalfix | 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. | |
| Mar 13, 2013 at 14:03 | comment | added | DanBennett | 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. | |
| Mar 13, 2013 at 13:27 | history | answered | criticalfix | CC BY-SA 3.0 |