Memory usage checking of MSSQSL server

Check the total host memory and the used memory using "Task Manager" -> "Performance" (tab)

To check how much memrory is used by MSSQL server instance, the following SQL can be used:
SELECT (physical_memory_in_use_kb/1024) AS Used_Memory_By_SqlServer_MB FROM sys.dm_os_process_memory;

To check the "Max Server Memory" and "Minimum Server Memory":
   In the MSSQL Server Managemenr Studio -> select the instance (Top Parent Node of all) -> right click -> Properties -> Memory.
 

Improving performance by making use of system catalog

It happened to use LIKE clauses in order to find out the the VIEW's dependency on the base tables.

 information_schema.views.view_definition LIKE    %sys.tables.name%

This caused a huge performance bottle-neck since there is a string search on source code of the views.

Without checking the source code, the result of the above clause can be achieved by using the following system catalog view: VIEW_TABLE_USAGE

This has following columns
exec sp_columns VIEW_TABLE_USAGE 

VIEW_CATALOG
VIEW_SCHEMA
VIEW_NAME
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME

Unique ID generation

In SQLServer, the the function newid() can be used for Unique ID generation as given below:

select newid(), a.* from tab1 a;

MINUS clause in SQLServer

DBAs who comes with Oracle background would look for MINUS clause in order to compare the results of the SQL statements that return same type of result sets.

In Oracle, we use

select * from tab1
minus 
select * from tab2;

But, in SQLserver, we have to use EXCEPT clause

select * from tab1
except
select * from tab2;

Helpful Bogs on MSSQL server

I found following blogs more helpful on troubleshooting SQLServer issue:


http://sqlblog.com/blogs/adam_machanic/

https://www.youtube.com/watch?v=3c_VGh8FtZc

MSSQL Server error logs Location

MSSQL Server error logs can be found in the following kind of folder structure:

D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log

By default, it keeps six ERRORLOGS, older than that are getting deleted.
Everytime, when MSSQL Server instance is restarted, a new ERRORLOG is created. Old ones are archived.

Troubleshooting who killed your session and reading the error log

The following link gives the steps to find the login name that killed your application session:
https://www.toadworld.com/platforms/sql-server/b/weblog/archive/2012/05/03/who-killed-my-sql-server-process


Though reading an ERRORLOG from MSSQL is faster using a TextEditor, the following URL gives the steps to grab the specific errors in MSSQL server instance itself:
http://sqlserverlearner.com/2011/read-sql-server-error-log-using-tsql-query