Content area
Full text
Identify performance problems before a SQL Server upgrade and hardware migration
You finally received the approval to upgrade SQL Server and buy a new server. As you think about all the new SQL Server features that you want to take advantage and the benefits of having new hardware, your boss stops by. She asks, "Now that we can spend the money on this project, how are you going to be certain that we won't have any issues? Arc you sure that things will run faster on the new server? How will you know?" As your boss walks away, you break out in a cold sweat at the thought of the upgrade failing or performing poorly. "There must be an easy, low-cost way to predict performance," you say to yourself as you start searching for a tool. The good news is that there is such a tool. You can use SQL Server Profiler's replayable trace functionality.
As a consultant, I'm often asked to assist with SQL Server upgrades and hardware migrations. I've found that many IT shops just assume that a new version of SQL Server will perform better than the old one. What they fail to realize is that they might be running legacy code that isn't efficient or won't benefit from the new functionality in the new SQL Server version. In the case of a migration to new hardware, many system administrators will configure a new server the same as the old one, which can perpetuate poor server response. Unfortunately, the poor response isn't realized until a load is placed on the new server. This often happens the day that users are allowed to access the new server. Failing to test new versions of SQL Server or new hardware can result in unpleasant surprises after the upgrade or migration.
Using Profiler's replayable trace functionality (i.e., the trace replay template), you can test query compatibility and performance in a new SQL Server version or test a workload on new hardware. You can also use it to find T-SQL code that needs to be optimized or disks that need to be reconfigured. You can even use it as an inexpensive stress tester. Although there are other tools you can use for these tasks (e.g.,...