SQL, Code, Coffee, Etc. - Data Musings by Marlon Ribunal

02/06/2018
by Marlon Ribunal
Comments Off on Waiting Tasks Reveal Pain Points

Waiting Tasks Reveal Pain Points

Waiting tasks quickly shows you where to look for the bottlenecks. This is only one of the many times that I didn’t have to go beyond identifying the waiting tasks to quickly troubleshoot a production performance issue.

I have encountered a case where an application filter got stuck in loading status and not returning any data (well, it’s stuck). This was impacting the overall performance of the application. Normally, I would start with my simple heuristic troubleshooting method to get an idea of what’s going on in the backend. But since the issue is quite specific and the application module is identified in this case, I can fairly conclude that the reason for the stuck procedure is due to requests waiting for resources or whatever they are waiting for.

Waiting Tasks

Thanks to Paul Randal (B | T) for his sys.dm_os_waiting_tasks script. Running that script revealed something like the Waiting Tasks you see below (snapshot only, showed different wait time, status and waits at different points):

Paul was even kind enough to share a quick insight into what could possibly be causing all these locks:

Looks like you’ve got an offline index operation happening maybe. Something holding a SCH_M lock.

— Paul Randal (@PaulRandal) February 2, 2018

Symptoms

So, that’s the first thing that I checked. Using Paul’s comprehensive SQL Server Wait Types Library, I looked for the definition of the waits that popped out:

IO_COMPLETION – “This wait type represents a variety of synchronous read and write operations in data files are not related to tables, plus reads from the transaction log.” From the snapshot returned by the query, I couldn’t really make anything out of this wait. From what I saw, this wait is incurring lots of reads and CPU resources. The SPID was running for more than 4 hours at this point. I looked up for more info on this wait, and it usually occurs in any of the following situations: Long-running I/O-bound operations, including BACKUP, ALTER DATABASE, CREATE DATABASE or database auto-growth. There were no such database maintenance operations within that timeframe though.

What the Waiting Tasks revealed, though, was that the top SPID wasn’t stuck. The task was still running but it’s just taking time and, therefore, taking substantial resources with it. At the rate it was going, it could have run for another 4-6 hours which could bring the SQL Server to its knees and render the application unusable from that point on.

LCK_M_SCH_S – “This wait type is when a thread is waiting to acquire a Schema Stability (also called Schema Share) lock on a resource and there is at least one other lock in an incompatible mode granted on the resource to a different thread.” This wait on the SPID shown in the query snapshot is quite obvious. This is a lock caused by the SPID on top of the chain.

LCK_M_IS – “This wait type is when a thread is waiting to acquire an Intent Shared lock on a resource and there is at least one other lock in an incompatible mode granted on the resource to a different thread.” Again, just like the above lock wait, this is caused by what’s on top of the blocking chain.

Findings

So what could have possibly caused the above waits? The primary cause of the blocking chain is the SPID on top that was on that IO_COMPLETION wait at the time the query snapshot was taken (see screen capture above). One of the stored procedures involved in this issue is writing data into a text file (orders). I cannot say that has something to do with the IO_COMPLETION.

I checked the metrics in Solarwinds DPA, and it showed a somewhat high signal wait percentage around the time this issue has occurred. The percentage around the same time was about 42%. Solarwinds has an info about this signal wait percentage that says “Anything over 20% would indicate that there is a possible CPU resource bottleneck.”

Fix

The spike in resource demand due to high usage of the application at the same time the database was writing data into the text files can certainly affect the CPU resources. Plus, the top SPID that caused all these blocking did not only incur most of the resources but also blocked a table that was also used by the other stored procedures down the chain, thus that explained the LCK_M_SCH_S & LCK_M_IS waits.

At that point, the action to take to fix the performance issue was becoming obvious. The SPID on top of the blocking chain didn’t seem to indicate that it would complete soon. There was only one command to make the application work again: KILL SPID.

Once the KILL command was issued against the top SPID, all the other dependent locks were released and the application was performing well again almost instantly after that.

But of course, you have to take extra precautions before you pull the plug on any task. If I kill the task, will it spawn another data issue? Will it cause other processes to fail? That’s your call.

KILL has saved the day again.

01/30/2018
by Marlon Ribunal
2 Comments

Layman’s Guide to Performance Tuning

Performance tuning is one of those things that is hard to get right when you do not have an established methodology of how to approach the problem.

Consider this scenario.

The application users are complaining about their app being so slow and explaining further that they didn’t do anything out of the ordinary. You confirmed with the dev team that they didn’t release any changes to the underlying data structure that could have caused the slow performance of the application.

This might just be one of those glitches that go away if you just ignore them, you thought to yourself. Although you very well know that this time it’s not the case. What makes this bad is, one, you’re not a DBA; and, two, which makes it worse, you do not have a DBA at all.

And since you are the go-to guy/gal whom everybody asks for anything and everything about technical, you are now tasked to troubleshoot this performance issue. Congratulations! You’ve just earned your official accidental DBA badge!

It’s easy to get overwhelmed with the number of things that you have to look into to get to the bottom of things. Is it a disk issue? Network issue? Is it SQL Server itself? Or, code issues?

If you are like most accidental DBA’s or those that are forced to manage their SQL Server databases, you can establish a heuristic approach to performance tuning:

Task Manager: CPU / Network / Memory

The first thing that I might look into is the CPU performance. This is for a simple reason – accessibility of this information. I can just fire up the Task Manager and see if there is any pressure on the CPU of the application server.

Is the CPU maxed out? What are the processes that are hogging the CPU? The task manager could also give me a peek at what’s going on with the Network and Memory utilization. This is where baselining is critical. You must have a baseline of how your CPU, Network, and Memory look like during your busiest time of the day. From here, you can have an idea if one of these may be an issue.

So, if there is an abnormal spike in CPU, I may want to check which processes are consuming most of the CPU.

Windows Event/Application Viewer

Just like the Task Manager, the Windows Event Viewer holds rich information about your application server. You may want to check if there are errors that occurred at the same time the application was experiencing slow performance. So, I would check the Application Log and even the System Log for any significant Error or Critical Events such as failed processes.

You can create a Custom View for specific applications or services that you want to track.

sp_whoisactive

So far, the two tools above may at least give you a hint of what’s going on behind the scene; or, hopefully, they will lead you to the culprit or culprits of the slow performance issue. If you think that neither the application nor the server itself is the source of the problem, you may want to ask, what is currently running on the SQL Server.

A simple sp_whoisactive query might reveal a long-running stored procedure:

EXEC sp_whoisactive @get_locks = 1 , @get_task_info = 2;

And I would run a quick check on any of the session ID’s that might be of interest:

DBCC INPUTBUFFER(13);

Are there any blocking issues? Deadlocks maybe? Do you have a session that’s been running for say, 6 hours? Are there any orphan sessions, like runaway sessions that kept running even after the process that called them have already been terminated?

If it’s a blocking issue, check the blocker and its victim. Well, if the worst comes to the worst:

KILL 13;

Dynamic Management Views

Or if you are using SQL Server 2016 or newer, you can use the new DMV sys.dm_exec_input_buffer instead of the DBCC command above:

SELECT * FROM sys.dm_exec_input_buffer(13,0)

You can join this info with other dynamic view objects to get more information about the running processes, like:

SELECT r.session_id,
 r.start_time,
 t.text,
 r.status,
 r.blocking_session_id
FROM sys.dm_exec_requests r
 CROSS APPLY sys.dm_exec_input_buffer(r.session_id, 0)
 CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.session_id IN ( 12, 13, 14 );

SQL Profiler / Trace / Extended Events

Hopefully, at this point, the above options might have led you to the problem and the root cause of that problem. If not, then you can probably capture a trace of the event or processes using SQL Server Profiler. Please take note, though, that SQL Server Profiler is now deprecated in SQL Server 2016 and replaced with Extended Events.

Next Steps

Well, if none of those work, then the next step would be to dive deeper into the following:

Waits – Wait Stats can show you where it hurts.

Statistics – Outdated SQL Server Statistics can cause performance issues.

Indexes – Index is not always the answer for slow queries but they are a big deal in SQL Server or any other RDBMS for that matter. Sp_BlitzIndex is a good tool to check if you have sufficient and effective index structures.

Execution Plans – This is a good tool to diagnose your queries. If you want insights into your queries and the cost they incur at every step of the way, then understanding how the Execution Plans work is critical.

IO’s and Memory Grants – If you’re feeling techy, you may dig deeper and look into IO Latency and pending memory grants.

I hope that helps. If you have tips and tricks about performance tuning, you can share them below in the comments.

Enjoy!

01/23/2018
by Marlon Ribunal
1 Comment

My Favorite SQL Prompt Features

SQL Prompt from Redgate Software is one of the tools that I cannot live without. Well, that’s probably an exaggeration but I use it daily in my job. Whether I’m writing stored procedures or crunching TSQL codes to troubleshoot data issues in SQL Server, I depend on it to show me the things that I need – and even the things that I don’t think I need. It’s the IntelliSense everyone needs for productivity.

The features and code-complete capabilities of SQL Prompt makes a productive SQL Developer or DBA more efficient. I think I have told my co-workers more often than I should that I couldn’t write TSQL without it. In fact, I wonder how anyone could do their job productively and efficiently without it or any other type of IntelliSense tool for that matter. SQL Prompt is just the best code completion and refactoring tool on the market.

A little disclaimer, though. I am part of the Friends of Redgate Program (FoRG). I have long been a fan of SQL Prompt even before I became part of FoRG and, I think, even before Redgate acquired the tool (If my memory serves me right, it was not an original product of Redgate, or maybe that was something else).

I like all the features of SQL Prompt, but these are the few that I like most.

Code Analysis

The Code Analysis feature guides you to writing better codes and avoiding common pitfalls of code smell. This is how it works. As you type, the tool scans your code against a set of rules and mark the lines that violate these rules. An explanation of each rule is provided and also some suggestions on how to improve your code. Most important of these, in my opinion, are the Deprecated Rules that warn you of, well, deprecated objects.

As the analysis is performed in real-time, you can correct your codes as you write. As of this writing, you cannot create custom rules but the existing ones are good enough to ensure that you are writing efficient codes. Other rules are Best Practice, Execution, Performace, and many others.

Snippets Template Parameter

You probably have a set of scripts that you often run, like troubleshooting scripts or query. My favorite feature is using snippets with template parameters. Say, you have a script that has a variable. Normally, you would hardcode the variable or leave it empty when writing the snippet. When invoked, the snippets allows you to specify the value of the parameter.

Here’s an example. Create a snippet and add the parameter placeholder with format <Parameter, Type, Value>.

SELECT ProductName, ProductDescription FROM Product WHERE Price > <price, smallmoney,100.00>

When the snippet is invoked, a form pops up:

Table Alias

This is a good way to be consistent with table aliasing. Consistent table aliases across the company is also a best practice to maintain. You can set object aliases in the options menu.

Encapsulate as New Stored Procedure

So you’ve written a long TSQL code and decided to convert it to a stored procedure. You can do that on the fly by using the Encapsulate as New Stored Procedure wizard. Here’s an example of the generated script.

/*
Script created by SQL Prompt version 9.0.7.3692 from Red Gate Software Ltd at 1/20/2018 10:57:28 PM
Run this script to create the encapsulated stored procedure.

Please back up your database before running this script.
*/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE dbo.ProductPrice AS
BEGIN
SELECT ProductName, ProductDescription
FROM dbo.Product 
WHERE Price > 200.00
END

GO

Custom Style

You can use Custom Styles to enforce Coding Conventions. You can create styles from scratch or use one of the existing styles.

You have an option to preview your current query with the style selected.

SQL Prompt is not only a productivity tool. You can also use it to enforce Best Practices and conventions within your organization. What is your favorite feature? Share them in the comment below.