Table Indexing in SQL 2000

[Grunners]

All,

After upsizing quite a few tables we're now using a SQL backend with Access at the front. Works fine. Well it did. Now we're getting timeouts and I haven't a clue why.

It seems to happen when a new record has been added to a table via the front end and the user clicks the 'arrow' in access to add a new record.

I've had a look at the indexes on the tables in the SQL DB and there are some really daft ones in there. I've never looked before so I can't have made them. Thus my question: Can SQLS 2000 create it's own indexes/have they been 'imported' when we upsized from Access and could they be slowing the DB down enough to cause timeouts? (And if they look useless can I just delete them and add useful ones?)

Any ideas would be much appreciated, as would sympathy for my lack of useful knowledge on this subject...

Many thanks in advance!

 

[SQL_Hell]

Hi there,

The daft indexes were probably created by the upsizing wizard, if you think they are hindering performance then by all means remove them and re-test.
The only index sql server will create by itself is the clustered index on the primary key so leave this one alone (you can only have 1 clustered index on a table).

Also check query timeout settings which can be viewed in enterprise manager by right clicking on the server and hitting the connections tab.

 

[Grunners]

Dear Hell,

Have removed indexes and it's a bit better. I've found my real problem are deadlocks! This is quite a big topic so may have to wait a day or two.

I will however change the timeouts as you have suggested.

Many thanks indeed,

 

[SQL_Hell]

Hi Grunners,

I have a fair bit of experience dealing with deadlocks, first thing I would check is index fragmentation, I know you have new indexes but please humour me a bit here.




run dbcc showcontig and see how bad the indexes are, I can provide code for this if needed
here is a great document to understand what you are looking at:

http://www.sqlservercentral.com/columnists/chedgate/fragmentation101.asp



Also when you create your indexes, set the fill factor to around 80, this helps deadlocks (in past experience I have had).

Next thing to check is disk IO, bad disk performance can lead to lock excalation, which in turn can cause deadlocks.

 

[Grunners]

Now you see there's things you've explained here that just aren't in the instruction book!

I ran the DBCC SHOWCONTIG yesterday although it was late and I had no clue what the results meant. After following your link it appears that my problem DB is fragmented, or rather three of the tables are, sysobjects, sysindexes and syscolumns.

After following your link all the other tables seem pretty much near prefect. Disk IO is something worth checking though.

I run the following script each night:

Use master
go
sp_dboption MYDATABASE, single, true

dbcc checkdb ('MYDATABASE', repair_rebuild)
go

Use master
go
sp_dboption MYDATABASE, single, FALSE

This said after running the script as soon as someone goes back in and starts using it the integrity checks that I've just set up using the wizard will always fail. I'll have to wait until everyone's out and run the above script again before the integrity checks will work.


I think it's just bad coding on the frontend but I'll check the fill factors and investigate reindexing more.

Many thanks!