Hi there,
My program in Delphi have 7 table with about 120,000 records and 200 fields.
I use SQL server for my database.
When my program launchs its speed is too low.
What shall I do to increase its speed?
Regards,
Doroodgarit probably has nothing to do with the number of tables and records in the
database. you can try several things:
* delay creating unnecessary objects and forms, and create them as needed
* delay connecting to the database, and again connect only when needed
* move time-consuming processing to background thread, if possible
* show splash screen, it will make the appearance better :)
in general, minimize the amount of work you perform on load.
btw, those '200 fields' seem pretty much..
dean
"doroodgar" <rdoroodgar@.noornet.net> wrote in message
news:uTKCZCpDFHA.148@.TK2MSFTNGP14.phx.gbl...
> Hi there,
> My program in Delphi have 7 table with about 120,000 records and 200
fields.
> I use SQL server for my database.
> When my program launchs its speed is too low.
> What shall I do to increase its speed?
> Regards,
> Doroodgar
>|||HI doroodgar,
You sound convinced SQL slow your application purely on the fact the you
return tons of records so I'll work with that.
Why do you need that size recordset on application launch ? This confuse me
a bit cause you load the records based on what ?
What does your application do ? Is there some sort of logic to the records
that you return, is it possible that you can narrow it down , you present
120k records on one page anyways... should you maybe try and load 100 at a
time ?
How do you application present the 200 fields ?
Is it a straight select statement that populate your recordset or do you use
joins ?
What's the timing compared to when you run it through QA ?
Hope this will help
Can you give more info regarding your query and application ?
"doroodgar" wrote:
> Hi there,
> My program in Delphi have 7 table with about 120,000 records and 200 field
s.
> I use SQL server for my database.
> When my program launchs its speed is too low.
> What shall I do to increase its speed?
> Regards,
> Doroodgar
>
>|||http://www.sql-server-performance.com
Regards
Mike
"Mal" wrote:
> HI doroodgar,
> You sound convinced SQL slow your application purely on the fact the you
> return tons of records so I'll work with that.
> Why do you need that size recordset on application launch ? This confuse m
e
> a bit cause you load the records based on what ?
> What does your application do ? Is there some sort of logic to the records
> that you return, is it possible that you can narrow it down , you present
> 120k records on one page anyways... should you maybe try and load 100 at a
> time ?
> How do you application present the 200 fields ?
> Is it a straight select statement that populate your recordset or do you u
se
> joins ?
> What's the timing compared to when you run it through QA ?
> Hope this will help
> Can you give more info regarding your query and application ?
> "doroodgar" wrote:
>sql
Showing posts with label speed. Show all posts
Showing posts with label speed. Show all posts
Thursday, March 29, 2012
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...
>
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...
>
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
> >
> >
>
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
> >
> >
>
Thursday, March 8, 2012
Database restore speed
This problem isn't specific to SQL Server, but because of the size of the files I deal with with SQL Server, it is the place I notice it more often, and hopefully one of you has, too.
If I am restoring a database (or simply copying a huge file) that takes more than a couple of minutes, I find, by watching the network bandwidth, that after a couple of minutes, the data rate cuts on half and stays that way for the rest of the restore (or copy). If I have a Gb/s connection, maybe I start at 240Mb/s and then drop off to 120MB/s. If I have a 100Mb/s connection, maybe I start at 70Mb/s and drop to 30-40MB/s. It is fairly consistant in how long before the drop and in the magnatude of the drop (approximately 50%).
The network staff have no clue. They say that they have put nothing in place to throttle bandwidth hogs. It doesn't seem to matter which servers the transfer is going between. It doesn't matter if they are plugged into the same switch or go across several switches.
I have googled for any reference to this with no luck. Has anyone else experienced this? Does anyone know the cause?
A couple of possibilities:
Have you observed Perfmon data on the drive you are copying your large file to? If the Average Disk Queue Length is exceeding 2 on a sustaining basis you are saturating the Disk I/O
Check the Read/Write cache ratio on your disk controller(s), if they are set to 100% Read and you are trying to write a large file to disk, it's going to slow it down significantly
Do you have the /3GB switch enabled in the boot.ini file on your server? We found on a number of our servers that large file copies (22GB+) were actually failing because we were exhausiting the Kernel resources on both the Source and Target servers. The resolution in this particular case was to remove the /3GB switch from both the source and target servers and then the large file copies succeeded.
Huge memory paging (2,000 pages/sec +) may also be another area to look into
Check the network card on your source and target servers to make sure they are negiotiating at Full Duplex. I have found that the network folks set the ports on the switch to Auto/Auto and the server SA's set the NIC cards to Forced Full. This causes a negotiation conflict and forces the NIC to negotiate at half duplex or worse
|||One reason I don;t think it is the target drive is that with I restore a DB, before data is read from the backup, the whole target file is written. With permon, I have seen it write out a 40G file at much higher, fairly constant rate than the fastest rate it will write to once it starts loading it with data from the backup. I haven't tried it lately, but I suspect I wouldn't see this throttling behavior if the backup file is on the local machine. Maybe I will try that to confirm.|||The /3GB switch in your boot.ini would help|||
I just tried it between two Win2003x64 servers with 8G of ram each and saw the same phenomenon. Presumably, the /3G option would be moot in this case.
|||Can't believe I found this thread by accident, when I just encountered the same error this morning
I was copying a 12GB SQL bak file from one server to another (identical servers, Windows 2003 R2 32-bit, 8GB RAM with /PAE and /3GB boot.ini, 15K rpm 200GB RAID5 disks, Gigabit NICs both at Auto speed)
The file copy would come to almost dead after a while (it initially displayed 3 minutes remaining, where NIC utilization is at 60%), then about one minute after (the estimate remaining time starts to go up to ~15 minutes, and NIC utilization is at 4~9% only
I saw this thread, remove the /3GB in boot.ini in BOTH servers, restart, re-copy, and the same thing still happened. Now I'm at a loss to what caused this
|||
We have the same problem - win2003x64(AMD) server - 1 with 4G ram the other 1G ram. Coping from the one with 4 to the 1 with 1.
Anyone ever come up with a solution for this? Would be most appreciated.
Thanks!
Mark
|||Regarding restore speed, do you have Windows Instant File Initialization enabled? You need to give the SQL Server Service account the right (in Group Policy Editor, for example), to "Perform Volume Maintenance Tasks". Otherwise, Windows has to zero out the space allocated for the file, which makes the restore take at least 3-4 times longer. You will have to restart the SQL Server Service for this to take effect.|||GlennAlanBerry,
Does this apply to SQL Server 2000 or just 2005? I just checked my SQL service account and it has this right, but (in 2000) it still makes the empty file first. In 2005, I have seen that it starts restoring instantly (determined by seeing that it is pulling data from across the network).
Most of the time, I am restoring over an existing DB, so this doesn't make a whole lot of difference to my speed issues. When it does matter, the writing of the empty file usually goes several times faster than the actual restore. That is what concerns me. With RAID5 arrays on either end and a gigabit connection in between, you would think that the array write speed should be the bottleneck. Even before this odd throttling affect kicks in, it isn't the bottleneck. I did a series of restores last night. Based on the statistics at the end, the smaller DBs (2G and 6G) restored at around 20MB/s, but the larger ones (16G and 47G) restored at around 8MB/s. I wasn't watching the network throughput, but I'm sure if I was, I would have seen the all to familiar pattern of starting off fast and then abruptly dropping to a slower speed after a few minutes.
|||Windows Instant File Initialization is not used by SQL Server 2000Database restore speed
This problem isn't specific to SQL Server, but because of the size of the files I deal with with SQL Server, it is the place I notice it more often, and hopefully one of you has, too.
If I am restoring a database (or simply copying a huge file) that takes more than a couple of minutes, I find, by watching the network bandwidth, that after a couple of minutes, the data rate cuts on half and stays that way for the rest of the restore (or copy). If I have a Gb/s connection, maybe I start at 240Mb/s and then drop off to 120MB/s. If I have a 100Mb/s connection, maybe I start at 70Mb/s and drop to 30-40MB/s. It is fairly consistant in how long before the drop and in the magnatude of the drop (approximately 50%).
The network staff have no clue. They say that they have put nothing in place to throttle bandwidth hogs. It doesn't seem to matter which servers the transfer is going between. It doesn't matter if they are plugged into the same switch or go across several switches.
I have googled for any reference to this with no luck. Has anyone else experienced this? Does anyone know the cause?
A couple of possibilities:
Have you observed Perfmon data on the drive you are copying your large file to? If the Average Disk Queue Length is exceeding 2 on a sustaining basis you are saturating the Disk I/O
Check the Read/Write cache ratio on your disk controller(s), if they are set to 100% Read and you are trying to write a large file to disk, it's going to slow it down significantly
Do you have the /3GB switch enabled in the boot.ini file on your server? We found on a number of our servers that large file copies (22GB+) were actually failing because we were exhausiting the Kernel resources on both the Source and Target servers. The resolution in this particular case was to remove the /3GB switch from both the source and target servers and then the large file copies succeeded.
Huge memory paging (2,000 pages/sec +) may also be another area to look into
Check the network card on your source and target servers to make sure they are negiotiating at Full Duplex. I have found that the network folks set the ports on the switch to Auto/Auto and the server SA's set the NIC cards to Forced Full. This causes a negotiation conflict and forces the NIC to negotiate at half duplex or worse
|||One reason I don;t think it is the target drive is that with I restore a DB, before data is read from the backup, the whole target file is written. With permon, I have seen it write out a 40G file at much higher, fairly constant rate than the fastest rate it will write to once it starts loading it with data from the backup. I haven't tried it lately, but I suspect I wouldn't see this throttling behavior if the backup file is on the local machine. Maybe I will try that to confirm.|||The /3GB switch in your boot.ini would help|||
I just tried it between two Win2003x64 servers with 8G of ram each and saw the same phenomenon. Presumably, the /3G option would be moot in this case.
|||Can't believe I found this thread by accident, when I just encountered the same error this morning
I was copying a 12GB SQL bak file from one server to another (identical servers, Windows 2003 R2 32-bit, 8GB RAM with /PAE and /3GB boot.ini, 15K rpm 200GB RAID5 disks, Gigabit NICs both at Auto speed)
The file copy would come to almost dead after a while (it initially displayed 3 minutes remaining, where NIC utilization is at 60%), then about one minute after (the estimate remaining time starts to go up to ~15 minutes, and NIC utilization is at 4~9% only
I saw this thread, remove the /3GB in boot.ini in BOTH servers, restart, re-copy, and the same thing still happened. Now I'm at a loss to what caused this
|||
We have the same problem - win2003x64(AMD) server - 1 with 4G ram the other 1G ram. Coping from the one with 4 to the 1 with 1.
Anyone ever come up with a solution for this? Would be most appreciated.
Thanks!
Mark
|||Regarding restore speed, do you have Windows Instant File Initialization enabled? You need to give the SQL Server Service account the right (in Group Policy Editor, for example), to "Perform Volume Maintenance Tasks". Otherwise, Windows has to zero out the space allocated for the file, which makes the restore take at least 3-4 times longer. You will have to restart the SQL Server Service for this to take effect.|||GlennAlanBerry,
Does this apply to SQL Server 2000 or just 2005? I just checked my SQL service account and it has this right, but (in 2000) it still makes the empty file first. In 2005, I have seen that it starts restoring instantly (determined by seeing that it is pulling data from across the network).
Most of the time, I am restoring over an existing DB, so this doesn't make a whole lot of difference to my speed issues. When it does matter, the writing of the empty file usually goes several times faster than the actual restore. That is what concerns me. With RAID5 arrays on either end and a gigabit connection in between, you would think that the array write speed should be the bottleneck. Even before this odd throttling affect kicks in, it isn't the bottleneck. I did a series of restores last night. Based on the statistics at the end, the smaller DBs (2G and 6G) restored at around 20MB/s, but the larger ones (16G and 47G) restored at around 8MB/s. I wasn't watching the network throughput, but I'm sure if I was, I would have seen the all to familiar pattern of starting off fast and then abruptly dropping to a slower speed after a few minutes.
|||Windows Instant File Initialization is not used by SQL Server 2000Database restore speed
This problem isn't specific to SQL Server, but because of the size of the files I deal with with SQL Server, it is the place I notice it more often, and hopefully one of you has, too.
If I am restoring a database (or simply copying a huge file) that takes more than a couple of minutes, I find, by watching the network bandwidth, that after a couple of minutes, the data rate cuts on half and stays that way for the rest of the restore (or copy). If I have a Gb/s connection, maybe I start at 240Mb/s and then drop off to 120MB/s. If I have a 100Mb/s connection, maybe I start at 70Mb/s and drop to 30-40MB/s. It is fairly consistant in how long before the drop and in the magnatude of the drop (approximately 50%).
The network staff have no clue. They say that they have put nothing in place to throttle bandwidth hogs. It doesn't seem to matter which servers the transfer is going between. It doesn't matter if they are plugged into the same switch or go across several switches.
I have googled for any reference to this with no luck. Has anyone else experienced this? Does anyone know the cause?
A couple of possibilities:
Have you observed Perfmon data on the drive you are copying your large file to? If the Average Disk Queue Length is exceeding 2 on a sustaining basis you are saturating the Disk I/O
Check the Read/Write cache ratio on your disk controller(s), if they are set to 100% Read and you are trying to write a large file to disk, it's going to slow it down significantly
Do you have the /3GB switch enabled in the boot.ini file on your server? We found on a number of our servers that large file copies (22GB+) were actually failing because we were exhausiting the Kernel resources on both the Source and Target servers. The resolution in this particular case was to remove the /3GB switch from both the source and target servers and then the large file copies succeeded.
Huge memory paging (2,000 pages/sec +) may also be another area to look into
Check the network card on your source and target servers to make sure they are negiotiating at Full Duplex. I have found that the network folks set the ports on the switch to Auto/Auto and the server SA's set the NIC cards to Forced Full. This causes a negotiation conflict and forces the NIC to negotiate at half duplex or worse
|||One reason I don;t think it is the target drive is that with I restore a DB, before data is read from the backup, the whole target file is written. With permon, I have seen it write out a 40G file at much higher, fairly constant rate than the fastest rate it will write to once it starts loading it with data from the backup. I haven't tried it lately, but I suspect I wouldn't see this throttling behavior if the backup file is on the local machine. Maybe I will try that to confirm.|||The /3GB switch in your boot.ini would help|||
I just tried it between two Win2003x64 servers with 8G of ram each and saw the same phenomenon. Presumably, the /3G option would be moot in this case.
|||Can't believe I found this thread by accident, when I just encountered the same error this morning
I was copying a 12GB SQL bak file from one server to another (identical servers, Windows 2003 R2 32-bit, 8GB RAM with /PAE and /3GB boot.ini, 15K rpm 200GB RAID5 disks, Gigabit NICs both at Auto speed)
The file copy would come to almost dead after a while (it initially displayed 3 minutes remaining, where NIC utilization is at 60%), then about one minute after (the estimate remaining time starts to go up to ~15 minutes, and NIC utilization is at 4~9% only
I saw this thread, remove the /3GB in boot.ini in BOTH servers, restart, re-copy, and the same thing still happened. Now I'm at a loss to what caused this
|||
We have the same problem - win2003x64(AMD) server - 1 with 4G ram the other 1G ram. Coping from the one with 4 to the 1 with 1.
Anyone ever come up with a solution for this? Would be most appreciated.
Thanks!
Mark
|||Regarding restore speed, do you have Windows Instant File Initialization enabled? You need to give the SQL Server Service account the right (in Group Policy Editor, for example), to "Perform Volume Maintenance Tasks". Otherwise, Windows has to zero out the space allocated for the file, which makes the restore take at least 3-4 times longer. You will have to restart the SQL Server Service for this to take effect.|||GlennAlanBerry,
Does this apply to SQL Server 2000 or just 2005? I just checked my SQL service account and it has this right, but (in 2000) it still makes the empty file first. In 2005, I have seen that it starts restoring instantly (determined by seeing that it is pulling data from across the network).
Most of the time, I am restoring over an existing DB, so this doesn't make a whole lot of difference to my speed issues. When it does matter, the writing of the empty file usually goes several times faster than the actual restore. That is what concerns me. With RAID5 arrays on either end and a gigabit connection in between, you would think that the array write speed should be the bottleneck. Even before this odd throttling affect kicks in, it isn't the bottleneck. I did a series of restores last night. Based on the statistics at the end, the smaller DBs (2G and 6G) restored at around 20MB/s, but the larger ones (16G and 47G) restored at around 8MB/s. I wasn't watching the network throughput, but I'm sure if I was, I would have seen the all to familiar pattern of starting off fast and then abruptly dropping to a slower speed after a few minutes.
|||Windows Instant File Initialization is not used by SQL Server 2000Friday, February 17, 2012
DataBase Performance Issues
Hello,
A couple of things -
Does too many stored procedures in your database affect the performance and speed of the calls?
I have many stored procedures and it seems like the more I create the slower it is to create them. Each time I use enterprise manager it also seems slower and slower to open tables, create tables and create stored procedures for code.
How can I increase the performance of my database by starting and stopping the server at a specified time. and how often should I do this restart?
Thanks in advance for input,
EIt may depend on your DBMS. In general, I wouldn't think more stored procedures would slow down the database. In my experience running Enterprise Manager is always slow when connecting to a remote machine. It is sometimes fast and sometimes slow when connecting to my localmachine's SQL server. Using Query Analyzer is ALWAYS fast (remote or local).
You may want to post this question to the forum at SQLMag.com|||No. Number of stored procedures donot slow performance by any means.
The GUI of using Enterprise manager can be a little heavy ... Is the performance same when using isqlw (Query Analyzer) ... I suppose that would be pretty fast ...
::how often should I do this restart?::
I am not sure why you would like to do this ... I've a SQL Server running for quite a while now without a restart ... So whay are you keen on doing this ...
Tuesday, February 14, 2012
Database Optimization (Increase Speed)
Well good morning/afternoon to everyone.
It's been a while sinse I've posted here and it seems that the site is a lot faster now. Good to see. :)
Anyways, I'm working a current problem here at work with our database being quite slow. I've done some research already and will continue to do so but i wanted to get some of your opinions.
Right now, I've run the 'DBCC SHOWCONTIG' command and it is telling the following in the first 3 system tables:
DBCC SHOWCONTIG scanning 'sysobjects' table...
Table: 'sysobjects' (1); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 34
- Extents Scanned.......................: 12
- Extent Switches.......................: 33
- Avg. Pages per Extent..................: 2.8
- Scan Density [Best Count:Actual Count]......: 14.71% [5:34]
- Logical Scan Fragmentation ..............: 41.18%
- Extent Scan Fragmentation ...............: 83.33%
- Avg. Bytes Free per Page................: 2303.6
- Avg. Page Density (full)................: 71.54%
DBCC SHOWCONTIG scanning 'sysindexes' table...
Table: 'sysindexes' (2); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 72
- Extents Scanned.......................: 16
- Extent Switches.......................: 59
- Avg. Pages per Extent..................: 4.5
- Scan Density [Best Count:Actual Count]......: 15.00% [9:60]
- Logical Scan Fragmentation ..............: 50.00%
- Extent Scan Fragmentation ...............: 81.25%
- Avg. Bytes Free per Page................: 4184.9
- Avg. Page Density (full)................: 48.30%
DBCC SHOWCONTIG scanning 'syscolumns' table...
Table: 'syscolumns' (3); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 323
- Extents Scanned.......................: 50
- Extent Switches.......................: 299
- Avg. Pages per Extent..................: 6.5
- Scan Density [Best Count:Actual Count]......: 13.67% [41:300]
- Logical Scan Fragmentation ..............: 48.61%
- Extent Scan Fragmentation ...............: 96.00%
- Avg. Bytes Free per Page................: 4527.0
- Avg. Page Density (full)................: 44.07%
DBCC SHOWCONTIG scanning 'systypes' table...
Table: 'systypes' (4); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 6712.0
- Avg. Page Density (full)................: 17.07%
According to the DBCC SHOWCONTIG command documentation, there should be no fragmentation at all.
Some questions:
1. would system performance be severly negatively reduced with the above fragmentation (logical and extent)?
2. can the 'DBCC INDEXDEFRAG(dbname, tablename, indexname)' command be issued against those system tables without consequences?
3. is there some other command that can defrag the entire database without having to specify which tables?
Also, I have also used the index tuning wizard after a profile trace but that failed with some unknown error.
Thats it for now, please let me know if you have some info I could use to help speed up my database.Microsoft put out a white paper about defragging. It is actually pretty good:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
so, based on that paper, my answers are:
1) Not even noticeably.
2) Nope.
Server: Msg 2566, Level 14, State 1, Line 1
DBCC DBREINDEX cannot be used on system tables.
3) If you discount maintenance plans, no. But a google search should come up with a bunch of scripts that will reindex all of your tables for you. Just be careful about space useage, and transaction log size. As always, try it on your test system first, before...yadday...yadda...yadda.|||thx for the quick response! That article is great. I'm going through it right now. I'll let you know how it goes.
thx again.|||Would the command 'DBCC SHRINKDATABASE' need to be run to increase performance if I were to clear out a LOT of old (unnecessary) data? Or does that not relate to overall db performance?|||dbcc shrinkdatabase can actually cause fragmentation|||I have not heard of a case where shrinking any of the datafiles will help performance. Potentially, it could hurt performance, if you expect your database wll be growing as most do.|||Alright, that sounds logical enough. The reason I was considering a database shrink was because I'm testing this database optimization on my local copy of msde. I quickly realized that because my database is already 2 gigs big, I cannot do anything to increase speed (such as the dbcc reindex command) because there is not enough space. It's too bad that MSDE has this limitation of 2 gigs. I'm currently looking into using a 2003 Server Install with SQL Server on it for testing.|||For a laptop, I could see the size of the file impacting your pagefile (if you run out of room entirely), but outside of that, you are better off not shrinking.|||I ran a script here that deleted all the old product data that was no longer needed and then our sql server maintenace plan brought the logical fragmintation down to 0% on the key tables (down from 99% fragmentation).
This brought about great increases in performance. However, what is questionable is that under the "Optimization" tab in the maintenance plan, "reorganize index and data pages" was selected, but it never seemed to make a significant difference until the old data was removed from the database. I suppose it was making a difference, but the fragmentation was not reduced as much as it is now and the fragmentation occurred again during the week.
It's been a while sinse I've posted here and it seems that the site is a lot faster now. Good to see. :)
Anyways, I'm working a current problem here at work with our database being quite slow. I've done some research already and will continue to do so but i wanted to get some of your opinions.
Right now, I've run the 'DBCC SHOWCONTIG' command and it is telling the following in the first 3 system tables:
DBCC SHOWCONTIG scanning 'sysobjects' table...
Table: 'sysobjects' (1); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 34
- Extents Scanned.......................: 12
- Extent Switches.......................: 33
- Avg. Pages per Extent..................: 2.8
- Scan Density [Best Count:Actual Count]......: 14.71% [5:34]
- Logical Scan Fragmentation ..............: 41.18%
- Extent Scan Fragmentation ...............: 83.33%
- Avg. Bytes Free per Page................: 2303.6
- Avg. Page Density (full)................: 71.54%
DBCC SHOWCONTIG scanning 'sysindexes' table...
Table: 'sysindexes' (2); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 72
- Extents Scanned.......................: 16
- Extent Switches.......................: 59
- Avg. Pages per Extent..................: 4.5
- Scan Density [Best Count:Actual Count]......: 15.00% [9:60]
- Logical Scan Fragmentation ..............: 50.00%
- Extent Scan Fragmentation ...............: 81.25%
- Avg. Bytes Free per Page................: 4184.9
- Avg. Page Density (full)................: 48.30%
DBCC SHOWCONTIG scanning 'syscolumns' table...
Table: 'syscolumns' (3); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 323
- Extents Scanned.......................: 50
- Extent Switches.......................: 299
- Avg. Pages per Extent..................: 6.5
- Scan Density [Best Count:Actual Count]......: 13.67% [41:300]
- Logical Scan Fragmentation ..............: 48.61%
- Extent Scan Fragmentation ...............: 96.00%
- Avg. Bytes Free per Page................: 4527.0
- Avg. Page Density (full)................: 44.07%
DBCC SHOWCONTIG scanning 'systypes' table...
Table: 'systypes' (4); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 6712.0
- Avg. Page Density (full)................: 17.07%
According to the DBCC SHOWCONTIG command documentation, there should be no fragmentation at all.
Some questions:
1. would system performance be severly negatively reduced with the above fragmentation (logical and extent)?
2. can the 'DBCC INDEXDEFRAG(dbname, tablename, indexname)' command be issued against those system tables without consequences?
3. is there some other command that can defrag the entire database without having to specify which tables?
Also, I have also used the index tuning wizard after a profile trace but that failed with some unknown error.
Thats it for now, please let me know if you have some info I could use to help speed up my database.Microsoft put out a white paper about defragging. It is actually pretty good:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
so, based on that paper, my answers are:
1) Not even noticeably.
2) Nope.
Server: Msg 2566, Level 14, State 1, Line 1
DBCC DBREINDEX cannot be used on system tables.
3) If you discount maintenance plans, no. But a google search should come up with a bunch of scripts that will reindex all of your tables for you. Just be careful about space useage, and transaction log size. As always, try it on your test system first, before...yadday...yadda...yadda.|||thx for the quick response! That article is great. I'm going through it right now. I'll let you know how it goes.
thx again.|||Would the command 'DBCC SHRINKDATABASE' need to be run to increase performance if I were to clear out a LOT of old (unnecessary) data? Or does that not relate to overall db performance?|||dbcc shrinkdatabase can actually cause fragmentation|||I have not heard of a case where shrinking any of the datafiles will help performance. Potentially, it could hurt performance, if you expect your database wll be growing as most do.|||Alright, that sounds logical enough. The reason I was considering a database shrink was because I'm testing this database optimization on my local copy of msde. I quickly realized that because my database is already 2 gigs big, I cannot do anything to increase speed (such as the dbcc reindex command) because there is not enough space. It's too bad that MSDE has this limitation of 2 gigs. I'm currently looking into using a 2003 Server Install with SQL Server on it for testing.|||For a laptop, I could see the size of the file impacting your pagefile (if you run out of room entirely), but outside of that, you are better off not shrinking.|||I ran a script here that deleted all the old product data that was no longer needed and then our sql server maintenace plan brought the logical fragmintation down to 0% on the key tables (down from 99% fragmentation).
This brought about great increases in performance. However, what is questionable is that under the "Optimization" tab in the maintenance plan, "reorganize index and data pages" was selected, but it never seemed to make a significant difference until the old data was removed from the database. I suppose it was making a difference, but the fragmentation was not reduced as much as it is now and the fragmentation occurred again during the week.
Subscribe to:
Posts (Atom)