RSS

Category Archives: Performance MSSQL

Query Store for Solving Query Performance Regressions

Query Store for Solving Query Performance Regressions

Query performance is a very important area of SQL server. We always have badly performance queries around.

Query store is the newest tool for tracking and resolving performance problems in SQL server.

In this article, we are going to have a look at some practical uses of SQL server Query store.

What is Query Store?

The query store has been described by Microsoft as a ‘flight data recorder’ for SQL server queries. It tracks the queries run against the database, recording the details of the queries and their plans and their runtime characteristics. Query store is per database feature and runs automatically. Once turned on, nothing further needs to be done to get it to track data. It runs in the background collecting data and storing it for later analysis.

Query store is available in SQL Server 2016 and later, and Azure SQLDB v12 and later. It is available in all editions of SQL server, even in Express edition.

How is Query store different from other tracking options?

We have had query performance tracking for some time though in the form of dynamic management views. Mostly, sys.dm_exec_query_stats and sys.dm_exec_query_plan and tracing tools like SQL server profiler and extended events.

So, what makes Query Store different? Let me start answering that by describing a scenario that I encountered a couple of years ago.

A particular critical system was suddenly performing badly. It had been fine the previous week and there have been no extended events sessions or profiler traces running historically. The admin had restarted the server when the performance problem started, just to make sure it was not something related to a pending reboot.

As such, there was no historical performance data at all and solving the problem of what happened, why the query performance is different this week was extremely difficult.

1

Read the rest of this entry »

 

Tags: ,

Improve SQL Server Performance with Compression

Improve SQL Server Performance with Compression

SQL Server data compression is now available in all editions of SQL Server, starting with 2016 SP1.

In this Article, you will not only know how data compression will save space, you’ll also find out how compression can sometimes improve performance as well.

Space Savings vs. Performance

When I first heard about compression back in 2008, my first thought is that it would have a big performance penalty. Compression would save disk space, but it would probably decrease performance as the data was compressed and decompressed. It turns out that compression can improve performance instead. Because compressed data fits in a smaller number of data pages, there are decreased I/O requirements. Since I/O is generally the bottleneck in SQL Server, this can improve performance. Compressed data also has a decreased memory requirement. When querying compressed data, a smaller number of pages will be copied to the buffer pool. The one area that is impacted is CPU. You do need to have some CPU headroom because compression will require some additional CPU resources.

a1

The good thing is that if the workload is reasonably tuned, many SQL Server instances have more CPU resources than they need. One note of caution here. Don’t look to compression as the solution to solving major performance issues. You need to look at physical resources, configuration, indexing, and query tuning. The point I’m trying to make is that decreased I/O and better memory utilization will benefit the workload in many cases.

Read the rest of this entry »

 

Tags: , , , ,

Dissecting SQL Server Execution Plans

I remember my days before, Microsoft SQL Server PFE.  I wanted to learn everything and know everything about SQL Server.  However, getting hold of good resources was tough, as I didn’t have any mentor when I started down my journey to becoming a SQL Server Database Administrator. 

Along the way I did pick up lots of books and references.  One of such books is Dissecting SQL Server Execution Plans. 

I read this book before becoming PFE, I read this now, and I recommend everyone read this book more then once. 

Grant Fritchey (Blog|Twitter) wrote the book back in 2008; I would still recommend take ready.  This book will only help you be better DBA and Developer.

SQL Central, Jeff Moden, Dissecting SQL Server Execution Plans

http://www.sqlservercentral.com/articles/Book+Reviews/69019/

Amazon, SQL Server Execution Plans

http://www.amazon.com/gp/product/1906434026?ie=UTF8&tag=dkranchnet&linkCode=as2&camp=1789&creative=390957&creativeASIN=1906434026

SQL Central, Red Gate, EBook

http://www.sqlservercentral.com/articles/books/65831/?utm_source=ssc&utm_medium=weblink&utm_content=Grant&utm_campaign=sqltoolbelt

This post is cross posted on my SQLCAN Blog, MSDN Blog, and SQL server Consultation blog.

 
Leave a comment

Posted by on June 1, 2016 in Performance MSSQL

 

Tags: ,

Index Creation Date

Often when troubleshooting performance related issues, it is beneficial to know when new indexes were introduced.  However currently there is no easy way to keep track of this information or attain this information.  That’s why one of my colleagues and a good friend, Mohamed Sharaf (Blog | Twitter), has submitted a request on Microsoft Connect, Expose index creation date in sys.indexes, Link.  Please take a few minutes to vote if you agree Smile.

This post is cross posted on my SQLCAN Blog, MSDN Blog, and SQL server Consultation blog.

 
Leave a comment

Posted by on October 19, 2015 in Performance MSSQL

 

Tags: ,

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

Stats

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

 
Leave a comment

Posted by on September 18, 2015 in Performance MSSQL

 

Tags:

Check and Enable Automatic Statistic Update on Database Level

AS SQL Server Best Practices: Auto-Create and Auto-Update Statistics Should Be On – Most of the Time in general they are a very good thing for performance. You could try to figure out which columns need statistics, but it’s often better to let SQL Server do that for you.You can turn on the AUTO_CREATE_STATISTICS database option and SQL Server will automatically determine when it needs the Statistics and create them for you.

Check And Enable Automatic Statistic Update on all databases have this option is disable

Select ‘ALTER DATABASE [‘+Name+’] SET AUTO_CREATE_STATISTICS ON;
GO
ALTER DATABASE [‘+Name+’] SET AUTO_UPDATE_STATISTICS ON;
GO
‘ from Sys.Databases where is_auto_update_stats_on = 0 or is_auto_create_stats_on = 0

Take the Result and Execute it in another session

Update Statistics

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

View all my tips , LinkedIn Slideshare ,Youtube Channel.My posts in MSDN

 

Tags: , , ,

Bookmark lookup , Key lookup , RID lookup in SQL Server

Introduction : 

Today i will Show  :

What  is the Meaning of Bookmark lookup , Key lookup , RID lookup in SQL Server ?

when SQL Server Retrieve Bookmark lookup , Key lookup , RID lookup ?

How can we Remove the Bookmark lookup , Key lookup , RID lookup ?

Bookmark lookup , Key lookup , RID lookup Meaning  and when SQL Server Retrieve it ?

Read the rest of this entry »

 
Leave a comment

Posted by on June 17, 2013 in Index, Performance MSSQL

 
 
%d bloggers like this: