Investigate stored procedure running too long, help pls. 1

[peac3]

Hi guys,

I have found my stored procedure running too long for last two days, I have checked the cpu and memory running normal from windows task manager.

I was just wondering what more checks would you do before I ask DBA to put the trace on, but to put the trace on will increase the database log so that would be my last resort.

I am using MSSQL 2005 and I heard there is a feature in 2005 to check memory space in 2005, please any advise will be appreciated.

Thanks in advance.

 

[markros]

Do you mean this SP worked fine before? Could it be a symptom of Parameters Sniffing?

Take a look at this blog

http://pratchev.blogspot.com/2007/08/parameter-sniffing.html

PluralSight Learning Library

 

[Crowley16]

here's a list of thing's I'd check, in order:

1) update statistics on the tables involved (full scan if possible)
2) check index fragmentation.
3) check execution plan.
4) when running, check activity monitor or sys.sysprocesses, look for the wait types, and/or blocking.
5) check the execution stats with sys.dm_exec_query_stats.

it's only very rarely I'd go beyond that. Certainly checking memory buffers is possible, but I've found it doesn't help that much as it doesn't tell you exactly what process is using what memory, and how.
I'm not sure there's a way to do that, but if anyone does know, then I'd be very interested.

--------------------
Procrastinate Now!

 

[peac3]

Hi Crowley16,

How to do the task no 1, using " include profile and client statistic"?

No 2, does it mean by using "sp_helpindex"?

No 3, the SP is part of the scheduling batch so how do I check execution plan?

I'll try no 4 & 5.

Thanks,

 

[markros]

For 2 check this blog

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/finding-fragmentation-of-an-index-and-fi

PluralSight Learning Library

 

[Crowley16]

peac3,

What level of dba experiance do you have with sql server? I really don't mean to be rude but it's very easy to do a lot of damage and if you're investigating stuff on a production server then you really need to have enough knowledge to not break stuff.
I've often done very dumb things and have occasionally caused down time due to lack of care and knowledge myself.

Taking that into account:

1) look at your stored procedure, find the tables that it references, and run: update statistics <tableName> with fullscan

2) index fragmentation can be checked by using sys.dm_db_index_physical_stats. This is a built in system function, check bol for inputs.
sp_helpindex can be useful for finding out which indexes you need to check.

3) turn the show execution plan option on, hotkey Ctrl + m, run the code of the stored procedure. You might want to read up on execution plans to understand how to use it.

Again, please take care when doing this stuff and read bol before running each command to understand exactly what it does and the potential impact.



--------------------
Procrastinate Now!