Search This Blog

Saturday, June 19, 2010

Data auditing in SQL Server

There are 2 ways we can audit the SQL Server events to the tracefile (people call it audit log file).

- setting sp_configure parameter 'c2 audit mode' to 1.This will automatically capture all the audit events for all the databases and all the columns . You cannot modify it .Even if you try to , it will
not take the changes made manually .

- Creating our own trace for selected events and columns .Please check BOL for it .

In case you want to go through the second option and that is to create our own trace please see the demo below:

Step 1
In this step we are creating test_yasir trace in C: drive.Then we are setting the Events and columns adn settin gthem to ON .I have choosen a few events and columns .

declare @TraceIdOut int
exec sp_trace_create @TraceIdOut OUTPUT,6, N'c:\test_Yasir'
PRINT @TraceIdOut

declare @On bit
SET @On = 1
exec sp_trace_setevent @TraceIdOut, 14, 6, @On
exec sp_trace_setevent @TraceIdOut, 14, 7, @On
exec sp_trace_setevent @TraceIdOut, 14, 8, @On
exec sp_trace_setevent @TraceIdOut, 14, 9, @On
exec sp_trace_setevent @TraceIdOut, 14, 10, @On
exec sp_trace_setevent @TraceIdOut, 15, 6, @On
exec sp_trace_setevent @TraceIdOut, 15, 7, @On
exec sp_trace_setevent @TraceIdOut, 15, 8, @On
exec sp_trace_setevent @TraceIdOut, 15, 9, @On
exec sp_trace_setevent @TraceIdOut, 15, 10, @On
exec sp_trace_setevent @TraceIdOut, 20, 6, @On
exec sp_trace_setevent @TraceIdOut, 20, 7, @On
exec sp_trace_setevent @TraceIdOut, 20, 8, @On
exec sp_trace_setevent @TraceIdOut, 20, 9, @On
exec sp_trace_setevent @TraceIdOut, 20, 10, @On


exec sp_trace_setevent @TraceIdOut, 104, 1, @On
exec sp_trace_setevent @TraceIdOut, 104, 3, @On
exec sp_trace_setevent @TraceIdOut, 104, 6, @On
exec sp_trace_setevent @TraceIdOut, 104, 7, @On
exec sp_trace_setevent @TraceIdOut, 104, 8, @On
exec sp_trace_setevent @TraceIdOut, 104, 7, @On
exec sp_trace_setevent @TraceIdOut, 104, 8, @On
exec sp_trace_setevent @TraceIdOut, 104, 10, @On
exec sp_trace_setevent @TraceIdOut, 104, 11, @On
exec sp_trace_setevent @TraceIdOut, 104, 14, @On
exec sp_trace_setevent @TraceIdOut, 104, 22, @On
exec sp_trace_setevent @TraceIdOut, 104, 26, @On
exec sp_trace_setevent @TraceIdOut, 104, 35, @On
exec sp_trace_setevent @TraceIdOut, 104, 7, @On
exec sp_trace_setevent @TraceIdOut, 104, 8, @On
exec sp_trace_setevent @TraceIdOut, 104, 7, @On
exec sp_trace_setevent @TraceIdOut, 104, 8, @On

exec sp_trace_setevent @TraceIdOut, 107, 1, @On
exec sp_trace_setevent @TraceIdOut, 107, 3, @On
exec sp_trace_setevent @TraceIdOut, 107, 6, @On
exec sp_trace_setevent @TraceIdOut, 107, 7, @On
exec sp_trace_setevent @TraceIdOut, 107, 8, @On
exec sp_trace_setevent @TraceIdOut, 107, 7, @On
exec sp_trace_setevent @TraceIdOut, 107, 8, @On
exec sp_trace_setevent @TraceIdOut, 107, 10, @On
exec sp_trace_setevent @TraceIdOut, 107, 11, @On
exec sp_trace_setevent @TraceIdOut, 107, 14, @On
exec sp_trace_setevent @TraceIdOut, 107, 22, @On
exec sp_trace_setevent @TraceIdOut, 107, 26, @On
exec sp_trace_setevent @TraceIdOut, 107, 35, @On
exec sp_trace_setevent @TraceIdOut, 107, 7, @On
exec sp_trace_setevent @TraceIdOut, 107, 8, @On
exec sp_trace_setevent @TraceIdOut, 107, 7, @On
exec sp_trace_setevent @TraceIdOut, 107, 8, @On

exec sp_trace_setevent @TraceIdOut, 106, 1, @On
exec sp_trace_setevent @TraceIdOut, 106, 3, @On
exec sp_trace_setevent @TraceIdOut, 106, 6, @On
exec sp_trace_setevent @TraceIdOut, 106, 7, @On
exec sp_trace_setevent @TraceIdOut, 106, 8, @On
exec sp_trace_setevent @TraceIdOut, 106, 7, @On
exec sp_trace_setevent @TraceIdOut, 106, 8, @On
exec sp_trace_setevent @TraceIdOut, 106, 10, @On
exec sp_trace_setevent @TraceIdOut, 106, 11, @On
exec sp_trace_setevent @TraceIdOut, 106, 14, @On
exec sp_trace_setevent @TraceIdOut, 106, 22, @On
exec sp_trace_setevent @TraceIdOut, 106, 26, @On
exec sp_trace_setevent @TraceIdOut, 106, 35, @On
exec sp_trace_setevent @TraceIdOut, 106, 7, @On
exec sp_trace_setevent @TraceIdOut, 106, 8, @On
exec sp_trace_setevent @TraceIdOut, 106, 7, @On
exec sp_trace_setevent @TraceIdOut, 106, 8, @On


exec sp_trace_setevent @TraceIdOut, 105, 1, @On
exec sp_trace_setevent @TraceIdOut, 105, 3, @On
exec sp_trace_setevent @TraceIdOut, 105, 6, @On
exec sp_trace_setevent @TraceIdOut, 105, 7, @On
exec sp_trace_setevent @TraceIdOut, 105, 8, @On
exec sp_trace_setevent @TraceIdOut, 105, 7, @On
exec sp_trace_setevent @TraceIdOut, 105, 8, @On
exec sp_trace_setevent @TraceIdOut, 105, 10, @On
exec sp_trace_setevent @TraceIdOut, 105, 11, @On
exec sp_trace_setevent @TraceIdOut, 105, 14, @On
exec sp_trace_setevent @TraceIdOut, 105, 22, @On
exec sp_trace_setevent @TraceIdOut, 105, 26, @On
exec sp_trace_setevent @TraceIdOut, 105, 35, @On
exec sp_trace_setevent @TraceIdOut, 105, 7, @On
exec sp_trace_setevent @TraceIdOut, 105, 8, @On
exec sp_trace_setevent @TraceIdOut, 105, 7, @On
exec sp_trace_setevent @TraceIdOut, 105, 8, @On


Step 2 :

In this step we wil apply the filter since you said you need to audit only a user database.We will achieve it using sp_trace_setfilter

sp_trace_setfilter 3,3,0,0,1

In this example I have set the filter on databaseid (to 1 which is master) in traceid 3

Step 3:
In this step we will first confirm if our trace is showing up in the metadata.Do a select * from sys.traces and check the trace you created as well its trace id .
Then start the trace (which is 1) using sp_trace_setstatus

example :
sp_trace_setstatus 3,1

Here traceid is 3 and staus is 1
Further , if you want to add or remove any event use sp_trace_setevent after stopping the trace using sp_trace_setstatus


But in this method there is a problem .The problem is that , if you restart the instance the trace metadata will be washed from the sys.traces DMV.
So you will have to manually run it again .Further the physical trace file (log file) still exist.So you will get the error while creaing the trace .To over come this :

1) I have added the datetime in the file name .So it will create a unique file each minute.
2) I have encapsulated the query into an SP and pinned it to SQL Server startup.

So now

step 1 would be

create proc audit_trace as
declare @TraceIdOut int
Declare @D1 nvarchar(30)
Declare @D2 nvarchar(30)
Declare @D3 nvarchar(30)
Declare @D4 nvarchar(30)
Declare @D5 nvarchar(30)
Declare @trace_name nvarchar(256)

SELECT @D1=DATENAME(Day, GETDATE())
SELECT @D2=DATENAME(month, GETDATE())
SELECT @D3=DATENAME(year, GETDATE())
SELECT @D4=DATENAME(hour, GETDATE())
SELECT @D5=DATENAME(minute, GETDATE())

set @trace_name='c:\trace_'+@d1+'_'+@d2+'_'+@d3+'_'+@d4+'_'+@d5+'_'
print @trace_name

--set @trace_name = 'c:\trace_'+@trace_date+'.trc'
--print @trace_name
exec sp_trace_create @TraceIdOut OUTPUT,6, @trace_name
PRINT @TraceIdOut

declare @On bit
SET @On = 1
exec sp_trace_setevent @TraceIdOut, 14, 6, @On
exec sp_trace_setevent @TraceIdOut, 14, 7, @On
exec sp_trace_setevent @TraceIdOut, 14, 8, @On
exec sp_trace_setevent @TraceIdOut, 14, 9, @On
exec sp_trace_setevent @TraceIdOut, 14, 10, @On
exec sp_trace_setevent @TraceIdOut, 15, 6, @On
exec sp_trace_setevent @TraceIdOut, 15, 7, @On
exec sp_trace_setevent @TraceIdOut, 15, 8, @On
exec sp_trace_setevent @TraceIdOut, 15, 9, @On
exec sp_trace_setevent @TraceIdOut, 15, 10, @On
exec sp_trace_setevent @TraceIdOut, 20, 6, @On
exec sp_trace_setevent @TraceIdOut, 20, 7, @On
exec sp_trace_setevent @TraceIdOut, 20, 8, @On
exec sp_trace_setevent @TraceIdOut, 20, 9, @On
exec sp_trace_setevent @TraceIdOut, 20, 10, @On


exec sp_trace_setevent @TraceIdOut, 104, 1, @On
exec sp_trace_setevent @TraceIdOut, 104, 3, @On
exec sp_trace_setevent @TraceIdOut, 104, 6, @On
exec sp_trace_setevent @TraceIdOut, 104, 7, @On
exec sp_trace_setevent @TraceIdOut, 104, 8, @On
exec sp_trace_setevent @TraceIdOut, 104, 7, @On
exec sp_trace_setevent @TraceIdOut, 104, 8, @On
exec sp_trace_setevent @TraceIdOut, 104, 10, @On
exec sp_trace_setevent @TraceIdOut, 104, 11, @On
exec sp_trace_setevent @TraceIdOut, 104, 14, @On
exec sp_trace_setevent @TraceIdOut, 104, 22, @On
exec sp_trace_setevent @TraceIdOut, 104, 26, @On
exec sp_trace_setevent @TraceIdOut, 104, 35, @On
exec sp_trace_setevent @TraceIdOut, 104, 7, @On
exec sp_trace_setevent @TraceIdOut, 104, 8, @On
exec sp_trace_setevent @TraceIdOut, 104, 7, @On
exec sp_trace_setevent @TraceIdOut, 104, 8, @On

exec sp_trace_setevent @TraceIdOut, 107, 1, @On
exec sp_trace_setevent @TraceIdOut, 107, 3, @On
exec sp_trace_setevent @TraceIdOut, 107, 6, @On
exec sp_trace_setevent @TraceIdOut, 107, 7, @On
exec sp_trace_setevent @TraceIdOut, 107, 8, @On
exec sp_trace_setevent @TraceIdOut, 107, 7, @On
exec sp_trace_setevent @TraceIdOut, 107, 8, @On
exec sp_trace_setevent @TraceIdOut, 107, 10, @On
exec sp_trace_setevent @TraceIdOut, 107, 11, @On
exec sp_trace_setevent @TraceIdOut, 107, 14, @On
exec sp_trace_setevent @TraceIdOut, 107, 22, @On
exec sp_trace_setevent @TraceIdOut, 107, 26, @On
exec sp_trace_setevent @TraceIdOut, 107, 35, @On
exec sp_trace_setevent @TraceIdOut, 107, 7, @On
exec sp_trace_setevent @TraceIdOut, 107, 8, @On
exec sp_trace_setevent @TraceIdOut, 107, 7, @On
exec sp_trace_setevent @TraceIdOut, 107, 8, @On

exec sp_trace_setevent @TraceIdOut, 106, 1, @On
exec sp_trace_setevent @TraceIdOut, 106, 3, @On
exec sp_trace_setevent @TraceIdOut, 106, 6, @On
exec sp_trace_setevent @TraceIdOut, 106, 7, @On
exec sp_trace_setevent @TraceIdOut, 106, 8, @On
exec sp_trace_setevent @TraceIdOut, 106, 7, @On
exec sp_trace_setevent @TraceIdOut, 106, 8, @On
exec sp_trace_setevent @TraceIdOut, 106, 10, @On
exec sp_trace_setevent @TraceIdOut, 106, 11, @On
exec sp_trace_setevent @TraceIdOut, 106, 14, @On
exec sp_trace_setevent @TraceIdOut, 106, 22, @On
exec sp_trace_setevent @TraceIdOut, 106, 26, @On
exec sp_trace_setevent @TraceIdOut, 106, 35, @On
exec sp_trace_setevent @TraceIdOut, 106, 7, @On
exec sp_trace_setevent @TraceIdOut, 106, 8, @On
exec sp_trace_setevent @TraceIdOut, 106, 7, @On
exec sp_trace_setevent @TraceIdOut, 106, 8, @On


exec sp_trace_setevent @TraceIdOut, 105, 1, @On
exec sp_trace_setevent @TraceIdOut, 105, 3, @On
exec sp_trace_setevent @TraceIdOut, 105, 6, @On
exec sp_trace_setevent @TraceIdOut, 105, 7, @On
exec sp_trace_setevent @TraceIdOut, 105, 8, @On
exec sp_trace_setevent @TraceIdOut, 105, 7, @On
exec sp_trace_setevent @TraceIdOut, 105, 8, @On
exec sp_trace_setevent @TraceIdOut, 105, 10, @On
exec sp_trace_setevent @TraceIdOut, 105, 11, @On
exec sp_trace_setevent @TraceIdOut, 105, 14, @On
exec sp_trace_setevent @TraceIdOut, 105, 22, @On
exec sp_trace_setevent @TraceIdOut, 105, 26, @On
exec sp_trace_setevent @TraceIdOut, 105, 35, @On
exec sp_trace_setevent @TraceIdOut, 105, 7, @On
exec sp_trace_setevent @TraceIdOut, 105, 8, @On
exec sp_trace_setevent @TraceIdOut, 105, 7, @On
exec sp_trace_setevent @TraceIdOut, 105, 8, @On

/*adding the SP to execute at SQl Server startup */
exec sp_procoption N'audit_trace', 'startup', 'on'

Step 2 and 3 will be same as mentioned above in the begening .

Disadvantage
-------------
Simple ..Its resource consuming .Do not add a lot of columns in the trace .Do specifically what you want to audit.
It entirely depends what all columns you are auditing.
You need to keep the instance under testing phase and monitor the resource consumption due to tracing .
Clear the client that we need to make sure that we have fast disks ,more/faster CPUs, IO processing capabbilities and enough RAM in case they need to do extensive auditing (if there are performance issues).

Using WMI and SQL Agent to fire low memory threshold alert ...

This will work perfectly ....The only thing I wanted to add to the table was when it alerts you it should also fill the column with available MBytes so that you know how much memory was available .......But after trying it for 2 days , I realized that the class through which I am checking another class (Perfmon >> memory >> Available Mbytes) does not have a column for this.I am using "_instance modification" class .May be its due to this that the alert is fired but the job fails when it inserts the availableMbytes ...because this column is not in _instancemodificationevent class...the error number also suggests that .

By the way this one will only alert if your RAM is > 256 every 10 seconds ....this is because I wanted to test it ....you need to modify it to < 256 and every 300 seconds ...so that you get alert every 5 mins or whatever you decide ....


/*******************************************************************************************
* This script will create an Alert to Monitor Physical RAM reaching a low threshold.
* The alert will run a job and the job will enter data in a table.
*******************************************************************************************/

/* Step 1: creating the table to capture the Event information */

USE Master
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[memory]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[memory]
GO

CREATE TABLE [dbo].[memory] (
[PostTime] [datetime] NOT NULL default (getdate()) ,
[computerName] sql_variant Not Null ,
[RecordID] [int] IDENTITY (1,1) NOT FOR REPLICATION NOT NULL,
[Flag] [int] NOT NULL CONSTRAINT [DF_MEMORY_Flag] DEFAULT ((0)),
) ON [PRIMARY]
GO

CREATE INDEX [Memory_IDX01] ON [dbo].[memory]([recordid]) WITH FILLFACTOR = 100 ON [PRIMARY]
GO

/*Step 2 : Creating the Job that will enter values into the table we created above*/
/*Service account and sql operator option are optional*/

USE [msdb]
GO

IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'Capture memory Event')
EXEC msdb.dbo.sp_delete_job @job_name = N'Capture Memory Event', @delete_unused_schedule=1

GO
--DECLARE @ServiceAccount varchar(128)
--SET @ServiceAccount = N''
--DECLARE @SQLOperator varchar(128)
--SET @SQLOperator = N''

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'Capture Memory Event',
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=3,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Job for responding to memory events',
@category_name=N'[Uncategorized (Local)]',
--@owner_login_name=@ServiceAccount,
--@notify_email_operator_name=@SQLOperator,
@job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/*Step 3: Insert graph into LogEvents*/


EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Insert data into LogEvents',
@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'
declare @@server sql_variant
select @@server =serverproperty (''machinename'')

INSERT INTO memory (
PostTime,
Computername
)

VALUES (
GETDATE(),
@@server)
',
@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

/*Creating the alert and associating it with the Job to be fired */

USE [msdb]
GO

IF EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Respond to Memory_event')
EXEC msdb.dbo.sp_delete_alert @name=N'Respond to memory_event'

GO

DECLARE @server_namespace varchar(255)
SET @server_namespace = N'\\.\root\Cimv2\'

EXEC msdb.dbo.sp_add_alert @name=N'Respond to memory_event',
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=0,
@category_name=N'[Uncategorized]',
@wmi_namespace=N'\\.\root\Cimv2',
@wmi_query=N'SELECT * FROM __InstanceModificationEvent WITHIN 10 WHERE TargetInstance ISA ''Win32_PerfFormattedData_PerfOS_Memory'' AND TargetInstance.AvailableBytes > 256',
@job_name='Capture memory Event' ;

--EXEC msdb.dbo.sp_add_notification @alert_name=N'Respond to memory_event', @operator_name=N'Test', @notification_method = 1
--GO

--/* Step 5: Create a stored proc for sending the [Create_user] information as .CSV file */

--Create proc [dbo].[Deadlock_rpt]
--as
--DECLARE @SQL varchar(Respond to memory_event2000)
--DECLARE @date varchar (2000)
--DECLARE @File varchar(1000)
--select @date= convert(date,GETDATE())
--SET @SQL = 'select * from [Create_user] where flag = 0'
--SET @File = '[Create_user] report'+@date+'.csv'

--EXECUTE msdb.dbo.sp_send_dbmail
--@profile_name = 'test',
--@recipients = 'your email.com',
--@subject = 'low memory threshold reached...',
--@body = '***URGENT***Attached please find the low memory threshold report',
--@query =@SQL ,
--@attach_query_result_as_file = 1,
--@query_attachment_filename = @file,
--@query_result_header = 1,
--@query_result_separator = ' ',
--@query_result_no_padding = 1,
--@query_result_width = 32767


--/* Step 6: Changing the flag to 1 so that next time this information is not sent*/
--update dbo.[Create_user] set flag = 1 where flag = 0
--go

Printing DBCC inputbuffer for any SPID > 50

It helps in many cases .For example if SPIDS are involved in blocking .and we are doing inputbuffer one by one.
For blocking you can just add : and blocked !=0 in the sysprocesses query
Similarly you can modify it for waittype,waittime, CPU etc (as per your need)

--Input buffers
PRINT 'Input buffer SPIDs'

declare @spid smallint
declare @i_buff_string char(30)
set nocount on
declare bufCursor CURSOR FOR SELECT spid from master.dbo.sysprocesses where spid > 50
FOR READ ONLY
open bufCursor
fetch next from bufCursor into @spid
while (@@fetch_status <> -1)
begin
SET @i_buff_string = ('DBCC INPUTBUFFER (' + convert(char(6),@spid) +')')
PRINT '-> '+@i_buff_string
exec (@i_buff_string)
PRINT ''
fetch next from bufCursor into @spid
end
close bufCursor
deallocate bufCursor

Why do we need the Non-clustered Index

I was asked this question by an SME , So I thought let me pass on if it makes sense .

Lets create a table test which has a few columns from Person.address table (Adventureworks database)
select addressid,addressline1,addressline2,postalcode,stateprovinceid into test from Person.Address

On this table I created clustered index on AddressID and stateprovinceID
CREATE UNIQUE CLUSTERED INDEX [combined index] ON [dbo].[testo]
(
[addressid] ASC,
[stateprovinceid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

Another non-clustered index was created with include option
CREATE NONCLUSTERED INDEX [provinceID] ON [dbo].[testo]
(
[postalcode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]


*** BUT I WILL DISABLE THE INDEX ********

Now I ran a few queries:
-----------------------------------
select * from test where postalcode >'98501'



select * from test where postalcode >'98501' order by stateprovinceid



select postalcode from testo where postalcode >'98501' order by stateprovinceid




select addressline1,addressline2 from testo where postalcode >'98501' order by stateprovinceid



Notice that In all the cases above we have the clustered index Scan and not a simple SCAN .
So the clustered index is being used .And since Its visible that a clustered index is being used there is no need of any other index .Why ............because the data itself lies in the clustered index and we can not improve the plan anymore.

Lets see why a scan would be used
Your Data is sorted on the bases of 2 columns in the clustered index .So any query will use that Index because the data lies there .So in worst to worst case you will have a Clustered Index Scan (Better than a Index scan [heap scan]).

But why Scan and not Seek
Because the columns you are using in the where clause or the columns you are looking for as an output have no index or you can say they are not indexed (indirectly , working like a
heap).So , You will do a scan for those Rows .But since its ordered already , So it will be a clustered Scan.

Now you enable the non-clustered index and run few queries

select postalcode from testo where postalcode >'98501'


It did an index seek .remember it is not a Clustered index seek like below

select addressid from testo where addressid < 100



The reason is that In the earlier case a non-cluseterd index was used to find out the postalcode very quickly but since the data was in the clustered index it needed to point to that Leaf and hence some more efforts .But these efforts were far less than Clustered index scan .

That is the reason we need to Non-clustered index .TO reduce the ROW Lookups or Bookmark lookups.which are the pointers to the clustered index.

Happy learning!!

xp_sendmail: failed with mail error 0x80040111

Hi guys,
Its been long since i posted any new stuff ...so far I've posted nothing related SQL 2000 but i thought this one is needed .

We recently faced a SQL Mail issue on WIN 2k Server .Anyone who has worked on SQL Mail issue in the past would be knowing how irritating this issue can be sometimes.But its actually our dis-interest that makes this issue difficult.Somehow we don't like such issues :) ...Since we resolved this issue and went through different permutations and combinations I would like to discuss this with you all so that in future we can save some time and resources on the same issue.This includes

-> Some very basic understanding on SQL mail .
-> What you should do/do not do when SQL Mail issue comes up.

Brief understanding on SQL Mail
--------------------------------
There are two ways you can send mails in SQL Server 2000 (Not talking about 2005 or 2008).

-> SQL Agent Mail
-> SQL Mail .

Yes , you heard it right .SQL Agent uses its own Extended M(ail)API session where as SQL Mail uses its own .However,Both SQL Mail and SQL Agent Mail connect with Microsoft Exchange Server, Microsoft Windows NT® Mail, or a Post Office Protocol 3 (POP3) server using Mail Profile. It is possible to use different profiles for SQL Mail and SQL Agent Mail at the same time.
Extended MAPI is nothing but a better version over Simple MAPI .Anyway, we don't have to go in this direction .

The SPs that we use in SQL Mail are :

xp_startmail <-- used to start the mail session
xp_sendmail <-- To send mail .This invokes sp_processmail.
xp_findnextmsg <--sp_processmail uses this SP
xp_readmail <-- used by sp_processmail
xp_deletemail<--deletes the message.
sp_processmail <-- processses the mail
xp_stopmail<-- Stops the mail session

We will concentrate on First two and the last one in the list above.You can read about these SPs under : http://msdn.microsoft.com/en-us/library/ms186841.aspx?PHPSESSID=lm71lj7i6gj5fjtebtb2srl4n3
This link is for sp_findnextmsg but has link to all other SPs and XPs.


The Issue that we faced.
-------------------------
One of the job was not sending the mail after completion .SQL Mail was connecting to Exchange server successfully before .
Outlook 2000 client was installed on the Win 2K server (and was working fine under SQL Server service account).
Although we concentrated mostly troubleshooting SQLMail it was SQL Agent mail issue actually .
But Since troubleshooting steps are same for both , we resolved the issue .

TroubleShooting done

-> Logged in to the Server > opened the QA and ran sp_stopmail .Success
-> Sp_startmail ....Took 10 mins to start and with error.
-> The error was :
Server: Msg 18025, Level 16, State 1, Line 0
xp_startmail: failed with mail error 0x80040111
-> We tried to send the test mail using xp_sendmail
xp_sendmail 'abhay.chaudhary@in.ibm.com','test'
Server: Msg 18025, Level 16, State 1, Line 0
xp_sendmail: failed with mail error 0x80040111
-> Assuming that the Profile might be corrupt we created nwe profile through control panel >> Mail option .
-> To our surprise we could not see that profile in the drop down list of both SQL Mail and Agent properties.
-> So we failed there as well .We have the new profile , but we cant use it .Another Big question ? WHY ?
-> Agent logs were showing this :[260] Unable to start mail session (reason: Unable to logon (reason: Unable to get the default MAPI Message Store due to MAPI
error 273: The information store could not be opened); check the mail profile and/or the SQLSERVERAGENT service startup account)
-> After wasting time on 18025 we concentrated on Error 0x80040111
-> Found KB 238119 (List of Extended MAPI numeric result codes)
-> found that the error means : MAPI_E_LOGON_FAILED
-> Now we were clear that this is an authentication issue.
-> SQL Agent mail was not able to talk to Exchange Server.
-> The exchange Engineer cleared that since both the servers can ping each other and we can use outlook web using the same account under Exchange is running , there is no isues with
Exchange Mail box .
-> He was pretty convincing.
-> Then we found that (Actually we already knew that )in the SQL Mail configuration it was mentioned that we need to make sure that in we are logged in to the server using the SQL Server
service account.
-> That is what we have not done and were not able to do actually (Since begening) because of some permission issues on that account while logging on to the Win 2k Server.
-> Since rest all trials were failing , we decided to force ourselves for one last time in resolving this issue after loggin gon tho the server using SQL Server domain account .
-> The Intel team helped us in doing that .
-> We logged on to the server >> created a new profile >> Changed the SQL Agent setting to point to that profile (which was now visible..wow).
-> Started the mail session using xp_startmail.
SQL Mail session started.
-> Sent a test mail : xp_sendmail 'abhay.chaudhary@in.ibm.com','test'
Mail sent.
-> That resolved the issue.

Do's to solve this issue
--------------------------
-> Ping the exchange server or the POP3 Server and make sure its a success.
-> Make sure that the Domain account for SQL Server(including agent) , Exchange and OS are same .
-> Stop the mail session first as it might be Stuck.
-> Start the mail session .If it works , fine .
-> If not then read the MAPI error and match it with the KB 238199 to understand the root cause .
-> In one of the situations when I was testing it on Lotus Notes to POP 3 account on Gmail I got 8004011d which means MAPI_E_FAILONEPROVIDER (due to slow network)
-> Check the Agent logs and make sure MAPI32.dll exists (Using MAPI32.DLL from C:\WINDOWS\SYSTEM32 (version 1.0.2536.0))
-> Make sure the mapistub.dll exists in C:\WINDOWS\SYSTEM32 folder
-> You can also test you MAPI profile : xp_test_mapi_profile 'hello'
-> Create the new profile and see if that helps .If it does it means that the previous profile was corrupt(We did not test this in our case and created a new profile straightaway).

Don'ts
---------
Never try to resolve this issue without logging on to the server with SQL Server service account .

Hope this helps .
let me know your comments ..