index tuning

Hi,
I have a very large table that holds all the ledger transactions. Its the
number 1 table that gets hit when posting invoices, etc, etc. It started
with 5 indexes that the developers did. I have been running traces that are
fed to the index tuning wizzard and now over the last 3 months it has
generated about 20 new indexes, one is clustered. No significant performance
is noticed, rather the opposite. What do I do now? Please help,
Regards,
dimitris,
The index tuning wizard should be used as a guide, not as a tool for
creating your indexes. I would run a trace on this table over a typical
working day, and find the top 10 most intensive queries against this
table based on IO and CPU, and possibly duration.
Find out what the SARGs are for these queries and make sure you are
indexed for these SARGs. To do this check the WHERE clauses of the
queries. The clustered index should be placed on a narrow column and
chosen very carefully. Try to place it on a FK column, or a column where
range queries are performed, or one where there are frequent ORDER BY
clauses.
Test your new queries in a test environment, don't do this in
production. Benchmark your results so you can compare what works against
what doesn't.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
dimitris wrote:
> Hi,
> I have a very large table that holds all the ledger transactions. Its the
> number 1 table that gets hit when posting invoices, etc, etc. It started
> with 5 indexes that the developers did. I have been running traces that are
> fed to the index tuning wizzard and now over the last 3 months it has
> generated about 20 new indexes, one is clustered. No significant performance
> is noticed, rather the opposite. What do I do now? Please help,
> Regards,
>
sql

Index corruption

Hi all.
I've got a database that people are posting data to
through the index. The other day, someone posted a
series of records and called a select statment from the
index to check their work. When they did, three entries
were missing. I went back and found that the data was on
the disks and could be regenerated by recreating the
index tables.
Now I'm trying to figure out why this happened and
whether I can expect that it will happen again. I
restored the last .bak file prior to the incident and
walked through the transaction logs up to the event,
running checkdb with each. The first trans log after the
event reported that it could not be restored due to an ID
being newer on the previous trans log.
I've now restored the first .bak file after the incident
and run checkdb. I'm getting this error:
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 1541580530: Errors found in text ID 25723928576
owned by data record identified by RID = (1:171739:3) id
= 390945.
If I do a select statement for this ID, I get:
Server: Msg 601, Level 12, State 3, Line 1
Could not continue scan with NOLOCK due to data movement.
Does anyone have an ideas for why this happened? Other
actions I should try? Or the likelihood that this will
happen again?
Thanks greatly,
~BrendaHi Brenda,
From you description, it seems that you have resolved this problem and you
want to point out what induce this issue. If I have misunderstood, please
feel free to let me know.
Could you also send me the following logs/files so I can perform a more
analysis of what is happening:
1. SQLDiag.txt from the SQL Server 2000. To generate this file you need to
use the command line utility SQLDiag.exe -I instance_name . For more
information about the utility check SQL Books On-Line article "sqldiag
Utility"
2. System and application event logs saved as text files. To do that in
Windows NT
4.0 Event Viewer go to Log menu, choose Save As. From "Save As Type" dialog
box choose Text Files (*.TXT).
3. Any previous reports/output files of Maintenance Plan like the one you
have already attached.
4. Any DBCC CHECKDB results if you have run those manually, outside the
Maintenance Plan.
I am standing by for your response.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.