'ase' Category

  • What configuration parameters control, affect, use, or report ASE memory / ASE cache configuration?

    February 22, 2011

    Alphabetical list of configuration parameters (used by sp_configure) that control, affect, use, or report ASE memory / ASE cache configuration.

    additional network memory - specifies the maximum size of additional memory that can be used for network packets that are larger than the default packet size.
    allow resource limits - specifies the use of resource limits. [...]

  • What is the maximum number of possible DB-Connections? ASE 15.0.3

    February 22, 2011

    Each connection requires ~350KB of memory. So the maximum number is limited by the physical memory whereas the ASE server allows to create up to 32.767 connnections.
    To verify how much connections are used. Execute the stored procedure:
    sp_monitorconfig "number of user connection"
    Examlpe output of sp_monitorconfig “number of user connection”
    Name [...]

  • Sybase ASE Query Optimization - webcast from Embarcadero and ISUG

    October 19, 2010

    This coming Thursday and Friday, Embarcadero and ISUG are hosting a free webcast on Sybase ASE Query Optimization. Two well-known Sybase experts, Peter Dobler and Kevin Sherlock, will be taking your questions live. This looks like a good one as they are going to go into areas that aren’t covered in the manuals. Here’s the [...]

  • Determine physical reads of a process/transaction - ASE MDA tables

    October 18, 2010

    SELECT
    a.SPID
    , convert(char(15), a.Login) AS Login
    , convert(char(15),a.Application) AS Application
    , convert(char(20),a.Command) AS Command
    , convert(char(20),a.DBName) AS Dbname
    , b.CpuTime
    , b.WaitTime
    , b.PhysicalReads
    , b.LogicalReads
    , b.PagesModified
    FROM
    [...]

  • Getting wait events statistics - ASE MDA tables

    October 18, 2010

    select w.WaitTime, w.Waits, i.Description
    from monSysWaits w, monWaitEventInfo i
    where w.WaitEventID = i.WaitEventID
    order by WaitTime
    desc
    These can all be ran on ASE version 12.5.0.3 and above.

  • Shows metrics for each stored procedure executed - ASE MDA tables

    October 18, 2010

    select ProcName = isnull(object_name(ProcedureID, DBID), “UNKNOWN”),DBName = isnull(db_name(DBID), “UNKNOWN”),ElapsedTime = datediff(ms, min(StartTime), max(EndTime))
    from master..monSysStatement
    group by SPID, DBID, ProcedureID, BatchID
    having ProcedureID != 0
    order by 3
    Note: The output contains 1 row for every run, meaning we may see 1 sp have 3 rows. You may create a temp table [...]

  • Identify usage statistics of user indexes or user tables - ASE MDA tables

    October 18, 2010

    select Object = convert(char(15), object_name(ObjectID, DBID)), IndexID, OptSelectCount, UsedCount, Operations
    from master..monOpenObjectActivity
    where ObjectID > 99
    and IndexID > 0
    order by UsedCount
    Note: If you want to check the system tables statistics, just put ObjectID < 100 instead of ObjectID >99.
    These can all be ran on ASE version 12.5.0.3 and above.

  • Getting the SQL Text of a Certain Process - ASE MDA tables

    October 18, 2010

    select SPID, suser_name(ServerUserID), SequenceInBatch, SQLText
    from master..monSysSQLText
    where SPID=<spid>
    order by SequenceInBatch
    NOTE: Why use this? We’ve all been used to executing dbcc sqltext
    to get the SQL command. But you may have noticed that sometimes, if not
    most of the time, we only get a part of the whole sql command. With [...]

  • capture show_missing_stats in ASE15

    November 18, 2009

    show_missing_stats shows details of useful statistics missing from SARG / Join columns.
    It is important to note there are several situations where show_missing_stats will not print.

    If you run a “stored-procedure” and there is a current plan in the master..sysqueryplans”, the “show_missing_statistics” will not print anything. You can do one of 2 things.

    Run the stored-procedure with [...]

  • Determine size of a text/image field

    October 21, 2009

    When trying to extract data from a text or image field, it is not always clear, how many bytes of data can be expected. Here’s a way how to determine this:
     
    1> SET textsize 1669842
    2> go
    1> SELECT Xml FROM TradeXml WHERE AsOfDate = ‘20091019′ AND TradeId = ‘xxxxxxx’
    2> go
     
    In order to determine the value that needs [...]

 
Powered by Wordpress and MySQL. Theme by Shlomi Noach, openark.org