(When it comes to concurrency, we talk about isolation levels, which will be covered in a later post.)
Performance tuning and analysis is highly dependent on the time factor, since SQL Server is a dynamic entity and the factors affecting performance are constantly changing.
In the picture above I am exhibiting the total waits which occur in the SQL Server data processing. Think of waits as time slots filled with shifted processing attention (shifted in the sense of ‘re-located, re-focused‘ to a different location).
So, what I am trying to say here is that SQL Server would be a completely different product if the factor time was not part of it.
When it comes to performance monitoring and analysis, I would like to briefly say that the SQL Server self-collected metadata is a great asset in the performance tuning, however it is greatly limited by its design.
There will be another post later on dealing with the metadata features in SQL Server, but for now let me just warn you about some limitations: keep in mind that the DMVs, for example, are clumsy when it comes to historical ‘point in time’ events. The DMVs are cumulative dumps of data, which are sometimes hard to analyze unless you have some additional functionality to take ‘snapshots’ for you. Extended events are less clumsier, of course, but then there is the steep learning curve.
Let’s not get off the subject, however.
The most important aspect to remember from the ‘factor time’ concept is, that SQL Server is only one part of a much bigger system (in the client-server-client model context).
SQL Server is never alone in it’s context. (Well, unless we really drill down to the metadata and the self-collected performance metrics by SQL Server; more on this topic will be posted later in the Metadata concept blog post.)