too many index scans!
-
Hi,
We have some stats on a 2 node clustered active/passive sql 2000 machine on a 4 processor 2003 server.
14.4 full scans/sec
7,556 Index scans/sec
5.88 page splits/sec
72.63 readahea/sec
The worst is the index scans, should be 977/s, industry standard according to a source.
I've logged some scan start events and it seems to be agreeing with the findings.
Good news: the app causing the trouble is an in-house app, so we can make changes.
Bad news: the trouble seems to be in the tempdb.
How do i tell what is causing these scans? The objectid for all of them are negative and they of course don't exist in the tempdb since they have been executed and sessions are over.
What can i do to track down the culprit stored procs?
Thanks for any help.
-
Have the profiler running and load all the traces into a table. Query the table for all procs taking longer or having more reads or taking too much of CPU. Run the test a few times for consistency. Get the top 20-30 offending procs from each run and if they show up consistently, go after them and fix them. Repeat the process until you are satisfied with the performance.
******************
Dinakar Nethi
Life is short. Enjoy it.
****************** -
I need to get this one metric, Index Scans down. How do I identify what tsql is executing these scans? Is there a way?
-
May I ask why you think you need to change this one metric?
-
It's a task I've been given - you recommend the tsql duration trace as well?
-
Index scans are not necessarily bad, but worth checking. For example, let's say you have a table with 10 rows in it, SQL could easily opt for a scan instead of a seek because it knows there are few rows in the table.
-
I was hoping to find these scans in profiler, link them to sysobjects and then take a look at the indexes on the table. The bulk of these seem to be temp tables - which I can't look at anymore. Is this route a dead end due to the tables being gone from sysobjects in tempdb?
-
You'll need to still run the trace and determine which sp's are using the temp tables. Depending on the size of the temporary tables you can create indexes on them within the stored procedure. This will of course cause one scan. Once again, not all full scans are a bad thing, just like not all loop joins are a good thing.
Tom
-
Well where I'm stuck at is where have they decided that they can't have more than x scans per sec??
Anyways I've always approached and seen approached performaace tuning by finding the most offending queries and tuning them one by one (using the profiler).
Also using the same profiler you can find what queries are using the most index scans. However keep in mind that an index scan is not necessary a bad thing.
-
I can't believe I'm reading this post !!! "The worst is the index scans, should be 977/s, industry standard according to a source" what a load of cr*p !
I have a system, approx 2k connections, a typical hour shows 5k to 20k index searches and maybe 20 full scans per sec as an average over an hourly period ( I maintain these stats for my production servers ) OK it's not the world's best app ( it's crm ) and yes it needs some tuning but industry standard - god save us poor DBA's from xxxxx*&^%$'s
To be honest index scans isn't the first metric I'd be looking at for tuning and I wouldn't tune to this counter ever - take a look at i/o, cpu and duration.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
btw page splits might be a concern, but depends upon your application, if that average is for an entire day that probably is a problem, I typically see 0.0 for my hourly figures, and yes it's quite a volatile database.
can I recommend, as ever, the sql server 2000 performance tuning technical reference as a starting point.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Interesting, indeed. No, there is no acceptable maximum for index scans. I'm not sure where you could come up with a number like 977 per second.
Can you cite the source for this arbitrary figure? I'd like to check it out....
I will tell you this. In general, and I don't mean for this to be taken as an absolute, but in general, I've discovered this about index and full scans:
The ones that don't belong, meaning the ones that I would try to get rid of, are typically caused by a blown rowcount estimate that leads to a hash join where a loop would be more appropriate. In the case of a query with no aggregate functions, I can point the compiler in the right direction by adding the OPTION (fast X) hint at the end of the query, where X is the number of rows the query is actually going to return on a regular basis.
This has worked for me on quite a few occasions. Mostly on queries involving tables that have > 1 million rows. Just yesterday, I discovered a query that estimated 200 million rows when around 500 were actually returned. And yes, statistics are correct, SQL server just got it wrong.
So where did you get 977 index scans per second?
jg
-
Patience Colin:-)
It's not where I start either, but it will be a good learning exercise and may well lead to something worth fixing.
-
Chalk it up to a consulting firm which did an audit of our environment to consolidate to virtual servers. They gave this document to my senior who now thinks this is the problem. I have a small amount of experience with performance tuning, so don't hit me.
-
It's just something that seemed pursuable in our system - most of our apps are vendored so we can't mess with the db. In this case, one of our apps was responsible, so I thought I'd look into it.
Anyway, I have some simple queries which are taking a VERY long time to execute on the tsql duration trace. I started another thread today. I'm a little lost when it comes to investigating blocking - but what better time to get started then now?
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply