Index Tuning with SQLXML

Is there an easy way to generate indexes based on xpath queries and a schema?
The problem that I am having is that the profiler is not able to capture my
larger queries because the generated query is too long. Because of this i
can not see the generated sql code and I am unable to decide how to generate
indexes from that. It seems like it would not be difficult (With inside
knowlege of how the queries are generated) to build a tool that suggests
indexes based off of xpath queries however I have been unable to find such a
tool.
Is there another method to retrieve the sql generated from an XPath query
other than SQL Profiler?
Tony
Senior Software Engineer / Network Administrator
Hi Tony,
Welcome to use MSDN Managed Newsgroup!
SQL Profiler is the only way for us trace the SQL queries.
For SQL Server 2005 and related issue, you are welcome to paste in the
newsgroup below
Welcome to the Microsoft SQL Server 2005 Community Technology (CTP)
Newsgroups
http://communities.microsoft.com/new...lserver2005&sl
cid=us
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||We are basically generating FOR XML EXPLICIT queries which are basically
large UNION ALL queries. Did you try to use the index tuning wizzard to see
if it can recommend indices for these queries?
Best regards
Michael
"Tony Moulton" <precursor@.community.nospam> wrote in message
news:F1C423FA-3894-4947-A35F-EFA0E9951C2D@.microsoft.com...
> Is there an easy way to generate indexes based on xpath queries and a
> schema?
> The problem that I am having is that the profiler is not able to capture
> my
> larger queries because the generated query is too long. Because of this i
> can not see the generated sql code and I am unable to decide how to
> generate
> indexes from that. It seems like it would not be difficult (With inside
> knowlege of how the queries are generated) to build a tool that suggests
> indexes based off of xpath queries however I have been unable to find such
> a
> tool.
> Is there another method to retrieve the sql generated from an XPath query
> other than SQL Profiler?
> --
> Tony
> Senior Software Engineer / Network Administrator
>

Index Tuning with SQLXML

Is there an easy way to generate indexes based on xpath queries and a schema
?
The problem that I am having is that the profiler is not able to capture my
larger queries because the generated query is too long. Because of this i
can not see the generated sql code and I am unable to decide how to generate
indexes from that. It seems like it would not be difficult (With inside
knowlege of how the queries are generated) to build a tool that suggests
indexes based off of xpath queries however I have been unable to find such a
tool.
Is there another method to retrieve the sql generated from an XPath query
other than SQL Profiler?
Tony
Senior Software Engineer / Network AdministratorHi Tony,
Welcome to use MSDN Managed Newsgroup!
SQL Profiler is the only way for us trace the SQL queries.
For SQL Server 2005 and related issue, you are welcome to paste in the
newsgroup below
Welcome to the Microsoft SQL Server 2005 Community Technology (CTP)
Newsgroups
http://communities.microsoft.com/ne...qlserver2005&sl
cid=us
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||We are basically generating FOR XML EXPLICIT queries which are basically
large UNION ALL queries. Did you try to use the index tuning wizzard to see
if it can recommend indices for these queries?
Best regards
Michael
"Tony Moulton" <precursor@.community.nospam> wrote in message
news:F1C423FA-3894-4947-A35F-EFA0E9951C2D@.microsoft.com...
> Is there an easy way to generate indexes based on xpath queries and a
> schema?
> The problem that I am having is that the profiler is not able to capture
> my
> larger queries because the generated query is too long. Because of this i
> can not see the generated sql code and I am unable to decide how to
> generate
> indexes from that. It seems like it would not be difficult (With inside
> knowlege of how the queries are generated) to build a tool that suggests
> indexes based off of xpath queries however I have been unable to find such
> a
> tool.
> Is there another method to retrieve the sql generated from an XPath query
> other than SQL Profiler?
> --
> Tony
> Senior Software Engineer / Network Administrator
>sql

Index tuning under heavy load...

I am trying to find the best way to tune/defrag the indexes on a database
which stays under heavy load 24/7/365
About 1-5 million records added daily...By the end of the day query
performance decreses dramatically.
DBCC DBREINDEX does the job the best but it is an OFFLINE operation and is
unacceptable in my case as the table becomes offline and users get hung...
ALTER INDEX REBUILD (WITH ONLINE) works ok, still queries are running slower
during this operation and it does not do as good of a job as DBREINDEX.
What are my options? Is there a solution to this...?I should add that we are on SQL Server 2005
"Michael Kansky" <mike@.zazasoftware.com> wrote in message
news:eAiuJRM6HHA.464@.TK2MSFTNGP02.phx.gbl...
>I am trying to find the best way to tune/defrag the indexes on a database
>which stays under heavy load 24/7/365
> About 1-5 million records added daily...By the end of the day query
> performance decreses dramatically.
> DBCC DBREINDEX does the job the best but it is an OFFLINE operation and is
> unacceptable in my case as the table becomes offline and users get hung...
> ALTER INDEX REBUILD (WITH ONLINE) works ok, still queries are running
> slower during this operation and it does not do as good of a job as
> DBREINDEX.
> What are my options? Is there a solution to this...?
>|||If you are on 2005 you should be using ALTER INDEX not DBCC xxx. Reindexing
is a very resource intensive operation and if you want to do this in a 24x7
operation you need to have the hardware to support it or performance will
suffer. But I would argue that your fill factors are not properly set to
limit fragmentation if by the end of the day performance suffers from
fragmentation. But fragmentation should not impact a properly tuned OLTP
system that much anyway. If you do a lot of scans it can hurt you but you
should find out why you are scanning and address that. Perhaps partitioning
is called for here as well. If you can partition such that most of the new
rows are in a different partition than the rest you won't need to rebuild
all the indexes.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Michael Kansky" <mike@.zazasoftware.com> wrote in message
news:%23LrM9cM6HHA.3940@.TK2MSFTNGP05.phx.gbl...
>I should add that we are on SQL Server 2005
>
> "Michael Kansky" <mike@.zazasoftware.com> wrote in message
> news:eAiuJRM6HHA.464@.TK2MSFTNGP02.phx.gbl...
>>I am trying to find the best way to tune/defrag the indexes on a database
>>which stays under heavy load 24/7/365
>> About 1-5 million records added daily...By the end of the day query
>> performance decreses dramatically.
>> DBCC DBREINDEX does the job the best but it is an OFFLINE operation and
>> is unacceptable in my case as the table becomes offline and users get
>> hung...
>> ALTER INDEX REBUILD (WITH ONLINE) works ok, still queries are running
>> slower during this operation and it does not do as good of a job as
>> DBREINDEX.
>> What are my options? Is there a solution to this...?
>|||I submit that it isn't index frag that is hurting here, but rather the
statistics. New records won't be reflected in the stats (assuming a large
table in which 1-5M rows doesn't trigger an automatic stats update). Thus
queries involving the newly inserted rows won't have optimal query plans
(such as index seeks).
Set up a job to refresh your stats several times throughout the day. Be
careful of the type of scan performed. You need the scan to go quickly.
For those indexes that receive values throughout the range of the index
(like on LastName for example), fragmentation can become an issue. Pick a
reasonable fillfactor to avoid lots of page splits between rebuilds.
Picking this number is part art, but mostly science.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Michael Kansky" <mike@.zazasoftware.com> wrote in message
news:eAiuJRM6HHA.464@.TK2MSFTNGP02.phx.gbl...
>I am trying to find the best way to tune/defrag the indexes on a database
>which stays under heavy load 24/7/365
> About 1-5 million records added daily...By the end of the day query
> performance decreses dramatically.
> DBCC DBREINDEX does the job the best but it is an OFFLINE operation and is
> unacceptable in my case as the table becomes offline and users get hung...
> ALTER INDEX REBUILD (WITH ONLINE) works ok, still queries are running
> slower during this operation and it does not do as good of a job as
> DBREINDEX.
> What are my options? Is there a solution to this...?
>|||I think you are absolutely right...
And i do need a really fast scan while updating stats because i think it
places share locks on the table..
Will this be the fastest scan i can achieve:
UPDATE STATISTICS Database.Table
WITH SAMPLE 5 PERCENT;
Thank you,
michael
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:uNHfyoX6HHA.5160@.TK2MSFTNGP05.phx.gbl...
>I submit that it isn't index frag that is hurting here, but rather the
>statistics. New records won't be reflected in the stats (assuming a large
>table in which 1-5M rows doesn't trigger an automatic stats update). Thus
>queries involving the newly inserted rows won't have optimal query plans
>(such as index seeks).
> Set up a job to refresh your stats several times throughout the day. Be
> careful of the type of scan performed. You need the scan to go quickly.
> For those indexes that receive values throughout the range of the index
> (like on LastName for example), fragmentation can become an issue. Pick a
> reasonable fillfactor to avoid lots of page splits between rebuilds.
> Picking this number is part art, but mostly science.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Michael Kansky" <mike@.zazasoftware.com> wrote in message
> news:eAiuJRM6HHA.464@.TK2MSFTNGP02.phx.gbl...
>>I am trying to find the best way to tune/defrag the indexes on a database
>>which stays under heavy load 24/7/365
>> About 1-5 million records added daily...By the end of the day query
>> performance decreses dramatically.
>> DBCC DBREINDEX does the job the best but it is an OFFLINE operation and
>> is unacceptable in my case as the table becomes offline and users get
>> hung...
>> ALTER INDEX REBUILD (WITH ONLINE) works ok, still queries are running
>> slower during this operation and it does not do as good of a job as
>> DBREINDEX.
>> What are my options? Is there a solution to this...?
>|||Be a little careful of too low of a sample. If the problem is due to rows
that the optimizer doesn't know about you may get similar results with only
a 5% sample. This is usually a try and see, then adjust methodology.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Michael Kansky" <mike@.zazasoftware.com> wrote in message
news:OD5EkRY6HHA.5844@.TK2MSFTNGP02.phx.gbl...
>I think you are absolutely right...
> And i do need a really fast scan while updating stats because i think it
> places share locks on the table..
> Will this be the fastest scan i can achieve:
> UPDATE STATISTICS Database.Table
> WITH SAMPLE 5 PERCENT;
> Thank you,
> michael
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:uNHfyoX6HHA.5160@.TK2MSFTNGP05.phx.gbl...
>>I submit that it isn't index frag that is hurting here, but rather the
>>statistics. New records won't be reflected in the stats (assuming a large
>>table in which 1-5M rows doesn't trigger an automatic stats update). Thus
>>queries involving the newly inserted rows won't have optimal query plans
>>(such as index seeks).
>> Set up a job to refresh your stats several times throughout the day. Be
>> careful of the type of scan performed. You need the scan to go quickly.
>> For those indexes that receive values throughout the range of the index
>> (like on LastName for example), fragmentation can become an issue. Pick
>> a reasonable fillfactor to avoid lots of page splits between rebuilds.
>> Picking this number is part art, but mostly science.
>> --
>> TheSQLGuru
>> President
>> Indicium Resources, Inc.
>> "Michael Kansky" <mike@.zazasoftware.com> wrote in message
>> news:eAiuJRM6HHA.464@.TK2MSFTNGP02.phx.gbl...
>>I am trying to find the best way to tune/defrag the indexes on a database
>>which stays under heavy load 24/7/365
>> About 1-5 million records added daily...By the end of the day query
>> performance decreses dramatically.
>> DBCC DBREINDEX does the job the best but it is an OFFLINE operation and
>> is unacceptable in my case as the table becomes offline and users get
>> hung...
>> ALTER INDEX REBUILD (WITH ONLINE) works ok, still queries are running
>> slower during this operation and it does not do as good of a job as
>> DBREINDEX.
>> What are my options? Is there a solution to this...?
>>
>

Index Tuning Problem

Hello:
I am attempting to tune the indexes on a frequently used
SQL 2000 (Enterprise Edition)database. I used Profiler
to create a workload file that captured 3/4 of a day's
transactions.
I started the Index Tuning Wizard and went through the
steps to have the wizard analyze my workload file. After
analyzing the database for awhile, the wizard returns the
following message:
The workload does not contain any events or queries that
can be tuned against the current database. Check to see
that the trace contains valid events or that the SQL
script contains valid queries and you are tuning against
the right database.
The workload file is close to 5GB in size and definitely
contains stored procedures and SQL statements. And I am
definitely sitting in the proper database.
Any comments or suggestions would be greatly appreciated
BrennanThis is a multi-part message in MIME format.
--=_NextPart_000_08A3_01C39C8E.8B8E6540
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
You may need to save it as a script and then put in a use statement.
Otherwise, it will assume master. For example, if your database is MyDB:
use MyDB
go
-- the rest of the script goes here
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Brennan" <anonymous@.discussions.microsoft.com> wrote in message
news:011601c39cb7$c7373c60$a401280a@.phx.gbl...
Hello:
I am attempting to tune the indexes on a frequently used
SQL 2000 (Enterprise Edition)database. I used Profiler
to create a workload file that captured 3/4 of a day's
transactions.
I started the Index Tuning Wizard and went through the
steps to have the wizard analyze my workload file. After
analyzing the database for awhile, the wizard returns the
following message:
The workload does not contain any events or queries that
can be tuned against the current database. Check to see
that the trace contains valid events or that the SQL
script contains valid queries and you are tuning against
the right database.
The workload file is close to 5GB in size and definitely
contains stored procedures and SQL statements. And I am
definitely sitting in the proper database.
Any comments or suggestions would be greatly appreciated
Brennan
--=_NextPart_000_08A3_01C39C8E.8B8E6540
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

You may need to save it as a script =and then put in a use statement. Otherwise, it will assume master. For =example, if your database is MyDB:
use MyDB
go
-- the rest of the script goes here
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Brennan" wrote in message news:011601c39cb7$c7=373c60$a401280a@.phx.gbl...Hello:I am attempting to tune the indexes on a frequently used SQL 2000 =(Enterprise Edition)database. I used Profiler to create a workload file =that captured 3/4 of a day's transactions.I started the Index =Tuning Wizard and went through the steps to have the wizard analyze my =workload file. After analyzing the database for awhile, the wizard =returns the following message:The workload does not contain any events =or queries that can be tuned against the current database. Check =to see that the trace contains valid events or that the SQL script =contains valid queries and you are tuning against the right =database.The workload file is close to 5GB in size and definitely contains stored =procedures and SQL statements. And I am definitely sitting in =the proper database.Any comments or suggestions would be greatly =appreciated Brennan

---

--=_NextPart_000_08A3_01C39C8E.8B8E6540--|||How do I save it as a script?
Brennan
>--Original Message--
>You may need to save it as a script and then put in a
use statement.
>Otherwise, it will assume master. For example, if your
database is MyDB:
>use MyDB
>go
>-- the rest of the script goes here
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Brennan" <anonymous@.discussions.microsoft.com> wrote in
message
>news:011601c39cb7$c7373c60$a401280a@.phx.gbl...
>Hello:
>I am attempting to tune the indexes on a frequently used
>SQL 2000 (Enterprise Edition)database. I used Profiler
>to create a workload file that captured 3/4 of a day's
>transactions.
>I started the Index Tuning Wizard and went through the
>steps to have the wizard analyze my workload file. After
>analyzing the database for awhile, the wizard returns the
>following message:
>The workload does not contain any events or queries that
>can be tuned against the current database. Check to see
>that the trace contains valid events or that the SQL
>script contains valid queries and you are tuning against
>the right database.
>The workload file is close to 5GB in size and definitely
>contains stored procedures and SQL statements. And I am
>definitely sitting in the proper database.
>Any comments or suggestions would be greatly appreciated
>Brennan
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0944_01C39C96.6BD78620
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
I was referring to your workload script. You can load your trace into the
Profiler and then click on File->Save As->SQL Script.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Brennan" <anonymous@.discussions.microsoft.com> wrote in message
news:038301c39cbf$4da0b450$a101280a@.phx.gbl...
How do I save it as a script?
Brennan
>--Original Message--
>You may need to save it as a script and then put in a
use statement.
>Otherwise, it will assume master. For example, if your
database is MyDB:
>use MyDB
>go
>-- the rest of the script goes here
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Brennan" <anonymous@.discussions.microsoft.com> wrote in
message
>news:011601c39cb7$c7373c60$a401280a@.phx.gbl...
>Hello:
>I am attempting to tune the indexes on a frequently used
>SQL 2000 (Enterprise Edition)database. I used Profiler
>to create a workload file that captured 3/4 of a day's
>transactions.
>I started the Index Tuning Wizard and went through the
>steps to have the wizard analyze my workload file. After
>analyzing the database for awhile, the wizard returns the
>following message:
>The workload does not contain any events or queries that
>can be tuned against the current database. Check to see
>that the trace contains valid events or that the SQL
>script contains valid queries and you are tuning against
>the right database.
>The workload file is close to 5GB in size and definitely
>contains stored procedures and SQL statements. And I am
>definitely sitting in the proper database.
>Any comments or suggestions would be greatly appreciated
>Brennan
>
--=_NextPart_000_0944_01C39C96.6BD78620
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

I was referring to your workload =script. You can load your trace into the Profiler and then click on File->Save =As->SQL Script.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Brennan" wrote in message news:038301c39cbf$4d=a0b450$a101280a@.phx.gbl...How do I save it as a script?Brennan>--Original Message-->You may need to save it as a script and then put in =a use statement.>Otherwise, it will assume master. For =example, if your database is MyDB:>>use =MyDB>go>-- the rest of the script goes here>>-->Tom>>--=---->Thomas A. Moreau, BSc, PhD, MCSE, MCDBA>SQL Server MVP>Columnist, =SQL Server Professional>Toronto, ON Canada>www.pinnaclepublishing.com/sql>>>"Bren=nan" wrote in message>news:011601c39cb7$c7373c60$a401280a@.phx.gbl...>=Hello:>>I am attempting to tune the indexes on a frequently used>SQL 2000 (Enterprise Edition)database. I used Profiler>to create a =workload file that captured 3/4 of a day's>transactions.>>I =started the Index Tuning Wizard and went through the>steps to have the =wizard analyze my workload file. After>analyzing the database for =awhile, the wizard returns the>following message:>>The =workload does not contain any events or queries that>can be tuned against =the current database. Check to see>that the trace contains =valid events or that the SQL>script contains valid queries and you are tuning against>the right database.>>The workload file is =close to 5GB in size and definitely>contains stored procedures and SQL statements. And I am>definitely sitting in the proper database.>>Any comments or suggestions would be greatly appreciated>>Brennan>

---

--=_NextPart_000_0944_01C39C96.6BD78620--

Index troubles

We're seeing funny things with SQL Server 2000 when dealing with indexes on
a customer table. When we issue:
DBCC reindex (cust)
we see an error Message 169: a column has been specified more than once in
the order by list.
We see the same error during Update Statistics.
We're using the Query analyzer - index tuning wizard to create indexes on
this table. The table has 12 indexes, and the field Cust_num is used in 7 of
12 indexes.
We tried to remove indexes one by one, we get same Message 169.
Any clues? Thanks!!> The table has 12 indexes,
WOW! That isn't the most I've ever seen, but it's certainly more than I'm
used to. Why is one column covered in seven different indices?
> We tried to remove indexes one by one, we get same Message 169.
Can you show the result of:
EXEC sp_help tablename
EXEC sp_helpindex tablename|||Here is the output from sp_helpindex
I think the hypotheticals are added by the Index wizard ... how do you get
rid of these?
--
Address nonclustered located on PRIMARY Address
CustID nonclustered unique located on PRIMARY CustID
CustNum nonclustered unique located on PRIMARY CustNum
hind_1682821057_24A_2A nonclustered hypothetical auto create located on
PRIMARY PriceCode CustNum
hind_1682821057_2A_24A_50A nonclustered hypothetical auto create located
on PRIMARY CustNum PriceCode AssignedLocation
hind_1682821057_2A_50A nonclustered hypothetical auto create located on
PRIMARY CustNum AssignedLocation
hind_1682821057_50A_24A_2A nonclustered hypothetical auto create located
on PRIMARY AssignedLocation PriceCode CustNum
hind_1682821057_50A_2A nonclustered hypothetical auto create located on
PRIMARY AssignedLocation CustNum
hind_c_1682821057_24A clustered hypothetical auto create located on
PRIMARY PriceCode
hind_c_1682821057_24A_2A clustered hypothetical auto create located on
PRIMARY PriceCode CustNum
hind_c_1682821057_2A clustered hypothetical auto create located on PRIMARY
CustNum
hind_c_1682821057_2A_24A clustered hypothetical auto create located on
PRIMARY CustNum PriceCode
Name nonclustered located on PRIMARY Name
tblCust10 nonclustered located on PRIMARY CustNum Name City Phone
tblCust13 nonclustered located on PRIMARY CustNum SalesComm
tblCust2 clustered located on PRIMARY State CustNum
tblCust23 nonclustered located on PRIMARY CustNum PriceCode
tblCust25 nonclustered located on PRIMARY CustNum Name City State
tblCust26 nonclustered located on PRIMARY PriceCode CustNum Name City
State
tblCust31 nonclustered located on PRIMARY AssignedLocation PriceCode
tblCust7 nonclustered located on PRIMARY SalesComm DateNewComm
tblCust9 nonclustered located on PRIMARY CustNum PriceIncrease
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:#oAjuKxLFHA.3844@.TK2MSFTNGP14.phx.gbl...
> > The table has 12 indexes,
> WOW! That isn't the most I've ever seen, but it's certainly more than I'm
> used to. Why is one column covered in seven different indices?
> > We tried to remove indexes one by one, we get same Message 169.
> Can you show the result of:
> EXEC sp_help tablename
> EXEC sp_helpindex tablename
>|||DROP INDEX should do it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Dean J Garrett" <info@.amuletc.com> wrote in message news:%23Sxvb3xLFHA.2648@.TK2MSFTNGP14.phx.gbl...
> Here is the output from sp_helpindex
> I think the hypotheticals are added by the Index wizard ... how do you get
> rid of these?
> --
> Address nonclustered located on PRIMARY Address
> CustID nonclustered unique located on PRIMARY CustID
> CustNum nonclustered unique located on PRIMARY CustNum
> hind_1682821057_24A_2A nonclustered hypothetical auto create located on
> PRIMARY PriceCode CustNum
> hind_1682821057_2A_24A_50A nonclustered hypothetical auto create located
> on PRIMARY CustNum PriceCode AssignedLocation
> hind_1682821057_2A_50A nonclustered hypothetical auto create located on
> PRIMARY CustNum AssignedLocation
> hind_1682821057_50A_24A_2A nonclustered hypothetical auto create located
> on PRIMARY AssignedLocation PriceCode CustNum
> hind_1682821057_50A_2A nonclustered hypothetical auto create located on
> PRIMARY AssignedLocation CustNum
> hind_c_1682821057_24A clustered hypothetical auto create located on
> PRIMARY PriceCode
> hind_c_1682821057_24A_2A clustered hypothetical auto create located on
> PRIMARY PriceCode CustNum
> hind_c_1682821057_2A clustered hypothetical auto create located on PRIMARY
> CustNum
> hind_c_1682821057_2A_24A clustered hypothetical auto create located on
> PRIMARY CustNum PriceCode
> Name nonclustered located on PRIMARY Name
> tblCust10 nonclustered located on PRIMARY CustNum Name City Phone
> tblCust13 nonclustered located on PRIMARY CustNum SalesComm
> tblCust2 clustered located on PRIMARY State CustNum
> tblCust23 nonclustered located on PRIMARY CustNum PriceCode
> tblCust25 nonclustered located on PRIMARY CustNum Name City State
> tblCust26 nonclustered located on PRIMARY PriceCode CustNum Name City
> State
> tblCust31 nonclustered located on PRIMARY AssignedLocation PriceCode
> tblCust7 nonclustered located on PRIMARY SalesComm DateNewComm
> tblCust9 nonclustered located on PRIMARY CustNum PriceIncrease
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:#oAjuKxLFHA.3844@.TK2MSFTNGP14.phx.gbl...
>> > The table has 12 indexes,
>> WOW! That isn't the most I've ever seen, but it's certainly more than I'm
>> used to. Why is one column covered in seven different indices?
>> > We tried to remove indexes one by one, we get same Message 169.
>> Can you show the result of:
>> EXEC sp_help tablename
>> EXEC sp_helpindex tablename
>>
>

Index troubles

We're seeing funny things with SQL Server 2000 when dealing with indexes on
a customer table. When we issue:
DBCC reindex (cust)
we see an error Message 169: a column has been specified more than once in
the order by list.
We see the same error during Update Statistics.
We're using the Query analyzer - index tuning wizard to create indexes on
this table. The table has 12 indexes, and the field Cust_num is used in 7 of
12 indexes.
We tried to remove indexes one by one, we get same Message 169.
Any clues? Thanks!!
> The table has 12 indexes,
WOW! That isn't the most I've ever seen, but it's certainly more than I'm
used to. Why is one column covered in seven different indices?

> We tried to remove indexes one by one, we get same Message 169.
Can you show the result of:
EXEC sp_help tablename
EXEC sp_helpindex tablename
|||Here is the output from sp_helpindex
I think the hypotheticals are added by the Index wizard ... how do you get
rid of these?
Address nonclustered located on PRIMARY Address
CustID nonclustered unique located on PRIMARY CustID
CustNum nonclustered unique located on PRIMARY CustNum
hind_1682821057_24A_2A nonclustered hypothetical auto create located on
PRIMARY PriceCode CustNum
hind_1682821057_2A_24A_50A nonclustered hypothetical auto create located
on PRIMARY CustNum PriceCode AssignedLocation
hind_1682821057_2A_50A nonclustered hypothetical auto create located on
PRIMARY CustNum AssignedLocation
hind_1682821057_50A_24A_2A nonclustered hypothetical auto create located
on PRIMARY AssignedLocation PriceCode CustNum
hind_1682821057_50A_2A nonclustered hypothetical auto create located on
PRIMARY AssignedLocation CustNum
hind_c_1682821057_24A clustered hypothetical auto create located on
PRIMARY PriceCode
hind_c_1682821057_24A_2A clustered hypothetical auto create located on
PRIMARY PriceCode CustNum
hind_c_1682821057_2A clustered hypothetical auto create located on PRIMARY
CustNum
hind_c_1682821057_2A_24A clustered hypothetical auto create located on
PRIMARY CustNum PriceCode
Name nonclustered located on PRIMARY Name
tblCust10 nonclustered located on PRIMARY CustNum Name City Phone
tblCust13 nonclustered located on PRIMARY CustNum SalesComm
tblCust2 clustered located on PRIMARY State CustNum
tblCust23 nonclustered located on PRIMARY CustNum PriceCode
tblCust25 nonclustered located on PRIMARY CustNum Name City State
tblCust26 nonclustered located on PRIMARY PriceCode CustNum Name City
State
tblCust31 nonclustered located on PRIMARY AssignedLocation PriceCode
tblCust7 nonclustered located on PRIMARY SalesComm DateNewComm
tblCust9 nonclustered located on PRIMARY CustNum PriceIncrease
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:#oAjuKxLFHA.3844@.TK2MSFTNGP14.phx.gbl...
> WOW! That isn't the most I've ever seen, but it's certainly more than I'm
> used to. Why is one column covered in seven different indices?
>
> Can you show the result of:
> EXEC sp_help tablename
> EXEC sp_helpindex tablename
>
|||DROP INDEX should do it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Dean J Garrett" <info@.amuletc.com> wrote in message news:%23Sxvb3xLFHA.2648@.TK2MSFTNGP14.phx.gbl...
> Here is the output from sp_helpindex
> I think the hypotheticals are added by the Index wizard ... how do you get
> rid of these?
> --
> Address nonclustered located on PRIMARY Address
> CustID nonclustered unique located on PRIMARY CustID
> CustNum nonclustered unique located on PRIMARY CustNum
> hind_1682821057_24A_2A nonclustered hypothetical auto create located on
> PRIMARY PriceCode CustNum
> hind_1682821057_2A_24A_50A nonclustered hypothetical auto create located
> on PRIMARY CustNum PriceCode AssignedLocation
> hind_1682821057_2A_50A nonclustered hypothetical auto create located on
> PRIMARY CustNum AssignedLocation
> hind_1682821057_50A_24A_2A nonclustered hypothetical auto create located
> on PRIMARY AssignedLocation PriceCode CustNum
> hind_1682821057_50A_2A nonclustered hypothetical auto create located on
> PRIMARY AssignedLocation CustNum
> hind_c_1682821057_24A clustered hypothetical auto create located on
> PRIMARY PriceCode
> hind_c_1682821057_24A_2A clustered hypothetical auto create located on
> PRIMARY PriceCode CustNum
> hind_c_1682821057_2A clustered hypothetical auto create located on PRIMARY
> CustNum
> hind_c_1682821057_2A_24A clustered hypothetical auto create located on
> PRIMARY CustNum PriceCode
> Name nonclustered located on PRIMARY Name
> tblCust10 nonclustered located on PRIMARY CustNum Name City Phone
> tblCust13 nonclustered located on PRIMARY CustNum SalesComm
> tblCust2 clustered located on PRIMARY State CustNum
> tblCust23 nonclustered located on PRIMARY CustNum PriceCode
> tblCust25 nonclustered located on PRIMARY CustNum Name City State
> tblCust26 nonclustered located on PRIMARY PriceCode CustNum Name City
> State
> tblCust31 nonclustered located on PRIMARY AssignedLocation PriceCode
> tblCust7 nonclustered located on PRIMARY SalesComm DateNewComm
> tblCust9 nonclustered located on PRIMARY CustNum PriceIncrease
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:#oAjuKxLFHA.3844@.TK2MSFTNGP14.phx.gbl...
>

Index troubles

We're seeing funny things with SQL Server 2000 when dealing with indexes on
a customer table. When we issue:
DBCC reindex (cust)
we see an error Message 169: a column has been specified more than once in
the order by list.
We see the same error during Update Statistics.
We're using the Query analyzer - index tuning wizard to create indexes on
this table. The table has 12 indexes, and the field Cust_num is used in 7 of
12 indexes.
We tried to remove indexes one by one, we get same Message 169.
Any clues? Thanks!!> The table has 12 indexes,
WOW! That isn't the most I've ever seen, but it's certainly more than I'm
used to. Why is one column covered in seven different indices?

> We tried to remove indexes one by one, we get same Message 169.
Can you show the result of:
EXEC sp_help tablename
EXEC sp_helpindex tablename|||Here is the output from sp_helpindex
I think the hypotheticals are added by the Index wizard ... how do you get
rid of these?
Address nonclustered located on PRIMARY Address
CustID nonclustered unique located on PRIMARY CustID
CustNum nonclustered unique located on PRIMARY CustNum
hind_1682821057_24A_2A nonclustered hypothetical auto create located on
PRIMARY PriceCode CustNum
hind_1682821057_2A_24A_50A nonclustered hypothetical auto create located
on PRIMARY CustNum PriceCode AssignedLocation
hind_1682821057_2A_50A nonclustered hypothetical auto create located on
PRIMARY CustNum AssignedLocation
hind_1682821057_50A_24A_2A nonclustered hypothetical auto create located
on PRIMARY AssignedLocation PriceCode CustNum
hind_1682821057_50A_2A nonclustered hypothetical auto create located on
PRIMARY AssignedLocation CustNum
hind_c_1682821057_24A clustered hypothetical auto create located on
PRIMARY PriceCode
hind_c_1682821057_24A_2A clustered hypothetical auto create located on
PRIMARY PriceCode CustNum
hind_c_1682821057_2A clustered hypothetical auto create located on PRIMARY
CustNum
hind_c_1682821057_2A_24A clustered hypothetical auto create located on
PRIMARY CustNum PriceCode
Name nonclustered located on PRIMARY Name
tblCust10 nonclustered located on PRIMARY CustNum Name City Phone
tblCust13 nonclustered located on PRIMARY CustNum SalesComm
tblCust2 clustered located on PRIMARY State CustNum
tblCust23 nonclustered located on PRIMARY CustNum PriceCode
tblCust25 nonclustered located on PRIMARY CustNum Name City State
tblCust26 nonclustered located on PRIMARY PriceCode CustNum Name City
State
tblCust31 nonclustered located on PRIMARY AssignedLocation PriceCode
tblCust7 nonclustered located on PRIMARY SalesComm DateNewComm
tblCust9 nonclustered located on PRIMARY CustNum PriceIncrease
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:#oAjuKxLFHA.3844@.TK2MSFTNGP14.phx.gbl...
> WOW! That isn't the most I've ever seen, but it's certainly more than I'm
> used to. Why is one column covered in seven different indices?
>
> Can you show the result of:
> EXEC sp_help tablename
> EXEC sp_helpindex tablename
>|||DROP INDEX should do it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Dean J Garrett" <info@.amuletc.com> wrote in message news:%23Sxvb3xLFHA.2648@.TK2MSFTNGP14.ph
x.gbl...
> Here is the output from sp_helpindex
> I think the hypotheticals are added by the Index wizard ... how do you get
> rid of these?
> --
> Address nonclustered located on PRIMARY Address
> CustID nonclustered unique located on PRIMARY CustID
> CustNum nonclustered unique located on PRIMARY CustNum
> hind_1682821057_24A_2A nonclustered hypothetical auto create located on
> PRIMARY PriceCode CustNum
> hind_1682821057_2A_24A_50A nonclustered hypothetical auto create located
> on PRIMARY CustNum PriceCode AssignedLocation
> hind_1682821057_2A_50A nonclustered hypothetical auto create located on
> PRIMARY CustNum AssignedLocation
> hind_1682821057_50A_24A_2A nonclustered hypothetical auto create located
> on PRIMARY AssignedLocation PriceCode CustNum
> hind_1682821057_50A_2A nonclustered hypothetical auto create located on
> PRIMARY AssignedLocation CustNum
> hind_c_1682821057_24A clustered hypothetical auto create located on
> PRIMARY PriceCode
> hind_c_1682821057_24A_2A clustered hypothetical auto create located on
> PRIMARY PriceCode CustNum
> hind_c_1682821057_2A clustered hypothetical auto create located on PRIMA
RY
> CustNum
> hind_c_1682821057_2A_24A clustered hypothetical auto create located on
> PRIMARY CustNum PriceCode
> Name nonclustered located on PRIMARY Name
> tblCust10 nonclustered located on PRIMARY CustNum Name City Phone
> tblCust13 nonclustered located on PRIMARY CustNum SalesComm
> tblCust2 clustered located on PRIMARY State CustNum
> tblCust23 nonclustered located on PRIMARY CustNum PriceCode
> tblCust25 nonclustered located on PRIMARY CustNum Name City State
> tblCust26 nonclustered located on PRIMARY PriceCode CustNum Name City
> State
> tblCust31 nonclustered located on PRIMARY AssignedLocation PriceCode
> tblCust7 nonclustered located on PRIMARY SalesComm DateNewComm
> tblCust9 nonclustered located on PRIMARY CustNum PriceIncrease
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:#oAjuKxLFHA.3844@.TK2MSFTNGP14.phx.gbl...
>

Index size.

hi,
from where we can find out index size..of clus index or al indexes of any particular table.
regardsHave a look at sp_spaceused. If you want to do this non-
programatically, you can use the taskpad view of tha
database.
Regards,
Paul Ibison|||thanks..got it.

Index replication in SQL Server 2000

Hello.
I am using an MS SQL Server 2000 with service pack 3.
How can I replicate indexes with EM replication wizard?
Is it possible in snapshots , merges and transactionl replications?
Thank you so much.
Have a look at the snapshot tab of the article properties - there is a
checkbox for clustered and non-clustered indexes.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

index replication from DB2

Has any body done a replication of indexes or Primary keys from DB2 to SQL 2005 tables. Is that possible and how pls?

Thank you very much.

I believe by default primary keys are replicated. Is it not working for you?

http://msdn2.microsoft.com/en-us/library/ms152492.aspx

Gary

|||

is that stated in the msdn document u sent me?

or does it say so in any other document, so I can show it to the person who said it s not possible<

Thanks a lot for your help.

|||

If you take a look at sp_addarticle at its @.schema_option, there is option to set for primary key constraint.

http://msdn2.microsoft.com/en-us/library/ms173857.aspx

If you look at the default schema option for all the log based article type 0x80 are enabled for all of them. I think this article is the closest I can think of. I'm pretty sure, it's on by default.

Also a quick test probably won't hurt, just create a simple table with two integer columns and make the first column the primary key, then replicate to db2. Seeing is believing :-)

Gary

|||actually we re replicating from DB2 to SQL and not the other way? is it the same?
sorry but i never done replication before. Thanks again for your help
|||

I misread your posting... I think that's the question for DB2 Replication forum.

Regards,

Gary

index replication from DB2

Has any body done a replication of indexes or Primary keys from DB2 to SQL 2005 tables. Is that possible and how pls?

Thank you very much.

I believe by default primary keys are replicated. Is it not working for you?

http://msdn2.microsoft.com/en-us/library/ms152492.aspx

Gary

|||

is that stated in the msdn document u sent me?

or does it say so in any other document, so I can show it to the person who said it s not possible<

Thanks a lot for your help.

|||

If you take a look at sp_addarticle at its @.schema_option, there is option to set for primary key constraint.

http://msdn2.microsoft.com/en-us/library/ms173857.aspx

If you look at the default schema option for all the log based article type 0x80 are enabled for all of them. I think this article is the closest I can think of. I'm pretty sure, it's on by default.

Also a quick test probably won't hurt, just create a simple table with two integer columns and make the first column the primary key, then replicate to db2. Seeing is believing :-)

Gary

|||actually we re replicating from DB2 to SQL and not the other way? is it the same?
sorry but i never done replication before. Thanks again for your help
|||

I misread your posting... I think that's the question for DB2 Replication forum.

Regards,

Gary

index replication from DB2

Has any body done a replication of indexes or Primary keys from DB2 to SQL 2005 tables. Is that possible and how pls?

Thank you very much.

I believe by default primary keys are replicated. Is it not working for you?

http://msdn2.microsoft.com/en-us/library/ms152492.aspx

Gary

|||

is that stated in the msdn document u sent me?

or does it say so in any other document, so I can show it to the person who said it s not possible<

Thanks a lot for your help.

|||

If you take a look at sp_addarticle at its @.schema_option, there is option to set for primary key constraint.

http://msdn2.microsoft.com/en-us/library/ms173857.aspx

If you look at the default schema option for all the log based article type 0x80 are enabled for all of them. I think this article is the closest I can think of. I'm pretty sure, it's on by default.

Also a quick test probably won't hurt, just create a simple table with two integer columns and make the first column the primary key, then replicate to db2. Seeing is believing :-)

Gary

|||actually we re replicating from DB2 to SQL and not the other way? is it the same?
sorry but i never done replication before. Thanks again for your help
|||

I misread your posting... I think that's the question for DB2 Replication forum.

Regards,

Gary

sql

Index rebuilds question

Hi,
My question is: How do you know when an index is
actually finished being rebuilt? I run a script that goes
through and searches for indexes that need rebuilding
because of fragmentation or "staleness" and then run DBCC
DBREINDEX on them. The DBCC command returns that the
index "is being rebuilt" and then moves on to the next
one. Is there anywhere I can query to see when the index
is actually finished being rebuilt? I'd like to know this
so I can then run an UPDATE STATISTICS on it. Right now
the procedure dies occasionally because it attempts to do
the stats update before the other process is complete.
I realize that I can schedule the stats update as a
separate process later in the evening (my current
workaround) but I was curious to see if there was another
alternative. Thanks!
-DanDan,
Not sure how your getting into this situation as the next line of code
should not execute until the DBCC DBREINDEX is completed. The first line
must execute before the next one can start. In either case there is no need
to update the stats on an index that you just rebuilt as it is done as part
of the DBREINDEX.
--
Andrew J. Kelly
SQL Server MVP
"Dan Wunder" <dwunder@.pentontech.com> wrote in message
news:019a01c39404$8b5ace70$a301280a@.phx.gbl...
> Hi,
> My question is: How do you know when an index is
> actually finished being rebuilt? I run a script that goes
> through and searches for indexes that need rebuilding
> because of fragmentation or "staleness" and then run DBCC
> DBREINDEX on them. The DBCC command returns that the
> index "is being rebuilt" and then moves on to the next
> one. Is there anywhere I can query to see when the index
> is actually finished being rebuilt? I'd like to know this
> so I can then run an UPDATE STATISTICS on it. Right now
> the procedure dies occasionally because it attempts to do
> the stats update before the other process is complete.
> I realize that I can schedule the stats update as a
> separate process later in the evening (my current
> workaround) but I was curious to see if there was another
> alternative. Thanks!
> -Dan

index rebuilds

I thought there was a DBCC command that would rebuild all the indexes in a
database with one statement. Can anyone help please.> I thought there was a DBCC command that would rebuild all the indexes in a
> database with one statement. Can anyone help please.
DBCC DBREINDEX can rebuild all indexes for a single table. You can use a
cusros to loop trough all tables and dynamically prepare and execute the
DBCC command for each table. There is also a shortcut:
http://www.mssqlcity.com/FAQ/Devel/sp_msforeachtable.htm.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Before you spend the time doing this, think about why you're doing it. You
should read the whitepaper
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
which explain when and how to reduced fragmentation in indexes.
Thanks
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:#J8ghKrtEHA.1008@.tk2msftngp13.phx.gbl...
> > I thought there was a DBCC command that would rebuild all the indexes in
a
> > database with one statement. Can anyone help please.
> DBCC DBREINDEX can rebuild all indexes for a single table. You can use a
> cusros to loop trough all tables and dynamically prepare and execute the
> DBCC command for each table. There is also a shortcut:
> http://www.mssqlcity.com/FAQ/Devel/sp_msforeachtable.htm.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>

index rebuilds

I thought there was a DBCC command that would rebuild all the indexes in a
database with one statement. Can anyone help please.
> I thought there was a DBCC command that would rebuild all the indexes in a
> database with one statement. Can anyone help please.
DBCC DBREINDEX can rebuild all indexes for a single table. You can use a
cusros to loop trough all tables and dynamically prepare and execute the
DBCC command for each table. There is also a shortcut:
http://www.mssqlcity.com/FAQ/Devel/s...eachtable.htm.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
|||Before you spend the time doing this, think about why you're doing it. You
should read the whitepaper
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
which explain when and how to reduced fragmentation in indexes.
Thanks
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in
message news:#J8ghKrtEHA.1008@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
a
> DBCC DBREINDEX can rebuild all indexes for a single table. You can use a
> cusros to loop trough all tables and dynamically prepare and execute the
> DBCC command for each table. There is also a shortcut:
> http://www.mssqlcity.com/FAQ/Devel/s...eachtable.htm.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>
sql

index rebuilds

I thought there was a DBCC command that would rebuild all the indexes in a
database with one statement. Can anyone help please.> I thought there was a DBCC command that would rebuild all the indexes in a
> database with one statement. Can anyone help please.
DBCC DBREINDEX can rebuild all indexes for a single table. You can use a
cusros to loop trough all tables and dynamically prepare and execute the
DBCC command for each table. There is also a shortcut:
http://www.mssqlcity.com/FAQ/Devel/...reachtable.htm.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Before you spend the time doing this, think about why you're doing it. You
should read the whitepaper
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
which explain when and how to reduced fragmentation in indexes.
Thanks
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:#J8ghKrtEHA.1008@.tk2msftngp13.phx.gbl...
a[vbcol=seagreen]
> DBCC DBREINDEX can rebuild all indexes for a single table. You can use a
> cusros to loop trough all tables and dynamically prepare and execute the
> DBCC command for each table. There is also a shortcut:
> http://www.mssqlcity.com/FAQ/Devel/...reachtable.htm.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>

Index REBUILD question

Hey all,
One of the guys here (Tibor Karaszi) kindly pointed me to a script in
BOL that checks the fragmentation of indexes in a db, then depending on
the amount of fragmentation it REBUILDS or REORGANIZES the index. The
script I want to use is as it appears in BOL, but I've tweaked it to
specify a FILLFACTOR of 95 for the REBUILDS.
So my question is, if I have this script say running in a maintenance
plan at a 24/7 site, and it decides to REBUILD an index, if someone
indirectly accesses the table via our application would the application
receive an error as the table has been locked by the REBUILD?
Or will the query from the user wait until the lock on the table is
returned by the REBUILD process? (this is probably the ideal situation
for us)

>From BOL I understand it may have a shared lock (ie. readonly) at some
times.
But I'm also concerned about writes to the table.
We would prefer that the access is slow at that point rather than
returning an error message.
Can anyone clear this up for me?
Cheers!!
PS:
We can't use REBUILD ONLINE as
1) We use large object data types and
2) The client does not have Enterprise versionA REBUILD by default is a totally off-line process in that the table has an
Exclusive lock for the duration. So if the user tries to access the table it
will be blocked. How long is determined by the amount of time it takes to
rebuild the index or the timeout setting which ever comes first. If you need
it to be available all the time then you should look into the REORGANIZE
option instead. And in either case I would not recommend specifying the fill
factor in that script. By doing so you loose the ability to tune the fill
factors on individual indexes. If you do not specify it then it will use the
last setting. So if you initially want 95 then you should have built them
with 95 or manually issue a REBUILD with 95 as the setting. From then on
don't specify it again in the general rebuild script.
Andrew J. Kelly SQL MVP
<davconts@.gmail.com> wrote in message
news:1152064393.005694.10670@.j8g2000cwa.googlegroups.com...
> Hey all,
> One of the guys here (Tibor Karaszi) kindly pointed me to a script in
> BOL that checks the fragmentation of indexes in a db, then depending on
> the amount of fragmentation it REBUILDS or REORGANIZES the index. The
> script I want to use is as it appears in BOL, but I've tweaked it to
> specify a FILLFACTOR of 95 for the REBUILDS.
> So my question is, if I have this script say running in a maintenance
> plan at a 24/7 site, and it decides to REBUILD an index, if someone
> indirectly accesses the table via our application would the application
> receive an error as the table has been locked by the REBUILD?
> Or will the query from the user wait until the lock on the table is
> returned by the REBUILD process? (this is probably the ideal situation
> for us)
>
> times.
> But I'm also concerned about writes to the table.
> We would prefer that the access is slow at that point rather than
> returning an error message.
> Can anyone clear this up for me?
> Cheers!!
> PS:
> We can't use REBUILD ONLINE as
> 1) We use large object data types and
> 2) The client does not have Enterprise version
>|||In addition, if you have Enterprise Edition, you can do the rebuild with the
ONLINE option.
REORGANIZE might be better for you than REBUILD with ONLINE, you have to tes
t to see what suits you
best.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e5Uduv9nGHA.4848@.TK2MSFTNGP03.phx.gbl...
>A REBUILD by default is a totally off-line process in that the table has an
Exclusive lock for the
>duration. So if the user tries to access the table it will be blocked. How
long is determined by
>the amount of time it takes to rebuild the index or the timeout setting whi
ch ever comes first. If
>you need it to be available all the time then you should look into the REOR
GANIZE option instead.
>And in either case I would not recommend specifying the fill factor in that
script. By doing so you
>loose the ability to tune the fill factors on individual indexes. If you do
not specify it then it
>will use the last setting. So if you initially want 95 then you should have
built them with 95 or
>manually issue a REBUILD with 95 as the setting. From then on don't specify
it again in the general
>rebuild script.
> --
> Andrew J. Kelly SQL MVP
> <davconts@.gmail.com> wrote in message news:1152064393.005694.10670@.j8g2000
cwa.googlegroups.com...
>

Index REBUILD question

Hey all,
One of the guys here (Tibor Karaszi) kindly pointed me to a script in
BOL that checks the fragmentation of indexes in a db, then depending on
the amount of fragmentation it REBUILDS or REORGANIZES the index. The
script I want to use is as it appears in BOL, but I've tweaked it to
specify a FILLFACTOR of 95 for the REBUILDS.
So my question is, if I have this script say running in a maintenance
plan at a 24/7 site, and it decides to REBUILD an index, if someone
indirectly accesses the table via our application would the application
receive an error as the table has been locked by the REBUILD?
Or will the query from the user wait until the lock on the table is
returned by the REBUILD process? (this is probably the ideal situation
for us)
>From BOL I understand it may have a shared lock (ie. readonly) at some
times.
But I'm also concerned about writes to the table.
We would prefer that the access is slow at that point rather than
returning an error message.
Can anyone clear this up for me?
Cheers!!
PS:
We can't use REBUILD ONLINE as
1) We use large object data types and
2) The client does not have Enterprise versionA REBUILD by default is a totally off-line process in that the table has an
Exclusive lock for the duration. So if the user tries to access the table it
will be blocked. How long is determined by the amount of time it takes to
rebuild the index or the timeout setting which ever comes first. If you need
it to be available all the time then you should look into the REORGANIZE
option instead. And in either case I would not recommend specifying the fill
factor in that script. By doing so you loose the ability to tune the fill
factors on individual indexes. If you do not specify it then it will use the
last setting. So if you initially want 95 then you should have built them
with 95 or manually issue a REBUILD with 95 as the setting. From then on
don't specify it again in the general rebuild script.
--
Andrew J. Kelly SQL MVP
<davconts@.gmail.com> wrote in message
news:1152064393.005694.10670@.j8g2000cwa.googlegroups.com...
> Hey all,
> One of the guys here (Tibor Karaszi) kindly pointed me to a script in
> BOL that checks the fragmentation of indexes in a db, then depending on
> the amount of fragmentation it REBUILDS or REORGANIZES the index. The
> script I want to use is as it appears in BOL, but I've tweaked it to
> specify a FILLFACTOR of 95 for the REBUILDS.
> So my question is, if I have this script say running in a maintenance
> plan at a 24/7 site, and it decides to REBUILD an index, if someone
> indirectly accesses the table via our application would the application
> receive an error as the table has been locked by the REBUILD?
> Or will the query from the user wait until the lock on the table is
> returned by the REBUILD process? (this is probably the ideal situation
> for us)
>>From BOL I understand it may have a shared lock (ie. readonly) at some
> times.
> But I'm also concerned about writes to the table.
> We would prefer that the access is slow at that point rather than
> returning an error message.
> Can anyone clear this up for me?
> Cheers!!
> PS:
> We can't use REBUILD ONLINE as
> 1) We use large object data types and
> 2) The client does not have Enterprise version
>|||In addition, if you have Enterprise Edition, you can do the rebuild with the ONLINE option.
REORGANIZE might be better for you than REBUILD with ONLINE, you have to test to see what suits you
best.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e5Uduv9nGHA.4848@.TK2MSFTNGP03.phx.gbl...
>A REBUILD by default is a totally off-line process in that the table has an Exclusive lock for the
>duration. So if the user tries to access the table it will be blocked. How long is determined by
>the amount of time it takes to rebuild the index or the timeout setting which ever comes first. If
>you need it to be available all the time then you should look into the REORGANIZE option instead.
>And in either case I would not recommend specifying the fill factor in that script. By doing so you
>loose the ability to tune the fill factors on individual indexes. If you do not specify it then it
>will use the last setting. So if you initially want 95 then you should have built them with 95 or
>manually issue a REBUILD with 95 as the setting. From then on don't specify it again in the general
>rebuild script.
> --
> Andrew J. Kelly SQL MVP
> <davconts@.gmail.com> wrote in message news:1152064393.005694.10670@.j8g2000cwa.googlegroups.com...
>> Hey all,
>> One of the guys here (Tibor Karaszi) kindly pointed me to a script in
>> BOL that checks the fragmentation of indexes in a db, then depending on
>> the amount of fragmentation it REBUILDS or REORGANIZES the index. The
>> script I want to use is as it appears in BOL, but I've tweaked it to
>> specify a FILLFACTOR of 95 for the REBUILDS.
>> So my question is, if I have this script say running in a maintenance
>> plan at a 24/7 site, and it decides to REBUILD an index, if someone
>> indirectly accesses the table via our application would the application
>> receive an error as the table has been locked by the REBUILD?
>> Or will the query from the user wait until the lock on the table is
>> returned by the REBUILD process? (this is probably the ideal situation
>> for us)
>>From BOL I understand it may have a shared lock (ie. readonly) at some
>> times.
>> But I'm also concerned about writes to the table.
>> We would prefer that the access is slow at that point rather than
>> returning an error message.
>> Can anyone clear this up for me?
>> Cheers!!
>> PS:
>> We can't use REBUILD ONLINE as
>> 1) We use large object data types and
>> 2) The client does not have Enterprise version
>

Index rebuild

I run dbcc dbreindex command and send the output to the text file. Is there any other way to check that indexes were in fact rebuild?You could do a before and after DBCC SHOWCONTIG. But, if DBREINDEX retunrs no errors, it ran successfully.

Index Ratification

Hi all,
I'm going through the process of identifying which items in my tables to
place indexes on.
I'm using the following general rules in deciding usage. Can anyone advise
whether these are correct?
- All primary keys to be a clustered index.
- All foreign keys to be unclustered.
- Items which are heavily used in WHERE etc are also unclustered.
DJWA bit simplified. What is the datatype of your PK? Is it int (identity) or
something else? Is it sequential?
As far as th FK goes yeah, I would say yes. There are no really general
rules, it all depends but I would say that those steps are a good start. You
do need to think really hard on your indexing however, perhaps performanse
could be better with some changes.
MC
"Daniel J Watkins" <danielwatkinslearn@.hotmail.com> wrote in message
news:4396883f$1_1@.glkas0286.greenlnk.net...
> Hi all,
> I'm going through the process of identifying which items in my tables to
> place indexes on.
> I'm using the following general rules in deciding usage. Can anyone
> advise
> whether these are correct?
> - All primary keys to be a clustered index.
> - All foreign keys to be unclustered.
> - Items which are heavily used in WHERE etc are also unclustered.
> DJW
>|||Daniel,
I would generalize it like this:
For a VERY write intensive table:
Only clustered index on the PK being a column containing sequential values.
For a more read intensive table:
Nonclustered on the PK (since we will usually only fetch one row at a time
from it)
Nonclustered for searches that find few rows.
Clustered (or covered nonclustered) for searches that find many rows
(typically FK or Date columns).
The "most important" FK of the table is usually a good column to use for the
clustered index. Many searches on it, returns many rows, small data type :-)
/ Tobias|||Is this an OLTP (operational) database or a Data Warehouse used for
reporting purposes?
"Daniel J Watkins" <danielwatkinslearn@.hotmail.com> wrote in message
news:4396883f$1_1@.glkas0286.greenlnk.net...
> Hi all,
> I'm going through the process of identifying which items in my tables to
> place indexes on.
> I'm using the following general rules in deciding usage. Can anyone
> advise
> whether these are correct?
> - All primary keys to be a clustered index.
> - All foreign keys to be unclustered.
> - Items which are heavily used in WHERE etc are also unclustered.
> DJW
>|||It's an OLTP, JT
"JT" <someone@.microsoft.com> wrote in message
news:e8zGYbz#FHA.228@.TK2MSFTNGP12.phx.gbl...
> Is this an OLTP (operational) database or a Data Warehouse used for
> reporting purposes?
> "Daniel J Watkins" <danielwatkinslearn@.hotmail.com> wrote in message
> news:4396883f$1_1@.glkas0286.greenlnk.net...
>|||Then perhaps optimizing inserts/upadtes/deletes is more important than
optimizing queries. In that case, minimizing the number of indexes and
avoiding clustered indexes.
"Daniel J Watkins" <danielwatkinslearn@.hotmail.com> wrote in message
news:43970494$1_1@.glkas0286.greenlnk.net...
> It's an OLTP, JT
> "JT" <someone@.microsoft.com> wrote in message
> news:e8zGYbz#FHA.228@.TK2MSFTNGP12.phx.gbl...
>sql