Ascending Date Column Statistics "dbcc traceon( 2389 )"

I am having trouble with query performance on SQL Server 2005. The database
is a basic book ordering application. A simple one to many Orders to Order
Items. The orders table contains 80,000 rows and the Order Items table
contains 280,000 rows. The problem query contains an inner join with Orders
and Order Items with where clause on Order Date. The problem is when you
execute the query on old data the inner join is based on a hash (1 second
execution). When you execute the query on new data the inner join is based
on a nested loop (2 minute execution). The reason for this is the stats.
The stats for the order date column do not contain the new data so the
estimated rows for the given date range is 1 (when the query actually returns
1320 rows). If I update the stats for order date the query will perform as
expected (hash join). The following blog explains the issue.
http://blogs.msdn.com/ianjo/archive/2006/04/24/582227.aspx
In order to fix this issue it appears I have to schedule a manual update of
the stats or use dbcc traceon( 2389 ). This application ran on SQL server
2000 for 4 years with no problems. Why did I not see an issue with SQL
server 2000? Does anyone have any better ideas except manually updating the
stats?First of all, have you tried a HASH JOIN hint? If the hash is working well,
force it.
I have also had a lot of statistics problems in some of the applications
I've been working on, and although I haven't had a chance to try these trace
flags yet, I have found workarounds to every issue (generally, involving
query hints and/or plan forcing). I agree, in some cases SQL Server 2005 is
not doing as well as SQL Server 2000 did, but overall I think performance is
improved in most scenarios. Hopefully as MS continues to evaluate use cases
these issues will go away and we can go back to thinking about other things!
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"achopp" <achopp@.discussions.microsoft.com> wrote in message
news:8E35C26D-A3F0-41BB-AE4B-303190B82483@.microsoft.com...
>I am having trouble with query performance on SQL Server 2005. The
>database
> is a basic book ordering application. A simple one to many Orders to
> Order
> Items. The orders table contains 80,000 rows and the Order Items table
> contains 280,000 rows. The problem query contains an inner join with
> Orders
> and Order Items with where clause on Order Date. The problem is when you
> execute the query on old data the inner join is based on a hash (1 second
> execution). When you execute the query on new data the inner join is
> based
> on a nested loop (2 minute execution). The reason for this is the stats.
> The stats for the order date column do not contain the new data so the
> estimated rows for the given date range is 1 (when the query actually
> returns
> 1320 rows). If I update the stats for order date the query will perform
> as
> expected (hash join). The following blog explains the issue.
> http://blogs.msdn.com/ianjo/archive/2006/04/24/582227.aspx
> In order to fix this issue it appears I have to schedule a manual update
> of
> the stats or use dbcc traceon( 2389 ). This application ran on SQL server
> 2000 for 4 years with no problems. Why did I not see an issue with SQL
> server 2000? Does anyone have any better ideas except manually updating
> the
> stats?
>|||Thanks for the help. The HASH Join hint does work.
"Adam Machanic" wrote:
> First of all, have you tried a HASH JOIN hint? If the hash is working well,
> force it.
> I have also had a lot of statistics problems in some of the applications
> I've been working on, and although I haven't had a chance to try these trace
> flags yet, I have found workarounds to every issue (generally, involving
> query hints and/or plan forcing). I agree, in some cases SQL Server 2005 is
> not doing as well as SQL Server 2000 did, but overall I think performance is
> improved in most scenarios. Hopefully as MS continues to evaluate use cases
> these issues will go away and we can go back to thinking about other things!
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "achopp" <achopp@.discussions.microsoft.com> wrote in message
> news:8E35C26D-A3F0-41BB-AE4B-303190B82483@.microsoft.com...
> >I am having trouble with query performance on SQL Server 2005. The
> >database
> > is a basic book ordering application. A simple one to many Orders to
> > Order
> > Items. The orders table contains 80,000 rows and the Order Items table
> > contains 280,000 rows. The problem query contains an inner join with
> > Orders
> > and Order Items with where clause on Order Date. The problem is when you
> > execute the query on old data the inner join is based on a hash (1 second
> > execution). When you execute the query on new data the inner join is
> > based
> > on a nested loop (2 minute execution). The reason for this is the stats.
> > The stats for the order date column do not contain the new data so the
> > estimated rows for the given date range is 1 (when the query actually
> > returns
> > 1320 rows). If I update the stats for order date the query will perform
> > as
> > expected (hash join). The following blog explains the issue.
> >
> > http://blogs.msdn.com/ianjo/archive/2006/04/24/582227.aspx
> >
> > In order to fix this issue it appears I have to schedule a manual update
> > of
> > the stats or use dbcc traceon( 2389 ). This application ran on SQL server
> > 2000 for 4 years with no problems. Why did I not see an issue with SQL
> > server 2000? Does anyone have any better ideas except manually updating
> > the
> > stats?
> >
>
>|||Or possibly the date_correlation_optimization_option database option might help. See also
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/275fb44b-417a-426a-bf7c-f23308ed72c0.htm.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"achopp" <achopp@.discussions.microsoft.com> wrote in message
news:609F8143-0EB4-4400-B84A-F3D75FBDDE95@.microsoft.com...
> Thanks for the help. The HASH Join hint does work.
> "Adam Machanic" wrote:
>> First of all, have you tried a HASH JOIN hint? If the hash is working well,
>> force it.
>> I have also had a lot of statistics problems in some of the applications
>> I've been working on, and although I haven't had a chance to try these trace
>> flags yet, I have found workarounds to every issue (generally, involving
>> query hints and/or plan forcing). I agree, in some cases SQL Server 2005 is
>> not doing as well as SQL Server 2000 did, but overall I think performance is
>> improved in most scenarios. Hopefully as MS continues to evaluate use cases
>> these issues will go away and we can go back to thinking about other things!
>>
>> --
>> Adam Machanic
>> Pro SQL Server 2005, available now
>> http://www.apress.com/book/bookDisplay.html?bID=457
>> --
>>
>> "achopp" <achopp@.discussions.microsoft.com> wrote in message
>> news:8E35C26D-A3F0-41BB-AE4B-303190B82483@.microsoft.com...
>> >I am having trouble with query performance on SQL Server 2005. The
>> >database
>> > is a basic book ordering application. A simple one to many Orders to
>> > Order
>> > Items. The orders table contains 80,000 rows and the Order Items table
>> > contains 280,000 rows. The problem query contains an inner join with
>> > Orders
>> > and Order Items with where clause on Order Date. The problem is when you
>> > execute the query on old data the inner join is based on a hash (1 second
>> > execution). When you execute the query on new data the inner join is
>> > based
>> > on a nested loop (2 minute execution). The reason for this is the stats.
>> > The stats for the order date column do not contain the new data so the
>> > estimated rows for the given date range is 1 (when the query actually
>> > returns
>> > 1320 rows). If I update the stats for order date the query will perform
>> > as
>> > expected (hash join). The following blog explains the issue.
>> >
>> > http://blogs.msdn.com/ianjo/archive/2006/04/24/582227.aspx
>> >
>> > In order to fix this issue it appears I have to schedule a manual update
>> > of
>> > the stats or use dbcc traceon( 2389 ). This application ran on SQL server
>> > 2000 for 4 years with no problems. Why did I not see an issue with SQL
>> > server 2000? Does anyone have any better ideas except manually updating
>> > the
>> > stats?
>> >
>>

articles on sql 2005

Not an article, but the book Pro SQL Server 2005 is the best I've found thus
far to show all the new features.
ISBN: 1-59059-477-0
"noam" <imss@.netvision.net.il> wrote in message
news:1150633683.695303.17100@.y41g2000cwy.googlegroups.com...
> Hi all
> i'm looking for good articles to learned about administration and
> develope on sql 2005
> Thanks
>Hi all
i'm looking for good articles to learned about administration and
develope on sql 2005
Thanks|||Not an article, but the book Pro SQL Server 2005 is the best I've found thus
far to show all the new features.
ISBN: 1-59059-477-0
"noam" <imss@.netvision.net.il> wrote in message
news:1150633683.695303.17100@.y41g2000cwy.googlegroups.com...
> Hi all
> i'm looking for good articles to learned about administration and
> develope on sql 2005
> Thanks
>

article request

can anybody tell me a useful online article,site or a book, to know about SQL
server internalsOn Thu, 13 Oct 2005 02:07:03 -0700, "enbee"
<enbee@.discussions.microsoft.com> wrote:
>can anybody tell me a useful online article,site or a book, to know about SQL
>server internals
You could do a lot worse than Kalen Delaney's book Inside SQL Server
2000.
If you are referrring to SQL Server 2005 I believe an updated edition
is in preparation.
Andrew Watt
MVP - InfoPath|||Inside Microsoft SQL Server 2000
http://vyaskn.tripod.com/inside_microsoft_sql_server_2000.htm
Also, "The Guru's Guide to SQL Server Architecture and Internals"
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"enbee" <enbee@.discussions.microsoft.com> wrote in message
news:9E5DBA88-D015-47FF-AC47-44AFFA4936CB@.microsoft.com...
can anybody tell me a useful online article,site or a book, to know about
SQL
server internals|||SQL Server magazine puts out a CD every 6 months with the latest and
greatest. Kalen Delaney, as one person noted, is certainly an authority on
SQL Server and there are loads of articles she's written. I use it
frequently and often and every chance I get.
A years subscription (which includes online access to SQL Server magazine)
is about $60.
Cheers.
HTH
"enbee" wrote:
> can anybody tell me a useful online article,site or a book, to know about SQL
> server internals

article request

can anybody tell me a useful online article,site or a book, to know about SQL
server internals
On Thu, 13 Oct 2005 02:07:03 -0700, "enbee"
<enbee@.discussions.microsoft.com> wrote:

>can anybody tell me a useful online article,site or a book, to know about SQL
>server internals
You could do a lot worse than Kalen Delaney's book Inside SQL Server
2000.
If you are referrring to SQL Server 2005 I believe an updated edition
is in preparation.
Andrew Watt
MVP - InfoPath
|||Inside Microsoft SQL Server 2000
http://vyaskn.tripod.com/inside_micr...erver_2000.htm
Also, "The Guru's Guide to SQL Server Architecture and Internals"
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"enbee" <enbee@.discussions.microsoft.com> wrote in message
news:9E5DBA88-D015-47FF-AC47-44AFFA4936CB@.microsoft.com...
can anybody tell me a useful online article,site or a book, to know about
SQL
server internals
|||SQL Server magazine puts out a CD every 6 months with the latest and
greatest. Kalen Delaney, as one person noted, is certainly an authority on
SQL Server and there are loads of articles she's written. I use it
frequently and often and every chance I get.
A years subscription (which includes online access to SQL Server magazine)
is about $60.
Cheers.
HTH
"enbee" wrote:

> can anybody tell me a useful online article,site or a book, to know about SQL
> server internals

article request

can anybody tell me a useful online article,site or a book, to know about SQ
L
server internalsOn Thu, 13 Oct 2005 02:07:03 -0700, "enbee"
<enbee@.discussions.microsoft.com> wrote:

>can anybody tell me a useful online article,site or a book, to know about S
QL
>server internals
You could do a lot worse than Kalen Delaney's book Inside SQL Server
2000.
If you are referrring to SQL Server 2005 I believe an updated edition
is in preparation.
Andrew Watt
MVP - InfoPath|||Inside Microsoft SQL Server 2000
http://vyaskn.tripod.com/inside_mic...server_2000.htm
Also, "The Guru's Guide to SQL Server Architecture and Internals"
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"enbee" <enbee@.discussions.microsoft.com> wrote in message
news:9E5DBA88-D015-47FF-AC47-44AFFA4936CB@.microsoft.com...
can anybody tell me a useful online article,site or a book, to know about
SQL
server internals|||SQL Server magazine puts out a CD every 6 months with the latest and
greatest. Kalen Delaney, as one person noted, is certainly an authority on
SQL Server and there are loads of articles she's written. I use it
frequently and often and every chance I get.
A years subscription (which includes online access to SQL Server magazine)
is about $60.
Cheers.
HTH
"enbee" wrote:

> can anybody tell me a useful online article,site or a book, to know about
SQL
> server internals