Index Statistics – DB Cloud TECH

September 18, 2015September 19, 2015 Mirza Husain Performance MSSQL

Difference between auto update statistics and auto update statistics asynchronously

Dear readers,

Please find the interview question and its answer based on statistics in SQL Server.

How to find auto update statistics and auto update statistics asynchronously options in SSMS:

Right click on your database–> Go to Properties…> options

The query optimizer uses statistics to create query plans that improve query performance.

AUTO_UPDATE_STATISTICS Option

As from snapshot above, by default its value is true.

When the automatic update statistics option, AUTO_UPDATE_STATISTICS, is on, the query optimizer determines when statistics might be out-of-date and then updates them when they are used by a query. Statistics become out-of-date after insert, update, delete, or merge operations change the data distribution in the table or indexed view. The query optimizer determines when statistics might be out-of-date by counting the number of data modifications since the last statistics update and comparing the number of modifications to a threshold. The threshold is based on the number of rows in the table or indexed view.

The query optimizer checks for out-of-date statistics before compiling a query and before executing a cached query plan. Before compiling a query, the query optimizer uses the columns, tables, and indexed views in the query predicate to determine which statistics might be out-of-date. Before executing a cached query plan, the Database Engine verifies that the query plan references up-to-date statistics.

The AUTO_UPDATE_STATISTICS option applies to statistics objects created for indexes, single-columns in query predicates, and statistics created with the CREATE STATISTICS statement. This option also applies to filtered statistics.

Auto Update Statistics Asynchronously

The default setting for this option is disabled.

The asynchronous statistics update option, AUTO_UPDATE_STATISTICS_ASYNC, determines whether the query optimizer uses synchronous or asynchronous statistics updates. By default, the asynchronous statistics update option is off, and the query optimizer updates statistics synchronously. The AUTO_UPDATE_STATISTICS_ASYNC option applies to statistics objects created for indexes, single columns in query predicates, and statistics created with the CREATE STATISTICS statement.

Statistics updates can be either synchronous (the default) or asynchronous. With synchronous statistics updates, queries always compile and execute with up-to-date statistics; when statistics are out-of-date, the query optimizer waits for updated statistics before compiling and executing the query. With asynchronous statistics updates, queries compile with existing statistics even if the existing statistics are out-of-date; the query optimizer could choose a suboptimal query plan if statistics are out-of-date when the query compiles. Queries that compile after the asynchronous updates have completed will benefit from using the updated statistics.

Consider using synchronous statistics when you perform operations that change the distribution of data, such as truncating a table or performing a bulk update of a large percentage of the rows. If you do not update the statistics after completing the operation, using synchronous statistics will ensure statistics are up-to-date before executing queries on the changed data.

Consider using asynchronous statistics to achieve more predictable query response times for the following scenarios:

Your application frequently executes the same query, similar queries, or similar cached query plans. Your query response times might be more predictable with asynchronous statistics updates than with synchronous statistics updates because the query optimizer can execute incoming queries without waiting for up-to-date statistics. This avoids delaying some queries and not others.
Your application has experienced client request time outs caused by one or more queries waiting for updated statistics. In some cases, waiting for synchronous statistics could cause applications with aggressive time outs to fail.

Thanks for reading!!

Your suggestions and likes will be appreciated

Follow Us :

LinkedIn , Slideshare ,Youtube Channel.MSDN POSTS , WHO WE ARE

August 12, 2015August 12, 2015 Mustafa EL-Masry Event

Next SQL Gulf event “SQL Gulf #2 “ at Microsoft Dubai

SQL Gulf Community reveals about its upcoming SQL Gulf event “SQL Gulf #2” at Microsoft Dubai on 5/9/2015, It is the first SQL Server event ever in the Middle East to talk about SQL Server 2016 ..!
SQL Gulf #1 was at Saudi Arabia, Riyadh on 30/8/2014 , indeed it was extremely successful one that is why we are so excited to spread the word everywhere in the Gulf through many SQL Gulf events at all Gulf cities ,interestingly said that the top notch SQL Server experts and most ever popular speakers are coming from US, UK ..etc are coming to speak to you at SQL Gulf #2 like Kevin Kline from US and Satya Shyam K Jayanty from UK ..etc and more importantly to know it is the first SQL Server event in the Middle East ever to talk about SQL Server 2016…

Session Abstracts:

1-FIRE! A Fullproof Checklist for Tuning and Troubleshooting

Session Abstract: Learning how to detect, diagnose and resolve performance problems in SQL Server is tough. Often, years are spent learning how to use the tools and techniques that help you detect when a problem is occurring, diagnose the root-cause of the problem, and then resolve the problem.
In this session, attendees will see demonstrations of the tools and techniques which make difficult troubleshooting scenarios much faster and easier, including:
•             XEvents, Profiler/Traces, and PerfMon
•             Using Dynamic Management Views (DMVs)
•             Advanced Diagnostics Using Wait Stats
•             Reading SQL Server execution plan

Every DBA needs to know how to keep their SQL Server in tip-top condition, and you’ll need skills the covered in this session to do it.
Prerequisites: Intermediate database administration and development skills, especially competence with SSMS.
Goal 1: Learn the “sieve” method of troubleshooting and problem solving, and how to make SQL Server alert you when problems arise.
Goal 2: Discover how to use wait stat analysis, as well as correlate performance information from other sources inside of SQL Server including DMVs, performance counters, and Xevent/trace information.
Goal 3: Learn how to use the most important native tools within SQL Server through live demos to successfully conduct troubleshooting and performance tuning.

Summary: Microsoft ships a multitude of tools to help detect, diagnose and resolve problems inside of SQL Server. But which is best to use and when? This session teaches attendees how to tackle the troubleshooting process to achieve repeated, optimal results.

2-What do I need to know about Data Platform Upgrade best practices & techniques

Data Platform Upgrade topic has been a popular session that I’ve presented in major conferences like Microsoft Tech-Ed (North America, Europe & India), SQLPASS, SQLSaturdays and SQLbits since the year 2008.

In this session, we will overview in depth end-to-end upgrade process that covers the essential phases, steps and issues involved in upgrading SQL Server 2000, 2005, 2008 R2 & SQL Server 2012/2014 (with a good overview on 2016 too) by using best practices and available resources.
We will cover the complete upgrade cycle, including the preparation tasks, upgrade tasks, and post-upgrade tasks. Real-world examples from my Consulting experience expanding on why & how such a solution will work in critical situations.

3-Performance Dreams started at SQL Server 2014 and come true now at SQL Server 2016

“Performance Dreams started at SQL Server 2014 and come true now at SQL Server 2016”
, it is just like this because you Microsoft launched live SQL Server 2014 with unbelievable performance reads and substantial improvement that you cannot give up them , come in here to my session and you will know more about many new features and rich powers of SQL Server 2014 regarding performance particularly like Microsoft project “Hekaton” for In-memory built in for OLTP , CCI( columnstore index) ,Resource Governor for IO consumption , lock priority management and also Single partition online index rebuild technologies ,you will get much hands-on experience for all definitions , architecture design , values and benefits ,caveats and recommendations related to each one of them so that you can drive a conscious decision for upgrading your DBs to SQL Server 2014 but keep in mind that are some limitations for those features that should be considered largely before production deployments …If not ,don’t be upset as it Is still not the end of way coz Microsoft had really rolled out SQL Server 2016 which could address successfully many of those limitations and indeed SQL Server 2014 performance dreams come true at SQL Server 2016, I will speak there simply to help those who didn’t breath SQL Server for the last little while

4-SQL Server Internals and Architecture

Session Abstract: Let’s face it. You can effectively do many IT jobs related to SQL Server without knowing the internals of how SQL Server works. Many great developers, DBAs, and designers get their day-to-day work completed on time and with reasonable quality while never really knowing what’s happening behind the scenes. But if you want to take your skills to the next level, it’s critical to know SQL Server’s internal processes and architecture. This session will answer questions like:

–       What are the various areas of memory inside of SQL Server?
–       How are queries handled behind the scenes?
–       What does SQL Server do with procedural code, like functions, procedures, and triggers?
–       What happens during checkpoints? Lazywrites?
–       How are IOs handled with regards to transaction logs and database?
–       What happens when transaction logs and databases grow or shrinks?

This fast paced session will take you through many aspects of the internal operations of SQL Server and, for those topics we don’t cover, will point you to resources where you can get more information. So strap on your silly, as we cover all these topics and more at speed with tongue planted firmly in cheek!

Prerequisites: Basic understanding of SQL Server operations and activities – such as transactions, queries, and preventative maintenance tasks like backup and recovery.

Goal 1: Learn about the major components within the SQL Server architecture, starting with the relational engine and storage engine, working down to greater and greater detail.
Goal 2: See what happens inside the SQL Server query optimizer and how the query optimizer affects transaction execution time, both read-only and read-write transactions
Goal 3: Review the major operational processes inside of SQL Server that affect memory management and IO activity.

5-Power BI deployment best practices to deliver data analytics to the business

How to deploy Power BI, how to implement configuration parameters and package BI features as a part of Office 365 roll out in your organisation.

Having said that, cloud computing is another aspect of this technology made is possible to get data within few clicks and ticks to the end-user. Let us review how to manage & connect on-premise data to cloud capabilities that can offer full advantage of data catalogue capabilities by keeping data secure as per Information Governance standards. Not just with nuts and bolts, performance is another aspect that every Admin is keeping up, let us look into few settings on how to maximize performance to optimize access to data as required.

Gain understanding and insight into number of tools that are available for your Business Intelligence needs.

There will be a showcase of events to demonstrate where to begin and how to proceed in BI world.

April 23, 2015April 23, 2015 Mustafa EL-Masry SQL Server 2014

SQL Server 2014 Health check DMV queries Day 2

Hi guys in the Previous post i explained How we can get the SQL Server instance version and How we can know the installed date for SQL Server instance put today is DAY 2 and i will show new DMV in our track Instance Level configuration Queries

DMV#3 Server Properties

below DMV is more helpful and useful this DMV This gives you a lot of useful information about your instance of SQL Server like (Computer name , ProcessID , SQL Server collation , Product level…etc)

DMV#4 SQL Server Agent Job Information

below DMV it will return for us basic information about SQl Server agent and the user name configure it to review it one by one.

DMV#5 SQL Server Agent Alert Information

by the below DMV we will return the basic information about SQL Server agent alerts

Check the Full posts and the scripts : http://sqlserver-performance-tuning.net/?p=5924

To check all my posts you can direct to my personal website or you can download this Excel sheet My Technical POSTS

View all my tips , LinkedIn , Website , Slideshare ,Youtube Channel.

April 16, 2015 Mustafa EL-Masry SQL Server 2014

SQL Server 2014 Service Pack 1 has released

Congratulation Microsoft and big Congratulation for us (SQL Server Releases blog)

One year ago, Microsoft launched SQL Server 2014. Today, April 15, Microsoft are pleased to announce the release of SQL Server 2014 Service Pack 1 (SP1). The Service Pack will be available for download on the Microsoft Download Center.

SQL Server 2014 SP1 contains fixes provided in SQL Server 2014 CU 1 up to and including CU 5, as well as a rollup of fixes previously shipped in SQL Server 2012 SP2. For highlights of the release, please read the Knowledge Base Article for Microsoft SQL Server 2014 SP1.

As part of our continued commitment to software excellence for our customers, this upgrade is available to all customers with existing SQL Server 2014 deployments via the download links below. SQL Server 2014 with SP1 will be available in additional venues including the Volume Licensing Center and via Microsoft Update starting May 1, 2015.

Microsoft® SQL Server® 2014 SP1
Microsoft® SQL Server® 2014 SP1 Express
Microsoft® SQL Server® 2014 SP1 Feature Pack

To check all my posts you can direct to my personal website or you can download this Excel sheet My Technical POSTS

View all my tips , LinkedIn , Website , Slideshare ,Youtube Channel.

February 2, 2015 Mustafa EL-Masry General topics

Index Statistics for all Databases Exists on your Server

How to return the unused index on all databases? How to save index Statistics? in the previous post i explained How to check the (unused index , duplicated index , missed index) but in one single database yesterday I updated the script of Unused index to can run on all databases exists in the server and by this new DMV I can save the result (index Statistics) in the table because if you do restart for SQL Server or do failover for the cluster you will lose the Index Statistics but by the below steps you can save it easily and return for it in any time to check all My posts and my Friends posts in SQL Server Index GO HERE

Steps for Saving index Statistics:

Create Table Index_Statistics on MSDB database

USE [msdb]
GO

/****** Object: Table [dbo].[Index_Statistics] Script Date: 01/02/2015 01:58:31 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Index_Statistics](
[ID] [INT] IDENTITY(1,1) NOT NULL,
[Database_name] NVARCHAR(200),
[ObjectName] [sysname] NOT NULL,
[IndexName] [sysname] NULL,
[IndexID] [INT] NOT NULL,
[UserSeek] [BIGINT] NOT NULL,
[UserScans] [BIGINT] NOT NULL,
[last_user_seek] [DATETIME] NULL,
[last_user_scan] [DATETIME] NULL,
[UserLookups] [BIGINT] NOT NULL,
[UserUpdates] [BIGINT] NOT NULL,
[TableRows] [BIGINT] NULL,
[drop statement] [NVARCHAR](790) NULL,
[Execution_time] [DATETIME] NOT NULL,
PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON,FILLFACTOR=80,DATA_COMPRESSION=PAGE) ON [PRIMARY]
) ON [PRIMARY]

Create Stored procedure Unused index

USE msdb
Go

Create PROC UnusedIndex
AS
BEGIN
SET NOCOUNT ON
DECLARE @name NVARCHAR(200)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN (‘master’,’model’,’msdb’,’tempdb’)

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @SQL NVARCHAR(MAX)=N’
Use ‘+@name+’
SELECT ”’+@name+”’ As Database_name,
o.name AS ObjectName
, i.name AS IndexName
, i.index_id AS IndexID
, dm_ius.user_seeks AS UserSeek
, dm_ius.user_scans AS UserScans
,dm_ius.last_user_seek
,dm_ius.last_user_scan
, dm_ius.user_lookups AS UserLookups
, dm_ius.user_updates AS UserUpdates
, p.TableRows
, ”DROP INDEX” + QUOTENAME(i.name)
+ ” ON ” + QUOTENAME(s.name) + ”.” + QUOTENAME(OBJECT_NAME(dm_ius.object_id)) as ” DROP STATEMENT ”
,GETDATE() AS Execution_time
FROM sys.dm_db_index_usage_stats dm_ius
INNER JOIN sys.indexes i ON i.index_id = dm_ius.index_id AND dm_ius.object_id = i.object_id
INNER JOIN sys.objects o on dm_ius.object_id = o.object_id
INNER JOIN sys.schemas s on o.schema_id = s.schema_id
INNER JOIN (
SELECT SUM(p.rows) TableRows, p.index_id, p.object_id
FROM sys.partitions p GROUP BY p.index_id, p.object_id
) p
ON p.index_id = dm_ius.index_id AND dm_ius.object_id = p.object_id
WHERE OBJECTPROPERTY(dm_ius.object_id,”IsUserTable”) = 1
AND dm_ius.database_id = DB_ID()
AND i.type_desc = ”NONCLUSTERED”
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
ORDER BY o.name ASC , I.name ASC ‘
EXEC SP_ExecuteSQL @SQL
FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor
END

Create Job to Execute the unused index (optional)

Because if you create the job I recommended to run it manually not schedule it because our target here to save the index statistics before restart the server or failover it so after this happened I should not overwrite on my data exists in index Statistics table .

Follow the author:

View all my tips , LinkedIn , Website , Slideshare