Post reply

Stored Procedure problem ??

  • February 17, 2005 at 12:56 am

    #85100

    Hi Guys,

    I've got a slight issue with a stored procedure.

    We have a website and we log the access to the site and save to a sql table...pretty straightforward.

    Recently we have a cases of of the site now allowing users to access it. Closer examination revealed the SP used to log the access was running slow/hanging/not working. It is a very simple procedure..simply take the parameters and INSERT into a table.

    The short term fix is to run the procedure with some dummy values with the WITH RECOMPILE option.

    I don't see why we should have to do this as the table is only increasing daily by a few thousand rows..nothing horrendous. I've even reduced the table to 200000 rows by archiving some information.

    Does any one have any thoughts on this?..a plan of attack perhaps.

    Cheers...Graeme

     

  • February 17, 2005 at 1:55 am

    #542726

    Do you have a clustered index on the table and if so on what column(s)?

    How many indices are there on your table? Do you really need them?

    It is always worth checking the statistics on the table.

    LinkedIn Profile

  • February 17, 2005 at 2:20 am

    #542738

    Hello David,

    Yeah! checked those.

    Only one clustered index on an ID column.

    This problem has only started to happen recently.

    Also, the table has transactional replication...could this be affecting it..shouldn't affect anything I know.

    At the moment everything is running ok.

    But...at 2 in the morning, I don't wish to be woken up to fix.

    Thanks for the suggestions ....I shall keep looking.

    Cheers..Graeme

  • February 18, 2005 at 4:22 am

    #543043

    Have you tried some maintenance on the table - dbcc reindex or indexdefrag )(or drop and recreate the indexes if you can), update stats and then recompile the stored procs. Also make sure your indexfill factor and pad_index are not 100% so you are avoiding page splits in the data pages and index structure.

    Hope this helps

  • February 22, 2005 at 1:35 am

    #543413

    Thanks for that.

    Yep, tried all that. It is a mystery.

    I shall have to keep monitoring.

    Many Thanks for all your input.

    Graeme

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply