Accelerate Sql Express?

Hi,

we're planning to migrate an existing dataset cache mechanism to Sql Server Express. The dataset isn't really able to handle and search one million recordsets any more :-)

Once a day the Cache Sql Database wil be filled with fresh data. In comparison with the dataset takes up to fourty times longer to build the cache on the same machine!! Ok, I expected that i will take longer than creating the objects in a database with all its "overhead" like atomic transactions etc. But not so much.

The current throughput on SQL Server ist nearly 400 inserted records per second in the same table. The dataset stores up to 17.000 of the same data.

I'm wondering about the the following fact. Allthough I'm testing the Mass-Inserts with generating synthetic records in a unlimited for-loop, the server CPU load is allways less than 3 %. For all I care it could be much higher while the nightly process of initilializing the cache. Could there be a unnecessary throttle that limits the amount of inserts per second?

Any ideas how to accelerate the database engine or optimizing bulk-inserts?

P.S. I tested some different options and played around with connection string options like packekt size, Named Pipes connections, recycling the last commando and parameters and so on. Without any significant effects..

Marcus

Could you post how you are doing the inserts are the moment? Are you using BULK INSERT or bcp, or are you doing regular inserts? Also, what is the transaction size you are using, and what are the specifications or your machine, disks, etc.|||Im inserting each record in a single regular insert statement via ADO.NET (2.0) ExecuteComand. As far as I know, buldinsert are only for files available, right?

The Maschine is a 4 x XEON 3,6 Ghz, 32 bit Win2003 Server Sp1, 4GB Ram and a fast SCSI Raid

What is the transaction size? Is this a configurable value? At the moment all values are unchanged
installation defaults.

Thanks
Marcus|||There are several ways to speedup:

1) Use a SqlTransaction in your ADO.NET code, and issue a commit after every N rows that you insert. If you don't do this, a commit will be done for each row, which is very expensive.

2) To get big perf improvements, you should use BulkInsert or BCP to bulk load your data in your database. These methods are much faster than doing individual inserts.

Thanks,

Abstracting cross-databases references with views

On our SQL Server, the databases reference each other quite often. eg. UPDAT
E
SalesDatabase..DailySales SET Flag = 1. We are planning to change that so
that all references to other databases are done through views, because we
want to abstract and centralise all the cross-database references.
So we'll end up with
CREATE View as SELECT * FROM SalesDatabase..DailySales
UPDATE DailySales SET Flag = 1
My question is this. By going through a view, will this have any impact on
performance ?
Thanks, CraigGraig
Generally speaking views are not about performance. Views are about
security. If a performance is important to you I'd suggest to use a stored
procedure instead and make sure that you jave properly defined indexes on
the table.
"Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
news:B6A50E38-BCF3-44F1-9179-DE653A340655@.microsoft.com...
> On our SQL Server, the databases reference each other quite often. eg.
> UPDATE
> SalesDatabase..DailySales SET Flag = 1. We are planning to change that so
> that all references to other databases are done through views, because we
> want to abstract and centralise all the cross-database references.
> So we'll end up with
> CREATE View as SELECT * FROM SalesDatabase..DailySales
> UPDATE DailySales SET Flag = 1
> My question is this. By going through a view, will this have any impact on
> performance ?
> Thanks, Craig|||I do use stored procedures and all the tables are indexed.
What I'm not sure about is whether performance will be affected if instead
of selecting / updating / deleting from a table that exists in another
database, I select / update / delete from a local view that selects from tha
t
table in the other database. The SQL I'm talking about would be in a stored
procedure.
BEFORE:
create proc Proc1 as UPDATE OtherDatabase..Sales SET Flag = 1
AFTER:
create view Sales as SELECT ProductID, SalesQty, SalesAmt, Flag FROM
OtherDatabase..Sales
create proc Proc1 as UPDATE Sales SET Flag = 1
The reason for doing is specific to our applications and that is not what I
am asking. What I need to know is whether doing this will affect perfomance.
An extra question is : would the stored procedure (that calls the local view
that calls the table in the other database) be able to use the indexes in th
e
table or would the view in between prevent that from happening.
Any help would be greatly appreciated,
Craig

About sql server administration tools

Hi Guys,
I am planning to buy some tools that facilitate my admin of my sql server
live database. Now I don't any tools to use. I just want to inquiry that is
there any tools I can buy to facilitate my admin of sql server, such as
minitor tools, backup tools, performance tunning tools, so on. Thanks.Iter
http://sqlblog.com/blogs/rhundhause...bs.a
spx
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:EA1E5BBC-F9DA-44E9-9BC2-2BE6E11B23C7@.microsoft.com...
> Hi Guys,
> I am planning to buy some tools that facilitate my admin of my sql server
> live database. Now I don't any tools to use. I just want to inquiry that
> is
> there any tools I can buy to facilitate my admin of sql server, such as
> minitor tools, backup tools, performance tunning tools, so on. Thanks.
>|||Hi
Check these sites
http://www.apexsql.com/
http://www.idera.com/
regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:EA1E5BBC-F9DA-44E9-9BC2-2BE6E11B23C7@.microsoft.com...
> Hi Guys,
> I am planning to buy some tools that facilitate my admin of my sql server
> live database. Now I don't any tools to use. I just want to inquiry that
> is
> there any tools I can buy to facilitate my admin of sql server, such as
> minitor tools, backup tools, performance tunning tools, so on. Thanks.
>|||I really like a few of Red-Gate's tools. I couldn't live without SQL
Compare and SQL Backup has been very useful.
http://www.red-gate.com/
For deployment I rely heavily on SQL Farms Combine:
http://www.sqlfarms.com/
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:EA1E5BBC-F9DA-44E9-9BC2-2BE6E11B23C7@.microsoft.com...
> Hi Guys,
> I am planning to buy some tools that facilitate my admin of my sql server
> live database. Now I don't any tools to use. I just want to inquiry that
> is
> there any tools I can buy to facilitate my admin of sql server, such as
> minitor tools, backup tools, performance tunning tools, so on. Thanks.
>|||As already mentioned, there are a number of tools that can do the job.
That being said, you should maybe evaluate what you need is before you
go out and buy a tool. SQL Servers own Enterprise Manager/SQL Server
Management Studio, can actually do the job very well for most
administrative purposes. You should also keep in mind that the tools you
can buy, in many cases can't do much more than what you can do your self
with Managent Studio and some SQL scripting knowledge - the tools just
make it a little bit more "userfriendly".
It's my personal feeling that if you only have a few SQL Servers to look
after, you might not really need a 3rd. party tool for the admin. If you
have many servers to look after, some of the tools might be better to
give you this "over-all" view of the whole SQL server farm.
I've over the years never really felt the need to use a 3rd. party tool
for administration, but have been quite happy with Enterprise Manager /
Query analyser / SQL Server Management studio. I've then written various
scripts over time to do some specific task that I needed - and the
benefit of that is that I've learned a lot more about how SQL server
does things "behind the scenes".
Regards
Steen Schlüter Persson
Database Administrator / System Administrator
Iter wrote:
> Hi Guys,
> I am planning to buy some tools that facilitate my admin of my sql server
> live database. Now I don't any tools to use. I just want to inquiry that i
s
> there any tools I can buy to facilitate my admin of sql server, such as
> minitor tools, backup tools, performance tunning tools, so on. Thanks.
>

About sql server administration tools

Hi Guys,
I am planning to buy some tools that facilitate my admin of my sql server
live database. Now I don't any tools to use. I just want to inquiry that is
there any tools I can buy to facilitate my admin of sql server, such as
minitor tools, backup tools, performance tunning tools, so on. Thanks.
Iter
http://sqlblog.com/blogs/rhundhausen/archive/2007/06/18/cool-dba-automation-jobs.aspx
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:EA1E5BBC-F9DA-44E9-9BC2-2BE6E11B23C7@.microsoft.com...
> Hi Guys,
> I am planning to buy some tools that facilitate my admin of my sql server
> live database. Now I don't any tools to use. I just want to inquiry that
> is
> there any tools I can buy to facilitate my admin of sql server, such as
> minitor tools, backup tools, performance tunning tools, so on. Thanks.
>
|||I really like a few of Red-Gate's tools. I couldn't live without SQL
Compare and SQL Backup has been very useful.
http://www.red-gate.com/
For deployment I rely heavily on SQL Farms Combine:
http://www.sqlfarms.com/
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:EA1E5BBC-F9DA-44E9-9BC2-2BE6E11B23C7@.microsoft.com...
> Hi Guys,
> I am planning to buy some tools that facilitate my admin of my sql server
> live database. Now I don't any tools to use. I just want to inquiry that
> is
> there any tools I can buy to facilitate my admin of sql server, such as
> minitor tools, backup tools, performance tunning tools, so on. Thanks.
>
|||As already mentioned, there are a number of tools that can do the job.
That being said, you should maybe evaluate what you need is before you
go out and buy a tool. SQL Servers own Enterprise Manager/SQL Server
Management Studio, can actually do the job very well for most
administrative purposes. You should also keep in mind that the tools you
can buy, in many cases can't do much more than what you can do your self
with Managent Studio and some SQL scripting knowledge - the tools just
make it a little bit more "userfriendly".
It's my personal feeling that if you only have a few SQL Servers to look
after, you might not really need a 3rd. party tool for the admin. If you
have many servers to look after, some of the tools might be better to
give you this "over-all" view of the whole SQL server farm.
I've over the years never really felt the need to use a 3rd. party tool
for administration, but have been quite happy with Enterprise Manager /
Query analyser / SQL Server Management studio. I've then written various
scripts over time to do some specific task that I needed - and the
benefit of that is that I've learned a lot more about how SQL server
does things "behind the scenes".
Regards
Steen Schlüter Persson
Database Administrator / System Administrator
Iter wrote:
> Hi Guys,
> I am planning to buy some tools that facilitate my admin of my sql server
> live database. Now I don't any tools to use. I just want to inquiry that is
> there any tools I can buy to facilitate my admin of sql server, such as
> minitor tools, backup tools, performance tunning tools, so on. Thanks.
>

About sql server administration tools

Hi Guys,
I am planning to buy some tools that facilitate my admin of my sql server
live database. Now I don't any tools to use. I just want to inquiry that is
there any tools I can buy to facilitate my admin of sql server, such as
minitor tools, backup tools, performance tunning tools, so on. Thanks.Iter
http://sqlblog.com/blogs/rhundhausen/archive/2007/06/18/cool-dba-automation-jobs.aspx
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:EA1E5BBC-F9DA-44E9-9BC2-2BE6E11B23C7@.microsoft.com...
> Hi Guys,
> I am planning to buy some tools that facilitate my admin of my sql server
> live database. Now I don't any tools to use. I just want to inquiry that
> is
> there any tools I can buy to facilitate my admin of sql server, such as
> minitor tools, backup tools, performance tunning tools, so on. Thanks.
>|||Hi
Check these sites
http://www.apexsql.com/
http://www.idera.com/
regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:EA1E5BBC-F9DA-44E9-9BC2-2BE6E11B23C7@.microsoft.com...
> Hi Guys,
> I am planning to buy some tools that facilitate my admin of my sql server
> live database. Now I don't any tools to use. I just want to inquiry that
> is
> there any tools I can buy to facilitate my admin of sql server, such as
> minitor tools, backup tools, performance tunning tools, so on. Thanks.
>|||I really like a few of Red-Gate's tools. I couldn't live without SQL
Compare and SQL Backup has been very useful.
http://www.red-gate.com/
For deployment I rely heavily on SQL Farms Combine:
http://www.sqlfarms.com/
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:EA1E5BBC-F9DA-44E9-9BC2-2BE6E11B23C7@.microsoft.com...
> Hi Guys,
> I am planning to buy some tools that facilitate my admin of my sql server
> live database. Now I don't any tools to use. I just want to inquiry that
> is
> there any tools I can buy to facilitate my admin of sql server, such as
> minitor tools, backup tools, performance tunning tools, so on. Thanks.
>|||As already mentioned, there are a number of tools that can do the job.
That being said, you should maybe evaluate what you need is before you
go out and buy a tool. SQL Servers own Enterprise Manager/SQL Server
Management Studio, can actually do the job very well for most
administrative purposes. You should also keep in mind that the tools you
can buy, in many cases can't do much more than what you can do your self
with Managent Studio and some SQL scripting knowledge - the tools just
make it a little bit more "userfriendly".
It's my personal feeling that if you only have a few SQL Servers to look
after, you might not really need a 3rd. party tool for the admin. If you
have many servers to look after, some of the tools might be better to
give you this "over-all" view of the whole SQL server farm.
I've over the years never really felt the need to use a 3rd. party tool
for administration, but have been quite happy with Enterprise Manager /
Query analyser / SQL Server Management studio. I've then written various
scripts over time to do some specific task that I needed - and the
benefit of that is that I've learned a lot more about how SQL server
does things "behind the scenes".
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator
Iter wrote:
> Hi Guys,
> I am planning to buy some tools that facilitate my admin of my sql server
> live database. Now I don't any tools to use. I just want to inquiry that is
> there any tools I can buy to facilitate my admin of sql server, such as
> minitor tools, backup tools, performance tunning tools, so on. Thanks.
>

About SQL Server 7.0 Developer Edition

Hi!
We're planning to run a web application as an intranet on Windows 2000
Server + IIS (ASP) + SQLServer 7.0.
We have a BackOffice 4.5 Developer Edition which includes SQLServer
7.0.
Can we use the SQLServer Developer Edition for our intranet?
Thanks.
Sam
Hi
No. You may not use Developer Edition in a production environment., It can
only be used for development.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<sam.s.kong@.gmail.com> wrote in message
news:1133286216.659677.70150@.g47g2000cwa.googlegro ups.com...
> Hi!
> We're planning to run a web application as an intranet on Windows 2000
> Server + IIS (ASP) + SQLServer 7.0.
> We have a BackOffice 4.5 Developer Edition which includes SQLServer
> 7.0.
> Can we use the SQLServer Developer Edition for our intranet?
>
> Thanks.
> Sam
>

About SQL Server 7.0 Developer Edition

Hi!
We're planning to run a web application as an intranet on Windows 2000
Server + IIS (ASP) + SQLServer 7.0.
We have a BackOffice 4.5 Developer Edition which includes SQLServer
7.0.
Can we use the SQLServer Developer Edition for our intranet?
Thanks.
SamHi
No. You may not use Developer Edition in a production environment., It can
only be used for development.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<sam.s.kong@.gmail.com> wrote in message
news:1133286216.659677.70150@.g47g2000cwa.googlegroups.com...
> Hi!
> We're planning to run a web application as an intranet on Windows 2000
> Server + IIS (ASP) + SQLServer 7.0.
> We have a BackOffice 4.5 Developer Edition which includes SQLServer
> 7.0.
> Can we use the SQLServer Developer Edition for our intranet?
>
> Thanks.
> Sam
>

About SQL Server 7.0 Developer Edition

Hi!
We're planning to run a web application as an intranet on Windows 2000
Server + IIS (ASP) + SQLServer 7.0.
We have a BackOffice 4.5 Developer Edition which includes SQLServer
7.0.
Can we use the SQLServer Developer Edition for our intranet?
Thanks.
SamHi
No. You may not use Developer Edition in a production environment., It can
only be used for development.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<sam.s.kong@.gmail.com> wrote in message
news:1133286216.659677.70150@.g47g2000cwa.googlegroups.com...
> Hi!
> We're planning to run a web application as an intranet on Windows 2000
> Server + IIS (ASP) + SQLServer 7.0.
> We have a BackOffice 4.5 Developer Edition which includes SQLServer
> 7.0.
> Can we use the SQLServer Developer Edition for our intranet?
>
> Thanks.
> Sam
>

about migrating to 2005 from 2000.

Hi, since havenot done migration yet, my company is planning to migrate from sql 2000 to sql 2005 server. and i am in group for capacity planning. can anybody suggest me the steps i should be really taking in order to migrate to 2005, if anyone has done the it in real world with database up to 50g.
thanks
Dereki would start with running the upgrade advisor. this does not catch everything. one thing i found annoying is that old stored procedures with words in them that became new keywords in 2K5 broke all over the place.