Is Windows 2003 really faster for a SQL Server/IIS

environment? Let us do all the leg work for you! We’re about to upgrade the

SQLServerCentral.com web and SQL Server from Windows 2000 to Windows 2003. As

part of the case study, we’re going to capture a baseline of the server’s

overall performance before the upgrade and then again after the upgrade. We’ll

then document the experience and benchmark numbers.

Currently, our SQL Server and IIS services are on the same

server, which is typical with small companies. We also run ListManager 7.0 on

the server. It’s a Dell 2way server with 4GB of RAM.

Windows 2003 Experience So Far

Recently we began to upgrade our development servers to

Windows 2003 at my day job. This has gone extremely well. The upgrade asks for

3 or so questions and chugs away until the end. On our SQL Server machines,

I’ve noticed a 5-10% improvement in overall utilization of resources without

making a single change. Also, my extract processes seem to be much faster on my

test system. A 3 hour process now is 15 minutes faster without changing any

code.

With that said, we’re curious about how it will handle our IIS

machine with SQL Server on it. We hope our real-world scenario will help you

out. We of course must upgrade the SQLServerCentral.com development server

first and then the production server, which should take a while. We’ll keep you

informed each step of the way though with how much code we have to change to

get it to work and how much grief (if any) it causes us.

The Baseline

Specifically, we plan to create a baseline with the following

counters in Windows System Monitor. Are there counters or measures that you’d

like us to monitor as well to create our before and after baseline with? If so,

please click on My Opinion and post what else you’d like us to use as a

measurement of success.

Hardware Counters

• Memory \ Available Bytes – Shows the available amount of physical memory

  on the server.

• Memory \ Pages/sec

  – Shows the number of pages that are read from or written to disk.

• Network Interface \ Bytes total/sec

  – This counter shows the amount of traffic through your network interface in

  bytes a second.

• Paging File \ % Usage – Similar to the Memory \ Pages/sec counter, this

  shows the percentage of the page file that is being utilized.

• Physical Disk \ % Disk Time

  – This counter shows how active your disk is in percentage form.

• Processor \ % Processor Time – This counter is one of the most important

  ones to me. It shows you how active the processor is in percentage form.

SQL Server Counters

• SQLServer:Access Methods \ Full Scans/sec

  – This shows the DBA how many full table or index scans are occurring a second.

• SQLServer:Buffer Manager \ Buffer Cache Hit Ratio

  – This shows you the ratio of how many pages are going to memory versus disk.

• SQLServer:Database Application Database \ Transactions/sec

  – Shows the amount of transactions on a give database or on the entire SQL

  Server a second.

• SQLServer:General Statistics \ User Connections

  – Like the transactions a second, this counter is merely used for creating a

  baseline on a server and in the troubleshooting process. This counter shows the

  amount of user connections on your SQL Server.

• SQLServer:Latches\Average Latch Wait Time (ms) – Shows the average time

  for a latch to wait before the request is met.

• SQLServer:Locks \ Lock Waits/sec –

  Shows the number of locks a second that could not be satisfied immediately and

  had to wait for resources.

• SQLServer:Locks \ Lock Timeouts/sec- This counter shows the number of

  locks a second that timed out.

• SQLServer:Locks \ Number of Deadlocks/sec

  – This counter shows the number of deadlocks on the SQL Server a second.

• SQLServer:Memory Manager \ Total Server Memory

  – Shows the amount of memory that SQL Server has allocated to it.

• SQLServer:SQL Statistics \ SQL Re-Compilations/sec – This counter shows

  the amount of SQL recompiles a second.