slow implementation of SQL

[jorg32]

Hi,

We have written a programme in SQL. Now we notice that this works at the customers enormously slowly!?

Our first thought goes to the indexes of the tables which are perhaps not well. The Second thought is that the "select" with bad indexing carries their work slowly out.

Is there a possibility in SQL or Analyzer of checking where and why some "SELECTS" are slowly carried out + Can the indexes of several databases be compared,because there are nevertheless some customers that their database works rapidly!?

The servers of the customers are very powerful, therefore this cannot be the cause!

Thanks for helping me !!

JM

 

[vongrunt]

Is there a possibility in SQL or Analyzer of checking where and why some "SELECTS" are slowly carried out

For "where" part - use profiler. Once you collect large enough samle workload it is relatively easy to identify "why" - are problems related to heavy usage of client-side code/cursors (ADO & stuff), individually slow queries (use profiler filters for that), lack of additional tuning or something else.

Can the indexes of several databases be compared, because there are nevertheless some customers that their database works rapidly!?

With 3rd party DB comparison tools or manual queries over information_schema views/system tables. Some DBCC statements may help as well.

The servers of the customers are very powerful, therefore this cannot be the cause!

IMO once you encounter bottleneck "powerful" doesn't matter much.

------
Math problems? Call 0800-[(10x)(13i)^2]-[sin(xy)/2.362x]

 

[SQLSister]

Reindexing is certainly something I would try. If the indexes are fragmented, then the system will work slowly. You may need to set these customers up with a regular schedule to reindex or defrag the indexes.

Also, if these customers are the ones with a large number of records, your problem could be inefficient code. It just hasn't shown up at the customers with fewer records yet. Inefficient code is the number one cause of performance problems. For instance, did you use such things are cursors, NOT IN, or Like '%sometext%'? All of those things are performance killers. If you join on composite keys instead of integers, you may eventually see a performance hit. Dynamic SQL can force stored procedure recompiles which slow up the system.

Other things which may be causing the problem are too few network resources available (i.e., too many people querying at once for their network. I once worked at a place with a network designed for 200 people, unfortunately we had 400 people, believe our databases crawled). You may have a lock on a table that isn't clearing due to a hung query. HAve you checked the locks? Does the system temporarily get faster if you restart the server?

They may be trying to run other applications on the same server. Nothing but SQL Server should run on a SQL Server machine. If they are running other applications on the machine and have limited the memory SQL Server can use, this can cause problems.

Do they have any jobs running during working hours (other than transaction log backups)? Those could be affecting performance.

Is the transaction log set to grow? Perhaps it needs to grow at a faster rate, especially if they are importing large amounts of data.

Using Profiler is a good way to diagnose problems. Be aware that while you are running the trace, it takes up extra resources and may make the system even slower.

Performance tuning is a very complex subject. You probably need to do some in depth research on it. This FAQ suggests some books on performance tuning:
Useful Reference Books for SQL Server Professionals faq183-3324

The last one will help you a lot with how to run profiler. The link for where to get it is:

http://www.shareit.com/product.html...com/shop/itembycat.asp?catid=3&currencies=USD

Are they up to date on their service packs? They could be affected by the Slammer virus if not.


Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief