Continuing with our Performance Tuning series as you have learned baselining and knowing your normal system performance metrics is an important part of performance tuning. In part 1 we discussed collecting vital system metrics such as CPU Utilization and Memory usage. In part 2 we took a glimpse into how we can gather those metrics and other using the different tools that are available to use like Performance Monitor, Profiler, Extended Events and DMVs. In this part 3 we will look at how identifying the appropriate waits events and corresponding resolution will be critical for resolving performance issues. But first you need to understand what waits are and where to find the information needed to identify waits in your system.

When a thread is being executed and is forced to wait on something this is called queueing. The thread waits for system resources, lining up in a first come first served fashion waiting for things like a CPU scheduler. The time waiting between actively running and suspended (waiting) states is the measurable wait event that SQL Server keeps track of via using. internal metrics. The database engine tracks why executed waits must wait and how long they are queuing for resource threads. These are commonly called wait statistics. These metrics are easily discernible using the dynamic management view (DMV) sys.dm_os_wait_stats (or in Azure SQL Database sys.dm_db_wait_stats).  This information is important to understanding what baseline performance of your database and can help you identify specific performance issues both with query execution and hardware limitations. Data gained from this view can be aggregated and tracked to provide a clearer picture on what might be causing the executed thread to wait. Knowing the top waits that occur in your environment is a key task for all DBAs. Below you will learn common actionable waits as well as those that are benign and occur normally as part of SQL Servers thread queueing process.

Check out the full part 3 article on the Idera Community Site.

https://community.idera.com/database-tools/blog/b/community_blog/posts/identifying-performance-problems-part-3

The post Identifying SQL Server Performance Problems Part 3 appeared first on A Shot of SQLEspresso.

When you are making changes to an existing Non-Clustered index SQL Server provides a wide variety of options. One of the more frequently method is DROP EXISTING; in this post you will learn all about that option. This option automatically drops an existing index after recreating it, without the index being explicitly dropped. Let us take a moment understand the behavior of this choice.

DROP EXSITING=ON which is my preferred method, will DROP the current index only after it finishes creating and building the index with the new definition. The pitfall is that if the index does not exist, you will get an error and must create it without the option or set it to OFF. However, the more important benefit of using this one is all about performance. The index will still be used by active queries until it is rebuilt with the new definition.

CREATE NONCLUSTERED INDEX [dcacIDX_ServiceType] ON [dbo].[Accounts]
(
       [ServiceType] ASC
)

INCLUDE([AccountId]) WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = ON, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

If index does not exist, you will get a 7999 error.

Msg 7999, Level 16, State 9, Line 1

Could not find any index named ‘dcacIDX_ServiceType’ for table ‘dbo.Accounts’.

There are a few exceptions to keep in mind per docs.microsoft.com.

With DROP_EXISTING, you can change:

• A nonclustered rowstore index to a clustered rowstore index.

With DROP_EXISTING, you cannot change:

• A clustered rowstore index to a nonclustered rowstore index.
• A clustered columnstore index to any type of rowstore index.

DROP and CREATE

This option is a cleaner and wont error if the index doesn’t already exist. However, I caution you when using this especially when it is a large table. Using this option drops the index before it creates the new, leaving your system without the previous index definition. This can create a huge performance issue while the system waits for the new index to be created. I know this firsthand, as I did this with a client a few years ago, during the day while trying to fix a performance issue. I created a worse issue while the waiting for the new one to be created. It took 45 mins to create the new index with the new definition which caused CPU to spike to 100% while active queries were trying to come through. Which sadly, in turn, slowed down the new index creation.

DROP INDEX IF EXISTS [dcacIDX_ServiceType] ON [dbo].[Accounts]

GO

CREATE NONCLUSTERED INDEX [dcacIDX_ServiceType] ON [dbo].[Accounts]

(
       [ServiceType] ASC    
)

INCLUDE([AccountId] WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

GO

Now I should also note that the DROP_EXISITING method is also faster when you must modify a Clustered index. Every Non-Clustered index refers to the Clustered index using what is called a clustering key, essentially, a pointer to the row in the clustered index. When a clustered index is dropped and re-created, SQL Server must rebuild the Non-Clustered indexes on that table. In fact, it gets done twice by actually rebuilding them upon drop and rebuild again on the create of the Clustered index. Using DROP_EXISTING=ON prevents you from having to rebuild all these indexes as their keys will stay the same, thus making it significantly faster.

The reason I took the time to write this quick blog is to remind those to consider using the DROP EXSITING=ON rather than using the DROP and CREATE method when possible. Do not introduce a performance issue when you can avoid it and you can more efficiently make the desire changes you need.  Just a friendly reminder.

The post SQL Index Creation Using DROP EXISTING= ON appeared first on A Shot of SQLEspresso.

Performance Monitoring Tools

In this second post we are diving into what tools you can use to capture the important performance metrics you need to gather for baselining your environment. Be sure to read part one first.

Performance Monitor

First let’s start with what comes native to Windows Server platform, whether you are on an Azure SQL Virtual Machine or your standard SQL Server instance install on premises you have a native tool, called Performance Monitor (affectionately known to most as PerfMon). This reliable tool allows you to easily monitor performance metrics through a Graphical User Interface (GUI), by selecting performance counters for the operating system as well as installed applications such as SQL Server. It groups these metrics for ease use by category and application specific counters. It is one of the tools best used for collecting those baseline statistics for your system.

Check out the full part 2 article on the Idera Community Site.

https://community.idera.com/database-tools/blog/b/community_blog/posts/identifying-performance-problems-part-2

The post Identifying SQL Server Performance Problems Part 2 appeared first on A Shot of SQLEspresso.

I regularly deliver a presentation called “Mastering TempDB”. I wrote it based on what I wish I knew about TempDB “growing up” in my career as a SQL Server DBA. It really is a breakdown of what TempDB is, how it is used, what problems actually occur within it, and how to best configure it. With advice from my colleagues, I have decided to break that down into this article to educate you.

The post Mastering TempDB: The Basics appeared first on A Shot of SQLEspresso.