How To Calculate Transactions Per Minute

Jan 17, 2002

How do i calculate the Transactions Per Minute (TPM). Do i need to use the Performance Monitor or Profiler. Let me know How do i calcualte.

I would like to have 24,000 inserts in One minute Per data migration. Is 24,000 will be Transaction Per Minute.

Thanks in Advance.

SS

View 1 Replies


ADVERTISEMENT

Changing Connection Transactions To Database Transactions

May 22, 2005

Hi there,
I have decided to move all my transaction handling from asp.net to stored procedures in a SQL Server 2000 database. I know the database is capable of rolling back the transactions just like myTransaction.Rollback() in asp.net. But what about exceptions? In asp.net, I am used to doing the following:
<code>Try   'execute commands   myTransaction.Commit()Catch ex As Exception   Response.Write(ex.Message)   myTransaction.Rollback()End Try</code>Will the database inform me of any exceptions (and their messages)? Do I need to put anything explicit in my stored procedure other than rollback transaction?
Any help is greatly appreciated

View 3 Replies View Related

Caching &&< 1 Minute

Mar 7, 2007

Is it possible expire a report cache after less than one minute? I'm looking for a way to only have a report hit the database once every 10 seconds, no matter how many people are hitting it. Thanks.

View 1 Replies View Related

Getting Every Other 5-minute Average Value...

Feb 1, 2008

Hi,

Here is a part of result set.
It is of every minute value.

How can I get every other 5-minute average value?

id datetime value
------------------- ----------------------------- --------

0xC00302FD 2008-01-31 18:36:00 0.104
0xC00302FD 2008-01-31 18:37:00 0.104
0xC00302FD 2008-01-31 18:38:00 0.104
0xC00302FD 2008-01-31 18:39:00 0.104
0xC00302FD 2008-01-31 18:40:00 0.104
0xC00302FD 2008-01-31 18:41:00 0.104
0xC00302FD 2008-01-31 18:42:00 0.104
...

...
...

View 1 Replies View Related

Minute Count Query

Aug 22, 2007

I need a query that gives me the sum of every rows (time column) with lower 'rownr'

the result:
rownrtimetimesum
1100
21010
31020
41030
51040
61050
71060
81070

current table looks like this:
rownrtime
110
210
310
410
510
610
710
810

and i want the 'timesum' column to be in format hhhh:mm
current format is rownr=int, time=datetime

thx for all help

//Mr

View 14 Replies View Related

Generate 5 Minute Intervals

Jan 28, 2008

Hello
Probably a very simple problem, but im stumped. I have a table which gives the start-time and end-time of an employees work day. I want to create a view which contains a line of data for each 5 minute period worked. Please help.

View 3 Replies View Related

Strip Off Hour, Minute, Second From A Datetime?

Jan 18, 2005

I would like something I can do inline eg:

select convert(blahdatatype,a.datefield) as smallerdatefield
from
a

where a.datefield is a datetime. If a contains rows like:

datefield
---------------------
01/20/2005 22:17:23
08/23/2001 03:04:15
...

Then the SQL above returns:

smallerdatefield
---------------------
01/20/2005 00:00:00
08/23/2001 00:00:00
...

Is there any non-obnoxious way (eg: without have to result to using datepart a million times) to do this? For instance, Oracle provides a function called Trunc which does it, but I cannot find an SQL Server equivalent. Anyone? TIA!!!

View 9 Replies View Related

Need To Only Return Rows In 5 Minute Intervals

Mar 6, 2012

I have collected perfmon data that is in every 15 seconds. I need to run a query that will only retrun rows that are 5 minutes from the last row starting at a specific date/time.

Here is the current query

Select
DisplayString,
MachineName,
ObjectName,
CounterName,
InstanceName,

[Code] ......

"CounterDateTime" is in every 15 seconds. So starting from '2012-03-02 11:59:00' I need only rows for every 5 minutes after that.

View 14 Replies View Related

How To AVG Values For One Hour Of Five-minute Increments?

Feb 18, 2008

Hello all,

I have the following very simple SQL Query:

SELECT TOP (100) AutoNumber, Date, ZNT, SAT, RAT, RH
FROM HV_Values
ORDER BY Date ASC

This shows the top 100 entries for a table (where there is a date entry every 5 minutes). Here's five sample records:

1 12/4/2006 4:12:11 PM 67.13 70.50 71.56 8.23
2 12/4/2006 4:17:11 PM 67.13 70.50 71.56 8.33
3 12/4/2006 4:22:11 PM 67.19 70.69 71.69 8.19
4 12/4/2006 4:27:11 PM 67.19 70.63 71.69 8.18
5 12/4/2006 4:32:11 PM 67.19 70.69 71.75 8.05

What I'd like to do with the sample query above is take an AVG for one hour for the values ZNT, SAT, RAT, RH...like I stated the records are every five minutes in the table, I'd like to create 60 minute AVGs.

Any ideas or sample queries I can work with?

Thank you very much! gad1

View 5 Replies View Related

Job Schedule Every Minute For A Stored Procedure

Jan 31, 2008



hi, i need to synchronize a table from a database to two tables on a different database. I was thinking in a stored procedure to do it and create a job that execute it every minute.

I don't know if this is the right method... maybe it can be a bad decision for the server performance.

Anyone can help me to do that?

View 3 Replies View Related

SQL Server Agent Jobs And A Frequency &< 1 Minute

Jan 22, 2004

Just wondering if anyone knew if it was possible to create a SQL Server agent job that ran once every 30 seconds. I can't enter a value below 1 minute when editing the job schedule in Enterprise Manager.

Alternatively, I can create duplicate stored procedures and set one to run on the minute and one on the half minute. Thought there might be a better alternative to this though and I'll always have to be remembering to makes changes to both sprocs.

Thanks!

View 3 Replies View Related

T-SQL (SS2K8) :: Difference In Seconds To Round To Minute?

May 20, 2014

I'm trying to get the difference between the two times in minutes with the seconds rounded up or down

As an example the difference in the times:

1900-01-01 09:27:49.000, 1900-01-01 09:32:28.000 is 279 seconds which is 4.65 minutes,

using

select
cast(round(datediff(second, convert(datetime, '1900-01-01 09:27:49.000'), convert(datetime, '1900-01-01 09:32:28.000')) / 60, 2) as numeric(18,2))

I am receiving the value 4.00 instead of 5 which I would like 4.65 to round to. How can I get the difference in seconds to round to the minute ?

View 5 Replies View Related

SQL 2012 :: Dump Every 1 Minute In Secondary Replica?

Jun 9, 2015

SQL is generating dump every 1 min with error

EXCEPTION_ACCESS_VIOLATION reading address 0000000000000020 at

Server is running SP2 CU2.

View 1 Replies View Related

T-SQL (SS2K8) :: Scrub Data In 5 Minute Intervals

Nov 2, 2015

I have a situation where I have table with over a billion records and needs to be scrubbed. Table does have a field with date time timestamp. I have been deleting rows from the table using the script below which basically provides me delete statements by date for records older than 90 days.

But now on each day row count is over 30 million rows and it takes forever to delete by date and transaction log becomes humongous.

So I would like to scrub it in 5 minute intervals instead of daily for records older than 90 days. Even in 5 minute intervals the record count tends to be around a million. This will keep the delete slice small enough to not a gigantic transaction log.

declare @startdate Datetime
declare @enddate Datetime
set @startdate = getdate()-480
set @enddate = getdate()-90

--set @vStart = select convert(varchar,@startdate, 102)

print @startdate
print @enddate

WHILE (@startdate < @enddate)
BEGIN
print 'delete from vending where DetectedDate < ''' + CONVERT(varchar(10), @startdate, 101) +''''
set @startdate = @startdate+1
END

I am hoping to modify the script above to produce a script with statements like this for a window between last 90 and 120 days:

delete from vending where DetectedDate <'6/15/2015 8:25:00 PM'
go
delete from vending where DetectedDate <'6/15/2015 8:30:00 PM'
go
delete from vending where DetectedDate <'6/15/2015 8:35:00 PM'
go

View 2 Replies View Related

Return Data Only If Time Is Equal By A Minute

Apr 9, 2015

Just wondering what is the best time to ensure that we only return data when the datetime field is the same when compared between two datetimes within a minute difference.

As in the following should return the data:

'2015-04-09 09:00:20' compared to '2015-04-09 09:00:50'

And the following should not return the data:

'2015-04-09 09:01:20' compared to '2015-04-09 09:00:50'

The problem, is that I'm merging data from three different result sets, which they all have data for every minute, however, the timestamp can be different by seconds or milliseconds.

So, I'm only interested to return the data when the two fields that I'm comparing are equal within a minute. I need to ignore seconds and milliseconds.

View 4 Replies View Related

Aggregating Financial Data To 1 Minute Intervals

Aug 19, 2006

I'm trying to create a query to return Open, Close, Max and Min Price for each 1 minute interval. Source data has two fields - Price, and Datestamp at 5 second intervals.

I can calculate the Max and Min (below) and set the datestamp to the middle of the interval, but get stuck on how to also return the Open and Close price for each interval.

SELECT MAX(price) AS MaxPrice, MIN(price) AS MinPrice,
DATEADD(ss, 30, DATEADD(n,DATEDIFF n, '1/1/2006', DateStamp),'1/1/2006')) AS DateStamp
FROM MasterData
GROUP BY DATEDIFF(n, '1/1/2006',DateStamp)

Any ideas?

thanks in advance.

View 1 Replies View Related

What Is The Right Datatype To Store Hours Up To The Minute Precision?

Jan 28, 2006

Right now the database I am working with is storing time inan Integer data type and is storing the time value in seconds.The application does not allow entering seconds. It acceptsminutes and hours.I have a report where it is doing:SELECT SUM(TIMEENTERED)and the SUM is *blowing* up as the SUM is reachingthe BIGINT range.I can fix the problem by changing all codes to:SELECT SUM(CAST(TIMEENTERED AS BIGINT))But now that I ran into this problem I want to find outif storing the time in seconds using INTEGER datatype is the best solution?I've been searching this newsgroup and other placesthe whole day. I even ran into my own three year oldpost. Three years ago my problem was data migrationrelated and now it is more of performance related thananything else.http://groups.google.com/groups?as_...y=2006&safe=offI could not find this specific topic in SQL books likeSQL for Smarties 2005 by Joe Celko (very good stuff ontemporal topics but nothing specific to my question),or Inside SQL Server 2000.Which data type would be ideal and why?smalldatetime?integer?decimal?float?The type of operations that are being done in the databaseare:1- Entering time in hours on work done on a taskFor the data entry part, the application accepts2.5 as 2 and a half hours and it is storing2.5 * 3600 = 9000 seconds.It also accepts entering 2:30 as 2 hours and30 minutes and again storing 9000 seconds.I even saw a page where you can enter clocktime: I worked from 9:30AM to 12:45PMas an exampleWhen i checked the underlying table(s) I sawthat the ENTEREDTIME is always the durationin seconds. So the data entry can either be2.5 hours where ENTEREDTIME = 9000 secondsor9:00AM to 11:30AMwhere STARTDATE is today's date for examplestored as 1/27/2005 09:00AMand where ENTEREDTIME = 9000 seconds2- All kinds of reports showing total time in hoursfor example: Project1 = 18.5 hoursThe code in the SP are all like:SUM(ENTEREDTIME) / CAST(3600 AS DECIMAL(6,2))AS TOTALTIME3- I am sure a lot of other arithmetic calculations arebeing done with this ENTEREDTIME field.What would be the best way to store hours/minutesbased on how we are using Time in the database?Either I will stick with Integer but store in minutestime instead of calculating in seconds and most likelyupdate all the SUM(ENTEREDTIME) toSUM(CAST(ENTEREDTIME AS BIGINT))or I will switch to storing in decimal/float andmaybe avoid doing :SUM(ENTEREDTIME) / CAST(3600 AS DECIMAL(6,2))AS TOTALTIMEsince the ENTEREDTIME would already be storedin hours time.or I will use DATETIME since in the cases ofI worked from 9:00AM to 11:30AMI have to have a separate column to store the date also.I am a little confused I am hoping I will get some helpfrom you and maybe if I can't find the best solution, atleast eliminate the NOT so good ones I am thinking of.Thank you

View 1 Replies View Related

Increase Of Lock Waits Per Minute With Mirroring

May 29, 2007

Hey All:

About a month ago I setup mirroring with our DB, High safety with automatic failover. Ever since the lock waits per minute in the DB went from maybe 2-5 per minute to 22-25 per minute. I am not sure if this was expected or what; but sometimes it spikes even higher than that.

Does this sound off the charts to anyone or normal for mirroring?

Thanks,

Let me know if you need anymore info....

View 6 Replies View Related

SQL Server 2014 :: Converting Hour And Minute To Decimal?

Jul 31, 2014

I am having some issues converting time to decimal from one of my column which display it as

2014-07- 08:16:31.000 (datetimecreated)

View 9 Replies View Related

SQL 2012 :: Server Agent Shutting Down Minute After Starting

Aug 27, 2014

When I start SQL Server Agent it's shutting down minute after starting. I tried to set Alerts to send email. After I configured Database Mail ,I was able to send test email , however after that SQL Server Agent started to misbehave.

View 3 Replies View Related

SQL Server 2012 :: Group Data Into 15 Minute Intervals

Apr 8, 2015

I want to group my data into 15 minute interval . Below is my sample data and desired result set.

Create TABLE #HalfHourlyIntervals
(
OrderDate DATETIME,
IRevenue FLOAT,
TRevenue FLOAT
)

[Code] ....

View 9 Replies View Related

Transact SQL :: Notify Only If Blocking Is Happening For More Than A Minute Or 30 Seconds

Aug 14, 2015

I have configured an alert like below to track all blocked events in SQL Server across all databases and then kick start a sql job when a blocking happens which inserts data to a table, when there is a blocking in SQL server , i get an email  --which is working fine and i am able to track all queries.

but, HOW to get notifications ONLY if BLOCKING IS HAPPENING FOR MORE THAN 30 SECONDS OR 1 MINUTE with out using sp_configure?

---ALERT
USE [msdb]
GO
EXEC msdb.dbo.sp_update_alert @name=N'Blocking Process', 
@message_id=0, 
@severity=0, 
@enabled=1, 

[Code] .....

View 4 Replies View Related

Transact SQL :: Counting Blocks Of Time In 15 Minute Chunks

Feb 16, 2012

We have data that consists of an employee number, a start time and a finish time, similar to the example below

EMP   STARTTIME            ENDTIME

00001 10-Feb-2012 06:00:00 10-Feb-2012 10:00:00

00002 10-Feb-2012 07:15:00 10-Feb-2012 10:00:00

00003 10-Feb-2012 08:00:00 10-Feb-2012 10:00:00

I am trying to come up with a procedure in SQL that will give me each 15 minute block throughout the day and a count of how many employees are expected to be at work at the start of that 15 minute block. So, given the example above I would like to return

10-Feb-2012 00:00:00     0
10-Feb-2012 00:15:00     0
10-Feb-2012 06:00:00     1
10-Feb-2012 06:15:00     1

[code]....

I'm not too worried if the date part is not included in the result as this could be determined elsewhere, but how can I do this grouping/counting?

View 7 Replies View Related

Transact SQL :: Update Date With Specific Hour / Minute And Second

May 7, 2015

How I can update the following date to get the desired result with specific hour, minute and second
2014-01-01 00:00:00.000

Desired Result
2014-01-01 17:20:20.000

View 4 Replies View Related

T-SQL (SS2K8) :: Table With 3 Million Plus Records Taking Half A Minute?

Aug 6, 2015

I have a table that I need to do some computations on all the data but first I need to remove the duplicate records and insert the results into a destination table. Here's the example below. My table has 3.1 million rows. I have tried using the DISTINCT and the GROUP BY but both ways to select the data takes about half a minute to run. I'm wondering if there is a way to increase performance. Users are ok with this time since the process runs overnight but improving it won't hurt. I do have a clustered index on these fields but that doesn't seem to improve any.

SELECTDateYear ,
DateMonth ,
Nbr ,
Nbr1 ,
Nbr2 ,
Datafield1 ,
Datafield2,

[code].....

View 7 Replies View Related

DB Engine :: Page Life Expectancy Increases Dramatically From A Minute To Another

May 22, 2015

I measure PLE on my server and insert them every minute into a table. Now, when I look into the table I just dont know how to interpret the following data. I dont understand how is that possible. It's an sql server bug? or? How to interpret that data? 

View 10 Replies View Related

Transact SQL :: Use Date / Hour And Minute In Datetime Column Type?

Nov 9, 2015

I just need the date, hour, and minute...not the micro seconds. Do I have to DATEPART and concatenate each or is there any way to simply truncate the milliseconds from it? Or is there a date format to put extract and report on it as...

MM/DD/CCYY HH:MM:SS AM

I see there is a format 131 that puts it in..

DD/MM/YYYY HH:MM:SS:MMMAM

View 7 Replies View Related

Changing Seconds To Hours And Minutes And Rounding To The Nearest 15 Minute.

Dec 6, 2007

I found in another forum that if I take the seconds and divide them by 15 then round up and multiply them by 4 I can get this done, but I can't figure out how to work it into my select statement. Anyhelp would be greatly appreciated. dbo.SLPTRANS.TimeSpent is the field I am trying to convert.

SELECT dbo.SLPTRANS.ClientID, SUM(dbo.SLPTRANS.TransValue) AS Expr1, dbo.SLPTRANS.TimeSpent AS Expr2
FROM dbo.SLPTRANS INNER JOIN
dbo.INVOICE ON dbo.SLPTRANS.InvoiceID = dbo.INVOICE.RecordID
GROUP BY dbo.SLPTRANS.ClientID
HAVING (dbo.SLPTRANS.ClientID = 405)

View 4 Replies View Related

Transact SQL :: Only Store Datetime Values Down To Nearest Minute Automatically Without Using Trigger

Sep 25, 2015

Is there a way that I can do this at the table level to automatically handle the rounding of seconds, etc. down to the minute automatically without having to use a trigger?  

Here is a very basic example of what I am trying to do:

--example:  '09-22-2007 15:07:18.850' this is the value inserted into the table by the code
select getdate() 

 --example: '2007-09-22 15:07:00.000'  this is the value I want to store in the table
select dateadd(mi, datediff(mi, 0, getdate()), 0)

View 24 Replies View Related

SQL 2012 :: 15 Minute Interval Report Between Two Dates With Total For Each Interval

Jul 21, 2014

I'm trying to create a report which will give me a break down of how many unique vehicles have been seen between two dates via a 15 minute interval and what Lane they were seen. My current script looks like this

SELECT l.Name [Name], count(l.Name) Total, p.Created
FROM PlateReads p
inner join Lanes l on p.Lane_ID = l.ID
where LicencePlate in (Select Plate from LPRnet_MelAir_C.dbo.TempVehiclePlates)
group by Name
Name being the Lane they were in and the Total being the amount of times a unique vehicle has been seen and p.Created being the date they were seen (thats what I need the interval powered off)

Ideally the output would look like this

16/03/201408:00 to 08:15Bus Lane 15
16/03/201408:00 to 08:15Elevated Road150
16/03/201408:00 to 08:15Public Pickup75

16/03/201408:15 to 08:30Bus Lane 13
16/03/201408:15 to 08:30Elevated Road120
16/03/201408:15 to 08:30Public Pickup55

All the way to 12/04/2014

I’ve got it so it says Lane and Count just can’t get the interval part

View 5 Replies View Related

How To Exract SQL Minute Data To Houly Data?

Aug 1, 2006

Hi

I have a SQL table that has data filled with million records and the date is in minutes it looks like :-








RowDateTime
Meter 1
Meter 2
Meter 3

25/05/2006 02:49:00
1220
450
489

25/05/2006 02:50:00
1223
470
500

25/05/2006 02:51:00
1227
490
511

25/05/2006 02:52:00
1230
510
522

25/05/2006 02:53:00
1233.5
530
533

25/05/2006 02:54:00
1236.9
550
544

25/05/2006 02:55:00
1240.3
570
555

25/05/2006 02:56:00
1243.7
590
566

I want to make a query to the above table and convert the data to houlry by summing Meter1,Meter 2 and Meter 3 to be the average. I want to import all the hourly data to a new table that will look like :-








RowDateTime
Meter 1
Meter 2
Meter 3

25/05/2006 03:00:00
9854.4
4160
527.5

25/05/2006 04:00:00
8634.4
3710
533

25/05/2006 05:00:00
7411.4
3240
538.5

Your help will be highly appreciated.

View 3 Replies View Related

EVENT ID 18456 STATE: 16 Login Failed For User 'DOMAIN/user'. [CLIENT: &&<local Machine&&>] Every Minute In Event Log.

Oct 22, 2007

We recently upgraded to SQL 2005 from SQL 2000. We have most of our issues ironed out however about every 1 minute there is a message in the Application Event log and the SQL log that states:

EVENT ID 18456 Login Failed for the users DOMAIN/ACCOUNT [CLIENT: <local machine>]

This is a state 16 message which I thought meant that the account does not have access to the default database. The account is actually the account that the SQL services run under.

Any ideas? We can't seem to figure this one out. We actually upgraded to 2005 from 2000 and had an error appear after every reboot that prevented the SQL Agent from running(This application has failed to start because GAPI32.dll was not found. Re-installing the application may fix this problem.) We did a full uninstall of SQL and reinstalled fresh and restored the databases from .bak files and that is when the EVENT ID 18546 started occuring every minute.

We don't have any SQL heavy hitters here so please be detailed with any possible solutions. That you very much for any help you can provide!

David

View 5 Replies View Related

Calculate % Qry

Feb 13, 2007

Hi,
 I am trying to execute a query which calculate % on group by. here is the query
SELECT     C.USR_HIGHEST_DEGREE,COUNT(DISTINCT C.MASTER_CUSTOMER_ID) AS [# MEM],(convert(numeric(5,2),COUNT(DISTINCT C.MASTER_CUSTOMER_ID))
/    (
 
SELECT convert(numeric(5,2),COUNT(CC.MASTER_CUSTOMER_ID))    FROM         MBR_PRODUCT AS MPP WITH (NOLOCK) INNER JOIN                      PRODUCT AS PP WITH (NOLOCK) ON MPP.PRODUCT_ID = PP.PRODUCT_ID INNER JOIN                      ORDER_MASTER AS AA WITH (NOLOCK) INNER JOIN                      CUSTOMER AS CC WITH (NOLOCK) ON AA.SHIP_MASTER_CUSTOMER_ID = CC.MASTER_CUSTOMER_ID INNER JOIN                      ORDER_DETAIL AS BB WITH (NOLOCK) ON AA.ORDER_NO = BB.ORDER_NO ON MPP.PRODUCT_ID = BB.PRODUCT_ID              WHERE (CC.CUSTOMER_STATUS_CODE = 'ACTIVE') AND (BB.CYCLE_END_DATE >= GETDATE()) AND (AA.ORDER_STATUS_CODE = 'A') AND                       (BB.LINE_STATUS_CODE = 'A') AND (BB.FULFILL_STATUS_CODE IN ('A', 'G')) AND (MPP.LEVEL1 IN ('NATIONAL'))
 
 
)*100 AS [%]
 
FROM         MBR_PRODUCT AS MP WITH (NOLOCK) INNER JOIN                      PRODUCT AS P WITH (NOLOCK) ON MP.PRODUCT_ID = P.PRODUCT_ID INNER JOIN                      ORDER_MASTER AS A WITH (NOLOCK) INNER JOIN                      CUSTOMER AS C WITH (NOLOCK) ON A.SHIP_MASTER_CUSTOMER_ID = C.MASTER_CUSTOMER_ID INNER JOIN                      ORDER_DETAIL AS B WITH (NOLOCK) ON A.ORDER_NO = B.ORDER_NO ON MP.PRODUCT_ID = B.PRODUCT_IDWHERE     (C.CUSTOMER_STATUS_CODE = 'ACTIVE') AND (B.CYCLE_END_DATE >= GETDATE()) AND (A.ORDER_STATUS_CODE = 'A') AND                       (B.LINE_STATUS_CODE = 'A') AND (B.FULFILL_STATUS_CODE IN ('A', 'G')) AND (MP.LEVEL1 IN ('NATIONAL')) GROUP BY C.USR_HIGHEST_DEGREE
My problem here it , query gives error. I would appreciate if someone can give me any idea how to go about this. I tried calculating % outside sql but still doesn't work.
Any help would be appreciate.

View 3 Replies View Related







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