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

MSSQL Server comparison with other databases

The following link gives the white paper about comparison of MSSQL server with other commercial databases:

https://assets.microsoft.com/pt-pt/SQL_Server_Mission_Critical_Performance_TDM_White_Paper.pdf

Checking data page corruption in the database

The following command can be used to display corruption related information and repair (with additional option) the corruption.


DBCC CHECKDB([database_name]);

Checking deadlock in the ERRRLOG after enabling the trace

First, to check if a specific trace is enabled on SQLServer, the following command can be used. If this command does not return any result, then there were no traces enabled by DBA.

DBCC TRACESTATUS();

To turn on the trace for detecting the deadlock in the ERRORLOG, the traces (1204, 1222) can be enabled using the commands:

DBCC TRACEON (1204, -1);

DBCC TRACEON (1222, -1);

Then, using the SQLServerManagement Console, you can keep on checking the ERRORLOG, to find if there are any deadlock occurred on not.

If the ERRORLOG is too big, the search process will choke the performance of the database server. To avoid that. The ERRORLOGs can be copied to another server and the following command can be used to find the lines that displays deadlock related information.

findstr “deadlock”  ERRORLOG

Here, deadlock is the search string and the ERRORLOG is filename.

Once you find the deadlock related information, the traces can be turned off using the following commands:

DBCC TRACEOFF (1204, -1);

DBCC TRACEOFF (1222, -1);

How to check which process killed the application process?

In the database server, we need to search for the string Kill in the SQLServer’s Error Log.

In SQLServerManagementWizard, Select the menus Management->SQL Server Logs-> <ERRORLOG> -> Search

Enter the string “Kill”, then click Search. 

If the search affects the server's performance or takes long time (It happens if the Error-Log size is too big), the Error-Log file can be copied to another machine, and then the following command can be used to find the lines that have the string Kill.

Example:

 findstr "Kill" ERRORLOG

(NOTE: K is upper case in Kill.   ERRORLOG is filename)

This usually displays the Process ID of the killed process, and the Host process ID of the killing process.  Using the host_process_id, the login_name that killed the process can found from the the table sys.dm_exec_sessions.

select login_name from sys.dm_exec_sessions where host_process_id = 'host process id got from the log';