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.
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.
View 5 Replies View RelatedHello, everyone:
For performance issue, I need to catch the stored procedure execution time. Any suggestion will be appreciated. Thanks.
ZYT
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?
Any feedback would be appreciated!
Thanks in advance...
I have a Stored Procedure (SP) that creates the data required for areport that I show on a web page. The SP does all the work and justreturns back a results set that I dump in an ASP.NET DataGrid. The SPtakes a product area and a start and end date as parameters.Here are the basics of the SP.1.Create temp table to store report results, all columns are createdthat will be needed at this point.2.Select products and general product data into the temp table.3.Create a cursor that loops through all the products in the temptable, running a more complex query with each individual product.4.The results of that query are updated on the temp table based on thecurrent product of the cursor.5.A complex "totals" query is run and the results from that areinserted into the temp table as the last 3 rows.In all we are talking about 120 rows in the temp table with 8 columnsthat are mostly numbers.I originally wrote this report SP about a month ago and it worked fine,ran in about 10 - 20 seconds based on server traffic and amount ofdata in the temp table. For the example I'm running there are the120 products.Just yesterday the (SP started timing out and when I ran the SPmanually from Query Analyzer (QA) (exec SP_NAME ... ) with the sameparameters as it was getting in the code it took 6 minutes to complete.I was floored. I immediately copied the SQL out of the SP and pastedinto another QA window, changed the variables to be hard coded valuesand ran it. It completed in 10 seconds.I'm really confused now. I ran a Profiler on the 2 when I ran themagain. The SQL code in QA executed again in ~10 seconds with 65,000reads. When the SP finished some 6 minutes later it had completed witthe right results but it needed 150,000,000 reads to do its job.How can the exact same SQL code produce such different results (time,disk reads) based on whether its in a SP or just run from QA but stillgive me the exact same output. The reports both look correct and havethe same numbers of rows.I asked my Sys Admin if he had done anything to anything and he saidno.I've been reading about recompiles and temp table indexes and allkinds of other stuff that could possibly be affecting it but havegotten nowhere.Any ideas are appreciated.
View 5 Replies View RelatedHi ,
I ma using sql server 2005.I have a bunch of statements of sql and i have created a stored procedure for those. When i execute i found that there is lot's of difference between execution time of stored procedure and direct sql in query windows.
can anyone help me to optimize the execution time for stored prcedure even stored prcedure is very simple.
I have used sql server 2000 and i am new in sql server 2005.
View 8 Replies View Related
Hello :
How to execute a procedure stored during execution of the report, that is before the poster the data.
Thnak you.
Has anyone encountered cases in which a proc executed by DTS has the following behavior:
1) underperforms the same proc when executed in DTS as opposed to SQL Server Managemet Studio
2) underperforms an ad-hoc version of the same query (UPDATE) executed in SQL Server Managemet Studio
What could explain this?
Obviously,
All three scenarios are executed against the same database and hit the exact same tables and indices.
Query plans show that one step, a Clustered Index Seek, consumes most of the resources (57%) and for that the estimated rows = 1 and actual rows is 10 of 1000's time higher. (~ 23000).
The DTS execution effectively never finishes even after many hours (10+)
The Stored procedure execution will finish in 6 minutes (executed after the update ad-hoc query)
The Update ad-hoc query will finish in 2 minutes
Background: We have SQL Server 2005 x64 running on a quad-core (dual dual-core) machine with 16GB of RAM. The database is about 10GB in size and we execute around a million stored procedures a day on it. Our application uses about 1000 different stored procedures on this machine. The application is a transactional B2B web-app with about 2000 users.
The problem we have is a really odd one that I can't seem to find much information on. We have a small number (3-4) of stored procedures that's exibiting this problem.
The stored proc in question takes on average 100ms CPU time to execute. It's a fairly complex stored proc, about 300 lines long, 6-7 select statements and it uses temp tables. No updates / inserts except for on the temp tables. It's executed about 5000 times per day. About once a week, though, execution times will suddenly jump up to 3000 ms average. This happens randomly during the day, although it seems to happen more often on Monday mornings (the DB is mostly unutilized over the weekend)
To fix this, I force the DB to recalculate the execution plan by adding / removing (depending what I did last time around) the line 'set arithabort on' at the top of the stored procedure. I have no idea why this works, but it does. Within seconds of changing it, the stored proc execution time will go back to it's normal range of 60-150ms.
I've tried setting the execution plan of the stored procedure but I can't get it to work - the execution plan is very long and I don't know how to debug the error I get.
What is happening? This happens with a couple of stored procedures - usually the more complex ones. Has anyone seen anything like this?
Dear group,is it possible in SQL-Server to see when a stored procedure wasexecuted ?I would say it is only possible with some traces but not with thestandard settings.For a short answer on that matter i'd be thankful.RegardsUli
View 4 Replies View RelatedI have a stored proc that is executing in 2 sec on production and test database. It is taking more than a min on dev environment.
I have verified sqlserver version is same on both of the server.Prod is running on 2012Sp1 however dev don't have sp1. I am downloading it.
Both are 64bit, has same collation and compatibility level.I have confirmed that sp on both servers has same execution plan. I have reset and import stats from prod too.
hi
how can i execute the stored procedure statements in asp with c#.net ?
Is there a way that a stored procedure (or a SQL script) can be forced to execute
when a user logs on?
1) Is it possible to run stored procedures at specified intervals without
using the job system (through T-SQL)? I want the schedule to be
independent of the MSDB database in case of temporary failures, etc.
2) Would extended stored procedures be helpful in this scenario?
Thanks
ziggy
i have a stored procedure that builds a dynamic insert statement & inserts data into a table. Now when I execute the
sp manually with a 'exec sptest parm1,parm2', it runs fine & inserts the data in the table. But when this sp is called from within a .net application,it prepares the insert statement but does not actually insert the record in the table. It comes back with a RPC: Completed so it seems like it completed but it does not insert the record in the table. Also just after the RPC:Completed, it throws an ATTENTION with nothing in the text data. I am confused on whats going on here. The definition of ATtention
in the event class implies that the query has been cancelled or it timed out. But we have no timeout on the sql server side. The application developer says there is no timeout on the application side (i dont totally believe that). so what else could
cause that Attention? There is nothing in the error log as well. Also why does the trace come back with a RPC:Completed when the stored procedure did NOT insert any data? Does the RPC:Complete only mean that the RPC completed - irrespective of success or failure? If the sp failed or had an error will it still come back with a rpc:completed?
Any thoughts are appreciated...
hi
i want to know the execution status of a stored procedure . That is i want to know whether the stored procedure was executed succesfully or not.If not i want to get the error message
I am looking for a way to count the number of times a stored procedure on the database has been executed over let's say over a period of time(month, years, etc).
Is there a system stored procedure or a system table that stores that information.
I am struggling to find some information about this topic
Thanks for the help
Hi. When SqlServer executes a procedure (any type: select, update, insert) after it´s executed can I get a default status for this executed procedure, like a return bool value from SqlServer as true for successfull and false for failed to execute?
»»» Ken.A
Hi,There is a stored procedure which runs through job.It is calling to other stored procedure and other stored procedures arecalling to another .. so on (approx 12-15 sp in batch)Problem:Sometime it does not execute properly. (approx very rare... once in 500execution or sometime on new site/database)I want to know the reason for it.If anybody have faced the similar problem.Please tell the possible causes and possible solutions.Thanks in Adv.T.S.Negi(MIND)
View 1 Replies View Relatedhi guys
I am having problems running a stored procedure where i am using two input parameters
my stored procedure is as follows
ALTER procedure [dbo].[enterdhbnameDhbService]
(
@dhb_service char, @dhbname char
)
as
SELECT dbo.DHBMappingTable.[DHB Name], dbo.Agency.DHB_service, dbo.PurchaseUnitMappingTable.PU,
SUM(dbo.[NMDS Data for IDF Report].[Number of caseweighted discharges]) AS Expr1, dbo.AdmissionMappingTable.Admission
FROM dbo.DomicileCodes INNER JOIN
dbo.[NMDS Data for IDF Report] ON dbo.DomicileCodes.[Domicile code] = dbo.[NMDS Data for IDF Report].[Domicile Code] INNER JOIN
dbo.PurchaseUnitMappingTable ON dbo.[NMDS Data for IDF Report].[Purchase Unit] = dbo.PurchaseUnitMappingTable.PU INNER JOIN
dbo.AdmissionMappingTable ON
dbo.[NMDS Data for IDF Report].[Admission Type Description] = dbo.AdmissionMappingTable.[Admission Type Description] INNER JOIN
dbo.Agency ON dbo.[NMDS Data for IDF Report].[Agency Name] = dbo.Agency.Agengy INNER JOIN
dbo.DHBMappingTable ON dbo.DomicileCodes.[DHB area] = dbo.DHBMappingTable.[DHB Code]
WHERE (dbo.[NMDS Data for IDF Report].[Financial Year] = '20062007')
GROUP BY dbo.DHBMappingTable.[DHB Name], dbo.Agency.DHB_service, dbo.PurchaseUnitMappingTable.PU, dbo.AdmissionMappingTable.Admission
HAVING (dbo.Agency.DHB_service = @dhb_service) and
AND (dbo.DHBMappingTable.[DHB Name] = @dhbname )
The values of " @dhb_service" and "@dhbname" need to be entered when the stored procedure is executed. Now when I execute the stored procedure through the following statement:
exec enterdhbnameDhbService
@dhb_service = 'canterbury' ,@dhbname = 'south canterbury'
SQL does not give me any results, only empty table gets displayed. I have checked the combination.. This combination does exist in my table
pls help guys
Hi,
I created the an assembly and stored procedure using the following steps:
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO
alter database TEST set trustworthy on
CREATE ASSEMBLY ClrWebServices
FROM 'D:Dataclr_4.dll'
WITH PERMISSION_SET = UNSAFE;
GO
CREATE ASSEMBLY [ClrWebServices.XmlSerializers]
FROM 'D:Dataclr_4.XmlSerializers.dll'
WITH PERMISSION_SET = SAFE;
GO
CREATE PROCEDURE InsertLocation(@city nvarchar(200),
@state nvarchar(200), @country nvarchar(200))
AS
EXTERNAL NAME ClrWebServices.StoredProcedures.clr_4
GO
After this when i am trying to execute the procedure InsertLocation
EXEC InsertLocation 'Sarasota','Florida','USA'
I am getting the following exception..
Msg 6522, Level 16, State 1, Procedure InsertLocation, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "InsertLocation":
System.Net.WebException: Unable to connect to the remote server ---> System.Net.Sockets.SocketException: No connection could be made because the target machine actively refused it
System.Net.Sockets.SocketException:
at System.Net.Sockets.Socket.DoConnect(EndPoint endPointSnapshot, SocketAddress socketAddress)
at System.Net.Sockets.Socket.InternalConnect(EndPoint remoteEP)
at System.Net.ServicePoint.ConnectSocketInternal(Boolean connectFailure, Socket s4, Socket s6, Socket& socket, IPAddress& address, ConnectSocketState state, IAsyncResult asyncResult, Int32 timeout, Exception& exception)
System.Net.WebException:
at System.Net.HttpWebRequest.GetRequestStream()
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
at ClrWebServices.Test.TerraService.GetPlaceFacts(Place place)
at StoredProcedures.GetLocationImage(SqlString city, SqlString state, SqlString country)
at StoredProcedures.clr_4(SqlString city, SqlString state, SqlString country)
I searched in the google and found one answer in http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=76222&SiteID=1 suggesting use
ALTER ASSEMBLY ClrWebServices WITH PERMISSION_SET=EXTERNAL_ACCESS
when i executed i got the exception
Msg 6213, Level 16, State 1, Line 1
ALTER ASSEMBLY failed because method "add_ConvertLonLatPtToNearestPlaceCompleted" on type "ClrWebServices.Test.TerraService" in external_access assembly "clr_4" has a synchronized attribute. Explicit synchronization is not allowed in external_access assemblies.
Can any body help on this?
Thanks in advance....
Hello,
I have a big problem with slow execution of stored procedure in SQL Server 2005 but I really don't understand the reason. I have a database with large table (about 400 million rows) and simple stored procedure to get data from that table (one select statement to select time and value columns).
Strange thing is that if I call that stored procedure from .net application (native SqlDataProvider) it takes about 6 seconds to execute but if I call the same procedure with the same parameters from within SQL Server Management Studio it takes only 25 milliseconds to execute!
I've noticed that from .net, procedure is called with binary data and in Management Studio sql script is executed so I've copied/pasted the script from Management Studio to .net code and again the same thing happens (6 seconds from .net and 25ms from Management Studio). I traced executions with SQL Profiler and everything seems to be identical for both applications except it takes much longer time for .net application.
Both SQL Server Management Studio and .net application are on the same machine and SQL Server is on another.
This is the query that when executed in Management Studio takes 25ms:
EXEC [dbo].[GetRawData] @pcu = N'DV_ZERJ_HEV1',@tag = N'MJERENO',@from = N'20070629 07:00:00',@to = N'20070629 08:00:00'
This is the same query in .net application code that takes 6 seconds to execute:
sqlCommand = new SqlCommand("EXEC [dbo].[GetRawData] @pcu = N'DV_ZERJ_HEV1',@tag = N'MJERENO',@from = N'20070629 07:00:00',@to = N'20070629 08:00:00'",sqlConnection);
sqlReader = sqlCommand.ExecuteReader();
At first I thought that Management Studio somehow caches results but if I change parameters of stored procedure it always takes less than 30ms to execute.
I really don't understand this. Please, help!
I want to simulate 100 users simultaneously executing a procedure in sql server (best example would be 100 users using one report with different parameters in ssrs at the same time ). can i do that ?
View 3 Replies View RelatedHello friends!
I have one query regarding execution of SSIS package through Stored Procedure.
I have created SSIS package which extract data from database and put that into various text files.Here I am using two global variables one is for Department ID and another is path where I wanna to place my text files as per departments.When I ran it through command prompt it works fine but now I want that dtsx package to run from stored procedure with same input parameters
when i searched on line i got this solution
Declare @FilePath varchar(2000)
Declare @Filename varchar(1000)
Declare @cmd varchar(2000)
set @FilePath = 'C:setupSSIS PackagesSSIS Package File Extract DataSSIS Package File Extract Datain'
set @Filename = 'DataExtract.dtsx'
select @cmd = 'DTExec /F "' + @FilePath + @Filename + '"'
print @cmd
exec master..xp_cmdshell @cmd
but when i execute it i got error like
Source: {8A27E8DF-051B-4F6B-9538-85BED1F161D8}
Description: Unable to load the package as XML because of package does not have a valid XML format. A specific XML parser error will be posted.
End Error
Error: 2007-02-22 11:31:37.32
Code: 0xC0011002
Source: {8A27E8DF-051B-4F6B-9538-85BED1F161D8}
Description: Failed to open package file "C:setupSSIS PackagesSSIS Package File Extract DataSSIS Package File Extract DatainDataExtract.dtsx" due to error 0x80070003 "The system cannot find the path specified.". This happens when loadin
g a package and the file cannot be opened or loaded correctly into the XML document. This can be the result of either providing an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format.
End Error
Could not load package "C:setupSSIS PackagesSSIS Package File Extract DataSSIS Package File Extract DatainDataExtract.dtsx" because of error 0xC0011002.
Description: Failed to open package file "C:setupSSIS PackagesSSIS Package File Extract DataSSIS Package File Extract DatainDataExtract.dtsx" due to error 0x80070003 "The system cannot find the path specified.". This happens when loading a
package and the file cannot be opened or loaded correctly into the XML document. This can be the result of either providing an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format.
And also I am not understand where i should pass my two input parameters which I used in SSIS package variables???????
Please help me out
Thanks
i have a few stored procedures called by an application that i would like to create execution plans for every time they are run. is there a way to do this? or are execution plans only done through the Sql Server Query Editor? these queries make comparisons between a temp table and a master table and setting up the data for the tables is time consuming. so id like to automate it by setting the store procedure to create the execution plan at runtime.
View 2 Replies View RelatedI think not. Microsoft says it is possible: one for parallel and one for serial execution. Don't believe that's possible for a stored procedure to change execution plans on the fly. Have an on-going problem with timeout occurring with an application and narrowed the culprit to a stored procedure. I couldn't find any obvious issues database wise, no locks, etc. so I recompiled (altered) the sproc without making any changes and the issue cleared for a couple days.
It happened again to day, and so I recompiled (altered) the sproc and it went away again. No code changes to both application (so they say) and stored procedure. I ran the below code snippet to check for sprocs with multiple cached plans and the offending one came up on a short list. So, my question is, Is it one sproc per query plan or can there be more than one. I understand the connection issues.
Code:
SELECT db_name(st.dbid) DBName,
object_schema_name(st.objectid, dbid) SchemaName,
object_name(st.objectid, dbid) StoredProcedure,
MAX(cp.usecounts) Execution_count,
st.text [Plan_Text]
INTO #TMP
[Code] .....
I'm trying to get the execution plan for a single stored procedurefrom Profiler. Now, I've isolated the procedure but I get allexecution plans. Any ideas on how to connect the SPIDs so that I onlyget the execution plan for the procedure I'm watching and not thewhole of the server?
View 4 Replies View RelatedIs it possible to check query execution plan of a store procedure from create script (before creating it)?
Basically the developers want to know how a newly developed procedure will perform in production environment. Now, I don't want to create it in production for just checking the execution plan. However they've provided SQL script for the procedure. Now wondering is there any way to look at the execution plan for this procedure from the script provided?
I am replicating a stored procedure execution, which builds and executes the following dynamic SQL command:
IF EXISTS (select * from MyDB..sysfiles sf (nolock) where name = 'MyDB_201201')
ALTER DATABASE [MyDB] REMOVE FILE [MyDB_201201]
IF EXISTS (select * from MyDB..sysfilegroups sfg (nolock)
where groupname = 'MyDB_201201' and sfg.groupname not in(
SELECT distinct fg.name file_group_name
[Code] ....
I can run this SP with no errors on both the publisher and the subscriber. However, when I try to replicate the execution of this SP, I get an error in replication monitor:
ALTER DATABASE statement not allowed within multi-statement transaction. (Source: MSSQLServer, Error number: 226)
How can I change my code to workaround this? Perhaps some explicit transactions?
I have the following execution of a global temporary stored procedure on a remote SQL 2000 server:
insert into targetTable
exec remoteServer.master.dbo.sp_MSforeachdb ' ', @precommand = 'exec ##up_fetchQuery'
This is an ugly duck query but it seems to work fine. when I try to directly execute the remote stored procedure such as with
insert into query_log exec remoteServer.master.dbo.##up_fetchQuery
I get execution error
Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure '##up_xportQueryLog'.
Database name 'master' ignored, referencing object in tempdb.
When I try
insert into query_log exec remoteServer.tempdb.dbo.##up_fetchQuery
I get
Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure '##up_xportQueryLog'.
Database name 'tempdb' ignored, referencing object in tempdb.
with
insert into query_log exec remoteServer..dbo.##up_fetchQuery
or
insert into query_log exec remoteServer...##up_fetchQuery
I get
Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure '##up_xportQueryLog'.
I guess the remote server has trouble resolving the name of the global temp stored procedure when its reference comes in as a remote stored procedure calls. Is there any way to directly call a global temp stored procedure from a remote server or do I need to stick with this goofy-looking work-around?
Dave
Hello, I have stored procedure that when executed it will check to see if a given name is found in the database, if the name is found, I would like to have it continue on to do its work, however if the name is not found, I would like it to raise an error and then stop execution at that point, however, the way it is currently working is, if the name is not found, it catches the error, raises it and then continues on and tries to do its work, which then it bombs out because it can't. I wasn't sure if there was a way to stop the execution of the procedure in the catch statement. I don't think I want to raise the error level to 20-25 because I don't want to drop the connection to the database per say, I just want to halt execution.
Here is a simple example of what I have:
Code Snippet
begin try
if not exists (select * from sys.database_principals where [name] = 'flea')
raiserror('flea not found', 16, 1)
end try
begin catch
declare @ErrorMessage nvarchar(4000);
declare @ErrorSeverity int;
select
@ErrorMessage = error_message(),
@ErrorSeverity = error_severity();
raiserror(@ErrorMessage, @ErrorSeverity, 1);
end catch
go
begin
print 'hello world'
end
At this point, if the user name (flea) is not found, I don't want it ever to get to the point of 'Hello World', I would like the error raised and the procedure to exit at this point. Any advice would be appreciated on how to best handle my situation!
Thanks,
Flea
I have created stored procedure to find out first word of the keyword. I am getting error below on execution:
"Must declare the scalar variable "@SubjectBeginning"."