One Stored Procedure started to run slow after our dba left the company( or this is a coincidence?). Before it was running about 1 min. Now it runs several hours and never ends (I cancel the job after several hours). This SP has 2 cursors. It creates temporary tables. Tempdb has about 3GB available space during SP execution.
I read several articles on internet, like http://dba.stackexchange.com/questions/60179/when-a-previously-fast-sql-query-starts-running-slow-where-do-i-look-to-find-th , https://blogs.msdn.microsoft.com/queryoptteam/2006/03/31/i-smell-a-parameter/
and more, also tried WITH RECOMPILE - nothing helps. It is SQL Server 2008 R2.
Server is restarted ones a week.
I read that Memory reservations cannot usually grow during query execution, regardless of how much free memory the server may have.
What else can I do? I'm a report developer I don't know a lot about SQL Server performance.
Since the query was running well my guess would be that it is database maintenance (or lack thereof) is the cause of the poor performance. So before you do any code changes such as WITH RECOMPILE and so on, make sure that the indexes are rebuilt/reorganized and statistics are kept up to date. Search and you will find lot of resources on how to check if the indexes are fragmented, how to rebuild etc.
If you added a lot more data or if the nature of the queries have changed, then you may need to investigate more and/or make code changes, but before you do any of that investigate indexes and statistics.
Specifically look for indexing code from Ola Hallengren. It is widely used and considered a VERY good resource. A simple search will turn up the site where you can download the code and supporting tables.
I checked for one table, which is used in this SP, if it has indexes and statistics and yes,
It has 9 indexes and several dozen statistics, but it looks like statistics were not updated in the last weekend maintenance.
For example, statistics on column ORIGTYPE were last updated 6/18/2016. is it very important?
Another question - should I trust to users of this site and download codes by their advice? What if this code will ruin my database?
Statistics has a histogram of the data distribution in your table/index. By default, statistics updates automatically when the data in the table changes by some percent or a certain number of rows. You can read some more about it here.
Ola Hallengren's code is widely used and highly recommended. So it is very very unlikely that it will damage your database. Nonetheless, you should try it out in a testing environment. Minion Reindex is another popular reindexing tool. You can use built in capabilities and t-sql to do the reindexing yourself as well. Take a look at the documentation and examples on this MSDN page.
OK, I solved this problem. And the reason was what some users messed up with one order and inconsistency with data created an endless loop in Stored Procedure. When I filtered this Order Number from SP - it executed fast. Now I describe how I found that. I tried to do different things like indexes and other staff, but SP ran slowly. One time I scheduled it to run at 1:00 am and when I came to work - it was still running. I made a copy of database, so nobody interrupts SP from running - nothing helped. But other SPs ran as usual. So, I decided that something is wrong with SP or data, not with SQL server. I created a table and made 2 insert statements, like INSERT GetDate(), OrderNo, ... INTO ThisTable. I have cursor and loop inside the cursor in SP, so I created one INSERT inside the cursor and another inside the inner loop. After 7 minutes of executing a query I cancelled it and looked at the table. I found that a new record was inserted every millisecond, and I also found that the first 5000 records had different Order Numbers, but next 500000 records had the same order number. So I found an order that creates an endless loop.
Thus the reason people say CURSORs/Loops are bad. 
I don't know who created this Stored Procedure, but sometime it is not possible to make a right code without Cursors or Loops. Just keep in mind that under some condition it can be endless.
The problem is that way too many people give up on avoiding such forms of RBAR way too early. start thinking in columns instead of rows and you'll come by it more and more naturally in a short period.