Hello,

For question 1:

I've learned a lot from http://www.sql-server-performance.com/articles/per/main.aspx for auditing performance, perfomance tuning. Use query analyzer to view queryplans, and sqlprofiler to see the duration of individual statements.

Basic: check normalisation,check...

A few months ago our data was backup up on a mobile HD of 500 gb. This made it easy to move around and test it. The main backdraw of...

We had a filesharing issue for large files in win2k3. Fixed with applying SP2, adding memory & flashing firware. Alas another feature crept up: commandshells don't always start (issue with...

If you have sysadmin rights, you could start a trace with sqlprofiler to see the troublesome queries.

sp_who2 gives the current connections and if it is blocked (temporarily) by another.

Have a look at the audit section of http://www.sql-server-performance.com/articles/audit/main.aspx

The timeout can come from "normal" locks on records (applications fetching not all or too many data), I/O throughput.

Are these readonly...

I work 39 hours from 8-16.30 (safe by some incident). So home by 17:15. The flexible hours are really appreciated.

You could check if the defrag actually helps (fragmentation before/after) with DBCC SHOWCONTIG.

Perhaps you should iterate on index position rather than tablename

(order by IndexId for the indexes cursor), so sql...

Interesting.

Thanks for sharing.

I would change 2 things

*Set rs2 = conn.Execute(sql)

        While Not rs2.EOF

... This will hold locks on non-fetched data

to

set rs2=conn.execute(sql)

if rs2.recordcount>0 then

rs2.movelast

rs2.movefirst

While Not rs2.EOF

... all data retrieved, no remaining locks

* ...

We had a similiar problem before. It was solved when we enabled connection pooling. The connection(s) are closed when they are no longer needed?

Have you stresstested the raid-configuration of the 2005 sql server?

depends if the function is based on a datacolumn (needs calculation per record) or if the function is based on a condition (calculated once, calculated value is than a candidate...

The amount of reads are a good indication of I/O. Since it says 30000 instead of 110000 it should be faster. A look at the actual execution plan wouldn't hurt.

Are you connecting via ip or naming? Sql authentication or windows authentication?

Have you found any info in the windows error log? (start->execute->eventvwr)