Basic Question on SQL SELECT Performance

Hi, I hope this is an appropriate group to ask a question about basic SQL
SELECT performance.
I'm creating a VIEW which draws together two tables; T_ONE and T_MANY.
Basically, the VIEW creates a LEFT JOIN from T_ONE to T_MANY so that the Ad
Hoc SQL submitted from the software is simply a SELECT * from the VIEW. (I
like to use views to keep software-embedded SQL as simple as possible.)
Now here's the thing. The item from the WHERE clause when selecting from
this view is from a column in T_MANY. Does this indicate that for
performance reasons I should place T_MANY on the left side of the JOIN? I've
always thought that relative JOIN placement defines logical join
relationship, but that the optimizer would intelligently decide upon the
quickest path to the data, regardless of where a particular table is placed
in the JOIN.
Disclaimer: I'm using a different DBMS right now, Interbase, and was getting
absolutely horrible performance until I switched T_MANY around to the left
side of the join. We're migrating to SQL Server and I'm wondering whether
what I'm seeing is actually a feature of SQL, or whether Interbase simply
doesn't 'have the brains' to optimize the query properly. Would this be a
problem in SQL Server as well? Would an INNER JOIN be more efficient than a
LEFT join (effectively, our relationship constraints mandate that these must
be INNER joins anyway - it's not possible for an entry to exist in either
table without at least one match in the other table) or should it make no
difference?
Thanks for your advice.
Joe GeretzDefintely Inner join is better then Outer Joins.
In the Query, is that you are only filtering the rows between the two tables
with a where clause or is it that you are trying to find out all the left
items and replacing them with values.
From my understanding you are trying to do a simple join. So please use
Inner Join and it should give you a good performance and make sure indexes
are done properly on the tables where the join is made. And if it contains
too many rows then collect statistics before you do the join.
Shyam
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:OxNKHB4DEHA.1544@.TK2MSFTNGP09.phx.gbl...
> Hi, I hope this is an appropriate group to ask a question about basic SQL
> SELECT performance.
> I'm creating a VIEW which draws together two tables; T_ONE and T_MANY.
> Basically, the VIEW creates a LEFT JOIN from T_ONE to T_MANY so that the
Ad
> Hoc SQL submitted from the software is simply a SELECT * from the VIEW. (I
> like to use views to keep software-embedded SQL as simple as possible.)
> Now here's the thing. The item from the WHERE clause when selecting from
> this view is from a column in T_MANY. Does this indicate that for
> performance reasons I should place T_MANY on the left side of the JOIN?
I've
> always thought that relative JOIN placement defines logical join
> relationship, but that the optimizer would intelligently decide upon the
> quickest path to the data, regardless of where a particular table is
placed
> in the JOIN.
> Disclaimer: I'm using a different DBMS right now, Interbase, and was
getting
> absolutely horrible performance until I switched T_MANY around to the left
> side of the join. We're migrating to SQL Server and I'm wondering whether
> what I'm seeing is actually a feature of SQL, or whether Interbase simply
> doesn't 'have the brains' to optimize the query properly. Would this be a
> problem in SQL Server as well? Would an INNER JOIN be more efficient than
a
> LEFT join (effectively, our relationship constraints mandate that these
must
> be INNER joins anyway - it's not possible for an entry to exist in either
> table without at least one match in the other table) or should it make no
> difference?
> Thanks for your advice.
> Joe Geretz
>
>

Balancing IO. File Groups vs Raid

I will be doing some performance testing on financial application next
month. Without going into a lot of details, I suspect I will have a
potential bottleneck when writing to the log file.

My hardware setup is a quad 2.8 Xeon Dell server direct attached to a
DELL/EMC CX200 (Fibre channel array with 10 X 30something GB, 15,000
rpm drives, with about 1GB of memory on the array for caching.

This is a benchmark environment, so I am not concerned about loosing
data. I am looking for a little guidance on using raid (0 or 10)
and/or file groups to spread IO to db objects (log file(s), data,
indexes, tempdb, etc). I have read about and played with file groups
enough to know that SQL server does some level of load balancing
across file, but am unclear it is in parallel or serialized.

Common wisdom seems to be to separate data, non-clustered index, logs,
and tempdb onto separate files, but I am unclear on how to make best
use of the high-speed disk array. I'd greatly appreciate opinions on
which would perform better; one file on a stripe set of N drives (raid
0 or 10), N files in a file group placed on N (non-striped) drives, or
a combination of the two? Is the answer the same for both log and data
(or index) files?

Thanks,
-Bernie"Bernie" <velivis@.ix.netcom.com> wrote in message
news:e707e6a.0312111018.475c199a@.posting.google.co m...
> I will be doing some performance testing on financial application next
> month. Without going into a lot of details, I suspect I will have a
> potential bottleneck when writing to the log file.
> My hardware setup is a quad 2.8 Xeon Dell server direct attached to a
> DELL/EMC CX200 (Fibre channel array with 10 X 30something GB, 15,000
> rpm drives, with about 1GB of memory on the array for caching.
> This is a benchmark environment, so I am not concerned about loosing
> data. I am looking for a little guidance on using raid (0 or 10)
> and/or file groups to spread IO to db objects (log file(s), data,
> indexes, tempdb, etc). I have read about and played with file groups
> enough to know that SQL server does some level of load balancing
> across file, but am unclear it is in parallel or serialized.
> Common wisdom seems to be to separate data, non-clustered index, logs,
> and tempdb onto separate files, but I am unclear on how to make best
> use of the high-speed disk array. I'd greatly appreciate opinions on
> which would perform better; one file on a stripe set of N drives (raid
> 0 or 10), N files in a file group placed on N (non-striped) drives, or
> a combination of the two? Is the answer the same for both log and data
> (or index) files?

Can't answer all of those (partly I'm not fully awake. :)

But you absolutely want your logs on RAID 1 or RAID 10, NOT RAID 0.
Remember, RAID 0 has no redundancy. If you lose your transaction log, you
lose data. Or more specifically, you lose your ability to recover up to the
most recent point in time.

Ideally, I'd probably dedicate some LUN to the Data (this lun can comprise
of a RAID 5, RAID 10, etc, depending on needs, disk space, etc. (I prefer
if I have the space to use RAID 10), have another for TEMPDB (though I've
always cheated on this and put it on my DATA disk, and then a separate RAID
10 for logs.

The best reason I can see for separate filegroups is to separate out indices
to their own drive (we do this) or to break out very large tables so that
you can use some more advanced backup schemes.

That's my 2 cents at 1:00 AM.

> Thanks,
> -Bernie|||The main things are that the transaction log shouldn't be on the same
disk (or array) as the data, and neither should the non-clustered
indexes.

Here's a setup I like for a six-drive box:

3 disks in a RAID-5 array for the data.
2 disks using RAID-1 (mirroring) for the transaction log.
1 disk for the non-clustered indexes.

My theory is if the singleton goes bad, we just reconstruct the
indexes, no big deal.

With more than six drives you can get fancier, e.g. have two data
files on separate RAID-5 arrays such that tables that are typically
joined in queries are not on the same array.

On 11 Dec 2003 10:18:37 -0800, velivis@.ix.netcom.com (Bernie) wrote:

>I will be doing some performance testing on financial application next
>month. Without going into a lot of details, I suspect I will have a
>potential bottleneck when writing to the log file.
>My hardware setup is a quad 2.8 Xeon Dell server direct attached to a
>DELL/EMC CX200 (Fibre channel array with 10 X 30something GB, 15,000
>rpm drives, with about 1GB of memory on the array for caching.
>This is a benchmark environment, so I am not concerned about loosing
>data. I am looking for a little guidance on using raid (0 or 10)
>and/or file groups to spread IO to db objects (log file(s), data,
>indexes, tempdb, etc). I have read about and played with file groups
>enough to know that SQL server does some level of load balancing
>across file, but am unclear it is in parallel or serialized.
>Common wisdom seems to be to separate data, non-clustered index, logs,
>and tempdb onto separate files, but I am unclear on how to make best
>use of the high-speed disk array. I'd greatly appreciate opinions on
>which would perform better; one file on a stripe set of N drives (raid
>0 or 10), N files in a file group placed on N (non-striped) drives, or
>a combination of the two? Is the answer the same for both log and data
>(or index) files?
>Thanks,
>-Bernie|||"Ellen K." <72322.enno.esspeeayem.1016@.compuserve.com> wrote in message
news:nqnntvk42k1b5fjq2ui7f8fa0huttaps42@.4ax.com...
> The main things are that the transaction log shouldn't be on the same
> disk (or array) as the data, and neither should the non-clustered
> indexes.
> Here's a setup I like for a six-drive box:
> 3 disks in a RAID-5 array for the data.
> 2 disks using RAID-1 (mirroring) for the transaction log.
> 1 disk for the non-clustered indexes.

Personally I would not do this (single disk for non-clustered indices).
Mostly because in my environment (others of course will vary) loss of the
indices will effectively make the DB useless from a DB point of view.
> My theory is if the singleton goes bad, we just reconstruct the
> indexes, no big deal.
> With more than six drives you can get fancier, e.g. have two data
> files on separate RAID-5 arrays such that tables that are typically
> joined in queries are not on the same array.|||If it happens and inconveniences the users, we're that much closer to
a SAN. :)

On Mon, 15 Dec 2003 03:57:12 GMT, "Greg D. Moore \(Strider\)"
<mooregr@.greenms.com> wrote:

>"Ellen K." <72322.enno.esspeeayem.1016@.compuserve.com> wrote in message
>news:nqnntvk42k1b5fjq2ui7f8fa0huttaps42@.4ax.com...
>> The main things are that the transaction log shouldn't be on the same
>> disk (or array) as the data, and neither should the non-clustered
>> indexes.
>>
>> Here's a setup I like for a six-drive box:
>>
>> 3 disks in a RAID-5 array for the data.
>> 2 disks using RAID-1 (mirroring) for the transaction log.
>> 1 disk for the non-clustered indexes.
>Personally I would not do this (single disk for non-clustered indices).
>Mostly because in my environment (others of course will vary) loss of the
>indices will effectively make the DB useless from a DB point of view.
>>
>> My theory is if the singleton goes bad, we just reconstruct the
>> indexes, no big deal.
>>
>> With more than six drives you can get fancier, e.g. have two data
>> files on separate RAID-5 arrays such that tables that are typically
>> joined in queries are not on the same array.|||"Ellen K." <72322.enno.esspeeayem.1016@.compuserve.com> wrote in message
news:bcgttvkt2u15bnfmlr9m6d1a1u6f2256k0@.4ax.com...
> If it happens and inconveniences the users, we're that much closer to
> a SAN. :)

Now see, thre I like how you think. :-)|||:)

Actually your previous post made me realize that I should organize
something the tech guys can use to make the database quickly reusable
in the event the singleton fails and I'm not around. So thank you!
:)

I think maybe I will create an empty filegroup "EMERGENCY" on the RAID
5 array, and make a script to drop all the nonclustered indexes from
the filegroup that's on the singleton and then recreate them in
EMERGENCY. Whaddya think?

On Tue, 16 Dec 2003 15:01:43 GMT, "Greg D. Moore \(Strider\)"
<mooregr@.greenms.com> wrote:

>"Ellen K." <72322.enno.esspeeayem.1016@.compuserve.com> wrote in message
>news:bcgttvkt2u15bnfmlr9m6d1a1u6f2256k0@.4ax.com...
>> If it happens and inconveniences the users, we're that much closer to
>> a SAN. :)
>>
>
>Now see, thre I like how you think. :-)

Bad Update Performance with very large recordsets

When updating a table with 80 million records we are experiencing very bad
times. We found using maxdop1 to keep the process in one thread helpful,
but something is still causing problems.
Updating one bigint field which is nullable to a bigint value from a table
inner joined with the current table is crawling.
Should we remove the nullability of the field and set it to a default of -1?
Would this ensure space is allocated for the field? What else can we look
for?
What about NO LOCK or Serializable? Could
What would be the *fastest* way to update records in a table assuming only 1
connection to the database? How do fields allowing NULLs affect this?
Thanks.Never try to update 80 million rows at once if you can avoid it. Wrap the
updates in smaller batches of say 10, 50 or 100K in size. If no one else
needs to use the table while this is happening you can use TABLOCK hint to
help.
--
Andrew J. Kelly
SQL Server MVP
"Kory Yingling" <kory@.removeme-mlsc.com> wrote in message
news:u$j1RcBbDHA.1744@.TK2MSFTNGP12.phx.gbl...
> When updating a table with 80 million records we are experiencing very bad
> times. We found using maxdop1 to keep the process in one thread helpful,
> but something is still causing problems.
> Updating one bigint field which is nullable to a bigint value from a table
> inner joined with the current table is crawling.
> Should we remove the nullability of the field and set it to a default
of -1?
> Would this ensure space is allocated for the field? What else can we look
> for?
> What about NO LOCK or Serializable? Could
> What would be the *fastest* way to update records in a table assuming only
1
> connection to the database? How do fields allowing NULLs affect this?
> Thanks.
>

Bad replication performance in SQL2k5 on links with high latency

I tested replication performance on slow network channels, for SQL2k and
SQL2k5. And quite disapplointed by SQL2k5 results...
Network channels were simulated, ping time 200 ms between distributor and
subscriber, unlimited bandwidth.
I tested how many transactions replication can deliver to subsciber. All
transactions are nearby same, they represent typical transactions in my
system.
Publisher, distributor and subsciber are all located on different computers.
In order to check capabilities of hardware, on each of the computers I run
tests to check speed of how many transactions per second it may insert into
database with turned on replication. For each computer, result was ~112
transactions per second, both for SQL2k and SQL2k5.
Next I started to measure replication delivery rate, how many transactions
per second distributor will be able to deliver to subscriber.
In SQL2k, replication in such condition was able to deliver 9 transactions
per second. The best result was obtained with 1 sec polling interval for log
reader and distribution agent, 64kb packet size for distribution agent. No
more differences from default profile.
In case if turn off network delay, rate of inserting transactions on
publisher become same as rate of delivering - replication start to deliver
~112 transactions per second.
For SQL2k5, results were around 5 transactions per second. The best result
was obtained on 1 sec polling interval for log reader and distribution agent,
64kb packet size for distribution agent, 3 SubscriptionStreams. No more
differences from default profile. If turn off network delays, it again
deliver at same rate as it inserting on publisher, about 112 transactions per
second.
Network delay simulator was tested several times to check is it really
simulate network delays as expected. All tests showed correct work of network
delay simulator. For simulating network delays, was used Shundra Cloud.
The question is - Is the replication in SQL2k5 really lost performance in
comparison with SQL2k? Is anything can be done to improve replication
delivery rate in SQL2k5?
the distribution agents in SQL 2005 has autosense network link technology
built into them. They will close down when they detect the link going down
and start up again when it is reconnected. I suspect you are bumping into
this.
I would open a support incident with MS to see if you can disable this
behavior or troubleshoot it further.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:D153A24B-63DF-45A6-A4B4-22A00221CA29@.microsoft.com...
>I tested replication performance on slow network channels, for SQL2k and
> SQL2k5. And quite disapplointed by SQL2k5 results...
> Network channels were simulated, ping time 200 ms between distributor and
> subscriber, unlimited bandwidth.
> I tested how many transactions replication can deliver to subsciber. All
> transactions are nearby same, they represent typical transactions in my
> system.
> Publisher, distributor and subsciber are all located on different
> computers.
> In order to check capabilities of hardware, on each of the computers I run
> tests to check speed of how many transactions per second it may insert
> into
> database with turned on replication. For each computer, result was ~112
> transactions per second, both for SQL2k and SQL2k5.
> Next I started to measure replication delivery rate, how many transactions
> per second distributor will be able to deliver to subscriber.
> In SQL2k, replication in such condition was able to deliver 9 transactions
> per second. The best result was obtained with 1 sec polling interval for
> log
> reader and distribution agent, 64kb packet size for distribution agent. No
> more differences from default profile.
> In case if turn off network delay, rate of inserting transactions on
> publisher become same as rate of delivering - replication start to deliver
> ~112 transactions per second.
> For SQL2k5, results were around 5 transactions per second. The best result
> was obtained on 1 sec polling interval for log reader and distribution
> agent,
> 64kb packet size for distribution agent, 3 SubscriptionStreams. No more
> differences from default profile. If turn off network delays, it again
> deliver at same rate as it inserting on publisher, about 112 transactions
> per
> second.
> Network delay simulator was tested several times to check is it really
> simulate network delays as expected. All tests showed correct work of
> network
> delay simulator. For simulating network delays, was used Shundra Cloud.
> The question is - Is the replication in SQL2k5 really lost performance in
> comparison with SQL2k? Is anything can be done to improve replication
> delivery rate in SQL2k5?
|||But network, during the tests, is not goes down - it is always work, at
constant ping time.
"Hilary Cotter" wrote:

> the distribution agents in SQL 2005 has autosense network link technology
> built into them. They will close down when they detect the link going down
> and start up again when it is reconnected. I suspect you are bumping into
> this.
> I would open a support incident with MS to see if you can disable this
> behavior or troubleshoot it further.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "andsm" <andsm@.discussions.microsoft.com> wrote in message
> news:D153A24B-63DF-45A6-A4B4-22A00221CA29@.microsoft.com...
>
>
|||I would open a support incident with Microsoft on this one.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:D9A495E2-6AD2-480E-B78F-A999A3781FC6@.microsoft.com...[vbcol=seagreen]
> But network, during the tests, is not goes down - it is always work, at
> constant ping time.
> "Hilary Cotter" wrote:

Bad print performance on HP Laserjet?

Hi,

I've finished working on reports (RS2000) and tested them on several printers, mostly OKI printers, which worked perfectly.

I've deployed these reports and now I'm getting calls from the customer that these reports are not good when performed on a HP Laserjet 2100:

- The print is processed very slow
- The text versus grid is not good aligned
- Every time a print job is submitted there has to be pressed on the button of the printer

I've suggested trying to install an other driver (PostScript, PCL 5.0, ...), but that didn't helped.

Is this a known bug? Is there a workaround here? Thank you.

As a follow-up: this problem was solved when I visited the customer and tried another printer driver in Windows XP.

So case closed

Bad print performance on HP Laserjet?

Hi,

I've finished working on reports (RS2000) and tested them on several printers, mostly OKI printers, which worked perfectly.

I've deployed these reports and now I'm getting calls from the customer that these reports are not good when performed on a HP Laserjet 2100:

- The print is processed very slow
- The text versus grid is not good aligned
- Every time a print job is submitted there has to be pressed on the button of the printer

I've suggested trying to install an other driver (PostScript, PCL 5.0, ...), but that didn't helped.

Is this a known bug? Is there a workaround here? Thank you.

As a follow-up: this problem was solved when I visited the customer and tried another printer driver in Windows XP.

So case closed

bad performance, help needed

Hi,
We have a working application that has been ok for some time but recently as
our data and users increased we started to get timeouts as some queries
take more than minute to execute. This happens only occasionally on a very
specific query that joins couple of big tables.
Now the queries are fairly complex and unoptimized but that is not what
bothers me. (also changing queries didn't help, may be something to do with
amounts of data involved?)
If I run the same query as SA it runs in a second. If I run it as a normal
user that has been given select permissions on the relevant tables it takes
ages. Are there any settings for normal users regarding limits on resource
or something like that?
We only support two users SA and TheUser as authentication is done server
side and everything is done through stored procedures spare the read
queries. TheUser is granted permissions to read some tables and to execute
the stored procedures nothing else.
Using the SQL Trace thing... I can see that the offending query does approx
350000 reads and 20 writes. I don't know what that means but thought it
might help you help me ;)
Thank you,
YordanIt is impossible to say without actually seeing the query and the DDL for
the tables involved but it sounds liek it is not using an index and scanning
the table at least once, probably more. Can you post the other info?
Andrew J. Kelly SQL MVP
"Yordan Gyurchev" <yg@.gyurchev.com> wrote in message
news:%234EzR%23czFHA.2312@.TK2MSFTNGP14.phx.gbl...
> Hi,
> We have a working application that has been ok for some time but recently
> as our data and users increased we started to get timeouts as some
> queries take more than minute to execute. This happens only occasionally
> on a very specific query that joins couple of big tables.
> Now the queries are fairly complex and unoptimized but that is not what
> bothers me. (also changing queries didn't help, may be something to do
> with amounts of data involved?)
> If I run the same query as SA it runs in a second. If I run it as a normal
> user that has been given select permissions on the relevant tables it
> takes ages. Are there any settings for normal users regarding limits on
> resource or something like that?
> We only support two users SA and TheUser as authentication is done server
> side and everything is done through stored procedures spare the read
> queries. TheUser is granted permissions to read some tables and to execute
> the stored procedures nothing else.
> Using the SQL Trace thing... I can see that the offending query does
> approx 350000 reads and 20 writes. I don't know what that means but
> thought it might help you help me ;)
> Thank you,
> Yordan
>
>|||Yordan,
Are you logging into Query Analyzer using SA and TheUser to make the
comparisons in time? Same T-SQL code? Are there any differences in the
execution plans chosen?
HTH
Jerry
"Yordan Gyurchev" <yg@.gyurchev.com> wrote in message
news:%234EzR%23czFHA.2312@.TK2MSFTNGP14.phx.gbl...
> Hi,
> We have a working application that has been ok for some time but recently
> as our data and users increased we started to get timeouts as some
> queries take more than minute to execute. This happens only occasionally
> on a very specific query that joins couple of big tables.
> Now the queries are fairly complex and unoptimized but that is not what
> bothers me. (also changing queries didn't help, may be something to do
> with amounts of data involved?)
> If I run the same query as SA it runs in a second. If I run it as a normal
> user that has been given select permissions on the relevant tables it
> takes ages. Are there any settings for normal users regarding limits on
> resource or something like that?
> We only support two users SA and TheUser as authentication is done server
> side and everything is done through stored procedures spare the read
> queries. TheUser is granted permissions to read some tables and to execute
> the stored procedures nothing else.
> Using the SQL Trace thing... I can see that the offending query does
> approx 350000 reads and 20 writes. I don't know what that means but
> thought it might help you help me ;)
> Thank you,
> Yordan
>
>|||If timeouts occur "only occasionally", then perhaps your problem is with
blocking:
INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems
http://support.microsoft.com/defaul...kb;EN-US;224453
Use SQL Server Profiler to verify that your SP is making good use of
indexes. Also, as your table gets larger, fragmentation may become an issue
that needs periodic maintenance.
How To: Use SQL Profiler
http://msdn.microsoft.com/library/d...
ethowto15.asp
Planning and Creating Indexes
http://www.microsoft.com/technet/pr...s/c0618260.mspx
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
"Yordan Gyurchev" <yg@.gyurchev.com> wrote in message
news:%234EzR%23czFHA.2312@.TK2MSFTNGP14.phx.gbl...
> Hi,
> We have a working application that has been ok for some time but recently
> as our data and users increased we started to get timeouts as some
> queries take more than minute to execute. This happens only occasionally
> on a very specific query that joins couple of big tables.
> Now the queries are fairly complex and unoptimized but that is not what
> bothers me. (also changing queries didn't help, may be something to do
> with amounts of data involved?)
> If I run the same query as SA it runs in a second. If I run it as a normal
> user that has been given select permissions on the relevant tables it
> takes ages. Are there any settings for normal users regarding limits on
> resource or something like that?
> We only support two users SA and TheUser as authentication is done server
> side and everything is done through stored procedures spare the read
> queries. TheUser is granted permissions to read some tables and to execute
> the stored procedures nothing else.
> Using the SQL Trace thing... I can see that the offending query does
> approx 350000 reads and 20 writes. I don't know what that means but
> thought it might help you help me ;)
> Thank you,
> Yordan
>
>|||Yordan...
How often do you reindex your database tables?
Yosh
"Yordan Gyurchev" <yg@.gyurchev.com> wrote in message
news:%234EzR%23czFHA.2312@.TK2MSFTNGP14.phx.gbl...
> Hi,
> We have a working application that has been ok for some time but recently
> as our data and users increased we started to get timeouts as some
> queries take more than minute to execute. This happens only occasionally
> on a very specific query that joins couple of big tables.
> Now the queries are fairly complex and unoptimized but that is not what
> bothers me. (also changing queries didn't help, may be something to do
> with amounts of data involved?)
> If I run the same query as SA it runs in a second. If I run it as a normal
> user that has been given select permissions on the relevant tables it
> takes ages. Are there any settings for normal users regarding limits on
> resource or something like that?
> We only support two users SA and TheUser as authentication is done server
> side and everything is done through stored procedures spare the read
> queries. TheUser is granted permissions to read some tables and to execute
> the stored procedures nothing else.
> Using the SQL Trace thing... I can see that the offending query does
> approx 350000 reads and 20 writes. I don't know what that means but
> thought it might help you help me ;)
> Thank you,
> Yordan
>
>|||Tables are not indexed... (yeah, I know)
I'll probably index to improve but I don't understand the difference the
actual user makes.Why would it work with SA? Is there any quota on resources
spared on one user? Session? I'd like to understand that...
Also same query runs fine most of the time but occasionally is taking >
minute.
its something of the sort (the actual query is too long):
select some stuff from table1 outer left join (select * from table 2) ON
(conditions) where other conditions
table1 is approx 40,000 rows
table2 is approx 600,000 rows
-Yordan
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OQ1SjFdzFHA.2652@.TK2MSFTNGP14.phx.gbl...
> It is impossible to say without actually seeing the query and the DDL for
> the tables involved but it sounds liek it is not using an index and
> scanning the table at least once, probably more. Can you post the other
> info?
> --
> Andrew J. Kelly SQL MVP
>
> "Yordan Gyurchev" <yg@.gyurchev.com> wrote in message
> news:%234EzR%23czFHA.2312@.TK2MSFTNGP14.phx.gbl...
>|||Thanks for the links. I'll try this tomorrow as I'm home now.
To be honest it does feel like a block because I've seen few before. Its
just I don't see how it can be this time.
Thanks,
Yordan
"JT" <someone@.microsoft.com> wrote in message
news:e%23daTHdzFHA.692@.TK2MSFTNGP10.phx.gbl...
> If timeouts occur "only occasionally", then perhaps your problem is with
> blocking:
> INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems
> http://support.microsoft.com/defaul...kb;EN-US;224453
> Use SQL Server Profiler to verify that your SP is making good use of
> indexes. Also, as your table gets larger, fragmentation may become an
> issue that needs periodic maintenance.
> How To: Use SQL Profiler
> http://msdn.microsoft.com/library/d...enethowto15.asp
> Planning and Creating Indexes
> http://www.microsoft.com/technet/pr...s/c0618260.mspx
> Microsoft SQL Server 2000 Index Defragmentation Best Practices
> [url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx[/ur
l]
>
> "Yordan Gyurchev" <yg@.gyurchev.com> wrote in message
> news:%234EzR%23czFHA.2312@.TK2MSFTNGP14.phx.gbl...
>|||I make my app dump the query in text. (so T-SQL code is the same).
Then I use the Query Analyzer to log-in and execute it. Using TheUser
outcomes are identical. With SA I've tried only the Analyser.
The difference in time is obvious. The exact times I get from the server
manager - using Trace (not sure about the name).
Thanks,
Yordan
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OnLPIGdzFHA.3256@.TK2MSFTNGP09.phx.gbl...
> Yordan,
> Are you logging into Query Analyzer using SA and TheUser to make the
> comparisons in time? Same T-SQL code? Are there any differences in the
> execution plans chosen?
> HTH
> Jerry
> "Yordan Gyurchev" <yg@.gyurchev.com> wrote in message
> news:%234EzR%23czFHA.2312@.TK2MSFTNGP14.phx.gbl...
>|||Here is an easy way to tell if it is a blocking problem. The next time a
query seems to be hung up, then run sp_who2. If a process has another
processes's SPID listed in the [blkby] column, then it is being blocked.
"Yordan Gyurchev" <yg@.gyurchev.com> wrote in message
news:%23u6dfXdzFHA.2348@.TK2MSFTNGP15.phx.gbl...
> Thanks for the links. I'll try this tomorrow as I'm home now.
> To be honest it does feel like a block because I've seen few before. Its
> just I don't see how it can be this time.
> Thanks,
> Yordan
> "JT" <someone@.microsoft.com> wrote in message
> news:e%23daTHdzFHA.692@.TK2MSFTNGP10.phx.gbl...
>|||Is it possible that since you run the same exact query as SA the execution
plan is already cached from the app and SQL server returns the result much
faster a second time
if the app user runs the same query twice does it take a long time both time
s?
http://sqlservercode.blogspot.com/
"Yordan Gyurchev" wrote:

> I make my app dump the query in text. (so T-SQL code is the same).
> Then I use the Query Analyzer to log-in and execute it. Using TheUser
> outcomes are identical. With SA I've tried only the Analyser.
> The difference in time is obvious. The exact times I get from the server
> manager - using Trace (not sure about the name).
> Thanks,
> Yordan
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:OnLPIGdzFHA.3256@.TK2MSFTNGP09.phx.gbl...
>
>

Bad performance with SQL Application when connecting from client

Hello,
I've a problem with a application witch uses SQL Server 2000 STD. Other
applications on the same SQL instance have no problems.
Windows 2000 SBS SP4
SQL Server 2000 SP3a
- Running the application from console or TS session on the SQL server is
fast.
- Named pipes connection or tcp/ip connection no difference
- shut down other applications on the server has no results in performance
- New installed test server with SBS 2000 gives no problem
- The performance of the server like not the problem.
Could you explain your question, please?
For example, what kind of conection do you use in the app.? , etc...
"GVWHIA" <GVWHIA@.discussions.microsoft.com> wrote in message
news:A6C849B2-C78B-4E66-B69E-0A888B7BDD70@.microsoft.com...
> Hello,
> I've a problem with a application witch uses SQL Server 2000 STD. Other
> applications on the same SQL instance have no problems.
> Windows 2000 SBS SP4
> SQL Server 2000 SP3a
> - Running the application from console or TS session on the SQL server is
> fast.
> - Named pipes connection or tcp/ip connection no difference
> - shut down other applications on the server has no results in performance
> - New installed test server with SBS 2000 gives no problem
> - The performance of the server like not the problem.
|||On Tue, 7 Dec 2004 06:53:03 -0800, GVWHIA
<GVWHIA@.discussions.microsoft.com> wrote:
>I've a problem with a application witch uses SQL Server 2000 STD. Other
>applications on the same SQL instance have no problems.
Could it be examining huge amounts of data that clog the network when
run remotely?
J.

Bad performance with SQL Application when connecting from client

Hello,
I've a problem with a application witch uses SQL Server 2000 STD. Other
applications on the same SQL instance have no problems.
Windows 2000 SBS SP4
SQL Server 2000 SP3a
- Running the application from console or TS session on the SQL server is
fast.
- Named pipes connection or tcp/ip connection no difference
- shut down other applications on the server has no results in performance
- New installed test server with SBS 2000 gives no problem
- The performance of the server like not the problem.Could you explain your question, please?
For example, what kind of conection do you use in the app.? , etc...
"GVWHIA" <GVWHIA@.discussions.microsoft.com> wrote in message
news:A6C849B2-C78B-4E66-B69E-0A888B7BDD70@.microsoft.com...
> Hello,
> I've a problem with a application witch uses SQL Server 2000 STD. Other
> applications on the same SQL instance have no problems.
> Windows 2000 SBS SP4
> SQL Server 2000 SP3a
> - Running the application from console or TS session on the SQL server is
> fast.
> - Named pipes connection or tcp/ip connection no difference
> - shut down other applications on the server has no results in performance
> - New installed test server with SBS 2000 gives no problem
> - The performance of the server like not the problem.|||On Tue, 7 Dec 2004 06:53:03 -0800, GVWHIA
<GVWHIA@.discussions.microsoft.com> wrote:
>I've a problem with a application witch uses SQL Server 2000 STD. Other
>applications on the same SQL instance have no problems.
Could it be examining huge amounts of data that clog the network when
run remotely?
J.

Bad performance in some questions (100% CPU)

Hi!
I have a database wich is about 40GB. One table is very big (and many rows),
about 20GB.
I can see in Sql Profiler that some questions are taking a few minutes to
end and during this time the processors are going 100% (sqlserver.exe).
After I did a maintenance job "Reorganize data and index pages" the same
questions ended in a few seconds only and no processor activity. Now a week
later I have the same problem. I cannot run the maintenance job "online"
because it's causing locks and heavy processor activity.
Can I solve this problem in any other way than "Reorganize data and index
pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it did not
help.
Regards
Johan
Johan
Identify a slow running queries. Get the execution plan of them and see
does the optimizer available to use index or it doesn't
"JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
news:496C6989-AD94-4BCE-A158-45D016F3A273@.microsoft.com...
> Hi!
> I have a database wich is about 40GB. One table is very big (and many
> rows),
> about 20GB.
> I can see in Sql Profiler that some questions are taking a few minutes to
> end and during this time the processors are going 100% (sqlserver.exe).
> After I did a maintenance job "Reorganize data and index pages" the same
> questions ended in a few seconds only and no processor activity. Now a
> week
> later I have the same problem. I cannot run the maintenance job "online"
> because it's causing locks and heavy processor activity.
> Can I solve this problem in any other way than "Reorganize data and index
> pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it did
> not
> help.
> --
> Regards
> Johan
|||I have identified a slow running query and run it in the execution plan.
The query (a join query) runs through 2 tables, both have one clustered
index and several non-clustered indexes.
Regards
Johan
"Uri Dimant" wrote:

> Johan
> Identify a slow running queries. Get the execution plan of them and see
> does the optimizer available to use index or it doesn't
>
>
> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> news:496C6989-AD94-4BCE-A158-45D016F3A273@.microsoft.com...
>
>
|||And the optimizer uses indexes to create an execution plan
"JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
news:9A0ADD64-D50C-4D0E-AD38-9B10A7FAEA1D@.microsoft.com...[vbcol=seagreen]
>I have identified a slow running query and run it in the execution plan.
> The query (a join query) runs through 2 tables, both have one clustered
> index and several non-clustered indexes.
> --
> Regards
> Johan
>
> "Uri Dimant" wrote:
|||Yes, it goes through 2 Indes Seeks, one for each table.
Regards
Johan
"Uri Dimant" wrote:

> And the optimizer uses indexes to create an execution plan
> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> news:9A0ADD64-D50C-4D0E-AD38-9B10A7FAEA1D@.microsoft.com...
>
>
|||Well, how much data does the query return ?
Perhaps , you need to re-design the indexes to be more efficient
Look at SET STATISTICS IO ON commad. (Logical reads)
"JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
news:21AD1D91-FFD9-468E-B0D6-B225FFC95118@.microsoft.com...[vbcol=seagreen]
> Yes, it goes through 2 Indes Seeks, one for each table.
> --
> Regards
> Johan
>
> "Uri Dimant" wrote:
|||The questions returns no data. 0 rows...
I did get this when I run with SET STATISTICS IO ON:
Table 'Worktable'. Scan count 6599, logical reads 158759, physical reads 0,
read-ahead reads 0.
Table 'Table1'. Scan count 16, logical reads 712, physical reads 0,
read-ahead reads 0.
Table 'Table2'. Scan count 688, logical reads 14286, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 7935, logical reads 188151, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 8680, logical reads 204541, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 9218, logical reads 216377, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 10121, logical reads 236243, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 9196, logical reads 215893, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 10211, logical reads 238223, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 11959, logical reads 276679, physical reads 0,
read-ahead reads 0.
Regards
Johan
"Uri Dimant" wrote:

> Well, how much data does the query return ?
> Perhaps , you need to re-design the indexes to be more efficient
> Look at SET STATISTICS IO ON commad. (Logical reads)
>
>
> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> news:21AD1D91-FFD9-468E-B0D6-B225FFC95118@.microsoft.com...
>
>
|||On Wed, 2 Nov 2005 04:26:14 -0800, "JohanSql"
<JohanSql@.discussions.microsoft.com> wrote:
>I have a database wich is about 40GB. One table is very big (and many rows),
>about 20GB.
>I can see in Sql Profiler that some questions are taking a few minutes to
>end and during this time the processors are going 100% (sqlserver.exe).
>After I did a maintenance job "Reorganize data and index pages" the same
>questions ended in a few seconds only and no processor activity. Now a week
>later I have the same problem. I cannot run the maintenance job "online"
>because it's causing locks and heavy processor activity.
>Can I solve this problem in any other way than "Reorganize data and index
>pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it did not
>help.
Seems odd that you go from a few seconds to several minutes.
I guess that many new records are added during the week?
Are many records also deleted?
What is the primary key on the big table?
Can you post the source for the query?
Josh
|||It would be useful to see the query plan, some DDL and the purpose of
the query.
Do you have foreign keys between the tables you are joining?
Does the query use parallelism during execution? What SQL-Server Service
pack are you running?
If the query plan is using loop joins, then it would mean that each
lookup requirs (on average) 24 logical reads. That does not sound
optimal. Index tuning could probably improve that.
Anyway, your post does not contain enough information to make an
educated guess, but just barely enough to make wild guesses.
Gert-Jan
JohanSql wrote:[vbcol=seagreen]
> The questions returns no data. 0 rows...
> I did get this when I run with SET STATISTICS IO ON:
> Table 'Worktable'. Scan count 6599, logical reads 158759, physical reads 0,
> read-ahead reads 0.
> Table 'Table1'. Scan count 16, logical reads 712, physical reads 0,
> read-ahead reads 0.
> Table 'Table2'. Scan count 688, logical reads 14286, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 7935, logical reads 188151, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 8680, logical reads 204541, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 9218, logical reads 216377, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 10121, logical reads 236243, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 9196, logical reads 215893, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 10211, logical reads 238223, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 11959, logical reads 276679, physical reads 0,
> read-ahead reads 0.
> --
> Regards
> Johan
> "Uri Dimant" wrote:

Bad performance in some questions (100% CPU)

Hi!
I have a database wich is about 40GB. One table is very big (and many rows),
about 20GB.
I can see in Sql Profiler that some questions are taking a few minutes to
end and during this time the processors are going 100% (sqlserver.exe).
After I did a maintenance job "Reorganize data and index pages" the same
questions ended in a few seconds only and no processor activity. Now a week
later I have the same problem. I cannot run the maintenance job "online"
because it's causing locks and heavy processor activity.
Can I solve this problem in any other way than "Reorganize data and index
pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it did not
help.
--
Regards
JohanJohan
Identify a slow running queries. Get the execution plan of them and see
does the optimizer available to use index or it doesn't
"JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
news:496C6989-AD94-4BCE-A158-45D016F3A273@.microsoft.com...
> Hi!
> I have a database wich is about 40GB. One table is very big (and many
> rows),
> about 20GB.
> I can see in Sql Profiler that some questions are taking a few minutes to
> end and during this time the processors are going 100% (sqlserver.exe).
> After I did a maintenance job "Reorganize data and index pages" the same
> questions ended in a few seconds only and no processor activity. Now a
> week
> later I have the same problem. I cannot run the maintenance job "online"
> because it's causing locks and heavy processor activity.
> Can I solve this problem in any other way than "Reorganize data and index
> pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it did
> not
> help.
> --
> Regards
> Johan|||I have identified a slow running query and run it in the execution plan.
The query (a join query) runs through 2 tables, both have one clustered
index and several non-clustered indexes.
--
Regards
Johan
"Uri Dimant" wrote:
> Johan
> Identify a slow running queries. Get the execution plan of them and see
> does the optimizer available to use index or it doesn't
>
>
> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> news:496C6989-AD94-4BCE-A158-45D016F3A273@.microsoft.com...
> > Hi!
> > I have a database wich is about 40GB. One table is very big (and many
> > rows),
> > about 20GB.
> > I can see in Sql Profiler that some questions are taking a few minutes to
> > end and during this time the processors are going 100% (sqlserver.exe).
> > After I did a maintenance job "Reorganize data and index pages" the same
> > questions ended in a few seconds only and no processor activity. Now a
> > week
> > later I have the same problem. I cannot run the maintenance job "online"
> > because it's causing locks and heavy processor activity.
> > Can I solve this problem in any other way than "Reorganize data and index
> > pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it did
> > not
> > help.
> > --
> > Regards
> > Johan
>
>|||And the optimizer uses indexes to create an execution plan
"JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
news:9A0ADD64-D50C-4D0E-AD38-9B10A7FAEA1D@.microsoft.com...
>I have identified a slow running query and run it in the execution plan.
> The query (a join query) runs through 2 tables, both have one clustered
> index and several non-clustered indexes.
> --
> Regards
> Johan
>
> "Uri Dimant" wrote:
>> Johan
>> Identify a slow running queries. Get the execution plan of them and see
>> does the optimizer available to use index or it doesn't
>>
>>
>> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
>> news:496C6989-AD94-4BCE-A158-45D016F3A273@.microsoft.com...
>> > Hi!
>> > I have a database wich is about 40GB. One table is very big (and many
>> > rows),
>> > about 20GB.
>> > I can see in Sql Profiler that some questions are taking a few minutes
>> > to
>> > end and during this time the processors are going 100% (sqlserver.exe).
>> > After I did a maintenance job "Reorganize data and index pages" the
>> > same
>> > questions ended in a few seconds only and no processor activity. Now a
>> > week
>> > later I have the same problem. I cannot run the maintenance job
>> > "online"
>> > because it's causing locks and heavy processor activity.
>> > Can I solve this problem in any other way than "Reorganize data and
>> > index
>> > pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it
>> > did
>> > not
>> > help.
>> > --
>> > Regards
>> > Johan
>>|||Yes, it goes through 2 Indes Seeks, one for each table.
--
Regards
Johan
"Uri Dimant" wrote:
> And the optimizer uses indexes to create an execution plan
> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> news:9A0ADD64-D50C-4D0E-AD38-9B10A7FAEA1D@.microsoft.com...
> >I have identified a slow running query and run it in the execution plan.
> > The query (a join query) runs through 2 tables, both have one clustered
> > index and several non-clustered indexes.
> > --
> > Regards
> > Johan
> >
> >
> > "Uri Dimant" wrote:
> >
> >> Johan
> >>
> >> Identify a slow running queries. Get the execution plan of them and see
> >> does the optimizer available to use index or it doesn't
> >>
> >>
> >>
> >>
> >>
> >> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> >> news:496C6989-AD94-4BCE-A158-45D016F3A273@.microsoft.com...
> >> > Hi!
> >> > I have a database wich is about 40GB. One table is very big (and many
> >> > rows),
> >> > about 20GB.
> >> > I can see in Sql Profiler that some questions are taking a few minutes
> >> > to
> >> > end and during this time the processors are going 100% (sqlserver.exe).
> >> > After I did a maintenance job "Reorganize data and index pages" the
> >> > same
> >> > questions ended in a few seconds only and no processor activity. Now a
> >> > week
> >> > later I have the same problem. I cannot run the maintenance job
> >> > "online"
> >> > because it's causing locks and heavy processor activity.
> >> > Can I solve this problem in any other way than "Reorganize data and
> >> > index
> >> > pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it
> >> > did
> >> > not
> >> > help.
> >> > --
> >> > Regards
> >> > Johan
> >>
> >>
> >>
>
>|||Well, how much data does the query return ?
Perhaps , you need to re-design the indexes to be more efficient
Look at SET STATISTICS IO ON commad. (Logical reads)
"JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
news:21AD1D91-FFD9-468E-B0D6-B225FFC95118@.microsoft.com...
> Yes, it goes through 2 Indes Seeks, one for each table.
> --
> Regards
> Johan
>
> "Uri Dimant" wrote:
>> And the optimizer uses indexes to create an execution plan
>> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
>> news:9A0ADD64-D50C-4D0E-AD38-9B10A7FAEA1D@.microsoft.com...
>> >I have identified a slow running query and run it in the execution plan.
>> > The query (a join query) runs through 2 tables, both have one clustered
>> > index and several non-clustered indexes.
>> > --
>> > Regards
>> > Johan
>> >
>> >
>> > "Uri Dimant" wrote:
>> >
>> >> Johan
>> >>
>> >> Identify a slow running queries. Get the execution plan of them and
>> >> see
>> >> does the optimizer available to use index or it doesn't
>> >>
>> >>
>> >>
>> >>
>> >>
>> >> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
>> >> news:496C6989-AD94-4BCE-A158-45D016F3A273@.microsoft.com...
>> >> > Hi!
>> >> > I have a database wich is about 40GB. One table is very big (and
>> >> > many
>> >> > rows),
>> >> > about 20GB.
>> >> > I can see in Sql Profiler that some questions are taking a few
>> >> > minutes
>> >> > to
>> >> > end and during this time the processors are going 100%
>> >> > (sqlserver.exe).
>> >> > After I did a maintenance job "Reorganize data and index pages" the
>> >> > same
>> >> > questions ended in a few seconds only and no processor activity. Now
>> >> > a
>> >> > week
>> >> > later I have the same problem. I cannot run the maintenance job
>> >> > "online"
>> >> > because it's causing locks and heavy processor activity.
>> >> > Can I solve this problem in any other way than "Reorganize data and
>> >> > index
>> >> > pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it
>> >> > did
>> >> > not
>> >> > help.
>> >> > --
>> >> > Regards
>> >> > Johan
>> >>
>> >>
>> >>
>>|||The questions returns no data. 0 rows...
I did get this when I run with SET STATISTICS IO ON:
Table 'Worktable'. Scan count 6599, logical reads 158759, physical reads 0,
read-ahead reads 0.
Table 'Table1'. Scan count 16, logical reads 712, physical reads 0,
read-ahead reads 0.
Table 'Table2'. Scan count 688, logical reads 14286, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 7935, logical reads 188151, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 8680, logical reads 204541, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 9218, logical reads 216377, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 10121, logical reads 236243, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 9196, logical reads 215893, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 10211, logical reads 238223, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 11959, logical reads 276679, physical reads 0,
read-ahead reads 0.
--
Regards
Johan
"Uri Dimant" wrote:
> Well, how much data does the query return ?
> Perhaps , you need to re-design the indexes to be more efficient
> Look at SET STATISTICS IO ON commad. (Logical reads)
>
>
> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> news:21AD1D91-FFD9-468E-B0D6-B225FFC95118@.microsoft.com...
> > Yes, it goes through 2 Indes Seeks, one for each table.
> > --
> > Regards
> > Johan
> >
> >
> > "Uri Dimant" wrote:
> >
> >> And the optimizer uses indexes to create an execution plan
> >>
> >> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> >> news:9A0ADD64-D50C-4D0E-AD38-9B10A7FAEA1D@.microsoft.com...
> >> >I have identified a slow running query and run it in the execution plan.
> >> > The query (a join query) runs through 2 tables, both have one clustered
> >> > index and several non-clustered indexes.
> >> > --
> >> > Regards
> >> > Johan
> >> >
> >> >
> >> > "Uri Dimant" wrote:
> >> >
> >> >> Johan
> >> >>
> >> >> Identify a slow running queries. Get the execution plan of them and
> >> >> see
> >> >> does the optimizer available to use index or it doesn't
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> >> >> news:496C6989-AD94-4BCE-A158-45D016F3A273@.microsoft.com...
> >> >> > Hi!
> >> >> > I have a database wich is about 40GB. One table is very big (and
> >> >> > many
> >> >> > rows),
> >> >> > about 20GB.
> >> >> > I can see in Sql Profiler that some questions are taking a few
> >> >> > minutes
> >> >> > to
> >> >> > end and during this time the processors are going 100%
> >> >> > (sqlserver.exe).
> >> >> > After I did a maintenance job "Reorganize data and index pages" the
> >> >> > same
> >> >> > questions ended in a few seconds only and no processor activity. Now
> >> >> > a
> >> >> > week
> >> >> > later I have the same problem. I cannot run the maintenance job
> >> >> > "online"
> >> >> > because it's causing locks and heavy processor activity.
> >> >> > Can I solve this problem in any other way than "Reorganize data and
> >> >> > index
> >> >> > pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it
> >> >> > did
> >> >> > not
> >> >> > help.
> >> >> > --
> >> >> > Regards
> >> >> > Johan
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||On Wed, 2 Nov 2005 04:26:14 -0800, "JohanSql"
<JohanSql@.discussions.microsoft.com> wrote:
>I have a database wich is about 40GB. One table is very big (and many rows),
>about 20GB.
>I can see in Sql Profiler that some questions are taking a few minutes to
>end and during this time the processors are going 100% (sqlserver.exe).
>After I did a maintenance job "Reorganize data and index pages" the same
>questions ended in a few seconds only and no processor activity. Now a week
>later I have the same problem. I cannot run the maintenance job "online"
>because it's causing locks and heavy processor activity.
>Can I solve this problem in any other way than "Reorganize data and index
>pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it did not
>help.
Seems odd that you go from a few seconds to several minutes.
I guess that many new records are added during the week?
Are many records also deleted?
What is the primary key on the big table?
Can you post the source for the query?
Josh|||It would be useful to see the query plan, some DDL and the purpose of
the query.
Do you have foreign keys between the tables you are joining?
Does the query use parallelism during execution? What SQL-Server Service
pack are you running?
If the query plan is using loop joins, then it would mean that each
lookup requirs (on average) 24 logical reads. That does not sound
optimal. Index tuning could probably improve that.
Anyway, your post does not contain enough information to make an
educated guess, but just barely enough to make wild guesses.
Gert-Jan
JohanSql wrote:
> The questions returns no data. 0 rows...
> I did get this when I run with SET STATISTICS IO ON:
> Table 'Worktable'. Scan count 6599, logical reads 158759, physical reads 0,
> read-ahead reads 0.
> Table 'Table1'. Scan count 16, logical reads 712, physical reads 0,
> read-ahead reads 0.
> Table 'Table2'. Scan count 688, logical reads 14286, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 7935, logical reads 188151, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 8680, logical reads 204541, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 9218, logical reads 216377, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 10121, logical reads 236243, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 9196, logical reads 215893, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 10211, logical reads 238223, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 11959, logical reads 276679, physical reads 0,
> read-ahead reads 0.
> --
> Regards
> Johan
> "Uri Dimant" wrote:
> > Well, how much data does the query return ?
> >
> > Perhaps , you need to re-design the indexes to be more efficient
> >
> > Look at SET STATISTICS IO ON commad. (Logical reads)
> >
> >
> >
> >
> > "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> > news:21AD1D91-FFD9-468E-B0D6-B225FFC95118@.microsoft.com...
> > > Yes, it goes through 2 Indes Seeks, one for each table.
> > > --
> > > Regards
> > > Johan
> > >
> > >
> > > "Uri Dimant" wrote:
> > >
> > >> And the optimizer uses indexes to create an execution plan
> > >>
> > >> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> > >> news:9A0ADD64-D50C-4D0E-AD38-9B10A7FAEA1D@.microsoft.com...
> > >> >I have identified a slow running query and run it in the execution plan.
> > >> > The query (a join query) runs through 2 tables, both have one clustered
> > >> > index and several non-clustered indexes.
> > >> > --
> > >> > Regards
> > >> > Johan
> > >> >
> > >> >
> > >> > "Uri Dimant" wrote:
> > >> >
> > >> >> Johan
> > >> >>
> > >> >> Identify a slow running queries. Get the execution plan of them and
> > >> >> see
> > >> >> does the optimizer available to use index or it doesn't
> > >> >>
> > >> >>
> > >> >>
> > >> >>
> > >> >>
> > >> >> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> > >> >> news:496C6989-AD94-4BCE-A158-45D016F3A273@.microsoft.com...
> > >> >> > Hi!
> > >> >> > I have a database wich is about 40GB. One table is very big (and
> > >> >> > many
> > >> >> > rows),
> > >> >> > about 20GB.
> > >> >> > I can see in Sql Profiler that some questions are taking a few
> > >> >> > minutes
> > >> >> > to
> > >> >> > end and during this time the processors are going 100%
> > >> >> > (sqlserver.exe).
> > >> >> > After I did a maintenance job "Reorganize data and index pages" the
> > >> >> > same
> > >> >> > questions ended in a few seconds only and no processor activity. Now
> > >> >> > a
> > >> >> > week
> > >> >> > later I have the same problem. I cannot run the maintenance job
> > >> >> > "online"
> > >> >> > because it's causing locks and heavy processor activity.
> > >> >> > Can I solve this problem in any other way than "Reorganize data and
> > >> >> > index
> > >> >> > pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it
> > >> >> > did
> > >> >> > not
> > >> >> > help.
> > >> >> > --
> > >> >> > Regards
> > >> >> > Johan
> > >> >>
> > >> >>
> > >> >>
> > >>
> > >>
> > >>
> >
> >
> >

Bad performance in some questions (100% CPU)

Hi!
I have a database wich is about 40GB. One table is very big (and many rows),
about 20GB.
I can see in Sql Profiler that some questions are taking a few minutes to
end and during this time the processors are going 100% (sqlserver.exe).
After I did a maintenance job "Reorganize data and index pages" the same
questions ended in a few seconds only and no processor activity. Now a week
later I have the same problem. I cannot run the maintenance job "online"
because it's causing locks and heavy processor activity.
Can I solve this problem in any other way than "Reorganize data and index
pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it did not
help.
--
Regards
JohanJohan
Identify a slow running queries. Get the execution plan of them and see
does the optimizer available to use index or it doesn't
"JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
news:496C6989-AD94-4BCE-A158-45D016F3A273@.microsoft.com...
> Hi!
> I have a database wich is about 40GB. One table is very big (and many
> rows),
> about 20GB.
> I can see in Sql Profiler that some questions are taking a few minutes to
> end and during this time the processors are going 100% (sqlserver.exe).
> After I did a maintenance job "Reorganize data and index pages" the same
> questions ended in a few seconds only and no processor activity. Now a
> week
> later I have the same problem. I cannot run the maintenance job "online"
> because it's causing locks and heavy processor activity.
> Can I solve this problem in any other way than "Reorganize data and index
> pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it did
> not
> help.
> --
> Regards
> Johan|||I have identified a slow running query and run it in the execution plan.
The query (a join query) runs through 2 tables, both have one clustered
index and several non-clustered indexes.
--
Regards
Johan
"Uri Dimant" wrote:

> Johan
> Identify a slow running queries. Get the execution plan of them and see
> does the optimizer available to use index or it doesn't
>
>
> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> news:496C6989-AD94-4BCE-A158-45D016F3A273@.microsoft.com...
>
>|||And the optimizer uses indexes to create an execution plan
"JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
news:9A0ADD64-D50C-4D0E-AD38-9B10A7FAEA1D@.microsoft.com...[vbcol=seagreen]
>I have identified a slow running query and run it in the execution plan.
> The query (a join query) runs through 2 tables, both have one clustered
> index and several non-clustered indexes.
> --
> Regards
> Johan
>
> "Uri Dimant" wrote:
>|||Yes, it goes through 2 Indes Seeks, one for each table.
--
Regards
Johan
"Uri Dimant" wrote:

> And the optimizer uses indexes to create an execution plan
> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> news:9A0ADD64-D50C-4D0E-AD38-9B10A7FAEA1D@.microsoft.com...
>
>|||Well, how much data does the query return ?
Perhaps , you need to re-design the indexes to be more efficient
Look at SET STATISTICS IO ON commad. (Logical reads)
"JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
news:21AD1D91-FFD9-468E-B0D6-B225FFC95118@.microsoft.com...[vbcol=seagreen]
> Yes, it goes through 2 Indes Seeks, one for each table.
> --
> Regards
> Johan
>
> "Uri Dimant" wrote:
>|||The questions returns no data. 0 rows...
I did get this when I run with SET STATISTICS IO ON:
Table 'Worktable'. Scan count 6599, logical reads 158759, physical reads 0,
read-ahead reads 0.
Table 'Table1'. Scan count 16, logical reads 712, physical reads 0,
read-ahead reads 0.
Table 'Table2'. Scan count 688, logical reads 14286, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 7935, logical reads 188151, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 8680, logical reads 204541, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 9218, logical reads 216377, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 10121, logical reads 236243, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 9196, logical reads 215893, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 10211, logical reads 238223, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 11959, logical reads 276679, physical reads 0,
read-ahead reads 0.
--
Regards
Johan
"Uri Dimant" wrote:

> Well, how much data does the query return ?
> Perhaps , you need to re-design the indexes to be more efficient
> Look at SET STATISTICS IO ON commad. (Logical reads)
>
>
> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> news:21AD1D91-FFD9-468E-B0D6-B225FFC95118@.microsoft.com...
>
>|||On Wed, 2 Nov 2005 04:26:14 -0800, "JohanSql"
<JohanSql@.discussions.microsoft.com> wrote:
>I have a database wich is about 40GB. One table is very big (and many rows)
,
>about 20GB.
>I can see in Sql Profiler that some questions are taking a few minutes to
>end and during this time the processors are going 100% (sqlserver.exe).
>After I did a maintenance job "Reorganize data and index pages" the same
>questions ended in a few seconds only and no processor activity. Now a week
>later I have the same problem. I cannot run the maintenance job "online"
>because it's causing locks and heavy processor activity.
>Can I solve this problem in any other way than "Reorganize data and index
>pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it did no
t
>help.
Seems odd that you go from a few seconds to several minutes.
I guess that many new records are added during the week?
Are many records also deleted?
What is the primary key on the big table?
Can you post the source for the query?
Josh|||It would be useful to see the query plan, some DDL and the purpose of
the query.
Do you have foreign keys between the tables you are joining?
Does the query use parallelism during execution? What SQL-Server Service
pack are you running?
If the query plan is using loop joins, then it would mean that each
lookup requirs (on average) 24 logical reads. That does not sound
optimal. Index tuning could probably improve that.
Anyway, your post does not contain enough information to make an
educated guess, but just barely enough to make wild guesses.
Gert-Jan
JohanSql wrote:[vbcol=seagreen]
> The questions returns no data. 0 rows...
> I did get this when I run with SET STATISTICS IO ON:
> Table 'Worktable'. Scan count 6599, logical reads 158759, physical reads 0
,
> read-ahead reads 0.
> Table 'Table1'. Scan count 16, logical reads 712, physical reads 0,
> read-ahead reads 0.
> Table 'Table2'. Scan count 688, logical reads 14286, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 7935, logical reads 188151, physical reads 0
,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 8680, logical reads 204541, physical reads 0
,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 9218, logical reads 216377, physical reads 0
,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 10121, logical reads 236243, physical reads
0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 9196, logical reads 215893, physical reads 0
,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 10211, logical reads 238223, physical reads
0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 11959, logical reads 276679, physical reads
0,
> read-ahead reads 0.
> --
> Regards
> Johan
> "Uri Dimant" wrote:
>

bad performance due to drivers?

Do you know if there are any performance issues using a sql server 3.70 driver on an application server versus using a 2000 version driver? The DB is on a server with SQL Server 2000 EE. We have four application servers and 2 have 2000 drivers (and window
s SP4) and the other 2 have 3.70 drivers (and windows SP3) and the ones with the 3.70 drivers keep getting timeouts. CPU and memory usage doesn't seem very high - does anyone know what else to monitor to see what could be affecting performance? Thanks.
One way to analyze the performance issues in your 7.0 environment would be
to enable SQL Server profiler and capture the execution plan, show plan
statistics, Started and completed events for RPC, SQL and Batch. We can
compare the results to SQL 2000.
Other factors (other than drivers) that could contribute to the
differences in the performance in the two environments are;
- SQL Server 2000 vs 7.0
- Data differences in the two environments
- Application differences
- Maybe network related issues
If you require assistance in analyzing the profiler data please contact
Microsoft Support.
HTH
Agnes Panosian
SQL Server Support
|||Here are some references that may also help
http://msdn.microsoft.com/library/de...us/dnsql7/html
/msdn_sql7perftune.asp
KB articles
INF: Job to Monitor SQL Server 7.0 Performance and Activity ID: 286191
HOW TO: Troubleshoot Application Performance with SQL Server ID: 224587
INF: How to Monitor SQL Server 7.0 Blocking ID: 251004.

bad performance due to drivers?

Do you know if there are any performance issues using a sql server 3.70 driver on an application server versus using a 2000 version driver? The DB is on a server with SQL Server 2000 EE. We have four application servers and 2 have 2000 drivers (and windows SP4) and the other 2 have 3.70 drivers (and windows SP3) and the ones with the 3.70 drivers keep getting timeouts. CPU and memory usage doesn't seem very high - does anyone know what else to monitor to see what could be affecting performance? Thanks.One way to analyze the performance issues in your 7.0 environment would be
to enable SQL Server profiler and capture the execution plan, show plan
statistics, Started and completed events for RPC, SQL and Batch. We can
compare the results to SQL 2000.
Other factors (other than drivers) that could contribute to the
differences in the performance in the two environments are;
- SQL Server 2000 vs 7.0
- Data differences in the two environments
- Application differences
- Maybe network related issues
If you require assistance in analyzing the profiler data please contact
Microsoft Support.
HTH
Agnes Panosian
SQL Server Support|||Here are some references that may also help
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql7/html
/msdn_sql7perftune.asp
KB articles
INF: Job to Monitor SQL Server 7.0 Performance and Activity ID: 286191
HOW TO: Troubleshoot Application Performance with SQL Server ID: 224587
INF: How to Monitor SQL Server 7.0 Blocking ID: 251004.

bad performance due to drivers?

Do you know if there are any performance issues using a sql server 3.70 driv
er on an application server versus using a 2000 version driver? The DB is on
a server with SQL Server 2000 EE. We have four application servers and 2 ha
ve 2000 drivers (and window
s SP4) and the other 2 have 3.70 drivers (and windows SP3) and the ones with
the 3.70 drivers keep getting timeouts. CPU and memory usage doesn't seem v
ery high - does anyone know what else to monitor to see what could be affect
ing performance? Thanks.One way to analyze the performance issues in your 7.0 environment would be
to enable SQL Server profiler and capture the execution plan, show plan
statistics, Started and completed events for RPC, SQL and Batch. We can
compare the results to SQL 2000.
Other factors (other than drivers) that could contribute to the
differences in the performance in the two environments are;
- SQL Server 2000 vs 7.0
- Data differences in the two environments
- Application differences
- Maybe network related issues
If you require assistance in analyzing the profiler data please contact
Microsoft Support.
HTH
Agnes Panosian
SQL Server Support|||Here are some references that may also help
http://msdn.microsoft.com/library/d...-us/dnsql7/html
/msdn_sql7perftune.asp
KB articles
INF: Job to Monitor SQL Server 7.0 Performance and Activity ID: 286191
HOW TO: Troubleshoot Application Performance with SQL Server ID: 224587
INF: How to Monitor SQL Server 7.0 Blocking ID: 251004.

bad excel performance / capacity question

a web application that i have recently deployed uses reporting services and exports data to excel reports with drill down options

i have reports that are being generated up to 5 MB and are not usable in excel (i.e. scrolling freezes, slow opening times, large file size)

the reason for this is the detail displayed for each record

each record has a drop down of approximatley 4-6 rows of detail

a report with 3000 lines will have those 4-6 rows of detail for each record

shouldn't excel be able to handle this?

the reports also take a long time to generate and i have had timeout issues, i was able to set the webservice that generates the reports to have no timeout to resolve this problem, my bigger concern is why excel performs so slowly and why the file size is so large

previously we ran these reports out of access, and instead of having the reporting services format, we displayed detail information across as columns and there was no performance issue with the same amount of data

the problems seems to be with out excel handles the (+) signs

Hi,

I also have this problem and it is a big problem according to my customers - I've contacted Microsoft via an incident.

The MS's answer was : the SSRS Excel renderer is the problem and it shall be solved in the next SQL Server version (completely rewritten). Not earlier, and there is no possibility to fix it now.

The only workaround I've found:

1. the main point is to open the exported excel file for the 1.time (you have simply to wait until it opens) and then copy-paste as values the whole sheet. save the file after this, the next time you open it you will see performance improvement.

2. maybe you can split the report into smaller parts - this means less data but more reports

3. you can modify the renderer by setting OMIT_FORMULAS to true in the renderer's config file (there will be no formulas exported, all is exported as values)

The only what really helps is the point 1.

Not only the (+) sign is the problem. I have a report exporting 7000 of rows in 20 columns and there is a problem too.

Hope this helps, if someone has more ideas please let me know, I would also appreciate it.

Jan

|||I would guess that you have a significant number of merged cells in your generated report, due to alignment issues in the RDL. Excel has known issues when loading a spreadsheet with many merge cells. The Excel Export in RS uses merged cells to maintain the layout defined in RDL, and if you have many rows, this can result in large numbers of merged cells.

Check out Chris's blog entry on merging cells and how to reduce them:
http://blogs.msdn.com/chrisbal/archive/2006/07/08/659545.aspx
|||

interesting...thanks for the answer

but there are no merged columns in my problematic excel file...and how is it possible that after copy-paste as values of the whole sheet becomes the opening and working with the excel file non-problematic...?

bad excel performance / capacity question

a web application that i have recently deployed uses reporting services and exports data to excel reports with drill down options

i have reports that are being generated up to 5 MB and are not usable in excel (i.e. scrolling freezes, slow opening times, large file size)

the reason for this is the detail displayed for each record

each record has a drop down of approximatley 4-6 rows of detail

a report with 3000 lines will have those 4-6 rows of detail for each record

shouldn't excel be able to handle this?

the reports also take a long time to generate and i have had timeout issues, i was able to set the webservice that generates the reports to have no timeout to resolve this problem, my bigger concern is why excel performs so slowly and why the file size is so large

previously we ran these reports out of access, and instead of having the reporting services format, we displayed detail information across as columns and there was no performance issue with the same amount of data

the problems seems to be with out excel handles the (+) signs

Hi,

I also have this problem and it is a big problem according to my customers - I've contacted Microsoft via an incident.

The MS's answer was : the SSRS Excel renderer is the problem and it shall be solved in the next SQL Server version (completely rewritten). Not earlier, and there is no possibility to fix it now.

The only workaround I've found:

1. the main point is to open the exported excel file for the 1.time (you have simply to wait until it opens) and then copy-paste as values the whole sheet. save the file after this, the next time you open it you will see performance improvement.

2. maybe you can split the report into smaller parts - this means less data but more reports

3. you can modify the renderer by setting OMIT_FORMULAS to true in the renderer's config file (there will be no formulas exported, all is exported as values)

The only what really helps is the point 1.

Not only the (+) sign is the problem. I have a report exporting 7000 of rows in 20 columns and there is a problem too.

Hope this helps, if someone has more ideas please let me know, I would also appreciate it.

Jan

|||I would guess that you have a significant number of merged cells in your generated report, due to alignment issues in the RDL. Excel has known issues when loading a spreadsheet with many merge cells. The Excel Export in RS uses merged cells to maintain the layout defined in RDL, and if you have many rows, this can result in large numbers of merged cells.

Check out Chris's blog entry on merging cells and how to reduce them:
http://blogs.msdn.com/chrisbal/archive/2006/07/08/659545.aspx|||

interesting...thanks for the answer

but there are no merged columns in my problematic excel file...and how is it possible that after copy-paste as values of the whole sheet becomes the opening and working with the excel file non-problematic...?