Getting max record from counts

SELECT CASEID
, SOURCEUSERID, count(*)
, ROW_NUMBER() OVER (PARTITION BY CASEID ORDER BY COUNT(*) DESC) AS RN
FROM LGNCC_CLOSEDCASEEVENTS WHERE caseid = 1086297
GROUP BY CASEID, SOURCEUSERID

SSIS add Oracle Data Provider for .Net

ConnMgr = Dts.Connections(“support.laganprod2″)
            ConnStr = ConnMgr.ConnectionString
            Dts.Events.FireInformation(1, “”, “Connection string = ” + ConnStr, “”, 0, True)
            Try
                conn1 = DirectCast(ConnMgr.AcquireConnection(Nothing), OracleConnection)
                Dts.Events.FireInformation(1, “”, “Connection acquired successfully on ” + ConnMgr.Name, “”, 0, False)
            Catch ex As Exception
                Dts.Events.FireError(-1, “”, “Connection failed on ” + ConnMgr.Name, “”, 0)
                bFailure = True
            End Try

VMware and Perfmon Counters

Just learned today that you can use Perfmon on a guest to see what is really going on. Look at the counters under VM Processor and VM Memory. Used these to diagnose a CPU issue where the VM Admin believed they had allocated more resources to the virtual that it actually had available.

Using Perfmon to Monitor Performance

These are the counters recommended when looking at SQL Server Performance. Counters should be sampled every 15 seconds and all instances included not just totals.

Memory – Available Mbytes (pref 512MB – 1GB)
Paging File – % Usage (pref <1)
SqlServer: Buffer Manager
Page Life expectancy – (pref >300, possible fix indexes)
SQLServer: Memory Manger
Total Server Memory
Target Server Memory – ( if reducing OS asking for memory )
Memory Grants Pending – ( should be zero, add memory )
Physical Disk
Avg. Disk sec/Read ( 100ms slow)
Avg. Disk sec/Write
Processor – % Processor Time
System – Processor Queue Length ( <0)
SQLServer: Batch Statistics
Compilations/sec
Recompliations/sec
SQLServer:General Statistics
User Connections ( Trends )
SQLServer: Access Methods ( Schema Design )
Page Splits/sec
Page Alloacted/sec
Range Scans/sec
Disk Reads/sec ( Trend )
Disk Writes/sec ( Trend )

These where taken from Brent Ozar’s presentation.

SQL Server Index Tuning.

Just listened to some online training on SQL Server Index Fragmentation.

Use Query:
SELECT i.name, indexstats.avg_fragmentation_in_percent, page_count
FROM
sys.dm_db_index_physical_stats(DB_ID(‘winnipeg’), OBJECT_ID(‘lgncc_enquiry’), NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes i ON i.OBJECT_ID = indexstats.OBJECT_ID
AND i.index_id = indexstats.index_id
order by indexstats.avg_fragmentation_in_percent desc;

Use general rules:

1. If page count < 1000 leave
2. If fragmentation < 30 reorganize
3. If fragmentation > 30% rebuild

Tracing

I hate to say it but there are days when I wish Oracle had a tool like SQL Profiler.

Code to look for gaps in records in a table

WITH aquery AS
(SELECT emailid after_gap,
LAG(emailid,1,0) OVER (ORDER BY emailid) before_gap
FROM lgncc_emaildetails)
SELECT
before_gap, after_gap
FROM
aquery
WHERE
before_gap != 0
AND
after_gap – before_gap > 1
ORDER BY
before_gap;

SPfile and invalid parameter

Had an instance that would not start due to an invalid parameter setting in the spfile. This can be fixed by connection to the idle instance and doing the following:

create pfile=’pfile_name’ from pfile;

Then edit the pfile to fix the parameter.

create spfile from pfile=’pfile_name’;

Ronnie

Nothing much going on.

Been very quiet recently, although I forsee thinks getting a little busier very soon.

Still do not understand why people do not use Oracle the way it should, after paying so much for it.

If it can be done in a single statement then do it in a single statement.

Ronnie

One to watch out for with JDBC

In PL/SQL, when a CHAR or a VARCHAR column is defined as a OUT or IN/OUT variable, the driver allocates a CHAR array of 32512 chars. This can cause a memory consumption problem. Note that VARCHAR2 columns do not exhibit this behavior.

You end up with traces like:

PARSING IN CURSOR #32 len=112 dep=0 uid=60 oct=47 lid=60 tim=2677334787 hv=2159500523 ad=’2db0cc60′

BEGIN LGNCC_ENQ_GetInteraction(:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17); END;

END OF STMT

PARSE #32:c=0,e=24,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=2677334785

EXEC #18:c=0,e=20,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=2677334986

FETCH #18:c=0,e=35,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=1,tim=2677335035

WAIT #32: nam=’SQL*Net message to client’ ela= 1 driver id=1952673792 #bytes=1 p3=0 obj#=68676 tim=2677335092

WAIT #32: nam=’SQL*Net more data to client’ ela= 22 driver id=1952673792 #bytes=2134 p3=0 obj#=68676 tim=2677335132

WAIT #32: nam=’SQL*Net more data to client’ ela= 7 driver id=1952673792 #bytes=2048 p3=0 obj#=68676 tim=2677335150

WAIT #32: nam=’SQL*Net more data to client’ ela= 7 driver id=1952673792 #bytes=2048 p3=0 obj#=68676 tim=2677335166

WAIT #32: nam=’SQL*Net more data to client’ ela= 6 driver id=1952673792 #bytes=1792 p3=0 obj#=68676 tim=2677335181

WAIT #32: nam=’SQL*Net more data to client’ ela= 8 driver id=1952673792 #bytes=2048 p3=0 obj#=68676 tim=2677335198

WAIT #32: nam=’SQL*Net more data to client’ ela= 751 driver id=1952673792 #bytes=2048 p3=0 obj#=68676 tim=2677335959

WAIT #32: nam=’SQL*Net more data to client’ ela= 316 driver id=1952673792 #bytes=2048 p3=0 obj#=68676 tim=2677336290

WAIT #32: nam=’SQL*Net more data to client’ ela= 13 driver id=1952673792 #bytes=2048 p3=0 obj#=68676 tim=2677336319

WAIT #32: nam=’SQL*Net more data to client’ ela= 1286 driver id=1952673792 #bytes=2048 p3=0 obj#=68676 tim=2677337620

WAIT #32: nam=’SQL*Net more data to client’ ela= 8 driver id=1952673792 #bytes=1792 p3=0 obj#=68676 tim=2677337644

WAIT #32: nam=’SQL*Net more data to client’ ela= 574307 driver id=1952673792 #bytes=2048 p3=0 obj#=68676 tim=2677911966

WAIT #32: nam=’SQL*Net more data to client’ ela= 645 driver id=1952673792 #bytes=2048 p3=0 obj#=68676 tim=2677912636

WAIT #32: nam=’SQL*Net more data to client’ ela= 6 driver id=1952673792 #bytes=2048 p3=0 obj#=68676 tim=2677912658

WAIT #32: nam=’SQL*Net more data to client’ ela= 5 driver id=1952673792 #bytes=2048 p3=0 obj#=68676 tim=2677912676

WAIT #32: nam=’SQL*Net more data to client’ ela= 655250 driver id=1952673792 #bytes=1792 p3=0 obj#=68676 tim=2678567941

WAIT #32: nam=’SQL*Net more data to client’ ela= 10 driver id=1952673792 #bytes=2048 p3=0 obj#=68676 tim=2678567982

EXEC #32:c=0,e=1233159,p=0,cr=4,cu=0,mis=0,r=1,dep=0,og=1,tim=2678568000

WAIT #32: nam=’SQL*Net message from client’ ela= 3385 driver id=1952673792 #bytes=1 p3=0 obj#=68676 tim=2678571444

XCTEND rlbk=0, rd_only=1

WAIT #0: nam=’SQL*Net message to client’ ela= 0 driver id=1952673792 #bytes=1 p3=0 obj#=68676 tim=2678571535

WAIT #0: nam=’SQL*Net message from client’ ela= 555 driver id=1952673792 #bytes=1 p3=0 obj#=68676 tim=2678572105

XCTEND rlbk=0, rd_only=1

WAIT #0: nam=’SQL*Net message to client’ ela= 0 driver id=1952673792 #bytes=1 p3=0 obj#=68676 tim=2678572143

WAIT #0: nam=’SQL*Net message from client’ ela= 2682513 driver id=1952673792 #bytes=1 p3=0 obj#=68676 tim=2681254673

*** 2009-09-04 11:32:44.610