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)

About these ads

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.

Click to Download Scripts

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)

SQL SERVER – Find Details for Statistics of Whole Database – DMV – T-SQL Script

I was recently asked is there a single script which can provide all the necessary details about statistics for any database. This question made me write following script. I was initially planning to use sp_helpstats command but I remembered that this is marked to be deprecated in future. Again, using DMV is the right thing to do moving forward. I quickly wrote following script which gives a lot more information than sp_helpstats.

USE AdventureWorks
GO
SELECT DISTINCT
OBJECT_NAME(s.[object_id]) AS TableName,
c.name AS ColumnName,
s.name AS StatName,
s.auto_created,
s.user_created,
s.no_recompute,
s.[object_id],
s.stats_id,
sc.stats_column_id,
sc.column_id,
STATS_DATE(s.[object_id], s.stats_id) AS LastUpdated
FROM sys.stats s JOIN sys.stats_columns sc ON sc.[object_id] = s.[object_id] AND sc.stats_id = s.stats_id
JOIN sys.columns c ON c.[object_id] = sc.[object_id] AND c.column_id = sc.column_id
JOIN sys.partitions par ON par.[object_id] = s.[object_id]
JOIN sys.objects obj ON par.[object_id] = obj.[object_id]
WHERE OBJECTPROPERTY(s.OBJECT_ID,'IsUserTable') = 1
AND (s.auto_created = 1 OR s.user_created = 1);

If you have better script to retrieve information about statistics, please share here and I will publish it with due credit.

Update: Read follow up excellent blog post by Jason Brimhall.

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

SQL SERVER – Puzzle – Statistics are not Updated but are Created Once

After having excellent response to my quiz – Why SELECT * throws an error but SELECT COUNT(*) does not?I have decided to ask another puzzling question to all of you.

I am running this test on SQL Server 2008 R2. Here is the quick scenario about my setup.

  • Create Table
  • Insert 1000 Records
  • Check the Statistics
  • Now insert 10 times more 10,000 indexes
  • Check the Statistics – it will be NOT updated

Note: Auto Update Statistics and Auto Create Statistics for database is TRUE

Expected Result – Statistics should be updated – SQL SERVER – When are Statistics Updated – What triggers Statistics to Update

Now the question is why the statistics are not updated?

The common answer is – we can update the statistics ourselves using

UPDATE STATISTICS TableName WITH FULLSCAN, ALL

However, the solution I am looking is where statistics should be updated automatically based on algorithm mentioned here.

Now the solution is to ____________________.

Vinod Kumar is not allowed to take participate over here as he is the one who has helped me to build this puzzle.

I will publish the solution on next week. Please leave a comment and if your comment consist valid answer, I will publish with due credit.

Here is the script to reproduce the scenario which I mentioned.

-- Execution Plans Difference
-- Create Sample Database
CREATE DATABASE SampleDB
GO
USE SampleDB
GO
-- Create Table
CREATE TABLE ExecTable (ID INT,
FirstName VARCHAR(100),
LastName VARCHAR(100),
City VARCHAR(100))
GO
-- Insert One Thousand Records
-- INSERT 1
INSERT INTO ExecTable (ID,FirstName,LastName,City)
SELECT TOP 1000 ROW_NUMBER() OVER (ORDER BY a.name) RowID,
'Bob',
CASE WHEN  ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN 'Smith'
ELSE 'Brown' END,
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%20 = 1 THEN 'New York'
WHEN  ROW_NUMBER() OVER (ORDER BY a.name)%20 = 5 THEN 'San Marino'
WHEN  ROW_NUMBER() OVER (ORDER BY a.name)%20 = 3 THEN 'Los Angeles'
WHEN  ROW_NUMBER() OVER (ORDER BY a.name)%20 = 7 THEN 'La Cinega'
WHEN  ROW_NUMBER() OVER (ORDER BY a.name)%20 = 13 THEN 'San Diego'
WHEN  ROW_NUMBER() OVER (ORDER BY a.name)%20 = 17 THEN 'Las Vegas'
ELSE 'Houston' END
FROM
sys.all_objects a
CROSS JOIN sys.all_objects b
GO
-- Display statistics of the table - none listed
sp_helpstats N'ExecTable', 'ALL'
GO
-- Select Statement
SELECT FirstName, LastName, City
FROM ExecTable
WHERE City  = 'New York'
GO
-- Display statistics of the table
sp_helpstats N'ExecTable', 'ALL'
GO

-- Replace your Statistics over here
-- NOTE: Replace your _WA_Sys with stats from above query
DBCC SHOW_STATISTICS('ExecTable', _WA_Sys_00000004_7D78A4E7);
GO
--------------------------------------------------------------
-- Round 2
-- Insert Ten Thousand Records
-- INSERT 2
INSERT INTO ExecTable (ID,FirstName,LastName,City)
SELECT TOP 10000 ROW_NUMBER() OVER (ORDER BY a.name) RowID,
'Bob',
CASE WHEN  ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN 'Smith'
ELSE 'Brown' END,
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%20 = 1 THEN 'New York'
WHEN  ROW_NUMBER() OVER (ORDER BY a.name)%20 = 5 THEN 'San Marino'
WHEN  ROW_NUMBER() OVER (ORDER BY a.name)%20 = 3 THEN 'Los Angeles'
WHEN  ROW_NUMBER() OVER (ORDER BY a.name)%20 = 7 THEN 'La Cinega'
WHEN  ROW_NUMBER() OVER (ORDER BY a.name)%20 = 13 THEN 'San Diego'
WHEN  ROW_NUMBER() OVER (ORDER BY a.name)%20 = 17 THEN 'Las Vegas'
ELSE 'Houston' END
FROM
sys.all_objects a
CROSS JOIN sys.all_objects b
GO
-- Select Statement
SELECT FirstName, LastName, City
FROM ExecTable
WHERE City  = 'New York'
GO
-- Display statistics of the table
sp_helpstats N'ExecTable', 'ALL'
GO
-- Replace your Statistics over here
-- NOTE: Replace your _WA_Sys with stats from above query
DBCC SHOW_STATISTICS('ExecTable', _WA_Sys_00000004_7D78A4E7);
GO
-- You will notice that Statistics are still updated with 1000 rows
-- Clean up Database
DROP TABLE ExecTable
GO
USE MASTER
GO
ALTER DATABASE SampleDB
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DROP DATABASE SampleDB
GO

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

SQLAuthority News – Solid Quality Journal – Importance of Statistics

My article on “Important of Statistics” has been published in Solid Quality Journal.

Statistics are a key part of getting solid performance. In this article we will go over the basics of the statistics and various best practices related to Statistics. We will go over various frequently asked questions like when to update statistics and difference between sync and async update of statistics. We will also discuss the pros and cons of the statistics update.

I have answered one very important questions in this article:

Should keep Auto Create Statistics and Auto Update Statistics settings true/on?

Download Importance of Statistics

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