Toad World® Forums

Spotlight causing high logical reads in SQL Server

Hi all,

In Spotlight on SQL Server Enterprise 10.5.2.7388, I’m seeing the tool itself responsible for 30,000 logical reads/sec on some SQL Servers. Here’s the scenario:

I fire up the client and click on one SQL Server. The home page dashboard shows 30K LIOs/s. If I switch over to Monitor->SQL Activity, then the Summary tab, I can see that’s been the minimum level of LIOs for days. Back to the home page for the SQL Server. I then right click on the SQL Server connection in the connection bar and choose “Disable Monitoring”, then immediately “Enable Monitoring”. The LIOs/s drop to “normal” levels. Due to some sort of entropy, the LIOs continue to increase over time (days? weeks?) until I again disable/enable monitoring from Spotlight. This tells me that either it’s Spotlight that’s causing the problem or it’s misreporting the logical reads, but I would suspect the former.

I see this on SS2K and SS2K5 primarily, but also on at least one SS2K8 R2. Other SS2K8 and SS2K12 servers aren’t up for more than a week for unrelated issues. I only have licenses to monitor 11 instances, so a larger sample isn’t in the budget at this time.

Anyone else see something similar?

TIA!
Rich

That’s really odd. Options:

  1. Go to Monitor --> SQL Activity --> Sessions.

Order by column Logical Reads desc. choose session that with high logical read.

Then you can see its sql and plan. Email it to Dell Support (Help – > Contact Support).

  1. Upgrade to version 11. There is a new feature called workload analysis.

you can see Logical Reads or CPU or others by database\application\user\sql statement clearly.