SQL SERVER – SUSPENDED Sessions Waiting on PAGELATCH_UP and PAGELATCH_SH – Solution

Today, We faced very intersting thing, On one of Our SQL Serevr database were many sessions suspending in the status of SUSPENDED and they were waiting on PAGELATCH_UP and PAGELATCH_SH and application working very slowly.

Suspended Status

Suspended Status means that the request currently is inactive because it is waiting on a resource and there is a good chance the request will start once the needed resource will be available.

Solution

Problem is misconfiguration of the TempDB. I noticed that there was a single tempdb file on the same drive as their data drive and was creating a performance issue. 

We immediately moved that file to a different drive and added few more (in our case 7 more) TempDB datafiles (ndf) and our performance issues were automatically resolved and all the queries got unsuspended. 

After a few minutes, the entire backlog was cleared the client got unparallel performance.

Tempdb files per core recomendation

On Microsoft website, it recommends :

"Create as many files as needed to maximize disk bandwidth. Using multiple files reduces tempdb storage contention and yields significantly better scalability. However, do not create too many files because this can reduce performance and increase management overhead. As a general guideline, create one data file for each CPU on the server (accounting for any affinity mask settings) and then adjust the number of files up or down as necessary. Note that a dual-core CPU is considered to be two CPUs."

SQL SERVER – DROP ASSEMBLY failed because '%ls' is referenced by object '%ls'

In this article we will discuss about an error message (DROP ASSEMBLY failed because '%ls' is referenced by object '%ls') related to Assembly.

Msg 6590, Level 16, State 1, Procedure procedure_name, Line 25
DROP ASSEMBLY failed because 'testi' is referenced by object 'test_wi'

when you are trying to drop assembly but it's dependent object exists in database you will get error:

Drop assembly assembly_name;

Msg 6590, Level 16, State 1, Line 1
DROP ASSEMBLY failed because 'testi' is referenced by object 'test_wi'.

Solution:
With this query you can find dependent objects

SELECT A.assembly_id,A.name as [Assembly Name],
B.object_id, C. name as [Object Name], C.type, C.type_desc
FROM Sys.Assemblies A
INNER JOIN SYS.ASSEMBLY_MODULES B oN a.assembly_id=B.assembly_id
INNER JOIN SYS.OBJECTS C ON B.object_id = C.object_id

in the above result set, you have the assembly name along with its dependent's objects names (CLR functions).
So you can drop CLR functions and then drop Assembly.

You cannot drop assembly without dropping its object. Preferably, you should alter the assembly not to drop. But if you need to drop, first drop all its objects and then you can drop Assembly.

SQL Server for Oracle Database Administrators (DBA)

These are Video Tutorials of SQL Server for Oracle Database Administrators (DBA) for easy understanding SQL Server concepts.
It contains:
-----------------------------------------
1) Database Instance
2) Database Architecture
3) Instance Architecture part 1
4) Instance Architecture part 2
5) Data Objects
6) Data Access
7) Data Protection
8) Basic Administration
9) Server Management
10) Schema Management
11) Database Security
12) Data Movement
13) Backup and Recovery
14) Performance Tuning
15) Scalability High Availability
16) Monitoring
-----------------------------------------
Here is playlist with all above tutorials:
SQL Server for Oracle Database Administrators (DBA)

Database Link between Oracle Database and SQL Server Database

We want to create database link from Oracle database to SQL Server database,
Environment :
Oracle Database 11.2.0.3.0 on Oracle Linux 6.5 x86_64
SQL Server 2008 on Windows Server 2008 R2

We need Software Oracle Database Gateways which can download from here

Here is step by step procedure for install Oracle Gateway and configure it for create database link from Oracle to SQL Server:
1) Install Oracle Database Gateways under ORACLE_HOME and check checkbox - Oracle Database Gateway for Microsoft SQL Server

2) Provide any existing Microsoft SQL Server details where you are planning to connect. It actually writes in configuration file and does not really verify

3) Run root script with root user

4) Make sure that the MS SQL database details are correct in the dg4msql parameter file.

vi $ORACLE_HOME/dg4msql/admin/initdg4msqltestdb.ora
# This is a customized agent init file that contains the HS parameters
# that are needed for the Database Gateway for Microsoft SQL Server

#
# HS init parameters
#
HS_FDS_CONNECT_INFO=SQLServer_HOST_IP:1433//testdb
# alternate connect format is hostname/serverinstance/databasename
HS_FDS_TRACE_LEVEL=off
#HS_FDS_RECOVERY_ACCOUNT=RECOVER
#HS_FDS_RECOVERY_PWD=RECOVER
HS_NLS_NCHAR = UCS2
# Oracle Database Characterset 
HS_LANGUAGE = AMERICAN_AMERICA.AL32UTF8

5) Append following in existing listener.ora. Change the name, path and port as required.

vi /u0/app/grid/product/11.2.0/network/admin/listener.ora
# listener.ora Network Configuration File: 

/u0/app/grid/product/11.2.0/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = my_oracle_db_ip)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /u0/app/grid

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent


SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (PROGRAM = /u0/app/oracle/product/11.2.0/bin/dg4msql)
      (ENV = "LD_LIBRARY_PATH=/u0/app/oracle/product/11.2.0/dg4msql/driver/lib:/u0/app/oracle/product/11.2.0/lib")
      (SID_NAME = dg4msqltestdb)
      (ORACLE_HOME = /u0/app/oracle/product/11.2.0)
    )
)

6) Reload listener for get new configuration and Verify that the service is registered with the listener

lsnrctl reload LISTENER
lsnrctl status LISTENER
……
Service "dg4msqltestdb" has 1 instance(s).
  Instance "dg4msqltestdb", status UNKNOWN, has 1 handler(s) for this service...

7) Append following in tnsnames.ora

DG4MSQLTETSDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = tcp)(HOST = my_oracle_host_ip)(PORT = 1521))
    (CONNECT_DATA =
      (SID = dg4msqltestdb)
    )
    (HS = OK)
  )

8)

tnsping DG4MSQLTESTDB
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = tcp)(HOST = my_oracle_host_ip)
(PORT = 1521)) (CONNECT_DATA = (SID = dg4msqltestdb)) (HS = OK))

OK (50 msec)

9) create database link in oracle database, This username must be already created in the Microsoft SQL Server database with proper permissions.

CREATE DATABASE LINK testdb CONNECT TO “SQLUser” IDENTIFIED BY “SQLUserPass” USING ‘DG4MSQLTESTDB’;

10) test database link :

select count(1) from “SQLTable”@testdb;

Note :
If you want to create multiple Database Links from oracle Database to different Sql Server databases you need to create different initdg4msql.ora file under $ORACLE_HOME/dg4msql/admin with corresponding parameters and also add entries in listener.ora and tnsnames.ora files.