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)

About these ads

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)

SQLAuthority News – SQLPASS – 100 SQL Wait Stats Book Print Copy Giveaway – A Book Every Minute for an Hour Tomorrow

“Appreciation is a wonderful thing: It makes what is excellent in others belong to us as well” – Voltaire

“The greatest of all gifts is the power to estimate things at their true worth” – Francois De La Rochefoucauld

Please Note: The date and time are Thursday 13 at 1 PM (not Wednesday) – there are few emails asking for the same.

Quotes listed above are really relevant to the news of the day. Regular readers of my blog knows that I have published SQL Server Wait Stats [Amazon] | [Flipkart] book. I am glad to say that this book has become extremely popular since it has been published. It has ran out of stock in India two times thus far. In USA, we could manage the inventory efficiently. This book is now available on Kindle (at a price less than that of a coffee).

Recently, the kind people at expressor Studio have previously gave away few copies of my book. The same folks have sent me email that they got excellent feedback for the book from the readers who received the book. They have decided to give away 100 copies of my Wait Stats Print Book on Thursday October 13, 2011 between 1.00 and 2.00 PM at SQLPASS exhibition at Booth #300. I am just speechless at their efforts and good will for my book.

Tomorrow, I will be available between 1.00 and 2.00 PM at booth #300 and signing the books personally. Just stop by the booth and pick up the book. We intend to give away 1 book every minute of that hour. Honestly, it will be more than 1 book as there will be 100 books to give away during the short period of 60 minutes (on First come first serve basis!)

See you tomorrow!

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