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 |
|
bigsapper
Starting Member
8 Posts |
 Posted - 2008-04-16 : 10:57:59
|
Hi all. I'm new to this forum and looking for some assistance.I've run into a unique (for me) performance problem.I have a select statement that performs fine ( < 1 second ) using one set of values in the criteria but very poorly ( > 3 minutes )using different values. In both circumstances the query returns zero rows. The query involves a parent-child join with the criteria spread across both tables.The execution plan looks similar between the two; the difference being a few percentage points difference on some of the operations. The tuning advisor has no recommendation in case 1 but suggests a couple of additional indexes and 4 statistics in case 2.My gut tells me that the solution is *not* applying the additional indexes/statistics but some other issue. Or it could be the sushi I just ate.  Anyway, I'm hoping someone can point me in the right direction as what to analyze to determine why simply changing a single supplied criteria value would have such a dramatic effect on performance. |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2008-04-16 : 12:36:35
|
| can you post the execution plans?you may improve things by researching the topic "parameter sniffing" posted elsehwere on this forum. |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2008-04-18 : 03:13:22
|
| Can you post the query and the table and existing index definition please?--Gail Shaw |
|
|
|
bigsapper
Starting Member
8 Posts |
Posted - 2008-04-18 : 07:37:38
|
| I'll post the query plans when I get into the office.I applied the parameter hint without success. |
|
|
|
bigsapper
Starting Member
8 Posts |
Posted - 2008-04-18 : 09:45:03
|
| Sorry for wasting anyone's time with this. I have a covering index for the query but it wasn't being used. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-04-18 : 09:53:19
|
| Unless you post the query, it would be very difficult for anybody to help you on this. |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2008-04-19 : 05:09:58
|
| Are your statistics up to date?Jack Vamvas--------------------Search IT jobs from multiple sources- http://www.ITjobfeed.com |
|
|
|
bigsapper
Starting Member
8 Posts |
Posted - 2008-04-19 : 07:34:59
|
| Sorry I wasn't more clear in my previous post. I knew my query had a covering index. I *assumed* it was being used. In looking at the query plan again, I discovered it wasn't so I just added a WITH ( INDEX ( ... ) ) and all is well, now. |
|
|
|
|
|
|
Query Performance Troubleshooting Help