Did SP1 fix Query Optimizer issues w/ Partitioned tables?

Hi there,

We've implemented a partitioned table to a large table within our EDW. However, we'd discovered that the Query Optimizer was not able to understand parameterized queries. It basically was scanning all the partitions even though a parameter was passed defining the partition to use. Consequently, the queries need to hard code parameter values!

Does anyone know if SP1 addressed this problem?

Thanks

-Walter

? Are you certain that it's scanning all of the partitions? You might want to look at the Actual Row Count in the execution plan to find out what's really happening. Partition elimination may not occur when the query is compiled -- instead, it may use a runtime value. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Walti@.discussions.microsoft.com> wrote in message news:fd66588f-8124-45c7-b977-6785bf117a17@.discussions.microsoft.com... Hi there, We've implemented a partitioned table to a large table within our EDW. However, we'd discovered that the Query Optimizer was not able to understand parameterized queries. It basically was scanning all the partitions even though a parameter was passed defining the partition to use. Consequently, the queries need to hard code parameter values! Does anyone know if SP1 addressed this problem? Thanks -Walter|||

Yes, I am sure that it is scanning all the partition. We had a MS champion at the site to overview the full EDW design, and he confirmed that it was a known problem and that it would have been fixed on a future SP release.

-Walter Biffi

|||? Can you describe the scenario in a bit more detail? Or better yet, post a script to reproduce the issue? -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Walti@.discussions.microsoft.com> wrote in message news:a11d1a77-7f52-43cb-bf37-5798188d9a0c@.discussions.microsoft.com... Yes, I am sure that it is scanning all the partition. We had a MS champion at the site to overview the full EDW design, and he confirmed that it was a known problem and that it would have been fixed on a future SP release. -Walter Biffi

Device activation error problem, Index file missing

Hi, everybody.
I have a big problem. One of the disks in RAID 5 array went dead, and
our costumer's administrator tried to fix the problem, which he did, in
the end, but thet ment that all the data on that partition was lost. A
great way to fix the problem, don't you think? Unfortunately, on that
partition there was Index_data.ndf, file that I used for all the
indexes in my database, all the data is in the other file, on the other
partition. There's no backup bacause the database size is about 500GB:(
Now, database is in suspect mode, of course, and I'm thinking about my
options. I know there's a way to put database back from suspect mode
and transport the data using DTS or something to another db, but I'd
really love to skip the part where I get to transport manually 300 GB
of data from one db to another.
What I want to know is this: is there a way to delete indexes from
tables in database, or to delete the index file from database
properties so I could start database again, recreate index file and
recreate indexes?
Any halp is appreciated...
Regards,
MarkoOf course database size does not deteremine backups. Have them buy idera or
redgate. I backed up a 600GB DB to 20GB file with both, no fuss no muss.
On to your problem. Is this 2000? You can reset the suspect flag with
sp_resetstatus. Barring any other options, I would use that proc and then
try to get a backup even if you have to download red-gates eval tool. If you
have a good backup, attempt to drop all indexes located on the now missing
partition by generating a script or turning on ad-hoc updates to system
tables(2k only) and attempting to drop them from there(sysindexes). if you
can't get a reliable backup(ensure you can restore it before doing anything
permanent) once you have the db out of suspect mode, you might have to force
table scans wth queries to get data out as the missing indexes could play
hell with the optimizer and any tool you choose. Red-gate's suite may also
help here, saving you some legwork.
Lastly, slap the admin.
"Marko.Sunjic@.gmail.com" wrote:
> Hi, everybody.
> I have a big problem. One of the disks in RAID 5 array went dead, and
> our costumer's administrator tried to fix the problem, which he did, in
> the end, but thet ment that all the data on that partition was lost. A
> great way to fix the problem, don't you think? Unfortunately, on that
> partition there was Index_data.ndf, file that I used for all the
> indexes in my database, all the data is in the other file, on the other
> partition. There's no backup bacause the database size is about 500GB:(
> Now, database is in suspect mode, of course, and I'm thinking about my
> options. I know there's a way to put database back from suspect mode
> and transport the data using DTS or something to another db, but I'd
> really love to skip the part where I get to transport manually 300 GB
> of data from one db to another.
> What I want to know is this: is there a way to delete indexes from
> tables in database, or to delete the index file from database
> properties so I could start database again, recreate index file and
> recreate indexes?
> Any halp is appreciated...
> Regards,
> Marko
>