question

Rick Schummer avatar image
Rick Schummer asked

SQL Server Performance Drops

One of my customers has a SQL Server 2005 Standard Edition with the current service pack. Over the weekend the Windows 2003 Server had some Windows Update (all security patches) applied. Performance on batch process doing adds, deletes, and updates went from 1000 transactions to 30 transactions per second.

SQL Server is on its own server box. Web application via IIS on its own server box. Both servers are hosted in the cloud and referenced in private network (IP 192.168.x.x)

Anyone heard of any Windows Updates that bog down SQL Server 2005?

Things that we have checked:

  • Running queries across the network and directly on the SQL Server server have same performance drop, so the network infrastructure, network card, and driver do no seem to be impacting the problem.
  • Not all queries on the server are suffering the same percentage of drop, and short queries still run as fast as normal. Batch process is most affected.
  • None of the Windows Updates have any mention of SQL Server.
  • Log file is around 5 GB, database is around 4 GB

The app has been running fine for the last few months since it was implemented.

I am not convinced the Windows Update is nothing more than a coincidence timing wise, but it is hard to ignore as well.

Wondering if there were any recent battles for DBAs or developers with a Windows Update killing SQL Server performance.

sql-server-2005performance
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

2 Answers

·
Blackhawk-17 avatar image
Blackhawk-17 answered

Based on your dB size I'll assume you are running off drives on the server and not a SAN.

Was anti-virus software re-configured and exclusions missed?

Did someone alter your SQL memory configs without your knowledge?

Did the O/S team move a pagefile to your dB drives?

Does any other team use your server?

It appears drive based on first glance but without more metrics it will be hard to pin down.

Ciao

1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Rick Schummer avatar image Rick Schummer commented ·
All good questions we are looking into. I know there is no AV, but in the cloud someone else does control certain aspects of the server.
0 Likes 0 ·
user-1392 avatar image
user-1392 answered

Did you get an answer for this? If so please post here, or flag the correct answer! If not...

Use SysInternal's Process Explorer to find out which processes are using most cpu and i/o and where they come from (I've had problems with the DPM agent hogging i/o).

Look for extra/long running jobs and in the logs for unusual activity.

Look at fragmentation, both within SQL and in Windows.

I'm assuming it's not a virtual server?

Windows search wasn't installed in the update was it?!

Dave.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.