Performance effect if SQL2000 and SQL2005 on same box

Hi everyone,
I am going to upgrade SQL2000 to SQL2005 because 3rd party application
requires SQL2005. My plan is upgrade my current database on SQL2000 to
SQL2005, but keeping SQL2000 for fall back plan.
Is there any impact on performance if I have SQL2005 and SQL2000 running on
the same machine?
Thanks alot for any help in advance.
Han.
The issues are not different from running two SQL2000 instances or two
SQL2005 instances.
Linchi
"Han" wrote:

> Hi everyone,
> I am going to upgrade SQL2000 to SQL2005 because 3rd party application
> requires SQL2005. My plan is upgrade my current database on SQL2000 to
> SQL2005, but keeping SQL2000 for fall back plan.
> Is there any impact on performance if I have SQL2005 and SQL2000 running on
> the same machine?
> Thanks alot for any help in advance.
> Han.
sql

Performance effect if SQL2000 and SQL2005 on same box

Hi everyone,
I am going to upgrade SQL2000 to SQL2005 because 3rd party application
requires SQL2005. My plan is upgrade my current database on SQL2000 to
SQL2005, but keeping SQL2000 for fall back plan.
Is there any impact on performance if I have SQL2005 and SQL2000 running on
the same machine?
Thanks alot for any help in advance.
Han.You will obviously have resource usage of you have both service started at the same time. But having
2000 installed but not started will not degrade performance of a 2005 install on that machine.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Han" <Han@.discussions.microsoft.com> wrote in message
news:FE01070A-7736-47CF-AECA-D9DB9CA5F75E@.microsoft.com...
> Hi everyone,
> I am going to upgrade SQL2000 to SQL2005 because 3rd party application
> requires SQL2005. My plan is upgrade my current database on SQL2000 to
> SQL2005, but keeping SQL2000 for fall back plan.
> Is there any impact on performance if I have SQL2005 and SQL2000 running on
> the same machine?
> Thanks alot for any help in advance.
> Han.|||The issues are not different from running two SQL2000 instances or two
SQL2005 instances.
Linchi
"Han" wrote:
> Hi everyone,
> I am going to upgrade SQL2000 to SQL2005 because 3rd party application
> requires SQL2005. My plan is upgrade my current database on SQL2000 to
> SQL2005, but keeping SQL2000 for fall back plan.
> Is there any impact on performance if I have SQL2005 and SQL2000 running on
> the same machine?
> Thanks alot for any help in advance.
> Han.

Performance and Execution Plan

Performance !!!

Hi!

I am trying to execute an stored procedured that have already been used in another server (the test server ) but it takes 25 hours and in the another server takes just 40 minutes. !!

I have reviewed the indexes, statistics an so on, I have exactly the same quantity of data but cannot explain why does it takes so long in the server that is supposed to be the better one.

When I show de Execution Plan in the Query Analyzer in one server I get:

- Select (0%)

- Compute scalar (0%)

- Remote Query (100 %)

When I executed in the another one I get:

- Select

- Compute Scalar

- Nested Loops Left Outer Join (80%)

- Merge Join / Right

- Remote Query

- Sort

- Hash Match Right …

Even if I make a simple select by a certain period, in one server takes so long and in the another one just a few minutes.

Coul anybody please help me?

Sure... How much memory is on the test server versus the "other" server? How much CPU? 32 bit for both of them? Same operating systems? More data?|||By looking at the execution plan, they don't look like the same query.|||Can you please post some sample queries? If it is distributed queries then it is possible that you are getting a plan that is retrieving large number of rows from remote data source. But this depends on the statisitcs on any local tables that you are joining with and the query. There are ways to force joins to happen on the remote side. But anyway, you need to give more details before I can suggest any solution.|||

There is a chance that on server A you are querying it's local tables

where on server B you are querying remote tables (on server A).

If this is the case, try not to commit huge transactions over the network.

Also the source code for the query and the exact location of the tables would help define the problem

|||

Thank you for all your answers, let me give a little more of detail to clarify.

In my real environment I have this:

I have the problematic stored procedure in one server (let me say "Server A") and this procedure gets
data from Itself and server B (the linked server).

Basically the procedure makes a select from remote tables into a temporary table with
just a "where" of dates and another inserts in local tables.

To simplify the case I extract just the main select I use and it is exactly the same:
takes hours and hours to get the results of the "select".
But this "select" when is executed in my Test environment takes just a few minutes. (I am talking of 25 Hours vs. 40 Minutes aprox.)

What I have already checked is to have the same indexes, same statistics created
in both servers of both environments (server A, B, X, Y)

I also may say, the databases that I have in my Real Environment are just a Restore of the Databases that I have in my Test Environment.

The Hardware...
Real Environment:
Server A: 4 GB RAM,
Server B: 4 GM RAM (The linked Server)

Test Environment:
Server X: 4 GM RAM
Server Y: 1 GB RAM (The linked Server)

The Query...

For this query I checked the next points after executed in both environments:

Results of Execution Plan:
The results between Test an Real are very different, because while in the Real Environment there are a lot of
Logical and Physical Operators that describe the steps followed.

In terms of Costs the most expensive is "Nested Loops/Left Outer Join" with
80% of total execution plan. (this step just does not appear when I use my test environment).

In the Test Environment the only steps that i can see in the execution plan are:

Select 0%,

Compute Scalar 0% and

Remote Query 100%|||

For starters, I want to suggest that you create a view on the linked server with the query and use it instead remotely. This will provide the best performance and the plan will be local to that server in most cases. This provides better maintainability also and more tuning options. Also, please make sure that you are running the same version + service pack of SQL Server in your environment for comparison purposes.

Having said this, since all of the tables in the query are remote tables I don't see why we should even be trying to do any operations locally. It is possible that your test queries are little bit different or you are using different parameters. I am assuming that you are comparing your SP call with the same parameters between the two environments.

|||

Well, the thing here is that the stored procedured that I have to use have more statements, some of them need to access local tables. The queries that i used in both environments are exactly the same and over the same quantity of data (just a copy of one into the another).

As the main cost that i have is in the extract of the stored procedure that I show here, I have also tried to make the select step by step. I mean adding table by table in order to find any reason for the excessive time spend. I have noticed that after adding the table that have an “inner join”the process gets worst. So please, if you have any other idea let me know.

Performance -- Plan Cost and Scan

I am looking some query stats and have a query.
Query 1 Plan Cost: 5.312 -- Execution 20.497 seconds -- total time 23.848
seconds --Physical Reads 1,404, Logical Reads, 927,701,Scans 621, Read Ahead
Reads - 3,976
Query 2 Plan Cost 9.469 -- Exection 00.143 seconds -- total time 02.016
seconds -- physical reads 0, logical reads 7146, sans 622 , read ahead reads
100
Query 2 obviously runs around 95% faster and it would seem difference
between plan cost is very little.
What is the downside of having the plan cost go up? Under heavier load
would this start to perform poorly because of that? At what point does the
plan cost become more important than the other items? Or are the logical
reads a better indiciator of which way to go.
Sorry for so many questions -- really starting to use the tools to tweak
queries and want to make sure I am going down right paths.
Thanks!!!Execution plan costs are worth looking at, but must be taken with a
bit of skepticism. The cost in the execution plan is just an
estimate. The actual results can be quite different. You can even
get completely different execution plans sometimes just by updating
statistics.
Roy Harvey
Beacon Falls, CT
On Tue, 28 Feb 2006 10:46:07 -0500, "Brian" <brian@.nospam.com> wrote:

>I am looking some query stats and have a query.
>Query 1 Plan Cost: 5.312 -- Execution 20.497 seconds -- total time 23.848
>seconds --Physical Reads 1,404, Logical Reads, 927,701,Scans 621, Read Ahea
d
>Reads - 3,976
>Query 2 Plan Cost 9.469 -- Exection 00.143 seconds -- total time 02.016
>seconds -- physical reads 0, logical reads 7146, sans 622 , read ahead read
s
>100
>
>Query 2 obviously runs around 95% faster and it would seem difference
>between plan cost is very little.
>What is the downside of having the plan cost go up? Under heavier load
>would this start to perform poorly because of that? At what point does the
>plan cost become more important than the other items? Or are the logical
>reads a better indiciator of which way to go.
>Sorry for so many questions -- really starting to use the tools to tweak
>queries and want to make sure I am going down right paths.
>Thanks!!!
>

Performance

we have application and customized it by using VBA
In VBA Code i run some queries witch summrize some information
we plan set the query in stored procedure then we execute it from VBA
insted of run query from VBA
so how this will effect in performance
Thank you for your help
> so how this will effect in performance
From the SQL Server 2000 Books Online:
<Excerpt href="http://links.10026.com/?link=optimsql.chm::/odp_tun_1a_6x45.htm">
All well-designed Microsoft SQL ServerT 2000 applications should use stored
procedures. This is true whether or not the business logic of the
application is written into stored procedures. Even standard Transact-SQL
statements with no business logic component gain a performance advantage
when packaged as stored procedures with parameters. Transact-SQL statements
compiled into stored procedures can save a significant amount of processing
at execution time. For more information, see Stored Procedures.
Another advantage of stored procedures is that client execution requests use
the network more efficiently than equivalent Transact-SQL statements sent to
the server. For example, suppose an application needs to insert a large
binary value into an image data column. To send the data in an INSERT
statement, the application must convert the binary value to a character
string (doubling its size), and then send it to the server. The server then
converts the value back into a binary format for storage in the image
column. In contrast, the application can create a stored procedure of the
form:
CREATE PROCEDURE P(@.p1 image) AS INSERT T VALUES (@.p1)
When the client application requests an execution of procedure P, the image
parameter value will stay in binary format all the way to the server,
thereby saving processing time and network traffic.
SQL Server stored procedures can provide even greater performance gains when
they include business services logic because it moves the processing to the
data, rather than moving the data to the processing.
</Excerpt>
There are also security benefits over direct table access.
Stored procedures are a Best Practice but whether or not your application
will experience faster performance simply by switching from embedded queries
to procs depends on other factors. It is doubtful you will see any
noticeable improvement if you currently have performance problems due to
lack of tuning or poor application design.
Hope this helps.
Dan Guzman
SQL Server MVP
"Serri" <Serrihakmi@.yahoo.com> wrote in message
news:1141037915.122884.185280@.i39g2000cwa.googlegr oups.com...
> we have application and customized it by using VBA
> In VBA Code i run some queries witch summrize some information
> we plan set the query in stored procedure then we execute it from VBA
> insted of run query from VBA
> so how this will effect in performance
>
> Thank you for your help
>

Performance

we have application and customized it by using VBA
In VBA Code i run some queries witch summrize some information
we plan set the query in stored procedure then we execute it from VBA
insted of run query from VBA
so how this will effect in performance
Thank you for your help> so how this will effect in performance
From the SQL Server 2000 Books Online:
<Excerpt href="http://links.10026.com/?link=optimsql.chm::/odp_tun_1a_6x45.htm">
All well-designed Microsoft SQL ServerT 2000 applications should use stored
procedures. This is true whether or not the business logic of the
application is written into stored procedures. Even standard Transact-SQL
statements with no business logic component gain a performance advantage
when packaged as stored procedures with parameters. Transact-SQL statements
compiled into stored procedures can save a significant amount of processing
at execution time. For more information, see Stored Procedures.
Another advantage of stored procedures is that client execution requests use
the network more efficiently than equivalent Transact-SQL statements sent to
the server. For example, suppose an application needs to insert a large
binary value into an image data column. To send the data in an INSERT
statement, the application must convert the binary value to a character
string (doubling its size), and then send it to the server. The server then
converts the value back into a binary format for storage in the image
column. In contrast, the application can create a stored procedure of the
form:
CREATE PROCEDURE P(@.p1 image) AS INSERT T VALUES (@.p1)
When the client application requests an execution of procedure P, the image
parameter value will stay in binary format all the way to the server,
thereby saving processing time and network traffic.
SQL Server stored procedures can provide even greater performance gains when
they include business services logic because it moves the processing to the
data, rather than moving the data to the processing.
</Excerpt>
There are also security benefits over direct table access.
Stored procedures are a Best Practice but whether or not your application
will experience faster performance simply by switching from embedded queries
to procs depends on other factors. It is doubtful you will see any
noticeable improvement if you currently have performance problems due to
lack of tuning or poor application design.
Hope this helps.
Dan Guzman
SQL Server MVP
"Serri" <Serrihakmi@.yahoo.com> wrote in message
news:1141037915.122884.185280@.i39g2000cwa.googlegroups.com...
> we have application and customized it by using VBA
> In VBA Code i run some queries witch summrize some information
> we plan set the query in stored procedure then we execute it from VBA
> insted of run query from VBA
> so how this will effect in performance
>
> Thank you for your help
>

Performance

we have application and customized it by using VBA
In VBA Code i run some queries witch summrize some information
we plan set the query in stored procedure then we execute it from VBA
insted of run query from VBA
so how this will effect in performance
Thank you for your help> so how this will effect in performance
From the SQL Server 2000 Books Online:
<Excerpt href="http://links.10026.com/?link=optimsql.chm::/odp_tun_1a_6x45.htm">
All well-designed Microsoft® SQL ServerT 2000 applications should use stored
procedures. This is true whether or not the business logic of the
application is written into stored procedures. Even standard Transact-SQL
statements with no business logic component gain a performance advantage
when packaged as stored procedures with parameters. Transact-SQL statements
compiled into stored procedures can save a significant amount of processing
at execution time. For more information, see Stored Procedures.
Another advantage of stored procedures is that client execution requests use
the network more efficiently than equivalent Transact-SQL statements sent to
the server. For example, suppose an application needs to insert a large
binary value into an image data column. To send the data in an INSERT
statement, the application must convert the binary value to a character
string (doubling its size), and then send it to the server. The server then
converts the value back into a binary format for storage in the image
column. In contrast, the application can create a stored procedure of the
form:
CREATE PROCEDURE P(@.p1 image) AS INSERT T VALUES (@.p1)
When the client application requests an execution of procedure P, the image
parameter value will stay in binary format all the way to the server,
thereby saving processing time and network traffic.
SQL Server stored procedures can provide even greater performance gains when
they include business services logic because it moves the processing to the
data, rather than moving the data to the processing.
</Excerpt>
There are also security benefits over direct table access.
Stored procedures are a Best Practice but whether or not your application
will experience faster performance simply by switching from embedded queries
to procs depends on other factors. It is doubtful you will see any
noticeable improvement if you currently have performance problems due to
lack of tuning or poor application design.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Serri" <Serrihakmi@.yahoo.com> wrote in message
news:1141037915.122884.185280@.i39g2000cwa.googlegroups.com...
> we have application and customized it by using VBA
> In VBA Code i run some queries witch summrize some information
> we plan set the query in stored procedure then we execute it from VBA
> insted of run query from VBA
> so how this will effect in performance
>
> Thank you for your help
>

percentage in matrix table

Hello Chris,
I have a data set with the following fileds:
plan
month
leads
orders
I have made the following matrix table: month for column, plan for row, and
leads and orders for the details, and subtotals.
--plan--Jan--Feb--Total
--leads orders-leads orders-leads orders
GOLD --9--8--17--12--26--20
GOLDP --9--7--38--21--47--28
PLATINUM --6--3--8--6--14--9
PLATINUMP --550--343--954--572--1504--915
GrandTotal --574--361--1017--611--1591--972
i want to add another column that calculates the percentage of
total orders / total leads, so it should look like this
--plan--Jan--Feb--Total--% ordered
-- leads orders leads orders leads orders
GOLD --9--8--17--12--26--20--76.92 %
GOLDP --9--7--38--21--47--28--59.57 %
PLATINUM --6--3--8--6--14--9--64.29 %
PLATINUMP --550--343--954--572--1504--915--60.84 %
GrandTotal --574--361--1017--611--1591--972
I have spent a many hours working on it. i ended up adding another table
just for the % ordered column, but it's impossible to align it with the
matrix table.
Is there anyway i can integrate it into the matrix table?
thanks
aaronHi, Roni
It is very strange how you ended with such % calculations they are not
correlate to the numbers (spatially in gold, platinum and so on it is
probably means something),
In any case, you can use calculated field with formula expression in the
same matrix.
Regards, ProJester,
MSN Programmer.
"aaron" wrote:
> Hello Chris,
> I have a data set with the following fileds:
> plan
> month
> leads
> orders
> I have made the following matrix table: month for column, plan for row, and
> leads and orders for the details, and subtotals.
> --plan--Jan--Feb--Total
> --leads orders-leads orders-leads orders
> GOLD --9--8--17--12--26--20
> GOLDP --9--7--38--21--47--28
> PLATINUM --6--3--8--6--14--9
> PLATINUMP --550--343--954--572--1504--915
> GrandTotal --574--361--1017--611--1591--972
> i want to add another column that calculates the percentage of
> total orders / total leads, so it should look like this
> --plan--Jan--Feb--Total--% ordered
> -- leads orders leads orders leads orders
> GOLD --9--8--17--12--26--20--76.92 %
> GOLDP --9--7--38--21--47--28--59.57 %
> PLATINUM --6--3--8--6--14--9--64.29 %
> PLATINUMP --550--343--954--572--1504--915--60.84 %
> GrandTotal --574--361--1017--611--1591--972
> I have spent a many hours working on it. i ended up adding another table
> just for the % ordered column, but it's impossible to align it with the
> matrix table.
> Is there anyway i can integrate it into the matrix table?
> thanks
> aaron|||% calculations are done by dividing total orders/total leads. why is it
strange?
and how do i do "use calculated field with formula expression in the
same matrix"? i can't figure out how to add it.
"ProJester" wrote:
> Hi, Roni
> It is very strange how you ended with such % calculations they are not
> correlate to the numbers (spatially in gold, platinum and so on it is
> probably means something),
> In any case, you can use calculated field with formula expression in the
> same matrix.
> Regards, ProJester,
> MSN Programmer.
> "aaron" wrote:
> > Hello Chris,
> >
> > I have a data set with the following fileds:
> > plan
> > month
> > leads
> > orders
> >
> > I have made the following matrix table: month for column, plan for row, and
> > leads and orders for the details, and subtotals.
> > --plan--Jan--Feb--Total
> > --leads orders-leads orders-leads orders
> > GOLD --9--8--17--12--26--20
> > GOLDP --9--7--38--21--47--28
> > PLATINUM --6--3--8--6--14--9
> > PLATINUMP --550--343--954--572--1504--915
> > GrandTotal --574--361--1017--611--1591--972
> >
> > i want to add another column that calculates the percentage of
> > total orders / total leads, so it should look like this
> >
> > --plan--Jan--Feb--Total--% ordered
> > -- leads orders leads orders leads orders
> > GOLD --9--8--17--12--26--20--76.92 %
> > GOLDP --9--7--38--21--47--28--59.57 %
> > PLATINUM --6--3--8--6--14--9--64.29 %
> > PLATINUMP --550--343--954--572--1504--915--60.84 %
> > GrandTotal --574--361--1017--611--1591--972
> >
> > I have spent a many hours working on it. i ended up adding another table
> > just for the % ordered column, but it's impossible to align it with the
> > matrix table.
> >
> > Is there anyway i can integrate it into the matrix table?
> >
> > thanks
> >
> > aaron|||the % is calculated by total orders / total leads. why is it strange?
and how exactly do you do "you can use calculated field with formula
expression in the same matrix"? i can't figure it out.
also, is there anyway to anchor the column heading when they scroll down
like it in ms excel?
thanks
"ProJester" wrote:
> Hi, Roni
> It is very strange how you ended with such % calculations they are not
> correlate to the numbers (spatially in gold, platinum and so on it is
> probably means something),
> In any case, you can use calculated field with formula expression in the
> same matrix.
> Regards, ProJester,
> MSN Programmer.
> "aaron" wrote:
> > Hello Chris,
> >
> > I have a data set with the following fileds:
> > plan
> > month
> > leads
> > orders
> >
> > I have made the following matrix table: month for column, plan for row, and
> > leads and orders for the details, and subtotals.
> > --plan--Jan--Feb--Total
> > --leads orders-leads orders-leads orders
> > GOLD --9--8--17--12--26--20
> > GOLDP --9--7--38--21--47--28
> > PLATINUM --6--3--8--6--14--9
> > PLATINUMP --550--343--954--572--1504--915
> > GrandTotal --574--361--1017--611--1591--972
> >
> > i want to add another column that calculates the percentage of
> > total orders / total leads, so it should look like this
> >
> > --plan--Jan--Feb--Total--% ordered
> > -- leads orders leads orders leads orders
> > GOLD --9--8--17--12--26--20--76.92 %
> > GOLDP --9--7--38--21--47--28--59.57 %
> > PLATINUM --6--3--8--6--14--9--64.29 %
> > PLATINUMP --550--343--954--572--1504--915--60.84 %
> > GrandTotal --574--361--1017--611--1591--972
> >
> > I have spent a many hours working on it. i ended up adding another table
> > just for the % ordered column, but it's impossible to align it with the
> > matrix table.
> >
> > Is there anyway i can integrate it into the matrix table?
> >
> > thanks
> >
> > aaron|||Hi, Aaron,
do you figure out how to add that last percent column? If Yes, please let
me know how. Thanks!
Henry
"aaron" wrote:
> Hello Chris,
> I have a data set with the following fileds:
> plan
> month
> leads
> orders
> I have made the following matrix table: month for column, plan for row, and
> leads and orders for the details, and subtotals.
> --plan--Jan--Feb--Total
> --leads orders-leads orders-leads orders
> GOLD --9--8--17--12--26--20
> GOLDP --9--7--38--21--47--28
> PLATINUM --6--3--8--6--14--9
> PLATINUMP --550--343--954--572--1504--915
> GrandTotal --574--361--1017--611--1591--972
> i want to add another column that calculates the percentage of
> total orders / total leads, so it should look like this
> --plan--Jan--Feb--Total--% ordered
> -- leads orders leads orders leads orders
> GOLD --9--8--17--12--26--20--76.92 %
> GOLDP --9--7--38--21--47--28--59.57 %
> PLATINUM --6--3--8--6--14--9--64.29 %
> PLATINUMP --550--343--954--572--1504--915--60.84 %
> GrandTotal --574--361--1017--611--1591--972
> I have spent a many hours working on it. i ended up adding another table
> just for the % ordered column, but it's impossible to align it with the
> matrix table.
> Is there anyway i can integrate it into the matrix table?
> thanks
> aaron