database speed

I have two database (structurly idenical) on the same SQL server, first
working good, but second is very slow
(noticed from web aplication)
I would like the same speed for both database
is there any database parameters need to be set to make same speed
Thanks for your answerHi
What is about amount of data contain on both database? Is it the same?
Did you identify what queries are caused to the problem? If you did, have
you tried to improve them?
"DS1979" <ds19792004@.yahoo.com> wrote in message
news:caovo2$qcg$1@.ls219.htnet.hr...
> I have two database (structurly idenical) on the same SQL server, first
> working good, but second is very slow
> (noticed from web aplication)
> I would like the same speed for both database
> is there any database parameters need to be set to make same speed
> Thanks for your answer
>|||"DS1979" <ds19792004@.yahoo.com> wrote in message
news:caovo2$qcg$1@.ls219.htnet.hr...
> I have two database (structurly idenical) on the same SQL server, first
> working good, but second is very slow
> (noticed from web aplication)
> I would like the same speed for both database
It is not so hard to make two databases equally slow just put "delay for" in
key places...|||Bojidar
What did you mean "put "delay for" in> key places..."?
"Bojidar Alexandrov" <bojo_do_not_spam@.kodar.net> wrote in message
news:%23x1h563UEHA.2564@.TK2MSFTNGP11.phx.gbl...
> "DS1979" <ds19792004@.yahoo.com> wrote in message
> news:caovo2$qcg$1@.ls219.htnet.hr...
>
> It is not so hard to make two databases equally slow just put "delay for"
in
> key places...
>|||We can say amount of data is the same (very small variation), and number of
users are almost the same,
I didn't mention, there is also two web aplication (ASP, medical aplication)
that are using mentioned database,
aplication are located on the same IIS server, and everting is working on
LAN (one hospital)
maybe I must set some parametrs in IIS '
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eCOUd23UEHA.2692@.TK2MSFTNGP09.phx.gbl...
> Hi
> What is about amount of data contain on both database? Is it the same?
> Did you identify what queries are caused to the problem? If you did, have
> you tried to improve them?
>
>
> "DS1979" <ds19792004@.yahoo.com> wrote in message
> news:caovo2$qcg$1@.ls219.htnet.hr...
>|||kidding
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:u2AsB%233UEHA.2408@.tk2msftngp13.phx.gbl...
> Bojidar
> What did you mean "put "delay for" in> key places..."?
> "Bojidar Alexandrov" <bojo_do_not_spam@.kodar.net> wrote in message
> news:%23x1h563UEHA.2564@.TK2MSFTNGP11.phx.gbl...
first[vbcol=seagreen]
for"[vbcol=seagreen]
> in
>|||You should consider the physical file system... Are the databases loaded to
the same or equivalent disks using the same filegroups/files?
Have both of the databases been maintained ( indexdefrag and update
statistics.)
As one other poster mentioned... If you feel like the problem is in the
database, isolate one query which works more slowly in one db than the
other, and look at the query plan... If you can not isolate this to a
single query ( or a group of queries) it might not be a database problem.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"DS1979" <ds19792004@.yahoo.com> wrote in message
news:caovo2$qcg$1@.ls219.htnet.hr...
> I have two database (structurly idenical) on the same SQL server, first
> working good, but second is very slow
> (noticed from web aplication)
> I would like the same speed for both database
> is there any database parameters need to be set to make same speed
> Thanks for your answer
>|||DS1979,
You might want to compare that the databases are indeed identical using
a third party tool such as http://www.red-gate.com/SQL_Compare.htm or
look at http://www.aspfaq.com/show.asp?id=2209 for more options.
This will highlight any differences. Once you have confirmed they are
the same, update statistics and defrag indexes. Also check that the
databases are residing on the same disk subsystem by running sp_helpfile
in each db.
Use Profiler to check the worst performing queries, then look at the
execution plans of those queries - there could be indexing differences
between the databases.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
DS1979 wrote:
> I have two database (structurly idenical) on the same SQL server, first
> working good, but second is very slow
> (noticed from web aplication)
> I would like the same speed for both database
> is there any database parameters need to be set to make same speed
> Thanks for your answer
>|||Thanks, I think maybe I found problem
See this
I found someting new in this story,
I found that administrator did this
1) copy xxx.mdf and xxx_log.ldf from (copy from file system) server A to
server B
2) Make new database on server B
3) Change path in server B database >>> properties >>> Data file and
Transaction Log to server B\xxx.mdf and server B\xxx_log.ldf
4) Put database server B\ working
no_sp_detach, no sp_attach ?
What do you think is this main reason for slow working (faster database
didn't moved from orginal location and it's working good)
What is the best solution to repair this situation now ?
Thanks, again
"Wayne Snyder" <wayne.snyder@.mariner-usa.com> wrote in message
news:%23PMikB5UEHA.3428@.TK2MSFTNGP12.phx.gbl...
> You should consider the physical file system... Are the databases loaded
to
> the same or equivalent disks using the same filegroups/files?
> Have both of the databases been maintained ( indexdefrag and update
> statistics.)
> As one other poster mentioned... If you feel like the problem is in the
> database, isolate one query which works more slowly in one db than the
> other, and look at the query plan... If you can not isolate this to a
> single query ( or a group of queries) it might not be a database problem.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
>
> "DS1979" <ds19792004@.yahoo.com> wrote in message
> news:caovo2$qcg$1@.ls219.htnet.hr...
>

database speed

I have two database (structurly idenical) on the same SQL server, first
working good, but second is very slow
(noticed from web aplication)
I would like the same speed for both database
is there any database parameters need to be set to make same speed
Thanks for your answer
Hi
What is about amount of data contain on both database? Is it the same?
Did you identify what queries are caused to the problem? If you did, have
you tried to improve them?
"DS1979" <ds19792004@.yahoo.com> wrote in message
news:caovo2$qcg$1@.ls219.htnet.hr...
> I have two database (structurly idenical) on the same SQL server, first
> working good, but second is very slow
> (noticed from web aplication)
> I would like the same speed for both database
> is there any database parameters need to be set to make same speed
> Thanks for your answer
>
|||"DS1979" <ds19792004@.yahoo.com> wrote in message
news:caovo2$qcg$1@.ls219.htnet.hr...
> I have two database (structurly idenical) on the same SQL server, first
> working good, but second is very slow
> (noticed from web aplication)
> I would like the same speed for both database
It is not so hard to make two databases equally slow just put "delay for" in
key places...
|||Bojidar
What did you mean "put "delay for" in> key places..."?
"Bojidar Alexandrov" <bojo_do_not_spam@.kodar.net> wrote in message
news:%23x1h563UEHA.2564@.TK2MSFTNGP11.phx.gbl...
> "DS1979" <ds19792004@.yahoo.com> wrote in message
> news:caovo2$qcg$1@.ls219.htnet.hr...
>
> It is not so hard to make two databases equally slow just put "delay for"
in
> key places...
>
|||We can say amount of data is the same (very small variation), and number of
users are almost the same,
I didn't mention, there is also two web aplication (ASP, medical aplication)
that are using mentioned database,
aplication are located on the same IIS server, and everting is working on
LAN (one hospital)
maybe I must set some parametrs in IIS ?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eCOUd23UEHA.2692@.TK2MSFTNGP09.phx.gbl...
> Hi
> What is about amount of data contain on both database? Is it the same?
> Did you identify what queries are caused to the problem? If you did, have
> you tried to improve them?
>
>
> "DS1979" <ds19792004@.yahoo.com> wrote in message
> news:caovo2$qcg$1@.ls219.htnet.hr...
>
|||kidding
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:u2AsB%233UEHA.2408@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Bojidar
> What did you mean "put "delay for" in> key places..."?
> "Bojidar Alexandrov" <bojo_do_not_spam@.kodar.net> wrote in message
> news:%23x1h563UEHA.2564@.TK2MSFTNGP11.phx.gbl...
first[vbcol=seagreen]
for"
> in
>
|||You should consider the physical file system... Are the databases loaded to
the same or equivalent disks using the same filegroups/files?
Have both of the databases been maintained ( indexdefrag and update
statistics.)
As one other poster mentioned... If you feel like the problem is in the
database, isolate one query which works more slowly in one db than the
other, and look at the query plan... If you can not isolate this to a
single query ( or a group of queries) it might not be a database problem.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"DS1979" <ds19792004@.yahoo.com> wrote in message
news:caovo2$qcg$1@.ls219.htnet.hr...
> I have two database (structurly idenical) on the same SQL server, first
> working good, but second is very slow
> (noticed from web aplication)
> I would like the same speed for both database
> is there any database parameters need to be set to make same speed
> Thanks for your answer
>
|||DS1979,
You might want to compare that the databases are indeed identical using
a third party tool such as http://www.red-gate.com/SQL_Compare.htm or
look at http://www.aspfaq.com/show.asp?id=2209 for more options.
This will highlight any differences. Once you have confirmed they are
the same, update statistics and defrag indexes. Also check that the
databases are residing on the same disk subsystem by running sp_helpfile
in each db.
Use Profiler to check the worst performing queries, then look at the
execution plans of those queries - there could be indexing differences
between the databases.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
DS1979 wrote:
> I have two database (structurly idenical) on the same SQL server, first
> working good, but second is very slow
> (noticed from web aplication)
> I would like the same speed for both database
> is there any database parameters need to be set to make same speed
> Thanks for your answer
>
|||Thanks, I think maybe I found problem
See this
I found someting new in this story,
I found that administrator did this
1) copy xxx.mdf and xxx_log.ldf from (copy from file system) server A to
server B
2) Make new database on server B
3) Change path in server B database >>> properties >>> Data file and
Transaction Log to server B\xxx.mdf and server B\xxx_log.ldf
4) Put database server B\ working
no_sp_detach, no sp_attach ?
What do you think is this main reason for slow working (faster database
didn't moved from orginal location and it's working good)
What is the best solution to repair this situation now ?
Thanks, again
"Wayne Snyder" <wayne.snyder@.mariner-usa.com> wrote in message
news:%23PMikB5UEHA.3428@.TK2MSFTNGP12.phx.gbl...
> You should consider the physical file system... Are the databases loaded
to
> the same or equivalent disks using the same filegroups/files?
> Have both of the databases been maintained ( indexdefrag and update
> statistics.)
> As one other poster mentioned... If you feel like the problem is in the
> database, isolate one query which works more slowly in one db than the
> other, and look at the query plan... If you can not isolate this to a
> single query ( or a group of queries) it might not be a database problem.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
>
> "DS1979" <ds19792004@.yahoo.com> wrote in message
> news:caovo2$qcg$1@.ls219.htnet.hr...
>

database speed

I have two database (structurly idenical) on the same SQL server, first
working good, but second is very slow
(noticed from web aplication)
I would like the same speed for both database
is there any database parameters need to be set to make same speed
Thanks for your answerHi
What is about amount of data contain on both database? Is it the same?
Did you identify what queries are caused to the problem? If you did, have
you tried to improve them?
"DS1979" <ds19792004@.yahoo.com> wrote in message
news:caovo2$qcg$1@.ls219.htnet.hr...
> I have two database (structurly idenical) on the same SQL server, first
> working good, but second is very slow
> (noticed from web aplication)
> I would like the same speed for both database
> is there any database parameters need to be set to make same speed
> Thanks for your answer
>|||"DS1979" <ds19792004@.yahoo.com> wrote in message
news:caovo2$qcg$1@.ls219.htnet.hr...
> I have two database (structurly idenical) on the same SQL server, first
> working good, but second is very slow
> (noticed from web aplication)
> I would like the same speed for both database
It is not so hard to make two databases equally slow just put "delay for" in
key places...|||Bojidar
What did you mean "put "delay for" in> key places..."?
"Bojidar Alexandrov" <bojo_do_not_spam@.kodar.net> wrote in message
news:%23x1h563UEHA.2564@.TK2MSFTNGP11.phx.gbl...
> "DS1979" <ds19792004@.yahoo.com> wrote in message
> news:caovo2$qcg$1@.ls219.htnet.hr...
> > I have two database (structurly idenical) on the same SQL server, first
> > working good, but second is very slow
> > (noticed from web aplication)
> >
> > I would like the same speed for both database
>
> It is not so hard to make two databases equally slow just put "delay for"
in
> key places...
>|||We can say amount of data is the same (very small variation), and number of
users are almost the same,
I didn't mention, there is also two web aplication (ASP, medical aplication)
that are using mentioned database,
aplication are located on the same IIS server, and everting is working on
LAN (one hospital)
maybe I must set some parametrs in IIS '
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eCOUd23UEHA.2692@.TK2MSFTNGP09.phx.gbl...
> Hi
> What is about amount of data contain on both database? Is it the same?
> Did you identify what queries are caused to the problem? If you did, have
> you tried to improve them?
>
>
> "DS1979" <ds19792004@.yahoo.com> wrote in message
> news:caovo2$qcg$1@.ls219.htnet.hr...
> > I have two database (structurly idenical) on the same SQL server, first
> > working good, but second is very slow
> > (noticed from web aplication)
> >
> > I would like the same speed for both database
> > is there any database parameters need to be set to make same speed
> >
> > Thanks for your answer
> >
> >
>|||kidding
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:u2AsB%233UEHA.2408@.tk2msftngp13.phx.gbl...
> Bojidar
> What did you mean "put "delay for" in> key places..."?
> "Bojidar Alexandrov" <bojo_do_not_spam@.kodar.net> wrote in message
> news:%23x1h563UEHA.2564@.TK2MSFTNGP11.phx.gbl...
> >
> > "DS1979" <ds19792004@.yahoo.com> wrote in message
> > news:caovo2$qcg$1@.ls219.htnet.hr...
> > > I have two database (structurly idenical) on the same SQL server,
first
> > > working good, but second is very slow
> > > (noticed from web aplication)
> > >
> > > I would like the same speed for both database
> >
> >
> > It is not so hard to make two databases equally slow just put "delay
for"
> in
> > key places...
> >
> >
>|||You should consider the physical file system... Are the databases loaded to
the same or equivalent disks using the same filegroups/files?
Have both of the databases been maintained ( indexdefrag and update
statistics.)
As one other poster mentioned... If you feel like the problem is in the
database, isolate one query which works more slowly in one db than the
other, and look at the query plan... If you can not isolate this to a
single query ( or a group of queries) it might not be a database problem.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"DS1979" <ds19792004@.yahoo.com> wrote in message
news:caovo2$qcg$1@.ls219.htnet.hr...
> I have two database (structurly idenical) on the same SQL server, first
> working good, but second is very slow
> (noticed from web aplication)
> I would like the same speed for both database
> is there any database parameters need to be set to make same speed
> Thanks for your answer
>|||Out of curiousity have set a performance monitor for both
servers, it could be that one server is being used for a
lot more work than the other.
Other than that just follow what the guys have said.
J
>--Original Message--
>I have two database (structurly idenical) on the same SQL
server, first
>working good, but second is very slow
>(noticed from web aplication)
>I would like the same speed for both database
>is there any database parameters need to be set to make
same speed
>Thanks for your answer
>
>.
>|||DS1979,
You might want to compare that the databases are indeed identical using
a third party tool such as http://www.red-gate.com/SQL_Compare.htm or
look at http://www.aspfaq.com/show.asp?id=2209 for more options.
This will highlight any differences. Once you have confirmed they are
the same, update statistics and defrag indexes. Also check that the
databases are residing on the same disk subsystem by running sp_helpfile
in each db.
Use Profiler to check the worst performing queries, then look at the
execution plans of those queries - there could be indexing differences
between the databases.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
DS1979 wrote:
> I have two database (structurly idenical) on the same SQL server, first
> working good, but second is very slow
> (noticed from web aplication)
> I would like the same speed for both database
> is there any database parameters need to be set to make same speed
> Thanks for your answer
>|||Thanks, I think maybe I found problem
See this
I found someting new in this story,
I found that administrator did this
1) copy xxx.mdf and xxx_log.ldf from (copy from file system) server A to
server B
2) Make new database on server B
3) Change path in server B database >> properties >> Data file and
Transaction Log to server B\xxx.mdf and server B\xxx_log.ldf
4) Put database server B\ working
no_sp_detach, no sp_attach ?
What do you think is this main reason for slow working (faster database
didn't moved from orginal location and it's working good)
What is the best solution to repair this situation now ?
Thanks, again
"Wayne Snyder" <wayne.snyder@.mariner-usa.com> wrote in message
news:%23PMikB5UEHA.3428@.TK2MSFTNGP12.phx.gbl...
> You should consider the physical file system... Are the databases loaded
to
> the same or equivalent disks using the same filegroups/files?
> Have both of the databases been maintained ( indexdefrag and update
> statistics.)
> As one other poster mentioned... If you feel like the problem is in the
> database, isolate one query which works more slowly in one db than the
> other, and look at the query plan... If you can not isolate this to a
> single query ( or a group of queries) it might not be a database problem.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
>
> "DS1979" <ds19792004@.yahoo.com> wrote in message
> news:caovo2$qcg$1@.ls219.htnet.hr...
> > I have two database (structurly idenical) on the same SQL server, first
> > working good, but second is very slow
> > (noticed from web aplication)
> >
> > I would like the same speed for both database
> > is there any database parameters need to be set to make same speed
> >
> > Thanks for your answer
> >
> >
>

Database size on IIS 5

Good Morning,
I have a web service developed using vb.net. The web
service returns xml from selected data (SQL SELECT
statement using XML AUTO) from SQL Server. The web service
runs properly on smaller databases on both IIS 5 (Windows
2000 Server) and IIS 6 (Windows 2003 Server), but returns
an error when run against larger databases on IIS 5. The
error has been 'out System.OutOfMemoryException'.
I am trying to figure out the difference between IIS 5 and
IIS 6 which is causing this, and a solution.I guess the database is too big for IIS5, so it can not run.
After all, when IIS5 design, no one can image a big database like we use
today.
I think If I am you, I will catch this exception and give the customer some
information to update the IIS.
Last: This is a problem of IIS, you may ask there

Database size on IIS 5

Good Morning,
I have a web service developed using vb.net. The web
service returns xml from selected data (SQL SELECT
statement using XML AUTO) from SQL Server. The web service
runs properly on smaller databases on both IIS 5 (Windows
2000 Server) and IIS 6 (Windows 2003 Server), but returns
an error when run against larger databases on IIS 5. The
error has been 'out System.OutOfMemoryException'.
I am trying to figure out the difference between IIS 5 and
IIS 6 which is causing this, and a solution.I guess the database is too big for IIS5, so it can not run.
After all, when IIS5 design, no one can image a big database like we use
today.
I think If I am you, I will catch this exception and give the customer some
information to update the IIS.
Last: This is a problem of IIS, you may ask there:)

Database Size

Hi,
I have a web application (MS SQL 2000 / ASP). Database has grown from 20 MB
to 400 MB in the last year or so. I am a little concerned about the size and
how to be prepared when something needs to be done in this sense.
At what size should I be concerned and is there any site that tells what to
do when the database grows a lot ? Will I have performance issues ?
Any information is greatly appreciated
AleksIf this 400 MB is al data then you can not make it smaller without deleting
data
You can however shrink the transaction log, do you have autoshrink enabled,
does someone else shrink it on regularely?
400 MB is nothing for SQL Server we have a DB here that well over 100 GB
with some tables having 8 million records
If you see problems in the future you can always 'archive' old records into
another DB or Data WareHouse
http://sqlservercode.blogspot.com/
"Aleks" wrote:

> Hi,
> I have a web application (MS SQL 2000 / ASP). Database has grown from 20 M
B
> to 400 MB in the last year or so. I am a little concerned about the size a
nd
> how to be prepared when something needs to be done in this sense.
> At what size should I be concerned and is there any site that tells what t
o
> do when the database grows a lot ? Will I have performance issues ?
> Any information is greatly appreciated
> Aleks
>
>|||Are you concerned about database administration/maintenance of large
databases or optimizing the performance of queries? Actually, 400mb is not
large at all for a medium powered box running SQL Server. A typical database
(or collection of related databases) for an insurance or ecommerce company
with a few years of transactional data would be > 20 GB. It's not until you
reach 100 GB that you're talking about something serious. Basically you want
to start gradually introducing data warehousing concepts into your system
design.
http://www.microsoft.com/technet/pr...n/rdbmspft.mspx
http://www.microsoft.com/sql/techin...calability.mspx
http://www.microsoft.com/technet/co...ql/sql0326.mspx
http://msdn.microsoft.com/library/d...br />
9okw.asp
"Aleks" <arkark2004@.hotmail.com> wrote in message
news:eWrusJEwFHA.2556@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have a web application (MS SQL 2000 / ASP). Database has grown from 20
> MB to 400 MB in the last year or so. I am a little concerned about the
> size and how to be prepared when something needs to be done in this sense.
> At what size should I be concerned and is there any site that tells what
> to do when the database grows a lot ? Will I have performance issues ?
> Any information is greatly appreciated
> Aleks
>|||Thanks .. this gives me some peace of mind.
A
"JT" <someone@.microsoft.com> wrote in message
news:eae8N%23EwFHA.2960@.tk2msftngp13.phx.gbl...
> Are you concerned about database administration/maintenance of large
> databases or optimizing the performance of queries? Actually, 400mb is not
> large at all for a medium powered box running SQL Server. A typical
> database (or collection of related databases) for an insurance or
> ecommerce company with a few years of transactional data would be > 20 GB.
> It's not until you reach 100 GB that you're talking about something
> serious. Basically you want to start gradually introducing data
> warehousing concepts into your system design.
> http://www.microsoft.com/technet/pr...calability.mspx
> http://www.microsoft.com/technet/co...ql/sql0326.mspx
> http://msdn.microsoft.com/library/d... />
0_9okw.asp
> "Aleks" <arkark2004@.hotmail.com> wrote in message
> news:eWrusJEwFHA.2556@.TK2MSFTNGP15.phx.gbl...
>

Database Server Hardware Architecture

Hi.
I am in the process of "ramping-up" (read: spending more money)
on a home project. To date, I have done the web and database
development on the same machine. I have been advised to put the
DB stuff on its own box. My question is, what kind of box should this
be? What are the essentials when buying hardware for a DB connection
(ie. serverboard vs. workstation motherboard, speen, disk space,
etc).
Thanks for any tips and advice you have."pbd22" <dushkin@.gmail.com> wrote in message
news:1175186968.716011.45140@.p77g2000hsh.googlegroups.com...
> Hi.
> I am in the process of "ramping-up" (read: spending more money)
> on a home project. To date, I have done the web and database
> development on the same machine. I have been advised to put the
> DB stuff on its own box. My question is, what kind of box should this
> be? What are the essentials when buying hardware for a DB connection
> (ie. serverboard vs. workstation motherboard, speen, disk space,
> etc).
"It depends" a lot on the nature of your data.
However, databases tend to like lots of RAM and fast disk channels.
I'd have at least 2 separate disk channels with RAID 1 (or other raid such
as 10, or 5/50 for data) for data and logs.
And as much RAM as your version of SQL Server can use.
But beyond that, impossible to really advise w/o knowing more about your
database setup.
> Thanks for any tips and advice you have.
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Waiting for more infos about your database, i suggest another point of view:
I suppose that "home project" means something developed for your pleasure.
If not, and especially if you intend expose on the Network your Web Server,
it's strongly recommended to protect the dbserver (this probably would be the
meaning of "...its own box"). At the box should added security (firewalling
and so on...)
Gilberto
"Greg D. Moore (Strider)" wrote:
>
>
> "pbd22" <dushkin@.gmail.com> wrote in message
> news:1175186968.716011.45140@.p77g2000hsh.googlegroups.com...
> > Hi.
> >
> > I am in the process of "ramping-up" (read: spending more money)
> > on a home project. To date, I have done the web and database
> > development on the same machine. I have been advised to put the
> > DB stuff on its own box. My question is, what kind of box should this
> > be? What are the essentials when buying hardware for a DB connection
> > (ie. serverboard vs. workstation motherboard, speen, disk space,
> > etc).
> "It depends" a lot on the nature of your data.
> However, databases tend to like lots of RAM and fast disk channels.
> I'd have at least 2 separate disk channels with RAID 1 (or other raid such
> as 10, or 5/50 for data) for data and logs.
> And as much RAM as your version of SQL Server can use.
> But beyond that, impossible to really advise w/o knowing more about your
> database setup.
>
> >
> > Thanks for any tips and advice you have.
> >
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
>
>|||On Mar 30, 3:34 am, Gilberto Zampatti
<GilbertoZampa...@.discussions.microsoft.com> wrote:
> Waiting for more infos about your database, i suggest another point of view:
> I suppose that "home project" means something developed for your pleasure.
> If not, and especially if you intend expose on the Network your Web Server,
> it's strongly recommended to protect the dbserver (this probably would be the
> meaning of "...its own box"). At the box should added security (firewalling
> and so on...)
> Gilberto
> "Greg D. Moore (Strider)" wrote:
>
> > "pbd22" <dush...@.gmail.com> wrote in message
> >news:1175186968.716011.45140@.p77g2000hsh.googlegroups.com...
> > > Hi.
> > > I am in the process of "ramping-up" (read: spending more money)
> > > on a home project. To date, I have done the web and database
> > > development on the same machine. I have been advised to put the
> > > DB stuff on its own box. My question is, what kind of box should this
> > > be? What are the essentials when buying hardware for a DB connection
> > > (ie. serverboard vs. workstation motherboard, speen, disk space,
> > > etc).
> > "It depends" a lot on the nature of your data.
> > However, databases tend to like lots of RAM and fast disk channels.
> > I'd have at least 2 separate disk channels with RAID 1 (or other raid such
> > as 10, or 5/50 for data) for data and logs.
> > And as much RAM as your version of SQL Server can use.
> > But beyond that, impossible to really advise w/o knowing more about your
> > database setup.
> > > Thanks for any tips and advice you have.
> > --
> > Greg Moore
> > SQL Server DBA Consulting Remote and Onsite available!
> > Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
Thanks all for your advice. Gilberto, indeed this is a stand-alone DB
server
but, per your firewalling suggestion, would you advise putting an
additional
router in between the connection from the web server and the DB box?
Or,
should the operating system's own firewalling software be sufficient
protection?
thanks again.|||Honestly i have poor skill in firewalling software; however i think that a
separated firewall would work better, considering that in this case you let
both your servers (application and DB) better devoted to their natural
"vocation", not spending CPU and resources do defend your network against
intrusions.
Gilberto
"pbd22" wrote:
> On Mar 30, 3:34 am, Gilberto Zampatti
> <GilbertoZampa...@.discussions.microsoft.com> wrote:
> > Waiting for more infos about your database, i suggest another point of view:
> > I suppose that "home project" means something developed for your pleasure.
> > If not, and especially if you intend expose on the Network your Web Server,
> > it's strongly recommended to protect the dbserver (this probably would be the
> > meaning of "...its own box"). At the box should added security (firewalling
> > and so on...)
> > Gilberto
> >
> > "Greg D. Moore (Strider)" wrote:
> >
> >
> >
> > > "pbd22" <dush...@.gmail.com> wrote in message
> > >news:1175186968.716011.45140@.p77g2000hsh.googlegroups.com...
> > > > Hi.
> >
> > > > I am in the process of "ramping-up" (read: spending more money)
> > > > on a home project. To date, I have done the web and database
> > > > development on the same machine. I have been advised to put the
> > > > DB stuff on its own box. My question is, what kind of box should this
> > > > be? What are the essentials when buying hardware for a DB connection
> > > > (ie. serverboard vs. workstation motherboard, speen, disk space,
> > > > etc).
> >
> > > "It depends" a lot on the nature of your data.
> >
> > > However, databases tend to like lots of RAM and fast disk channels.
> >
> > > I'd have at least 2 separate disk channels with RAID 1 (or other raid such
> > > as 10, or 5/50 for data) for data and logs.
> >
> > > And as much RAM as your version of SQL Server can use.
> >
> > > But beyond that, impossible to really advise w/o knowing more about your
> > > database setup.
> >
> > > > Thanks for any tips and advice you have.
> >
> > > --
> > > Greg Moore
> > > SQL Server DBA Consulting Remote and Onsite available!
> > > Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
>
> Thanks all for your advice. Gilberto, indeed this is a stand-alone DB
> server
> but, per your firewalling suggestion, would you advise putting an
> additional
> router in between the connection from the web server and the DB box?
> Or,
> should the operating system's own firewalling software be sufficient
> protection?
> thanks again.
>|||On 29 Mar 2007 09:49:28 -0700, "pbd22" <dushkin@.gmail.com> wrote:
>I am in the process of "ramping-up" (read: spending more money)
>on a home project. To date, I have done the web and database
>development on the same machine. I have been advised to put the
>DB stuff on its own box. My question is, what kind of box should this
>be? What are the essentials when buying hardware for a DB connection
>(ie. serverboard vs. workstation motherboard, speen, disk space,
>etc).
Besides other good advice, know that SQLServer likes multiple
processors. A single dual-core is good and might do you for a start,
but depending on your anticipated load, more might be (much) better.
When things go mondo you'll want to go 64-bit, but that's probably
overkill at the moment. Say, 4gb RAM would be a good start.
If the database is large and the contents valuable, you'll need some
backup plans. These days it's probably easiest to buy a stack of USB
drives and/or burn DVDs regularly, but do plan ahead!
Again, if the system is likely to be heavily loaded, gigabit LAN is a
good thing, too.
J.

Database Server Hardware Architecture

Hi.
I am in the process of "ramping-up" (read: spending more money)
on a home project. To date, I have done the web and database
development on the same machine. I have been advised to put the
DB stuff on its own box. My question is, what kind of box should this
be? What are the essentials when buying hardware for a DB connection
(ie. serverboard vs. workstation motherboard, speen, disk space,
etc).
Thanks for any tips and advice you have."pbd22" <dushkin@.gmail.com> wrote in message
news:1175186968.716011.45140@.p77g2000hsh.googlegroups.com...
> Hi.
> I am in the process of "ramping-up" (read: spending more money)
> on a home project. To date, I have done the web and database
> development on the same machine. I have been advised to put the
> DB stuff on its own box. My question is, what kind of box should this
> be? What are the essentials when buying hardware for a DB connection
> (ie. serverboard vs. workstation motherboard, speen, disk space,
> etc).
"It depends" a lot on the nature of your data.
However, databases tend to like lots of RAM and fast disk channels.
I'd have at least 2 separate disk channels with RAID 1 (or other raid such
as 10, or 5/50 for data) for data and logs.
And as much RAM as your version of SQL Server can use.
But beyond that, impossible to really advise w/o knowing more about your
database setup.

> Thanks for any tips and advice you have.
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Waiting for more infos about your database, i suggest another point of view:
I suppose that "home project" means something developed for your pleasure.
If not, and especially if you intend expose on the Network your Web Server,
it's strongly recommended to protect the dbserver (this probably would be th
e
meaning of "...its own box"). At the box should added security (firewalling
and so on...)
Gilberto
"Greg D. Moore (Strider)" wrote:

>
>
> "pbd22" <dushkin@.gmail.com> wrote in message
> news:1175186968.716011.45140@.p77g2000hsh.googlegroups.com...
> "It depends" a lot on the nature of your data.
> However, databases tend to like lots of RAM and fast disk channels.
> I'd have at least 2 separate disk channels with RAID 1 (or other raid such
> as 10, or 5/50 for data) for data and logs.
> And as much RAM as your version of SQL Server can use.
> But beyond that, impossible to really advise w/o knowing more about your
> database setup.
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com [url]http://www.greenms.com/sqlserver.html[/ur
l]
>
>|||On Mar 30, 3:34 am, Gilberto Zampatti
<GilbertoZampa...@.discussions.microsoft.com> wrote:[vbcol=seagreen]
> Waiting for more infos about your database, i suggest another point of vie
w:
> I suppose that "home project" means something developed for your pleasure.
> If not, and especially if you intend expose on the Network your Web Server
,
> it's strongly recommended to protect the dbserver (this probably would be
the
> meaning of "...its own box"). At the box should added security (firewallin
g
> and so on...)
> Gilberto
> "Greg D. Moore (Strider)" wrote:
>
>
>
>
>
>
>
>
>
>
Thanks all for your advice. Gilberto, indeed this is a stand-alone DB
server
but, per your firewalling suggestion, would you advise putting an
additional
router in between the connection from the web server and the DB box?
Or,
should the operating system's own firewalling software be sufficient
protection?
thanks again.|||Honestly i have poor skill in firewalling software; however i think that a
separated firewall would work better, considering that in this case you let
both your servers (application and DB) better devoted to their natural
"vocation", not spending CPU and resources do defend your network against
intrusions.
Gilberto
"pbd22" wrote:

> On Mar 30, 3:34 am, Gilberto Zampatti
> <GilbertoZampa...@.discussions.microsoft.com> wrote:
>
> Thanks all for your advice. Gilberto, indeed this is a stand-alone DB
> server
> but, per your firewalling suggestion, would you advise putting an
> additional
> router in between the connection from the web server and the DB box?
> Or,
> should the operating system's own firewalling software be sufficient
> protection?
> thanks again.
>|||On 29 Mar 2007 09:49:28 -0700, "pbd22" <dushkin@.gmail.com> wrote:

>I am in the process of "ramping-up" (read: spending more money)
>on a home project. To date, I have done the web and database
>development on the same machine. I have been advised to put the
>DB stuff on its own box. My question is, what kind of box should this
>be? What are the essentials when buying hardware for a DB connection
>(ie. serverboard vs. workstation motherboard, speen, disk space,
>etc).
Besides other good advice, know that SQLServer likes multiple
processors. A single dual-core is good and might do you for a start,
but depending on your anticipated load, more might be (much) better.
When things go mondo you'll want to go 64-bit, but that's probably
overkill at the moment. Say, 4gb RAM would be a good start.
If the database is large and the contents valuable, you'll need some
backup plans. These days it's probably easiest to buy a stack of USB
drives and/or burn DVDs regularly, but do plan ahead!
Again, if the system is likely to be heavily loaded, gigabit LAN is a
good thing, too.
J.

Database Server Hardware Architecture

Hi.
I am in the process of "ramping-up" (read: spending more money)
on a home project. To date, I have done the web and database
development on the same machine. I have been advised to put the
DB stuff on its own box. My question is, what kind of box should this
be? What are the essentials when buying hardware for a DB connection
(ie. serverboard vs. workstation motherboard, speen, disk space,
etc).
Thanks for any tips and advice you have.
"pbd22" <dushkin@.gmail.com> wrote in message
news:1175186968.716011.45140@.p77g2000hsh.googlegro ups.com...
> Hi.
> I am in the process of "ramping-up" (read: spending more money)
> on a home project. To date, I have done the web and database
> development on the same machine. I have been advised to put the
> DB stuff on its own box. My question is, what kind of box should this
> be? What are the essentials when buying hardware for a DB connection
> (ie. serverboard vs. workstation motherboard, speen, disk space,
> etc).
"It depends" a lot on the nature of your data.
However, databases tend to like lots of RAM and fast disk channels.
I'd have at least 2 separate disk channels with RAID 1 (or other raid such
as 10, or 5/50 for data) for data and logs.
And as much RAM as your version of SQL Server can use.
But beyond that, impossible to really advise w/o knowing more about your
database setup.

> Thanks for any tips and advice you have.
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||On 29 Mar 2007 09:49:28 -0700, "pbd22" <dushkin@.gmail.com> wrote:

>I am in the process of "ramping-up" (read: spending more money)
>on a home project. To date, I have done the web and database
>development on the same machine. I have been advised to put the
>DB stuff on its own box. My question is, what kind of box should this
>be? What are the essentials when buying hardware for a DB connection
>(ie. serverboard vs. workstation motherboard, speen, disk space,
>etc).
Besides other good advice, know that SQLServer likes multiple
processors. A single dual-core is good and might do you for a start,
but depending on your anticipated load, more might be (much) better.
When things go mondo you'll want to go 64-bit, but that's probably
overkill at the moment. Say, 4gb RAM would be a good start.
If the database is large and the contents valuable, you'll need some
backup plans. These days it's probably easiest to buy a stack of USB
drives and/or burn DVDs regularly, but do plan ahead!
Again, if the system is likely to be heavily loaded, gigabit LAN is a
good thing, too.
J.

Database server connection

I am trying to using sqlserver as back-end for an application(web) running
on the window 2X, one thing I am not sure is that once I establish the
connection between database server and application, should I keep this
connection open while application is running, or only open it when
there is a requet to qury records from database?

Thanks for your help."Leon" <penghao98@.hotmail.com> wrote in message
news:89a0a7de.0409221926.6c394129@.posting.google.c om...
>I am trying to using sqlserver as back-end for an application(web) running
> on the window 2X, one thing I am not sure is that once I establish the
> connection between database server and application, should I keep this
> connection open while application is running, or only open it when
> there is a requet to qury records from database?
> Thanks for your help.

I believe the usual approach is to leave the connection open, to avoid the
overhead associated with opening and closing the connection again and again.
This is one reason for using connection pooling, for example.

You might want to post in a forum for your client library (eg ADO, JDBC) or
development environment (eg ASP, PHP) to see if there are any specific
considerations for your application.

Simon|||penghao98@.hotmail.com (Leon) wrote in message news:<89a0a7de.0409221926.6c394129@.posting.google.com>...
> I am trying to using sqlserver as back-end for an application(web) running
> on the window 2X, one thing I am not sure is that once I establish the
> connection between database server and application, should I keep this
> connection open while application is running, or only open it when
> there is a requet to qury records from database?
> Thanks for your help.

Hi,
This is question basically regarding connection pooling.
Which technology you are using for database acess? like ADO,JDO, JDBc
etc..?
In all these you can create a pool of connections and maintain it. So
the best thing which I feel is ,during boot up of your application
create a pool of connections, store it and use the connection when
needed.
This is purely dependent on your application also.

Some technologies like ADO and JDBC now support disconnected rowsets
also like CachedRowset in Java where internally it will(means
driver/proider) handle reconnecting to the server when a request
comes.

But from performance point of view, better to create connections
during start up.

How many connections? etc depending on the usage of your application
Regards
Zunil
Cordys

Database security

Hi,
I'm trying to implement some security on our more sensitive tables in a database.
The database is used by all for read/write via Web pages (IIS).
Is there any way to restrict users from accessing a table other than from a specific application (i.e. IIS or Crystal Reports)?
Am I looking in the wrong direction?

Thanks
MottyYes, you can do that by implementing application security(application role).
For more details see "application roles" in BOL.

Originally posted by mseal1
Hi,
I'm trying to implement some security on our more sensitive tables in a database.
The database is used by all for read/write via Web pages (IIS).
Is there any way to restrict users from accessing a table other than from a specific application (i.e. IIS or Crystal Reports)?
Am I looking in the wrong direction?

Thanks
Motty|||How is the access to the tables controlled?Thru Stored procedure ,roles?|||I have no control at this time as to how users access the Db.
Security is using NT logons, and domain users can read/write to all tables.
(Hope I don't sound too naive about administrating my database (SQL 7.0)

Thanks
Motty|||What if I have no control over the application that accesses SQL, then I can't run the sp_setapprole to gain access?|||Once the app role in place, you won`t need to keep NT logons , so this it would be the only way to connect to the database for the users. (supposing of course that guest acc. don`t exists in the current DB)

Originally posted by mseal1
What if I have no control over the application that accesses SQL, then I can't run the sp_setapprole to gain access?|||I know I'm sounding a little thick today
I have several applications (off the shelf) such as Crystal reporting, Access, Excel
I want to be able to limit access to a table based on the application name the users are coming from.
If I use Profiler, I have a column called 'Application Name' that identifies the type of application.
Can I use that information? At times I don't have a way to 'send' the sp_setapprole command.

Thanks for all your help!|||No you don't because SQL implements the security based on accounts and roles. The only way to restrict the access is to declare a custom role in your DB for each app., then set the privileges according to your policy, and map your users to these roles.

Originally posted by mseal1
I know I'm sounding a little thick today
I have several applications (off the shelf) such as Crystal reporting, Access, Excel
I want to be able to limit access to a table based on the application name the users are coming from.
If I use Profiler, I have a column called 'Application Name' that identifies the type of application.
Can I use that information? At times I don't have a way to 'send' the sp_setapprole command.

Thanks for all your help!|||Thanks,
I think I have enough to start

Database Readonly

I copied over a small website to 2003 Web Server and got:

Exception Details:System.Data.SqlClient.SqlException:Failed to update database"D:\INETPUB\WWWROOT\LUNCHCOUNT\APP_DATA\LUNCHCOUNT.MDF" because thedatabase is read-only.

It works just fine in VS - what to do?

Thanks

Whoops - should have followed the 10 minute rule. Found the folder didn't have Write turned on it permissions.|||Check if the MDF file is readonly file and change the permissions.

database protection

Does anyone know a good web site which tells me how to protect database
data? e.g. hot stand-by machine. I am looking at the low cost. Thanks.If your looking to keep costs down then Log shipping may be your best bet.
You can find details in BooksOnLine or at www.microsoft.com/sql
Andrew J. Kelly
SQL Server MVP
"Beyonce K." <bk@.bk.com> wrote in message
news:OO563PP9DHA.3404@.TK2MSFTNGP09.phx.gbl...
> Does anyone know a good web site which tells me how to protect database
> data? e.g. hot stand-by machine. I am looking at the low cost. Thanks.
>

database protection

Does anyone know a good web site which tells me how to protect database
data? e.g. hot stand-by machine. I am looking at the low cost. Thanks.If your looking to keep costs down then Log shipping may be your best bet.
You can find details in BooksOnLine or at www.microsoft.com/sql
Andrew J. Kelly
SQL Server MVP
"Beyonce K." <bk@.bk.com> wrote in message
news:OO563PP9DHA.3404@.TK2MSFTNGP09.phx.gbl...
> Does anyone know a good web site which tells me how to protect database
> data? e.g. hot stand-by machine. I am looking at the low cost. Thanks.
>

Database Properties

is there anyway to retrieve the database properties and display in an asp.net web form as a quick over view rather than logging onto the server to check, items such as size, space available, last backup data etc?

Cheers

I have managed to get the database size using :

EXEC sp_MSforeachtable @.command1=EXEC sp_spaceused

|||

Hi,

Haven't tried it but you should be able to use theMicrosoft.SqlServer.Management.Smo to do so:

Here's an example :

http://www.aspfree.com/c/a/MS-SQL-Server/Retrieving-SQL-Server-2005-Database-Info-Using-SMO-Database-Info-Table-Info/1/

HTH,
Suprotim Agarwal

--
http://www.dotnetcurry.com
--

database problem

i have 2 database tables and want the primary key from table 1 to appear in table 2. The tables are connected to two web forms, so in other words, the user enters personal details into form1 which is sent to table1..then the user enters company details into form 2 which is sent to table 2, the trouble is, is that at the moment there is no way of telling which personal details relate to which company details...

any ideas?

When you direct users to form 2, send a parameter: Response.Redirect("Form2.aspx?ID=" + ID.ToString())

Alternately, if users login, then you have some way to identify which user is logged in (User.Identity.Name will likely have some useful information on who the user is). Another alternative is to use a session variable.

|||

ok, how would that ...Response.Redirect("Form2.aspx?ID=" + ID.ToString())...store it in database table 2?

i dont understand how this works

Database permissions

Hi,

In my web app I've got two databases (the asp profile one and my own custom one). If I take the code and data and use it to create another website on another box, I usually get a database error saying the permissions are wrong. In this case I usually just give everyone full control and it works, but this obviously isn't good practice!

So, I'm wondering what permissions on database files does ASP need? Which users need what permissions?

Thanks

The most reliable way to move a SQL Server database and permissions is Backup and Restore all other methods sometimes will not move permissions so you have to go in and add those as needed. There are two permissions in SQL Server needed to run Asp.net both are covered in the second thread and the first thread is a FAQ I created to help with moving SQL Server database from one computer to another. The permissions you give a user is at you discretion and that is covered under SQL Server object permissions in SQL Server BOL(books online). Hope this helps.

http://forums.asp.net/thread/1454694.aspx

http://forums.asp.net/thread/1492092.aspx

|||

Hi,

That's great thanks. The only problem is that my database is stored on a .mdf file and I cannot open it in MS SQL server management studio to change the database permissions. Is there a way around this?

Thanks

|||

That is not good practice so the SQL Server team have created a tutorial to help you connect your user instance to Management Studio when you do that you can go in and change the permissions. I would not advice you to give the everyone group full control but don't remove it from your Asp.net folders either because Asp.net is also a member of that group. Post again if you still have questions. Hope this helps.

http://blogs.msdn.com/sqlexpress/archive/2006/11/22/connecting-to-sql-express-user-instances-in-management-studio.aspx

Database Paging Options with 2M Rows

After seaching a few resources and seeing a few ideas for database paging
(e.g. returning a subset of result to a web app based upon some paging
algorithm on the web, I was wondering if anyone has used paging effectively
on a result set as large as 2 M rows? I have currently built a table with
all the columns possible for the web app we are using; built a clustered
index the most used field (e.g. date_received desc order); build nonclustered
indices on the other columns that could be sorted by in the app; and finally
a procedure that dynamically (using 'exec (' select ...') builds the query.
Where I see a performance issue with this type of sql is when I have a large
result set (50K rows or so) and need to get to page 100. Any immediate
thoughts that come to mind?
http://www.aspfaq.com/2120
http://www.aspfaq.com/
(Reverse address to reply.)
"Jay Schirmacher" <JaySchirmacher@.discussions.microsoft.com> wrote in
message news:53C30568-F9E1-4418-8E66-B0FE840608E4@.microsoft.com...
> After seaching a few resources and seeing a few ideas for database paging
> (e.g. returning a subset of result to a web app based upon some paging
> algorithm on the web, I was wondering if anyone has used paging
effectively
> on a result set as large as 2 M rows? I have currently built a table
with
> all the columns possible for the web app we are using; built a clustered
> index the most used field (e.g. date_received desc order); build
nonclustered
> indices on the other columns that could be sorted by in the app; and
finally
> a procedure that dynamically (using 'exec (' select ...') builds the
query.
> Where I see a performance issue with this type of sql is when I have a
large
> result set (50K rows or so) and need to get to page 100. Any immediate
> thoughts that come to mind?
|||I did see your previous post with this link. I will test it out later and
reply with the repsonses. My situation is slightly different (as one would
expect) in that most of the query will need to be dynamically built.
More later and thanks for the reply.
"Aaron [SQL Server MVP]" wrote:

> http://www.aspfaq.com/2120
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Jay Schirmacher" <JaySchirmacher@.discussions.microsoft.com> wrote in
> message news:53C30568-F9E1-4418-8E66-B0FE840608E4@.microsoft.com...
> effectively
> with
> nonclustered
> finally
> query.
> large
>
>

Database Paging Options with 2M Rows

After seaching a few resources and seeing a few ideas for database paging
(e.g. returning a subset of result to a web app based upon some paging
algorithm on the web, I was wondering if anyone has used paging effectively
on a result set as large as 2 M rows? I have currently built a table with
all the columns possible for the web app we are using; built a clustered
index the most used field (e.g. date_received desc order); build nonclustere
d
indices on the other columns that could be sorted by in the app; and finally
a procedure that dynamically (using 'exec (' select ...') builds the query.
Where I see a performance issue with this type of sql is when I have a large
result set (50K rows or so) and need to get to page 100. Any immediate
thoughts that come to mind?http://www.aspfaq.com/2120
http://www.aspfaq.com/
(Reverse address to reply.)
"Jay Schirmacher" <JaySchirmacher@.discussions.microsoft.com> wrote in
message news:53C30568-F9E1-4418-8E66-B0FE840608E4@.microsoft.com...
> After seaching a few resources and seeing a few ideas for database paging
> (e.g. returning a subset of result to a web app based upon some paging
> algorithm on the web, I was wondering if anyone has used paging
effectively
> on a result set as large as 2 M rows? I have currently built a table
with
> all the columns possible for the web app we are using; built a clustered
> index the most used field (e.g. date_received desc order); build
nonclustered
> indices on the other columns that could be sorted by in the app; and
finally
> a procedure that dynamically (using 'exec (' select ...') builds the
query.
> Where I see a performance issue with this type of sql is when I have a
large
> result set (50K rows or so) and need to get to page 100. Any immediate
> thoughts that come to mind?|||I did see your previous post with this link. I will test it out later and
reply with the repsonses. My situation is slightly different (as one would
expect) in that most of the query will need to be dynamically built.
More later and thanks for the reply.
"Aaron [SQL Server MVP]" wrote:

> http://www.aspfaq.com/2120
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Jay Schirmacher" <JaySchirmacher@.discussions.microsoft.com> wrote in
> message news:53C30568-F9E1-4418-8E66-B0FE840608E4@.microsoft.com...
> effectively
> with
> nonclustered
> finally
> query.
> large
>
>

Database Paging Options with 2M Rows

After seaching a few resources and seeing a few ideas for database paging
(e.g. returning a subset of result to a web app based upon some paging
algorithm on the web, I was wondering if anyone has used paging effectively
on a result set as large as 2 M rows? I have currently built a table with
all the columns possible for the web app we are using; built a clustered
index the most used field (e.g. date_received desc order); build nonclustered
indices on the other columns that could be sorted by in the app; and finally
a procedure that dynamically (using 'exec (' select ...') builds the query.
Where I see a performance issue with this type of sql is when I have a large
result set (50K rows or so) and need to get to page 100. Any immediate
thoughts that come to mind?http://www.aspfaq.com/2120
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Jay Schirmacher" <JaySchirmacher@.discussions.microsoft.com> wrote in
message news:53C30568-F9E1-4418-8E66-B0FE840608E4@.microsoft.com...
> After seaching a few resources and seeing a few ideas for database paging
> (e.g. returning a subset of result to a web app based upon some paging
> algorithm on the web, I was wondering if anyone has used paging
effectively
> on a result set as large as 2 M rows? I have currently built a table
with
> all the columns possible for the web app we are using; built a clustered
> index the most used field (e.g. date_received desc order); build
nonclustered
> indices on the other columns that could be sorted by in the app; and
finally
> a procedure that dynamically (using 'exec (' select ...') builds the
query.
> Where I see a performance issue with this type of sql is when I have a
large
> result set (50K rows or so) and need to get to page 100. Any immediate
> thoughts that come to mind?|||I did see your previous post with this link. I will test it out later and
reply with the repsonses. My situation is slightly different (as one would
expect) in that most of the query will need to be dynamically built.
More later and thanks for the reply.
"Aaron [SQL Server MVP]" wrote:
> http://www.aspfaq.com/2120
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Jay Schirmacher" <JaySchirmacher@.discussions.microsoft.com> wrote in
> message news:53C30568-F9E1-4418-8E66-B0FE840608E4@.microsoft.com...
> > After seaching a few resources and seeing a few ideas for database paging
> > (e.g. returning a subset of result to a web app based upon some paging
> > algorithm on the web, I was wondering if anyone has used paging
> effectively
> > on a result set as large as 2 M rows? I have currently built a table
> with
> > all the columns possible for the web app we are using; built a clustered
> > index the most used field (e.g. date_received desc order); build
> nonclustered
> > indices on the other columns that could be sorted by in the app; and
> finally
> > a procedure that dynamically (using 'exec (' select ...') builds the
> query.
> > Where I see a performance issue with this type of sql is when I have a
> large
> > result set (50K rows or so) and need to get to page 100. Any immediate
> > thoughts that come to mind?
>
>