topic RE: Database Performance Troubleshooting Queries in Digital Exchange

Database Performance Troubleshooting Queries

David-Dinh — Mon, 12 Jul 2021 19:45:00 GMT

This is the beginning of a chat for Database Performance Query Troubleshooting Guide.

------------------------------
David Dinh
Software Support Engineer
Blue Prism
America/Chicago
------------------------------

RE: Database Performance Troubleshooting Queries

peterlacken — Tue, 13 Jul 2021 10:19:00 GMT

Hi David,
Is there such a troubleshooting guide? We continue to have problems with slow queries on Work Queues from the Control Room when searching on Item Key. It takes upto 25 seconds to search for a single item key. Contrast that with just a few seconds to return all the matches on a specific status or tag, and under a second to return all of the items with no filters set. Our database team tell us that no queries are taking more than a few seconds on the database side pointing to something in the Blue Prism code itself that is filtering after the queries are executed.

------------------------------
PeterLacken
Ren Røros Intelligent Automation
Peter.Lacken@rria.no
http://www.rria.no
------------------------------

RE: Database Performance Troubleshooting Queries

David-Dinh — Tue, 13 Jul 2021 15:11:00 GMT

Hi, this thread was created as I am putting together a general guide so users can offer feedback. I'm not done yet, but it was not intended for this specific scenario. However, what you have raised does interest me. I would think the item key would be super fast.
How often do you rebuild your index's? I would check the last time this was rebuilt. It should be part of regular maintenance.
I'm uploading some files that my help. Please see file #4 and #12.

------------------------------
David Dinh
Software Support Engineer
Blue Prism
America/Chicago
------------------------------

RE: Database Performance Troubleshooting Queries

peterlacken — Thu, 15 Jul 2021 06:52:00 GMT

Many thanks for the input David, We rebuild indexes weekly in a sunday maintenance job rebuilds and reorganizes. These scripts we have previously aquired from Blue Prism. It looks like your material in the zip could give us some new things to check so I will share with our DBAs and post the results. Summer holidays here so maybe Aug / Sept before we close the case 🙂

------------------------------
PeterLacken
Ren Røros Intelligent Automation
Peter.Lacken@rria.no
http://www.rria.no
------------------------------

RE: Database Performance Troubleshooting Queries

chris.mcgowan — Wed, 28 Jul 2021 11:08:00 GMT

Hi David,

To complement the scripts we already use at Blue Prism I also use Glenn Alan Berry's SQL Server Diagnostic Queries. Glenn is one of the finest minds on the planet when it comes to SQL Server, in particular the hardware it is running on. He updates the scripts every month and they are pretty comprehensive, there are different scripts for each version of SQL Server including Azure SQL Database and SQL Managed Instance. Even better Glenn has some supporting YouTube videos to assist with the queries, which are great and he also has several courses on Pluralsight if people have a subscription.

There is a lot of information in these scripts and as always the benefit is in understanding the results which can be far from easy.

Regards

------------------------------
Chris McGowan
Senior Technical Consultant
Blue Prism
------------------------------

RE: Database Performance Troubleshooting Queries

David-Dinh — Wed, 28 Jul 2021 12:50:00 GMT

Thanks @chris.mcgowan! I will definitely have a look at these.

------------------------------
David Dinh
Software Support Engineer
Blue Prism
America/Chicago
------------------------------