Percentage of total: comparison

Showing posts with label comparison. Show all posts

Wednesday, March 21, 2012

Performance comparison using Compability 8.0 on SQL 2005 vs. SQL 2000

.
Andrew J. Kelly SQL MVP
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:69a40194e9cbc@.uwe...
> In production we are running SQL Server 2000, SP4, on Windows 2003, using
> 4
> processors with 6 GB AWE Memory.
> I run DBCC DBReindex on all indexes and Update Statistics With FullScan on
> all indexes.
> A stored procedure with a complex query is timing out.
> SP_Who shows that the procedure is not getting blocked, but if anything,
> is
> doing the blocking.
> We restore the production database to our DEV environment which is running
> SQL Server 2005, SP1, on Windows 2003, using 2 processors and 3 GB Memory.
> The database from production, when restored, is left at Compatability 8.0.
> When this same stored procedure is run in DEV it executes in under 2
> seconds,
> with an entirely different execution plan than production.
> Is it possible, that the procedure, when run in DEV is utilizing some
> components of SQL 2005 to build a better plan, even though the
> Compatibility
> is left at 8.0?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200611/1
>
|||I did not run update stats on the DEV machine, since it was a restore of a
database that just had updated stats run on it, and additionally, the
procedure ran like a champ on the DEV machine.
Andrew J. Kelly wrote:[vbcol=seagreen]
>Did you update the stats after the restore on the dev machine? If not the
>optimizer may not have the correct info even if it is a better plan

.
>[quoted text clipped - 21 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200611/1
|||You should as a matter of practice update the stats after a restore
regardless of when they were ran last.
Andrew J. Kelly SQL MVP
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:69acf56bf722a@.uwe...
>I did not run update stats on the DEV machine, since it was a restore of a
> database that just had updated stats run on it, and additionally, the
> procedure ran like a champ on the DEV machine.
> Andrew J. Kelly wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200611/1
>

Performance comparison using Compability 8.0 on SQL 2005 vs. SQL 2000

.
Andrew J. Kelly SQL MVP
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:69a40194e9cbc@.uwe...
> In production we are running SQL Server 2000, SP4, on Windows 2003, using
> 4
> processors with 6 GB AWE Memory.
> I run DBCC DBReindex on all indexes and Update Statistics With FullScan on
> all indexes.
> A stored procedure with a complex query is timing out.
> SP_Who shows that the procedure is not getting blocked, but if anything,
> is
> doing the blocking.
> We restore the production database to our DEV environment which is running
> SQL Server 2005, SP1, on Windows 2003, using 2 processors and 3 GB Memory.
> The database from production, when restored, is left at Compatability 8.0.
> When this same stored procedure is run in DEV it executes in under 2
> seconds,
> with an entirely different execution plan than production.
> Is it possible, that the procedure, when run in DEV is utilizing some
> components of SQL 2005 to build a better plan, even though the
> Compatibility
> is left at 8.0?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200611/1
>|||I did not run update stats on the DEV machine, since it was a restore of a
database that just had updated stats run on it, and additionally, the
procedure ran like a champ on the DEV machine.
Andrew J. Kelly wrote:[vbcol=seagreen]
>Did you update the stats after the restore on the dev machine? If not the
>optimizer may not have the correct info even if it is a better plan

.
>
>[quoted text clipped - 21 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200611/1|||You should as a matter of practice update the stats after a restore
regardless of when they were ran last.
Andrew J. Kelly SQL MVP
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:69acf56bf722a@.uwe...
>I did not run update stats on the DEV machine, since it was a restore of a
> database that just had updated stats run on it, and additionally, the
> procedure ran like a champ on the DEV machine.
> Andrew J. Kelly wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200611/1
>

Performance comparison using Compability 8.0 on SQL 2005 vs. SQL 2000

In production we are running SQL Server 2000, SP4, on Windows 2003, using 4
processors with 6 GB AWE Memory.
I run DBCC DBReindex on all indexes and Update Statistics With FullScan on
all indexes.
A stored procedure with a complex query is timing out.
SP_Who shows that the procedure is not getting blocked, but if anything, is
doing the blocking.
We restore the production database to our DEV environment which is running
SQL Server 2005, SP1, on Windows 2003, using 2 processors and 3 GB Memory.
The database from production, when restored, is left at Compatability 8.0.
When this same stored procedure is run in DEV it executes in under 2 seconds,
with an entirely different execution plan than production.
Is it possible, that the procedure, when run in DEV is utilizing some
components of SQL 2005 to build a better plan, even though the Compatibility
is left at 8.0?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:69a40194e9cbc@.uwe...
> In production we are running SQL Server 2000, SP4, on Windows 2003, using
> 4
> processors with 6 GB AWE Memory.
> I run DBCC DBReindex on all indexes and Update Statistics With FullScan on
> all indexes.
> A stored procedure with a complex query is timing out.
> SP_Who shows that the procedure is not getting blocked, but if anything,
> is
> doing the blocking.
> We restore the production database to our DEV environment which is running
> SQL Server 2005, SP1, on Windows 2003, using 2 processors and 3 GB Memory.
> The database from production, when restored, is left at Compatability 8.0.
> When this same stored procedure is run in DEV it executes in under 2
> seconds,
> with an entirely different execution plan than production.
> Is it possible, that the procedure, when run in DEV is utilizing some
> components of SQL 2005 to build a better plan, even though the
> Compatibility
> is left at 8.0?
>
Yes, in fact, it's certain. Even in 8.0 Compatibility mode you get get the
(enhanced) SQL Server 2005 query optimizer. In 8.0 Compatibility mode
you're still running SQL Server 2005.
David|||Did you update the stats after the restore on the dev machine? If not the
optimizer may not have the correct info even if it is a better plan:).
--
Andrew J. Kelly SQL MVP
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:69a40194e9cbc@.uwe...
> In production we are running SQL Server 2000, SP4, on Windows 2003, using
> 4
> processors with 6 GB AWE Memory.
> I run DBCC DBReindex on all indexes and Update Statistics With FullScan on
> all indexes.
> A stored procedure with a complex query is timing out.
> SP_Who shows that the procedure is not getting blocked, but if anything,
> is
> doing the blocking.
> We restore the production database to our DEV environment which is running
> SQL Server 2005, SP1, on Windows 2003, using 2 processors and 3 GB Memory.
> The database from production, when restored, is left at Compatability 8.0.
> When this same stored procedure is run in DEV it executes in under 2
> seconds,
> with an entirely different execution plan than production.
> Is it possible, that the procedure, when run in DEV is utilizing some
> components of SQL 2005 to build a better plan, even though the
> Compatibility
> is left at 8.0?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1
>|||I did not run update stats on the DEV machine, since it was a restore of a
database that just had updated stats run on it, and additionally, the
procedure ran like a champ on the DEV machine.
Andrew J. Kelly wrote:
>Did you update the stats after the restore on the dev machine? If not the
>optimizer may not have the correct info even if it is a better plan:).
>> In production we are running SQL Server 2000, SP4, on Windows 2003, using
>> 4
>[quoted text clipped - 21 lines]
>> Compatibility
>> is left at 8.0?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1|||You should as a matter of practice update the stats after a restore
regardless of when they were ran last.
--
Andrew J. Kelly SQL MVP
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:69acf56bf722a@.uwe...
>I did not run update stats on the DEV machine, since it was a restore of a
> database that just had updated stats run on it, and additionally, the
> procedure ran like a champ on the DEV machine.
> Andrew J. Kelly wrote:
>>Did you update the stats after the restore on the dev machine? If not the
>>optimizer may not have the correct info even if it is a better plan:).
>> In production we are running SQL Server 2000, SP4, on Windows 2003,
>> using
>> 4
>>[quoted text clipped - 21 lines]
>> Compatibility
>> is left at 8.0?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1
>

Performance comparison between VARCHAR and CLOB?

Any idea on performance comparison between VARCHAR and CLOB? For example, if I want to insert or update 4000 characters in a field of a row and I am confused whether that column's type would better be decalred as VARCHAR or CLOB, what do you suggest?I suggest you not declare anything as a CLOB in sql server.

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)

Performance Comparison - Code vs SqlDataSource, Gridview etc vs PlainControl

There are so many ways to use database in asp.net/ado.net, I'm a bit confused about their difference from the performance point of view.

So apparently SqlDataSource in DataReader mode is faster than DataSet mode, at a cost of losing some bolt-on builtin functions.

What about SqlDataSource in DataReader mode vs manual binding in code? Say creating a SqlDataSource ds1 and set "DataSourceID" in Gridview, vs manually creating the SqlConnection, SqlCommand, SqlDataReader objects and mannually bind the myReader object to the gridview with the Bind() method.

Also Gridview is a very convenient control for many basic tasks. But for more complex scenarios it requires lots of customization and modification. Now if I do not use gridview at all and build the entire thing from scratch with basic web controls such as table and label controls, and mannually read and display everything from a DataReader object, how's the performance would be like compared to the Gridview-databind route?

I don't have a tested answer for you, just an opinion. Re your own connect vs SqlDataSource, there should be no differenct. The SqlDataSource has to do everything you do so I don't see any performance implication on a single screen. With that said, though, I could see somepotential connection pooling issues since minor differences in connection strings prevent asp.net from reusing the same connection. Without going into details, I think that awell writtenconnection object (that gets reused) is more likely to allow pooling than typing your connection details into wizards all the time. In any case, I wouldn't worry about it at this stage of the game.

Re GridView vs writing all that stuff yourself. If you use all or most or even some of that functionality, I don't think you should attempt to rewrite it -- it's just not worth it. You may even make performance worse if you write it wrong, and frankly, programmer time is worth more than cpu time.

If you're interested in pursuing the issue, Farhan Muhammed wrote a book which gives some fairly detailed numbers comparing different access methods and different controls -- he did real performance comparisons. Real World ASP.NET Best Practiceshttp://www.amazon.com/Real-World-ASP-NET-Best-Practices/dp/1590591003

|||

Thanks a lot for your opinion. By "well written connection object that gets reused" do you mean creating an SqlConnection object once, and try to re-use that same connection for as many command objects (select,insert,update tasks etc) as possible within the same scope?

|||

ilovecats:
do you mean creating an SqlConnection object once, and try to re-use that same connection for as many command objects (select,insert,update tasks etc) as possible within the same scope

Well, what I really mean is that you have a common object used by all pages & modules in your application that manages connections for you, along the lines of an Application Block like this onehttp://msdn2.microsoft.com/en-us/library/aa480458.aspx. Using something like this not only hides the details of the connection from the programmer, it goes a long way towards assuring consistency in connection strings -- which is something you need if asp.net is to be able to pool connections (ie, getting a connection is very expensive, so asp.net keeps a pool of connections around for reuse, if you request a connection and one is available from the pool that has an identical connection string, it gets that one instead of creating a new one).

Now, I'm sure that MS uses good programming when they obtain connections, but it used to be the case -- and I think still is largely the case -- that any differences in your connection string (even cosmetic ones, like case and extra white space) prevent sharing (pooling) connections, so if 2 people put in the same connection strings but with different case, they could not share connections in the pool. I don't know anymore how true this, I seem to recall reading that leading and trailing spaces don't matter anymore, but I'm not sure. A data connection application block would typically get the connection info from a config file, which is to say that everyone uses the same config file, which is to say everyone who connects to sqlserver1.mydatabase has an identical connection string. OTOH, if you use the SqlDataSource, every programmer is entering the connectio info (isn't that right -- perhaps I'm missing something because I haven't used them very much, but I think that's how it has to work -- if I'm wrong I hope someone will correct me), raising the possibility that the connection strings will be a little different, hence they cannot share connections.

I think I'll post a question on this topic to see if my understanding is current.

However, I frankly wouldn't worry about it too much. If you get into it, fine, but unless you're working on a high volume application it won't make a meaningful difference.

In my shop, we do use a data access application block that someone else wrote, but that didn't stop me from using the"no programming" features of asp.net 2.0 SqlDataSource because we just aren't a high volume app.

Wednesday, March 21, 2012

Performance comparison using Compability 8.0 on SQL 2005 vs. SQL 2000

Performance comparison using Compability 8.0 on SQL 2005 vs. SQL 2000

Performance comparison using Compability 8.0 on SQL 2005 vs. SQL 2000

Performance comparison between VARCHAR and CLOB?

Performance comparison between Enterprise and Standard edition

Performance comparison between Enterprise and Standard edition

Performance comparison between Enterprise and Standard edition

Performance Comparison - Code vs SqlDataSource, Gridview etc vs PlainControl

Percentage of total

Blog Archive

About Me