volatile table & staging table

A volatile table is defined as a table whose contents can vary from empty to very large at run time.

A staging table allows incremental maintenance support for deferred materialized query table.

Quick test for SAN throughput

backup database Trace_DB4DBA to disk = 'Nul'

dtexec & dtutil

dtexec /sq PWeb /ser 10.1.1.1\uat /va /l "DTS.logprovidertextfile;c:\log.txt"

dtexec /ser 137.154.158.191\uat /sq Callista_IncrementalEnrolmentProcess_PWeb /va /U ssis /P ssis /l "DTS.LogProviderTextFile.1;c:\log.txt"

dtexec /ser 137.154.158.191\uat /sq Callista_IncrementalEnrolmentProcess_PWeb /U ssis /P ssis /l "DTS.LogProviderTextFile.1;c:\log.txt"


dtexec /ser nelson\dev /sq "Ipay Delegations Register Package DEV" /va


dtutil /Sql "Ipay Delegations Register Package DEV" /copy dts;abc

Can you set a pagefile more than 4GB?

http://support.microsoft.com/kb/971284
http://www.msfn.org/board/topic/90244-creating-memory-dumps/

Memory dump of the entire system:

1. Create or set the following registry value:
Key: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\i8042prt\Parameters
Value: CrashOnCtrlScroll
Type: REG_DWORD
Data: 1

Note You must restart the computer for the changes that you made in the register settings to take effect.

2. Right-Click on the "My Computer" icon on the desktop and select "Properties", then click the "Advanced tab. On the "Advanced" tab, click "Settings" under the "Performance" header. Click the "Advanced" tab, then click "Change" under "Virtual Memory". Set the pagefile to be located on the partition where the OS is installed, and set it to be equal to Physical RAM + 50 MB.

3. Also in the "System Properties" window on the "Advanced" tab, click the "Settings" buttun under the "Startup and Recovery" header. Make sure "Complete Memory Dump" is selected (see 3a if this is not in the list). You can change the location of the memory dump file to a different local partition if you do not have enough room on the partition where the OS is installed.

3a. If the "Complete Memory Dump" option in step 3 is not available, you will need to manually set this registry value:

Key: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\CrashControl
Value: CrashDumpEnabled
Type: REG_DWORD
Value: 1

4. You will need to reboot for these changes to take effect.

5. The next time that the system is exhibiting the problem you were asked to dump the machine for, hold down the RIGHT CTRL key and press the SCROLL LOCK key twice to cause the machine to bugcheck and create a memory dump. After the box comes back up, you'll find the resulting memory dump file in %systemroot%\memory.dmp that can be analyzed


Can you set a pagefile more than 4GB?
http://www.sqlservercentral.com/articles/Administration/70559/

TransactionOption property in SSIS

The TransactionOption property exists at the
1. package level,
2. container level (e.g. For Loop, Foreach Loop, Sequence, etc.),
3. as well as just about any Control Flow task (e.g. Execute SQL task, Data Flow task, etc.). Transactions work at control flow level and not within a data flow.


TransactionOption can be set to one of the following:

Required - if a transaction exists join it else start a new one
Supported - if a transaction exists join it (this is the default)
NotSupported - do not join an existing transaction

The built-in transaction support in SSIS makes use of the Distributed Transaction Coordinator (MSDTC) service which must be running. MSDTC also allows you to perform distributed transactions; e.g. updating a SQL Server database and an Oracle database in the same transaction.

OLAP QUERY LOG Table

- SQL Server Native Client 10.0. Windows Authentication.
- CreateOlapQueryLog = true
- Table name - OlapQueryLog
- Sampling = 10
- Integrated Auth was not working eventhough it has full access(using SQL login Account)

FlightRecorder.trc (SSAS) SQL Server 2005/8

The trace definition file (or XMLA script) references events using their identifiers and not names, therefore you need to know which ID is associated with each event. The following table describes each event and its corresponding id:

Event ID Name Description
1 Audit Login Capture new logins after the trace is started
2 Audit Logout Capture log outs after the trace is started
4 Audit Server Start / Stop Audit Server Start / Stop
18 Audit Object Permission Event Record object permission changes
19 Audit Backup / Restore Record backup / restore
5 Progress Report Begin Record starting of processing events
6 Progress Report End Record ending of processing events
7 Progress Report Current Record progress of processing events
8 Progress Report Error Record processing errors
9 Query Begin Query Begin (MDX, DMX, SQL)
10 Query End Query Begin (MDX, DMX, SQL)
15 Command Begin XMLA command begin (create / alter / delete / restore, etc)
16 Command End XMLA command end
(create / alter / delete / restore, etc)
17 Error Record any server level error / exception
33 Server State Discover Begin Record any locks / transactions / connections / jobs, etc.
34 Server State Discover Data Record any locks / transactions / connections / jobs, etc.
35 Server State Discover End Record any locks / transactions / connections / jobs, etc.
36 Discover Begin Records meta data queries, such as expanding the databases, cubes, dimension, measure groups' folders in SQL Server Management Studio.
38 Discover End Records meta data queries
39 Notification Records proactive caching related events and when Flight Recorder snapshots start / end. Also supposed to record when lazy processing starts and completes.
41 Existing Connection Records connections that exist when trace is started. Unfortunately there is no way to retrospectively retrieve any commands that an existing connection has submitted.
42 Existing Session Records sessions that exist when trace is started. Unfortunately there is no way to retrospectively retrieve any commands that an existing session has submitted.
43 Session Initialize Records creation of new sessions
50 Deadlock Records occurrence of a metadata deadlock
51 Lock Timeout Records occurrence of metadata lock timeout
70 Query Cube Begin Query Cube Begin
71 Query Cube End Query Cube End
72 Calculate Non Empty Begin Record queries using Non Empty keyword, NONEMPTY or NONEMPTYCROSSJOIN function.
73 Calculate Non Empty Current Record progress of Non Empty queries
74 Calculate Non Empty End Record Non Empty queries
75 Serialize Results Begin Return query results once data set has been retrieved
76 Serialize Results Current Progress of returning query results the requesting application.
77 Serialize Results End Return query results
78 Execute MDX Script Begin Record execution of MDX script (cube level definition of calculations)
79 Execute MDX Script Current Record the progress of MDX script execution
80 Execute MDX Script End Record execution of MDX script
81 Query Dimension Record retrieval of dimension data
11 Query Subcube Query Sub-cube
12 Query Subcube Verbose Include bitmap of sub-cube being queried
60 Get Data From Aggregation Get Data From Aggregation
61 Get Data from Cache Get Data From Cache