Logical Reads in SQL Server 2008

Logical Reads count can be get by using follwoing ways
 Below are the ways to check logical Reads:
 1. set statistics io on
 2. sys.dm_exec_query_Stats by executing the below statement we can find detailed info about reads/writes select * from sys.dm_exec_query_Stats
 3. SQL Profiler: by executing the sql profiler on that database we can find out logical reads.. there are some other SQL DMV/F which also help us to get logical reads...

Summary Info: 
Logical Reads : Reading Data pages from Cache
Physical Reads : Reading Data pages from Hard Disk
Buffer Cach Hit Ratio: logical reads – physical reads)/logical read * 100%
note: Buffer cach hit ratio can be easily found using performance moniter...

Detailed Info: Logical Reads: Logical read indicates total number of data pages needed to be accessed from data cache to process query.
It is very possible that logical read will access same data pages many times, so count of logical read value may be higher than actual number of pages in a table. Usually the best way to reduce logical read is to apply correct index or to rewrite the query.

 Physical Reads Physical read indicates total number of data pages that are read from disk. In case no data in data cache, the physical read will be equal to number of logical read. And usually it happens for first query request. And for subsequent same query request the number will be substantially decreased because the data pages have been in data cache.

Buffer Cash Hit Ratio Buffer hit ratio will be calculated based on these two kinds of read as the following formula: (logical reads – physical reads)/logical read * 100%.
The high buffer hit ratio (if possible to near 100%) indicates good database performance on SQL Server level.
So use information from physical read and buffer hit ratio to measure performance in server level and logical read to measure individual query level Execess of the Logical Reads tends high memory Usage
There are some ways by which we can Reduce Logical Reads:
1. Improper/Useless/Insufficient Indexes: Indexes should be build on the basis of data access or retrieval process if any of the indexes is build on the columns which are not used in a query will leads to High Logical reads and will degrade the performance while reads and writing the data....
2.Poor Fill Factor/Page Density: Page use should should not be very less. otherwise large number of page will be used for small amount of data which will also leads to High Logical Reads....
 3.Wide Indexes: Indexing on the large number of columns will leads to high logical reads....
 4. Index scanning: if query is leads to index scanning on the table then logical reads will be high...

Deprecated Features in SQL Server 2012 Denali

Deprecated Features in SQL Server 2012 Denali


1. Old style join are no more supported like *= and =*

2. Compute and Compute by are no more supported

3. Added Restriction in Usage of Raiserror

RAISERROR in the format RAISERROR integer 'string' is discontinued

4. Fastfirstrow query hint is no more supported

5. Databaseproperty function is no more supported

6. 32-bit Address Windowing Extensions (AWE) and 32-bit Hot Add memory support are discontinued

7. SQL Mail is no more supported

8. sp_dboption is no more supported

9. with append clause on trigger is no more supported

10. I. sp_configure 'user instance timeout'

II. sp_configure 'user instances enabled'

11. 80 Compatibility level is not supported

12. 1. RESTORE DATABASE WITH DBO_ONLY

2. RESTORE LOG WITH DBO_ONLY

13. The Active Directory Helper service and the related components has been removed

sp_ActiveDirectory_Obj

sp_ActiveDirectory_SCP

sp_ActiveDirectory_Start



14. SQL Server 2012 Denali does not support SQL Server Distributed Management Objects (SQL-DMO) any more. SQL Server Management Objects (SMO) is the recommended alternative

15. SQL Server 2012 (Denali) no more supports the following backup commands.

BACKUP DATABASE WITH PASSWORD

BACKUP LOG WITH PASSWORD

BACKUP DATABASE WITH MEDIAPASSWORD

BACKUP LOG WITH MEDIAPASSWORD

16. SQL Server 2012 Denali does not support VIA protocol any more

Use TCP instead of VIA protocol

17. The ability for users to create new remote servers by using sp_addserver is discontinued. sp_addserver with the 'local' option remains available. Remote servers preserved during upgrade or created by replication can be used

18. sp_dropalias

19. The version parameter of PWDCOMPARE representing a value from a login earlier than SQL Server 2000 is discontinued

20. System table sys.database_principal_aliases is no more available

21. Xevent

databases_data_file_size_changed, databases_log_file_size_changed

eventdatabases_log_file_used_size_changed

locks_lock_timeouts_greater_than_0

locks_lock_timeouts

22. Set Options SET DISABLE_DEF_CNST_CHK is no more available

23. Data types text ,ntext,Image are no more avaiableData types



Data Transformation Services (DTS)

Support for migrating or running Data Transformation Services (DTS) packages has been discontinued in this release. The following DTS functionality has been discontinued.

• DTS runtime

• DTS API

• Package Migration Wizard for migrating DTS packages to the next version of Integration Services

• Support for DTS package maintenance in SQL Server Management Studio

• Execute DTS 2000 Package task

• Upgrade Advisor scan of DTS packages.

Execute DTS 2000 Package Task

The Execute DTS 2000 Package task has been discontinued.



To know the details about depreciated features in SQL Server 2012 Denli

Check below link:

http://msdn.microsoft.com/en-us/library/ms143729(v=sql.110).aspx

DeadLocks in SQL Server

Dead Lock: when two transactions are holding resources and both of them require before completion.
Once the dead lock situation is detected lock manager make one of the transactions as victim for rollback and raise error 1205 which is captured/handled properly

we can check Dead Lock , Blocking and Locking processes using bleow:
1. sp_who
2. sp_who2
3. select * from sys.sysprocesses
4. select  *  from      Sys.dm_tran_locks
5. Using activity montor(in sql server 2008 only)
6. Using  SQL Server Profiler(using template TSQL_Locks)
7. sp_lock
8. select * from master.dbo.syslockinfo

Locks and Dead Lock in SQL Server

Locks and Dead Lock in SQL Server 2008:


SQL Server uses locking and blocking mechanism to maintain data consistency in multiuser environment. Lock Manager is the SQL Server Internal governing body to handle locking scenarios like:

1. Determine Appropriate lock to be acquire/Release on resource

2. How much time will retain the lock

3. Decide when processes are allowed to modify data so that reads should be consistent

4. I. When SQL Server started with lock set to 0,lock manager allocate 2 percent of the memory allocated for SQL Server for an initial pool of lock structure(SQL 2000)

II. When SQL Server started with lock set to 0,lock manager allocate appropriate percent of the memory allocated for SQL Server for an initial pool of l 2500 lock structure(SQL 2005) as soon as lock pool is exhausted lock manager request for additional memory

III. The dynamic lock pool does not acquire more than 60 percent of the memory allocated to the Database Engine. After the lock pool has reached 60 percent of the memory acquired by an instance of the Database Engine, or no more memory is available on the computer, further requests for locks generate an error

5. Resolve Dead locks

6. compatibility between locks



SQL Server has seven different locking modes and three different lock types:

Locking modes are:

1. Shared locks(S): placed on resource for read operation and compatible for other shared and update lock except exclusive lock. When repeatable read or locking hint is used then shared lock is retained till duration of the transaction otherwise shared lock released as soon as read is completed

Equation: S on S = OK, S on U = OK, S on X = WAIT



2. Update Lock(U): placed on resource where shared lock is required and need to upgrade to exclusive lock. one transaction can acquire update lock on resource once the modification is required on resource update lock upgrade to exclusive lock

Equation: U on S = OK, U on U = WAIT, U on X = WAIT



3. Exclusive Lock(X): placed on resource for data modification and not compatible with any other type of lock .only nolock hint and read uncommitted isolation level overrides exclusive lock functionality.

Equation: X on S = WAIT, X on U = WAIT, X on X = WAIT



4. Intent(IS,IX,SIX):placed on resource to improve performance and locking efficiency by placing at high level i.e. table level



5. Schema(Sch-M,Sch-S):Sch-M is placed on resource while schema modification like while adding column to a table and Sch-S are placed on object/resource while query are being compiled or executed.

Equation: Sch-M Block all other operations until lock is released

Sch-S Locks are not compatible with Sch-M Locks







6. Bulk Update(BU):Placed on resource for Bulk insert and update of the data, these locks allow multiple bulk insert threads to access table but does not allow other processes to access the table,BU locks is enable by using TABLOCK hint or by using sp_tableoption for table lock for bulk load table option.



7. Key Range: Placed on the range of rows to protect against phantom insertion/deletion in a record set accessed by transaction, these locks are used by transaction using SERILIZABLE transaction isolation level.





These three different lock mode can be acquire at Row, Page, Table level and scope

Up to session, transaction, and cursor.



Dead Lock: when two transactions are holding resources and both of them require before completion.

Once the dead lock situation is detected lock manager make one of the transactions as victim for rollback and raise error 1205 which is captured/handled properly.