SQL compact edition: poor

Showing posts with label poor. Show all posts

Friday, March 30, 2012

Poor SQL Server Performance on Windows 2003 Server

I'm having a problem with SQL Server 2000 SP4 running on Windows 2003 Server
SP1. The performance is just awful.
I'm running a benchmark that creates a table and inserts 10000 records. This
is an ODBC application written in C++.
The server is a Quad 2.8G XEON with 2G RAM. On this server it takes 46
seconds to run the application that inserts the records.
On my laptop which is a 2.0G Pentium M with 1G RAM the same application
takes 7 seconds. My laptop has WinXP rather than Win2003 Server.
Now if you're not a very astute reader of benchmark results the bottom line
is that it takes 6 times longer to run this test on a high spec server
machine than on my laptop.
This is what I've done so far to try and fix this:
1. Try various configurations of processors and memory in the SQL
server configuration.
2. Reinstall SQL server.
3. Install Win2003 SP1.
4. Investigate hardware conflicts on the machine.
5. Benchmark disk performance. It's about twice as fast as the disk in
my laptop for both sequential and burst access.
So does anyone have any idea why the performance on this high spec server
would be so bad?
Thanks,
ColinA likely cause is that the server does not have disk write caching on the
SQL Server log but your laptop does. Assuming each insert is an individual
transaction, 10000 log I/Os are required. At a typical rate of 200 physical
disk I/Os per second, this comes to about 50 seconds.
A decent disk controller with write caching (and importantly battery backup)
ought to address your performance issue.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Colin Blair" <col_blair@.nospam.hotmail.com> wrote in message
news:eDIS4Ye1FHA.2212@.TK2MSFTNGP15.phx.gbl...
> I'm having a problem with SQL Server 2000 SP4 running on Windows 2003
> Server SP1. The performance is just awful.
>
> I'm running a benchmark that creates a table and inserts 10000 records.
> This is an ODBC application written in C++.
>
> The server is a Quad 2.8G XEON with 2G RAM. On this server it takes 46
> seconds to run the application that inserts the records.
>
> On my laptop which is a 2.0G Pentium M with 1G RAM the same application
> takes 7 seconds. My laptop has WinXP rather than Win2003 Server.
>
> Now if you're not a very astute reader of benchmark results the bottom
> line is that it takes 6 times longer to run this test on a high spec
> server machine than on my laptop.
>
> This is what I've done so far to try and fix this:
>
> 1. Try various configurations of processors and memory in the SQL
> server configuration.
> 2. Reinstall SQL server.
> 3. Install Win2003 SP1.
> 4. Investigate hardware conflicts on the machine.
> 5. Benchmark disk performance. It's about twice as fast as the disk
> in my laptop for both sequential and burst access.
>
> So does anyone have any idea why the performance on this high spec server
> would be so bad?
>
> Thanks,
> Colin
>|||Thanks for your reply Dan.
I checked the disk write cache option in the drive policies in the Win2003
device manager and it was enabled.
I also checked the SCSI card setup and that was fine too.
I should have also mentioned that the number of rows per transaction is
10000. So there is basically only 1 transaction.
Cheers,
Colin.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uL$WGhe1FHA.556@.TK2MSFTNGP12.phx.gbl...
>A likely cause is that the server does not have disk write caching on the
>SQL Server log but your laptop does. Assuming each insert is an individual
>transaction, 10000 log I/Os are required. At a typical rate of 200
>physical disk I/Os per second, this comes to about 50 seconds.
> A decent disk controller with write caching (and importantly battery
> backup) ought to address your performance issue.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Colin Blair" <col_blair@.nospam.hotmail.com> wrote in message
> news:eDIS4Ye1FHA.2212@.TK2MSFTNGP15.phx.gbl...
>> I'm having a problem with SQL Server 2000 SP4 running on Windows 2003
>> Server SP1. The performance is just awful.
>>
>> I'm running a benchmark that creates a table and inserts 10000 records.
>> This is an ODBC application written in C++.
>>
>> The server is a Quad 2.8G XEON with 2G RAM. On this server it takes 46
>> seconds to run the application that inserts the records.
>>
>> On my laptop which is a 2.0G Pentium M with 1G RAM the same application
>> takes 7 seconds. My laptop has WinXP rather than Win2003 Server.
>>
>> Now if you're not a very astute reader of benchmark results the bottom
>> line is that it takes 6 times longer to run this test on a high spec
>> server machine than on my laptop.
>>
>> This is what I've done so far to try and fix this:
>>
>> 1. Try various configurations of processors and memory in the SQL
>> server configuration.
>> 2. Reinstall SQL server.
>> 3. Install Win2003 SP1.
>> 4. Investigate hardware conflicts on the machine.
>> 5. Benchmark disk performance. It's about twice as fast as the disk
>> in my laptop for both sequential and burst access.
>>
>> So does anyone have any idea why the performance on this high spec server
>> would be so bad?
>>
>> Thanks,
>> Colin
>>
>|||What kind of drive is this in the Win2003 Server? Is it a drive array or
just a drive? Where are the log files located? A typical drive in a laptop
has more cache than one you normally find in a server. It can be several
times more in fact and this can make a huge difference when you have lots of
writes such as this. You also want the log drive on a physically separate
(not logically) drive than the data file. Even though the cache may be
enabled it may not be 100% write back. Normally they come as a 50/50 or
60/40 split between read and write cache. You can get a big performance
boost on writes by changing it to 100% write back.
Andrew J. Kelly SQL MVP
"Colin Blair" <col_blair@.nospam.hotmail.com> wrote in message
news:OauuqrD2FHA.2072@.TK2MSFTNGP14.phx.gbl...
> Thanks for your reply Dan.
> I checked the disk write cache option in the drive policies in the Win2003
> device manager and it was enabled.
> I also checked the SCSI card setup and that was fine too.
> I should have also mentioned that the number of rows per transaction is
> 10000. So there is basically only 1 transaction.
> Cheers,
> Colin.
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:uL$WGhe1FHA.556@.TK2MSFTNGP12.phx.gbl...
>>A likely cause is that the server does not have disk write caching on the
>>SQL Server log but your laptop does. Assuming each insert is an
>>individual transaction, 10000 log I/Os are required. At a typical rate of
>>200 physical disk I/Os per second, this comes to about 50 seconds.
>> A decent disk controller with write caching (and importantly battery
>> backup) ought to address your performance issue.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Colin Blair" <col_blair@.nospam.hotmail.com> wrote in message
>> news:eDIS4Ye1FHA.2212@.TK2MSFTNGP15.phx.gbl...
>> I'm having a problem with SQL Server 2000 SP4 running on Windows 2003
>> Server SP1. The performance is just awful.
>>
>> I'm running a benchmark that creates a table and inserts 10000 records.
>> This is an ODBC application written in C++.
>>
>> The server is a Quad 2.8G XEON with 2G RAM. On this server it takes 46
>> seconds to run the application that inserts the records.
>>
>> On my laptop which is a 2.0G Pentium M with 1G RAM the same application
>> takes 7 seconds. My laptop has WinXP rather than Win2003 Server.
>>
>> Now if you're not a very astute reader of benchmark results the bottom
>> line is that it takes 6 times longer to run this test on a high spec
>> server machine than on my laptop.
>>
>> This is what I've done so far to try and fix this:
>>
>> 1. Try various configurations of processors and memory in the SQL
>> server configuration.
>> 2. Reinstall SQL server.
>> 3. Install Win2003 SP1.
>> 4. Investigate hardware conflicts on the machine.
>> 5. Benchmark disk performance. It's about twice as fast as the disk
>> in my laptop for both sequential and burst access.
>>
>> So does anyone have any idea why the performance on this high spec
>> server would be so bad?
>>
>> Thanks,
>> Colin
>>
>>
>|||It seems a bit suspect that your throughput is as if each insert was in an
individual transaction. The log i/o during the COMMIT is synchronous so
your app will wait for completion. How are you doing the inserts?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Colin Blair" <col_blair@.nospam.hotmail.com> wrote in message
news:OauuqrD2FHA.2072@.TK2MSFTNGP14.phx.gbl...
> Thanks for your reply Dan.
> I checked the disk write cache option in the drive policies in the Win2003
> device manager and it was enabled.
> I also checked the SCSI card setup and that was fine too.
> I should have also mentioned that the number of rows per transaction is
> 10000. So there is basically only 1 transaction.
> Cheers,
> Colin.
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:uL$WGhe1FHA.556@.TK2MSFTNGP12.phx.gbl...
>>A likely cause is that the server does not have disk write caching on the
>>SQL Server log but your laptop does. Assuming each insert is an
>>individual transaction, 10000 log I/Os are required. At a typical rate of
>>200 physical disk I/Os per second, this comes to about 50 seconds.
>> A decent disk controller with write caching (and importantly battery
>> backup) ought to address your performance issue.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Colin Blair" <col_blair@.nospam.hotmail.com> wrote in message
>> news:eDIS4Ye1FHA.2212@.TK2MSFTNGP15.phx.gbl...
>> I'm having a problem with SQL Server 2000 SP4 running on Windows 2003
>> Server SP1. The performance is just awful.
>>
>> I'm running a benchmark that creates a table and inserts 10000 records.
>> This is an ODBC application written in C++.
>>
>> The server is a Quad 2.8G XEON with 2G RAM. On this server it takes 46
>> seconds to run the application that inserts the records.
>>
>> On my laptop which is a 2.0G Pentium M with 1G RAM the same application
>> takes 7 seconds. My laptop has WinXP rather than Win2003 Server.
>>
>> Now if you're not a very astute reader of benchmark results the bottom
>> line is that it takes 6 times longer to run this test on a high spec
>> server machine than on my laptop.
>>
>> This is what I've done so far to try and fix this:
>>
>> 1. Try various configurations of processors and memory in the SQL
>> server configuration.
>> 2. Reinstall SQL server.
>> 3. Install Win2003 SP1.
>> 4. Investigate hardware conflicts on the machine.
>> 5. Benchmark disk performance. It's about twice as fast as the disk
>> in my laptop for both sequential and burst access.
>>
>> So does anyone have any idea why the performance on this high spec
>> server would be so bad?
>>
>> Thanks,
>> Colin
>>
>>
>

Poor SQL Server Performance on Windows 2003 Server

Poor SQL Server Performance

Hello,
I have a big problem with the performance of SQL Server 2000 on an IBM
eSeries 335, with one Intel Xeon 2,4 GHz CPU and 1 GB RAM. As OS I tried:
Windows Server 2003
Windows Server 2000
Windows 2000 Professional
To insert of 6400 lines needs around 28 seconds. It doesn`t matter on
witch OS the SQL Server 2000 is installed. My desktop system with
Windows XP Professional, Windows 2000 Professional and SQL Server 2000
needs for the same datas just 8 seconds.
I supose the SQL Server 2000 may have a problem with the hyperthreading
of the Xeon CPU. I have configured the SQL Server 2000 to use only one
CPU but it has no effects to the performance of the SQL Server 2000.
Is there any possibility to disable the hyperthreading within the OS?
Hope somebody an help.
Cu
Thomas M.There are a lot of factors in how long it takes to do something like an
insert. What is the client and how are you actually doing the inserts?
What is the DDL of the table like and a typical row look like? Do you have
your log file on a separate RAID 1 from anything else?
--
Andrew J. Kelly
SQL Server MVP
"Thomas Meinke" <thomas.meinke@.arcord.de> wrote in message
news:1hhs9ollnxc5x$.dzb5as2qlncf.dlg@.40tude.net...
> Hello,
> I have a big problem with the performance of SQL Server 2000 on an IBM
> eSeries 335, with one Intel Xeon 2,4 GHz CPU and 1 GB RAM. As OS I tried:
> Windows Server 2003
> Windows Server 2000
> Windows 2000 Professional
> To insert of 6400 lines needs around 28 seconds. It doesn`t matter on
> witch OS the SQL Server 2000 is installed. My desktop system with
> Windows XP Professional, Windows 2000 Professional and SQL Server 2000
> needs for the same datas just 8 seconds.
> I supose the SQL Server 2000 may have a problem with the hyperthreading
> of the Xeon CPU. I have configured the SQL Server 2000 to use only one
> CPU but it has no effects to the performance of the SQL Server 2000.
> Is there any possibility to disable the hyperthreading within the OS?
> Hope somebody an help.
> Cu
> Thomas M.|||On Fri, 31 Oct 2003 07:51:12 -0500, Andrew J. Kelly wrote:
> There are a lot of factors in how long it takes to do something like an
> insert. What is the client and how are you actually doing the inserts?
> What is the DDL of the table like and a typical row look like? Do you have
> your log file on a separate RAID 1 from anything else?
Hello,
the client is a .Net-Application, that uses the sql ADO.NET drivers. The
inserts are made by calling a stored procedure.
Here is the sql create syntax for the stored procedure:
CREATE PROCEDURE sp_teilenummer_insert
@.tln_pk_tln_vchar VARCHAR(10),
@.tln_tind_vchar VARCHAR(2),
@.tln_bng_vchar VARCHAR(40),
@.wgp_fk_wgpid_vchar VARCHAR(10),
@.tln_tart_vchar VARCHAR(20),
@.tln_tstat_vchar VARCHAR(2)
AS
INSERT INTO tbl_liste_teilenummer(
tln_pk_tln_vchar,
tln_tind_vchar,
tln_bng_vchar,
wgp_fk_wgpid_vchar,
tln_tart_vchar,
tln_tstat_vchar
)
VALUES (@.tln_pk_tln_vchar,
@.tln_tind_vchar,
@.tln_bng_vchar,
@.wgp_fk_wgpid_vchar,
@.tln_tart_vchar,
@.tln_tstat_vchar
)
The amazing thing is that the same software configuration on my development
machine is so much faster than the server machine with much better
hardware. My development machine is an Intel Pentium III with 1 GHz and 512
MB RAM. It is the same application with the same datas but its poor slow on
the server machine.
Cu
Thomas M.|||Isn't it simply because you add network overhead? If you insert row by row
you add 6400 network roundtrips to what you might have tested locally on
your XP machine.
--
regards,
Mario
http://www.sqlinternals.com
"Thomas Meinke" <thomas.meinke@.arcord.de> wrote in message
news:1107459esxrhn.f67d4a2opmrw.dlg@.40tude.net...
> On Fri, 31 Oct 2003 07:51:12 -0500, Andrew J. Kelly wrote:
> > There are a lot of factors in how long it takes to do something like an
> > insert. What is the client and how are you actually doing the inserts?
> > What is the DDL of the table like and a typical row look like? Do you
have
> > your log file on a separate RAID 1 from anything else?
> Hello,
> the client is a .Net-Application, that uses the sql ADO.NET drivers. The
> inserts are made by calling a stored procedure.
> Here is the sql create syntax for the stored procedure:
> CREATE PROCEDURE sp_teilenummer_insert
> @.tln_pk_tln_vchar VARCHAR(10),
> @.tln_tind_vchar VARCHAR(2),
> @.tln_bng_vchar VARCHAR(40),
> @.wgp_fk_wgpid_vchar VARCHAR(10),
> @.tln_tart_vchar VARCHAR(20),
> @.tln_tstat_vchar VARCHAR(2)
> AS
> INSERT INTO tbl_liste_teilenummer(
> tln_pk_tln_vchar,
> tln_tind_vchar,
> tln_bng_vchar,
> wgp_fk_wgpid_vchar,
> tln_tart_vchar,
> tln_tstat_vchar
> )
> VALUES (@.tln_pk_tln_vchar,
> @.tln_tind_vchar,
> @.tln_bng_vchar,
> @.wgp_fk_wgpid_vchar,
> @.tln_tart_vchar,
> @.tln_tstat_vchar
> )
> The amazing thing is that the same software configuration on my
development
> machine is so much faster than the server machine with much better
> hardware. My development machine is an Intel Pentium III with 1 GHz and
512
> MB RAM. It is the same application with the same datas but its poor slow
on
> the server machine.
> Cu
> Thomas M.|||Are you comparing like with like? I.E It sounds like in your development
case your client and the db are on the same machine, whereas for production
the client and the db are on different machines?
--
Niall Litchfield
Oracle DBA
Audit Commission Uk
"Thomas Meinke" <thomas.meinke@.arcord.de> wrote in message
news:1107459esxrhn.f67d4a2opmrw.dlg@.40tude.net...
> On Fri, 31 Oct 2003 07:51:12 -0500, Andrew J. Kelly wrote:
> > There are a lot of factors in how long it takes to do something like an
> > insert. What is the client and how are you actually doing the inserts?
> > What is the DDL of the table like and a typical row look like? Do you
have
> > your log file on a separate RAID 1 from anything else?
> Hello,
> the client is a .Net-Application, that uses the sql ADO.NET drivers. The
> inserts are made by calling a stored procedure.
> Here is the sql create syntax for the stored procedure:
> CREATE PROCEDURE sp_teilenummer_insert
> @.tln_pk_tln_vchar VARCHAR(10),
> @.tln_tind_vchar VARCHAR(2),
> @.tln_bng_vchar VARCHAR(40),
> @.wgp_fk_wgpid_vchar VARCHAR(10),
> @.tln_tart_vchar VARCHAR(20),
> @.tln_tstat_vchar VARCHAR(2)
> AS
> INSERT INTO tbl_liste_teilenummer(
> tln_pk_tln_vchar,
> tln_tind_vchar,
> tln_bng_vchar,
> wgp_fk_wgpid_vchar,
> tln_tart_vchar,
> tln_tstat_vchar
> )
> VALUES (@.tln_pk_tln_vchar,
> @.tln_tind_vchar,
> @.tln_bng_vchar,
> @.wgp_fk_wgpid_vchar,
> @.tln_tart_vchar,
> @.tln_tstat_vchar
> )
> The amazing thing is that the same software configuration on my
development
> machine is so much faster than the server machine with much better
> hardware. My development machine is an Intel Pentium III with 1 GHz and
512
> MB RAM. It is the same application with the same datas but its poor slow
on
> the server machine.
> Cu
> Thomas M.|||On Fri, 31 Oct 2003 14:16:04 +0100, Mario wrote:
> Isn't it simply because you add network overhead? If you insert row by row
> you add 6400 network roundtrips to what you might have tested locally on
> your XP machine.
Hello,
I don`t think so, because I have installed the .NET Application on the
server to test it. And the application needs the same time to insert the
data as over the network.
I tried to insert the datas into another SQL Server 2000 on another
developer machine over network and it needs the same time as locally on
my machine.
Cu
Thomas M.|||6,400 inserts in 28 sec for 228 inserts/sec sounds like
the log disk bottleneck to me.
for each insert, sql svr is waiting for the prev insert
log write to complete.
In Computer Management -> Disk Management, right click on
the physical disk (disk 0, etc) go to the Policies tab,
try various settings of write cache enabled, adv. perf etc.
you can do this while your insert test is running, be sure
to monitor disk writes/sec
>--Original Message--
>Hello,
>I have a big problem with the performance of SQL Server
2000 on an IBM
>eSeries 335, with one Intel Xeon 2,4 GHz CPU and 1 GB
RAM. As OS I tried:
>Windows Server 2003
>Windows Server 2000
>Windows 2000 Professional
>To insert of 6400 lines needs around 28 seconds. It
doesn`t matter on
>witch OS the SQL Server 2000 is installed. My desktop
system with
>Windows XP Professional, Windows 2000 Professional and
SQL Server 2000
>needs for the same datas just 8 seconds.
>I supose the SQL Server 2000 may have a problem with the
hyperthreading
>of the Xeon CPU. I have configured the SQL Server 2000 to
use only one
>CPU but it has no effects to the performance of the SQL
Server 2000.
>Is there any possibility to disable the hyperthreading
within the OS?
>Hope somebody an help.
>Cu
>Thomas M.
>.
>|||If you are the only one one the test machine the following might show where
you 'loose' time:
if you test program is logged on: select cpu from sysprocesses
do a dbcc sqlperf(waitstats,clear)
do your test run
now a dbcc sqlperf(waitstats)
check sysprocesses.cpu for the spid that ran the test run
the overall resource consumption (cpu+wait+sigwait) should be about the
elapsed time of your testrun.
If you do the same on the 'fast' host, you should see a difference
somewhere: either in cpu consumption or on one of the waitstats.
(a sqlprofiler run might be more easy to do, but doesn;t show wait
statistics, which can reveal your current problem)
--
regards,
Mario
http://www.sqlinternals.com
"Thomas Meinke" <thomas.meinke@.arcord.de> wrote in message
news:1uzrvpac6ff4d$.1un4vediy6zql$.dlg@.40tude.net...
> On Fri, 31 Oct 2003 14:16:04 +0100, Mario wrote:
> > Isn't it simply because you add network overhead? If you insert row by
row
> > you add 6400 network roundtrips to what you might have tested locally on
> > your XP machine.
> Hello,
> I don`t think so, because I have installed the .NET Application on the
> server to test it. And the application needs the same time to insert the
> data as over the network.
> I tried to insert the datas into another SQL Server 2000 on another
> developer machine over network and it needs the same time as locally on
> my machine.
> Cu
> Thomas M.|||"joe chang" <anonymous@.discussions.microsoft.com> wrote in message
news:0b1801c39fb9$ce6c8350$a401280a@.phx.gbl...
> 6,400 inserts in 28 sec for 228 inserts/sec sounds like
> the log disk bottleneck to me.
> for each insert, sql svr is waiting for the prev insert
> log write to complete.
> In Computer Management -> Disk Management, right click on
> the physical disk (disk 0, etc) go to the Policies tab,
> try various settings of write cache enabled, adv. perf etc.
> you can do this while your insert test is running, be sure
> to monitor disk writes/sec
>
Good thought. To further reduce log write waits, wrap all of your inserts
in a single transaction.
This will allow you to defer the log writes until the end and do one large
write instead of 6,400 small ones.
David|||The information gets written to the log regardless of if it's in a user
defined transaction or not. There can be a certain amount of overhead
associated with 6400 separate log entries vs 1 but the info is still all
there. You have to be careful with a general statement (unfortunately we
have to do it at times) as that since it can depend on a lot of factors as
to which would be faster. I do agree that the log seems a likely place to
start though. Thomas, is it on a separate disk?
--
Andrew J. Kelly
SQL Server MVP
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:u4jBJL8nDHA.2068@.TK2MSFTNGP09.phx.gbl...
> "joe chang" <anonymous@.discussions.microsoft.com> wrote in message
> news:0b1801c39fb9$ce6c8350$a401280a@.phx.gbl...
> > 6,400 inserts in 28 sec for 228 inserts/sec sounds like
> > the log disk bottleneck to me.
> > for each insert, sql svr is waiting for the prev insert
> > log write to complete.
> > In Computer Management -> Disk Management, right click on
> > the physical disk (disk 0, etc) go to the Policies tab,
> > try various settings of write cache enabled, adv. perf etc.
> > you can do this while your insert test is running, be sure
> > to monitor disk writes/sec
> >
> >
> Good thought. To further reduce log write waits, wrap all of your inserts
> in a single transaction.
> This will allow you to defer the log writes until the end and do one large
> write instead of 6,400 small ones.
> David
>|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OCbW13%23nDHA.1764@.tk2msftngp13.phx.gbl...
> The information gets written to the log regardless of if it's in a user
> defined transaction or not. There can be a certain amount of overhead
> associated with 6400 separate log entries vs 1 but the info is still all
> there.
Yes, but the log is flushed to disk after each of the 6400 inserts unless
they are all in a transaction. With a transaction, the log is flushed on
its normal schedule, or at the latest when the transaction is commited.
David

Poor Server Performance When Running Data Driven Subscriptions

We are seeing poor server performance when running simple data driven
subscriptions. The subscription references a simple view that provides
parameter data. When we run the subscription, we see disk usage and memeory
usage increase dramatically on the server and the server becomes very slow
(almost to the point where other activity on the server stops).
Has anyone seen this behavior and does anyone have any recommendations?We are experiencing the same performance issue, our server gets 100% busy
when we run subscriptions, also, some subscriptions never get completed.sql

Poor query performance with: set statistics time on (SQL2K5)

Hi,
I try this query:
--
use AdventureWorks
go
select c.*,o.* from sales.salesorderheader o join sales.customer c
on o.customerid=c.customerid
go
set statistics time on
go
-- try the query again
select c.*,o.* from sales.salesorderheader o join sales.customer c
on o.customerid=c.customerid
--
the second query after setting "statistics time" is very slow on my
computer. I'd like to know if anybody else has the same problem?
Thanks in advance,
LeilaI tested it on 2005 SP1 using Management Studio. I ran it as give,
and with the order reversed. I also added select getdate() lines to
give a measure of elapsed time. Results were returned to a grid.
With statistics time on the query took a bit more than twice as long
to run.
Roy Harvey
Beacon Falls, CT
On Wed, 3 May 2006 03:39:17 +0430, "Leila" <Leilas@.hotpop.com> wrote:

>Hi,
>I try this query:
>--
>use AdventureWorks
>go
>select c.*,o.* from sales.salesorderheader o join sales.customer c
>on o.customerid=c.customerid
>go
>set statistics time on
>go
>-- try the query again
>select c.*,o.* from sales.salesorderheader o join sales.customer c
>on o.customerid=c.customerid
>--
>the second query after setting "statistics time" is very slow on my
>computer. I'd like to know if anybody else has the same problem?
>Thanks in advance,
>Leila|||Leila and Roy,
I can also reproduce this behavior. I don't have an answer
or workaround, but I'd suggest one of you file it as a bug
at http://lab.msdn.microsoft.com/productfeedback/
Steve Kass
Drew University
Leila wrote:

>Hi,
>I try this query:
>--
>use AdventureWorks
>go
>select c.*,o.* from sales.salesorderheader o join sales.customer c
>on o.customerid=c.customerid
>go
>set statistics time on
>go
>-- try the query again
>select c.*,o.* from sales.salesorderheader o join sales.customer c
>on o.customerid=c.customerid
>--
>the second query after setting "statistics time" is very slow on my
>computer. I'd like to know if anybody else has the same problem?
>Thanks in advance,
>Leila
>
>
>|||On Wed, 03 May 2006 00:00:42 -0400, Steve Kass <skass@.drew.edu> wrote:
>Leila and Roy,
>I can also reproduce this behavior. I don't have an answer
>or workaround, but I'd suggest one of you file it as a bug
>at http://lab.msdn.microsoft.com/productfeedback/
>Steve Kass
>Drew University
Yikes.
Just for the record, I've been using statistics io on a lot over the
last year or so, and on SQL2000 it generally had a small impact, maybe
5 to 10 percent.
Josh

Poor query performance with: set statistics time on (SQL2K5)

Hi,
I try this query:
--
use AdventureWorks
go
select c.*,o.* from sales.salesorderheader o join sales.customer c
on o.customerid=c.customerid
go
set statistics time on
go
-- try the query again
select c.*,o.* from sales.salesorderheader o join sales.customer c
on o.customerid=c.customerid
--
the second query after setting "statistics time" is very slow on my
computer. I'd like to know if anybody else has the same problem?
Thanks in advance,
LeilaI tested it on 2005 SP1 using Management Studio. I ran it as give,
and with the order reversed. I also added select getdate() lines to
give a measure of elapsed time. Results were returned to a grid.
With statistics time on the query took a bit more than twice as long
to run.
Roy Harvey
Beacon Falls, CT
On Wed, 3 May 2006 03:39:17 +0430, "Leila" <Leilas@.hotpop.com> wrote:
>Hi,
>I try this query:
>--
>use AdventureWorks
>go
>select c.*,o.* from sales.salesorderheader o join sales.customer c
>on o.customerid=c.customerid
>go
>set statistics time on
>go
>-- try the query again
>select c.*,o.* from sales.salesorderheader o join sales.customer c
>on o.customerid=c.customerid
>--
>the second query after setting "statistics time" is very slow on my
>computer. I'd like to know if anybody else has the same problem?
>Thanks in advance,
>Leila|||Leila and Roy,
I can also reproduce this behavior. I don't have an answer
or workaround, but I'd suggest one of you file it as a bug
at http://lab.msdn.microsoft.com/productfeedback/
Steve Kass
Drew University
Leila wrote:
>Hi,
>I try this query:
>--
>use AdventureWorks
>go
>select c.*,o.* from sales.salesorderheader o join sales.customer c
>on o.customerid=c.customerid
>go
>set statistics time on
>go
>-- try the query again
>select c.*,o.* from sales.salesorderheader o join sales.customer c
>on o.customerid=c.customerid
>--
>the second query after setting "statistics time" is very slow on my
>computer. I'd like to know if anybody else has the same problem?
>Thanks in advance,
>Leila
>
>
>|||On Wed, 03 May 2006 00:00:42 -0400, Steve Kass <skass@.drew.edu> wrote:
>Leila and Roy,
>I can also reproduce this behavior. I don't have an answer
>or workaround, but I'd suggest one of you file it as a bug
>at http://lab.msdn.microsoft.com/productfeedback/
>Steve Kass
>Drew University
Yikes.
Just for the record, I've been using statistics io on a lot over the
last year or so, and on SQL2000 it generally had a small impact, maybe
5 to 10 percent.
Josh

Poor query performance with: set statistics time on (SQL2K5)

poor preformance usinng sql 2005 with access 2003 application

we have run access 2003 application using SQL 2005 server
and the data transfer rate is extreamly slow.
we are using ODBC connection
and the access apllication works very fast with SQL 2000.

we have the tested the comptabilty of the application to SQL 2005 with Microsoft wizard and it had no problems.
even when i run simple application on the sql 2005 server, it runs very slowly.

i am using the MDAC clients ODBC component and the SQL Native Clients one.
which eliminates the possibilty of network problem.

what could be the problem?

Most likely the problem is improper indexing on the SQL Server side. Typically the driver used is not going to have a huge impact on overall performance. The simplest way to verify if you have index issues is run your application against SQL 2005 and capture a SQL Profiler trace of the activity. Then take the profiler trace and run it though the "Database Engine Tuning Advisor" tool that comes with SQL 2005 and it will analyze the trace and make recommendations as to what indexes to build.

Poor plan choice

Hello,
A couple days ago one of our queries suddenly started to perform
abyssmally. The query is pretty straightforward - it joins several
tables all on foreign keys and includes a GROUP BY with COUNT(*). I
looked over the query plan and it looked a little odd so I tried
cutting the query down to see where the issue might be. I eventually
came up with the following:
SELECT COUNT(*)
FROM Table1 T1
INNER JOIN Table2 T2 ON T2.table2_id = T1.table2_id
WHERE T1.my_date = '2004-11-18'
The table and column names have been changed to protect the innocent,
but that is the exact format of the tables. Table1 has about 35M
records. Table2 has about 6.5M records. For the date in question,
Table1 has about 165K records.
There is a non-clustered index on T1.my_date and there is a clustered
index on T2.table2_id.
The query plan for this simple query does an index seek on T1.my_date
as I expected then it does a bookmark lookup (presumably because it
needs T1.table2_id). It then includes parallelism, a hash, and then a
bitmap creation. Meanwhile, it does an index scan using an index on
Table2 that includes a single column that isn't even mentioned in the
query(?!?!). It then uses parallelism and does a hash match/inner
join.
I've done UPDATE STATISTICS using WITH FULLSCAN for both tables and
I've done a DBCC CHECKTABLE on both tables. Neither had any effect. I
also tried to force the query to use the clustered index for Table2.
For the simple query above it doesn't seem to help performance as the
clustered index scan has a very large cost to it (I'm not sure that I
entirely understand why). In the original query it helps substantially
though. Instead of joining the 6.5M records to a lookup table first it
joins it to Table1 first, which cuts down the number of records to the
165K before going about with other joins.
What I'm looking for is any advice on other things that I can look at
or any ideas on why SQL Server might be making these kinds of choices.
I would have thought that the simple query above would have performed
much better than it is currently (~30-35 seconds). I realize that
there has to be a bookmark lookup, but I was still expecting a quick
response from the server based on the indexes.
Because of the table sizes, etc. I don't expect anyone to reproduce my
results, so please don't ask me to provide DDL for all of the tables
involved. If you have some ideas or even just guesses great, if not
then that's ok too.
Thanks,
-Tom.Thomas, see inline
"Thomas R. Hummel" wrote:
> Hello,
> A couple days ago one of our queries suddenly started to perform
> abyssmally. The query is pretty straightforward - it joins several
> tables all on foreign keys and includes a GROUP BY with COUNT(*). I
> looked over the query plan and it looked a little odd so I tried
> cutting the query down to see where the issue might be. I eventually
> came up with the following:
> SELECT COUNT(*)
> FROM Table1 T1
> INNER JOIN Table2 T2 ON T2.table2_id = T1.table2_id
> WHERE T1.my_date = '2004-11-18'
> The table and column names have been changed to protect the innocent,
> but that is the exact format of the tables. Table1 has about 35M
> records. Table2 has about 6.5M records. For the date in question,
> Table1 has about 165K records.
> There is a non-clustered index on T1.my_date and there is a clustered
> index on T2.table2_id.
Consider adding a nonclustered index on T1(my_date,table2_id). This will
prevent the (quite expensive) bookmark lookups.
> The query plan for this simple query does an index seek on T1.my_date
> as I expected then it does a bookmark lookup (presumably because it
> needs T1.table2_id). It then includes parallelism, a hash, and then a
> bitmap creation. Meanwhile, it does an index scan using an index on
> Table2 that includes a single column that isn't even mentioned in the
> query(?!?!). It then uses parallelism and does a hash match/inner
> join.
Apparently SQL-Server estimates that the parallel plan will be faster.
If you expect differently, then you could add the hint OPTION (MAXDOP 1)
to force the serial plan.
Since the index on T2(table2_id) is clustered it is very wide at the
page level. In this case, SQL-Server estimates that it is faster to scan
a nonclustered index of table T2 (which also includes the clustered
index key) than it is to seek (or partially scan) the clustered index
for the estimated rows of the query.
Hope this helps,
Gert-Jan
> I've done UPDATE STATISTICS using WITH FULLSCAN for both tables and
> I've done a DBCC CHECKTABLE on both tables. Neither had any effect. I
> also tried to force the query to use the clustered index for Table2.
> For the simple query above it doesn't seem to help performance as the
> clustered index scan has a very large cost to it (I'm not sure that I
> entirely understand why). In the original query it helps substantially
> though. Instead of joining the 6.5M records to a lookup table first it
> joins it to Table1 first, which cuts down the number of records to the
> 165K before going about with other joins.
> What I'm looking for is any advice on other things that I can look at
> or any ideas on why SQL Server might be making these kinds of choices.
> I would have thought that the simple query above would have performed
> much better than it is currently (~30-35 seconds). I realize that
> there has to be a bookmark lookup, but I was still expecting a quick
> response from the server based on the indexes.
> Because of the table sizes, etc. I don't expect anyone to reproduce my
> results, so please don't ask me to provide DDL for all of the tables
> involved. If you have some ideas or even just guesses great, if not
> then that's ok too.
> Thanks,
> -Tom.|||I fully agree. OPTION (MAXDOP 1) should resolve the problem. In my
experience, UPDATE STATISTICS would temporaily fix it. And index hint,
query hint would also force a right plan (not a best practice though). If
it's from a stored procedure, WITH RECOMPILE would also fix it (not a best
practice).
Gary
SELECT COUNT(*)
> FROM Table1 T1
> INNER JOIN Table2 T2 ON T2.table2_id = T1.table2_id
> WHERE T1.my_date = '2004-11-18'
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:419E4013.6DBC44D@.toomuchspamalready.nl...
> Thomas, see inline
> "Thomas R. Hummel" wrote:
> >
> > Hello,
> >
> > A couple days ago one of our queries suddenly started to perform
> > abyssmally. The query is pretty straightforward - it joins several
> > tables all on foreign keys and includes a GROUP BY with COUNT(*). I
> > looked over the query plan and it looked a little odd so I tried
> > cutting the query down to see where the issue might be. I eventually
> > came up with the following:
> >
> > SELECT COUNT(*)
> > FROM Table1 T1
> > INNER JOIN Table2 T2 ON T2.table2_id = T1.table2_id
> > WHERE T1.my_date = '2004-11-18'
> >
> > The table and column names have been changed to protect the innocent,
> > but that is the exact format of the tables. Table1 has about 35M
> > records. Table2 has about 6.5M records. For the date in question,
> > Table1 has about 165K records.
> >
> > There is a non-clustered index on T1.my_date and there is a clustered
> > index on T2.table2_id.
> Consider adding a nonclustered index on T1(my_date,table2_id). This will
> prevent the (quite expensive) bookmark lookups.
> > The query plan for this simple query does an index seek on T1.my_date
> > as I expected then it does a bookmark lookup (presumably because it
> > needs T1.table2_id). It then includes parallelism, a hash, and then a
> > bitmap creation. Meanwhile, it does an index scan using an index on
> > Table2 that includes a single column that isn't even mentioned in the
> > query(?!?!). It then uses parallelism and does a hash match/inner
> > join.
> Apparently SQL-Server estimates that the parallel plan will be faster.
> If you expect differently, then you could add the hint OPTION (MAXDOP 1)
> to force the serial plan.
> Since the index on T2(table2_id) is clustered it is very wide at the
> page level. In this case, SQL-Server estimates that it is faster to scan
> a nonclustered index of table T2 (which also includes the clustered
> index key) than it is to seek (or partially scan) the clustered index
> for the estimated rows of the query.
> Hope this helps,
> Gert-Jan
> > I've done UPDATE STATISTICS using WITH FULLSCAN for both tables and
> > I've done a DBCC CHECKTABLE on both tables. Neither had any effect. I
> > also tried to force the query to use the clustered index for Table2.
> > For the simple query above it doesn't seem to help performance as the
> > clustered index scan has a very large cost to it (I'm not sure that I
> > entirely understand why). In the original query it helps substantially
> > though. Instead of joining the 6.5M records to a lookup table first it
> > joins it to Table1 first, which cuts down the number of records to the
> > 165K before going about with other joins.
> >
> > What I'm looking for is any advice on other things that I can look at
> > or any ideas on why SQL Server might be making these kinds of choices.
> > I would have thought that the simple query above would have performed
> > much better than it is currently (~30-35 seconds). I realize that
> > there has to be a bookmark lookup, but I was still expecting a quick
> > response from the server based on the indexes.
> >
> > Because of the table sizes, etc. I don't expect anyone to reproduce my
> > results, so please don't ask me to provide DDL for all of the tables
> > involved. If you have some ideas or even just guesses great, if not
> > then that's ok too.
> >
> > Thanks,
> > -Tom.|||Thanks for the suggestions. I had thought of the nonclustered index,
but while that would help with the pared down query that I came up
with, it wouldn't help with the underlying query because that one
involves a lot of additional columns. I'm still considering a covering
index, but I'm not sure why it should be necessary. Due to the number
of columns in the query as well as the number of rows in the table,
I'm a little hesitant to create a covering index.
I may try changing the clustered index for Table1. Right now it is on
an identity column (not my design...) that is also being used as a
surrogate primary key. It makes more sense to me to have that on the
date column since most reports run off of that date column and use
date ranges. This should prevent bookmark lookups for these large
groups of rows as well. When looking up by the primary key it is
usually to grab one row anyway.
Thanks!
-Tom.
Gert-Jan Strik <sorry@.toomuchspamalready.nl> wrote in message news:<419E4013.6DBC44D@.toomuchspamalready.nl>...
> Thomas, see inline
> "Thomas R. Hummel" wrote:
> >
> > Hello,
> >
> > A couple days ago one of our queries suddenly started to perform
> > abyssmally. The query is pretty straightforward - it joins several
> > tables all on foreign keys and includes a GROUP BY with COUNT(*). I
> > looked over the query plan and it looked a little odd so I tried
> > cutting the query down to see where the issue might be. I eventually
> > came up with the following:
> >
> > SELECT COUNT(*)
> > FROM Table1 T1
> > INNER JOIN Table2 T2 ON T2.table2_id = T1.table2_id
> > WHERE T1.my_date = '2004-11-18'
> >
> > The table and column names have been changed to protect the innocent,
> > but that is the exact format of the tables. Table1 has about 35M
> > records. Table2 has about 6.5M records. For the date in question,
> > Table1 has about 165K records.
> >
> > There is a non-clustered index on T1.my_date and there is a clustered
> > index on T2.table2_id.
> Consider adding a nonclustered index on T1(my_date,table2_id). This will
> prevent the (quite expensive) bookmark lookups.
> > The query plan for this simple query does an index seek on T1.my_date
> > as I expected then it does a bookmark lookup (presumably because it
> > needs T1.table2_id). It then includes parallelism, a hash, and then a
> > bitmap creation. Meanwhile, it does an index scan using an index on
> > Table2 that includes a single column that isn't even mentioned in the
> > query(?!?!). It then uses parallelism and does a hash match/inner
> > join.
> Apparently SQL-Server estimates that the parallel plan will be faster.
> If you expect differently, then you could add the hint OPTION (MAXDOP 1)
> to force the serial plan.
> Since the index on T2(table2_id) is clustered it is very wide at the
> page level. In this case, SQL-Server estimates that it is faster to scan
> a nonclustered index of table T2 (which also includes the clustered
> index key) than it is to seek (or partially scan) the clustered index
> for the estimated rows of the query.
> Hope this helps,
> Gert-Jan
> > I've done UPDATE STATISTICS using WITH FULLSCAN for both tables and
> > I've done a DBCC CHECKTABLE on both tables. Neither had any effect. I
> > also tried to force the query to use the clustered index for Table2.
> > For the simple query above it doesn't seem to help performance as the
> > clustered index scan has a very large cost to it (I'm not sure that I
> > entirely understand why). In the original query it helps substantially
> > though. Instead of joining the 6.5M records to a lookup table first it
> > joins it to Table1 first, which cuts down the number of records to the
> > 165K before going about with other joins.
> >
> > What I'm looking for is any advice on other things that I can look at
> > or any ideas on why SQL Server might be making these kinds of choices.
> > I would have thought that the simple query above would have performed
> > much better than it is currently (~30-35 seconds). I realize that
> > there has to be a bookmark lookup, but I was still expecting a quick
> > response from the server based on the indexes.
> >
> > Because of the table sizes, etc. I don't expect anyone to reproduce my
> > results, so please don't ask me to provide DDL for all of the tables
> > involved. If you have some ideas or even just guesses great, if not
> > then that's ok too.
> >
> > Thanks,
> > -Tom.

Poor Performing Query

Here is the situation: I have a process that auto-generates reports and e-mails them to end users (no, we are not allowed to use SQL Mail due to security issues and the fact that we are a Lotus shop). The process runs the query, saves the data to a text file and then mails the file as an attachment.

If the particular report to be run has multiple recipients, we make multiple passes (ie, we run the same query multiple times and send each recipient a "personalized" version of the report).

The issue is that one of my auto reports fails for just the first recipient. Subsequent recipients all receive the report normally, but the first recipient consistently fails to receive the data.

When I run the T-SQL multiple times in Query analyzer, I get the following results:

Pass 1: 87 seconds
Pass 2: 2 seconds
Pass 3: 3 seconds

I know that the process that we wrote (which is wrapped in a DLL) uses an ADO connection/command timeout setting of 60 seconds (both properties are set to the same value).

The SQL is not a stored proc. When I run the Index tuning wizard, no additional indexes are recommended.

I'm struggling to determine the next step.

1. I am loathe to increase the command timeout setting, since I have already done that once (from 30 seconds to one minute). I'm pretty sure this will only defer final resolution.

2. I suppose I could create a stored proc (so that it doesn't have to recompile the execution plan each time), but that would make this one report different from the hundreds of other reports that my super users have created; it would also mean that my super users would not be able to control the layout of the report without coming to me.

3. I have a feeling I should be using query hints or some such, but I am completely unfamiliar with these optimization tools.

Is there another approach that I am overlooking?

Regards,

Hugh ScottIf these are similar then I would guess that for the first call it has to read the data from disk. For subsequent calls the data is in memory so is much quicker.

Maybe put in a dummy call first?|||Yes, the calls are identical. I agree with you that on subsequent passes, the data is being read from memory. What has me puzzled is what I can do about it. I have considered putting in a dummy call first (as you suggested), but I wanted to try something a little more orthodox first.

Do you think that adding memory to the server would help? The server currently has 2.6 GB of memory. The database itself is a little over 30 GB now. I have run performance monitor on the server. While pages/sec does spike during this operation, it usually averages around 4.5. It does not seem sufficiently spiky to warrant $4,000 for additional memory.

Some more information:

There are three tables involved:

table a left join view b left join table c

Table a: 5.4 million rows (~ 4 GB)
View b: 1.3 million rows (~ 2.3 GB)
View c: 15 rows

View b uses the UNION operator and thus cannot be indexed.
Table a is indexed six ways from Sunday.

Thanks again,

Hugh Scott

Originally posted by nigelrivett
If these are similar then I would guess that for the first call it has to read the data from disk. For subsequent calls the data is in memory so is much quicker.

Maybe put in a dummy call first?|||What's your UNION view code? UNION queries are very handy, and I've used them many times myself, but just as often I've seen them used unnecessarily. Perhaps there is a more efficient method of combining your view code directly in your procedure code.

blindman|||UNION views can be indexed. Also, have you tried to see what you get on IO stats ON when doing a SELECT from just that view using WHERE that would match your JOIN?|||Views can be indexed, but unless you use a clustered index then the index must be recreated each time the view is called, and you lose the benefit of indexes on the underlying tables.

Clustered indexes on views can greatly boost performance, but they cannot be used on UNION queries. From books-online:

A view must meet these requirements before you can create a clustered index on it:
.
.
.
The SELECT statement in the view cannot contain these Transact-SQL syntax elements:
.
.
.UNION operator.

blindman|||blindman: thanks for the reminder. UNION is not allowed in indexed views. But you're also referring to non-clustered indexes. I haven't found any info that would suggest that a non-clustered index on a view needs to be rebuilt each time the view is called. Can you enlighten me here?

hmscott: adding memory will not resolve 87-second processing time on the first call. Adding indexes to base tables may.|||Normally the results of a view are not stored in the database, but if a clustered index is created on the view then the results are stored just like a table, and the values in the view are updated whenever the values in the underlying table are updated. (This of course adds overhead to processing changes on the underlying tables.) The clustered index is necessary in order to update the values on the view.

Long story short, if you don't use a clustered index then the results are not stored and thus any indexing must be recreated each time.

Honestly, I don't know whether non-clustered indexes are maintained on views that also have clustered indexes. I think I also read somewhere that you can't create an index on a view unless it also has a clustered index, so maybe UNION queries can't have indexes at all.

It's also possible that the optimizer might be smart enough to apply filters from the procedure to the underlying tables prior to creating the UNION view when it is called, but this probably depends on a lot of factors.

I've just seen too many instances when a UNION view was used instead of a more appropriate WHERE clause criteria.

blindmansql

Poor Performing Query

Hi
The answer to this one will be v simple for someone, alas my memory fails
me, what I am after is the term for whats I am experiencing. I have a stored
procedure which has a list of paramaters passed to it, one of these
parameters is set to NULL in the procedure header, within the code at the
begining is a IF NULL statement which then gives the variable a value. This
causes havoc with the execution plan as it believes a NULL value will be use
d
but the procedure uses the value assigned to in the IF NULL statement and th
e
time to execute can be significantly longer using the default. I am aware of
this and I know there is a term associated to this but for the life of me I
can't remember what its called.
anyoneParameter Sniffing:
http://www.google.co.uk/groups?as_e...lic.sqlserver.*
David Portas
SQL Server MVP
--|||"Parameter sniffing". The optimizer sniffs the parameter for the proc when i
t is to create a proc
plan. Apart from that, a DML statement has no context. When the optimizer lo
ok at a batch/proc it
only cares about the SELECT, INSERT, UPDATE ad DELETE statements. From that,
you can probably
realize that the SET command haven't executed and the value you passed to th
e proc is the one used
by the optimizer (parameter sniffing).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"MysticMart" <MysticMart@.discussions.microsoft.com> wrote in message
news:6A43A4CB-F8DE-41C8-B53E-FB830599E976@.microsoft.com...
> Hi
> The answer to this one will be v simple for someone, alas my memory fails
> me, what I am after is the term for whats I am experiencing. I have a stor
ed
> procedure which has a list of paramaters passed to it, one of these
> parameters is set to NULL in the procedure header, within the code at the
> begining is a IF NULL statement which then gives the variable a value. Thi
s
> causes havoc with the execution plan as it believes a NULL value will be u
sed
> but the procedure uses the value assigned to in the IF NULL statement and
the
> time to execute can be significantly longer using the default. I am aware
of
> this and I know there is a term associated to this but for the life of me
I
> can't remember what its called.
> anyone

Poor performance: SQL Server 2000 & Analysis Services with 20% aggregations

I would really appreaciate some advice:

We use SQL 2000 and want to move new hardware but we're experiecing performance problems on the new hardware compared to the old hardware.

Old hardware spec: Entry level intel server, 64b Pentium, 4GB RAM, 1TB Raid 5 SATA , 64 bit Windows 2003

New hardware:HP Intel Zeon Server 3.4 GhZ * 2, 64bit Windows 2003, 6GB RAM, Fibre Optic SAN - 1TG Raid 5

Software on both the same:SQL Server 2000, sp4, 32bit

Data volumes: 15GB DB, 12Cubes, 4virtual, 20dimensions-biggest leave member count of 350000

Problem: Huge performance difference between the entry level server and the new hardware. Huge improvement in performance on the new hardware when populating the DB and running the DTS packages, however, the munite we try and use aggregation the servers comes to an halt. The design of the DB is not our concern as it has been tried and tested many times over and has always given us realatively good performance.

Building the cubes with No aggregation: 4h on new. Then using 20% Aggregation: Indefinately on new vs 19h on old

Hi Jeanne,

check the AS-Properties in the AS-Manager-Tab > Process-Tab

Read-Ahead-Buffer should be 16 MB (or more?)

Process-Buffer-Size is on my machine 2000 MB (!)

Under Environment-Tab

Max Threads: 8

Minimum reserved Ram: 1024

My machine:

2 Xeon 3,6 GHz Hyperthreading, 4 GB RAM, Windows 2003 Enterprise Sp1, SQL 2000 SP4 32Bit (I am working in a governmental institution and MS Germany installed this box).

check this link: http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ansvcspg.mspx#EJEAE (Appendix D)

hth

Bernhard

Poor performance with NEWID()

We're experiencing very poor performance on successive runs of queries such
as the following:
'---
SET NOCOUNT ON
TRUNCATE TABLE tblSurveyTemp
INSERT INTO tblSurveyTemp (FirstName, LastName,
Email,Sex,Age,City,State,Country,ZipCode,Married,C hildrenAtHome,Education,Em
ploymentStatus,Occupation,Industry,Income,Ethnicit y,DateSent)
SELECT TOP 250 FirstName, LastName,
Email,Sex,Age,City,State,Country,ZipCode,Married,C hildrenAtHome,Education,Em
ploymentStatus,Occupation,Industry,Income,Ethnicit y,getdate()
FROM tblMember t2
WHERE Country IN ('U.S.') AND Age in ('13','20','30','40','50') AND Sex in
('F')
AND Not Exists ( Select email FROM tblSurvey t WHERE t.email=t2.email AND
t.survey='grainactivity')
ORDER BY NEWID()
SELECT FirstName,LastName,Email FROM tblSurveyTemp
'---
We're using the NEWID() function to randomize the sample. CPU is always at
100% when we run the query. The first time it runs successfully takes about
20 seconds. Second time, maybe 1 min. Third time it timed out.
Does anyone have any advice?
Thank you!!!
I dont have an answer, but a question. Why would you ever want to order by
newid()?
TIA,
ChrisR
"Dean J Garrett" wrote:

> We're experiencing very poor performance on successive runs of queries such
> as the following:
> '---
> SET NOCOUNT ON
> TRUNCATE TABLE tblSurveyTemp
> INSERT INTO tblSurveyTemp (FirstName, LastName,
> Email,Sex,Age,City,State,Country,ZipCode,Married,C hildrenAtHome,Education,Em
> ploymentStatus,Occupation,Industry,Income,Ethnicit y,DateSent)
> SELECT TOP 250 FirstName, LastName,
> Email,Sex,Age,City,State,Country,ZipCode,Married,C hildrenAtHome,Education,Em
> ploymentStatus,Occupation,Industry,Income,Ethnicit y,getdate()
> FROM tblMember t2
> WHERE Country IN ('U.S.') AND Age in ('13','20','30','40','50') AND Sex in
> ('F')
> AND Not Exists ( Select email FROM tblSurvey t WHERE t.email=t2.email AND
> t.survey='grainactivity')
> ORDER BY NEWID()
> SELECT FirstName,LastName,Email FROM tblSurveyTemp
> '---
>
> We're using the NEWID() function to randomize the sample. CPU is always at
> 100% when we run the query. The first time it runs successfully takes about
> 20 seconds. Second time, maybe 1 min. Third time it timed out.
> Does anyone have any advice?
> Thank you!!!
>
>
|||On Fri, 4 Nov 2005 14:12:01 -0800, ChrisR wrote:

>I dont have an answer, but a question. Why would you ever want to order by
>newid()?
Hi Chris,
The combination of TOP ... and ORDER BY NEWID() is often used to get a
pseudo-random sample. The ORDER BY NEWID() makes sure that the rows are
scrambled in an unpredictabable way; the TOP then takes only the few
rows that happen to be first.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||This method of choosing a random set of records (250 in your case) out of a
larger set of records is pretty effective when there are not many records in
the filtered select. In your case tblMember filtered by your where clause
must produce quite a few records. SQL Server has to pull those records
togther and sort them by the newid() value. Sorting a lot of records can take
a long time. To see how many records we're talking run:
SELECT COUNT(*)
FROM tblMember t2
WHERE Country IN ('U.S.') AND Age in ('13','20','30','40','50') AND Sex in
('F')
AND Not Exists ( Select email FROM tblSurvey t WHERE t.email=t2.email AND
t.survey='grainactivity')
In the end you may need to choose another method to choose your records
randomly.
Good luck!
-Phil
"Dean J Garrett" wrote:

> We're experiencing very poor performance on successive runs of queries such
> as the following:
> '---
> SET NOCOUNT ON
> TRUNCATE TABLE tblSurveyTemp
> INSERT INTO tblSurveyTemp (FirstName, LastName,
> Email,Sex,Age,City,State,Country,ZipCode,Married,C hildrenAtHome,Education,Em
> ploymentStatus,Occupation,Industry,Income,Ethnicit y,DateSent)
> SELECT TOP 250 FirstName, LastName,
> Email,Sex,Age,City,State,Country,ZipCode,Married,C hildrenAtHome,Education,Em
> ploymentStatus,Occupation,Industry,Income,Ethnicit y,getdate()
> FROM tblMember t2
> WHERE Country IN ('U.S.') AND Age in ('13','20','30','40','50') AND Sex in
> ('F')
> AND Not Exists ( Select email FROM tblSurvey t WHERE t.email=t2.email AND
> t.survey='grainactivity')
> ORDER BY NEWID()
> SELECT FirstName,LastName,Email FROM tblSurveyTemp
> '---
>
> We're using the NEWID() function to randomize the sample. CPU is always at
> 100% when we run the query. The first time it runs successfully takes about
> 20 seconds. Second time, maybe 1 min. Third time it timed out.
> Does anyone have any advice?
> Thank you!!!
>
>
|||Here is the article we used to figure out this technique
http://www.windowsitpro.com/Articles...842/19842.html but we don't
know if it is the best performing!
Thanks
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:B2EF1E86-E0AF-4FA3-A787-67C30B2AEA32@.microsoft.com...[vbcol=seagreen]
> I dont have an answer, but a question. Why would you ever want to order by
> newid()?
> --
> TIA,
> ChrisR
>
> "Dean J Garrett" wrote:
such[vbcol=seagreen]
Email,Sex,Age,City,State,Country,ZipCode,Married,C hildrenAtHome,Education,Em[vbcol=seagreen]
Email,Sex,Age,City,State,Country,ZipCode,Married,C hildrenAtHome,Education,Em[vbcol=seagreen]
in[vbcol=seagreen]
AND[vbcol=seagreen]
at[vbcol=seagreen]
about[vbcol=seagreen]
|||Very kinky.
I have no idea why you would get such differential results, if indeed
you are using the same parameters each time.
On a toy table, you can see that the newid() gets called BEFORE the
top function.
select top 10 * from mytable
order by newid()
StmtText
-----
|--Sort(TOP 10, ORDER BY

[Expr1002] ASC))
|--Compute Scalar(DEFINE

[Expr1002]=newid()))
|--Clustered Index Scan(OBJECT

[HaxPlans].[dbo].[MyTable].[PK_MyTable]))
So, I guess you can do a little hack like this:
select * from
(
select top 10 * from mytable
) x
order by newid()
And get fewer calls to newid()
StmtText
-----
|--Sort(ORDER BY

[Expr1002] ASC))
|--Compute Scalar(DEFINE

[Expr1002]=newid()))
|--Top(10)
|--Clustered Index Scan(OBJECT

[HaxPlans].[dbo].[MyTable].[PK_MyTable]))
Oh, wait a minute, you were doing an INSERT, maybe there is something
funky about the table you're inserting into? Maybe you're really
doing large numbers than 250?
But wait another minute, if you're doing an insert, WHY ARE YOU
ORDERING THE RECORDS ANYWAY? Is the destination table "flat", with no
indexes, just really an output buffer? Well, hmm, that should WORK,
and I still don't understand in that case especially why the
performance would vary so much. Just noodling around with it.
J.
On Fri, 4 Nov 2005 13:05:25 -0800, "Dean J Garrett" <info@.amuletc.com>
wrote:
>We're experiencing very poor performance on successive runs of queries such
>as the following:
>'---
>SET NOCOUNT ON
>TRUNCATE TABLE tblSurveyTemp
>INSERT INTO tblSurveyTemp (FirstName, LastName,
>Email,Sex,Age,City,State,Country,ZipCode,Married, ChildrenAtHome,Education,Em
>ploymentStatus,Occupation,Industry,Income,Ethnici ty,DateSent)
>SELECT TOP 250 FirstName, LastName,
>Email,Sex,Age,City,State,Country,ZipCode,Married, ChildrenAtHome,Education,Em
>ploymentStatus,Occupation,Industry,Income,Ethnici ty,getdate()
>FROM tblMember t2
>WHERE Country IN ('U.S.') AND Age in ('13','20','30','40','50') AND Sex in
>('F')
>AND Not Exists ( Select email FROM tblSurvey t WHERE t.email=t2.email AND
>t.survey='grainactivity')
>ORDER BY NEWID()
>SELECT FirstName,LastName,Email FROM tblSurveyTemp
>'---
>
>We're using the NEWID() function to randomize the sample. CPU is always at
>100% when we run the query. The first time it runs successfully takes about
>20 seconds. Second time, maybe 1 min. Third time it timed out.
>Does anyone have any advice?
>Thank you!!!
>
|||You just need to generate random number to select 250 random records. So a
variation of following query can be used for this purpose.
select au_id,au_lname, au_fname,
convert(smallint,rand() * ascii(left(au_lname,1)) *
ascii(right(au_lname,1))) % 77 value1 from authors
order by value1
The newid() creates a unique value of type uniqueidentifier which is a
16-byte binary values. Thus the filtered records are being sorted on a very
wide column. The query suggested by me will be sorted on a smallint data type
column which takes 2 bytes only.

Friday, March 30, 2012

Poor SQL Server Performance on Windows 2003 Server

Poor SQL Server Performance on Windows 2003 Server

Poor SQL Server Performance on Windows 2003 Server

Poor SQL Server Performance

Poor Server Performance When Running Data Driven Subscriptions

Poor query performance with: set statistics time on (SQL2K5)

Poor query performance with: set statistics time on (SQL2K5)

Poor query performance with: set statistics time on (SQL2K5)

poor preformance usinng sql 2005 with access 2003 application

Poor plan choice

Poor plan choice

Poor plan choice

Poor Performing Query

Poor Performing Query

Poor performance: SQL Server 2000 & Analysis Services with 20% aggregations

Poor performance with NEWID()

SQL compact edition

Blog Archive

About Me