Troubleshooting SQL Server Connectivity Issues

When you failed to connect to your SQL Server, the issue could be:

•  Network issue
• SQL Server configuration issue
• Firewall issue
• Client driver issue
• Application configuration issue
• Authentication and logon issue

Please go throught the below link on how to resolve these issues,

https://blogs.msdn.microsoft.com/sql_protocols/2008/04/30/steps-to-troubleshoot-sql-connectivity-issues/

Best Practices

Server Side:

- Check that TCP/IP protocol is enabled in SQL Server Network Configuration section in SQL Server Configuration manager

- Check that SQL Server Service is Running.

- SQL Browser service status is “Running”. (if you have got an instance that is not by default with port 1433)

- Check that Firewall is configured to allow acces to SQL Server by opening the desired port.

https://docs.microsoft.com/en-us/sql/sql-server/install/configure-the-windows-firewall-to-allow-sql-server-access

https://support.microsoft.com/en-us/help/968872/how-to-open-the-firewall-port-for-sql-server-on-windows-server-2008

- Verify whether Remote Connections are accepted by the server or not.

Client Side:

- Verify that the Connection string is correct and pointing to the right server. Be sure on the instance name.

- Check whether the Server is reachable from client machine or not.(use Ping)

- Also try connecting to Server machine out of your application.(use SQLCMD)

Page Life Expectancy

Page Life Expectancy is the number of seconds the average page of data has been in the buffer pool. Keeping the data in memory gives SQL Server quicker access to it instead of making it long by accessing the same from disk.

You can find the value of the PLE by running the following query.

SELECT [object_name],
[counter_name],
[cntr_value] FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Manager%'
AND [counter_name] = 'Page life expectancy'

The recommended value of the PLE counter is (updated: minimum of) 300 seconds. I have seen on busy system this value to be as low as even 45 seconds and on unused system as high as 1250 seconds. Page Life Expectancy is the number of seconds a page will stay in the buffer pool without references. In simple words, if your page stays longer in the buffer pool (area of the memory cache) your PLE is higher, leading to higher performance as every time request comes there are chances it may find its data in the cache itself instead of going to the hard drive to read the data.

Also Refer

https://www.brentozar.com/askbrent/page-life-expectancy/

https://blogs.msdn.microsoft.com/sqlcat/2006/06/23/oltp-blueprint-a-performance-profile-of-oltp-applications/

https://blog.sqlauthority.com/2014/05/01/sql-server-good-value-for-page-life-expectancy-notes-from-the-field-026/

SQL Server Resource Governor

Pinal Dave as already written an excellent article on how to configure and use Resource Governor. Please refer that in the below link:

https://blog.sqlauthority.com/2012/06/04/sql-server-simple-example-to-configure-resource-governor-introduction-to-resource-governor/

Other useful resources:
https://www.red-gate.com/simple-talk/sql/learn-sql-server/resource-governor/
https://www.sqlshack.com/sql-server-2014-resource-governor/
http://www.sqlpassion.at/archive/2014/01/14/how-to-throttle-your-storage-with-resource-governor-in-sql-server-2014/

Enable this feature at your own risk. Go through the below link to know the problems with it.
https://www.brentozar.com/blitz/resource-governor-enabled/ 

Difference between Inner Join and Left Semi Join

Semi joins will not have any syntax in SQL.

The following are the logical join operators that SQL Server supports:

• Inner join
• Outer join
• Cross join
• Cross apply
• Semi-join
• Anti-semi-join

An INNER JOIN returns the columns from both tables. A LEFT SEMI JOIN only returns the records from the left-hand table. It's equivalent to (in standard SQL):

SELECT name 

FROM table_1 a 

WHERE EXISTS(

SELECT * FROM table_2 b WHERE (a.name=b.name))

If there are multiple matching rows in the column of right-side table, an INNER JOIN will return one row for each matching column, while a LEFT SEMI JOIN only returns the matching rows from the left table. That's why you're seeing a different number of rows in your result.

Example:

Suppose there are 2 tables TableA and TableB with only 2 columns (Id, Data) and following data:

TableA:

Id Data
1 DataA11
1 DataA12
1 DataA13
2 DataA21
3 DataA31

TableB:
Id Data
1 DataB11
2 DataB21
2 DataB22
2 DataB23
4 DataB41

Inner Join on column Id will return columns from both the tables and only the matching records:
Id Data Id Data
1 DataA11  1 DataB11
1 DataA12  1 DataB11
1 DataA13  1 DataB11
2 DataA21  2 DataB21
2 DataA21  2 DataB22
2 DataA21  2 DataB23

Left Join (or Left outer join) on column Id will return columns from both the tables and matching records with records from left table (Null values from right table):
Id Data Id Data
1 DataA11  1 DataB11
1 DataA12  1 DataB11
1 DataA13  1 DataB11
2 DataA21  2 DataB21
2 DataA21  2 DataB22
2 DataA21  2 DataB23
3 DataA31

Right Join (or Right outer join) on column Id will return columns from both the tables and matching records with records from right table (Null values from left table):
Id Data Id Data
1 DataA11 1 DataB11
1 DataA12 1 DataB11
1 DataA13 1 DataB11
2 DataA21 2 DataB21
2 DataA21 2 DataB22
2 DataA21 2 DataB23
4 DataB41
    
Outer Join (or Full outer join) on column Id will return columns from both the tables and matching records with records from left table (Null values from right table) and records from right table (Null values from left table):
Id Data Id Data
1 DataA11 1 DataB11
1 DataA12 1 DataB11
1 DataA13 1 DataB11
2 DataA21 2 DataB21
2 DataA21 2 DataB22
2 DataA21 2 DataB23
3 DataA31
4 DataB41
    
Left Semi Join on column Id will return columns only from left table and matching records only from left table:
Id Data
1 DataA11
1 DataA12
1 DataA13
2 DataA21

Please refer the below link for other types of semi joins.
https://blog.jooq.org/2015/10/13/semi-join-and-anti-join-should-have-its-own-syntax-in-sql/
https://blogs.msdn.microsoft.com/craigfr/2006/07/19/introduction-to-joins/
https://technet.microsoft.com/en-us/library/ms191255(v=sql.105).aspx