SQL SERVER – Three Simple Guidelines for System Maintenance – Notes from the Field #098

[Note from Pinal]: This is a 98th episode of Notes from the Field series. Maintenance of the database is a very critical activity and I have always seen DBA taking it very seriously. There is a only one problem – there is no single solution or guidance for how to maintain the database. Everybody has their own opinion and way to do different tasks. System maintenance is very straight forward task but I have seen quite often experts even getting confused with the same. Many only focus on index maintenance, statistics maintenance and a few other tasks are common, but understanding the real philosophy of this task is something beyond indexes. When I asked my good friend Stuart about system maintenance, he came up with very interesting guidelines for system maintenance.

In this episode of the Notes from the Field series database expert Stuart Ainsworth explains about three simple guidelines for system maintenance.

 SQL SERVER   Three Simple Guidelines for System Maintenance   Notes from the Field #098

This week, I was helping a client review their backup and disaster recovery strategy when they asked me how to handle an ongoing maintenance issue; it wasn’t really a SQL issue, but they were concerned about what to do for a virtualized server running an older, critical website. They’re in the process of replacing the site, but they have to maintain the machine for at least another 6 months.

A recent patch had crippled the machine, and they had to restore the VM from backup, which took the site down for a few hours. Thankfully, their backups worked, but a retrospective look at what happened showed some flaws in their process:

  1. They knew that the server was critical AND having issues before they applied the patch, and
  2. Their system admin was inexperienced with this particular type of restore, and that cost them some time during their recovery.

At Linchpin People, we usually get asked questions like this as part of the WellDBA exam. Here’s my short list of guidelines for system maintenance; they can be applied to more than just backups.

Automate what you can

Most modern system tools will allow you to do some form of automatic maintenance; SQL Server runs jobs on SQL Agent, Windows Server has Task Scheduler, and Linux has cron. Lots of third-party tools offer not only backup capabilities, but also restore verification capabilities and reporting. The security and time saved by using a tool are usually worth the investment; it frees your biggest expense (your people) up in order to focus on other things.

maintenance SQL SERVER   Three Simple Guidelines for System Maintenance   Notes from the Field #098If you must have a manual process, make it simple, and document it

Sometimes you can’t escape manual processes. For this client, the critical nature of this web site meant that they didn’t want to have an automatic reboot cycle, so while patches got pushed automatically, they rebooted manually. Given the fragility of their recent experience, they’ve decided to stop automatically patching, and doing the following steps each month:

  • Snapshot the server (for quicker restores).
  • Patch and reboot.
  • If patch is unsuccessful, restore from snapshot.
  • If patch is successful, delete the snapshot.

Simple enough. However, my suggestion was that they write those steps down. Operating from a checklist ensures that procedures are followed consistently, elevating the process to near automatic status. It also gives you an opportunity to periodically review manual processes, and automate pieces as situations and technology changes.

Validate, validate, validate…

So, if you’re automating your processes as much as you can, and simplifying your manual processes, what are your engineers spending their time on? They should be periodically validating that the maintenance plans you implemented are successfully working, including routinely doing recovery drills. Just like a fire drill, IT professionals need to practice what they would do in case of a recovery scenario in order to minimize the downtime associated with an actual recovery.

This is one of those activities that is well received in theory, but rarely done in reality. Support queues are always full, new projects and feature requests are always pressing, and maintenance issues are usually at the bottom of the pile. However, in the event of an outage, routines that have been well practiced are the ones that are the fastest to recover; a crisis situation is the absolute worst time for an engineer to feel like they don’t understand what to do.


Routine maintenance isn’t difficult, but it can be time consuming. To minimize time, automate what you can, and simplify your manual processes. Then, invest your time in preparing for the inevitable. Technology breaks; don’t let it break you.

If you want to get started with SQL Server with the help of experts, read more over at Fix Your SQL Server.

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

SQL SERVER – Reading Statistics IO data with SQL Server

directory main statistics SQL SERVER   Reading Statistics IO data with SQL ServerI am a supporter of always going to the basics when it comes to performance troubleshooting. Whenever I have done any perf tuning exercise, I start by enabling the Statistics IO as part of my debugging. When STATISTICS I/O is enabled, SQL Server maintains I/O statistics for the queries on a per-table basis (as opposed to a cumulative fashion for all tables). This output is sent back as a message after the query completes.

For each table referenced in the query, there will be one row that is similar to this:

Table ‘authors’. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.

Scan count: shows the number of times that the OLE DB row set corresponding with the table or index was opened for reading. This value is dependent on the type of plan chosen and where this table is accessed in relation to the other tables listed in the query. Note that just because this says scan that it does not necessarily mean that it was a table scan or index scan—it may have been an index seek.

Logical reads: counts the number of times that a request was made to access a page belonging to that particular table. This is incremented in the underlying calls to one of the page suppliers (linked pages, unordered page, etc.). This counter gets incremented regardless of whether the page was already in cache or a disk I/O was required.

Physical reads: is incremented in the same place that increments the logical reads, but only if the underlying request read the page from the disk. Note that each one of these requests is statistics1 SQL SERVER   Reading Statistics IO data with SQL Serveressentially synchronous – the page was requested and the caller had to wait for the I/O to complete before continuing execution.

Read ahead reads: is incremented when the page(s) were not already found in cache and had to be read from the disk. Here, the caller generally is able to do the other work after calling this and will use the page (latch it) at a later time. It is possible for the caller to request the page(s) to be read, but never actually use it. This might happen in the case of queries with TOP or SET ROWCOUNT, semi joins or anti semi joins where we can break out of the loop on the first match/non-match etc. Pages that are included in the read ahead counter are not included in the physical read counter.

Now that we have this understanding in place, I hope you will use this information at some point in time while doing performance tuning in your environments. Do let me know if you ever used this information as your tuning techniques and how you did troubleshooting with such information.

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

SQL SERVER – Basic Statistics Maintenance – Notes from the Field #083

[Note from Pinal]: This is a 83rd episode of Notes from the Field series. Maintenance of the database is very critical activity and I have always seen DBA taking it very seriously. There is a only one problem – there is no single solution or guidance for how to maintain the database. Everybody has their own opinion and way to do different tasks. Statistics is one of the most important aspect of the database. The performance of entire application can depend on statistics, as it can help SQL Engine with intelligence to execute optimal plan.

In this episode of the Notes from the Field series database expert Stuart Ainsworth explains about basic statistics maintenance.

 SQL SERVER   Basic Statistics Maintenance   Notes from the Field #083

Statistic maintenance is an important (but often overlooked) aspect of performance tuning for SQL Server.  The query optimizer relies on distribution statistics to determine how the query will be executed, with a particular emphasis on SEEKS vs SCANS as well as estimates of effort needed (the cost threshold for parallelism).  Out-of date stats can impact performance significantly.

Luckily, the default setting for most databases covers most database performance scenarios; SQL Server offers three basic settings for statistics maintenance:

  • Auto Create Statistics – SQL Server will create statistics during an index creation script, or when deemed necessary to satisfy a query; enabled by default.
  • Auto Update Statistics – SQL Server will update statistics when it deems them to be outdated; enabled by default.
  • Auto Update Statistics Asynchronously – When enabled, SQL Server will updated statistics after the execution of a query if it determines that an update is necessary; if disabled, the statistics will be updated first, and then the query executed. This setting is disabled by default, and there’s some controversy over whether or not it’s useful.

notes 83 1 SQL SERVER   Basic Statistics Maintenance   Notes from the Field #083

For the most part, SQL Server does a good job of maintaining statistics if these options are left with the defaults; however, statistics can still become stale over time if the data is updated at a slow rate of change.  If your statistics are more than a year old in your database, it’s probably time to do a refresh.

But how do you tell when your stats are out of date? There’s a catalog view in SQL Server called sys.stats that will give you a lot of information about statistics, but it’s very detailed; data is collected down to the column level, which may be overwhelming if you have lots of databases.  I suggest starting at a higher level, by taking a look at how out of date statistics are across all of your databases.   I use the following query to help me quickly identify troublesome databases:

/*checks last updated stats date for all databases; calculates a percentage of stats updated within the last 24 hours.
useful to determine how out-of-date statistics are.  Also identifies if auto updatestates are on and if asynchronous updates
are enabled.
database_name VARCHAR(100)
oldest_stats DATETIME
, newest_stats DATETIME
, percent_update_within_last_day DECIMAL(5, 2)
is_auto_update_stats_on BIT
, is_auto_update_stats_async_on BIT
DECLARE @dynsql NVARCHAR(4000) = 'use ?
if db_id() >4
WITH    x AS ( SELECT   STATS_DATE(s.object_id, stats_id) date_updated
FROM     sys.stats s
JOIN sys.tables t ON t.object_id = s.object_id
WHERE    t.is_ms_shipped = 0
) ,
AS ( SELECT   MIN(date_updated) AS oldest_stats
, MAX(date_updated) AS newest_stats
FROM     x
SELECT DB_NAME() database_name
, oldest_stats
, newest_stats
, SUM(CASE WHEN DATEDIFF(d, date_updated, newest_stats) <=1 THEN 100.00 ELSE 0.00 END)/COUNT(*) "percent_update_within_last_day"
, d.is_auto_update_stats_on
, d.is_auto_update_stats_async_on
FROM    x
CROSS JOIN sys.databases d
WHERE d.database_id = db_id()
GROUP BY oldest_stats
, newest_stats
, d.is_auto_update_stats_on
, d.is_auto_update_stats_async_on
( database_name
, oldest_stats
, newest_stats
, percent_update_within_last_day
, is_auto_update_stats_on
, is_auto_update_stats_async_on
EXEC sp_MSforeachdb @dynsql
FROM    #dbs d

Results will look similar to the following:

notes 83 2 SQL SERVER   Basic Statistics Maintenance   Notes from the Field #083

Looking at the results, if I see that the oldest database stats are more than a year old, it’s s a pretty good indicator that statistics are not being maintained by some form of ongoing maintenance operation.  If the defaults are not being used, that’s also something that needs to be investigated.

The percent_update_within_last_day is also a good trigger for me to investigate a potential issue; if less than 20% of the statistics in a database were updated in the last 24 hours, the odds of a less-than-optimal execution plan increase significantly.  If the tables in the database are large, it may take several million rows of changed data to trigger a refresh.

To refresh outdated statistics, I recommend Ola Hallengren’s maintenance scripts; they offer a lot of flexibility for developing a customized index and statistics maintenance plan, including the ability to update statistics on a scheduled basis, and focus on updating statistics that need to be refreshed.  Basic maintenance (such as this) can help prevent performance problems from occurring.

If you want to get started with SQL Server with the help of experts, read more over at Fix Your SQL Server.

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

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.

IF DB_ID('DropMeAfterDemo') IS NOT NULL
USE DropMeAfterDemo
CREATE TABLE Employee(EmpID INT, name NVARCHAR(100))
CREATE INDEX ix_Employee_name ON Employee(name)
CREATE STATISTICS ix_Employee_EmpID_name ON Employee(EmpID, name)
CREATE CLUSTERED INDEX ix_Sales_id_amount ON Sales(EmpID, SalesAmount)

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')
-- Pinal did very bad with just one Sale
--  but Nupur did very well with 5000 Sales
@loop = 1
WHILE @loop <= 5000 BEGIN
Sales  VALUES (2, @loop, GETDATE()-RAND()*1000)
SET @loop = @loop + 1

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

-- updating all statistics for both tables

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
SELECT salesamount
FROM employee
WHERE employee.empid = sales.empid
AND name = 'pinal'
DBCC freeproccache
SELECT salesamount
FROM employee
WHERE employee.empid = sales.empid
AND name = 'nupur'

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.

FilterStats 01 SQL SERVER   What is Filtered Statistics?

FilterStats 02 SQL SERVER   What is Filtered Statistics?

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'
CREATE STATISTICS  Employee_stats_EmpID2 ON Employee (EmpID)
WHERE name = 'Nupur'

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

Here is the plan for “name = pinal”

FilterStats 03 SQL SERVER   What is Filtered Statistics?

Here is the plan for “name = nupur”

FilterStats 04 SQL SERVER   What is Filtered Statistics?

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

FROM   sys.stats
WHERE  filter_definition IS NOT NULL

FilterStats 05 SQL SERVER   What is Filtered Statistics?

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.

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.

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

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.


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
-- Create Partition Function
(44000, 54000, 64000, 74000)
-- Create Partition Scheme
-- Create Table Incremental_Statistics
[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)
-- 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

Check Details

Now we will check details in the partition table IncrStatSch.

-- Check the partition
FROM sys.partitions

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

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
ON [IncrStatTab] (SalesOrderID)

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

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

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

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

-- Check the partition
FROM sys.partitions

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

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

Now let us check the statistics once again.

-- Show Statistics

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

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


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.


You can clean up the database by executing following scripts.

-- Clean up
DROP TABLE [IncrStatTab]

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

bigstats SQL SERVER   What is Incremental Statistics?   Performance improvements in SQL Server 2014   Part 1Well, 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.


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.


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

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

SQLAuthority News – Microsoft Whitepaper – Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator

I have been reading this Whitepaper from a couple of days and I am yet not done reading it completely, but I think it is one of the best white papers I have read in the recent time. First of all, it is written by my friend and SQL Expert Joe Sack. If you know Joe, you know that he is blessed with the skill to make a complex subject very easy. There are very few experts can do this. Kudos to Joe for amazing white paper. This white paper is 43 pages long and over 10,000 words, but trust me, every single word is worth it. I am planning to complete this whitepaper this weekend.

Every new version of SQL Server gets new performance enhancement features. In SQL Server 2014 SQL Server Query Optimizer cardinality estimation process got a major overhaul. A new algorithm for the cardinality estimation now has improved accuracy, consistency and suitability of key areas within the cardinality estimation process. In simple words, it is going to improve performance of queries executed on SQL Server.

Here is the paragraph from the Whitepaper which describes what actually this white paper is going to include:

The SQL Server query optimization process seeks the most efficient processing strategy for executing queries across a wide variety of workloads. Achieving predictable query performance across online transaction processing (OLTP), relational data warehousing, and hybrid database schemas is inherently difficult. While many workloads will benefit from the new cardinality estimator changes, in some cases, workload performance may degrade without a specific tuning effort. In this paper, we will discuss the fundamentals of the SQL Server 2014 cardinality estimator changes. We will provide details on activating and deactivating the new cardinality estimator. We will also provide troubleshooting guidance for scenarios where query performance degrades as a direct result of cardinality estimate issues.

I personally believe there are so many new enhancements in SQL Server that sometimes, I feel foreign to this entire subject. I feel that SQL Server has moved ahead and I am still working with version n-1. However, when I come across white paper like this, I regain my own confidence as now I know I can depend on this white paper to learn what are the new features available. Later on I can go and implement this feature on production server and master the basics as well as advanced concepts.

Download the white paper from here. 

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.

temporarylogo SQL SERVER   Wrap on Series of Temporary Statistics

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.

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

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

Step 1: Change Database status to Read-Write

USE [master]

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]

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

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