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 |
|
chriss
Starting Member
3 Posts |
 Posted - 2005-06-28 : 06:18:40
|
| Hi, this is a strange one.I have a partitioned (by date) view on a set of large tables. my query looks up on a index across one or more partitions by date. it works fine and never has any issue. however recently i built a front end in ado.net using a parameterised query with SqlCommand/SqlParamter objects. It seemed to work fine and then after a while of running a few queries the query plan looked to revert to a full table scan of all my partitions. in my case my execution time goes from a minute or so to two hours, not good! if i re-build the partitioned view i'm ok again, but after some time i get the same issue. note i don't get this issue if i just use dynamic sql string with my where values embedded (not using SqlParameter objects).any ideas?Cheers,Chris |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2005-06-28 : 07:05:58
|
| Can you PROFILE the queries getting into server....maybe the execution plan is being changed based on a reevaluation of its options based on the data values being input? |
 |
|
|
chriss
Starting Member
3 Posts |
Posted - 2005-06-28 : 08:08:36
|
| thanks andrew,i'll get our DBA to look at PROFILE, it's probably a good place to start. However with the values i'm sending i wouldn't expect the query plan to evalute to a table scan, the query is not complex it is merely a select .. from table where X = value and Y between on value1 and value2. X is a clustered index and highly selective, Y is the date that the view is partitioned on. |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-06-28 : 22:18:29
|
| Have you tried dumping the cache during this time to see if that fixes the issue? Also, what version/service pack are you on?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
chriss
Starting Member
3 Posts |
Posted - 2005-06-29 : 09:31:53
|
| No haven't done that yet, but worth a try - thanks.currently on SP3, we have sp4 in a test environment at the moment, but i haven't as yet got around to trying to re-produce this issue on that platform. |
|
|
|
|
|
|
|
|
Query plan going bad