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)
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,
Our Report Server DB almost doubled in size over the period of a week and is causing backup issues and using too much disk space.
We have over a hundred SQL 2005 DBs running in a portal on the same server as the Report Server all connecting to SSRS and many run their own Report Builder models and custom reports.
The table dbo.ServerParametersInstance is currently 3.6gb. I'm struggling to find any help in how to manage this in terms of reducing its size or what it is even used for.
If monitoring for duration with sql profiler, what does the number represent ie 2733906 is it milliseconds, thousandths, looked in BOL no clear definition
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:
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.
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.
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.
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
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.
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.
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:
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?
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?
/* 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
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.
Hi guys, I am having difficulty calculating the time duration between receiving process to shipping process. I have a table that consists of: Order#, Processes, Time_In, Time_Out. Order# can be 1, 2, 3, 4, 5. While at the same time Order# 1 can go through more than one process, i.e.: Receiving, VisualTest, MechanicalTest, ..., Shipping. Every Order# does not necessarily goes through all processes, but surely they will go through receiving process and shipping process. For each process we will have recorded time when the order# comes in and when it finishes with each process. I need to calculate the length of time from Time_In from Receiving to Time_Out in Shipping.
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.
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?
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 ?
Hi guys, I am having difficulty calculating the time duration between receiving process to shipping process. I have a table that consists of: Order#, Processes, Time_In, Time_Out. Order# can be 1, 2, 3, 4, 5. While at the same time Order# 1 can go through more than one process, i.e.: Receiving, VisualTest, MechanicalTest, ..., Shipping. Every Order# does not necessarily goes through all processes, but surely they will go through receiving process and shipping process. For each process we will have recorded time when the order# comes in and when it finishes with each process. I need to calculate the length of time from Time_In from Receiving to Time_Out in Shipping.
Please forgive the stupidity of this question - it should be something very easy and I'm just not seeing it.
I am trying to set the duration of the ExecutionLog table. Currently it is ~ 1 week, and I would like to up the lifetime of the records - say, to 1 year. I would think this is as easy as changing a .config file value, but am unable to find something for this. I found the entry for the log files, but nothing for the execution log.
In the past I have tinkered with the Execution History DTS package, which would need to be scheduled to run and pull over only the newest records - is there anything else besides this? (just let them sit in the table?)
I have a procedure in a history database that does insert into 3 tables inside a transaction. users complaint that the proc sometimes takes too long during heavy usage. I did some traces to see what is taking up the time, I found that the rpc duration was averaging > 500 ms (should only take 50ms). I checked to see if one of that statements were taking too much time, but only see the commit transaction statement taking around 500 ms). I check the avg disk queue to be around 30. ( this is on a single local disk) .
So is this definitely a disk issue, or is there something else I need to check
Hi, I want to store a time duration such as 1:30 (mm:ss), 1:00, or 1:23 in a SQL 2005 database. What is the best data field type for this data? DateTime or TimeStamp? Thanks
Does anyone know how to tell how long it took for an auto update statistics to run? I looked under DBCC Show_Statistics and it shows the time the stats were last updated, but not how long it took to update them. Thanks.
I have a critical job. When i take the view job history of that job here it has a colum named as 'duration'. It gives the exact duration of each step it has taken in its last run. But i have the same job in test environment. But when i take take the history here i dont see any duration column. how can i add that column.
Hi, I am interested to know how I can capture the amount of time bcp takes in a table.... the whole idea is to keep track of all bcp activities and create atable to keep bcp log time to the following table: create table bcp_log(table_name char(20),row_count int,time_in datetime)
what I thought is to create a trigger on each table I want to log its bcp. then declare a variable and assign the duration of bcp .. But I did not know where to pull that value..... Dose anyone knows how to capture the duration of bcp for a table....
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.
On SQL Server 2000. IBM X series, dual 2GHz Xeon processors, 3GB RAM, RAID 5 DB array and separate Mirrored drive for OS, Windows 2000 Server.
Note: I should have moved the log files to the mirrored volume - I only just noticed it wasn't like that ... oops.:o It only has the OS and backup files (different logical volumes).
Problem: I have a Bill of Material system. It has a batch (Stored Procedure) that explodes Order Line Items. Each line has about 40 components.
The SP has 5 major steps to explode the Assembly, figure out colors/sizes to get the actual part numbers, assign quantities, update flags on the line item table (that has 3,500,000 rows but is not a clustered index - fill factor 85% on the unique autonumber index I use for the updates). The SP is jam packed with User Function calls, sub-Stored Procedures, and (yes) some cursors (on small declared temp tables created for each Order Line processed).
The execution plan for the whole batch of 100 is humongous - like 1000 pages, however nearly all steps are about 16 - 40 milliseconds to execute.
It normally takes 13 seconds to process 100 line items (3900 assembly rows created). However; about every 20th run, it takes 2 or 3 minutes ON THE VERY SAME LINE ITEMS. No programming changes! Run back-to-back, or with a minute or two delay. Then running it again, back to 13 seconds. Sometimes it takes 25 or so seconds, but that oddball 3 minutes is a killer.
I put some timing INSERTs into a temporary log file to localize the problem.
So; here's the REALLY weird thing. The part that experiences the intermittent 2 minute delay is a VERY SIMPLE call to a SP that INSERTs to the tbBATCHs table and gets the identity and passes it back with an Output variable. The tbBATCHs table only has about 8 columns. Nobody else uses it so it's not locking. It has an "If Exists" selection following the Insert to see if another batch is in progress not yet flagged as complete, but's not the issue because it's never true (hard coded values for testing forces this).
I have a Standby Server (duplicate config of main server) so I ran it there. Same issue.
If I loop 100 times on the tbBATCHs INSERT SP, it is pretty quick (like 2 seconds for all 100).
It's never on the first run, so I think it may be some sort of Log File catch-up.
Also; I DO NOT have Transaction Logging in place yet.