Performance Question about: ExecStoredProcedure with ATL OLEDB Consumer Templates.

Hi Guys,

I need help in one fine tuning problem. I have developed one Datalayer that it have more implementations depedending on Database Server.

Our older implementation it is based on the db Library from Sql Server. And this implememtation we have replace it with an OLE DB ATL Consumer Templates implementation.

After comparing the performance of course and after we saw that the OLE DB ATL implementation faster is, we said it is a good decision. The compare i have made it with Microsoft SQL Server 2000 for both implementations.

But I forgot to test one statement and this was the Stored procedure exec statement.

All the other statement were faster with ATL OLE DB in compare with DB Library but the ExecStoredProcedure not, and I mean select, delete, update, insert and BCP insert are faster.

After making research why the OLE DB exec Stored Procedure call it is not faster I found the reason for: the OLE DB it is asking the SQL Server before running the store procedure the parameter structure( info like name, type, size etc ..).

If the stored procedure call it is fast (very fast - by example if i read only one row from one table based on a cluster index) then the overload for asking the parameter information it is too big in compare with db Library run time who it is not asking such information.

Lets say I have a stored procedure who it is reading from the database one book with a specific autoid.

So I would expect to run on a SQL Server side this statement:

exec di_sp_di_book_autoid @.autoid = 452

but starting the SQL Profiler I saw that instead of one statement the OLE DB it is sending 2 statements:

exec [demo_test]..sp_procedure_params_rowset N'di_sp_di_book_autoid', 1, NULL, NULL

exec di_sp_di_book_autoid @.autoid = 452,

First statement it is reading the information about the input and output parameters like type size and others... and after it runs the stored procedure only.

The ATL OLE DB it have a lot of properties that somebody can used them to control the behavior of different OLE DB Classes.

I've tried to find one property ( in OLEDB named Parameter) to deactivate this first call to win time and performance of course maybe loosind some error tolerance.

Here I attach the example code as Visual C++ Project example in Visual Studio 2003:

I describe how you can create such a project:

Create one Sample Project with just one Windows Dialog,

One this Dialog make 2 Buttons. In the first button event handler write this connection code:

void CSPTestDlg::OnButtonConnect()

{

USES_CONVERSION;

CWaitCursor waitCursor;

if (m_pDataSource = new CDataSource)

{

CString sServer = "DIKON_NT_005\\SQL2K";

CString sDatabase = "demo_oledb";

CString sUserId = "sqluser1";

CString sPassword = "***";

CDBPropSet dbinit[2] = {DBPROPSET_DBINIT, DBPROPSET_DATASOURCEINFO};

dbinit[0].AddProperty(DBPROP_INIT_DATASOURCE, sServer);

dbinit[0].AddProperty(DBPROP_INIT_CATALOG, sDatabase);

dbinit[0].AddProperty(DBPROP_AUTH_USERID, sUserId);

dbinit[0].AddProperty(DBPROP_AUTH_PASSWORD, sPassword);

dbinit[0].AddProperty(DBPROP_INIT_LCID, (long) 2057);

dbinit[0].AddProperty(DBPROP_INIT_PROMPT, (short) 4);

if (m_pDataSource->Open(_T("SQLOLEDB.1"), dbinit, 2) == S_OK)

{

_variant_t v;

HRESULT hr = m_pDataSource->GetProperty(DBPROPSET_DATASOURCEINFO, DBPROP_MULTIPLERESULTS, &v);

AtlTraceErrorRecords(hr);

if (m_pSession = new CSession)

{

if (m_pSession->Open(*m_pDataSource) == S_OK)

{

GetDlgItem(IDC_BUTTON_CONNECT)->EnableWindow(FALSE);

GetDlgItem(IDC_BUTTON_DISCONNECT)->EnableWindow(TRUE);

GetDlgItem(IDC_BUTTON_EXEC)->EnableWindow(TRUE);

return;

}

else {

MessageBox("Cannot open session!", NULL, MB_OK | MB_ICONERROR);

}

}

}

else {

CString sMessage;

sMessage.Format("Failed to connect to server '%s', database '%s'...", sServer, sDatabase);

MessageBox(sMessage, NULL, MB_OK | MB_ICONERROR);

}

}

delete m_pSession;

m_pSession = NULL;

delete m_pDataSource;

m_pDataSource = NULL;

return;

}

In the second button handler pls copy this code:

void CSPTestDlg::OnButtonExec()

{

DWORD dwStart = GetTickCount();

DWORD dwSum[3] = {0};

for (int nCount = 0; nCount < 10000; nCount++) {

DoTestManualAccessor();

}

DWORD dwEnd = GetTickCount();

DWORD dwTime = dwEnd - dwStart;

CString sMessage;

sMessage.Format("Time: %.3lf sec", dwTime / 1000.0);

MessageBox(sMessage);

return;

}

Here come in plus the code that truns in the second button :

void CSPTestDlg::DoTestManualAccessor()

{

CString sqlStatement;

sqlStatement = "{call di_sp_di_book_autoid(?)}";

CCommand<CManualAccessor, CRowset, CMultipleResults> cmd;

VARIANT v[nParameters];

for (int n = 0; n < nParameters; n++) {

VariantInit(& v[ n ]);

}

v[nParameters-1].vt = VT_I4;

v[nParameters-1].lVal = 15;

/*

CDBPropSet dbinit(DBPROPSET_ROWSET);

dbinit.AddProperty(DBPROP_SERVERCURSOR, true);

dbinit.AddProperty(DBPROP_IRowsetChange, false);

*/

HRESULT hr;

hr = cmd.Create(*m_pSession, sqlStatement);

//hr = cmd.Prepare();

hr = cmd.CreateParameterAccessor(nParameters, (void*) 0x01, 0);

char c_buf[4096] = {NULL};

wchar_t w_buf[4096] = {NULL};

cmd.AddParameterEntry(1, DBTYPE_VARIANT, sizeof(_variant_t), &v[0], NULL, NULL, DBPARAMIO_INPUT);

//(CDBPropSet*)&dbinit

//hr = cmd.Open((CDBPropSet*)&dbinit, NULL, false);

hr = cmd.Open(NULL, NULL,false);

AtlTraceErrorRecords(hr);

if (cmd.m_spRowset) {

cmd.CreateAccessor(3, (void*) 0xfefefefe, 0);

long id = 0;

cmd.AddBindEntry(1, DBTYPE_I4, sizeof(id), &id, NULL, NULL);

long ref = 0;

cmd.AddBindEntry(2, DBTYPE_I4, sizeof(ref), &ref, NULL, NULL);

char name[256] = {NULL};

cmd.AddBindEntry(3, DBTYPE_STR, sizeof(name), &name, NULL, NULL);

hr = cmd.Bind();

do

{

hr = cmd.MoveFirst();

while (hr == S_OK)

{

hr = cmd.MoveNext();

}

long out;

hr = cmd.GetNextResult(&out);

}

while (hr == S_OK);

}

for (int n = 0; n < nParameters; n++) {

VariantClear(& v[ n ]);

}

}

In the header file you must declare this 2 members:

CDataSource* m_pDataSource;

CSession* m_pSession;

In the Header File where the Dialog declared is you must include this fiels too:

#include "atldbcli.h"

#include "comutil.h"

#pragma comment(lib, "comsupp.lib")

Now the Project must run.First correct the login information of course for the connection on your MS SQL Server 2000.

Who wants to get the complete project pls give me your email adress and I will send you the complete Project.

Some comments about this very simple example are: I was commenting the Prepare call because instead of making the call faster it was making the call slower.

And I was trying to set with help of the class CDBPropSet some propertis to make the call faster.The same problem no succes with all porperties I have tried.

The question is it: is my code ok ?

Somebody knows any optimization that i can use ?

It is possible to deactivate this call for asking the parameter info that ATL OLE DB it is making automatically ?

Hi Vasile,

Try binding the parameter as something other than DBTYPE_VARIANT, e.g.

int a1 = 2;

cmd.AddParameterEntry(1, DBTYPE_I4, sizeof(a1), &a1, NULL, NULL, DBPARAMIO_INPUT);

I believe that binding as a variant causes oledb to go once to the server to figure out the actual data type the server is expecting. If the command is going to be executed repeatedly, this is not so bad since the server won't have to do the conversion - better to do that on the client, for the sake of scalability.

Note that if you execute the command in a loop, without destroying the CCommand object between iterations, the sp_procedure_params_rowset call is only done once.

- Dave

|||

Hi David,

I have tested your solution. It is working! Thanks a lot.

In the test program(release version) running the stored procedure 10000 times with the Variant bind it takes 15 seconds and with the int bind it takes 7.5 seconds.

So it takes double so much time with the Variant as with the int.The overload making the bind with the Variant it is to much I think. Maybe should be made clear in the documention form ATL Consumer Templates this costs, for who it is taking the decision to do the Variant bind instead of more type specific bind.

About the second idea it is good too. It is allready implemented but the problem it is I have a IConnection interface who it is having 2 object implementations with DB Library and with ATL OLE DB Consumer Templates. So everything it is encapsulated the client doesn't know what implementation it is using only one Object from our application it takes the responsability to choose and set the implementation.

I said this to know that we are not working directly with CCommand objects in the client code. The CCommand object in the OLEDBConnection I am not destroying it after every call but in general I am using not more then one call one after another from the same stored procedure(I am saving the last Store procedure name executed so if the next call it is made with the same stored procedure I don't need to make the initialization part but in general more then 99% from the use cases it is not helping because in the real case the execution of one store procedure it is followed by the execution of another stored procedure ).

Don't take the test application as I send it to you the real case. It is just a test program to isolate the execute store procedure implementation and to be able to test the performance and the behavior of OLE DB.

Your solution was helping me a lot. Thanks again!

performance problems

hi!
we have developed an application with sql server 2000 as database server. we
still have enormous performance problem and so we started to search for
reasons.
now we made a test were we send a sql query (about 10.000 data records) to
the server and were quite wondering. when we made the test with a lokal pc,
sometimes really old boys, it took some seconds to finish the query. with ou
r
server (dual xenon board, 2,6 mhz, 2 gb mem) it lasts over 3 minutes! when w
e
check the performance monitor of the server he will not have any ressource
troubles. i really dont know which processor the server is using ;-)
how could that happen? are there any troubles with dual boards?
i have also check the processor properties of the server. there are still
two processors listed. the settings should be ok unless i could not change
the settings.
also the disk is ok.
would be great if someone could give me a hint.
greetings,
markusMarkus hi,
Consider that there are many things to check in order to issue a best
performance in SQL 2000. First of all, you need to check the disk I/O. The
partitioning is one other thing.
In the Microsoft SQL 2000 resource kit there is the following article
2061.mspx" target="_blank">http://www.microsoft.com/resources/...r />
2061.mspx
In this article you will find plenty of information regarding performance in
RDBMS
HTH
Andreas
"markus" wrote:

> hi!
> we have developed an application with sql server 2000 as database server.
we
> still have enormous performance problem and so we started to search for
> reasons.
> now we made a test were we send a sql query (about 10.000 data records) to
> the server and were quite wondering. when we made the test with a lokal pc
,
> sometimes really old boys, it took some seconds to finish the query. with
our
> server (dual xenon board, 2,6 mhz, 2 gb mem) it lasts over 3 minutes! when
we
> check the performance monitor of the server he will not have any ressource
> troubles. i really dont know which processor the server is using ;-)
> how could that happen? are there any troubles with dual boards?
> i have also check the processor properties of the server. there are still
> two processors listed. the settings should be ok unless i could not change
> the settings.
> also the disk is ok.
> would be great if someone could give me a hint.
> greetings,
> markus
>
>|||hi andreas,
thanks for the link. i went through it quickly. sure there are a lot of
important things mentioned. but this still can't explain the gap of my
performance test. and i did not notice hardly any change in the performance
monitor during the test. the cpu load is about 7% and it's the same with ram
.
regards,
markus
"Andreas Mavrogenis" wrote:
[vbcol=seagreen]
> Markus hi,
> Consider that there are many things to check in order to issue a best
> performance in SQL 2000. First of all, you need to check the disk I/O. The
> partitioning is one other thing.
> In the Microsoft SQL 2000 resource kit there is the following article
> /c2061.mspx" target="_blank">http://www.microsoft.com/resources/.../>
/c2061.mspx
> In this article you will find plenty of information regarding performance
in
> RDBMS
> HTH
> Andreas
> "markus" wrote:
>|||Hi,
In your test, you run the query in a single processor pc with no-one loged
in. In your server is always a little bit slower. If your server is a HT
server, you will see in the task manager (tab performance), 4 CPU histograms
.
Notice if running the query, captures only one CPU. If this is happening,
check your code if there is a max dop = 1 option. This option is to run the
query only in one processor.
Also, you must tell us if your table has any indexes. If there are, try to
re-index them.
FInally, you must check your disks. Is it possible that you have RAID-5 for
both mdf and ldf files?
What is the configuration ?
HTH
Andreas
"markus" wrote:
[vbcol=seagreen]
> hi andreas,
> thanks for the link. i went through it quickly. sure there are a lot of
> important things mentioned. but this still can't explain the gap of my
> performance test. and i did not notice hardly any change in the performanc
e
> monitor during the test. the cpu load is about 7% and it's the same with r
am.
> regards,
> markus
> "Andreas Mavrogenis" wrote:
>|||hi,
yes, we use a raid 5. the other traffic load on the server is not really
heavy.
at the end of my post i attached the query, nothing sophisticated.
thanks for your help so far,
***********************
if exists (select 'TRUE' from sysobjects where name = 'performance' and
type = 'u')
drop table performance
go
CREATE TABLE performance (
ident int NOT NULL ,
blabla varchar (250) COLLATE Latin1_General_CI_AS NOT NULL ,
datum datetime NOT NULL CONSTRAINT DF__performan__datum__102C51FF DEFAULT
(getdate()),
rowguid uniqueidentifier NOT NULL CONSTRAINT DF__performan__rowgu__11207638
DEFAULT (newid()),
Cash money not null,
CONSTRAINT pk_performance PRIMARY KEY CLUSTERED (ident),
CONSTRAINT ux_performance UNIQUE NONCLUSTERED (blabla)
)
GO
go
set nocount on
go
declare @.i int,
@.count int
set @.count = 10000
select @.i = isnull(max(ident) + 1, 1) from performance
set @.count = @.count + @.i
while @.i < @.count begin
insert into performance (ident, blabla, cash)
values (@.i,
convert(varchar(10), @.i) +
'blablablablablablablablablablablablabla
blabla' + convert(varchar(10), @.i),
rand(@.i)
)
set @.i = @.i + 1
end
go
select * from performance
go
"Andreas Mavrogenis" wrote:
[vbcol=seagreen]
> Hi,
> In your test, you run the query in a single processor pc with no-one loged
> in. In your server is always a little bit slower. If your server is a HT
> server, you will see in the task manager (tab performance), 4 CPU histogra
ms.
> Notice if running the query, captures only one CPU. If this is happening,
> check your code if there is a max dop = 1 option. This option is to run th
e
> query only in one processor.
> Also, you must tell us if your table has any indexes. If there are, try to
> re-index them.
> FInally, you must check your disks. Is it possible that you have RAID-5 fo
r
> both mdf and ldf files?
> What is the configuration ?
> HTH
> Andreas
>
> "markus" wrote:
>|||Hi,
Try to change your while statement with cursor. You will find sample code in
BOL. It works better!. Also, the RAID-5 is mostly for lots of reads and not
for writes. If you can, add two more disks in your array, make them RAID 1
and detach/attach the log (ldf ) file to this new drive.
HTH
Andreas
"markus" wrote:
[vbcol=seagreen]
> hi,
> yes, we use a raid 5. the other traffic load on the server is not really
> heavy.
> at the end of my post i attached the query, nothing sophisticated.
> thanks for your help so far,
> ***********************
> if exists (select 'TRUE' from sysobjects where name = 'performance' and
> type = 'u')
> drop table performance
> go
> CREATE TABLE performance (
> ident int NOT NULL ,
> blabla varchar (250) COLLATE Latin1_General_CI_AS NOT NULL ,
> datum datetime NOT NULL CONSTRAINT DF__performan__datum__102C51FF DEFAULT
> (getdate()),
> rowguid uniqueidentifier NOT NULL CONSTRAINT DF__performan__rowgu__112076
38
> DEFAULT (newid()),
> Cash money not null,
> CONSTRAINT pk_performance PRIMARY KEY CLUSTERED (ident),
> CONSTRAINT ux_performance UNIQUE NONCLUSTERED (blabla)
> )
> GO
> go
> set nocount on
> go
> declare @.i int,
> @.count int
> set @.count = 10000
> select @.i = isnull(max(ident) + 1, 1) from performance
> set @.count = @.count + @.i
> while @.i < @.count begin
> insert into performance (ident, blabla, cash)
> values (@.i,
> convert(varchar(10), @.i) +
> 'blablablablablablablablablablablablabla
blabla' + convert(varchar(10), @.i)
,
> rand(@.i)
> )
> set @.i = @.i + 1
> end
> go
> select * from performance
> go
> "Andreas Mavrogenis" wrote:
>|||hi,
unfortunately i have to use raid 5. i should not mention that there are
running all windows things and no only the database stuff.
but can it be that the raid 5 causes such a gap? with a normal pc it took
appr. 10 sec to carry out the statement. with the server i need more than 3
minutes! and when raid 5 causes that delay i should even notice some tasks i
n
the performance monitor. it's really confusing.
regards,
markus
"Andreas Mavrogenis" wrote:
[vbcol=seagreen]
> Hi,
> Try to change your while statement with cursor. You will find sample code
in
> BOL. It works better!. Also, the RAID-5 is mostly for lots of reads and no
t
> for writes. If you can, add two more disks in your array, make them RAID 1
> and detach/attach the log (ldf ) file to this new drive.
> HTH
> Andreas
> "markus" wrote:
>|||Have you looked at the execution plans for both queries on the server and
client? Is there any difference?
Also check the query Analyzer version on the client and the server.
"markus" <markus@.discussions.microsoft.com> wrote in message
news:5F17AC7D-EA4A-400C-A67C-22F578AE016D@.microsoft.com...
> hi!
> we have developed an application with sql server 2000 as database server.
> we
> still have enormous performance problem and so we started to search for
> reasons.
> now we made a test were we send a sql query (about 10.000 data records) to
> the server and were quite wondering. when we made the test with a lokal
> pc,
> sometimes really old boys, it took some seconds to finish the query. with
> our
> server (dual xenon board, 2,6 mhz, 2 gb mem) it lasts over 3 minutes! when
> we
> check the performance monitor of the server he will not have any ressource
> troubles. i really dont know which processor the server is using ;-)
> how could that happen? are there any troubles with dual boards?
> i have also check the processor properties of the server. there are still
> two processors listed. the settings should be ok unless i could not change
> the settings.
> also the disk is ok.
> would be great if someone could give me a hint.
> greetings,
> markus
>
>|||both tests took place on a sql server 2k, just the hardware is really
different ;-)
the query analyzer version is the same.
"Richard Ding" wrote:

> Have you looked at the execution plans for both queries on the server and
> client? Is there any difference?
> Also check the query Analyzer version on the client and the server.
>
> "markus" <markus@.discussions.microsoft.com> wrote in message
> news:5F17AC7D-EA4A-400C-A67C-22F578AE016D@.microsoft.com...
>
>|||Marcus Hi,
Please check this article, and follow it's steps to optimize your query
http://support.microsoft.com/defaul...kb;en-us;243589
If you don't have any results, try to disable one of your two processors,
stop-start SQL Services and run the query again. Notice the task manager
performance to see it's activity.
If not, try to create a trace file and capture what your query is doing.
Use this article to analyze your performance data
http://support.microsoft.com/defaul...kb;en-us;283886
HTH
Andreas
"markus" wrote:
[vbcol=seagreen]
> both tests took place on a sql server 2k, just the hardware is really
> different ;-)
> the query analyzer version is the same.
>
> "Richard Ding" wrote:
>

performance problems

hi!
we have developed an application with sql server 2000 as database server. we
still have enormous performance problem and so we started to search for
reasons.
now we made a test were we send a sql query (about 10.000 data records) to
the server and were quite wondering. when we made the test with a lokal pc,
sometimes really old boys, it took some seconds to finish the query. with our
server (dual xenon board, 2,6 mhz, 2 gb mem) it lasts over 3 minutes! when we
check the performance monitor of the server he will not have any ressource
troubles. i really dont know which processor the server is using ;-)
how could that happen? are there any troubles with dual boards?
i have also check the processor properties of the server. there are still
two processors listed. the settings should be ok unless i could not change
the settings.
also the disk is ok.
would be great if someone could give me a hint.
greetings,
markus
Markus hi,
Consider that there are many things to check in order to issue a best
performance in SQL 2000. First of all, you need to check the disk I/O. The
partitioning is one other thing.
In the Microsoft SQL 2000 resource kit there is the following article
http://www.microsoft.com/resources/d...rt5/c2061.mspx
In this article you will find plenty of information regarding performance in
RDBMS
HTH
Andreas
"markus" wrote:

> hi!
> we have developed an application with sql server 2000 as database server. we
> still have enormous performance problem and so we started to search for
> reasons.
> now we made a test were we send a sql query (about 10.000 data records) to
> the server and were quite wondering. when we made the test with a lokal pc,
> sometimes really old boys, it took some seconds to finish the query. with our
> server (dual xenon board, 2,6 mhz, 2 gb mem) it lasts over 3 minutes! when we
> check the performance monitor of the server he will not have any ressource
> troubles. i really dont know which processor the server is using ;-)
> how could that happen? are there any troubles with dual boards?
> i have also check the processor properties of the server. there are still
> two processors listed. the settings should be ok unless i could not change
> the settings.
> also the disk is ok.
> would be great if someone could give me a hint.
> greetings,
> markus
>
>
|||hi andreas,
thanks for the link. i went through it quickly. sure there are a lot of
important things mentioned. but this still can't explain the gap of my
performance test. and i did not notice hardly any change in the performance
monitor during the test. the cpu load is about 7% and it's the same with ram.
regards,
markus
"Andreas Mavrogenis" wrote:
[vbcol=seagreen]
> Markus hi,
> Consider that there are many things to check in order to issue a best
> performance in SQL 2000. First of all, you need to check the disk I/O. The
> partitioning is one other thing.
> In the Microsoft SQL 2000 resource kit there is the following article
> http://www.microsoft.com/resources/d...rt5/c2061.mspx
> In this article you will find plenty of information regarding performance in
> RDBMS
> HTH
> Andreas
> "markus" wrote:
|||Hi,
In your test, you run the query in a single processor pc with no-one loged
in. In your server is always a little bit slower. If your server is a HT
server, you will see in the task manager (tab performance), 4 CPU histograms.
Notice if running the query, captures only one CPU. If this is happening,
check your code if there is a max dop = 1 option. This option is to run the
query only in one processor.
Also, you must tell us if your table has any indexes. If there are, try to
re-index them.
FInally, you must check your disks. Is it possible that you have RAID-5 for
both mdf and ldf files?
What is the configuration ?
HTH
Andreas
"markus" wrote:
[vbcol=seagreen]
> hi andreas,
> thanks for the link. i went through it quickly. sure there are a lot of
> important things mentioned. but this still can't explain the gap of my
> performance test. and i did not notice hardly any change in the performance
> monitor during the test. the cpu load is about 7% and it's the same with ram.
> regards,
> markus
> "Andreas Mavrogenis" wrote:
|||hi,
yes, we use a raid 5. the other traffic load on the server is not really
heavy.
at the end of my post i attached the query, nothing sophisticated.
thanks for your help so far,
***********************
if exists (select 'TRUE' from sysobjects where name = 'performance' and
type = 'u')
drop table performance
go
CREATE TABLE performance (
ident int NOT NULL ,
blabla varchar (250) COLLATE Latin1_General_CI_AS NOT NULL ,
datum datetime NOT NULL CONSTRAINT DF__performan__datum__102C51FF DEFAULT
(getdate()),
rowguid uniqueidentifier NOT NULL CONSTRAINT DF__performan__rowgu__11207638
DEFAULT (newid()),
Cash money not null,
CONSTRAINT pk_performance PRIMARY KEY CLUSTERED (ident),
CONSTRAINT ux_performance UNIQUE NONCLUSTERED (blabla)
)
GO
go
set nocount on
go
declare @.i int,
@.count int
set @.count = 10000
select @.i = isnull(max(ident) + 1, 1) from performance
set @.count = @.count + @.i
while @.i < @.count begin
insert into performance (ident, blabla, cash)
values (@.i,
convert(varchar(10), @.i) +
'blablablablablablablablablablablablablablabla' + convert(varchar(10), @.i),
rand(@.i)
)
set @.i = @.i + 1
end
go
select * from performance
go
"Andreas Mavrogenis" wrote:
[vbcol=seagreen]
> Hi,
> In your test, you run the query in a single processor pc with no-one loged
> in. In your server is always a little bit slower. If your server is a HT
> server, you will see in the task manager (tab performance), 4 CPU histograms.
> Notice if running the query, captures only one CPU. If this is happening,
> check your code if there is a max dop = 1 option. This option is to run the
> query only in one processor.
> Also, you must tell us if your table has any indexes. If there are, try to
> re-index them.
> FInally, you must check your disks. Is it possible that you have RAID-5 for
> both mdf and ldf files?
> What is the configuration ?
> HTH
> Andreas
>
> "markus" wrote:
|||Hi,
Try to change your while statement with cursor. You will find sample code in
BOL. It works better!. Also, the RAID-5 is mostly for lots of reads and not
for writes. If you can, add two more disks in your array, make them RAID 1
and detach/attach the log (ldf ) file to this new drive.
HTH
Andreas
"markus" wrote:
[vbcol=seagreen]
> hi,
> yes, we use a raid 5. the other traffic load on the server is not really
> heavy.
> at the end of my post i attached the query, nothing sophisticated.
> thanks for your help so far,
> ***********************
> if exists (select 'TRUE' from sysobjects where name = 'performance' and
> type = 'u')
> drop table performance
> go
> CREATE TABLE performance (
> ident int NOT NULL ,
> blabla varchar (250) COLLATE Latin1_General_CI_AS NOT NULL ,
> datum datetime NOT NULL CONSTRAINT DF__performan__datum__102C51FF DEFAULT
> (getdate()),
> rowguid uniqueidentifier NOT NULL CONSTRAINT DF__performan__rowgu__11207638
> DEFAULT (newid()),
> Cash money not null,
> CONSTRAINT pk_performance PRIMARY KEY CLUSTERED (ident),
> CONSTRAINT ux_performance UNIQUE NONCLUSTERED (blabla)
> )
> GO
> go
> set nocount on
> go
> declare @.i int,
> @.count int
> set @.count = 10000
> select @.i = isnull(max(ident) + 1, 1) from performance
> set @.count = @.count + @.i
> while @.i < @.count begin
> insert into performance (ident, blabla, cash)
> values (@.i,
> convert(varchar(10), @.i) +
> 'blablablablablablablablablablablablablablabla' + convert(varchar(10), @.i),
> rand(@.i)
> )
> set @.i = @.i + 1
> end
> go
> select * from performance
> go
> "Andreas Mavrogenis" wrote:
|||hi,
unfortunately i have to use raid 5. i should not mention that there are
running all windows things and no only the database stuff.
but can it be that the raid 5 causes such a gap? with a normal pc it took
appr. 10 sec to carry out the statement. with the server i need more than 3
minutes! and when raid 5 causes that delay i should even notice some tasks in
the performance monitor. it's really confusing.
regards,
markus
"Andreas Mavrogenis" wrote:
[vbcol=seagreen]
> Hi,
> Try to change your while statement with cursor. You will find sample code in
> BOL. It works better!. Also, the RAID-5 is mostly for lots of reads and not
> for writes. If you can, add two more disks in your array, make them RAID 1
> and detach/attach the log (ldf ) file to this new drive.
> HTH
> Andreas
> "markus" wrote:
|||Have you looked at the execution plans for both queries on the server and
client? Is there any difference?
Also check the query Analyzer version on the client and the server.
"markus" <markus@.discussions.microsoft.com> wrote in message
news:5F17AC7D-EA4A-400C-A67C-22F578AE016D@.microsoft.com...
> hi!
> we have developed an application with sql server 2000 as database server.
> we
> still have enormous performance problem and so we started to search for
> reasons.
> now we made a test were we send a sql query (about 10.000 data records) to
> the server and were quite wondering. when we made the test with a lokal
> pc,
> sometimes really old boys, it took some seconds to finish the query. with
> our
> server (dual xenon board, 2,6 mhz, 2 gb mem) it lasts over 3 minutes! when
> we
> check the performance monitor of the server he will not have any ressource
> troubles. i really dont know which processor the server is using ;-)
> how could that happen? are there any troubles with dual boards?
> i have also check the processor properties of the server. there are still
> two processors listed. the settings should be ok unless i could not change
> the settings.
> also the disk is ok.
> would be great if someone could give me a hint.
> greetings,
> markus
>
>
|||both tests took place on a sql server 2k, just the hardware is really
different ;-)
the query analyzer version is the same.
"Richard Ding" wrote:

> Have you looked at the execution plans for both queries on the server and
> client? Is there any difference?
> Also check the query Analyzer version on the client and the server.
>
> "markus" <markus@.discussions.microsoft.com> wrote in message
> news:5F17AC7D-EA4A-400C-A67C-22F578AE016D@.microsoft.com...
>
>
|||Marcus Hi,
Please check this article, and follow it's steps to optimize your query
http://support.microsoft.com/default...b;en-us;243589
If you don't have any results, try to disable one of your two processors,
stop-start SQL Services and run the query again. Notice the task manager
performance to see it's activity.
If not, try to create a trace file and capture what your query is doing.
Use this article to analyze your performance data
http://support.microsoft.com/default...b;en-us;283886
HTH
Andreas
"markus" wrote:
[vbcol=seagreen]
> both tests took place on a sql server 2k, just the hardware is really
> different ;-)
> the query analyzer version is the same.
>
> "Richard Ding" wrote:
sql

performance problems

hi!
we have developed an application with sql server 2000 as database server. we
still have enormous performance problem and so we started to search for
reasons.
now we made a test were we send a sql query (about 10.000 data records) to
the server and were quite wondering. when we made the test with a lokal pc,
sometimes really old boys, it took some seconds to finish the query. with our
server (dual xenon board, 2,6 mhz, 2 gb mem) it lasts over 3 minutes! when we
check the performance monitor of the server he will not have any ressource
troubles. i really dont know which processor the server is using ;-)
how could that happen? are there any troubles with dual boards?
i have also check the processor properties of the server. there are still
two processors listed. the settings should be ok unless i could not change
the settings.
also the disk is ok.
would be great if someone could give me a hint.
greetings,
markusMarkus hi,
Consider that there are many things to check in order to issue a best
performance in SQL 2000. First of all, you need to check the disk I/O. The
partitioning is one other thing.
In the Microsoft SQL 2000 resource kit there is the following articl
http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/part5/c2061.mspx
In this article you will find plenty of information regarding performance in
RDBMS
HTH
Andreas
"markus" wrote:
> hi!
> we have developed an application with sql server 2000 as database server. we
> still have enormous performance problem and so we started to search for
> reasons.
> now we made a test were we send a sql query (about 10.000 data records) to
> the server and were quite wondering. when we made the test with a lokal pc,
> sometimes really old boys, it took some seconds to finish the query. with our
> server (dual xenon board, 2,6 mhz, 2 gb mem) it lasts over 3 minutes! when we
> check the performance monitor of the server he will not have any ressource
> troubles. i really dont know which processor the server is using ;-)
> how could that happen? are there any troubles with dual boards?
> i have also check the processor properties of the server. there are still
> two processors listed. the settings should be ok unless i could not change
> the settings.
> also the disk is ok.
> would be great if someone could give me a hint.
> greetings,
> markus
>
>|||hi andreas,
thanks for the link. i went through it quickly. sure there are a lot of
important things mentioned. but this still can't explain the gap of my
performance test. and i did not notice hardly any change in the performance
monitor during the test. the cpu load is about 7% and it's the same with ram.
regards,
markus
"Andreas Mavrogenis" wrote:
> Markus hi,
> Consider that there are many things to check in order to issue a best
> performance in SQL 2000. First of all, you need to check the disk I/O. The
> partitioning is one other thing.
> In the Microsoft SQL 2000 resource kit there is the following article
> http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/part5/c2061.mspx
> In this article you will find plenty of information regarding performance in
> RDBMS
> HTH
> Andreas
> "markus" wrote:
> > hi!
> >
> > we have developed an application with sql server 2000 as database server. we
> > still have enormous performance problem and so we started to search for
> > reasons.
> >
> > now we made a test were we send a sql query (about 10.000 data records) to
> > the server and were quite wondering. when we made the test with a lokal pc,
> > sometimes really old boys, it took some seconds to finish the query. with our
> > server (dual xenon board, 2,6 mhz, 2 gb mem) it lasts over 3 minutes! when we
> > check the performance monitor of the server he will not have any ressource
> > troubles. i really dont know which processor the server is using ;-)
> >
> > how could that happen? are there any troubles with dual boards?
> >
> > i have also check the processor properties of the server. there are still
> > two processors listed. the settings should be ok unless i could not change
> > the settings.
> > also the disk is ok.
> >
> > would be great if someone could give me a hint.
> >
> > greetings,
> > markus
> >
> >
> >
> >|||Hi,
In your test, you run the query in a single processor pc with no-one loged
in. In your server is always a little bit slower. If your server is a HT
server, you will see in the task manager (tab performance), 4 CPU histograms.
Notice if running the query, captures only one CPU. If this is happening,
check your code if there is a max dop = 1 option. This option is to run the
query only in one processor.
Also, you must tell us if your table has any indexes. If there are, try to
re-index them.
FInally, you must check your disks. Is it possible that you have RAID-5 for
both mdf and ldf files?
What is the configuration ?
HTH
Andreas
"markus" wrote:
> hi andreas,
> thanks for the link. i went through it quickly. sure there are a lot of
> important things mentioned. but this still can't explain the gap of my
> performance test. and i did not notice hardly any change in the performance
> monitor during the test. the cpu load is about 7% and it's the same with ram.
> regards,
> markus
> "Andreas Mavrogenis" wrote:
> > Markus hi,
> >
> > Consider that there are many things to check in order to issue a best
> > performance in SQL 2000. First of all, you need to check the disk I/O. The
> > partitioning is one other thing.
> >
> > In the Microsoft SQL 2000 resource kit there is the following article
> > http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/part5/c2061.mspx
> >
> > In this article you will find plenty of information regarding performance in
> > RDBMS
> >
> > HTH
> > Andreas
> >
> > "markus" wrote:
> >
> > > hi!
> > >
> > > we have developed an application with sql server 2000 as database server. we
> > > still have enormous performance problem and so we started to search for
> > > reasons.
> > >
> > > now we made a test were we send a sql query (about 10.000 data records) to
> > > the server and were quite wondering. when we made the test with a lokal pc,
> > > sometimes really old boys, it took some seconds to finish the query. with our
> > > server (dual xenon board, 2,6 mhz, 2 gb mem) it lasts over 3 minutes! when we
> > > check the performance monitor of the server he will not have any ressource
> > > troubles. i really dont know which processor the server is using ;-)
> > >
> > > how could that happen? are there any troubles with dual boards?
> > >
> > > i have also check the processor properties of the server. there are still
> > > two processors listed. the settings should be ok unless i could not change
> > > the settings.
> > > also the disk is ok.
> > >
> > > would be great if someone could give me a hint.
> > >
> > > greetings,
> > > markus
> > >
> > >
> > >
> > >|||hi,
yes, we use a raid 5. the other traffic load on the server is not really
heavy.
at the end of my post i attached the query, nothing sophisticated.
thanks for your help so far,
***********************
if exists (select 'TRUE' from sysobjects where name = 'performance' and
type = 'u')
drop table performance
go
CREATE TABLE performance (
ident int NOT NULL ,
blabla varchar (250) COLLATE Latin1_General_CI_AS NOT NULL ,
datum datetime NOT NULL CONSTRAINT DF__performan__datum__102C51FF DEFAULT
(getdate()),
rowguid uniqueidentifier NOT NULL CONSTRAINT DF__performan__rowgu__11207638
DEFAULT (newid()),
Cash money not null,
CONSTRAINT pk_performance PRIMARY KEY CLUSTERED (ident),
CONSTRAINT ux_performance UNIQUE NONCLUSTERED (blabla)
)
GO
go
set nocount on
go
declare @.i int,
@.count int
set @.count = 10000
select @.i = isnull(max(ident) + 1, 1) from performance
set @.count = @.count + @.i
while @.i < @.count begin
insert into performance (ident, blabla, cash)
values (@.i,
convert(varchar(10), @.i) +
'blablablablablablablablablablablablablablabla' + convert(varchar(10), @.i),
rand(@.i)
)
set @.i = @.i + 1
end
go
select * from performance
go
"Andreas Mavrogenis" wrote:
> Hi,
> In your test, you run the query in a single processor pc with no-one loged
> in. In your server is always a little bit slower. If your server is a HT
> server, you will see in the task manager (tab performance), 4 CPU histograms.
> Notice if running the query, captures only one CPU. If this is happening,
> check your code if there is a max dop = 1 option. This option is to run the
> query only in one processor.
> Also, you must tell us if your table has any indexes. If there are, try to
> re-index them.
> FInally, you must check your disks. Is it possible that you have RAID-5 for
> both mdf and ldf files?
> What is the configuration ?
> HTH
> Andreas
>
> "markus" wrote:
> > hi andreas,
> >
> > thanks for the link. i went through it quickly. sure there are a lot of
> > important things mentioned. but this still can't explain the gap of my
> > performance test. and i did not notice hardly any change in the performance
> > monitor during the test. the cpu load is about 7% and it's the same with ram.
> >
> > regards,
> > markus
> >
> > "Andreas Mavrogenis" wrote:
> >
> > > Markus hi,
> > >
> > > Consider that there are many things to check in order to issue a best
> > > performance in SQL 2000. First of all, you need to check the disk I/O. The
> > > partitioning is one other thing.
> > >
> > > In the Microsoft SQL 2000 resource kit there is the following article
> > > http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/part5/c2061.mspx
> > >
> > > In this article you will find plenty of information regarding performance in
> > > RDBMS
> > >
> > > HTH
> > > Andreas
> > >
> > > "markus" wrote:
> > >
> > > > hi!
> > > >
> > > > we have developed an application with sql server 2000 as database server. we
> > > > still have enormous performance problem and so we started to search for
> > > > reasons.
> > > >
> > > > now we made a test were we send a sql query (about 10.000 data records) to
> > > > the server and were quite wondering. when we made the test with a lokal pc,
> > > > sometimes really old boys, it took some seconds to finish the query. with our
> > > > server (dual xenon board, 2,6 mhz, 2 gb mem) it lasts over 3 minutes! when we
> > > > check the performance monitor of the server he will not have any ressource
> > > > troubles. i really dont know which processor the server is using ;-)
> > > >
> > > > how could that happen? are there any troubles with dual boards?
> > > >
> > > > i have also check the processor properties of the server. there are still
> > > > two processors listed. the settings should be ok unless i could not change
> > > > the settings.
> > > > also the disk is ok.
> > > >
> > > > would be great if someone could give me a hint.
> > > >
> > > > greetings,
> > > > markus
> > > >
> > > >
> > > >
> > > >|||Hi,
Try to change your while statement with cursor. You will find sample code in
BOL. It works better!. Also, the RAID-5 is mostly for lots of reads and not
for writes. If you can, add two more disks in your array, make them RAID 1
and detach/attach the log (ldf ) file to this new drive.
HTH
Andreas
"markus" wrote:
> hi,
> yes, we use a raid 5. the other traffic load on the server is not really
> heavy.
> at the end of my post i attached the query, nothing sophisticated.
> thanks for your help so far,
> ***********************
> if exists (select 'TRUE' from sysobjects where name = 'performance' and
> type = 'u')
> drop table performance
> go
> CREATE TABLE performance (
> ident int NOT NULL ,
> blabla varchar (250) COLLATE Latin1_General_CI_AS NOT NULL ,
> datum datetime NOT NULL CONSTRAINT DF__performan__datum__102C51FF DEFAULT
> (getdate()),
> rowguid uniqueidentifier NOT NULL CONSTRAINT DF__performan__rowgu__11207638
> DEFAULT (newid()),
> Cash money not null,
> CONSTRAINT pk_performance PRIMARY KEY CLUSTERED (ident),
> CONSTRAINT ux_performance UNIQUE NONCLUSTERED (blabla)
> )
> GO
> go
> set nocount on
> go
> declare @.i int,
> @.count int
> set @.count = 10000
> select @.i = isnull(max(ident) + 1, 1) from performance
> set @.count = @.count + @.i
> while @.i < @.count begin
> insert into performance (ident, blabla, cash)
> values (@.i,
> convert(varchar(10), @.i) +
> 'blablablablablablablablablablablablablablabla' + convert(varchar(10), @.i),
> rand(@.i)
> )
> set @.i = @.i + 1
> end
> go
> select * from performance
> go
> "Andreas Mavrogenis" wrote:
> > Hi,
> >
> > In your test, you run the query in a single processor pc with no-one loged
> > in. In your server is always a little bit slower. If your server is a HT
> > server, you will see in the task manager (tab performance), 4 CPU histograms.
> > Notice if running the query, captures only one CPU. If this is happening,
> > check your code if there is a max dop = 1 option. This option is to run the
> > query only in one processor.
> >
> > Also, you must tell us if your table has any indexes. If there are, try to
> > re-index them.
> >
> > FInally, you must check your disks. Is it possible that you have RAID-5 for
> > both mdf and ldf files?
> >
> > What is the configuration ?
> >
> > HTH
> > Andreas
> >
> >
> > "markus" wrote:
> >
> > > hi andreas,
> > >
> > > thanks for the link. i went through it quickly. sure there are a lot of
> > > important things mentioned. but this still can't explain the gap of my
> > > performance test. and i did not notice hardly any change in the performance
> > > monitor during the test. the cpu load is about 7% and it's the same with ram.
> > >
> > > regards,
> > > markus
> > >
> > > "Andreas Mavrogenis" wrote:
> > >
> > > > Markus hi,
> > > >
> > > > Consider that there are many things to check in order to issue a best
> > > > performance in SQL 2000. First of all, you need to check the disk I/O. The
> > > > partitioning is one other thing.
> > > >
> > > > In the Microsoft SQL 2000 resource kit there is the following article
> > > > http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/part5/c2061.mspx
> > > >
> > > > In this article you will find plenty of information regarding performance in
> > > > RDBMS
> > > >
> > > > HTH
> > > > Andreas
> > > >
> > > > "markus" wrote:
> > > >
> > > > > hi!
> > > > >
> > > > > we have developed an application with sql server 2000 as database server. we
> > > > > still have enormous performance problem and so we started to search for
> > > > > reasons.
> > > > >
> > > > > now we made a test were we send a sql query (about 10.000 data records) to
> > > > > the server and were quite wondering. when we made the test with a lokal pc,
> > > > > sometimes really old boys, it took some seconds to finish the query. with our
> > > > > server (dual xenon board, 2,6 mhz, 2 gb mem) it lasts over 3 minutes! when we
> > > > > check the performance monitor of the server he will not have any ressource
> > > > > troubles. i really dont know which processor the server is using ;-)
> > > > >
> > > > > how could that happen? are there any troubles with dual boards?
> > > > >
> > > > > i have also check the processor properties of the server. there are still
> > > > > two processors listed. the settings should be ok unless i could not change
> > > > > the settings.
> > > > > also the disk is ok.
> > > > >
> > > > > would be great if someone could give me a hint.
> > > > >
> > > > > greetings,
> > > > > markus
> > > > >
> > > > >
> > > > >
> > > > >|||hi,
unfortunately i have to use raid 5. i should not mention that there are
running all windows things and no only the database stuff.
but can it be that the raid 5 causes such a gap? with a normal pc it took
appr. 10 sec to carry out the statement. with the server i need more than 3
minutes! and when raid 5 causes that delay i should even notice some tasks in
the performance monitor. it's really confusing.
regards,
markus
"Andreas Mavrogenis" wrote:
> Hi,
> Try to change your while statement with cursor. You will find sample code in
> BOL. It works better!. Also, the RAID-5 is mostly for lots of reads and not
> for writes. If you can, add two more disks in your array, make them RAID 1
> and detach/attach the log (ldf ) file to this new drive.
> HTH
> Andreas
> "markus" wrote:
> > hi,
> >
> > yes, we use a raid 5. the other traffic load on the server is not really
> > heavy.
> > at the end of my post i attached the query, nothing sophisticated.
> >
> > thanks for your help so far,
> >
> > ***********************
> > if exists (select 'TRUE' from sysobjects where name = 'performance' and
> > type = 'u')
> > drop table performance
> > go
> > CREATE TABLE performance (
> > ident int NOT NULL ,
> > blabla varchar (250) COLLATE Latin1_General_CI_AS NOT NULL ,
> > datum datetime NOT NULL CONSTRAINT DF__performan__datum__102C51FF DEFAULT
> > (getdate()),
> > rowguid uniqueidentifier NOT NULL CONSTRAINT DF__performan__rowgu__11207638
> > DEFAULT (newid()),
> > Cash money not null,
> > CONSTRAINT pk_performance PRIMARY KEY CLUSTERED (ident),
> > CONSTRAINT ux_performance UNIQUE NONCLUSTERED (blabla)
> >
> > )
> > GO
> > go
> > set nocount on
> > go
> > declare @.i int,
> > @.count int
> >
> > set @.count = 10000
> > select @.i = isnull(max(ident) + 1, 1) from performance
> > set @.count = @.count + @.i
> >
> > while @.i < @.count begin
> > insert into performance (ident, blabla, cash)
> > values (@.i,
> > convert(varchar(10), @.i) +
> > 'blablablablablablablablablablablablablablabla' + convert(varchar(10), @.i),
> > rand(@.i)
> > )
> > set @.i = @.i + 1
> > end
> >
> > go
> > select * from performance
> > go
> >
> > "Andreas Mavrogenis" wrote:
> >
> > > Hi,
> > >
> > > In your test, you run the query in a single processor pc with no-one loged
> > > in. In your server is always a little bit slower. If your server is a HT
> > > server, you will see in the task manager (tab performance), 4 CPU histograms.
> > > Notice if running the query, captures only one CPU. If this is happening,
> > > check your code if there is a max dop = 1 option. This option is to run the
> > > query only in one processor.
> > >
> > > Also, you must tell us if your table has any indexes. If there are, try to
> > > re-index them.
> > >
> > > FInally, you must check your disks. Is it possible that you have RAID-5 for
> > > both mdf and ldf files?
> > >
> > > What is the configuration ?
> > >
> > > HTH
> > > Andreas
> > >
> > >
> > > "markus" wrote:
> > >
> > > > hi andreas,
> > > >
> > > > thanks for the link. i went through it quickly. sure there are a lot of
> > > > important things mentioned. but this still can't explain the gap of my
> > > > performance test. and i did not notice hardly any change in the performance
> > > > monitor during the test. the cpu load is about 7% and it's the same with ram.
> > > >
> > > > regards,
> > > > markus
> > > >
> > > > "Andreas Mavrogenis" wrote:
> > > >
> > > > > Markus hi,
> > > > >
> > > > > Consider that there are many things to check in order to issue a best
> > > > > performance in SQL 2000. First of all, you need to check the disk I/O. The
> > > > > partitioning is one other thing.
> > > > >
> > > > > In the Microsoft SQL 2000 resource kit there is the following article
> > > > > http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/part5/c2061.mspx
> > > > >
> > > > > In this article you will find plenty of information regarding performance in
> > > > > RDBMS
> > > > >
> > > > > HTH
> > > > > Andreas
> > > > >
> > > > > "markus" wrote:
> > > > >
> > > > > > hi!
> > > > > >
> > > > > > we have developed an application with sql server 2000 as database server. we
> > > > > > still have enormous performance problem and so we started to search for
> > > > > > reasons.
> > > > > >
> > > > > > now we made a test were we send a sql query (about 10.000 data records) to
> > > > > > the server and were quite wondering. when we made the test with a lokal pc,
> > > > > > sometimes really old boys, it took some seconds to finish the query. with our
> > > > > > server (dual xenon board, 2,6 mhz, 2 gb mem) it lasts over 3 minutes! when we
> > > > > > check the performance monitor of the server he will not have any ressource
> > > > > > troubles. i really dont know which processor the server is using ;-)
> > > > > >
> > > > > > how could that happen? are there any troubles with dual boards?
> > > > > >
> > > > > > i have also check the processor properties of the server. there are still
> > > > > > two processors listed. the settings should be ok unless i could not change
> > > > > > the settings.
> > > > > > also the disk is ok.
> > > > > >
> > > > > > would be great if someone could give me a hint.
> > > > > >
> > > > > > greetings,
> > > > > > markus
> > > > > >
> > > > > >
> > > > > >
> > > > > >|||Have you looked at the execution plans for both queries on the server and
client? Is there any difference?
Also check the query Analyzer version on the client and the server.
"markus" <markus@.discussions.microsoft.com> wrote in message
news:5F17AC7D-EA4A-400C-A67C-22F578AE016D@.microsoft.com...
> hi!
> we have developed an application with sql server 2000 as database server.
> we
> still have enormous performance problem and so we started to search for
> reasons.
> now we made a test were we send a sql query (about 10.000 data records) to
> the server and were quite wondering. when we made the test with a lokal
> pc,
> sometimes really old boys, it took some seconds to finish the query. with
> our
> server (dual xenon board, 2,6 mhz, 2 gb mem) it lasts over 3 minutes! when
> we
> check the performance monitor of the server he will not have any ressource
> troubles. i really dont know which processor the server is using ;-)
> how could that happen? are there any troubles with dual boards?
> i have also check the processor properties of the server. there are still
> two processors listed. the settings should be ok unless i could not change
> the settings.
> also the disk is ok.
> would be great if someone could give me a hint.
> greetings,
> markus
>
>|||both tests took place on a sql server 2k, just the hardware is really
different ;-)
the query analyzer version is the same.
"Richard Ding" wrote:
> Have you looked at the execution plans for both queries on the server and
> client? Is there any difference?
> Also check the query Analyzer version on the client and the server.
>
> "markus" <markus@.discussions.microsoft.com> wrote in message
> news:5F17AC7D-EA4A-400C-A67C-22F578AE016D@.microsoft.com...
> > hi!
> >
> > we have developed an application with sql server 2000 as database server.
> > we
> > still have enormous performance problem and so we started to search for
> > reasons.
> >
> > now we made a test were we send a sql query (about 10.000 data records) to
> > the server and were quite wondering. when we made the test with a lokal
> > pc,
> > sometimes really old boys, it took some seconds to finish the query. with
> > our
> > server (dual xenon board, 2,6 mhz, 2 gb mem) it lasts over 3 minutes! when
> > we
> > check the performance monitor of the server he will not have any ressource
> > troubles. i really dont know which processor the server is using ;-)
> >
> > how could that happen? are there any troubles with dual boards?
> >
> > i have also check the processor properties of the server. there are still
> > two processors listed. the settings should be ok unless i could not change
> > the settings.
> > also the disk is ok.
> >
> > would be great if someone could give me a hint.
> >
> > greetings,
> > markus
> >
> >
> >
> >
>
>|||Marcus Hi,
Please check this article, and follow it's steps to optimize your query
http://support.microsoft.com/default.aspx?scid=kb;en-us;243589
If you don't have any results, try to disable one of your two processors,
stop-start SQL Services and run the query again. Notice the task manager
performance to see it's activity.
If not, try to create a trace file and capture what your query is doing.
Use this article to analyze your performance data
http://support.microsoft.com/default.aspx?scid=kb;en-us;283886
HTH
Andreas
"markus" wrote:
> both tests took place on a sql server 2k, just the hardware is really
> different ;-)
> the query analyzer version is the same.
>
> "Richard Ding" wrote:
> > Have you looked at the execution plans for both queries on the server and
> > client? Is there any difference?
> >
> > Also check the query Analyzer version on the client and the server.
> >
> >
> > "markus" <markus@.discussions.microsoft.com> wrote in message
> > news:5F17AC7D-EA4A-400C-A67C-22F578AE016D@.microsoft.com...
> > > hi!
> > >
> > > we have developed an application with sql server 2000 as database server.
> > > we
> > > still have enormous performance problem and so we started to search for
> > > reasons.
> > >
> > > now we made a test were we send a sql query (about 10.000 data records) to
> > > the server and were quite wondering. when we made the test with a lokal
> > > pc,
> > > sometimes really old boys, it took some seconds to finish the query. with
> > > our
> > > server (dual xenon board, 2,6 mhz, 2 gb mem) it lasts over 3 minutes! when
> > > we
> > > check the performance monitor of the server he will not have any ressource
> > > troubles. i really dont know which processor the server is using ;-)
> > >
> > > how could that happen? are there any troubles with dual boards?
> > >
> > > i have also check the processor properties of the server. there are still
> > > two processors listed. the settings should be ok unless i could not change
> > > the settings.
> > > also the disk is ok.
> > >
> > > would be great if someone could give me a hint.
> > >
> > > greetings,
> > > markus
> > >
> > >
> > >
> > >
> >
> >
> >|||hi andreas!
thank you for your information. i will optimize the query in the way
described but thats still another topic. today i tried to change the server
properties but therefore i have to restart teh server and that's possible in
the evening hours :-(
i analyzed the server via the profiler and compared the result of the server
and another pc:
reads writes duration
server: 43151 322 228796
"normal pc": 43148 325 333
so far so good. bat how can I identify where the bottleneck really is. are
there special event catagories which concern I/O.
my further activities are:
1) disable von cpu
2) reinstall sql server on existing server
3) install sql server on "stand-alone server"
what conerns issue 2 i will also check if there are existing problems with
an old msde installation.
regards,
markus
"Andreas Mavrogenis" wrote:
> Marcus Hi,
> Please check this article, and follow it's steps to optimize your query
> http://support.microsoft.com/default.aspx?scid=kb;en-us;243589
> If you don't have any results, try to disable one of your two processors,
> stop-start SQL Services and run the query again. Notice the task manager
> performance to see it's activity.
> If not, try to create a trace file and capture what your query is doing.
> Use this article to analyze your performance data
> http://support.microsoft.com/default.aspx?scid=kb;en-us;283886
> HTH
> Andreas
>
>
> "markus" wrote:
> > both tests took place on a sql server 2k, just the hardware is really
> > different ;-)
> >
> > the query analyzer version is the same.
> >
> >
> > "Richard Ding" wrote:
> >
> > > Have you looked at the execution plans for both queries on the server and
> > > client? Is there any difference?
> > >
> > > Also check the query Analyzer version on the client and the server.
> > >
> > >
> > > "markus" <markus@.discussions.microsoft.com> wrote in message
> > > news:5F17AC7D-EA4A-400C-A67C-22F578AE016D@.microsoft.com...
> > > > hi!
> > > >
> > > > we have developed an application with sql server 2000 as database server.
> > > > we
> > > > still have enormous performance problem and so we started to search for
> > > > reasons.
> > > >
> > > > now we made a test were we send a sql query (about 10.000 data records) to
> > > > the server and were quite wondering. when we made the test with a lokal
> > > > pc,
> > > > sometimes really old boys, it took some seconds to finish the query. with
> > > > our
> > > > server (dual xenon board, 2,6 mhz, 2 gb mem) it lasts over 3 minutes! when
> > > > we
> > > > check the performance monitor of the server he will not have any ressource
> > > > troubles. i really dont know which processor the server is using ;-)
> > > >
> > > > how could that happen? are there any troubles with dual boards?
> > > >
> > > > i have also check the processor properties of the server. there are still
> > > > two processors listed. the settings should be ok unless i could not change
> > > > the settings.
> > > > also the disk is ok.
> > > >
> > > > would be great if someone could give me a hint.
> > > >
> > > > greetings,
> > > > markus
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> > >|||As I said before, try comparing the query execution plans probably will
unveil the huge performance difference.
"markus" <markus@.discussions.microsoft.com> wrote in message
news:E64A2A20-2258-4EC8-B557-527549F57272@.microsoft.com...
> hi andreas!
> thank you for your information. i will optimize the query in the way
> described but thats still another topic. today i tried to change the
> server
> properties but therefore i have to restart teh server and that's possible
> in
> the evening hours :-(
> i analyzed the server via the profiler and compared the result of the
> server
> and another pc:
> reads writes duration
> server: 43151 322 228796
> "normal pc": 43148 325 333
>
> so far so good. bat how can I identify where the bottleneck really is. are
> there special event catagories which concern I/O.
> my further activities are:
> 1) disable von cpu
> 2) reinstall sql server on existing server
> 3) install sql server on "stand-alone server"
> what conerns issue 2 i will also check if there are existing problems with
> an old msde installation.
> regards,
> markus
> "Andreas Mavrogenis" wrote:
>> Marcus Hi,
>> Please check this article, and follow it's steps to optimize your query
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;243589
>> If you don't have any results, try to disable one of your two processors,
>> stop-start SQL Services and run the query again. Notice the task manager
>> performance to see it's activity.
>> If not, try to create a trace file and capture what your query is doing.
>> Use this article to analyze your performance data
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;283886
>> HTH
>> Andreas
>>
>>
>> "markus" wrote:
>> > both tests took place on a sql server 2k, just the hardware is really
>> > different ;-)
>> >
>> > the query analyzer version is the same.
>> >
>> >
>> > "Richard Ding" wrote:
>> >
>> > > Have you looked at the execution plans for both queries on the server
>> > > and
>> > > client? Is there any difference?
>> > >
>> > > Also check the query Analyzer version on the client and the server.
>> > >
>> > >
>> > > "markus" <markus@.discussions.microsoft.com> wrote in message
>> > > news:5F17AC7D-EA4A-400C-A67C-22F578AE016D@.microsoft.com...
>> > > > hi!
>> > > >
>> > > > we have developed an application with sql server 2000 as database
>> > > > server.
>> > > > we
>> > > > still have enormous performance problem and so we started to search
>> > > > for
>> > > > reasons.
>> > > >
>> > > > now we made a test were we send a sql query (about 10.000 data
>> > > > records) to
>> > > > the server and were quite wondering. when we made the test with a
>> > > > lokal
>> > > > pc,
>> > > > sometimes really old boys, it took some seconds to finish the
>> > > > query. with
>> > > > our
>> > > > server (dual xenon board, 2,6 mhz, 2 gb mem) it lasts over 3
>> > > > minutes! when
>> > > > we
>> > > > check the performance monitor of the server he will not have any
>> > > > ressource
>> > > > troubles. i really dont know which processor the server is using
>> > > > ;-)
>> > > >
>> > > > how could that happen? are there any troubles with dual boards?
>> > > >
>> > > > i have also check the processor properties of the server. there are
>> > > > still
>> > > > two processors listed. the settings should be ok unless i could not
>> > > > change
>> > > > the settings.
>> > > > also the disk is ok.
>> > > >
>> > > > would be great if someone could give me a hint.
>> > > >
>> > > > greetings,
>> > > > markus
>> > > >
>> > > >
>> > > >
>> > > >
>> > >
>> > >
>> > >|||Hi,
Check the following article from technet, in order to specify your counters
for logging. Try to monitor also the Disk cashe.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops5.mspx
It's an article that requires a lot of time reading.
Let me Know of your status,
HTH
Andreas
"Richard Ding" wrote:
> As I said before, try comparing the query execution plans probably will
> unveil the huge performance difference.
> "markus" <markus@.discussions.microsoft.com> wrote in message
> news:E64A2A20-2258-4EC8-B557-527549F57272@.microsoft.com...
> > hi andreas!
> >
> > thank you for your information. i will optimize the query in the way
> > described but thats still another topic. today i tried to change the
> > server
> > properties but therefore i have to restart teh server and that's possible
> > in
> > the evening hours :-(
> >
> > i analyzed the server via the profiler and compared the result of the
> > server
> > and another pc:
> >
> > reads writes duration
> > server: 43151 322 228796
> > "normal pc": 43148 325 333
> >
> >
> > so far so good. bat how can I identify where the bottleneck really is. are
> > there special event catagories which concern I/O.
> >
> > my further activities are:
> >
> > 1) disable von cpu
> > 2) reinstall sql server on existing server
> > 3) install sql server on "stand-alone server"
> >
> > what conerns issue 2 i will also check if there are existing problems with
> > an old msde installation.
> >
> > regards,
> > markus
> >
> > "Andreas Mavrogenis" wrote:
> >
> >> Marcus Hi,
> >>
> >> Please check this article, and follow it's steps to optimize your query
> >> http://support.microsoft.com/default.aspx?scid=kb;en-us;243589
> >>
> >> If you don't have any results, try to disable one of your two processors,
> >> stop-start SQL Services and run the query again. Notice the task manager
> >> performance to see it's activity.
> >>
> >> If not, try to create a trace file and capture what your query is doing.
> >> Use this article to analyze your performance data
> >> http://support.microsoft.com/default.aspx?scid=kb;en-us;283886
> >>
> >> HTH
> >> Andreas
> >>
> >>
> >>
> >>
> >> "markus" wrote:
> >>
> >> > both tests took place on a sql server 2k, just the hardware is really
> >> > different ;-)
> >> >
> >> > the query analyzer version is the same.
> >> >
> >> >
> >> > "Richard Ding" wrote:
> >> >
> >> > > Have you looked at the execution plans for both queries on the server
> >> > > and
> >> > > client? Is there any difference?
> >> > >
> >> > > Also check the query Analyzer version on the client and the server.
> >> > >
> >> > >
> >> > > "markus" <markus@.discussions.microsoft.com> wrote in message
> >> > > news:5F17AC7D-EA4A-400C-A67C-22F578AE016D@.microsoft.com...
> >> > > > hi!
> >> > > >
> >> > > > we have developed an application with sql server 2000 as database
> >> > > > server.
> >> > > > we
> >> > > > still have enormous performance problem and so we started to search
> >> > > > for
> >> > > > reasons.
> >> > > >
> >> > > > now we made a test were we send a sql query (about 10.000 data
> >> > > > records) to
> >> > > > the server and were quite wondering. when we made the test with a
> >> > > > lokal
> >> > > > pc,
> >> > > > sometimes really old boys, it took some seconds to finish the
> >> > > > query. with
> >> > > > our
> >> > > > server (dual xenon board, 2,6 mhz, 2 gb mem) it lasts over 3
> >> > > > minutes! when
> >> > > > we
> >> > > > check the performance monitor of the server he will not have any
> >> > > > ressource
> >> > > > troubles. i really dont know which processor the server is using
> >> > > > ;-)
> >> > > >
> >> > > > how could that happen? are there any troubles with dual boards?
> >> > > >
> >> > > > i have also check the processor properties of the server. there are
> >> > > > still
> >> > > > two processors listed. the settings should be ok unless i could not
> >> > > > change
> >> > > > the settings.
> >> > > > also the disk is ok.
> >> > > >
> >> > > > would be great if someone could give me a hint.
> >> > > >
> >> > > > greetings,
> >> > > > markus
> >> > > >
> >> > > >
> >> > > >
> >> > > >
> >> > >
> >> > >
> >> > >
>
>