Full scans/sec metric seems very high, dropped after killing 2 queries Our app today (java spring web application) suffered a major slump and Investigating the SQL Server metrics I found what seems to me a very high full scans/sec value.

I killed the 2 SPIDs that where responsible for the query (a big report that after the execution is written in Jasper Report) and the value dropped instantly.

Those are two screenshots before and after the kill:

Before:


After:



I'm not very expert in SQL Server since I'm not the DBA, but that values looks very worrying to me, can you confirm it?

Instance is SQL Server 2014.

Thanks
These metrics often have to be looked at in parallel with other metrics.  Simply doing a table scan is not a bad thing in itself.  Often the query engine will pick a table scan even if an index exists if it's faster, for example on a tiny table.  You need to look at other metrics, such as Disk Read/Sec &amp; Disk Write/Sec, along with PLE and others.  

Note that your memory metrics did not change at all, and you're still at 100% cache hit ratio, assuming you waited before taking the screenshot.  What it did affect was the CPU usage, which I didn't notice you mentioning as a concern, but if you are concerned about it, there it is.  Note that you reduced 3 ms of latch wait times, which means most likely you had some sort of writes or a high isolation level.

If your table scans are causing performance issues it'll probably show up in the other metrics or in your blocking reports based on your isolation level.  If you look at your PLE it didn't seem like the drops in scans did anything to it.  Maybe you need to give it more time but look to see if that drastically rises.  That'll be 1 indication that there was pressure because of the scans.  Collect the other metrics and review them before and after.

Also I'm sure you already know but be very careful about using the kill command.  In this case it was a read only query, in a write it will not perform a rollback thus voiding your ACID compliance.  Consider a DBCC INPUTBUFFER('spid') first if you're not already using that.