SP takes long time but reboot fixes it

[mmitchell]

I have a stored procedure that normally takes 1-2 seconds to run.

However, after a couple of days, it then takes 7-8 minutes to run.

The tables are not being heavily modified.

If I stop the SQL server and its service then start it again and run the same stored procedure, it runs again in under 2 seconds.

I am doing a nightly "Reorginize data and index pages" with the option checked for "Change free space per page percentage to" set to 10%.

Any thoughts?

 

[SQL_Hell]

hi,

is anything else running on this server?

what does task manager / performance tell you when it starts to run slow?

 

[FoFa]

At first glance I would say it is an indexing issue. How many, what type of indexes are in this DB? I usually find a nightly run of reorg is over kill, I usually run them weekly.

 

[mmitchell]

hi,

is anything else running on this server?

what does task manager / performance tell you when it starts to run slow?

A few things, but it is a quad processor and when I look at it nothing is being taxed much.

 

[mmitchell]

At first glance I would say it is an indexing issue. How many, what type of indexes are in this DB? I usually find a nightly run of reorg is over kill, I usually run them weekly.

But shouyldn't the nightly reorg "fix" that?

Also, is the 10% setting OK? I'm not real sure what that means, but it was the default.

 

[SQL_Hell]

what about memory?

I was wondering whether something else running on that server is causing a memeory leak.

Check whether the peak commit charge is greater than the physical amount of ram

 

[FoFa]

But shouyldn't the nightly reorg "fix" that?

Also, is the 10% setting OK? I'm not real sure what that means, but it was the default.

Maybe, if you have plenty of disk space, you may try to up the amount of free space. May not have any effect. How often are you empting the log file?

 

[mmitchell]

Maybe, if you have plenty of disk space, you may try to up the amount of free space. May not have any effect. How often are you empting the log file?

The server has 2% free disk space, which is about 800 meg on this disk.

And since I don't know how to empty the log file, I will say that I never have empted it. (How do I?)

 

[mmitchell]

Well, now when I reboot the server it still takes 8 min to run, so now that is not even fixing things!!

Is there a way for me to completely rebuild the indexes and statistics and whatever else on all my tables all at once.

If so how?

 

[SQL_Hell]

there are two ways of reindexing a sql server database, one on-line method and one off line method, here are examples of both....

USE DatabaseName --Enter the name of the database you want to reindex

DECLARE @TableName varchar(255)

DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor

DEALLOCATE TableCursor


This will have to be done when the database is offline, the other method is DBCC INDEXDEFRAG which can be done when the database is on line.


Look here fro more info

http://www.sql-server-performance.com/rebuilding_indexes.asp

 

[mmitchell]

the other method is DBCC INDEXDEFRAG which can be done when the database is on line.

Do I just replace the line: DBCC DBREINDEX(@TableName,' ',90)

with: DBCC INDEXDEFRAG (@TableName,' ',90)

and run the same code, just with it off-line?

 

[mmitchell]

Now, this makes no scense to me, but maybe it does to you all:

I made a backup of my production database that I am having the issue with and brought it home with me and restored it to my personal SQL server at my home (dosen't everyone have one?) and ran my sp and it took 3 seconds, just like it is supposed to!! I just now logged back into my production server and ran the same sp and it took over 8 minutes!!!

Does this help anyone in helping me?

I have not touched the database in any way, i.e. I have not tried any of the above DBCC stuff yet.

I think I am going to quit computers and become a hermitt!!

 

[SQL_Hell]

Hi there

sorry for the late reply, I have had a rather hectic christmas to say the least.

That is a rather bizaar issue you have described here, have you had any luck sorting it?

 

[FoFa]

OK, What does your SP do?