SQL Server Performance Tuning Part 6

Below are the few setting in SQL Server that can improve the performance of your queries.

Ø  Statistics Update – enables the optimizer to create better execution plans

Ø  Tempdb Configuration – improves concurrency

Ø  Max Degree of Parallelism(MAXDOP) – improves query performance

Ø  Cost Threshold for Parallelism - improves query performance

Ø  Instant File Initialization – faster file growth  

Ø  Optimize for Ad Hoc Workloads – reduces cached plan bloat  

Ø  Memory Configuration – how much memory to give to SQL Server

Ø  Lock Pages in Memory – prevents swapping to disk

Ø  Query Governor Cost Limit - allows resources to be shared fairly 

  

Counters from Perfmon :

Ø  Page Reads/​Sec – indicates time spent reading data

Ø  Page Writes/​Sec -indicates time spent writing data

Ø  Page Life Expectancy - how long data lives in cache

Ø  Buffer Cache Hit Ratio – is data coming from cache or disk

Ø  % Processor Time – CPU usage

Ø  Processor Queue Length – waiting on CPU

Ø  Locks – indicates reduced concurrency

DMV’s : 

Ø  sys.​dm_​io_​virtual_​file_​stats – IO and wait information

Ø  sys.​dm_​db_​index_​usage_​stats – how a table/index is used

Ø  sys.​dm_​exec_​query_​stats – records time, IO, CPU etc used by queries

Ø  sys.​dm_​os_​sys_​info – OS information e.g. CPU count, server memory  

CREATE DATABASE failed. Some file names listed could not be created. Check related errors. [SQLSTATE 42000] (Error 1802). The step failed.

Possible Reasons and Resolutions :

 This problem usually occurs when the user that is trying to create the database doesn't have the necessary permissions in SQL Server. If you use the Administrator's account to create the database, this situation can be avoided. You can also check if the account that failed to create the database has the permissions described in this article.
 The problem can also occur if the name of the database you attempted to create is already in use by another database or if files from a database with the same name remain on the computer. Try creating a database with a different name.
 Check if the disk on which the SQL Server is trying to create the database has enough free space.

Cluster resource 'SQL Server' in clustered service or application 'SQL Group' failed.

We got this error while doing fail over or fail back in cluster environment. Unfortunately we didn't find any accurate information in cluster event log or in window event log.

I have used the below approach to find out the root cause of failure.

Start SQL Service via Net Start NOT via Failover cluster Manager.

Ex : net start MSSQL$SQL2014

How to fix log cannot be rebuilt the database because the database was not cleanly shut down

Requirement : I got few mdf and ldf files from client and requested to restore the databases on production environment. I got the below error for one of the database during mdf and ldf attachment.

"File activation failure. The physical file name "G:\MS SQL Server 2005 Data Files\MSSQL.1\MSSQL\DATA\TEST.ldf" may be incorrect.
The log cannot be rebuilt because the database was not cleanly shut down."

Resolution :

I have used the below command to fix the issue.
CREATE DATABASE TEST ON (FILENAME = 'R:\SQL Data\TEST.mdf')
FOR ATTACH_FORCE_REBUILD_LOG

Note : Make sure service account or your user has the proper access to place the files in respective drive.

ORA-01219: database not open: queries allowed on fixed tables/views only

Error : ORA-01219: database not open: queries allowed on fixed tables/views only

I got this error when I tried to startup the database.

Possibilities : System files might be corrupted or lost.

Resolution :

1. SQL> recover datafile 1;

2.SQL> alter database open;

2.SQL> select * from tab;

unable to take database online in ORACLE 11g

Error : ORA-01113: file 4 needs media recovery

I got the below error during database recovery (after performing the database backup using HOT backup method .)
 alter database datafile '/disk1/dev/data/users01.dbf' online;

ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: '/disk1/dev/data/users01.dbf'

Resolution 

In order to make the database online you have to follow the below steps

1. alter database datafile  '/disk1/dev/data/users01.dbf' offline;

2. cp users01.dbf /disk1/dev/data/ # From hot backup location.

3. recover datafile '/disk1/dev/data/user01.dbf';

4.alter database datafile 'disk1/dev/data/users01.dbf' online;

How to fix data file (ORA-01110: data file 4: '/disk1/dev/data/users01.dbf') missing issue in oracle.

I got the below error when I am trying to start up the data base in oracle 11g.

ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/disk1/dev/data/users01.dbf'

Error screen : 

In order to fix the above error. We just need to copy the users01.dbf file from missing location.

Resolution :

In my case the backup file is located in disk2/coldbackup so I have copied this file to the target location i.e /disk1/dev/data/

Startup and shutdown Methods in Oracle

When startup command is issued, Oracle looks for the parameter file in following order.

1. Spfile<SID>.ora

2. init<SID>.ora

Oracle look for the parameter file in $ORACLE_HOME/dbs

We can startup the database instance with following options.

1. startup

2. startup nomount

3. Startup mount

startup pfile=’/location/filename’

4. startup upgrade

5. startup mount restrict

1. Startup:-

ü  It will read the parameter file.

ü  It will start the background processes and allocate memory (SGA). – Instance started.

ü  It reads the controlfile. –Instance mounted.

ü  Physically checks the datafiles and redolog files.

ü  Checks the SCN number of the datafiles and controlfiles.

ü  It is used in normal situations.

ü  If the SCNs in the control files don’t match some of the SCNs in the data files headers—the background process (smon) will automatically perform an instance recovery before opening the database.

2. Startup mount:--

ü  It will read the parameter file.

ü  It will start the background processes and allocate memory (SGA). – Instance started.

ü  It reads the controlfile. –Instance mounted.

ü  Can be used when:-- Recovery is needed for the entire database, Flashback is to be performed, any feature like archive log is to be enabled or disabled.

3. startup nomount:--

ü  It will read the parameter file.

ü  It will start the background processes and allocate memory (SGA). – Instance started

ü  Can be used when:-- Creating database and creating controlfile.

4. startup upgrade:--

ü  It will do 1 to 5 and

ü   Give the option of upgrading database.

ü  Users cannot connect to the database.

ü  Can be used to upgrade the database dictionary

ü  Database must be shutdown and restarted for users to connect.

5. startup mount restrict:--

ü  It will read 1, 2 and 3.

ü  It will not read 4 and 5.

ü  Used when the database is dropped.

Changing the status:--

Alter database mount;

This command can be used when database is in nomount state, this will read the controlfiles and mount the database.

Alter database open;

This command can be used when database is mounted.

This will read the datafiles and redolog file and will check if the datafiles are consistent. Allows the users to connect to the database,

Alter database close;

This command can be used when database open. This will release the datafiles and redolog files.

User connections will not be allowed. Status will change to mount.

Alter database dismount;

This command can be used when database is at mount state. This will release the control file and change the status to nomount.

The database can be shutdown with the following options:--

Shutdown:--

No new user connections can be made to the database once the command is issued. Oracle waits for all users to exit their sessions before shutting down the database. No instance recovery is needed when you restart database, it will be consistent when it’s shut down in this way. Oracle closes the data files and terminates the background processes. Oracle’s SGA is deallocated.

Shutdown transactional:--

No new user connections are permitted once the command is issued. Existing users can’t start a new transaction and will be disconnected. If a user has a transaction in progress, oracle will wait until the transaction is completed before disconnecting the user. After all existing transactions are completed. Oracle shuts down the instance and deallocates memory. Oracle writes all redo log buffers and data block buffers to disk. No instance recovery is needed because the database is consistent.

BEGIN
  DBMS_LOCK.sleep(seconds => 60.01);
END;
/

Shutdown immediate:--

No new user connections are allowed once the command is issued. Oracle immediately disconnects all users. Oracle terminates all currently executing transactions. For all transactions terminated midway, oracle will perform a rollback so that database ends up consistent. Sometimes oracle may be busy rolling back the transactions it just terminated. Oracle terminates the background processes and deallocates memory. No instance recovery is needed upon starting up the database because it is consistent when shut down.

Shutdown  abort:--

No new connections are permitted once the command is issued. Existing sessions are terminated, regardless of whether they have an active transaction or not. Oracle doesn’t roll back the terminated transactions. Oracle doesn’t write the redo log buffers and data buffers to disk. Oracle terminates the background processes, de allocates memory immediately, and shutdown. Upon restarting, oracle will perform an automatic instance recovery, because the database isn’t guaranteed to be consistent when shut down.

How to install Oracle on Linux environment

I have done Oracle 11g installation on Linux environment by creating VM on windows laptop. Below are the steps as well as screen shots to install Oracle step by step.

[root@myoracle ~]# groupadd oinstall
[root@myoracle ~]# groupadd dba
[root@myoracle ~]# useradd -g oinstall -G dba oracle
[root@myoracle ~]# cd /u01
[root@myoracle u01]# ls -ltr
[root@myoracle ~]# ls -ltr /u01
[root@myoracle ~]# chown -R oracle:ointall /u01
[root@myoracle ~]# chmod 775 /u01
[root@myoracle ~]# ls -lrt /u01
[root@myoracle u01]# passwd oracle
[root@myoracle ~]# su - oracle
[root@myoracle ~]# cd /mnt/hgfs
[root@myoracle hgfs]# cd database/
[root@myoracle ~]# cd /opt
[root@myoracle ~]# ls -ltr
[root@myoracle ~]# chown -R oracle:oinstall /opt
[root@myoracle ~]# chown -R oracle:oinstall /disk1
[root@myoracle ~]# chown -R oracle:oinstall /disk2
[root@myoracle ~]# su - oracle
[root@myoracle ~]# cd /mnt/hgfs
[root@myoracle hgfs]# cd database/
[root@myoracle database]# unzip P1339067_112040_Linux-x86-64_lof7.zip -d/opt
[root@myoracle database]# unzip P1339067_112040_Linux-x86-64_2of7.zip -d/opt
[root@myoracle database]#cd /opt
[root@myoracle opt]#cd database
[root@myoracle database]#ls
In new terminal type xhost +
again in old terminal
[root@myoracle opt]# sh runInstaller

After installation completes you need to run the below script in new terminal under root user.

[root@myoracle ~]# /u01/app/oraInventory/orainstRoot.sh

[root@myoracle ~]# /u01/app/oracle/product/11.2.0/dbhome_1/root.sh

How to connect Oracle in Linux environment ?

Below is the step by step procedure to Connect Oracle.

T-SQL scripts to monitor Logshipping in SQL Server

--RETURNS A RESULT SET CONTAINING STATUS AND OTHER INFORMATION FOR REGISTERED PRIMARY AND SECONDARY DATABASES ON A PRIMARY, SECONDARY, OR MONITOR SERVER.
EXEC SP_HELP_LOG_SHIPPING_MONITOR

--GET LOG SHIPPING SQL JOBS
SELECT *
FROM MSDB.DBO.SYSJOBS
WHERE CATEGORY_ID = 6

--LOG SHIPPING: GET SQL JOB HISTORY: COPY JOB (INCLUDE YOUR JOBID)
SELECT MSDB.DBO.AGENT_DATETIME(RUN_DATE, RUN_TIME) AS 'RUNDATETIME'
 ,*
FROM MSDB.DBO.SYSJOBHISTORY
WHERE JOB_ID = 'D74C0F28-8626-4FD2-AA8F-72D00DCF5FDD'
 --UNCOMMENT TO GET ONLY THE LAST RUN HISTORY
 AND MSDB.DBO.AGENT_DATETIME(RUN_DATE, RUN_TIME) = (
  SELECT TOP 1 MSDB.DBO.AGENT_DATETIME(RUN_DATE, RUN_TIME) AS 'RUNDATETIME'
  FROM MSDB.DBO.SYSJOBHISTORY
  WHERE JOB_ID = 'D74C0F28-8626-4FD2-AA8F-72D00DCF5FDD'
  ORDER BY RUNDATETIME DESC
  )
ORDER BY RUNDATETIME DESC
 ,INSTANCE_ID DESC

--EXEC MSDB.DBO.SP_HELP_JOBHISTORY @JOB_NAME = N'LS_COPY_XYZ' ; 
--GET LAST RUN HISTORY: RESTORE JOB (INCLUDE YOUR JOBID)
SELECT MSDB.DBO.AGENT_DATETIME(RUN_DATE, RUN_TIME) AS 'RUNDATETIME'
 ,*
FROM MSDB.DBO.SYSJOBHISTORY
WHERE JOB_ID = 'E19D351F-22B8-4624-A8A2-102C094D5E84'
 --UNCOMMENT TO GET ONLY THE LAST RUN HISTORY
 AND MSDB.DBO.AGENT_DATETIME(RUN_DATE, RUN_TIME) = (
  SELECT TOP 1 MSDB.DBO.AGENT_DATETIME(RUN_DATE, RUN_TIME) AS 'RUNDATETIME'
  FROM MSDB.DBO.SYSJOBHISTORY
  WHERE JOB_ID = 'E19D351F-22B8-4624-A8A2-102C094D5E84'
  ORDER BY RUNDATETIME DESC
  )
ORDER BY RUNDATETIME DESC
 ,INSTANCE_ID DESC

--EXEC MSDB.DBO.SP_HELP_JOBHISTORY @JOB_NAME = N'LS_RESTORE_XYZ'; 
--STORES ALERT JOB ID
SELECT *
FROM MSDB.DBO.LOG_SHIPPING_MONITOR_ALERT

--STORES ERROR DETAIL FOR LOG SHIPPING JOBS
SELECT *
FROM MSDB.DBO.LOG_SHIPPING_MONITOR_ERROR_DETAIL

--STORES HISTORY DETAILS FOR LOG SHIPPING JOBS (AGENT SESSION DETAILS)
SELECT *
FROM MSDB.DBO.LOG_SHIPPING_MONITOR_HISTORY_DETAIL
ORDER BY LOG_TIME DESC

--STORES ONE MONITOR RECORD PER PRIMARY DATABASE IN EACH LOG SHIPPING CONFIGURATION 
SELECT *
FROM MSDB.DBO.LOG_SHIPPING_MONITOR_PRIMARY

--STORES ONE MONITOR RECORD PER SECONDARY DATABASE IN A LOG SHIPPING CONFIGURATION
SELECT *
FROM MSDB.DBO.LOG_SHIPPING_MONITOR_SECONDARY

--STORES ONE RECORD FOR THE PRIMARY DATABASE IN A LOG SHIPPING CONFIGURATION
SELECT *
FROM MSDB.DBO.LOG_SHIPPING_PRIMARY_DATABASES;

--STORES ONE RECORD PER SECONDARY DATABASE IN A LOG SHIPPING CONFIGURATION
SELECT *
FROM MSDB.DBO.LOG_SHIPPING_SECONDARY_DATABASES;

How to add new articles to Transactional Replication without Generating Snapshot of All Articles

Run the below commands on the Publication database

USE Distribution
GO

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT DISTINCT srv.srvname publication_server
 ,a.publisher_db
 ,p.publication publication_name
 ,a.article
 ,a.destination_object
 ,ss.srvname subscription_server
 ,s.subscriber_db
 ,da.name AS distribution_agent_job_name
FROM MSArticles a
INNER JOIN MSpublications p ON a.publication_id = p.publication_id
INNER JOIN MSsubscriptions s ON p.publication_id = s.publication_id
INNER JOIN master..sysservers ss ON s.subscriber_id = ss.srvid
INNER JOIN master..sysservers srv ON srv.srvid = p.publisher_id
INNER JOIN MSdistribution_agents da ON da.publisher_id = p.publisher_id
 AND da.subscriber_id = s.subscriber_id
ORDER BY 1 ,2 ,3

If the columns “immediate_sync” and “allow_anonymous” are having a value of 1(Enabled) for both of them, the Snapshot of all the articles will be generated.

As we do not want this behavior, we will change these values to 0(Disabled) for both the “immediate_sync” and “allow_anonymous” options.

We can disable these options by running below commands on Publication database.

Run the below commands on the Publication database


use <PublicationDB>
go

EXEC SP_CHANGEPUBLICATION @PUBLICATION = 'YOUR PUBLICATION NAME'

,@PROPERTY = 'ALLOW_ANONYMOUS' ,@VALUE = 'FALSE'

GO

EXEC SP_CHANGEPUBLICATION @PUBLICATION = 'YOUR PUBLICATION NAME'

,@PROPERTY = 'IMMEDIATE_SYNC' ,@VALUE = 'FALSE'

GO




Add article through GUI



use <PublicationDB>
go
EXEC SP_CHANGEPUBLICATION @PUBLICATION = 'YOUR PUBLICATION NAME'

,@PROPERTY = 'IMMEDIATE_SYNC' ,@VALUE = 'TRUE'

GO
EXEC SP_CHANGEPUBLICATION @PUBLICATION = 'YOUR PUBLICATION NAME'

,@PROPERTY = 'ALLOW_ANONYMOUS' ,@VALUE = TRUE

Where to start performance tuning in SQLServer

There is no special area to start debugging the issue in SQL Server.

Refer the below screens shots for quick idea.