Wait Types in SQL Server

Often we we get in a scenario where Application team knocks our door complaining slow processing of data causing delay to the scheduled processes and first reaction a DBA provides is checking running processes and blocking if any on the server. Many a times blocking is a culprit which keeps system busy and processes halted but many a time there is another thing which we need to check and that is checking wait types on the server.

There are many wait types which and we need to know their meaning so I would like to focus on that point today.

Common Wait Types in SQL Server

ASYNC_NETWORK_IO—This wait types points towards a possible network-related issue and most-often caused by a client application not consuming and processing results efficiently from the SQL Server and causing delays. In this wait type SQL is helpless as nothing can be done from SQL Server point of view. We have to escalate and engage the network team if there’s a long distance between servers or the application team to check on application/server resources.

CXPACKET—CXPACKET wait type is one of the most misinterpreted wait stats. The CXPACKET means Class Exchange Packet and in its essence, this can be described as data rows exchanged among two parallel threads that are the part of a single process. One thread is the “producer thread” and another thread is the “consumer thread”. This wait type is directly related to parallelism and it occurs in SQL Server whenever SQL Server executes a query using parallel plan.

This CXPACKET wait type is normal for SQL Server and it is an indicator that SQL Server is using a parallel plan in executing a query, which is generally faster comparing to a query executed in a serialized process. When the parallel plan is used, the query is executed in multiple threads and the query can continue only when all parallel threads are completed. This mean that query will be as fast as the slowest thread. Look at researching and changing Maximum Degree of Parallelism (MAXDOP).

DTC—This wait type is not on the local system. When using Microsoft Distributed Transaction Coordinator (MS-DTC), a single transaction is opened on multiple systems at the same time, and the transaction cannot be concluded until it’s been completed on all of the systems. This wait type accumulates while SQL Server is waiting for the Distributed Transaction Co-Ordinator (DTC) to access the internal service global state object. Distributed transactions run across multiple database instances and sometimes other platforms like Oracle. This wait type is uncommon and if values are excessive then further investigation is required

LCK_M*—This wait type happens when a query locks an object while another query is waiting to lock the same object. A common scenario is when a query is trying to modify a row while another query is trying to read the same row. Review the day and time the locking occurred and which SQL statements were being executed. This wait occurs when a request is waiting to acquire a shared lock. This typically happens when read requests are blocked by write transactions (implicit or explicit) that have been kept open for extended periods of time. Tuning these statements will reduce the session holds on the locks.

NETWORKIO—This wait indicates that one of two scenarios are happening. The first scenario is that the session (i.e., SPID) is waiting for the client application to process the result set and send a signal back to SQL Server that it is ready to process more data. The second is that there may be a network performance issue.

OLEDB—This wait type indicates that a SPID has made a function call to an OLE DB provider and is waiting for the function to return the required data. This wait type may also indicate the SPID is waiting for remote procedure calls, linked server queries, BULK INSERT commands, or full-search queries.

PAGEIOLATCH_*— This wait type occurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Shared mode. Long waits may indicate problems with the disk subsystem. Buffer latches, including the PAGEIOLATCH_EX wait type, are used to synchronize access to BUF structures and associated pages in the SQL Server database. The most frequently occurring buffer latching situation is when SQL Server is waiting to read a data file page or workload from storage. These pages and workloads were not cached in memory and need to be retrieved from the disk. Additional memory will help prevent pages from getting pushed out.

SOS_SCHEDULER_YIELD—SQL Server instances with high CPU usage often show the SOS_SCHEDULER_YIELD wait type. This doesn’t mean the server is underpowered; it means further research is needed to find which individual task in a query needs more CPU time. Check the Max Degree of Parallelism (MAXDOP) to ensure proper core usage. Ensure high CPU performance from both within Windows and the system BIOS.

WRITELOG—When a SQL Server session waits on the WRITELOG wait type, it’s waiting to write the contents of the log cache (user delete/update/inserts operations) to the disk where the transaction log is stored and before telling the end user his or her transactions have been committed. Disabling unused indexes will help, but the disk is the bottleneck here, and the transition log should be moved to more appropriate storage.

SSIS package fails from sql job but succeeds from development studio

Recently I was debugging a issue which many of you might have faced and even may have fixed but still I thought to share it on an open portal so in case if someone is still looking for solution or yet stuck in this then he can get benefited from this piece.

Issue observed was that the a SSIS package was working fine in business development studio but the same package was not working via SQL agent job and throwing misleading message pointing towards connection issue. In case if package has connection to other databases like DB2, Oracle or MYSQL then the situation becomes more worse and confusing.

Below is the sample of the error message,

******************************************************************

Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.

End Error

An OLE DB record is available.  Source: "Microsoft DB2 OLE DB Provider"  Hresult: 0x80040E14  Description: "An internal network library error has occurred. A network level syntax error has occurred.".

Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "XXXXXXXX" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.

******************************************************************

Here in this error message SQL is trying to point that there is some issue with connection but unable to tell exactly where to check and even none of the blogs I visited were even roaming around this. Many blogs suggested that I should change executing user to SQL proxy with appropriate permission etc etc but no exact help or fix provided.

While debugging with my colleague we started digging this up and after the long hunt we found a solution which made my day. Basically when we define a connection via connection manager in package and SSIS package would use it for execution but when we point the same SSIS package through SQL jobs it shows us multiple tabs in job step among which we have to click on the "Data Sources" tab and put the correct password in our desired connection string and save the changes. 

In case if you again wish to make even a small change to same job without touching SSIS package step, again put the password and save it because once you edit the step and save it SQL agent hides the password and remains invisible to users. If we again click on save even without making any changes to job step or package, password disappears and users have to put that password manually.

This sounds something strange but this is for the security so any random user having access to sql jobs does not see password easily and password remains safe.

Hope this fixes your issues. 

Please try this and share the feedback whether this fixed your issue or not of if something else needs to be added in this to make it more easy to understand.

The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine

Hello Techies,

While working on SQL import wizard using excel with (.xlsx) extension my client started experiencing issues with the below mentioned issue. Surprisingly he was not encountering any issue while working with .xls or .csv file 

---------------------------------------------------------------------

TITLE: SQL Server Import and Export Wizard

---------------------------------------------------------------------

The operation could not be completed.

---------------------------------------------------------------------

ADDITIONAL INFORMATION:

The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. (System.Data)

---------------------------------------------------------------------

While working on this to find solution I found many websites claiming many solutions which provided and their own experiences but again digging this more I came to know that Excel 2010 driver is 64 bit but by default SSMS import export wizard is 32 therefore the error message appears.

To fix this issue you can import using the Import Export Data (64 bit) tool for trial.

Depending on SQL Binary location

You can find your binary location doing below steps,

Right click on Instance name --> Root Directory

This value shown on the right hand side shows your SQL installation folder so access the "DTSWizard.exe" application.

“C:\Program Files\Microsoft SQL Server\110\DTS\Binn\DTSWizard.exe” 

I hope you were able to proceed with the import export wizard 😊 and to fix this permanently you should copy the “DTSWizard.exe” 64 bit file from the below location to x86 folder with replace option.

Note: Its always good to copy the file to be replaced to be on safer side to some other location.

Source

C:\Program Files\Microsoft SQL Server\110\DTS\Binn\DTSWizard.exe

Destination

C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\DTSWizard.exe

Hope this fixed your issue so please help me with your feedback to encourage me writing further sharing my experience.

syspolicy_purge_history failing in SQL 2014

This is very common job which gets created once the server is ready as a free gift from MS to perform cleanup on the server for purging job history.

This job never troubled till recent time but you never know in IT that when the rules get violated and DBA's start scratching their head for finding fix and in a recent scenario similar issue made me put my 100% to troubleshoot and find a fix.

This job works excellent in all versions on SQL server but this has some issue in SQL 2014 and if there is difference in the registry setting and PowerShell execution policy shown below then it would fail. To work this job smoothly both the settings need to be same and for making any registry changes there is no windows reboot required but safer side ensure to have registry backup in case of rollback. 



Once both these values match this job should start working normal with no issues.

Please let me know your feedback on this.

KEY Lookup & RID Lookup

In sql server there are lot of terms which confuse the developer many times about the functionality but while understanding indexing it is very important to understand these as by properly using it the query performance could be increased.

Here i will quickly discuss about two terms i.e. KEY LOOKUP(Previously known as bookmark lookup) and RID Lookup.

KEY LOOKUP:

If a table has a clustered index on it is then it is called as KEY LOOKUP. In sql server data is stored is stored in Binary Tree format & by following this way data is stored. So here the table having a clustered index have a key point to data information.

RID LOOKUP (ROW IDENTIFIER LOOKUP):

In a case where a table does not have clustered index, but the non-clustered index is created on it then it is called RID lookup.

This operation is very expensive as syetem needs to scan through the B-Tree heap with the row Identifier.

 

The best suggested for overcoming these scenarios is to have a clustered index on a table.

Joins in SQL SERVER

Sql Server is all about data & the data may be stored on the multiple places(tables) which could be queried to trace information at any point of time. Whenever thinking about retrieving the concerned information from all the tables in the form of report the joins come in picture. Joins are an very important part of database & without using them it is really impossible to even think about database.

These are the most used & main type of joins in SqlServer,

1. Inner Join
2. Outer Join
3. Cross Join

Use below mentioned script on any of your test database for understanding the various types with example.

CREATE TABLE STUDENTS(STUDENTID INT IDENTITY(1,1), NAME VARCHAR(100), MOBILENO BIGINT)

CREATE TABLE SPECIALCOURSE(ID INT IDENTITY(1,1), STUDENTID INT, STATUS BIT)

INSERT INTO STUDENTS(NAME, MOBILENO)

SELECT 'AJAY',9797979797

UNION ALL

SELECT 'SANJAY',1234567890

UNION ALL

SELECT 'SAMMY',9874563210

UNION ALL

SELECT 'SAMUAL',8789456512

UNION ALL

SELECT 'GANESH',7412589630

UNION ALL

SELECT 'MAHESH', 9632587400

INSERT INTO SPECIALCOURSE(STUDENTID, STATUS)

SELECT 1,0

UNION ALL

SELECT 3,1

Inner Join

1.1) Inner Join:

Inner join is the most used join type in SqlServer. Whenever trying to check the concerned data only from the system the inner join is used. Inner join is very useful whenever user needs to get the only matching data from the concerned tables. To use this join type the matching columns from all the required tables needs to be mention so while executing query SqlServer engine consider the specified column for returning the proper information.

Ex. Code:

SELECT A.STUDENTID, A.NAME, A.MOBILENO, B.STATUS

FROM STUDENTS A INNER JOIN SPECIALCOURSE B ON A.STUDENTID = B.STUDENTID

1.2)Self Join:

Another type of inner join is Self join which could be defined as join within same table for extraction of related data within same table. The best example for this will be Manager/Employee relationship between same table. Unfortunately I have not designed my table which has this relationship otherwise I could have showed the example query.

Outer Join

2.1) Left Outer Join:

Whenever we wish to get all the data from the left table we need to use Left Outer Join.. Left join will return all the matching rows from the left table & in case of no match it will return NULL.
It is really useful when we want to find all the information from one side table with the relevant information from second table.

Here the left table is STUDENTS & in second table i.e. SPECIALCOURSE there is some information about few students who have joined special course.

Ex. Code:

SELECT A.STUDENTID, A.NAME, A.MOBILENO, B.STATUS

FROM STUDENTS A LEFT JOIN SPECIALCOURSE B ON A.STUDENTID = B.STUDENTID

2.2) Right Outer Join:

There may be a situation during the query when user needs information from second second & the matching or non-matching records from first table. In these scenarios developer will not swap the table from left to right as there is already a provision called Right Outer Join where the purpose can be achieved by using Right Outer Join.

In this scenario user will receive all the the information from second(Right) table & the matching information from first table.

Ex. Code:

SELECT A.STUDENTID, A.NAME, A.MOBILENO, B.STATUS

FROM STUDENTS A RIGHT JOIN SPECIALCOURSE B ON A.STUDENTID = B.STUDENTID

2.3) FULL Outer Join:

FULL outer join do not look for the match between the two tables & returns all the information from both the tables. This kind of results may be required when there is a need of all the data from system which both the tables.

Ex. Code:

SELECT A.STUDENTID, A.NAME, A.MOBILENO, B.STATUS

FROM STUDENTS A FULL OUTER JOIN SPECIALCOURSE B ON A.STUDENTID = B.STUDENTID

CROSS JOIN

Working with Cross join like working with multiplication between tables. Whenever there is some kind of situation when a user needs to map all the records from left table with all records of left table then in this situation the Cross join is brought into picture.

Here in this below query Students table 6 records 6 records & SPECIALCOURSE table has 2 records. As mentioned above it will return the multiplication the output will be 6 * 2 = 12 records & in output all the students will be mapped to the all SPECIALCOURSE records.

Ex. Code:

SELECT A.STUDENTID, A.NAME, A.MOBILENO, B.STATUS

FROM STUDENTS A CROSS JOIN SPECIALCOURSE B

Note: If the any of the two tables do not have record then there will be no output from this join.

Like other join type this join type needs not to have a ON condition.

Normalization VS De-Normalization

Normalization & De-Normalization

Normalization can be described as a breaking up of the information into multiple parts or logical units in the form of tables. The concept of normalization is used for organizing the data in database in a managed manner where the redundancy of data does not happen & data remains accurate.

Normalization is basically a process of reducing redundancy i.e. Data repetition in database for similar type of information where-in De-normalizations only limited number of tables are created. There are lot of many more merits & demerits of Normalization & De-normalization so let’s look them.

 Normalization:

As mentioned above Normalization is a process of reducing redundancy i.e. Data repetition in database for similar type of information so While applying normalization on database the data is spilt into multiple sets of table like, master tables are created for each category of information so that the same string do not gets repeated into system. Normalization is a process of set guidelines which needs to be followed for implementing normalization.

A non-normalized database may contain some tables which may contain the most of data into a few tables. There are some points which indicate the general problems of non-normalized database.

1.       Data is stored in few tables only instead of multiple tables but this is not good as data is easily identical which is bad practice from the security perspective

2.        Query performance will be affected as the table will grow drastically

3.       Activities like Update will be affected causing the poor efficiency of database and Data integrity.

In a process of applying normalization on database few points needs to be keep in mind among which some are as follow,

·         Data should not be redundant (Duplicate) or very minimum for some cases for ex. In a candidate management system where candidates information is stored. Here in this case the address & other optional information is not required to stored in a same table as it is not always required while searching basic information about candidate.

In the process of normalization naming convention is always an important point because in vice-versa to de-normalization the data is split into multiple tables & during the needs the tables needs to be joined with one another & in this case the identical & proper naming convention will play a key role.

Normalization benefits the database in many ways some of them are as follow,

1.       Proper database organization

2.       Avoiding redundant data

3.       Database consistency and integrity in a which assures the accurate & co-related data within same database.

4.       Proper & Secure database system as an unknown & unfamiliar user cannot find & understand data easily.

5.       Referential integrity means the values of one table is dependent upon the one or more than one number of columns

6.       Having a parent child relations between tables so that any referential data is not lost

As like the other thing normalization also has some drawbacks which is that due to heavy normalization during the processing of data during the insert, update or select all the relational objects are required to be bring in use which cause the high CPU use & IO operation increase so although the huge benefits the normalization should be carried out wisely.

De-Normalization:

Almost described most of the differences between normalization & de-normalization & the various pros & cons of both concepts

Description of de-normalization in simple terms “Instead of storing data into multiple space a few number of tables where most of the data can be stored for stress free environment without caring about too much of data hiding methodology”