Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Soulweaver
Starting Member
18 Posts |
 Posted - 2003-08-01 : 08:56:44
|
| Recently, we have acquired the 'QA' rights for one of our production sql server systems, previously owned by an external vendor.We have been tasked to QA the sql used by the application but aren't sure of a decent rule of thumb when it comes to the estimated execution 'cost' of a query.we've seen a large number of them range between 0.0032 for a single table single row index seek, to 445 for a multitable join with index scans etc, no tablespace scans as yet.what can be seen as a 'good' number? just so that we can find an 80/20 mix of where we should be spending our tuning efforts.Any recommendations etc will be appreciatedThanksTiaan-----------------------Black Holes exist where God divided by Zero----------------------- |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2003-08-01 : 11:44:37
|
My rule is anything above .1 on a small database (sub 1GB) and anything above about 5.0 or so on a larger database is too much.One thing to look at is the IO cost. In my experience, that's what REALY tells you the cost.When you run a query put this at the topSET STATISTICS IO ONexec yourstoredproc This will tell you how many pages etc SQL server had to read to complete your request. If you can reduce that number, you can realy increase your performance. This is really true when you are testing on a small DB that will eventually be a larger DB. On the small DB, 1000 reads will probably return nearly instantly. Once you have a ton of data in the system, those 1000 reads might be more like 8k, or 10k reads. Oh, you mentioned a subtree cost of 445 with index SCANS.Scans are bad, seeks are good. You need to retool the query or the indexes and make sure you get as many index seeks as you can. On small tables (like a state lookup table), a Clustered index scan will probably be faster than an index seek, so be sure to test it both ways.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
 |
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2003-08-01 : 12:02:47
|
| Hi, I work with Soulweaver.Our DB is at 161 GB, we started off using a rule of thumb of greater than 1.0.Yeah - we know to tune to get index scans into index seeks, where possible. It isn't always possible, of course. We had, for example, a query that had a total cost of 6.82, and it's starting point had 2 index seeks, which were being hashed and joined, if I remember corectly. That little section was costing us 3.96 due to number of executions of the index seeks. After some covering indexes, we managed to get it down to 3.12, but then it was doing index scans! Turns out that on one table (5400 rows or so) 88% of the data met the query critieria, and on the other table (522000 rows), 71% met the criteria (320 000 rows or so). With that large an amount of data required, it seems that the index scans are actually more efficient than the index seeks. We eventually managed to get it to 5.92, but no better, unless they will change their criteria to restrict those 2 initial selects.On further investigation, going back and eventually finding a developer, it turns out that the statement forms part of a report, and is only run once a week. It doens't deserve to be this high on our priority list.(you don't want to know the mess we have - we have had to try and reverse a list of SQL statement from upgrade scripts - these are not in stored procedure's ((I know, I know!! Now that the code is owned by the client, and we can QA, we will be pushing for a change, but it means a MASSIVE re-design ... which I feel is needed, but that's another story)))Anyway, since we've had the whole mess dumped on us, we're just trying to get an idea as to what other people think are good heuristics for cost estimates, so thanx for you mail!! What do other people have to say ?CiaO*#&#* *#&#* *#&#* *#&#* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
|
|
|
|
|
Rule of Thumb for estimated sql execution costs