Home » SQL Server Admin » Page 5

SQL Server Admin

sql server DBA 999

Memory configuration and using AWE /3GB /USERVA in SQL Servers 2005/2008 in 64 bit and 32 bit installations

In this post we will discuss various memory configurations in 64 bit and 32 bit installations of SQL Server:

In 32 bit OS systems, AWE was used for addressing the issue to access the memory above the 32 bit process limitation. It also enables SQL Server to lock pages in memory.
Servers that are x86 or 32 bit have a VAS region that is 2^32 in size or roughly speaking 4 GB. This is by default split 50/50 into kernel mode and user mode VAS regions. To work around this limitation, the /3GB and /USERVA boot.ini switches can be used to change the user/kernel mode proportions from 50/50 to 75/25 (/3GB) or a user defined … Read the rest

sql server DBA 999

Why Copy-Only Backups taken in SQL Server?

A copy-only backup in SQL Server(any version like 2012 or 2008 or 2005) is taken in cases you do not want effect the sequence of conventional SQL Server backups.
Usually in using a normal Backup,the backup changes the database and affects how later backups are restored. However, occasionally, it is useful to take a backup for a special purpose without affecting the overall backup and restore procedures for the database. copy-only backups serve this purpose.

Note: The transaction log is never truncated after a copy-only backup.

The types of copy-only backups are as follows:

Copy-only full backups (all recovery models)

Copy-only log backups (full recovery model and bulk-logged recovery model only)

Note:A copy-only full backup cannot serve as a … Read the rest

sql server DBA 999

SQL Server 2005/2008 Lock Escalations

Please read my previous post on SQL Server Locks and concurrency control before this….
Lock Escalations are there for a reason. For every lock taking place on SQL Server it takes up around 100 bytes approximately in memory in RAM. So, if we are holding 500,000 locks then it would be 47.68372 MB of memory used. This includes all types of locks.

Lock escalation is the process of converting many fine-grain locks into fewer coarse-grain locks, reducing system overhead while increasing the probability of concurrency contention.
As the SQL Server Database Engine acquires low-level locks, it also places intent locks on the objects that contain the lower-level objects:

•When locking rows or index key ranges, the Database Engine places an … Read the rest

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 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 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