Re: SQL Server vs. Oracle
- From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 3 Jun 2004 10:50:57 +0100
http://www.databasejournal.com/features/mssql/article.php/2239461
outlines quite well the tool that is appropriate for use with
sqlserver and that is what they call serverside tracing. The trace
file that is generated is not anywhere near as useful as a 10046 level
8 trace (and if you thought that the event syntax in Oracle is cryptic
you should see the sp interface for tracing in mssql). Never the less
usable information is there.
There are 2 basic problems that I see prevalent in the MSSQL community though
1. I agree with John that whilst excessive elapsed time is used to
determine a poorly performing process, the focus of tuning efforts
isn't on the makeup of that elapsed time. I think this is partly due
to still being in what I call the sysadmin mode of what % of x
resource is being used so we look at BCHR, %processor time and all
sorts of other tangential metrics. It is also partly a problem of the
underlying product - so you find that you spend all your time waiting
for a hot page. How are you going to fix this within sqlservers
limitations?
2. The basic admin interface is really really straightforward - though
I hate the fact that the mmc shows you out of date information and
doesn't refresh reliably. Then you move from click here, set this
checkmark here, run this wizard to code like this
-- Declarations
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 50000
declare @on bit
set @on = 1
declare @intfilter int
declare @bigintfilter bigint
-- create trace
exec @rc = sp_trace_create @TraceID output, 0, N'C:\trace\stdTrace',
@maxfilesize, NULL
if (@rc != 0) goto error -- something bad happened
-- set the events we care about
exec sp_trace_setevent @TraceID, 10, 1, @on
exec sp_trace_setevent @TraceID, 10, 6, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 13, 1, @on
exec sp_trace_setevent @TraceID, 13, 6, @on
exec sp_trace_setevent @TraceID, 13, 10, @on
exec sp_trace_setevent @TraceID, 13, 11, @on
exec sp_trace_setevent @TraceID, 13, 12, @on
exec sp_trace_setevent @TraceID, 13, 13, @on
exec sp_trace_setevent @TraceID, 13, 14, @on
exec sp_trace_setevent @TraceID, 13, 16, @on
exec sp_trace_setevent @TraceID, 13, 17, @on
exec sp_trace_setevent @TraceID, 13, 18, @on
exec sp_trace_setevent @TraceID, 37, 1, @on
exec sp_trace_setevent @TraceID, 37, 6, @on
exec sp_trace_setevent @TraceID, 37, 10, @on
exec sp_trace_setevent @TraceID, 37, 11, @on
exec sp_trace_setevent @TraceID, 37, 12, @on
exec sp_trace_setevent @TraceID, 37, 13, @on
exec sp_trace_setevent @TraceID, 37, 14, @on
exec sp_trace_setevent @TraceID, 37, 16, @on
exec sp_trace_setevent @TraceID, 37, 17, @on
exec sp_trace_setevent @TraceID, 37, 18, @on
exec sp_trace_setevent @TraceID, 40, 1, @on
exec sp_trace_setevent @TraceID, 40, 6, @on
exec sp_trace_setevent @TraceID, 40, 10, @on
exec sp_trace_setevent @TraceID, 40, 11, @on
exec sp_trace_setevent @TraceID, 40, 12, @on
exec sp_trace_setevent @TraceID, 40, 13, @on
exec sp_trace_setevent @TraceID, 40, 14, @on
exec sp_trace_setevent @TraceID, 40, 16, @on
exec sp_trace_setevent @TraceID, 40, 17, @on
exec sp_trace_setevent @TraceID, 40, 18, @on
exec sp_trace_setevent @TraceID, 41, 1, @on
exec sp_trace_setevent @TraceID, 41, 6, @on
exec sp_trace_setevent @TraceID, 41, 10, @on
exec sp_trace_setevent @TraceID, 41, 11, @on
exec sp_trace_setevent @TraceID, 41, 12, @on
exec sp_trace_setevent @TraceID, 41, 13, @on
exec sp_trace_setevent @TraceID, 41, 14, @on
exec sp_trace_setevent @TraceID, 41, 16, @on
exec sp_trace_setevent @TraceID, 41, 17, @on
exec sp_trace_setevent @TraceID, 41, 18, @on
exec sp_trace_setevent @TraceID, 43, 1, @on
exec sp_trace_setevent @TraceID, 43, 6, @on
exec sp_trace_setevent @TraceID, 43, 10, @on
exec sp_trace_setevent @TraceID, 43, 11, @on
exec sp_trace_setevent @TraceID, 43, 12, @on
exec sp_trace_setevent @TraceID, 43, 13, @on
exec sp_trace_setevent @TraceID, 43, 14, @on
exec sp_trace_setevent @TraceID, 43, 16, @on
exec sp_trace_setevent @TraceID, 43, 17, @on
exec sp_trace_setevent @TraceID, 43, 18, @on
exec sp_trace_setevent @TraceID, 44, 1, @on
exec sp_trace_setevent @TraceID, 44, 6, @on
exec sp_trace_setevent @TraceID, 44, 10, @on
exec sp_trace_setevent @TraceID, 44, 11, @on
exec sp_trace_setevent @TraceID, 44, 12, @on
exec sp_trace_setevent @TraceID, 44, 13, @on
exec sp_trace_setevent @TraceID, 44, 14, @on
exec sp_trace_setevent @TraceID, 44, 16, @on
exec sp_trace_setevent @TraceID, 44, 17, @on
exec sp_trace_setevent @TraceID, 44, 18, @on
exec sp_trace_setevent @TraceID, 45, 1, @on
exec sp_trace_setevent @TraceID, 45, 6, @on
exec sp_trace_setevent @TraceID, 45, 10, @on
exec sp_trace_setevent @TraceID, 45, 11, @on
exec sp_trace_setevent @TraceID, 45, 12, @on
exec sp_trace_setevent @TraceID, 45, 13, @on
exec sp_trace_setevent @TraceID, 45, 14, @on
exec sp_trace_setevent @TraceID, 45, 16, @on
exec sp_trace_setevent @TraceID, 45, 17, @on
exec sp_trace_setevent @TraceID, 45, 18, @on
-- limit ourselves to the <removed> database
exec sp_trace_setfilter @TraceID, 35, 1, 6, N'<removed>'
-- start immediately
exec sp_trace_setstatus @TraceID, 1
-- display trace id for future references
select TraceID=@TraceID
goto finish
error:
select ErrorCode=@rc
finish:
go
clear as mud really.
oh and 3 which has only just occurred to me but happens with Oracle
systems as well - the question John paraphrases is exactly the sort of
question that gets asked - how can I fix my "system" not "this order
process has problems".
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com
On Wed, 2 Jun 2004 15:15:54 -0700 , John Kanagaraj
<john.kanagaraj@xxxxxxx> wrote:
>
> Fernando,
>
> >SQLServer suffers from the same "syndrome" as Visual Basic: it makes
> >the easiest parts of programming (in VB) or of administration
> >(SQLServer) easier. But the hard parts actually get much harder. So,
> >the experience of administering a large, production SQLServer2k db is
> >probably as nasty as maintaining/upgrading a large VB app.
>
> I was just looking up one of the SS forums - the (paraphrased) question was
> "What can I do to make my 1000 user Website backed by SS2K go faster?" And
> the answer (again paraphrased) was 'Use DML with the 'with nolock'"
> parameter!! SS2k suffers from basic concurrency, transaction management and
> scalability issues. [And they only recenly moved from the Block-level lock
> to the Row-Level lock mode in SS2k (or was in in 6.x?)] Not sure what the
> new 'Yukon' version does though...
>
> >One thing is that SQLServer doesn't give as much control as you can
> >expect, so when things like "why is this so slow" happens,
> >life is much
> >harder. Of course, things really go bad when you have a very large
> >legacy VB code using SQLServer2k and everything is very slow and they
> >just call you to "optimize this" (where "this" can be legacy vb code,
> >db schema, sql, etc, etc).
>
> I think this sort of thing is available - just that the SS2K community
> hasn't quite figured out how to really use trace, bind vars, Cost
> optimization, Wait event based tuning and ultimately good SQL tuning. [MS
> has also not released much information as well] It seems that the
> master.dbo.sysperfinfo does contain Stats (including the 'Buffer Caceh Hit
> Ratio'!!!) and master.dbo.sysprocesses does contain basic Wait event info,
> including 'PAGEIOLATCH_SH', 'PAGEIOLATCH_EX', 'NETWORKIO' etc. However,
> there is either no or mimimal information on this very basic event out
> there...
>
> John Kanagaraj <><
> DB Soft Inc
> Phone: 408-970-7002 (W)
>
> Listen to great, commercial-free christian music 24x7x365 at
> http://www.klove.com
>
> ** The opinions and facts contained in this message are entirely mine and do
> not reflect those of my employer or customers **
>
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
