By Justin Chlan

Introduction

With the exponential increase in computing power and broadband speeds, and the relative affordability of both, today's computer user has become accustomed to and expects rapid response from the applications and services they execute. The days of waiting for a webpage or file to load are well behind us, or so we thought. With all of this speed at our finger tips, why do we still find ourselves frequently in front of a computer monitor waiting for something to load?

The reasons for performance issues can be multiple; from network or system design flaws and poorly configured or overloaded servers, to imperfect database design and non-optimized queries. Often these non-optimized queries and processes can cause a crosstab analysis to take minutes (or even longer) to execute.

The Process Management feature of PBI is a tool to help identify poorly-performing processes and ultimately view and analyze the SQL statements in the process. Process Management in PBI is a tool typically reserved for Administrators. It is accessed through the Administration tab in the main PBI screen.

Navigating Process Management

Once you have opened Process Management, you are presented with a number of sorting, viewing and exporting options. The following section will summarize these functions.

 

1. Refresh button - Refresh is used to refresh the processes being viewed, based on the filtering criteria. A user may want to refresh the list if they are actively investigating a running process.

2. Top - Adjust the number of processes viewed. Options range from viewing a minimum of the top 10 to a maximum of the top 100. You may also manually type in any number between 1 and 100.

3. User - Filter the list by user. This filter is especially helpful in narrowing the search for a particular process.

4. Start Date/End Date - Filter the list of processes by the date the process started and/or the date the process ended.

5. SQL Button – Use the SQL Button to copy the SQL Statements from the selected process to the clipboard. Simply paste the SQL statements into SQL Server Management Studio or Query Analyzer.

6. Export – Use the Export feature to export the SQL statements for all of the processes or just those selected.

7. Email – The Email button will generate the SQL statements and, if the client computer has installed a MAPI email client such as Microsoft Outlook, attach the exported file to an email.

8. Currently Running PBI Processes – In conjunction with filtering, this tab will display those PBI processes actively running. A good example for using this tab would be when a user is currently experiencing difficulties with a particular process.

9. All Server Processes – This tab lists all processes running on the database server. Being able to view all the server processes is helpful in narrowing down the sources of performance issues. In some cases, the performance issue can be caused by a poorly-performing process in another database.

10. Top ### Longest Running/Most Rows/Un-End/Killed – Use these tabs to prioritize the list of processes based on the length of time to execute and complete, total number of rows returned, processes that did not end and processes that were killed.

Utilizing the Output

Most of the information collected in the Process Management tool is self explanatory, so the focus of this section will discuss how to best utilize the information provided. Performance issues related to queries typically fall into one of two categories, poorly optimized or too large (return too many rows). Use the "Longest Running" and "Most Rows" tabs to help identify these types of processes.

The most powerful feature of Process Management is the ability to copy and paste (or export) the SQL statements. At this point you can employ various tests and optimizations to the statements and hopefully decrease the processing time.

Conclusion

In conclusion, Process Management is a powerful tool to help identify or troubleshoot poorly-performing processes. Through various methods of sorting and filtering information, along with exporting SQL statements, the PBI administrator can quickly identify problems - and more importantly - diagnose and resolve issues more efficiently.