SQL Server Performance Forum – Threads Archive

Temp table Performance

Hi, I have main stored procedure which is calling 6 stored procedure in sequence. Since the data fetched in main stored procedure is required in sub stored procedures, temp tables are used (which contains the data fetched in main stored proc). The records which are processed are deleted from temp table so that it is not processed again. Because of this there are lots of read and delete and update to temp table. While running main stored procedure through query analyser it takes 2 – 3 seconds.. which seems to be not good but ok.. but the main problem comes after that. I have windows service which runs continuosly and it calls main stored procedure in loop with an interval of 1 second. And this is where it is creating problem. When it is running as service (that is same procedure called in loop), SQL Server takes more then 90% of CPU continuosly and also takes more memory. Dont know why? If I increase the interval to say 5 second then utilization decreases to approx 80%.. I guess its taking more CPU because of temp table.. not sure though.. Can u throw some light on how can I reduce CPU utilization taken by SQL Server. Thanks,
Nilay.

If the procedure takes 2-3 secods to execute and it is being called every second then there are going to be performance issues. Let’s address this one by one. The windows service which calls the procedure must be waiting for the previous execution to end before calling it again or does it start a new thread and call procedure from there? 2-3 seconds for a procedure is too much to ask for. I am sure there is some scope of performance tuning for the same. Have you tried using ITW to check if there are any recommnedations for indexes? Also check if you can find any scope for creating index. One generic tip, do not create temp tables in a transaction. This may lead to some locking issues. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.

Hi gaurav_bindlish, Could this also related to stored procedure being recompiled?

BTW: What OS, SQL and SP?
Luis Martin
Moderator
SQL-Server-Performance.com

OS: Windows 2000 Advance Server SQL Server 2000 The windows service – Executes Stored procedure – Waits for completion of stored procedure – Waits for 1 second – Above cycle continues To give u more background.. My application has multiple databases on same server and Stored procedure contains joins between tables of multiple database. Because of this I am not able to run ITW to analyse on this query. Any idea how to make Index Tuning Wizard run of this query? Temp tables are not created in transaction (so i am safe as far as locking is concerned <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />) but they are used in transaction.

Check: http://groups.google.com/groups?hl=en&[email protected]&rnum=3 HTH Luis Martin
Moderator
SQL-Server-Performance.com

Having creation of temporary tables casn certainly lead to SP recompilation. This behavior can be confirmed by running SQL profiler. The only way to avoid it is to create the temp tables at the begining of the procedure. This reduces the chances of recomiplation and even if it happens, it will happen only once at the begining of the procedure. Another thing worth mentioning is having permanenet empty tables in place of tmp tables. Here the processing becomes more comples as you have to store the ID for the user running the query in the empty table as you don’t want to mix the data between users if more than one is running the same query. The way it works is that you insert temp table data in this table and then clean it up after the SP processing is over. The advantage is that you can create indexes on these empty tables as well. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.

Hey gaurav, what do you mean by creating the temo tables in the beginning for the stored procedure.

http://www.nigelrivett.net/AccessTempTablesAcrossSPs.html Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

I think Satya’s link should explain the same. In short I meant that all the Create Table / Create Index etc. statements (DML’s) should be at the start of the stored procedure like… CREATE PROCEDURE….. AS CREATE #TEMP….
CREATE #TEMP2…. INSERT INTO #TEMP…
<Continue Processing….> Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.

]]>