i observed a strange problem in my production setup. i have a job which updates usage metrics (for reporting) which is scheduled to run once in a day. (the job invokes an sp to do this. the sp refers two tables to retrieve/update information, say TableA and TableB).
the job normally takes an average of 25 seconds to complete. all of a sudden the job execution time increased to 6 minutes and 52 seconds. now, the average job execution time is 8 minutes. there is no table/sp change in the DB
the only thing i observed is that one of the tables referred by the sp has 30,000 records added to it, on the day from which the job execution time increaed to 6 minutes.
i have updated the statistics on the Table, but the execution time remains unchanged. can any one suggest any possible causes for such a scenario.
i expect a few hints with which i can explore my production DB and find out the causes for the increased execution time for the sp.
Is there a way to keep track in real time on how long a stored procedure is running for? So what I want to do is fire off a trace in a stored procedure if that stored procedure is running for over like 5 minutes.
Hello Anybody ! I want to get the execution time of a query, I mean I will run the one sql statement like this " SELECT * FROM tblname WHERE field1 = '009' and then I want to get from my program execution time of this query. I think I just keep the sys time before run it and compare with sys time when finished it. But I don't like this one, So, can I get the execution time from sql server by running their sys s-procedure or something like. Thanks.
Is it possible that a stored procedure runs slower when called by an application,and runs faster when executed as 'exec xxxxx' on query analyzer? It's actually happening to us.Any clue?? thanks. Di.
When I manually run the ssis package i.e. by clicking the run button) it takes about a second to complete. This package is scheduled to run as a job every two minutes. In the history window of jobs in sql server 2005, I see that each time the job takes about 31 seconds. Do you know why it takes 31 seconds where it should take about 1 second to complete? Thanks
I am trying to run a SQL Server procedure from a program in ASP.Net 2005. This procedure is to insert around 500 records(can exceed every month) in a table with 4 columns and is also containing another small procedure also. When this procedure is executed from online server, it shows timeout message as: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. But when the same procedure is run from SQL Query Anayser it excute within seconds. How can i solve this problem , i need this solution urgently too. Hope to get ur response soon.
IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'Fn_Get_Consensus_Curve_41_Data') AND ((type = 'P') OR (type = 'IF') OR (type = 'TF') OR (type = 'FN')))) DROP FUNCTION [dbo].Fn_Get_Consensus_Curve_41_Data
GO
*/ declare @p_ENTITYID INT declare @p_CUSTOMERID INT
Declare @p_Login_Type int Declare @p_Result_Status int set @p_Login_Type = (SELECT DBO.GET_USER_LOGIN_TYPE_ID(@p_UserID))
If @p_Login_Type=1 and not (@p_CustId is null or @p_CustId='') Set @p_Result_Status = 1 Else if @p_Login_Type > 1 Set @p_Result_Status = 2 Else Set @p_Result_Status = 0
If @p_Result_Status > 0 -- if user is valid and given enough parameters than Begin If @p_Result_Status = 1 -- if User is trader and gives customer id Begin Declare Cur_Fetch_Curve_Cust_Data cursor for Select Distinct Customerid From PricesRR PRR Where Convert(Nvarchar,Matchdate,101) = Convert(Nvarchar,@p_Match_Date,101) And Sector_Id = @p_Sector_Id And Location_Code = @p_Location_Code And CustomerID = @p_CustId And --CustomerID <> 0 --CustomerID not in (0, -1, -2, -3, -100, -200) CustomerId Not In (Select CustomerId From Fn_Get_PricesRR_Not_To_Include_Cust_Id('V')) and isnull(PRR.Record_Last_Action,'N') <> 'D' and Version = dbo.GET_PRICESRR_MAX_VERSION(@p_Location_Code, @p_Sector_Id, @p_Match_Date, PRR.EntityID, @p_CustId, PRR.Date)
Declare Cur_Fetch_Curve_Entity_Data cursor for Select Distinct EntityID From PricesRR PRR Where Convert(Nvarchar,Matchdate,101) = Convert(Nvarchar,@p_Match_Date,101) And Sector_Id = @p_Sector_Id And Location_Code = @p_Location_Code AND EntityId IN ( Select Distinct Entity_Id from Fn_Get_Allowed_Entity_List(@p_Location_Code , @p_Sector_Id , @p_Match_Date ,@p_UserID )) and isnull(PRR.Record_Last_Action,'N') <> 'D' and Version = dbo.GET_PRICESRR_MAX_VERSION(@p_Location_Code, @p_Sector_Id, @p_Match_Date, PRR.EntityID, @p_CustId, PRR.Date)
End Else If @p_Result_Status = 2 -- if User is higher than trader.. means broker or higher Begin Declare Cur_Fetch_Curve_Cust_Data cursor for Select Distinct Customerid From PricesRR PRR Where Convert(Nvarchar,Matchdate,101) = Convert(Nvarchar,@p_Match_Date,101) And Sector_Id = @p_Sector_Id And Location_Code = @p_Location_Code And --CustomerID <> 0 --CustomerID not in (0, -1, -2, -3, -100, -200) CustomerId Not In (Select CustomerId From Fn_Get_PricesRR_Not_To_Include_Cust_Id('V')) and isnull(PRR.Record_Last_Action,'N') <> 'D' --and Version = dbo.GET_PRICESRR_MAX_VERSION(@p_Location_Code, @p_Sector_Id, @p_Match_Date, PRR.EntityID, @p_CustId, PRR.Date)
Declare Cur_Fetch_Curve_Entity_Data cursor for Select Distinct EntityID From PricesRR PRR Where Convert(Nvarchar,Matchdate,101) = Convert(Nvarchar,@p_Match_Date,101) And Sector_Id = @p_Sector_Id And Location_Code = @p_Location_Code and isnull(PRR.Record_Last_Action,'N') <> 'D' --and Version = dbo.GET_PRICESRR_MAX_VERSION(@p_Location_Code, @p_Sector_Id, @p_Match_Date, PRR.EntityID, @p_CustId, PRR.Date)
End delete from @Temp_Curve_Submission_Data
----------------------- -----------------------
Open Cur_Fetch_Curve_Cust_Data fetch next from Cur_Fetch_Curve_Cust_Data into @p_CUSTOMERID WHILE @@FETCH_STATUS = 0 BEGIN
IF @@FETCH_STATUS <> 0 break BEGIN ----------------------- ----------------------- Open Cur_Fetch_Curve_Entity_Data fetch next from Cur_Fetch_Curve_Entity_Data into @p_ENTITYID WHILE @@FETCH_STATUS = 0 BEGIN
I have a query that is taking 30-40sec to execute in a SQL Server 2005 Standard Edition database. However, when I use that same query to create a named view, and then try to open the view, I get the following error (eventually) after I attempt to open the view:
Executed SQL Statement: select ....
Error Source: .net sql data provider
Error Message: Timeout expired. The timeout period elapsed prior to completion of the operation, or the server is not responding.
Is there a server or set parameter that I can adjust that will allow my view to complete execution?
Hell All, Following query takes 7 minutes to execute while using search criteria as shown below in blue text(ie. IN(2006,2007) if criteria changes to =2006 as shown in 2),this takes 2minutes
But I want expected output as in query 1) in less time. How to optimize following query for execution time?
1)select sum(PB.CONSN_QTY)Consumption,Count(*),PB.BillPro_Year from tbtrans_prowaterbill PB INNER JOIN MIDC_AREA MA ON PB.Area_cd = MA.Area_cd INNER JOIN MIDC_Division MD ON MA.Div_CD = MD.Division_CD INNER JOIN MIDC_Circle MC ON MD.Circle_CD = MC.Circle_CD INNER JOIN TBMST_SubDiv TS ON MA.SubDiv_CD = TS.SubDiv_CD INNER JOIN MIDC_Zone MZ ON MD.Zone_CD = MZ.Zone_CD INNER JOIN tbmst_consumer TC ON PB.cons_no = TC.Cons_No INNER JOIN TBMST_CONSTYPE TCT ON TCT.Cons_Type = TC.Cons_Type where pb.billpro_year IN('2006','2007') and MTR_Size = 15 and TCT.Cons_Type = '1A2' and MZ.Zone_Name = 'MUMBAI' and MC.Circle_NAME = 'MMR' and MD.Division_Name = 'Dombivli' and TS.SubDiv_DESC = 'THANE DIVISION STAFF' group by PB.BillPro_Year
2)select sum(PB.CONSN_QTY)Consumption,Count(*),PB.BillPro_Year from tbtrans_prowaterbill PB INNER JOIN MIDC_AREA MA ON PB.Area_cd = MA.Area_cd INNER JOIN MIDC_Division MD ON MA.Div_CD = MD.Division_CD INNER JOIN MIDC_Circle MC ON MD.Circle_CD = MC.Circle_CD INNER JOIN TBMST_SubDiv TS ON MA.SubDiv_CD = TS.SubDiv_CD INNER JOIN MIDC_Zone MZ ON MD.Zone_CD = MZ.Zone_CD INNER JOIN tbmst_consumer TC ON PB.cons_no = TC.Cons_No INNER JOIN TBMST_CONSTYPE TCT ON TCT.Cons_Type = TC.Cons_Type where pb.billpro_year = '2006' and MTR_Size = 15 and TCT.Cons_Type = '1A2' and MZ.Zone_Name = 'MUMBAI' and MC.Circle_NAME = 'MMR' and MD.Division_Name = 'Dombivli' and TS.SubDiv_DESC = 'THANE DIVISION STAFF' group by PB.BillPro_Year
Hello All.We have tested following SQL script from query analyzer:-- Script beginDECLARE @I int;SET @I = 1;WHILE @I < 10000000 BEGINSET @I = @I + 1;END-- Script endThe script was tested on the folowing PCs with following results:PC 1:Pentium 4:CPU: 1.6 MhzRAM: 256 MbHDD: 80 Gb (IDE)OS: Windows Advanced Server SP4SQL: Developer Edition SP4Result: Script execution time - 0:54PC 2:IBM X445, 4 XEON 3.0 Ghz, 4 Mb L3 CacheRAM: 8 GbRAID 5 with (SCSI) 15k disksOS: Windows 2000 Advanced server SP4SQL: Enterprise Edition SP 4 + FixResult: Script execution time - 2:19PC 3:DELL Power edge 1600, 2 XEON 2.40 Ghz,RAM: 1 GbRAID 1 with (SCSI) 10k disksOS: Windows 2003 Enterprise Edition SP1SQL: Enterprise Edition SP 4 + FixResult: Script execution time - 1:16NOTE 1:All PCs have latest hardware updatesNOTE 2:PC 1 - is local pc, with no external connectionsPC 2 - is server with some number of other, external connections;PC 3 - is server with a few external connections;I have following question: Is the above mentioned execution time isnormal for such script ?Also it would be very nice of You to run the script on Your SQL serversand inform me about execution time resultsThank You beforehand
Hello,I ran a query that I thought would take an hour, but instead took 14hours to run. The consequence was it bogged down our data warehouseand the overnight build was adversely impacted.Is there a local setting I can set to limit the execution time myquery will take? I dont want to have a server setting and impact otherqueries, just the one I am running.I know there will be people asking about the 14 hour build and what isit doing and so forth. I will address that but I also look to thesesituations as a learning opportunity.Thanks in advance.Rob
Currently I have around 4 million records. I have normalized the table the best I know how. I am using SQL Server 2008 r2 express running on a local instance. I am trying to create a view in the View designer. I run the query in query editor it takes just under a minute to run. When I run it in the view designer it times out in 30 sec.
If I use the following query for a Dataset and the execution takes a few seconds to show results
SELECT *
FROM dbo.ICParameter
WHERE (PatientID = @ID ) AND (LogTime > DATEADD(day, -1, GETDATE()))
ORDER BY LogTime
If I replace '99010200101' with @ID and enter '99010200101' when prompted for ID, the execution takes forever. Actually I have never got any results even after waiting for 10 minutes.
Hi all,I have a problem with a stored procedure.This stored procedure inserts around bout 500,000 records but when it is executed it takes about 15-16 hours to do so.The stored procedure is using a temporary table to do this and is also calling a function.Please let me know if there is a way to reduce the execution time.will a cursor help? Thanks, Anne.
i am currently in the process of moving a bunch of jobs into SSIS from another ETL tool. I would like to benchmark the two products against each other by comparing how long each step of an ETL process would take.
I see no way to do this in SSIS, there is the Progress tab but it doesnt list start/time and end/time. Plus I having loops and things which I want to know how long each iteration takes.
I have a query which contains 12 left outer join. I remove some of the joins that don't have parameters. The result is coming same but usually when we remove joining it should take less exec time but for me it is taking more time. What could be the reason?
I have a SP SPone. i have optimized that and kept it as SPone_Optimized. i would like to test the both SP's execution time to find out how best the optimized one fares.
i planned to test it as follows
declare @starttime datetime,@endtime datetime declare @count int=0 select @starttime=getdate() while(@i<10000) begin execute SPone_optimized @param='value1' end select @endtime=getdate() select datediff(ms,@stattime,@endtime) 'total_exec_time'
----- for the SP that is before optimize
declare @starttime datetime,@endtime datetime declare @count int=0 select @starttime=getdate() while(@i<10000) begin execute SPone @param='value1' end select @endtime=getdate() select datediff(ms,@stattime,@endtime) 'total_exec_time'
I have created 2 tables in a database which are mostly similar, the table1 will execute with more speed (take only less than or equal to 1 sec) but the table2 will take 4 or 5 secs to execute the query,moreover the similar datas was presented in both the tables. the eg:- query that i have executed is select max(c_code) from table1 and select max(c_code) from table2, the first one take less than 1 sec and the second one take more than 4 or 5 secs, also there is a procedure i hve written to update both the tables, and i got the time out error, if the table1 alone is updated using the procedure it is OK but the table2 alone is updated using the procedure the time out error will be shown, pls reply the reason for this problem as early as possible, it will be a grateful if anybody reply for this trouble?
The format of execution time is as below: 6/17/2008 4:43:07PM. Is it possible to change the format of execution time into the following format: 17/06/2008 16:43:07 ???? Thanks....
On SSMS 9.00.3042.00, any query that runs longer than 10 minutes getsthe following error message:The statement has been terminated.Msg -2, Level 11, State 0, Line 0Timeout expired. The timeout period elapsed prior to completion ofthe operation or the server is not responding.I have changed the setting for Execution time-out to 0 in Tools-
I have a query, rather complex one to deal with more than 1 million rows, used to run 40 minutes in SQL Server 2000 in query analyzer. Now, it has been 10 hours in SQL Server 2005 in management studio. And still has not finished yet! Anything can go wrong here. Basically nothing changes, except for I have my server upgrade from SQL Server 2000 to SQL Server 2005. Seems something is wrong crazy in SQL Server 2005. Any suggestions?
HI, I have an interesting situation. I have created a stored procedure which has a select union query and it accepts some parameters. When I execute this procedure it takes 8 minutes. When I copy the script in stored procedure and run it directly in Query Analyzer it takes 2 1/2 minutes?? Same numbers of rows are returned either way in the result set with about 13,000.
I cannot figure this out and it is almost the same thing except that in Query Analyzer I declare the parameters variables and its values?
