SQL Memory usage and server rebooting

[PTW]

We had a stored procedure that was taking 42 secs to run on our production SQL Server, and only 10 secs to run on a development server using a copy of the production database. The production server has a lot more memory and processors (it is actually a cluster of 2 SQL Servers--one as a failover--using a shared disk array).

The Windows Performance Monitor showed memory usage on the server at a persistent 2.8GB out of 4GB (we reserved 3GB for SQL). The tempdb was pretty empty, as was the database log file. After rebooting the server, the memory usage went down to 300K and the Stored Proc ran in 8 secs instead of 42 secs.

Any idea on what the problem could be? Do we need to reboot our SQL Server occasionally? (It had not been rebooted in 9 months of operation.) Could it just have been a memory leak from something else?

Thanks.

 

[SQLBill]

It could be many things....you rebooted your server before checking so it will be hard to check. The first thing I would have checked is Current Processes. Sometimes a process won't release it's connection...too many of those can slow things down. Sometimes a query never finishes or a transaction is cancelled without being committed or rolled back. All of those can cause blocking, slowness on the database.

-SQLBill

Posting advice: FAQ481-4875