Find the Index Fragmentation on SQL Server 2005 and 2008

sys.dm_db_index_physical_stats dynamic view which returns size and fragmentation information.but query returns lots of information.

SELECT OBJECT_NAME(idx.OBJECT_ID) AS TableName,
idx.name AS IndexName,idxst.avg_fragmentation_in_percent,
idxst.avg_fragment_size_in_pages,
idxst.avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') idxst
INNER JOIN sys.indexes idx ON idx.OBJECT_ID = idxst.OBJECT_ID
AND idx.index_id = idxst.index_id
WHERE idxst.avg_fragmentation_in_percent > 5

Decide to de-fragment an index we should determine how much affected it is. Fragmentation is measured as a percentage that indicates the number of pages that are not in the ideal order. A value below 5% indicates a very low fragmented index that requires no maintenance. A value of between 5% and 30% can be improved by reorganizing the idex. Greater than 30% fragmentation warrants an index rebuild.

Check Table Last Accessed by User. with TSQL Script

Select DB_Name(usts.[database_id]) AS [Database],

Object_Name(usts.[object_id]) AS [TableName],

MAX(usts.[last_user_lookup]) AS [last_user_lookup],

MAX (usts.[last_user_scan]) AS [last_user_scan],

MAX(usts.[last_user_seek]) AS [last_user_seek]

FROM sys.dm_db_index_usage_stats AS usts

WHERE usts.[database_id] = DB_ID()

AND usts.[object_id] = Object_ID(‘Table_Name’)

GROUP BY usts.[database_id], usts.[object_id];

After Database Services/ Server restart View also reset.

Change your database context to your current database.

Change the Table Name for which you want to get last access details. 

Oracle V SQL Server Comparison of Core Schema and Data Structures

Oracle V SQL Server Comparison of Core Schema and Data Structures (Objects)

Oracle	SQL Server
Table	Table
Index	Index
View	View
Synonym	Synonym
Sequence	Identity Columns
Procedure	Stored Procedure
Function	Function
Package	N/A
Queue in Streams Advanced Queuing	Service Broker Queue
Object Type	Type
XML DB	XML Schema Collection

                                                                             

Data Blocks, Extents And Segments
                                                                                           

Structure	Oracle	SQL Server 2008
Smallest unit of logical storage	Block	Page
Block size	Variable	8 KB fixed
Storage allocation	Performed in multiple blocks; are ‘extents’	Performed in multiple pages; are ‘extents’
Extent size	Variable	64 KB fixed
Segment	Any logical structure that is allocated storage	No equivalent structure

Solution for Downtime in Oracle

v Unplanned Down time: - The result of computer failures or data failures

Ø  System failures

•          Fast-start Fault Recovery: - Fast-Start Fault Recovery enables you to bound the database Crash recovery time. The database self-tunes checkpoint processing to safeguard the desired recovery time objective.

•          RAC Data Guard Stream: - RAC provides optimal performance, scalability, and availability gains.

Ø  Data failures

•          RMAN backup/recovery: - Recovery Manager (RMAN) automates database backup and recovery. Data Recovery Advisor (not supported for RAC) diagnoses data failures and presents repair options.

•          ASM: - ASM provides a higher level of availability using online provisioning of database storage.

•          Flashback: - Flashback provides a quick resolution to human errors.

•          Oracle Hardware Assisted Resilient Data (HARD): - It’s a comprehensive program designed to prevent data corruptions before they happen.

•          Data Guard & Streams: - Data Guard must be the foundation of any Oracle database disaster-recovery plan.

v  Planned down time: - Due to data changes or system changes:

Ø  System changes

•          Rolling upgrades/Online patching: - The Oracle database supports the application of patches to the nodes of a RAC system, as well as database software upgrades, in a rolling fashion.

•          Dynamic provisioning: - The Oracle database continues to broaden support for dynamic reconfiguration, enabling it to adapt to changes in demand and hardware with no disruption of service.

Ø  Data changes

•          Online Redefinition: - With online redefinition, the Oracle database supports many maintenance operations without disrupting database operations or users updating or accessing data. 

Microsoft SQL Server Virtual Labs

Try Microsoft SQL Server in a virtual lab and learn more about AlwaysOn, ColumnStore Index, PowerView, and other new features. Virtual labs are simple, with no complex setup or installation required.

You get a downloadable manual and a 90-minute block of time for each module. You can sign up for additional 90-minute blocks at any time.

http://msdn.microsoft.com/en-us/hh859579.aspx
http://technet.microsoft.com/en-us/virtuallabs/bb467605.aspx

Causes of Database Down Time

Causes of Unplanned Down Time

Ø  Software failures

·         Operating system

·         Database

·         Application

·         Network

·         Middleware

Ø  Hardware failures

·         Memory

·         CPU

·         Power Supply

·         Disk

·         Tape

·         Bus

·         Controllers

·         Network

·         Power

Ø  Human errors

·         Operator Error

·         User Error

·         DB Admin Error

·         System Admin Error

·         Sabotage

Ø  Disasters

·         Earthquake

·         Flood

·         Fire

·         Power Failure

·         Bombing

Causes of Planned Down Time

Ø  Routine operations

·         Backups

·         Performance mgmt

·         Security mgmt

·         Batches operations

Ø  Periodic maintenance

·         Storage maintenance

·         Schema management

·         Operating system

·         Initialization parameters

·         Software patches

·         Middleware

·         Network

Ø  New deployments

·         DB upgrades

·         OS upgrades

·         HW upgrade

·         Middleware upgrades

·         App upgrades

·         Net upgrades

All Oracle Application server, Database books / study material / pdf / Documents

All Oracle  books / study material / pdf / Documents at one place.

• Oracle Application Express, Release 4.0
• Oracle Application Express, Release 4.1
• Oracle Application Server 10g (Release 10.1.2)
• Oracle Application Server 10g (Release 10.1.3)
• Oracle Application Server 10g (Release 9.0.4)
• Oracle Application Server Standard Edition One, 10g Release 2 (10.1.2)
• Oracle Application Server, 10g (9.0.4)
• Oracle Application Server, 10g Release 2 (10.1.2)
• Oracle Application Server, 10g Release 2 (10.1.2)
• Oracle Application Server, 10g Release 2 (10.1.3.1.0)
• Oracle Application Server, 10g Release 2 (10.1.3.2.0)
• Oracle Application Server, 10g Release 2 (10.1.4.0.1)
• Oracle Beehive, Release 1 (1.5)
• Oracle Beehive, Release 2 (2.0)
• Oracle Collaboration Suite, 10g Release 1 (10.1.2)
• Oracle Collaboration Suite, Release 2 (9.0.4)
• Oracle Collaboration Suite, Release 9.0.3
• Oracle Database Express Edition, 10g Release 2 (10.2)
• Oracle Database Express Edition, 11g Release 2 (11.2)
• Oracle Database, 10g Release 1 (10.1)
• Oracle Database, 10g Release 2 (10.2)
• Oracle Database, 11g Release 1 (11.1)
• Oracle Database, 11g Release 2 (11.2)
• Oracle E-Business Suite, Release 12.1
• Oracle Enterprise Manager Cloud Control, 12c Release 1 (12.1)
• Oracle Enterprise Manager Ops Center, 11g Release 1 (11.1.3)
• Oracle Enterprise Manager, 10g Release 5 (10.2)
• Oracle Enterprise Manager, 11g Release 1 (11.1)
• Oracle Enterprise Manager, Release 4.0
• Oracle Fusion Applications, 11g Release 1 (11.1.1.5)
• Oracle Fusion Applications, 11g Release 1 (11.1.2)
• Oracle Fusion Applications, 11g Release 1 (11.1.3)
• Oracle Fusion Applications, 11g Release 1 (11.1.4)
• Oracle Fusion Middleware, 11g (Release 11.1.1)
• Oracle Fusion Middleware, 12c Release 1 (12.1.1)
• Oracle Fusion Middleware, Release 1 (11.1.1)
• Oracle Fusion Middleware, Release 1 (11.1.1.0.0)
• Oracle Fusion Middleware, Release 1 (11.1.1.1)
• Oracle Fusion Middleware, Release 1 (11.1.1.2)
• Oracle Fusion Middleware, Release 1 (11.1.1.3)
• Oracle Fusion Middleware, Release 1 (11.1.1.4)
• Oracle Fusion Middleware, Release 1 (11.1.1.5)
• Oracle Fusion Middleware, Release 1 (11.1.1.6)
• Oracle Fusion Middleware, Release 1 (11.1.1.7)
• Oracle Fusion Middleware, Release 1 (11.1.2.0.0)
• Oracle Fusion Middleware, Release 1 (11.1.2.1.0)
• Oracle Fusion Middleware, Release 1 (11.1.2.2.0)
• Oracle Identity Management 10g (Release 10.1.4)
• Oracle JD Edwards EnterpriseOne Applications, Release 9.1
• Oracle JD Edwards EnterpriseOne Installation and Upgrade, Release 9.1
• Oracle JD Edwards EnterpriseOne Tools, Release 8.98 Update 4
• Oracle JD Edwards EnterpriseOne Tools, Release 9.1
• Oracle JD Edwards World, Release A9.3
• Oracle My Oracle Support Help
• Oracle PeopleSoft
• Oracle TimesTen In-Memory Database, 11g (11.2.1)
• Oracle TimesTen In-Memory Database, 11g (11.2.2)
• Oracle8i, Release 8.1.7
• Oracle9 Collaboration Suite, Release 9.0.3
• Oracle9 Collaboration Suite, Release 9.0.4
• Oracle9i Application Server, Release 2 (9.0.2)
• Oracle9i, Release 9.0.1
• Oracle9i, Release 2 (9.2)
• Oracle9iAS, Release 9.0.2

http://tahiti.oracle.com/

Oracle system statistic average by day

Oracle system statistic average by hour


select      to_char (begin_interval_time, 'day' ) snap_time, avg(value) avg_value
from        dba_hist_sysstat
natural join
            dba_hist_snapshot
where
            stat_name = '&stat_name'
group by    to_char(begin_interval_time, 'day')
order by  
        decode(
        to_char (begin_interval_time, 'day'),
        'sunday', 1,
        'monday', 2,
        'tuesday', 3,
        'wednesday', 4,
        'thursday', 5,
        'friday', 6,
        'saturday', 7
          );

Query uses dba_hist_sysstat view to show average values by day of week.

Oracle system statistic average by hour

select      to_char (begin_interval_time, 'hh24' ) snap_time, avg(value) avg_value
from        dba_hist_sysstat
natural join
            dba_hist_snapshot
where
            stat_name = '&stat_name'
group by    to_char(begin_interval_time, 'hh24')
order by    to_char (begin_interval_time, 'hh24');

Query uses dba_hist_sysstat view to show average values by hour or the day.

Oracle Tuning Hierarchy

Improving SQL Statement Tuning with Automatic SQL Tuning

This tutorial describes how to benefit from Automatic SQL Tuning to automatically tune your high loaded SQL statements.

http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r2/prod/manage/ast/ast.htm

SQL usage & statistics in the shared pool

v$sqlarea contains statistics about SQL in the shared pool and its usage statistics

select * from
                     ( select sql_text,
                                 cpu_time/1000000000 cpu_time,
                                 elapsed_time/1000000000 elapsed_time,
                                 disk_reads,
                                 buffer_gets,
                                 rows_processed
                       from v$sqlarea
                       order by cpu_time desc, disk_reads desc
                     )
where rownum   < 21

Query shows SQL statement by their CPU usage.

CPU usage statistics (CPU Time)

CPU usage is important for response time analysis.
Response Time = Service Time + Wait Time.
Service Time = CPU Parse Time/CPU Recursive Time/CPU Other
If CPU usage shows large response time, the database should be tuned according to CPU usage.

Query show the CPU time utilization by the database since last startup of database.

CPU-Time

Select
          name,
          value
from   v$sysstat
where upper (name) like '%CPU%';
CPU statistics from v$sysstat, database 9i R2

Temporary space used by SQL call in current session (oracle 10g)

select
        sql_text,
        sid,
        c.username,
        machine,
        tablespace,
        extents,
        blocks
from
        sys.v_$sort_usage a,
        sys.v_$sqlarea b,
        sys.v_$session c
where
        a.sqladdr = b.address and
        a.sqlhash = b. hash_value and
        a.session_addr = c.saddr
order by sid;

We can check, SQL call current session information and details. How much temporary space SQL call is using.

Temporary space used by connected sessions (oracle 10g)

select
        tablespace_name,
        current_users,
        total_extents,
        used_extents,
        free_extents,
        max_used_size,
        max_sort_size
from
        sys.v_$sort_segment
order by 1;  

Temporary space storage issue like large disk sorts can cause out of space conditions.
Query shows that users are currently using space in temporary tablespace.

Storage Space consumption by user account (Oracle 10g)

Select
      owner,
      round((byte_count / 1024 /1024 ), 2) space_used_MB,
      round(100 * (byte_count / tot_bytes) , 2) pct_of_database
from
    (select
            owner,
            sum(bytes) as byte_count
      from
            sys.dba_segments
      where
            segment_type not in ('TEMPORARY', 'CACHE')
      group by owner
      order by 2 desc),
        ( select sum(bytes) as tot_bytes
          from sys.dba_segments);


IF users account found with large amount of data, user should checked is there unused or unnecessary objects have not been left.

Temporary space used by connected sessions (oracle 10g)
Temporary space used by SQL call in current session (oracle 10g)

Truncate and Delete command

Truncate and Delete command in SQL Server

DELETE	TRUNCATE
DDL command	DML command
Used delete all or selected rows from a table based on WHERE clause.	Removes all rows from a table.
This is a logged operation for every row.	This is also a logged operation but in terms of deallocation of data pages.
Any row not violating a constraint can be Deleted.	Cannot TRUNCATE a table that has foreign key constraints.
Need to Commit or Rollback	Cannot be Rolled back.
Does not reset the identity column. Starts where it left from last.	Resets identity column to the default starting value.