MS SQL Server vs MySQL question..

I use MS profiler to run traces to diagnose issues/problems with any MS SQL Server database. Is there an equivalent tool when using MySQL database? What about Enterprise Manger and Query Analyzer? Is it fair to assume that the administrative tools available for MySQL will do the job that MS tools do for MS SQL Server with comparable ease?

Thanks.I don't know of any Profiler-like applications for MySQL but I use SQLyog to administer my MySQL databases (http://www.webyog.com).

In my opinion comparing MySQL to MS SQL Server is comparing a bycicle with a Mercedes. They both get you from A to B but that's the only fair comparison. MySQL is a nice database for small non-critical applications but it can't do halve the things that SQL Server can. So tools for MySQL won't do the same as the ones for SQL Server.

Lex

PS. Again, this is my own opinion on the matter. So feel free to contradict :D|||small non-critical applications? whoa

have a look at some of the mysql customers (http://www.mysql.com/customers/), my friend, and you will see some fairly big, critical names

craigslist, slashdot, technorati are small, non-critical?

ticketmaster is small, non-critical?

you've been buying the "open source can't be any good" comic books, haven't you

:)|||In terms of industry support, developer and dba tools, and more importantly vendor support, I think Microsoft SQL Server is the best bang for the buck available. The features available (many of them "out of the box" such as SQL Profiler, Enterprise Manager, Analysis Services, and Reporting Services) are more readily available and well understood than comparable tools for any product currently available, at any price.

This doesn't mean that MySQL is a bad tool, and it emphatically does not mean that it isn't suitable for doing serious work. It does however mean that you need to find support from someone other than the vendor (MySQL AB), which gives you a very low cost of entry, but often a HUGE ongoing cost for the people you need to support the product. For large organizations that need people with "heavy firepower" to support their applications like the ones that R937 pointed out, the cost is irrelevant. They need to have folks that can handle the support on staff 24 by 365 anyway, so they have no additional cost of ownership.

For most of us, the cost of the people needed to support the tools would exceed what the company makes. Many folks choose to live without the support, and hope that they never need it. That has proven to be a fatal assumption for a number of small (under $10 million per year gross revenue) businesses every year.

Microsoft SQL Server isn't a "magic bullet". It won't solve every problem. I don't always recommend it, but for more than 80% of the business use that I see, MS-SQL is the best database tool for the job in my opinion.

-PatP|||Alright, alright, I take back some of the words I said about MySQL. It was after one o'clock at night when I wrote that post and I was feeling rebellious. I overstated the comparison and my humble opinion actually comes close to that of Pat's.

I'm still not fully over the first impression I got when, years back, I learned of MySQL and did some research. I looked up the support for referential integrety and was horrified to read that, not only was that support at the time non-existing but it was stated that it was cumbersome and troublesome and you wouldn't need it.

I tossed it aside and didn't look at it for a few years. When I re-researched it a few years later the support was added and I find the product most agreeable. But aside from the features it lacks in comparison to SQL Server (and Oracle and DB2 and Sybase and ...) I think the most crucial thing is the absence of a big vendor who is and feels responsible for the product. And that's what you need when your core business depends on it!

For the record: I think I'm not biased when it comes to open source or even databases. Oracle is as good a database as Sybase or SQL Server. They all have their weaknesses and strong points (When's MS gonna give me function based idexes and a sequence generator!? ;)). MySQL has it's strongpoints and I will keep using it for the projects I think it is most suitable for.

Lex

PS. is that comic on the same shelve as the "Big commercial companies are all money grabbin' crooks thus open source is the solution for everything" comics? :D

MS SQL Server Profiler Reads

Hello,
following questions. I have two server systems. The configuration for both systems are exact the same. I create an empty database on both server systems. Next i start the query analyzer and start the following query :
USE TEST
DECLARE @.akt decima
SET NOCOUNT O
SET STATISTICS IO ON
create table dbo.tes
zahl decima
SET @.akt=
WHILE NOT @.akt=100
BEGI
SET @.akt=@.akt+
insert into dbo.test VALUES (@.akt
EN
SET NOCOUNT OF
SET STATISTICS IO OF
G
On the first system that query runs 1 sec on the other system the query runs 4 se
When i show the values of sysprocesses, all physical_IO, CPU .. are near the same. When i use the MS SQL Profiler, the query need on the first system 1000 Reads and 1000 ms on the other system it takes 6000 reads and 4000 ms. Why are the performance values so different ? What does reads exactly mean in MS SQL Profiler ? When i perform a lot of IO Trafffic (with Backup and and and ... the reads increase to 25000 and takes 118000 in Duration.
How can i increase the performance with the same TSQL Script (see above)
On the temp db the query above has the same performance on both systems ? Why ?Please see KB ID 314648 INF: Differences in STATISTICS IO, SQL Profiler and
Sysprocesses.
As for optimizing the queries we need to collect Executions Plans ,
STATISTICS PROFILE and performance monitor from both servers to have a
better understanding of the differences in the query execution and
bottlenecks.
Please also see
243589. INF: Troubleshooting Slow-running Queries
243588. INF: Troubleshooting Performance of Ad-Hoc Queries
243589 INF: Troubleshooting Slow-running Queries
HTH
Agnes Panosian
SQL Server Support|||Can i sent it via eMail to you ? (I have Performance Counters Profler Informations and the STATISTICS from sysprocesses|||The profiler reads counter will not show exactly the same value as what you
would get from statistics io for a query because it will also accumulate the
reads profiler itself did. The best way to find actual reads for a query, in
my opinion, is to rely on stats io output alone.
To answer the 1 sec to 4 sec difference, I can only assume that this is
because of the underlying h/w. It is best to turn off tracing(profiling),
then run the queries on both the systems and re-evaluate because profiler
has some overhead.
"Dataworld" <anonymous@.discussions.microsoft.com> wrote in message
news:445C02E4-92AC-40C6-9F3D-6F2171D260E0@.microsoft.com...
> Hello,
> following questions. I have two server systems. The configuration for both
> systems are exact the same. I create an empty database on both server
> systems. Next i start the query analyzer and start the following query :
> USE TESTX
> DECLARE @.akt decimal
> SET NOCOUNT ON
> SET STATISTICS IO ON
> create table dbo.test
> (
> zahl decimal
> )
> SET @.akt=0
> WHILE NOT @.akt=1000
> BEGIN
> SET @.akt=@.akt+1
> insert into dbo.test VALUES (@.akt)
> END
> SET NOCOUNT OFF
> SET STATISTICS IO OFF
> GO
> On the first system that query runs 1 sec on the other system the query
> runs 4 sec
> When i show the values of sysprocesses, all physical_IO, CPU .. are near
> the same. When i use the MS SQL Profiler, the query need on the first
> system 1000 Reads and 1000 ms on the other system it takes 6000 reads and
> 4000 ms. Why are the performance values so different ? What does reads
> exactly mean in MS SQL Profiler ? When i perform a lot of IO Trafffic
> (with Backup and and and ... the reads increase to 25000 and takes
> 118000 in Duration.
> How can i increase the performance with the same TSQL Script (see above) ?
> On the temp db the query above has the same performance on both systems ?
> Why ?|||You might want to take a look with sysinternal's filemon, and see how many
reads (and how many blocks at the time) are done at the OS level, sometimes
the readsizes (clusters of 8k blocks) can differ, and thus I/O access times.
Also take a look at sqlperf(waitstats) and see where you loose time..(make
sure you reset the stats before you run your tests)
Or use my tools to monitor what is really happening, downloadable for build
760. Build 800 on request..
--
regards,
Mario
http://www.sqlinternals.com
"Dataworld" <anonymous@.discussions.microsoft.com> wrote in message
news:445C02E4-92AC-40C6-9F3D-6F2171D260E0@.microsoft.com...
> Hello,
> following questions. I have two server systems. The configuration for both
systems are exact the same. I create an empty database on both server
systems. Next i start the query analyzer and start the following query :
> USE TESTX
> DECLARE @.akt decimal
> SET NOCOUNT ON
> SET STATISTICS IO ON
> create table dbo.test
> (
> zahl decimal
> )
> SET @.akt=0
> WHILE NOT @.akt=1000
> BEGIN
> SET @.akt=@.akt+1
> insert into dbo.test VALUES (@.akt)
> END
> SET NOCOUNT OFF
> SET STATISTICS IO OFF
> GO
> On the first system that query runs 1 sec on the other system the query
runs 4 sec
> When i show the values of sysprocesses, all physical_IO, CPU .. are near
the same. When i use the MS SQL Profiler, the query need on the first system
1000 Reads and 1000 ms on the other system it takes 6000 reads and 4000 ms.
Why are the performance values so different ? What does reads exactly mean
in MS SQL Profiler ? When i perform a lot of IO Trafffic (with Backup and
and and ... the reads increase to 25000 and takes 118000 in Duration.
> How can i increase the performance with the same TSQL Script (see above) ?
> On the temp db the query above has the same performance on both systems ?
Why ?

MS SQL Server Profiler Reads

Hello,
following questions. I have two server systems. The configuration for both s
ystems are exact the same. I create an empty database on both server systems
. Next i start the query analyzer and start the following query :
USE TESTX
DECLARE @.akt decimal
SET NOCOUNT ON
SET STATISTICS IO ON
create table dbo.test
(
zahl decimal
)
SET @.akt=0
WHILE NOT @.akt=1000
BEGIN
SET @.akt=@.akt+1
insert into dbo.test VALUES (@.akt)
END
SET NOCOUNT OFF
SET STATISTICS IO OFF
GO
On the first system that query runs 1 sec on the other system the query runs
4 sec
When i show the values of sysprocesses, all physical_IO, CPU .. are near the
same. When i use the MS SQL Profiler, the query need on the first system 10
00 Reads and 1000 ms on the other system it takes 6000 reads and 4000 ms. Wh
y are the performance value
s so different ? What does reads exactly mean in MS SQL Profiler ? When i pe
rform a lot of IO Trafffic (with Backup and and and ... the reads increase
to 25000 and takes 118000 in Duration.
How can i increase the performance with the same TSQL Script (see above) ?
On the temp db the query above has the same performance on both systems ? Wh
y ?Please see KB ID 314648 INF: Differences in STATISTICS IO, SQL Profiler and
Sysprocesses.
As for optimizing the queries we need to collect Executions Plans ,
STATISTICS PROFILE and performance monitor from both servers to have a
better understanding of the differences in the query execution and
bottlenecks.
Please also see
243589. INF: Troubleshooting Slow-running Queries
243588. INF: Troubleshooting Performance of Ad-Hoc Queries
243589 INF: Troubleshooting Slow-running Queries
HTH
Agnes Panosian
SQL Server Support|||Can i sent it via eMail to you ? (I have Performance Counters Profler Inform
ations and the STATISTICS from sysprocesses|||The profiler reads counter will not show exactly the same value as what you
would get from statistics io for a query because it will also accumulate the
reads profiler itself did. The best way to find actual reads for a query, in
my opinion, is to rely on stats io output alone.
To answer the 1 sec to 4 sec difference, I can only assume that this is
because of the underlying h/w. It is best to turn off tracing(profiling),
then run the queries on both the systems and re-evaluate because profiler
has some overhead.
"Dataworld" <anonymous@.discussions.microsoft.com> wrote in message
news:445C02E4-92AC-40C6-9F3D-6F2171D260E0@.microsoft.com...
> Hello,
> following questions. I have two server systems. The configuration for both
> systems are exact the same. I create an empty database on both server
> systems. Next i start the query analyzer and start the following query :
> USE TESTX
> DECLARE @.akt decimal
> SET NOCOUNT ON
> SET STATISTICS IO ON
> create table dbo.test
> (
> zahl decimal
> )
> SET @.akt=0
> WHILE NOT @.akt=1000
> BEGIN
> SET @.akt=@.akt+1
> insert into dbo.test VALUES (@.akt)
> END
> SET NOCOUNT OFF
> SET STATISTICS IO OFF
> GO
> On the first system that query runs 1 sec on the other system the query
> runs 4 sec
> When i show the values of sysprocesses, all physical_IO, CPU .. are near
> the same. When i use the MS SQL Profiler, the query need on the first
> system 1000 Reads and 1000 ms on the other system it takes 6000 reads and
> 4000 ms. Why are the performance values so different ? What does reads
> exactly mean in MS SQL Profiler ? When i perform a lot of IO Trafffic
> (with Backup and and and ... the reads increase to 25000 and takes
> 118000 in Duration.
> How can i increase the performance with the same TSQL Script (see above) ?
> On the temp db the query above has the same performance on both systems ?
> Why ?|||You might want to take a look with sysinternal's filemon, and see how many
reads (and how many blocks at the time) are done at the OS level, sometimes
the readsizes (clusters of 8k blocks) can differ, and thus I/O access times.
Also take a look at sqlperf(waitstats) and see where you loose time..(make
sure you reset the stats before you run your tests)
Or use my tools to monitor what is really happening, downloadable for build
760. Build 800 on request..
--
regards,
Mario
http://www.sqlinternals.com
"Dataworld" <anonymous@.discussions.microsoft.com> wrote in message
news:445C02E4-92AC-40C6-9F3D-6F2171D260E0@.microsoft.com...
> Hello,
> following questions. I have two server systems. The configuration for both
systems are exact the same. I create an empty database on both server
systems. Next i start the query analyzer and start the following query :
> USE TESTX
> DECLARE @.akt decimal
> SET NOCOUNT ON
> SET STATISTICS IO ON
> create table dbo.test
> (
> zahl decimal
> )
> SET @.akt=0
> WHILE NOT @.akt=1000
> BEGIN
> SET @.akt=@.akt+1
> insert into dbo.test VALUES (@.akt)
> END
> SET NOCOUNT OFF
> SET STATISTICS IO OFF
> GO
> On the first system that query runs 1 sec on the other system the query
runs 4 sec
> When i show the values of sysprocesses, all physical_IO, CPU .. are near
the same. When i use the MS SQL Profiler, the query need on the first system
1000 Reads and 1000 ms on the other system it takes 6000 reads and 4000 ms.
Why are the performance values so different ? What does reads exactly mean
in MS SQL Profiler ? When i perform a lot of IO Trafffic (with Backup and
and and ... the reads increase to 25000 and takes 118000 in Duration.
> How can i increase the performance with the same TSQL Script (see above) ?
> On the temp db the query above has the same performance on both systems ?
Why ?sql

MS SQL Server Profiler Reads

Hello,
following questions. I have two server systems. The configuration for both systems are exact the same. I create an empty database on both server systems. Next i start the query analyzer and start the following query :
USE TESTX
DECLARE @.akt decimal
SET NOCOUNT ON
SET STATISTICS IO ON
create table dbo.test
(
zahl decimal
)
SET @.akt=0
WHILE NOT @.akt=1000
BEGIN
SET @.akt=@.akt+1
insert into dbo.test VALUES (@.akt)
END
SET NOCOUNT OFF
SET STATISTICS IO OFF
GO
On the first system that query runs 1 sec on the other system the query runs 4 sec
When i show the values of sysprocesses, all physical_IO, CPU .. are near the same. When i use the MS SQL Profiler, the query need on the first system 1000 Reads and 1000 ms on the other system it takes 6000 reads and 4000 ms. Why are the performance value
s so different ? What does reads exactly mean in MS SQL Profiler ? When i perform a lot of IO Trafffic (with Backup and and and ... the reads increase to 25000 and takes 118000 in Duration.
How can i increase the performance with the same TSQL Script (see above) ?
On the temp db the query above has the same performance on both systems ? Why ?
Please see KB ID 314648 INF: Differences in STATISTICS IO, SQL Profiler and
Sysprocesses.
As for optimizing the queries we need to collect Executions Plans ,
STATISTICS PROFILE and performance monitor from both servers to have a
better understanding of the differences in the query execution and
bottlenecks.
Please also see
243589.INF: Troubleshooting Slow-running Queries
243588.INF: Troubleshooting Performance of Ad-Hoc Queries
243589 INF: Troubleshooting Slow-running Queries
HTH
Agnes Panosian
SQL Server Support
|||Can i sent it via eMail to you ? (I have Performance Counters Profler Informations and the STATISTICS from sysprocesses
|||The profiler reads counter will not show exactly the same value as what you
would get from statistics io for a query because it will also accumulate the
reads profiler itself did. The best way to find actual reads for a query, in
my opinion, is to rely on stats io output alone.
To answer the 1 sec to 4 sec difference, I can only assume that this is
because of the underlying h/w. It is best to turn off tracing(profiling),
then run the queries on both the systems and re-evaluate because profiler
has some overhead.
"Dataworld" <anonymous@.discussions.microsoft.com> wrote in message
news:445C02E4-92AC-40C6-9F3D-6F2171D260E0@.microsoft.com...
> Hello,
> following questions. I have two server systems. The configuration for both
> systems are exact the same. I create an empty database on both server
> systems. Next i start the query analyzer and start the following query :
> USE TESTX
> DECLARE @.akt decimal
> SET NOCOUNT ON
> SET STATISTICS IO ON
> create table dbo.test
> (
> zahl decimal
> )
> SET @.akt=0
> WHILE NOT @.akt=1000
> BEGIN
> SET @.akt=@.akt+1
> insert into dbo.test VALUES (@.akt)
> END
> SET NOCOUNT OFF
> SET STATISTICS IO OFF
> GO
> On the first system that query runs 1 sec on the other system the query
> runs 4 sec
> When i show the values of sysprocesses, all physical_IO, CPU .. are near
> the same. When i use the MS SQL Profiler, the query need on the first
> system 1000 Reads and 1000 ms on the other system it takes 6000 reads and
> 4000 ms. Why are the performance values so different ? What does reads
> exactly mean in MS SQL Profiler ? When i perform a lot of IO Trafffic
> (with Backup and and and ... the reads increase to 25000 and takes
> 118000 in Duration.
> How can i increase the performance with the same TSQL Script (see above) ?
> On the temp db the query above has the same performance on both systems ?
> Why ?
|||You might want to take a look with sysinternal's filemon, and see how many
reads (and how many blocks at the time) are done at the OS level, sometimes
the readsizes (clusters of 8k blocks) can differ, and thus I/O access times.
Also take a look at sqlperf(waitstats) and see where you loose time..(make
sure you reset the stats before you run your tests)
Or use my tools to monitor what is really happening, downloadable for build
760. Build 800 on request..
regards,
Mario
http://www.sqlinternals.com
"Dataworld" <anonymous@.discussions.microsoft.com> wrote in message
news:445C02E4-92AC-40C6-9F3D-6F2171D260E0@.microsoft.com...
> Hello,
> following questions. I have two server systems. The configuration for both
systems are exact the same. I create an empty database on both server
systems. Next i start the query analyzer and start the following query :
> USE TESTX
> DECLARE @.akt decimal
> SET NOCOUNT ON
> SET STATISTICS IO ON
> create table dbo.test
> (
> zahl decimal
> )
> SET @.akt=0
> WHILE NOT @.akt=1000
> BEGIN
> SET @.akt=@.akt+1
> insert into dbo.test VALUES (@.akt)
> END
> SET NOCOUNT OFF
> SET STATISTICS IO OFF
> GO
> On the first system that query runs 1 sec on the other system the query
runs 4 sec
> When i show the values of sysprocesses, all physical_IO, CPU .. are near
the same. When i use the MS SQL Profiler, the query need on the first system
1000 Reads and 1000 ms on the other system it takes 6000 reads and 4000 ms.
Why are the performance values so different ? What does reads exactly mean
in MS SQL Profiler ? When i perform a lot of IO Trafffic (with Backup and
and and ... the reads increase to 25000 and takes 118000 in Duration.
> How can i increase the performance with the same TSQL Script (see above) ?
> On the temp db the query above has the same performance on both systems ?
Why ?