SSB Threads Are Blocking (blkby In Sp_who2)
Jun 21, 2007
am experiencing excessive SSB thread block'n...sql error log is reporting LOTS of Resource Monitor messages about non-yielding threads (nothing meaningful can be surmised from it).
I am running on a 4way 64bit 2003 box w/6gb ram!!!
SSB architecture is simple implementation... Leveraging async trigger(s) in 42 db's (all on same instance) that post (via srvc) into a mstr db queue...where a listener is pull'n them off and applyies to a table (trying to avoid excessive 1205's that I was experiencing using sync trigger approach before)....messages sit in respective db's trans queue and draining of queues is extremely SLOW!!!! I mean SLOW!!!
Eventually SqlServer.exe process pegs out ALL processors!!! Only can reboot box to get connectivity back...~
Anyone have this experience!? (really hope not...but I need help)
Have completely cycled SSB machinery (via disable/enable)...and have even stepped thru enabling one db at a time...but still very poor performance!!!
Anyone?
-mt
sp_who output here...
BACKGROUND sa . 16 NULL RESOURCE MONITOR
BACKGROUND sa . . NULL LAZY WRITER
SUSPENDED sa . . NULL LOG WRITER
BACKGROUND sa . . master SIGNAL HANDLER
BACKGROUND sa . . NULL LOCK MONITOR
sleeping sa . . master TASK MANAGER
BACKGROUND sa . . master TRACE QUEUE TASK
sleeping sa . . NULL UNKNOWN TOKEN
BACKGROUND sa . . master BRKR TASK
BACKGROUND sa . . master TASK MANAGER
SUSPENDED sa . . master CHECKPOINT
sleeping sa . . master TASK MANAGER
sleeping sa . . master TASK MANAGER
BACKGROUND sa . 16 ThompsonTractorD43 KILLED/ROLLBACK
sleeping sa . . master TASK MANAGER
BACKGROUND sa . . master KILLED/ROLLBACK
BACKGROUND sa . 16 master KILLED/ROLLBACK
sleeping sa . . master TASK MANAGER
BACKGROUND sa . . master BRKR TASK
BACKGROUND sa . 16 master BRKR TASK
sleeping sa . . master TASK MANAGER
sleeping sa . . master TASK MANAGER
sleeping sa . . master TASK MANAGER
sleeping sa . . master TASK MANAGER
BACKGROUND sa . 16 YancyMachineryCat KILLED/ROLLBACK
BACKGROUND sa . . master BRKR EVENT HNDLR
BACKGROUND sa . . master BRKR TASK
sleeping NT AUTHORITYSYSTEM REFINERY1 . msdb AWAITING COMMAND
sleeping NT AUTHORITYSYSTEM REFINERY1 . msdb AWAITING COMMAND
sleeping NT AUTHORITYSYSTEM REFINERY1 . msdb AWAITING COMMAND
sleeping NT AUTHORITYSYSTEM REFINERY1 . msdb AWAITING COMMAND
SUSPENDED NT AUTHORITYSYSTEM REFINERY1 . msdb DELETE
sleeping fastironweb DETROIT . Cat_Lvl3 AWAITING COMMAND
sleeping mike REFINERY1 . master AWAITING COMMAND
SUSPENDED NT AUTHORITYSYSTEM REFINERY1 . distribution WAITFOR
sleeping mike REFINERY1 . Cat_Cfsc AWAITING COMMAND
sleeping mike REFINERY1 . Cat_Cfsc AWAITING COMMAND
sleeping mike REFINERY1 . Cat_Cfsc AWAITING COMMAND
RUNNABLE mike REFINERY1 . Cat_Cfsc SELECT INTO
sleeping NT AUTHORITYSYSTEM REFINERY1 . msdb AWAITING COMMAND
View 15 Replies
ADVERTISEMENT
Oct 24, 2007
Hello,
When I run sp_who2, the blkby col shows spid -2.
From doing some reading I understand this is caused by SQL waiting on MSDTC.
If I look at MSDTC running transactions I can see 2 transactions, the transaction are not mark as indoubt so I cannot commit, abort or forget them.
If I stop ALL the Processes that are using the DB, eg IIS and a 3rd party workflow engine, these transactions do not go away.
Anyone got a clue?
If I restart MSDTC, the blocks go away in SQL but this is not a solution to the problem.
All MSDTC gives me is the ID for the transaction, can I get more info on whats happening from this ID.
Thanks
Steve
View 1 Replies
View Related
Nov 19, 2001
Whe we execute sp_who2 on a SQL server
Command column displays the following:Any idea on what is CONDITIONAl??
SIGNAL HANDLER
LOCK MONITOR
LAZY WRITER
LOG WRITER
CHECKPOINT SLEEP
AWAITING COMMAND
BULK INSERT
CONDITIONAl
Thanks.
Di.
View 3 Replies
View Related
Apr 19, 2008
sp_who2 shows the block by user and some information.Currently we are moving from sql 2000 to sql 2005. if i execute the sp_who2 in sql 2005 it shows only my login block information. it does not show other users. is there any admin rights has to give to view other block user ??? how to do that.
View 7 Replies
View Related
Aug 14, 2002
DiskIO: Any idea what exactly this tells on sp_who2 output? Does that men anything if its high/low?
Thanks.
Ravi.
View 1 Replies
View Related
Jul 23, 2005
Is there any way to calculate (just an approximation is fine) theamount of work that is done for an insert statement?For example, can I calculate the approximate row size of my table andthen compare that with the DiskIO for my SPID to determineapproximately how many rows have already been written? Or, does theDiskIO include shuffling data around or other DiskIO that makes thiskind of comparison impossible?Thanks,-Tom.
View 1 Replies
View Related
Jul 23, 2005
Thanks, folks, for taking time to help!In query analyzer and profiler, there seem to be system processes thatare generating high cpu time and disk io. Well, I guess the short ofit is that I am having a problem interpreting what I am seeing so thatI can take the next step in corrective action. There are also severaldomain controlled logins that appear to be on the same host name, atthe same time--these are laptops, not terminal servers--why would thesystem be reporting such a thing?Here is a sample of the output from sp_who2:SPIDSTATUSLOGINHOSTNAMEBLKBYDBNAMECOMMANDCPU TIMEDISKIOLASTBATCHPROGRAMNAME1BACKGROUND sa . . NULLLAZY WRITER75006/3/2005 5:142sleeping sa . . NULLLOG WRITER1025006/3/2005 5:143BACKGROUND sa . . masterSIGNAL HANDLER1606/3/2005 5:144BACKGROUND sa . . NULLLOCK MONITOR20306/3/2005 5:145BACKGROUND sa . . masterTASK MANAGER01516/3/2005 5:147sleeping sa . . NULLCHECKPOINTSLEEP243822396/3/2005 5:1410BACKGROUND sa . . masterTASK MANAGER0256/3/2005 5:1411BACKGROUND sa . . masterTASK MANAGER01636/3/2005 5:1412BACKGROUND sa . . masterTASK MANAGER0706/3/2005 5:1413BACKGROUND sa . . masterTASK MANAGER01556/3/2005 5:1451sleeping saMyServer . msdbAWAITINGCOMMAND683371386/8/2005 13:00SQLAgent - Generic Refresher52sleeping saMyServer . msdbAWAITINGCOMMAND1746266/8/2005 13:00SQLAgent - Alert Engine53sleeping DomainUser1LT1 . VPNMasterAWAITING COMMAND1687546/8/2005 12:31Crystal Reports54sleeping saMyServer . VPNMasterAWAITING COMMAND377296/8/2005 10:49MS SQLEM55RUNNABLE DomainUser2MyServer .masterSELECT 86006/8/2005 12:40SQL Profiler56RUNNABLE DomainUser2LT2 . VPNSELECT34496/8/2005 12:50Microsoft® Access57sleeping DomainUser3LT2 . VPNMasterAWAITING COMMAND110296/8/2005 12:52Microsoft® Access58sleeping DomainUser1LT1 .masterAWAITING COMMAND1172156/8/2005 9:31Crystal Reports59sleeping DomainUser4LT2 . VPNMasterAWAITING COMMAND1516/8/2005 12:50Microsoft® Access62sleeping DomainUser5LT2 . VPNMasterAWAITING COMMAND3226/8/2005 12:52Microsoft® Access63sleeping DomainUser6LT2 . VPNMasterAWAITING COMMAND50106/8/2005 12:52Microsoft® Access64sleeping DomainUser6LT2 . VPNAWAITINGCOMMAND101606/8/2005 12:52Microsoft® Access65sleeping DomainUser6LT2 . VPNMasterAWAITING COMMAND47496/8/2005 12:52Microsoft® Access66sleeping DomainUser3LT2 . VPNAWAITINGCOMMAND20306/8/2005 12:50Microsoft® Access67sleeping DomainUser6LT2 . VPNAWAITINGCOMMAND24906/8/2005 12:52Microsoft® Access68RUNNABLE saMyServer . masterSELECTINTO 62126/8/2005 12:59SQL Query AnalyzerThanks,Eric
View 2 Replies
View Related
Mar 16, 2006
The behavior seems to have changed on SP_WHO2 in SQL 2005.
In SQL 2000 I could be logged in as a regular user in my system and run SP_WHO2 and get all the users currently logged in. This is no longer working in SQL 2005. It now only returns "me".
What kind of rights do you need to have to see who is logged in?
View 4 Replies
View Related
Jun 10, 2004
In the sql server analyzer, i ran sp_who2 store procedure to find out the procesess and locks on the sql server. I noticed on the status column some entries "RUNNABLE", does this could trigger blocked to other processess if not closed by our application? or What does runnable status mean?
View 1 Replies
View Related
Apr 2, 2008
Dear All,
i've a doubt regarding the difference between sp_who and sp_who2.
i've read in one article stating that sp_who is documented and sp_who2 is un documented.what is meant by this?
and please provide me the articles related to locks(what are different types of locks)
thank you very much
Vinod
Even you learn 1%, Learn it with 100% confidence.
View 4 Replies
View Related
Apr 4, 2007
Hi!
I want to extract the same information as the Activity Monitor does with SQL in SQL Server 2000.
Have tried to use sp_who2 but I miss some important columns.
How do I do?
Thanks!
View 2 Replies
View Related
Jul 23, 2005
I've got a new server with Pentium4 xeon processors. I've used sp_who2for quite a while on my old server -- to look at CPU and IO usage. Onthe new server, the CPU times are huge -- although actual performanceis quite good. I know sp_who2 is an undocumented proc. Has anyoneexperienced this or is it just me?
View 1 Replies
View Related
Feb 29, 2008
How can i delete my old threads from Mythreads.
Any Body
View 3 Replies
View Related
Jan 24, 2001
Hi,
I'm trying to troubleshoot a SQL problem that we are having and I'm having difficulty with identifying the guilty process.
Using NT performance monitor I am monitoring all active Threads on the system and I have noticed that one particular SQLSERVR thread (then number obviously changes with each server restart) is hogging 100% CPU.
Is it possible to find out what process a particular thread number relates to ?
As far as I can tell the SQL SPID (from Enterprise manager) does not correlate to a SQL Thread.
Thanks,
Tim
View 4 Replies
View Related
Jul 21, 2004
I have an app that is critical to our business. It handles and syncronises several SQL Servers, checks integrety etc. I need to make the app so it can run a few things at once. Does anyone have any experience with this? Currently we use Delphi and ADO. I have been fiddling with DMO to get more performance - I am not sure ADO is very quick for some I tasks I need to do.
I suppose my main question *really* is does ADO/DMO multi-thread and has anyone tried it. If not how do people do it?
View 3 Replies
View Related
May 31, 2007
Here's what I know about max worker threads:
http://technet.microsoft.com/en-us/library/ms187024.aspx
http://sqlblogcasts.com/blogs/thepremiers/archive/2007/05/17/max-worker-threads-configuration-in-sql-server-2005.aspx
and this is kind-of related:
http://arstechnica.com/news.ars/post/20070529-microsoft-exec-next-version-of-windows-to-be-fundamentally-redesigned.html
How do you know how many threads are being used and how many of those are being shared? Or whether they are all even being used? Are there PerfMon stats for this?
View 9 Replies
View Related
Jul 20, 2005
Everyone,I have a data warehouse that at the moment includes around 2500hundred jobs. I am planning for a worst case scenario and would liketo increase the maximum number of sql server threads so that more jobscan execute simultaneously. Could this pose a problem and if so, atwhat number of maximum threads??Thanks!
View 1 Replies
View Related
Nov 20, 1998
that if you reduce your max worker threads it will enhance the ability to kill processes (spid###) which cannot be killed.
Recently, we have been experiencing runaway processes. If you run these processes continuously, eventually it will run without failure. We are investigating as to the reasons why this is happening. At this very moment I am sifting through the code to try and find an answer. Evidently, the process hangs and cannot be killed. We are then forced to down the server and I wish not to do this so often. If I reduce the max worker threads from it's current number now (512) to a lesser number. Will this help? and if so, by how much should this number be decreased.
Thanks a gig for your help.
View 6 Replies
View Related
Aug 30, 2005
Hi,I have an application where I need to find out about the followinginformation regarding SQL server:Processors enabledi.Threads allocatedii.PriorityCan somebody throw some light on this. How are the processors relatedto the threads running and the priority is w.r.t. what?Thanks,Verve.
View 1 Replies
View Related
Oct 10, 2007
We've invested ourselves heavily in subscription-based reporting where the SSRS service is responsible for rendering and delivering reports (to email, file shares, printers, document repositories, etc). We figured that this would be a model that would allow for easier scaling. Users submit their reports and allow SSRS to deliver them in due time. The biggest part of our reporting is now done via subscriptions.
However, adding long-running reports and short-running (but very critical) reports together to the same SSRS database has proven problematic. The long-running reports eventually make make the short reports starve for CPU time.
Does anyone know if there is a way to implement a CPU resource allocation strategy so that short running (but critical) reports will always have the ability to run? For example, it would be nice if certain user accounts or even report paths (RDL's) could have dedicated CPU resources (eg SSRS threads on which to run).
In other words, without creating additional ReportServer databases, I'd like a "pool" of threads for "severity 1" report subscriptions, a separate "pool" of threads for "severity 2" and so on. Then we'd be able to make sure that our critical subscriptions would get a chance to run. Sounds pretty straight-forward, right? Can't figure out how to go about this...
View 4 Replies
View Related
Jan 15, 2008
Sometimes ( on average once every two weeks ) I am getting the following error message:
Code BlockError: 2008-01-15 06:51:02.91
Code: 0xC0047024
Source: DF FACT SC 1 DTS.Pipeline
Description: The number of threads required for this pipeline is 98, which is more than the system limit of 64. The pipeline requires too many threads as configured. There are either too many asynchronous outputs, or EngineThreads property is set too high. Split the pipeline into multiple packages, or reduce the value of the EngineThreads property.
End Error
This is one of the final stages of the ETL, so several other packages are finishing correctly before this package is run.
When we restart the complete ETL all other packages are automaticly skipped, and when the ETL arrives to this package it runs without any problems.
So my questions are:
What does this error message mean?
Is this "64" a SSIS setting or a SQL Server setting or a server setting?
Can we increase this setting?
The number of engine threads is set to 5 ( default ), what is the relation between the engine thread setting and the system limit of 64?
Can we safely reduce the engine threads property?
What is causing the SSIS package to need 98 engine threads?
View 7 Replies
View Related
Jun 16, 2006
I am working on a project that will require the use of SQL Server 2005 Workgroup Edition. We were planning to use the version that comes with 5 CALs instead of the version licensed based on processors due to the enormous cost difference. Our customer who will be using this is a goverment agency and ms charges them ~$800 for the 5 user version, but the version licensed by processor is ~$20000. This software will be potentially installed in lots of locations so cost is a big factor.
The application I am working on is using c#. I am being told that if I use threads, each thread will require a CAL. Is that correct? I can not find anything in the licensing information that explicitly states this. It was my understanding that a CAL based on user or device could make as many connections to the db as needed.
Thanks
View 1 Replies
View Related
Mar 12, 2007
SQL Server 2005 gives you a possibility to create managed triggers. In a managed trigger I can create a new thread and process trigger event in various ways. My question is that are there any reasons why I should NOT start new threads in database triggers? The following code shows how I could create new threads. Do you see that this could cause any errors or problems in SQL Server functionality? My goal is to minimize the trigger effects in a database overall performance.
'This handles database updates of AdventureWorks Person.Contact table.
<Microsoft.SqlServer.Server.SqlTrigger(Name:="UpdateContact_Trigger", Target:="Person.Contact", Event:="FOR UPDATE")> _
Public Shared Sub UpdateContact_Trigger()
'Notify:
SqlContext.Pipe.Send("Trigger FIRED")
Dim triggContext As SqlTriggerContext = SqlContext.TriggerContext()
'Ensure that it was update:
If triggContext.TriggerAction = TriggerAction.Update Then
'Open a connection:
Using conn As New SqlConnection("context connection=true")
conn.Open()
'Fetch some information about the update:
Using sqlComm As New SqlCommand
sqlComm.Connection = conn
sqlComm.CommandText = "SELECT ContactID, FirstName, LastName from INSERTED"
Using rdr As SqlDataReader = sqlComm.ExecuteReader
If rdr.Read Then
'Process the data in a separate thread. The thread could send a message to MSMQ about the change
'or process if differently. The purpose of using separate thread is avoid performance hit
'in the application / database operation.
Dim oData As New TriggerData("update", rdr.GetInt32(0).ToString, "contact")
Dim trd As New Threading.Thread(New Threading.ThreadStart(AddressOf oData.ProcessEvent))
trd.IsBackground = True
trd.Start()
End If
End Using
End Using
End Using
End If
End Sub
View 2 Replies
View Related
Aug 2, 2007
I want to select all threads even though the count(Messages.TitleID) is zero.. Is like when u start a new thread with no messages currently posted. How can i modify the codes to do it?SELECT Thread.ThreadTitle, COUNT(Messages.TitleID) AS NoOfMessage, Thread.ThreadID FROM (Thread INNER JOIN Messages ON Thread.ThreadID = Messages.ThreadID) GROUP BY Thread.ThreadTitle, Thread.ThreadID
View 2 Replies
View Related
Jul 20, 2005
using osql to apply SPs in mutiple threadsHello,I got a weird problem when I was using osql to apply scripts for msdedatabase in multiple threads mode. Sometime 2 sps were missing duringthe whole apply process, sometime not, and seems like only those twoSPs met the problem. No error was appeared. Did anyone meet sameproblem before? Or any possible solutions?Thank you very much!
View 4 Replies
View Related
Aug 22, 2007
hi,
do i need to use specially synchronized code if i have multiple threads inserting, updating and reading rows to and from the same database?? in this case, i know that no 2 threads will try to insert or update the exact same row into the DB, however, multiple threads might try to read the same row from the database.
thanks!
View 3 Replies
View Related
Aug 9, 2006
Hi, I'm trying to stress test my web application, but when I get high load, the queries that used to take 10-20 ms starts taking 500 - 2000+ ms. Or to put it another way, when i run them single threaded i can do about 43000 a minute, when they are run in paralell it drops to about 2500 a minute.
What can i do about this ?
There are severeal queries thats affected, but here is one example:
update [user] with (ROWLOCK XLOCK) set timestamp = getdate() where userid = 1''
btw: im running sql server 2005 sp 1. The stress test is run on 3 machines total (web, sql and client) the client is simulation 400 users, cliking a page as soon as the last one is loaded, ie there will always be 400 page requests.
View 9 Replies
View Related
Apr 3, 2006
That is a SqlException I got at a...
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
Anyone an idea what THAT means? How do I cause it? How can I work around it?
View 4 Replies
View Related
Dec 21, 2004
Our company is in the retail business, thus, the window for processing cubes is very small during Christmas season (only 4 hours each day).
To speed things up, we have partitioned our cube at monthly level so, potentially, 12 threads can be run simultantsly. However, when I looked at DTS, I am not so sure whether or how it can accomplish that task. Has anyone tried this before or is aware of another third party tool can do the trick?
thx in adcance,
Carl.
View 2 Replies
View Related
Mar 3, 2008
Hi All
I have been asked by developers if there is any advantage in processing multiple clustering models simultaneously by using AMO and multiple threads as against processing one after another.
I have limited experience with Analysis Services but based on my reading I don't see this method providing any advantage.
Does anyone have any recommendations or advice? The system Enterprise Edition running on an x86 Server with 2 dual core processors and 4GB of RAM. Would the answer alter if the server running x64 version of SQL Server and Windows.
Thanks
Nadreck
View 3 Replies
View Related
May 12, 2008
Are there any draw backs to spawning additional threads inside of the script tasks for SISS packages?
View 4 Replies
View Related
Jan 24, 2006
I found a peculiar thing today while working with SQL Mobile in a multithreaded application (VS2005, application for Pocket PC 2003).
I created a class which has one SqlCeConnection object. Every time I call a function to insert/select/delete something from the local db, I open the connection, execute the query an then close the connection again.
But when I'm calling a function from the db class in thread 1 and in the meantime call a different function (from the same db class of course) in thread 2, things go wrong. Because when function 1 wants to close the connection, function 2 is still using the connection and it will crash my application with a native exception (0xC0000005: access violation).
I can see why the error is happening, but shouldn't there be a nice .NET handled exception instead of a native exception which grinds my app to a hold?
(A workaround I use now is to use multiple connection objects instead of one, but I thought I'd give this feedback anyway)
View 5 Replies
View Related
Nov 29, 2006
Recently I'm working on a multi-thread solution based on SQL-Server, now I'm facing such a problem:
Suppose I have process No.1(with multi-threads) inserting data to Table A, which has its identity column auto generated. And process No.2(also with multi-threads) retrieving data from Table A ,generate some records and insert the result into Table B. Both of these two processes are doing batch processing(batch retrieving and batch writing), and they are running parallelly.
Now since process No.2 retrieve data sequencely by the identity of Table A, it found there exists missing results. This is due to that records with bigger identities are not necessarily commited earlier than those who have smaller identities.
One direct solution is add one flag field in Table A indicating whether this record has been processed by process No.2, and each time it was processed , the field will be set. But unfortunatelly the table structure is not supposed to be modified.
So is there any other good solutions for this problem? Thanks.
View 7 Replies
View Related
