Archive for June, 2011

Are User Defined Functions (UDF) pre-compiled – MSDN TSQL forum

–> Question:

I have seen several posts saying that a UDF is not pre-compiled like a stored procedure.

But this MSDN Page it says both are.

Could any one let me know which one is correct and what is meant by this?

–> My Answer:

UDF are not pre-compiled, UDFs similar to SPs are only compiled on their first execution. They are just parsed and created in SQL catalog.

Try to execute the DMV query before the SQL query where the function TsqlSplit() is called. It won’t give to you any cache’d record. But after you execute the SQL query and then execute the DMV query then you will see a cache’d record for Function execution.

Check this:

dbcc freeproccache


CREATE FUNCTION [dbo].[TsqlSplit] (@List As varchar(8000)) 
RETURNS @Items table ( 
  Item varchar(8000) Not Null) 
      DECLARE @Item As varchar(8000), 
              @Pos  As int 

      WHILE DATALENGTH(@List) > 0 
            SET @Pos=CHARINDEX(',', @List) 

            IF @Pos = 0 
              SET @Pos=DATALENGTH(@List) + 1 

            SET @Item = LTRIM(RTRIM(LEFT(@List, @Pos - 1))) 

            IF @Item <> '' 
              INSERT INTO @Items 
              SELECT @Item 

            SET @List=SUBSTRING(@List, @Pos + DATALENGTH(','), 8000) 


SELECT objtype, 
FROM   sys.dm_exec_cached_plans AS ECP 
       CROSS APPLY sys.dm_exec_sql_text(ECP.plan_handle) AS EST 
WHERE  EST.text LIKE '%TsqlSplit%' 


FROM   [TsqlSplit] ('1,2,3') 

SELECT objtype, 
FROM   sys.dm_exec_cached_plans AS ECP 
       CROSS APPLY sys.dm_exec_sql_text(ECP.plan_handle) AS EST 
WHERE  EST.text LIKE '%TsqlSplit%' 


Ref Link.


Logging in SSIS… using SQL Server Log Provider

June 15, 2011 4 comments

As per MS BOL SSIS provides a diverse set of log providers, and gives you the ability to create custom log providers. The Integration Services log providers can write log entries to text files, SQL Server Profiler, SQL Server, Windows Event Log, or XML files. This help in implementing logging in SSIS packages. With logging you can capture run-time information about a package, helping you audit and troubleshoot a package every time it is run.

Following are the different log providers:
1. Text File
2. SQL Server Profiler
3. SQL Server
4. Windows Event
5. XML File

Here we will see how can we use the SQL Server log provider (#3 above), which writes log entries to the sysssislog table in a SQL Server database.
Please note in 2005 the logs are stored in sysdtslog90 table. In 2008 the table name is changed to sysssislog.

Following are the easy steps one can follow to configure logging:

1. Create a SSIS package, as shown below are the 2 Execute SQL tasks. To enable logging goto menu, select SSIS-> Logging…

2. On this new window select the Provider Type as “SSIS log provider for SQL Server”, click Add.

3. The log provider will be added, select by checking the check box. Now provide the connection where the log table should get created.

4. Switch to the Details tab and select the events you want to log and audit. Click OK, save your SSIS package and your logging is enabled.

5. After this run your package and switch to  SSMS and check on object explorer that the table sysdtslog90 has been created on the database you added as a connection. Now you can query the sysdtslog90 table where the logs are stored.

select * from FROM [AdventureWorks].[dbo].[sysdtslog90]

The sysdtslog90 table contains following columns listed in the query below. All are self explanatory, one can also check the links at the end of the post to get more info about them.

SELECT [id], [event], [computer], [operator], , --,[sourceid], [executionid],
      [starttime],[endtime],[datacode], [databytes], [message]
FROM [AdventureWorks].[dbo].[sysdtslog90]


>> Check & Subscribe my [YouTube videos] on SQL Server.

SQL DBA – DBID 32767 | Resource Database

June 3, 2011 1 comment

select * from sys.databases

On executing the above query it gives me 9 records with DBID ranging from 1 to 9. First 4 DBIDs (1-4) for master, tempdb, model & msdb and another 5 databases (5-9) created by me.

But when I queried some DMVs & DMFs it resulted some records with DBID 32767 which left me clueless until I googled about it. Also submitted a post on my favourite MSDN T-SQL forum and got to know that this ID is reserved for Resource DatabaseThe Resource database is readonly database that does not appear on SSMS and is managed internally by SQL Server engine. More on MS BOL about Resources.

To regenerate this scenario, lets do a simple exercise. Execute the following code (make sure you have AdventureWorks sample database installed):

USE [AdventureWorks]

-- Execute some sample SQL statements
select top 10 * from Person.Contact
select top 10 * from Production.Product
go 10

-- Execute sp_who2 proc
go 5

Now execute the query below using sys.dm_exec_query_stats DMV & sys.dm_exec_sql_text(sql_handle) DMF to get the status of what SQL engine is doing behind:

select x.dbid, x.text, a.creation_time, a.Last_execution_time, a.execution_count
from sys.dm_exec_query_stats a
cross apply sys.dm_exec_sql_text(a.sql_handle) x


The results displayed in the image above shows 2 records with DBID 32767.