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. [...]
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 [...]
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 [...]
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
[...]
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.
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 [...]
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.
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 [...]
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 [...]
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 [...]