NOLOCK CONTINUED...

[HulaGirl]

This is a continuation of a previous question on NOLOCK.

Here is the situation. we have a static database -- a copy of the production database. there is no update, insert, or delete activity on the database, it is strictly used for reporting and research of production problem. there are a lot of views, stored procedures, and DTS jobs accessing this database. lately, we have experienced very slow response from these views, stored proc, and DTS jobs. Time out error occurs very often.

will putting NOLOCK in every FROM clause of the above objects help to improve the situation (remember, there is no update activity in this database at all)?

 

[clapag22]

I wouldn't do that. If it's truly a read only DB then make it a read only DB (right click on DB - Properties - Options tab on SQL2000). That eliminates any locking issues you may have. Although I suspect your problems may be something else.

 

[donutman]

I've noticed that after a reboot of my server, a particularly complicated SP takes about 45 sec to run. After about 2 weeks the SP takes 4 min. to run. Try rebooting and see what happens. Does anyone have an explaination?
-Karl

 

[bertrandkis]

Dotnutman, is your stored procedure accessing a fast growing table? Remember that the perfomance of your stored procedure may degrade depending on the amount of data it handles especially if it was not written bearing in mind the volume of data it could handle.

 

[donutman]

I wouldn't characterize the tables as fast growing. The SP adds summary totals of sales and labor costs for the previous week. All tables involved do get larger, but the point is that after a reboot, the performance dramatically improves. It once again executes in about 45 sec. I've been playing with a weekly automatic early morning reboot to see how that helps.
-Karl