I'm suspecting our new Dell 2850/ SQL Server 2005 machine is not performing
like it should. What we expericence is ok performance running single long
running queries. But running 2-3 long running queries at the same time
really makes the whole server very slow. Imagine this:
1) Start 1 long running query accessing 2 large tables. CPU is used 25-30%
2) At the same time, run a select on a small table not involved in the query
above: Sometimes the result is coming after 15-30 secs. Sometimes it will
not display until the first query is done. There is no lock-situation
involved.
I have done the same thing on SQL Server 2000 with slower hardware for
several years. Sadly I don't have access to the old machine anymore, so it's
hard to tell the real difference.
CPU is not an issue - SQL server is using only 25-50 percent. So my first
conclusion is that the disk-system is not performing well. We separate RAIDs
for system (Raid1), tempdb (raid1), log (raid1), data (raid5), index
(raid1). The raids are spread on 2 controllers with 2 channels each.
But where to start? Does anyone have some stored procedures+testdata which I
can use as a start to compare with?
Per SchjetneOne place I'd start is to look at the disk-related perfmon counter values. In
particular, I'd look at Disk Sec/Read and Disk sec/Write; these are disk
latency counters. If there is a significantly higher I/O latency during the
period you are running your queries concurrently as compared to when you are
not running them at the same time, you may have a disk I/O issue.
To benchmark the disk subsystem, I have found sqlio.exe from Microsoft to be
very useful. Every time when you get a new disk system, run sqlio.exe to get
a comprehensive performance profile of the disk system. This establishes a
baseline for you. Later, if you suspect something is not behaving properly in
your disk subsystem, you can run the same benchmark and compare the results
with the saved baseline.
Linchi
"Per Schjetne" wrote:
> I'm suspecting our new Dell 2850/ SQL Server 2005 machine is not performing
> like it should. What we expericence is ok performance running single long
> running queries. But running 2-3 long running queries at the same time
> really makes the whole server very slow. Imagine this:
> 1) Start 1 long running query accessing 2 large tables. CPU is used 25-30%
> 2) At the same time, run a select on a small table not involved in the query
> above: Sometimes the result is coming after 15-30 secs. Sometimes it will
> not display until the first query is done. There is no lock-situation
> involved.
> I have done the same thing on SQL Server 2000 with slower hardware for
> several years. Sadly I don't have access to the old machine anymore, so it's
> hard to tell the real difference.
> CPU is not an issue - SQL server is using only 25-50 percent. So my first
> conclusion is that the disk-system is not performing well. We separate RAIDs
> for system (Raid1), tempdb (raid1), log (raid1), data (raid5), index
> (raid1). The raids are spread on 2 controllers with 2 channels each.
> But where to start? Does anyone have some stored procedures+testdata which I
> can use as a start to compare with?
> Per Schjetne
>
>|||Thanks a lot for the tip! I'll try it.
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:92E26405-BF1A-4BBD-9E80-BED6D7CA246E@.microsoft.com...
> One place I'd start is to look at the disk-related perfmon counter values.
> In
> particular, I'd look at Disk Sec/Read and Disk sec/Write; these are disk
> latency counters. If there is a significantly higher I/O latency during
> the
> period you are running your queries concurrently as compared to when you
> are
> not running them at the same time, you may have a disk I/O issue.
> To benchmark the disk subsystem, I have found sqlio.exe from Microsoft to
> be
> very useful. Every time when you get a new disk system, run sqlio.exe to
> get
> a comprehensive performance profile of the disk system. This establishes a
> baseline for you. Later, if you suspect something is not behaving properly
> in
> your disk subsystem, you can run the same benchmark and compare the
> results
> with the saved baseline.
> Linchi
> "Per Schjetne" wrote:
>> I'm suspecting our new Dell 2850/ SQL Server 2005 machine is not
>> performing
>> like it should. What we expericence is ok performance running single long
>> running queries. But running 2-3 long running queries at the same time
>> really makes the whole server very slow. Imagine this:
>> 1) Start 1 long running query accessing 2 large tables. CPU is used
>> 25-30%
>> 2) At the same time, run a select on a small table not involved in the
>> query
>> above: Sometimes the result is coming after 15-30 secs. Sometimes it will
>> not display until the first query is done. There is no lock-situation
>> involved.
>> I have done the same thing on SQL Server 2000 with slower hardware for
>> several years. Sadly I don't have access to the old machine anymore, so
>> it's
>> hard to tell the real difference.
>> CPU is not an issue - SQL server is using only 25-50 percent. So my first
>> conclusion is that the disk-system is not performing well. We separate
>> RAIDs
>> for system (Raid1), tempdb (raid1), log (raid1), data (raid5), index
>> (raid1). The raids are spread on 2 controllers with 2 channels each.
>> But where to start? Does anyone have some stored procedures+testdata
>> which I
>> can use as a start to compare with?
>> Per Schjetne
>>
Showing posts with label machine. Show all posts
Showing posts with label machine. Show all posts
Wednesday, March 28, 2012
Performance testing - tips
I'm suspecting our new Dell 2850/ SQL Server 2005 machine is not performing
like it should. What we expericence is ok performance running single long
running queries. But running 2-3 long running queries at the same time
really makes the whole server very slow. Imagine this:
1) Start 1 long running query accessing 2 large tables. CPU is used 25-30%
2) At the same time, run a select on a small table not involved in the query
above: Sometimes the result is coming after 15-30 secs. Sometimes it will
not display until the first query is done. There is no lock-situation
involved.
I have done the same thing on SQL Server 2000 with slower hardware for
several years. Sadly I don't have access to the old machine anymore, so it's
hard to tell the real difference.
CPU is not an issue - SQL server is using only 25-50 percent. So my first
conclusion is that the disk-system is not performing well. We separate RAIDs
for system (Raid1), tempdb (raid1), log (raid1), data (raid5), index
(raid1). The raids are spread on 2 controllers with 2 channels each.
But where to start? Does anyone have some stored procedures+testdata which I
can use as a start to compare with?
Per SchjetneOne place I'd start is to look at the disk-related perfmon counter values. I
n
particular, I'd look at Disk Sec/Read and Disk sec/Write; these are disk
latency counters. If there is a significantly higher I/O latency during the
period you are running your queries concurrently as compared to when you are
not running them at the same time, you may have a disk I/O issue.
To benchmark the disk subsystem, I have found sqlio.exe from Microsoft to be
very useful. Every time when you get a new disk system, run sqlio.exe to get
a comprehensive performance profile of the disk system. This establishes a
baseline for you. Later, if you suspect something is not behaving properly i
n
your disk subsystem, you can run the same benchmark and compare the results
with the saved baseline.
Linchi
"Per Schjetne" wrote:
> I'm suspecting our new Dell 2850/ SQL Server 2005 machine is not performin
g
> like it should. What we expericence is ok performance running single long
> running queries. But running 2-3 long running queries at the same time
> really makes the whole server very slow. Imagine this:
> 1) Start 1 long running query accessing 2 large tables. CPU is used 25-30%
> 2) At the same time, run a select on a small table not involved in the que
ry
> above: Sometimes the result is coming after 15-30 secs. Sometimes it will
> not display until the first query is done. There is no lock-situation
> involved.
> I have done the same thing on SQL Server 2000 with slower hardware for
> several years. Sadly I don't have access to the old machine anymore, so it
's
> hard to tell the real difference.
> CPU is not an issue - SQL server is using only 25-50 percent. So my first
> conclusion is that the disk-system is not performing well. We separate RAI
Ds
> for system (Raid1), tempdb (raid1), log (raid1), data (raid5), index
> (raid1). The raids are spread on 2 controllers with 2 channels each.
> But where to start? Does anyone have some stored procedures+testdata which
I
> can use as a start to compare with?
> Per Schjetne
>
>|||Thanks a lot for the tip! I'll try it.
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:92E26405-BF1A-4BBD-9E80-BED6D7CA246E@.microsoft.com...[vbcol=seagreen]
> One place I'd start is to look at the disk-related perfmon counter values.
> In
> particular, I'd look at Disk Sec/Read and Disk sec/Write; these are disk
> latency counters. If there is a significantly higher I/O latency during
> the
> period you are running your queries concurrently as compared to when you
> are
> not running them at the same time, you may have a disk I/O issue.
> To benchmark the disk subsystem, I have found sqlio.exe from Microsoft to
> be
> very useful. Every time when you get a new disk system, run sqlio.exe to
> get
> a comprehensive performance profile of the disk system. This establishes a
> baseline for you. Later, if you suspect something is not behaving properly
> in
> your disk subsystem, you can run the same benchmark and compare the
> results
> with the saved baseline.
> Linchi
> "Per Schjetne" wrote:
>
like it should. What we expericence is ok performance running single long
running queries. But running 2-3 long running queries at the same time
really makes the whole server very slow. Imagine this:
1) Start 1 long running query accessing 2 large tables. CPU is used 25-30%
2) At the same time, run a select on a small table not involved in the query
above: Sometimes the result is coming after 15-30 secs. Sometimes it will
not display until the first query is done. There is no lock-situation
involved.
I have done the same thing on SQL Server 2000 with slower hardware for
several years. Sadly I don't have access to the old machine anymore, so it's
hard to tell the real difference.
CPU is not an issue - SQL server is using only 25-50 percent. So my first
conclusion is that the disk-system is not performing well. We separate RAIDs
for system (Raid1), tempdb (raid1), log (raid1), data (raid5), index
(raid1). The raids are spread on 2 controllers with 2 channels each.
But where to start? Does anyone have some stored procedures+testdata which I
can use as a start to compare with?
Per SchjetneOne place I'd start is to look at the disk-related perfmon counter values. I
n
particular, I'd look at Disk Sec/Read and Disk sec/Write; these are disk
latency counters. If there is a significantly higher I/O latency during the
period you are running your queries concurrently as compared to when you are
not running them at the same time, you may have a disk I/O issue.
To benchmark the disk subsystem, I have found sqlio.exe from Microsoft to be
very useful. Every time when you get a new disk system, run sqlio.exe to get
a comprehensive performance profile of the disk system. This establishes a
baseline for you. Later, if you suspect something is not behaving properly i
n
your disk subsystem, you can run the same benchmark and compare the results
with the saved baseline.
Linchi
"Per Schjetne" wrote:
> I'm suspecting our new Dell 2850/ SQL Server 2005 machine is not performin
g
> like it should. What we expericence is ok performance running single long
> running queries. But running 2-3 long running queries at the same time
> really makes the whole server very slow. Imagine this:
> 1) Start 1 long running query accessing 2 large tables. CPU is used 25-30%
> 2) At the same time, run a select on a small table not involved in the que
ry
> above: Sometimes the result is coming after 15-30 secs. Sometimes it will
> not display until the first query is done. There is no lock-situation
> involved.
> I have done the same thing on SQL Server 2000 with slower hardware for
> several years. Sadly I don't have access to the old machine anymore, so it
's
> hard to tell the real difference.
> CPU is not an issue - SQL server is using only 25-50 percent. So my first
> conclusion is that the disk-system is not performing well. We separate RAI
Ds
> for system (Raid1), tempdb (raid1), log (raid1), data (raid5), index
> (raid1). The raids are spread on 2 controllers with 2 channels each.
> But where to start? Does anyone have some stored procedures+testdata which
I
> can use as a start to compare with?
> Per Schjetne
>
>|||Thanks a lot for the tip! I'll try it.
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:92E26405-BF1A-4BBD-9E80-BED6D7CA246E@.microsoft.com...[vbcol=seagreen]
> One place I'd start is to look at the disk-related perfmon counter values.
> In
> particular, I'd look at Disk Sec/Read and Disk sec/Write; these are disk
> latency counters. If there is a significantly higher I/O latency during
> the
> period you are running your queries concurrently as compared to when you
> are
> not running them at the same time, you may have a disk I/O issue.
> To benchmark the disk subsystem, I have found sqlio.exe from Microsoft to
> be
> very useful. Every time when you get a new disk system, run sqlio.exe to
> get
> a comprehensive performance profile of the disk system. This establishes a
> baseline for you. Later, if you suspect something is not behaving properly
> in
> your disk subsystem, you can run the same benchmark and compare the
> results
> with the saved baseline.
> Linchi
> "Per Schjetne" wrote:
>
Friday, March 23, 2012
Performance puzzle
We have a new Compaq Proliant DL760 server running
Windows 2003 that we bought to replace a Dell 8450
running Windows 2000
The new machine has 4 2.5 Ghz processors with hyper-
threading (when you open Task Mgr it looks like it
has 8 processors)
The old machine has 8 P3 700 Mhz processors.
Both machines have direct attached fiber channel
arrays configured identically except the new one uses
72 Gig hard drives and the old one has 36 Gig drives.
I am using 9 drives in Raid5 for data and 4 drives in Raid
1+0 for logs on both machines. Both arrays have 15000 RPM
drives.
Both machines have SQL Server 2000. My test database is
about 4 Gig and it is identical on both servers.
I have a stress test script with a variety of operations
such as bulk insert, create clustered index, calculations,
etc.
As you would expect, the new machine runs processor
intensive operations much faster. But the old machine runs
disk intensive operations faster than the new one. We had
expected at least comparable performance.
Overall, the test runs in 39 minutes on the old machine
and 40 minutes on the new one (pretty close, I know, but
the new one should win by a larger margin).
Is there anything special about running SQL Server 2000 on
Windows 2003 that we need to know?
Does the Compaq drive array have some limitation that the
Dell does not?
Thanks
Dave GDave,
don't know whether you have considered the following two variants:
1. seek time. it is a specification of the hard drive, and though not much,
different drives can have different seek times. With the rotation speed on
the higher end, the seek times' difference plays a larger role
2. sorting mechanism of the RAID controllers: some controllers support
elevator sorting that is clearly at advantage than without.
hth
Quentin
"DaveG" <anonymous@.discussions.microsoft.com> wrote in message
news:02c701c3dae2$a256fde0$a401280a@.phx.gbl...
> We have a new Compaq Proliant DL760 server running
> Windows 2003 that we bought to replace a Dell 8450
> running Windows 2000
> The new machine has 4 2.5 Ghz processors with hyper-
> threading (when you open Task Mgr it looks like it
> has 8 processors)
> The old machine has 8 P3 700 Mhz processors.
> Both machines have direct attached fiber channel
> arrays configured identically except the new one uses
> 72 Gig hard drives and the old one has 36 Gig drives.
> I am using 9 drives in Raid5 for data and 4 drives in Raid
> 1+0 for logs on both machines. Both arrays have 15000 RPM
> drives.
> Both machines have SQL Server 2000. My test database is
> about 4 Gig and it is identical on both servers.
> I have a stress test script with a variety of operations
> such as bulk insert, create clustered index, calculations,
> etc.
> As you would expect, the new machine runs processor
> intensive operations much faster. But the old machine runs
> disk intensive operations faster than the new one. We had
> expected at least comparable performance.
> Overall, the test runs in 39 minutes on the old machine
> and 40 minutes on the new one (pretty close, I know, but
> the new one should win by a larger margin).
> Is there anything special about running SQL Server 2000 on
> Windows 2003 that we need to know?
> Does the Compaq drive array have some limitation that the
> Dell does not?
> Thanks
> Dave G
>|||Thanks, I'll see if I can find the specs.
DG
>--Original Message--
>Dave,
>don't know whether you have considered the following two
variants:
>1. seek time. it is a specification of the hard drive,
and though not much,
>different drives can have different seek times. With the
rotation speed on
>the higher end, the seek times' difference plays a larger
role
>2. sorting mechanism of the RAID controllers: some
controllers support
>elevator sorting that is clearly at advantage than
without.
>hth
>Quentin
>
>"DaveG" <anonymous@.discussions.microsoft.com> wrote in
message
>news:02c701c3dae2$a256fde0$a401280a@.phx.gbl...
>> We have a new Compaq Proliant DL760 server running
>> Windows 2003 that we bought to replace a Dell 8450
>> running Windows 2000
>> The new machine has 4 2.5 Ghz processors with hyper-
>> threading (when you open Task Mgr it looks like it
>> has 8 processors)
>> The old machine has 8 P3 700 Mhz processors.
>> Both machines have direct attached fiber channel
>> arrays configured identically except the new one uses
>> 72 Gig hard drives and the old one has 36 Gig drives.
>> I am using 9 drives in Raid5 for data and 4 drives in
Raid
>> 1+0 for logs on both machines. Both arrays have 15000
RPM
>> drives.
>> Both machines have SQL Server 2000. My test database is
>> about 4 Gig and it is identical on both servers.
>> I have a stress test script with a variety of operations
>> such as bulk insert, create clustered index,
calculations,
>> etc.
>> As you would expect, the new machine runs processor
>> intensive operations much faster. But the old machine
runs
>> disk intensive operations faster than the new one. We
had
>> expected at least comparable performance.
>> Overall, the test runs in 39 minutes on the old machine
>> and 40 minutes on the new one (pretty close, I know, but
>> the new one should win by a larger margin).
>> Is there anything special about running SQL Server 2000
on
>> Windows 2003 that we need to know?
>> Does the Compaq drive array have some limitation that
the
>> Dell does not?
>> Thanks
>> Dave G
>>
>
>.
>|||You don't mention which drive arrays you have but from what I've seen of our
Compaq RA8000's there is more to the configuration than just the RAID level.
So it might still be something between the way the arrays are configured. If
you're familiar with IOMeter from Intel you might want to use that to test
the disk systems performance.
Also, from the tests I've seen hyperthreading isn't the same as having more
full processors, so maybe the difference in processor count plays a larger
part than expected. It may be possible that hyperthreading is actually
causing worse performance, you might want to test disabling it.
Mike Kruchten
"DaveG" <anonymous@.discussions.microsoft.com> wrote in message
news:02c701c3dae2$a256fde0$a401280a@.phx.gbl...
> We have a new Compaq Proliant DL760 server running
> Windows 2003 that we bought to replace a Dell 8450
> running Windows 2000
> The new machine has 4 2.5 Ghz processors with hyper-
> threading (when you open Task Mgr it looks like it
> has 8 processors)
> The old machine has 8 P3 700 Mhz processors.
> Both machines have direct attached fiber channel
> arrays configured identically except the new one uses
> 72 Gig hard drives and the old one has 36 Gig drives.
> I am using 9 drives in Raid5 for data and 4 drives in Raid
> 1+0 for logs on both machines. Both arrays have 15000 RPM
> drives.
> Both machines have SQL Server 2000. My test database is
> about 4 Gig and it is identical on both servers.
> I have a stress test script with a variety of operations
> such as bulk insert, create clustered index, calculations,
> etc.
> As you would expect, the new machine runs processor
> intensive operations much faster. But the old machine runs
> disk intensive operations faster than the new one. We had
> expected at least comparable performance.
> Overall, the test runs in 39 minutes on the old machine
> and 40 minutes on the new one (pretty close, I know, but
> the new one should win by a larger margin).
> Is there anything special about running SQL Server 2000 on
> Windows 2003 that we need to know?
> Does the Compaq drive array have some limitation that the
> Dell does not?
> Thanks
> Dave G
>|||> Also, from the tests I've seen hyperthreading isn't the same as having
more
> full processors, so maybe the difference in processor count plays a larger
> part than expected. It may be possible that hyperthreading is actually
> causing worse performance, you might want to test disabling it.
Absolutely right.
> "DaveG" <anonymous@.discussions.microsoft.com> wrote in message
> news:02c701c3dae2$a256fde0$a401280a@.phx.gbl...
> > We have a new Compaq Proliant DL760 server running
> > Windows 2003 that we bought to replace a Dell 8450
> > running Windows 2000
> > The new machine has 4 2.5 Ghz processors with hyper-
> > threading (when you open Task Mgr it looks like it
> > has 8 processors)
> > The old machine has 8 P3 700 Mhz processors.
> > Both machines have direct attached fiber channel
> > arrays configured identically except the new one uses
> > 72 Gig hard drives and the old one has 36 Gig drives.
> > I am using 9 drives in Raid5 for data and 4 drives in Raid
> > 1+0 for logs on both machines. Both arrays have 15000 RPM
> > drives.
> >
> > Both machines have SQL Server 2000. My test database is
> > about 4 Gig and it is identical on both servers.
> >
> > I have a stress test script with a variety of operations
> > such as bulk insert, create clustered index, calculations,
> > etc.
> >
> > As you would expect, the new machine runs processor
> > intensive operations much faster. But the old machine runs
> > disk intensive operations faster than the new one. We had
> > expected at least comparable performance.
> >
> > Overall, the test runs in 39 minutes on the old machine
> > and 40 minutes on the new one (pretty close, I know, but
> > the new one should win by a larger margin).
> >
> > Is there anything special about running SQL Server 2000 on
> > Windows 2003 that we need to know?
> > Does the Compaq drive array have some limitation that the
> > Dell does not?
> >
> > Thanks
> > Dave G
> >
> >
>sql
Windows 2003 that we bought to replace a Dell 8450
running Windows 2000
The new machine has 4 2.5 Ghz processors with hyper-
threading (when you open Task Mgr it looks like it
has 8 processors)
The old machine has 8 P3 700 Mhz processors.
Both machines have direct attached fiber channel
arrays configured identically except the new one uses
72 Gig hard drives and the old one has 36 Gig drives.
I am using 9 drives in Raid5 for data and 4 drives in Raid
1+0 for logs on both machines. Both arrays have 15000 RPM
drives.
Both machines have SQL Server 2000. My test database is
about 4 Gig and it is identical on both servers.
I have a stress test script with a variety of operations
such as bulk insert, create clustered index, calculations,
etc.
As you would expect, the new machine runs processor
intensive operations much faster. But the old machine runs
disk intensive operations faster than the new one. We had
expected at least comparable performance.
Overall, the test runs in 39 minutes on the old machine
and 40 minutes on the new one (pretty close, I know, but
the new one should win by a larger margin).
Is there anything special about running SQL Server 2000 on
Windows 2003 that we need to know?
Does the Compaq drive array have some limitation that the
Dell does not?
Thanks
Dave GDave,
don't know whether you have considered the following two variants:
1. seek time. it is a specification of the hard drive, and though not much,
different drives can have different seek times. With the rotation speed on
the higher end, the seek times' difference plays a larger role
2. sorting mechanism of the RAID controllers: some controllers support
elevator sorting that is clearly at advantage than without.
hth
Quentin
"DaveG" <anonymous@.discussions.microsoft.com> wrote in message
news:02c701c3dae2$a256fde0$a401280a@.phx.gbl...
> We have a new Compaq Proliant DL760 server running
> Windows 2003 that we bought to replace a Dell 8450
> running Windows 2000
> The new machine has 4 2.5 Ghz processors with hyper-
> threading (when you open Task Mgr it looks like it
> has 8 processors)
> The old machine has 8 P3 700 Mhz processors.
> Both machines have direct attached fiber channel
> arrays configured identically except the new one uses
> 72 Gig hard drives and the old one has 36 Gig drives.
> I am using 9 drives in Raid5 for data and 4 drives in Raid
> 1+0 for logs on both machines. Both arrays have 15000 RPM
> drives.
> Both machines have SQL Server 2000. My test database is
> about 4 Gig and it is identical on both servers.
> I have a stress test script with a variety of operations
> such as bulk insert, create clustered index, calculations,
> etc.
> As you would expect, the new machine runs processor
> intensive operations much faster. But the old machine runs
> disk intensive operations faster than the new one. We had
> expected at least comparable performance.
> Overall, the test runs in 39 minutes on the old machine
> and 40 minutes on the new one (pretty close, I know, but
> the new one should win by a larger margin).
> Is there anything special about running SQL Server 2000 on
> Windows 2003 that we need to know?
> Does the Compaq drive array have some limitation that the
> Dell does not?
> Thanks
> Dave G
>|||Thanks, I'll see if I can find the specs.
DG
>--Original Message--
>Dave,
>don't know whether you have considered the following two
variants:
>1. seek time. it is a specification of the hard drive,
and though not much,
>different drives can have different seek times. With the
rotation speed on
>the higher end, the seek times' difference plays a larger
role
>2. sorting mechanism of the RAID controllers: some
controllers support
>elevator sorting that is clearly at advantage than
without.
>hth
>Quentin
>
>"DaveG" <anonymous@.discussions.microsoft.com> wrote in
message
>news:02c701c3dae2$a256fde0$a401280a@.phx.gbl...
>> We have a new Compaq Proliant DL760 server running
>> Windows 2003 that we bought to replace a Dell 8450
>> running Windows 2000
>> The new machine has 4 2.5 Ghz processors with hyper-
>> threading (when you open Task Mgr it looks like it
>> has 8 processors)
>> The old machine has 8 P3 700 Mhz processors.
>> Both machines have direct attached fiber channel
>> arrays configured identically except the new one uses
>> 72 Gig hard drives and the old one has 36 Gig drives.
>> I am using 9 drives in Raid5 for data and 4 drives in
Raid
>> 1+0 for logs on both machines. Both arrays have 15000
RPM
>> drives.
>> Both machines have SQL Server 2000. My test database is
>> about 4 Gig and it is identical on both servers.
>> I have a stress test script with a variety of operations
>> such as bulk insert, create clustered index,
calculations,
>> etc.
>> As you would expect, the new machine runs processor
>> intensive operations much faster. But the old machine
runs
>> disk intensive operations faster than the new one. We
had
>> expected at least comparable performance.
>> Overall, the test runs in 39 minutes on the old machine
>> and 40 minutes on the new one (pretty close, I know, but
>> the new one should win by a larger margin).
>> Is there anything special about running SQL Server 2000
on
>> Windows 2003 that we need to know?
>> Does the Compaq drive array have some limitation that
the
>> Dell does not?
>> Thanks
>> Dave G
>>
>
>.
>|||You don't mention which drive arrays you have but from what I've seen of our
Compaq RA8000's there is more to the configuration than just the RAID level.
So it might still be something between the way the arrays are configured. If
you're familiar with IOMeter from Intel you might want to use that to test
the disk systems performance.
Also, from the tests I've seen hyperthreading isn't the same as having more
full processors, so maybe the difference in processor count plays a larger
part than expected. It may be possible that hyperthreading is actually
causing worse performance, you might want to test disabling it.
Mike Kruchten
"DaveG" <anonymous@.discussions.microsoft.com> wrote in message
news:02c701c3dae2$a256fde0$a401280a@.phx.gbl...
> We have a new Compaq Proliant DL760 server running
> Windows 2003 that we bought to replace a Dell 8450
> running Windows 2000
> The new machine has 4 2.5 Ghz processors with hyper-
> threading (when you open Task Mgr it looks like it
> has 8 processors)
> The old machine has 8 P3 700 Mhz processors.
> Both machines have direct attached fiber channel
> arrays configured identically except the new one uses
> 72 Gig hard drives and the old one has 36 Gig drives.
> I am using 9 drives in Raid5 for data and 4 drives in Raid
> 1+0 for logs on both machines. Both arrays have 15000 RPM
> drives.
> Both machines have SQL Server 2000. My test database is
> about 4 Gig and it is identical on both servers.
> I have a stress test script with a variety of operations
> such as bulk insert, create clustered index, calculations,
> etc.
> As you would expect, the new machine runs processor
> intensive operations much faster. But the old machine runs
> disk intensive operations faster than the new one. We had
> expected at least comparable performance.
> Overall, the test runs in 39 minutes on the old machine
> and 40 minutes on the new one (pretty close, I know, but
> the new one should win by a larger margin).
> Is there anything special about running SQL Server 2000 on
> Windows 2003 that we need to know?
> Does the Compaq drive array have some limitation that the
> Dell does not?
> Thanks
> Dave G
>|||> Also, from the tests I've seen hyperthreading isn't the same as having
more
> full processors, so maybe the difference in processor count plays a larger
> part than expected. It may be possible that hyperthreading is actually
> causing worse performance, you might want to test disabling it.
Absolutely right.
> "DaveG" <anonymous@.discussions.microsoft.com> wrote in message
> news:02c701c3dae2$a256fde0$a401280a@.phx.gbl...
> > We have a new Compaq Proliant DL760 server running
> > Windows 2003 that we bought to replace a Dell 8450
> > running Windows 2000
> > The new machine has 4 2.5 Ghz processors with hyper-
> > threading (when you open Task Mgr it looks like it
> > has 8 processors)
> > The old machine has 8 P3 700 Mhz processors.
> > Both machines have direct attached fiber channel
> > arrays configured identically except the new one uses
> > 72 Gig hard drives and the old one has 36 Gig drives.
> > I am using 9 drives in Raid5 for data and 4 drives in Raid
> > 1+0 for logs on both machines. Both arrays have 15000 RPM
> > drives.
> >
> > Both machines have SQL Server 2000. My test database is
> > about 4 Gig and it is identical on both servers.
> >
> > I have a stress test script with a variety of operations
> > such as bulk insert, create clustered index, calculations,
> > etc.
> >
> > As you would expect, the new machine runs processor
> > intensive operations much faster. But the old machine runs
> > disk intensive operations faster than the new one. We had
> > expected at least comparable performance.
> >
> > Overall, the test runs in 39 minutes on the old machine
> > and 40 minutes on the new one (pretty close, I know, but
> > the new one should win by a larger margin).
> >
> > Is there anything special about running SQL Server 2000 on
> > Windows 2003 that we need to know?
> > Does the Compaq drive array have some limitation that the
> > Dell does not?
> >
> > Thanks
> > Dave G
> >
> >
>sql
Performance puzzle
We have a new Compaq Proliant DL760 server running
Windows 2003 that we bought to replace a Dell 8450
running Windows 2000
The new machine has 4 2.5 Ghz processors with hyper-
threading (when you open Task Mgr it looks like it
has 8 processors)
The old machine has 8 P3 700 Mhz processors.
Both machines have direct attached fiber channel
arrays configured identically except the new one uses
72 Gig hard drives and the old one has 36 Gig drives.
I am using 9 drives in Raid5 for data and 4 drives in Raid
1+0 for logs on both machines. Both arrays have 15000 RPM
drives.
Both machines have SQL Server 2000. My test database is
about 4 Gig and it is identical on both servers.
I have a stress test script with a variety of operations
such as bulk insert, create clustered index, calculations,
etc.
As you would expect, the new machine runs processor
intensive operations much faster. But the old machine runs
disk intensive operations faster than the new one. We had
expected at least comparable performance.
Overall, the test runs in 39 minutes on the old machine
and 40 minutes on the new one (pretty close, I know, but
the new one should win by a larger margin).
Is there anything special about running SQL Server 2000 on
Windows 2003 that we need to know?
Does the Compaq drive array have some limitation that the
Dell does not?
Thanks
Dave GDave,
don't know whether you have considered the following two variants:
1. seek time. it is a specification of the hard drive, and though not much,
different drives can have different seek times. With the rotation speed on
the higher end, the seek times' difference plays a larger role
2. sorting mechanism of the RAID controllers: some controllers support
elevator sorting that is clearly at advantage than without.
hth
Quentin
"DaveG" <anonymous@.discussions.microsoft.com> wrote in message
news:02c701c3dae2$a256fde0$a401280a@.phx.gbl...
DG
variants:
and though not much,
rotation speed on
role
controllers support
without.
message
Compaq RA8000's there is more to the configuration than just the RAID level.
So it might still be something between the way the arrays are configured. If
you're familiar with IOMeter from Intel you might want to use that to test
the disk systems performance.
Also, from the tests I've seen hyperthreading isn't the same as having more
full processors, so maybe the difference in processor count plays a larger
part than expected. It may be possible that hyperthreading is actually
causing worse performance, you might want to test disabling it.
Mike Kruchten
"DaveG" <anonymous@.discussions.microsoft.com> wrote in message
news:02c701c3dae2$a256fde0$a401280a@.phx.gbl...
more
Absolutely right.
Windows 2003 that we bought to replace a Dell 8450
running Windows 2000
The new machine has 4 2.5 Ghz processors with hyper-
threading (when you open Task Mgr it looks like it
has 8 processors)
The old machine has 8 P3 700 Mhz processors.
Both machines have direct attached fiber channel
arrays configured identically except the new one uses
72 Gig hard drives and the old one has 36 Gig drives.
I am using 9 drives in Raid5 for data and 4 drives in Raid
1+0 for logs on both machines. Both arrays have 15000 RPM
drives.
Both machines have SQL Server 2000. My test database is
about 4 Gig and it is identical on both servers.
I have a stress test script with a variety of operations
such as bulk insert, create clustered index, calculations,
etc.
As you would expect, the new machine runs processor
intensive operations much faster. But the old machine runs
disk intensive operations faster than the new one. We had
expected at least comparable performance.
Overall, the test runs in 39 minutes on the old machine
and 40 minutes on the new one (pretty close, I know, but
the new one should win by a larger margin).
Is there anything special about running SQL Server 2000 on
Windows 2003 that we need to know?
Does the Compaq drive array have some limitation that the
Dell does not?
Thanks
Dave GDave,
don't know whether you have considered the following two variants:
1. seek time. it is a specification of the hard drive, and though not much,
different drives can have different seek times. With the rotation speed on
the higher end, the seek times' difference plays a larger role
2. sorting mechanism of the RAID controllers: some controllers support
elevator sorting that is clearly at advantage than without.
hth
Quentin
"DaveG" <anonymous@.discussions.microsoft.com> wrote in message
news:02c701c3dae2$a256fde0$a401280a@.phx.gbl...
quote:|||Thanks, I'll see if I can find the specs.
> We have a new Compaq Proliant DL760 server running
> Windows 2003 that we bought to replace a Dell 8450
> running Windows 2000
> The new machine has 4 2.5 Ghz processors with hyper-
> threading (when you open Task Mgr it looks like it
> has 8 processors)
> The old machine has 8 P3 700 Mhz processors.
> Both machines have direct attached fiber channel
> arrays configured identically except the new one uses
> 72 Gig hard drives and the old one has 36 Gig drives.
> I am using 9 drives in Raid5 for data and 4 drives in Raid
> 1+0 for logs on both machines. Both arrays have 15000 RPM
> drives.
> Both machines have SQL Server 2000. My test database is
> about 4 Gig and it is identical on both servers.
> I have a stress test script with a variety of operations
> such as bulk insert, create clustered index, calculations,
> etc.
> As you would expect, the new machine runs processor
> intensive operations much faster. But the old machine runs
> disk intensive operations faster than the new one. We had
> expected at least comparable performance.
> Overall, the test runs in 39 minutes on the old machine
> and 40 minutes on the new one (pretty close, I know, but
> the new one should win by a larger margin).
> Is there anything special about running SQL Server 2000 on
> Windows 2003 that we need to know?
> Does the Compaq drive array have some limitation that the
> Dell does not?
> Thanks
> Dave G
>
DG
quote:
>--Original Message--
>Dave,
>don't know whether you have considered the following two
variants:
quote:
>1. seek time. it is a specification of the hard drive,
and though not much,
quote:
>different drives can have different seek times. With the
rotation speed on
quote:
>the higher end, the seek times' difference plays a larger
role
quote:
>2. sorting mechanism of the RAID controllers: some
controllers support
quote:
>elevator sorting that is clearly at advantage than
without.
quote:
>hth
>Quentin
>
>"DaveG" <anonymous@.discussions.microsoft.com> wrote in
message
quote:|||You don't mention which drive arrays you have but from what I've seen of our
>news:02c701c3dae2$a256fde0$a401280a@.phx.gbl...
Raid[QUOTE]
RPM[QUOTE]
calculations,[QUOTE]
runs[QUOTE]
had[QUOTE]
on[QUOTE]
the[QUOTE]
>
>.
>
Compaq RA8000's there is more to the configuration than just the RAID level.
So it might still be something between the way the arrays are configured. If
you're familiar with IOMeter from Intel you might want to use that to test
the disk systems performance.
Also, from the tests I've seen hyperthreading isn't the same as having more
full processors, so maybe the difference in processor count plays a larger
part than expected. It may be possible that hyperthreading is actually
causing worse performance, you might want to test disabling it.
Mike Kruchten
"DaveG" <anonymous@.discussions.microsoft.com> wrote in message
news:02c701c3dae2$a256fde0$a401280a@.phx.gbl...
quote:|||> Also, from the tests I've seen hyperthreading isn't the same as having
> We have a new Compaq Proliant DL760 server running
> Windows 2003 that we bought to replace a Dell 8450
> running Windows 2000
> The new machine has 4 2.5 Ghz processors with hyper-
> threading (when you open Task Mgr it looks like it
> has 8 processors)
> The old machine has 8 P3 700 Mhz processors.
> Both machines have direct attached fiber channel
> arrays configured identically except the new one uses
> 72 Gig hard drives and the old one has 36 Gig drives.
> I am using 9 drives in Raid5 for data and 4 drives in Raid
> 1+0 for logs on both machines. Both arrays have 15000 RPM
> drives.
> Both machines have SQL Server 2000. My test database is
> about 4 Gig and it is identical on both servers.
> I have a stress test script with a variety of operations
> such as bulk insert, create clustered index, calculations,
> etc.
> As you would expect, the new machine runs processor
> intensive operations much faster. But the old machine runs
> disk intensive operations faster than the new one. We had
> expected at least comparable performance.
> Overall, the test runs in 39 minutes on the old machine
> and 40 minutes on the new one (pretty close, I know, but
> the new one should win by a larger margin).
> Is there anything special about running SQL Server 2000 on
> Windows 2003 that we need to know?
> Does the Compaq drive array have some limitation that the
> Dell does not?
> Thanks
> Dave G
>
more
quote:
> full processors, so maybe the difference in processor count plays a larger
> part than expected. It may be possible that hyperthreading is actually
> causing worse performance, you might want to test disabling it.
Absolutely right.
quote:
> "DaveG" <anonymous@.discussions.microsoft.com> wrote in message
> news:02c701c3dae2$a256fde0$a401280a@.phx.gbl...
>
Monday, March 12, 2012
Performance problem
HI, I have a performance problem with one of my set of packages. They run fine on dev machine but much slower on test server which has twice (8gb) the memory of my dev server (4gb). The first time I run the set of package they execute in less than a minute which is normal. But subsequent executions in same conditions (tables are empty) takes 2-3 minutes. When I restart Sql server 2005, the loading time is back to less than a minute.
I enables the "use awe" for memory beyond 4 gb on test server and while executing, total memory is far beyond the 8gb of test server and on the four available processors, only one seems to be used. Also, deployment on other machines have no problems. We rebuilt the test server (re-installed windows, Sql server 2005, SP1 and patches) with no luck.
I would like to monitor the problem using performance monitor and Sql server profiler, is there some specific counters or trace events that I should focus on? My set of packages (one main package calling several execute package tasks) load approximately 100K lines and they are deployes on file system.
Thank you,
Ccote
Not sure about the cause of your problem. I would sugest to enable package logging and compare execution logs between server and try to identify possible botle necks.
Also there is an interesting document about performance tunning:
http://download.microsoft.com/download/1/3/4/134644fd-05ad-4ee8-8b5a-0aed1c18a31e/SSISPerfTuning.doc
Wednesday, March 7, 2012
Performance of CLR
I have stored procedure in database. The procedure have near 2000 lines of
code, it implement state machine. No access to data in the procedure, only
lots of "if else" to find next state. The procedure is called for every
change of some objects, from tens of other SPs. From BOL it looks like it is
good candidate for moving it to C#. I restored database from SQL2k in SQL2k5.
Next I created C# function, which do nothing except accept same parameters
and return value. The value is constant, no any code in the C# function. Next
I comment all code in TSQL procedure, and add call of CLR procedure in the
SP. Next I measure performance of other SPs which call the SP. From my
results - it become worse, duration increased. Execution path in callers are
remain same. Why it is so? Is cost of CLR function call so high?
The cost of the function call to a CLR procedure from inside the SQL Server
environment is indeed going to be higher than the cost of calling a TSQL
function/procedure. But once inside the CLR procedure computation will be
much faster.
The reason for the slowdown is that you are switching from one environment
to another with CLR while in TSQL you are staying in the same environment
across the call boundary.
Hal Berenson, President
PredictableIT, LLC
www.predictableit.com
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:DC10BABA-32E1-4FA4-BA69-061B49F93F9E@.microsoft.com...
>I have stored procedure in database. The procedure have near 2000 lines of
> code, it implement state machine. No access to data in the procedure, only
> lots of "if else" to find next state. The procedure is called for every
> change of some objects, from tens of other SPs. From BOL it looks like it
> is
> good candidate for moving it to C#. I restored database from SQL2k in
> SQL2k5.
> Next I created C# function, which do nothing except accept same parameters
> and return value. The value is constant, no any code in the C# function.
> Next
> I comment all code in TSQL procedure, and add call of CLR procedure in the
> SP. Next I measure performance of other SPs which call the SP. From my
> results - it become worse, duration increased. Execution path in callers
> are
> remain same. Why it is so? Is cost of CLR function call so high?
|||"Hal Berenson" <hberenson@.predictableit.com> wrote in message
news:ORYGWLX6FHA.808@.TK2MSFTNGP09.phx.gbl...
> The cost of the function call to a CLR procedure from inside the SQL
> Server environment is indeed going to be higher than the cost of calling a
> TSQL function/procedure. But once inside the CLR procedure computation
> will be much faster.
Actually, that does not appear to be true based on my tests. I just ran
a test comparing a T-SQL UDF against an identical CLR UDF. Both do nothing
more than return an input parameter. The CLR version is approximately 27%
faster over the course of 1,000,000 iterations, on my test server... I'd be
interested in seeing some other results. Don't forget to compile the
assembly in release mode so that you get more optimized code.
The two functions are:
CREATE FUNCTION ReturnMe(@.Input INT)
RETURNS INT
AS
BEGIN
RETURN(@.Input)
END
...and...
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlInt32 ReturnMe_CLR(SqlInt32 Input)
{
return(Input);
}
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
|||Yeah that is the same I have seen in our tests. Scalar functions blow TSQL
UDF's out of the water, see SQLCLRPERF project which is part of the demo
files of a recent performance evaluation session, it compares some string
manipulation, results when using the UDF
set statistics time on
go
print 'call CLR UDF'
select dbo.GetMemberLength(StringValue, 2) from dbo.MyStringTable
print 'call T-SQL UDF'
select dbo.GetMemberLength_TSQL(StringValue, 2) from dbo.MyStringTable
set statistics time off
Passing in 19614 rows
call CLR UDF
SQL Server Execution Times:
CPU time = 140 ms, elapsed time = 108 ms.
call T-SQL UDF
SQL Server Execution Times:
CPU time = 6650 ms, elapsed time = 15671 ms.
See http://sqldev.net/download/conf/SQLC...USA/SAV303.zip
for the demo files and
http://sqldev.net/download/conf/SQLC...Evaluation.zip
for the presentation
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:unUEMeY6FHA.2176@.TK2MSFTNGP14.phx.gbl...
> "Hal Berenson" <hberenson@.predictableit.com> wrote in message
> news:ORYGWLX6FHA.808@.TK2MSFTNGP09.phx.gbl...
> Actually, that does not appear to be true based on my tests. I just
> ran a test comparing a T-SQL UDF against an identical CLR UDF. Both do
> nothing more than return an input parameter. The CLR version is
> approximately 27% faster over the course of 1,000,000 iterations, on my
> test server... I'd be interested in seeing some other results. Don't
> forget to compile the assembly in release mode so that you get more
> optimized code.
> The two functions are:
> CREATE FUNCTION ReturnMe(@.Input INT)
> RETURNS INT
> AS
> BEGIN
> RETURN(@.Input)
> END
>
> ...and...
>
> [Microsoft.SqlServer.Server.SqlFunction]
> public static SqlInt32 ReturnMe_CLR(SqlInt32 Input)
> {
> return(Input);
> }
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
|||Just to add my experience with that. This week I wrote a performace
measurement function which executed some really easy UDAs.
(Based on a million records, summing up integer values)
-A SUM used in TSQL ran about 500 ms
-A SUM within a UDA ran 800 ms
-A Cursor ran 35000 ms !!!
Jens Suessmeyer.
|||After more investigation I found the problem was caused by broken state
machine. Execution path in calling SP was changed. After fix of the problem,
I see ~10% increase in performance.
code, it implement state machine. No access to data in the procedure, only
lots of "if else" to find next state. The procedure is called for every
change of some objects, from tens of other SPs. From BOL it looks like it is
good candidate for moving it to C#. I restored database from SQL2k in SQL2k5.
Next I created C# function, which do nothing except accept same parameters
and return value. The value is constant, no any code in the C# function. Next
I comment all code in TSQL procedure, and add call of CLR procedure in the
SP. Next I measure performance of other SPs which call the SP. From my
results - it become worse, duration increased. Execution path in callers are
remain same. Why it is so? Is cost of CLR function call so high?
The cost of the function call to a CLR procedure from inside the SQL Server
environment is indeed going to be higher than the cost of calling a TSQL
function/procedure. But once inside the CLR procedure computation will be
much faster.
The reason for the slowdown is that you are switching from one environment
to another with CLR while in TSQL you are staying in the same environment
across the call boundary.
Hal Berenson, President
PredictableIT, LLC
www.predictableit.com
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:DC10BABA-32E1-4FA4-BA69-061B49F93F9E@.microsoft.com...
>I have stored procedure in database. The procedure have near 2000 lines of
> code, it implement state machine. No access to data in the procedure, only
> lots of "if else" to find next state. The procedure is called for every
> change of some objects, from tens of other SPs. From BOL it looks like it
> is
> good candidate for moving it to C#. I restored database from SQL2k in
> SQL2k5.
> Next I created C# function, which do nothing except accept same parameters
> and return value. The value is constant, no any code in the C# function.
> Next
> I comment all code in TSQL procedure, and add call of CLR procedure in the
> SP. Next I measure performance of other SPs which call the SP. From my
> results - it become worse, duration increased. Execution path in callers
> are
> remain same. Why it is so? Is cost of CLR function call so high?
|||"Hal Berenson" <hberenson@.predictableit.com> wrote in message
news:ORYGWLX6FHA.808@.TK2MSFTNGP09.phx.gbl...
> The cost of the function call to a CLR procedure from inside the SQL
> Server environment is indeed going to be higher than the cost of calling a
> TSQL function/procedure. But once inside the CLR procedure computation
> will be much faster.
Actually, that does not appear to be true based on my tests. I just ran
a test comparing a T-SQL UDF against an identical CLR UDF. Both do nothing
more than return an input parameter. The CLR version is approximately 27%
faster over the course of 1,000,000 iterations, on my test server... I'd be
interested in seeing some other results. Don't forget to compile the
assembly in release mode so that you get more optimized code.
The two functions are:
CREATE FUNCTION ReturnMe(@.Input INT)
RETURNS INT
AS
BEGIN
RETURN(@.Input)
END
...and...
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlInt32 ReturnMe_CLR(SqlInt32 Input)
{
return(Input);
}
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
|||Yeah that is the same I have seen in our tests. Scalar functions blow TSQL
UDF's out of the water, see SQLCLRPERF project which is part of the demo
files of a recent performance evaluation session, it compares some string
manipulation, results when using the UDF
set statistics time on
go
print 'call CLR UDF'
select dbo.GetMemberLength(StringValue, 2) from dbo.MyStringTable
print 'call T-SQL UDF'
select dbo.GetMemberLength_TSQL(StringValue, 2) from dbo.MyStringTable
set statistics time off
Passing in 19614 rows
call CLR UDF
SQL Server Execution Times:
CPU time = 140 ms, elapsed time = 108 ms.
call T-SQL UDF
SQL Server Execution Times:
CPU time = 6650 ms, elapsed time = 15671 ms.
See http://sqldev.net/download/conf/SQLC...USA/SAV303.zip
for the demo files and
http://sqldev.net/download/conf/SQLC...Evaluation.zip
for the presentation
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:unUEMeY6FHA.2176@.TK2MSFTNGP14.phx.gbl...
> "Hal Berenson" <hberenson@.predictableit.com> wrote in message
> news:ORYGWLX6FHA.808@.TK2MSFTNGP09.phx.gbl...
> Actually, that does not appear to be true based on my tests. I just
> ran a test comparing a T-SQL UDF against an identical CLR UDF. Both do
> nothing more than return an input parameter. The CLR version is
> approximately 27% faster over the course of 1,000,000 iterations, on my
> test server... I'd be interested in seeing some other results. Don't
> forget to compile the assembly in release mode so that you get more
> optimized code.
> The two functions are:
> CREATE FUNCTION ReturnMe(@.Input INT)
> RETURNS INT
> AS
> BEGIN
> RETURN(@.Input)
> END
>
> ...and...
>
> [Microsoft.SqlServer.Server.SqlFunction]
> public static SqlInt32 ReturnMe_CLR(SqlInt32 Input)
> {
> return(Input);
> }
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
|||Just to add my experience with that. This week I wrote a performace
measurement function which executed some really easy UDAs.
(Based on a million records, summing up integer values)
-A SUM used in TSQL ran about 500 ms
-A SUM within a UDA ran 800 ms
-A Cursor ran 35000 ms !!!
Jens Suessmeyer.
|||After more investigation I found the problem was caused by broken state
machine. Execution path in calling SP was changed. After fix of the problem,
I see ~10% increase in performance.
Performance of CLR
I have stored procedure in database. The procedure have near 2000 lines of
code, it implement state machine. No access to data in the procedure, only
lots of "if else" to find next state. The procedure is called for every
change of some objects, from tens of other SPs. From BOL it looks like it is
good candidate for moving it to C#. I restored database from SQL2k in SQL2k5.
Next I created C# function, which do nothing except accept same parameters
and return value. The value is constant, no any code in the C# function. Next
I comment all code in TSQL procedure, and add call of CLR procedure in the
SP. Next I measure performance of other SPs which call the SP. From my
results - it become worse, duration increased. Execution path in callers are
remain same. Why it is so? Is cost of CLR function call so high?The cost of the function call to a CLR procedure from inside the SQL Server
environment is indeed going to be higher than the cost of calling a TSQL
function/procedure. But once inside the CLR procedure computation will be
much faster.
The reason for the slowdown is that you are switching from one environment
to another with CLR while in TSQL you are staying in the same environment
across the call boundary.
--
Hal Berenson, President
PredictableIT, LLC
www.predictableit.com
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:DC10BABA-32E1-4FA4-BA69-061B49F93F9E@.microsoft.com...
>I have stored procedure in database. The procedure have near 2000 lines of
> code, it implement state machine. No access to data in the procedure, only
> lots of "if else" to find next state. The procedure is called for every
> change of some objects, from tens of other SPs. From BOL it looks like it
> is
> good candidate for moving it to C#. I restored database from SQL2k in
> SQL2k5.
> Next I created C# function, which do nothing except accept same parameters
> and return value. The value is constant, no any code in the C# function.
> Next
> I comment all code in TSQL procedure, and add call of CLR procedure in the
> SP. Next I measure performance of other SPs which call the SP. From my
> results - it become worse, duration increased. Execution path in callers
> are
> remain same. Why it is so? Is cost of CLR function call so high?|||"Hal Berenson" <hberenson@.predictableit.com> wrote in message
news:ORYGWLX6FHA.808@.TK2MSFTNGP09.phx.gbl...
> The cost of the function call to a CLR procedure from inside the SQL
> Server environment is indeed going to be higher than the cost of calling a
> TSQL function/procedure. But once inside the CLR procedure computation
> will be much faster.
Actually, that does not appear to be true based on my tests. I just ran
a test comparing a T-SQL UDF against an identical CLR UDF. Both do nothing
more than return an input parameter. The CLR version is approximately 27%
faster over the course of 1,000,000 iterations, on my test server... I'd be
interested in seeing some other results. Don't forget to compile the
assembly in release mode so that you get more optimized code.
The two functions are:
CREATE FUNCTION ReturnMe(@.Input INT)
RETURNS INT
AS
BEGIN
RETURN(@.Input)
END
...and...
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlInt32 ReturnMe_CLR(SqlInt32 Input)
{
return(Input);
}
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||Yeah that is the same I have seen in our tests. Scalar functions blow TSQL
UDF's out of the water, see SQLCLRPERF project which is part of the demo
files of a recent performance evaluation session, it compares some string
manipulation, results when using the UDF
set statistics time on
go
print 'call CLR UDF'
select dbo.GetMemberLength(StringValue, 2) from dbo.MyStringTable
print 'call T-SQL UDF'
select dbo.GetMemberLength_TSQL(StringValue, 2) from dbo.MyStringTable
set statistics time off
Passing in 19614 rows
call CLR UDF
SQL Server Execution Times:
CPU time = 140 ms, elapsed time = 108 ms.
call T-SQL UDF
SQL Server Execution Times:
CPU time = 6650 ms, elapsed time = 15671 ms.
See http://sqldev.net/download/conf/SQLConnections-2005-11-USA/SAV303.zip
for the demo files and
http://sqldev.net/download/conf/SQLConnections-2005-11-USA/SAV303%20-%20SQL-CLR%20A%20Performance%20Evaluation.zip
for the presentation
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2005 All rights reserved.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:unUEMeY6FHA.2176@.TK2MSFTNGP14.phx.gbl...
> "Hal Berenson" <hberenson@.predictableit.com> wrote in message
> news:ORYGWLX6FHA.808@.TK2MSFTNGP09.phx.gbl...
>> The cost of the function call to a CLR procedure from inside the SQL
>> Server environment is indeed going to be higher than the cost of calling
>> a TSQL function/procedure. But once inside the CLR procedure computation
>> will be much faster.
> Actually, that does not appear to be true based on my tests. I just
> ran a test comparing a T-SQL UDF against an identical CLR UDF. Both do
> nothing more than return an input parameter. The CLR version is
> approximately 27% faster over the course of 1,000,000 iterations, on my
> test server... I'd be interested in seeing some other results. Don't
> forget to compile the assembly in release mode so that you get more
> optimized code.
> The two functions are:
> CREATE FUNCTION ReturnMe(@.Input INT)
> RETURNS INT
> AS
> BEGIN
> RETURN(@.Input)
> END
>
> ...and...
>
> [Microsoft.SqlServer.Server.SqlFunction]
> public static SqlInt32 ReturnMe_CLR(SqlInt32 Input)
> {
> return(Input);
> }
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>|||Just to add my experience with that. This week I wrote a performace
measurement function which executed some really easy UDAs.
(Based on a million records, summing up integer values)
-A SUM used in TSQL ran about 500 ms
-A SUM within a UDA ran 800 ms
-A Cursor ran 35000 ms !!!
Jens Suessmeyer.|||After more investigation I found the problem was caused by broken state
machine. Execution path in calling SP was changed. After fix of the problem,
I see ~10% increase in performance.
code, it implement state machine. No access to data in the procedure, only
lots of "if else" to find next state. The procedure is called for every
change of some objects, from tens of other SPs. From BOL it looks like it is
good candidate for moving it to C#. I restored database from SQL2k in SQL2k5.
Next I created C# function, which do nothing except accept same parameters
and return value. The value is constant, no any code in the C# function. Next
I comment all code in TSQL procedure, and add call of CLR procedure in the
SP. Next I measure performance of other SPs which call the SP. From my
results - it become worse, duration increased. Execution path in callers are
remain same. Why it is so? Is cost of CLR function call so high?The cost of the function call to a CLR procedure from inside the SQL Server
environment is indeed going to be higher than the cost of calling a TSQL
function/procedure. But once inside the CLR procedure computation will be
much faster.
The reason for the slowdown is that you are switching from one environment
to another with CLR while in TSQL you are staying in the same environment
across the call boundary.
--
Hal Berenson, President
PredictableIT, LLC
www.predictableit.com
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:DC10BABA-32E1-4FA4-BA69-061B49F93F9E@.microsoft.com...
>I have stored procedure in database. The procedure have near 2000 lines of
> code, it implement state machine. No access to data in the procedure, only
> lots of "if else" to find next state. The procedure is called for every
> change of some objects, from tens of other SPs. From BOL it looks like it
> is
> good candidate for moving it to C#. I restored database from SQL2k in
> SQL2k5.
> Next I created C# function, which do nothing except accept same parameters
> and return value. The value is constant, no any code in the C# function.
> Next
> I comment all code in TSQL procedure, and add call of CLR procedure in the
> SP. Next I measure performance of other SPs which call the SP. From my
> results - it become worse, duration increased. Execution path in callers
> are
> remain same. Why it is so? Is cost of CLR function call so high?|||"Hal Berenson" <hberenson@.predictableit.com> wrote in message
news:ORYGWLX6FHA.808@.TK2MSFTNGP09.phx.gbl...
> The cost of the function call to a CLR procedure from inside the SQL
> Server environment is indeed going to be higher than the cost of calling a
> TSQL function/procedure. But once inside the CLR procedure computation
> will be much faster.
Actually, that does not appear to be true based on my tests. I just ran
a test comparing a T-SQL UDF against an identical CLR UDF. Both do nothing
more than return an input parameter. The CLR version is approximately 27%
faster over the course of 1,000,000 iterations, on my test server... I'd be
interested in seeing some other results. Don't forget to compile the
assembly in release mode so that you get more optimized code.
The two functions are:
CREATE FUNCTION ReturnMe(@.Input INT)
RETURNS INT
AS
BEGIN
RETURN(@.Input)
END
...and...
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlInt32 ReturnMe_CLR(SqlInt32 Input)
{
return(Input);
}
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||Yeah that is the same I have seen in our tests. Scalar functions blow TSQL
UDF's out of the water, see SQLCLRPERF project which is part of the demo
files of a recent performance evaluation session, it compares some string
manipulation, results when using the UDF
set statistics time on
go
print 'call CLR UDF'
select dbo.GetMemberLength(StringValue, 2) from dbo.MyStringTable
print 'call T-SQL UDF'
select dbo.GetMemberLength_TSQL(StringValue, 2) from dbo.MyStringTable
set statistics time off
Passing in 19614 rows
call CLR UDF
SQL Server Execution Times:
CPU time = 140 ms, elapsed time = 108 ms.
call T-SQL UDF
SQL Server Execution Times:
CPU time = 6650 ms, elapsed time = 15671 ms.
See http://sqldev.net/download/conf/SQLConnections-2005-11-USA/SAV303.zip
for the demo files and
http://sqldev.net/download/conf/SQLConnections-2005-11-USA/SAV303%20-%20SQL-CLR%20A%20Performance%20Evaluation.zip
for the presentation
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2005 All rights reserved.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:unUEMeY6FHA.2176@.TK2MSFTNGP14.phx.gbl...
> "Hal Berenson" <hberenson@.predictableit.com> wrote in message
> news:ORYGWLX6FHA.808@.TK2MSFTNGP09.phx.gbl...
>> The cost of the function call to a CLR procedure from inside the SQL
>> Server environment is indeed going to be higher than the cost of calling
>> a TSQL function/procedure. But once inside the CLR procedure computation
>> will be much faster.
> Actually, that does not appear to be true based on my tests. I just
> ran a test comparing a T-SQL UDF against an identical CLR UDF. Both do
> nothing more than return an input parameter. The CLR version is
> approximately 27% faster over the course of 1,000,000 iterations, on my
> test server... I'd be interested in seeing some other results. Don't
> forget to compile the assembly in release mode so that you get more
> optimized code.
> The two functions are:
> CREATE FUNCTION ReturnMe(@.Input INT)
> RETURNS INT
> AS
> BEGIN
> RETURN(@.Input)
> END
>
> ...and...
>
> [Microsoft.SqlServer.Server.SqlFunction]
> public static SqlInt32 ReturnMe_CLR(SqlInt32 Input)
> {
> return(Input);
> }
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>|||Just to add my experience with that. This week I wrote a performace
measurement function which executed some really easy UDAs.
(Based on a million records, summing up integer values)
-A SUM used in TSQL ran about 500 ms
-A SUM within a UDA ran 800 ms
-A Cursor ran 35000 ms !!!
Jens Suessmeyer.|||After more investigation I found the problem was caused by broken state
machine. Execution path in calling SP was changed. After fix of the problem,
I see ~10% increase in performance.
Performance of CLR
I have stored procedure in database. The procedure have near 2000 lines of
code, it implement state machine. No access to data in the procedure, only
lots of "if else" to find next state. The procedure is called for every
change of some objects, from tens of other SPs. From BOL it looks like it is
good candidate for moving it to C#. I restored database from SQL2k in SQL2k5
.
Next I created C# function, which do nothing except accept same parameters
and return value. The value is constant, no any code in the C# function. Nex
t
I comment all code in TSQL procedure, and add call of CLR procedure in the
SP. Next I measure performance of other SPs which call the SP. From my
results - it become worse, duration increased. Execution path in callers are
remain same. Why it is so? Is cost of CLR function call so high?The cost of the function call to a CLR procedure from inside the SQL Server
environment is indeed going to be higher than the cost of calling a TSQL
function/procedure. But once inside the CLR procedure computation will be
much faster.
The reason for the slowdown is that you are switching from one environment
to another with CLR while in TSQL you are staying in the same environment
across the call boundary.
Hal Berenson, President
PredictableIT, LLC
www.predictableit.com
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:DC10BABA-32E1-4FA4-BA69-061B49F93F9E@.microsoft.com...
>I have stored procedure in database. The procedure have near 2000 lines of
> code, it implement state machine. No access to data in the procedure, only
> lots of "if else" to find next state. The procedure is called for every
> change of some objects, from tens of other SPs. From BOL it looks like it
> is
> good candidate for moving it to C#. I restored database from SQL2k in
> SQL2k5.
> Next I created C# function, which do nothing except accept same parameters
> and return value. The value is constant, no any code in the C# function.
> Next
> I comment all code in TSQL procedure, and add call of CLR procedure in the
> SP. Next I measure performance of other SPs which call the SP. From my
> results - it become worse, duration increased. Execution path in callers
> are
> remain same. Why it is so? Is cost of CLR function call so high?|||"Hal Berenson" <hberenson@.predictableit.com> wrote in message
news:ORYGWLX6FHA.808@.TK2MSFTNGP09.phx.gbl...
> The cost of the function call to a CLR procedure from inside the SQL
> Server environment is indeed going to be higher than the cost of calling a
> TSQL function/procedure. But once inside the CLR procedure computation
> will be much faster.
Actually, that does not appear to be true based on my tests. I just ran
a test comparing a T-SQL UDF against an identical CLR UDF. Both do nothing
more than return an input parameter. The CLR version is approximately 27%
faster over the course of 1,000,000 iterations, on my test server... I'd be
interested in seeing some other results. Don't forget to compile the
assembly in release mode so that you get more optimized code.
The two functions are:
CREATE FUNCTION ReturnMe(@.Input INT)
RETURNS INT
AS
BEGIN
RETURN(@.Input)
END
...and...
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlInt32 ReturnMe_CLR(SqlInt32 Input)
{
return(Input);
}
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||Yeah that is the same I have seen in our tests. Scalar functions blow TSQL
UDF's out of the water, see SQLCLRPERF project which is part of the demo
files of a recent performance evaluation session, it compares some string
manipulation, results when using the UDF
set statistics time on
go
print 'call CLR UDF'
select dbo.GetMemberLength(StringValue, 2) from dbo.MyStringTable
print 'call T-SQL UDF'
select dbo.GetMemberLength_TSQL(StringValue, 2) from dbo.MyStringTable
set statistics time off
Passing in 19614 rows
call CLR UDF
SQL Server Execution Times:
CPU time = 140 ms, elapsed time = 108 ms.
call T-SQL UDF
SQL Server Execution Times:
CPU time = 6650 ms, elapsed time = 15671 ms.
See http://sqldev.net/download/conf/SQL...-USA/SAV303.zip
for the demo files and
http://sqldev.net/download/conf/SQL...0Evaluation.zip
for the presentation
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:unUEMeY6FHA.2176@.TK2MSFTNGP14.phx.gbl...
> "Hal Berenson" <hberenson@.predictableit.com> wrote in message
> news:ORYGWLX6FHA.808@.TK2MSFTNGP09.phx.gbl...
> Actually, that does not appear to be true based on my tests. I just
> ran a test comparing a T-SQL UDF against an identical CLR UDF. Both do
> nothing more than return an input parameter. The CLR version is
> approximately 27% faster over the course of 1,000,000 iterations, on my
> test server... I'd be interested in seeing some other results. Don't
> forget to compile the assembly in release mode so that you get more
> optimized code.
> The two functions are:
> CREATE FUNCTION ReturnMe(@.Input INT)
> RETURNS INT
> AS
> BEGIN
> RETURN(@.Input)
> END
>
> ...and...
>
> [Microsoft.SqlServer.Server.SqlFunction]
> public static SqlInt32 ReturnMe_CLR(SqlInt32 Input)
> {
> return(Input);
> }
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>|||Just to add my experience with that. This week I wrote a performace
measurement function which executed some really easy UDAs.
(Based on a million records, summing up integer values)
-A SUM used in TSQL ran about 500 ms
-A SUM within a UDA ran 800 ms
-A Cursor ran 35000 ms !!!
Jens Suessmeyer.|||After more investigation I found the problem was caused by broken state
machine. Execution path in calling SP was changed. After fix of the problem,
I see ~10% increase in performance.
code, it implement state machine. No access to data in the procedure, only
lots of "if else" to find next state. The procedure is called for every
change of some objects, from tens of other SPs. From BOL it looks like it is
good candidate for moving it to C#. I restored database from SQL2k in SQL2k5
.
Next I created C# function, which do nothing except accept same parameters
and return value. The value is constant, no any code in the C# function. Nex
t
I comment all code in TSQL procedure, and add call of CLR procedure in the
SP. Next I measure performance of other SPs which call the SP. From my
results - it become worse, duration increased. Execution path in callers are
remain same. Why it is so? Is cost of CLR function call so high?The cost of the function call to a CLR procedure from inside the SQL Server
environment is indeed going to be higher than the cost of calling a TSQL
function/procedure. But once inside the CLR procedure computation will be
much faster.
The reason for the slowdown is that you are switching from one environment
to another with CLR while in TSQL you are staying in the same environment
across the call boundary.
Hal Berenson, President
PredictableIT, LLC
www.predictableit.com
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:DC10BABA-32E1-4FA4-BA69-061B49F93F9E@.microsoft.com...
>I have stored procedure in database. The procedure have near 2000 lines of
> code, it implement state machine. No access to data in the procedure, only
> lots of "if else" to find next state. The procedure is called for every
> change of some objects, from tens of other SPs. From BOL it looks like it
> is
> good candidate for moving it to C#. I restored database from SQL2k in
> SQL2k5.
> Next I created C# function, which do nothing except accept same parameters
> and return value. The value is constant, no any code in the C# function.
> Next
> I comment all code in TSQL procedure, and add call of CLR procedure in the
> SP. Next I measure performance of other SPs which call the SP. From my
> results - it become worse, duration increased. Execution path in callers
> are
> remain same. Why it is so? Is cost of CLR function call so high?|||"Hal Berenson" <hberenson@.predictableit.com> wrote in message
news:ORYGWLX6FHA.808@.TK2MSFTNGP09.phx.gbl...
> The cost of the function call to a CLR procedure from inside the SQL
> Server environment is indeed going to be higher than the cost of calling a
> TSQL function/procedure. But once inside the CLR procedure computation
> will be much faster.
Actually, that does not appear to be true based on my tests. I just ran
a test comparing a T-SQL UDF against an identical CLR UDF. Both do nothing
more than return an input parameter. The CLR version is approximately 27%
faster over the course of 1,000,000 iterations, on my test server... I'd be
interested in seeing some other results. Don't forget to compile the
assembly in release mode so that you get more optimized code.
The two functions are:
CREATE FUNCTION ReturnMe(@.Input INT)
RETURNS INT
AS
BEGIN
RETURN(@.Input)
END
...and...
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlInt32 ReturnMe_CLR(SqlInt32 Input)
{
return(Input);
}
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||Yeah that is the same I have seen in our tests. Scalar functions blow TSQL
UDF's out of the water, see SQLCLRPERF project which is part of the demo
files of a recent performance evaluation session, it compares some string
manipulation, results when using the UDF
set statistics time on
go
print 'call CLR UDF'
select dbo.GetMemberLength(StringValue, 2) from dbo.MyStringTable
print 'call T-SQL UDF'
select dbo.GetMemberLength_TSQL(StringValue, 2) from dbo.MyStringTable
set statistics time off
Passing in 19614 rows
call CLR UDF
SQL Server Execution Times:
CPU time = 140 ms, elapsed time = 108 ms.
call T-SQL UDF
SQL Server Execution Times:
CPU time = 6650 ms, elapsed time = 15671 ms.
See http://sqldev.net/download/conf/SQL...-USA/SAV303.zip
for the demo files and
http://sqldev.net/download/conf/SQL...0Evaluation.zip
for the presentation
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:unUEMeY6FHA.2176@.TK2MSFTNGP14.phx.gbl...
> "Hal Berenson" <hberenson@.predictableit.com> wrote in message
> news:ORYGWLX6FHA.808@.TK2MSFTNGP09.phx.gbl...
> Actually, that does not appear to be true based on my tests. I just
> ran a test comparing a T-SQL UDF against an identical CLR UDF. Both do
> nothing more than return an input parameter. The CLR version is
> approximately 27% faster over the course of 1,000,000 iterations, on my
> test server... I'd be interested in seeing some other results. Don't
> forget to compile the assembly in release mode so that you get more
> optimized code.
> The two functions are:
> CREATE FUNCTION ReturnMe(@.Input INT)
> RETURNS INT
> AS
> BEGIN
> RETURN(@.Input)
> END
>
> ...and...
>
> [Microsoft.SqlServer.Server.SqlFunction]
> public static SqlInt32 ReturnMe_CLR(SqlInt32 Input)
> {
> return(Input);
> }
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>|||Just to add my experience with that. This week I wrote a performace
measurement function which executed some really easy UDAs.
(Based on a million records, summing up integer values)
-A SUM used in TSQL ran about 500 ms
-A SUM within a UDA ran 800 ms
-A Cursor ran 35000 ms !!!
Jens Suessmeyer.|||After more investigation I found the problem was caused by broken state
machine. Execution path in calling SP was changed. After fix of the problem,
I see ~10% increase in performance.
Monday, February 20, 2012
performance monitor not able to connect
Hi ,
I have set up SQL server 2000 on this machine COM_A and i am trying to
monitor the performance of the server via performance monitor on my desktop(
i have got MSDE2000 installed locally on my machine)
however, when i enter the server name \\COM_A or it's ip addr to get the
counters from the server it says "Unable to connect to machine"
could anyone advise ?
tks & rdgs
Can you connect though Query Analyzer or OSQL to COM_A?
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:7EF07AD4-D19B-4A9E-9DC7-4AFADFA0881A@.microsoft.com...
> Hi ,
> I have set up SQL server 2000 on this machine COM_A and i am trying to
> monitor the performance of the server via performance monitor on my
> desktop(
> i have got MSDE2000 installed locally on my machine)
> however, when i enter the server name \\COM_A or it's ip addr to get the
> counters from the server it says "Unable to connect to machine"
> could anyone advise ?
> tks & rdgs
|||Hi Mike ,
I can access COM_A via Query Analyzer
rdgs
"Mike Epprecht (SQL MVP)" wrote:
> Can you connect though Query Analyzer or OSQL to COM_A?
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:7EF07AD4-D19B-4A9E-9DC7-4AFADFA0881A@.microsoft.com...
>
>
|||Hi
You may want to check out
http://support.microsoft.com/default...b;en-us;300702
John
"maxzsim" wrote:
[vbcol=seagreen]
> Hi Mike ,
> I can access COM_A via Query Analyzer
> rdgs
> "Mike Epprecht (SQL MVP)" wrote:
|||tks John , i got it
cheers
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> You may want to check out
> http://support.microsoft.com/default...b;en-us;300702
> John
> "maxzsim" wrote:
I have set up SQL server 2000 on this machine COM_A and i am trying to
monitor the performance of the server via performance monitor on my desktop(
i have got MSDE2000 installed locally on my machine)
however, when i enter the server name \\COM_A or it's ip addr to get the
counters from the server it says "Unable to connect to machine"
could anyone advise ?
tks & rdgs
Can you connect though Query Analyzer or OSQL to COM_A?
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:7EF07AD4-D19B-4A9E-9DC7-4AFADFA0881A@.microsoft.com...
> Hi ,
> I have set up SQL server 2000 on this machine COM_A and i am trying to
> monitor the performance of the server via performance monitor on my
> desktop(
> i have got MSDE2000 installed locally on my machine)
> however, when i enter the server name \\COM_A or it's ip addr to get the
> counters from the server it says "Unable to connect to machine"
> could anyone advise ?
> tks & rdgs
|||Hi Mike ,
I can access COM_A via Query Analyzer
rdgs
"Mike Epprecht (SQL MVP)" wrote:
> Can you connect though Query Analyzer or OSQL to COM_A?
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:7EF07AD4-D19B-4A9E-9DC7-4AFADFA0881A@.microsoft.com...
>
>
|||Hi
You may want to check out
http://support.microsoft.com/default...b;en-us;300702
John
"maxzsim" wrote:
[vbcol=seagreen]
> Hi Mike ,
> I can access COM_A via Query Analyzer
> rdgs
> "Mike Epprecht (SQL MVP)" wrote:
|||tks John , i got it
cheers
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> You may want to check out
> http://support.microsoft.com/default...b;en-us;300702
> John
> "maxzsim" wrote:
performance monitor not able to connect
Hi ,
I have set up SQL server 2000 on this machine COM_A and i am trying to
monitor the performance of the server via performance monitor on my desktop(
i have got MSDE2000 installed locally on my machine)
however, when i enter the server name \\COM_A or it's ip addr to get the
counters from the server it says "Unable to connect to machine"
could anyone advise ?
tks & rdgsCan you connect though Query Analyzer or OSQL to COM_A?
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:7EF07AD4-D19B-4A9E-9DC7-4AFADFA0881A@.microsoft.com...
> Hi ,
> I have set up SQL server 2000 on this machine COM_A and i am trying to
> monitor the performance of the server via performance monitor on my
> desktop(
> i have got MSDE2000 installed locally on my machine)
> however, when i enter the server name \\COM_A or it's ip addr to get the
> counters from the server it says "Unable to connect to machine"
> could anyone advise ?
> tks & rdgs|||Hi Mike ,
I can access COM_A via Query Analyzer
rdgs
"Mike Epprecht (SQL MVP)" wrote:
> Can you connect though Query Analyzer or OSQL to COM_A?
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:7EF07AD4-D19B-4A9E-9DC7-4AFADFA0881A@.microsoft.com...
>
>|||Hi
You may want to check out
http://support.microsoft.com/defaul...kb;en-us;300702
John
"maxzsim" wrote:
[vbcol=seagreen]
> Hi Mike ,
> I can access COM_A via Query Analyzer
> rdgs
> "Mike Epprecht (SQL MVP)" wrote:
>|||tks John , i got it
cheers
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> You may want to check out
> http://support.microsoft.com/defaul...kb;en-us;300702
> John
> "maxzsim" wrote:
>
I have set up SQL server 2000 on this machine COM_A and i am trying to
monitor the performance of the server via performance monitor on my desktop(
i have got MSDE2000 installed locally on my machine)
however, when i enter the server name \\COM_A or it's ip addr to get the
counters from the server it says "Unable to connect to machine"
could anyone advise ?
tks & rdgsCan you connect though Query Analyzer or OSQL to COM_A?
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:7EF07AD4-D19B-4A9E-9DC7-4AFADFA0881A@.microsoft.com...
> Hi ,
> I have set up SQL server 2000 on this machine COM_A and i am trying to
> monitor the performance of the server via performance monitor on my
> desktop(
> i have got MSDE2000 installed locally on my machine)
> however, when i enter the server name \\COM_A or it's ip addr to get the
> counters from the server it says "Unable to connect to machine"
> could anyone advise ?
> tks & rdgs|||Hi Mike ,
I can access COM_A via Query Analyzer
rdgs
"Mike Epprecht (SQL MVP)" wrote:
> Can you connect though Query Analyzer or OSQL to COM_A?
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:7EF07AD4-D19B-4A9E-9DC7-4AFADFA0881A@.microsoft.com...
>
>|||Hi
You may want to check out
http://support.microsoft.com/defaul...kb;en-us;300702
John
"maxzsim" wrote:
[vbcol=seagreen]
> Hi Mike ,
> I can access COM_A via Query Analyzer
> rdgs
> "Mike Epprecht (SQL MVP)" wrote:
>|||tks John , i got it
cheers
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> You may want to check out
> http://support.microsoft.com/defaul...kb;en-us;300702
> John
> "maxzsim" wrote:
>
performance monitor not able to connect
Hi ,
I have set up SQL server 2000 on this machine COM_A and i am trying to
monitor the performance of the server via performance monitor on my desktop(
i have got MSDE2000 installed locally on my machine)
however, when i enter the server name \\COM_A or it's ip addr to get the
counters from the server it says "Unable to connect to machine"
could anyone advise ?
tks & rdgsCan you connect though Query Analyzer or OSQL to COM_A?
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:7EF07AD4-D19B-4A9E-9DC7-4AFADFA0881A@.microsoft.com...
> Hi ,
> I have set up SQL server 2000 on this machine COM_A and i am trying to
> monitor the performance of the server via performance monitor on my
> desktop(
> i have got MSDE2000 installed locally on my machine)
> however, when i enter the server name \\COM_A or it's ip addr to get the
> counters from the server it says "Unable to connect to machine"
> could anyone advise ?
> tks & rdgs|||Hi Mike ,
I can access COM_A via Query Analyzer
rdgs
"Mike Epprecht (SQL MVP)" wrote:
> Can you connect though Query Analyzer or OSQL to COM_A?
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:7EF07AD4-D19B-4A9E-9DC7-4AFADFA0881A@.microsoft.com...
> > Hi ,
> >
> > I have set up SQL server 2000 on this machine COM_A and i am trying to
> > monitor the performance of the server via performance monitor on my
> > desktop(
> > i have got MSDE2000 installed locally on my machine)
> > however, when i enter the server name \\COM_A or it's ip addr to get the
> > counters from the server it says "Unable to connect to machine"
> >
> > could anyone advise ?
> >
> > tks & rdgs
>
>|||Hi
You may want to check out
http://support.microsoft.com/default.aspx?scid=kb;en-us;300702
John
"maxzsim" wrote:
> Hi Mike ,
> I can access COM_A via Query Analyzer
> rdgs
> "Mike Epprecht (SQL MVP)" wrote:
> > Can you connect though Query Analyzer or OSQL to COM_A?
> >
> > Regards
> > --
> > Mike Epprecht, Microsoft SQL Server MVP
> > Zurich, Switzerland
> >
> > IM: mike@.epprecht.net
> >
> > MVP Program: http://www.microsoft.com/mvp
> >
> > Blog: http://www.msmvps.com/epprecht/
> >
> > "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> > news:7EF07AD4-D19B-4A9E-9DC7-4AFADFA0881A@.microsoft.com...
> > > Hi ,
> > >
> > > I have set up SQL server 2000 on this machine COM_A and i am trying to
> > > monitor the performance of the server via performance monitor on my
> > > desktop(
> > > i have got MSDE2000 installed locally on my machine)
> > > however, when i enter the server name \\COM_A or it's ip addr to get the
> > > counters from the server it says "Unable to connect to machine"
> > >
> > > could anyone advise ?
> > >
> > > tks & rdgs
> >
> >
> >|||tks John , i got it
cheers
"John Bell" wrote:
> Hi
> You may want to check out
> http://support.microsoft.com/default.aspx?scid=kb;en-us;300702
> John
> "maxzsim" wrote:
> > Hi Mike ,
> >
> > I can access COM_A via Query Analyzer
> >
> > rdgs
> >
> > "Mike Epprecht (SQL MVP)" wrote:
> >
> > > Can you connect though Query Analyzer or OSQL to COM_A?
> > >
> > > Regards
> > > --
> > > Mike Epprecht, Microsoft SQL Server MVP
> > > Zurich, Switzerland
> > >
> > > IM: mike@.epprecht.net
> > >
> > > MVP Program: http://www.microsoft.com/mvp
> > >
> > > Blog: http://www.msmvps.com/epprecht/
> > >
> > > "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> > > news:7EF07AD4-D19B-4A9E-9DC7-4AFADFA0881A@.microsoft.com...
> > > > Hi ,
> > > >
> > > > I have set up SQL server 2000 on this machine COM_A and i am trying to
> > > > monitor the performance of the server via performance monitor on my
> > > > desktop(
> > > > i have got MSDE2000 installed locally on my machine)
> > > > however, when i enter the server name \\COM_A or it's ip addr to get the
> > > > counters from the server it says "Unable to connect to machine"
> > > >
> > > > could anyone advise ?
> > > >
> > > > tks & rdgs
> > >
> > >
> > >
I have set up SQL server 2000 on this machine COM_A and i am trying to
monitor the performance of the server via performance monitor on my desktop(
i have got MSDE2000 installed locally on my machine)
however, when i enter the server name \\COM_A or it's ip addr to get the
counters from the server it says "Unable to connect to machine"
could anyone advise ?
tks & rdgsCan you connect though Query Analyzer or OSQL to COM_A?
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:7EF07AD4-D19B-4A9E-9DC7-4AFADFA0881A@.microsoft.com...
> Hi ,
> I have set up SQL server 2000 on this machine COM_A and i am trying to
> monitor the performance of the server via performance monitor on my
> desktop(
> i have got MSDE2000 installed locally on my machine)
> however, when i enter the server name \\COM_A or it's ip addr to get the
> counters from the server it says "Unable to connect to machine"
> could anyone advise ?
> tks & rdgs|||Hi Mike ,
I can access COM_A via Query Analyzer
rdgs
"Mike Epprecht (SQL MVP)" wrote:
> Can you connect though Query Analyzer or OSQL to COM_A?
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:7EF07AD4-D19B-4A9E-9DC7-4AFADFA0881A@.microsoft.com...
> > Hi ,
> >
> > I have set up SQL server 2000 on this machine COM_A and i am trying to
> > monitor the performance of the server via performance monitor on my
> > desktop(
> > i have got MSDE2000 installed locally on my machine)
> > however, when i enter the server name \\COM_A or it's ip addr to get the
> > counters from the server it says "Unable to connect to machine"
> >
> > could anyone advise ?
> >
> > tks & rdgs
>
>|||Hi
You may want to check out
http://support.microsoft.com/default.aspx?scid=kb;en-us;300702
John
"maxzsim" wrote:
> Hi Mike ,
> I can access COM_A via Query Analyzer
> rdgs
> "Mike Epprecht (SQL MVP)" wrote:
> > Can you connect though Query Analyzer or OSQL to COM_A?
> >
> > Regards
> > --
> > Mike Epprecht, Microsoft SQL Server MVP
> > Zurich, Switzerland
> >
> > IM: mike@.epprecht.net
> >
> > MVP Program: http://www.microsoft.com/mvp
> >
> > Blog: http://www.msmvps.com/epprecht/
> >
> > "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> > news:7EF07AD4-D19B-4A9E-9DC7-4AFADFA0881A@.microsoft.com...
> > > Hi ,
> > >
> > > I have set up SQL server 2000 on this machine COM_A and i am trying to
> > > monitor the performance of the server via performance monitor on my
> > > desktop(
> > > i have got MSDE2000 installed locally on my machine)
> > > however, when i enter the server name \\COM_A or it's ip addr to get the
> > > counters from the server it says "Unable to connect to machine"
> > >
> > > could anyone advise ?
> > >
> > > tks & rdgs
> >
> >
> >|||tks John , i got it
cheers
"John Bell" wrote:
> Hi
> You may want to check out
> http://support.microsoft.com/default.aspx?scid=kb;en-us;300702
> John
> "maxzsim" wrote:
> > Hi Mike ,
> >
> > I can access COM_A via Query Analyzer
> >
> > rdgs
> >
> > "Mike Epprecht (SQL MVP)" wrote:
> >
> > > Can you connect though Query Analyzer or OSQL to COM_A?
> > >
> > > Regards
> > > --
> > > Mike Epprecht, Microsoft SQL Server MVP
> > > Zurich, Switzerland
> > >
> > > IM: mike@.epprecht.net
> > >
> > > MVP Program: http://www.microsoft.com/mvp
> > >
> > > Blog: http://www.msmvps.com/epprecht/
> > >
> > > "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> > > news:7EF07AD4-D19B-4A9E-9DC7-4AFADFA0881A@.microsoft.com...
> > > > Hi ,
> > > >
> > > > I have set up SQL server 2000 on this machine COM_A and i am trying to
> > > > monitor the performance of the server via performance monitor on my
> > > > desktop(
> > > > i have got MSDE2000 installed locally on my machine)
> > > > however, when i enter the server name \\COM_A or it's ip addr to get the
> > > > counters from the server it says "Unable to connect to machine"
> > > >
> > > > could anyone advise ?
> > > >
> > > > tks & rdgs
> > >
> > >
> > >
Subscribe to:
Posts (Atom)