SQL SERVER – What is Filtered Statistics?

Continuous learning is the only way to keep myself up-to-date with SQL Server product features. While I was working for a demo to show usage of SQL Server Statistics, I came across feature called Filter Statistics which was introduced in SQL Server 2008.

There are many times when we have all indexes and statistics in-place, but still optimizer is not able to make a proper estimation because it doesn’t understand the co-relation of the data. To show the power of this, I was able to make a small demo and show its real time usage.

In my database, I have created two tables. Employee and Sales. Here is the script.

USE MASTER
GO
IF DB_ID('DropMeAfterDemo') IS NOT NULL
BEGIN
ALTER DATABASE
DropMeAfterDemo SET single_user WITH ROLLBACK IMMEDIATE
DROP DATABASE
DropMeAfterDemo
END
GO
CREATE DATABASE DropMeAfterDemo
GO
SET NOCOUNT ON
GO
USE DropMeAfterDemo
GO
IF OBJECT_ID('Employee') IS NOT NULL
DROP TABLE Employee
GO
IF OBJECT_ID('Sales') IS NOT NULL
DROP TABLE Sales
GO
CREATE TABLE Employee(EmpID INT, name NVARCHAR(100))
GO
CREATE TABLE Sales(EmpID INT, SalesAmount INT, DATETIME DATETIME)
GO
CREATE CLUSTERED INDEX cidx_EmpID ON Employee(EmpID)
GO
CREATE INDEX ix_Employee_name ON Employee(name)
GO
CREATE STATISTICS ix_Employee_EmpID_name ON Employee(EmpID, name)
GO
CREATE CLUSTERED INDEX ix_Sales_id_amount ON Sales(EmpID, SalesAmount)
GO

This company has only two employees and they are not doing the same amount of sales. This is what is called as skewness in data. Here is the insert script to populate them in uneven fashion.

-- SQLAuthority is a small blog where me and my wife work
INSERT Employee VALUES(1, 'Pinal')
INSERT Employee VALUES(2, 'Nupur')
GO
SET NOCOUNT ON
-- Pinal did very bad with just one Sale
INSERT Sales VALUES(1, 100,GETDATE())
--  but Nupur did very well with 5000 Sales
DECLARE @loop INT
SET
@loop = 1
WHILE @loop <= 5000 BEGIN
INSERT
Sales  VALUES (2, @loop, GETDATE()-RAND()*1000)
SET @loop = @loop + 1
END
GO

To make sure the statistics are proper, I would update it with full scan.

-- updating all statistics for both tables
UPDATE STATISTICS Employee WITH fullscan
GO
UPDATE STATISTICS Sales WITH fullscan
GO

Now, let’s run the query and have a look at the actual execution plan. You need to enable that in SQL Server Management Studio.

-- Let's look at estimated and actual number of rows
DBCC freeproccache
GO
SELECT salesamount
FROM employee
,sales
WHERE employee.empid = sales.empid
AND name = 'pinal'
GO
DBCC freeproccache
GO
SELECT salesamount
FROM employee
,sales
WHERE employee.empid = sales.empid
AND name = 'nupur'
GO

Here is the query plan. As we can see that the estimated are not correct because SQL can’t determine the matching rows until it find Employee ID from Employee Table.

Now, let’s create filtered statistics based on where clause.

-- create filter statistics based on where clause in query.
CREATE STATISTICS Employee_stats_EmpID ON Employee (EmpID)
WHERE name = 'Pinal'
GO
GO
CREATE STATISTICS  Employee_stats_EmpID2 ON Employee (EmpID)
WHERE name = 'Nupur'
GO

If we run exact same query as earlier, here are the plans.

Here is the plan for “name = pinal”

Here is the plan for “name = nupur”

Notice two things here:
1. Statistics picked by optimizer are accurate now. Estimated rows is equal to actual rows.
2. Query Plan is different for two different values.

Here is how we can see filtered statistics

SELECT *
FROM   sys.stats
WHERE  filter_definition IS NOT NULL

The main usage of this feature would be in a situation where data is skewed. Have you ever got a chance to use this feature in your environment? Let me know.

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

SQL SERVER – DMV to Identify Incremental Statistics – Performance improvements in SQL Server 2014 – Part 3

This is the third part of the series Incremental Statistics. Here is the index of the complete series.


In earlier two parts we have seen what is incremental statistics and its simple example. In this blog post we will be discussing about DMV, which will list all the statistics which are enabled for Incremental Updates.

SELECT  OBJECT_NAME(sys.stats.OBJECT_ID) AS TableName,
sys.columns.name AS ColumnName,
sys.stats.name AS StatisticsName
FROM   sys.stats
INNER JOIN sys.stats_columns ON sys.stats.OBJECT_ID = sys.stats_columns.OBJECT_ID
AND sys.stats.stats_id = sys.stats_columns.stats_id
INNER JOIN sys.columns ON sys.stats.OBJECT_ID = sys.columns.OBJECT_ID
AND sys.stats_columns.column_id = sys.columns.column_id
WHERE   sys.stats.is_incremental = 1

If you run above script in the example displayed, in part 1 and part 2 you will get resultset as following.

When you execute the above script, it will list all the statistics in your database which are enabled for Incremental Update. The script is very simple and effective. If you have any further improved script, I request you to post in the comment section and I will post that on blog with due credit.

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

SQL SERVER – Simple Example of Incremental Statistics – Performance improvements in SQL Server 2014 – Part 2

This is the second part of the series Incremental Statistics. Here is the index of the complete series.


In part 1 we have understood what is incremental statistics and now in this second part we will see a simple example of incremental statistics. This blog post is heavily inspired from my friend Balmukund’s must read blog post. If you have partitioned table and lots of data, this feature can be specifically very useful.

Prerequisite

Here are two things you must know before you start with the demonstrations.

AdventureWorks – For the demonstration purpose I have installed AdventureWorks 2012 as an AdventureWorks 2014 in this demonstration.

Partitions – You should know how partition works with databases.

Setup Script

Here is the setup script for creating Partition Function, Scheme, and the Table. We will populate the table based on the SalesOrderDetails table from AdventureWorks.

-- Use Database
USE AdventureWorks2014
GO
-- Create Partition Function
CREATE PARTITION FUNCTION IncrStatFn (INT) AS RANGE LEFT FOR VALUES
(44000, 54000, 64000, 74000)
GO
-- Create Partition Scheme
CREATE PARTITION SCHEME IncrStatSch AS PARTITION [IncrStatFn] TO
([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY])
GO
-- Create Table Incremental_Statistics
CREATE TABLE [IncrStatTab](
[SalesOrderID] [int] NOT NULL,
[SalesOrderDetailID] [int] NOT NULL,
[CarrierTrackingNumber] [nvarchar](25) NULL,
[OrderQty] [smallint] NOT NULL,
[ProductID] [int] NOT NULL,
[SpecialOfferID] [int] NOT NULL,
[UnitPrice] [money] NOT NULL,
[UnitPriceDiscount] [money] NOT NULL,
[ModifiedDate] [datetime] NOT NULL)
ON IncrStatSch(SalesOrderID)
GO
-- Populate Table
INSERT INTO [IncrStatTab]([SalesOrderID], [SalesOrderDetailID],
[CarrierTrackingNumber], [OrderQty], [ProductID],
[SpecialOfferID], [UnitPrice],   [UnitPriceDiscount], [ModifiedDate])
SELECT     [SalesOrderID], [SalesOrderDetailID],
[CarrierTrackingNumber], [OrderQty], [ProductID],
[SpecialOfferID], [UnitPrice],   [UnitPriceDiscount], [ModifiedDate]
FROM       [Sales].[SalesOrderDetail]
WHERE      SalesOrderID < 54000
GO

Check Details

Now we will check details in the partition table IncrStatSch.

-- Check the partition
SELECT *
FROM sys.partitions
WHERE OBJECT_ID = OBJECT_ID('IncrStatTab')
GO

You will notice that only a few of the partition are filled up with data and remaining all the partitions are empty.

Now we will create statistics on the Table on the column SalesOrderID.

However, here we will keep adding one more keyword which is INCREMENTAL = ON. Please note this is the new keyword and feature added in SQL Server 2014. It did not exist in earlier versions.

-- Create Statistics
CREATE STATISTICS IncrStat
ON [IncrStatTab] (SalesOrderID)
WITH FULLSCAN, INCREMENTAL = ON
GO

Now we have successfully created statistics let us check the statistical histogram of the table.

Now let us once again populate the table with more data. This time the data are entered into a different partition than earlier populated partition.

-- Populate Table
INSERT INTO [IncrStatTab]([SalesOrderID], [SalesOrderDetailID],
[CarrierTrackingNumber], [OrderQty], [ProductID],
[SpecialOfferID], [UnitPrice],   [UnitPriceDiscount], [ModifiedDate])
SELECT     [SalesOrderID], [SalesOrderDetailID],
[CarrierTrackingNumber], [OrderQty], [ProductID],
[SpecialOfferID], [UnitPrice],   [UnitPriceDiscount], [ModifiedDate]
FROM       [Sales].[SalesOrderDetail]
WHERE      SalesOrderID > 54000
GO

Let us check the status of the partition once again with following script.

-- Check the partition
SELECT *
FROM sys.partitions
WHERE OBJECT_ID = OBJECT_ID('IncrStatTab')
GO

Statistics Update

Now here has the new feature come into action. Previously, if we have to update the statistics, we will have to FULLSCAN the entire table irrespective of which partition got the data.

However, in SQL Server 2014 we can just specify which partition we want to update in terms of Statistics. Here is the script for the same.

-- Update Statistics Manually
UPDATE STATISTICS IncrStatTab (IncrStat)
WITH RESAMPLE ON PARTITIONS(3, 4)
GO

Now let us check the statistics once again.

-- Show Statistics
DBCC SHOW_STATISTICS('IncrStatTab', IncrStat)
WITH HISTOGRAM
GO

Upon examining statistics histogram, you will notice that now the distribution has changed and there is way more rows in the histogram.

Summary

The new feature of Incremental Statistics is indeed a boon for the scenario where there are partitions and statistics needs to be updated frequently on the partitions. In earlier version to update statistics one has to do FULLSCAN on the entire table which was wasting too many resources. With the new feature in SQL Server 2014, now only those partitions which are significantly changed can be specified in the script to update statistics.

Cleanup

You can clean up the database by executing following scripts.

-- Clean up
DROP TABLE [IncrStatTab]
DROP PARTITION SCHEME [IncrStatSch]
DROP PARTITION FUNCTION [IncrStatFn]
GO

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

SQL SERVER – What is Incremental Statistics? – Performance improvements in SQL Server 2014 – Part 1

This is the first part of the series Incremental Statistics. Here is the index of the complete series.


Statistics are considered one of the most important aspects of SQL Server Performance Tuning. You might have often heard the phrase, with related to performance tuning.

“Update Statistics before you take any other steps to tune performance”.

Honestly, I have said above statement many times and many times, I have personally updated statistics before I start to do any performance tuning exercise. You may agree or disagree to the point, but there is no denial that Statistics play an extremely vital role in the performance tuning.

SQL Server 2014 has a new feature called Incremental Statistics. I have been playing with this feature for quite a while and I find that very interesting. After spending some time with this feature, I decided to write about this subject over here.

New in SQL Server 2014 – Incremental Statistics

Well, it seems like lots of people wants to start using SQL Server 2014’s new feature of Incremetnal Statistics. However, let us understand what actually this feature does and how it can help. I will try to simplify this feature first before I start working on the demo code.

Code for all versions of SQL Server

Here is the code which you can execute on all versions of SQL Server and it will update the statistics of your table. The keyword which you should pay attention is WITH FULLSCAN. It will scan the entire table and build brand new statistics for you which your SQL Server Performance Tuning engine can use for better estimation of your execution plan.

UPDATE STATISTICS TableName(StatisticsName) WITH FULLSCAN

Who should learn about this? Why?

If you are using partitions in your database, you should consider about implementing this feature. Otherwise, this feature is pretty much not applicable to you. Well, if you are using single partition and your table data is in a single place, you still have to update your statistics the same way you have been doing.

If you are using multiple partitions, this may be a very useful feature for you. In most cases, users have multiple partitions because they have lots of data in their table. Each partition will have data which belongs to itself. Now it is very common that each partition are populated separately in SQL Server.

Real World Example

For example, if your table contains data which is related to sales, you will have plenty of entries in your table. It will be a good idea to divide the partition into multiple filegroups for example, you can divide this table into 3 semesters or 4 quarters or even 12 months. Let us assume that we have divided our table into 12 different partitions. Now for the month of January, our first partition will be populated and for the month of February our second partition will be populated. Now assume, that you have plenty of the data in your first and second partition. Now the month of March has just started and your third partition has started to populate. Due to some reason, if you want to update your statistics, what will you do?

In SQL Server 2012 and earlier version

You will just use the code of WITH FULLSCAN and update the entire table. That means even though you have only data in third partition you will still update the entire table. This will be VERY resource intensive process as you will be updating the statistics of the partition 1 and 2 where data has not changed at all.

In SQL Server 2014

You will just update the partition of Partition 3. There is a special syntax where you can now specify which partition you want to update now. The impact of this is that it is smartly merging the new data with old statistics and update the entire statistics without doing FULLSCAN of your entire table. This has a huge impact on performance.

Remember that the new feature in SQL Server 2014 does not change anything besides the capability to update a single partition. However, there is one feature which is indeed attractive. Previously, when table data were changed 20% at that time, statistics update were triggered. However, now the same threshold is applicable to a single partition. That means if your partition faces 20% data, change it will also trigger partition level statistics update which, when merged to your final statistics will give you better performance.

In summary

If you are not using a partition, this feature is not applicable to you.

If you are using a partition, this feature can be very helpful to you.

Tomorrow:

We will see working code of SQL Server 2014 Incremental Statistics.

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

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)

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 – 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)