Home » SQL Server Architecture » Page 2

SQL Server Architecture

sql server DBA 999

SQL Server 2008/2005 Concurrency control with locks

Today let us discuss more on SQL Server 20008/2005 Locks and concurrency control……

Locks are very essential to maintain concurrent data access without issues. Say you are trying to update some data row and someone else wants to read that data, to avoid phantom reads (inconsistent view of data or data which is not yet committed) a Exclusive lock is placed on the row. But it does not start nor end there. First thing when you issue a update statement is a lock is placed on database which is called intent exclusive (IX) then same IX on table with EX(Exclusive lock on table) once it available for you.
Some locks are compatible with each other like a simple select queries … Read the rest

sql server DBA 999

SQL Server 2008/2005 Storage Internals, GAM, SGAM, PFS, IAM

Today lets discuss some of the SQL Server Storage internals like what is GAM, SGAM, IAM etc
Basically in short words:
1.GAM(Global Allocation Map) tracks around a 64,000 extents(extent is 64KB which consists of 8 pages) which is around 4GB of space. GAM has bit indicating if the extent is currently available for allocation or not. GAM bit for a extent is 1 only if it’s not being used at the moment.
2.SGAM(Shared Global Allocation Map) tracks mixed extents with free space. A mixed extent is used by multiple objects like table, index etc. SGAM bit is 1 for a extent only if it has free space in it and it is mixed extent. Even SGAM tracks 64,000 extents same … Read the rest

sql server DBA 999

SQL Server 2008/2005 – Comparing Index rebuild/Reorganize and increase in Transaction Log file

Comparing ALTER INDEX options Index Rebuild and Reorganize:

1.Index Rebuild requires building the new index before dropping the old one. This means there has to be enough free space in the database to accommodate the new index; otherwise the database will grow to provide the required free space. This can be problematic for large indexes. Index Reorganize only requires 8KB of additional space in the database.
2.Index Rebuild can use multiple CPUs so the operation runs faster. Index Reorganize is always single-threaded.
3.Index Rebuild may require long-term locks on the table that can limit concurrent operations. Index REORGANIZE doesn’t hold blocking locks and it’s always been an online operation.
4.Index Rebuild can use minimal-logging to reduce transaction log growth. Index … 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 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 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