SQL Server Express: How to determine SP memory usage

Question

I am developing with Microsoft SQL Server 2008 R2 Express.

I have a stored procedure that uses temp tables and outputs some processed data usually within 1 second.

Over a few months, my DB has gathered a lot of data almost reaching the 10 GB limit. At this point, this particular stored procedure started taking as much as 5 mins for the same input parameters. After I emptied some of the huge tables in DB, it got back to normal.

After this incident, I am worried if my stored procedure needs more than necessary space in DB. How can I be sure? Any leads?

Thanks already Jyotsna

Answer 1

Follow this article

Other old school way is run spwho2 check your spid related to the database see CPU and IO usage.

To validate run DBCC INPUTBUFFER(spid)

Also check STATISTICS of SP in original scenario without purging data from tables.

SET STATISTICS IO ON EXEC [YourSPName]

see the logical reads , also refer article