Tuesday, February 21, 2012

ACID properties of transactions

In the context of transaction processing, the acronym ACID refers to the four key properties of a transaction: atomicity, consistency, isolation, and durability.

All changes to data are performed as if they are a single operation. That is, all the changes are performed, or none of them are.
For example, in an application that transfers funds from one account to another, the atomicity property ensures that, if a debit is made successfully from one account, the corresponding credit is made to the other account.
Data is in a consistent state when a transaction starts and when it ends.
For example, in an application that transfers funds from one account to another, the consistency property ensures that the total value of funds in both the accounts is the same at the start and end of each transaction.
The intermediate state of a transaction is invisible to other transactions. As a result, transactions that run concurrently appear to be serialized.
For example, in an application that transfers funds from one account to another, the isolation property ensures that another transaction sees the transferred funds in one account or the other, but not in both, nor in neither.
After a transaction successfully completes, changes to data persist and are not undone, even in the event of a system failure.
For example, in an application that transfers funds from one account to another, the durability property ensures that the changes made to each account will not be reversed.

Saturday, February 11, 2012

How to get a history of SQL scripts that have been run on a DB

If you don’t have any sort of auditing mechanism enabled, its really hard to find get these information. But you may get that info from the cached plan. p

first step is to make sure that “optimize for ad hoc workloads” option is disabled on the sql server.

EXEC sp_CONFIGURE 'show advanced options',1
EXEC sp_CONFIGURE 'optimize for ad hoc workloads'

if the configured value is 1, then you may find those result using the following query which uses one of my favorite dmvs

SELECT  dest.text,deqs.last_execution_time
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC

remember it is not guaranteed to return a results; the dmvs stores the data from the last sql server service start.

Thursday, February 2, 2012

Find all SQL Objects with QUOTED_IDENTIFIER set to OFF

Whenever you create or recreate an SP, make sure QUOTED_IDENTIFIER is set to ON. The default value of OFF is there for backward compatibility only. Many modern features of SQL Server (such as filtered indexes, computed persisted columns, xml columns ) require Quoted Identifiers enabled.

Here’s is the script to list all objects with QUOTED_IDENTIFIER set to OFF

  1: SELECT  SCHEMA_NAME(s.[schema_id]) + '.' + s.name AS name
  2:        ,s.create_date
  3:        ,s.modify_date
  4:        ,OBJECTPROPERTY(s.[object_id], 'ExecIsQuotedIdentOn') AS IsQuotedIdentOn
  5:        ,s.type_desc AS object_type
  6: FROM    sys.objects s
  7: WHERE   s.type IN ( 'P', 'TR', 'V', 'IF', 'FN', 'TF' )
  8:         AND OBJECTPROPERTY(s.[object_id], 'ExecIsQuotedIdentOn') = 0
  9: ORDER BY SCHEMA_NAME(s.[schema_id]) + '.' + s.name DESC 

Jobs failing due to "incorrect settings: QUOTED_IDENTIFIER"

Did you ever noticed after adding a filtered index or a Computed column, jobs/stored procedures that were using these tables suddenly started failing with the following error

Executed as user: XXXXX\XXXXXX. DELETE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations. [SQLSTATE 42000] (Error 1934).  The step failed.

The work around of this is to add SET QUOTED_IDENTIFIER ON to each job step.