I have a SQL procedure that can take several minutes to complete. I allow users to initiate the process through a web site and view a progress bar. When the process is running, though, the site slows to a crawl or times out completely. That long running process seems to block all other queries on the database. Is there a way to give this process a low priority or somehow throttle its resource use so that the other web processes can get a chance to run in a timely manner?
I have 3 three scheduled job: one runs onece a day, one runs once per hour, and another runs every 17 minutes. It is a NetIQ application. I just scheduled SQL Server maintianace job last night which ran at 2:00Am and 4:00Am. This morning, I came in office and found all my jobs were still running; and they were all blocked by the first 3 jobs. I had to kill all of them. In this afternoon, I kicked off one of my many DTS packages which runs usually about 40 minutes, but it failed. I tried several times but no luck. I suspected one of user tables corrupted or one of stored procedures corrupted. After I recycle the server, and dropped the table and the stored procedure, and recreated them, the package went fine. The store procedure involves many updates and inserts.
The question I have is: is it possible to cause this problem because I killed the unfinished jobs (especially the sql maintanace job)?
NOTE: the sql maintanace job does not include the backup of database and transaction log.
We have a DTSX in which parallel threads are processing ETL. There is a Final step in the DTS to do cleanup tasks. we are getting the following error after ETL completes. This is not consistently reproduced.
Description: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Login timeout expired". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Unable to complete login process due to delay in prelogin response". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Shared Memory Provider: Timeout error [258].
If I start a long running query running on a background thread is there a way to abort the query so that it does not continue running on SQL server?
The query would be running on SQL Server 2005 from a Windows form application using the Background worker component. So the query would have been started from the background workers DoWork event using ado.net. If the user clicks an abort button in the UI I would want the query to die so that it does not continue to use sql server resources.
dear guys. i have this one problem, where the sql statements really took very long time to be processed. It took more than 1 minute, depending on the total data in the table. I guest this have to do with the 'count' statements. here is the code:
------------------------------------------------------------ $sql = "SELECT company,theID,abbs,A as Active,N as Nonactive,(A+N) as Total FROM( select distinct D.nama As company, C.domID As theID, D.abbrew As abbs, count(distinct case when B.ids is NOT NULL THEN A.dauserid END) As A, count(distinct case when B.ids is NULL THEN A.dauserid END) As N FROM tableuser A LEFT OUTER JOIN tabletranscript B on (A.dauserid=B.dauserid) INNER JOIN thedommember C ON(C.entitybuktiID=1 AND C.mypriority=1 AND
C.entitybuktiID=A.dauserid) INNER JOIN mydomain D ON (C.domID=".$getID.") GROUP BY D.nama, C.domID, D.abbrew ORDER BY company )";
Hope any of you can simplify this statements into a query that doesnt take ages to be processed.
Good afternoon everyone, I have written a view that pulls customer demographic infomration as well as pulling data from multiple scalar-valued functions. I am using this view to pull and send data from one database to another in the same SQL server. The problem that I am having is that I am running this import as a scheduled job in windows. The job is taking almost 24 hours to complete this task. The total number of records that are being pulled is around 21,000+. I have tried removing the functions from the view and it only takes the view 20 seconds to pull the demographic information from the same 21,000+ records but when I add the function calls this is where the time to complete goes through the roof. Has anyone encountered this before if so what would you suggest doing? Any help would be appreciated. Here is the syntax for my view: SELECT TOP 100 PERCENT CUS_EMAIL AS Email, CUS_CUSTNUM AS MemberID, CUS_PREFIX AS Prefix, CUS_FNAME AS FirstName, CUS_LNAME AS LastName, CUS_SUFFIX AS Suffix, CUS_TITLE AS Title, CUS_STATE AS State, CUS_COUNTRY AS Country, CUS_ZIP AS ZipCode, CUS_SEX AS Gender, CAST(CUS_DEMCODEA AS nvarchar(20)) + ',' + CAST(CUS_DEMCODEB AS nvarchar(20)) + ',' + CAST(CUS_DEMCODEC AS nvarchar(20)) + ',' + CAST(CUS_DEMCODED AS nvarchar(20)) AS DemoCodes, dbo.GetSubScribedDateMLA(CUS_CUSTNUM, CUS_EMAIL) AS MLASubscribedDate, dbo.GetSubScribedDateMLP(CUS_CUSTNUM, CUS_EMAIL) AS MLPSubscribedDate, dbo.GetSubScribedDateLDC(CUS_CUSTNUM, CUS_EMAIL) AS LDCSubscribedDate, dbo.GetMLAExpiration(CUS_CUSTNUM, CUS_EMAIL) AS MLASubExpireDate, dbo.GetMLPExpiration(CUS_CUSTNUM, CUS_EMAIL) AS MLPSubExpireDate, dbo.GetLDCExpiration(CUS_CUSTNUM, CUS_EMAIL) AS LDCSubExpireDate, dbo.IsProspect(CUS_CUSTNUM, CUS_EMAIL) AS AGMProspect, dbo.IsCurrentCustomer(CUS_CUSTNUM, CUS_EMAIL) AS AGMCurrentCustomer, dbo.IsMLAMember(CUS_CUSTNUM, CUS_EMAIL) AS MLAMember, dbo.IsMLPMember(CUS_CUSTNUM, CUS_EMAIL) AS MLPMember, dbo.IsLDCMember(CUS_CUSTNUM, CUS_EMAIL) AS LDCMember, dbo.CalculateTotalRevenue(CUS_CUSTNUM, CUS_EMAIL) AS AGMTotalRevenue, dbo.GetPubCodes(CUS_CUSTNUM, CUS_EMAIL) AS ProductsPurchased, dbo.GetEmailType(CUS_CUSTNUM, CUS_EMAIL, CUS_RENT_EMAIL) AS EmailType, CUS_COMPANY AS Company, CUS_CITY AS City FROM dbo.CUS WHERE (CUS_EMAIL IS NOT NULL) AND (CUS_EMAIL <> '') AND (CUS_EMAIL_VALID = 'Y') AND (CUS_EMAIL LIKE '%@%.%') AND (CUS_RENT_EMAIL = 'Y' OR CUS_RENT_EMAIL = 'R' OR CUS_RENT_EMAIL = 'I') AND (CHARINDEX(' ', CUS_EMAIL) = 0) AND (CUS_EMAIL NOT LIKE '@%') Thanks in advance Michael Reyeros
I have a pretty complex query that aggregates lots of data and inserts multiple rows of that data into a reporting table. When I call this SPROC from SQL Server Management Studio, it executes in under 3 seconds. When I try to execute the same SPROC using .NET's SqlCommand object the query runs indefinitely until the CommandTimeout is reached. Why would this SPROC behave differently with the same inputs, but being called from .NET? Thanks for your help!
Hi everyone.... I'm trying to execute this update statement... It takes an eternity... any ideas on how to rewrite or speed it up?
It's a several step process... below is everything that i run, one step at a time. The final update statement is what takes so long. It should only affect about 2600 rows out of a potential 9000. That's why I'm confused on the response time
select d.olddevicename, de.device, d.newdevicename into #temp9 from dns d, devices de where de.device = d.olddevicename
update #temp9 set device = newdevicename where olddevicename = device
update devices set device = #temp9.device from #temp9, devices where #temp9.device in (select #temp9.device from #temp9, devices where #temp9.olddevicename = devices.device)
My backups are running 5-6 hours on SQL2000. I'm sure they only used to take 1 hour or so. On another server, backing up the same database (both about 50 gig), the backup only takes 45 min - 1 hour. What can I look at to see why it's taking so long ?
Trying to come up with a way to monitor (without profiler, hopefully with a job and a select statement) a specific sql job that may cause a problem if the duration is too long. It seems that there is an sp called sp_sqlagent_log_jobhistory that shoves a record in sysjobhistory, but only after all the job steps run. Anyone tried this before?
Hello Gurus I am using sql 2005 and one job status is executing in job monitor in 2005,How can i check since how long this job is running? Please advice
I've got a server (SQL 2K, Win2K) where the backupshave started running long.The database is a bit largish -- 150GB or so. Up untillast month, the backups were taking on the order of4 to 5 hours -- depending on the level of activity on theserver.I'm using a T-SQL script in the SQLAgent to run thebackups. Native SQL backup to an AIT tape drive.Now, for no apparent reason, the backups are takingon the order of 24 to 26 hours. The backups completesuccessfully -- no errors, just taking an outrageouslylong time to complete. DBCCs check out AOK, noproblems with the database.No changes to the machine. No hardware changes. Nosoftware changes. Weird.Multiple tape media have been tried -- it's not a caseof a tape going bad.We've had no problems with this box for almost 4years. Now it's gettin' jiggy with us!Any ideas on where to start with this one?Thanks in advance.
Hi. I'm fairly new to SSRS, and very new to this forum. I have a report based on a stored procedure. I've optimized the procedure so that it runs from 2-4 minutes (previously over half an hour). However, when I run the report that calls the sp, it runs forever (well over 45 minutes in some cases), and the users basically give up on it. Any ideas of why this happens and what steps I can take to improve performance?
I need to execute a long running package (it takes about 16 hours to finish) to load a data warehouse for the first time with all historical data. This package it's a master package and execute other packages; I log the start time and the finish time of the package in a table to manage future incremental loads.
I executed the package on sql server where it is saved, but after 8 hours it was running, a new package was started automatically. Then two more packages started .. each every two hours.
I set the MaxConcorrentExecutable = 4, this could affect this strange behavoir ?
I'm trying to optimize a long running (several hours) query. This query is a cross join on two tables. Table 1 has 3 fields - ROWID, LAt and Long. Table 2 has Name, Addr1,Addr2,City,State,Zip,Lat,Long.
Both tables has LatRad - Lat in radians, LonRad- Lon in Radians. Sin and Cos values of Lat and Lon are calulated and stored to be used in the distance formula.
What I'm trying to do here is find the nearest dealer (Table 2) for each of Table 1 rows. The Select statement takes a long time to execute as there are about 19 million recs on table 1 and 1250 rows in table 2. I ran into Log issues- filling the transaction log, so I'm currently using table variables and split up the process into 100000 recs at a time. I cross join and calculate the distance (@DistValues) and then find the minimum distance (tablevar2) for each rowid and then the result is inserted into another Table (ResultTable).
distance=3963.1*Case when cast(S.SinLat * T.SinLat + S.CosLat * T.cosLat * cos(T.Lonrad - s.Lonrad) as numeric(20,15)) not between -1.0 and 1.0 then 0.0 else acos(cast(S.SinLat * T.SinLat + S.CosLat * T.cosLat * cos(T.Lonrad - s.Lonrad) as numeric(20,15))) end
from dbo.TopNForProcess T , dbo.Table2 S where Isnull(T.Lat,0) <> 0 and Isnull(T.Lon,0)<> 0
Insert into @MinDistance
Select DataSeqno,Min(distance) From @DistValues Group by DataSeqno
Insert into ResultTable (DataSeqno,Lat2,Lon2,StoreNo,LAt1,Long1,distance)
Select D.DataSeqno, D.Lat2, D.Lon2, D.StoreNo, D.LAt1, D.Long1, M.distance from @DistValues D Inner Join @MinDistance M on D.DataSeqno = M.DataSeqno and D.Distance = M.Distance
I created a View called TopNForProcess which looks like this. This cut down the processing time compared to when I had this as the Subquery.
SELECT TOP (100000) DataSeqno, lat, Lon, LatRad, LonRad, SinLat, cosLat, SinLon, CosLon FROM Table1 WHERE (DataSeqno NOT IN (SELECT DataSeqno FROM dbo.ResultTable)) AND (ISNULL(lat, 0) <> 0) AND (ISNULL(Lon, 0) <> 0)
I have indexes on table table1 - Rowid and another one with Lat and lon. Table2 - Lat and Long.
Is there any way this can be optimized/improved? This is already in a stored procedure.
I am having some difficulty with data-driven processes in an internal web application using ASP.NET framework 2.0 with SQL Server 2000.
I have a facility where the user enters search criteria to retrieve a list with the idea being that when they click a button the list set is migrated to a table as a batch. The SQL Server processes to perform the database operations can take up to 5min because of volume of data and relational complexity, which is fine.
The only problem is that I'm having trouble managing the SQL Server process if the user closes the browser or go to a new URL etc. If this happens on the client side it's like the HTTP request is terminated but the SQL statement left on the SQL Server still running, eventually it runs to completion but I would prefer the SQL process to also be abandoned and rolled back.
Any idea what is happening here and how it can be handled?
I have queries which take over 30 secs to run, which I wish to monitor. Currently, I am monitoring using sql profiler. Is there any way of setting up mail to e-mail me when such a query happens. Could I set up an Alert, or is there some other method.
I want to be able to react to these events faster before the users complain. I am using sql server 7 enterprise and I have exchange set up.
Problem: I schedule a job that calls a stored procedure which loads around 1.5 million records. The Job takes 19 hrs to complete. However, if i run that stored procedure manually in Query Analyser it takes only 45 minutes..
Did anyone faced this problem? Is this known problem..Any suggestions/recommendations?
I have a very long running stored proc (import and transformation of 2-3 M. records). The duration of about 1h is not the problem. My problem is that i want to send some notifications to the UI for showing a progress counter in the UI.
Is there any possibility to send send out a msg after certain steps (e.g. after positioning the cursor to next row) via an extended stored procedure which I could catch in my UI?
I would be very appreciative if somebody would send me a hint.
I’m trying to write a script that will detect long running agent jobs.
Having looked at this article: http://www.databasejournal.com/features/mssql/article.php/3500276
It appears that agent job job id’s don’t necessarily get stored in the programname of the sysprocesses table. This is true if the agent executes an os command. It also appears that job steps do not get stored in the sysjobhistory until the step is complete so that cannot be used accurately.
Does anyone know of an effective way to find if there are long running jobs other than these methods?
I just wanted to post a follow up to a message I posted some months agoabout a long running transaction that was blocking all other users...The link is belowhttp://groups.google.com/group/comp...649bee2002646a2By using the new "Row versioning" functionality of SQL 2005, itcompletely solved this problem. By reading the books online, it saysthere is a performance impact, but that the better performance of SQL2005 in general might offset it. So far this seems to be the case. justposting it here in case anyone else has the problem. The SQL command Iihad to execute to get everything working properly was:ALTER DATABASE DBnameSET READ_COMMITTED_SNAPSHOT ON;
How does one prevent a long running procedure form crapping out in CLR? I am trying to do a pull from a distant data source and it works, except I have to break down my stored procedure call into several smaller calls. I would like to do everything in one shot, but I get the thread abort exception when I try to get a lot of data.
The query show below is designed to use seasonal profiles to compute 53 weeks of forecast data and then from that compute the number of weeks of supply of each item at each location. The query works but the volume of data produced (20+M rows) is substantial. If I limit the CTE to a single location, it run is 2 seconds and returns 41,000 rows. But when run for all locations and items, it runs for more than 4 hours. Would I do better converting the CTE to a sub-query and adding an index to improve the performance of the main query?
WITH Forecast AS
(SELECT Location_Idx
,Item_Idx
,Week_Code
,(CAST(AnnualQty AS DECIMAL(9))/53.0)*[Profile] AS fcst
FROM dbo.FactReplenishmentProfile rp
INNER JOIN dbo.FactSeasonalProfile sp
ON sp.SeasonalProfile_Idx = rp.SeasonalProfile_Idx
)
SELECT fcst1.Location_Idx
,fcst1.Item_Idx
,fcst1.Week_Code
,fcst1.fcst AS WeekQty
,SUM(fcst2.fcst) AS CumQty
FROM Forecast fcst1
INNER JOIN Forecast fcst2
ON fcst2.Location_Idx = fcst1.Location_Idx
AND fcst2.Item_Idx = fcst1.Item_Idx
AND fcst2.Week_code <= fcst1.Week_Code
GROUP BY fcst1.Location_Idx,fcst1.Item_Idx,fcst1.Week_code,fcst1.fcst
I have a table that contains approx 200 thousand records that I need to run validations on. Here's my stored proc:
[code] CREATE PROCEDURE [dbo].[uspValidateLoadLeads] @sQuotes char(1) = null, @sProjectId varchar(10) = null, @sErrorText varchar(1000) out AS BEGIN DECLARE @ProcName sysname, @Error int, @RC int, @lErrorCode bigint, DECLARE @SQL varchar(8000)
IF @sQuotes = '0' BEGIN UPDATE dbo.prProjectDiallingList_staging SET sPhone = RTrim(LTrim(Convert(varchar(30), Convert(numeric(20, 1), phone)))) END ELSE BEGIN UPDATE dbo.prProjectDiallingList_staging SET sPhone = phone END
--4. Update failed Validation column if not 10 digits UPDATE dbo.prProjectDiallingList_staging SET sFailedValidation = 'X' WHERE(Len(RTrim(LTrim(sPhone))) <> 10)
--5. Dedup UPDATE a SET a.sFailedValidation = 'X' FROM dbo.prProjectDiallingList_staging a (nolock) INNER JOIN dbo.prProjectDiallingList_staging b ON a.sPhone= b.sPhone WHERE(a.iList_StagingID > b.iList_StagingID)
--6. Update failed Validation column if not numeric UPDATE dbo.prProjectDiallingList_staging SET sFailedValidation = 'X' WHERE(IsNumeric(RTrim(LTrim(sPhone))) = 0)
--7. Update time zones UPDATE s SET s.sTimeZone =z.sTimeZone FROM dbo.prProjectDiallingList_staging s (nolock) LEFT OUTER JOIN dbo.prPhoneTimeZone z ON left(rtrim(ltrim(s.sphone)),3) = z.sPhoneAreaCode
--8. Insert into dialing table only records that have not failed the validation INSERT dbo.prProjectDiallingList(iPrProjectId, sPhoneNumber, sTimeZone) SELECT @sProjectId,sPhone, sTimeZone FROM dbo.prProjectDiallingList_staging WHERE ISNULL(sFailedValidation,'1') = '1'
UPDATE d SET d.bProcessReporting = 1 FROM dbo.prProjectDialling d WHERE d.iPrProjectId = @sProjectId END [/code]
When I execute this stored proc it runs for more than 5 minutes. Is there anything i can do to speed it up? Maybe there is a faster way of writing these queries?
I€™m trying to write a script that will detect long running agent jobs.
Having looked at this article: http://www.databasejournal.com/features/mssql/article.php/3500276
It appears that agent job job id€™s don€™t necessarily get stored in the programname of the sysprocesses table. This is true if the agent executes an os command. It also appears that job steps do not get stored in the sysjobhistory until the step is complete so that cannot be used accurately.
Does anyone know of an effective way to find if there are long running jobs other than these methods?
Is there any way to measure the progress of a long running query, for instance, to find where in a query plan a query is in SQL 7.0?
I have a query I am running that is currently 2 1/2 hours into the query. Since it's joining three large tables, one with 42 million rows and two with 7 million rows, I'm expecting the query to take a while. However, I have no way of estimating exactly how long it will take. Before I ran it, I optimized it the best I could in Query Analyzer using an estimated query plan, making sure I had all the right indexes, etc. I've been trying to use the estimated cost to project query time, but that hasn't been working since queries with similar costs can take radically different amounts of time to execute.
Now I'm sitting here waiting, wondering if the query is just taking too long, and I should stop it and work on optimizing it some more (since I will have to run a couple more queries like it), or let it finish. But I have no clue how close it is to finishing. I've tried looking at the Physical I/O given by sp_who2 and then trying to calculate the number of pages it would have to read if it had to read everything from disk, then estimating it's progress by that, but this seems dubious at best, since I don't know a whole slew of factors (ie: how many pages are being read from the cache, is my page calculation correct, etc).
So, does anyone know of any way to figure out how soon a long running query will finish in SQL 7.0?
I have a stored procedure being called from Visual Cafe 4.0 that takes over 30 minutes to run. Is there any way to backround this so that control returns to the browser that the JFC Applet is running in? The result set is saved to local disk and an email message sent to the user on completion. Thanks, Dave.
Does Anyone of the SQLServer Guru has a smart script to alert DBA by email for failed jobs or jobs running more then their normal time(long running jobs), so that I dont have to go and look at the jobs everyday manually...on different servers...
