Tuesday, August 20, 2013

Dynamic Management View

Dynamic Management View

Brief Summary

  • Introduced in 2005
  • They are system views
  • Basically they are very useful to DBAs to get database / server level information in a most simplest and fastest way
  • Really helpful in monitoring for DBAs
  • Will be prefixed with dm_
  • Will be part of sys schema


Total 12 categories

  • DB Related
  • CLR Related
  • Full Text Search Related
  • Transaction Related
  • Index Related
  • OS Related
  • Replication Related
  • DB Mirroring Related
  • Execution Related
  • I/O Related
  • Query Notification Related
  • Service Brocker Related

Few Useful DMVs

  • sys.dm_db_index_physical_stats
  • sys.dm_os_performance_counters
  • sys.dm_exec_query_stats

MS SQL 2008 TDE

MS SQL 2008 TDE

Introduction


Transparent Data Encryption (TDE) is the feature of MS SQL Server 2008. Data is encrypted before it is written to disk and decrypted when it is read from disk.  It is so easy to implement, just follow some steps and that’s all.

By the new feature of MS SQL 2008 TDE the backup files are also encrypted, when we take STANDARD BACKUP.

Before MS SQL Server 2008 it is not possible to secure the Backup Data. Anyone can restore and read the sensitive data that any developer does not want.
In this article we are trying to discuss related to TDE feature of MS SQL Server 2008.
Here we assume that the we all know about the Encryption Mechanism.

So what are the Steps that we must follow for TED

Step - 1  [ Create a MASTER KEY ]                                                                       

USE master;
GO
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'My@Key';
GO

Step - 2  [ Create a CERTIFICATE that is Protected by Master Key ]

USE master;
GO

CREATE CERTIFICATE MyCertificate
WITH SUBJECT = 'TDE Certificate';
GO

Step - 3  [ Create a DATABASE ENCRYPTION KEY protected by Certificate ]

USE master;
GO

CREATE DATABASE DEMODB;
GO

USE  DEMODB;
GO

CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE MyCertificate;
GO

Step - 4  [ Set DATABASE to Use Encryption ]

ALTER DATABASE DEMODB
SET ENCRYPTION ON
GO

SELECT [name], is_encrypted FROM sys.databases
GO

Step - 5  [ For BACKUP Purpose ]

Once our certificate is created we can either backup using the WITH ENCRYPTION option, or through backup options in the GUI.

Blocking in SQL Server

Blocking in SQL Server


In this article I am trying to explain the Blocking.
What is it
Blocking in SQL Server is a scenario where one connection to SQL Server locks one or more records, and a second connection to SQL Server requires a conflicting lock type on the record or records locked by the first connection. This causes the second connection to wait until the first connection releases its locks. By default, a connection will wait an unlimited amount of time for the blocking lock to go away. 
A certain amount of blocking is normal and unavoidable. But too much blocking can cause connections (representing applications and users) to wait extensive periods of time, hurting overall SQL Server performance.
Type of Blocking
1.     Short-time Blocking - average duration less than three seconds. They do not create problems.

2.     Mid-time Blocking - average duration ranges from 3 seconds to a maximum of 10 seconds. Usually do not create problems

3.     Long-time Blocking - average duration more than 10 seconds. Require attention.
Monitoring Blocking
Sysprocesses is the system table that can reveal the most clues about why a process is blocking others. The sysprocesses table holds information about processes running on Microsoft SQL Server. These processes can be client processes or system processes. Sysprocesses is stored in the master database.

SELECT 'Server : ' + rtrim(@@servername) + ',  SPID=' +
        rtrim(convert(char(6),p.spid))+',  STATUS='+
        rtrim(convert(char(15),IsNull(p.status, ' ')))+'  , LOGIN NAME='+
        rtrim(convert(char(25),IsNull(p.loginame, ' ')))+'  ,HOST NAME='+
        rtrim(convert(char(15),IsNull(p.hostname, ' ')))+'  ,PROGRAM NAME='+
        rtrim(convert(char(30),IsNull(p.program_name, ' ')))+'  ,COMMAND='+
        rtrim(convert(char(25),IsNull(p.cmd, ' ')))+', LOGIN TIME='+
        rtrim(convert(char(19),IsNull(p.login_time,'1900-01-01'),121))+' ,LAST BATCH='+
        rtrim(convert(char(19),IsNull(p.last_batch,'1900-01-01'),121))
FROM master..sysprocesses p
GO

To avoided unnatural blocking
1.    Keep the transactions as short as possible. We must take care the transaction time by optimize Transact-SQL code, optimize indexes, break long transactions into multiple, smaller transactions, avoiding cursors, etc.
2.    In any cases if the running query is cancelled it is important that the code also roll back the transaction. If this does not happen, locks held by the query will not be released, which means blocking can occur.
3.    Avoid INSER, UPDATE or DELETE large numbers of records in a single transaction.
4.    Add query time-out for each of the query.
5.    Add lock time-out for each of the query.

Using SET LOCK_TIMEOUT

Specifies the number of milliseconds a statement waits for a lock to be released. This is the number of milliseconds that will pass before Microsoft SQL Server returns a locking error. A value of -1 (default) indicates no time-out period (that is, wait forever). When a wait for a lock exceeds the time-out value, an error is returned. The value of "0" means not to wait and return a message as soon as a lock is encountered.
SET LOCK_TIMEOUT 1800
GO





BLOCKING IN SQL SERVER





Let say one session has placed a lock which will not allow other sessions to access the same resource (table or row or page). The other session query waits for unlimited time till the previous session releases the lock. This scenario is called blocking.
When a transaction is trying to place any type of lock (shared, exclusive, update) on a resource on which already exclusive lock is placed, then it results into blocking.
Certain amount of blocking is normal and unavoidable but regular blocking or blocking which causes the applications or users to wait for unlimited times should be consider. We have to minimize blocking in order to reduce the performance bottlenecks. Blocking causes timeout problems for applications.





Example



In the above figure check that session1 has placed the exclusive lock on the row with pid=1 and second session is trying to access the same row. The second session is currently executing and waiting for the first session to release the lock. Hence session2 is blocked by session1.
SELECT command causes any blocking?
When select command is executed it places Shared Locks and restricts other queries to perform manipulations till the lock is released. But this lock does not cause too much blocking. But locks placed by insert or update or delete as we have seen in the previous example causes excessive blocking.
Scenarios where sessions experience with excessive blocking
1. Long running queries which places Shared or Exclusive locks.
2. Queries which are cancelled and not rollback.
3. In some cases, lock escalation also one reason for blocking because SQL has escalated locks, and so locked the entire table.
4. When the process of index rebuilding is running.
5. Snapshot agent is generating fresh snapshot in case of Snapshot of transactional replication.


FAQ: - While tracing server activities using profiler, any blocking occurs?
While using tuning template in profiler causes blocking.
Monitoring blocking
SQL Server supports stored procedures and DMVs (Dynamic Management Views) to monitor and troubleshoot blocking issues. Using DMVs as well as event notifications, and the blocked_
process_report and lock_escalation events, we can automate notification when a
blocking event occurs.

To find blocking we can use sp_who or sp_who2.







In the above picture, we can check that session 53 is blocking session 55.




Alternatively we can use sysprocesses of master database.







Steps to reduce blocking
1. Keep the workload small as possible
a. Avoid INSERTing, UPDATEing, or DELETEing large numbers of records in a single transaction.
2. Ensure that your tables have appropriate indexes
a. For the above indexes specially clustered we can set Online Index Processing to true.
3. Use NOLOCK hint or read uncommitted isolation level
a. We can make use of NOLOCK option to read the rows and provide more concurrency.

Using SNAPSHOT ISOLATION LEVEL