Database Performance Troubleshooting Queries

[David-Dinh]

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

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

[peterlacken]

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
------------------------------

[David-Dinh]

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
------------------------------

[peterlacken]

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
------------------------------

[chris.mcgowan]

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
------------------------------

[David-Dinh]

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

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