Is your SQL Server Performance S-L-O-W?

Welcome to the Q!

One of the benefits of being on our monthly Outsourced PDM Administration service, where we become your PDM Admin team, is SQL Server performance monitoring.

SQL Server Issue Indicators

The SQL Server management Studio Activity Monitor is a great indicator for what is happening with your database performance. We also use the Server Resource Monitor that reports disk usage and memory usage. EQUIVAQ monitors trends to determine where the biggest bottlenecks in performance are hiding. 

One of the most costly issues recently discovered with one of our clients, was when their users were sensing that there was just something not quite right - they would click and then wait a long time for the folder contents to display.

Keep in mind, there are limits to the quantity of files you can have inside any folder within PDM, less than 1,000 is usually best for performance.

Solving SQL issues

We took a look at the Disk Monitor in the Resource Monitor and found that for every folder the user was navigating into, the antivirus software on the Server was scanning every file there, including the ARCHIVE! As a result of our SQL monitoring, EQUIVAQ uncovered what was actually happening; we disabled the antivirus from doing active scans on the file Archives and enabled periodic scans outside of normal business hours.

In another instance, we found a TempDB database and log were not sized correctly. There are different schools of thought here, we follow the Microsoft SQL Server recommended settings. With SQL Server 2016, every database that gets installed will automatically create a TempDB file (not the log) for each CPU on the server, up to a maximum of 8 CPUs. So, if the database has only one TempDB file, it may be worth looking at some of those resources.

Lastly, disk latency is one of the biggest technology pain points (if not the biggest) that plague PDM users. If you can access TempDB files from a flash drive or an SSD drive, the PDM performance will increase. The TempDB is the lifeblood of your SQL Server, so accessing them as fast as possible will help. Another option to increase PDM performance and decrease access times would be relocating the PDM database onto a flash drive.

Monitoring Memory

Monitoring memory can really help drive performance. Fine-tuning the settings for memory usage of the SQL Server to ensure it will not consume ALL your server’s memory is crucial for performance.

Subscribe to our newsletter to get more of these insights AND schedule a free consult to review your system today!