Tuesday, November 29, 2011

Query Architecture

Performance Tuning, Monitoring and Troubleshooting

* As part of performance tuning we have to analyze and work with
    * Physical I/O and Logical I/O
    * CPU usage
    * Memory usage
    * Database Design
    * Application's db programming methods

Query Architecture
* Once the query is submitted to Database Engine for first time it performs the following          tasks.
    * Parsing        (Compiling)
    * Resolving    (Verifying syntax, table, col names etc)
    * Optimizing    (Generating execution plan)
    * Executing    (Executing query)
* For next time if the query was executed with same case and same no of characters i.e            with no extra spaces then the query is executed by taking existing plan.
* To display cached plans
       SELECT cp.objtype AS PlanType,
       OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
       cp.refcounts AS ReferenceCounts,cp.usecounts AS UseCounts,
       st.text AS SQLBatch,qp.query_plan AS QueryPlan
    FROM sys.dm_exec_cached_plans AS cp
    CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st;
* To remove plans from cache memory

Execution Plan
* Step by step process followed by SS to execute a query is called execution plan.
* It is prepared by Query Optimizer using STATISTICS.
* Query optimizer prepares execution plan and stores in Procedurec Cache.
* Execution plans are different for
    * Different case statements
    * Different size statements (spaces.)
* To view graphical execution plan
    * select the query --> press ctrl+M/L
* To view xml execution plan
    * set showplan_xml on/off
    * Execute the query
* To view text based execution plan
    * set showplan_text on/off
    * Execute the query.

* Consists of meta data of the table or index.
* If statistics are out of date, query optimizer may prepare poor plan.
* We have to update statistics weekly with maintenance plan.
USE master
-- Enable Auto Update of Statistics
-- Update Statistics for whole database
EXEC sp_updatestats
-- Get List of All the Statistics of Employee table
sp_helpstats 'HumanResources.Employee', 'ALL'
-- Get List of statistics of  AK_Employee_NationalIDNumber index
DBCC SHOW_STATISTICS ("HumanResources.Employee",AK_Employee_NationalIDNumber)
-- Update Statistics for single table
UPDATE STATISTICS HumanResources.Employee
-- Update Statistics for single index on single table
UPDATE STATISTICS HumanResources.Employee AK_Employee_NationalIDNumber

* It is another database objects which can be used
    * To reduce searching process
    * To enforce uniqueness
* By default SS search for the rows by following the process called table scan.
* If the table consists of huge data then table scan provides less performance.
* Index is created in tree-like structure which consists of root,node and leaf level.
* At leaf level, index pages are present by default.
* We can place max 250 indexes per table.
* Indexes are automatically placed if we place
    * Primary key (clustered)
    * Unique      (unique non clustered index)
* We can place indexes as follows
    create [unique][clustered/nonclustered] index <indexName> on                      <tname>/<viewName>(col1,col2,....)

    * Clustered
    * NonClustered

1. Clustered Index-----------------------
* It physically sorts the rows in the table.
* A table can have only ONE clustered index.
* Both data and index pages are merged and stored at third level (Leaf level).
* We can place on columns which are used to search a range of rows,
Create table prods(pid int,pname varchar(40), qty int)
insert prods values(4,'Books',50),(2,'Pens',400)

select *  from prods (run the query by pressing ctrl+L)

create clustered index pid_indx on prods(pid)

select * from prods -- check the rows are sorted in asc order to pid

FAQ:- Difference between P.K and Clustered Index?
* Primary key enforce uniqueness and allows to eshtablish relationship. But by default       clustered index cannot.

select * from prods where pid=2 -- press ctrl+L to check execution plan
insert prods values(3,'Pencils',500) -- Check this row is inserted as second record.

Note:     A table without clustered index is called HEAP where the rows and pages of the         table are not  present in any order.

NonClustered Index-----------------------
* It cannot sort the rows physically.
* We can place max 249 nonclustered indexes on table.
* Both data and index pages are stored seperately.
* It locates rows either from heap (Table scan) or from clustered index.
* Always we have to place first clustered index then nonclustered.
* If the table is heap the index page consists of
    IndexKeyColvalues        rowreference
* If the table consists of clustered index then index page consists of
    IndexKeyColValues        Clusteredindexkeycolvalues
* Nonclustered indexes are rebuilded when
    * Clustered index is created/droped/modified

Ex: Create nonclustered index on pname column of prods table.
    create index indx1 on prods(pname)
    select * from prods where pname='Books' -- check execution plan

* To disp indexes present on a table
    sp_helpindex <tname>
* To drop index
    drop index prods.pid_indx
* To disp space used by the index
    sp_spaceused prods

Using Included Columns in NonClustered Index--------------------------------------------------------
* We can maintain regularly used columns in nonclustered index so that   no need that      SQL Server should take data from heap or clustered   index.
* If the no of rows are more it provides better performance.

USE AdventureWorks
ON Person.Address (PostalCode)
INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID)

SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
FROM Person.Address
WHERE PostalCode BETWEEN '98000'
AND '99999';

Index Management

* Percentage of space used in leaf level index pages.
* By default it is 100%.
* To reduce page splits when the data is manipulated in the base table we can set proper       FillFactor.
* It allows online index processing
    * While the index rebuilding process is going on users can work with the table.

Page Split------------
* Due to regular changes in the table if the index pages are full to allocate memory for          the index key   columns SS takes remaining rows into new page. This process is called          Page split.
* Page split increases size of index and the index pages order changes.
* This situation where unused free space is available and the index pages are not in the        order of key column values is called fragmentation.
* To find fragmentation level we can use
    dbcc showcontig
    We can use sys.dm_db_index_physical_stats DMF as follows

    SELECT a.index_id, name, avg_fragmentation_in_percent
    FROM sys.dm_db_index_physical_stats
    OBJECT_ID('Production.Product'), NULL, NULL, NULL)
    AS a JOIN sys.indexes AS b
    ON a.object_id = b.object_id
    AND a.index_id =b.index_id;

* To control fragmentation we can either reorganize the index or rebuild the index.

1. Reorganizing Index    * It is the process of arranging the index pages according to the order of index key column values.
    * If the fragmentation level is more than 5 to 8% and less than 28to 30% then we can reorganize the indexes.
    * It cannot reduce the index size as well as statistics are not updated.
    ALTER INDEX <indexName>/<All> on <tname> REORGANIZE
2. Index Rebuilding    * It is the process of deleting and creating fresh index.
    * It reduces the size of index and updates statistics
    * If the fragmentation level is more than 30% then we can rebuild indexes.
    ALTER INDEX <indexName>/<ALL> on <tname> REBUILD
    If we have mentioned ONLINE INDEX PROCESSING option then rebuilding takes space in TEMPDB.
    To check consistancy of a database we can use DBCC CHECKDB('dbName') it disp if any corrupted pages are present, use space in tempdb.

Transactions and Locks

* A transaction is single unit of work which may consists of one or more commands.
* Transactions works with ACID properties
    * Automicity
    * Consistancy
    * Isolation
    * Durability
* SQL Server supports 2 types of transactions
    * Implicit
    * Explicit
* By default SS supports implicit transaction where for every insert, update and delete 3            records are stored in T.Log file
    Begin tran
    commit tran
* To implement business logic i.e. according to the required if we want to commit or                rollback the changes we can use explicit transactions.
    Begin Tran   
    commit/rollback tran
* Any transaction which consists of manipulations places locks on the tables.
* By default when we make a db as current db automatically Shared Lock is placed.
* While working with insert,update,delete by default SS places Exclusive lock.
* Type of locks placed on objects depends on isolation levels.

Isolation Levels

* It is a transaction property.
* Types of locks placed by SS on the resource depends on isolation levels.
* SS supports 5 isolation levels
    * Read Committed (Default)
    * Read Uncommitted
    * Repeatable Reads
    * Snapshot
    * Serializable
* To check the isolation level
    dbcc useroptions
* To set the isolation level
    SET TRANSACTION ISOLATION LEVEL <requiredisolationlevel>
* To handle the concurrency related problems SS places locks
* SS supports 2 types of concurrencies
    * Optimistic Concurrency
        * Uses Shared Locks
        * More concurrency
    * Pessimistic Concurrency
        * Uses Exclusive Locks
        * Low concurrency

Ex:    Open new query window
    use Test
    begin tran
        update emp set sal=5000
    Take new query -->
    use Test
    select * from emp    (--query runs continuesly till user1 session releases lock)
    Take new query
    set transaction isolation level read uncommitted
    select * from emp

    --Take new query
    sp_lock            -- To view locks information
    select * from sys.dm_tran_locks

    --check blocking using

    -- To check locks placed by a particular session
    sp_lock <spid>
    sp_lock 56

Wednesday, November 23, 2011

Database Mirroring

                It is another high availability feature available from SQL Server 2005. Previous versions support the simple high availability feature called Transaction Log Shipping. Log shipping has its own limitation as it doesn’t support automatic failover as well as there might be data loss.  All these limitation we can overcome with database mirroring.
Database mirroring supports automatic failover and the transactions are applied to standby (Mirror) server immediately once they are committed at principle server. Like Log shipping no need of backup, copy and restore operations and jobs.
Points to remember
·         In Principal server database is in ONLINE state.
·         In mirror server database is in a restoring state, which means it is not available for incoming requests.
·         However, we can create a database snapshot of the mirror database, which provides a point-in-time read-only view of the database.

Advantages and benefits:

·          Protection against database failures
·         Automatic failure detection and failover
·         Support of easy manual failover
·         Automatic client redirection
·         Multiple operating modes
·         No special hardware requirements
·         Minimized chance of data loss
·         Relatively ease of set up and configuration

FAQ: - What are new features introduced in SQL Server 2008 mirroring?
1. Automatic page repair
                Database mirroring can, however, recover from the following errors:
• Error 823: Operating system Cyclic Redundancy Check (CRC) failure
• Error 824: Logical errors including a bad page checksum or torn write
• Error 829: Page has been marked as restore pending
* To view the repaired pages
                Select * from sys.dm_db_mirroring_auto_page_repair

2. Log Stream Compression
                Log stream compression between the principal and the mirror server to               minimize network           bandwidth.
2. Mirroring Architecture

Mirroring Operating Modes
                * Synchronous
                                * High Availability             (High safety with automatic failover)
                                                * Principal, Mirror and witness
                                                * Supports automatic failover
                                * High protection
                                                * Principal, Mirror
                                                * No automatic failover
                * Asynchronous
                                * High performance
                                                * Principal, Mirror
                                                * No automatic failover

FAQ: - How to enable mirroring feature in SS 2005 RTM?

1.       SQL Server 2005 with SP1 or SQL Server 2008
2.       Database should be in FULL recovery model.
3.       Service Broker should be enabled on the database.
4.       Both the servers should have either Enterprise or standard editions.
5.       Both the servers should have same edition.
6.       Witness server can have any edition.
Configuring Mirroring – Steps
1.       Configuring security and communication between instances
a.       Configuring endpoint
b.      Creating logins for other servers service accounts
c.       Grant connect permission to this logins on endpoints

2.       Create mirror database
a.       Take full and T.Log backup from principle server and restore it in mirror server with NORECOVERY.
3.       Establish mirroring session using ALTER DATABASE command
1.       Go to SSMS
2.       Connect 2 or 3 instances
For example
                CLASS2\sql2K8                  -              Principal
                CLASS2\FIRST                    -              Mirror
                CLASS2\THIRD                   -              Witness
3.       Note down the above instances service accounts
a.       CLASS2\SQL2K8                                                (CLASS2\KAREEM)
b.      CLASS2\FIRST                                    (CLASS2\KAREEM)
c.       CLASS2\THIRD                                   (CLASS2\SQLUSER)
4.       Verify both Principal and Mirror has same editions or not i.e.  Enterprise or Standard.
By running the following command in both the servers
Select serverproperty('edition')
5.       Go to Principal server and create a sample database (In real time environment we have to use existing database) with the name OptimizeSQL
6.       Create one sample table in the database with some rows.
7.       Take FULL and Transaction Log Backup of OptimizeSQL database in principal server.
Use master
backup database OptimizeSQL to disk='\\Class2\backups\OptimizeSQL.bak'
backup log OptimizeSQL to disk='\\Class2\backups\OptimizeSQL.bak'
8.       Go to Mirror Server and create a folder with the name d:\OptimizeSQL_Files and grant read write permissions to service account. Restore database by using the Recovery State WITH NORECOVERY
FROM DISK='\\Class2\backups\OptimizeSQL.bak'
MOVE 'OptimizeSQL' TO 'd:\OptimizeSQL_Files\OptimizeSQL.mdf',
MOVE 'OptimizeSQL_log' TO ' d:\OptimizeSQL_Files\OptimizeSQL_1.ldf' ,NORECOVERY
FROM DISK='\\Class2\backups\OptimizeSQL.bak'
9.       Configuring Mirroring –
10.   Go to Principal Server à Right Click on database OptimizeSQLà Tasks à Mirror
11.   Click on Configure Securityà Click Next


12.   Select Yes if you have witness instance otherwise select No.

13.   Next àNext
14.   Select principal instance à Next
15.   Click on Connect select Mirror Server instance name (e.g class2\First)
16.   Select Connect à Next
17.   Once again click on Connect button select Witness Server instance name (CLASS2\THIRD) à Next
18.   Enter service accounts
19.   Click Next à Finish

20.   Close.
21.   Select “Do Not Start Mirroring”.
22.   Select Start Mirroring
23.   Check the status à OK

FAQ: - While configuring mirroring what errors you have faced?

Points to Remember
1.       One job is created on both the servers 
Database Mirroring Monitor Job
2.       Default Partner Timeout is 10Sec.
3.      How can you say that both the dbs are 100% sync?
a.      We can view unsent log and un restored log values. If both are 0 then 100% sync. (In Mirroring Monitor)
b.       We can view Mirroring Failover LSN and Replication LSN with sys.database_mirroring. Both should be same.
4. Mirroring States
1.      Synchronizing
2.      Synchronized
3.      Disconnected (If mirror or principal failed)
4.      Suspended (If the principal is un available or unable to send transactions to mirror)
5.      Pending Failover – If the unsent log is >0.
5. To change mirroring timeout (Run in principal server)
            Alter database OptimizeSQL SET PARTNER TIMEOUT 30
Monitoring Mirroring
·        We can monitor mirroring using the following options
o   Using MSDB tables and Views
o   Using Database Mirroring Monitor
o   Using Performance Monitor
o   Using Profiler
1.      Using MSDB tables and Views
o   To view complete details of mirroring (In Principal Server)
Select * from sys.database_mirroring
o   To view mirroring endpoint details (In Principal Server)
Select * from sys.database_mirroring_endpoints
o   To view about Principal, mirror server details and mirroring state run the following query in witness   server
  Select * from sys.database_mirroring_witnesses
2.      Using Database Mirroring Monitor
o   We can monitor the following features
                                                * Unsent Log     (at principal)
                                                * Un restored Log (at mirror)
                                                * Transaction Rate
                                                * Commit Overhead (Transactions applied rate at mirror)                         
Ex:  Go to principal server and run the following query
                                use OptimizeSQL
declare @n int=100
while @n<=1000000
                insert emp values(@n,'Rajesh',60)
                set @n+=1
                b. Right click on OptimizeSQL db --> Tasks -->Launch Database Mirroring Monitor
                                c. Select "Database Mirroring Monitor"
                                d. Click on Register Mirror databases
                                e. Click on Connect and select Mirror Server
                                f. Select the database OptimizeSQL --> OK
                                g. Observe the parameters by refreshing (F5) the monitor.
Configuring Thresholds
·         Go to Mirroring Monitor à Select “Warnings” tab à Set Thresholds à
3.      Using Performance Monitor
a.      We can monitor the following counters for Mirrored databases
* We can use the performance object called
                                    "<instanceName>:Database Mirroring"
                        * Counters which we have to observe regularly
                                    * Bytes sends/sec
                                    * Log Harden time (Commit overhead)
                                    * Total sends/sec
                                    * Transaction delay (Principal)
                                    * Pages Sends/sec

1.      Start à run à perfmon
2.      Add counter (Ctrl + I) or Click on +symbol, present on toolbar.
3.      Add required counters by selectingMSSQL$SQL2K8:DatabaseMirroringperformance object
4.       To view the changes run the previous script.
5.        Configuring Alerts

Performing Fail Over
1.      Fail over process depends on Operating Modes.
2.      If the operating mode is "High safety with automatic failover" then witness server makes mirror db online automatically within the configured timeout.
3.      In case of other operating modes we have to perform fail over manually.
In High Performance
1.      Run the following command in mirror server
2.      Transfer the logins.
3.      Make the database available to the users and applications.
In High Protection
1.      Run the following commands in Mirror server
ALTER DATABASE <dbname> SET PARTNER OFF;  (To break mirroring)
2.      Database comes into restoring state run the following command to take it online

Threads created for database mirroring

FAQ :- If mirror server is failed then what is the effect on principle database T.Log file?

Quorum is a relationship that exists when two or more server instances in a database mirroring session are connected to each other. Typically, quorum involves three interconnected server instances. When a witness is set, quorum is required to make the database available. Designed for high-safety mode with automatic failover, quorum makes sure that a database is owned by only one partner at a time.
Three types of quorum are possible:
·         A full quorum includes both partners and the witness.
·         A witness-to-partner quorum consists of the witness and either partner.
·         A partner-to-partner quorum consists of the two partners.

Possible Failures during Database Mirroring

·         As part of mirroring generally we have two types of errors
·         Soft errors
·         Hard Errors
·         Soft Errors
·         Errors identified by SQL Server service i.e. sqlservr.exe is called soft error.
      • Network errors such as TCP link time-outs, dropped or corrupted packets, or packets that are in an incorrect order.
      • A hanging operating system, server, or database state.
      • A Windows server timing out.
·         Hard Errors
·         Errors identified by windows and notified to sqlservr.exe file are called hard errors.
·         A broken connection or wire
·         A bad network card
·         A router change
·         Changes in the firewall
·         Endpoint reconfiguration
·         Loss of the drive where the transaction log resides
·         Operating system or process failure

SQL Server Error Logs

* Error Logs maintains events raised by SQL Server database engine or Agent. * Error Logs are main source for troubleshooting SQL Server...

SQL Server DBA Training

SQL Server DBA Training
SQL Server DBA