Convert a sql express to mysql

Hello.

I have created a web site using visual web developer (free download) The website was created using asp.net 2.0. The vwd I have incorporated the cool new roles and membership features of asp.net 2.0. When i did this vwd automatically created a sql express database (aspnetdb.mdf) to hold the login stuff.

I then purchased som e web hosting from a company and found out that they only support MySQL databases.

My questions are;

1. Can anyone tell me the difference between the two types of database (i know what sql express is but am not sure what MySQL is)

2. Secondly, is there a tool or way I can convert the SQL Express database to a MySQL database?

TIA

ICW

MySQL is a rather poor database that lots of people use illegally because they think it's free. If the web hosting company is charging you for hosting, they are using MySQL commercially and either paying for it or breaking the law.

Odds are that the SQL Server database is using stored procs, if this is so, then possibly not. If it's just using tables and dynamic queries, then probably.

|||

Thanks

The SQL Express database does indeed have several stored procs "aspnet_membership...". are you saying that MySQL cannot do stored procedures?

Regards

ICW

|||Hi there,

Yes, MySQL does have stored procedures. It didn't use to (pre version 5.0) but it does now. Please see this link for a write-up: http://dev.mysql.com/doc/refman/5.0/en/stored-procedures.html

If the hosting company you have chosen is using anything before MySQL 5.0 then stored procedures are out of the question. Also, if the stored procedures are using T-SQL specific constructs then you may have to find their equivalents (if they exist) in MySQL. For example, if you use X() in SQL Server the equivalent may be Y() in MySQL and so when you code your MySQL stored procedure you use Y().

However, if your stored procedures are doing something fancy then chances are you won't find a MySQL equivalent (although MySQL is faithful to all the standard SQL constructs for the most part so if you only use standard SQL then there's a chance there that you can migrate (reasonably) painlessly).

I have never had the experience of doing a SQL Server --> MySQL conversion, but here is a page that may help you:
http://dev.mysql.com/tech-resources/articles/migrating-from-microsoft.html

A quick browse shows it references a few tools that you might use and also has links to forums and other places where people (like MySQL experts) could offer some good advice.

Sorry I can't be of more help at the mo.
|||

Thanks for your help

regards

ICW

|||

Hi everyone

I too have been struggling to deploy the ASPNETDB database on alternative platforms. From many hours of reading MS documentation it seems that the only answer is to create your own Membership and Role providers that conform to the published API of ASP.NET This means writing a lot of code but it can be done. You also have to tell your application to use your version rather than the default one by altering the settings in the web.config file.

It seems that Microsoft left the door open for this and publish guidance to that effect. It's not a question of converting the tables etc, you can't do it! Its a matter of providing an interface between the objects built-in to the Framework class library and your own database engine and data files.

Now the real question is has anyone out there done this for MySql? Is there a software company selling addons to support it?

Regards

Phil Hall

Convert a sql express to mysql

Hello.

I have created a web site using visual web developer (free download) The website was created using asp.net 2.0. The vwd I have incorporated the cool new roles and membership features of asp.net 2.0. When i did this vwd automatically created a sql express database (aspnetdb.mdf) to hold the login stuff.

I then purchased som e web hosting from a company and found out that they only support MySQL databases.

My questions are;

1. Can anyone tell me the difference between the two types of database (i know what sql express is but am not sure what MySQL is)

2. Secondly, is there a tool or way I can convert the SQL Express database to a MySQL database?

TIA

ICW

MySQL is a rather poor database that lots of people use illegally because they think it's free. If the web hosting company is charging you for hosting, they are using MySQL commercially and either paying for it or breaking the law.

Odds are that the SQL Server database is using stored procs, if this is so, then possibly not. If it's just using tables and dynamic queries, then probably.

|||

Thanks

The SQL Express database does indeed have several stored procs "aspnet_membership...". are you saying that MySQL cannot do stored procedures?

Regards

ICW

|||Hi there,

Yes, MySQL does have stored procedures. It didn't use to (pre version 5.0) but it does now. Please see this link for a write-up: http://dev.mysql.com/doc/refman/5.0/en/stored-procedures.html

If the hosting company you have chosen is using anything before MySQL 5.0 then stored procedures are out of the question. Also, if the stored procedures are using T-SQL specific constructs then you may have to find their equivalents (if they exist) in MySQL. For example, if you use X() in SQL Server the equivalent may be Y() in MySQL and so when you code your MySQL stored procedure you use Y().

However, if your stored procedures are doing something fancy then chances are you won't find a MySQL equivalent (although MySQL is faithful to all the standard SQL constructs for the most part so if you only use standard SQL then there's a chance there that you can migrate (reasonably) painlessly).

I have never had the experience of doing a SQL Server --> MySQL conversion, but here is a page that may help you:
http://dev.mysql.com/tech-resources/articles/migrating-from-microsoft.html

A quick browse shows it references a few tools that you might use and also has links to forums and other places where people (like MySQL experts) could offer some good advice.

Sorry I can't be of more help at the mo.
|||

Thanks for your help

regards

ICW

|||

Hi everyone

I too have been struggling to deploy the ASPNETDB database on alternative platforms. From many hours of reading MS documentation it seems that the only answer is to create your own Membership and Role providers that conform to the published API of ASP.NET This means writing a lot of code but it can be done. You also have to tell your application to use your version rather than the default one by altering the settings in the web.config file.

It seems that Microsoft left the door open for this and publish guidance to that effect. It's not a question of converting the tables etc, you can't do it! Its a matter of providing an interface between the objects built-in to the Framework class library and your own database engine and data files.

Now the real question is has anyone out there done this for MySql? Is there a software company selling addons to support it?

Regards

Phil Hall

Conversion MSDE to SQL Server 2000

Here is the scoop:

I have MSDE installed on a production machine together with a bunch of ASP.NET applications. I was asked to replace MSDE with SQL Server 2000 and move every application from MSDE to SQL Server 2000 with minimum downtime.

Did any of you go through a similar process?

What is the best way of doing this conversion?

How to move the backups from MSDE to SQL Server?

Is it possible to have MSDE and SQL Server 2000 coexists for a time while moving stuff around and redirecting every connection?

Any hints or suggestions would be greatly appreciated.

Thanks.You could install Sql Server 2000 & use transfer objects (with data) to move the data between the databases.
MSDE is a "lite" version of Sql Server 2000, so you shouldn't need any conversions or transformations.|||Thanks for the answer, I did some research and I found this also, perhaps it will help others as well

http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q325023

Conversion from vb.net 1.0 to vb.net 2.0 (Crystal Reports Error)

i am facing the crystal reports error when i convert my project from vb.net 1.0 to 2.0. can any one tell me how i remove these errors

waiting ........

you havn't said what the errors are. AND this is not the forum for your question.

You will be waiting a long time.

sqlsql

Conversion from type SqlInt32 to type Integer is not valid]

Hi all,

I am developing ASP.NET 1.1 application using VB.NET & SQL Server, on my machine I am using SQL Server 2000, and everything is working just fine.

The problem appears when I uploaded the site to the Host, they are using SQL Server 2005, is there any reason for this, I am using casting in the code, and I am sure there is something wrong with the hosting settings.

Any suggestions.

Best Regards

Wafi Mohtaseb

You can perform an explicit conversion between those two types

Dim xAs System.Data.SqlTypes.SqlInt32 = 5Dim yAs Integer y =CType(x,Integer)

|||

This is what I am doing in the code, and its working on my local server, the problem appears when I uploaded the site to the hosting company server.

Best regards,

|||

please post the code you are having trouble with.

|||

1Public Overrides Function Insert()As Boolean2 Dim cmdToExecuteAs SqlCommand =New SqlCommand3 cmdToExecute.CommandText ="dbo.[UserFiles_Insert]"4 cmdToExecute.CommandType = CommandType.StoredProcedure56' // Use base class' connection object7 cmdToExecute.Connection = _mainConnection89Try10 cmdToExecute.Parameters.Add(New SqlParameter("@.UserID", SqlDbType.Int, 4, ParameterDirection.Input,False, 10, 0,"", DataRowVersion.Proposed, _userID))11 cmdToExecute.Parameters.Add(New SqlParameter("@.FileName", SqlDbType.VarChar, 50, ParameterDirection.Input,False, 0, 0,"", DataRowVersion.Proposed, _fileName))12 cmdToExecute.Parameters.Add(New SqlParameter("@.FileType", SqlDbType.VarChar, 50, ParameterDirection.Input,False, 0, 0,"", DataRowVersion.Proposed, _fileType))13 cmdToExecute.Parameters.Add(New SqlParameter("@.FileSize", SqlDbType.Decimal, 9, ParameterDirection.Input,False, 18, 0,"", DataRowVersion.Proposed, _fileSize))14Dim lengthAs Integer = 015If Not _fileContent.IsNullThen16 length = _fileContent.Length17End If18 cmdToExecute.Parameters.Add(New SqlParameter("@.FileContent", SqlDbType.Image, length, ParameterDirection.Input,False, 0, 0,"", DataRowVersion.Proposed, _fileContent))19 cmdToExecute.Parameters.Add(New SqlParameter("@.FileID", SqlDbType.Int, 4, ParameterDirection.Output,False, 10, 0,"", DataRowVersion.Proposed, _fileID))2021' // Open connection.22 _mainConnection.Open()2324' // Execute query.25 cmdToExecute.ExecuteNonQuery()26Dim _fileIDAs SqlInt32

//Error ocures here27_fileID =New SqlInt32(CType(cmdToExecute.Parameters.Item("@.FileID").Value,Integer))28Return True29 Catch exAs Exception30' // some error occured. Bubble it to caller and encapsulate Exception object31Throw New Exception("UserFiles::Insert::Error occured.", ex)32Finally33' // Close connection.34 _mainConnection.Close()35 cmdToExecute.Dispose()36End Try37 End Function

Converse to BulkLoad...

I've an Annotated Schema file (XSD) which describes how data is laid out in
multiple tables in SQL Server.
I've a .Net application that creates an instance of the BulkLoad COM object,
and calls the Execute method, populating my tables using the XSD with my XML
data.
Question is, how do I use the same XSD to then read that data from the
database?
Thanks for your time.
Daniel.Daniel,
Well, you don't really need the XSD to read the data from the database.
You can just read the data from the field when you perform a select. You
can call the ExecuteXmlReader on the SqlCommand to get the data (make sure
that you use a FOR XML clause, or that you select one column, one row that
returns text/ntext/xml data).
Because you used an XSD to validate the data going in, you can assume
that the field only contains data which conforms to that XSD. You don't
have to worry about validating on the way out.
- Nicholas Paldino [.NET/C# MVP]
- mvp@.spam.guard.caspershouse.com
"Daniel Bass" <danREMOVEbass@.blueCAPSbottle.comFIRST> wrote in message
news:%230Hf$CxvHHA.1756@.TK2MSFTNGP05.phx.gbl...
> I've an Annotated Schema file (XSD) which describes how data is laid out
> in multiple tables in SQL Server.
> I've a .Net application that creates an instance of the BulkLoad COM
> object, and calls the Execute method, populating my tables using the XSD
> with my XML data.
> Question is, how do I use the same XSD to then read that data from the
> database?
> Thanks for your time.
> Daniel.
>|||Thanks for your prompt reply.
The situation I have is that I'm using a generic "loader" which I pass XML
into, and using some XPath configuration, decide which XSD fits the XML and
loads the data into my tables. The Xml contains multiple levels and so I'll
be pushing data into parent/child/grandchild structured tables.
In my code, I also want some way of initiating a data "pull", so that on
some event, given some XSD, I'll pull the data from the database. I need the
XSD because in the compiled application I'll have no way of knowing the
table structure...
As I discuss this, I'm wondering about creating a "select" stored procedure
for each message type, which uses FOR XML to correctly format my text into
the XML I want. Then all I need is some event table which tells the compiled
application what new message I need to retrieve, which then lets me know the
sp I need to call...
What do you think?
Thanks,
Dan.
"Nicholas Paldino [.NET/C# MVP]" <mvp@.spam.guard.caspershouse.com> wrote in
message news:OR0cSHxvHHA.4640@.TK2MSFTNGP03.phx.gbl...
> Daniel,
> Well, you don't really need the XSD to read the data from the database.
> You can just read the data from the field when you perform a select. You
> can call the ExecuteXmlReader on the SqlCommand to get the data (make sure
> that you use a FOR XML clause, or that you select one column, one row that
> returns text/ntext/xml data).
> Because you used an XSD to validate the data going in, you can assume
> that the field only contains data which conforms to that XSD. You don't
> have to worry about validating on the way out.
>
> --
> - Nicholas Paldino [.NET/C# MVP]
> - mvp@.spam.guard.caspershouse.com
> "Daniel Bass" <danREMOVEbass@.blueCAPSbottle.comFIRST> wrote in message
> news:%230Hf$CxvHHA.1756@.TK2MSFTNGP05.phx.gbl...
>

Controls on reports

Hi,
I have just created my first report using VS.NET 2005 and Reporting
Services. There is a small choice of controls to une on my reports.
The simplest control is Label, which I cannot find.
Please tell me how to place some text on a report.
regards
PrzemoThere is no difference between text and label. The reason is, this is a
report not a data entry form so it is not necessary to differentiate between
them. Put a text box on the form and then just type in whatever text you
want. Or, if you are going to be more complicated, set it to an expression
and use the expression builder to put in more complex text (for instance you
can refer to the parameters so you can show what parameters the user picked
when running the report).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Przemo" <Przemo@.discussions.microsoft.com> wrote in message
news:C75CC836-E033-4ACD-9FE1-35992B25B326@.microsoft.com...
> Hi,
> I have just created my first report using VS.NET 2005 and Reporting
> Services. There is a small choice of controls to une on my reports.
> The simplest control is Label, which I cannot find.
> Please tell me how to place some text on a report.
> regards
> Przemo

Controlling Security through Web Application

Hello all,

I have some questions hopefully you can help with regarding controlling access through ASP.NET.

We'd like to take advantage of reporting services functionality for reports, but we'd like to use our own security model. We have an extensive database-driven security model that exists independently of active directory and windows permissions. We use the NT Logon of the user, but that is all we use. Everything else is maintained within our database structure. We also have all of our web pages and reports in a large database with individual ID's that are permissioned against th

Currently we need a way to link users to reporting services using our own authentication, but this presents a problem. Currently we have a wrapper page (let's call it ReportAccess.aspx). That page authenticates the user and decides whether or not they have access to the report, and then should deliver the report.

Here is where I'm not sure what to do. Our original component simply threw the report URL into an IFRAME. So in order to make this work, we had to give ALL users permissions to all reports on reporting services, since the credentials get passed through. The problem here is that savvy people could look at the URLs and hack their way into reports they should not be seeing.

Ideally, we'd like to only give access to one account and have the ReportAccess.aspx page control that access, but I am not sure how to pull this off. Is this even possible?

The ugly alternative would be maintaining permissions in our system AND Reporting Services ... which would be a lot of work and juggling. There has to be a better way

Okay, I came across the ReportViewer control in ASP.NET and that may be exactly what I was looking for, but I am having a little bit of trouble getting it to work.

I have setup the component as follows:

<rsweb:ReportViewer ID="ReportViewer1" runat="server" ProcessingMode="Remote" >
<ServerReport ReportServerUrl="http://rs2k5/Reports/Pages/Folder.aspx" ReportPath="/MyReportFolder/MyReportName" DisplayName="Test Report" />
<LocalReport />
</rsweb:ReportViewer>

For the record, you can access the reports using this URL:
http://rs2k5/Reports/Pages/Report.aspx?ItemPath=%2fMyReportFolder%2fMyReportName

When I use [http://rs2k5/Reports/Pages/] as the ServerURL, I get a 404 file not found. Interestingly enough, when I use [http://rs2k5/Reports/Pages/Folder.aspx], I get something back, but it looks like this:

Client found response content type of 'text/html; charset=utf-8', but expected 'text/xml'. The request failed with the error message: -- <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" > <HTML> <HEAD> <script language="JScript" type="text/Javascript" ****snipped - very long HTML here****
|||To get entirely around security using the report viewer, you need to use an rdlc local to your project that does not rely on the report server all.|||

This is a pretty interesting approach. I had no idea the RDLC option existed. I'm going to test some with that.

Currently, I was able to get the ReportViewer working with Remote option. Turns out you can't point at your ReportServer, instead you have to point at your ReportManager. Using impersonation, I was able to successfully mimic a user's security, so that all users could access the reports using one single account (and then I can use my own validation on the ASP.NET page.

control concurrent users?

is it possible to limit the concurrent users in SQL connection string? Now, i m using .net 2003.

regards,

You can change the "max worker threads" option in SQL Server by running the statements below:

sp_configure 'show advanced options',1
reconfigure
go
sp_configure 'max worker threads',255
reconfigure
go

For more information about this option, please take a look at:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_config_09wu.asp

|||

the problem is how can i configure my customer's SQL Server configuration. Now, i get one way. is it the right one?

Data Source=XXX;database=XXX;UID=XXX;PWD=XXX;MAX POOL SIZE=5

|||Sorry for misunderstanding. Yes the MAX POOL SIZE property should work in this case.

Context Swiitching

Hi All
We recently upgraded one of our application rinning on .net and sql server
to New IBM Server.
This is a Quad processor(1.7Ghz), with 8 gb of ram. but when we migrated we
found that the system is much slower then the old system,
This is a Win2K3, running SQL 2K with SP3a. , hyperthreading on.
All my reporting queries are running slower. in fact the whole application
is running slower.
What i found out is, on old and new box the CPU util is just around 40%
with full load. But on old box which is a dual processor with hyperthreading
on, the contex swith/sec is 4000 but on new box it is about 9000/sec, I did
turn on the Lightweight pooling so that SQL uses Fiber instead of thread
mode, i have tried adjusting both the parallelism paramater, cost threshold
and max degree.. but do not see any difference in performance..Context
swithching is still 8k to 9k .
please help..
Definitely turn off the fiber mode either way. An 4 proc system will have
more context switching than a 2 proc in general, especially if they are HT.
You also want to make sure the MAXDOP at the server level is no more than
the # of physical processors. I would even try 2 and see how it goes. If the
new machine can process stuff faster or with more processors and your I/O
can't keep up or you have too little memory you can also get more context
switches. This is basically due to the fact it can't keep working while it
is waiting on resources and will swap out for another thread. That causes
more overhead and decreases overall throughput. But the bottom line is that
you can't directly compare two completely different hardware platforms as
apples to apples. You didn't mention what the old system was , how much ram,
speed of procs etc but it could be you need to adjust your configuration to
take advantage of the newer hardware.
Andrew J. Kelly SQL MVP
"Nik" <sqlserverkb@.yahoo.com> wrote in message
news:%23htrpLQQFHA.2584@.TK2MSFTNGP15.phx.gbl...
> Hi All
> We recently upgraded one of our application rinning on .net and sql server
> to New IBM Server.
> This is a Quad processor(1.7Ghz), with 8 gb of ram. but when we migrated
> we
> found that the system is much slower then the old system,
> This is a Win2K3, running SQL 2K with SP3a. , hyperthreading on.
> All my reporting queries are running slower. in fact the whole application
> is running slower.
> What i found out is, on old and new box the CPU util is just around 40%
> with full load. But on old box which is a dual processor with
> hyperthreading
> on, the contex swith/sec is 4000 but on new box it is about 9000/sec, I
> did
> turn on the Lightweight pooling so that SQL uses Fiber instead of thread
> mode, i have tried adjusting both the parallelism paramater, cost
> threshold
> and max degree.. but do not see any difference in performance..Context
> swithching is still 8k to 9k .
> please help..
>
>
|||Thanks Andrew,
I already made the MAXDOP to 4 . no improvement.
Old System is 2 procs(2Ghz), 2Gb ram, HT on, but it hosts SQL, IIS,
Reporting Services, Web Services
New One is 4 proc (1.7Ghz),8GB ram, HT on but just hosts SQL Server and
reports database used for reporting services.
Do you think the CPU speed is making it much slower, which i do not think
would be the case, I know the queries are badly written, and i mean bad.
but still the same query runs much much say on old system it takes 17 sec to
bring the report on new system it takes around 27 to 30 secs to bring the
report up.
Database setups are the same, we are using DVP but both the databases reside
on the same server and a dvp is created on both the datbases.
what elso do you think i should check for...
any ideas are most welcome..
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OViGjBRQFHA.164@.TK2MSFTNGP12.phx.gbl...
> Definitely turn off the fiber mode either way. An 4 proc system will have
> more context switching than a 2 proc in general, especially if they are
HT.
> You also want to make sure the MAXDOP at the server level is no more than
> the # of physical processors. I would even try 2 and see how it goes. If
the
> new machine can process stuff faster or with more processors and your I/O
> can't keep up or you have too little memory you can also get more context
> switches. This is basically due to the fact it can't keep working while it
> is waiting on resources and will swap out for another thread. That causes
> more overhead and decreases overall throughput. But the bottom line is
that
> you can't directly compare two completely different hardware platforms as
> apples to apples. You didn't mention what the old system was , how much
ram,
> speed of procs etc but it could be you need to adjust your configuration
to[vbcol=seagreen]
> take advantage of the newer hardware.
> --
> Andrew J. Kelly SQL MVP
>
> "Nik" <sqlserverkb@.yahoo.com> wrote in message
> news:%23htrpLQQFHA.2584@.TK2MSFTNGP15.phx.gbl...
server[vbcol=seagreen]
application[vbcol=seagreen]
40%
>
|||DPV's don't do a whole lot in most cases for performance if they are on the
same machine. If your query scans the whole table then partitioning it out
may help but better indexing would probably do more. When it comes to
processors faster is always better but the amount of cache may make quite a
bit of difference as well. A faster proc with more L2 or L3 cache can be a
lot faster than a slower one with less cache. If your queries are poorly
written then you are probably doing lots of scans and you may be using too
much parallelism still. Try setting the MAXDOP to 2 and see if that makes a
difference. Are the query plans the same as before? Have you run
sp_updatestats lately?
Andrew J. Kelly SQL MVP
"Nik" <sqlserverkb@.yahoo.com> wrote in message
news:%23ua%23CRRQFHA.3664@.TK2MSFTNGP15.phx.gbl...
> Thanks Andrew,
> I already made the MAXDOP to 4 . no improvement.
> Old System is 2 procs(2Ghz), 2Gb ram, HT on, but it hosts SQL, IIS,
> Reporting Services, Web Services
> New One is 4 proc (1.7Ghz),8GB ram, HT on but just hosts SQL Server and
> reports database used for reporting services.
> Do you think the CPU speed is making it much slower, which i do not think
> would be the case, I know the queries are badly written, and i mean bad.
> but still the same query runs much much say on old system it takes 17 sec
> to
> bring the report on new system it takes around 27 to 30 secs to bring the
> report up.
> Database setups are the same, we are using DVP but both the databases
> reside
> on the same server and a dvp is created on both the datbases.
> what elso do you think i should check for...
> any ideas are most welcome..
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OViGjBRQFHA.164@.TK2MSFTNGP12.phx.gbl...
> HT.
> the
> that
> ram,
> to
> server
> application
> 40%
>
|||Yes DVP perrformance is not affected as it is on the same box, and that was
the reason we had to bring on one box.
but The older box has 512k L3 on2.8 ghz and new one has 1Mb L3 on 1.5 ghz
still i do not understand the context switching.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O1r9EXSQFHA.688@.TK2MSFTNGP10.phx.gbl...
> DPV's don't do a whole lot in most cases for performance if they are on
the
> same machine. If your query scans the whole table then partitioning it
out
> may help but better indexing would probably do more. When it comes to
> processors faster is always better but the amount of cache may make quite
a
> bit of difference as well. A faster proc with more L2 or L3 cache can be
a
> lot faster than a slower one with less cache. If your queries are poorly
> written then you are probably doing lots of scans and you may be using too
> much parallelism still. Try setting the MAXDOP to 2 and see if that makes
a[vbcol=seagreen]
> difference. Are the query plans the same as before? Have you run
> sp_updatestats lately?
> --
> Andrew J. Kelly SQL MVP
>
> "Nik" <sqlserverkb@.yahoo.com> wrote in message
> news:%23ua%23CRRQFHA.3664@.TK2MSFTNGP15.phx.gbl...
think[vbcol=seagreen]
sec[vbcol=seagreen]
the[vbcol=seagreen]
than[vbcol=seagreen]
If[vbcol=seagreen]
I/O[vbcol=seagreen]
context[vbcol=seagreen]
as[vbcol=seagreen]
configuration[vbcol=seagreen]
I[vbcol=seagreen]
performance..Context
>
|||What about disk system and where are located tempdb, data and transaction log.
AMB
"Nik" wrote:

> Thanks Andrew,
> I already made the MAXDOP to 4 . no improvement.
> Old System is 2 procs(2Ghz), 2Gb ram, HT on, but it hosts SQL, IIS,
> Reporting Services, Web Services
> New One is 4 proc (1.7Ghz),8GB ram, HT on but just hosts SQL Server and
> reports database used for reporting services.
> Do you think the CPU speed is making it much slower, which i do not think
> would be the case, I know the queries are badly written, and i mean bad.
> but still the same query runs much much say on old system it takes 17 sec to
> bring the report on new system it takes around 27 to 30 secs to bring the
> report up.
> Database setups are the same, we are using DVP but both the databases reside
> on the same server and a dvp is created on both the datbases.
> what elso do you think i should check for...
> any ideas are most welcome..
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OViGjBRQFHA.164@.TK2MSFTNGP12.phx.gbl...
> HT.
> the
> that
> ram,
> to
> server
> application
> 40%
>
>
|||But the older box was almost twice as fast processor speed wise. That is a
big difference. How could you even get a new box with just 1.5GHZ proc's?
What about the disk system? Is that the same or is that different as well.
Andrew J. Kelly SQL MVP
"Nik" <sqlserverkb@.yahoo.com> wrote in message
news:%23jvSu6SQFHA.1500@.TK2MSFTNGP09.phx.gbl...
> Yes DVP perrformance is not affected as it is on the same box, and that
> was
> the reason we had to bring on one box.
> but The older box has 512k L3 on2.8 ghz and new one has 1Mb L3 on 1.5 ghz
> still i do not understand the context switching.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O1r9EXSQFHA.688@.TK2MSFTNGP10.phx.gbl...
> the
> out
> a
> a
> a
> think
> sec
> the
> than
> If
> I/O
> context
> as
> configuration
> I
> performance..Context
>
|||AFAIK, you can't event get Xeons/Pentium that slow anymore. Maybe IA64?
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23GFEvOWQFHA.3336@.TK2MSFTNGP09.phx.gbl...
> But the older box was almost twice as fast processor speed wise. That is
> a big difference. How could you even get a new box with just 1.5GHZ
> proc's? What about the disk system? Is that the same or is that different
> as well.
> --
> Andrew J. Kelly SQL MVP
>
> "Nik" <sqlserverkb@.yahoo.com> wrote in message
> news:%23jvSu6SQFHA.1500@.TK2MSFTNGP09.phx.gbl...
>
|||i know but this was a management decision, without the geting dba's opinion.
I know they had screwed up in that part, but do you guys think that CPU
speed is contributing to the slowness of the entire system,
the I/O subsystem is faster on the new box.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:OltsgBcQFHA.3544@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> AFAIK, you can't event get Xeons/Pentium that slow anymore. Maybe IA64?
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23GFEvOWQFHA.3336@.TK2MSFTNGP09.phx.gbl...
is[vbcol=seagreen]
different[vbcol=seagreen]
ghz[vbcol=seagreen]
on[vbcol=seagreen]
it[vbcol=seagreen]
17[vbcol=seagreen]
bring[vbcol=seagreen]
databases[vbcol=seagreen]
will[vbcol=seagreen]
more[vbcol=seagreen]
goes.[vbcol=seagreen]
your[vbcol=seagreen]
line[vbcol=seagreen]
sql
>
|||TempDb is on a raid 10,same with data and tran logs. they all are on
different drives..with individual controller
Tempdb is scattered on 4 data files..
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:F9CE53C8-8563-41F9-8A4A-9818B9C5C0C0@.microsoft.com...
> What about disk system and where are located tempdb, data and transaction
log.[vbcol=seagreen]
>
> AMB
> "Nik" wrote:
think[vbcol=seagreen]
sec to[vbcol=seagreen]
the[vbcol=seagreen]
reside[vbcol=seagreen]
have[vbcol=seagreen]
are[vbcol=seagreen]
than[vbcol=seagreen]
If[vbcol=seagreen]
I/O[vbcol=seagreen]
context[vbcol=seagreen]
while it[vbcol=seagreen]
causes[vbcol=seagreen]
is[vbcol=seagreen]
as[vbcol=seagreen]
much[vbcol=seagreen]
configuration[vbcol=seagreen]
migrated[vbcol=seagreen]
around[vbcol=seagreen]
9000/sec, I[vbcol=seagreen]
thread[vbcol=seagreen]
performance..Context[vbcol=seagreen]

Context Swiitching

Hi All
We recently upgraded one of our application rinning on .net and sql server
to New IBM Server.
This is a Quad processor(1.7Ghz), with 8 gb of ram. but when we migrated we
found that the system is much slower then the old system,
This is a Win2K3, running SQL 2K with SP3a. , hyperthreading on.
All my reporting queries are running slower. in fact the whole application
is running slower.
What i found out is, on old and new box the CPU util is just around 40%
with full load. But on old box which is a dual processor with hyperthreading
on, the contex swith/sec is 4000 but on new box it is about 9000/sec, I did
turn on the Lightweight pooling so that SQL uses Fiber instead of thread
mode, i have tried adjusting both the parallelism paramater, cost threshold
and max degree.. but do not see any difference in performance..Context
swithching is still 8k to 9k .
please help..Definitely turn off the fiber mode either way. An 4 proc system will have
more context switching than a 2 proc in general, especially if they are HT.
You also want to make sure the MAXDOP at the server level is no more than
the # of physical processors. I would even try 2 and see how it goes. If the
new machine can process stuff faster or with more processors and your I/O
can't keep up or you have too little memory you can also get more context
switches. This is basically due to the fact it can't keep working while it
is waiting on resources and will swap out for another thread. That causes
more overhead and decreases overall throughput. But the bottom line is that
you can't directly compare two completely different hardware platforms as
apples to apples. You didn't mention what the old system was , how much ram,
speed of procs etc but it could be you need to adjust your configuration to
take advantage of the newer hardware.
Andrew J. Kelly SQL MVP
"Nik" <sqlserverkb@.yahoo.com> wrote in message
news:%23htrpLQQFHA.2584@.TK2MSFTNGP15.phx.gbl...
> Hi All
> We recently upgraded one of our application rinning on .net and sql server
> to New IBM Server.
> This is a Quad processor(1.7Ghz), with 8 gb of ram. but when we migrated
> we
> found that the system is much slower then the old system,
> This is a Win2K3, running SQL 2K with SP3a. , hyperthreading on.
> All my reporting queries are running slower. in fact the whole application
> is running slower.
> What i found out is, on old and new box the CPU util is just around 40%
> with full load. But on old box which is a dual processor with
> hyperthreading
> on, the contex swith/sec is 4000 but on new box it is about 9000/sec, I
> did
> turn on the Lightweight pooling so that SQL uses Fiber instead of thread
> mode, i have tried adjusting both the parallelism paramater, cost
> threshold
> and max degree.. but do not see any difference in performance..Context
> swithching is still 8k to 9k .
> please help..
>
>|||Thanks Andrew,
I already made the MAXDOP to 4 . no improvement.
Old System is 2 procs(2Ghz), 2Gb ram, HT on, but it hosts SQL, IIS,
Reporting Services, Web Services
New One is 4 proc (1.7Ghz),8GB ram, HT on but just hosts SQL Server and
reports database used for reporting services.
Do you think the CPU speed is making it much slower, which i do not think
would be the case, I know the queries are badly written, and i mean bad.
but still the same query runs much much say on old system it takes 17 sec to
bring the report on new system it takes around 27 to 30 secs to bring the
report up.
Database setups are the same, we are using DVP but both the databases reside
on the same server and a dvp is created on both the datbases.
what elso do you think i should check for...
any ideas are most welcome..
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OViGjBRQFHA.164@.TK2MSFTNGP12.phx.gbl...
> Definitely turn off the fiber mode either way. An 4 proc system will have
> more context switching than a 2 proc in general, especially if they are
HT.
> You also want to make sure the MAXDOP at the server level is no more than
> the # of physical processors. I would even try 2 and see how it goes. If
the
> new machine can process stuff faster or with more processors and your I/O
> can't keep up or you have too little memory you can also get more context
> switches. This is basically due to the fact it can't keep working while it
> is waiting on resources and will swap out for another thread. That causes
> more overhead and decreases overall throughput. But the bottom line is
that
> you can't directly compare two completely different hardware platforms as
> apples to apples. You didn't mention what the old system was , how much
ram,
> speed of procs etc but it could be you need to adjust your configuration
to
> take advantage of the newer hardware.
> --
> Andrew J. Kelly SQL MVP
>
> "Nik" <sqlserverkb@.yahoo.com> wrote in message
> news:%23htrpLQQFHA.2584@.TK2MSFTNGP15.phx.gbl...
server[vbcol=seagreen]
application[vbcol=seagreen]
40%[vbcol=seagreen]
>|||DPV's don't do a whole lot in most cases for performance if they are on the
same machine. If your query scans the whole table then partitioning it out
may help but better indexing would probably do more. When it comes to
processors faster is always better but the amount of cache may make quite a
bit of difference as well. A faster proc with more L2 or L3 cache can be a
lot faster than a slower one with less cache. If your queries are poorly
written then you are probably doing lots of scans and you may be using too
much parallelism still. Try setting the MAXDOP to 2 and see if that makes a
difference. Are the query plans the same as before? Have you run
sp_updatestats lately?
Andrew J. Kelly SQL MVP
"Nik" <sqlserverkb@.yahoo.com> wrote in message
news:%23ua%23CRRQFHA.3664@.TK2MSFTNGP15.phx.gbl...
> Thanks Andrew,
> I already made the MAXDOP to 4 . no improvement.
> Old System is 2 procs(2Ghz), 2Gb ram, HT on, but it hosts SQL, IIS,
> Reporting Services, Web Services
> New One is 4 proc (1.7Ghz),8GB ram, HT on but just hosts SQL Server and
> reports database used for reporting services.
> Do you think the CPU speed is making it much slower, which i do not think
> would be the case, I know the queries are badly written, and i mean bad.
> but still the same query runs much much say on old system it takes 17 sec
> to
> bring the report on new system it takes around 27 to 30 secs to bring the
> report up.
> Database setups are the same, we are using DVP but both the databases
> reside
> on the same server and a dvp is created on both the datbases.
> what elso do you think i should check for...
> any ideas are most welcome..
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OViGjBRQFHA.164@.TK2MSFTNGP12.phx.gbl...
> HT.
> the
> that
> ram,
> to
> server
> application
> 40%
>|||Yes DVP perrformance is not affected as it is on the same box, and that was
the reason we had to bring on one box.
but The older box has 512k L3 on2.8 ghz and new one has 1Mb L3 on 1.5 ghz
still i do not understand the context switching.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O1r9EXSQFHA.688@.TK2MSFTNGP10.phx.gbl...
> DPV's don't do a whole lot in most cases for performance if they are on
the
> same machine. If your query scans the whole table then partitioning it
out
> may help but better indexing would probably do more. When it comes to
> processors faster is always better but the amount of cache may make quite
a
> bit of difference as well. A faster proc with more L2 or L3 cache can be
a
> lot faster than a slower one with less cache. If your queries are poorly
> written then you are probably doing lots of scans and you may be using too
> much parallelism still. Try setting the MAXDOP to 2 and see if that makes
a
> difference. Are the query plans the same as before? Have you run
> sp_updatestats lately?
> --
> Andrew J. Kelly SQL MVP
>
> "Nik" <sqlserverkb@.yahoo.com> wrote in message
> news:%23ua%23CRRQFHA.3664@.TK2MSFTNGP15.phx.gbl...
think[vbcol=seagreen]
sec[vbcol=seagreen]
the[vbcol=seagreen]
than[vbcol=seagreen]
If[vbcol=seagreen]
I/O[vbcol=seagreen]
context[vbcol=seagreen]
as[vbcol=seagreen]
configuration[vbcol=seagreen]
I[vbcol=seagreen]
performance..Context[vbcol=seagreen]
>|||What about disk system and where are located tempdb, data and transaction lo
g.
AMB
"Nik" wrote:

> Thanks Andrew,
> I already made the MAXDOP to 4 . no improvement.
> Old System is 2 procs(2Ghz), 2Gb ram, HT on, but it hosts SQL, IIS,
> Reporting Services, Web Services
> New One is 4 proc (1.7Ghz),8GB ram, HT on but just hosts SQL Server and
> reports database used for reporting services.
> Do you think the CPU speed is making it much slower, which i do not think
> would be the case, I know the queries are badly written, and i mean bad.
> but still the same query runs much much say on old system it takes 17 sec
to
> bring the report on new system it takes around 27 to 30 secs to bring the
> report up.
> Database setups are the same, we are using DVP but both the databases resi
de
> on the same server and a dvp is created on both the datbases.
> what elso do you think i should check for...
> any ideas are most welcome..
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OViGjBRQFHA.164@.TK2MSFTNGP12.phx.gbl...
> HT.
> the
> that
> ram,
> to
> server
> application
> 40%
>
>|||But the older box was almost twice as fast processor speed wise. That is a
big difference. How could you even get a new box with just 1.5GHZ proc's?
What about the disk system? Is that the same or is that different as well.
Andrew J. Kelly SQL MVP
"Nik" <sqlserverkb@.yahoo.com> wrote in message
news:%23jvSu6SQFHA.1500@.TK2MSFTNGP09.phx.gbl...
> Yes DVP perrformance is not affected as it is on the same box, and that
> was
> the reason we had to bring on one box.
> but The older box has 512k L3 on2.8 ghz and new one has 1Mb L3 on 1.5 ghz
> still i do not understand the context switching.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O1r9EXSQFHA.688@.TK2MSFTNGP10.phx.gbl...
> the
> out
> a
> a
> a
> think
> sec
> the
> than
> If
> I/O
> context
> as
> configuration
> I
> performance..Context
>|||AFAIK, you can't event get Xeons/Pentium that slow anymore. Maybe IA64?
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23GFEvOWQFHA.3336@.TK2MSFTNGP09.phx.gbl...
> But the older box was almost twice as fast processor speed wise. That is
> a big difference. How could you even get a new box with just 1.5GHZ
> proc's? What about the disk system? Is that the same or is that different
> as well.
> --
> Andrew J. Kelly SQL MVP
>
> "Nik" <sqlserverkb@.yahoo.com> wrote in message
> news:%23jvSu6SQFHA.1500@.TK2MSFTNGP09.phx.gbl...
>|||i know but this was a management decision, without the geting dba's opinion.
I know they had screwed up in that part, but do you guys think that CPU
speed is contributing to the slowness of the entire system,
the I/O subsystem is faster on the new box.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:OltsgBcQFHA.3544@.TK2MSFTNGP12.phx.gbl...
> AFAIK, you can't event get Xeons/Pentium that slow anymore. Maybe IA64?
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23GFEvOWQFHA.3336@.TK2MSFTNGP09.phx.gbl...
is[vbcol=seagreen]
different[vbcol=seagreen]
ghz[vbcol=seagreen]
on[vbcol=seagreen]
it[vbcol=seagreen]
17[vbcol=seagreen]
bring[vbcol=seagreen]
databases[vbcol=seagreen]
will[vbcol=seagreen]
more[vbcol=seagreen]
goes.[vbcol=seagreen]
your[vbcol=seagreen]
line[vbcol=seagreen]
sql[vbcol=seagreen]
>|||TempDb is on a raid 10,same with data and tran logs. they all are on
different drives..with individual controller
Tempdb is scattered on 4 data files..
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:F9CE53C8-8563-41F9-8A4A-9818B9C5C0C0@.microsoft.com...
> What about disk system and where are located tempdb, data and transaction
log.[vbcol=seagreen]
>
> AMB
> "Nik" wrote:
>
think[vbcol=seagreen]
sec to[vbcol=seagreen]
the[vbcol=seagreen]
reside[vbcol=seagreen]
have[vbcol=seagreen]
are[vbcol=seagreen]
than[vbcol=seagreen]
If[vbcol=seagreen]
I/O[vbcol=seagreen]
context[vbcol=seagreen]
while it[vbcol=seagreen]
causes[vbcol=seagreen]
is[vbcol=seagreen]
as[vbcol=seagreen]
much[vbcol=seagreen]
configuration[vbcol=seagreen]
migrated[vbcol=seagreen]
around[vbcol=seagreen]
9000/sec, I[vbcol=seagreen]
thread[vbcol=seagreen]
performance..Context[vbcol=seagreen]

Context Swiitching

Hi All
We recently upgraded one of our application rinning on .net and sql server
to New IBM Server.
This is a Quad processor(1.7Ghz), with 8 gb of ram. but when we migrated we
found that the system is much slower then the old system,
This is a Win2K3, running SQL 2K with SP3a. , hyperthreading on.
All my reporting queries are running slower. in fact the whole application
is running slower.
What i found out is, on old and new box the CPU util is just around 40%
with full load. But on old box which is a dual processor with hyperthreading
on, the contex swith/sec is 4000 but on new box it is about 9000/sec, I did
turn on the Lightweight pooling so that SQL uses Fiber instead of thread
mode, i have tried adjusting both the parallelism paramater, cost threshold
and max degree.. but do not see any difference in performance..Context
swithching is still 8k to 9k .
please help..Definitely turn off the fiber mode either way. An 4 proc system will have
more context switching than a 2 proc in general, especially if they are HT.
You also want to make sure the MAXDOP at the server level is no more than
the # of physical processors. I would even try 2 and see how it goes. If the
new machine can process stuff faster or with more processors and your I/O
can't keep up or you have too little memory you can also get more context
switches. This is basically due to the fact it can't keep working while it
is waiting on resources and will swap out for another thread. That causes
more overhead and decreases overall throughput. But the bottom line is that
you can't directly compare two completely different hardware platforms as
apples to apples. You didn't mention what the old system was , how much ram,
speed of procs etc but it could be you need to adjust your configuration to
take advantage of the newer hardware.
--
Andrew J. Kelly SQL MVP
"Nik" <sqlserverkb@.yahoo.com> wrote in message
news:%23htrpLQQFHA.2584@.TK2MSFTNGP15.phx.gbl...
> Hi All
> We recently upgraded one of our application rinning on .net and sql server
> to New IBM Server.
> This is a Quad processor(1.7Ghz), with 8 gb of ram. but when we migrated
> we
> found that the system is much slower then the old system,
> This is a Win2K3, running SQL 2K with SP3a. , hyperthreading on.
> All my reporting queries are running slower. in fact the whole application
> is running slower.
> What i found out is, on old and new box the CPU util is just around 40%
> with full load. But on old box which is a dual processor with
> hyperthreading
> on, the contex swith/sec is 4000 but on new box it is about 9000/sec, I
> did
> turn on the Lightweight pooling so that SQL uses Fiber instead of thread
> mode, i have tried adjusting both the parallelism paramater, cost
> threshold
> and max degree.. but do not see any difference in performance..Context
> swithching is still 8k to 9k .
> please help..
>
>|||Thanks Andrew,
I already made the MAXDOP to 4 . no improvement.
Old System is 2 procs(2Ghz), 2Gb ram, HT on, but it hosts SQL, IIS,
Reporting Services, Web Services
New One is 4 proc (1.7Ghz),8GB ram, HT on but just hosts SQL Server and
reports database used for reporting services.
Do you think the CPU speed is making it much slower, which i do not think
would be the case, I know the queries are badly written, and i mean bad.
but still the same query runs much much say on old system it takes 17 sec to
bring the report on new system it takes around 27 to 30 secs to bring the
report up.
Database setups are the same, we are using DVP but both the databases reside
on the same server and a dvp is created on both the datbases.
what elso do you think i should check for...
any ideas are most welcome..
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OViGjBRQFHA.164@.TK2MSFTNGP12.phx.gbl...
> Definitely turn off the fiber mode either way. An 4 proc system will have
> more context switching than a 2 proc in general, especially if they are
HT.
> You also want to make sure the MAXDOP at the server level is no more than
> the # of physical processors. I would even try 2 and see how it goes. If
the
> new machine can process stuff faster or with more processors and your I/O
> can't keep up or you have too little memory you can also get more context
> switches. This is basically due to the fact it can't keep working while it
> is waiting on resources and will swap out for another thread. That causes
> more overhead and decreases overall throughput. But the bottom line is
that
> you can't directly compare two completely different hardware platforms as
> apples to apples. You didn't mention what the old system was , how much
ram,
> speed of procs etc but it could be you need to adjust your configuration
to
> take advantage of the newer hardware.
> --
> Andrew J. Kelly SQL MVP
>
> "Nik" <sqlserverkb@.yahoo.com> wrote in message
> news:%23htrpLQQFHA.2584@.TK2MSFTNGP15.phx.gbl...
> > Hi All
> > We recently upgraded one of our application rinning on .net and sql
server
> > to New IBM Server.
> > This is a Quad processor(1.7Ghz), with 8 gb of ram. but when we migrated
> > we
> > found that the system is much slower then the old system,
> > This is a Win2K3, running SQL 2K with SP3a. , hyperthreading on.
> > All my reporting queries are running slower. in fact the whole
application
> > is running slower.
> > What i found out is, on old and new box the CPU util is just around
40%
> > with full load. But on old box which is a dual processor with
> > hyperthreading
> > on, the contex swith/sec is 4000 but on new box it is about 9000/sec, I
> > did
> > turn on the Lightweight pooling so that SQL uses Fiber instead of thread
> > mode, i have tried adjusting both the parallelism paramater, cost
> > threshold
> > and max degree.. but do not see any difference in performance..Context
> > swithching is still 8k to 9k .
> >
> > please help..
> >
> >
> >
> >
>|||DPV's don't do a whole lot in most cases for performance if they are on the
same machine. If your query scans the whole table then partitioning it out
may help but better indexing would probably do more. When it comes to
processors faster is always better but the amount of cache may make quite a
bit of difference as well. A faster proc with more L2 or L3 cache can be a
lot faster than a slower one with less cache. If your queries are poorly
written then you are probably doing lots of scans and you may be using too
much parallelism still. Try setting the MAXDOP to 2 and see if that makes a
difference. Are the query plans the same as before? Have you run
sp_updatestats lately?
--
Andrew J. Kelly SQL MVP
"Nik" <sqlserverkb@.yahoo.com> wrote in message
news:%23ua%23CRRQFHA.3664@.TK2MSFTNGP15.phx.gbl...
> Thanks Andrew,
> I already made the MAXDOP to 4 . no improvement.
> Old System is 2 procs(2Ghz), 2Gb ram, HT on, but it hosts SQL, IIS,
> Reporting Services, Web Services
> New One is 4 proc (1.7Ghz),8GB ram, HT on but just hosts SQL Server and
> reports database used for reporting services.
> Do you think the CPU speed is making it much slower, which i do not think
> would be the case, I know the queries are badly written, and i mean bad.
> but still the same query runs much much say on old system it takes 17 sec
> to
> bring the report on new system it takes around 27 to 30 secs to bring the
> report up.
> Database setups are the same, we are using DVP but both the databases
> reside
> on the same server and a dvp is created on both the datbases.
> what elso do you think i should check for...
> any ideas are most welcome..
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OViGjBRQFHA.164@.TK2MSFTNGP12.phx.gbl...
>> Definitely turn off the fiber mode either way. An 4 proc system will
>> have
>> more context switching than a 2 proc in general, especially if they are
> HT.
>> You also want to make sure the MAXDOP at the server level is no more than
>> the # of physical processors. I would even try 2 and see how it goes. If
> the
>> new machine can process stuff faster or with more processors and your I/O
>> can't keep up or you have too little memory you can also get more context
>> switches. This is basically due to the fact it can't keep working while
>> it
>> is waiting on resources and will swap out for another thread. That
>> causes
>> more overhead and decreases overall throughput. But the bottom line is
> that
>> you can't directly compare two completely different hardware platforms as
>> apples to apples. You didn't mention what the old system was , how much
> ram,
>> speed of procs etc but it could be you need to adjust your configuration
> to
>> take advantage of the newer hardware.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Nik" <sqlserverkb@.yahoo.com> wrote in message
>> news:%23htrpLQQFHA.2584@.TK2MSFTNGP15.phx.gbl...
>> > Hi All
>> > We recently upgraded one of our application rinning on .net and sql
> server
>> > to New IBM Server.
>> > This is a Quad processor(1.7Ghz), with 8 gb of ram. but when we
>> > migrated
>> > we
>> > found that the system is much slower then the old system,
>> > This is a Win2K3, running SQL 2K with SP3a. , hyperthreading on.
>> > All my reporting queries are running slower. in fact the whole
> application
>> > is running slower.
>> > What i found out is, on old and new box the CPU util is just around
> 40%
>> > with full load. But on old box which is a dual processor with
>> > hyperthreading
>> > on, the contex swith/sec is 4000 but on new box it is about 9000/sec, I
>> > did
>> > turn on the Lightweight pooling so that SQL uses Fiber instead of
>> > thread
>> > mode, i have tried adjusting both the parallelism paramater, cost
>> > threshold
>> > and max degree.. but do not see any difference in performance..Context
>> > swithching is still 8k to 9k .
>> >
>> > please help..
>> >
>> >
>> >
>> >
>>
>|||Yes DVP perrformance is not affected as it is on the same box, and that was
the reason we had to bring on one box.
but The older box has 512k L3 on2.8 ghz and new one has 1Mb L3 on 1.5 ghz
still i do not understand the context switching.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O1r9EXSQFHA.688@.TK2MSFTNGP10.phx.gbl...
> DPV's don't do a whole lot in most cases for performance if they are on
the
> same machine. If your query scans the whole table then partitioning it
out
> may help but better indexing would probably do more. When it comes to
> processors faster is always better but the amount of cache may make quite
a
> bit of difference as well. A faster proc with more L2 or L3 cache can be
a
> lot faster than a slower one with less cache. If your queries are poorly
> written then you are probably doing lots of scans and you may be using too
> much parallelism still. Try setting the MAXDOP to 2 and see if that makes
a
> difference. Are the query plans the same as before? Have you run
> sp_updatestats lately?
> --
> Andrew J. Kelly SQL MVP
>
> "Nik" <sqlserverkb@.yahoo.com> wrote in message
> news:%23ua%23CRRQFHA.3664@.TK2MSFTNGP15.phx.gbl...
> > Thanks Andrew,
> > I already made the MAXDOP to 4 . no improvement.
> >
> > Old System is 2 procs(2Ghz), 2Gb ram, HT on, but it hosts SQL, IIS,
> > Reporting Services, Web Services
> > New One is 4 proc (1.7Ghz),8GB ram, HT on but just hosts SQL Server and
> > reports database used for reporting services.
> > Do you think the CPU speed is making it much slower, which i do not
think
> > would be the case, I know the queries are badly written, and i mean bad.
> > but still the same query runs much much say on old system it takes 17
sec
> > to
> > bring the report on new system it takes around 27 to 30 secs to bring
the
> > report up.
> > Database setups are the same, we are using DVP but both the databases
> > reside
> > on the same server and a dvp is created on both the datbases.
> >
> > what elso do you think i should check for...
> > any ideas are most welcome..
> >
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:OViGjBRQFHA.164@.TK2MSFTNGP12.phx.gbl...
> >> Definitely turn off the fiber mode either way. An 4 proc system will
> >> have
> >> more context switching than a 2 proc in general, especially if they are
> > HT.
> >> You also want to make sure the MAXDOP at the server level is no more
than
> >> the # of physical processors. I would even try 2 and see how it goes.
If
> > the
> >> new machine can process stuff faster or with more processors and your
I/O
> >> can't keep up or you have too little memory you can also get more
context
> >> switches. This is basically due to the fact it can't keep working while
> >> it
> >> is waiting on resources and will swap out for another thread. That
> >> causes
> >> more overhead and decreases overall throughput. But the bottom line is
> > that
> >> you can't directly compare two completely different hardware platforms
as
> >> apples to apples. You didn't mention what the old system was , how much
> > ram,
> >> speed of procs etc but it could be you need to adjust your
configuration
> > to
> >> take advantage of the newer hardware.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "Nik" <sqlserverkb@.yahoo.com> wrote in message
> >> news:%23htrpLQQFHA.2584@.TK2MSFTNGP15.phx.gbl...
> >> > Hi All
> >> > We recently upgraded one of our application rinning on .net and sql
> > server
> >> > to New IBM Server.
> >> > This is a Quad processor(1.7Ghz), with 8 gb of ram. but when we
> >> > migrated
> >> > we
> >> > found that the system is much slower then the old system,
> >> > This is a Win2K3, running SQL 2K with SP3a. , hyperthreading on.
> >> > All my reporting queries are running slower. in fact the whole
> > application
> >> > is running slower.
> >> > What i found out is, on old and new box the CPU util is just around
> > 40%
> >> > with full load. But on old box which is a dual processor with
> >> > hyperthreading
> >> > on, the contex swith/sec is 4000 but on new box it is about 9000/sec,
I
> >> > did
> >> > turn on the Lightweight pooling so that SQL uses Fiber instead of
> >> > thread
> >> > mode, i have tried adjusting both the parallelism paramater, cost
> >> > threshold
> >> > and max degree.. but do not see any difference in
performance..Context
> >> > swithching is still 8k to 9k .
> >> >
> >> > please help..
> >> >
> >> >
> >> >
> >> >
> >>
> >>
> >
> >
>|||What about disk system and where are located tempdb, data and transaction log.
AMB
"Nik" wrote:
> Thanks Andrew,
> I already made the MAXDOP to 4 . no improvement.
> Old System is 2 procs(2Ghz), 2Gb ram, HT on, but it hosts SQL, IIS,
> Reporting Services, Web Services
> New One is 4 proc (1.7Ghz),8GB ram, HT on but just hosts SQL Server and
> reports database used for reporting services.
> Do you think the CPU speed is making it much slower, which i do not think
> would be the case, I know the queries are badly written, and i mean bad.
> but still the same query runs much much say on old system it takes 17 sec to
> bring the report on new system it takes around 27 to 30 secs to bring the
> report up.
> Database setups are the same, we are using DVP but both the databases reside
> on the same server and a dvp is created on both the datbases.
> what elso do you think i should check for...
> any ideas are most welcome..
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OViGjBRQFHA.164@.TK2MSFTNGP12.phx.gbl...
> > Definitely turn off the fiber mode either way. An 4 proc system will have
> > more context switching than a 2 proc in general, especially if they are
> HT.
> > You also want to make sure the MAXDOP at the server level is no more than
> > the # of physical processors. I would even try 2 and see how it goes. If
> the
> > new machine can process stuff faster or with more processors and your I/O
> > can't keep up or you have too little memory you can also get more context
> > switches. This is basically due to the fact it can't keep working while it
> > is waiting on resources and will swap out for another thread. That causes
> > more overhead and decreases overall throughput. But the bottom line is
> that
> > you can't directly compare two completely different hardware platforms as
> > apples to apples. You didn't mention what the old system was , how much
> ram,
> > speed of procs etc but it could be you need to adjust your configuration
> to
> > take advantage of the newer hardware.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Nik" <sqlserverkb@.yahoo.com> wrote in message
> > news:%23htrpLQQFHA.2584@.TK2MSFTNGP15.phx.gbl...
> > > Hi All
> > > We recently upgraded one of our application rinning on .net and sql
> server
> > > to New IBM Server.
> > > This is a Quad processor(1.7Ghz), with 8 gb of ram. but when we migrated
> > > we
> > > found that the system is much slower then the old system,
> > > This is a Win2K3, running SQL 2K with SP3a. , hyperthreading on.
> > > All my reporting queries are running slower. in fact the whole
> application
> > > is running slower.
> > > What i found out is, on old and new box the CPU util is just around
> 40%
> > > with full load. But on old box which is a dual processor with
> > > hyperthreading
> > > on, the contex swith/sec is 4000 but on new box it is about 9000/sec, I
> > > did
> > > turn on the Lightweight pooling so that SQL uses Fiber instead of thread
> > > mode, i have tried adjusting both the parallelism paramater, cost
> > > threshold
> > > and max degree.. but do not see any difference in performance..Context
> > > swithching is still 8k to 9k .
> > >
> > > please help..
> > >
> > >
> > >
> > >
> >
> >
>
>|||But the older box was almost twice as fast processor speed wise. That is a
big difference. How could you even get a new box with just 1.5GHZ proc's?
What about the disk system? Is that the same or is that different as well.
--
Andrew J. Kelly SQL MVP
"Nik" <sqlserverkb@.yahoo.com> wrote in message
news:%23jvSu6SQFHA.1500@.TK2MSFTNGP09.phx.gbl...
> Yes DVP perrformance is not affected as it is on the same box, and that
> was
> the reason we had to bring on one box.
> but The older box has 512k L3 on2.8 ghz and new one has 1Mb L3 on 1.5 ghz
> still i do not understand the context switching.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O1r9EXSQFHA.688@.TK2MSFTNGP10.phx.gbl...
>> DPV's don't do a whole lot in most cases for performance if they are on
> the
>> same machine. If your query scans the whole table then partitioning it
> out
>> may help but better indexing would probably do more. When it comes to
>> processors faster is always better but the amount of cache may make quite
> a
>> bit of difference as well. A faster proc with more L2 or L3 cache can be
> a
>> lot faster than a slower one with less cache. If your queries are poorly
>> written then you are probably doing lots of scans and you may be using
>> too
>> much parallelism still. Try setting the MAXDOP to 2 and see if that
>> makes
> a
>> difference. Are the query plans the same as before? Have you run
>> sp_updatestats lately?
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Nik" <sqlserverkb@.yahoo.com> wrote in message
>> news:%23ua%23CRRQFHA.3664@.TK2MSFTNGP15.phx.gbl...
>> > Thanks Andrew,
>> > I already made the MAXDOP to 4 . no improvement.
>> >
>> > Old System is 2 procs(2Ghz), 2Gb ram, HT on, but it hosts SQL, IIS,
>> > Reporting Services, Web Services
>> > New One is 4 proc (1.7Ghz),8GB ram, HT on but just hosts SQL Server
>> > and
>> > reports database used for reporting services.
>> > Do you think the CPU speed is making it much slower, which i do not
> think
>> > would be the case, I know the queries are badly written, and i mean
>> > bad.
>> > but still the same query runs much much say on old system it takes 17
> sec
>> > to
>> > bring the report on new system it takes around 27 to 30 secs to bring
> the
>> > report up.
>> > Database setups are the same, we are using DVP but both the databases
>> > reside
>> > on the same server and a dvp is created on both the datbases.
>> >
>> > what elso do you think i should check for...
>> > any ideas are most welcome..
>> >
>> >
>> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> > news:OViGjBRQFHA.164@.TK2MSFTNGP12.phx.gbl...
>> >> Definitely turn off the fiber mode either way. An 4 proc system will
>> >> have
>> >> more context switching than a 2 proc in general, especially if they
>> >> are
>> > HT.
>> >> You also want to make sure the MAXDOP at the server level is no more
> than
>> >> the # of physical processors. I would even try 2 and see how it goes.
> If
>> > the
>> >> new machine can process stuff faster or with more processors and your
> I/O
>> >> can't keep up or you have too little memory you can also get more
> context
>> >> switches. This is basically due to the fact it can't keep working
>> >> while
>> >> it
>> >> is waiting on resources and will swap out for another thread. That
>> >> causes
>> >> more overhead and decreases overall throughput. But the bottom line
>> >> is
>> > that
>> >> you can't directly compare two completely different hardware platforms
> as
>> >> apples to apples. You didn't mention what the old system was , how
>> >> much
>> > ram,
>> >> speed of procs etc but it could be you need to adjust your
> configuration
>> > to
>> >> take advantage of the newer hardware.
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >>
>> >>
>> >> "Nik" <sqlserverkb@.yahoo.com> wrote in message
>> >> news:%23htrpLQQFHA.2584@.TK2MSFTNGP15.phx.gbl...
>> >> > Hi All
>> >> > We recently upgraded one of our application rinning on .net and sql
>> > server
>> >> > to New IBM Server.
>> >> > This is a Quad processor(1.7Ghz), with 8 gb of ram. but when we
>> >> > migrated
>> >> > we
>> >> > found that the system is much slower then the old system,
>> >> > This is a Win2K3, running SQL 2K with SP3a. , hyperthreading on.
>> >> > All my reporting queries are running slower. in fact the whole
>> > application
>> >> > is running slower.
>> >> > What i found out is, on old and new box the CPU util is just
>> >> > around
>> > 40%
>> >> > with full load. But on old box which is a dual processor with
>> >> > hyperthreading
>> >> > on, the contex swith/sec is 4000 but on new box it is about
>> >> > 9000/sec,
> I
>> >> > did
>> >> > turn on the Lightweight pooling so that SQL uses Fiber instead of
>> >> > thread
>> >> > mode, i have tried adjusting both the parallelism paramater, cost
>> >> > threshold
>> >> > and max degree.. but do not see any difference in
> performance..Context
>> >> > swithching is still 8k to 9k .
>> >> >
>> >> > please help..
>> >> >
>> >> >
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>|||AFAIK, you can't event get Xeons/Pentium that slow anymore. Maybe IA64?
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23GFEvOWQFHA.3336@.TK2MSFTNGP09.phx.gbl...
> But the older box was almost twice as fast processor speed wise. That is
> a big difference. How could you even get a new box with just 1.5GHZ
> proc's? What about the disk system? Is that the same or is that different
> as well.
> --
> Andrew J. Kelly SQL MVP
>
> "Nik" <sqlserverkb@.yahoo.com> wrote in message
> news:%23jvSu6SQFHA.1500@.TK2MSFTNGP09.phx.gbl...
>> Yes DVP perrformance is not affected as it is on the same box, and that
>> was
>> the reason we had to bring on one box.
>> but The older box has 512k L3 on2.8 ghz and new one has 1Mb L3 on 1.5 ghz
>> still i do not understand the context switching.
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:O1r9EXSQFHA.688@.TK2MSFTNGP10.phx.gbl...
>> DPV's don't do a whole lot in most cases for performance if they are on
>> the
>> same machine. If your query scans the whole table then partitioning it
>> out
>> may help but better indexing would probably do more. When it comes to
>> processors faster is always better but the amount of cache may make
>> quite
>> a
>> bit of difference as well. A faster proc with more L2 or L3 cache can
>> be
>> a
>> lot faster than a slower one with less cache. If your queries are
>> poorly
>> written then you are probably doing lots of scans and you may be using
>> too
>> much parallelism still. Try setting the MAXDOP to 2 and see if that
>> makes
>> a
>> difference. Are the query plans the same as before? Have you run
>> sp_updatestats lately?
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Nik" <sqlserverkb@.yahoo.com> wrote in message
>> news:%23ua%23CRRQFHA.3664@.TK2MSFTNGP15.phx.gbl...
>> > Thanks Andrew,
>> > I already made the MAXDOP to 4 . no improvement.
>> >
>> > Old System is 2 procs(2Ghz), 2Gb ram, HT on, but it hosts SQL, IIS,
>> > Reporting Services, Web Services
>> > New One is 4 proc (1.7Ghz),8GB ram, HT on but just hosts SQL Server
>> > and
>> > reports database used for reporting services.
>> > Do you think the CPU speed is making it much slower, which i do not
>> think
>> > would be the case, I know the queries are badly written, and i mean
>> > bad.
>> > but still the same query runs much much say on old system it takes 17
>> sec
>> > to
>> > bring the report on new system it takes around 27 to 30 secs to bring
>> the
>> > report up.
>> > Database setups are the same, we are using DVP but both the databases
>> > reside
>> > on the same server and a dvp is created on both the datbases.
>> >
>> > what elso do you think i should check for...
>> > any ideas are most welcome..
>> >
>> >
>> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> > news:OViGjBRQFHA.164@.TK2MSFTNGP12.phx.gbl...
>> >> Definitely turn off the fiber mode either way. An 4 proc system will
>> >> have
>> >> more context switching than a 2 proc in general, especially if they
>> >> are
>> > HT.
>> >> You also want to make sure the MAXDOP at the server level is no more
>> than
>> >> the # of physical processors. I would even try 2 and see how it goes.
>> If
>> > the
>> >> new machine can process stuff faster or with more processors and your
>> I/O
>> >> can't keep up or you have too little memory you can also get more
>> context
>> >> switches. This is basically due to the fact it can't keep working
>> >> while
>> >> it
>> >> is waiting on resources and will swap out for another thread. That
>> >> causes
>> >> more overhead and decreases overall throughput. But the bottom line
>> >> is
>> > that
>> >> you can't directly compare two completely different hardware
>> >> platforms
>> as
>> >> apples to apples. You didn't mention what the old system was , how
>> >> much
>> > ram,
>> >> speed of procs etc but it could be you need to adjust your
>> configuration
>> > to
>> >> take advantage of the newer hardware.
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >>
>> >>
>> >> "Nik" <sqlserverkb@.yahoo.com> wrote in message
>> >> news:%23htrpLQQFHA.2584@.TK2MSFTNGP15.phx.gbl...
>> >> > Hi All
>> >> > We recently upgraded one of our application rinning on .net and sql
>> > server
>> >> > to New IBM Server.
>> >> > This is a Quad processor(1.7Ghz), with 8 gb of ram. but when we
>> >> > migrated
>> >> > we
>> >> > found that the system is much slower then the old system,
>> >> > This is a Win2K3, running SQL 2K with SP3a. , hyperthreading on.
>> >> > All my reporting queries are running slower. in fact the whole
>> > application
>> >> > is running slower.
>> >> > What i found out is, on old and new box the CPU util is just
>> >> > around
>> > 40%
>> >> > with full load. But on old box which is a dual processor with
>> >> > hyperthreading
>> >> > on, the contex swith/sec is 4000 but on new box it is about
>> >> > 9000/sec,
>> I
>> >> > did
>> >> > turn on the Lightweight pooling so that SQL uses Fiber instead of
>> >> > thread
>> >> > mode, i have tried adjusting both the parallelism paramater, cost
>> >> > threshold
>> >> > and max degree.. but do not see any difference in
>> performance..Context
>> >> > swithching is still 8k to 9k .
>> >> >
>> >> > please help..
>> >> >
>> >> >
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>|||i know but this was a management decision, without the geting dba's opinion.
I know they had screwed up in that part, but do you guys think that CPU
speed is contributing to the slowness of the entire system,
the I/O subsystem is faster on the new box.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:OltsgBcQFHA.3544@.TK2MSFTNGP12.phx.gbl...
> AFAIK, you can't event get Xeons/Pentium that slow anymore. Maybe IA64?
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23GFEvOWQFHA.3336@.TK2MSFTNGP09.phx.gbl...
> > But the older box was almost twice as fast processor speed wise. That
is
> > a big difference. How could you even get a new box with just 1.5GHZ
> > proc's? What about the disk system? Is that the same or is that
different
> > as well.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Nik" <sqlserverkb@.yahoo.com> wrote in message
> > news:%23jvSu6SQFHA.1500@.TK2MSFTNGP09.phx.gbl...
> >> Yes DVP perrformance is not affected as it is on the same box, and that
> >> was
> >> the reason we had to bring on one box.
> >>
> >> but The older box has 512k L3 on2.8 ghz and new one has 1Mb L3 on 1.5
ghz
> >>
> >> still i do not understand the context switching.
> >>
> >>
> >> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> >> news:O1r9EXSQFHA.688@.TK2MSFTNGP10.phx.gbl...
> >> DPV's don't do a whole lot in most cases for performance if they are
on
> >> the
> >> same machine. If your query scans the whole table then partitioning
it
> >> out
> >> may help but better indexing would probably do more. When it comes to
> >> processors faster is always better but the amount of cache may make
> >> quite
> >> a
> >> bit of difference as well. A faster proc with more L2 or L3 cache can
> >> be
> >> a
> >> lot faster than a slower one with less cache. If your queries are
> >> poorly
> >> written then you are probably doing lots of scans and you may be using
> >> too
> >> much parallelism still. Try setting the MAXDOP to 2 and see if that
> >> makes
> >> a
> >> difference. Are the query plans the same as before? Have you run
> >> sp_updatestats lately?
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "Nik" <sqlserverkb@.yahoo.com> wrote in message
> >> news:%23ua%23CRRQFHA.3664@.TK2MSFTNGP15.phx.gbl...
> >> > Thanks Andrew,
> >> > I already made the MAXDOP to 4 . no improvement.
> >> >
> >> > Old System is 2 procs(2Ghz), 2Gb ram, HT on, but it hosts SQL, IIS,
> >> > Reporting Services, Web Services
> >> > New One is 4 proc (1.7Ghz),8GB ram, HT on but just hosts SQL Server
> >> > and
> >> > reports database used for reporting services.
> >> > Do you think the CPU speed is making it much slower, which i do not
> >> think
> >> > would be the case, I know the queries are badly written, and i mean
> >> > bad.
> >> > but still the same query runs much much say on old system it takes
17
> >> sec
> >> > to
> >> > bring the report on new system it takes around 27 to 30 secs to
bring
> >> the
> >> > report up.
> >> > Database setups are the same, we are using DVP but both the
databases
> >> > reside
> >> > on the same server and a dvp is created on both the datbases.
> >> >
> >> > what elso do you think i should check for...
> >> > any ideas are most welcome..
> >> >
> >> >
> >> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> >> > news:OViGjBRQFHA.164@.TK2MSFTNGP12.phx.gbl...
> >> >> Definitely turn off the fiber mode either way. An 4 proc system
will
> >> >> have
> >> >> more context switching than a 2 proc in general, especially if they
> >> >> are
> >> > HT.
> >> >> You also want to make sure the MAXDOP at the server level is no
more
> >> than
> >> >> the # of physical processors. I would even try 2 and see how it
goes.
> >> If
> >> > the
> >> >> new machine can process stuff faster or with more processors and
your
> >> I/O
> >> >> can't keep up or you have too little memory you can also get more
> >> context
> >> >> switches. This is basically due to the fact it can't keep working
> >> >> while
> >> >> it
> >> >> is waiting on resources and will swap out for another thread. That
> >> >> causes
> >> >> more overhead and decreases overall throughput. But the bottom
line
> >> >> is
> >> > that
> >> >> you can't directly compare two completely different hardware
> >> >> platforms
> >> as
> >> >> apples to apples. You didn't mention what the old system was , how
> >> >> much
> >> > ram,
> >> >> speed of procs etc but it could be you need to adjust your
> >> configuration
> >> > to
> >> >> take advantage of the newer hardware.
> >> >>
> >> >> --
> >> >> Andrew J. Kelly SQL MVP
> >> >>
> >> >>
> >> >> "Nik" <sqlserverkb@.yahoo.com> wrote in message
> >> >> news:%23htrpLQQFHA.2584@.TK2MSFTNGP15.phx.gbl...
> >> >> > Hi All
> >> >> > We recently upgraded one of our application rinning on .net and
sql
> >> > server
> >> >> > to New IBM Server.
> >> >> > This is a Quad processor(1.7Ghz), with 8 gb of ram. but when we
> >> >> > migrated
> >> >> > we
> >> >> > found that the system is much slower then the old system,
> >> >> > This is a Win2K3, running SQL 2K with SP3a. , hyperthreading on.
> >> >> > All my reporting queries are running slower. in fact the whole
> >> > application
> >> >> > is running slower.
> >> >> > What i found out is, on old and new box the CPU util is just
> >> >> > around
> >> > 40%
> >> >> > with full load. But on old box which is a dual processor with
> >> >> > hyperthreading
> >> >> > on, the contex swith/sec is 4000 but on new box it is about
> >> >> > 9000/sec,
> >> I
> >> >> > did
> >> >> > turn on the Lightweight pooling so that SQL uses Fiber instead of
> >> >> > thread
> >> >> > mode, i have tried adjusting both the parallelism paramater, cost
> >> >> > threshold
> >> >> > and max degree.. but do not see any difference in
> >> performance..Context
> >> >> > swithching is still 8k to 9k .
> >> >> >
> >> >> > please help..
> >> >> >
> >> >> >
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >>
> >>
> >
> >
>|||TempDb is on a raid 10,same with data and tran logs. they all are on
different drives..with individual controller
Tempdb is scattered on 4 data files..
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:F9CE53C8-8563-41F9-8A4A-9818B9C5C0C0@.microsoft.com...
> What about disk system and where are located tempdb, data and transaction
log.
>
> AMB
> "Nik" wrote:
> > Thanks Andrew,
> > I already made the MAXDOP to 4 . no improvement.
> >
> > Old System is 2 procs(2Ghz), 2Gb ram, HT on, but it hosts SQL, IIS,
> > Reporting Services, Web Services
> > New One is 4 proc (1.7Ghz),8GB ram, HT on but just hosts SQL Server and
> > reports database used for reporting services.
> > Do you think the CPU speed is making it much slower, which i do not
think
> > would be the case, I know the queries are badly written, and i mean bad.
> > but still the same query runs much much say on old system it takes 17
sec to
> > bring the report on new system it takes around 27 to 30 secs to bring
the
> > report up.
> > Database setups are the same, we are using DVP but both the databases
reside
> > on the same server and a dvp is created on both the datbases.
> >
> > what elso do you think i should check for...
> > any ideas are most welcome..
> >
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:OViGjBRQFHA.164@.TK2MSFTNGP12.phx.gbl...
> > > Definitely turn off the fiber mode either way. An 4 proc system will
have
> > > more context switching than a 2 proc in general, especially if they
are
> > HT.
> > > You also want to make sure the MAXDOP at the server level is no more
than
> > > the # of physical processors. I would even try 2 and see how it goes.
If
> > the
> > > new machine can process stuff faster or with more processors and your
I/O
> > > can't keep up or you have too little memory you can also get more
context
> > > switches. This is basically due to the fact it can't keep working
while it
> > > is waiting on resources and will swap out for another thread. That
causes
> > > more overhead and decreases overall throughput. But the bottom line
is
> > that
> > > you can't directly compare two completely different hardware platforms
as
> > > apples to apples. You didn't mention what the old system was , how
much
> > ram,
> > > speed of procs etc but it could be you need to adjust your
configuration
> > to
> > > take advantage of the newer hardware.
> > >
> > > --
> > > Andrew J. Kelly SQL MVP
> > >
> > >
> > > "Nik" <sqlserverkb@.yahoo.com> wrote in message
> > > news:%23htrpLQQFHA.2584@.TK2MSFTNGP15.phx.gbl...
> > > > Hi All
> > > > We recently upgraded one of our application rinning on .net and sql
> > server
> > > > to New IBM Server.
> > > > This is a Quad processor(1.7Ghz), with 8 gb of ram. but when we
migrated
> > > > we
> > > > found that the system is much slower then the old system,
> > > > This is a Win2K3, running SQL 2K with SP3a. , hyperthreading on.
> > > > All my reporting queries are running slower. in fact the whole
> > application
> > > > is running slower.
> > > > What i found out is, on old and new box the CPU util is just
around
> > 40%
> > > > with full load. But on old box which is a dual processor with
> > > > hyperthreading
> > > > on, the contex swith/sec is 4000 but on new box it is about
9000/sec, I
> > > > did
> > > > turn on the Lightweight pooling so that SQL uses Fiber instead of
thread
> > > > mode, i have tried adjusting both the parallelism paramater, cost
> > > > threshold
> > > > and max degree.. but do not see any difference in
performance..Context
> > > > swithching is still 8k to 9k .
> > > >
> > > > please help..
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
> >|||It's really hard to say how much of it is due to CPU speed alone without
actually being there and taking into account everything. But going to a
system with processors that are almost half as slow as before is certainly
not a step up in my opinion.
--
Andrew J. Kelly SQL MVP
"Nik" <sqlserverkb@.yahoo.com> wrote in message
news:e%23do4ncQFHA.3496@.TK2MSFTNGP09.phx.gbl...
>i know but this was a management decision, without the geting dba's
>opinion.
> I know they had screwed up in that part, but do you guys think that CPU
> speed is contributing to the slowness of the entire system,
> the I/O subsystem is faster on the new box.
>
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:OltsgBcQFHA.3544@.TK2MSFTNGP12.phx.gbl...
>> AFAIK, you can't event get Xeons/Pentium that slow anymore. Maybe IA64?
>> Regards
>> --
>> Mike Epprecht, Microsoft SQL Server MVP
>> Zurich, Switzerland
>> IM: mike@.epprecht.net
>> MVP Program: http://www.microsoft.com/mvp
>> Blog: http://www.msmvps.com/epprecht/
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:%23GFEvOWQFHA.3336@.TK2MSFTNGP09.phx.gbl...
>> > But the older box was almost twice as fast processor speed wise. That
> is
>> > a big difference. How could you even get a new box with just 1.5GHZ
>> > proc's? What about the disk system? Is that the same or is that
> different
>> > as well.
>> >
>> > --
>> > Andrew J. Kelly SQL MVP
>> >
>> >
>> > "Nik" <sqlserverkb@.yahoo.com> wrote in message
>> > news:%23jvSu6SQFHA.1500@.TK2MSFTNGP09.phx.gbl...
>> >> Yes DVP perrformance is not affected as it is on the same box, and
>> >> that
>> >> was
>> >> the reason we had to bring on one box.
>> >>
>> >> but The older box has 512k L3 on2.8 ghz and new one has 1Mb L3 on 1.5
> ghz
>> >>
>> >> still i do not understand the context switching.
>> >>
>> >>
>> >> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> >> news:O1r9EXSQFHA.688@.TK2MSFTNGP10.phx.gbl...
>> >> DPV's don't do a whole lot in most cases for performance if they are
> on
>> >> the
>> >> same machine. If your query scans the whole table then partitioning
> it
>> >> out
>> >> may help but better indexing would probably do more. When it comes
>> >> to
>> >> processors faster is always better but the amount of cache may make
>> >> quite
>> >> a
>> >> bit of difference as well. A faster proc with more L2 or L3 cache
>> >> can
>> >> be
>> >> a
>> >> lot faster than a slower one with less cache. If your queries are
>> >> poorly
>> >> written then you are probably doing lots of scans and you may be
>> >> using
>> >> too
>> >> much parallelism still. Try setting the MAXDOP to 2 and see if that
>> >> makes
>> >> a
>> >> difference. Are the query plans the same as before? Have you run
>> >> sp_updatestats lately?
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >>
>> >>
>> >> "Nik" <sqlserverkb@.yahoo.com> wrote in message
>> >> news:%23ua%23CRRQFHA.3664@.TK2MSFTNGP15.phx.gbl...
>> >> > Thanks Andrew,
>> >> > I already made the MAXDOP to 4 . no improvement.
>> >> >
>> >> > Old System is 2 procs(2Ghz), 2Gb ram, HT on, but it hosts SQL, IIS,
>> >> > Reporting Services, Web Services
>> >> > New One is 4 proc (1.7Ghz),8GB ram, HT on but just hosts SQL
>> >> > Server
>> >> > and
>> >> > reports database used for reporting services.
>> >> > Do you think the CPU speed is making it much slower, which i do not
>> >> think
>> >> > would be the case, I know the queries are badly written, and i mean
>> >> > bad.
>> >> > but still the same query runs much much say on old system it takes
> 17
>> >> sec
>> >> > to
>> >> > bring the report on new system it takes around 27 to 30 secs to
> bring
>> >> the
>> >> > report up.
>> >> > Database setups are the same, we are using DVP but both the
> databases
>> >> > reside
>> >> > on the same server and a dvp is created on both the datbases.
>> >> >
>> >> > what elso do you think i should check for...
>> >> > any ideas are most welcome..
>> >> >
>> >> >
>> >> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> >> > news:OViGjBRQFHA.164@.TK2MSFTNGP12.phx.gbl...
>> >> >> Definitely turn off the fiber mode either way. An 4 proc system
> will
>> >> >> have
>> >> >> more context switching than a 2 proc in general, especially if
>> >> >> they
>> >> >> are
>> >> > HT.
>> >> >> You also want to make sure the MAXDOP at the server level is no
> more
>> >> than
>> >> >> the # of physical processors. I would even try 2 and see how it
> goes.
>> >> If
>> >> > the
>> >> >> new machine can process stuff faster or with more processors and
> your
>> >> I/O
>> >> >> can't keep up or you have too little memory you can also get more
>> >> context
>> >> >> switches. This is basically due to the fact it can't keep working
>> >> >> while
>> >> >> it
>> >> >> is waiting on resources and will swap out for another thread.
>> >> >> That
>> >> >> causes
>> >> >> more overhead and decreases overall throughput. But the bottom
> line
>> >> >> is
>> >> > that
>> >> >> you can't directly compare two completely different hardware
>> >> >> platforms
>> >> as
>> >> >> apples to apples. You didn't mention what the old system was , how
>> >> >> much
>> >> > ram,
>> >> >> speed of procs etc but it could be you need to adjust your
>> >> configuration
>> >> > to
>> >> >> take advantage of the newer hardware.
>> >> >>
>> >> >> --
>> >> >> Andrew J. Kelly SQL MVP
>> >> >>
>> >> >>
>> >> >> "Nik" <sqlserverkb@.yahoo.com> wrote in message
>> >> >> news:%23htrpLQQFHA.2584@.TK2MSFTNGP15.phx.gbl...
>> >> >> > Hi All
>> >> >> > We recently upgraded one of our application rinning on .net and
> sql
>> >> > server
>> >> >> > to New IBM Server.
>> >> >> > This is a Quad processor(1.7Ghz), with 8 gb of ram. but when we
>> >> >> > migrated
>> >> >> > we
>> >> >> > found that the system is much slower then the old system,
>> >> >> > This is a Win2K3, running SQL 2K with SP3a. , hyperthreading on.
>> >> >> > All my reporting queries are running slower. in fact the whole
>> >> > application
>> >> >> > is running slower.
>> >> >> > What i found out is, on old and new box the CPU util is just
>> >> >> > around
>> >> > 40%
>> >> >> > with full load. But on old box which is a dual processor with
>> >> >> > hyperthreading
>> >> >> > on, the contex swith/sec is 4000 but on new box it is about
>> >> >> > 9000/sec,
>> >> I
>> >> >> > did
>> >> >> > turn on the Lightweight pooling so that SQL uses Fiber instead
>> >> >> > of
>> >> >> > thread
>> >> >> > mode, i have tried adjusting both the parallelism paramater,
>> >> >> > cost
>> >> >> > threshold
>> >> >> > and max degree.. but do not see any difference in
>> >> performance..Context
>> >> >> > swithching is still 8k to 9k .
>> >> >> >
>> >> >> > please help..
>> >> >> >
>> >> >> >
>> >> >> >
>> >> >> >
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >>
>> >>
>> >
>> >
>>
>