Tracing UDFs
Jun 6, 2006How would I go about tracing UDF performance in profiler? I'd like to
specifically know the impact of the UDF without having to dig into the
execution plan of the statement containing it. Is this possible?
ADVERTISEMENT
Replicate SPs And UDFs
Aug 2, 2007Can changes made to replicated stored procedures (and User-defined Functions) on the Publisher update the Subscriber database?
If I ALTER a SP (or UDF) on Database1 with peer-to-peer Transaction Replication to Database2, will the alter replicate to the Subscriber(s)?
Thomas
ThomBeaux
Views, UDFs
Aug 15, 2006I know there is a lot of information already out there on this topic,but given the following scenario...--------------------------------------------------------------------------------------------------------------------------------------Create a view like so ( pardon the pseudo-code )...CREATE View vwContactAddressesSelect * FROM Contact INNER JOIN Address ON Contact.ContactID =Address.ContactIDAnd then do a sargable select from the view using a stored procedureCREATE STORED PROCEDURE spSelect_ContactAddresses@ContactID intASSelect * FROM vwContactAddresses WHERE ContactID = @ContactID--------------------------------------------------------------------------------------------------------------------------------------In my understanding, "vwContactAddresses" would be substituted with theactual SQL join statement when the view is accessed.So for the stored procedure in question an execution plan for"Select * FROM Contact INNER JOIN Address ON Contact.ContactID =Address.ContactID WHERE ContactID = @ContactID" would be cached.Correct?With regards to execution plan caching, is this not the same ascreating an inline UDF that takes parameters or just creating a storedprocedure that would do the join w/out the view reference?
View 3 Replies View RelatedComputed Columns Or UDFs
Jul 8, 2004Hi,
What is the difference between a computed column and a UDF?
Is a computed column the same as the "Formula" field under Design Table in Enterprise Manager?
Also, what is the proper syntax for the Formula field? Can I use regular SQL on it or is there more to it?
thanks,
Frank
UDFs GETWORDCOUNT, GETWORDNUM
Jun 17, 2005-- Author: Igor Nikiforov, Montreal, EMail: udfs@sympatico.ca
-- GETWORDCOUNT() User-Defined Function Counts the words in a string.
-- GETWORDCOUNT(@cString[, @cDelimiters])
-- Parameters
-- @cString nvarchar(4000) - Specifies the string whose words will be counted.
-- @cDelimiters nvarchar(256) - Optional. Specifies one or more optional characters used to separate words in @cString.
-- The default delimiters are space, tab, carriage return, and line feed. Note that GETWORDCOUNT( ) uses each of the characters in @cDelimiters as individual delimiters, not the entire string as a single delimiter.
-- Return Value smallint
-- Remarks GETWORDCOUNT() by default assumes that words are delimited by spaces or tabs. If you specify another character as delimiter, this function ignores spaces and tabs and uses only the specified character.
-- If you use 'AAA aaa, BBB bbb, CCC ccc.' as the target string for dbo.GETWORDCOUNT(), you can get all the following results.
-- declare @cString nvarchar(4000)
-- set @cString = 'AAA aaa, BBB bbb, CCC ccc.'
-- select dbo.GETWORDCOUNT(@cString, default) -- 6 - character groups, delimited by ' '
-- select dbo.GETWORDCOUNT(@cString, ',') -- 3 - character groups, delimited by ','
-- select dbo.GETWORDCOUNT(@cString, '.') -- 1 - character group, delimited by '.'
-- See Also GETWORDNUM() User-Defined Function
-- UDF the name and functionality of which correspond to the same built-in function of Visual FoxPro
CREATE function GETWORDCOUNT (@cSrting nvarchar(4000), @cDelimiters nvarchar(256) )
returns smallint
as
begin
-- if no break string is specified, the function uses spaces, tabs and line feed to delimit words.
set @cDelimiters = isnull(@cDelimiters, space(1)+char(9)+char(10))
declare @p smallint, @end_of_string smallint, @wordcount smallint
select @p = 1, @wordcount = 0
select @end_of_string = 1 + datalength(@cSrting)/(case SQL_VARIANT_PROPERTY(@cSrting,'BaseType') when 'nvarchar' then 2 else 1 end) -- for unicode
while dbo.CHARINDEX_BIN(substring(@cSrting, @p, 1), @cDelimiters, 1) > 0 and @end_of_string > @p -- skip opening break characters, if any
set @p = @p + 1
if @p < @end_of_string
begin
set @wordcount = 1 -- count the one we are in now count transitions from 'not in word' to 'in word'
-- if the current character is a break char, but the next one is not, we have entered a new word
while @p < @end_of_string
begin
if @p +1 < @end_of_string and dbo.CHARINDEX_BIN(substring(@cSrting, @p, 1), @cDelimiters, 1) > 0 and dbo.CHARINDEX_BIN(substring(@cSrting, @p+1, 1), @cDelimiters, 1) = 0
select @wordcount = @wordcount + 1, @p = @p + 1 -- Skip over the first character in the word. We know it cannot be a break character.
set @p = @p + 1
end
end
return @wordcount
end
GO
-- Author: Igor Nikiforov, Montreal, EMail: udfs@sympatico.ca
-- GETWORDNUM() User-Defined Function
-- Returns a specified word from a string.
-- GETWORDNUM(@cString, @nIndex[, @cDelimiters])
-- Parameters @cString nvarchar(4000) - Specifies the string to be evaluated
-- @nIndex smallint - Specifies the index position of the word to be returned. For example, if @nIndex is 3, GETWORDNUM( ) returns the third word (if @cString contains three or more words).
-- @cDelimiters nvarchar(256) - Optional. Specifies one or more optional characters used to separate words in @cString.
-- The default delimiters are space, tab, carriage return, and line feed. Note that GetWordNum( ) uses each of the characters in @cDelimiters as individual delimiters, not the entire string as a single delimiter.
-- Return Value nvarchar(4000)
-- Remarks Returns the word at the position specified by @nIndex in the target string, @cString. If @cString contains fewer than @nIndex words, GETWORDNUM( ) returns an empty string.
-- See Also
-- GETWORDCOUNT() User-Defined Function
-- UDF the name and functionality of which correspond to the same built-in function of Visual FoxPro
CREATE function GETWORDNUM (@cSrting nvarchar(4000), @nIndex smallint, @cDelimiters nvarchar(256) )
returns nvarchar(4000)
as
begin
-- if no break string is specified, the function uses spaces, tabs and line feed to delimit words.
set @cDelimiters = isnull(@cDelimiters, space(1)+char(9)+char(10))
declare @i smallint, @j smallint, @p smallint, @q smallint, @qmin smallint, @end_of_string smallint, @LenDelimiters smallint, @outstr nvarchar(4000)
select @i = 1, @p = 1, @q = 0, @outstr = ''
select @end_of_string = 1 + datalength(@cSrting)/(case SQL_VARIANT_PROPERTY(@cSrting,'BaseType') when 'nvarchar' then 2 else 1 end) -- for unicode
select @LenDelimiters = datalength(@cDelimiters)/(case SQL_VARIANT_PROPERTY(@cDelimiters,'BaseType') when 'nvarchar' then 2 else 1 end) -- for unicode
while @i <= @nIndex
begin
while dbo.CHARINDEX_BIN(substring(@cSrting, @p, 1), @cDelimiters, 1) > 0 and @end_of_string > @p -- skip opening break characters, if any
set @p = @p + 1
if @p >= @end_of_string
break
select @j = 1, @qmin = @end_of_string -- find next break character it marks the end of this word
while @j <= @LenDelimiters
begin
set @q = dbo.CHARINDEX_BIN(substring(@cDelimiters, @j, 1), @cSrting, @p)
set @j = @j + 1
if @q > 0 and @qmin > @q
set @qmin = @q
end
if @i = @nIndex -- this is the actual word we are looking for
begin
set @outstr = substring(@cSrting, @p, @qmin-@p)
break
end
set @p = @qmin + 1
if (@p >= @end_of_string)
break
set @i = @i + 1
end
return @outstr
end
GO
-- Is similar to the built-in function Transact-SQL charindex, but regardless of collation settings,
-- executes case-sensitive search
-- Author: Igor Nikiforov, Montreal, EMail: udfs@sympatico.ca
CREATE function CHARINDEX_BIN(@expression1 nvarchar(4000), @expression2 nvarchar(4000), @start_location smallint = 1)
returns nvarchar(4000)
as
begin
return charindex( cast(@expression1 as nvarchar(4000)) COLLATE Latin1_General_BIN, cast(@expression2 as nvarchar(4000)) COLLATE Latin1_General_BIN, @start_location )
end
GO
How To Recompile / Refresh UDFs ?
Mar 6, 2007Hi!I need to refresh an entire database.I can recompile SPs with sp_recompile (or DBCC FLUSHPROCINDB), andrefresh views with sp_refreshView, but I cannot find any way torefresh my user-defined functions (some of them are like views, withparameters).Any help appreciated :) !Ben
View 5 Replies View RelatedStrange Behaviour Of System UDFs
Jan 28, 2004Hi,
I have written an udf that checks for validations of an email address and returns 0 / 1 as per the validations. I came across an artical on MSDN that describes the steps to make my own system udf. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro01/html/sql01l1.asp
with help of that artical, I made my udf as a system udf. and now I can use it as follows.
select * , fn_isvalidemail(email) from #tp
now, I wanted to use this as a declarative check constraints in my tables. so I tried
create table testtable
( email_addr varchar (255) null CONSTRAINT CK_email_chk CHECK ( fn_isvalidemail(email) = 1) )
although this is syntax is logically correct, it does not get compiled
it gives me an error saying,
Server: Msg 195, Level 15, State 10, Line 2
'fn_isvalidemail' is not a recognized function name.
the strange part is that i made the function as another normal udf, It works in declarative constraints.
create table testtable
( email varchar (255) null CONSTRAINT CK_1p12 CHECK ( dbo.isvalidemail(email) = 1) )
Any thoughts on this ?
- Amit
UDFs Are Pre-compiled Or Not Like Stored Procedures?
Jun 9, 2015I have always learn that UDFs are not Pre-Compiled while Stored procedures are. But I always had this questions why are UDFs are not Pre-Compiled.
I searched online for the answer but dint get an concrete justification on the same.
Calling UDFs On Linked Server
Feb 25, 2008Hello,
How can I call UDFs on linked servers in SQL Server 2005?
More details:
I have some UDFs on Instance "VAXPServerA" in Database "DB1", for example "getSomething(number)".
It might look like this (for example):
----
CREATE FUNCTION [dbo].[getSomething] (
@theNumber int
)
RETURNS int AS
BEGIN
DECLARE @result int
SET @result = theNumber * 5
RETURN @result
END
----
I can call this function inside of the DB by typing:
----
SELECT dbo.getSomething(5)
----
I also can call the function from another DB on instance "VAXPServerA" by typing:
----
SELECT DB1.dbo.getSomething(5)
----
I have a second instance called "VAXPServerB", and "VAXPServerA" is a linked server in "VAXPServerB". I can do selects and stuff, it works fine.
But when I want to call the function...
----
SELECT [VAXPServerA].DB1.dbo.getSomething(5)
----
I obtain the next error:
Msg. 207, Level 16, State 1, Line 1
The column name 'VAXPServerA' is not valid.
Any hint?
Thanks in advance
David
Large Arrays, UDFs And The Text Datatype
Jul 23, 2005I have a bunch of SPs that all rely on a UDF that parses a commadelimitted list of numbers into a table. Everything was working fine,but now my application is growing and I'm starting to approach the 8000character limit of the varChar variable used to store the list.I would like to change the UDF only and avoid having to dig through allof my stored procedures. I was hoping to use the text datatype toallow for much larger lists, but I am unable to perform anymanipulations necessary to parse the list into a table. I have triedPATINDEX, but it alone is not enough without the text maniuplations andI don't think the sp_xml_preparedocument can be used in a UDF.Anyone with any thoughts on managing large arrays in t-sql?thanks,Matt Weiner
View 2 Replies View RelatedProblem With Nested Function Call (UDFs)
Jul 20, 2007Hello Folks,I encountered a problem with SQL server 2000 and UDFs.I have a scalar UDF and a table UDF where I would like the scalar UDFto provide the argument for the table UDF like in:SELECT*FROMtransaction_tWHEREtrxn_gu_id in (select get_trxns_for_quarter(get_current_quarter( GetDate() ) ))'get_current_quarter' returns an integer which is a GUID in a tablecontaining business quarter definitions, like start date, end date.'get_current_quarter' is a scalar UDF.'get_trxns_for_quarter' will then get all transctions that fall intothat quarter and return their GUID's in a table.'get_trxns_for_quarter' is a table UDF.This doesn't seem to work at all. Regardless whether I provide thenamespace (schema) calling the scalar UDF or not. Error message isjust different.Both functions operate correctly invoked un-nested.The whole expression does work fine if I turn 'get_trxns_for_quarter'into a scalar UDF as well, e.g. by returning just one trxn_gu_id withe.g. MAX() in a scalar datatype. But of course that's no good to me.It also works fine if I select the result of 'get_current_quarter'into a variable and pass that variable into 'get_trxns_for_quarter'.But that's no good to me either since then I cannot use the wholething embedded into other SELECT clauses.Both UDF's are non-deterministic but I couldnt see how that would havean impact anyway.Never mind the syntax on that example or anyhting, I tried all theobvious and not so obvious stuff and it really seems to come down tothe fact that one UDF is scalar and the other one is not. However, Idid not come across any type of information saying that this cannot bedone.Have you any ideas?Any help would be greatly appreciated.Carsten
View 6 Replies View RelatedBusiness Rules -&&> Using Lots Of UDFs && Views
Sep 7, 2007I am in the process of building my first "large scale" database system (after 15+ years of developing Windows Apps and Web Apps) - so I am very VERY "Green" when it comes to Database development & SQL et al.
A little context setting: I am building a multi-tier Statistical Analysis & Reporting system where the "end product" will be Reports created in Reporting Services. There are a ton of business rules that I am implementing in a Business Logic Tier (hidden from the "end user" by a Data Access Tier) comprised of SQL in the form of UDFs (scalar) and Views.
The question: I have been reading that UDFs cause a performance hit compared to things like in-line functions. Alot of the Rules (implemented as Scalar UDFs) build on each other so that the output of UDF #1 is used as input to UDF #2.
So far I am implementing the Business Logic as a hierarchy of Views (7 Views to be exact) with each view implementing multiple Rules; each Rule basically a Scalar UDF. Below is an example of what I am doing:
Example
View #1 -> Select A, B, C, funcX1(A) as ValueX1, funcY1(B, C) as ValueY1 FROM someView
Then
View #2 -> Select A, B, C, ValueX1, ValueY1, funcX2 (ValueX1) as ValueX2, funcY2(ValueY2) as ValueY2 FROM View#1
Currently I have a hierarchy of 7 views that each use UDFs to implement the Business Rules, where the value calculated from a UDF in one View is used as input to UDF in a View further down the Hierarchy.
Is there a better way of implementing all of the Rules instead of using multiple Views with a bunch of UDFs?
The "end product" dataset is then exposed as a Stored Procedure to the reports in Reporting Services.
Any help would be GREATLY appreciated.
Thanks!
- marty
Fun With Tracing
May 24, 2001I would greatly appreciate any help with this problem, as I've been digging thru every resource I can find looking for a solution with no luck.
I'm going to be monitoring a database for all SQL statements containing INSERT, DELETE, or UPDATE. I'm grabbing the user name, time, and the entire text of the query. I can already do this programmatically, no problem. The problem lies in this. When I set up a trace on SQL Server 2000 using the system stored procedures sp_trace_create, sp_trace_setfilter, etc, and set the trace to save to a trace file, I find that I must first stop the trace then close the trace before I can use fn_trace_gettable to get the information that I want. However, this is undesirable, because this database may be accessed worldwide, and stopping the trace to read the data could cause the trace to miss some users making changes. Does anyone know how that I could get my trace data into a table so that I can just run queries on that table to get my data? It's very important that I not stop the trace to do this. Thanks for your help!
JR Rickerson
Software Engineer
Infinite Software Solutions, Inc.
Tracing
May 20, 2008What are different kind of traces we can take regarding the performace and how traces can be used for improving performance ?
spatle
Major Query Optimiser Weirdness With UDFs And SPs On SQL 2000
Jul 20, 2005There is something very strange going on here. Tested with ADO 2.7 andMSDE/2000. At first, things look quite sensible.You have a simple SQL query, let's sayselect * from mytab where col1 = 1234Now, let's write a simple VB program to do this query back to anMSDE/2000 database on our local machine. Effectively, we'llrs.open sSQLrs.closeand do that 1,000 times. We wont bother fetching the result set, itisn't important in this example.No problem. On my machine this takes around 1.6 seconds and modifyingthe code so that the column value in the where clause changes eachtime (i.e col1 = nnnn), doesn't make a substantial difference to thistime. Well, that all seems reasonable, so moving right along...Now we do it with a stored procedurecreate procedure proctest(@id int)asselect * from mytab where col1 = @idand we now find that executingproctest nnnn1,000 times takes around 1.6 seconds whether or not the argumentchanges. So far so good. No obvious saving, but then we wouldn'texpect any. The query is very simple, after all.Well, get to the point!Now create a table-returning UDFcreate function functest(@id int) returns table asreturn(select * from mytab where col1 = @id)try calling that 1,000 times asselect * from functest(nnnn)and we get around 5.5 seconds on my machine if the argument changes,otherwise 1.6 seconds if it remains the same for each call.Hmm, looks like the query plan is discarded if the argument changes.Well, that's fair enough I guess. UDFs might well be more expensive...gotta be careful about using them. It's odd that discarding the queryplan seems to be SO expensive, but hey, waddya expect?. (perhaps theUDF is completely rebuilt, who knows)last test, then. Create an SP that calls the UDFcreate procedure proctest1(@id int)asselect * from functest(@id)Ok, here's the $64,000 question. How long will this take if @idchanges each time. The raw UDF took 5.5 seconds, remember, so thisshould be slightly slower.But... IT IS NOT.. It takes 1.6 seconds whether or not @id changes.Somehow, the UDF becomes FOUR TIMES more efficient when wrapped in anSP.My theory, which I stress is not entirely scientific, goes somethinglike this:-I deduce that SQL Server decides to reuse the query plan in thiscircumstance but does NOT when the UDF is called directly. This iscounter-intuitive but it may be because SQL Server's query parser istuned for conventional SQL i.e it can saywell, I've gotselect * from mytab WHERE [something or other]and now I've gotselect * from mytab WHERE [something else]so I can probably re-use the query plan from last time. (I don't knowif it is this clever, but it does seem to know when twotextually-different queries have some degree of commonality)Whereas withselect * from UDF(arg1)andselect * from UDF(arg2)it goes... hmm, mebbe not.... I better not risk it.But withsp_something arg1andsp_something arg2it goes... yup, i'll just go call it... and because the SP was alreadycompiled, the internal call to the UDF already has a query plan.Anyway, that's the theory. For more complex UDFs, by the way, theperformance increase can be a lot more substantial. On a big complexUDF with a bunch of joins, I measured a tenfold increase inperformance just by wrapping it in an SP, as above.Obviously, wrapping a UDF in an SP isn't generally a good thing; theidea of UDFs is to allow the column list and where clause to filterthe rowset of the UDF, but if you are repeatedly calling the UDF withthe same where clause and column list, this will make it a *lot*faster.
View 3 Replies View RelatedTransact SQL :: Referencing UDFs And Sprocs In Linked Server
Oct 7, 2015We have migrated a database (myDb2014)Â to a SQL Server 2014 instance. We have another on a 2008R2 instance (myDb2008). Both on the same server.
I have setup a linked server to the 2014 instance from the 2008R2 one.
I have a number of sprocs on myDb2008 that call TVFs and sprocs on myDb2014. This worked fine when they were both on the same instance.
Now however, I have an issue where I would have to update all the references in each calling query.
I looked into creating Synonyms but this only works for Tables/Views.
Is there a workaround to querying myDb2014 TVFs/sprocs without having to update each calling query in myDb2008?
How To Find Which Stored Procs And UDFs Reference A Column
Jun 12, 2006How can I list the stored procedures and user-defined functions that reference a given column? I could search ROUTINE_DEFINITION in INFORMATION_SCHEMA.ROUTINES for '%MyColumnName%' but MyColumnName is not always unique.
Thanks.
Tracing The Deadlock
Jul 12, 2006Hi,
I need to trace deadlock, one of article was mentioning “QL Server Profiler's Create
Trace Wizard to run the "Identify The Cause of a Deadlock" for SQL Server 7.0, is there any way I can do this in Sql Server 2000?
Tracing Sql For Locks
Jul 20, 2005I am fairly new to SQL Server. I am writing a tool in stored procedureto identify locks in a table. I have already written the basic frameworkof the SP. It will reside in master database and take two inputs. Databasename and table name. From that it will show all locks at that instanton that table of that database. If table name is omitted, then it will showlocks on all tables.I am using syslockinfo, spt_values tables and joining with SP_WHO procedureto get the table name, user name and the session id.Now what I need is to find out which SQL is causing the lock and since whenlock is being held on the table. Which tables in master database holds therequired information.TIA.Ravi
View 2 Replies View RelatedTracing Sql From Code
Jul 20, 2005HiI want to trace all the selects/deletes/modifys whatever on a databasein an application that are performed in a seperate application.I need to look into this, any ideas?- Can triggers do this kind of thing- Can you somehow access the profiler via OLE or similiar to do this?- Anything else?TaF
View 5 Replies View RelatedTracing The Deadlock
Jul 12, 2006Hi,
I need to trace deadlock, one of article was mentioning €œQL Server Profiler's Create
Trace Wizard to run the "Identify The Cause of a Deadlock" for SQL Server 7.0, is there any way I can do this in Sql Server 2000?
Tracing The Sp_executesql
Oct 17, 2006Hi,
I am using sp_executesql to get some data but it is not working. Is there a way to actually see the actual statement where subsituted variables are replaced with the actual values.
SET @SQLString = N'SELECT Top(1) @Passenger_OUT = Name, @Ticket_OUT = TicketNumber , @TAN_OUT= [TAN] '
+ N'FROM dbo.Table1 WHERE 1 = 1 '
+ Case @MatchAmount When 1 Then N' and Amount = @BillingAmount ' Else N'' End
+ Case @MatchTicket When 1 Then N' and LTrim(TicketNumber) = STUFF(STUFF(@TicketNumber,Len(@TicketNumber)-@RemoveRight+1,@RemoveRight,''''),1,@RemoveLeft,'''') ' Else N'' End
+ Case @DaysDiff When 0 Then N'' Else N' and DATEDIFF(d,@BillingDate , InvoiceDate) <= @DaysDiff ' End
+ Case @MatchName When 1 Then N' and Left(Name,@CharsToMatch) = Left(@PassengerName, @CharsToMatch) ' Else N'' End ;
SET @ParmDefinition = N'@BillingAmount decimal, @TicketNumber varchar(15), @RemoveRight tinyint, @RemoveLeft tinyint, @BillingDate datetime, @DaysDiff tinyint, @CharsToMatch tinyint, @PassengerName varchar(35), @Passenger_OUT varchar(35) OUTPUT, @Ticket_OUT varchar(15) OUTPUT, @TAN_OUT varchar(25) OUTPUT';
EXECUTE sp_executesql @SQLString, @ParmDefinition,@BillingAmount=@BillingAmount, @TicketNumber=@TicketNumber, @RemoveRight=@RemoveRight, @RemoveLeft=@RemoveLeft, @BillingDate=@BillingDate, @DaysDiff=@DaysDiff , @CharsToMatch=@CharsToMatch, @PassengerName=@PassengerName, @Passenger_OUT=@Passenger_new OUTPUT, @Ticket_OUT=@Ticket_new OUTPUT, @TAN_OUT=@TAN_new OUTPUT;
Print @SQLString will gives the statement with variables and I need to see the actual data plugged in during runtime.
If I take the ouput of @SQLString and run it myself by adding variables it works fine
Thanks
Shafiq
Serious Perfomance Problem Using UDFs As Function Arguments. (I Suspect A Bug, Show Me I'm Wrong!)
Nov 29, 2007
(From an exchange originally posted on SQLServer.com, which wasn't resolved...)
To return views tailored to the user, I have a simple users table that holds user IDs, view names, parameter names, and parameter values that are fetched based on SUSER_SNAME(). The UDF is called MyParam, and takes as string arguments, the name of the view in use, and a parameter name. (The view the user sees is really a call to a corresponding table returning UDF, which accepts some parameters corresponding to the user.)
But the performance is very dependent on the nature of the function call. Here are two samples and the numbers reported by (my first use of) the performance monitor:
Call to table returning UDF, using local variables:
declare @orgauth varchar(50)
set @orgauth = dbo.MyParam('DeptAwards', 'OrgAuth')
declare @since datetime
set @since = DATEADD(DAY,-1 * dbo.MyParam('DeptAwards', 'DaysAgo'),CURRENT_TIMESTAMP)
select * from deptAwardsfn(@orgauth,@since)
[187 CPU, 16103 Reads, 187 Duration]
Call to same table returning UDF, using scalar UDFs in parameters:
SELECT *
from deptAwardsFn (
dbo.MyParam('DeptAwards', 'OrgAuth')
,DATEADD(DAY,-1 * dbo.MyParam('DeptAwards', 'DaysAgo'),CURRENT_TIMESTAMP)
)
[20625 CPU, 1709010 Reads, 20632 Duration]
(My BOL documentation claims the CPU is in milliseconds and the Duration is in microseconds -- which I question.) Regardless of the unit of measure, it takes a whole bunch longer in the second case.
My only guess is that T-SQL is deciding that the parameter values (returned by dbo.MyParam) are nondeterministic, and continually reevaluates them somehow or other. (What ever happened to call by value?)
Can anyone shed some light on this strange (to me) behavior?
----- (and later, from me)---
(I have since discovered that the reference to CURRENT_TIMESTAMP in the function argument is the cause, but I suspect that is an error -- it should only capture the value of CURRENT_TIMESTAMP once, when making the function call IMHO.)
Profiler Only Tracing One Server
Feb 4, 2002There is a bug in one of the service packs where Profiler (7.0) only traces one server (regardless of the server you tell it to trace). Can anyone tell me how to fix this or point me to a KB article? I thought this was fixed in SQL 7 SP 3, however I'm experiencing this problem with SP3 installed.
View 1 Replies View RelatedTracing Failed Logins
Jul 19, 2001SQL 7 profiler has an event in the Misc. category of Failed Login. It does not, or at least I cannot get it to, produce any output when a failed login occurs. Any hints?
I tried this because every week or so I get this in the error log:
Login failed for user 'Admin'.
It occurs several hundred times within a minute or so. It obviously has to be an automated process as you couldn't click a button or press a key 13 times a second.
The login does not exist as a SQL login so I can't tell which database it is trying to get at. Any suggestions gratefully received.
Simon
SQL Server Error Tracing
Jan 24, 2000Hi There,
I hope someone can assist me in tracing the cause of a problem I am experiencing. I have a Web Server with ASP's querying the SQL Server 6.50.416 database. There is only one user db on this machine and yet I am running out of User Connections (current setting 2000) and memory (128MB RAM). Also, NT repeatedly experiences Stack Dumps. I have used the PRINTDMP utility to try and trace the cause of the error. The "Input Buffer" section of the Stack Dump (symptom dump) contains the following:
SELECT FK_SUB_PRODUCT_GROUPING, FK_SUB_PRODUCT_NAME FROM RESOLUTION_PRODUCT_SUB_PRODUCT WHERE FK_PRODUCT_CODE=1072 order by FK_SUB_PRODUCT_GROUPING
Is this SQL Statement the cause of the Stack Dump? Does anyone have any other ideas on what may be causing my problem. Any help would be greatly appreciated.
Tracing Or Debugging A Procedure
Dec 21, 2005I have a very simple piece of code (see below) which when executed sometimes takes around 7 minutes and sometimes around 3.5 hours. the difference is that during the 3.5 hours there is a lot of querying of the table being updated. But I don't know how to find out if this is the case. How can I find out whether my process is waiting (for locks or for any other reason) - is there a trace or debug facility within the tandard Microsoft Toolset which I can use.
Regards
Colin
Problem code below
===============
print 'Updating stm_brnline - Start time is ' + convert(char(25),getdate(),113)
--
update m
set m.branchpgrade = s.branchgrade
from stm_brnline m, tmp_brngrades s
where m.traddiv = s.traddiv and
m.contcode = s.contcode and
m.merchsect = s.merchsect and
m.branch = s.branchcode
--
print 'Updating stm_brnline - End time is ' + convert(char(25),getdate(),113)
Actual Syntax By Tracing
Feb 3, 2004Hello
I am running 6.5 sql and work with a traffic and billing software ( called NOvar) from another company(encoda system) which does a lot of scheduling, reporting etc
I dont know the contents of table (100 table ) and their column
or which table its querying to take out reports
Can i create a trace to know the syntax each time some thing is executed.
I also need to create customized reports, can this be done by sql reporting or does i need to go from crystal reports or someone else
For i dont know any language except sql and HTML
sej
Tracing Users Of A Database
Jun 13, 2007Hi
As a newbie to DBA type tasks, how can I trace who has accessed the server/database. I know there is a SPID in the Server log but what does this represent?
Thanks!
Tracing Query Time
Oct 5, 2007I have a Stored Procedure that execute some queries on link server. It takes so long to complete so my application get timeout error. There was no problem until last week. I suspect, remote queries that qorks on link server takes long. How can i trace the time of queries. Any idea about link server timeout problems?
Thanks in advance.
SQL Profiler And Single Client Tracing
Mar 10, 2000I have a client which makes about 50 connections to my SQL Server using a non-unique username. Under SQL 6.5 I could trace all SQL activity by PC name, is this possible using the SQL Profiler in SQL 7?
Gordon
Tracing The Actual Syntaz As Is Excuted
Jan 30, 2004I am using 6.5 and on this i used different program
and people execute query
Can i place a trace to show what query is being executed from which program and at what time
Meaning the syntax of it and by whom
I start tracing and it paging file becomes to large
sej
Question On Tracing Data Change
Jul 11, 2007I have an issue where i have a field that changes data and i need to trace what it is that is changing it. I have setup a trace for the database but i would like to be able to narrow it down even further to the table and even the column if possible. Is there a way that i can trace data changes on such a granular level
View 1 Replies View Related