SQL SERVER – What is Hypothetical Indexes?

If you ever thought this is some sort of trick to bring you to the blog, well you are wrong. This infact is something worth a look and interesting thing to know. Before I start to explain you the finer details, let me tell you that this is NOT a new feature for performance improvement of the SQL Server Engine.

During profiling one of the databases, one of my DBA friends asked a question about why there is a difference in the indexes shown in SQL Server Management Studio vs. T-SQL query. Though it didn’t make sense to me, I asked for details and wanted him to get me the details. This leads to the learning which I thought was worth a share. Here is what I mean:

Trust me, there is absolutely no Photoshop trick in the above image. Sys.Indexes catalog view shows 7 entries for a table but object explorer shown only one index on the table which is the ONLY object inside the database.

Before we talk about the reason, you can play around with me by creating sample table using the below script.

USE MASTER
GO
IF DB_ID('HypotheticalIndex') IS NOT NULL
BEGIN
ALTER DATABASE
HypotheticalIndex SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE
HypotheticalIndex
END
CREATE DATABASE
HypotheticalIndex
GO
USE HypotheticalIndex
GO
CREATE TABLE [Alumni]
(
[SSN]         [INT] IDENTITY(1, 1) NOT NULL,
[StudentName] [CHAR](200) NULL,
[GradDate]    [DATETIME] NULL
)
GO
SET NOCOUNT ON
GO
INSERT INTO Alumni
(StudentName,
GradDate)
VALUES      (RAND() * 1000,
DATEADD(dd, RAND() * 100, GETDATE()))
GO 60000 -- should take around 50 seconds
SET NOCOUNT OFF
GO

Above script would create a database and populate rows. Let’s create an index on the table.

CREATE INDEX Idx_Alumni_GradDate
ON Alumni(GradDate)

To create hypothetical index on the same column, we can run the below command:

CREATE INDEX Alumni_hyp_1
ON Alumni(GradDate)
WITH STATISTICS_ONLY=1
GO

Here the keyword is undocumented extension WITH STATISTICS_ONLY which is available with CREATE INDEX command.
Let us next look at sys.indexes output:

SELECT name,
index_id,
type_desc,
data_space_id,
is_hypothetical
FROM   sys.indexes
WHERE  OBJECT_ID = OBJECT_ID('Alumni')
AND    
type_desc <> 'HEAP'

Notice the highlighted column values for hypothetical index. Data_space_id is zero because there is no physical storage for this index. It is not stored in any filegroup or file.

Let’s look at the statistics.

DBCC SHOW_STATISTICS (Alumni, Alumni_hyp_1) WITH STAT_HEADER
GO

We can see that rows are sampled and statistics object is generated for hypothetical index.

This means that the hypothetical index is an index that has the metadata (in sys.indexes with is_hypothetical = 1) and a statistics associated to it (in sys.stats), but does not have physical storage. This is used only for costing evaluation of query plan in conjunction with “DBCC AUTOPILOT” and “SET AUTOPILOT ON” commands. These settings are for a future discussion and blog, let us move along.

Since SQL Server Management Studio is filtering out the hypothetical indexes in Object Explorer – this is the reason my friend saw a difference in T-SQL and SSMS.

STATISTICS_ONLY option is undocumented but I was playing around with the option and found something interesting. Based on the value passed to this parameter the statistics, sampling would be changed.

CREATE INDEX Alumni_hyp_0
ON Alumni(GradDate)
WITH STATISTICS_ONLY=0
GO
DBCC SHOW_STATISTICS (Alumni, Alumni_hyp_0) WITH STAT_HEADER
GO

If we pass a value as zero the statistics isnot generated.

If I pass the highest value (2^31-1) = 2147483647 then sampling is done with Full Scan.

CREATE INDEX [Alumni_hyp_2147483647]
ON Alumni(GradDate)
WITH STATISTICS_ONLY=2147483647
GO
DBCC SHOW_STATISTICS (Alumni, 'Alumni_hyp_2147483647') WITH STAT_HEADER
GO

To find a hypothetical index in the database, we can run below query.

SELECT  *
FROM    sys.indexes
WHERE   is_hypothetical = 1

Generally the index name would be _dta_index because the DTA uses them behind the scene to evaluate the cost of an index by creating them hypothetically. If DTA exits gracefully than it does clean up these indexes. But if someone is losing patience while DTA is running and kills it using task manager, then those indexes would be left behind. It is safe to drop hypothetical indexes. They can be dropped using the normal DROP INDEX command. I must point out that if you apply recommendation provided by the DTA while tuning a query and don’t change the suggested name, they would have _dta_ in their name. The name doesn’t mean they are hypothetical – we need to use the is_hypothetical column to filter them.

How did I learn this? Someone asked how the Database Engine Tuning Advisor (a.k.a. DTA) works. How does it create and evaluate indexes on huge tables? So I captured profiler while running DTA and found many interesting facts and under the cover working of the tool. This blog is an idea after seeing profiler. Learning never stops if you are working with SQL Server!

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

SQL SERVER – FIX – Error: One or more files do not match the primary file of the database

Writing about SQL Server for close to 8 years, almost every single day means I get a lot of questions from people on a daily basis. Though I try to answer as many as possible (via email, blog, twitter and Facebook), I get many interesting questions. There are a variety of questions ranging from installation, scalability, performance, TSQL, new feature and a variety of error messages. Recently I got an email which had below the question:

Hi Pinal,

I was given task to move the files for few databases and also to rename them. While doing this activity I did some mistake and few of my databases are not coming online. Here is the error I am getting for those databases.

2014-10-20 17:52:39.08 spid37s     Error: 5173, Severity: 16, State: 1.

2014-10-20 17:52:39.08 spid37s     One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files.  If this is an existing database, the file may be corrupted and should be restored from a backup.

2014-10-20 17:52:39.08 spid37s     Log file ‘E:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\SQLAuth_log.ldf’ does not match the primary file.  It may be from a different database or the log may have been rebuilt previously.

Can you please help me how I can resolve this issue and what needs to be done to mitigate?

On further investigation of ERRORLOG it was found that we have “RECOVERY_PENDING” state for the database because recovery of database could not be complete. The cause of the error is all files which are getting used by database don’t belong to the same database. There is a safety mechanism in the database startup where it checks whether all files below to same database. This is so very important because we don’t want to override existing files inadvertently.

It is not possible to open and read the files to find out information about MDF files when it is attached. We can use DBCC CHECKPRIMARYFILE command to read the primary file header to know the information stored. Here is the quick demo of various parameters and the output. I have found this quite powerful and thought was worth a share.

First, let’s create the database with two data files and one log file.

CREATE DATABASE [SQLAuthority]
ON PRIMARY
(NAME = N'SQLAuthority_MDF', FILENAME = N'C:\Temp\SQLAuthority_MDF.mdf'),
(
NAME = N'SQLAuthority_NDF', FILENAME = N'C:\Temp\SQLAuthority_NDF.ndf')
LOG ON
(NAME = N'SQLAuthority_log', FILENAME = N'C:\Temp\SQLAuthority_log.ldf')
GO

Let us detach the database using the following command:

sp_detach_db 'SQLAuthority'

Now let’s use DBCC CHECKPRIMARYFILE to read file header and get details.

We would see “1” and output only for first file because that’s the primary data file. Let’s run the same command with other parameters and check the output.

As we can see that by using the command we can read the header of the file and get various properties of primary files.

  1. Option 1: File IDs, filegroup id, logical name, physical name for other files belonging to same database.
  2. Option 2: Which database this file belongs to, what was the database version and collation.
  3. Option 3 is subset of option 1

This doesn’t mean that we can use two different files of same database taken from different servers and attach them. Sometime this command is useful when you don’t know the logical file names of the files which you have got and want to use the file (when attach is not working due to corruption).

Hope this was an interesting option and you were able to learn something from it as I did when first using the same.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Take the Quiz for a chance to win a Quadcopter Drone

It has been a long time since we ran quiz. So let us get ready for a quiz. The quiz has two parts. You have to get both the parts correct to win Quadcopter with Camera (we will call it drone). We will be giving away a total of 2 Quadcopters.

The quiz is extremely easy and I will ship the Drone anywhere in the world where Amazon will ship it.

Let us jump directly to the quiz. Please complete both the parts the contest. 

Contest Part 1: Brain Teasers

Please execute following script and answer the questions.

-- What will be the output of the following? and Why?
SELECT 28E3
-- What will be the output of the following? and Why?
SELECT 28F3
-- What will be the output of the following? and Why?
SELECT F328

Contest Part 2: Download and Activate Rapid SQL

Question: Download and Activate Rapid SQL.

Hint: You have to download and activate Rapid SQL. If you do not activate Rapid SQL, you will be disqualified for the contest. Why take risk, let us start!

That’s it!

Just answer above questions in the following comments area, in following format.

Remember:

  • Download RapidSQL from this link.
  • Use comments area right below the blog to take participation in the contest
  • Answer before November 21, 2014 midnight GMT.
  • The winner will be announced on December 8.
  • The winner will be selected randomly from all the valid answers.
  • All the valid answers will be kept hidden till December 24, 2014.
  • There will be a total of two winners.
  • The contest is open for any country of the world where Amazon ships products.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – FIX: ERROR : Msg 3136, Level 16, State 1 – This differential backup cannot be restored because the database has not been restored to the correct earlier state

During my recent visit to customer site for a session on backups, they asked me to find the cause of the error while restoring a differential backup. Though this seemed to be completely an admin related topic and I had gone for some other session, I took the challenge head-on. These are wonderful ways to explore and learn SQL Server better. The error they showed me was:

Msg 3136, Level 16, State 1, Line 39
This differential backup cannot be restored because the database has not been restored to the correct earlier state.
Msg 3013, Level 16, State 1, Line 39
RESTORE DATABASE is terminating abnormally.

In this blog post I will try to explain about the error in detail. In the same context, long time back, I did write a blog post on: SQL SERVER – Backup Timeline and Understanding of Database Restore Process in Full Recovery Model

Over there, I have explained details and co-relation of the various backup type i.e. Full, Differential and Transaction Log backups. I will refrain from rehashing them here again.

Recently, one of my friends asked about if we have differential backup, how we can find the full backup on which differential backup can be restored. If we go back to basics, the differential backup has all the changes in the database made since last full backup was taken.

Let us understand this concept using an example:

CREATE DATABASE SQLAuthority
GO
USE SQLAuthority
GO
CREATE TABLE t1 (i INT)
GO
BACKUP DATABASE SQLAuthority TO DISK = 'E:\temp\F1.bak'
GO
INSERT INTO t1 VALUES (1)
GO
BACKUP DATABASE SQLAuthority TO DISK = 'E:\temp\D1.bak' WITH DIFFERENTIAL
GO
INSERT INTO t1 VALUES (2)
GO
BACKUP DATABASE SQLAuthority TO DISK = 'E:\temp\D2.bak' WITH DIFFERENTIAL
GO
INSERT INTO t1 VALUES (3)
GO
BACKUP DATABASE SQLAuthority TO DISK = 'E:\temp\F2.bak'
GO
INSERT INTO t1 VALUES (4)
GO
BACKUP DATABASE SQLAuthority TO DISK = 'E:\temp\D3.bak' WITH DIFFERENTIAL
GO

Once the script has been run we have below backups.

Looking at the backup chain, it is clear that D3 is valid for F2. On the other hand D1 and D2 are valid and restorable on top of F1. Let us drop the database and try to restore D3 on top of F1.

USE MASTER
GO
DROP DATABASE SQLAuthority
GO
RESTORE DATABASE SQLAuthority FROM DISK = 'E:\temp\F1.bak' WITH NORECOVERY
GO
RESTORE DATABASE SQLAuthority FROM DISK = 'E:\temp\D3.bak' WITH NORECOVERY
GO

Here is the output.

Processed 296 pages for database 'SQLAuthority', file 'SQLAuthority' on file 1.
Processed 6 pages for database 'SQLAuthority', file 'SQLAuthority_log' on file 1.
RESTORE DATABASE successfully processed 302 pages in 0.213 seconds (11.076 MB/sec).
Msg 3136, Level 16, State 1, Line 43
This differential backup cannot be restored because the database has not been restored to the correct earlier state.
Msg 3013, Level 16, State 1, Line 43
RESTORE DATABASE is terminating abnormally. 

This means that first restore was successful and next one has error which means that this is not a valid differential backup to be restored. How would we figure out the correct sequence of restore? Well, there are multiple ways.

1. Have a look at SQL Server ErrorLog where we have successful backup messages. Here is what we saw in ERRORLOG while running above backups.

As highlighted above, we can find the full back up LSN from the message of differential backup.

2. Have a look at Standard Reports to find previous backup events.

SQL SERVER – SSMS: Backup and Restore Events Report

3. Run below query on the server from where backup was taken.

SQL SERVER – Get Database Backup History for a Single Database

Hope fully this blog demystifies and tells you usefulness of the messages in ERRORLOG and logging capability of SQL Server. Do let me know if you have ever encountered these errors in your environments.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – FIX: ERROR : Msg 3023, Level 16, State 2 – Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized

Errors are the best way to learn how SQL Server works and as DBA’s we are bound to see many of them from time to time. One of the primary functions of a DBA would include creating backups and most importantly trying to automate the same using jobs and maintenance plans.

Here is a typical scenario which a DBAs can encounter. One fine day they notice that some backup jobs are failing for no reason. Normal troubleshooting always starts with an error message. Recently, one of my blog readers sent an email to me which was worth a look.

I am getting below error. What is the cause and solution?

Msg 3023, Level 16, State 2, Line 1
Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized. 
Reissue the statement after the current backup or file manipulation operation is completed.

I pinged him on twitter and asked more details. He informed that they have a job which runs and fails with the error described above. I asked him to get more details about the job and post back. I also asked him to check details from my good friend Balmukund’s blog – query to find what is running at the same time when job runs. He didn’t come back to me – that means his issue might be resolved.

But that left me curious to find the possible causes of the error Msg 3023, Level 16, State 2. Reading the message again, it looks like two parallel backups would cause error. So I ran two parallel backup command for a database which was little big in size (100GB). As soon as two full backups started, I could see that only one backup was making progress (session id 57) and another (session id 58) was waiting for first one to finish.

Which means the error is not raised and backup is waiting. But as soon as I cancelled the query (session 58), I got below message.

Another possible reason of the error is that if we perform shrink operation in parallel to backup operation. (Shrink is NOT something which I recommend, but people would never listen)

Here is the text

Msg 3140, Level 16, State 5, Line 1
Could not adjust the space allocation for file 'SQLAuthority'.
Msg 3023, Level 16, State 2, Line 1
Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized. 
Reissue the statement after the current backup or file manipulation operation is completed.

Depending on who came first, here is the behavior. If a backup is started when either add or remove file operation is in progress, the backup will wait for a timeout period, then fail. If a backup is running and one of these operations is attempted, the operation fails immediately.

Solution: Find out the conflicting operation and retry your operation after stopping or finishing conflicting operation.

Learning using error messages is a great way to understand what happens inside SQL Server. Do let me know in the recent past, what have you learnt from error messages in your environments.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Fix – Error 5058, Level 16, State 1 – Option cannot be set in database

Of late I have been writing about errors quite a bit because I seem to have been digging these from my email archives based on interaction with multiple DBA’s over the past 6-7 years. These are interesting conversations that have become blog posts for your reference. I feel these error messages give me an opportunity to understand SQL Server better.

One of these many interactions brought me to the mail from one of the DBA’s where he mentioned that one of his maintenance tasks were sending error of Msg 5058. At first look I was clueless to what this error is, so I turned myself to DMVs for some help. I executed the following command to start with:

SELECT *
FROM sys.messages
WHERE message_id = 5058 AND language_id = 1033

This returned me the error text of:

Option '%.*ls' cannot be set in database '%.*ls'.

Still not convinced why this can ever happen. I wanted to learn from this DBA to what the specific scenario was and why he was getting this error. So I emailed in anticipation to the response. After a couple of days, he did send me back the message and everything fell into place. The mail reads as:

Msg 5058, Level 16, State 1, Line 3
Option 'RECOVERY' cannot be set in database 'tempdb'.

Now, the error message made complete sense and it was super easy for me to give him an explanation to what needs to be done on his server. I quickly got onto a chat window to understand how the maintenance plans were made.

Pinal: Hi there

DBA: Hello Pinal

Pinal: I saw your mail and want to know how you set your maintenance plans and what is the process?

DBA: As per our company policy, we need to have our databases in the FULL recovery model.

Pinal: Ahha Now I know the problem. I think you have scripted everything using a looping logic?

DBA: Let me check, yes I can see.

Pinal: Just go ahead and please exclude the TempDB database from that list of databases list.

DBA: Ok. Will do so. But why?

Pinal: That will solve your problem and I will write about the why in my next blog post later this week.

DBA: Wow, thanks it helped.

Explanation / Reasons

As per the DBA and his company policies, he had gone ahead and made all DB’s as FULL recovery mode. But by design we cannot change the recovery model of TempDB. It will always be SIMPLE and cannot be changed. To mimic this behavior, here is the command that I can execute:

ALTER DATABASE [tempdb] SET RECOVERY FULL WITH NO_WAIT
GO

And we will get the error message as mentioned before.

Msg 5058, Level 16, State 1, Line 3
Option 'RECOVERY' cannot be set in database 'tempdb'.

Even if the recovery model is set to BULK_LOGGED or even SIMPLE, the error message is the same. There is no change in the error message. For demo purposes, I even tried using SIMPLE recovery model using:

ALTER DATABASE [tempdb] SET RECOVERY SIMPLE WITH NO_WAIT

The error message was consistent and same. We are not allowed to change the recovery settings of TempDB.

Here is another example of 5058 error.

ALTER DATABASE MASTER SET OFFLINE

As expected, we should get an error.  (How can we take master offline J)
Msg 5058, Level 16, State 5, Line 1

Option ‘OFFLINE’ cannot be set in database ‘master’.

Notice that the error number is same but the state is different here because we are changing different setting of database (not recovery model).

Have you ever encountered similar errors in your environment? Let me know, how you found the same?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Msg 1206, Level 18, State 118 – The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction

The saga of working with error messages continues. Here is an error that was shared at one of the User group meetings by a member. While working via linked server to do data manipulation they reported this error on their servers:

Msg 1206, Level 18, State 118, Line 9
The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction.

Initially I thought it was due to misconfigured DTC between source and destination, but digging more into this error revealed something really interesting and again I learned something new. Hence this blog post was born.

Whenever I get any DTC errors while working with linked server, the very first test I perform is by doing the dummy distributed transaction to see whether DTC between two servers is working or not.

BEGIN DISTRIBUTED TRANSACTION
SELECT
* FROM <linkedServer>.<DatabaseName>.dbo.TestTable
ROLLBACK TRANSACTION

If above test is failing, then make sure you follow the KB article 2027550 and make setting as shown below:

Below are some errors which I have seen in this regards in the past:

  • OLE DB provider “SQLNCLI11″ for linked server “linkedservername” returned message “No transaction is active.”
  • The operation could not be performed because OLE DB provider “SQLNCLI11″ for linked server “linkedservername” was unable to begin a distributed transaction.

In the error message for which I was consulted, the DBA/Developer confirmed the above test was successful. Moreover, the setting were are per above screenshot too. So there was something else causing the DTC error which made me curious. I asked what type of code raised this sort of error.

Here was the piece of code which was failing as per the Developer. I have done simplification of the code for your reference.

SET XACT_ABORT ON
GO
BEGIN DISTRIBUTED TRANSACTION
BEGIN
TRY
INSERT INTO SQLLinkedServer.SQLAuthority.dbo.TestTable VALUES (1,'MyFirstName')
END TRY
BEGIN CATCH
IF @@Trancount > 0
ROLLBACK TRAN
END
CATCH
GO
SET XACT_ABORT OFF

In above script, SQLLinkedServer is the name of linked server, SQLAuthority is the name of the database located on the linked server instance.

We captured profiler and found that the actual error was something else which was not shown due to the fact that we have used TRYCATCH block. If we remove the block, we can see exact error.

The actual error is masked due to the way TRY… CATCH works. As per this – This is more of a limitation with the ERROR functions since they can return only one error & hence the last one.

To reproduce the error, I have already inserted a record in table residing under database on linked server. That’s why we are seeing primary key violation error.

Though this is an interesting scenario to investigate, I generally try to understand why we are using Linked Server and if there are ways to mitigate the same. But that will be a different discussion for some other blog post. If you ever encountered this error, do let me know what ways in which you found a solution.

Reference: Pinal Dave (http://blog.sqlauthority.com)