Improving SQL Performance - Index Optimisation

Introduction

Microsoft SQL Server does a great job of self tuning and self management, however after a while you may find that your Myriad Playout is starting to slow down, and even starting to show some Timeout Errors. 

Whilst these can be caused by a variety of different things - anything from simply needing some TLC or networking issues, all the way up to hardware issues, one of the most common issues is that the Indexes in the SQL Tables need optimising.

A bit of background

All Myriad products stores their primary data in Microsoft SQL Server ("SQL") inside "tables" in one or more "databases". The way that SQL keeps track of this information is because each table has one or more "indexes".  Over time, these indexes start to become "fragmented" which leads to data access slowing down.

Myriad Playout v5.21 includes a built in report under Database Reports that will even show you the level of fragmentation in your active Myriad Playout Database, and provides recommended actions.

The good news it this is easily fixed, and can even be done automatically. We actually recommend running this batch file once every week or two, and using the built in Windows Task Scheduler is an ideal solution.

How to fix it

Download the attached "SQL_Reorganise.bat" file and copy it onto the server that is running SQL. We suggest creating a SQLScripts folder in the root of the same drive that SQL is storing it's data in - for example d:\SQLScripts 

Depending on your installation, you may need to edit the batch file to alter the location of the SQL Server "Instance" - you can edit this file simply by opening notepad.exe and opening the file.

By default, Express versions of SQL are installed using the instance name "SQLEXPRESS" and if you look on line 5 you can see this how the batch file is already configured:

SET "INSTANCE=.\SQLEXPRESS"

If you have altered the SQL Instance name then you should alter this line accordingly.

If you are using Microsoft SQL Server Standard edition then you will need to edit this line to say:

SET "INSTANCE=.\MSSQLSERVER"

Save the batch file then right click and "Run As Administrator"

The script will take a short while to run, and will report it's progress as it goes, and once completed then your table's indexes will be fully optimised. Naturally over time they will start to gain a little fragmentation, so this process can be re-run as often as you like.

This process is safe to run while Myriad Playout is playing, but as a cautionary note, if your indexes have never been optimised before then this process can take a little while, particularly on very large databases, so you should wait until the start of a song, just to reduce any slight performance slowdowns whilst the optimisation is running.

Proactively running the Optimisation process.

To prevent fragmentation building up we recommend running the Optimisation process regularly - once a week or once every 2 weeks, we suggest at time when it won't affect users such as 4:30am on a Sunday morning. The easiest way to achieve this is using the built in Windows Task Scheduler, and creating a task that is set to "Run whether the user is logged on or not" and also "Run with the highest privileges"