SQL SERVER – Life of a SQL Query – Query States

This is the guest blog post written by James Davies, Sales Engineer, Confio Software, a SolarWinds company. It is written based on the Database Performance Analyzer. I encourage you to download it for free from here.

From the moment a query request is made until the query response is delivered, what really happens to your query in SQL Server? Let’s find out, and talk about why that matters to you.

Put simply, queries in SQL Server can exist in one of three states while executing inside the database engine. These states are defined as:

  • RUNNING–meaning that the query is actively running on the CPU
  • RUNNABLE–meaning that the query is ready to run but CPU resources are not yet available. It is waiting in the Runnable Queue for a CPU to open up
  • SUSPENDED–meaning that the query is waiting for a third party resource to become available (for example,, disk I/O, blocking and so on)

In an overly simplified way, it’s a bit like the checkout line at the grocery store, in which each shopper has just one item. The cashier is the CPU and the item the checker is actively scanning is like a query that is running. The shoppers waiting in line, each with their own item, are like runnable queries, just waiting to be scanned and checked out. Let’s say that when the checker scans your item, the price doesn’t come up, and a price check is called. The checker asks you to step aside, and wait for the price check to complete. When the price comes back, the checker asks you to step back in line, all the way at the end of the line. That’s like a suspended query (and that’s not something you want to happen to your query!). If there are four checkout lines running, then that would be a bit like having four CPU cores, each with their own set of queries in various states of running, runnable and suspended.

So then what is a wait type? In SQL Server wait types are simply a more detailed explanation of the suspension state. As DBAs, we don’t like to see queries in the suspended state, and want to find out just what is causing the query not to process.

When you look at wait statistics, you want to see queries spending the majority of their time in a running or runnable state. You can also use wait statistics to discover why a query is in a suspended state. If you can answer that question, you can fix the problem so that the query is mostly in a non-suspended state. And this is why understanding your query wait statistics is key to maximizing query performance.

You can look at wait statistics using SQL Server Management Studio (SSMS) or third-party tools such as SolarWinds Database Performance Analyzer (DPA). DPA provides simple, fast and visual identification of query wait statistics correlated with system resources, including historical and trend information, so that you can easily pinpoint root cause of query performance issues.


Database Performance Analyzer (formerly Confio Ignite) Shows Top Waits and Which Resources Are Being Waited On

Whichever tools you use, understanding query states and wait statistics is the foundation to a good proactive database performance practice.

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

About these ads

SQL SERVER – 28 Links for Learning SQL Wait Stats from Beginning

It has been exact two years since I have written series on SQL Wait Statistics and Queues. I often received quite a few questions related to this subject. Here are my answers to the questions.

Q: The series which you have written two years ago, is it still relevant to latest SQL Server?
A: Yes, absolutely. Everything which I have written earlier is still relevant to the latest version of SQL Server. The matter of the fact, most of it will remain relevant forever.

Q: Is there anyway, I can read everything together in an eBook format?
A: Yes, you can get SQL Wait Stats on Kindle over here.

Q: Is SQL Wait Stats a good logical starting point for SQL Performance Tuning?
A: I believe so. It gives you a good idea where exactly your bottleneck is in your server.

Q: I have previously not learned about SQL Wait Stats, can I start now?
A: Absolutely, Yes, here are the links:

SQL SERVER – Introduction to Wait Stats and Wait Types – Wait Type – Day 1 of 28

SQL SERVER – Signal Wait Time Introduction with Simple Example – Wait Type – Day 2 of 28

SQL SERVER – DMV – sys.dm_os_wait_stats Explanation – Wait Type – Day 3 of 28

SQL SERVER – DMV – sys.dm_os_waiting_tasks and sys.dm_exec_requests – Wait Type – Day 4 of 28

SQL SERVER – Capturing Wait Types and Wait Stats Information at Interval – Wait Type – Day 5 of 28

SQL SERVER – CXPACKET – Parallelism – Usual Solution – Wait Type – Day 6 of 28

SQL SERVER – CXPACKET – Parallelism – Advanced Solution – Wait Type – Day 7 of 28

SQL SERVER – SOS_SCHEDULER_YIELD – Wait Type – Day 8 of 28

SQL SERVER – PAGEIOLATCH_DT, PAGEIOLATCH_EX, PAGEIOLATCH_KP, PAGEIOLATCH_SH, PAGEIOLATCH_UP – Wait Type – Day 9 of 28

SQL SERVER – IO_COMPLETION – Wait Type – Day 10 of 28

SQL SERVER – ASYNC_IO_COMPLETION – Wait Type – Day 11 of 28

SQL SERVER – PAGELATCH_DT, PAGELATCH_EX, PAGELATCH_KP, PAGELATCH_SH, PAGELATCH_UP – Wait Type – Day 12 of 28

SQL SERVER – FT_IFTS_SCHEDULER_IDLE_WAIT – Full Text – Wait Type – Day 13 of 28

SQL SERVER – BACKUPIO, BACKUPBUFFER – Wait Type – Day 14 of 28

SQL SERVER – LCK_M_XXX – Wait Type – Day 15 of 28

SQL SERVER – Guest Post – Jonathan Kehayias – Wait Type – Day 16 of 28

SQL SERVER – WRITELOG – Wait Type – Day 17 of 28

SQL SERVER – LOGBUFFER – Wait Type – Day 18 of 28

SQL SERVER – PREEMPTIVE and Non-PREEMPTIVE – Wait Type – Day 19 of 28

SQL SERVER – MSQL_XP – Wait Type – Day 20 of 28

SQL SERVER – Guest Posts – Feodor Georgiev – The Context of Our Database Environment – Going Beyond the Internal SQL Server Waits – Wait Type – Day 21 of 28

SQL SERVER – Guest Post – Jacob Sebastian – Filestream – Wait Types – Wait Queues – Day 22 of 28

SQL SERVER – OLEDB – Link Server – Wait Type – Day 23 of 28

SQL SERVER – 2000 – DBCC SQLPERF(waitstats) – Wait Type – Day 24 of 28

SQL SERVER – 2011 – Wait Type – Day 25 of 28

SQL SERVER – Guest Post – Glenn Berry – Wait Type – Day 26 of 28

SQL SERVER – Best Reference – Wait Type – Day 27 of 28

SQL SERVER – Summary of Month – Wait Type – Day 28 of 28

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

SQL SERVER – Learning More About SQL Wait Stats

I was recently visiting my customer for my day job in Delhi. During the meeting, I had a great time to meet a few of the technical domain experts of the leading organization. We discussed lots of different things while we were there and much of the conversation was surrounding to SQL Server and overall direction of Big Data world. While we were discussing about pretty much everything one of the attendees of the meeting asked a question about SQL Wait Stats. Here is the question in his own words:

Attendee: “Is SQL waiting Stats still relevant for performance tuning? If yes, where should we start learning about it.”

Pinal: “Absolutely. SQL Wait stats are always going to be there as a one of the most promising ways to know what is the real bottleneck of the server. You can run the diagnostic query on your server and immediately know what was  causing your query to wait.”

Attendee: “Where do we start?”

I love this question as SQL Wait Statistics are my favorite topic. I have earlier written about SQL Wait Stats in my 28 day series over here: SQL SERVER – Summary of Month – Wait Type – Day 28 of 28. I strongly encourage to bookmark the page to read more about the wait stats.

Pinal: “Read my blog posts on this subject as they gives you quite a good start on this subject.”

Attendee: “Can you help us with an analysis of the query for our server?”

Pinal: “Sure, I can. Execute following query on your server and send me the output in Excel format. I will reply back to you very soon with my findings.”

SELECT *
FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC
GO

Attendee: “Thank you. However, we have more than one server, honestly we have over 100’s of server in our system and we can’t just manually do that.”

Pinal: “True, we can’t do that manually for you all of the 100’s of servers. If that is the case, I suggest you use professional performance tuning tool.”

Attendee: “Is it Free? Because…”

Pinal: “Sure it is FREE – You can download  download Spotlight on SQL Server Freemium right away and check your various wait stats. It even sends alerts if the health of your server goes down.”

Attendee: “Good. I will try now.”

Pinal: “One more thing – if you want to learn more you can watch Paul Randal’s SQL Wait Stats course on Pluralsight as well. It is the most comprehensive video course available online from most renowned SQL Expert in the world.”

Well, this was my conversation earlier but if you are facing issues with Wait Stats, just run above query and do send it to me at my email address and I will make sure that I reply you faster. Meanwhile do try out Spotlight On SQL Server Freemium on your server and do let me know your findings.

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

SQL SERVER – Speed Up! – Parallel Processes and Unparalleled Performance – TechEd 2012 India

TechEd India 2012 is just around the corner and I will be presenting there on two different session. SQL Server Performance Tuning is a very challenging subject that requires expertise in Database Administration and Database Development. I always have enjoyed talking about SQL Server Performance tuning subject. Just like doctors I like to call my every attempt to improve the performance of SQL Server queries and database server as a practice too. I have been working with SQL Server for more than 8 years and I believe that many of the performance tuning concept I have mastered. However, performance tuning is not a simple subject. However there are occasions when I feel stumped, there are occasional when I am not sure what should be the next step. When I face situation where I cannot figure things out easily, it makes me most happy because I clearly see this as a learning opportunity.

I have been presenting in TechEd India for last three years. This is my fourth time opportunity to present a technical session on SQL Server. Just like every other year, I decided to present something different, something which I have spend years of learning. This time, I am going to present about parallel processes. It is widely believed that more the CPU will improve performance of the server. It is true in many cases. However, there are cases when limiting the CPU usages have improved overall health of the server.

I will be presenting on the subject of Parallel Processes and its effects. I have spent more than a year working on this subject only. After working on various queries on multi-CPU systems I have personally learned few things. In coming TechEd session, I am going to share my experience with parallel processes and performance tuning.

Session Details

Title: Speed Up! – Parallel Processes and Unparalleled Performance (Add to Calendar)

Abstract:
 “More CPU More Performance” – A  very common understanding is that usage of multiple CPUs can improve the performance of the query. To get maximum performance out of any query – one has to master various aspects of the parallel processes. In this deep dive session, we will explore this complex subject with a very simple interactive demo. An attendee will walk away with proper understanding of CX_PACKET wait types, MAXDOP, parallelism threshold and various other concepts.

Date and Time: March 23, 2012, 12:15 to 13:15

Location:
 Hotel Lalit Ashok – Kumara Krupa High Grounds, Bengaluru – 560001, Karnataka, India.

Add to Calendar

Please submit your questions in the comments area and I will be for sure discussing them during my session. If I pick your question to discuss during my session, here is your gift I commit right now – SQL Server Interview Questions and Answers Book.

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

SQLAuthority News – Online Webcast How to Identify Resource Bottlenecks – Wait Types and Queues

As all of you know I have been working a recently on the subject SQL Server Wait Statistics, the reason is since I have published book on this subject SQL Wait Stats Joes 2 Pros: SQL Performance Tuning Techniques Using Wait Statistics, Types & Queues [Amazon] | [Flipkart] | [Kindle], lots of question and answers I am encountering. When I was writing the book, I kept version 1 of the book in front of me. I wanted to write something which one can use right away. I wanted to create an primer for everybody who have not explored wait stats method of performance tuning.

Well, the books have been very well received and in fact we ran out of huge stock 2 times in India so far and once in USA during SQLPASS. I have received so many questions on this subject that I feel I can write one more book of the same size. I have been asked if I can create videos which can go along with this book. Personally I am working with SQL Server 2012 CTP3 and there are so many new wait types, I feel the subject of wait stats is going to be very very crucial in next version of SQL Server. If you have not started learning about this subject, I suggest you at least start exploring this right now. Learn how to begin on this subject atleast as when the next version comes in, you know how to read DMVs.

I will be presenting on the same subject of performance tuning by wait stats in webcast embarcadero SQL Server Community Webinar. Here are few topics which we will be covering during the webinar.

  • Beginning with SQL Wait Stats
  • Understanding various aspect of SQL Wait Stats
  • Understanding Query Life Cycle
  • Identifying three TOP wait Stats
  • Resolution of the common 3 wait types and queues

Details of the webcast:
How to Identify Resource Bottlenecks – Wait Types and Queues
Date and Time: Wednesday, November 2, 11:00 AM PDT
Registration Link

I thank embarcadero for organizing opportunity for me to share my experience on subject of wait stats and connecting me with community to further take this subject to next level.

One more interesting thing, I will ask one question at the end of the webinar and I will be giving away 5 copy of my SQL Wait Stats print book to first five correct answers.

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

SQL SERVER – TRACEWRITE – Wait Type – Wait Related to Buffer and Resolution

Earlier this year I wrote for a whole month on SQL Server Wait Stats and the series was one of the best reviewed I have ever written. The same series has been enhanced and compiled into a book as SQL Server Wait Stats [Amazon] | [Flipkart] | [Kindle]. The best part of this book is it is an evolving book. I am planning to expand this book at certain intervals.

Yesterday I came across a very interesting system, where the top most wait type was TRACEWRITE. The DBA of the system reached out to me asking what this wait types means and how it can be resolved. As I had not written about this in the book so far, this is the blog post dedicated to his question. It will definitely be included in future versions of the book. For the moment let us go over it quickly and see what we can learn about TRACEWRITE.

Personally I consider TRACEWRITE a harmless and innocent wait type and I recommend that you ignore it too. However, the DBA who ran the script mentioned in my very first blog still found this wait type on the top as I did not include this wait type in the exclusion list.  I think it should be there and in future versions of the script I will include it.

From Book On-Line:

TRACEWRITE
Occurs when the SQL Trace rowset trace provider waits for either a free buffer or a buffer with events to process.

TRACEWRITE Explanation:

SQL Trace is a SQL Server database engine technology which monitors specific events generated when various actions occur in the database engine. When any event is fired it goes through various stages as well various routes. One of the routes is Trace I/O Provider, which sends data to its final destination either as file or rowset. This rowset provider does not provide any guarantees to data. It stores the data into its internal buffers. If the data from the internal buffer is not consumed quickly enough (20 seconds) the buffers start to drop the events to free itself up to handle future events. This is when it sends a special error messages to the profiler client. When more threads are waiting for free buffers the wait type TRACEWRITE is implemented. The higher this wait type, the higher the number of threads waiting for free buffer, degrading performance in most of the system.

Reducing TRACEWRITE wait:

It is not necessary to consider this wait type as bad or evil. In most systems it can be a perfectly normal wait type and you just need to ignore it. If you are convinced, you should stop reading this blog post here. However, if still want to reduce this wait type, here is something you can experiment with on your development server (never on production server).

Run the following query and see if it returns any value. This query will list all the trace running in your system.

SELECT *
FROM sys.fn_trace_getinfo(0)

In most of the systems I have come across I have noticed default trace enabled for the system. I personally like this to keep it on as it helps the DBA to diagnosis problems the first time they occur. Additionally, this helps Microsoft to diagnosis your system if you request their support. (One more reason to ignore this wait type and do not attempt to disable default trace). However, you can manually disable this trace by following script.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'default trace enabled', 0;
GO
RECONFIGURE;
GO

Well, as this is not harmful wait type, I had not included it in my book or initial series. Anyway, now we have learned about this wait type so I will include it in future versions.

Read all the post in the Wait Types and Queue series.

Note: The information presented here is from my experience and there is no way that I claim it to be accurate. I suggest reading my book OnLine for further clarification. All the discussion of Wait Stats in this blog is generic and varies from system to system. It is recommended that you test this on a development server before implementing it to a production server.

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

SQLAuthority News – SQLPASS – Today FREE 100 SQL Wait Stats Book Print Copy – Book Signing

“If there’s a book you really want to read, but it hasn’t been written yet, then you must write it.” ~Toni Morrison

I wrote book on SQL Wait Stats. [Amazon] | [Flipkart] | [Kindle]

I really wanted to learn about SQL Wait Stats. There was no real book available so I wrote the book myself. Since I wrote this book, I feel I can now more 100 pages to what I had contributed. I am very fortunate that my SQL Wait Stats book is very well accepted in community. Every author who authors book has dream that his book is well received and well read. In the recent internet era eBooks and PDF copies are everywhere and the job of reading print book and using bookmark is getting faded. Just like the same every author wants to have their books book signing event. I am so fortunate that during this SQLPASS the similar opportunity has presented to me.

I will be also doing book signing but here is interesting twist – you do not have to purchase the books (USD 11.99) – this book will be FREE for you.

Here are further details:

Event: SQL Server Wait Stats Book Signing
Date: October 13, 2011 Thursday
Time: 1 PM to 2 PM
Location: SQLPASS Exhibitors Booth #300

Thanks to kind people at expressor Studio for kindly helping me to with this arrangement.

Read the review of the book on SQLConcepts and on flipkart.

See you tomorrow!

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