1

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jyotsna Sonawane
  • 303
  • 1
  • 3
  • 17

1 Answers1

0

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

Harsimranjeet Singh
  • 514
  • 2
  • 6
  • 19