SQL SERVER – Wrap on Series of Temporary Statistics

This is the final post in the series of the Temporary Statistics. I have previously written about this subject in a few posts. I have received quite a few questions about this series so I decided to include all the questions in this one blog post. Before you read this blog post, here are a few blog posts related to this subject.

Here are a few questions which I have received.

Question: Can I update the temporary statistics?
Answer: No, the user cannot create or update temporary statistics. SQL Server only can create or update temporary statistics. However, the user can drop the statistics with the help of DROP STATISTICS command.

Question: Is there any way the user can retain created temporary statistics permanently on SQL Server.
Answer: You can script out the temporary statistics and create them in the database with the steps described in the blog Scripting Statistics for Read-Only Database and Making it Permanent

Question: If database is read-only where are the temporary statistics are stored?
Answer: In the TempDB. Hence when you restart your SQL Server services, all the temporary statistics disappear.

Question: How can I know that if the statistics created is temporary or permanent?
Answer: You can run the SELECT query on sys.stats catalogue view and checkout the status of the index. If the is_temporary column is 1 for statistics it is temporary and if it is 0 it is permanent. Read more about that over here:  Statistics for Read-Only Database are in TempDB.  

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

About these ads

SQL SERVER – Scripting Statistics for Read-Only Database and Making it Permanent

This is a follow up blog post to my earlier blog posts about statistics for Read-Only Database. Please read the following blog post in this series to get further idea about this subject:

One question which keeps on coming to my Inbox which was based on Read-Only Database and its best practices. Everybody wanted to know that they do understand that they have a read-only database and its statistics is in the TempDB. When TempDB is restarted it resets the statistics. If the user knows that next time they are going to execute the same kind of queries on the Read-Only database and it will once again create the similar statistics in TempDB, is there any way to create those statistics in the Read-Only database so next time when the server restarts (or service restarts) the statistics remains in the database instead of disappearing with TempDB?

In simple words the question is – How to move statistics from temporary status  to permanent status in the Read-Only database?

This is very interesting question and indeed a very easy one. First of all users should only move the statistics from the TempDB database to Read-Only database if he is confident that the same statistics are created multiple times and there is absolutely no change in the queries which are running against the Read-Only database. In my career I have seen Read-Only database which are refreshed daily, weekly or at intervals. If you have such database in that case, you should not think of moving the statistics from the TempDB till the database is in a consistent stage where data is not moving much.

Now if you have decided to move your database from TempDB to Read-Only database, here is a big problem – we can’t create statistics in Read-Only Database. A database must be in ReadWrite mode to create statistics. Hence, users will have to change the status of the database from Read-Only to ReadWrite and move the statistics from TempDB. Once the statistics are moved from TempDB, you can once again change the status of the database from ReadWrite to Read-Only. When statistics are generated based from your Read-Only database, it will additionally append suffix _scripted at the end of the statistic’s name. For example if your original statistic’s name is  MyStatisticsName_readonly_database_statistic, it will be automatically converted to MyStatisticsName_readonly_database_statistic_scripted.

Here is a summary of how we can do the same.

Step 1: Change Database status to Read-Write

USE [master]
GO
ALTER DATABASE [dbname] SET  READ_WRITE WITH NO_WAIT
GO

Step 2: Follow the blog post to create statistics from Read-Only Database How to Script Statistics from SSMS? – Scripting Statistics is Disabled in SSMS

Step 3: Execute the script generated from Step 2

Step 4: Change Database status to Read-Only

USE [master]
GO
ALTER DATABASE [dbname] SET  READ_ONLY WITH NO_WAIT
GO

Let me know what you think of this blog post series.

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

SQL SERVER – Location of Statistics Before and After Database is Marked as a Read-Only

Earlier last week I wrote a blog post about Statistics for Read-Only Database are in TempDB, I received a very interesting comment from Ayman El-Ghazali on the same blog post. Let me reproduce the same comment over here.

“Nice post. What if you create a database, create some statistics in there, then later decide to switch it to Read-Only? How are those statistics updated if they exist inside the Read-Only DB? From your post I understood that all new statistics created will be in TempDB but what about the older stuff that resides in the DB that was switched to Read-Only?” (Original Comment)

In simple words what will happen to the statistics which were created before the database changed status to Read-Only.

Here is the answer – the statistics which were created before the database was marked as a read-only will still stay in the same database and it will be permanent statistics. However, all the new statistics which we will be creating after the database is marked as a read-only are created in the TempDB.

Let us validate the same with the help of demonstration:

In following script below we are creating the statistics before and after the database is marked as a read-only. We will check the status of the statistics location before and after the database is marked as a Read-Only.

-- Create Database
CREATE DATABASE [Read-Only]
GO
-- Use Database
USE [Read-Only]
GO
-- Create Table
CREATE TABLE [Shift](
[ShiftID] [tinyint] NOT NULL,
[Name] VARCHAR(100) NOT NULL,
[StartTime] [time](7) NOT NULL,
[EndTime] [time](7) NOT NULL,
[ModifiedDate] [datetime] NOT NULL
)
GO
-- Insert Table
INSERT [Shift] ([ShiftID], [Name], [StartTime], [EndTime], [ModifiedDate])
VALUES (1, N'Day', GETDATE(), GETDATE(), GETDATE())
GO
INSERT [Shift] ([ShiftID], [Name], [StartTime], [EndTime], [ModifiedDate])
VALUES (2, N'Evening', GETDATE(), GETDATE(), GETDATE())
GO
INSERT [Shift] ([ShiftID], [Name], [StartTime], [EndTime], [ModifiedDate])
VALUES (3, N'Night', GETDATE(), GETDATE(), GETDATE())
GO
-- Simple Select Statement to build statistics
-- BEFORE Database is Read Only
SELECT *
FROM [Shift]
WHERE Name = 'Day'
GO
-- Check if there are temporary statistics
SELECT OBJECT_NAME(OBJECT_ID) TableName, name, auto_created,
user_created, is_temporary
FROM   sys.stats
WHERE OBJECT_NAME(OBJECT_ID) = 'Shift'
GO
-- Make Database Read Only
ALTER DATABASE [Read-Only] SET READ_ONLY
GO
-- Check if there are temporary statistics
SELECT OBJECT_NAME(OBJECT_ID) TableName, name, auto_created,
user_created, is_temporary
FROM   sys.stats
WHERE OBJECT_NAME(OBJECT_ID) = 'Shift'
GO
-- Simple Select Statement to build statistics
-- AFTER Database is Read Only
SELECT *
FROM [Shift]
WHERE ShiftID = 1
GO
-- Check if there are temporary statistics
SELECT OBJECT_NAME(OBJECT_ID) TableName, name, auto_created,
user_created, is_temporary
FROM   sys.stats
WHERE OBJECT_NAME(OBJECT_ID) = 'Shift'
GO
-- Clean up
USE MASTER
GO
DROP DATABASE [Read-Only]
GO

Here is the result set explained with comments.

Let me know if you find this information interesting. Please note that this only applies to SQL Server 2012.

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

SQL SERVER – How to Script Statistics from SSMS? – Scripting Statistics is Disabled in SSMS

Here is a very interesting question, I have received after reading yesterday’s blog post SQL SERVER – Statistics for Read-Only Database are in TempDB.

“Pinal,

I like your blog post. I recently tried to create statistics with the help of SSMS but I was not successful. Everytime when I go to the option to script the statistics, it is always disabled. I initially thought this was because of the read-only database, however, when I tried to research more, I realized that this is not because of the Read-Only but this option is disabled across all the database irrespective of the database status.

See attached image? Do you know is there is any way to copy statistics from the server?”

Very interesting question – the matter of the fact, I have even noticed this behavior earlier but never wrote about it. Here is the answer.

We can’t create statistics with the help of SSMS CREATE script menu as displayed in the image above. If you want to copy or create statistics from any table or view you will have to use different methods in SSMS. You will have to Right Click on the Database and Script the Table/View with Statistics option enabled. Here is the blog post where I have explained this in detail.

Refer this blog post for the solution: SQL SERVER – Copy Statistics from One Server to Another Server

Here is the option which one has to enable scripting the table with Statistics details:

Do let me know if there is any other way to enable the scripting statistics. I will be glad to post the solution with due credit.

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

SQL SERVER – Statistics for Read-Only Database are in TempDB

Here is the question I received when I was explaining about Statistics in SQLLive360 event in Orlando.

“As we use database tables and run various queries on SQL Server, it automatically creates statistics and the statistics helps eventually queries to execute efficiently. I also understand that statistics are stored in the same database where original object (table, view) exists. In the case of the Read-Only Database, what actually happens? Does Read-Only Database have statistics? If they can have statistics, how do they store them as database is already a Read-Only database?”

Well, this is a fantastic question as I have never blogged about it before. Here is how I would like to sum up Read-Only Database and statistics.

Read-Only Database and Statistics

Just like any other database Read-Only Database can also have statistics. In all of the other cases the statistics are stored in the database where the original object is stored but in the case of the Read-Only Databases, statistics are stored in TempDB. The behavior of the statistics created on Read-Only Database is absolutely same as they are created on any other database. However, there is a one thing to remember – whenever we restart the server the TempDB is reset to original state hence, every single time the server is restarted the statistics are built again from scratch for your Read-Only Database in TempDB.

Script to list Statistics

Here is a script which we can use to list all the statistics from any database.

SELECT OBJECT_ID, name, auto_created,
user_created, is_temporary
FROM sys.stats;

Script to list Statistics for Read-Only Database

Here is a script which we can use to list all the statistics from any database.

SELECT OBJECT_ID, name, auto_created,
user_created, is_temporary
FROM sys.stats
WHERE is_temporary = 1;

Column is_temporary  is set to 1 when statistics are created on the Read-Only Database and they are stored in the TempDB. If you restart the server statistics will go away and will be created when needed again.

Example

Let us see an example, where we will create a database and later on populate that with the data. Once we have data in the table, we will make it read only and check if there are any statistics on that data table or not. We will not find any statistics on that table as we have so far not executed any query. Now let us execute a query on the table and once again check the status of statistics. You will find necessary stats created on the table. When you pay attention to the last column it will have is_temporary = 1, this means that the statistics has been created in TempDB.

-- Create Database
CREATE DATABASE [Read-Only]
GO
-- Use Database
USE [Read-Only]
GO
-- Create Table
CREATE TABLE [Shift](
[ShiftID] [tinyint] NOT NULL,
[Name] VARCHAR(100) NOT NULL,
[StartTime] [time](7) NOT NULL,
[EndTime] [time](7) NOT NULL,
[ModifiedDate] [datetime] NOT NULL
)
GO
-- Insert Table
INSERT [Shift] ([ShiftID], [Name], [StartTime], [EndTime], [ModifiedDate])
VALUES (1, N'Day', GETDATE(), GETDATE(), GETDATE())
GO
INSERT [Shift] ([ShiftID], [Name], [StartTime], [EndTime], [ModifiedDate])
VALUES (2, N'Evening', GETDATE(), GETDATE(), GETDATE())
GO
INSERT [Shift] ([ShiftID], [Name], [StartTime], [EndTime], [ModifiedDate])
VALUES (3, N'Night', GETDATE(), GETDATE(), GETDATE())
GO
-- Make Database Read Only
ALTER DATABASE [Read-Only] SET READ_ONLY
GO
-- Check if there are temporary statistics
SELECT OBJECT_NAME(OBJECT_ID) TableName, name, auto_created,
user_created, is_temporary
FROM   sys.stats
WHERE  is_temporary = 1
GO
-- Simple Select Statement
SELECT *
FROM [Shift]
WHERE ShiftID = 1
GO
-- Check if there are temporary statistics
SELECT OBJECT_NAME(OBJECT_ID) TableName, name, auto_created,
user_created, is_temporary
FROM   sys.stats
WHERE  is_temporary = 1
GO
-- Clean up
USE MASTER
GO
DROP DATABASE [Read-Only]
GO

I have executed all of my scripts in SQL Server 2012. Let me know what you think about this subject.

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

SQL SERVER – Guest Post by Sandip Pani – SQL Server Statistics Name and Index Creation

Sometimes something very small or a common error which we observe in daily life teaches us new things. SQL Server Expert Sandip Pani (winner of Joes 2 Pros Contests) has come across similar experience. Sandip has written a guest post on an error he faced in his daily work. Sandip is working for QSI Healthcare as an Associate Technical Specialist and have more than 5 years of total experience. He blogs at SQLcommitted.com and contribute in various forums. His social media hands are LinkedIn, Facebook and Twitter.


Once I faced following error when I was working on performance tuning project and attempt to create an Index.

Mug 1913, Level 16, State 1, Line 1
The operation failed because an index or statistics with name ‘Ix_Table1_1′ already exists on table ‘Table1′.

The immediate reaction to the error was that I might have created that index earlier and when I researched it further I found the same as the index was indeed created two times. This totally makes sense. This can happen due to many reasons for example if the user is careless and executes the same code two times as well, when he attempts to create index without checking if there was index already on the object. However when I paid attention to the details of the error, I realize that error message also talks about statistics along with the index. I got curious if the same would happen if I attempt to create indexes with the same name as statistics already created. There are a few other questions also prompted in my mind. I decided to do a small demonstration of the subject and build following demonstration script.

The goal of my experiment is to find out the relation between statistics and the index. Statistics is one of the important input parameter for the optimizer during query optimization process. If the query is nontrivial then only optimizer uses statistics to perform a cost based optimization to select a plan. For accuracy and further learning I suggest to read MSDN.

Now let’s find out the relationship between index and statistics. We will do the experiment in two parts. i) Creating Index ii) Creating Statistics

We will be using the following T-SQL script for our example.

IF (OBJECT_ID('Table1') IS NOT NULL)
DROP TABLE Table1
GO
CREATE TABLE Table1
(Col1 INT NOT NULL,
Col2 VARCHAR(20) NOT NULL)
GO

We will be using following two queries to check if there are any index or statistics on our sample table Table1.

-- Details of Index
SELECT OBJECT_NAME(OBJECT_ID) AS TableName, Name AS IndexName, type_desc
FROM sys.indexes
WHERE OBJECT_NAME(OBJECT_ID) = 'table1'
GO
-- Details of Statistics
SELECT OBJECT_NAME(OBJECT_ID) TableName, Name AS StatisticsName
FROM sys.stats
WHERE OBJECT_NAME(OBJECT_ID) = 'table1'
GO

When I ran above two scripts on the table right after it was created it did not give us any result which was expected.

Now let us begin our test.

1) Create an index on the table

Create following index on the table.

CREATE NONCLUSTERED INDEX Ix_Table1_1 ON Table1(Col1)
GO

Now let us use above two scripts and see their results.

We can see that when we created index at the same time it created statistics also with the same name.

Before continuing to next set of demo – drop the table using following script and re-create the table using a script provided at the beginning of the table.

DROP TABLE table1
GO

2) Create a statistic on the table

Create following statistics on the table.

CREATE STATISTICS Ix_table1_1 ON Table1 (Col1)
GO

Now let us use above two scripts and see their results.

We can see that when we created statistics Index is not created. The behavior of this experiment is different from the earlier experiment.

Clean up the table setup using the following script:

DROP TABLE table1
GO

Above two experiments teach us very valuable lesson that when we create indexes, SQL Server generates the index and statistics (with the same name as the index name) together. Now due to the reason if we have already had statistics with the same name but not the index, it is quite possible that we will face the error to create the index even though there is no index with the same name.

A Quick Check

To validate that if we create statistics first and then index after that with the same name, it will throw an error let us run following script in SSMS. Make sure to drop the table and clean up our sample table at the end of the experiment.

-- Create sample table
CREATE TABLE TestTable
(Col1 INT NOT NULL,
Col2 VARCHAR(20) NOT NULL)
GO
-- Create Statistics
CREATE STATISTICS IX_TestTable_1 ON TestTable (Col1)
GO
-- Create Index
CREATE NONCLUSTERED INDEX IX_TestTable_1 ON TestTable(Col1)
GO
-- Check error
/*Msg 1913, Level 16, State 1, Line 2
The operation failed because an index or statistics with name 'IX_TestTable_1' already exists on table 'TestTable'.
*/
-- Clean up
DROP TABLE TestTable
GO

While creating index it will throw the following error as statistics with the same name is already created.

In simple words – when we create index the name of the index should be different from any of the existing indexes and statistics.

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

SQL SERVER – Fix: Error: Msg 1904, Level 16 The statistics on table has 33 column names in statistics key list. The maximum limit for index or statistics key column list is 32

Earlier I wrote an article where I demonstrated that an index with more than 16 column is not possible. Here is the link to the article. After reading the same article I received email from user suggesting does it mean that statistics can be only created on only 16 columns. Well, answer is NO. One can create statistics on total of 32 columns, where as the limit of creating index is only 16 columns (and 900 bytes).

Here is the quick example where when attempted to create statistics on 33 columns is generating error but when statistics are created on 32 columns it works successfully.

Set up Script:

CREATE DATABASE TestDB
GO
USE TestDB
GO
CREATE TABLE Test1
(ID1 INT,  ID2 INT, ID3 INT,
ID4 INT, ID5 INT, ID6 INT,
ID7 INT, ID8 INT, ID9 INT,
ID10 INT, ID11 INT, ID12 INT,
ID13 INT, ID14 INT, ID15 INT,
ID16 INT, ID17 INT, ID18 INT,
ID19 INT, ID20 INT, ID21 INT,
ID22 INT, ID23 INT, ID24 INT,
ID25 INT, ID26 INT, ID27 INT,
ID28 INT, ID29 INT, ID30 INT,
ID31 INT, ID32 INT, ID33 INT)
GO

Here is the example when index created on 33 columns it gives error.

CREATE STATISTICS [Stats_Test1]
ON [dbo].[Test1]
([ID1], [ID2], [ID3], [ID4], [ID5],
[ID6], [ID7], [ID8], [ID9], [ID10],
[ID11], [ID12], [ID13], [ID14], [ID15],
[ID16], [ID17], [ID18], [ID19], [ID20],
[ID21], [ID22], [ID23], [ID24], [ID25],
[ID26], [ID27], [ID28], [ID29], [ID30],
[ID31], [ID32], [ID33]
)
GO

Msg 1904, Level 16, State 2, Line 1
The statistics ‘Stats_Test1′ on table ‘dbo.Test1′ has 33 column names in statistics key list. The maximum limit for index or statistics key column list is 32.

Here is the example when index created on 32 columns it works perfectly fine.

CREATE STATISTICS [Stats_Test1]
ON [dbo].[Test1]
([ID1], [ID2], [ID3], [ID4], [ID5],
[ID6], [ID7], [ID8], [ID9], [ID10],
[ID11], [ID12], [ID13], [ID14], [ID15],
[ID16], [ID17], [ID18], [ID19], [ID20],
[ID21], [ID22], [ID23], [ID24], [ID25],
[ID26], [ID27], [ID28], [ID29], [ID30],
[ID31], [ID32]
)
GO

Well, in simple words, one can create statistics on 32 columns. Please additionally, note, the scope of this blog is not to discuss if that is good or bad. The purpose of this post is notice the Index can have maximum 16 columns but statistics can have 32 columns.

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