What causes queries to hang - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

What causes queries to hang

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2012-01-05 : 10:07:26

We have a staging database in which we have 2 identical schemas. We load a set of data into the first schema. Once a certain amount of data is loaded, we switch the load to insert into the second schema, and then update a fact table within the first schema. Once the data has been updated, we then load this data into a repository database and truncate that set of tables (the first schema). We then stop loading into the second schema and start loading data into the first schema again..... we update the data in the second schema, load into the repository database, truncate, and so on so forth.

This 'Switch' process is then repeated throughout the day.

We have a series of procedures called UpdateFacts. Each of these procedures update a different fact attribute in the Fact table. A wrapper procedure calls each of these procs in a while loop using dynamic sql. It should be a sequential process, as no other queries will be running against the same set of staging tables while the facts are being updated.

However, one set of staging queries seem to hang for long periods on end. It is always the same staging area that experiences this, until we run FREEPROCCACHE. Then this resolves the issue, and the queries complete. However, the issue occurs again not long after, but this time against the other staging area.

My theory was that is was something to do with reusing the compiled query plan for one set of staging tables for the other, and it wasn't very optimal. Hence why freeing the cache resolved it.

But why would this cause such a levels of locking/hanging etc. Has anyone experienced something similar?

Does anyone understand SQL Server so totally that they could explain what is going on and how I can resolve it?? (fingers, toes, arms and legs all crossed for this otucome)

Thanks

Hearty head pats

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2012-01-05 : 11:04:03

quote:
Originally posted by Bex

Does anyone understand SQL Server so totally that they could explain what is going on and how I can resolve it??

No but maybe a few ideas. Maybe running the FREEPROCCACHE was a coindidence? i.e. the hanging was just about to be over then you ran FREEPROCCACHE. I would try this again to confirm your theory.

Here is a query that may give insight. It shows free memory and used memory the proc cache is part of 'Stolen pages'. But keep in mind even if there is no Free Pages that is not neccessarily a problem. SQL Server frees up pages as it goes. Maybe try running FREEPROCCACHE before the process starts.

select 
    *
from 
    master..sysperfinfo
where 
    object_name = 'SQLServer:Buffer Manager'

You also want to be sure what is hanging. Do you know for sure which Stored Procedure it is? Do you have a query which will tell you which sql statement within the stored procedure is running? Try this one?

SELECT 
      r.Session_ID, 
      DB_Name(Database_ID) DatabaseName,
      r.Start_Time, 
      convert(decimal(10, 3), datediff(ms, r.start_time, getdate())/1000.0) Running_Time_Seconds,
      left(s.text, 2000) Statement_Start, 
      object_name(objectID, database_id) [Object_Name],
      SUBSTRING(text, (statement_start_offset/2) + 1,      
                  ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(text) ELSE statement_end_offset END
                  - statement_start_offset)/2) + 1) AS Statement_Text,  
      Blocking_Session_ID, 
      *
FROM 
      sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
order by 
      R.Start_Time

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2012-01-05 : 11:11:34

Here is something - I just found someone with a similar problem:
[url]http://www.sqlservercentral.com/Forums/Topic444800-8-1.aspx[/url].

At first I was skeptical that FREEPROCCACHE was actually fixing the problem. But maybe I can see it. Are you starting from an empty table then uploading a huge amount of rows? This could quickly put your stats out of date during this process. You may need to use a query hint for the index (assuming you have indexes). Or try updating stats on just the particular table involved at some point during the process.

Or better yet use WITH RECOMPILE.

Bex
Aged Yak Warrior

580 Posts

Posted - 2012-01-06 : 03:51:25

Hey Denis

Thanks for your suggestions. That is a huge help. We are trying what you suggested, and are updating the stats on the main problem table. I'll let you know if it works :)

Hearty head pats

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2012-01-06 : 08:38:05

You're welcome.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources