I have a VB.NET application using SQL Server that I have been developing
using a test database. Recently I wrote a program to convert data from a
customer's old DOS based system to an SQL server database, in the same
format as the test system. When I had finished converting (it ran for about
24 hours), I ran my application, and started to get timeout errors when
accessing the server - the converted database was about 5 times the size of
the test database. However, the errors occurred when accessing tables that
were no bigger than those in the test database and no more data was being
transferred over the network.
I have rebooted the server and my development machine, and performance
is now back to normal with the converted database, but I am slightly
concerned about what caused the errors in the first place - is it likely to
be a problem with my conversion program or a more serious problem with the
database itself? Any suggestions would be appreciated, although I know it is
difficult to give an answer without all the details.

Andy Baker

That's not much to go on since it can be so many things. But some things to
look for are:

Blocking - Use sp_who2 to see if there is any blocking occurring when the
timeouts are happening.
Disk & CPU usage - Run Perfmon to see how the server is reacting at that
time
Indexes - Do you have proper indexes on these tables so that you do seeks
instead of scans?
Memory - How much memory is there? Are there other apps ont he server than
SQL Server? If so you may need to set the Max memory on SQL Server so as to
leave enough memory of the OS and the other apps to do their thing properly.
Query plans - Look at the plans of the queries before and during the issues
to see if they changed. If so you may be getting bad plans. If these are
stored procedures run sp_recompile to see if the plans change.



Performance
http://www.sql-server-performance.co...ce_audit10.asp
Performance Audit
http://www.microsoft.com/technet/pro...perations.mspx
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.co...ance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.co...mance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/de...rfmon_24u1.asp
Disk Monitoring
http://sqldev.net/misc/WaitTypes.htm Wait Types


--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"Andy Baker" <abaker (AT) NOSPAMvanputer (DOT) com> wrote

Thanks for those tips - I'll look into it, although I don't seem to have a
problem any more so I don't know how far I'll get.

Andy Baker

"Andrew J. Kelly" <sqlmvpnooospam (AT) shadhawk (DOT) com> wrote