UDFs & ITW - The workload does not contain any eve

• iamih

  Grasshopper

  Points: 13

  More actions

  November 3, 2002 at 3:56 pm

  #105370

  I have MSSQL SP2.

  Usualy I have no problem with the profiler or ITW, but when I tried to

  run the ITW on a query which has a scalar user defined function in the

  where clause, it returnd onw of the following errors:

  "The workload does not contain any events or queries..."

  or

  "There are no events in the workload"

  I tried the same query without the where clause and it worked.

  the udf:

  create function IndexErrorFunction () returns varchar(2)

  begin

  return ''

  end

  the table:

  create table IndexError (

  KeyRow uniqueidentifier,

  Row2 varchar(2),

  CONSTRAINT [PK_IndexError] PRIMARY KEY CLUSTERED

  (

  [KeyRow]

  ) ON [PRIMARY]

  ) ON [PRIMARY]

  the query that the ITW analyzes:

  select * from IndexError

  where Row2 = ''

  the query that the ITW cannot analyze:

  select * from IndexError

  where Row2 = dbo.IndexErrorFunction()

  Any idea?

  10x.

• Simon Sabin

  SSCrazy Eights

  Points: 8142

  More actions

  November 4, 2002 at 3:01 am

  #440180

  I can well believe that the index tuning wizard cannot cope with udfs. It all comes down to determining what will come out of the function.

  If you use an "Inline Table-valued Function" you will probably have more success.

  Simon Sabin

  Co-author of SQL Server 2000 XML Distilled

  http://www.amazon.co.uk/exec/obidos/ASIN/1904347088

  
  Simon Sabin
  SQL Server MVP
  
  http://sqlblogcasts.com/blogs/simons