How to find when the database was taken offline????

In this small blog post, I'm going to tell you the way in which you can can find the exact time when your database was taken offline.

As per my experience you can see the the database offline log from :

  1: Event Viewer
  2: Error Log File

If your default trace is enabled then you are lucky as you can find enough information related to same.

Now, I have a sample database "TestVK" which I'm going to set OFFLINE.

Use master
Go
Alter database TestVK set OFFLINE
Go

You can see in object explorer it is offline now:

Using Event Viewer:

Go to RUN > Event viewer (Press Enter Key)

In event viewer window Go to windows Log > Applications

Here you'll see an event from MSSQLSERVER with a description such as "Setting database option OFFLINE to ON for database TestVK". See the Below screen shot for same.

Using Error Log File:
You can open error log file following the path like C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log (it may be different in you case)

OR

You can run system store procedure SP_readerrorlog to read the error log file in SSMS. For OFFLINE databases You will see the log as below:

exec sp_readerrorlog  or sp_readerrorlog (both will work)

Thanks For Reading this Blog!!!!!

I have Non- Clustered Index on My Table then Why My Query is Running Slow??

Yesterday, I was working on a database that was having some performance issue. I asked to one of team member that what type of problem they are facing in their database. He told me that some time query takes a lot of time to give result. Although we have already created indexes but still it takes much time, looks like there is something else which is degrading the performance of these queries. I asked him to expand a table and then index related to same in SSMS. He expanded a table and said see We have non-clustered index for this table on ID field. But when we try to run any query related to this table, it takes so much time. He ran a query and it was taking time. 

I told him that it’s not what you are thinking about Non-clustered Index. I explained him about Non-clustered index using execution plans that how non-clustered index work. And that’s where I got a topic for my blog post:

Although there are so many differences between clustered index and Non-Clustered Index but the point on which I’m going to focus is:

For a clustered index, the leaf page of the index is the same as the data page of the table. Therefore, when reading the values of the clustered index key columns, the database engine can also read the values of other columns without any navigation from the index row.  

But in case of Non-clustered index, it has only information about the column on which it is created. So, if a query refers a column which is not part of non-clustered index then it has to navigate from a non-clustered index row to the corresponding data row in the clustered index or the heap. This process is known as Bookmark Lookup or RID lookups.

So this is all about lookups which makes query slower in case of non-clustered index. If we have smaller number of rows in result then it will not having much impact but non-clustered index becomes ineffective as the number of rows retrieved increases.

Let’s create the environment then we will examine the same:

Use master

Go

Create database NCI

GO

use NCI

GO

CREATE TABLE TEST1 (ID INT IDENTITY ,NAME CHAR(40), AGE INT)

GO

INSERT INTO TEST1 VALUES ('VIMAL',34)

GO 199000

CREATE NONCLUSTERED INDEX ID ON TEST1 (ID)

Now let’s execute the below query then we will see the number of logical read and execution plan for same: Include the actual execution plan by pressing ctrl+M

SET STATISTICS IO ON -- Will display the number of logical and physical reads

SELECT * FROM TEST1 WHERE ID=5

 Number of logical reads: 6

Execution Plan:

You can see the output columns of these lookups. Just put your mouse cursor over this lookup as below:

So it is clear from the screenshot that the output column from this lookup is Name and Age (From total of 3 columns). As we have NON-CLUSTERED INDEX on column ID so it has the information only about ID. For other column it has to navigate to heap/cluster. So the 50% of total cost is used in just getting the other columns except ID. i.e. Name and Age. This is just an example as there is 50-50 % cost. But in production you can see the situation where all the cost is taken by these lookups.

Let’s create a cluster index on this table then we will see that this lookup gets changed or not.

CREATE  CLUSTERED INDEX Index_Age ON TEST1 (Age)

Now let’s execute the same query again:

SET STATISTICS IO ON -- Will display the number of logical and physical reads

SELECT * FROM TEST1 WHERE ID=5

Let’s see the execution plan:

From above screen shot we can see that RID lookup has been changed to Key Lookup. So, it is clear now that if we do not have any cluster index on table then it will do RID lookup.

Now run the below query. Please note that now we are only selecting the ID column

SET STATISTICS IO ON -- Will display the number of logical and physical reads

SELECT ID FROM TEST1 WHERE ID=5

Number of Logical reads: 3

Execution Plan:

Index seek with no look-up operator. Here we need only ID and this NON-CLUSTERED INDEX has information about this. So this does not required any look-up.

Resolving Lookups: 

There are different ways in which you can resolve these lookups which are as follows:

Using a Clustered Index:  You can read about this on MSDN. But just want to mention here that there may be situation where we have already clustered index and we cannot change as it’s very critical.

Using a Covering Index 

Using a Covering Index Using Include

Using an Index Join

You can go through above methods to resolve lookups. I will post about same in future post. For now in our example we will use INCLUDE columns in our NON-CLUSTERED INDEX and then will see the plan for same:

DROP INDEX [Index_id] ON [dbo].[TEST1] WITH ( ONLINE = OFF )

GO

USE [NCI]

GO

CREATE NONCLUSTERED INDEX [Index_id] ON [dbo].[TEST1]

(

      [ID] ASC

)

INCLUDE ( [NAME],

[AGE])

GO

Now lets execute the first query and see number of logical read and the query plan :

SET STATISTICS IO ON -- Will display the number of logical and physical reads

SELECT * FROM TEST1 WHERE ID=5

Number of logical read is reduced to 3 from 6. It was a test environment, in production these number will be so different.

Execution Plan:

 So, this was the answer to my friend who was asking "why my query is running slow although I’m having Non-Clustered index on that table". This all was because of lookups.  The lookup step associated with a non-clustered index can make data retrieval through a non-clustered index very costly. The SQL Server optimizer takes this into account when generating an execution plan, and if it finds the overhead cost of using a non-clustered index to be very high, it discards the index and performs a table scan (or a clustered index scan if the table is stored as a clustered index).

Therefore, to improve the effectiveness of a non-clustered index, it makes sense to analyze the cause of a lookup and consider whether you can avoid it completely by adding fields to the index key or to the INCLUDE column (or index join) and creating a covering index.

                             Thanks For Reading This Blog Post!!!

Use Of REPLICATE() Function In Sql Server..

This Function Repeats a string value a specified number of times. You can use this function In SQL Server 2008 and later versions.

Syntax:
REPLICATE ( string_expression ,integer_expression ) 

string_expression
Is an expression of a character string or binary data type. string_expression can be either character or binary data.
integer_expression
Is an expression of any integer type, including bigint. If integer_expression is negative, NULL is returned.

Excute below select statement and see the output:

SELECT REPLICATE( 'Vimal Kumar Prajapati' , 3 ) As Name

Thanks For Reading This Post!!!

References: Microsoft BOL

How to Find and Fix Orphaned Users in SQL Server???

What is orphan user??
================
A database user can become orphaned if the corresponding SQL Server login is dropped. Also, a database user can become orphaned after a database is restored or attached to a different instance of SQL Server. Orphaning can happen if the database user is mapped to a SID that is not present in the new server instance.

what is a SID?
============ 
SID is short for “security identifier” A SID is an internal id which gets assigned to a server login when the login is created. The SID can be viewed by querying the Sys.server_principals system view or you can also view it by looking at sys.syslogins.

SELECT principal_id, sid, name FROM sys.server_principals;

SELECT sid, name FROM sys.syslogins;

How to Find SQL Server orphaned users??
=================================== 
There may be a number of orphaned users. So get the list of orphaned  user in your database you can execute below SP: 

USE DatabaseName --Replace DatabaseName  with actual database name
Go
EXEC sp_change_users_login 'Report';

Example:

USE TestVK
Go
EXEC sp_change_users_login 'Report';

How to Fix SQL Server orphaned users??
================================== 
If you already created a login which you want to map your database user to, you could run the below script: 

USE <database_name>;
GO
sp_change_users_login @Action='update_one', @UserNamePattern='<database_user>', 
   @LoginName='<login_name>';
GO

We have the database TestVK in which we got the orphaned user. Now suppose we have already a login "'LoginUser1" which we want to map. The we can execute the below code:

USE TestVK;
GO
EXEC sp_change_users_login 'update_one', 'User1', 'LoginUser1';

If we don’t have any login to map , in that case we can use sp_change_users_login to create one with a password. The following code does this and creates a login with the same name and a password of ‘Abc@321′ as an example.

EXEC sp_change_users_login 'Auto_Fix', 'User1', NULL, 'Abc@321#';

Thanks For Reading this Blog!!!

Partial Database Restore In SQL Server..

In previous blog, I have explained how to take partial database backup in sql server. If you want to visit that page then please click on below link:

How to take Partial Backup Of Database in SQL Server?

Now, We'll be using previous backup files to show How to restore  those backups. First I will be Restoring a Full Partial Backup, Then Partial backup which is the base of Differential Partial Backup.

USE [MASTER]
GO
RESTORE DATABASE [PARTIALBACKUP]
FROM DISK = N'C:\DATA\PARTIALBACKUP_FULL.BAK'
WITH NORECOVERY
GO

Now we will restore actual partial backups which will include Read-Write File Groups.

Use master
GO
RESTORE DATABASE [PartialBackup]
FROM DISK =N'C:\Data\PartialBackup_PARTIAL_KA_Diff.bak'
WITH NORECOVERY
GO

OOppssss!!! Error. As we supplied the wrong backup file. This backup file was of differential partial backup that is why we got this error. Correct the backup file path and execute it. It will be restored successfully.

use master
Go
RESTORE DATABASE [PartialBackup]
FROM DISK =
N'C:\Data\PartialBackup_PARTIAL_KA_Full.bak'
WITH NORECOVERY
GO

Restoring a Differential Partial Backup:
Now we will see how to restore a Differential Partial Backup. We will use below script for same:
Use master
GO
RESTORE DATABASE [PartialBackup]
FROM DISK =N'C:\Data\PartialBackup_PARTIAL_KA_Diff.bak'
WITH RECOVERY
GO

So, This is how you can partially restore your database.

Thanks For Reading This Blog!!!