Home » Archives for April 2012

April 2012

sql server DBA 999

SQL Server 2008 Indexes: Types, Performance gain, Lookup and more…

Indexes are secret for faster query execution when designed properly.
Types of indexes in SQL Server :
1. Clustered Indexes: A clustered index determines the physical order of data in a table. A clustered index is analogous to a telephone directory, which arranges data by last name.
2.Non-Clustered Indexes: Nonclustered indexes have the same B-tree structure as clustered indexes, except for the following significant differences:
• The data rows of the underlying table are not sorted and stored in order based on their nonclustered keys.
• The leaf layer of a nonclustered index is made up of index pages instead of data pages.

Nonclustered indexes can be defined on a table or view with a … Read the rest

sql server DBA 999

SQL Server 2008 Resource Database, Metadata Storage, Catalog Views and Hidden secrets

Today let us discus more on how SQL Server stores metadata (Data about Data) inside SQL Server:

The metadata is stored in special System Tables. Starting with SQL 2005, System Tables are hidden and they cannot be directly queried. Even with full DBA rights, System Tables are restricted. Although not directly accessible, there are built in views and procedures for extracting metadata.

a. System Views
System Views are predefined Microsoft created views for extracting SQL Server metadata.
Let us discuss these views in a order
1. Information Schema
These System Views belongs to the Information Schema which is an ANSI specification for obtaining metadata. There about twenty different views for displaying most physical aspects of a database, such as table, … Read the rest

sql server DBA 999

SQL Server 2008 Index Maintenance Rebuild vs Reorganize

Today, let us discuss on Index Maintenance on SQL Server. Indexes tend to get fragmented as the data gets updated. If the update is under the free space on page which was specified using Fill Factor of index creation or Rebuild then it would not be fragmented. The clustered index is the index by which the Leaf Nodes are Stored and thus does not require additional space.

ALTER INDEX ... REORGANIZE
ALTER INDEX .... REBUILD

Here are some Pros and Cons between the two:
1) Index rebuild works by re-creating the index internally and when that completes, it drops the existing index where as index reorganize is the process of physically re-organizing the leaf nodes of the index.
2) During … Read the rest

sql server DBA 999

Policy Based Management to check database recovery model to be Simple

Policy Based Management to check database recovery model to be Simple in Simple Steps:

1. Create a Condition based on a Facet of Database
Policy Based Management to check database recovery model to be Simple 6Policy Based Management to check database recovery model to be Simple 5

2. Now Create a policy using the condition created above to evaluate on Demand
Policy Based Management to check database recovery model to be Simple 4

Policy Based Management to check database recovery model to be Simple 3

3.Now Evaluate the above policy to check any deviations:

Policy Based Management to check database recovery model to be Simple 2

4.Output will look like this, showing Red mark on database with any deviations
Policy Based Management to check database recovery model to be Simple… Read the rest

sql server DBA 999

SQL Server 2008 Extended Events 3

Now lets create a New Event Session to capture Deadlock Victims


IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='Demo_session_Deadlock')
DROP EVENT SESSION Demo_session_Deadlock ON SERVER
GO

-- CREATES THE SESSION
CREATE EVENT SESSION Demo_session_Deadlock
ON SERVER
ADD EVENT
sqlserver.lock_deadlock
( ACTION ( sqlserver.database_id,
sqlserver.client_app_name,
sqlserver.sql_text ) )
ADD TARGET package0.asynchronous_file_target
-- CONFIGURES THE FILE TARGET
(set filename = 'G:Datadata2.xel' , metadatafile = 'G:Datadata2.xem')
GO

-- STARTS THE SESSION
ALTER EVENT SESSION Demo_session_Deadlock ON SERVER STATE = START
GO

Now create a deadlock by using some updates

--DeadLock Simulation
--First Session
begin tran
update [Demo1] set [Column1] = 'abcd'

/*

--Second Session, Run this from Another window

begin tran
update [Demo2] set [column1] = 'abcd'
update [Demo1] set [Column2] = 'abcd'
--commit … Read the rest

sql server DBA 999

SQL Server 2008 Extended Events 2

SQL Server 2008 Extended Events 2:

Now lets create a New Event Session to capture LongRunning Queries which are running for more that 10 seconds

Now in this post we will write the details of the event Ringer Buffer and XML file. It is not necessary to write at both places but for this demo we will write to both of them.


-- Extended Event for finding the long running queries
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='LongRQ')
DROP EVENT SESSION LongRQ ON SERVER
GO
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='LongRQ')
DROP EVENT SESSION LongRQ ON SERVER
GO
-- Create Event Session
CREATE EVENT SESSION LongRQ
ON SERVER
-- Add events to capture events needed as necessary
ADD EVENT … Read the rest

sql server DBA 999

SQL Server 2008 Extended Events 1

SQL Server 2008 Extended Events 1:

This is a new bee article for Extended Events.


--Now quickly searching to view all events based on sql keyword
select * from sys.dm_xe_objects
where OBJECT_type = 'event'
and name like'%sql%'

--Now for viewing all object columns of any events
select * from sys.dm_xe_object_columns
where [object_name] = 'sql_statement_completed'

--To view actions in Extended events
select * from sys.dm_xe_objects
where OBJECT_type = 'action'

Events: The SQL Server code is populated with event calls that which is in disabled state by default. Adding events to a session enables those event calls. Once enabled, they will execute the set of functionality defined by the session.

Target: This is an Extended Event Object that can be used to … Read the rest

sql server DBA 999

SQL Server 2008 Update Statistics and Index Rebuild

SQL Server 2008 Update Statistics and Index Rebuild:

Whenever a Index is rebuild the statistics are updated but not for the non indexed Statistics on the table. This means if a Index rebuild occurs the Statistics are updated for the indexes which is a known fact. But if we have many other statistics which are not on Indexes then they are not updated by this Index Rebuild.

Let us do a quick Demo below:

Let us create a Demo table with a Index and a non index statistics created on it.


SET NOCOUNT ON

DECLARE @RowID INT
SET @RowID = 0
if exists(select name from sys.tables where name = 'DemoTable')
begin
drop table DemoTable;
end
CREATE TABLE DemoTable(
ID INT,… Read the rest

sql server DBA 999

SQL Server RPO and RTO

Recovery Point Objective(RPO) describes a point in time that data can be restored from. For instance, if there is data corruption, Data loss or unavailability, at what point in time can a valid copy of the data be restored from? RPO is usually measured as minutes, hours, days, weeks, etc… for Business Continuity.

If a database is corrupted then can we restore it back to what it was 15 minutes ago? This would be an RPO of 15 minutes for Business Continuity..

Recovery Time Objective (RTO) is the amount of time which data or hardware is desired to be restored after a data corruption or hardware failure.

If a Database server just died. Time needed to bring it up and … Read the rest