dropping performance on SQL server

I am in no way familiar with SQL server (DB2/ORACLE background), but since we have a bought application running on this RDBMS and performance is dropping off, I would like to do a little investigation into this new field.
Main problem is query time-outs and swapping during query execution. What can be done with the standard administration tools SQL server provides? I am used to such fancy tools as TOAD for ORACLE, but in this case I have no special tools at hand. Can someone suggest a strategy on how to handle such an investigation?

Example : I run a simple command line statement in DB2 to check whether reorganization is needed. What is the equivalent in SQL server?Query Analyzer is one tool.Sql Server profiler is the other tool.Go to Start-->Programs-->Microsoft Sql Server--Profiler(Windows) and run it from there.You need to be sa in order to run it.Create a new trace on the server you want to run this,save it to a file,let it run for some time(may be 2-3 hours during the peak use).Stop the profiler after the desired time and Click on the Tools-->Index Tuning Wizard and that will guide you to the Index Suggestion.
There are so many other ways to tune a sql server things like update statistics,which you should be doing quite often,Index rebuilding or Defrag(I prefer defrag as it's an online operation),Recompiling your sp and you can also use the Performance monitor to monitor the box running the sql server.
There is a tool avaliable from netiq.com ,called sqlcheck which helps you in monitoring the sql box.
Hope it helps.|||So many places to start from here.

1. I think the nearest equivalent tool to show you whether a reorg is needed is DBCC SHOWCONTIG (table_name). It will show you how fragmented a particular table is. A badly fragmented table can be re-org'd using DBCC DBREINDEX (table_name). Use caution, this can be a resource pig. Check SQL BOL.

2. Check your hardware and especially your disk setup. Ideally you want RAID 1&0 for your data and your log file partitions. You want your log files on a separate partition from your data. If you can afford it, get your tempdb off onto it's own partition (this is a luxury).

3. In general, the Index Tuning Wizard mentioned above works quite well. It has stood me well in the past when I overlooked an index.

4. Try to have one clustered index per table (the limit is one, but try to have one).

5. Make sure SQL is running on a dedicated box (not concurrent with other services like IIS).

6. Boost priority to SQL Server (only if it's on a dedicated box). This will have only marginal effect.

7. If possible, SQL seems to perform very well with multiple processors. If you're running it on a single processor box, consider an upgrade.

8. Look at your memory and be sure you have enough. It's cheap and it may help.

But most of the time your problems will lie with poorly tuned queries or indices which are notably absent.

There is much more to be said, of course, but those should do for starters.

HTH,

Hugh Scott

Originally posted by blom0344
I am in no way familiar with SQL server (DB2/ORACLE background), but since we have a bought application running on this RDBMS and performance is dropping off, I would like to do a little investigation into this new field.
Main problem is query time-outs and swapping during query execution. What can be done with the standard administration tools SQL server provides? I am used to such fancy tools as TOAD for ORACLE, but in this case I have no special tools at hand. Can someone suggest a strategy on how to handle such an investigation?

Example : I run a simple command line statement in DB2 to check whether reorganization is needed. What is the equivalent in SQL server?sql