Category : SQL Server


Dynamic management views and functions in SQL Server 1

In this post named “Dynamic management objects in SQL Server”, we will explore the DMVs and DMFs in SQL Server. Dynamic management views (DMVs) and dynamic management functions (DMFs) are used to extract current internal information of the Server State using T-SQL commands. Formerly such types of data were only available in Performance Monitor (a Microsoft tool to analyze System performance), now SQL Server has exposed such information with some other important information related to current server state through dynamic management views and dynamic management functions. Both dynamic management views and dynamic management functions are collectively known as Dynamic Management Objects (DMOs). These objects were introduced primarily in SQL Server 2005 which allow us to monitor the current internal status of SQL Server using simple T-SQL.

DMVs and DMFs are very useful in collecting current performance related data of the system. We can categorize these objects in two groups as … More


Open SQL Server Management Studio as different windows user

Have you ever required to open SQL Server Management Studio Application (SSMS instance) as different windows user from your local machine? Sometimes we require to “Run SSMS instance as different Windows user” another machine which is not available physically to us. In such type of scenarios we can open our local SSMS instance as different windows user at our local machine. In this article we are going to learn how we can open SSMS instance as different windows user. If you are still not required this till now, may be sometime in future you will be required to do the same.

Now to accomplish this task i.e. to open SSMS as a different windows user, we have below approaches;

  1. Using Run as different user option in windows explorer
  2. Using runas command in command prompt
  3. Using runas command with “.bat” file

We will cover all the above techniques below.

1. Using

More

Deterministic and non-deterministic functions in SQL Server 6

In this topic “Deterministic and non-deterministic functions”, we are going to discuss the deterministic and non-deterministic functions in sql server. Lets start with “what is a deterministic function in sql server?”. It’s a common interview question also. I will elaborate the definition of deterministic as well as non-deterministic functions and also provide some good examples. So after reading this article you will be able to distinguish deterministic and non-deterministic functions.

Deterministic Functions

The functions which always return same output when they called with a particular set of input values in the same state of the database are known as deterministic functions.

SELECT ISNULL('ABCDEF', '1')

Above t-sql code will always return the output as ‘ABCDEF’ and thus ISNULL is a deterministic function. Below is the list of some other deterministic functions.

ISNULL, ISNUMERIC, DATEDIFF, POWER, CEILING, FLOOR, DATEADD, DAY, MONTH, YEAR, SQUARE, SQRT etc.

Non-deterministic Functions

The functions which may return … More


Finding dependencies of a table in sql server

Many a times we face the problem of finding the foreign key relationships, triggers and stored procedures that are referring to a particular table. The below query will help you in identifying the following things:

  1. Find the entire hierarchy of all the parent tables and the corresponding parent table column names and their respective child(s).
  2. Find the entire hierarchy of all the child tables and the corresponding child table column names and their respective parent(s).
  3. The name of the triggers the table is referring to.
  4. The name of the stored proc which uses the table.

Let me elaborate this with the help of an example to have better understanding.

Let’s say I have created 3 tables as follows:

CREATE TABLE parent
(
parent_col1 int primary key,
parent_col2 int
)

GO
CREATE TABLE Child1
(
Child1_col1 int primary key,
Child1_col2 int Foreign key references parent(parent_col1)
)
GO

CREATE TABLE Child2
(
More

Tracing deadlock with Extended events

In last two articles, we have learned how to capture deadlock using SQL Server Profiler and Tracing deadlock with Trace Flag 1222 and Trace Flag 1204. Now in this post we are going to learn a very useful method of tracing deadlock with Extended Events. These events are less resource intensive and light weight in comparison of SQL Server Profiler. We use these events to capture interested information to be analyzed now or later from heavy load servers (Like production server).

If we don’t have any clue about deadlock being occurred in midnight at our production server and because of heavy load on server, we can not use SQL Server Profiler their. Extended events are very useful because of very few resource utilization. We can create a session and start it to capture only those events in which we are interested. Extended events are very useful in such types … More


Tracing deadlock with Trace Flag 1222 and Trace Flag 1204

In previous article, we have already learned that how we can trace a deadlock situation with SQL Server Profiler. In case you don’t read, click here. And to understand locking, blocking and deadlocking in sql server, click here. In this post we will learn how we can use “trace flag 1204 and trace flag 1222” to trace a deadlock.

In this article we are going to learn how we can trace SQL Server deadlock and extract the deadlock information from captured data. We will use DBCC TRACEON command, Trace Flag 1204 and Trace Flag 1222 to capture the details.

What is DBCC (Database Console Commands)?

DBCC statements act as Database Console Commands for SQL Server. We can run it using TSQL programming language. It provides commands related to various categories like Maintenance, Miscellaneous,  Informational and Validation.

Deadlock

As we know that Deadlock in SQL Server is a situation … More


Tracing deadlock with sql profiler 2

Hi folks, In this article “Trace deadlock sql profiler”, we are going to learn, how we can capture a deadlock in a graphical form in sql server using an inbuilt tool of sql server named “SQL Server Profiler”.

First of all note that, don’t try to reproduce this sample deadlock and start SQL Server Profiler trace on your production server unless you are well versed of Profiler and event tracing. I recommend to do all these stuffs on you local machine to learn capturing deadlocks using SQL Server Profiler.

We are going to cover below points in this article;

  1. What is a deadlock?
  2. How to create a demo purpose deadlock?
  3. Using SQL Server Profiler to capture deadlock details and graphs.
  4. Understanding the deadlock important events in sql server profiler.

What is a deadlock?

A short note on deadlock:

Deadlock occurs when two sessions are waiting for a lock to clear … More


Get total number of rows for all tables

How to get total number of rows for all the tables in a particular database? In this post we are going to learn how we can get “total number of rows for all tables” in a database.

We will use the inbuilt system views of SQL Server to generate this list. Sometimes we need to pick some high record containing tables for some demo or any other purpose.
To get the desired output, first select the database for which you want to extract the list of tables with total number of rows as below;

USE AdventureWorks2012
GO

 

Now use the below query and have a look on its output;

SELECT SCHEMA_NAME(ST.schema_id) + '.' + ST.Name AS TableName, MAX(SP.rows) AS TotalRows
FROM SYS.Tables ST
INNER JOIN SYS.partitions SP on SP.object_id = ST.object_id
GROUP BY SCHEMA_NAME(ST.schema_id) + '.' + ST.Name
ORDER BY TotalRows DESC

OR

SELECT SCHEMA_NAME(ST.schema_id) + '.' + ST.Name 
More

SET XACT_ABORT in SQL Server 2

What is SET XACT_ABORT in SQL Server?

In this post “SET XACT_ABORT in SQL Server”, we will discuss about the XACT_ABORT command. SET XACT_ABORT controls the atomicity of any user defined transaction. By default SET XACT_ABORT is OFF. It specifies whether SQL Server automatically rollback and abort a transaction if any error occurred in any statement of the transaction. If SET XACT_ABORT is ON, if any run time error occur in any t-sql statement of the transaction, entire transaction is terminated and rolled back. If SET XACT_ABORT is OFF, only that statement which raised the error is rolled back and transaction continues the processing of other statements.

Note that if the severity of the error is high, entire transaction can be rolled back even if SET XACT_ABORT is OFF.

Scope of XACT_ABORT

Scope of XACT_ABORT is at connection level and remains until either reconfigured or connection is closed. Note that … More


Locking, Blocking and Deadlocking in sql server 1

Lock vs Block vs Deadlock

Have you ever asked to differentiate lock, block and deadlock in sql server during your interview session? In this article we are going to differentiate these frequently confused terms – Lock, Block and Deadlock. So lets have a look on “Lock vs Block vs Deadlock”

These terms sound the same but are different in their meanings. Have a look on below definition to understand it.

Lock

When any session needs access to a piece of data from Database, a lock of some type is placed on that data to maintain the isolation property of database. Locking is different from blocking.

Block

When a session needs to wait for a resource being locked by another session, this process is called as blocking. e.g. If session “A” is waiting for a resource “R” being used by session “B”, you can say that session “B” is blocking session … More