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.
