Odd behavior in partitioning in SQL 2000

Problem: We've performed distributed partitioning on one of our
largest and most hit tables, over 25 million rows Most of the 15
underlying tables called by the partition view have 1 distinct value
in it clustered index, while others have multiple values (ids). It
works well for most queries ran against it, however there is the
occasional query that seems to run long and create locking on the
underlying partition table.
Resolution 1: Called Microsoft in search of resolution and after
months of back and forth with them their original suggestion was
create an Indexed view. This was shot down in the blue sky phase
because it would undo most of the performance benefits we are seeing.
Their next suggestion was a complete table redesign and that too did
not sit well with management as we had followed the structure
suggestions given in the SBO.
Resolution 2: This was discovered by accident in testing, but, we
found that executing a query as dynamic SQL instead of inline (from a
stored proc). Despite the recompile cost it began using different
indexes and would show us a boost in performance.
Question: Has anyone else seen this behavior before in either
partitioning or with gaining performance increase by converting the
query in a stored proc to dynamic SQL? Microsoft rationalized that
since a view does not have access to a histogram that it may be
creating some of our performance problems and explain that why they
are sporadic instead of constant. Any insight would be greatly
appreciated.
This seems to suggest parameter sniffing:
http://groups.google.ca/groups/search?q=%22parameter+sniffing%22&hl=en&ie=UTF-8&oe=UTF-8&
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Pawn" <TheRealPawn@.gmail.com> wrote in message
news:1181744183.369244.18310@.i38g2000prf.googlegro ups.com...
Problem: We've performed distributed partitioning on one of our
largest and most hit tables, over 25 million rows Most of the 15
underlying tables called by the partition view have 1 distinct value
in it clustered index, while others have multiple values (ids). It
works well for most queries ran against it, however there is the
occasional query that seems to run long and create locking on the
underlying partition table.
Resolution 1: Called Microsoft in search of resolution and after
months of back and forth with them their original suggestion was
create an Indexed view. This was shot down in the blue sky phase
because it would undo most of the performance benefits we are seeing.
Their next suggestion was a complete table redesign and that too did
not sit well with management as we had followed the structure
suggestions given in the SBO.
Resolution 2: This was discovered by accident in testing, but, we
found that executing a query as dynamic SQL instead of inline (from a
stored proc). Despite the recompile cost it began using different
indexes and would show us a boost in performance.
Question: Has anyone else seen this behavior before in either
partitioning or with gaining performance increase by converting the
query in a stored proc to dynamic SQL? Microsoft rationalized that
since a view does not have access to a histogram that it may be
creating some of our performance problems and explain that why they
are sporadic instead of constant. Any insight would be greatly
appreciated.
|||Thanks for the input Tom. I took one of our procedures and tried a
workaround suggested: http://omnibuzz-sql.blogspot.com/2006/11/parameter-sniffing-stored-procedures.html
The end result was that the execution time was actually higher than
the original. The estimated row count was less (from 332 to 44) and
executions jumped from 4 to over 200k. I think this may work for more
simple queries but, dynamic still gives us the best performance.
Any other ideas or insight about this kind of performance problem/fix
seen with SQL 2000 distributed partitioning
On Jun 13, 10:36 am, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
> This seems to suggest parameter sniffing:
> http://groups.google.ca/groups/search?q=%22parameter+sniffing%22&hl=e...
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
>
|||Without seeing all of your DDL and knowing your data distribution, it would
be hard to tell. Since you have a workaround via dynamic SQL, you may very
well have to live with that until you can upgrade to SQL 2005. Just make
sure you put in some defensive coding to prevent SQL injection attacks.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Pawn" <TheRealPawn@.gmail.com> wrote in message
news:1181763906.605010.12270@.d30g2000prg.googlegro ups.com...
Thanks for the input Tom. I took one of our procedures and tried a
workaround suggested:
http://omnibuzz-sql.blogspot.com/2006/11/parameter-sniffing-stored-procedures.html
The end result was that the execution time was actually higher than
the original. The estimated row count was less (from 332 to 44) and
executions jumped from 4 to over 200k. I think this may work for more
simple queries but, dynamic still gives us the best performance.
Any other ideas or insight about this kind of performance problem/fix
seen with SQL 2000 distributed partitioning
On Jun 13, 10:36 am, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
> This seems to suggest parameter sniffing:
> http://groups.google.ca/groups/search?q=%22parameter+sniffing%22&hl=e...
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
>
|||Interesting that you should mention upgrading to 2005. What
enhancement would that grant us or problems would that address. We're
looking for a target date for going to 2005 and any information you
have on it pertaining to this topic may help me make the case that we
should upgrade sooner than later.
|||Some resources:
http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?culture=en-US&EventID=1032276813&CountryCode=US
[url]http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?EventID=1032270016&EventC ategory=3&culture=en-US&CountryCode=US[/url]
http://download.microsoft.com/download/4/1/f/41f09116-19b3-40fe-9d54-d1e9b7af9e82/REAL_Lifecycle_Partitioning.doc
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Pawn" <TheRealPawn@.gmail.com> wrote in message
news:1181931792.613927.65580@.c77g2000hse.googlegro ups.com...
Interesting that you should mention upgrading to 2005. What
enhancement would that grant us or problems would that address. We're
looking for a target date for going to 2005 and any information you
have on it pertaining to this topic may help me make the case that we
should upgrade sooner than later.