SQL Server with Mr. Denny

Jun 26 2013   2:00PM GMT

Performance Tuning a Spotlight for SQL Server Query

Denny Cherry Denny Cherry Profile: Denny Cherry

The other day I was looking at parallel query plans on a customers system and I noticed that the bulk of the parallel queries on the system where coming from Spotlight for SQL Server.

The query in question is used by spotlight to figure out when the most recent full, differential and log database backups were taken on the server.  The query itself is pretty short, but it was showing a query cost of 140 on this system.  A quick index created within the MSDB database solved this problem reducing the cost of the query down to 14.  The query cost was reduced because a clustered index scan of the backupset table was changed into a nonclustered index scan of a much smaller index.

The index I created was:

CREATE INDEX mrdenny_databasename_type_backupfinishdate on backupset
(database_name, type, backup_finish_date)
with (fillfactor=70, online=on, data_compression=page)

Now if you aren’t running Enterprise edition you’ll want to turn the online index building off, and you may need to turn the data compression off depending on the edition and version of SQL Server that you are running.

If you are running SpotLight for SQL Server I’d recommend adding this index as this will fix the performance of one of the queries which SpotLight for SQL Server is running against the database engine pretty frequently.  I’d recommend adding this index to all the SQL Server’s which SpotLight for SQL Server monitors.


2  Comments on this Post

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.
  • mdean109
    You're providing a performance solution to a query that runs, I believe, every 5 minutes by default. But in this case, I feel a policy change is more prudent. 

    We have other processes in place to let us know if our TLog backups failed, so I changed the setting to only check once a day for the FULL and DIFF backups.

    If we didn't have those other processes, I would have set the backup check to run every 120 minutes. 

    Since we have 40+ instances, that's 40+ indexes that we don't have to create on the msdb database.
    10 pointsBadges:
  • Denny Cherry
    I believe that you are correct that it runs about every 5 minutes by default.  While a change to that may be a good idea, most people don't make a chance as they have SpotLight be the check for everything as they buy it to handle everything.  Having the check run every 120 minutes may not be often enough for some people.  It all depends on the RPO that they have defined.  If the RPO is 20 minutes, but you only look for failed backups every 120 minutes you could end up outside your RPO if there was a problem.
    68,390 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: