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

Drop identity propert

Can use this staement
Alter table |<table name>
alter column <Column name>
drop identity
DB2 allows this....
How can I drop identity propery of a column with out first moving the data
out from that table and later getting the data back in that same table.
--
Sr DBA
Pier 1 Imports
mabbas@.Pier1.comYou cannot remove the identity property. If you use the GUI, you will see that it creates a new
table, copy data etc.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Moh" <mabbas@.Pier1.com> wrote in message news:C8DE380E-B90F-4039-B174-92ED35BB3FC9@.microsoft.com...
> Can use this staement
> Alter table |<table name>
> alter column <Column name>
> drop identity
> DB2 allows this....
> How can I drop identity propery of a column with out first moving the data
> out from that table and later getting the data back in that same table.
> --
> Sr DBA
> Pier 1 Imports
> mabbas@.Pier1.com|||Moh
CREATE TABLE #T (c INT NOT NULL IDENTITY(1,1),c1 CHAR(1))
GO
INSERT INTO #T (c1) VALUES ('c')
GO
ALTER TABLE #T DROP COLUMN c
DROP TABLE #T
"Moh" <mabbas@.Pier1.com> wrote in message
news:C8DE380E-B90F-4039-B174-92ED35BB3FC9@.microsoft.com...
> Can use this staement
> Alter table |<table name>
> alter column <Column name>
> drop identity
> DB2 allows this....
> How can I drop identity propery of a column with out first moving the data
> out from that table and later getting the data back in that same table.
> --
> Sr DBA
> Pier 1 Imports
> mabbas@.Pier1.com

Driver questions running with DB2 as datasource

I need some help and feedbacks from other users that are currently running Reporting Services using DB2 as a database. I have been testing OLE DB and ODBC from Microsoft and IBM, and recently DB2 .Net Data provider from IBM.

We are currently running DB2 on z/os v 7, gateway v.8 fixpack 10. We will mainly be calling Stored Procedures on the DB2 and passing recordsets back to the client.

First I installed IBM DB2 ODBC DRIVER v. 7.01.00.88, but I did not get this too work with reporting Services calling Stored Procedures, so I had too upgrade the driver to v8.01.10.812 to get it to work.

I have been in contact with Microsoft and they recommend using Microsoft OLE DB Provider for DB2 (30.sept 2005). I have tried too get this driver too work but I have problems configuring it. I think that it has something too do with some packages that the driver tries too create on DB2. I get error messages both on the DB2 and in the Data access Tool that runs a wizard setting up the driver. '—The package creation process has failed on resource "mytest", Data description mismatch SQLSTATE:HY000, SQLCODE: -292'

IBM have also developed a IBM DB2 Data provider for .Net Framework 2.0. This is the only driver that I can use too browse the database in the Server Explorer in VS. If you edit the RSReportDesigner.config file you can also use this driver in Report Server projects. Just add <Extension Name="DB2" Type="IBM.Data.DB2.DB2Connection,IBM.Data.DB2, Version=9.0.0.2, Culture=neutral, PublicKeyToken=7c307b91aa13d208" /> too the data section of the xml file.

So too wrap it all up. Which provider should I use? Are there any recommendations out there?

Thanks

B

well i've a question.

how to get IBM DB2 Data Provider for .Net Framework 2.0

Driver questions running with DB2 as datasource

I need some help and feedbacks from other users that are currently running Reporting Services using DB2 as a database. I have been testing OLE DB and ODBC from Microsoft and IBM, and recently DB2 .Net Data provider from IBM.

We are currently running DB2 on z/os v 7, gateway v.8 fixpack 10. We will mainly be calling Stored Procedures on the DB2 and passing recordsets back to the client.

First I installed IBM DB2 ODBC DRIVER v. 7.01.00.88, but I did not get this too work with reporting Services calling Stored Procedures, so I had too upgrade the driver to v8.01.10.812 to get it to work.

I have been in contact with Microsoft and they recommend using Microsoft OLE DB Provider for DB2 (30.sept 2005). I have tried too get this driver too work but I have problems configuring it. I think that it has something too do with some packages that the driver tries too create on DB2. I get error messages both on the DB2 and in the Data access Tool that runs a wizard setting up the driver. '—The package creation process has failed on resource "mytest", Data description mismatch SQLSTATE:HY000, SQLCODE: -292'

IBM have also developed a IBM DB2 Data provider for .Net Framework 2.0. This is the only driver that I can use too browse the database in the Server Explorer in VS. If you edit the RSReportDesigner.config file you can also use this driver in Report Server projects. Just add <Extension Name="DB2" Type="IBM.Data.DB2.DB2Connection,IBM.Data.DB2, Version=9.0.0.2, Culture=neutral, PublicKeyToken=7c307b91aa13d208" /> too the data section of the xml file.

So too wrap it all up. Which provider should I use? Are there any recommendations out there?

Thanks

B

well i've a question.

how to get IBM DB2 Data Provider for .Net Framework 2.0