Fun with sp_executeSQL

http://www.sqlservercentral.com/articles/nHibernate/86913/

When I first ran SQL Profiler against nHibernate I noticed that calls to the database were being made via sp_prepexec. in sp_prepexec and nHibernate.  I did quite a bit of digging around to find out more about sp_prepexec which I wrote up in "sp_prepexec and nHibernate".
To summarise the article, until recently sp_prepexec was an undocumented stored procedure and even today very little documentation is available for it.

• It is in a family of cursor stored procs which include sp_prepare, sp_prepexec and sp_unprepare
• It should not be called directly
• It is called as part of the fundamental under-the-hood operation of ADO.NET, OLEDB operations
• Its scope is session based though any queries plans generated are global

Given its undocumented nature and a natural DBA aversion to putting undocumented system procs in production systems I asked our developers to set the nHibernate configuration switch to use sp_executesql instead.

<property name='prepare_sql'>false</property>

At this point a number of problems came to the fore which have been fixed as part of nHibernate V2.2
What I hope to cover here is not nHibernate but a write-up of the issues were found in the way that dynamic and parameterised SQL were called using sp_executeSQL.

Sp_executesql

The basics

Sp_executesql is fully documented both in books-online and MSDN.  Its purpose is to allow queries with the same signature but different parameter values to use the same query plan.
To show an example of this run the following code

USE AdventureWorks
go

DECLARE     
 @EmployeeID int,
 @Statement nvarchar(2000),
 @ParamDefinitions nvarchar(2000)

SET @Statement = N'SELECT * FROM HumanResources.Employee WHERE ManagerId=@ManagerID AND MaritalStatus=@MaritalStatus'
SET @ParamDefinitions = N'@ManagerID INT,@MaritalStatus char(1)'

exec sp_executesql @statement, @ParamDefinitions,@ManagerID=21,@Maritalstatus='S'

Then run the following query

SELECT
      ST.dbid,
      st.text,
      cp.usecounts,
      cp.size_in_bytes,
      qp.query_plan,
      cp.cacheobjtype,
      cp.objtype,
      cp.plan_handle,
      qp.number

FROM sys.dm_exec_cached_plans cp
      CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
      CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp

WHERE st.text like '%SELECT * FROM HumanResources.Employee WHERE ManagerId%'

What you should see in the recordset with a line containing

(@ManagerID INT,@MaritalStatus char(1))SELECT * FROM HumanResources.Employee WHERE ManagerId=@ManagerID AND MaritalStatus=@MaritalStatus

Now if we go back and change the marital status from S to M by changing the sp_executesql statement from

exec sp_executesql @statement, @ParamDefinitions,@ManagerID=21,@Maritalstatus='S'

to

exec sp_executesql @statement, @ParamDefinitions,@ManagerID=21,@Maritalstatus='M'

Execute the query and run our query plan statement again you will see that although sp_executesql gave us a different recordset our query plan statement still returned the same record.
In fact, as long as all we change is the parameter values sp_executeSQL will reuse this plan.
There are a couple of points to note with our query plan statement.

• Usecounts increments every time we use sp_executesql to call our statement
• Size_in_bytes tells us the size of the execution plan in memory.

The significance of these figures will become apparent later.

Digging deeper

Now let us try changing the case of the SELECT statement used by sp_executeSQL from

SET @Statement = N'SELECT * FROM HumanResources.Employee WHERE ManagerId=@ManagerID AND MaritalStatus=@MaritalStatus'

TO

SET @Statement = N'select * FROM HumanResources.Employee WHERE ManagerId=@ManagerID AND MaritalStatus=@MaritalStatus'

Running our query plan statement  now reveals that we have two separate plans even though as human beings we can see the two queries are the same.
Just for the sake of completeness let us try changing the case of the parameter definitions from

SET @ParamDefinitions = N'@ManagerID INT,@MaritalStatus char(1)'

TO

SET @ParamDefinitions = N'@ManagerID INT,@MARITALSTATUS char(1)'

Again, running our query plan statement reveals that we now have 3 separate plans!

LESSON ONE:  The cache is case sensitive.

For nHibernate this isn't an issue as the objects are predefined and statements always follow the same pattern.  If your application developers do not use an ORM framework and call sp_executeSQL this is something to bear in mind.

What about stored procedures?

You can see that parameterised SQL is case sensitive but what about calls to stored procedures?
I ran the following T-SQL in the AdventureWorks database

USE [AdventureWorks]
GO

DECLARE    @return_value int

EXEC    @return_value = [dbo].[uspGetEmployeeManagers]
        @EmployeeID = 21

SELECT    'Return Value' = @return_value

GO

Closely followed by

USE [AdventureWorks]
GO

DECLARE    @return_value int

EXEC    @return_value = [dbo].[USPGETEMPLOYEEMANAGERS]
        @EmployeeID = 21

SELECT    'Return Value' = @return_value

GO

Using our query to have a look at the proc cache revealled the following

dbid	text	usecounts	size_in_bytes	query_plan	cacheobjecttype	objtype
9	CREATE PROCEDURE [dbo].[uspGetEmployeeManagers]  @Emp...	2	122880	<ShowPlanXML xmlns="http://schemas.microsoft.com...	Compiled Plan	Proc

Here you can see that the case in which the stored proc is called has no bearing on the number of entries in the proc cache.

Variable length parameters

Let us try adding in a variable length parameter into the statement we use with sp_executeSQL

DECLARE
      @EmployeeID int,
      @Statement nvarchar(2000),
      @ParamDefinitions nvarchar(2000)


SET @Statement = N'select * FROM HumanResources.Employee WHERE ManagerId=@ManagerID AND MaritalStatus=@MaritalStatus AND Title=@Title'
SET @ParamDefinitions = N'@ManagerID INT,@MaritalStatus char(1),@Title nvarchar(50)'

exec sp_executesql @statement, @ParamDefinitions,@ManagerID=21,@Maritalstatus='S',@Title='Production Supervisor - WC60'

Execute the script then change the definition of @Title from nvarchar(50) to nvarchar(40) and re-execute it

SET @ParamDefinitions = N'@ManagerID INT,@MaritalStatus char(1),@Title nvarchar(40)'

exec sp_executesql @statement, @ParamDefinitions,@ManagerID=21,@Maritalstatus='S',@Title='Production Supervisor - WC60'

If we run our query plan statement we will see two separate entries for our query.
LESSON TWO: A separate query plan will result if parameter sizes differ for what is essentially the same query.
Prior to nHibernate V2.2 this was a problem because nHibernate sized the parameters to the contents of the value being passed to it.

• @FirstName = 'JIM' would be passed as NVARCHAR(3)
• @FirstName = 'DAVE' would be passed as NVARCHAR(4)

Why is this a problem?

Take a look at the variable length fields in the Adventureworks.Person.Address table.

• AddressLine1
• AddressLine2
• City
• PostalCode

Running the query below tells us that there are 1,320 possible variations of lengths for combinations of those fields in the table.

SELECT
      LEN(AddressLine1) AS Ad1Length,
      LEN(AddressLine2) AS Ad2Length,
      LEN(city) AS CityLength,
      LEN(PostalCode) AS PostalLength

FROM Person.Address

GROUP BY   
      LEN(AddressLine1),
      LEN(AddressLine2) ,
      LEN(city) ,
      LEN(PostalCode)

A parameterised INSERT statement is 25K so factor in the variability and our 25K query plan is now 25K x 1,320 = 33MB!
That is one query against one table!
Adventureworks is a small sample database with 71 tables so considering the different combinations of variable length fields for each table and the number of different queries that are to be run across the database you can soon see why the our proc cache starts to balloon into the GB range!
There is another problem associated with varying parameter sizes for what is essentially the same query.
Whenever a new query is run it has to pass through the cost based optimisation process. A simplified illustration of this process is shown by the diagram below.

Zoom in  |  Open in new window

This is a phased process so if the query cost falls below the threshold for stage one then it that plan will be cached and executed.
If it is exceeds the threshold for simple optimisation then it is passed to the 2^nd phase optimisation and if it can be optimised within the limits of the 2^nd phase then again, its plan is cached and the query executed.
There is a 3^rd and 4^th phase and obviously each phase gets progressively more expensive.  A colleague mentioned that he had seen a complex query take 17 seconds just to go through the optimisation process.
Most of the queries I have seen coming out of nHibernate are relatively straight forward and have had a modest cost but you still have the overhead of compiling the query prior to execution.

Why do we care about memory taken to story query plans?

I have often asked the question as to what is the optimum memory for a SQL Server instance and the answer always seems to be "as much as you can afford".
At this point it is a good idea to take a look at what SQL Server uses its memory for.  The principal items are as follows

Memory	Comment
Data cache known as the buffer pool	Ideally we need enough memory to hold the active data portion of our database in memory. Being able to retrieve data from memory is absolutely key to giving good performance.
Query cache known as the proc cache	If the proc cache bloat gets out of hand then pages will be "stolen" from the buffer pool to allocate to the proc cache.
Connections	94KB + (3 x network package size - typically 4096KB) Clearly we would need a very high number of users for connection memory to pose a problem. What those connections actually do in terms of querying is another matter, particularly if they are running heavy weight queries bringing back loads of data.
Locks	64 bytes  32 bytes per owner. Lock escalation is a trade-off between concurrency and memory usage. Lock escalation occurs when 5,000 or more locks occur on an object.  If it didn't occur then the risk is that the amount of memory used to maintain those locks would start to eat into the memory available for the buffer pool.

http://msdn.microsoft.com/en-us/library/aa337559.aspx

Obviously memory is consumed by the operating system, CLR functions, full text index service, SSIS processes and a whole range of other things.

Problems with 64 Bit SQL Server

If you get proc cache bloat on a 32 bit server then to a certain extent this gets masked by the fact that the proc cache can't stray into AWE memory.  Effectively it is constrained to a 2GB maximum.
Switch to 64 Bit however and such constraints vanish and it will continue to grow as much as it needs to until it starts to eat into the buffer pool.
When this happens your server will start to show symptoms of coming under memory pressure.

• Increased paging to disk
• Increased IO
• Decreased performance
• Increased demand on CPU
• …etc

What affect does query complexity have on the proc cache?

Data can stay in the buffer cache until the allocated memory is needed for newer data.  If your server has more memory than there is data then it is conceivable that the data will remain in the buffer cache until the SQL Server service is restarted.
Query plans age out of the cache eventually based on an algorithm that checks when they were last used and how complex the plan was.

• The more costly a query was to compile the longer it will stay in the proc cache
• If the query is reused then it will stay in the proc cache.

If you run a query on your development box tonight, check the existence of the execution plan and come back the next day then it will be gone by the morning.
Internally SQL Server is constantly cycling through the proc cache aging the items in the cache until they qualify for removal.

To summarise it all...

• For parameterised SQL the proc cache is case sensitive
• Any change in one or more parameters means the parameterised SQL is treated as a different
• When moving from 32 Bit to 64 Bit SQL Server it is wise to inspect the proc cache to see what demands are being made on it i.e. will the elimination of the 2GB limit affect your system.
• Query plans will age out of the proc cache according to a time based algorithm.  If you have dynamic SQL and/or parameterised SQL with parameter lengths that vary depending on their contents rather than their intent then the cache will be eaten faster than it can age out.

How to read registry and find sql service instance name

DECLARE @InstName VARCHAR(16)

DECLARE @RegLoc VARCHAR(100)

SELECT @InstName = @@SERVICENAME

IF @InstName = 'MSSQLSERVER'

  BEGIN

    SET @RegLoc='Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp\'

  END

 ELSE

  BEGIN

   SET @RegLoc='Software\Microsoft\Microsoft SQL Server\' + @InstName + '\MSSQLServer\SuperSocketNetLib\Tcp\'

  END

EXEC [master].[dbo].[xp_regread] 'HKEY_LOCAL_MACHINE', @RegLoc, 'tcpPort'

How to receive Deadlock information automatically via email

http://www.sqlservercentral.com/articles/deadlock/71374/

Receive Deadlock detail via email.

I had spent many hours on Google trying to find the best way to send a notification on deadlock occurrences. While it was a fairly straight forward process on how we get notified a deadlock has occurred, I wanted a bit more. I not only wanted to be told when the Deadlock occurred, I wanted to also be emailed the actual Deadlock information.
Every time a deadlock occurs in SQL Server, the detailed info about things like the SPID, the statement that was running, who the victim was, and so on does get logged. But getting the generic alert that SQL Server can create simply will tell you "something" has occurred. It would be the dutiful DBA's job to log into the server in question and dig into the Error Log to get the Deadlock details.
Since capturing Deadlock info is not turned on by default. We do have to do two things in order to make the scripts I have written work properly.

Requirement number one

The first requirement is to turn on the appropriate Trace Flags. We do that by running the following command.

DBCC TRACEON (3605,1204,1222,-1)

A brief overview to what those flags do is listed below.

• 3605 = write what we want to the error log.
• 1204 = Capture Deadlock Events.
• 1222 = Capture Deadlock Events with more info (SQL 2005 and higher)

It is important to note that setting trace flags this way will only work until the next time SQL Server is restarted. If you want to make sure your trace flags are always set this way, you can edit the startup options of SQL Server by using the -T switch or creating a SQL Agent job to run that code when the Agent starts.

Requirement number two

The second requirement is to ensure you have DB Mail setup and working. I will provide no detail on how to accomplish that. It will just be assumed that you have a working DB Mail profile.

Lets get started.

So now we have trace flags set and DB Mail is working we are ready to get into how we send the Deadlock information to an email address when a Deadlock occurs.
Since the structure of the error log changed in SQL 2005, we have two ways of doing this. Each method is basically the same, however, pay attention to where you deploy this script. I have included detailed comments so you can follow along.

For SQL Server 2000

--== This is for SQL 2000. ==--
--== We will create a temporary table to hold the error log detail. ==--
--== Before we create the temporary table, we make sure it does not already exist. ==--
 IF OBJECT_ID('tempdb.dbo.ErrorLog') IS Not Null
 BEGIN
 DROP TABLE tempdb.dbo.ErrorLog
 END
 --== We have checked for the existence of the temporary table and dropped it if it was there. ==--
 --== Now, we can create the table called tempdb.dbo.ErrorLog ==--
 CREATE TABLE tempdb.dbo.ErrorLog (Id int IDENTITY (1, 1) NOT NULL,
 ERRORLOG VARCHAR(4000), ContRow int)
--== We create a 3 column table to hold the contents of the SQL Server Error log. ==--
--== Then we insert the actual data from the Error log into our newly created table. ==--
 INSERT INTO tempdb.dbo.ErrorLog
 EXEC master.dbo.sp_readerrorlog
--== With our table created and populated, we can now use the info inside of it. ==--
 BEGIN
--== Set a variable to get our instance name. ==--
--== We do this so the email we receive makes more sense. ==--
 declare @servername nvarchar(150)
 set @servername = @@servername
--== We set another variable to create a subject line for the email. ==-- 
 declare @mysubject nvarchar(200)
 set @mysubject = 'Deadlock event notification on server '+@servername+'.'
 --== Now we will prepare and send the email. Change the email address to suite your environment. ==-- 
 exec master.dbo.xp_sendmail @recipients = 'DBA_Super_Hero@email.com',
 @subject = @mysubject,
 @message = 'Deadlock has occurred. View attachment to see the deadlock info',
 @query = 'select ERRORLOG from tempdb.dbo.ErrorLog where Id >= (select TOP 1 Id from tempdb.dbo.ErrorLog WHERE ERRORLOG Like ''%Deadlock encountered%'' order by Id DESC)',
 @width = 600,
 @attach_results = 'True',
 @no_header = 'True'
 END
 --== Clean up our process by dropping our temporary table. ==--
 DROP TABLE tempdb.dbo.ErrorLog

And for all other version, (2005, 2008, 2008 R2)

--== This is for SQL 2005 and higher. ==--
--== We will create a temporary table to hold the error log detail. ==--
--== Before we create the temporary table, we make sure it does not already exist. ==--
 IF OBJECT_ID('tempdb.dbo.ErrorLog') IS Not Null
 BEGIN
 DROP TABLE tempdb.dbo.ErrorLog
 END
 --== We have checked for the existence of the temporary table and dropped it if it was there. ==--
 --== Now, we can create the table called tempdb.dbo.ErrorLog ==--
CREATE TABLE tempdb.dbo.ErrorLog (Id int IDENTITY (1, 1) NOT NULL, 
logdate DATETIME, procInfo VARCHAR(10), ERRORLOG VARCHAR(MAX))
--== We create a 3 column table to hold the contents of the SQL Server Error log. ==--
--== Then we insert the actual data from the Error log into our newly created table. ==--
 INSERT INTO tempdb.dbo.ErrorLog
 EXEC master.dbo.sp_readerrorlog
--== With our table created and populated, we can now use the info inside of it. ==--
 BEGIN
--== Set a variable to get our instance name. ==--
--== We do this so the email we receive makes more sense. ==--
 declare @servername nvarchar(150)
 set @servername = @@servername
--== We set another variable to create a subject line for the email. ==-- 
 declare @mysubject nvarchar(200)
 set @mysubject = 'Deadlock event notification on server '+@servername+'.'
 --== Now we will prepare and send the email. Change the email address to suite your environment. ==-- 
 EXEC msdb.dbo.sp_send_dbmail @recipients='DBA_Super_Hero@email.com',
 @subject = @mysubject,
 @body = 'Deadlock has occurred. View attachment to see the deadlock info',
 @query = 'select logdate, procInfo, ERRORLOG from tempdb.dbo.ErrorLog where Id >= (select TOP 1 Id from tempdb.dbo.ErrorLog WHERE ERRORLOG Like ''%Deadlock encountered%'' order by Id DESC)',
 @query_result_width = 600,
 @attach_query_result_as_file = 1
 END
 --== Clean up our process by dropping our temporary table. ==--
 DROP TABLE tempdb.dbo.ErrorLog

Next Steps

In order to get those to work every time SQL Server encounters a Deadlock, we have to create a SQL Server Agent Job and a SQL Server Agent Alert. The basic approach is to create a Job that is called by the Alert service.
Let's first create a SQL Agent Job. The job will have no schedule. Note that the script below needs to be edited. If you do not run your SQL Server in mixed mode, you will need to change @owner_login_name=N'sa' to a user that can run the job. Also, note that the script contains an email address. You will have to enter a valid email address. This would be the email address of the person that will be troubleshooting the Deadlock occurrences. You will create this SQL Agent job on every instance you want to receive Deadlock info for.

SQL Server 2000 Job

USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Deadlock Job', 
 @enabled=1, 
 @notify_level_eventlog=0, 
 @notify_level_email=0, 
 @notify_level_netsend=0, 
 @notify_level_page=0, 
 @delete_level=0, 
 @description=N'No description available.', 
 @category_name=N'[Uncategorized (Local)]', 
 @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Deadlock has occurred.', 
 @step_id=1, 
 @cmdexec_success_code=0, 
 @on_success_action=1, 
 @on_success_step_id=0, 
 @on_fail_action=2, 
 @on_fail_step_id=0, 
 @retry_attempts=0, 
 @retry_interval=0, 
 @os_run_priority=0, @subsystem=N'TSQL', 
 @command=N'--== This is for SQL 2000. ==--
--== We will create a temporary table to hold the error log detail. ==--
--== Before we create the temporary table, we make sure it does not already exist. ==--
 IF OBJECT_ID(''tempdb.dbo.ErrorLog'') IS Not Null
 BEGIN
 DROP TABLE tempdb.dbo.ErrorLog
 END
 --== We have checked for the existence of the temporary table and dropped it if it was there. ==--
 --== Now, we can create the table called tempdb.dbo.ErrorLog ==--
 CREATE TABLE tempdb.dbo.ErrorLog (Id int IDENTITY (1, 1) NOT NULL,
 ERRORLOG VARCHAR(4000), ContRow int)
--== We create a 3 column table to hold the contents of the SQL Server Error log. ==--
--== Then we insert the actual data from the Error log into our newly created table. ==--
 INSERT INTO tempdb.dbo.ErrorLog
 EXEC master.dbo.sp_readerrorlog
--== With our table created and populated, we can now use the info inside of it. ==--
 BEGIN
--== Set a variable to get our instance name. ==--
--== We do this so the email we receive makes more sense. ==--
 declare @servername nvarchar(150)
 set @servername = @@servername
--== We set another variable to create a subject line for the email. ==-- 
 declare @mysubject nvarchar(200)
 set @mysubject = ''Deadlock event notification on server ''+@servername+''.''
 --== Now we will prepare and send the email. Change the email address to suite your environment. ==-- 
 exec master.dbo.xp_sendmail @recipients = ''DBA_Super_Hero@email.com'',
 @subject = @mysubject,
 @message = ''Deadlock has occurred. View attachment to see the deadlock info'',
 @query = ''select ERRORLOG from tempdb.dbo.ErrorLog where Id >= (select TOP 1 Id from tempdb.dbo.ErrorLog WHERE ERRORLOG Like ''''%Deadlock encountered%'''' order by Id DESC)'',
 @width = 600,
 @attach_results = ''True'',
 @no_header = ''True''
 END
 --== Clean up our process by dropping our temporary table. ==--
 DROP TABLE tempdb.dbo.ErrorLog
', 
 @database_name=N'master', 
 @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
 IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

SQL Server 2005 and higher Job

Note that the script below needs to be edited. If you do not run your SQL Server in mixed mode, you will need to change @owner_login_name=N'sa' to a user that can run the job. Also, note that the script contains an email address. You will have to enter a valid email address. This would be the email address of the person that will be troubleshooting the Deadlock occurrences. You will create this SQL Agent job on every instance you want to receive Deadlock info for.

USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Deadlock Job', 
 @enabled=1, 
 @notify_level_eventlog=0, 
 @notify_level_email=0, 
 @notify_level_netsend=0, 
 @notify_level_page=0, 
 @delete_level=0, 
 @description=N'No description available.', 
 @category_name=N'[Uncategorized (Local)]', 
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Deadlock has occurred.', 
 @step_id=1, 
 @cmdexec_success_code=0, 
 @on_success_action=1, 
 @on_success_step_id=0, 
 @on_fail_action=2, 
 @on_fail_step_id=0, 
 @retry_attempts=0, 
 @retry_interval=0, 
 @os_run_priority=0, @subsystem=N'TSQL', 
 @command=N'--== This is for SQL 2005 and higher. ==--
--== We will create a temporary table to hold the error log detail. ==--
--== Before we create the temporary table, we make sure it does not already exist. ==--
 IF OBJECT_ID(''tempdb.dbo.ErrorLog'') IS Not Null
 BEGIN
 DROP TABLE tempdb.dbo.ErrorLog
 END
 --== We have checked for the existence of the temporary table and dropped it if it was there. ==--
 --== Now, we can create the table called tempdb.dbo.ErrorLog ==--
CREATE TABLE tempdb.dbo.ErrorLog (Id int IDENTITY (1, 1) NOT NULL, 
logdate DATETIME, procInfo VARCHAR(10), ERRORLOG VARCHAR(MAX))
--== We create a 3 column table to hold the contents of the SQL Server Error log. ==--
--== Then we insert the actual data from the Error log into our newly created table. ==--
 INSERT INTO tempdb.dbo.ErrorLog
 EXEC master.dbo.sp_readerrorlog
--== With our table created and populated, we can now use the info inside of it. ==--
 BEGIN
--== Set a variable to get our instance name. ==--
--== We do this so the email we receive makes more sense. ==--
 declare @servername nvarchar(150)
 set @servername = @@servername
--== We set another variable to create a subject line for the email. ==-- 
 declare @mysubject nvarchar(200)
 set @mysubject = ''Deadlock event notification on server ''+@servername+''.''
 --== Now we will prepare and send the email. Change the email address to suite your environment. ==-- 
 EXEC msdb.dbo.sp_send_dbmail @recipients=''DBA_Super_Hero@email.com'',
 @subject = @mysubject,
 @body = ''Deadlock has occurred. View attachment to see the deadlock info'',
 @query = ''select logdate, procInfo, ERRORLOG from tempdb.dbo.ErrorLog where Id >= (select TOP 1 Id from tempdb.dbo.ErrorLog WHERE ERRORLOG Like ''''%Deadlock encountered%'''' order by Id DESC)'',
 @query_result_width = 600,
 @attach_query_result_as_file = 1
 END
 --== Clean up our process by dropping our temporary table. ==--
 DROP TABLE tempdb.dbo.ErrorLog
', 
 @database_name=N'master', 
 @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
 IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

Final step

Now we have to create a SQL Agent alert to call the job we created. Open SSMS and log into the instance we are capturing Deadlock information for and expand the SQL Server Agent. Right click on the word Alerts and choose "New Alert..."
On the General page it should look like this;

On the Response page it should look like this:


On the Options page it should look like this:



That's all there is to it. The next time a Deadlock occurs on the instance you are monitoring you will you receive an email. The email you receive will have an attachment that will actually tell you what the deadlock was. 

SQL SERVER – 2012 – List All The Column With Specific Data Types in Database

http://blog.sqlauthority.com/2012/10/13/sql-server-2012-list-all-the-column-with-specific-data-types-in-database/

5 years ago I wrote script SQL SERVER – 2005 – List All The Column With Specific Data Types, when I read it again, it is very much relevant and I liked it. This is one of the script which every developer would like to keep it handy. I have upgraded the script bit more. I have included few additional information which I believe I should have added from the beginning. It is difficult to visualize the final script when we are writing it first time. I use every script which I write on this blog, the matter of the fact, I write only those scripts here which I was using at that time. It is quite possible that as time passes by my needs are changing and I change my script. Here is the updated script of this subject. If there are any user data types, it will list the same as well.

SELECT s.name AS 'schema', ts.name AS TableName,c.name AS column_name, c.column_id,SCHEMA_NAME(t.schema_id) AS DatatypeSchema,t.name AS Datatypename
,t.is_user_defined, t.is_assembly_type
,c.is_nullable, c.max_length, c.PRECISION,c.scale
FROM sys.columns AS c
INNER JOIN sys.types AS t ON c.user_type_id=t.user_type_id
INNER JOIN sys.tables ts ON ts.OBJECT_ID = c.OBJECT_IDINNER JOIN sys.schemas s ON s.schema_id = ts.schema_id
ORDER BY s.name, ts.name, c.column_id

I would be very interested to see your script which lists all the columns of the database with data types. If I am missing something in my script, I will modify it based on your comment. This way this page will be a good bookmark for the future for all of us.

SQL SERVER – 2005 – List All The Column With Specific Data Types

Since we upgraded to SQL Server 2005 from SQL Server 2000, we have used following script to find out columns with specific datatypes many times. It is very handy small script.

SQL Server 2005 has new datatype of VARCHAR(MAX), we decided to change all our TEXT datatype columns to VARCHAR(MAX). The reason to do that as TEXT datatype will be deprecated in future version of SQL Server and VARCHAR(MAX) is superior to TEXT datatype in features. We run following script to identify all the columns which are TEXT datatype and developer converts them to VARCHAR(MAX)

Script 1 : Simple script to identify all the columns with datatype TEXT in specific database
SELECT OBJECT_NAME(c.OBJECT_ID) TableName, c.name ColumnName
FROM sys.columns AS c
JOIN sys.types AS t ON c.user_type_id=t.user_type_id
WHERE t.name = 'text' --you can change text to other datatypesORDER BY c.OBJECT_ID;GO
Script 2 : Extended script to identify all the columns datatype and other information in specific database
SELECTOBJECT_NAME(c.OBJECT_ID) TableName
,c.name AS ColumnName
,SCHEMA_NAME(t.schema_id) AS SchemaName
,t.name AS TypeName
,t.is_user_defined
,t.is_assembly_type
,c.max_length
,c.PRECISION,c.scale
FROM sys.columns AS c
JOIN sys.types AS t ON c.user_type_id=t.user_type_id
ORDER BY c.OBJECT_ID;

File Validation in SQL Server with xp_fileexist stored procedure

http://www.mssqltips.com/sqlservertip/1272/file-validation-in-sql-server-with-xpfileexist-stored-procedure/

ProblemIn a recent tip (Accessing the Windows File System from SQL Server) options were outlined to capture files from a specific directory into a temporary table or table variable for additional processing.  A similar file operations task that is needed in particular T-SQL code is to validate a specific file exists or not.  Then logic can be written to either process the file, retry at a specific interval or fail the process.  As such, how can this be accomplished and can you provide me with some concrete examples in SQL Server 2000 and 2005?
SolutionOne option to validate that a file exists or not is to use the master.dbo.xp_fileexist extended stored procedure.  In a nutshell this extended stored procedure will validate that a file exists.  Unfortunately, master.dbo.xp_fileexist is not a documented extended stored procedure in SQL Server Books Online.  As such, be aware that the functionality may change over time, although to the best of my knowledge this extended stored procedure has maintained its functionality between SQL Server 2000 and 2005.  Nevertheless, let's jump into a few examples to see how this code behaves in SQL Server 2000 and 2005.

Example 1 - Execute master.dbo.xp_fileexist without any parameters Version - SQL Server 2000 and SQL Server 2005

EXEC Master.dbo.xp_fileexist GO

Server: Msg 22027, Level 15, State 1, Line 0 Usage: EXECUTE xp_fileexist <filename> [, <file_exists INT> OUTPUT]

Example 2 - Execute master.dbo.xp_fileexist with valid file and directory with the results indicating success for both the file and directory, in addition the number of rows is accurately suppressed with the NOCOUNT command  Version - SQL Server 2000 and SQL Server 2005

SET NOCOUNT ON DECLARE @FileName varchar(255) SELECT @FileName='C:\boot.ini' EXEC Master.dbo.xp_fileexist @filename GO

File Exists   File is a Directory  Parent Directory Exists ----------- ------------------- ----------------------- 1                0                            1

Example 3 - Execute master.dbo.xp_fileexist without a valid file and directory with the results indicating failure for both the file and directory, in addition the number of rows is accurately suppressed with the NOCOUNT command Version - SQL Server 2000 and SQL Server 2005

SET NOCOUNT ON DECLARE @FileName varchar(255) SELECT @FileName='Z:\bootdoesnotexist.ini' EXEC Master.dbo.xp_fileexist @filename GO

File Exists   File is a Directory  Parent Directory Exists ----------- ------------------- ----------------------- 0                0                            0

Example 4 - Execute master.dbo.xp_fileexist with a valid file and directory with the results indicating 'File Found', in addition the number of rows is inaccurately suppressed with the NOCOUNT command in SQL Server 2000 and accurately in SQL Server 2005 Version - SQL Server 2000 and SQL Server 2005

SET NOCOUNT ON DECLARE @FileName varchar(255) DECLARE @File_Exists int SELECT @FileName='C:\boot.ini' EXEC Master.dbo.xp_fileexist @FileName, @File_Exists OUT IF @File_Exists = 1 PRINT 'File Found' ELSE PRINT 'File Not Found' GO

SQL Server 2000 (1 row(s) affected) File Found SQL Server 2005 File Found

Example 5 - Execute master.dbo.xp_fileexist with an invalid file and directory with the results indicating 'File Not Found', in addition the number of rows is inaccurately suppressed with the NOCOUNT command in SQL Server 2000 and accurately in SQL Server 2005 Version - SQL Server 2000 and SQL Server 2005

SET NOCOUNT ON DECLARE @FileName varchar(255) DECLARE @File_Exists int SELECT @FileName='C:\boot.ini' EXEC Master.dbo.xp_fileexist @FileName, @File_Exists OUT IF @File_Exists = 1 PRINT 'File Found' ELSE PRINT 'File Not Found' GO

SQL Server 2000 (1 row(s) affected) File Not Found SQL Server 2005 File Not Found

Example 6 - Execute master.dbo.xp_fileexist with a valid file and directory with the results stored in a temporary table Version - SQL Server 2000 and SQL Server 2005

SET NOCOUNT ON DECLARE @FileName varchar(255) CREATE TABLE #File_Results ( File_Exists int, File_is_a_Directory int, Parent_Directory_Exists int ) SELECT @FileName='C:\boot.ini' INSERT INTO #File_Results (File_Exists, file_is_a_directory, parent_directory_exists) EXEC Master.dbo.xp_fileexist @filename SELECT * FROM #File_Results DROP TABLE #File_Results GO

File Exists   File is a Directory  Parent Directory Exists ----------- ------------------- ----------------------- 1                0                            1

Example 7 - Execute master.dbo.xp_fileexist with an invalid file and directory with the results stored in a temporary table Version - SQL Server 2000 and SQL Server 2005

SET NOCOUNT ON DECLARE @FileName varchar(255) CREATE TABLE #File_Results ( File_Exists int, File_is_a_Directory int, Parent_Directory_Exists int ) SELECT @FileName='Z:\bootdoesnotexist.ini' INSERT INTO #File_Results (File_Exists, file_is_a_directory, parent_directory_exists) EXEC Master.dbo.xp_fileexist @filename SELECT * FROM #File_Results DROP TABLE #File_Results GO

File Exists   File is a Directory  Parent Directory Exists ----------- ------------------- ----------------------- 0                0                            0

Next Steps

• When you have the need to determine if a file exists or not, consider this tip as one means to do so.  Just keep in mind that the functionality may differ slightly between SQL Server 2000 and 2005.
• Keep in mind that the native result set may be a good indicator if a file exists.  So first check for the native result prior to building a custom need.  If you can save yourself the time, you might as well.
• Unfortunately, the Master.dbo.xp_fileexist extended stored procedure is undocumented.  As such, be sure to test appropriately with this code and validate the functionality as you apply hot fixes, patches and upgrade to the latest SQL Server version.
• Check out these related tips:
  • Accessing the Windows File System from SQL Server
• Special thanks is extended to Rudy Komacsar of the MSSQLTips.com for contributing these scripts to the community!

Why doesn’t ISNUMERIC work correctly? (SQL Spackle)

http://www.sqlservercentral.com/articles/ISNUMERIC()/71512/

This is a very old subject but, according to a lot of posts, a lot of folks still don't understand that ISNUMERIC is NOT an "IsAllDigits" function. There are many cases where you need to ensure that the string data you are working with includes only numeric digits. Most Developers will use the built in ISNUMERIC function to make such a check. Here's why that's a bad idea and what to do about it.

What is ISNUMERIC?

"Books OnLine" summarizes the description of the ISNUMERIC function as...
"Determines whether an expression is a valid numeric type."
...and that's a 100% accurate description that leaves much to be desired. Just what is a "valid numeric type"? Reading further in BOL (short for "Books OnLine), we find additional information:
"ISNUMERIC returns 1 when the input expression evaluates to a valid integer, floating point number, money or decimal type; otherwise it returns 0. A return value of 1 guarantees that expression can be converted to one of these numeric types."
Again, read the wording... "when the input expression evaluates to a valid integer", etc, etc. And, that's the catch. There are many different things that you may not expect that will evaluate to one of the data types listed in the description of ISNUMERIC and a lot of them are NOT the digits 0 thru 9. ISNUMERIC will return a "1" for all of them.
Let's consider the most obvious... what will ISNUMERIC('-10') return? What will ISNUMERIC('1,000') return? And how about the not-so-obvious... what will ISNUMERIC('0d1234') or ISNUMERIC('13e20') return? How about ISNUMERIC('1,2,3,4,5')? There are many different combinations of letters, numbers, and symbols that can actually be converted to numeric data types and ISNUMERIC will return a "1" for all of them. It's not a flaw... that's the way it's supposed to work!

What IS Actually Considered "Numeric" by ISNUMERIC?

This code will show all of the single characters that ISNUMERIC thinks of as "Numeric"...

--===== Return all characters that ISNUMERIC thinks is numeric
     -- (uses values 0-255 from the undocumented spt_Values table
     -- instead of a loop from 0-255)
 SELECT [Ascii Code]        = STR(Number),
        [Ascii Character]   = CHAR(Number),
        [ISNUMERIC Returns] = ISNUMERIC(CHAR(Number))
   FROM Master.dbo.spt_Values
  WHERE Type = 'P'
    AND Number BETWEEN 0 AND 255
    AND ISNUMERIC(CHAR(Number)) = 1

That code produces the following list of characters...

Ascii Code Ascii Character ISNUMERIC Returns 
---------- --------------- ----------------- 
         9                 1
        10                 1
        11                 1
        12                 1
        13                 1
        36 $               1
        43 +               1
        44 ,               1
        45 -               1
        46 .               1
        48 0               1
        49 1               1
        50 2               1
        51 3               1
        52 4               1
        53 5               1
        54 6               1
        55 7               1
        56 8               1
        57 9               1
       128 €               1
       160                 1
       163 £               1
       164 ¤               1
       165 ¥               1

What are those characters?

Ascii Code 9 is a TAB character and is included because a column of numbers is frequently delimited by a TAB.
Ascii Code 10 is a Line Feed character and is included because the last column of numbers is frequently terminated by a Line Feed character.
Ascii Code 11 is a Vertical Tab character and is included because the last column of numbers is frequently terminated by a Vertical Tab character.
Ascii Code 12 is a Form Feed character and is included because the last column numbers of the last row is sometimes terminated by a Form Feed character.
Ascii Code 13 is a Carriage Return character and is included because the last column of numbers is frequently terminated by a Carriage Return character.
Ascii Codes 36 (Dollar sign), 128 (Euro sign), 163 (British Pound sign), and 164 (Yen sign) are included because they are frequently used as enumerators to identify the type of number or, in this case, the currency type the number is meant to represent.
Ascii Codes 43 (Plus sign), 44 (Comma), 45 (Minus sign), and 46 (Decimal place) are included because they are frequently included in numeric columns to mark where on the number line the number appears and for simple formatting.
Ascii Code 160 is a special "hard space" and is included because it is frequently used to left pad numeric columns so the column of numbers appears to be right justified.
Ascii Code 32 is a "soft space" and is not included because a single space does not usually represent a column of numbers. Ascii Code 32 is, however, a valid numeric character when used to create right justified numbers as is Ascii Code 160 but a single Ascii Code 32 character is NOT numeric. In fact, a string of Ascii Code 32 spaces is not considered to be numeric but a string of spaces with even a single digit in it is considered to be numeric.
Ascii Code 164 is a special character and is included because it is frequently used by accountants and some software to indicate a total or subtotal of some type. It is also used by some to indicate they don't know what the enumerator is.
Ascii Codes 48 thru 59 are included because they represent the digits 0 through 9

Sets of Characters Treated as "Numeric" by ISNUMERIC

Do notice that "e" and "d" (everybody forgets about this) are not included as numeric in the results because a single "e" or "d is NOT considered to be numeric. HOWEVER, these letters represent two different forms of numeric notation (the one with the "e" is Scientific Notation). So, if you have anything that looks like the following, ISNUMERIC will identify them as "Numeric"...

SELECT ISNUMERIC('0d2345')
SELECT ISNUMERIC('12e34')

The "Regular" Solution

Hopefully, I've proven that ISNUMERIC is NOT the way to determine if a value or a column of values IS ALL DIGITS. So, what to do? We could write something really complex that loops through each character to see if it's a digit... or ... we can use a very simple (almost) regular-expression to do the dirty work for us. The formula is...
NOT LIKE '%[^0-9]%'
... and it can be used directly (preferred method for performance reasons)...

 SELECT *
   FROM sometable
  WHERE somecolumn NOT LIKE '%[^0-9]%'

... or you can create your own "IsAllDigits" function (this one is an "Inline Table Valued Function" or "iTVF").

 CREATE FUNCTION dbo.IsAllDigits 
/********************************************************************
 Purpose:
 This function will return a 1 if the string parameter contains only 
 numeric digits and will return a 0 in all other cases.  Use it in
 a FROM clause along with CROSS APPLY when used against a table.

 --Jeff Moden
********************************************************************/
--===== Declare the I/O parameters
        (@MyString VARCHAR(8000))
RETURNS TABLE AS
 RETURN (
         SELECT CASE 
                WHEN @MyString NOT LIKE '%[^0-9]%'
                THEN 1
                ELSE 0
                END AS IsAllDigits
        )

The reason why the formula works is that the "^" means "NOT". So the formula is stating "find everything that's not like something that has something not like a numeric digit." Doing the Boolean math, it means "If everything in the string is a digit from 0 to 9, return a 1".
Crack filled! ;-)
Thanks for listening, folks.

--Jeff Moden