Donghua's Blog - DBAGlobe

Saturday, February 8, 2014

Log mining SQL server Transaction Log

use AdventureWorks2012
go
dbcc sqlperf(logspace)
/*-----------------------------
Database Name    Log Size (MB)    Log Space Used (%)    Status
master    2.242188    26.02352    0
tempdb    0.7421875    59.01316    0
model    0.4921875    90.77381    0
msdb    0.7421875    49.21053    0
AdventureWorks2012    0.9921875    55.56102    0
ReportServer    1.054688    44.02778    0
ReportServerTempDB    1.054688    43.84259    0
---------------------------*/s
go
select * from sys.dm_db_log_space_usage
/*-----------------------------
database_id    total_log_size_in_bytes    used_log_space_in_bytes    used_log_space_in_percent    log_space_in_bytes_since_last_backup
5    1040384    578048    55.56102    101888
---------------------------*/
go

select * into dbo.emplyee_new from HumanResources.Employee

go
select o.name, o.type,o.create_date,o.modify_date,m.definition from sys.all_objects o inner join sys.all_sql_modules m
on o.object_id=m.object_id
where name like '%dblog%'
/*---------------------------
name    type    create_date    modify_date
fn_dblog    IF    2011-11-04 21:26:15.210    2011-11-04 21:27:08.297
fn_dblog_xtp    IF    2013-10-03 18:03:58.533    2013-10-03 18:03:58.567
fn_dump_dblog    IF    2011-11-04 21:26:16.583    2011-11-04 21:27:08.300
fn_dump_dblog_xtp    IF    2013-10-03 18:03:58.580    2013-10-03 18:03:58.597
---------------------------*/
GO

select [Current LSN], Context, [Operation],AllocUnitName from sys.fn_dblog(null,null)
-- where [Current LSN]> '0000002d:0000017b:0002'
go

select [Current LSN],[Begin Time],[Transaction Name] from sys.fn_dblog(null,null) where [Transaction Name] is not null
go

select convert(char(30),AllocUnitName) as AllocUnitName,
sum(convert(numeric, [Log Record Length])) as log_record_bytes,
sum(convert(numeric, [Log Reserve])) as log_reserve_bytes from sys.fn_dblog('45:379:1',null)
group by AllocUnitName order by log_reserve_bytes desc
/*---------------------------
AllocUnitName                  log_record_bytes                        log_reserve_bytes
------------------------------ --------------------------------------- ---------------------------------------
dbo.emplyee_new                75956                                   136732
NULL                           1360                                    36966
sys.sysobjvalues.clst          1540                                    297
sys.sysprufiles.clst           244                                     209
sys.sysiscols.clst             128                                     74
sys.sysiscols.nc1              116                                     74
sys.sysidxstats.nc             164                                     74
sys.sysidxstats.clst           196                                     74
Unknown Alloc Unit             4216                                    0
sys.sysschobjs.clst            420                                     0
---------------------------*/
go

select * from fn_dblog_xtp(null,null)
go
select * from sys.database_files where type_desc='LOG'
GO

delete from dbo.emplyee_new where SickLeaveHours>20
go

BACKUP LOG [AdventureWorks2012] TO DISK = N'C:\SampleDB\AdventureWorks2012_log.bak'
WITH NOFORMAT, NOINIT,
NAME = N'AdventureWorks2012-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

select * from fn_dump_dblog(null,null,'DISK',1,'C:\SampleDB\AdventureWorks2012_log.ldf',
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL)

select * from fn_dump_dblog(null,null,'DISK',1,'C:\SampleDB\AdventureWorks2012_log.bak',
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL)
go

select * from fn_dump_dblog_xtp(null,null,'DISK',1,'C:\SampleDB\AdventureWorks2012_log.bak',
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL)
go

How to clean SQL Buffer and Plan Cache

checkpoint DBCC FreeProcCache DBCC FreeSystemCache DBCC FlushProcInDB() DBCC DropCleanBuffers

Thursday, February 6, 2014

T-SQL Index Internal & Statistics


use AdventureWorks2012   
GO

select object_id   
,name     
,index_id    
,type    
,type_desc    
,is_unique    
,data_space_id    
,ignore_dup_key    
,is_primary_key    
,is_unique_constraint    
,fill_factor    
,is_padded    
,is_disabled    
,is_hypothetical    
,allow_row_locks     
,allow_page_locks     
,has_filter     
,filter_definition    
from sys.indexes where object_id=object_id('HumanResources.Employee')    
GO

select object_name=object_name(i.object_id),    
    index_name=id.name,    
    index_type=id.type_desc,    
    id.index_id,    
    column_name=c.name,    
    c.column_id     
from sys.index_columns i join sys.columns c    
  on i.object_id=c.object_id    
  and i.column_id=c.column_id    
  join sys.indexes id    
  on i.object_id=id.object_id    
  and i.index_id=id.index_id    
    where i.object_id=object_id('HumanResources.Employee')    
    order by index_id,column_id    
GO

select database_id   
,object_id   
,index_id    
,partition_number     
,index_type_desc       
,alloc_unit_type_desc         
,index_depth     
,index_level     
,avg_fragmentation_in_percent     
,fragment_count       
,avg_fragment_size_in_pages     
,page_count           
,avg_page_space_used_in_percent     
,record_count         
,ghost_record_count   
,version_ghost_record_count     
,min_record_size_in_bytes     
,max_record_size_in_bytes     
,avg_record_size_in_bytes     
,forwarded_record_count     
,compressed_page_count    
from sys.dm_db_index_physical_stats(db_id(),object_id('HumanResources.Employee'),1,DEFAULT,'DETAILED' )    
GO

select database_id   
--,object_id    
--,index_id    
--,partition_id    
--,rowset_id    
--,allocation_unit_id    
--,allocation_unit_type    
,allocation_unit_type_desc    
--,data_clone_id    
--,clone_state    
--,clone_state_desc    
,extent_file_id    
,extent_page_id    
,allocated_page_iam_file_id    
,allocated_page_iam_page_id    
,allocated_page_file_id    
,allocated_page_page_id    
,is_allocated    
,is_iam_page    
,is_mixed_page_allocation    
,page_free_space_percent    
,page_type    
,page_type_desc    
,page_level    
,next_page_file_id    
,next_page_page_id    
--,previous_page_file_id    
--,previous_page_page_id    
--,is_page_compressed    
--,has_ghost_records     
from sys.dm_db_database_page_allocations(db_id(),object_id('HumanResources.Employee'),1,DEFAULT,'DETAILED' )    
GO


DBCC IND('AdventureWorks2012','HumanResources.Employee',1)    
GO

DBCC TRACEON(3604,-1)   
GO    
DBCC PAGE(AdventureWorks2012,1,1052,3) with tableresults

sp_helptext fn_PhysLocCracker   
GO

select [NationalIDNumber], 'Location(File:Page:Slot)'=sys.fn_physLocFormatter(%%physloc%%),   
KeyHashValue=%%lockres%%    
from [AdventureWorks2012].[HumanResources].[Employee]    
GO

DBCC SHOW_STATISTICS('HumanResources.Employee','PK_Employee_BusinessEntityID')   
WITH STAT_HEADER, HISTOGRAM    
GO

SELECT OBJECT_NAME(s.object_id) AS object_name,   
    COL_NAME(sc.object_id, sc.column_id) AS column_name,    
    s.name AS statistics_name,    
    STATS_DATE=STATS_DATE(s.object_id,s.stats_id),    
    auto_created,    
    user_created    
FROM sys.stats AS s JOIN sys.stats_columns AS sc    
    ON s.stats_id = sc.stats_id AND s.object_id = sc.object_id    
WHERE --s.name like '_WA%'    
sc.object_id=object_id('HumanResources.Employee')    
ORDER BY OBJECT_NAME(s.object_id),stats_date    
GO

UPDATE STATISTICS HumanResources.Employee   
-- WITH FULLSCAN    
WITH SAMPLE 50 PERCENT     
GO

Sunday, February 2, 2014

How to Install Tomcat 7

oracle@solaris:~$ sudo mkdir /opt/tomcat7
oracle@solaris:~$ sudo chown oracle:dba /opt/tomcat7
oracle@solaris:~$ unzip apache-tomcat-7.0.50.zip -d /opt/tomcat7

oracle@solaris:/opt/tomcat7/apache-tomcat-7.0.50/bin$ ./startup.sh
Using CATALINA_BASE:   /opt/tomcat7/apache-tomcat-7.0.50
Using CATALINA_HOME:   /opt/tomcat7/apache-tomcat-7.0.50
Using CATALINA_TMPDIR: /opt/tomcat7/apache-tomcat-7.0.50/temp
Using JRE_HOME:        /usr
Using CLASSPATH:       /opt/tomcat7/apache-tomcat-7.0.50/bin/bootstrap.jar:/opt/tomcat7/apache-tomcat-7.0.50/bin/tomcat-juli.jar

oracle@solaris/opt/tomcat7/apache-tomcat-7.0.50/bin$ ps -ef|grep tomcat7
oracle 4360 1 0 23:39:06 pts/1 0:03 /usr/bin/java -Djava.util.logging.config.file=/opt/tomcat7/apache-tomcat-7.0.50

JDK6 and above is required to run Tomcat7. Refer to here below on installation of JDK7.

http://www.dbaglobe.com/2014/02/install-jdk-in-solaris-11-using-pkg.html

Install JDK in Solaris 11 using PKG command

root@solaris:/opt/jdk7# pkg search -p jdk
PACKAGE PUBLISHER
pkg:/developer/java/jdk-6@1.6.0.35-0.175.1.0.0.24.1 solaris
pkg:/developer/java/jdk-7@1.7.0.7-0.175.1.0.0.24.0 solaris
pkg:/developer/java/jdk64@0.5.11-0.151.0.1 solaris
pkg:/developer/java/jdk@0.5.11-0.151.0.1 solaris
pkg:/developer/java/jdk@0.5.11-0.173 solaris
pkg:/developer/java/jdk@1.6.0.35-0.175.1.0.0.24.1 solaris
pkg:/developer/java/jdk@1.7.0.21-0.175.1.7.0.4.0 solaris
pkg:/developer/java/jdk@1.7.0.25-0.175.1.9.0.3.0 solaris
pkg:/developer/java/jdk@1.7.0.45-0.175.1.12.0.5.0 solaris
pkg:/developer/java/jdk@1.7.0.5-0.175.0.10.1.0.0 solaris
pkg:/developer/java/jdk@1.7.0.51.13 solaris
pkg:/developer/java/jdk@1.7.0.7-0.175.1.0.0.24.0 solaris
pkg:/gnome/accessibility/gnome-a11y-libs@2.30.0-0.175.1.0.0.21.0 solaris
pkg:/library/java/commons-collections@3.2.1-0.175.0.0.0.0.0 solaris
pkg:/library/java/demo64@0.5.11-0.151.0.1 solaris
pkg:/library/java/demo@0.5.11-0.151.0.1 solaris
pkg:/library/java/host-config@0.5.11-0.151.0.1 solaris
pkg:/library/java/java-demo-6@1.6.0.35-0.175.1.0.0.24.1 solaris
pkg:/library/java/java-demo-7@1.7.0.7-0.175.1.0.0.24.0 solaris
pkg:/library/java/java-demo@1.6.0.35-0.175.1.0.0.24.1 solaris
pkg:/library/java/java-demo@1.7.0.21-0.175.1.7.0.4.0 solaris
pkg:/library/java/java-demo@1.7.0.25-0.175.1.9.0.3.0 solaris
pkg:/library/java/java-demo@1.7.0.45-0.175.1.12.0.5.0 solaris
pkg:/library/java/java-demo@1.7.0.5-0.175.0.10.1.0.0 solaris
pkg:/library/java/java-demo@1.7.0.51.13 solaris
pkg:/library/java/java-demo@1.7.0.7-0.175.1.0.0.24.0 solaris
pkg:/library/java/javahelp@0.5.11-0.151.0.1 solaris
pkg:/library/java/javahelp@0.5.11-0.175.0.0.0.2.0 solaris
pkg:/library/java/javahelp@0.5.11-0.175.1.0.0.11.0 solaris
pkg:/library/java/manual/locale/ja@0.5.11-0.151.0.1 solaris
pkg:/library/java/manual@0.5.11-0.151.0.1 solaris
pkg:/runtime/java/jre-6@1.6.0.35-0.175.1.0.0.24.1 solaris
pkg:/runtime/java/jre-7@1.7.0.7-0.175.1.0.0.24.0 solaris
pkg:/runtime/java/runtime64@0.5.11-0.151.0.1 solaris

root@solaris:/opt/jdk7# pkg install --accept pkg:/developer/java/jdk-7
Packages to install: 3
Create boot environment: No
Create backup boot environment: No

Planning linked: 0/1 done; 1 working: zone:testzone
Planning linked: 1/1 done
DOWNLOAD PKGS FILES XFER (MB) SPEED
Completed 3/3 699/699 42.8/42.8 172k/s

Downloading linked: 0/1 done; 1 working: zone:testzone
Downloading linked: 1/1 done
PHASE ITEMS
Installing new actions 856/856
Updating package state database Done
Updating image state Done
Creating fast lookup database Done
Executing linked: 0/1 done; 1 working: zone:testzone
Executing linked: 1/1 done

Quick way to configure Statis IP in Virtual-box Solaris 11 Guest

root@solaris:~# ipadm show-if
IFNAME CLASS STATE ACTIVE OVER
lo0 loopback ok yes --
net0 ip ok yes --

root@solaris:~# ipadm delete-ip net0

root@solaris:~# ipadm show-if
IFNAME CLASS STATE ACTIVE OVER
lo0 loopback ok yes --

root@solaris:~# ipadm create-ip net0

root@solaris:~# ipadm create-addr -T static -a 192.168.6.101/24 net0

root@solaris:~# ipadm show-if
IFNAME CLASS STATE ACTIVE OVER
lo0 loopback ok yes --
net0 ip ok yes --

root@solaris:~# ipadm show-addr
ADDROBJ TYPE STATE ADDR
lo0/v4 static ok 127.0.0.1/8
net0/v4 static ok 192.168.6.101/24
lo0/v6 static ok ::1/128

Configure default gateway

root@solaris112:~# route -p show
No persistent routes are defined
root@solaris112:~# route -p add default 192.168.6.2
add net default: gateway 192.168.6.2: entry exists
add persistent net default: gateway 192.168.6.2
root@solaris112:~# route -p add -net 192.168.6.0 -gateway 192.168.6.2
add net 192.168.6.0: gateway 192.168.6.2: entry exists
add persistent net 192.168.6.0: gateway 192.168.6.2
root@solaris112:~# route -p show
persistent: route add default 192.168.6.2
persistent: route add -net 192.168.6.0 -gateway 192.168.6.2

Configure DNS Client

root@solaris112:~# svccfg -s "dns/client" setprop 'config/nameserver = net_address: ( 192.168.6.2 )'
root@solaris112:~# svccfg -s "dns/client" setprop 'config/domain = astring: ("dbaglobe.com")'
root@solaris112:~# svccfg -s "name-service/switch" setprop 'config/host = astring: "files dns"'
root@solaris112:~# svccfg -s "dns/client" listprop 'config/nameserver'
config/nameserver net_address 192.168.6.2
root@solaris112:~# svccfg -s "dns/client" listprop 'config/domain'
config/domain astring dbaglobe.com
root@solaris112:~# svccfg -s "name-service/switch" listprop 'config/nameserver'
root@solaris112:~# svccfg -s "name-service/switch" listprop 'config/host'
config/host astring "files dns"
root@solaris112:~# svcadm refresh name-service/switch
root@solaris112:~# svcadm enable dns/client

Saturday, February 1, 2014

Use sys.dm_io_pending_io_requests MV to check IO contention

use
go

select vfs.database_id,
df.name,
df.physical_name,
vfs.file_id,
ior.io_pending,
ior.io_handle,
ior.io_type
from sys.dm_io_pending_io_requests ior
right outer join sys.dm_io_virtual_file_stats(db_id(),null) vfs
on (vfs.file_handle=ior.io_handle)
inner join sys.database_files df on (df.file_id=vfs.file_id)
go