|
|
You're in the
sql server data warehouse
group:Statistics, reindex, defragindex... which first?
sql server data warehouse:
in a datawarehouse with SQL Server 2000, what are your recommendations for when to reindex; update stats or just defrag the indexes? Actually, I update the statistics of the loaded table in my DTS package. So, I've 1 DTS by table, and , at the end of the package, I update with a full scan my statistics. Because SQL server completly lost its stats after to many updates, and the query performance slow down. But when defragmagting indexes? or reindex it? does a reindex can update the statistics at the same time? Some of my tables will be updated daily, and other tables weekly. I've incremental processes and full table processes (I truncate my table and refill it or I delete the current year information and refill it) So I presume I have to do different job regarding how the table is filled. thanks for your guides ! Jerome.
Unlike DBCC DBREINDEX or any general index build, DBCC INDEXDEFRAG is an
online operation, so it does not hold long-term locks that can block running queries or updates. Depending on the amount of fragmentation, DBCC INDEXDEFRAG can be considerably faster than running DBCC DBREINDEX because a relatively unfragmented index can be defragmented much faster than a new index can be built. Another advantage is that with DBCC INDEXDEFRAG, the index is always available, unlike DBREINDEX. A large amount of fragmentation can cause DBCC INDEXDEFRAG to run considerably longer than DBCC DBREINDEX, which may or may not outweigh the benefit of the command's online capabilities. DBCC INDEXDEFRAG will not help if two indexes are interleaved on the disk because INDEXDEFRAG shuffles the pages in place. To improve the clustering of pages, rebuild the index. [quoted text, click to view] "Jéjé" <willgart@_A_hAotmail_A_.com> wrote in message news:uD8nGV8gEHA.3536@TK2MSFTNGP12.phx.gbl... > Hi, > > in a datawarehouse with SQL Server 2000, > what are your recommendations for when to reindex; update stats or just > defrag the indexes? > > Actually, I update the statistics of the loaded table in my DTS package. > So, I've 1 DTS by table, and , at the end of the package, I update with a > full scan my statistics. > Because SQL server completly lost its stats after to many updates, and the > query performance slow down. > > But when defragmagting indexes? or reindex it? > > does a reindex can update the statistics at the same time? > > Some of my tables will be updated daily, and other tables weekly. I've > incremental processes and full table processes (I truncate my table and > refill it or I delete the current year information and refill it) > So I presume I have to do different job regarding how the table is filled. > > thanks for your guides ! > > Jerome. > >
And to answer the second question, a full rebuild of the indexes also
updates statistics, but indexdefrag does not. -- Wayne Snyder, MCDBA, SQL Server MVP Mariner, Charlotte, NC www.mariner-usa.com (Please respond only to the newsgroups.) I support the Professional Association of SQL Server (PASS) and it's community of SQL Server professionals. www.sqlpass.org [quoted text, click to view] "Jéjé" <willgart@_A_hAotmail_A_.com> wrote in message news:uD8nGV8gEHA.3536@TK2MSFTNGP12.phx.gbl... > Hi, > > in a datawarehouse with SQL Server 2000, > what are your recommendations for when to reindex; update stats or just > defrag the indexes? > > Actually, I update the statistics of the loaded table in my DTS package. > So, I've 1 DTS by table, and , at the end of the package, I update with a > full scan my statistics. > Because SQL server completly lost its stats after to many updates, and the > query performance slow down. > > But when defragmagting indexes? or reindex it? > > does a reindex can update the statistics at the same time? > > Some of my tables will be updated daily, and other tables weekly. I've > incremental processes and full table processes (I truncate my table and > refill it or I delete the current year information and refill it) > So I presume I have to do different job regarding how the table is filled. > > thanks for your guides ! > > Jerome. > >
does the full rebuild of an index updates ONLY the statistics of the index
? or does this action will update ALL statistics? Because some columns has no indexes, but there is statistics. "Wayne Snyder" <wayne.nospam.snyder@mariner-usa.com> a écrit dans le message de news:eGJMtydhEHA.2784@TK2MSFTNGP09.phx.gbl... [quoted text, click to view] > And to answer the second question, a full rebuild of the indexes also > updates statistics, but indexdefrag does not. > > -- > Wayne Snyder, MCDBA, SQL Server MVP > Mariner, Charlotte, NC > www.mariner-usa.com > (Please respond only to the newsgroups.) > > I support the Professional Association of SQL Server (PASS) and it's > community of SQL Server professionals. > www.sqlpass.org > > "Jéjé" <willgart@_A_hAotmail_A_.com> wrote in message > news:uD8nGV8gEHA.3536@TK2MSFTNGP12.phx.gbl... > > Hi, > > > > in a datawarehouse with SQL Server 2000, > > what are your recommendations for when to reindex; update stats or just > > defrag the indexes? > > > > Actually, I update the statistics of the loaded table in my DTS package. > > So, I've 1 DTS by table, and , at the end of the package, I update with a > > full scan my statistics. > > Because SQL server completly lost its stats after to many updates, and the > > query performance slow down. > > > > But when defragmagting indexes? or reindex it? > > > > does a reindex can update the statistics at the same time? > > > > Some of my tables will be updated daily, and other tables weekly. I've > > incremental processes and full table processes (I truncate my table and > > refill it or I delete the current year information and refill it) > > So I presume I have to do different job regarding how the table is filled. > > > > thanks for your guides ! > > > > Jerome. > > > > > >
Reindex only updates the statistics on the indexes that are reindexed.
But if you have statistics on a column without an index, it is an indication that an index on that column could improve performance, and you should consider creating an index on it. -- Jacco Schalkwijk SQL Server MVP [quoted text, click to view] "Jéjé" <willgart@_A_hAotmail_A_.com> wrote in message news:elI4r1ehEHA.2916@TK2MSFTNGP12.phx.gbl... > does the full rebuild of an index updates ONLY the statistics of the index > ? > or does this action will update ALL statistics? > > Because some columns has no indexes, but there is statistics. > > "Wayne Snyder" <wayne.nospam.snyder@mariner-usa.com> a écrit dans le > message > de news:eGJMtydhEHA.2784@TK2MSFTNGP09.phx.gbl... >> And to answer the second question, a full rebuild of the indexes also >> updates statistics, but indexdefrag does not. >> >> -- >> Wayne Snyder, MCDBA, SQL Server MVP >> Mariner, Charlotte, NC >> www.mariner-usa.com >> (Please respond only to the newsgroups.) >> >> I support the Professional Association of SQL Server (PASS) and it's >> community of SQL Server professionals. >> www.sqlpass.org >> >> "Jéjé" <willgart@_A_hAotmail_A_.com> wrote in message >> news:uD8nGV8gEHA.3536@TK2MSFTNGP12.phx.gbl... >> > Hi, >> > >> > in a datawarehouse with SQL Server 2000, >> > what are your recommendations for when to reindex; update stats or just >> > defrag the indexes? >> > >> > Actually, I update the statistics of the loaded table in my DTS >> > package. >> > So, I've 1 DTS by table, and , at the end of the package, I update with > a >> > full scan my statistics. >> > Because SQL server completly lost its stats after to many updates, and > the >> > query performance slow down. >> > >> > But when defragmagting indexes? or reindex it? >> > >> > does a reindex can update the statistics at the same time? >> > >> > Some of my tables will be updated daily, and other tables weekly. I've >> > incremental processes and full table processes (I truncate my table and >> > refill it or I delete the current year information and refill it) >> > So I presume I have to do different job regarding how the table is > filled. >> > >> > thanks for your guides ! >> > >> > Jerome. >> > >> > >> >> > >
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |