Filter SPIDs & Filter DBs: These free text fields can be used to filter out results based on specific SPID numbers or database names. For instance if we had a SQL snap from a hosted client’s server that showed 5 other client’s databases we could enter in text to filter solely on one client’s database based on its name (i.e. SMP_EC_40). If I were to type this in the Filter DBs field it would show the SPIDs and activity from that specific database.
Left Pane: The left pane allows us to scroll through the individual ‘SQL snapshots’ that were taken against the server based on the date and time. This is why date and time information is so important when referencing the start of a process when reviewing performance issues. We can select a particular date/time entry that will then populate the middle and bottom pane with information pertaining to what was running on the server. We can then click into those other panes and review the processes that were running.
Center Pane: This is the pane that shows us the actively running SPIDs, aka business processes. SPIDs are numerical ID values that SQL gives business processes to help identify them. This pane shows a lot of information pertaining the business process while scrolling through the various columns. The most important of which are explained below:
- SPID: The primary ID of the business process. This correlates with the Filter SPIDs option in the top menu bar. Entering a specific SPID into this filter option will filter out all other SPIDs from the results in the middle pane making for easier review.
- Host: The web server that the business process was called through
- LoginName: Name of the user that started the process. This can be misleading at times because there are several business process types that will show under different system defined user accounts. For instance reports may show under the report user whereas queue or scheduled business processes will show up under the BBEC job user account.
- Last Buffer Age: This shows how long the process has been running in seconds. This can be used to quickly determine if a process has been running for a long period of time simply by looking for any large values.
- Last Batch: This represents when a process or child/sub-process was started. Sub-processes are mentioned because sometime we can have an overarching stored procedure running that makes other calls that start us other processes as child/dependent processes based off the original/parent process. The Login time can be used to locate the parent process start time.
- Login Time: This can be used to determine the actual start time of the parent process that’s running.
- Status: Represents whether it’s actively ‘running’ or if it’s ‘suspended’
- Blocked: This is one of the most useful columns as this will show you what other SPID the process is blocked on and gives you another quick way to determine if there’s blocking going on other than reviewing the colored coded Time Line option.
- Database: This shows the name of the database and can be used to verify the database that the SPID/process is running on. This is associated with the Filter DB option in the top section of SQL snaps.
Bottom Pane: This is where we see the magic! This is where the SQL of what’s actually running under the selected/highlighted SPID from the middle column is displayed. This will show whether it’s a Stored Procedure (indicated by USP_*), a Trigger (indicated by TR_*), or some other object. Most code that shows here will be grey but if we scroll down through the code we may encounter code that’s highlighted in blue which indicates the actively running code in the business process at that point in time of the SQL snap. For example see the below screen shot that shows blue code amidst gray. We can tell from this that the blue code is what’s actively running and that it’s running something associated with revenue stuff based on FINANCIALTRANSACTIONLINEITEM references. Taking this a step further if we see this same section of code in blue continuing to run for a long period of time while scrolling through the snaps this could indicate that this process is slow in this particular area and may be the source of a performance issue.
Time line (all connections): This can be used as a quick visual representation of what’s running. Its colored coordinated to show active processes/SPIDs as Yellow while any Blocked processes/SPIDs are shown as RED. We can also quickly review this to determine any long running processes by looking at the length of the yellow coded processes. If these extend for a long period of time this represents the same process continuing to run and we can begin to review that specifically or as appropriate.