SQL Agent Full Scans
-
We use SQL Agent to process backups and optimizations. While using performance monitor to check out our server this weekend, I decided to add Full Scans/second and Index Searches/second to give me some general feedback on data access and how well our indexes and queries were designed. I was disappointed to find an average of 16 Full Scans/second with light load. It peaks every 20 seconds at 161 Full Scans/second.
I'll spare you the details of the time I spent with profiler and reviewing our code to find the culprit, here's the summary:
It's SQL Server Agent doing the full scans. With it stopped our Full Scans/second goes to 0. Now, we're not doing backups every 20 seconds. But it seems that Agent checks out something every 20 seconds.
Questions: One: Full scans are supposed to be a bad thing. Is this amount worth chasing? (Probably not, but I wanted the Full Scans/second to be a red flag for me.)
Two: How do I fix it if it is? Can I add an index to whatever Agent is scanning? Can I turn off a piece of Agent that I don't use like Alerts?
Student of SQL and Golf, Master of Neither
Student of SQL and Golf, Master of Neither -
Don't everyone answer at once!
Student of SQL and Golf, Master of Neither
Student of SQL and Golf, Master of Neither -
Havent ever looked into it, I know there are some registry flags you can set to tweak behavior, to what extent Im not sure of.
Andy
-
I found my answer this afternoon through sheer exploring and experimentation. This isn't for the faint of heart, it involves editing a MS supplied system stored procedure.
It seems that every 20 seconds, Agent runs msdb.dbo.sp_sqlagent_get_perf_counters to see what's going on with your server so it can decide if you need any alerts generated. This scan takes place whether you have any defined, active alerts or not. I decided to "improve" on MS's efforts just a bit.
The SP does two things. It first makes a temp table of all your enabled, defined alerts. Then it does a pretty involved Select statement against the Master DB to find the alerts in your temp table that have out of band numbers in the Master DB.
It seems smarter to me to just avoid that second chunk entirely if you don't have any alerts defined. So that's what I did:
First Chunk --Insert on temp table for each alert
If @@RowCount > 0
Begin
Second Chunk --Check every perf counter in the world
End
It's working just fine. My every 20 second Full Scans are gone from the NT Performance monitor. Presumably if I add alerts in the future, I haven't broken the process. And my original goal, of treating any Full Scans as bad things that need to be investigated, is easier to monitor.
Student of SQL and Golf, Master of Neither
Student of SQL and Golf, Master of Neither
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply