SQL Server Select – Back to Basics

Comments: No Comments
Published on: April 1, 2016

Prelude in SQL Minor

translucentman_greenBack in late December of 2015, a challenge of sorts was issued by Tim Ford (twitter) to write a blog post each month on a SQL Server Basic. Some have hash-tagged this as #backtobasics. Here is the link to that challenge sent via tweet.

I did not officially accept the challenge. Was an official acceptance required? I don’t know. I do know that I think it is a good challenge and that I intend to participate in the challenge. I hope I can meet the requirements and keep the posts to “basics”. Let’s just call this first post in the challenge to be my official acceptance.

With this being another installment in a monthly series, here is a link to review the other posts in the series – back to basics.

SQL Server Select

atlasian_manOf all the fundamental concepts within SQL Server, nothing is potentially more basic and fundamental than the often overlooked Select statement. Think about that statement for just a moment.

How often is the select statement overlooked and under-analyzed? Yet, this tiny little word is a rather large part of the transactions that occur within each instance of SQL Server on a routine basis.

Today, I want to break down the SQL Select statement and show the components of the statement along with the logical processing order of those components.

Let’s put on our thinking caps for a moment and think of all the possible components of this super fundamental piece of the TSQL language – the Select statement.

Anatomy of the Select

The first important piece of information with the Select is to understand what it does. What is a Select statement? What is the purpose of a Select? Really, the purpose of this statement should make sense based on the word alone – to select. In other words, to get or retrieve something that is of interest to you at that moment. In our case, that means to retrieve some piece of data from the database to meet our needs. Very simple, right? You issue a SELECT statement to get something out of the database.

Thinking through the Select statement, certain common elements probably stick out pretty easily. These elements might include the following:

  • Select
  • From
  • Join
  • Where

Then with a slightly more complex query, one might see the following components associated with a Select statement.

  • Top
  • Distinct
  • Group By
  • Order By

Then stepping it up to another level again, one might see the following:

  • Having
  • For
  • Into
  • With (Cube or Rollup)

Soak that in for a moment. Suddenly this fundamental piece of TSQL is starting to look a little more like black voodoo magic. It can get rather complex with this fundamental statement. Now soak in what the purpose of the SELECT is, as was stated just a bit ago. That purpose is to retrieve data that you need. One cannot always retrieve the desired data without some options to go with the SELECTion criteria. Suddenly, this complexity renders itself more as raw DBA power.

Knowing the various aspects of the Select, and not diving too far into what each does, you may be wondering how these pieces fit together into a query. Let’s take a quick look at a few different examples illustrating how these puzzle pieces fit together into a SELECT statement.

Here is an example showing a SELECT that utilizes the FROM, WHERE, GROUP BY, and ORDER BY clauses. When writing a SELECT statement, we will write the query in the order shown in the query. Using these clauses in a different location doesn’t read very well to humans and will throw a syntax error.

In this next example, I have used a few different clauses:

The real differences here being that I have used the DISTINCT, TOP and JOIN clauses. Again, trying to write this query with the TOP preceding the TOP or maybe with the JOIN preceding the top doesn’t really make much sense when reading it. Nor would it work if we tried to execute the query due to syntax errors.

This next example uses another option to output the results into an XML document using the FOR clause:

And in this last example, I take the previous example to output the results into a temp table.

These are all examples of the possibilities that can be used with the SELECT statement to retrieve data from the database in order to meet our needs. Each one illustrates different variations and some level of complexities between each of the examples.

Now the question is, did you know that SQL Server does not process these statements in the same syntactic sequence or English friendly forms that we write these SELECT statements? You may be surprised to learn that there is a different processing order to these pieces of the SELECT. This processing order is called the LOGICAL Processing order.

Let’s take one of the queries already shown and compare.

The query as we may write it will look like this:


The query as SQL Server will process it will look like this:


Or in other words, we may see it as shown in this video:

That is significantly different than we might think, but it makes sense if you think about it long enough.

How does one know that SQL Server processes these steps in this order? Well, besides the documentation in BOL that supports this, one can dive into the internals and generate a tree that will show this in action. For instance, let’s use the same query as just shown and see what the processing tree would look like.


Besides looking very colorful and almost as if it were in Greek, this processing tree is somewhat revealing in how SQL Server breaks down that SELECT statement we have been using. One more pic, and then a quick explanation.


This last image was generated using the SHOWPLAN_TEXT setting. I want to show this because it helps to reinforce a concept from the processing tree. In this last image, note that the OUTER REFERENCE is to the SalesPerson table. This also means (without stating it) that the inner reference is to the Employee table. I have added the INNER and OUTER table references to the image of the processing tree. Why is this important? Well, when trying to interpret the tree it is important to understand that one starts from the inner most node and then read from bottom to top, right to left (as you ascend the nodes).

In this case, we will see that the Employee table is first referenced and processed, then the SalesPerson table, then the ON clause is evaluated. In the tree, I highlighted the ON that pertains to the join in bright green. The vertical dotted lines on the left are to help visually keep the nodes aligned as you step in/out from one level to the next. As we climb the tree to the top left corner, we will see that the last piece of the select to be processed in this case is the TOP statement. We can also see that the top is dependent on the ORDER BY (highlighted in RED) in order to get the correct desired rows.


This article jumps through the SELECT statement very briskly. The SELECT statement is a powerful tool within SQL Server. Gaining an understanding how the SELECT statement is processed can be helpful to write an appropriate SELECT and how to potentially pseudo code that SELECT. If you can understand that the engine first determines the source of the data and then starts to break down the additional requirements that have been sent with the SELECT. The more options that are thrown in with the SELECT statement, the larger and more complex the processing tree becomes for that statement.

Used well, a SELECT will become your ally and a handy tool in the toolbox. This has been an introduction to the SELECT and how it is processed behind the scenes. If you would like to see more information about the different clauses related to the select statement, check out the BOL entry here.

As an aside, the tree that was displayed is called the “converted tree.” Here is a listing of the other possible trees.


SQL Agent Job Schedules

Many moons ago, I posted a script to help report on the human friendly scheduling sched_reportinformation for SSRS subscriptions. You can read all about that here.

In a very similar fashion, today I want to share how easy it is to turn the internal schedules for SQL Agent jobs into something that is more human friendly. SQL Agent job schedules should be easy to read for humans if for nothing else than the fact that you have to provide this information to the auditors from time to time. We all know they can’t properly interpret the numeric job schedules and properly determine what they mean.

The job schedules work quite well with the current design – which is not human friendly. The use of numbers is a streamlined approach and far more efficient. It may be a bit more useful to us if there were some sort of translation table that was stored. A stored translation table would make it terribly easy to figure out what is happening within the schedules for the SQL Agent jobs. We don’t have that, so we often have to do a little trial and error. Or we rely on people that have done that trial and error – with a little verification on our part. Afterall, these scripts are posted on the internet.

SQL Agent Job Schedules

I am not going to break down the script. With the similarity to the aforementioned SSRS script, I would recommend looking there for a little bit of an explanation. Here is the script to retrieve SQL Agent Job Schedules from your MSDB instance.

This script will only retrieve the SQL Agent Job schedules that are not SSRS related and that are not SSIS maintenance package related. The SSRS stuff has already been introduced, and a quick eval of those blog posts will show the differences between grabbing SSRS information vs. grabbing just SQL Agent Jobs.

Stay tuned. I will post a script for the SSIS related scheduling information.

Now, go forth and audit your agent jobs. Document the schedules in an easy to read format. Give that report / document to the auditors and your manager and look like a rock star for the day!

Index Cannot Be Reorganized…

sunburst_spaceWorking diligently as any good DBA might, you have established maintenance routines for each of the SQL Servers under your purview.

As a part of this maintenance you have scripted solutions to intelligently manage and maintain the fragmentation levels for each of the indexes within each database on each instance.

To further show how diligent you are as a DBA, the outcomes of each maintenance run are logged and you review the logs each morning. This routine helps keep you on top of everything that is happening within the environment.

For months, maybe even years, things are running smoothly. Never a failure. Never an error. Just routine log review day after day. Then one day it happens – there is an error. The index maintenance script failed one night.

Index Cannot be Reorganized…

You receive the error message similar to the following:

Msg 2552, Level 16, State 1, Line 1 The index “blah” (partition 1) on table “blah_blah_blah” cannot be reorganized because page level locking is disabled

Immediately, you start double-checking yourself and verifying that it worked the previous night. You even go so far as to confirm that the same index was previously reorganized. How is it possible that it is failing now on this index. What has changed? Has something changed?

Time for a little digging and investigating, so the dirty work begins. On one side of the coin you are relieved to be able to do something different. On the other side of that coin, you are rather annoyed that something seems to have changed. These feelings are perfectly normal!

First things first – you investigate the indexes in question to confirm what the error is saying. This is easily done with a query such as the following:

Scrolling through the results, you notice (eventually) that the IX_SpecialOfferProduct_ProductID in the AdventureWorks2014 database has page locks disabled. You are absolutely certain that this index was created allowing page locks. Pondering the problem for a moment, you recall having read about the default trace (there are several articles on the default trace – here) and the thought occurs to try and see if there is a breadcrumb there about the change. Using the query from that default trace article, a picture starts to unscramble. Here is that query reposted and a snippet of the results:


This is a great start. Not seen in the results is the timestamp showing when it was done – which was due solely to snipping. Also not shown is the text of the statement that was run for those three events. So close, yet so far away. This is not quite enough to have the smoking gun evidence to show Jason (me) that I did something wrong and unauthorized. What to do now?

All is not lost yet! Your stunning memory kicks in and you recall several articles about using Extended Events to audit server and database changes. Better yet, you recall that you deployed an XE session to the server where this error occurred. And yes, you are slightly embarrassed that you failed to remove the XE session after fiddling with it. We won’t tell anybody that you deployed a test XE session to a production server as if it were your sandbox. The session currently deployed is trapping all object changes unlike the following session that has it filtered down to just objects that are indexes.

You query the trace file with a query like this:

Wow! Look at the results! There is a smoking gun finally.


Two results happen to pin the root cause of the change squarely on my shoulders. Yup, Jason changed that index to disallow page locks. I highlighted three different areas of interest in the results. The yellow and green indicate the DDL phase. One row for the start of the statement, and another row for the commit of that statement. The red highlight shows me the exact change that was made to this index. This is all very good info!

What Now?

It really is great to have the smoking gun. If something is broke and it worked previously, it is essential to find the root cause. With a root cause under the belt, what needs to be done to fix the failure? That is a little bit easier that finding the root cause. That is, unless there is a technical reason for the index to no longer allow page locks (maybe that smoking gun is less of a smoking gun and more like baby spittle after all).

Here is how you fix the problem:

But, But, But…

The Extended Events session would be very noisy and capture every alter index statement, right? It should capture statements like the following, right?

The answer to that question is: Yes, Yes, Yes. These statements are all captured due to the use of the ALTER statement. Here are the results of the XE session with all of these scripts having been executed:


If you want to audit when the indexes are changing and how they are changing, this will do just the trick. If there are a ton of changes, then be prepared for a deluge of information.

The Virginia Tour 2016

db nsfwLong Time Coming

With less than a week before the Richmond SQLSaturday event, I am finally getting this post done – maybe.

This is not just a one day event for me. No no! I will be presenting for three consecutive days on many things SQL Server related. The Virginia Tour 2016 really is more of a Richmond Tour 2016 (March 16-19) with all three days being presentations in the Richmond area.

The tour starts off with a visit to the Richmond User Group. You can find details on the evening and event on their website – here.

Here is a quick recap of the NSFW (Naked Sql: Fundamentals to Wisdom) session:

Ever feel like there are just too many layers with SQL Server? You peel off a layer and behold there is yet another layer. SQL Server is rather complex. The complexities can be rather frustrating from time to time.

Join this NSFW (not safe for whining) session as we strip SQL down to the bare bones. I will help you undress the complexities of a few of the basic SQL Server concepts. These concepts will include locks, principals, query directives and deadlocks.

In this session I will disrobe misunderstandings related to the aforementioned topics. I will show the different types of principals in SQL Server. I will also show various scenarios involving locks and deadlocks along with some of their effects with and without query directives. The goal is to better understand these topics and to hopefully stop using and perpetuating common mis-uses of terminology and common misconceptions about the technology.

This should be a light-hearted and fun session with a bit of NSFW learning. Just hope the censors don’t get to the slide deck first!

After the user group presentation to kick off the week, there will be an all-day encore on Friday. Friday is officially a part of the SQLSaturday event. You are welcome to come and participate in my all day workshop – Monitoring SQL Server with Extended Events. There is an event page with details for registering that you can visit – here!

Here is some of what I will be covering in day 2 of this Richmond tour:

Every good enterprising DBA has the prime directive of monitoring the SQL Servers under his/her jurisdiction. The last thing a DBA wants to hear is that there is a problem with a database under his/her purview from an end-user.

SQL Server has progressed over time to give better and better information to the DBA.  Much of this information can be used in a home grown solution to monitor SQL Server. With the constant increase in surface area from Extended Events, the power to build a home grown solution becomes more and more feasible while being limited only by the imagination of the enterprising DBA (or developer or data professional). This full day workshop will introduce you to an Extended Events based solution to achieve that home-grown monitoring goal.

This full day session on Extended Events will help prepare you to put this tool to immediate use as you walk back to your daily duties.  This workshop will teach you about Extended Events starting with the basics and moving through how to create XE sessions that will get the right data for you, while doing so with minimal impact.

You will be exposed to advanced troubleshooting techniques as we work through complex issues that are made easier through the use of XE.  Take advantage of this opportunity to dive into the world of Extended Events and learn how you can make best use of this tool in your SQL 2008+ environment.

This will be a fun day if for nothing else than it is about Extended Events and how to glean great information from your SQL Servers. That and we will keep it light. Just remember, the more you are involved in the workshop the better it will be!

sqlsat486webAfter a brief break to grab some zzzz’s, I will be wrapping up this tour with the grand finale – SQL Saturday 486!!

Not only will I be presenting at this event, I have also volunteered.

I will have a short introductory style presentation about Extended Events. I intend to show a little profiler and a little Extended Events. As usual, I intend to keep the session light, fun and engaging. Here is a little more info on that session:

Extended Events were introduced in SQL Server 2008. With SQL 2014, we have seen a significant upgrade to this feature. Join me for a little adventure into the realm of extended events. We will discuss how to use extended events to aid in performance tuning and in day to day administration. We will also explore some background and the architecture of extended events. This is a fast paced session and will be a lot of fun. Attendees will be able to take back several sample XE sessions to help troubleshoot different issues and get to more intimately know their data/environment.

wheelofdoomThen to cap the day, I will be helping to assist with the Wheel of Doom.

The name of the session sounds much worse than it really is. The premise is simple. Speakers and attendees are invited to submit a lightening talk style session with slide deck. Then attendees of the session can volunteer to spin the wheel and present whichever session pops up.

This session is to help people get over the nerve of presenting. It will be very light-hearted and entertaining. No judgement, no shaming, just have fun with it!



SQL Server Locks – Back to Basics

loch_nessPrelude in SQL Minor

Back in late December of 2015, a challenge of sorts was issued by Tim Ford (twitter) to write a blog post each month on a SQL Server Basic. Some have hash-tagged this as #backtobasics. Here is the link to that challenge sent via tweet.

I did not officially accept the challenge. Was an official acceptance required? I don’t know. I do know that I think it is a good challenge and that I intend to participate in the challenge. I hope I can meet the requirements and keep the posts to “basics”. Let’s just call this first post in the challenge to be my official acceptance.

SQL Server Locks

A fundamental component of SQL Server is locking and locks. Locks within SQL Server are critical to the proper functioning of the database and the integrity of the data within the database. The presence of locks does not inherently mean there is a problem. In no way should locking within SQL Server be considered a monster, though locks may often times be misconstrued in that light.

So what is locking? What are locks? In SQL Server, locking is a pessimistic mechanism that limits, or synchronizes, access to resources between multiple different requests. A lock is the manifestation of that mechanism. Think of a lock within SQL Server as if it were a padlock that was temporarily securing a gate to a storage shed limiting access to the shed.

That is the overly simplistic illustration of a lock in SQL Server. Have you ever seen a gate with more than one lock on it? Well, just like that multi-lock gate, resources within SQL Server can have several different locks placed on them. This is where it starts to get complicated and tricky.

To help control the locks and direct access to resources within the database, SQL Server utilizes a service called the lock manager. When a query is executed, the Database Engine (more precisely the query processor) determines what resources are needed and what locks are needed for those resources. Then the query processor walks over to the lock manager and begs for the lock types needed. The lock manager takes a look at the locks in use on the resources and either grants the locks or tells the query processor to wait until the locks are available.

This process is not terribly different from a tenant requesting the use of a meeting room from the building manager (or property manager). If the meeting room is available, the building manager will grant access to the meeting room and place a hold on that room preventing other tenants from using it at the time you requested it. Well, not terribly different but on a much much larger scale.

Just as you may see in a building with multiple meeting spaces, offices, rooms, and equipment as resources, there are many types of resources within SQL Server. The lock manager has to coordinate different types of locks for the varying types of resources. Let’s take a look at some of the lock types and resource types.

Lock Types

First up is to explore the different types of locks in SQL Server. One can either explore the internet or query SQL Server direct to try and find what is available. I will show two ways to find the different types of locks available within SQL Server via the following queries:

Looking at the results from the first query, I will get results similar to the following:

name map_value
lock_mode NL
lock_mode SCH_S
lock_mode SCH_M
lock_mode S
lock_mode U
lock_mode X
lock_mode IS
lock_mode IU
lock_mode IX
lock_mode SIU
lock_mode SIX
lock_mode UIX
lock_mode BU
lock_mode RS_S
lock_mode RS_U
lock_mode RI_NL
lock_mode RI_S
lock_mode RI_U
lock_mode RI_X
lock_mode RX_S
lock_mode RX_U
lock_mode LAST_MODE

The last lock type in this list is kind of an interesting one. I believe this maps to the RangeX-X lock type seen in the documentation and in the second of the two queries provided. What do these letters mean? It looks like a bunch of alphabet soup right? Well, there is a little more info to be found once turning to the documentation (check the image by the heading of “key”). Here’s a little table to decrypt it as well:

name map_value Decrypted
lock_mode NL No Lock
lock_mode SCH_S Schema Stability Locks
lock_mode SCH_M Schema Modification Locks
lock_mode S Shared
lock_mode U Update
lock_mode X Exclusive
lock_mode IS Intent Shared
lock_mode IU Intent Update
lock_mode IX Intent Exclusive
lock_mode SIU Shared with Intent Update
lock_mode SIX Shared with Intent Exclusive
lock_mode UIX Update with Intent Exclusive
lock_mode BU Bulk Update
lock_mode RS_S Shared Range-Shared
lock_mode RS_U Shared Range-Update
lock_mode RI_NL Insert Range-Null
lock_mode RI_S Insert Range-Shared
lock_mode RI_U Insert Range-Update
lock_mode RI_X Insert Range-Exclusive
lock_mode RX_S Exclusive Range-Shared
lock_mode RX_U Exclusive Range-Update
lock_mode LAST_MODE Exclusive Range-Exclusive

I want to just cover some quick highlights about a few lock types:

Lock mode Description
Shared (S) Used for Read operations that do not change data.
Update (U) Used on resources that can be updated. Helps to reduce some deadlocks.
Exclusive (X) Used for data-modification operations, such as INSERT, UPDATE, or DELETE. Ensures that multiple updates cannot be made to the same resource at the same time.
Intent Used to establish imminent need for a lock.
Schema Used when a schema dependent operation is executing.
Bulk Update (BU) Used when bulk copying data into a table and the TABLOCK hint is specified.
Key-range Protects the range of rows read by a query when using the serializable transaction isolation level. Ensures that other transactions cannot insert rows that would qualify for the queries of the serializable transaction if the queries were run again.

Lock Resources

Lock resources are the things upon which a lock is held. This is the lock granularity and hierarchy. It is this hierarchy that helps to reduce the overall cost of locking in SQL Server (again banishing the notion of it being a monster). The hierarchy of locks can be explored via queries in SQL Server (and it can be found – to a degree – in the documentation). Here are a couple of queries to help explore the locks hierarchy:

The results of the first of these queries are:

name map_value
lock_resource_type UNKNOWN_LOCK_RESOURCE
lock_resource_type NULL_RESOURCE
lock_resource_type DATABASE
lock_resource_type FILE
lock_resource_type UNUSED1
lock_resource_type OBJECT
lock_resource_type PAGE
lock_resource_type KEY
lock_resource_type EXTENT
lock_resource_type RID
lock_resource_type APPLICATION
lock_resource_type METADATA
lock_resource_type HOBT
lock_resource_type ALLOCATION_UNIT
lock_resource_type OIB
lock_resource_type ROWGROUP
lock_resource_type LAST_RESOURCE

There are a few differences between this list and what is found in the documentation. There are also a few differences between the results of the first and second queries. This should give a pretty good idea about the level at which locks could be held from a key all the way up to database.

I have shared these because the lock resources may come in handy when troubleshooting or even designing queries and database schemas.

Lock Owners

The last bit about locks for this time is the lock owner. Another way of thinking about this one is what is happening while the lock is being held. Is the query a part of a transaction that is holding the lock? Maybe the query is a cursor and the cursor is holding locks for the queries within the construct of the cursor. The following query that shows what the possible owners are can be a little helpful in understanding what an owner is.

name map_value
lock_owner_type Transaction
lock_owner_type Cursor
lock_owner_type Session
lock_owner_type SharedXactWorkspace
lock_owner_type ExclusiveXactWorkspace
lock_owner_type LockConflictNotificationObject
lock_owner_type LockTableIterator
lock_owner_type LastLockInfoOwner

This is another one of those items to explore which could help understand the nature of the locks if you happen to be troubleshooting or investigating a certain locking issue within your environment.



Locking is a fundamental concept in SQL Server. Locking is the mechanism that coordinates access to resources while maintaining data integrity. Locks are the tool used in locking to ensure access is only granted to the correct requestor and preventing access to the resource to outside sessions.

This was a basic introductory article on the topic. Stay tuned for a more in-depth look at a specific scenario involving locking and deadlocking.

«page 2 of 104»

July 2016
« Apr    


Welcome , today is Sunday, July 24, 2016