Good Statistic For Blocking

Jan 14, 2004

We are having an occasional problem occur where a
process will not stop blocking.

We are trying to trace the problem, but in the interim,
I would like to set up an alert that notifies me when a
process has been blocking for too long.

Are any of the lock wait times good statistics to use
for such an alert? If not, is there anything else I could
look at from the alert level?

If I had to, I could periodically create a table of
sysprocess spids that are at the top of blocking chains,
then test for a spid that lingers. I'm hoping I can
avoid this and use the built-in monitoring instead, though.

Thanks!
Geoff

View 6 Replies


ADVERTISEMENT

F-Statistic

Oct 30, 2007

Has anyone implemented or knows an easy refernce to implement F-statistic for single variable linear regression? I was told it's not implemented in SSAS, so it seems like implementing it manually is the only way to go.

Thanks in advance,

View 1 Replies View Related

Statistic AddOn

Sep 10, 2003

hi,

i need to know if there is usefull Statistic AddOn available for the SQL-Server 2000

Anyhow exists somewhere on the www a site where all AddOn are collected?

thanx, birni

View 3 Replies View Related

Statistic For Heavilly Used SPs

Apr 30, 2007

Hi,
Is there possibility to get somehow statistic for mostly used SPs in the SQL Server 2000 Database? Any field in sysobjects for number of executions for certian SP?

Thanks,
sqlzagi

View 2 Replies View Related

Survey Statistic Query

Feb 5, 2007

Is there a possibility to express the text column with SELECT SQL command, to get AS column which will represent all values in the field?

I mean - to convert:


IDresult__IDquestion_answers
....
24______72_________178,180,191
25______77_________221,224
26______79_________291,298,301,302
27______84_________522,523,525
....


into:


IDresult__IDquestion_numberedanswers
....
24______72_________178
24______72_________180
24______72_________191
25______77_________221
25______77_________224
26______79_________291
26______79_________298
26______79_________301
26______79_________302
27______84_________522
27______84_________523
27______84_________525
....


Can SQL do that?

View 14 Replies View Related

SQL 2012 :: How To Determine When A Statistic Was Last Used By The Optimizer

Aug 25, 2015

Is there a DMV or similar in SQL 2012, or SQL 2008, that shows when a statistic was last used by the optimizer? I would like to cleanup some of the auto-generated stats, assuming it's possible to do so. In particular I'm looking to drop those statistics that were created by one-off queries, data loads, etc, and are now doing nothing but adding to the execution time of Update Statistics jobs.

View 6 Replies View Related

Generating A Daily Statistic Report SQL

Sep 10, 2007



Hi,

I Have a table below.
Query
PKEY id int
name varchar(128)
date_add DateTime

What is the SQL statement to get the number of query on each day?

the output should be date and quantity. There should still be an output even if there is no query on that day.

The only way I can think of is by a table-value UDF. (rough design)

function(startdate, enddate)
{
for each day from start to end
insert into result select count(*) from Query where date_add = currentDate
return
}


Is there a more efficient way to do this?

Thanks,
Max

View 1 Replies View Related

Blocking

Aug 15, 2000

One sql transaction is in rollback state and optimization job is trying to restructure index on the same table that this transaction is using. As a result,
transaction is in 'blocking' state and optimization job is in 'blocked' state. 'Kill process' doesn't do anything. How can this situation be resolved?

View 1 Replies View Related

Blocking

Nov 26, 2001

Is it possible that blocking can occur between a select and update statements running at the same time on the same object??thanks for the reply!
Sheila.

View 1 Replies View Related

Self Blocking SP4

Nov 1, 2007

Anyone out there having "self blocking" issues since upgrading to SP4 on SQL 2000? Enabing trace flag 9059 has not helped, is this a parallelism issue ?

thanks,
Paul

View 7 Replies View Related

Blocking When Doing Bcp

Jan 6, 2004

Any ideas?

I execute a sproc from QA.
It interogates a folder:


Select @Command_String = 'Dir ' + @FilePath + '*.txt'
Insert Into XLAT_Folder exec master..xp_cmdshell @Command_String
[/code[


Which I then Parse and get the details...

Set up a CURSOR (Booo hiss....)

[code]

DECLARE XLAT_Folder CURSOR FOR
SELECT Create_Time
, File_Size
, [File_Name]
FROM XLAT_Folder_Parsed
ORDER BY [File_Name]





WHILE FETCH STATUS = 0
BEGIN
BEGIN TRAN


Then, based on the methodology that the file name must match the table and format file (I check to make sure everything is out there)

I then bcp the data in using my connection pooling id (I'm logged on as sa in qa)



SET @cmd = 'bcp ' + @db_name + '..' + SUBSTRING(@File_Name,1,CHARINDEX('.',@File_Name)-1) + ' in '
+ @FilePathAndName
+ ' -f' + 'd:DataTax' + SUBSTRING(@File_Name,1,CHARINDEX('.',@File_Name)-1) + '.fmt'
+ ' -S' + @@servername + ' -U -P'

SET @Command_string = 'EXEC master..xp_cmdshell ''' + @cmd + '''' + ', NO_OUTPUT'

INSERT INTO #XLAT_BCP_Results(Col1) Exec(@Command_String)




MOVE DATA FILE TO ARCHIVE
COMMIT TRAN
ANOTHER FETCH



A spid is launched to do the bcp...I have 4 files...on the last load The connection Pooling lauched spid gets blocked by the sa spid that launched the sproc...it doesn't happen all the time, just most of the time....

I've put a trace on in Profiler, but I don't see anything...I've picked the event class of deadlocks...but I never see it...

When I do sp_who, it shows the blocking...

I'm so confused....

View 8 Replies View Related

Blocking

Aug 31, 2006

Hi just read an article on the internet that states

"A quick way to resolve a blocking problem is to disconnect the connection blocking the rest of the users. All you have to do is ask your database administrator to execute the KILL statement. Obviously, this is not a permanent solution, and won't really help anything except take care of an immediate need."

Now when i run sp_who ... i see that the spid that is blocking is the same as the spid itself....

Now the above article states that killing the one that is blocking the rest of the other spids is ok ...

But what if its the same SPID that is blocking ...what does it signify & whats the solution

Thanks

View 2 Replies View Related

Blocking

Nov 13, 2007

Hi Gurus,

I am running a simple SELECT statement on my TempDB

SELECT * FROM TempDB..SysObjects

When I run this , it's getting blocked by some other Process which is not at all relevant to the above Qry. First it gets blocked by one Stored Proc say Proc1 ( The Proc1 is just a SELECT statement on a table which just has four rows ). So I just kill this PROC1 and again its gets blocked by some other stored Proc PROC2 which again is a simple stored Proc and it runs in not time.

Any Suggestions as to where to start.

Thx in Advance

Venu

View 13 Replies View Related

Blocking

Jul 23, 2005

hi,I am using SQL SERVER 2000.Problem that i am facing is when ever I check locks in EnterpriseManager I findfollowing blocking - :1) sp_cursoropen;12) sp_cursorclose;13) sp_cursorfetch;1We dont have any control over application(source code).Whateverwe have to do, has to done from database side.can anyone guide me to solve this problem.Thanks

View 4 Replies View Related

Sql Blocking Problem

Dec 13, 2007

I have one batch jobs running 4 time a day and web application.
We have hosted the site long time already but this month we are facing the store procedure blocked problem.
The problem is that when user submit the form when during the execution of batch job, two storeprocedure are blocked.
 One store procedure that is using from batch job read the table "A" and another store procedure that is using from web app update the table "A".
Then deadlock is occure. but i don't understand it is hosted long time already. Please let me know if you know how to solve this issue.
 Thanks,
Aung

View 6 Replies View Related

Blocking URGENT!!

Jan 14, 2002

Hi ALL!
Can a sleeping process block another process sleeping/runnable?It's happening in our SQL server.Can someone expalin??
thanks.
Di.

View 1 Replies View Related

SQL Server 7 Blocking

Apr 24, 2002

I have a powerbuilder application that is connecting to SQL Server. We get very frequent DeadLock situations. Is there a way that I can check to see as what SQL is causing the Lock?

Thanks in advance for any suggestions...
-Raj

View 1 Replies View Related

Blocking Processes

May 29, 2001

I have upgraded a MS SQL database from 6.5 to 7.0. The database functioned fine in 6.5, now I have a table that is locking due to a blocking process. If I kill the process all is fine, but am trying to determine what is causing the process to hang. Has anyone experience any similar situations.

View 2 Replies View Related

Blocking (Urgent)

Jul 10, 2001

Hello,

In the morning at 9:32 one of the spid was blocked and got time out msg for select statement. the statement was sp_execute wht is sp_execute?
can anyone guide me?

Thanks

View 2 Replies View Related

Blocking (Urgent)

Jul 13, 2001

Hello,

I've a problems with blocking
simple select * from table1
is blocked
how to know which statement is blocking
select * from table1 is spid 30 blocked by spid 26
and in current activity spid 26 has no select or any statement
how could i know the spid 26 statement ?
And can just select statement be blocked?

wht should we do to monitor locks?

Thanks

Binoy

View 2 Replies View Related

Spid - Blocking

Nov 15, 2000

I know that spid 22 is blocking 2 other users. How do I tell who that user is? Is this just matching the same spid# in the process info in EM and if it doesn't give me an NT name it's probably because they're coming through the application and I can't figure it out?

Thanks

View 2 Replies View Related

Blocking Problem.

Feb 4, 2001

Hi there,

I've a encountering a problem with "Blocking" and would highly appreciatd, if anyone out there please advice how to get to the bottom of this. the problem in as follows:

Every now and then, my SQL Server Freezes and when i look at the process info, i see some user connections in blocked mode and some in blocking. i dont know, where to start looking for. my first question would be, what should I do here? Should i kill the blocked user or blocking user? Also, Whats the difference between this two modes (blocked and blocking) and why does this happening? can i trace the user id which is causing this problem?

Experts please advice, what to do in the near future to aviod this.

I search at microsoft support site and came up nothing good about this problem.

Appreciated, If anyone out there advice on this.

Many thanks in advance.

Sindi

View 1 Replies View Related

Blocking Problem

Apr 19, 2000

When an SA attempts to change permission on any object they are shown as blocked as expected. However as new users access the object the SA's process is continuely blocked until the object is free. Can anyone explain this??

Thanks
David

View 3 Replies View Related

Blocked/Blocking

Dec 7, 1999

Hello...

Is it normal in SQL Server 6.5 the user who only running the query blocking the other user who try to update/add the records?

note: The query is a complex SQL.

Many Thanks!

View 2 Replies View Related

Insert Blocking...

Jun 2, 2005

found many blocking on SQL2000 enterprise by insert statement, the same insert statement failed in the sql/w with violation on the primary key.

however, the appl insert also got the same error from log, but still in 'blocking' instead of to be 'killed' by the pk violation.

what would be the options to deal with this issue?

thanks
-D

View 5 Replies View Related

Blocking Issue

Nov 17, 2005

Please advice.

I have a sql2K SP3 active/passive failover cluster running on W2K SP4. I epxerienced a blocking scenario. The blocking process was killed only to find another process cause blocking. Killing the blocking process didn't resolve the blocking issue.
Eventually I had to fail over the SQL Server instance to another node and that brought resove the blocking issue.
What direction should I look for investigation and solution.
URGENT PLEASE.

Help appreciated

View 3 Replies View Related

Blocking Problem

Aug 24, 2001

I have a vendor supplied database which seems to have a simple blocking problem. The blocking will occur when a user tries to delete from a table where another user might be inserting or selecting from. The table has only 3 columns and the delete statement looks fine. (they are not deleting the same record) This is on SQL 2000 sp1. has anyone seen this or have any ideas. The db is on a fast server (quad 2 gig of ram). I did have the thought on a problem I ran into with an older db on SQL 7 where the write-ahead RAID controller was actually causing a simlar problem. Any help appreciated

View 2 Replies View Related

Blocking Sa User

Dec 8, 2006

Hi All,
I have a hypothetical scenario. I have few developers who all know the sa password. I want to block one developer from accessing the SQL server eventhough he knows the sa password.

Do we have any mechanism in SQL Server 2000 to block client based on the IP address of the client?

SQl Server is installed on Windows 2000 server. It uses SQL Server & Windows authentication.


Regards,
Anand

View 8 Replies View Related

Odd Blocking Issue

Sep 4, 2007

I don't understand why a CREATE PROC from 3.5 hours ago is the head blocker. The SP wasn't changed since in months so this must be a compile/recompile? It's not a COMPILE lock though but a PAGE lock. It's certainly not the initial creation. What else can be done to debug this? It's happening and usually resolving itself in 4-6 hours or if I kill the head blocker myself. It doesn't happen every day but almost every day.

I've retrieved this info about the blocking from DMVStats and found similar info using my regular blocking info script also pulling info from DMV's.

I'm running SQL 2005 SP2 Enterprise.

statement started 6:35AM
waiting statement (PAGE lock):

insert into grades (blah,blah)
select blah blah from homework join blah blah

statement started at 3:01AM
blocking statement: =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[ResultsHomeworkDelete]
@homeworkResultId int,
@NumDeleted int output
AS

delete homeworksessions where homeworkresult_id = @homeworkresultId

set @NumDeleted=@@ROWCOUNT

delete homeworkresults where homeworkresult_id=@homeworkresultId

set @NumDeleted=@NumDeleted + @@ROWCOUNT

View 3 Replies View Related

Blocking And Transactions OK?

Sep 23, 2005

I'm wondering if I'm doing this right. Wondering about the transactionsand error handling. (Do I even need to put BEGIN TRANSACTION ANDCOMMIT TRANSACTION in there?)I think that this sproc is causing occasional blocking:Alter Procedure sprocINSERTSTUFF@Col1Data int = Null,@Col2Data nvarchar(255) = Null,@Col3Data ntext = Null,@UniqueID int OUTPUTASset nocount onset xact_abort onDECLARE @err intDECLARE @ServerDate DateTimeSELECT @ServerDate = GETUTCDATE()BEGIN TRANSACTIONINSERT INTOtblStuff (Col1, Col2, Col3, DateCreated, etc.)VALUES(@Col1Data, @Col2Data, @Col3Data, @ServerDate, etc.)SELECT @err = @@error IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN@err ENDSELECT @UniqueID = SCOPE_IDENTITY()COMMIT TRANSACTIONBEGIN TRANSACTIONINSERT INTOtblMoreStuff (UniqueID, DateCreated, Col1, Col2, Col3)Values(@UniqueID, @ServerDate, @Col1Data, @Col2Data, 'Text Label: ' +isnull(Cast(@Col3Data AS nvarchar(4000)),'<none>')SELECT @err = @@error IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN@err ENDCOMMIT TRANSACTIONSELECT @err = @@error IF @err <> 0 RETURN @err

View 9 Replies View Related

MOM Blocking Analysis

Oct 19, 2005

MOM contains its own Block Analysis script. It is a script written onVB, that looks for the monitored servers, creates for each oneMomCreateObject("SQLDMO.SQLServer"), afterwards connects to eachdatabase on each server and executes SELECT GETDATE() query. If MOMdoesn't receive answer for 6 minutes, it sends alert. Several timessince I started to monitor my servers I received the next alert:The program "SQLDMO_789" has been blocked for 6 minutes on databaseBurstingDataWarehouse in the SQL instance MSSQLSERVER. The definedacceptable blocking threshold is 1 minute(s). "SQLDMO_789" is runningon SPID 134 as login NT AUTHORITYSYSTEM and is blocked by SPID 133.The resource id is KEY: 10:2:1 (a2007950f190)SQLDMO_789 - is the MOM itself (number varies from time to time). 10 -is BurstingDataWarehouse database. As far as I can judge, MOM connectsto the server succesfully (otherwise, how can it know SPID?) The serveritself worked fine at that time - nothing unusual, all the jobsfinished succesfully including the heavy ones. What can block SELECTGETDATE() query for 6 minutes???

View 1 Replies View Related

Cascading Blocking ?

Jul 20, 2005

We have a situation that occurs every so often with blocking ofvarious databases on one server (Win200 SQL7). It appears to happen atrandom, so I'm assuming it originates from something a user does andnot a regularily run process.We've examined the data available to us and used the very helpfulblocking code on http://www.algonet.se/~sommar/sqlutil/aba_lockinfo.html(Thanks Erland).Were getting closer to finding the problem, but need some advice onwhat to look for.This is a bit of guesswork, but we suspect that we get into asituation where blocking takes places, and this then cascades to otherprocesses which then block others in turn. The original culprit thenfinishes, but the blocks continue as the newer processes are holdingsomething else up. A bit like dominoes. It seems to take a while tofree this up.The problem we have is determining the start of this process. Once weare made aware of blocking issues, we can find out who is doing what,but almost always get a different answer/user and think we're gettingto it a little late.Ideally, I want to log the blocking somewhere so I can examine thefiles when this occurs and can therefore establish a pattern etc...Any ideas or suggestions would be welcome.

View 1 Replies View Related

Blocking Problem

Jul 20, 2005

SQL2K, sp3Active Server Pages front-endI am having a blocking problem with a spid executing a storedprocedure that does no updates. When I look at the locks the blockingspid has, some of them have lock mode "IS". Looking in BOL, I see "S"is shared, but I don't see "IS". Can anyone tell me what lock mode"IS" is?And how could a stored procedure that does no updates be guilty ofblocking?There are no cursors in it.Thanks!

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved