Sp Execution
Dec 7, 2001Hi!
I need to calculate how many times stored procedure executes and keep the information for several months. What is the best way to do it if I cannot use global variables.
Thank you,
Elena.
ADVERTISEMENT
Static Variables In A SQLCLR Stored Proc Seem To Get Reused From Execution To Execution Of The Sp
Aug 23, 2007after moving off VS debugger and into management studio to exercise our SQLCLR sp, we notice that the 2nd execution gets an error suggesting that our static SqlCommand object is getting reused from the 1st execution (of the sp under mgt studio). If this is expected behavior, we have no problem limiting our statics to only completely reusable objects but would first like to know if this is expected? Is the fact that debugger doesnt show this behavior also expected?
View 4 Replies View RelatedExecution Plans &<&> Proportionate Execution Times
Dec 7, 2005Hi I am slowly getting to grips with SQL Server. As a part of this, I have been attempting to work on producing more efficient queries. This post is regarding what appears to be a discrepancy between the SQL Server execution plan and the actual time taken by a query to run. My brief is to produce an attendance system for an education establishment (I presume you know I'm not an A-Level student completing a project :p ). Circa 1.5m rows per annum, testing with ~3m rows currently. College_Year could strictly be inferred from the AttDateTime however it is included as a field because it a part of just about every PK this table is ever likely to be linked to. Indexes are not fully optimised yet. Table:CREATE TABLE [dbo].[AttendanceDets] ([College_Year] [smallint] NOT NULL ,[Group_Code] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,[Student_ID] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,[Session_Date] [datetime] NOT NULL ,[Start_Time] [datetime] NOT NULL ,[Att_Code] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY]GO CREATE CLUSTERED INDEX [IX_AltPK_Clust_AttendanceDets] ON [dbo].[AttendanceDets]([College_Year], [Group_Code], [Student_ID], [Session_Date], [Att_Code]) ON [PRIMARY]GO CREATE INDEX [All] ON [dbo].[AttendanceDets]([College_Year], [Group_Code], [Student_ID], [Session_Date], [Start_Time], [Att_Code]) ON [PRIMARY]GO CREATE INDEX [IX_AttendanceDets] ON [dbo].[AttendanceDets]([Att_Code]) ON [PRIMARY]GOALL inserts are via an overnight sproc - data comes from a third party system. Group_Code is 12 chars (no more no less), student_ID 8 chars (no more no less). I have created a simple sproc. I am using this as a benchmark against which I am testing my options. I appreciate that this sproc is an inefficient jack of all trades - it has been designed as such so I can compare its performance to more specific sprocs and possibly some dynamic SQL. Sproc:CREATE PROCEDURE [dbo].[CAMsp_Att] @College_Year AS SmallInt,@Student_ID AS VarChar(8) = '________', @Group_Code AS VarChar(12) = '____________', @Start_Date AS DateTime = '1950/01/01', @End_Date as DateTime = '2020/01/01', @Att_Code AS VarChar(1) = '_' AS IF @Start_Date = '1950/01/01'SET @Start_Date = CAST(CAST(@College_Year AS Char(4)) + '/08/31' AS DateTime) IF @End_Date = '2020/01/01'SET @End_Date = CAST(CAST(@College_Year +1 AS Char(4)) + '/07/31' AS DateTime) SELECT College_Year, Group_Code, Student_ID, Session_Date, Start_Time, Att_Code FROM dbo.AttendanceDets WHERE College_Year = @College_YearAND Group_Code LIKE @Group_CodeAND Student_ID LIKE @Student_IDAND Session_Date <= @End_DateAND Session_Date >=@Start_DateAND Att_Code LIKE @Att_CodeGOMy confusion lies with running the below script with Show Execution Plan:--SET SHOWPLAN_TEXT ON--Go DECLARE @Time as DateTime Set @Time = GetDate() select College_Year, group_code, Student_ID, Session_Date, Start_Time, Att_Code from attendanceDetswhere College_Year = 2005 AND group_code LIKE '____________' AND Student_ID LIKE '________'AND Session_Date <= '2005-11-16' AND Session_Date >= '2005-11-16' AND Att_Code LIKE '_' Print 'First query took: ' + CAST(DATEDIFF(ms, @Time, GETDATE()) AS VarCHar(5)) + ' milli-Seconds' Set @Time = GetDate() EXEC CAMsp_Att @College_Year = 2005, @Start_Date = '2005-11-16', @End_Date = '2005-11-16' Print 'Second query took: ' + CAST(DATEDIFF(ms, @Time, GETDATE()) AS VarCHar(5)) + ' milli-Seconds'GO --SET SHOWPLAN_TEXT OFF--GOThe execution plan for the first query appears miles more costly than the sproc yet it is effectively the same query with no parameters. However, my understanding is the cached plan substitutes literals for parameters anyway. In any case - the first query cost is listed as 99.52% of the batch, the sproc 0.48% (comparing the IO, cpu costs etc support this). BUT the text output is:(10639 row(s) affected) First query took: 596 milli-Seconds (10639 row(s) affected) Second query took: 2856 milli-SecondsI appreciate that logical and physical performance are not one and the same but can why is there such a huge discrepancy between the two? They are tested on a dedicated test server, and repeated running and switching the order of the queries elicits the same results. Sample data can be provided if requested but I assumed it would not shed much light. BTW - I know that additional indexes can bring the plans and execution time closer together - my question is more about the concept. If you've made it this far - many thanks.If you can enlighten me - infinite thanks.
View 10 Replies View RelatedExecution Procedure Stored During Execution Of The Report .
Aug 3, 2007
Hello :
How to execute a procedure stored during execution of the report, that is before the poster the data.
Thnak you.
Execution In Scheduled Job Vs Direct Execution
Nov 29, 2004Here's my case, I have written a stored procedure which will perform the following:
1. Grab data from a table using cursor,
2. Process data,
3. Write the result into another table
If I execute the stored procedure directly (thru VS.NET, or Query Analyser), it will run, but when I tried to execute it via a scheduled job, it fails.
I used the same record, same parameters, and the same statements to call the stored procedure.
Any idea?
Actual Execution Plan Vs Estimated Execution Plan
Jul 7, 2006The benefit of the actual execution plan is that you can see the actual number of rows passing through each step - compared to the estimated number of rows.But what about the "cost percentages" ?I believe I've read somewhere that these percentages is still just an estimate and is not based on the real execution.Does anyone know this and preferable have a link to something that documents it?Thanks
View 1 Replies View RelatedCan I Roll Back Certain Query(insert/update) Execution In One Page If Query (insert/update) In Other Page Execution Fails In Asp.net
Mar 1, 2007Can I roll back certain query(insert/update) execution in one page if query (insert/update) in other page execution fails in asp.net.( I am using sqlserver 2000 as back end)
scenario
In a webpage1, I have insert query into master table and Page2 I have insert query to store data in sub table.
I need to rollback the insert command execution for sub table ,if insert command to master table in web page1 is failed. (Query in webpage2 executes first, then only the query in webpage1) Can I use System. Transaction to solve this? Thanks in advance
SQL EXECUTION
Jan 29, 2007As per coding below. Who's can show me how to execute this coding to my sql data source? I already setting my data source, but i can't call my data source to my table. I wanna show the data in my data grid when i type ID Number in textbox but when i click the command button, no record appear. Is it got something error in my coding or no connection between my sql data source? Plssss....help me.....I'm still new in asp.net and no experience with that.
Dim strSQL As String = "SELECT * FROM anggota"
Dim strWhere As String = String.Empty
Dim strCriteria As String() = txtIC.Text.Split(" ")
For Each str As String In strCriteria
If Not String.IsNullOrEmpty(str) Then
If Not String.IsNullOrEmpty(strWhere) Then strWhere &= "AND"
strWhere &= String.Format(" (Column LIKE ' %" &{0}& " % ') ", str)
' %" & myvar & " % '
End If
Next
If Not String.IsNullOrEmpty(strWhere) Then
strSQL &= String.Concat(" WHERE", strWhere)
End If
MsgBox(strSQL)
Execution Of SP
Jul 23, 2004When a SP errors with a deadlock, does the SP stop executing or does it fail the @@error and continue through the next steps in the stored proc?
View 3 Replies View RelatedDTS Execution From ASP
Feb 17, 2006Hello,
I need to execute a package from a ASP page.
SQL server and Webserver are on different machines.
It is possible?
If yes How?
Thank you
Bcp Execution
Aug 26, 2004Hai..
I have two systems. SQL server 2000 is installed in one system (SERVER ) and the client tools are installed in a seperate system (Client)
My question is from which system should i have to execute the bcp command line utility, from the server or from the client. ?
Thanks in advance...
with regards
Sudar
Sp Execution
Dec 13, 2005Is there a way to tell if a stored proc has ever been executed? I've taken over another system where the developers backed up there procs by creating them with a different name. Of course there's no pattern to the naming convention either. :mad:
View 11 Replies View RelatedDTS Execution
Mar 29, 2007I'm having trouble with dts. I used the dtswizard to create a dts package, yet when I use dtsrun with the package name, I get a message saying that the package cannot be found. I've even copied the .dtsx file into the same folder as dtsrun and still gotten the same message. Can someone shed some light on what I might be messing up. Btw, I'd love to run the dtsrunui, but apparently that file wasn't part of my distribution.
View 3 Replies View RelatedSP Execution
Jan 15, 2004Can i write a trigger to execute a SP When one row is inerted into the table.If yes,can anyone let me know the syntax.
Thanks.
Job Execution
May 24, 2007Greetings.
We generate table exports on a SQL Server 2005 instance at irregular intervals. Often when exports are required we have a number of them that need to be run. We've found that the exports job run sequentially. Is there a way of simultaneously executing the jobs ?
Thanks.
alan
UDF EXecution
Nov 20, 2007
HI all
How can i execute UDF's in MS SQL SERVER 2005 can u any body plz explain me
Regards
subu
Execution ID
Jan 11, 2006Hi ,
For how long is the value of Execution ID valid? Is it valid after the session also?
Can i store the value of Execution ID in some place and use it after some time/day to get the last excution done on the report?
Thanks
Execution Log
Apr 24, 2008
Hi,
I just installed SQL Server 2005 RS Samples to be able to create and use the Execution Log as it says on
http://technet.microsoft.com/en-us/library/aa964131.aspx#rsmnrptexpf04
but when it comes to the point that I just go and open the Solution named "Execution Log" I don't find it! I don't have it at all and neither the reports which are shown in the solution in the above link such us "Todays Reports.rdl", "Report Size.rdl" or "Longest Running Reports.rdl".
WHy is that? Is it a bug in the Samples file?
Please help me!
If someone has the reports or the entire solution I would appreciate if I could get it!
Thanks
Enrico
Job Execution
Sep 12, 2007I've got a scheduled job that runs a package every couple of minutes, and I also have a method in an application that calls a stored proc that can also execute the same scheduled job. My question is what will happen if I call the stored proc to execute the job at the same time that the job is executing on its regular schedule. Is it possible that the an instance of the package will execute in parallel?
View 3 Replies View RelatedExecution Time
Jan 10, 2008Hello 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.
Execution Plans
Nov 16, 2001I have two schematically identical databases on the same MS SQL 2000 server. The differences in the data are very slight. Here is my problem: the identical query has totally different execution plans on the different databases. One is (in my opinion) correct, the other causes the query to take 60 times as long. This is not an exaggeration, on the quick DB the query takes 3 seconds, on the other DB it takes 3 minutes. I have tried the following to help the optimizer pick a better execution plan on the slow db:
rebuild the indexes
dbcc indexdefrag
update statistics
I CAN put in a hint to cause the query to execute faster, but my employer now knows about the problem and he (and I) want to know WHY this is happening.
Any ideas would be greatly appreciated.
Thanks.
-Scott
Execution Time Of An Sp.
Jan 18, 2002Is 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.
DTS Package Execution
May 15, 2001I have already created package which loads a text file to database.I wanted to execute that package based on the availability of the file using Visual Basic,Perl or VBSCRIPT whichever is easier.Please advise.Thanks
View 4 Replies View RelatedDTS Execution Order
Jun 5, 2001I want a task to run last after all of the other task have run. How do I ensure that this task will run last?
View 3 Replies View RelatedDynamic SQL Execution
Dec 21, 2000Hey guys,
I've got a problem here. I gave execute permission to an
user on a particular stored proc which has simple select queries.
(The tables have no select permissions for that user.) Now, the user was able to run it fine and got the results. When hdynamically creates sql queries inside the proc (Based on the input parameters) sqlserver is checking for the permissions on the tables and got permission denied message.
Is there any solution to avoid this problem?..please let me know
and i'd appreciate that..
thank you very much.
DTS Package Execution - When Is It Done?
Oct 30, 2000I wrote a stored procedure that calls a DTS package that imports a file into a SQL Server table, then executes some commands on that table. How can I tell that the DTS package has completed, so that I can continue with the next command? Here is my code:
-- Run the DTS package
EXEC @Result = msdb..sp_start_job @job_name = 'MyJob'
WAITFOR DELAY '00:00:02'
-- Call the table that the package created
SELECT * FROM <imported_table> WHERE ...
I had to insert a WAITFOR statement to make this work. Any other suggestions as to how to know when to continue?
Thank you.
Dts Vs. Agent Pkg Execution
Oct 16, 2000Execution of a scheduled package fails however, direct execution of the package is successful. The package owner and job owner are the local admin account under which SQLAgent and SQLService are starting.
Ideas?
Dynamic Execution
Oct 2, 2002Hello,
How do I get quiry result in variable? exp. -
DECLARE @varVariable varchar(50),
@varFld varcahr(50),
@varSel varchar(1000)
SELECT @varVariable = (SELECT Field1 FROM Table1 WHERE Field2 = 1234)
This will give value of Field1 in @varVariable
But how I will get this value in Dynamic Execution?
It will work? -
SET varFld = 'Field1'
SELECT @varVariable = (SELECT @varSel = 'SELECT '+@varFld+' FROM Table1 WHERE Field2 = 1234')
EXEC (@varSel)
PRINT @varVariable
************************************
This will work? what is the alternative?
help please.
ANB
Bad Execution Plan
Aug 21, 2001I'm new to SQL server but familiar enough with databases to know this doesn't seem right.
Here's the situation:
I have a table with real estate property information. There are about 650,000 rows in it. I have a nonclustered non-unique index on the city where the property is located. There are about 40 unique values in this index.
I do a simple query like:
SELECT city,address from propinfo where city= 'CARLSBAD'. The query will return about 4,000 rows. The problem is that the execution plan that it chooses is to do a full table scan. I.E. Even though there is an index on City, it chooses to look through 650,000 rows rather than do an index seek. Something sounds inefficient here. BTW, this happens in both SQL 7 and SQL 2000. Can anyone explain why this happens? I've got to think that SQL Server is more efficient here.
Schedule DTS For Later Execution
Aug 9, 2002When I create a package and schedule it for later execution
the job is failed at the time of execution. If I choose the Execute Package option for the package which is already exist, or chose the Run Immediately option at the time I create the package it works successfully. Is it a time problem?
I would appreciate your help.
Thanks.
Denny.
How To Make A Certain Log During SP Execution?
Sep 28, 2001hi all,
Now i want to log some information (e.g.time,count...)during SP execution,how can i do it in Sql?
Thanks
DTS Execution Using The Scheduler
Feb 6, 2001I have several DTS packages that I run in steps thru the scheduler. The only one that is giving me grief is one that extracts large amounts of data from an Oracle database. Any extract that is less than 30,000 rows is fine but more than that, only the last completed batch that keeps the rows returned under 30,000 will write out to either a new table or to a file. Not only do the rows not return, but it holds open any file that is being used (the one it is writing out to as well as the errorlog file.) I have changed batch sizes, max error counts, timeouts and every other variable I could find to no avail...The job immediately 'completes' its execution with a good return code but that appears only to be 'I sent your request off to Oracle' which releases the next step even though the data hasn't been returned (an issue I will tackle once I get all the data.) My Network folks and Oracle DBA are telling me that there are no size restrictions for passing the data back.
Any ideas where I have strayed off the path? Thanks for your help.
Marianne G. Burhans
Sr. Database Administrator
Stopping Execution
Jul 13, 2004Quick question...
I was under the impression that RAISERROR stopped excution in my DTS SQL Task, but it really just passes right over it.
For example:
RAISERROR('test error',19,1) WITH LOG
CREATE TABLE delete_me (data int)
Even though I rasie the error, the table still gets created. Any ideas on how I stop the task from going to the next line?