MS SQL Server :: Stop Time Duration Problem

Profiler, Duration And Start/Stop Times

Aug 14, 2007

Profiler was run against a database looking for "long running" queries. I used the Duration column to filter out the queries that I didn't want. When reviewing the output, I noticed that for some queries the StartTime was equal to the EndTime even though the Duration was set higher.
My question is, "What can account for this discrepancy and what inferences should I draw about the difference?"
Does the difference represent a resouce being locked or some other type of blocking (Duration) and once the query was allowed to run, it completed quickly?
TIA

=======================================
If Tyranny and Oppression come to this land, it will be in the guise of fighting a foreign enemy. -James Madison, fourth US president (1751-1836)

View 2 Replies View Related

Help With Calculating Duration Time

Oct 14, 2005

I have a table called Tickets which contains ticket information for a machine. Each machine can have more than one ticket number opened at the same time. The ticket number contains start date/time and end date/time of the ticket. Thereefore the table looks something like this:

Ticket_No (int)
Machine_No (int)
Description (char)
Start_Time (datetime)
End_Time (datetime)

I want to be able to calculate total duration time(in hours) that EACH MACHINE had a ticket open...but here is the tricky part. The total duration time that a machine had ticket open has to encompas any tickets that may fall in the same time period. For example:
If Machine A has a ticket open at 8:30 and the ticket is closed at 10:00. Meanwhile, Machine A had another separate ticket open at 9:30 which was closed at 10:30. In this case, the total duration time for this machine would be from 8:30 to 10:30 for a total of 2 hrs duration time.

Can anyone help me get started in tackling this problem or provide any examples?

View 4 Replies View Related

Sum Of Duration Between Time Records

Aug 6, 2013

In my example, i have a table as below:

Time|Machine
2013-08-05 10:12:00 |M1
2013-08-05 10:15:00 |M1
2013-08-05 10:20:00 |M1
2013-08-05 10:11:00 |M2
2013-08-05 10:15:00 |M2
2013-08-05 10:16:00 |M2
2013-08-05 10:17:00 |M2
2013-08-06 11:12:00 |M1
2013-08-05 11:42:00 |M1
2013-08-05 12:02:00 |M1

I want to have a report for every machine by time that will show how long each machine worked. The result is something like below:

M1|8 (minutes)
M2|6
M1|50

View 5 Replies View Related

Calculating Duration Time

Dec 13, 2007

View 2 Replies View Related

I Need Help In Calculating Time Duration

Dec 13, 2007

View 6 Replies View Related

Storing Time Duration Data

Nov 15, 2006

i've got an excel spreadsheet generated everyday which stores the duration of talk time as 0:02:09

When i use the Integration Services Package to move the data into a table. I've used datetime column. It comes up as 1899-12-30 00:00:02:09.000

What's best practice to store duration as in a SQL Server table? or should i convert it to seconds.

View 2 Replies View Related

How To Convert Time Duration Format?

Jul 2, 2007

Hello, everyone:

I have a customer time duration format like "12:15" that means 12 minutes and 15 seconds. I want to convert to be "12.25 " that means 12.25 minutes. Any suggestion please. Thanks a lot.

ZYT

View 1 Replies View Related

Calculating The Time Duration For Each Event

Jun 6, 2008

Hi all,

Thanks for setting up such a great site and forum.

Here is my problem:

I have a table like the following in SQL Server 2005:

order | taskid | main_person | temp_person | start_assign_date
1 | 3 | John | John | 2008-01-01 10:20:22
2 | 3 | John | Joe | 2008-02-05 15:20:22
3 | 3 | John | John | 2008-02-07 20:25:20
4 | 6 | Joe | Joe | 2008-01-01 10:20:22
5 | 6 | Joe | Mike | 2008-02-01 10:20:22
6 | 10 | Doug | Doug | 2008-01-01 10:20:22
7 | 7 | Russ | Russ | 2008-02-01 11:20:22
8 | 7 | Russ | Mike | 2008-02-08 12:20:22
9 | 7 | Russ | John | 2008-02-10 20:05:12

It was made to record who was in charge of a specific task at a specific time. Each task has its own main responsible person and some substitutes for that person as Temporary Persons (who did the task while main person was away). The Main Person's name is in the temp_person column when he is doing the task by himself.

I'd like to generate a report that shows:
- in a specific time period
- which persons were in charge of a specific task and
- for how long

Something like this:

From 2008-##-## to 2008-##-##
Task 3 - John - 15 days
Task 3 - Joe - 5 days
Task 6 - Joe - 18 days
Task 6 - Mike - 2 days
Task 10 - Doug - 20 days

I have some ideas to do that when there are both start and end dates for every record but I couldn't find a way to use the next assignment start date for each task, as the end date for its previous record (in that task group) to calculate the duration for that record.

I can group the tasks and users and put them in the chronological order but I can't indicate the next start date as the end date for the previous record (in specific task group) to use the date difference functions.

Any hint or comment would be appreciated.

Thanks
Sami

View 4 Replies View Related

Time Duration Calculation Ignoring Overlaps

Apr 16, 2008

Would like to know if it is possible to calculate the duration of a Datetime Start and End Dates ignoring all overlapps?
Eg:
1) StartTime 10:00:00 EndTime 11:00:00 Duration: 01:00:00
2) StartTime 10:30:00 EndTime 11:15:00 Duration: 00:45:00
Total Duration should be 01:15:00 and not 01:45:00

View 16 Replies View Related

Transact SQL :: Get Total Request Duration From Multiple Task Duration?

Jun 4, 2015

I have the following SQL query

SELECT
[Req_ID]
,[Service_Name]
,[Req_Started_Date]
,[Task_Name]
,[Task_Status]
,[Performer_Full_Name]

[code]....

Which works fine, but what I need to calculate the total duration of a request based on the duration of the tasks completed in the request based on Req_ID. I would like to use the CASE statement I have to determine the SLA_Mins for each task and add them together to get total request SLA_Mins.

Below is the create table schema and data

GO
/****** Object: Table [dbo].[MidrangeOtherSourceControl] Script Date: 06/03/2015 18:13:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MidrangeOtherSourceControl](
[Req_ID] [float] NULL,
[Service_Name] [nvarchar](255) NULL,

[code]....

View 9 Replies View Related

Data Access :: How To Reduce Record On The Basis Of Time If Minimum 2 Minutes Duration In Server

Sep 8, 2015

I have a table with following data

(Id, date ,time)

11 2015/8/1
12:20:00

11
2015/8/1 12:21:00

11 2015/8/1
18:05:20

12 2015/8/1
11:20:00

12 2015/8/1
11:21:00

12 2015/8/1
18:10:20

I need the table with following record only

(Id, date ,time)

11 2015/8/1
12:20:00

11 2015/8/1
18:05:20

12 2015/8/1
11:20:00

12 2015/8/1
18:10:20

View 7 Replies View Related

How To Stop Job If Runs For More Then Specified Time?

Nov 14, 2007

Friends, I have one job running for more then esteemeted hours. I would like to stop the job if it runs for more then 30 mins. What I need to do for that? Thanks.

View 1 Replies View Related

How To START And Stop Profiler At Specific Time

Feb 25, 2004

Hi,

We want trace ALL activity on prod
server between 3:00 AM and 3:15 AM

Profiler has stop time option ,but
no start option.

I am looking for example how it could be done

Thank you

Alex

View 5 Replies View Related

SQL Server 2012 :: Cursor Stop Query At Specific Time?

Oct 29, 2015

This store procedure will get some executable queries from the select statement, the cursor will fetch each rows to execute the query and insert the queries into table_3 to mark as 'E'. Until 17:00, this store procedure will stop execute the queries and just get the queries from select statement insert into table_3 to mark as 'C'.

I don't know why the outputs in table_3 are quiet different than I think. This store procedure comes out with two exactly same queries and one marked as C and another marked as E.

CREATE PROCEDURE procedure1
AS
DECLARE cursor_1 CURSOR FOR
SELECT
'This is a executable query'
FROM table_1
DECLARE @table_2

[code]....

View 1 Replies View Related

SQL Server Admin 2014 :: Cursor Stop Query At Specific Time

Oct 29, 2015

This store procedure will get some executable queries from the select statement, the cursor will fetch each rows to execute the query and insert the queries into table_3 to mark as 'E'. Until 17:00, this store procedure will stop execute the queries and just get the queries from select statement insert into table_3 to mark as 'C'.

I don't know why the outputs in table_3 are quiet different than I think. This store procedure comes out with two exactly same queries and one marked as C and another marked as E.

CREATE PROCEDURE procedure1
AS
DECLARE cursor_1 CURSOR FOR
SELECT
'This is a executable query'
FROM table_1
DECLARE @table_2
DECLARE @stoptime DATETIME = NULL;

[Code] ....

View 8 Replies View Related

Duration In Sql Profiler

Jan 20, 2005

If monitoring for duration with sql profiler, what does the number represent ie 2733906 is it milliseconds, thousandths, looked in BOL no clear definition

View 2 Replies View Related

SQL Profiler - Duration

Aug 1, 2002

Can someone please tell me what unit of measurement the Duration column is in when running SQL Profiler? I assume milleseconds, but am not sure.

I'm trying to filter on this and want to be sure I'm not missing anything.

Thanks!

View 1 Replies View Related

How To Run CAST On Duration

May 13, 2015

I found this nifty code on stackoverflow that works well but I'm trying to send the results to a text file and the column lengths are huge. I used CAST for the first line and it worked great but I can't seem to make it work with duration. Here's the original code:

SELECT
j.name,
h.run_status,
durationHHMMSS = STUFF(STUFF(REPLACE(STR(h.run_duration,7,0),
' ','0'),4,0,':'),7,0,':'),

[code]....

how to run a CAST on DURATION?

View 5 Replies View Related

Calculating Duration

Jan 18, 2007

I am trying to get a query that will allow me to report the time taken to complete a certain training module.

The database itself does not have a duration field so I am tring to get the duration by using MIN and MAX. I can get the timing for when the module was opened and the time for the last mouse click on it, from this I need to be able to calculate the time taken to complete.

Query I am using to get the basic info comes from 3 tables so I have only attached the relevent output. Query used is as follow:

SELECT *
FROM PPS_SCOS, PPS_TRANSCRIPTS, PPS_TRANSCRIPT_DETAILS, PPS_PRINCIPALS
WHERE PPS_SCOS.SCO_ID = PPS_TRANSCRIPTS.SCO_ID
AND PPS_TRANSCRIPTS.TRANSCRIPT_ID = PPS_TRANSCRIPT_DETAILS.TRANSCRIPT_ID
AND PPS_TRANSCRIPTS.PRINCIPAL_ID = PPS_PRINCIPALS.PRINCIPAL_ID
AND PPS_SCOS.NAME LIKE 'MTM-106 The Dangers of Smoking'
AND PPS_PRINCIPALS.NAME LIKE 'Nigel Cordiner'
AND PPS_TRANSCRIPTS.TICKET NOT LIKE 'l-%'
ORDER BY PPS_TRANSCRIPT_DETAILS.DATE_CREATED

Output:

pps_scospps_scospps_transcript_detailspps_principalspps_principals
SCO_IDNAME DATE_CREATED PRINCIPAL_ID NAME
136850MTM-106 The Dangers of Smoking08:17:2516287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:17:2516287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:17:4016287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:18:2516287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:18:5716287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:19:1416287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:19:4716287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:20:2116287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:20:4416287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:21:2616287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:22:1316287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:24:5516287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:25:1216287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:25:2916287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:26:4916287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:27:0216287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:27:2916287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:27:4316287Nigel Cordiner

Have added the column heading and the tables the output comes from.

Relatively new to SQL so any help would be greatly received.

View 4 Replies View Related

Need Help With Job-duration Query

Jan 24, 2008

I have a query that displays number of hours duration in a jobs by months matrix. Here is a simplified version of my tables.

CREATE TABLE jobhead (
jobnum varchar (14) NULL,
jobcompletiondate datetime NULL
)

CREATE TABLE labordtl (
jobnum varchar (14) NULL ,
clockindate datetime NULL ,
clockintime decimal(6, 2) NULL ,
)
Here's an abbreviated query.

SELECT h.JobNum,
(CASE WHEN MONTH(h.JobCompletionDate) = 1 THEN datediff(day,MIN(l.ClockInDate),MAX(l.clockInDate)) ELSE 0 END) AS JAN,
(CASE WHEN MONTH(h.JobCompletionDate) = 2 THEN datediff(day,MIN(l.ClockInDate),MAX(l.clockInDate)) ELSE 0 END) AS FEB,
...
FROM JobHead h
INNER JOIN LaborDtl l ON h.JobNum=l.JobNum
WHERE JobCompletionDate>='20070101' AND JobCompletionDate <'20080101'
AND l.ClockInTime<>0
GROUP BYh.JobNum
,h.JobCompletionDate

The query shows, for each job, the month in which the job completed, and the number of hours it took to complete. I��m calculating the number of days�� duration by doing a datediff between the oldest and newest clockindates. I need to ignore adjustment transactions in the labordtl table �� these rows are easily identified as they have clockintime values of 0. So far, so good. Now here��s my problem.

There are some jobs which have only one ��real�? labor transaction �� this could happen if the job only took one day to complete. Other labor transactions may exist for that job, but let's say they are adjustments which we can ignore -- the date they were entered should not extend the duration of the job. In this situation, my datediff between the oldest valid transaction and the newest, returns 0. I don��t have to count hours between clockintime and clockouttime. The rule is simply that if there is only one "real" labor transaction, I need to count this as a 1 day job.

I thought a nested CASE statement or expression might be the way to go but I didn't make any real progress.

Any ideas to solve this problem would be appreciated.

View 5 Replies View Related

Querying For Job Duration

Apr 13, 2007

Hi!

Does anyone know how to query for the job duration of all jobs in a server?

Thanks!

View 6 Replies View Related

How Do I Get Overall Package Duration?

May 20, 2006

I'm developing a web app that displays the running packages and the total elapsed time. I'm calling GetRunningPackages() method and using the ExecutionDuration property of the returned package. The duration seems to be only for the currently executing container and not the entire package. Is there a way to get the duration of the entire package? Thanks.

View 2 Replies View Related

How To Store Duration In Database?

Nov 27, 2007

Hi all, I want to find working duration between two datetimes in c#.i'm using following code... DateTime starttime = Convert.ToDateTime(Session["StartTime"]); DateTime endtime = DateTime.Now; TimeSpan duration = endtime - starttime; DateTime period = new DateTime(duration.Ticks); i want to store this duration in database through stored procedure, i've give datetime datatype to duration but it is giving error in conversion of TimeSpan to DateTime..Please help... Thanks

View 1 Replies View Related

Run Duration On SQL Job Doubled After Conversion From 6.5 To 7.0

Nov 29, 1999

SQL 6.5 - run duration 6-7 hours
SQL 7.0 - run duration 12-13 hours
175+ columns with total record size=570
4.2M records with tablesize 2.5G

It's a simple 'select into...' with some embedded logic from a work table with all char fields into the actual table converting char fields into various data types (int, datetime, real, etc.) Here is a sample of the code:

SELECT
LoanNum=CASE
WHEN ISNUMERIC(ACCT#)=1 THEN CONVERT(int,ACCT#)
ELSE NULL
END,
PaidToDt=CASE
WHEN PAIDDT = '0001-01-01' THEN NULL
WHEN ISDATE(PAIDDT)=1 AND SUBSTRING(PAIDDT,1,2) = '19' THEN CONVERT(smalldatetime,PAIDDT)
WHEN ISDATE(PAIDDT)=1 AND SUBSTRING(PAIDDT,1,2) = '20' AND SUBSTRING(PAIDDT,3,2) < '79' THEN CONVERT(smalldatetime,PAIDDT)
ELSE NULL
END,
.
.
.
INTO db.owner.tablename
FROM db.owner.wrktablename (NOLOCK)

View 1 Replies View Related

Duration Too Long (opinions)?

Sep 15, 2004

Hearing complaints from users about speed on db server (I have almost no control on design) it just has to work. Ran profiler looking for all sql statements over 4000 millsec and in one hour returned over 715 tsql statements. Over 300 of these were over 10000 milliseconds. THis is on an 8 way Dell with 8 gig of RAM. Looking for opinions, how bad does this look compared to other servers you are taking care of? Cache hit ratio is at 99 % and system queue length still under 1, but this does not look good.

View 2 Replies View Related

How To Disable A Trigger In Duration?

Feb 21, 2005

Hello, everyone:

There is a trigger to monitor the modification on a table, and it turn on. For a special duration, I need to turn off this trigger to modify the table. And then turn on the trigger again.

Any help will be appreciated.

Thanks

ZYT

View 2 Replies View Related

DBCC SHRINKDATABASE Duration

Nov 8, 2006

Is there anyway to tell how long this will run for -- or how far it has got? I have a large database that has just had most of the data removed. The command has been running for 8 hours and I have just stopped it to let something else run quickly. Any way of telling how much longer it will take?

View 7 Replies View Related

SQL Agent Job Failures And Duration

Nov 6, 2003

/*
This SP has 2 functions.
a) if @method='duration' gives the average run duration in minutes for successful jobs
b) if @method='failures' displays failures/cancels/still executing jobs
It defaults to today's date. Specify @xdate for a different date
-- Louis Nguyen
*/

CREATE PROCEDURE UtilityJobsHistory
(
@method varchar(100)='duration'
,@xdate datetime=null
)
AS
set nocount on
set transaction isolation level read uncommitted

if @method='duration' begin

select @xdate=isnull(@xdate,getdate())

/*run_duration is in HHMMSS format; drop SS*/
/*run_staus: 1 complete 2 retry*/
/*step_id: 0 is final job outcome*/
/*run_date: yyyymmdd format*/

/*today's performance*/
select a.name,minutes=avg((b.run_duration / 100)/100*60 + (b.run_duration / 100)%100)
into #today
from msdb..sysjobs as a
join msdb..sysjobhistory as b
on a.job_id=b.job_id
where run_status in ('1','2') and step_id=0 and run_date =convert(varchar,@xdate,112)
group by a.name

/*7 day average performance*/
/*populate #D with dates in yyyymmdd format*/
create table #D (run_date varchar(50))
declare @idate datetime set @idate=@xdate
while @idate>dateadd(day,-7,@xdate) begin
insert into #D
select run_date=convert(varchar,@idate,112)
select @idate=dateadd(day,-1,@idate)
end

/*Avg7Days*/
select a.name,minutes=avg((b.run_duration / 100)/100*60 + (b.run_duration / 100)%100)
into #avg7Days
from msdb..sysjobs as a
join msdb..sysjobhistory as b
on a.job_id=b.job_id
join #D as c
on b.run_date = c.run_date
where run_status in ('1','2') and step_id=0
group by a.name

/*output*/
select name=cast(a.name as varchar(35)),OneDayAvg=a.minutes,SevenDayAvg=b.minutes
from #today as a
join #avg7days as b
on a.name=b.name
order by a.name

return end

if @method='failures' begin

select @xdate=isnull(@xdate,getdate())

select status=case run_status when 0 then 'FAILED' when 3 then 'CANCELED' when 4 then 'EXECUTING' end
,name=cast(a.name as varchar(35)),step_name
,time=replace(convert(varchar,@xdate,107),' ','')+' '+right('0000'+cast(b.run_time/100 as varchar),4)
,b.message
from msdb..sysjobs as a
join msdb..sysjobhistory as b
on a.job_id=b.job_id
where run_status in ('0','3','4') and run_date=convert(varchar,@xdate,112)
order by run_status,a.name

return end

View 1 Replies View Related

SQL Profiler Duration And CPU Baseline

Jan 9, 2007

Sarfaraz writes "I have captured SQL Profiler data. I was reviewing top running CPU intensive SQL statements. The Duration (in seconds) 1.39, 1.09, 0.16 and CPU (in seconds) 0.97, 0.95, 0.16 respectively for some SQL statements. How do I know what is the normal baseline for duration and CPU in order to determine the CPU intensive SQL statements.

Secondly same question for long running procedure duration 0.14, 0.11. What is the normal baseline here. Is this normal or too long.

Thanks,
Sarfaraz"

View 5 Replies View Related

Sp_primary_keys_rowset Loong Duration

Mar 19, 2008

Hi!

I'm using RDA (Remote Data Access) to pull 20 tables to my Pocket PC. It took quite a long time so I ran a trace to see what happened. Everything looks fine except for when it runs:

exec [mydb]..sp_primary_keys_rowset N'Person',NULL
The duration is: 18446744073!!!

A couple of more tables has this enomous duration others have about 5000 which seems more normal.

Any clues?

/Magnus

Jesus saves. But Gretzky slaps in the rebound.

View 3 Replies View Related

Duration Of The Ssis Package

Sep 8, 2006

i want to get the execution duration of the ssis package and insert into one table,so i used a variable--"duration" and specified it's expression is getdate(). I think that only i can get the start value of the varible and the finish value,then i can know the duration.but whether i can get these two values? or better way i should try?

thanks

View 3 Replies View Related

Profiler Duration Column Value.

Jul 12, 2007

Hi Experts,

We have a VLDB ( few table with above 200 million records ). This database is used for performance testing by simulating for 150 users and executing all necessary functional flows.

When I examined the profiler results , I could see some very high values as shown below in the duration column for many events.

1521729
3462142
1624325
3211255
1248276
3903998

Does it mean that that SP or the T-Sql statement is taking this much time in milliseconds to execute and give the output ?

Any help would be greatly appreciated.

Thanks & Regards,

DBLearner

View 1 Replies View Related