Performance Problem - Request for advice

I am a DBA working for a large business in the North East of England.

One of our main Database servers (SQL Server 2000 SP4) is configured as follows:

Storage - 1. Logical Drive configured as RAID5 with about 800MB of storage, 40% free space. Used for main database files. 8 Physical drives.
- 2. Logical Drive configured as RAID1 with about 140MB of storage, 20% free space. Used for sql log files.

Both 1 and 2 are on the same controller (SCSI).

- 3. System Drive (C:)

Storage configuration was done by an external consultancy and disk performance (when benchmarked) was excellent.

Memory - 8GB of memory 6.5GB allocated to SQL Server
Operating System: Windows server 2003 Enterprise Edition
SQL Server: 2000 SP4

We would appreciate your advice regarding the following problem that we have on busy times:

Typical number of users/connections to SQL Server is between 200 to 250 (sysprocesses)
Typical number of active processes can vary between 5 to 30 (all sorts of applications)

The problem we have is that on some busy days a combination of processes grinding the server to a halt.
When we check the activity (normally using sp_who2) we can see that there are no blocked processes, cpu and DiskIO progressing as normal
(some values maybe large but not beyond expectation).


However, when "the problem" occurs, everything (queries, updates, etc) is extremely slow for 5-20 minutes until the bottleneck is freed.

We would appreciate any advice (tempdb?, disk bottleneck?, etc) and recommendation for any useful tools.

Thank you,You say when the bottleneck is freed...do you know what it is? Do you assume that it's freed because performance come back.

IMHO, I would not mess with memory allocation. I would let SQL Server do whatever it wants

BTW, the number of users/connections seems pretty low.

My guess is that there is some background batch job kibking off during that time which is a pig....|||Hows your tempdb during the time of extremely slow?|||Other simple stuff,

Are your statistics upto date on tables?
Is your disk storage fragmented?
Any bad disk warnings from the raid control?

The memory allocation for db growth (i.e 10% or 10MB) in the Transaction log can bugger it up too depending on it's growth and backups.

..and the killer... any virus checking going on?
I've had issues where {insert top end brand name here} have caused massive performance reductions due to unnecessary scheduled scans (company policy) or signature update downloads.

Is someone starting up a games server on the box?

Cheers
Phil

--
Special one just for you:
Police in Yorkshire are concerned about an alarming rise in the new trend of drug takers using dentists's syringes to inject drugs directly into the mouth.
They have described this tactic as "E by Gum":shocked:

Performance of SSIS Vs Other ETL Tools

Hi All,

I'm working on a conversion project and I'm trying to compare performance of SSIS with Other ETL Tools, especially Informatica PowerCenter. Which one do you think is better ETL performer, when source and destination being SQL Server databases. Is there any benchmark available?

Thanks.

I'm sure that you have seen this, but for future reference, here is a link to jamie's article regarding this question....

http://blogs.conchango.com/jamiethomson/archive/2007/08/21/Informatica-_2D00_vs_2D00_-SSIS-pipeline-throughput.aspx

|||I have used Informatica, SSIS, ETI, and CloverETL.

All the above use Microsoft technologies to connect to SQLServer. Some use ODBC, others use ADO.NET and OLEDB. There are ways with a few of the technologies to configure a 'bulk load' process as an ETL target.

Since the ETL tools all use common Windows technologies for connections, there is very little difference in performance for simple data movement.

Data movement with transformations, sorting, aggregation, filtering, insert/update logic, and other 'intermediate actions' is where you see large differences. There is no overall winner on performance for these types of ETL actions in my opinion. The technology used to for each action dictates how fast things go. Also, if the ETL process uses a bulk read or load process, this can assist with performance increases, though it is only for the initial read and/or final load and not the intermediate actions.

Some examples:
ETI outputs source code you compile into a conversion. So, if you have a complex conversion with SQLServer you are going to compile and deploy a C/C++ program which will usually be much faster than any GUI/Engine-based ETL process. Its not really 'faster' than other tools, its that the approach of using a small C program to move the data is just inheriently faster on average.
CloverETL has a few screaming-fast transformation features, and they are probably fast due to the use of a RegEx engine. Having used JDBC though, I would estimate that DB reads and Writes are faster with ADO/OLEDB. This is not a knock, but a comparison of the technologies used.

It should be noted that I have used Informatica and ETI to create conversions so complex that they should not even be attempted in SSIS. This is where I believe that other tools can outshine SSIS - handling complexity with multiple databases for source and destination, lookups, and hundreds of intermediate actions in-between.

The term 'performance' also needs to be defined a bit here.
We test performance of SSIS based on the time for a Package to run.
If you run a package in the BIDS it usually will run a little slower than if you configure a job or execute DTEXEC.
If you run on 64-bit, it will tend to run a little faster than 32-bit.
Since there are a number of ways to execute a SSIS package, there are a number of ways one can perceive the performance.

Performance of SSIS Vs Other ETL Tools

Hi All,

I'm working on a conversion project and I'm trying to compare performance of SSIS with Other ETL Tools, especially Informatica PowerCenter. Which one do you think is better ETL performer, when source and destination being SQL Server databases. Is there any benchmark available?

Thanks.

I'm sure that you have seen this, but for future reference, here is a link to jamie's article regarding this question....

http://blogs.conchango.com/jamiethomson/archive/2007/08/21/Informatica-_2D00_vs_2D00_-SSIS-pipeline-throughput.aspx

|||I have used Informatica, SSIS, ETI, and CloverETL.

All the above use Microsoft technologies to connect to SQLServer. Some use ODBC, others use ADO.NET and OLEDB. There are ways with a few of the technologies to configure a 'bulk load' process as an ETL target.

Since the ETL tools all use common Windows technologies for connections, there is very little difference in performance for simple data movement.

Data movement with transformations, sorting, aggregation, filtering, insert/update logic, and other 'intermediate actions' is where you see large differences. There is no overall winner on performance for these types of ETL actions in my opinion. The technology used to for each action dictates how fast things go. Also, if the ETL process uses a bulk read or load process, this can assist with performance increases, though it is only for the initial read and/or final load and not the intermediate actions.

Some examples:
ETI outputs source code you compile into a conversion. So, if you have a complex conversion with SQLServer you are going to compile and deploy a C/C++ program which will usually be much faster than any GUI/Engine-based ETL process. Its not really 'faster' than other tools, its that the approach of using a small C program to move the data is just inheriently faster on average.
CloverETL has a few screaming-fast transformation features, and they are probably fast due to the use of a RegEx engine. Having used JDBC though, I would estimate that DB reads and Writes are faster with ADO/OLEDB. This is not a knock, but a comparison of the technologies used.

It should be noted that I have used Informatica and ETI to create conversions so complex that they should not even be attempted in SSIS. This is where I believe that other tools can outshine SSIS - handling complexity with multiple databases for source and destination, lookups, and hundreds of intermediate actions in-between.

The term 'performance' also needs to be defined a bit here.
We test performance of SSIS based on the time for a Package to run.
If you run a package in the BIDS it usually will run a little slower than if you configure a job or execute DTEXEC.
If you run on 64-bit, it will tend to run a little faster than 32-bit.
Since there are a number of ways to execute a SSIS package, there are a number of ways one can perceive the performance.