Basic Functionality Questions

We've been trying to implement Reporting Services in our web application. We've run into 2 issues that I'd like to verify:

First, HTML rendering. In our application we provide an HTML Editor for formatting. The formatting does not render in the reporting services. I understand that's a known issue with no work around and no timeframe for resolution. Is that correct?

Second issue comes with tying is the Reporting Services together with our ASP.NET code. Do any of the Reporting Services controls allow you to bind to classes that support the IList Interface?

Thanks,

Frank

1) RS text box control does not render HTML. It supports standard formatting which applies to the entire contents of the textbox rather than individual bits of text as in a rich text box control.

2) RS reports are rendered based on the report definition and do not support binding custom datasets at runtime through the IList interface.

sql

Balancing scalability with availability

We are beginning to design a new application with SQL Server 2005. Our current production environment is slated to be two SQL Server 2005 machines with the databases residing on an EMC SAN. We have requirements to both have automatic failover between servers for availability and also be able to balance the load over two hot servers for scalability.

Can anyone point me in the right direction for things I need to consider in order to be able to implement both of these requirements? Can I implement database mirroring (for failover) and transactional replication (for balancing) given the hardware configuration I'v mentioned? Is more information needed? Where should I turn next?

I am coming off a mainly Oracle background for the last ten or so years with a smattering of SQL Server mixed in. I've tried to hit the ground running on this project, but sometimes find myself hitting the wall running instead.

Thanks,

Larry

Automatic failover can be accomplished with Database Mirroring, and clustering. With both of these technologies the mirrored server or the failover node will be offline so you can't access the databases on these mirrored servers - hence they won't deliver load balancing. For load balancing you could use replication assuming all the DML occurs on one server. Replication will not provide client failover however.

-- Hilary Cotter

|||

That is not correct.

The instance hosting the mirror database is NOT offline. It is online and running otherwise transactions would not be able to reach it. The mirror database is inaccessible, because it is in a recovering state.

As far as clustering goes, the secondary node is also not offline. It is online and functioning, otherwise, there would be no way to failover to the node. There is only one copy of the database within a cluster, so you can't use the secondary node to execute anything.

The combination that is being asked for - automatic failover along with scalability is not possible. Failover clustering will automatically failover, but is not a scalability solution. Database Mirroring running in high availability operating mode will automatically failover, but is not a scalability solution. However, if your applications are not coded to intercept the disconnect and then reconnect to the cluster, it doesn't matter if the cluster automatically fails over, because only those applications forming new connections will find it. If your applications are not coded to utilize the new connection library in ADO.NET that shipped with VS 2005, then your applications are not going to automatically failover to the mirror when it gets promoted.

Replication is your closest bet to getting both fault tolerance as well as scalability. If your applications are built with a connection library that can detect that the server they are connected to is offline and then reform a connection to a secondary server and continue issuing transactions, then you can accomplish the basic goals. Your application then handles the automatic failover while the replication engine keeps everything synchronized to allow scalability. The basic options here are transactional with queued updating subscribers and merge. I would NOT configure a bi-directional or peer-to-peer architecture unless your are going to have full control over your applications and can guarantee that you can never have a conflict generated.

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. :-)

BadImageFormatException in tablet application

I'm currently developing a tablet application which uses SQL Mobile 2005.

I added the reference to the System.Data.SqlServerCe DLL and copied all the necessary DLLs of the SQL Mobile SDK (from the Program Files\Microsoft SQL Sevrver 2005 Mobile Edition\Device\Mobile\v3.0\tablet directory) to my output directory.

But when my code hits the first SQL Mobile related line:

SqlCeEngine dbEngine = new SqlCeEngine();

I get an BadImageFormatException. This happens both on the Tablet and my development machine. I already reinstalled the SQL Mobile 2005 Device SDK, both nothing changed after that.

Any ideas why this fails? Is it a bug in the System.Data.SqlServerCe DLL or the SDK?

I fixed this issue myself. Apparently I needed to copy the DLLs from Program Files\Microsoft Visual Studio 8\Common7\IDE, opposed to the directory mentioned in my first post.

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 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...?

Bad characters in strings

Hi
We have a problem whereby a particular application is writing certain ascii
characters to our table which is causing an issue in another application.
Obviously the solution is to fix the application, but it's taking some time.
In the meantime I'm trying to develop a trigger to fix the data. I have a
table which contains a row for every ascii character which we do not want to
allow, I'm trying to develop a very efficient trigger to remove these bad
ascii characters (on around 7 string columns in 1 table)
Any hints on how to do this?
ThanksUse an INSTEAD OF trigger rather than an AFTER (the default), the INSTEAD OF
will do it before the insert/update occurrs thereby making it more
efficient, if you did it in the AFTER trigger (the default) then you'll be
doing an insert and then a further delete.
Use nested REPLACE to remove the characters, do you have a list of the
characters - are there a lot?
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"..." <...@.nowhere.com> wrote in message
news:O2Joviu5FHA.472@.TK2MSFTNGP15.phx.gbl...
> Hi
> We have a problem whereby a particular application is writing certain
> ascii characters to our table which is causing an issue in another
> application.
> Obviously the solution is to fix the application, but it's taking some
> time. In the meantime I'm trying to develop a trigger to fix the data. I
> have a table which contains a row for every ascii character which we do
> not want to allow, I'm trying to develop a very efficient trigger to
> remove these bad ascii characters (on around 7 string columns in 1 table)
> Any hints on how to do this?
> Thanks
>
>

Backward compatibility with AMO clients

We are an ISV whose application uses AMO (and ADOMD.NET) for manipulating SSAS cubes. I tried running our app against SQL Server 2008 (June CTP) and it failed (as I expected) because it is bound to the version of AMO that comes with SQL Server 2005 (9.0.x.x), and the assembly version numbers were bumped to 10.0.0.0 for 2008.

I installed the XMO libraries from the 2005 feature pack and tried those. The assemblies loaded, but when AMO tries to connect it gets the error:

An error occurred creating the OLAP database: Microsoft.AnalysisServices.ConnectionException: Cannot connect to Analysis Services version '10.0.1019.17'.
at Microsoft.AnalysisServices.Server.Connect(String connectionString, String sessionId)
at Microsoft.AnalysisServices.Server.Connect(String connectionString)
...

I tried adding binding redirects from the 9.0 version to the 10.0 version of the libraries, and that seemed to work (at least for the minimal testing I've done so far). Of course, I have no idea how compatible the 10.x and 9.x libraries actually are.

I'm curious what Microsoft's general philosophy around compatibility between 2005 clients and 2008 clients is going to be. What will be the officially recommended approach for supporting both platforms (assuming it is supported)?

Hi,

The problem with AMO version 9 being unable to connect to servers 10+ will be fixed in a next cumulative update for SP2.

Meanwhile, the work-around is the one you use: .config files to redirect from 9.0 to 10.0.

AMO version 10.0 is backward compatible, you can use it to connect to AS2005; BI Development Studio version 10.0 also connects to AS2005.

Thank you for posting the feedback,

Adrian Dumitrascu

|||Thanks Adrian.

Will there be a refresh for the Feature Pack components that include that fix? Or can rollups be applied to feature pack installs?

|||

The AS2005 Feature Pack will not be refreshed. The AMO fix will be included in the next Service Pack (if any) and sooner in the next cumulative update for SP2. If you cannot install the full cumulative update, it will work to just install to GAC the AMO (Microsoft.AnalysisServices.dll).

Adrian Dumitrascu

|||

Adrian,

Here's the scenario I'm concerned about. A customer installs our software for use with a server running SSAS 2008. They need the 9.x version of AMO (since that's what we've built against), and since the SQL Server 2008 client tools installs version 10, their only option is to download the Feature Pack components. However, the roll-up won't update components installed through the feature pack install (at least, the latest one doesn't - I tried it). At this point, the customer is hosed - they have no real way to get a version of AMO that works with SSAS2008. The only solutions are to install the binding redirects (ugly) or hack in a patched AMO client assembly (uglier).

Please consider this sort of scenario in your planning. Take pity on us poor ISVs.

|||

Hi,

I forwarded the message to our team, I'll post new info once I have it.

At the moment, none of the SP2 cumulative update builds at http://support.microsoft.com/kb/937137 have the AMO fix (the latest build is 09.00.3182 now). But the next build should have it (you can download it and install from it just the AMO assembly, this will be the work-around until we'll refresh the Feature Packs, if we'll be able to do that).

Adrian

Backward compatibility with AMO clients

We are an ISV whose application uses AMO (and ADOMD.NET) for manipulating SSAS cubes. I tried running our app against SQL Server 2008 (June CTP) and it failed (as I expected) because it is bound to the version of AMO that comes with SQL Server 2005 (9.0.x.x), and the assembly version numbers were bumped to 10.0.0.0 for 2008.

I installed the XMO libraries from the 2005 feature pack and tried those. The assemblies loaded, but when AMO tries to connect it gets the error:

An error occurred creating the OLAP database: Microsoft.AnalysisServices.ConnectionException: Cannot connect to Analysis Services version '10.0.1019.17'.
at Microsoft.AnalysisServices.Server.Connect(String connectionString, String sessionId)
at Microsoft.AnalysisServices.Server.Connect(String connectionString)
...

I tried adding binding redirects from the 9.0 version to the 10.0 version of the libraries, and that seemed to work (at least for the minimal testing I've done so far). Of course, I have no idea how compatible the 10.x and 9.x libraries actually are.

I'm curious what Microsoft's general philosophy around compatibility between 2005 clients and 2008 clients is going to be. What will be the officially recommended approach for supporting both platforms (assuming it is supported)?

Hi,

The problem with AMO version 9 being unable to connect to servers 10+ will be fixed in a next cumulative update for SP2.

Meanwhile, the work-around is the one you use: .config files to redirect from 9.0 to 10.0.

AMO version 10.0 is backward compatible, you can use it to connect to AS2005; BI Development Studio version 10.0 also connects to AS2005.

Thank you for posting the feedback,

Adrian Dumitrascu

|||Thanks Adrian.

Will there be a refresh for the Feature Pack components that include that fix? Or can rollups be applied to feature pack installs?

|||

The AS2005 Feature Pack will not be refreshed. The AMO fix will be included in the next Service Pack (if any) and sooner in the next cumulative update for SP2. If you cannot install the full cumulative update, it will work to just install to GAC the AMO (Microsoft.AnalysisServices.dll).

Adrian Dumitrascu

|||

Adrian,

Here's the scenario I'm concerned about. A customer installs our software for use with a server running SSAS 2008. They need the 9.x version of AMO (since that's what we've built against), and since the SQL Server 2008 client tools installs version 10, their only option is to download the Feature Pack components. However, the roll-up won't update components installed through the feature pack install (at least, the latest one doesn't - I tried it). At this point, the customer is hosed - they have no real way to get a version of AMO that works with SSAS2008. The only solutions are to install the binding redirects (ugly) or hack in a patched AMO client assembly (uglier).

Please consider this sort of scenario in your planning. Take pity on us poor ISVs.

|||

Hi,

I forwarded the message to our team, I'll post new info once I have it.

At the moment, none of the SP2 cumulative update builds at http://support.microsoft.com/kb/937137 have the AMO fix (the latest build is 09.00.3182 now). But the next build should have it (you can download it and install from it just the AMO assembly, this will be the work-around until we'll refresh the Feature Packs, if we'll be able to do that).

Adrian

Backup, restore database online and offline

Hi all,
I have an application running on internet, it uses SQL server datatase.
I have an question want to as: can i backup database and update database online or offline?
Thanks.
TungNTSure you can but it depens on what exactly are you going to do.