dbstudents-blog

[dbstudents-blog]

Basics Of Update Stats In SQL Server

Basics Of Update Stats In SQL Server

Friends today I would like to discuss something about update stats. In most of our day to day working we always hear this work specially when you we get any issue related to performance.

So the question aries that what is UPDATE STATS and why it’s required and even before that it comes in mind that what is stats and how it’s helpful to handle something related to performance. Based on my…

[dbstudents-blog]

Finding Temp Tables In TempDB along with spaced used by specific tables

Finding Temp Tables In TempDB along with spaced used by specific tables

/*Understanding what user defined objects in tempdb and space used*/ USE TempDB GO /*Creating New TempTable*/ CREATE TABLE #TempRecords ( RecordId INT IDENTITY(1,1) ,ItemName NVARCHAR(200) ,ItemQuantity NVARCHAR(200) ) GO /*Inserting Records Into It.*/ CREATE TABLE #Products ( ProductId INT ,ProductName NVARCHAR(200) ) GO INSERT INTO #Products (ProductId, ProductName) VALUES (‘1′,’P001’) ,(‘2′,’P0…

[dbstudents-blog]

TempDB Contention: Trace Flag 1117 & 1118

TempDB Contention: Trace Flag 1117 & 1118

USE MASTER GO /*TempDB : Importance Of Trace Flag -1117 & -1118 In TempDB*/ Continuing on my last post regarding tempdb I would like to extend it a bit more on contention in tempdb. Today I would like to discuss on two of the important trace flag i.e. -1117 and -1118.

Before starting discussion on the traceflag I would like to discuss two basic concepts in SQL and I know most of you are aware of…

[dbstudents-blog]

TempDB

Details of tempdb.

a.  TempDB is a system database. b.  All the temporary objects such as local and global temporary table and indexes created on the temp tables, temporary stored procedures, table variable, result of table valued functions or cursors. c. It also holds the work table for intermediate results for spools, cursors , sort etc. d. It also holds the intermediate sort result…

[dbstudents-blog]

Covering Index

We all might have come across lookups operators while examining our execution plans. The common lookup types are KeyLookup and RID Lookup. So before starting with covering index I would like to discuss a little bit on what Lookups are? (In my next writeup I will explain Lookups in details with simualtion code). As of now in simple terms lookups are a way to look data either in clustered index or…

[dbstudents-blog]

Filtered Index

/* Filtered Index is a type of Non-Clustered index. It uses a filter, in terms of Where clause, to create a higly selective set of keys against a column or columns not having good selectivity. For example columns with high number of NULL columns etc. Several other kind of scenario can be taken and checked to get benefit of filtered index. */ –Code to create a filtered index: CREATE TABLE SalesOrder

[dbstudents-blog]

Merge Join

The merge join requires both the join inputs in sorted order on merge columns. If indexes are available on both joining columns, then the join inputs are sorted by the index. As all the input is sorted, the merge join gets a row from each input and compare it for equality. A matching row is will be displayed records are equal and it will be processed till all rows are processed. It is one of the…

[dbstudents-blog]

Nested Loop

The nested loop join is a joining mechanism in which one join input as the outer input table and another one is the inner input table. The outer loop consumes the outer input table row by row. The inner loop, executed for each outer row, searches for matching rows in the inner table. In a more simpler way we can say that the all rows of outer table get evaluated against every single row in inner…

[dbstudents-blog]

Hash Match (Join) In SQL Server

Hash Match (Join) In SQL Server

Hash Match (Join) In SQL Server: You all might have seen a number of times an operator called HASH MATHC (Join) while looking at the execution plan of a query.This is sometime become once of the expensive operation and can impact the query performance. Before understanding more on it,I would like to discuss a bit on Hashing and Hash Table.

Hashing: In simple terms hashing is a programming…

[dbstudents-blog]

Clustered Index In SQL Server

Clustered Index In SQL Server

USE TestSQL2016 GO SELECT * FROM SYS.TABLES

/* Clustered Index keeps the data in phycally sorted order. It reorders the data in the way the data is stored in the table. Hence there can be only 1 clustered index in the table. In clustered index the data pages are stored in the leaf node. Only when a table is having the clustered index the data rows will be sorted in order. If there is no clustered…

[dbstudents-blog]

Computed Column In SQL

Computed Column In SQL

/* Computed column is one of a very good feature added in SQL Server. This feature can be used if it is required to combine the values of one or more column. We can take an example. Suppose we have to display full name of a student in the application. Previously we need to implement this using the select statement. Now this can be achieved using the computed columns.

Properties of computed…

[dbstudents-blog]

Determine the Installation Date Of SQL Server

Determine the Installation Date Of SQL Server

Friends a lots of time I have gone through the question that when the SQL Server get installed. I also searched for the information on web and thought of sharing with all of you. Hope this will help you to get the installation information of SQL Instance.

SELECT create_date FROM sys.server_principals WHERE sid = 0x010100000000000512000000 GO

[dbstudents-blog]

Understanding Of Primry Key

Understanding Of Primry Key

Today I am going to write something on a very basic topic that eveyday a developer or an application DBA faces on his day to day work. Primay keys is one of the major building block of any database table which has the ability to uniquely indentify the record. The primary key can be of one column or can have multiple columns. This is totally based on the requirement of the system that how a system…

[dbstudents-blog]

SQL 2016: Data Compression While Data Insert

SQL 2016: Data Compression While Data Insert

In SQL 2016 there is a very interesting feature provided for data compression while inserting any data in the table. The input values get compressed using the GZIP algorithm. The result of the compression is byte array of type varbinary(max).

/*Value Compression*/ CREATE TABLE ProductDetail ( ProductId INT ,ProductName NVARCHAR(200) ,ProductInfo NVARCHAR(300) ) GO

INSERT INTO…

[dbstudents-blog]

SQL 2016 Live Query Store

SQL 2016 Live Query Store

From performance point of view, the Query Store is one of the most interesting feature provided in SQL Server 2016.It provides the insight on query plan choice and performance. The performance troubleshooting becomes very easy as the change in query plan is captured and we can easily get the query plan change details. Query store captures the history of queries , plans and runtime statistics and…

[dbstudents-blog]

SQL 2016 Live Query Statistics

SQL 2016 Live Query Statistics

The SSMS (SQL Server Management Studio) gives the ability to view the live execution plan of an active query. The live query plan provides some of the real time insight into the query execution process as the control flow from one query plan operator to another. The live query plan displays the overall query progress and operator level run time execution statistics such as the number of rows…

[dbstudents-blog]

Moving Table From One Filegroup to different filegroup in SQL Server

Moving Table From One Filegroup to different filegroup in SQL Server

/*Ref# Example 1

To move table from one filegroup to another filegroup,First we need to check if the table has a clustered index or not.If the table has clustered index (Unique or NonUnique) in that case we will recreate the index on with DROP_EXISTING = ON option on the new file group.

Ref# Example 2

If the table is a heap in that case we will create non unique clustered index on that table…