Full Scans/sec
-
Hi there, thought that I'd ask a question for a change
I'm monitoring our SQL server via perfmon and am noticing an increase in full scans/sec when the number of user connections increases, no suprise there I hear you say.
I know we do still have a few small tables without indexes (working on getting this down to none and they're not used very often) but just to muddy the water a little we're also heavily using user defined functions (returning table variables), which generally show as table scans when showing the execution plan in query analyser.
My question is, does anyone know whether performance monitor will treat these UDF calls as table scans, which would help me understand why there are so many of them, or is it purely table scans taking place (which I doubt).
-
as far as I am aware table vaiables and temp tables are treated the same as physical tables by perfmon.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
If you profile, you might be able to see high reads on certain calls. (or minimumly higher duration). Once you know which things are the problem, you can make the call in QA with SET STATISTICS IO ON to have a look at the reads per table. Once you do that, you should be able to determine where the full scans are coming from either from that information or looking at the execution plan.
-
I ran across this last week when tuning a query. The query plan shows it as a Table Scan for a Table UDF.
-Paul
-
Yep, because if you're returning a table variable then it has to do a table scan on the results, I hate that there's no way to have statistics or indexes on a table variable (it would make my life much easier performance turning if there was, just imagine not having to go through 3 levels of nested udf's just to find something not working as expected)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply