Performance table vs databases

What is the best thing to do to get the best performance ? Multiple tables
in 1 database or multiple databases containing less tables ?typically, database contain numerous tables.
not sure if your question is clear.....
Greg Jackson
PDX, Oregon|||Hi,
Keeping tables in mutiple database or viceversa will not give you
performance improvements.
Tips for Performance.
1. If you have mutiple disk controllers, Create file groups and place tge
tables and indexes in seperate file group. this will reduce I/O
2. Allot more physicval memory for SQL server
3. Allocate dedicated server for SQL server
4. Design the database in proper and structure manner
5. Create the necessory indexes
6. Optimize your SQL's (Select statements)
Make use of Query optimizer, Performance monitor, Profiler and Index tuning
wizard to tune your application and database.
Thanks
Hari
MCDBA
"Cris" <anonymous@.discussions.microsoft.com> wrote in message
news:D3F1AA37-12C0-4F1F-AEA2-9D4FB10AAB90@.microsoft.com...
> What is the best thing to do to get the best performance ? Multiple
tables in 1 database or multiple databases containing less tables ?|||I don't think performance is the question. There are several techniques to
handle large databases ... but you ask your self if you logically need/shoul
d create two+ databases. You wouldn't typically combine your Reporting with
Orders, right ?
What's the motivation behind your question ?

Performance question: View vs. Table

Hi all, I am new to the forum~

Suppose I have multiple tables, T1, T2, T3. I will use SELECT queries and apply AVG() and STDEV() on each of their columns, and average their results.

I can do this in two ways: one is to apply my SELECT multiple (3) times, then divide it by 3 in this case.

Another way is I create a View that UNIONS all T1 T2 T3, and apply AVG() and STDEV() on each columns.

Which solution is better? I mean, from a performance point of view. This is just a simplified version of my problem, and I would like to know what is the performance of using one (View) over the other (Table)... Does using View instead of table give me any performance overhead? Thanks everyone...They are the same solution. whether you use a select statment or a veiw you will be pulling data off the drives (or cache) to produce the answer. The view has the advantage of being pr-optimized. Views are generally used for this type of thing as it hides all the aggregation.

Don't know if this helped, Books Online has some interesting info look up views-SQL Server, overview and follow the hyper link to Scenarios for Using Views.sql

performance question/multiple keys

Hi All,
The table I'm using has full-text columns and also a date column thats
indexed. If I do a query on a date range and the full-text column does SQL
Server return the full-text rows first, then subset by date, or subset by
date and pass that set of rows to MSSEARCH?
I'm wondering about performance issues on tables with many rows (3 millions
or so). I'm wondering if i should break the data up into tables by day so
that i'm not doing full-text searches if I know that I'll be getting a small
subset according to a date range.
Any insight on this issue?
thanks,
John
Rows are first returned from MSSearch and then trimmed.
Partitioning is a good idea. However, how large are your results sets? If
they are small (i.e. under 500 rows) this should not be a problem.
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
"John Mott" <johnmott59@.hotmail.com> wrote in message
news:eBaJxILvFHA.3080@.tk2msftngp13.phx.gbl...
> Hi All,
> The table I'm using has full-text columns and also a date column thats
> indexed. If I do a query on a date range and the full-text column does SQL
> Server return the full-text rows first, then subset by date, or subset by
> date and pass that set of rows to MSSEARCH?
> I'm wondering about performance issues on tables with many rows (3
millions
> or so). I'm wondering if i should break the data up into tables by day so
> that i'm not doing full-text searches if I know that I'll be getting a
small
> subset according to a date range.
> Any insight on this issue?
> thanks,
> John
>
|||John,
First of all, it is always a good idea to get the SQL Server & OS platform
version info. Could you post the full output of SELECT @.@.version ?
Q. If I do a query on a date range and the full-text column does SQL Server
return the full-text rows first, then subset by date, or subset by date and
pass that set of rows to MSSEARCH?
A. SQL Server first queries the MSSearch service for all rows that match the
FTS query, then applies the WHERE clause filter after ALL results are
returned from the FT Catalog.
Yes, there can be performance issues with SQL Server 2000, but on which side
of the equation (FT Indexing &/or FT Search) - running a Full Population vs
running CONTAINS query are you concerned with? If the former, see the below
blog entry detailed resources.Also, review SQL Server 2000 BOL Title
"Full-text Search Recommendations". If the latter, you should review KB
article 240833 (Q240833) "FIX: Full-Text Search Performance Improved via
Support for TOP" and consider using the Top_N_by_Rank with either
CONTAINSTABLE or FREETEXTTABLE. If possible, partitioning the table into
smaller table can be helpful.
SQL Server 2000 Full-Text Search Resources and Links
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.entry
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"John Mott" <johnmott59@.hotmail.com> wrote in message
news:eBaJxILvFHA.3080@.tk2msftngp13.phx.gbl...
> Hi All,
> The table I'm using has full-text columns and also a date column thats
> indexed. If I do a query on a date range and the full-text column does SQL
> Server return the full-text rows first, then subset by date, or subset by
> date and pass that set of rows to MSSEARCH?
> I'm wondering about performance issues on tables with many rows (3
> millions
> or so). I'm wondering if i should break the data up into tables by day so
> that i'm not doing full-text searches if I know that I'll be getting a
> small
> subset according to a date range.
> Any insight on this issue?
> thanks,
> John
>

Performance question : One single MDF file ? Or multiple MDF file ?

Dear,
My friend told me : using multiple MDF files is much better than use one
single MDF file even within the same hard disk as SQL Server's multi thread
feature can handle multiple files concurrently.
My argument : Within the same hard disk, we should use one single MDF file,
but we can split the MDF file into small files if there are different
physical hard disks.
How do you feel ?
In order to have better performance, we should use 1 or multiple MDF files
(I mean within the same hard disk) ?
Any Microsoft documents supported ?
> My friend told me : using multiple MDF files is much better than use one
> single MDF file even within the same hard disk as SQL Server's multi
> thread
> feature can handle multiple files concurrently.
This is an urban legend:
http://blogs.msdn.com/psssql/archive/2007/02/21/sql-server-urban-legends-discussed.aspx.
Also, there should only be one MDF per database, which is the primary data
file. The file extention NDF should be used for the secondary

> My argument : Within the same hard disk, we should use one single MDF
> file,
> but we can split the MDF file into small files if there are different
> physical hard disks.
Yes, it's best to distribute the workload over as many disks as possible.
For large data files, you might want to create multiple files even if on a
single physical disk for manageability.
Hope this helps.
Dan Guzman
SQL Server MVP
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:4732f819$1@.127.0.0.1...
> Dear,
> My friend told me : using multiple MDF files is much better than use one
> single MDF file even within the same hard disk as SQL Server's multi
> thread
> feature can handle multiple files concurrently.
> My argument : Within the same hard disk, we should use one single MDF
> file,
> but we can split the MDF file into small files if there are different
> physical hard disks.
> How do you feel ?
> In order to have better performance, we should use 1 or multiple MDF files
> (I mean within the same hard disk) ?
> Any Microsoft documents supported ?
>
>
|||I would say that if you aren't seeing I/O issues (disk queue length, avg ms
per read/write, etc) with a single file then you can increase to more than
one file. Be aware however that when you do hit that break-over point and
have too many files, performance can decrease dramatically and quickly due
to disk head thrashing.
Assuming you have well-indexed structures, I would recommend first simply
moving all non-clustered indexes off to a separate file and see how that
works. You could also consider moving a very large table off to it's own
filegroup. But if you have that you really should be on multiple disks.
Also, what is your tempdb and transaction log file placement? If everything
is one same drive, separating those is job number 1.
BTW, you do have a baseline for your performance to compare to, right? :-))
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:4732f819$1@.127.0.0.1...
> Dear,
> My friend told me : using multiple MDF files is much better than use one
> single MDF file even within the same hard disk as SQL Server's multi
> thread
> feature can handle multiple files concurrently.
> My argument : Within the same hard disk, we should use one single MDF
> file,
> but we can split the MDF file into small files if there are different
> physical hard disks.
> How do you feel ?
> In order to have better performance, we should use 1 or multiple MDF files
> (I mean within the same hard disk) ?
> Any Microsoft documents supported ?
>
>
|||See also
http://sqlblog.com/blogs/linchi_shea/archive/2007/01/29/how-many-data-files-should-i-create-for-a-user-database.aspx
Linchi
"cpchan" wrote:

> Dear,
> My friend told me : using multiple MDF files is much better than use one
> single MDF file even within the same hard disk as SQL Server's multi thread
> feature can handle multiple files concurrently.
> My argument : Within the same hard disk, we should use one single MDF file,
> but we can split the MDF file into small files if there are different
> physical hard disks.
> How do you feel ?
> In order to have better performance, we should use 1 or multiple MDF files
> (I mean within the same hard disk) ?
> Any Microsoft documents supported ?
>
>
|||noted and thanks.
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:45ECE387-9152-4E2A-8B07-31572C29152B@.microsoft.com...
> See also
>
http://sqlblog.com/blogs/linchi_shea/archive/2007/01/29/how-many-data-files-
should-i-create-for-a-user-database.aspx[vbcol=seagreen]
> Linchi
> "cpchan" wrote:
thread[vbcol=seagreen]
file,[vbcol=seagreen]
files[vbcol=seagreen]
|||noted and thanks.
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13j64cso0q4stec@.corp.supernews.com...
> I would say that if you aren't seeing I/O issues (disk queue length, avg
ms
> per read/write, etc) with a single file then you can increase to more than
> one file. Be aware however that when you do hit that break-over point and
> have too many files, performance can decrease dramatically and quickly due
> to disk head thrashing.
> Assuming you have well-indexed structures, I would recommend first simply
> moving all non-clustered indexes off to a separate file and see how that
> works. You could also consider moving a very large table off to it's own
> filegroup. But if you have that you really should be on multiple disks.
> Also, what is your tempdb and transaction log file placement? If
everything
> is one same drive, separating those is job number 1.
> BTW, you do have a baseline for your performance to compare to, right?
:-))[vbcol=seagreen]
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "cpchan" <cpchaney@.netvigator.com> wrote in message
> news:4732f819$1@.127.0.0.1...
files
>
|||noted and thanks.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:5764EB3B-20BA-4663-8ADF-F7BDE6757654@.microsoft.com...
> This is an urban legend:
>
http://blogs.msdn.com/psssql/archive/2007/02/21/sql-server-urban-legends-dis
cussed.aspx.[vbcol=seagreen]
> Also, there should only be one MDF per database, which is the primary data
> file. The file extention NDF should be used for the secondary
>
> Yes, it's best to distribute the workload over as many disks as possible.
> For large data files, you might want to create multiple files even if on a
> single physical disk for manageability.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "cpchan" <cpchaney@.netvigator.com> wrote in message
> news:4732f819$1@.127.0.0.1...
files
>

Performance question : One single MDF file ? Or multiple MDF file ?

Dear,
My friend told me : using multiple MDF files is much better than use one
single MDF file even within the same hard disk as SQL Server's multi thread
feature can handle multiple files concurrently.
My argument : Within the same hard disk, we should use one single MDF file,
but we can split the MDF file into small files if there are different
physical hard disks.
How do you feel ?
In order to have better performance, we should use 1 or multiple MDF files
(I mean within the same hard disk) ?
Any Microsoft documents supported ?> My friend told me : using multiple MDF files is much better than use one
> single MDF file even within the same hard disk as SQL Server's multi
> thread
> feature can handle multiple files concurrently.
This is an urban legend:
http://blogs.msdn.com/psssql/archive/2007/02/21/sql-server-urban-legends-discussed.aspx.
Also, there should only be one MDF per database, which is the primary data
file. The file extention NDF should be used for the secondary
> My argument : Within the same hard disk, we should use one single MDF
> file,
> but we can split the MDF file into small files if there are different
> physical hard disks.
Yes, it's best to distribute the workload over as many disks as possible.
For large data files, you might want to create multiple files even if on a
single physical disk for manageability.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:4732f819$1@.127.0.0.1...
> Dear,
> My friend told me : using multiple MDF files is much better than use one
> single MDF file even within the same hard disk as SQL Server's multi
> thread
> feature can handle multiple files concurrently.
> My argument : Within the same hard disk, we should use one single MDF
> file,
> but we can split the MDF file into small files if there are different
> physical hard disks.
> How do you feel ?
> In order to have better performance, we should use 1 or multiple MDF files
> (I mean within the same hard disk) ?
> Any Microsoft documents supported ?
>
>|||I would say that if you aren't seeing I/O issues (disk queue length, avg ms
per read/write, etc) with a single file then you can increase to more than
one file. Be aware however that when you do hit that break-over point and
have too many files, performance can decrease dramatically and quickly due
to disk head thrashing.
Assuming you have well-indexed structures, I would recommend first simply
moving all non-clustered indexes off to a separate file and see how that
works. You could also consider moving a very large table off to it's own
filegroup. But if you have that you really should be on multiple disks.
Also, what is your tempdb and transaction log file placement? If everything
is one same drive, separating those is job number 1.
BTW, you do have a baseline for your performance to compare to, right' :-))
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:4732f819$1@.127.0.0.1...
> Dear,
> My friend told me : using multiple MDF files is much better than use one
> single MDF file even within the same hard disk as SQL Server's multi
> thread
> feature can handle multiple files concurrently.
> My argument : Within the same hard disk, we should use one single MDF
> file,
> but we can split the MDF file into small files if there are different
> physical hard disks.
> How do you feel ?
> In order to have better performance, we should use 1 or multiple MDF files
> (I mean within the same hard disk) ?
> Any Microsoft documents supported ?
>
>|||See also
http://sqlblog.com/blogs/linchi_shea/archive/2007/01/29/how-many-data-files-should-i-create-for-a-user-database.aspx
Linchi
"cpchan" wrote:
> Dear,
> My friend told me : using multiple MDF files is much better than use one
> single MDF file even within the same hard disk as SQL Server's multi thread
> feature can handle multiple files concurrently.
> My argument : Within the same hard disk, we should use one single MDF file,
> but we can split the MDF file into small files if there are different
> physical hard disks.
> How do you feel ?
> In order to have better performance, we should use 1 or multiple MDF files
> (I mean within the same hard disk) ?
> Any Microsoft documents supported ?
>
>|||noted and thanks.
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:45ECE387-9152-4E2A-8B07-31572C29152B@.microsoft.com...
> See also
>
http://sqlblog.com/blogs/linchi_shea/archive/2007/01/29/how-many-data-files-
should-i-create-for-a-user-database.aspx
> Linchi
> "cpchan" wrote:
> > Dear,
> >
> > My friend told me : using multiple MDF files is much better than use one
> > single MDF file even within the same hard disk as SQL Server's multi
thread
> > feature can handle multiple files concurrently.
> >
> > My argument : Within the same hard disk, we should use one single MDF
file,
> > but we can split the MDF file into small files if there are different
> > physical hard disks.
> >
> > How do you feel ?
> >
> > In order to have better performance, we should use 1 or multiple MDF
files
> > (I mean within the same hard disk) ?
> >
> > Any Microsoft documents supported ?
> >
> >
> >
> >|||noted and thanks.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:5764EB3B-20BA-4663-8ADF-F7BDE6757654@.microsoft.com...
> > My friend told me : using multiple MDF files is much better than use one
> > single MDF file even within the same hard disk as SQL Server's multi
> > thread
> > feature can handle multiple files concurrently.
> This is an urban legend:
>
http://blogs.msdn.com/psssql/archive/2007/02/21/sql-server-urban-legends-dis
cussed.aspx.
> Also, there should only be one MDF per database, which is the primary data
> file. The file extention NDF should be used for the secondary
> > My argument : Within the same hard disk, we should use one single MDF
> > file,
> > but we can split the MDF file into small files if there are different
> > physical hard disks.
> Yes, it's best to distribute the workload over as many disks as possible.
> For large data files, you might want to create multiple files even if on a
> single physical disk for manageability.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "cpchan" <cpchaney@.netvigator.com> wrote in message
> news:4732f819$1@.127.0.0.1...
> > Dear,
> >
> > My friend told me : using multiple MDF files is much better than use one
> > single MDF file even within the same hard disk as SQL Server's multi
> > thread
> > feature can handle multiple files concurrently.
> >
> > My argument : Within the same hard disk, we should use one single MDF
> > file,
> > but we can split the MDF file into small files if there are different
> > physical hard disks.
> >
> > How do you feel ?
> >
> > In order to have better performance, we should use 1 or multiple MDF
files
> > (I mean within the same hard disk) ?
> >
> > Any Microsoft documents supported ?
> >
> >
> >
>|||noted and thanks.
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13j64cso0q4stec@.corp.supernews.com...
> I would say that if you aren't seeing I/O issues (disk queue length, avg
ms
> per read/write, etc) with a single file then you can increase to more than
> one file. Be aware however that when you do hit that break-over point and
> have too many files, performance can decrease dramatically and quickly due
> to disk head thrashing.
> Assuming you have well-indexed structures, I would recommend first simply
> moving all non-clustered indexes off to a separate file and see how that
> works. You could also consider moving a very large table off to it's own
> filegroup. But if you have that you really should be on multiple disks.
> Also, what is your tempdb and transaction log file placement? If
everything
> is one same drive, separating those is job number 1.
> BTW, you do have a baseline for your performance to compare to, right'
:-))
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "cpchan" <cpchaney@.netvigator.com> wrote in message
> news:4732f819$1@.127.0.0.1...
> > Dear,
> >
> > My friend told me : using multiple MDF files is much better than use one
> > single MDF file even within the same hard disk as SQL Server's multi
> > thread
> > feature can handle multiple files concurrently.
> >
> > My argument : Within the same hard disk, we should use one single MDF
> > file,
> > but we can split the MDF file into small files if there are different
> > physical hard disks.
> >
> > How do you feel ?
> >
> > In order to have better performance, we should use 1 or multiple MDF
files
> > (I mean within the same hard disk) ?
> >
> > Any Microsoft documents supported ?
> >
> >
> >
>sql

Performance question : One single MDF file ? Or multiple MDF file ?

Dear,
My friend told me : using multiple MDF files is much better than use one
single MDF file even within the same hard disk as SQL Server's multi thread
feature can handle multiple files concurrently.
My argument : Within the same hard disk, we should use one single MDF file,
but we can split the MDF file into small files if there are different
physical hard disks.
How do you feel ?
In order to have better performance, we should use 1 or multiple MDF files
(I mean within the same hard disk) ?
Any Microsoft documents supported ?> My friend told me : using multiple MDF files is much better than use one
> single MDF file even within the same hard disk as SQL Server's multi
> thread
> feature can handle multiple files concurrently.
This is an urban legend:
http://blogs.msdn.com/psssql/archiv...d
.aspx.
Also, there should only be one MDF per database, which is the primary data
file. The file extention NDF should be used for the secondary

> My argument : Within the same hard disk, we should use one single MDF
> file,
> but we can split the MDF file into small files if there are different
> physical hard disks.
Yes, it's best to distribute the workload over as many disks as possible.
For large data files, you might want to create multiple files even if on a
single physical disk for manageability.
Hope this helps.
Dan Guzman
SQL Server MVP
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:4732f819$1@.127.0.0.1...
> Dear,
> My friend told me : using multiple MDF files is much better than use one
> single MDF file even within the same hard disk as SQL Server's multi
> thread
> feature can handle multiple files concurrently.
> My argument : Within the same hard disk, we should use one single MDF
> file,
> but we can split the MDF file into small files if there are different
> physical hard disks.
> How do you feel ?
> In order to have better performance, we should use 1 or multiple MDF files
> (I mean within the same hard disk) ?
> Any Microsoft documents supported ?
>
>|||I would say that if you aren't seeing I/O issues (disk queue length, avg ms
per read/write, etc) with a single file then you can increase to more than
one file. Be aware however that when you do hit that break-over point and
have too many files, performance can decrease dramatically and quickly due
to disk head thrashing.
Assuming you have well-indexed structures, I would recommend first simply
moving all non-clustered indexes off to a separate file and see how that
works. You could also consider moving a very large table off to it's own
filegroup. But if you have that you really should be on multiple disks.
Also, what is your tempdb and transaction log file placement? If everything
is one same drive, separating those is job number 1.
BTW, you do have a baseline for your performance to compare to, right' :-))
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:4732f819$1@.127.0.0.1...
> Dear,
> My friend told me : using multiple MDF files is much better than use one
> single MDF file even within the same hard disk as SQL Server's multi
> thread
> feature can handle multiple files concurrently.
> My argument : Within the same hard disk, we should use one single MDF
> file,
> but we can split the MDF file into small files if there are different
> physical hard disks.
> How do you feel ?
> In order to have better performance, we should use 1 or multiple MDF files
> (I mean within the same hard disk) ?
> Any Microsoft documents supported ?
>
>|||noted and thanks.
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13j64cso0q4stec@.corp.supernews.com...
> I would say that if you aren't seeing I/O issues (disk queue length, avg
ms
> per read/write, etc) with a single file then you can increase to more than
> one file. Be aware however that when you do hit that break-over point and
> have too many files, performance can decrease dramatically and quickly due
> to disk head thrashing.
> Assuming you have well-indexed structures, I would recommend first simply
> moving all non-clustered indexes off to a separate file and see how that
> works. You could also consider moving a very large table off to it's own
> filegroup. But if you have that you really should be on multiple disks.
> Also, what is your tempdb and transaction log file placement? If
everything
> is one same drive, separating those is job number 1.
> BTW, you do have a baseline for your performance to compare to, right'
:-))
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "cpchan" <cpchaney@.netvigator.com> wrote in message
> news:4732f819$1@.127.0.0.1...
files[vbcol=seagreen]
>|||noted and thanks.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:5764EB3B-20BA-4663-8ADF-F7BDE6757654@.microsoft.com...
> This is an urban legend:
>
http://blogs.msdn.com/psssql/archiv...ban-legends-dis
cussed.aspx.
> Also, there should only be one MDF per database, which is the primary data
> file. The file extention NDF should be used for the secondary
>
> Yes, it's best to distribute the workload over as many disks as possible.
> For large data files, you might want to create multiple files even if on a
> single physical disk for manageability.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "cpchan" <cpchaney@.netvigator.com> wrote in message
> news:4732f819$1@.127.0.0.1...
files[vbcol=seagreen]
>

Performance problems when using multiple SqlConnection objects?

Hello,

I am planning to use three databases on the local SQL Server. DB1 will be for App1, DB2 for App2, and DB3 will hold tables, that both, App1 and App2 need to access.

Obviously, I need in App1 and App2 at least two SqlConnection and SqlCommand objects, one that access their respective databases, and one that will access DB3.

Will this result in a worse performance? (The using of two SqlConnection objects in one aspx page as example, and switching them)

Should I for better performance get rid of DB3 and add the tables there to DB1 and DB2 ? (Thus multiplying data, but, if the performance will be much better, it will be fine with me)

I would be thankful for any advices.

Hello my friend,

Try and use the same database where possible. By using the same database, and therefore the same connection string, the built-in connection pooling will work and therefore conserve resources for you.

Kind regards

Scotty

|||Thanks|||

ask_Scotty:

Hello my friend,

Try and use the same database where possible. By using the same database, and therefore the same connection string, the built-in connection pooling will work and therefore conserve resources for you.

Kind regards

Scotty

Thanks again, but, the more I am thinking about this and your advice, the more confused I am.

The Databases are all on the same machine and on the same instance. Will pooling stop working, or be inefficent, if I use a second SqlConnection object with anohter connection string in a aspx page? In the source code, I usualy close connections immediately, the two will not be open at the same time.

I am still somewhat unsure what to do.

|||

Hello again my friend,

Connecting to different databases, if necessary, from your web pages should be fine. If you can restrict the number of times you need to connect, this would be better. To understand what I mean, read up on page caching and especially data caching in ASP.NET applications. Basically, data caching involves connecting to the database on the first request for the data and then caching it to service subsequent requests as opposed to connecting to the database each time. Caching methods and strategies form a huge subject but one worth getting into.

Kind regards

Scotty

Performance of multiple charts on a report (RenderStream issue with RS2000)

I have a 'questionnaire analysis' report. The report consists of approx
25-30 questions and each question has a bar chart next to it to
show the average score out of five for each multiple choice answer
within a question.

The report ends up containing 25-30 bar graphs all contained within a
List control. The report performance is acceptable using URL access but
is exceptionally slow (well over 1 minute) when using SOAP access.

The performance bottle-neck seems to be the multiple calls to
RenderStream to render all of the images that get generated when the
report executes (one image for each chart.)

I was wondering if there was any way, still using SOAP, of avoiding
using RenderStream or of speeding up performance. Report caching is not
really an option in my scenario nor is splitting into multiple pages
(user wants everything on screen at once.).

Jon

I have a similar issue. I have a report that reports the movement of various different items that are purchased from our company. The process involves the running of a VB application that extracts, calculates and generates data on a reporting table. Then, the application runs the Reporting services reports and renders them to PDF files. The PDF files are then to be e-mailed to several managers at the company. Of the three reports, two of them only have one Graph. These have multiple pages with the graph at the top of the first page. Those two reports generate the one graph and multiple detail pages just fine.

However, the third report does a page break by item with a graph for each item at the top of its respective page. I am only getting the first page generated through the Print preview in Visual studio or rendered to the PDF file through the VB application. When I view the report through the web interface, I get all of the page breaks and graphs as expected. Several of the managers want to store these e-mails in outlook for future reference, but if they don't contain all of the data, it isn't much help to them.

Any help would be greatly appreciated. Thanks! - Eric -

|||Some more information for you all. When I tried to export the same report from the web interface to PDF, this failed to export anything beyond the first page as well. Now my management is really getting restless about this not working. HELP!

Performance of multiple charts on a report (RenderStream issue with RS2000)

I have a 'questionnaire analysis' report. The report consists of approx
25-30 questions and each question has a bar chart next to it to
show the average score out of five for each multiple choice answer
within a question.

The report ends up containing 25-30 bar graphs all contained within a
List control. The report performance is acceptable using URL access but
is exceptionally slow (well over 1 minute) when using SOAP access.

The performance bottle-neck seems to be the multiple calls to
RenderStream to render all of the images that get generated when the
report executes (one image for each chart.)

I was wondering if there was any way, still using SOAP, of avoiding
using RenderStream or of speeding up performance. Report caching is not
really an option in my scenario nor is splitting into multiple pages
(user wants everything on screen at once.).

Jon

I have a similar issue. I have a report that reports the movement of various different items that are purchased from our company. The process involves the running of a VB application that extracts, calculates and generates data on a reporting table. Then, the application runs the Reporting services reports and renders them to PDF files. The PDF files are then to be e-mailed to several managers at the company. Of the three reports, two of them only have one Graph. These have multiple pages with the graph at the top of the first page. Those two reports generate the one graph and multiple detail pages just fine.

However, the third report does a page break by item with a graph for each item at the top of its respective page. I am only getting the first page generated through the Print preview in Visual studio or rendered to the PDF file through the VB application. When I view the report through the web interface, I get all of the page breaks and graphs as expected. Several of the managers want to store these e-mails in outlook for future reference, but if they don't contain all of the data, it isn't much help to them.

Any help would be greatly appreciated. Thanks! - Eric -

|||Some more information for you all. When I tried to export the same report from the web interface to PDF, this failed to export anything beyond the first page as well. Now my management is really getting restless about this not working. HELP!

Performance of multiple bar charts per report (RS2000)

I have a 'questionnaire analysis' report. The report consists of approx
25-30 questions and each question has a bar chart attached to it to
show the average score out of five for each multiple choice answer
within a question.
The report ends up containing 25-30 bar graphs all contained within a
List control. The report performance is acceptable using URL access but
is exceptionally slow (over 1 minute) when using SOAP access.
The performance bottle-neck seems to be the multiple calls to
RenderStream to render all of the images that get generated when the
report executes (one for each chart.)
I was wondering if there was any way, still using SOAP, of avoiding
using RenderStream or of speeding up performance. Report caching is not
really an option in my scenario nor is splitting into multiple pages
(user wants everything on screen at once.).
JonYou could make the calls asynchronous (I'm assuming all calls are
synchronous).
"jonny" <jon@.jongianni.com> wrote in message
news:1147337806.062580.126100@.u72g2000cwu.googlegroups.com...
>I have a 'questionnaire analysis' report. The report consists of approx
> 25-30 questions and each question has a bar chart attached to it to
> show the average score out of five for each multiple choice answer
> within a question.
> The report ends up containing 25-30 bar graphs all contained within a
> List control. The report performance is acceptable using URL access but
> is exceptionally slow (over 1 minute) when using SOAP access.
> The performance bottle-neck seems to be the multiple calls to
> RenderStream to render all of the images that get generated when the
> report executes (one for each chart.)
> I was wondering if there was any way, still using SOAP, of avoiding
> using RenderStream or of speeding up performance. Report caching is not
> really an option in my scenario nor is splitting into multiple pages
> (user wants everything on screen at once.).
> Jon
>

Performance Monitoring

Hi All
I am running web applications over multiple database and web servers. Each
web application runs on a Web Server and connects to a seperate Database
Server. As more web applications are created, we have to purchase more web
and database servers as the systems slow down. The problem is that we don't
really know if we need more database or more web servers. Does anyone know a
good tool for monitoring web AND database servers to help us to decide when
to buy new servers?
We are running windows 2003 (IIS 6) and SQL Server 2000.
Thanx in advance
Joe
Well Profiler & Perfmon should allow you to see how SQL Server is
performing. IF you want 3rd party tools you might want to look at Quest for
SQL Server. Veritas has a product line called i3 that will monitor all
levels of the app. From the web server to the db and give you plenty of
reports etc. to show where problems may be occurring. But they do cost a
few dollars.
Andrew J. Kelly SQL MVP
"Joe Zammit" <zammit_joe@.hotmail.com> wrote in message
news:eQj6zKsjFHA.2920@.TK2MSFTNGP14.phx.gbl...
> Hi All
> I am running web applications over multiple database and web servers. Each
> web application runs on a Web Server and connects to a seperate Database
> Server. As more web applications are created, we have to purchase more web
> and database servers as the systems slow down. The problem is that we
> don't really know if we need more database or more web servers. Does
> anyone know a good tool for monitoring web AND database servers to help us
> to decide when to buy new servers?
> We are running windows 2003 (IIS 6) and SQL Server 2000.
> Thanx in advance
> Joe
>
|||I work for a company that might be able to help you or is partners with
companies who could. The website is www.gomez.com. Check it out. Just
thought it might help.
|||Cheers, I didn't think about built in SQL Server Tools. I shall try those
first.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ulh9B8sjFHA.4028@.TK2MSFTNGP10.phx.gbl...
> Well Profiler & Perfmon should allow you to see how SQL Server is
> performing. IF you want 3rd party tools you might want to look at Quest
> for SQL Server. Veritas has a product line called i3 that will monitor
> all levels of the app. From the web server to the db and give you plenty
> of reports etc. to show where problems may be occurring. But they do cost
> a few dollars.
> --
> Andrew J. Kelly SQL MVP
>
> "Joe Zammit" <zammit_joe@.hotmail.com> wrote in message
> news:eQj6zKsjFHA.2920@.TK2MSFTNGP14.phx.gbl...
>
|||Check for http://www.agileinfollc.com DataStudio, it has a good performance
monitoring facility under Performance node.
"Joe Zammit" <zammit_joe@.hotmail.com> wrote in message
news:eQj6zKsjFHA.2920@.TK2MSFTNGP14.phx.gbl...
> Hi All
> I am running web applications over multiple database and web servers. Each
> web application runs on a Web Server and connects to a seperate Database
> Server. As more web applications are created, we have to purchase more web
> and database servers as the systems slow down. The problem is that we
> don't really know if we need more database or more web servers. Does
> anyone know a good tool for monitoring web AND database servers to help us
> to decide when to buy new servers?
> We are running windows 2003 (IIS 6) and SQL Server 2000.
> Thanx in advance
> Joe
>

Performance Monitor Counters with Multiple Instances

I have a server that has 2 instances of SQL Server 2000. The first is the default instance and the second is a named instance. When I go to Performance Monitor to view counters, I can only find counters for the named instance. Is ther a way to view counters for both/all instances?

Thanks in advance for your help.

Bob

IS that default instance sql services are started?

What is the OS version? Service pack?
When some counters disapear, what counters are still there?
Do you start perfmon first (remotely or localy) and then SQL Server?
Do you change the service account for the SQL Server instances while perfmon is running (remotely or localy)
When counters disapear do you see any rows with select * from sysperfinfo? If you get no rows you should check the errorlog for the instances affected.

|||

Thanks for the response Satya:

IS that default instance sql services are started? >> Yes

What is the OS version? Service pack? >> Not Sure
When some counters disapear, what counters are still there? >> Counters don't "disappear" - they are never there
Do you start perfmon first (remotely or localy) and then SQL Server? >> SQL Server(s) are running first
Do you change the service account for the SQL Server instances while perfmon is running (remotely or localy) >> No
When counters disapear do you see any rows with select * from sysperfinfo? >> Again, counters don't "disappear" - yes I get 700+ rows

If you get no rows you should check the errorlog for the instances affected.

|||I believe still there is much information needed to assess, check the service pack level on SQL and operating system. BTW are there any clustered instances?|||

Thanks again Satya...

I am trying to get my Windows Admin to get the information on the operating system. The instances are not clustered. Was there a problem with performance counters for a given SP?

Bob

Performance Monitor Counters with Multiple Instances

I have a server that has 2 instances of SQL Server 2000. The first is the default instance and the second is a named instance. When I go to Performance Monitor to view counters, I can only find counters for the named instance. Is ther a way to view counters for both/all instances?

Thanks in advance for your help.

Bob

IS that default instance sql services are started?

What is the OS version? Service pack?
When some counters disapear, what counters are still there?
Do you start perfmon first (remotely or localy) and then SQL Server?
Do you change the service account for the SQL Server instances while perfmon is running (remotely or localy)
When counters disapear do you see any rows with select * from sysperfinfo? If you get no rows you should check the errorlog for the instances affected.

|||

Thanks for the response Satya:

IS that default instance sql services are started? >> Yes

What is the OS version? Service pack? >> Not Sure
When some counters disapear, what counters are still there? >> Counters don't "disappear" - they are never there
Do you start perfmon first (remotely or localy) and then SQL Server? >> SQL Server(s) are running first
Do you change the service account for the SQL Server instances while perfmon is running (remotely or localy) >> No
When counters disapear do you see any rows with select * from sysperfinfo? >> Again, counters don't "disappear" - yes I get 700+ rows

If you get no rows you should check the errorlog for the instances affected.

|||I believe still there is much information needed to assess, check the service pack level on SQL and operating system. BTW are there any clustered instances?|||

Thanks again Satya...

I am trying to get my Windows Admin to get the information on the operating system. The instances are not clustered. Was there a problem with performance counters for a given SP?

Bob

Performance Monitor Counters with Multiple Instances

I have a server that has 2 instances of SQL Server 2000. The first is the default instance and the second is a named instance. When I go to Performance Monitor to view counters, I can only find counters for the named instance. Is ther a way to view counters for both/all instances?

Thanks in advance for your help.

Bob

IS that default instance sql services are started?

What is the OS version? Service pack?
When some counters disapear, what counters are still there?
Do you start perfmon first (remotely or localy) and then SQL Server?
Do you change the service account for the SQL Server instances while perfmon is running (remotely or localy)
When counters disapear do you see any rows with select * from sysperfinfo? If you get no rows you should check the errorlog for the instances affected.

|||

Thanks for the response Satya:

IS that default instance sql services are started? >> Yes

What is the OS version? Service pack? >> Not Sure
When some counters disapear, what counters are still there? >> Counters don't "disappear" - they are never there
Do you start perfmon first (remotely or localy) and then SQL Server? >> SQL Server(s) are running first
Do you change the service account for the SQL Server instances while perfmon is running (remotely or localy) >> No
When counters disapear do you see any rows with select * from sysperfinfo? >> Again, counters don't "disappear" - yes I get 700+ rows

If you get no rows you should check the errorlog for the instances affected.

|||I believe still there is much information needed to assess, check the service pack level on SQL and operating system. BTW are there any clustered instances?|||

Thanks again Satya...

I am trying to get my Windows Admin to get the information on the operating system. The instances are not clustered. Was there a problem with performance counters for a given SP?

Bob