Percentage of total: dear

Showing posts with label dear. Show all posts

Wednesday, March 28, 2012

Performance dropped after upgraded to MS SQL 2000

Dear all,
Last Saturday, I upgraded my SQL 7.0 database to SQL 2000 Standard Edition
succesfully.
I also executed "sp_updatestats" and reindexed all the databases, but I
found the server's overall performance is worse than before.
That means SQL 2000 is slower than SQL 7.0, why ?
Possibly you have bad luck with some queries for which SQL Server picks less good execution plans in
2000 compared to 7.0. You need to work those queries and see why. One plausible reason is below:
WHERE col = value
If col and value (or parameter or variable) aren't of the same datatype, then 7.0 would convert the
non-column side toe the column datatype. 2000 converts more correctly, according to datatype
precedence documentation in Books Online. This can case your queries be effectively the same as:
WHERE CAST(col AS dt) = value
And since the column side isn't alone (it is wrapped in a function), typically index on that column
cannot be used.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John" <cpjohn@.netvigator.com> wrote in message news:d7cem9$b4i20@.imsp212.netvigator.com...
> Dear all,
> Last Saturday, I upgraded my SQL 7.0 database to SQL 2000 Standard Edition
> succesfully.
> I also executed "sp_updatestats" and reindexed all the databases, but I
> found the server's overall performance is worse than before.
> That means SQL 2000 is slower than SQL 7.0, why ?
>
>
|||Thanks a lot.
I will try it.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O5p6mi0ZFHA.3784@.TK2MSFTNGP12.phx.gbl...
> Possibly you have bad luck with some queries for which SQL Server picks
less good execution plans in
> 2000 compared to 7.0. You need to work those queries and see why. One
plausible reason is below:
> WHERE col = value
> If col and value (or parameter or variable) aren't of the same datatype,
then 7.0 would convert the
> non-column side toe the column datatype. 2000 converts more correctly,
according to datatype
> precedence documentation in Books Online. This can case your queries be
effectively the same as:
> WHERE CAST(col AS dt) = value
> And since the column side isn't alone (it is wrapped in a function),
typically index on that column
> cannot be used.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John" <cpjohn@.netvigator.com> wrote in message
news:d7cem9$b4i20@.imsp212.netvigator.com...[vbcol=seagreen]
Edition
>

Performance dropped after upgraded to MS SQL 2000

Dear all,
Last Saturday, I upgraded my SQL 7.0 database to SQL 2000 Standard Edition
succesfully.
I also executed "sp_updatestats" and reindexed all the databases, but I
found the server's overall performance is worse than before.
That means SQL 2000 is slower than SQL 7.0, why ?Possibly you have bad luck with some queries for which SQL Server picks less
good execution plans in
2000 compared to 7.0. You need to work those queries and see why. One plausi
ble reason is below:
WHERE col = value
If col and value (or parameter or variable) aren't of the same datatype, the
n 7.0 would convert the
non-column side toe the column datatype. 2000 converts more correctly, accor
ding to datatype
precedence documentation in Books Online. This can case your queries be effe
ctively the same as:
WHERE CAST(col AS dt) = value
And since the column side isn't alone (it is wrapped in a function), typical
ly index on that column
cannot be used.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John" <cpjohn@.netvigator.com> wrote in message news:d7cem9$b4i20@.imsp212.netvigator.com...[
vbcol=seagreen]
> Dear all,
> Last Saturday, I upgraded my SQL 7.0 database to SQL 2000 Standard Edition
> succesfully.
> I also executed "sp_updatestats" and reindexed all the databases, but I
> found the server's overall performance is worse than before.
> That means SQL 2000 is slower than SQL 7.0, why ?
>
>[/vbcol]|||Thanks a lot.
I will try it.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O5p6mi0ZFHA.3784@.TK2MSFTNGP12.phx.gbl...
> Possibly you have bad luck with some queries for which SQL Server picks
less good execution plans in
> 2000 compared to 7.0. You need to work those queries and see why. One
plausible reason is below:
> WHERE col = value
> If col and value (or parameter or variable) aren't of the same datatype,
then 7.0 would convert the
> non-column side toe the column datatype. 2000 converts more correctly,
according to datatype
> precedence documentation in Books Online. This can case your queries be
effectively the same as:
> WHERE CAST(col AS dt) = value
> And since the column side isn't alone (it is wrapped in a function),
typically index on that column
> cannot be used.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John" <cpjohn@.netvigator.com> wrote in message
news:d7cem9$b4i20@.imsp212.netvigator.com...
Edition[vbcol=seagreen]
>

Performance Difference between SQL Server 2005 Standard Edition and Enterprise Edition

Dear All,

Our database does not use any features which are present in Enterprise
Edition and not present in Standard Edition. We want to know what are
the differences between Standard Edition and Enterprise Edition for
performance. Why should we go for Enterprise Edition when Standard
Edition has all the features required.

We are presently using evaluation versions of SQL Server 2005 Standard
and Enterprise Editions.

Thanks and regards,
Nishant Saini
http://www.simplyjava.comNishant Saini (nishant.saini@.gmail.com) writes:

Quote:

Originally Posted by

We have a database which contains many tables which have millions of
records. When We attach the database with MS SQL Server 2005 Standard
Edition Server and run some queries (having joins, filters etc.) then
they take very long time to execute while when We execute same queries
on Enterprise Edition then they run 10 times faster than on standard
edition.
>
Our database does not use any features which are present in Enterprise
Edition and not present in Standard Edition. We want to know what are
the differences between Standard Edition and Enterprise Edition for
performance. Why should we go for Enterprise Edition when Standard
Edition has all the features required.

There is a topic that covers this in Books Online. On the top of my head
I don't recall anything immediate, but I'm off to other things right now,
and don't want to look around.

Could you post one of your queries you've been testing with and the
query plan on Standard and Enterprise?

I presume that you are running the two editions on the same hardware?

Quote:

Originally Posted by

Dear All,
>
We have a database which contains many tables which have millions of
records. When We attach the database with MS SQL Server 2005 Standard
Edition Server and run some queries (having joins, filters etc.) then
they take very long time to execute while when We execute same queries
on Enterprise Edition then they run 10 times faster than on standard
edition.
>
Our database does not use any features which are present in Enterprise
Edition and not present in Standard Edition. We want to know what are
the differences between Standard Edition and Enterprise Edition for
performance. Why should we go for Enterprise Edition when Standard
Edition has all the features required.
>

The Enterprise Edition offers improvements over the Standard Edition,
especially on parallel operations and caching. If you have a
significant amount of data you will defnitely notice a better
performance with this edition.

Regards,
lucm|||Are there any indexed views in the database? From the Books On Line
(BOL):

"Indexed views can be created in any edition of SQL Server 2005. In
SQL Server 2005 Enterprise Edition, the query optimizer automatically
considers the indexed view. To use an indexed view in all other
editions, the NOEXPAND table hint must be used."

So if there were indexed views, but no references to the table hint,
performance could certainly be far better using the Enterprise
edition.

Roy Harvey
Beacon Falls, CT

On 15 Dec 2006 03:01:20 -0800, "Nishant Saini"
<nishant.saini@.gmail.comwrote:

Quote:

Originally Posted by

>Dear All,
>
>We have a database which contains many tables which have millions of
>records. When We attach the database with MS SQL Server 2005 Standard
>Edition Server and run some queries (having joins, filters etc.) then
>they take very long time to execute while when We execute same queries
>on Enterprise Edition then they run 10 times faster than on standard
>edition.
>
>Our database does not use any features which are present in Enterprise
>Edition and not present in Standard Edition. We want to know what are
>the differences between Standard Edition and Enterprise Edition for
>performance. Why should we go for Enterprise Edition when Standard
>Edition has all the features required.
>
>We are presently using evaluation versions of SQL Server 2005 Standard
>and Enterprise Editions.
>
>Thanks and regards,
>Nishant Saini
>http://www.simplyjava.com

|||Although Enterprise Edition is more aggresive when it comes to
read-aheads, and has some features that can improves performance in some
very specific situations (such a parallel index creation, Advanced
Scanning, etc.), if both systems have the same amount of memory, then I
would not expect a 10-fold performance difference.

How much memory does the system have, and have you assigned this memory
to SQL Server?

You could copy the "fast" database to the "slow" configuration with
detach/attach and see if that makes a difference. This way you can rule
out any database differences (such as one database with up-to-date
statistics and another with unusable or no statistics).

HTH,
Gert-Jan

Nishant Saini wrote:

Quote:

Originally Posted by

>
Dear All,
>
We have a database which contains many tables which have millions of
records. When We attach the database with MS SQL Server 2005 Standard
Edition Server and run some queries (having joins, filters etc.) then
they take very long time to execute while when We execute same queries
on Enterprise Edition then they run 10 times faster than on standard
edition.
>
Our database does not use any features which are present in Enterprise
Edition and not present in Standard Edition. We want to know what are
the differences between Standard Edition and Enterprise Edition for
performance. Why should we go for Enterprise Edition when Standard
Edition has all the features required.
>
We are presently using evaluation versions of SQL Server 2005 Standard
and Enterprise Editions.
>
Thanks and regards,
Nishant Saini
http://www.simplyjava.com

|||"Nishant Saini" <nishant.saini@.gmail.comwrote in message
news:1166180480.672397.223250@.73g2000cwn.googlegro ups.com...

Quote:

Originally Posted by

Assuming identical hardware, a 10x speed difference is usually the
difference between memory and disk access. Clear the procedure and memory
cache prior to side by side testing.

Quote:

Originally Posted by

>
Our database does not use any features which are present in Enterprise
Edition and not present in Standard Edition. We want to know what are
the differences between Standard Edition and Enterprise Edition for
performance. Why should we go for Enterprise Edition when Standard
Edition has all the features required.

I only consider EE if the hardware requires the use.

Quote:

Originally Posted by

>
We are presently using evaluation versions of SQL Server 2005 Standard
and Enterprise Editions.
>
Thanks and regards,
Nishant Saini
http://www.simplyjava.com
>

|||On Fri, 15 Dec 2006 15:54:07 -0600, "Russ Rose" <russrose@.hotmail.com>
wrote:

Quote:

Originally Posted by

>Assuming identical hardware, a 10x speed difference is usually the
>difference between memory and disk access.

My experience, for what it is worth, has been that extreme performance
differences - better or worse - are usually the result of different
execution plans.

Roy Harvey
Beacon Falls, CT|||Thanks for the responses...

Yes, The execution plans are different in both the databases.
Why the execution plans are so different in both the versions of SQL
Server 2005? Can we control the execution plans?

On Dec 16, 6:39 am, Roy Harvey <roy_har...@.snet.netwrote:

Quote:

Originally Posted by

On Fri, 15 Dec 2006 15:54:07 -0600, "Russ Rose" <russr...@.hotmail.com>
wrote:
>

Quote:

Originally Posted by

Assuming identical hardware, a 10x speed difference is usually the
difference between memory and disk access.My experience, for what it is worth, has been that extreme performance

differences - better or worse - are usually the result of different
execution plans.
>
Roy Harvey
Beacon Falls, CT

If there are no indexed views in the database ans data is selected from
tables directly, then should there be any difference in performance?

Quote:

Originally Posted by

>"Indexed views can be created in any edition of SQL Server 2005. In
>SQL Server 2005 Enterprise Edition, the query optimizer automatically
>considers the indexed view. To use an indexed view in all other
>editions, the NOEXPAND table hint must be used."

Thanks and regards,
Nishant Saini
http://www.simplyjava.com|||Nishant Saini (nishant.saini@.gmail.com) writes:

Quote:

Originally Posted by

Thanks for the responses...
>
Yes, The execution plans are different in both the databases.
Why the execution plans are so different in both the versions of SQL
Server 2005?

There could be many reasons for that. Roy Harvey mentioned indexed views
for instance.

If you want a better answer, please post the query and the two plans,
so that we know what we are talking about.

Quote:

Originally Posted by

Can we control the execution plans?

Yes. SQL 2005 actually permit you to specify the plan exactly to using
plan guides. This is definitely an advance feature, and nothing you
should use at a whim.

--
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|||As Nishant said we are not using any special features of Enterprise
Edition. Indexed views are not being used and data is taken directly
from tables.

Regarding the execution plan, it may be different due to different
sizes of both databases. We are right now trying to check execution
plan for similar databases on both editions. We will post our findings
as soon as we get them.

Right now we need to know

1. Enterprise Edition has its own Query running mechanism ( which is
different and faster than Standard Edition mechanism) or NOT ?
2. If it is special, what kind of performance differences we will get
while using Std Edition.
3. Is there any specific DB size ( or table sizes ) for which Standard
Edition is fine and after that one should go for Enterprise.

Thanks in advance
Nitin Goyal

On Dec 16, 4:39 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

Nishant Saini (nishant.sa...@.gmail.com) writes:

Quote:

Originally Posted by

Thanks for the responses...

Quote:

Originally Posted by

Yes, The execution plans are different in both the databases.
Why the execution plans are so different in both the versions of SQL
Server 2005?There could be many reasons for that. Roy Harvey mentioned indexed views

for instance.
>
If you want a better answer, please post the query and the two plans,
so that we know what we are talking about.
>

Quote:

Originally Posted by

Can we control the execution plans?Yes. SQL 2005 actually permit you to specify the plan exactly to using

plan guides. This is definitely an advance feature, and nothing you
should use at a whim.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

|||<nitin.goel@.daffodildb.comwrote in message
news:1166429585.200709.298530@.80g2000cwy.googlegro ups.com...

Quote:

Originally Posted by

As Nishant said we are not using any special features of Enterprise
Edition. Indexed views are not being used and data is taken directly
from tables.
>
Regarding the execution plan, it may be different due to different
sizes of both databases. We are right now trying to check execution
plan for similar databases on both editions. We will post our findings
as soon as we get them.
>
Right now we need to know
>
1. Enterprise Edition has its own Query running mechanism ( which is
different and faster than Standard Edition mechanism) or NOT ?

The query engine is the same.

Quote:

Originally Posted by

2. If it is special, what kind of performance differences we will get
while using Std Edition.
3. Is there any specific DB size ( or table sizes ) for which Standard
Edition is fine and after that one should go for Enterprise.

Not specifically.

However, Enterprise Edition supports more physical RAM which can make some
queries faster.

In addition, it can support things like parellelized query builds.

In general EE is more for specific features (like clustering beyond 2 nodes)
than pure size.

If you have a mission critical database that's 1 gig, but you want an N+2
architecture, you'll want EE.

If you have a datawarehouse that 500 gig that can be down for periods of
time during restores, etc, Standard Edition may be fine.

Check out MS's page on feature differences.

Quote:

Originally Posted by

>
Thanks in advance
Nitin Goyal
>
>
On Dec 16, 4:39 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

>Nishant Saini (nishant.sa...@.gmail.com) writes:

Quote:

Originally Posted by

Thanks for the responses...

Quote:

Originally Posted by

>for instance.
>>
>If you want a better answer, please post the query and the two plans,
>so that we know what we are talking about.
>>

Quote:

Originally Posted by

Can we control the execution plans?Yes. SQL 2005 actually permit you to
specify the plan exactly to using

>plan guides. This is definitely an advance feature, and nothing you
>should use at a whim.
>>
>--
>Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>>
>Books Online for SQL Server 2005
>athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
>Books Online for SQL Server 2000
>athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

|||

Quote:

Originally Posted by

Right now we need to know
>
1. Enterprise Edition has its own Query running mechanism ( which is
different and faster than Standard Edition mechanism) or NOT ?
2. If it is special, what kind of performance differences we will get
while using Std Edition.
3. Is there any specific DB size ( or table sizes ) for which Standard
Edition is fine and after that one should go for Enterprise.
>
Thanks in advance
Nitin Goyal

The engine is not the same. Have a look here:
http://www.microsoft.com/sql/prodin...e-features.mspx
especially in "Scalability and Performance" and "High Availability"
sections.

As for choosing Standard or Enterprise, you should think about
concurrent users and operations, not database size.

Regards,
lucm|||lucm wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

Right now we need to know

Thanks in advance
Nitin Goyal

>
The engine is not the same. Have a look here:
http://www.microsoft.com/sql/prodin...e-features.mspx
especially in "Scalability and Performance" and "High Availability"
sections.
>
As for choosing Standard or Enterprise, you should think about
concurrent users and operations, not database size.
>
Regards,
lucm

Although the storage engine is different (or at least exposes more
features), the query optimizer is the same. So if you are not using
indexed views, you can live with 4 CPU's and you do not need the
advanced OLAP tools, then you can expect the same query performance. The
only documented exception is Advanced Scans which could improve
performance in OLAP type situations.

For a manageability and high availability point of view, there are many
reasons to choose EE, but IMO not from a performance point of view
(given the restrictions above).

If you disagree with me, then please mention a specific feature that
will cause EE to outperform SE (on regular DML statements).

Gert-Jan|||(nitin.goel@.daffodildb.com) writes:

Quote:

Originally Posted by

If you want to compare Standard and Enterprise, you must of course
use the same database on the same hardware. Else the test is meaningless.

Quote:

Originally Posted by

Rather than asking questions on the net, and hope that people will look
up the answers for you, why not look in this place in Books Online
instead:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/81f3e917-884a-4cc8-aca2-0a5fea89f355.htm

--
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|||Here is the H/W and database configuration in this case.

Parallel queries: Parallel Query processing runs only on multiple
processors. In our case only one processor is available to SQL Server.

RAM: it is an OS issue and not of SQL Server. There is no RAM support
specification in SQL Server (EE / SE). By the way, OS used by us is Win
2003 Server EE.

FYI, we have searched MS SQL Server books online + seen the differences
of SE and EE given by MS + asked our local vendor but he knows nothing.
And the queries run by us are select queries with joins on around 5 -1
0 tables. So the picture is still murky and Microsoft is not answering.

And Mr Sommarskog, we do not want anyone to search for us. We are
looking for very specific answers and we will be thankful if someone
can help us (we feel others have also faced same problems.)

Thanks
Nitin Goyal

On Dec 19, 3:14 am, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

(nitin.g...@.daffodildb.com) writes:

Quote:

Originally Posted by

As Nishant said we are not using any special features of Enterprise
Edition. Indexed views are not being used and data is taken directly
from tables.

Quote:

Originally Posted by

Regarding the execution plan, it may be different due to different
sizes of both databases.If you want to compare Standard and Enterprise, you must of course

use the same database on the same hardware. Else the test is meaningless.
>

Quote:

Originally Posted by

up the answers for you, why not look in this place in Books Online
instead:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/81f3e917-884a-4cc8-aca2-0a5fea89f355.htm
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

|||

Quote:

Originally Posted by

System: - HP Proliant ML 370 G4 Server ( Intel Xeon 3.2 GH on Intel
7520 Chipset; Dual CPU capable )
RAM: - 4 GB DDR2 ECC
HDD: - 2 * 73 GB SCSI ( 15000 RPM ) RAID 0
DB size: - 20 GB
Downtime: - 2 hours a day i.e. morning 5 to 7 AM.
>
Parallel queries: Parallel Query processing runs only on multiple
processors. In our case only one processor is available to SQL Server.
>
RAM: it is an OS issue and not of SQL Server. There is no RAM support
specification in SQL Server (EE / SE). By the way, OS used by us is Win
2003 Server EE.
>

First of all, RAID-0 is a no-go. If this is a production server you
need at least a mirror (RAID-1).

Now, do you *need* EE or not? Without a load average it is difficult to
say. Since you purchased a server with a single CPU and a small amount
of RAM, you probably don't have too many concurrent users.

One of my customers has up to 100 concurrent users, he has a hardware
weaker than yours, a bigger database, and he is very happy with SE.
They make quite an extensive use of the server, and they can afford the
downtime when they restore lost data or rebuild indexes.

Regards,
lucm|||nitin.goel@.daffodildb.com wrote:

Quote:

Originally Posted by

>
Here is the H/W and database configuration in this case.
>
System: - HP Proliant ML 370 G4 Server ( Intel Xeon 3.2 GH on Intel
7520 Chipset; Dual CPU capable )
RAM: - 4 GB DDR2 ECC
HDD: - 2 * 73 GB SCSI ( 15000 RPM ) RAID 0
DB size: - 20 GB
Downtime: - 2 hours a day i.e. morning 5 to 7 AM.
[snip]

I am not sure you are aware of this, but the EE is approximately 4 times
as expensive as SE (when using CPU-based licensing). There is no way you
can make a business case that justifies spending an additional $15,000
on EE for performance reasons. If you were to spend another x dollars on
performance it would definitely be in the hardware, in whatever area is
limiting perfomance (in your case most likely in the I/O area). But if
you don't have to spend the money now, then you can postpone such
decision and see how it runs with the current hardware.

As for your maintenance window: it should be more than sufficient to
handle any problems with a database of just 20 GB, so from that point of
view you don't need EE either.

Gert-Jan|||(nitin.goel@.daffodildb.com) writes:

Quote:

Originally Posted by

Here is the H/W and database configuration in this case.
>
System: - HP Proliant ML 370 G4 Server ( Intel Xeon 3.2 GH on Intel
7520 Chipset; Dual CPU capable )
RAM: - 4 GB DDR2 ECC
HDD: - 2 * 73 GB SCSI ( 15000 RPM ) RAID 0
DB size: - 20 GB
Downtime: - 2 hours a day i.e. morning 5 to 7 AM.
>
Parallel queries: Parallel Query processing runs only on multiple
processors. In our case only one processor is available to SQL Server.

Does this mean that there are more CPUs in the box, but there are
other applications that gets to use those? Or is there only one CPU
in the box, and SQL Server is the only app on the machine?

The main reason I ask this, is that if there other apps on the machine,
this makes it even more difficult to assess your questions.

Quote:

Originally Posted by

And since you never post any of the queries, and nor the query plans,
you are not giving us any chances to straighten things out.

But I like to agree with what Gert-Jan said. If you have extrememe
performance requirement, you may want to cough up the extra money for
Enterprise to get whatever small fraction you can win. But if you
had those requirements, you would not let it suffice with a single
CPU box.

So go for Standard. Keep in mind that if your business expands and
you will want Enterprise one day, you can always upgrade at that point.

--
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|||<nitin.goel@.daffodildb.comwrote in message
news:1166597810.015478.291810@.a3g2000cwd.googlegro ups.com...

Quote:

Originally Posted by

RED ALERT!!!

Switch to RAID 1 or add at least one more disk and go RAID 5 or kiss your
data (and job?) goodbye.|||Thank you for replies. Some more information from my side:

1. Sorry I have made a mistake, our DB size is 50 GB not 20 GB (typing
mistake).
2. There are around 100 concurrent users accessing this db from 30
locations.
3. There is only one CPU in the box and only SQL Server runs on it.
4. Regarding I/O performance, what more can be done in I/O area (
that's a very novice Q but I have never worked in this field ).
5. We will post some queries by Monday morning.

BTW I know price of both SE and EE but we will spend only if we see any
gain EE. And we are also ready to spend on I/O if it is good for us.

Thanks again
Nitin Goyal|||Thank you for replies. Some more information from my side:

BTW I know price of both SE and EE but we will spend only if we see any
gain EE. And we are also ready to spend on I/O if it is good for us.

Thanks again
Nitin Goyal|||Thank you for replies. Some more information from my side:

BTW I know price of both SE and EE but we will spend only if we see any
gain EE. And we are also ready to spend on I/O if it is good for us.

Thanks again
Nitin Goyal|||<nitin.goel@.daffodildb.comwrote in message
news:1166681115.492154.131080@.79g2000cws.googlegro ups.com...

Quote:

Originally Posted by

Thank you for replies. Some more information from my side:
>
1. Sorry I have made a mistake, our DB size is 50 GB not 20 GB (typing
mistake).
2. There are around 100 concurrent users accessing this db from 30
locations.

Again, the TYPE of query really matters. If each query takes a 100
milliseconds vs. 10 seconds.

Quote:

Originally Posted by

3. There is only one CPU in the box and only SQL Server runs on it.

Well you may or may not be CPU bound, again, depends a lot on what you're
doing her.e

Quote:

Originally Posted by

4. Regarding I/O performance, what more can be done in I/O area (
that's a very novice Q but I have never worked in this field ).

Add disks. Lots of disks. Fast disks.

You're better off with 10 40 gig disk tha 1 400 gig disk for example.

(of course that's ignoring RAID and doing stuff like putting your logs on
their own physical disk group.)

Just as a point of reference we had a system handling 14 million selects and
inserts a day running on a 6 yo 4-way Xeon machine up until recently. Just
recently moved this to a 2-way modern box AND added a bunch of other
queries... and if anything it's faster than before.

Quote:

Originally Posted by

5. We will post some queries by Monday morning.
>
BTW I know price of both SE and EE but we will spend only if we see any
gain EE. And we are also ready to spend on I/O if it is good for us.
>
>
Thanks again
Nitin Goyal
>

|||1. Sorry I have made a mistake, our DB size is 50 GB not 20 GB (typing

Quote:

Originally Posted by

mistake).
2. There are around 100 concurrent users accessing this db from 30
locations.
3. There is only one CPU in the box and only SQL Server runs on it.
4. Regarding I/O performance, what more can be done in I/O area (
that's a very novice Q but I have never worked in this field ).
BTW I know price of both SE and EE but we will spend only if we see any
gain EE. And we are also ready to spend on I/O if it is good for us.

Basic rules for the I/O on a database server: a RAID-10 array using as
many disks as possible (even number) is the best scenario for the
databases, while a 2-disk RAID-1 array is great for transaction logs.

If you use temporary tables, having a distinct 2-disk RAID-1 array for
tempbd is excellent (even better: a 4-disk RAID-10). As for the disks
themselves, the smaller and faster is the better; your 73 GB 15k are
excellent.

If you can afford it, having a RAID controller the databases (RAID-10)
and another one for the transaction logs and tempdb (RAID-1 or RAID-10)
is optimal.

Also don't be shy to add some RAM, as much as you can.

Regards,
lucm

Monday, March 26, 2012

performance difference between query analyzer and .NET code.

Dear Sir,
I used dynamic SQL on stored procedure to do the paging in my site.
I often hit a connection time out problem with that dynamic SQl when I call
it from .NET code.
However if it run it under query analyzer, everything works fine.
Who can explain this to me and what should I do in this case?
regards,
Guoqi Zheng
http://www.ureader.comWhen you say "everything works fine", do you also mean that the execution
time in Query Analyzer is within the connection timeout you have set in your
.Net application?
ML
http://milambda.blogspot.com/|||> I used dynamic SQL on stored procedure to do the paging in my site.
> I often hit a connection time out problem with that dynamic SQl when I
> call
> it from .NET code.
> However if it run it under query analyzer, everything works fine.
> Who can explain this to me and what should I do in this case?
If "everything works fine" still means it's slow, and extends beyond the
timeout setting that ML mentioned, then everything is not working fine.
One thing you might try is a different paging approach. There are probably
a handful of samples here that will be faster and safer than dynamic SQL:
http://www.aspfaq.com/2120

Performance degraded after DBCC DBReindex

Dear Sir,
I have been tried to Reindex all tables' indexs as I expected this can
improve the performance of query e.g. DBCC DBReindex ( 'dbo.<TableName>',
'',0). But after that, I found that the overall performance is actually
degraded and the disk I/O seems to be heavier also. The performance is
degraded for three days already, can any one think of any reason that can
explain this issue? I have another question for statistic, do I needed to
update statistics after the reindex?
Pls. help, thanks a lot!
Yours sincerely,
Henry
In my opinion it's very important to update sql stats (proc sp_updatestats)
because like all RDBMS those stats are used by MSSQL to optimize the way it
compute your query.
Philippe RUELLO
DBA MSSQL
|||stats will get auto recomputed for new indexes created when you run dbcc
dbreindex. . .if you have high IO after re-index then you need to
set statistics profile on
set showplan on
set statistic io on
and determine which tables are causing the procedures/queries to run slow. .
.. .
determine where table scans and/or (nested loop joins) are used and
establish if there are useful indexes on these columns.
HTH
"Henry" wrote:

> Dear Sir,
> I have been tried to Reindex all tables' indexs as I expected this can
> improve the performance of query e.g. DBCC DBReindex ( 'dbo.<TableName>',
> '',0). But after that, I found that the overall performance is actually
> degraded and the disk I/O seems to be heavier also. The performance is
> degraded for three days already, can any one think of any reason that can
> explain this issue? I have another question for statistic, do I needed to
> update statistics after the reindex?
> Pls. help, thanks a lot!
> Yours sincerely,
> Henry
>
|||It is possible the reindex forced the recompile of a plan and it was not
optimal for most of the queries. Run profiler to see if you have some bad
queries and try recompiling them.
Andrew J. Kelly SQL MVP
"Henry" <Henry@.discussions.microsoft.com> wrote in message
news:BF0B8C2C-5183-4D32-ACF4-1AD870940995@.microsoft.com...
> Dear Sir,
> I have been tried to Reindex all tables' indexs as I expected this can
> improve the performance of query e.g. DBCC DBReindex ( 'dbo.<TableName>',
> '',0). But after that, I found that the overall performance is actually
> degraded and the disk I/O seems to be heavier also. The performance is
> degraded for three days already, can any one think of any reason that can
> explain this issue? I have another question for statistic, do I needed to
> update statistics after the reindex?
> Pls. help, thanks a lot!
> Yours sincerely,
> Henry
>
|||Since you have ran the dbcc reindex with 0, excessive IO signifies lot of
page splits.
so next time if u need to run dbcc reindex(u should have strong reason to
run this)
run this command seperate for each index depending upon its
usability/behavior(unique or non unique) and pass appropriate fillfactorto
avoid excessive page splits.
Thanks
--Harvinder
"Andrew J. Kelly" wrote:

> It is possible the reindex forced the recompile of a plan and it was not
> optimal for most of the queries. Run profiler to see if you have some bad
> queries and try recompiling them.
> --
> Andrew J. Kelly SQL MVP
>
> "Henry" <Henry@.discussions.microsoft.com> wrote in message
> news:BF0B8C2C-5183-4D32-ACF4-1AD870940995@.microsoft.com...
>
>
|||I am just rephrasing Harvinder here.
A fillfactor of 0 is really only useful for a read-only table. It will
leave absolutely no room for additional rows, or changes of rows that
causes the row to expand.
This means, that if inserts and update occur on the table after the
reindex with fillfactor=0, then page splits will occur. After that,
scanning the table will require more I/O, and because of fragmentation,
true sequential I/O will not be possible.
If you reindex regularly, then you can set the fillfactor accordingly.
For example, let's say 10% of the table is changed in one week. If you
reindex the table on a weekly basis, then a 10% fillfactor should work
quite well. It will avoid unnecessary page splits, because the
fillfactor will allow the table to grow (on average) 10 percent.
Hope this helps,
Gert-Jan
Henry wrote:
> Dear Sir,
> I have been tried to Reindex all tables' indexs as I expected this can
> improve the performance of query e.g. DBCC DBReindex ( 'dbo.<TableName>',
> '',0). But after that, I found that the overall performance is actually
> degraded and the disk I/O seems to be heavier also. The performance is
> degraded for three days already, can any one think of any reason that can
> explain this issue? I have another question for statistic, do I needed to
> update statistics after the reindex?
> Pls. help, thanks a lot!
> Yours sincerely,
> Henry
|||Thanks for your reply. Isn't the DBCC DBReindex ( 'dbo.<Table Name>', '',0),
the 0 in the script means just follow whatever the fillfactor being used
during the index being created? Will it automatically change to fill factor
to 0?
But any way, I will beware of it! Thx!
From,
Henry
"Gert-Jan Strik" wrote:

> I am just rephrasing Harvinder here.
> A fillfactor of 0 is really only useful for a read-only table. It will
> leave absolutely no room for additional rows, or changes of rows that
> causes the row to expand.
> This means, that if inserts and update occur on the table after the
> reindex with fillfactor=0, then page splits will occur. After that,
> scanning the table will require more I/O, and because of fragmentation,
> true sequential I/O will not be possible.
> If you reindex regularly, then you can set the fillfactor accordingly.
> For example, let's say 10% of the table is changed in one week. If you
> reindex the table on a weekly basis, then a 10% fillfactor should work
> quite well. It will avoid unnecessary page splits, because the
> fillfactor will allow the table to grow (on average) 10 percent.
> Hope this helps,
> Gert-Jan
>
> Henry wrote:
>
|||Thanks for your email, how do I identify the right percentage of sample data
to be used for updating the statistics? In general, is 30% enought?
From,
Henry
"Philippe RUELLO" wrote:

> In my opinion it's very important to update sql stats (proc sp_updatestats)
> because like all RDBMS those stats are used by MSSQL to optimize the way it
> compute your query.
> Philippe RUELLO
> DBA MSSQL
>
>
|||Henry wrote:
> Thanks for your reply. Isn't the DBCC DBReindex ( 'dbo.<Table Name>', '',0),
> the 0 in the script means just follow whatever the fillfactor being used
> during the index being created? Will it automatically change to fill factor
> to 0?
> But any way, I will beware of it! Thx!
> From,
> Henry
Yes, you are right. I forgot one little step in my explanation, and that
is that if you create an index without fill factor, then by default a
fill factor of 0 is used.
So if you created the indexes without fill factor, and run DBCC
DBREINDEX(..,.., 0), then at the leaf level there will be no space left
after reindexing, and in the index tree just one entry per page.
HTH,
Gert-Jan
sql

Performance degraded after DBCC DBReindex

Dear Sir,
I have been tried to Reindex all tables' indexs as I expected this can
improve the performance of query e.g. DBCC DBReindex ( 'dbo.<TableName>',
'',0). But after that, I found that the overall performance is actually
degraded and the disk I/O seems to be heavier also. The performance is
degraded for three days already, can any one think of any reason that can
explain this issue? I have another question for statistic, do I needed to
update statistics after the reindex?
Pls. help, thanks a lot!
Yours sincerely,
HenryIn my opinion it's very important to update sql stats (proc sp_updatestats)
because like all RDBMS those stats are used by MSSQL to optimize the way it
compute your query.
Philippe RUELLO
DBA MSSQL|||stats will get auto recomputed for new indexes created when you run dbcc
dbreindex. . .if you have high IO after re-index then you need to
set statistics profile on
set showplan on
set statistic io on
and determine which tables are causing the procedures/queries to run slow. .
. .
determine where table scans and/or (nested loop joins) are used and
establish if there are useful indexes on these columns.
HTH
"Henry" wrote:
> Dear Sir,
> I have been tried to Reindex all tables' indexs as I expected this can
> improve the performance of query e.g. DBCC DBReindex ( 'dbo.<TableName>',
> '',0). But after that, I found that the overall performance is actually
> degraded and the disk I/O seems to be heavier also. The performance is
> degraded for three days already, can any one think of any reason that can
> explain this issue? I have another question for statistic, do I needed to
> update statistics after the reindex?
> Pls. help, thanks a lot!
> Yours sincerely,
> Henry
>|||It is possible the reindex forced the recompile of a plan and it was not
optimal for most of the queries. Run profiler to see if you have some bad
queries and try recompiling them.
--
Andrew J. Kelly SQL MVP
"Henry" <Henry@.discussions.microsoft.com> wrote in message
news:BF0B8C2C-5183-4D32-ACF4-1AD870940995@.microsoft.com...
> Dear Sir,
> I have been tried to Reindex all tables' indexs as I expected this can
> improve the performance of query e.g. DBCC DBReindex ( 'dbo.<TableName>',
> '',0). But after that, I found that the overall performance is actually
> degraded and the disk I/O seems to be heavier also. The performance is
> degraded for three days already, can any one think of any reason that can
> explain this issue? I have another question for statistic, do I needed to
> update statistics after the reindex?
> Pls. help, thanks a lot!
> Yours sincerely,
> Henry
>|||Since you have ran the dbcc reindex with 0, excessive IO signifies lot of
page splits.
so next time if u need to run dbcc reindex(u should have strong reason to
run this)
run this command seperate for each index depending upon its
usability/behavior(unique or non unique) and pass appropriate fillfactorto
avoid excessive page splits.
Thanks
--Harvinder
"Andrew J. Kelly" wrote:
> It is possible the reindex forced the recompile of a plan and it was not
> optimal for most of the queries. Run profiler to see if you have some bad
> queries and try recompiling them.
> --
> Andrew J. Kelly SQL MVP
>
> "Henry" <Henry@.discussions.microsoft.com> wrote in message
> news:BF0B8C2C-5183-4D32-ACF4-1AD870940995@.microsoft.com...
> > Dear Sir,
> > I have been tried to Reindex all tables' indexs as I expected this can
> > improve the performance of query e.g. DBCC DBReindex ( 'dbo.<TableName>',
> > '',0). But after that, I found that the overall performance is actually
> > degraded and the disk I/O seems to be heavier also. The performance is
> > degraded for three days already, can any one think of any reason that can
> > explain this issue? I have another question for statistic, do I needed to
> > update statistics after the reindex?
> > Pls. help, thanks a lot!
> > Yours sincerely,
> > Henry
> >
>
>|||I am just rephrasing Harvinder here.
A fillfactor of 0 is really only useful for a read-only table. It will
leave absolutely no room for additional rows, or changes of rows that
causes the row to expand.
This means, that if inserts and update occur on the table after the
reindex with fillfactor=0, then page splits will occur. After that,
scanning the table will require more I/O, and because of fragmentation,
true sequential I/O will not be possible.
If you reindex regularly, then you can set the fillfactor accordingly.
For example, let's say 10% of the table is changed in one week. If you
reindex the table on a weekly basis, then a 10% fillfactor should work
quite well. It will avoid unnecessary page splits, because the
fillfactor will allow the table to grow (on average) 10 percent.
Hope this helps,
Gert-Jan
Henry wrote:
> Dear Sir,
> I have been tried to Reindex all tables' indexs as I expected this can
> improve the performance of query e.g. DBCC DBReindex ( 'dbo.<TableName>',
> '',0). But after that, I found that the overall performance is actually
> degraded and the disk I/O seems to be heavier also. The performance is
> degraded for three days already, can any one think of any reason that can
> explain this issue? I have another question for statistic, do I needed to
> update statistics after the reindex?
> Pls. help, thanks a lot!
> Yours sincerely,
> Henry|||Thanks for your reply. Isn't the DBCC DBReindex ( 'dbo.<Table Name>', '',0),
the 0 in the script means just follow whatever the fillfactor being used
during the index being created? Will it automatically change to fill factor
to 0?
But any way, I will beware of it! Thx!
From,
Henry
"Gert-Jan Strik" wrote:
> I am just rephrasing Harvinder here.
> A fillfactor of 0 is really only useful for a read-only table. It will
> leave absolutely no room for additional rows, or changes of rows that
> causes the row to expand.
> This means, that if inserts and update occur on the table after the
> reindex with fillfactor=0, then page splits will occur. After that,
> scanning the table will require more I/O, and because of fragmentation,
> true sequential I/O will not be possible.
> If you reindex regularly, then you can set the fillfactor accordingly.
> For example, let's say 10% of the table is changed in one week. If you
> reindex the table on a weekly basis, then a 10% fillfactor should work
> quite well. It will avoid unnecessary page splits, because the
> fillfactor will allow the table to grow (on average) 10 percent.
> Hope this helps,
> Gert-Jan
>
> Henry wrote:
> >
> > Dear Sir,
> > I have been tried to Reindex all tables' indexs as I expected this can
> > improve the performance of query e.g. DBCC DBReindex ( 'dbo.<TableName>',
> > '',0). But after that, I found that the overall performance is actually
> > degraded and the disk I/O seems to be heavier also. The performance is
> > degraded for three days already, can any one think of any reason that can
> > explain this issue? I have another question for statistic, do I needed to
> > update statistics after the reindex?
> > Pls. help, thanks a lot!
> > Yours sincerely,
> > Henry
>|||Thanks for your email, how do I identify the right percentage of sample data
to be used for updating the statistics? In general, is 30% enought?
From,
Henry
"Philippe RUELLO" wrote:
> In my opinion it's very important to update sql stats (proc sp_updatestats)
> because like all RDBMS those stats are used by MSSQL to optimize the way it
> compute your query.
> Philippe RUELLO
> DBA MSSQL
>
>|||Henry wrote:
> Thanks for your reply. Isn't the DBCC DBReindex ( 'dbo.<Table Name>', '',0),
> the 0 in the script means just follow whatever the fillfactor being used
> during the index being created? Will it automatically change to fill factor
> to 0?
> But any way, I will beware of it! Thx!
> From,
> Henry
Yes, you are right. I forgot one little step in my explanation, and that
is that if you create an index without fill factor, then by default a
fill factor of 0 is used.
So if you created the indexes without fill factor, and run DBCC
DBREINDEX(..,.., 0), then at the leaf level there will be no space left
after reindexing, and in the index tree just one entry per page.
HTH,
Gert-Jan

Performance degraded after DBCC DBReindex

Dear Sir,
I have been tried to Reindex all tables' indexs as I expected this can
improve the performance of query e.g. DBCC DBReindex ( 'dbo.<TableName>',
'',0). But after that, I found that the overall performance is actually
degraded and the disk I/O seems to be heavier also. The performance is
degraded for three days already, can any one think of any reason that can
explain this issue? I have another question for statistic, do I needed to
update statistics after the reindex?
Pls. help, thanks a lot!
Yours sincerely,
HenryIn my opinion it's very important to update sql stats (proc sp_updatestats)
because like all RDBMS those stats are used by MSSQL to optimize the way it
compute your query.
Philippe RUELLO
DBA MSSQL|||stats will get auto recomputed for new indexes created when you run dbcc
dbreindex. . .if you have high IO after re-index then you need to
set statistics profile on
set showplan on
set statistic io on
and determine which tables are causing the procedures/queries to run slow. .
. .
determine where table scans and/or (nested loop joins) are used and
establish if there are useful indexes on these columns.
HTH
"Henry" wrote:

> Dear Sir,
> I have been tried to Reindex all tables' indexs as I expected this can
> improve the performance of query e.g. DBCC DBReindex ( 'dbo.<TableName>',
> '',0). But after that, I found that the overall performance is actually
> degraded and the disk I/O seems to be heavier also. The performance is
> degraded for three days already, can any one think of any reason that can
> explain this issue? I have another question for statistic, do I needed to
> update statistics after the reindex?
> Pls. help, thanks a lot!
> Yours sincerely,
> Henry
>|||It is possible the reindex forced the recompile of a plan and it was not
optimal for most of the queries. Run profiler to see if you have some bad
queries and try recompiling them.
Andrew J. Kelly SQL MVP
"Henry" <Henry@.discussions.microsoft.com> wrote in message
news:BF0B8C2C-5183-4D32-ACF4-1AD870940995@.microsoft.com...
> Dear Sir,
> I have been tried to Reindex all tables' indexs as I expected this can
> improve the performance of query e.g. DBCC DBReindex ( 'dbo.<TableName>',
> '',0). But after that, I found that the overall performance is actually
> degraded and the disk I/O seems to be heavier also. The performance is
> degraded for three days already, can any one think of any reason that can
> explain this issue? I have another question for statistic, do I needed to
> update statistics after the reindex?
> Pls. help, thanks a lot!
> Yours sincerely,
> Henry
>|||Since you have ran the dbcc reindex with 0, excessive IO signifies lot of
page splits.
so next time if u need to run dbcc reindex(u should have strong reason to
run this)
run this command seperate for each index depending upon its
usability/behavior(unique or non unique) and pass appropriate fillfactorto
avoid excessive page splits.
Thanks
--Harvinder
"Andrew J. Kelly" wrote:

> It is possible the reindex forced the recompile of a plan and it was not
> optimal for most of the queries. Run profiler to see if you have some bad
> queries and try recompiling them.
> --
> Andrew J. Kelly SQL MVP
>
> "Henry" <Henry@.discussions.microsoft.com> wrote in message
> news:BF0B8C2C-5183-4D32-ACF4-1AD870940995@.microsoft.com...
>
>|||I am just rephrasing Harvinder here.
A fillfactor of 0 is really only useful for a read-only table. It will
leave absolutely no room for additional rows, or changes of rows that
causes the row to expand.
This means, that if inserts and update occur on the table after the
reindex with fillfactor=0, then page splits will occur. After that,
scanning the table will require more I/O, and because of fragmentation,
true sequential I/O will not be possible.
If you reindex regularly, then you can set the fillfactor accordingly.
For example, let's say 10% of the table is changed in one week. If you
reindex the table on a weekly basis, then a 10% fillfactor should work
quite well. It will avoid unnecessary page splits, because the
fillfactor will allow the table to grow (on average) 10 percent.
Hope this helps,
Gert-Jan
Henry wrote:
> Dear Sir,
> I have been tried to Reindex all tables' indexs as I expected this can
> improve the performance of query e.g. DBCC DBReindex ( 'dbo.<TableName>',
> '',0). But after that, I found that the overall performance is actually
> degraded and the disk I/O seems to be heavier also. The performance is
> degraded for three days already, can any one think of any reason that can
> explain this issue? I have another question for statistic, do I needed to
> update statistics after the reindex?
> Pls. help, thanks a lot!
> Yours sincerely,
> Henry|||Thanks for your reply. Isn't the DBCC DBReindex ( 'dbo.<Table Name>', '',0),
the 0 in the script means just follow whatever the fillfactor being used
during the index being created? Will it automatically change to fill factor
to 0?
But any way, I will beware of it! Thx!
From,
Henry
"Gert-Jan Strik" wrote:

> I am just rephrasing Harvinder here.
> A fillfactor of 0 is really only useful for a read-only table. It will
> leave absolutely no room for additional rows, or changes of rows that
> causes the row to expand.
> This means, that if inserts and update occur on the table after the
> reindex with fillfactor=0, then page splits will occur. After that,
> scanning the table will require more I/O, and because of fragmentation,
> true sequential I/O will not be possible.
> If you reindex regularly, then you can set the fillfactor accordingly.
> For example, let's say 10% of the table is changed in one week. If you
> reindex the table on a weekly basis, then a 10% fillfactor should work
> quite well. It will avoid unnecessary page splits, because the
> fillfactor will allow the table to grow (on average) 10 percent.
> Hope this helps,
> Gert-Jan
>
> Henry wrote:
>|||Thanks for your email, how do I identify the right percentage of sample data
to be used for updating the statistics? In general, is 30% enought?
From,
Henry
"Philippe RUELLO" wrote:

> In my opinion it's very important to update sql stats (proc sp_updatestats
)
> because like all RDBMS those stats are used by MSSQL to optimize the way i
t
> compute your query.
> Philippe RUELLO
> DBA MSSQL
>
>|||Henry wrote:
> Thanks for your reply. Isn't the DBCC DBReindex ( 'dbo.<Table Name>', '',0
),
> the 0 in the script means just follow whatever the fillfactor being used
> during the index being created? Will it automatically change to fill facto
r
> to 0?
> But any way, I will beware of it! Thx!
> From,
> Henry
Yes, you are right. I forgot one little step in my explanation, and that
is that if you create an index without fill factor, then by default a
fill factor of 0 is used.
So if you created the indexes without fill factor, and run DBCC
DBREINDEX(..,.., 0), then at the leaf level there will be no space left
after reindexing, and in the index tree just one entry per page.
HTH,
Gert-Jan

Wednesday, March 21, 2012

Performance comparison between Enterprise and Standard edition

Dear all,

I am considering for a new installation of Enterprise edition because of its OLAP semi-additive feature. Are there any other feature that is worth for Enterprise edition ?

I am using it as a small size datawarehouse (<200 GB). Then, no need for scalability and availability.

Are there any performance paper about ETL, OLAP creation, OLAP browsing, reporting between enterprise and standard edition ? Both of them should use the same machine sizing , i.e. 2 CPU.

Best regards,

Chaivat

the main difference between enterprise and standard is features, also standard edition will use a maximum of 4 processors.|||

Here is a handy chart of feature comparison:

http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

Enterprise offers several things above Standard, such as partioning, indexed views, CPU support, and more. The link makes it pretty easy to compare the two SKU's.

Thanks,
Sam Lester (MSFT)

sql

Performance comparison between Enterprise and Standard edition

Dear all,

I am considering for a new installation of Enterprise edition because of its OLAP semi-additive feature. Are there any other feature that is worth for Enterprise edition ?

I am using it as a small size datawarehouse (<200 GB). Then, no need for scalability and availability.

Are there any performance paper about ETL, OLAP creation, OLAP browsing, reporting between enterprise and standard edition ? Both of them should use the same machine sizing , i.e. 2 CPU.

Best regards,

Chaivat

the main difference between enterprise and standard is features, also standard edition will use a maximum of 4 processors.|||

Here is a handy chart of feature comparison:

http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

Enterprise offers several things above Standard, such as partioning, indexed views, CPU support, and more. The link makes it pretty easy to compare the two SKU's.

Thanks,
Sam Lester (MSFT)

Performance comparison between Enterprise and Standard edition

Dear all,

I am considering for a new installation of Enterprise edition because of its OLAP semi-additive feature. Are there any other feature that is worth for Enterprise edition ?

I am using it as a small size datawarehouse (<200 GB). Then, no need for scalability and availability.

Are there any performance paper about ETL, OLAP creation, OLAP browsing, reporting between enterprise and standard edition ? Both of them should use the same machine sizing , i.e. 2 CPU.

Best regards,

Chaivat

the main difference between enterprise and standard is features, also standard edition will use a maximum of 4 processors.|||

Here is a handy chart of feature comparison:

http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

Enterprise offers several things above Standard, such as partioning, indexed views, CPU support, and more. The link makes it pretty easy to compare the two SKU's.

Thanks,
Sam Lester (MSFT)

Tuesday, March 20, 2012

Performance and Sizing question

Dear experts,
I've a system using SQL Server 2000 and ASP. The database size is around 3G.
I found that my server will be CPU bound when I running the load test.
The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and 2
36G harddisks.
I would like to know the rules/guideline to set the following system
settings/parameters:
1. OS page file - size ?
2. Memory - minimum and maximum for sql server ?
3. Procedure cahe - size ?
4. tempdb - size
5. no index for all the tables except setting primary for each tables
Thank you in advance!
Hi
Check out http://www.sql-server-performance.co...erformance.asp for
information on setting and tuning the system. You may also want to read
SQL Server 2000 Performance Tuning Technical Reference (MS Press)
http://www.microsoft.com/mspress/books/4944.asp
SQL Server 2000 Performance Optimization and Tuning Handbook (England)
http://www.amazon.com/exec/obidos/tg...568550-7347917
The Guru's Guide to SQL Server Architecture and Internals
http://www.awprofessional.com/catalo...7-2FE1E3211BA3}
http://www.windowsitpro.com/SQLServe...588/45588.html
Others inline:
"AC" wrote:

> Dear experts,
> I've a system using SQL Server 2000 and ASP. The database size is around 3G.
> I found that my server will be CPU bound when I running the load test.
> The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and 2
> 36G harddisks.
> I would like to know the rules/guideline to set the following system
> settings/parameters:
> 1. OS page file - size ?
In general I believe 1.5*memory is recommended, if you really have the money
have it on a dedicated drive. You should not need the page file

> 2. Memory - minimum and maximum for sql server ?
As much as you can afford upto the limit set by your version
http://msdn.microsoft.com/library/de...ar_ts_8dbn.asp
http://www.windowsitpro.com/SQLServe...156/45156.html
http://www.windowsitpro.com/SQLServe...155/45155.html
The links above may be able to provide a more precise estimate!
It is worth making sure that you have the ability to easily expand the
amount of memory without replacement.

> 3. Procedure cahe - size ?
In general this is tuned while under load (firstly load testing and then
live environment)

> 4. tempdb - size
In general you can tune this as you go along. Make sure that the disc is not
fragmented so the files are not fragmented.
http://www.windowsitpro.com/Articles...layTab=Article
http://www.windowsitpro.com/Articles...layTab=Article
http://www.windowsitpro.com/Articles...layTab=Article

> 5. no index for all the tables except setting primary for each tables
>
As a first cut you may want to run the index tuning wizard on a profile
taken of realistic load/activity. Check out commonly used queries/procedures
and their query plans and profile performance before and after to make sure
that you have not seriously affected another part of the system adversly.

> Thank you in advance!
John
|||http://www.sql-server-performance.co...nitor_tips.asp
http://www.sql-server-performance.com/
Cristian Lefter, SQL Server MVP
"AC" <AC@.discussions.microsoft.com> wrote in message
news:75CE2434-45DD-4629-A604-2B731F019358@.microsoft.com...
> Dear experts,
> I've a system using SQL Server 2000 and ASP. The database size is around
> 3G.
> I found that my server will be CPU bound when I running the load test.
> The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and
> 2
> 36G harddisks.
> I would like to know the rules/guideline to set the following system
> settings/parameters:
> 1. OS page file - size ?
> 2. Memory - minimum and maximum for sql server ?
> 3. Procedure cahe - size ?
> 4. tempdb - size
> 5. no index for all the tables except setting primary for each tables
> Thank you in advance!

Performance and Sizing question

Dear experts,
I've a system using SQL Server 2000 and ASP. The database size is around 3G.
I found that my server will be CPU bound when I running the load test.
The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and 2
36G harddisks.
I would like to know the rules/guideline to set the following system
settings/parameters:
1. OS page file - size '
2. Memory - minimum and maximum for sql server '
3. Procedure cahe - size '
4. tempdb - size
5. no index for all the tables except setting primary for each tables
Thank you in advance!Hi
Check out http://www.sql-server-performance.c...performance.asp for
information on setting and tuning the system. You may also want to read
SQL Server 2000 Performance Tuning Technical Reference (MS Press)
http://www.microsoft.com/mspress/books/4944.asp
SQL Server 2000 Performance Optimization and Tuning Handbook (England)
http://www.amazon.com/exec/obidos/t...6568550-7347917
The Guru's Guide to SQL Server Architecture and Internals
http://www.awprofessional.com/catal...37-2FE1E3211BA3}
http://www.windowsitpro.com/SQLServ...5588/45588.html
Others inline:
"AC" wrote:

> Dear experts,
> I've a system using SQL Server 2000 and ASP. The database size is around 3
G.
> I found that my server will be CPU bound when I running the load test.
> The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and
2
> 36G harddisks.
> I would like to know the rules/guideline to set the following system
> settings/parameters:
> 1. OS page file - size '
In general I believe 1.5*memory is recommended, if you really have the money
have it on a dedicated drive. You should not need the page file

> 2. Memory - minimum and maximum for sql server '
As much as you can afford upto the limit set by your version
http://msdn.microsoft.com/library/d...br />
8dbn.asp
http://www.windowsitpro.com/SQLServ...5156/45156.html
http://www.windowsitpro.com/SQLServ...5155/45155.html
The links above may be able to provide a more precise estimate!
It is worth making sure that you have the ability to easily expand the
amount of memory without replacement.

> 3. Procedure cahe - size '
In general this is tuned while under load (firstly load testing and then
live environment)

> 4. tempdb - size
In general you can tune this as you go along. Make sure that the disc is not
fragmented so the files are not fragmented.
[url]http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=39158&DisplayTab=Article[
/url]
[url]http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=45154&DisplayTab=Article[
/url]
[url]http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=39157&DisplayTab=Article[
/url]

> 5. no index for all the tables except setting primary for each tables
>
As a first cut you may want to run the index tuning wizard on a profile
taken of realistic load/activity. Check out commonly used queries/procedures
and their query plans and profile performance before and after to make sure
that you have not seriously affected another part of the system adversly.

> Thank you in advance!
John|||http://www.sql-server-performance.c...onitor_tips.asp
http://www.sql-server-performance.com/
Cristian Lefter, SQL Server MVP
"AC" <AC@.discussions.microsoft.com> wrote in message
news:75CE2434-45DD-4629-A604-2B731F019358@.microsoft.com...
> Dear experts,
> I've a system using SQL Server 2000 and ASP. The database size is around
> 3G.
> I found that my server will be CPU bound when I running the load test.
> The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and
> 2
> 36G harddisks.
> I would like to know the rules/guideline to set the following system
> settings/parameters:
> 1. OS page file - size '
> 2. Memory - minimum and maximum for sql server '
> 3. Procedure cahe - size '
> 4. tempdb - size
> 5. no index for all the tables except setting primary for each tables
> Thank you in advance!

Performance and Sizing question

Dear experts,
I've a system using SQL Server 2000 and ASP. The database size is around 3G.
I found that my server will be CPU bound when I running the load test.
The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and 2
36G harddisks.
I would like to know the rules/guideline to set the following system
settings/parameters:
1. OS page file - size '
2. Memory - minimum and maximum for sql server '
3. Procedure cahe - size '
4. tempdb - size
5. no index for all the tables except setting primary for each tables
Thank you in advance!Hi
Check out http://www.sql-server-performance.com/articles_performance.asp for
information on setting and tuning the system. You may also want to read
SQL Server 2000 Performance Tuning Technical Reference (MS Press)
http://www.microsoft.com/mspress/books/4944.asp
SQL Server 2000 Performance Optimization and Tuning Handbook (England)
http://www.amazon.com/exec/obidos/tg/detail/-/1555582419/104-6568550-7347917
The Guru's Guide to SQL Server Architecture and Internal
http://www.awprofessional.com/catalog/product.asp?product_id={FA365B66-8A6F-400E-A637-2FE1E3211BA3}
http://www.windowsitpro.com/SQLServer/Article/ArticleID/45588/45588.html
Others inline:
"AC" wrote:
> Dear experts,
> I've a system using SQL Server 2000 and ASP. The database size is around 3G.
> I found that my server will be CPU bound when I running the load test.
> The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and 2
> 36G harddisks.
> I would like to know the rules/guideline to set the following system
> settings/parameters:
> 1. OS page file - size '
In general I believe 1.5*memory is recommended, if you really have the money
have it on a dedicated drive. You should not need the page file
> 2. Memory - minimum and maximum for sql server '
As much as you can afford upto the limit set by your version
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_8dbn.asp
http://www.windowsitpro.com/SQLServer/Article/ArticleID/45156/45156.html
http://www.windowsitpro.com/SQLServer/Article/ArticleID/45155/45155.html
The links above may be able to provide a more precise estimate!
It is worth making sure that you have the ability to easily expand the
amount of memory without replacement.
> 3. Procedure cahe - size '
In general this is tuned while under load (firstly load testing and then
live environment)
> 4. tempdb - size
In general you can tune this as you go along. Make sure that the disc is not
fragmented so the files are not fragmented.
http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=39158&DisplayTab=Article
http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=45154&DisplayTab=Article
http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=39157&DisplayTab=Article
> 5. no index for all the tables except setting primary for each tables
>
As a first cut you may want to run the index tuning wizard on a profile
taken of realistic load/activity. Check out commonly used queries/procedures
and their query plans and profile performance before and after to make sure
that you have not seriously affected another part of the system adversly.
> Thank you in advance!
John|||http://www.sql-server-performance.com/performance_monitor_tips.asp
http://www.sql-server-performance.com/
Cristian Lefter, SQL Server MVP
"AC" <AC@.discussions.microsoft.com> wrote in message
news:75CE2434-45DD-4629-A604-2B731F019358@.microsoft.com...
> Dear experts,
> I've a system using SQL Server 2000 and ASP. The database size is around
> 3G.
> I found that my server will be CPU bound when I running the load test.
> The configuration of server is IBM 360 with 4 CPUs (1.5GZ) and 6G ram and
> 2
> 36G harddisks.
> I would like to know the rules/guideline to set the following system
> settings/parameters:
> 1. OS page file - size '
> 2. Memory - minimum and maximum for sql server '
> 3. Procedure cahe - size '
> 4. tempdb - size
> 5. no index for all the tables except setting primary for each tables
> Thank you in advance!

Monday, March 12, 2012

performance and locking problems - urget

Dear Gurus,

A Client has the following problems/requests for their Production
databases, what is your professional/practical advises to tackle and
resolve these issues:

1)Number of Transactions per day The current database can currently
handle about 5000 order transactions per day

2)Table locking When doing row updates SQL Server will lock the
whole table. For example after a few instances of a driver updating an
order record it will lock the entire orders table

3)Reports When a user runs a report involving big quantities of
order-related data other processes will suffer occasional timeouts

4)Archiving There is no efficient way of archiving historical system
dataDavid McGeorge (soalvajavab1@.yahoo.com) writes:
> A Client has the following problems/requests for their Production
> databases, what is your professional/practical advises to tackle and
> resolve these issues:

I'm afraid that your questions are far too open-ended for it to be
possible to give an exhaustive answer in a newsgroup post. Had I been
a consultant, I would have been tempted to refer you the web site for
my business. Judging from your mail address you're a web programmer,
and from your questions it appears that you client is a need of someone
with SQL expertise.

> 1)Number of Transactions per day ? The current database can currently
> handle about 5000 order transactions per day

What do you mean with "can"? Is 5000 transactions/day the actual load?
Or is the database at the maximum of its capacity with that rate? I
would assume the former, since 5000 transactions/day is a low number,
unless the transactions are extremely complex.

> 2)Table locking ? When doing row updates SQL Server will lock the
> whole table. For example after a few instances of a driver updating an
> order record it will lock the entire orders table

This sounds like the indexing of the database needs to be improved.
If you say:

UPDATE tbl
SET col = 23
WHERE othercol = 12

And there is no index on othercol, SQL Server will have no choice but
to lock the entire table.

One tool to improve indexing in the database is the Index Tuning Wizard.
You give it a day's workload, and it will suggest suitable indexes to add.

> 3)Reports ? When a user runs a report involving big quantities of
> order-related data other processes will suffer occasional timeouts

Reports are often problem in OLTP databases, because they need to scan
lots of data. Indexing can help, but if user can build their own reports,
or there are report functions with lots of selection criteria, this
may not be feasible. Not the least, if the users can accept that an
odd report takes 20-30 minutes to run.

Therefore many sites sets up a report database, which is a copy of the
live database. Depending on the requirements, the report database can
be loaded from yesterday's backup, or you could keep it updated with
log shipping or replication. The latter is required if users must have
today's data in their reports, but it takes more effort to get there.

> 4)Archiving ? There is no efficient way of archiving historical system
> data

This point is completely impossible to address without knowing the
business requirements.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Saturday, February 25, 2012

Perfomance tuning

Dear friends
We have one problem in our existing system.We are expecting some expert comment on this.We have one corebanking system back end as MS SQL server with IIS server.Our system is always very slow in the peak times of tranasactions.We are planning to optimize this with a short time plan .So pls give some suggestions that our DBA team can implement in a short time with SQL SERVER 2000

Thanks in Advance
Filson

A possible course of action is this:

Try to check your IIS's and SQL's processor during the peak times to see if the problem is with your SQL or IIS

If your problem is not in IIS:

Try to see if your sql server's processor is 100% or near. If it is, then your queries are probably too heavy - if it is not, then you possibly have locking problems.

Try to find if there are specific queries that are taking longer. You could use Sql Profiler for this, by specifying a duration minimum threshold for the events - for instance, only show queries that take more than 5 seconds (warning: this might deterioate even further your performance while the trace is active). After finding out the worse queries, try to understand why they are running slow - you could use SQL Query Analyzer for this, using the SP's execution plans.

Perfomance Enhancement through proper database designing

Dear Reader

I am trying to design a database. How can I make best Judgement that Indexing (which I am trying to fix during Diagram Desingning process)is ok.

I am able to identify the best candidate for the indexing.

Below is the details I want to understand:

Area
ZIP
City
County
District
State/Province
Country

Now I want the data retrival optimization through Index. (you can suggest another idea, also)

Entities Area,..., Country have independent tables.
Example:

Area_Table
AreaID (PK)
Area
They have relationship- one to many- if you go from Country to Area.

There is one more table:

Location_Table (PK)
LocationID
AreaID
ZIPID
CityID
CountyID
DistrictID
State/ProvinceID
CountryID

(Location_ID is further related to the Address of the contact.)

GUI has a single form to enter these details.On a save command details in all the tables -Area to Country- (individually) being inserted.
& simultaniously Location_Table is also being inserted with the details.

Following is the situation of being queried these tables:

(1) GUI user can select an Area than the related details of ZIP .., ..., ...upto Country etc. should be loaded automatically (id it is previously stored by the user entry in the database.)

(2) Contacts have to be retrived on the basis of Area, ZIP, ....County. (Necessary Groupings are required )

Example:
If Contacts are queried Country Wise then the Display should be
Country1
State1
District1
County1
City1
ZIP1
Area1
Area2
ZIP2
City2

County2
District2
Country2

Please Guide.

SuryaPrakash

*****************************************
* This message was posted via http://www.sqlmonster.com
*
* Report spam or abuse by clicking the following URL:
* http://www.sqlmonster.com/Uwe/Abuse...0255a1765491f15
*****************************************SuryaPrakash Patel via SQLMonster.com (forum@.SQLMonster.com) writes:
> I am trying to design a database. How can I make best Judgement that
> Indexing (which I am trying to fix during Diagram Desingning process)is
> ok.

I was not really able to understand exactly what the queries would look
like. While a trained database designer certainly puts indexes already
in the early design stage, it's better to focus to get the data model
right to support the functional requirements first. Once we have the
queries and the tables, it may be eaesier to say what would be the
best indexes.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Tue, 09 Nov 2004 04:32:02 GMT, SuryaPrakash Patel via SQLMonster.com
wrote:

>Dear Reader
>I am trying to design a database.
(snip)

Hi SuryaPrakash,

Further to Erlands's comments: you should also not think about GUI or
report formats when designing a database. Database design should be driven
by the structure of the data only.

Only when you have a properly normalized database that will store all data
the application needs and that will reject all modifications that would
hurt data integrity comes the time to think about the user interface (both
for in- and output screens and for reports) and about adding extra indexes
for performance enhancement.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Dear Erland & Hugo

Thanks

I got your suggestions.

I have specific needs to get the certain ways of output. So it is obvious that I incorporate the needs while doing the database design.

But nowonwards I will try to stick the database structure, only. And leave the GUI part and Indexing part for later stages of development.

Point to be noted is that I will not have any chance to deploy any index after the software has been installed at user's end. Any comments.

Thanks again

SuryaPrakash

*****************************************
* A copy of the whole thread can be found at:
* http://www.sqlmonster.com/Uwe/Forum...sql-server/5093
*
* Report spam or abuse by clicking the following URL:
* http://www.sqlmonster.com/Uwe/Abuse...4931f7dbd3d74e1
*****************************************|||On Wed, 10 Nov 2004 00:59:23 GMT, SuryaPrakash Patel via SQLMonster.com
wrote:

>Point to be noted is that I will not have any chance to deploy any index after the software has been installed at user's end. Any comments.

Hi SuryaPrakash,

That's a logical and quite common scenario. That's why you should only
install the software when it's completely finished. I'd recommend to build
the software first, then test and debug until it works as desired, then
start doing performance test and tweaking things (adding indexes,
rewriting queries, etc) until the speed is as desired. Then do a final
test to check that performance tweaking didn't break functionality.

Installing at the user's end should be postponed until all these stages
are done and the product is completely finished.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||SuryaPrakash Patel via SQLMonster.com (forum@.SQLMonster.com) writes:
> Point to be noted is that I will not have any chance to deploy any index
> after the software has been installed at user's end. Any comments.

So you need to test carefully with real-world data before you ship.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Wednesday, March 28, 2012

Performance dropped after upgraded to MS SQL 2000

Performance dropped after upgraded to MS SQL 2000

Performance Difference between SQL Server 2005 Standard Edition and Enterprise Edition

Monday, March 26, 2012

performance difference between query analyzer and .NET code.

Performance degraded after DBCC DBReindex

Performance degraded after DBCC DBReindex

Performance degraded after DBCC DBReindex

Wednesday, March 21, 2012

Performance comparison between Enterprise and Standard edition

Performance comparison between Enterprise and Standard edition

Performance comparison between Enterprise and Standard edition

Tuesday, March 20, 2012

Performance and Sizing question

Performance and Sizing question

Performance and Sizing question

Monday, March 12, 2012

performance and locking problems - urget

Saturday, February 25, 2012

Perfomance tuning

Perfomance Enhancement through proper database designing

Percentage of total

Blog Archive

About Me