Showing posts with label performance. Show all posts
Showing posts with label performance. Show all posts

Wednesday, March 28, 2012

install Performance Tools

Hi,
I have looked for some time now and I cannot see how to install the
Performance Tools in a pre installed SQL Server 20005. How do I go about this?
I am unaware of Microsoft Provided Performance tools. Are you by chance
talking about performance monitor? You can launch this via perfmon from the
command line. The DMV's also provide important performance counters.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"SteelMillDon" <SteelMillDon@.noemail.noemail> wrote in message
news:2131CACB-066A-40F5-A95E-B3EAFE509DB5@.microsoft.com...
> Hi,
> I have looked for some time now and I cannot see how to install the
> Performance Tools in a pre installed SQL Server 20005. How do I go about
> this?
|||Hi,
No. SQL Server has these tool. I belive it is what edition I have. I need
the standard not the workgroup.
Thanks
"Hilary Cotter" wrote:

> I am unaware of Microsoft Provided Performance tools. Are you by chance
> talking about performance monitor? You can launch this via perfmon from the
> command line. The DMV's also provide important performance counters.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "SteelMillDon" <SteelMillDon@.noemail.noemail> wrote in message
> news:2131CACB-066A-40F5-A95E-B3EAFE509DB5@.microsoft.com...
>
>
|||Hi,
I have upgraded from workgroup to standard and still I cannot find how to
add the sql performanve tools. We have one system that hase the tools on it
that was configured from the start but we cannot seem to add the tools if
they where not installed from the start. Please help.
Thanks
"SteelMillDon" wrote:

> Hi,
> I have looked for some time now and I cannot see how to install the
> Performance Tools in a pre installed SQL Server 20005. How do I go about this?
|||Do you have access to the SQL install media? If so, try running Setup again.
Sorry I can't be specific, I don't know 2005, but at some point during the
install, you should be offered the opportunity to install missing compnents,
such as Performance Tools.
Technosapien
"SteelMillDon" wrote:
[vbcol=seagreen]
> Hi,
> I have upgraded from workgroup to standard and still I cannot find how to
> add the sql performanve tools. We have one system that hase the tools on it
> that was configured from the start but we cannot seem to add the tools if
> they where not installed from the start. Please help.
> Thanks
> "SteelMillDon" wrote:
|||I have tried this but cannot find where this option is. I'm tryin to get
help from microsoft with my msdn support.
"Technosapien" wrote:
[vbcol=seagreen]
> Do you have access to the SQL install media? If so, try running Setup again.
> Sorry I can't be specific, I don't know 2005, but at some point during the
> install, you should be offered the opportunity to install missing compnents,
> such as Performance Tools.
> --
> Technosapien
>
> "SteelMillDon" wrote:
|||Are you looking for SQL Server Profiler and Database Engine Tuning Advisor?
Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/
"SteelMillDon" <SteelMillDon@.noemail.noemail> wrote in message
news:C5534832-C3E4-48ED-AABC-3F20E8C5116C@.microsoft.com...[vbcol=seagreen]
>I have tried this but cannot find where this option is. I'm tryin to get
> help from microsoft with my msdn support.
> "Technosapien" wrote:
|||Yes
"Paul A. Mestemaker II [MSFT]" wrote:

> Are you looking for SQL Server Profiler and Database Engine Tuning Advisor?
> Paul A. Mestemaker II
> Program Manager
> Microsoft SQL Server Manageability
> http://blogs.msdn.com/sqlrem/
> "SteelMillDon" <SteelMillDon@.noemail.noemail> wrote in message
> news:C5534832-C3E4-48ED-AABC-3F20E8C5116C@.microsoft.com...
>
>
|||What SKU of SQL Server are you using? (Express Edition, Workgroup Edition,
Standard Edition, Enterprise Edition, or Developer Edition). I believe
these performance tools are only included in Standard, Enterprise, and
Developer. They should be installed if you check the "Workstation
Components" in setup.
Feature Comparison Matrix:
http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
With a default installation of the workstation components, you can start
Profiler by running "Profiler90" from the run menu.
Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/
"SteelMillDon" <SteelMillDon@.noemail.noemail> wrote in message
news:89E6CBD9-FC09-4D3D-B3CB-A1A26EED57F7@.microsoft.com...[vbcol=seagreen]
> Yes
> "Paul A. Mestemaker II [MSFT]" wrote:
|||Standard. Read my past posts for more details
"Paul A. Mestemaker II [MSFT]" wrote:

> What SKU of SQL Server are you using? (Express Edition, Workgroup Edition,
> Standard Edition, Enterprise Edition, or Developer Edition). I believe
> these performance tools are only included in Standard, Enterprise, and
> Developer. They should be installed if you check the "Workstation
> Components" in setup.
> Feature Comparison Matrix:
> http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
> With a default installation of the workstation components, you can start
> Profiler by running "Profiler90" from the run menu.
> Paul A. Mestemaker II
> Program Manager
> Microsoft SQL Server Manageability
> http://blogs.msdn.com/sqlrem/
> "SteelMillDon" <SteelMillDon@.noemail.noemail> wrote in message
> news:89E6CBD9-FC09-4D3D-B3CB-A1A26EED57F7@.microsoft.com...
>
>
sql

Friday, March 9, 2012

Inserts performance

Hello.
I'm trying to optimize a process that synchronizes some proprietary objects to a database. Currently, this synchronization is made via ADO by using SQL statements. There are two differents actions that are made: the first initialization is made by a lot of INSERTs into different tables and the second operation is inserting/updating/removing objects one by one when it has changed.
Our objects are stored across multiple tables because they have a hierarchical structure.
My question is about performance for the first operation : what is the best way to achieve multiple inserts across multiple tables in the shortest time. We actually use ADO and an INSERT statement for each row which is obviously not the preferred method.
Thanks.One method you can try is:: Define cluster indexes on each table such that new inserts are distributed on multiple pages. Not only on last page.
Multiple inserts on last page will slow down ur speed.|||Thanks, but i was thinking about client methods to improve the performance of inserts such as batches. But i've got no knowledge of advanced methods. The actual problem is that one request is sent by INSERT of a row and we have roughly 1 million inserts to do.

Originally posted by avneesh
One method you can try is:: Define cluster indexes on each table such that new inserts are distributed on multiple pages. Not only on last page.
Multiple inserts on last page will slow down ur speed.|||1. SET NOCOUNT ON
removes useless "(1 row(s) affected)" messages (server feedback)

2. Create SPs for the insert
SPs are precompiled and therefore faster.

Insertion and Updates on 20.000.000 tuples table.

Hi,
I have a table with 20.000.000 of tuples.
I have been monitoring the performance of the insertion and updates,
but not convince me at all.
The table have 30 columns, what and 12 of it, are calcultated column.

The test that i do was this:

1 Insertion with all the columns and calculing the calcultated columns
in the insertion sentence.

1 insertion and all the columns calculated in @.vars..

1 insertion with the basic fields, and 10 updates.

And the result was that the last test was the most performant.

What is your opinion?Andrix (elkpichico@.gmail.com) writes:
> I have a table with 20.000.000 of tuples.
> I have been monitoring the performance of the insertion and updates,
> but not convince me at all.
> The table have 30 columns, what and 12 of it, are calcultated column.
> The test that i do was this:
> 1 Insertion with all the columns and calculing the calcultated columns
> in the insertion sentence.
> 1 insertion and all the columns calculated in @.vars..
> 1 insertion with the basic fields, and 10 updates.
> And the result was that the last test was the most performant.
> What is your opinion?

That your posting is not very clear. I would take "calculated columns"
to mean "computed columns", but since you can't insert explicit values
in computed columns that does not really fit.

Why not post the code you used, so it's easier to understand what you
are talking about.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi.

The test that i do, were this:
select @.PK = ....
INSERT INTO shared_calc
VALUES (@.PK,10,20,223,"calculo trivial",...... ,@.imp * @.ohter, ...)

I mean that in the same Insert sentence, i do all the calcs to insert
in the table.

the other,
was
select @.cal1 = @.imp * @.other
select @.cal2 = @.imp * @.other - @.umbral

select @.PK = ....
INSERT INTO shared_calc
VALUES (@.PK,10,20,223,"calculo trivial",...... ,@.calc1,@.calc2, ...)

and the last one was:
select @.PK = ....
INSERT INTO shared_calc
VALUES (@.PK,10,20,223,"calculo trivial",...... )

select @.cal1 = @.imp * @.other
select @.cal2 = @.imp * @.other - @.umbral

update shared_calc_imp
set calc1 = @.calc1
where pk = @.PK

update shared_calc_imp
set calc2 = @.calc2
where pk = @.PK

thanks!

Andrix.

Erland Sommarskog wrote:
> Andrix (elkpichico@.gmail.com) writes:
> > I have a table with 20.000.000 of tuples.
> > I have been monitoring the performance of the insertion and updates,
> > but not convince me at all.
> > The table have 30 columns, what and 12 of it, are calcultated column.
> > The test that i do was this:
> > 1 Insertion with all the columns and calculing the calcultated columns
> > in the insertion sentence.
> > 1 insertion and all the columns calculated in @.vars..
> > 1 insertion with the basic fields, and 10 updates.
> > And the result was that the last test was the most performant.
> > What is your opinion?
> That your posting is not very clear. I would take "calculated columns"
> to mean "computed columns", but since you can't insert explicit values
> in computed columns that does not really fit.
> Why not post the code you used, so it's easier to understand what you
> are talking about.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Do not store calculations in a table. You can do the calculations in a
VIEW, in the application or in computed columns (a proprietary
shorthand) for a VIEW. This will save you disk space of course. It
will also run faster, since reading from a disk is very slow compared
to math done in main storage. But the real benefit is data integrity,
which your approach will destroy.|||Andrix (elkpichico@.gmail.com) writes:
> The test that i do, were this:
> select @.PK = ....
> INSERT INTO shared_calc
> VALUES (@.PK,10,20,223,"calculo trivial",...... ,@.imp * @.ohter, ...)
> I mean that in the same Insert sentence, i do all the calcs to insert
> in the table.
> the other,
> was
> select @.cal1 = @.imp * @.other
> select @.cal2 = @.imp * @.other - @.umbral
> select @.PK = ....
> INSERT INTO shared_calc
> VALUES (@.PK,10,20,223,"calculo trivial",...... ,@.calc1,@.calc2, ...)
> and the last one was:
> select @.PK = ....
> INSERT INTO shared_calc
> VALUES (@.PK,10,20,223,"calculo trivial",...... )
> select @.cal1 = @.imp * @.other
> select @.cal2 = @.imp * @.other - @.umbral
> update shared_calc_imp
> set calc1 = @.calc1
> where pk = @.PK
> update shared_calc_imp
> set calc2 = @.calc2
> where pk = @.PK

And you are saying that the last test had the best performance?
Surprising.

But how many times did you run each test? Did you ensure that there
was no other load on the server? Did you work with the same table
that you just added to each time? Or did you recreate the table for
each test? And what were the results in numbers? Were the huge
differences?

Running performance tests requires care to avoid sources of error. One
very important is that any measurement below 50 ms contains to much
white noise to be reliable.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx