Identify in details the consumption of memory from a specific session

After some research I've discovered the view V$PROCESS_MEMORY_DETAIL and how use it.
By default it's always empty and need some commands to "enable" it (oradebug or alter session).

References I found:

How To Find Where The Memory Is Growing For A Process (Doc ID 822527.1)
Oracle Memory Troubleshooting, Part 4: Drilling down into PGA memory usage with V$PROCESS_MEMORY_DETAIL

This quote from Oracle Article says everything :

How to use V$PROCESS_MEMORY and V$PROCESS_MEMORY_DETAIL to identify
where the memory is growing.

In Oracle 10.2 and higher exist 2 new views that can be used to find
where the memory continue to grow. This views can be used instead of
heap dump to find where the memory is growing:

 - V$PROCESS_MEMORY:

     This view displays dynamic PGA memory usage by named component categories for each Oracle process. This view will contain up to six
rows for each Oracle process, one row for:
      - Java
      - PL/SQL
      - OLAP
      - SQL
      - Freeable
      - Other

 - V$PROCESS_MEMORY_DETAIL
    Contain break down of memory allocation for each component.
    - To activate this view can one of following commands executed:
       SQL> alter session set events'immediate trace name PGA_DETAIL_GET level <PID>';
       From ORADEBUG:
       SQL> ORADEBUG SETMYPID;
       SQL> ORADEBUG DUMP PGA_DETAIL_GET <PID>;
    - To remove all rows in the view run following command:
       SQL> alter session set events'immediate trace name PGA_DETAIL_CANCEL level <PID>';
       From ORADEBUG:
       SQL> ORADEBUG DUMP PGA_DETAIL_CANCEL <PID>;

and a useful SQL:

SELECT
    s.sid,p.spid,pm.*
FROM
    v$session s
  , v$process p
  , v$process_memory pm
WHERE
    s.paddr = p.addr
AND p.pid = pm.pid
-- AND p.spid IN (1423)
AND s.sid IN (244)
ORDER BY
    sid
  , category

Here is the informations detailed...
Just needs digging the mysterious names .

  1* select * from v$process_memory_detail
SQL> /

       PID    SERIAL# CATEGORY        NAME                       HEAP_NAME            BYTES ALLOCATION_COUNT HEAP_DESCRIPTOR  PARENT_HEAP_DESC
---------- ---------- --------------- -------------------------- --------------- ---------- ---------------- ---------------- ----------------
        15          1 SQL             miscellaneous                                   18880               80 00               00
        15          1 Other           koi korfc                  koh-kghu sessi       24544                2 0000002A97A1A1C0 0000002A97935BE0
        15          1 Other           kxsFrame8kPage             session heap         24744                3 0000002A97935BE0 000000000B0BC680
        15          1 PL/SQL          static frame of inst       koh-kghu sessi       26696                3 0000002A97A2BD50 0000002A97935BE0
        15          1 Other           kgsc ht segs               session heap         29824              327 0000002A97935BE0 000000000B0BC680
        15          1 Other           kgh stack                  pga heap             32832                1 000000000B0B6C20 00
        15          1 Other           kopo object                koh-kghu sessi       34728                3 0000002A97A1A1C0 0000002A97935BE0
        15          1 Other           koh-kghu session heap      session heap         35352               15 0000002A97935BE0 000000000B0BC680
        15          1 Other           Fixed Uga                  pga heap             39024                1 000000000B0B6C20 00
        15          1 PL/SQL          miscellaneous                                   64736               47 00               00
        15          1 Other           mark                       Alloc server h      101088               24 0000002A97A99ED0 0000002A9795E090
        15          1 Other           free memory                top call heap       107520                3 000000000B0BC460 00
        15          1 Other           mark                       Alloc environm      111576               18 0000002A9795E090 0000002A97935BE0
        15          1 Other           kxsFrame4kPage             session heap        120408               29 0000002A97935BE0 000000000B0BC680
        15          1 Other           free memory                session heap        345672              213 0000002A97935BE0 000000000B0BC680
        15          1 Other           miscellaneous                                  380264              619 00               00
        15          1 Other           free memory                pga heap            917616             4618 000000000B0B6C20 00
        15          1 Other           permanent memory           koh-kghu call      2618480            32731 0000002AAE10E8C0 000000000B0B6C20
        15          1 Other           permanent memory           koh dur heap d     2619072            32731 0000002A97947E40 0000002A97935BE0
        15          1 Other           permanent memory           session heap       7450512              180 0000002A97935BE0 000000000B0BC680
        15          1 Other           permanent memory           pga heap          23983776             2907 000000000B0B6C20 00
        15          1 Other           kol vstring                koh-kghu call     34298216            32727 0000002AAE10E8C0 000000000B0B6C20
        15          1 Other           kadaih image               koh dur heap d   132475264            65452 0000002AAE0E66C8 000000000B0B6C20
        15          1 Other           koh-kghu call heap         pga heap         164949272            65457 000000000B0B6C20 00
        37         67 Other           mark                       Alloc server h        8480                3 0000002A97870860 0000002A978715C8
        37         67 Other           KFKPGA                     KFK_IO_SUBHEAP        9936                1 0000002A97899E10 000000000B0B6C20
        37         67 Other           dbgr entry                 diag pga             12248              218 0000002A976007E0 000000000B0B6C20
        37         67 Other           permanent memory           diag pga             16056                6 0000002A976007E0 000000000B0B6C20
        37         67 PL/SQL          miscellaneous                                   16232               34 00               00
        37         67 PL/SQL          PL/SQL STACK               PLS PGA hp           16432                2 0000002A9788F150 000000000B0B6C20
        37         67 Other           kgh stack                  pga heap             17024                1 000000000B0B6C20 00
        37         67 Other           dbgeInitProcessCtx:InvCtx  diag pga             17088                2 0000002A976007E0 000000000B0B6C20
        37         67 Other           kgsc ht segs               session heap         17320              286 0000002A979467C8 000000000B0BC680
        37         67 Other           kxsc: kkspsc0 2            session heap         20056               23 0000002A979467C8 000000000B0BC680
        37         67 Other           koh-kghu session heap      session heap         22752               10 0000002A979467C8 000000000B0BC680
        37         67 Other           free memory                top uga heap         31880                1 000000000B0BC680 00
        37         67 SQL             miscellaneous                                   32728               93 00               00
        37         67 Other           kxsFrame16kPage            session heap         32880                2 0000002A979467C8 000000000B0BC680
        37         67 Other           Fixed Uga                  pga heap             39024                1 000000000B0B6C20 00
        37         67 Other           free memory                pga heap             47192                5 000000000B0B6C20 00
        37         67 PL/SQL          recursive addr reg file    koh-kghu sessi       51112                3 0000002A9810AA50 0000002A979467C8
        37         67 Other           permanent memory           session heap         52672                8 0000002A979467C8 000000000B0BC680
        37         67 Other           free memory                session heap         72944               23 0000002A979467C8 000000000B0BC680
        37         67 Other           miscellaneous                                  101120              178 00               00
        37         67 Other           free memory                top call heap       127936                3 000000000B0BC460 00
        37         67 Other           permanent memory           pga heap            175960               22 000000000B0B6C20 00
        37         67 Other           kxsFrame4kPage             session heap        195144               47 0000002A979467C8 000000000B0BC680

47 rows selected.


SQL logs showing login that I can't find

Can you try running the following:

EXEC xp_logininfo @acctname = 'SERVER\loginname', @option='all';

This should return the SQL login the Windows user is being authenticated as.

Reference here.


Statistics disappear after incremental update

Not sure if it's a bug, per se but it's definitely an interesting occurrence. Online partition rebuilds are new in SQL Server 2014 so there may be some internals to sort through with this.

Here's my best explanation for you. Incremental statistics absolutely require that all partitions be sampled at the same rate so that when the engine merges the stats pages it can be confident that the sampled distribution is comparable. REBUILD necessarily samples data at a 100% sample rate. There's no guarantee that the 100% sample rate on partition 9 is always going to be the exact sample rate of the rest of the partitions. Because of this, it appears as though the engine cannot merge the samples and you end up with an empty stats blob. However, the statistics object is still there:

select
    check_time = sysdatetime(),
    schema_name = sh.name,
    table_name = t.name,
    stat_name = s.name,
    index_name = i.name,
    stats_column = index_col(quotename(sh.name)+'.'+quotename(t.name),s.stats_id,1),
    s.stats_id,
    s.has_filter,
    s.is_incremental,
    s.auto_created,
    sp.last_updated,
    sp.rows,
    sp.rows_sampled,
    sp.unfiltered_rows,
    modification_counter
from sys.stats s
join sys.tables t
    on s.object_id = t.object_id
join sys.schemas sh
    on t.schema_id = sh.schema_id
left join sys.indexes i
    on s.object_id = i.object_id
    and s.name = i.name
outer apply sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
where t.name = 'TransactionHistory' and sh.name = 'dbo'

You can fill the blob through any number of means:

UPDATE STATISTICS dbo.TransactionHistory (IDX_ProductId) WITH RESAMPLE;

or

UPDATE STATISTICS dbo.TransactionHistory (IDX_ProductId) WITH RESAMPLE ON PARTITIONS (9);

or you can wait for AutoStats to update on the first compilation of a query plan using that object:

-- look at my creative query
select *
from dbo.TransactionHistory
where TransactionDate = '20140101';

Having said all of that, this enlightening post by Erin Stellato highlights what has come to be perceived as a major deficiency of incremental stats. Their partition-level data are not are not used by the optimizer in query plan generation, reducing the presumed benefit of incremental statistics. What, then, is the current benefit of incremental statistics? I'd submit that their primary utility is in an ability to sample large tables more consistently at a higher rate than with traditional statistics.

Using your example, here's how things look:

set statistics time on;

update statistics dbo.TransactionHistory(IDX_ProductId)
with fullscan;

--SQL Server Execution Times:
--  CPU time = 94 ms,  elapsed time = 131 ms.

update statistics dbo.TransactionHistory(IDX_ProductId)
with resample on partitions(2);

 --SQL Server Execution Times:
 --  CPU time = 0 ms,  elapsed time = 5 ms.

drop index IDX_ProductId On dbo.TransactionHistory;

CREATE NONCLUSTERED INDEX IDX_ProductId ON dbo.TransactionHistory (ProductId)
  WITH (DATA_COMPRESSION = ROW)
  ON [PRIMARY]

update statistics dbo.TransactionHistory(IDX_ProductId)
with fullscan;

 --SQL Server Execution Times:
 --  CPU time = 76 ms,  elapsed time = 66 ms.

A fullscan statistics update on the incremental statistic costs 131 ms. A fullscan statistics update on the non partition-aligned statistic costs 66 ms. The non-aligned statistic is slower most likely due to overhead incurred with merging the individual statistics pages back into the main histogram. However, using the partition-aligned statistic object, we can update one partition and merge it back into the main histogram blob in 5 ms. So at this point the administrator with the incremental statistic is faced with a decision. They can decrease their overall statistics maintenance time by only updating partitions would traditionally need to be updated, or they can experiment with higher sample rates such that they potentially get more rows sampled in the same period of time as their previous maintenance timeframe. The former allows breathing room in the maintenance window, the latter might push statistics on a very large table to a place where queries get better plans based on more accurate statistics. This is not a guarantee and your mileage may vary.

The reader can see that 66 ms is not a painful statistics update time on this table so I tried setting up a test on the stackexchange data set. There are 6,418,608 posts (excluding StackOverflow posts and all posts from 2012 - a data error on my part) in the recent dump that I downloaded.

I've partitioned the data by [CreationDate] because ... demo.

Here are some timings for some pretty standard scenarios (100% - index rebuild, default - stats auto-update or UPDATE STATISTICS without a specified sample rate:

Create Non-Incremental Statistic with Fullscan: CPU time = 23500 ms,  elapsed time = 22521 ms.
Create Incremental Statistic WIth Fullscan: CPU time = 20406 ms,  elapsed time = 15413 ms.
Update Non-Incremental Statistic with Default Sample Rate: CPU time = 406 ms,  elapsed time = 408 ms.
Update Incremental Statistic with Default Sample Rate: CPU time = 453 ms,  elapsed time = 507 ms.

Let's say we're more sophisticated than these default scenarios and have decided that a 10% sample rate is the minimum rate that should get us the plans we need while keeping maintenance time to a reasonable timeframe.

Update Non-Incremental Statistic with sample 10 percent: CPU time = 2344 ms,  elapsed time = 2441 ms.
Update Incremental Statistic with sample 10 percent: CPU time = 2344 ms,  elapsed time = 2388 ms.

So far there's no clear benefit to having an incremental statistic. However, if we leverage the undocumented sys.dm_db_stats_properties_internal() DMV (below), you can get some insight into which partition(s) you may want to update. Let's say we made changes to data in partition 3 and we want ensure stats are fresh for incoming queries. Here are our options:

Update Non-Incremental at Default (also the default behavior of Auto-Stats Update): 408 ms.
Update Non-Incremental at 10%: 2441 ms.
Update Incremental Statistics, Partition 3 With Resample (10% - our defined sample rate): CPU time = 63 ms,  elapsed time = 63 ms.

Here's where we need to make a decision. Do we take the win of a 63 ms. partition-based update of statistics, or do we bump the sample rate even higher? Let's say we are willing to take the initial hit of sampling at 50% on an incremental statistic:

Update Incremental Statistics at 50%: elapsed time = 16840 ms.
Update Incremental Statistics, Partition 3 with Resample (50% - our new update time): elapsed time = 295 ms.

We're able to sample a lot more data, perhaps setting up the optimizer to make better guesses about our data (even though it's not using partition-level statistics, yet) and we're able to do this more quickly now that we have incremental statistics.

One last fun thing to figure out, though. What about synchronous stats updates? Is the 50% sample rate preserved even when autostats kicks in?

I deleted data from partition 3 and ran a query on CreationDate and checked then checked the rates with the same query below. The 50% sample rate was preserved.

So, long story short: Incremental Statistics can be a useful tool with the right amount of thought and initial setup work. However, you must know the problem that you're trying to solve and then you need to solve for it appropriately. If you're getting bad cardinality estimates, you might be able to get better plans with a strategic sample rate and some invested intervention. However, you're only getting a small portion of the benefit since the histogram being used is the single, merged stats page and not the partition-level information. If you're feeling pain in your maintenance window, then maybe incremental statistics can help you, but it will probably require you setting up a high-touch maintenance intervention process. Regardless, keep in mind the requirements for incremental statistics:

Statistics created with indexes that are not partition-aligned with the base table.
Statistics created on AlwaysOn readable secondary databases.
Statistics created on read-only databases.
Statistics created on filtered indexes.
Statistics created on views.
Statistics created on internal tables.
Statistics created with spatial indexes or XML indexes.

Hope this helps

select
    sysdatetime(),
    schema_name = sh.name,
    table_name = t.name,
    stat_name = s.name,
    index_name = i.name,
    leading_column = index_col(quotename(sh.name)+'.'+quotename(t.name),s.stats_id,1),
    s.stats_id,
    parition_number = isnull(sp.partition_number,1),
    s.has_filter,
    s.is_incremental,
    s.auto_created,
    sp.last_updated,
    sp.rows,
    sp.rows_sampled,
    sp.unfiltered_rows,
    modification_counter = coalesce(sp.modification_counter, n1.modification_counter)
from sys.stats s
join sys.tables t
    on s.object_id = t.object_id
join sys.schemas sh
    on t.schema_id = sh.schema_id
left join sys.indexes i
    on s.object_id = i.object_id
        and s.name = i.name
cross apply sys.dm_db_stats_properties_internal(s.object_id, s.stats_id) sp
outer apply sys.dm_db_stats_properties_internal(s.object_id, s.stats_id) n1
where n1.node_id = 1
    and (
            (is_incremental = 0)
               or
            (is_incremental = 1 and sp.partition_number is not null)
         )
    and t.name = 'Posts'
    and s.name like 'st_posts%'
order by s.stats_id,isnull(sp.partition_number,1)


Bypass truncated data type error in `CREATE TABLE AS`

After much googling, I came to realize that the error message was exactly correct though somewhat vague.

Truncated incorrect DOUBLE value: '1\x00\x00\x00' meant that somewhere in my query, my CAST BINARY was being converted back to a DOUBLE data type.

Once I figured that out, re-reading the query made me consider how this little gem was working: cte.sort_path + CAST(recur.tree_user_id AS BINARY(4)). Turns out, based on a small sample query that the BINARY value was being converted so addition could be performed.

Switching to CONCAT(cte.sort_path, CAST(recur.tree_user_id AS BINARY(8))) appears to have corrected the problem.


MongoDB - Mongoose query findOneAndUpdate() doesn't update/duplicates the DB

As MongoDB Github collection documented with respect to Node.js MongoDB Driver API here Find a document and update it in one atomic operation, requires a write lock for the duration of the operation.

Syntax

findOneAndUpdate(filter, update, options, callback)

// Example of a simple findOneAndUpdate operation

var MongoClient = require('mongodb').MongoClient,
  test = require('assert');
MongoClient.connect('mongodb://localhost:27017/test', function(err, db) {
  // Get the collection
  var col = db.collection('find_one_and_update');
  col.insertMany([{a:1, b:1}], {w:1}, function(err, r) {
    test.equal(null, err);
    test.equal(1, r.result.n);

    col.findOneAndUpdate({a:1}
      , {$set: {d:1}}
      , {
            projection: {b:1, d:1}
          , sort: {a:1}
          , returnOriginal: false
          , upsert: true
        }
      , function(err, r) {
        test.equal(null, err);
        test.equal(1, r.lastErrorObject.n);
        test.equal(1, r.value.b);
        test.equal(1, r.value.d);

        db.close();
    });
  });
});

// Example of a simple findOneAndUpdate operation using a Promise.

var MongoClient = require('mongodb').MongoClient,
  test = require('assert');
MongoClient.connect('mongodb://localhost:27017/test', function(err, db) {
  // Get the collection
  var col = db.collection('find_one_and_update_with_promise');
  col.insertMany([{a:1, b:1}], {w:1}).then(function(r) {
    test.equal(1, r.result.n);

    col.findOneAndUpdate({a:1}
      , {$set: {d:1}}
      , {
            projection: {b:1, d:1}
          , sort: {a:1}
          , returnOriginal: false
          , upsert: true
        }
      ).then(function(r) {
        test.equal(1, r.lastErrorObject.n);
        test.equal(1, r.value.b);
        test.equal(1, r.value.d);

        db.close();
    });
  });
});

// Example of a simple findOneAndUpdate operation using a Generator and the co module.

var MongoClient = require('mongodb').MongoClient,
  co = require('co');
  test = require('assert');

co(function*() {
  var db = yield MongoClient.connect('mongodb://localhost:27017/test');
  // Get the collection
  var col = db.collection('find_one_and_update_with_generators');
  var r = yield col.insertMany([{a:1, b:1}], {w:1});
  test.equal(1, r.result.n);

  var r = yield col.findOneAndUpdate({a:1}
    , {$set: {d:1}}
    , {
          projection: {b:1, d:1}
        , sort: {a:1}
        , returnOriginal: false
        , upsert: true
      }
    );
  test.equal(1, r.lastErrorObject.n);
  test.equal(1, r.value.b);
  test.equal(1, r.value.d);

  db.close();
});

As Mongoosejs documented here

Query.prototype.findOneAndUpdate()
Parameters

[query] «Object|Query»
[doc] «Object»

[options] «Object»

[options.rawResult] «Boolean» if true, returns the raw result from
the MongoDB driver
[options.strict] «Boolean|String» overwrites the schema's strict mode
option
[options.multipleCastError] «Boolean» by default, mongoose only
returns the first error that occurred in casting the query. Turn on
this option to aggregate all the cast errors.
[options.lean] «Object» if truthy, mongoose will return the document
as a plain JavaScript object rather than a mongoose document. See
Query.lean().
[callback] «Function» optional params are (error, doc), unless
rawResult is used, in which case params are (error, writeOpResult)

This function triggers the following middleware.

findOneAndUpdate()

Available options

new: bool - if true, return the modified document rather than the
original. defaults to false (changed in 4.0)
upsert: bool - creates the object if it doesn't exist. defaults to
false.
fields: {Object|String} - Field selection. Equivalent to
.select(fields).findOneAndUpdate()
sort: if multiple docs are found by the conditions, sets the sort
order to choose which doc to update
maxTimeMS: puts a time limit on the query - requires mongodb >= 2.6.0
runValidators: if true, runs update validators on this command.
Update validators validate the update operation against the model's
schema.
setDefaultsOnInsert: if this and upsert are true, mongoose will apply
the defaults specified in the model's schema if a new document is
created. This option only works on MongoDB >= 2.4 because it relies
on MongoDB's $setOnInsert operator.
rawResult: if true, returns the raw result from the MongoDB driver
context (string) if set to 'query' and runValidators is on, this will
refer to the query in custom validator functions that update
validation runs. Does nothing if runValidators is false.

  Callback Signature function(error, doc) {   // error: any errors that
  occurred   // doc: the document before updates are applied if new:
  false, or after updates if new = true }

Examples

query.findOneAndUpdate(conditions, update, options, callback) // executes
query.findOneAndUpdate(conditions, update, options)  // returns Query
query.findOneAndUpdate(conditions, update, callback) // executes
query.findOneAndUpdate(conditions, update)           // returns Query
query.findOneAndUpdate(update, callback)             // returns Query
query.findOneAndUpdate(update)                       // returns Query
query.findOneAndUpdate(callback)                     // executes
query.findOneAndUpdate()                             // returns Query


Strange UTC offset time zone parsing in Postgres

Instead of using the time zone 'UTC-7' it's using 'UTC+7', which is 14 hours different.

It depends whether +/- means east or west of Greenwich, and it turns out both conventions exist. PostgreSQL doc warns about that:

http://www.postgresql.org/docs/current/static/datatype-datetime.html

Excerpt from 8.5.3. Time Zones (but you really want to read the whole paragraph):

  Another issue to keep in mind is that in POSIX time zone names,
  positive offsets are used for locations west of Greenwich. Everywhere
  else, PostgreSQL follows the ISO-8601 convention that positive
  timezone offsets are east of Greenwich.

Using ISO 8601 time zone notation also yields the opposite from the expected result:

In the same page, the accepted formats for time zone literals are listed, here's a condensed version:

  PostgreSQL allows you to specify time zones in three different forms:

  A full time zone name, for example America/New_York [...]
  A time zone abbreviation, for example PST[...]
  In addition to the timezone names and abbreviations, PostgreSQL will accept POSIX-style time zone specifications of the form STDoffset
  or STDoffsetDST, where STD is a zone abbreviation, offset is a numeric
  offset in hours west from UTC, and DST is an optional daylight-savings
  zone abbreviation,[...]

Note that it never pretends to accept an ISO 8601 time zone designator. And as you found out with -0700, it doesn't. You should just use one of the forms above.

Also beware of (false) conclusions that because the parser doesn't reject a time zone, it's valid:

  One should be wary that the POSIX-style time zone feature can lead to
  silently accepting bogus input, since there is no check on the
  reasonableness of the zone abbreviations


Which SQL standard is most broadly and completely supported?

I think you are trying to live in Joe Celko's dream world where you can only use standard SQL and in any given week you may have to port all of your code from SQL Server to Oracle and then Oracle to DB2 and then back to SQL Server again. Twice.

While the core and fundamental aspects of standard SQL will help you anywhere, trying to limit yourself to that set of the language for fear of future porting (or just on principle) is not a path I'd recommend. Personally, I stick to ANSI standard stuff when I can (e.g. <> vs. !=, COALESCE vs. ISNULL, CURRENT_TIMESTAMP vs. GETDATE(), etc.), but I'm also not afraid to use the SQL Server-specific stuff that makes my work easier.

It is important to understand how SQL works in general; it is equally important to understand how the language works in your RDBMS(es) - including deviations from the standard, deviations from the way another RDBMS might have implemented the same concept, and proprietary extensions that don't exist elsewhere.

SQL Server, as an example, covers a good portion of the standard, and it gets closer to full compliance with each new version. Will it ever cover 100%? Highly doubtful. Will it continue to add proprietary extensions not in the standard? Certainly. If everyone covered the standard and nobody stepped outside of it, then there would be no advantages to choosing one platform over another, and we'd all be using the same thing.


SSIS gets its power by being an in-memory transformation engine. The base unit of work within a data flow task is the buffer. If you ever wonder why SSIS is so persnickety about data types, it's because it calculates the cost for a row and then allocates memory for N rows. All* the downstream components use the same memory address to do their part of the ETL, which allows for parallelization. This means that the cost for a multicast with 1 output is the same as a multicast with 10 outputs. It simply allows multiple components to consume the data.

Where I'm not 100% certain on is a situation where pre-multicast, I have column called SSN. In output 1 from the MC, I rot-5 the digits in place and write to a destination and output 2 with the unaltered version, there has to be an additional memory cost associated with that but I assume it's just the space required for the column to be duplicated.

*All the downstream components use the same memory address until you introduce asynchronous components (Sort, aggregate, etc). When those hit the data flow, then that buffer ends and the data is copied from memory space 1 into memory space 2. Which sounds good from the perspective of "I just reduced the number of columns from 100 to 10" but you've fractured your total memory space between "before async component" and "after async". Instead of being able to run 100 buffers at once, you're at 30 before and 70 after and that memory copy in between is an expensive operation.

If you want to reduce memory usage, architect your packages to use less ;)

You can use less by avoiding the asynchronous transformations.
Only pull in the columns you need.
If you have a lookup, write a query explicitly enumerating the needed columns instead of clicking the table in the drop down list.
If you have a lookup, look at restricting the depth of the rows returned. If you only need to lookup current values, in the query above, make it WHERE ISCurrent = CAST(1 AS bit);
Watch your data types. Importing from a file, don't be lazy and start with strings only to convert to a data type later (unless you have questionable data cleanliness)
Watch your data types too! It's unlikely you're dealing with a binary/LOB data type coming from a flat file but those data types can't fit in memory with any certainty so SSIS will carry a pointer in memory to an address on disk. While this doesn't eat all your memory, it does slow down processing as many tiny files are created and destroyed during the operation


Disk Separation DB installation and data files

Why do we separate things onto different disks?
Performance and resilience.

From the Disaster Recovery perspective, if you lose any one of your disks, how much damage does it do?

Lose the operating system disk?  OK, that's a biggy. :-(

Lose the backup disk?  Non-event; you still have a running database instance.

Lose the data disk?  Ouch!  But that's OK; you've got your backups ...
You just need to restore them into a running SQLServer instance ...

Ah.

But you had that on the same [data] disk that just failed, taking the data with it!

Keep the installation and data separate.

Whilst it's only semantics, I'd suggest keeping a closer "association" between the Data and Logs disks; they make up all the stuff you really care about.

Move the backups off to another server altogether.  (Whilst it's unlikely these days) If all these disks are local to the machine and the machine's motherboard were to fail, it wouldn't matter how many disks you had; they'd all be gone!


User creation and rights to import a dump

The error you receive means that the user ANC does not have any quota on the TOOLS tablespace, as FreshGrinchOfSO says.
From your code, it seems that your default permanent tablespace is TOOLS (and not USERS as usual).

SELECT * FROM database_properties WHERE property_name LIKE 'DEFAULT%TABLESPACE';

PROPERTY_NAME                  PROPERTY_VALUE       DESCRIPTION
------------------------------ -------------------- ----------------------------------------
DEFAULT_TEMP_TABLESPACE        TEMP                 Name of default temporary tablespace
DEFAULT_PERMANENT_TABLESPACE   USERS                Name of default permanent tablespace

Otherwise, you could create the user ANC just like this way:

CREATE USER anc IDENTIFIED BY anc
       DEFAULT TABLESPACE tools;

To verify the quota of ANC on TOOLS use this query:

SELECT tablespace_name, username, bytes, max_bytes FROM dba_ts_quotas;

You will obtain "no rows selected".
To assign a quota to ANC on TOOLS you can proceed as follows:

expdp histo/passwd estimate_only=y NOLOGFILE=y
..
Total estimation using BLOCKS method: 1.256 KB
..
ALTER USER anc QUOTA 5m on tools; -- a x3, safety margin :-)

Then perform the import:

impdp anc/anc directory=MIG11G dumpfile=dump_histo.expdp schemas=histo

If the job seems to be suspended, check for errors in the alertlog file. For example:

statement in resumable session 'ANC.SYS_IMPORT_SCHEMA_01' was suspended due to
    ORA-01536: space quota exceeded for tablespace 'TOOLS'

Then, it is necessary to increase the quota, than the job will resume automatically.

statement in resumable session 'ANC.SYS_IMPORT_SCHEMA_01' was resumed

To see all current jobs:

sqlplus / as sysdba
SQL> SELECT job_name, state FROM dba_datapump_jobs;


You could add a column significance to your crimetypes table, with increasing values for the crimes (ie. 1 for INFRACTION,2 for 'MISDEMEANOR', 3 for 'FELONY', etc.)

Then your query could be written as:

CREATE VIEW person_assessment AS
SELECT p.name,
       CASE COALESCE(MAX(c.significance), 0)
           WHEN 0 THEN 'exemplary'
           WHEN 1 THEN 'law-abiding'
           WHEN 2 THEN 'relatively law-abiding'
           WHEN 3 THEN CASE WHEN COUNT(CASE WHEN c.significance >= 3 THEN 1 END) <= 4
                                THEN 'felon'
                                ELSE 'career criminal'
                       END
                  ELSE 'unhandled case'
       END AS citizen_type
FROM person AS p
  LEFT JOIN infraction AS i
    ON i.person = p.name
  LEFT JOIN crimetypes AS c
    ON i.crimetype = p.crimetype
GROUP BY p.name ;

This is more of a way to have the query "tidied up", readable and easy to change in the future.

It may not be as efficient as your query though, depends on the data. If no person in the database has any crime stored, only the first WHEN of your case has to be run. If on the other side, most persons in the database have felonies, several correlated subqueries will have to be run so the above strategy will probably be more efficient as it uses only a join and a GROUP BY.

About question 2, you can always join this view to another table with descriptions.

SELECT
    ....
FROM person_assessment AS pa
  JOIN citizen_types AS ct
    ON ct. citizen_type = pa. citizen_type ;


PostgreSQL 9.3: Aggregate collection of JSON arrays

You can use json_each and LEFT JOIN LATERAL for this purpose:

WITH inventory AS (
  SELECT '{
  "category": {
    "item": ["price", "quantity"]
  },
  "widgets": {
    "foo": [300, 15],
    "bar": [500, 5],
    "baz": [400, 10]
  }
}'::text AS value
  )
SELECT
  v.key,
  (v.value->>0)::INT AS price
, (v.value->>1)::INT AS quantity
FROM inventory i
LEFT JOIN LATERAL json_each(i.value::JSON->$1) v ON (true)
WHERE as_of_date = $3::DATE;

You can then filter, group by and sum as you need.


Point in time recovery Oracle 12c with TDPO

As the error message already states, this is not an Oracle issue. The backup piece simply can not be found on the TSM server where Oracle searches - based on the tdpo configuration.

Maybe it was created with a different tdpo configuration under a different node name or in another filespace. Maybe it is simply not there on the TSM server.

You can query the contents of TSM for this tdpo config with:

tdposync query -tdpo_optfile=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt

Most likely you will not find DB_954446866_1042_1 in the list, and that is the problem. Ask the TSM administrators to search for DB_954446866_1042_1 in their catalog. If they find it in a different filespace, or registered under a different nodename, you can restore it by collaborating with them and using the correct tdpo configuration. If they can not find it, you are out of luck as that backup simply does not exist on TSM.


MySQL DB import/export command line in Windows

Use this for export:

mysqldump -u username -p databasename > filename.sql

this is my Sample Export in CMD and i'll save in drive D:\

mysqldump -u root -pmypassword pos > d:\pos.sql


UDF in MS SQL Server returning a query starting with "with" cause

you are mixing procedure and function

for UDF, your syntax should be
CREATE or alter function x (@__employee_id uniqueidentifier)
returns table
AS
RETURN (
with t as (select 'a' as attr)
select * from t
);


Best way to back up databases on a Microsoft SQL Server Availability Group DR Site node

You can use some system dynamic management views (DMVs) and functions in your backup script, for example:

sys.dm_hadr_availability_replica_states
sys.fn_hadr_backup_is_preferred_replica
sys.availability_databases_cluster
sys.dm_database_encryption_keys

to determine the current node's assignment (primary, secondary, etc) and direct where the full/diff/t-log backups should be coming from. If you use that in conjunction with a file share (as @Steve mentioned), then all backups can be pointed to the same target. You can get creative from there (eg, copying the backup files from the share to another site) to bolster for DR purposes, etc.
You may also glean some good script setup insights from Ola Hallengren's backup scripts (https://ola.hallengren.com) which take AGs into consideration.
Hopefully that helps some.


Most likely /etc/my.cnf or /etc/mysql/my.cnf.  Also check /home/username/my.cnf and /home/username/.mysql/my.cnf

If no luck there you can run

find / -type f -name my.cnf

That will scan you're entire disk where non priviledged access is allowed (which is fine; it should find it, i'm assuming your distro came with it and you're able to connect or you wouldn't be asking this much; but it will tie your disk up)


How to read metadata from Sqlite database

The equivalent of, for instance, MySQL describe t1; is

PRAGMA TABLE_INFO(t1);

and

SELECT * FROM sqlite_master WHERE tbl_name = 't1';

is also useful. Most of the time, however, I simply use

.schema t1

which outputs the SQL definition of the table or view, even with the original comments (exactly the same as SELECT sql FROM sqlite_master WHERE..., with less typing. .sch works too.)


i've found the definitive answer from the oracle docs

http://docs.oracle.com/cd/E11882_01/license.112/e47877/editions.htm#DBLIC109

in the Oracle Database Standard Edition and Oracle Real Application Clusters (Oracle RAC) section, it specifies

"Oracle Automatic Storage Management is required for creating and managing all Oracle database file types. Raw volumes, partitions, or third-party cluster file systems are not supported for storing Oracle database files with Oracle Standard Edition and Oracle RAC"


Need demo scripts and sample corrupted databases for corruption databases troubleshoot

Check out the work by Steve Stedman. He has corruption examples for days, http://stevestedman.com/?s=corruption&submit=Go


Changing a materialized view column's length after a change in the base table?

You have to drop and recreate.

If you look at the documentation here, you can see that only modification of the storage clauses are allowed.


Tempdb files are recreated each time the sql server starts.  After you change their location using ALTER DATABASE, you can verify your change by checking the dmv tempdb.sys.database_files.  (not sys.master_files, which won't be updated until sql restarts).

Sql will create new files for tempdb, and will leave the old ones on disk, so you should delete them.


Most likely activity on the server over the weekend has caused your data to be evicted from memory. This is just the way life is in a shared environment with limited resources. It would be good to understand what's happening over the weekend and how your usage affect other users of this service.

You could schedule a job for early on Monday, before anyone is in the office.  It would run your SPs with the various parameter values to warm up the bufferpool with the data you will be using.  This will evict whatever is in memory currently, slowing the process that is using it.


How do I change all Oracle profiles PASSWORD_LIFE_TIME to UNLIMITED?

You can simply do it in PL/SQL in a loop:

begin
  for c in (select distinct profile from dba_profiles)
  loop
    execute immediate 'alter profile ' || c.profile || ' limit password_life_time unlimited';
  end loop;
end;
/


MySQL slow query log to identify queries that scan tables

I find log_queries_not_using_indexes to be useless.  More useful is the queries that take a long time; this will include any queries that do some or all table scans and are slow, but exclude trivial table scans that are fast.

If a table has 5 rows, it does not really matter whether it uses an index or does a table scan -- the speed will be virtually the same.

These start with the most important queries to work on.  I suggest you work on the first 3, then wait for the impact to happen and get new slowlog:

mysqldumpslow -s t   # to sort by most impact first
pt-query-digest

Also, long_query_time=1 is probably a good starting point.

(I keep my ear to the ground; I have not heard of the option you suggest.  You could file a "feature request" with bugs.mysql.com.)

More

The Optimizer sometimes decides that a table scan is likely to be faster than using an index.  This happens when it estimates that more than 20% of the rows will be touched.  (The "20" varies with the phases of the moon.)  This is because of bouncing between the BTree of a secondary index and the data BTree has some cost.


How to implement Ola Hallengren backup plan on the server for some databases nightly full backups and for other weekly full backup only

No you don't need to install the Ola's solution again since the initially installed commandexecute.sql and databasebackup.sql would have been saved on master DB as SP.

You just need to create a new job , as one created early but this time for 5 left over DB;s and schedule it weekly per you're timings.

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',-- Name of those 5 DB's would come here
@Directory = 'C:\Backup',-- You're backup path
@BackupType = 'FULL',
@Verify = 'Y',
@Compress = 'Y',
@CheckSum = 'Y'

You can use above script while creating the T-SQL job and schedule it accordingly.

More details on how to do above refer to schedule job

Also, just to make sure both, initial backup and this newly created backup job not to run on same time you can put a condition or per the analysis when you're daily backup job completes, you can schedule this new weekly/nightly backup job for remaining 5 DB's


I know this question is a year old but thought I would post an answer considering there is no real answer to this that I have found. In this answer, for the remote server commands, I'm assuming you're using a Linux Debian-based OS. Otherwise there is another option at the end.

This sounds like an issue in the storage engine "ibdata1" file in your /var/lib/mysql/ folder. It can happen if the tables aren't copied correctly. So the storage engine can't present the database correctly even though the tables are there.

The best way to resolve this issue is to do a mysqldump on the healthy database on the remote server and restore the tables back into your local database server and vice versa.  So, if you have root or sudo access on your remote server and your mysql password you can use:

$ mysqldump -u root -p *database-name* > *sqlbackupname*.sql
$ Enter mysql password: *your-mysql-password*

This will dump your database into a .sql backup. Then to restore the database on your localhost you can run this in your linux terminal:

$ mysql -u root -p *database-you-want-to-restore-to* < *sqlbackupname*.sql

So you're going to need to have a database set up ready to accept the information into it and then the information in the mysqldump will be restored to that database. The mysqldump will look for a database to dump into.

If you DO NOT have server access and you're only using phpmyadmin, you'd be best exporting the healthy database from your remote server into a .sql file. So go to your healthy database, then click the "export" button at the top. That will give you some options and  download onto your computer. Then you can "import" the file back into your local install of phpmyadmin. This option should work on any operating system as long as you're using phpmyadmin.

I hope this works for someone who is searching for the answer to this issue.


Is it generally faster to select into a temp table than selecting into an actual table?

writing to tempdb is faster than an actual table not in tempdb

It's true.  There are two IO enhancements in TempDb.

Writes to a table in a user database must have their log records flushed to disk on commit, or if a minimally-logged insert (like SELECT ... INTO), must have the database pages flushed to disk on commit.  The way the minimal logging works in a user database is that the database pages are written directly to disk.  By the time the SELECT ... INTO completes, the new pages have all been written to the physical files.

TempDb writes don't have to be flushed to disk on commit since TempDb is never recovered after a failure.  So they simply aren't.  Your changes generate log records, but the log buffer is flushed to disk only when it's full, not for every commit.

And since SQL Server 2014 the minimally-logged inserts in TempDb aren't always written to disk either.  If you load a small, short-lived temp table it may never be written to disk at all.  The log will have a few records about the page allocations and metadata entries for the table, but that's it.

EG run the following batch in tempdb, a full recovery database and a simple recovery database to see the differences.

drop table if exists foo
go
declare @data bigint
declare @log bigint
select @log = sum(case when type_desc = 'LOG' then num_of_bytes_written end)
      ,@data =  sum(case when type_desc = 'ROWS' then num_of_bytes_written end)
from sys.database_files f
cross apply sys.dm_io_virtual_file_stats(db_id(),f.file_id) fs

select *
into foo
from sys.objects

select -@log + sum(case when type_desc = 'LOG' then num_of_bytes_written end)  log_bytes
      ,-@data +  sum(case when type_desc = 'ROWS' then num_of_bytes_written end) data_bytes
      , (select recovery_model_desc from sys.databases where database_id = db_id()) recovery_model
from sys.database_files f
cross apply sys.dm_io_virtual_file_stats(db_id(),f.file_id) fs

and you'll see something like:

For simple recovery:

log_bytes            data_bytes           recovery_model
-------------------- -------------------- ---------------
24576                16384                SIMPLE

for full recovery:

log_bytes            data_bytes           recovery_model
-------------------- -------------------- ---------------
36864                0                    FULL

and for tempdb:

log_bytes            data_bytes           recovery_model
-------------------- -------------------- ---------------
0                    0                    SIMPLE

Sometimes for tempdb you'll see the log buffer flushed:

log_bytes            data_bytes           recovery_model
-------------------- -------------------- ---------------
61440                0                    SIMPLE


Move data from 4 shard, replicated mongodb cluster to a single server without downtime

In your scenario there is a good discussion on this already on the Mongo Jira server on unsharding a database:
https://jira.mongodb.org/browse/SERVER-9845

Test and stage these steps using your real data in a non prod environment repeatedly until you are confident and find one that works:

database = 'test'
collection = database + '.fs.chunks'

sh.stopBalancer()
use config
primary = db.databases.findOne({_id: database}).primary

// move all chunks to primary
db.chunks.find({ns: collection, shard: {$ne: primary}}).forEach(function(chunk){
    print('moving chunk from', chunk.shard, 'to', primary, '::', tojson(chunk.min), '-->', tojson(chunk.max));
    sh.moveChunk(collection, chunk.min, primary);
});

// unshard
db.collections.remove({ "_id" : "test.fs.chunks" })
db.chunks.remove({ ns : collection })

// flush all mongos, is this neccessary?!
use admin
db.runCommand({ flushRouterConfig: 1 })

Note now you have unsharded your data.  If you don't want to just directly unshard it before sending it over to the new cluster, then setup the new cluster sharded, and after it's synched then unshard it.


Access is denied for SQLDumper.exe in MS SQL Server

Try running CMD using administrative Privileges.

Start->CMD->right Click on it and click run as administrator


Database backup - Use case of FORMAT and INIT

FORMAT recreates the header from scratch, effectively completely obliterating the backup file, or tape.  If the backup file or tape is corrupted, this option ignores the corruption, and enables you to reuse the file or tape.  From the Microsoft Docs:

FORMAT specifies that a new media set be created. FORMAT causes the backup operation to write a new media header on all media volumes used for the backup operation. The existing contents of the volume become invalid, because any existing media header and backup sets are overwritten.

INIT rewrites field values in the header info.  If the device is corrupt, this may not correct the issue.  Also from the Microsoft Docs:

Specifies that all backup sets should be overwritten, but preserves the media header. If INIT is specified, any existing backup set on that device is overwritten, if conditions permit. By default, BACKUP checks for the following conditions and does not overwrite the backup media if either condition exists:

Any backup set has not yet expired. For more information, see the EXPIREDATE and RETAINDAYS options.
The backup set name given in the BACKUP statement, if provided, does not match the name on the backup media. For more information, see the NAME option, earlier in this section.


Which is more efficient: contradict a unique constraint or check if row exists?

You need the unique index (or constraint, I prefer indexes because of includes and filters) anyway to guarantee data integrity.

So the question is one of

do check-as-part-of-insert? OR
try insert-and-catch?

Note, checking before insert is not reliable, for at least these reasons

if someone does it manually
assumes you have a consistent code path to insert
2 concurrent IF checks can potentially both see that the row does not exist and try to insert the same value

Using INSERT...WHERE NOT EXISTS (As mentioned in comments) will use the index, so will be efficient anyway.

There is no benefit in dealing with exceptions unless you have a real need to.


What is the cost of having many columns as included columns in a nonclustred index?

The more columns you include to index the larger this index becomes. It affects all operations with this index (inserts, updates, selects). It takes more space in buffer pool and when you use index more data needs to be processed (including maintenance tasks like integrity checks and backups). Also when you update column included to this index SQL Server needs to update this index too (it means more reads, writes and even more locks).


How can a primary key be inserted as foreign key?

If you insert multiple Order records (pick a new name that's not a keyword, by the way) then you need to use the OUTPUT clause.  Otherwise SCOPE_IDENTITY() should work.

Quick example using SCOPE_IDENTITY:

DECLARE @OrderID int

INSERT INTO
  Orders
   (<columns>
VALUES
 (....)

SET @OrderID = SCOPE_IDENTITY()

INSERT INTO
  Properties
   (OrderID, ...)
VALUES
  (@OrderID, ...)


Does MS Access have anything equivalent to triggers?

If the ID is simply a number look intot the autonumber feature in Access.


Post installation of SSIS for existing SQL Server instance with different build versions

I have answer for this question from member of Microsoft Technet called Lydia Zhang:

  ...SSIS will inherit the patch level that your installation media contains, which has no impact on the patch level of your existing SQL Server 2014 failover cluster instance...
  https://social.technet.microsoft.com/Forums/en-US/83f5eef4-bf7a-439e-8e6c-a3124434b12d/post-installation-of-ssis-for-existing-mssql-instance-with-different-build-versions?forum=sqlsetupandupgrade

So my question is resolved.


To expand on Dan's answer with some detailed info:

If you have the option setup a small Linux server at home. You might have to modify the sshdconfig to allow for setting up SSH tunnels depending on your release. You might also need to modify the firewall config to allow port 22 connections, though most distributions do this automatically now when you install openssh.

You can use a dynamic DNS service in case your home IP address changes and you can still access remotely. For that I actually prefer to use freedns.afraid.org and setup a sub-domain off of a publicly available domain they host. Then added a cron job on my Linux server to run once per hour and hit a special link that keeps the IP for that sub-domain updated. Many home WiFi routers actually support one or more dynamic DNS services directly within the config.

You'll need to setup a port forward on your home router as well. Setup an external connection port forwarded to your Linux server's IP address on port 22. The specifics of doing this is highly dependent on which router you're using.

IMPORTANT
Whatever you do don't setup the port forward to expose port 22 externally. Hackers/Script-Kiddies constantly scan for port 22. When they find it they'll start trying to hack root's password. You'll notice this happening if you look in /var/log/secure and see the file is huge. I did this once and within just a few hours I saw connections and hack attempts already occurring.

So use a non-standard port (like 5828 or something else equally as random). Just keep it above 1024 and below 32767.

On Windows a very good SSH client is PuTTY (www.chiark.greenend.org.uk/~sgtatham/putty). In the connection setup put your internet hostname (from either the dynamic DNS service you chose or through the freedns service I mentioned). Set your port to the one you chose in your port forward.

Then go to the SSH/Tunnels section.
On that screen leave the top two check boxes unchecked (no need to allow remote connections).
Enter a source port (3306 is fine if you don't have MySQL installed on your client machine).
For the Destination enter "gcs-host:3306".  "gcs-host" meaning the Google Cloud Services hostname.
Then for the two radio button options below that, Local and Auto.

Save your connection info and connect.

Now you should be able to start MySQL workbench and use "localhost:3306" as the server name.


Advisable number of foreign keys in a single table

Yes, ultimately FKs can affect performance. They will certainly affect insert/delete performance (more lookups), but you have the advantage of guaranteed data integrity.

You should be more than happy to pay a high price for data integrity - a high performance database with inconsistent data is worse than useless - certainly not as much use as a database that's a bit slow but with consistent data.

Yah gets what yah pays for!


What are the best practices for ETL of data from one OLTP system to another?

Use the tools that you are most comfortable with. Your situation seems simple and lasts only for a short time. If you have to keep this up and running for a long time, you might end up by using more advanced tooling, where you define rules for conversions and synchronizations to be performed for you. Doing so is easier to maintain and to document. The learning curve might be a little steep.
On the other hand, you could use this case to train yourself. There is no urgent deadline and when it gets more urgent, you probably build the home cooked version within a day.


It's hard to say which is "better" -- Percona's PXC or MariaDB w/Galera.  Both use Galera as the underlying "cluster" technology and HA.  Percona is based on Oracle's 5.6, MariaDB is slightly forked back around 5.5 timeframe.  But both are great.

In any case, you really need 3 servers to get HA.  The 3rd is the tie-breaker in case one goes down.

Furthermore, if HA is a big priority, the think about tornadoes, earthquakes, floods, etc.  The 3 machines should not be in the same geographic location.

Bottom line:  Flip a coin for picking between them.  (I'm eager to hear from those who have a strong bias, but if you do, please state whether you have ties to one or the other.)

Don't use NDB Cluster -- it is too far from the main path of things.  There is a lot of setup required, and it has a bunch of limitations.


copying data from a SQL server-table to a SQLCE-table

After reading the comment, I would like to suggest that you can use SQL Server Compact bulk copy library

Check sqlcebulkcopy.codeplex.com
Or you can use ErikEJ’s blog post to migrate and the utility SQLCMD.
Or create Integration Services packages that write data to SQL Server Compact databases check link


Percona/XtraDB Database with many of writes to a small table uses less CPU than the same database with less writes?

In all versions (Percona, XtraDB, MariaDB) of InnoDB, writes and index updates are delayed.  A separate thread(s) takes care of doing the writes 'eventually' and in the 'background'.

Data writes are put into the desired blocks immediately, but not written until they get bumped out of the buffer_pool.

UNIQUE indexes are updated immediately but left cached in the buffer_pool (like data).

Non-unique indexes are stored in the "Change buffer".  Later they are collected together for putting into the actual index blocks and eventually written to disk.

Redo log and double-write writes are done more immediately.

There are many settings relating to the frequency of background actions, sizes of caches, etc.  These have an impact on both CPU and I/O.  Normally it is best to leave them alone.

To further address your question, please provide the RAM size of the servers being tested and a diff of the SHOW VARIABLES.  The diff may help zoom in on the cause of the difference you are seeing.


VBA Maximize Access Application Main Window

I've spend a while battling with this problem too, reading various blogs and posts. I've found a very simple way to achieve the desired outcome in VBA using just one line of code. In the VBA editor, place this code AFTER your On Load code:

[Your On Load code]

KeyCode = 16

This code achieves the equivalent of pressing F11, ie maximising the view of the currently open window.


ORA-01950 error during the execution of insert statement

This is already asked on ORA-01950: no privileges on tablespace 'USERS' [closed]. It was answered as:

You cannot insert data because you have a quota of 0 on the tablespace. To fix this, run

ALTER USER <user> quota unlimited on <tablespace name>;

or

ALTER USER <user> quota 100M on <tablespace name>;

as a DBA user (depending on how much space you need / want to grant).


As @JNK mentioned, you can use a view.  There are some steps to take, of course.  The view should map only to columns in a single table.

I assume that you do not want to deploy completely updated code that uses the new name.  So, you can create a view with a different (but similar) name and any new code could use the view, and the new name. Existing code would still be going directly to the table.

This would get you moving, but one day you will probably want everthing to use the new column name.  Eventually, you will have to deploy new code.  Which would still mean a number of name changes if you don't want to have the view layer over the table.


Create job to send alert using sp_send_dbmail when table value greater than x

You can use convert (date, GETDATE())  to get just the 'date' part:

select GETDATE(), convert (date, GETDATE())
----------------------- ----------
2012-11-16 08:58:20.750 2012-11-16

And if you're only ever going to be comparing 'date' parts, it might be worth changing the datatype in your table to DATE, to save having to convert each time you query it.

For an IF statement you need to pass in a value to be checked, and then wrap the code to be run within BEGIN and END statements. Here's an example using the sysobjects table:

declare @cnt integer
select @cnt=COUNT(1) from sysobjects

if @cnt>50
begin
  select 'hello'
end

which will say hello if you've more than 50 objects in your DB

So in your case:

USE DATABASEname
go
declare @counter integer

select @counter=MAX([Counter]) from SMSCounter where convert(date,CounterDate) = convert(date,GETDATE())

IF @counter > 950
BEGIN
  EXEC msdb.dbo.sp_send_dbmail
  @recipients=N'scott@mydomain.com',
  @body='1000 Daily SMS cap near',
  @subject ='1000 Daily SMS cap near',
  @profile_name ='SCOTT',
  @query =  '(select * from DATABASEname.dbo.SMSCounter where convert(date,CounterDate) = convert)(Date,GETDATE()))'
END

I've added a MAX function to your check query, as this will only return a single row containing the maximum value for that day, otherwise it'll return a random value from all the matching rows. If you're overwriting the day's value each time you write to the table, then it's superfluous.


SELECT B.contractid,B.startdate,B.day
FROM
    (SELECT MAX(startdate) startdate,contractid
    FROM contractupdate GROUP BY contractid) A
    INNER JOIN
    contractupdate B USING (contractid,startdate)
;


Is there some way to store EITHER a specific day (date) OR a year in the same column?

I would model this thusly:
CREATE TABLE ... (
   birthday date NOT NULL,
   only_year boolean DEFAULT FALSE NOT NULL,
   ...
);

The second column indicates if only the year of the data is relevant or not.


as it will have three table

one table for Owner,  Identity Auto-Increment by 1 will give OwnerId
one table for gizmos, Identity Auto-Increment by 1 will give gizmosId
One table for mapping the many to many relationship as

OwnerId - FK
gizmosId - FK and
 Other columns

Now as the combination of OwnerId and gizmosId will make a row unique you can create primary key constraints that will enforce Uniqueness as well.

ALTER TABLE schemaName.TableName
ADD CONSTRAINT PK_Relation PRIMARY KEY CLUSTERED(OwnerId , gizmosId);


How do I organize tables for simple lottery game? mysql

I'd create another table to store the number range each week (store the date and range) and then another table to store the user's selection (store the user id, date, and chosen number).


Automatically add new dbo objects in SQL Publication

I was kinda intruiged by your question, since we use transactional replication a lot. So I tried to come with a script to answer your question (run inside the publication db):

-- your publication name
DECLARE @PubName SYSNAME = 'publication name'

-- your subscriber
DECLARE @subscriber SYSNAME = 'target server name'
DECLARE @subscriberDatabase SYSNAME = 'target db name'

 -- Get publication id
DECLARE @PubId INT
SELECT @PubId = pubid
FROM   syspublications
WHERE  name = @PubName

-- Find tables in dbo schema, not in current publication
DECLARE tablecursor CURSOR fast_forward FOR
  SELECT TOP(2) o.name  -- for testing I used a TOP clause
  FROM   sys.objects o
  WHERE  type = 'U'
         AND schema_id = SCHEMA_ID('dbo')
         AND object_id NOT IN (
            SELECT objid
            FROM   sysarticles
            WHERE  pubid = @PubId
         )

DECLARE @tablename AS sysname

OPEN tablecursor

FETCH next FROM tablecursor INTO @tablename

WHILE @@FETCH_STATUS = 0 BEGIN
    -- Adding the transactional article
    EXEC sp_addarticle
            @publication = @PubName
        ,@article = @tablename
        ,@source_owner = N'dbo'
        ,@source_object = @tablename
        ,@type = N'logbased'
        ,@schema_option = 0x08030F3--0x80030F3
        ,@vertical_partition = N'true'
    -- All columns
    EXEC sp_articlecolumn
        @publication = @PubName,
        @article = @tablename;

    FETCH next FROM tablecursor INTO @tablename
END

CLOSE tablecursor

DEALLOCATE tablecursor

EXEC sp_dropsubscription
  @publication = @PubName,
  @article = N'all',
  @subscriber = @subscriber;

exec sp_addsubscription
     @publication = @PubName
    ,@subscriber = @subscriber
    ,@destination_db = @subscriberDatabase
    ,@subscription_type = N'Push'
    ,@sync_type = N'automatic'
    ,@article = N'ALL', @update_mode = N'read only'
    ,@subscriber_type = 0

exec sp_addpushsubscription_agent
    @publication = @PubName
    ,@subscriber = @subscriber
    ,@subscriber_db = @subscriberDatabase
    ,@job_login = null
    ,@job_password = null
    ,@subscriber_security_mode = 0
    ,@subscriber_login = N'some login'
    ,@subscriber_password = 'some (matching:)) password'
    ,@frequency_type = 64
    ,@frequency_interval = 1
    ,@frequency_relative_interval = 1
    ,@frequency_recurrence_factor = 0
    ,@frequency_subday = 4
    ,@frequency_subday_interval = 5
    ,@active_start_time_of_day = 0
    ,@active_end_time_of_day = 235959
    ,@active_start_date = 0
    ,@active_end_date = 0
    ,@dts_package_location = N'Distributor'

-- Re init subscription(s)
--EXEC sp_reinitsubscription
--   @publication = @PubName
--  ,@subscriber =  @subscriber
--  ,@invalidate_snapshot = 1

-- Start snapshot agent
EXEC sp_startpublication_snapshot @publication = @PubName

Now I found out the hard way, there are some things that can go wrong:

First of all, its obvious to restart the snapshot agent to pick up new articles. However, on my test machine the snapshot agent didn't pick up newly added articles. I did some googling and found simular issues (Snapshot not picking up new articles in SQL Server 2012 transaction replication when added through script). I ended up with dropping and re adding the subscription.
The @schema_option parameter for sp_addarticle is a bit of obscure to me. I used some tables that had timestamp columns and these were propagated to the subscriber as BINARY(8) and the rows could not be inserted at the subscriber ("Field Size too large"). See https://social.msdn.microsoft.com/Forums/sqlserver/en-US/b56433c1-7f6f-4d2c-9593-3a11035fb1fe/cannot-update-a-timestamp-column-on-subscriber-using-transactional-replication?forum=sqlreplication and https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-addarticle-transact-sql?view=sql-server-ver15


I would venture that your best approach is to use separate statements for each of the potential actions, and put them in a serializable transaction. You get to use tried and true statements with no funny semantics or "best practices" violations, and you get to avoid all of the problems I outline in this post, including wrong results bugs and potential index corruption:

MERGE with Caution


How can I set a constant number as the lowest number in a column?

SET reputation =
    GREATEST(1,
        CAST(reputation AS SIGNED) - ( ... )
            )


With a bit of help I have solved this, but thought I would post the answer in case it helps someone else.

I have changed the expression to read:

=Fields!txtLabelSalutation.Value & vbCRLF & Fields!txtAddress1.Value & vbCRLF &
    IIF(
            Fields!txtAddress2.Value <> "", Fields!txtAddress2.Value & vbCRLF, nothing) &
    IIF (
            Fields!txtAddress3.Value <> "", Fields!txtAddress3.Value & vbCRLF, nothing) &
    IIF(
            Fields!txtTown.Value <> "", Fields!txtTown.Value & vbCRLF, nothing) &
    IIF(
            Fields!txtCounty.Value <> "", Fields!txtCounty.Value & vbCRLF, nothing) &
    IIF(
            Fields!txtCountry.Value <> "United Kingdom", Fields!txtCountry.Value & vbCRLF, nothing) &
    IIF(
            Fields!txtPostcode.Value <> "", Fields!txtPostcode.Value & vbCRLF, nothing)

My problem was where I was including the line break within the statement.


Easier way to handle so many isnull() situation

No, there is no way to tell SQL Server to treat all NULL float values as zero. You will have to surround these expressions with ISNULL() or, better yet IMHO, COALESCE(). You can do this in a view so you don't have to repeat it in every query.


SQL Replication - Specify Schema on destination Table

Yes, this approach is feasible; it just requires the right setup.  I'm going to assume you'll be setting up replication via the wizard because any other way is basically like trying to navigate your way out of a series of caverns with a terrible flashlight.  This answer is a bit wordy and light on instructions because it combines links to a few walkthroughs that are just better documented.  I'm only going to draw attention to the key items you need to adjust in order to achieve your desired setup.

First, you must define publications at each individual server.  When specifying your articles in each publication, you need to be on the lookout for the Destination Object Owner setting.  You can specify a new schema for your articles to get pushed to.  I assume you'll want this to be different for each publication.

The above screenshot was taken from a SQL Server Central Walkthrough on how to set this up within the publication, which can be found here.

After you have completed configuring your publications and distributor, you now setup what I've heard referred to as a central subscriber.  To do this, you basically setup subscriptions to point to the same subscriber database.  This SQL Server Central Article walks through the process, but because you've setup your publications to remap the tables to different schemas (unlike the article), I believe you can initialize each publication via snapshot, which should hopefully save you some time on initial setup and anytime down the line you need to reset the subscription.  It's only when you are combining tables within the subscriber that one publisher snapshot will overwrite the data from a different publisher's snapshot.  I don't believe this will occur in your setup, but test to be certain.


Multi-statement Table Valued Function causing massive PAGELATCH_EX waits

Since you are on SQL Server 2017 CU2 - you might be hitting this bug - Heavy tempdb contention occurs in SQL Server 2016 or 2017  .

Update to atleast CU5 since CU5 has fixed above bug.

I would recommend CU12 (latest).


Procedure with "bind varibale" does not return the desired result set

order by 2 does not equal to order by :ord where :ord = 2.
With order by 2, 2 represents a position, meaning the result must be sorted always by 2nd column in select list.
With order by :ord, :ord is an expression, which will be evaluated to a value, and sorting will be done by that value, not a column.
If you use order by :ord where :ord = 2, that is handled like like order by '2' (logically, ignore the type difference for now).
Do not bind the order by position.


SQL Server to MySQL migration - how can I remove UCS-2 surrogate pairs in SQL Server?

You need to take the data from UTF-8 and convert it into UCS-2LE using something like iconv. For example, using the character in your example:

echo "010000: dcb3" | xxd -r -s -0x10000 | iconv -f "UTF-8" -t "UCS-2LE" | xxd
0000000: 3307

Now I'm not sure what character UTF-8 \xdcb3 is, but apparently it's correct translation to UCS-2LE is \U0733. If you have \0xDCB3 in the SQL Server it means it was not translated into UCS-2LE before import. You should not have surrogates in the NVARCHAR fields, UCS-2 is "surrogate agnostic". See UCS-2 vs. UTF-16 (not quite Kramer vs. Kramer).

I'm not an expert in the MySQL tool set so I can't say what step is missing that was supposed to do the iconv.

Update

to locate the records with surrogates you must turn to the binary representation, since any character function will threat the surrogates as 'special':. Luckily the string manipulation functions work on binary too with the expected semantics. Eg. CHARINDEX:

insert into test(a) values  (N'a');
insert into test(a) values  (NCHAR(0xdc83));
insert into test(a) values  (N'b');
go

select * from test where charindex(0x83dc, cast(a as varbinary(8000))) > 0;


What is the PRIMARY KEY of the table?  Probably commentID is unique to each row??  If so, add PRIMARY KEY(commentID).

For WHERE postID = "...", you should add INDEX(postID).  That will make that query fast even for a billion rows.

Read about "indexes" (aka, "keys").


Why does adding ORDER BY makes the select much faster?

What was the IP?  1.1.1.1 would do best by starting from the beginning.  255.255.255.255 would work better by starting at the end.

The EXPLAIN does not say whether the "range" was scanned ascending or descending.  I don't even think EXPLAIN FORMAT=JSON SELECT ... would point this out.

So... I think your fast one had a high IP address and did start from the end due to the ORDER BY.

More discussion of the intractability of queries like this, plus a solution if the ranges are non-overlapping.


Yes, the problem seems to be the condition that involves the CashInvoice table.
It should be moved from WHERE to ON. Besides that, I'd make a few more changes to the query, to make it more readable:

Refactor using aliases. Makes the queries shorter and easier to read.
Add comments in your code.
Format the queries nicely. No one likes to read code that is 300 characters wide.
Use LEFT JOIN instead of RIGHT JOIN. This is rather a stylistic matter but RIGHT joins are confusing to many. Even worse, if a query combines LEFT and RIGHT joins. I suggest you use one or the other everywhere, with preference to LEFT. This way, the "base" table(s) can be put always first, after the FROM and the "optional" tables in the end. Makes it easier for the future reader to identify which is which, especially when the query has many joins.
Add a statement terminator (;) at the end of every statement. Microsoft recommends that and in future versions non-terminated queries will be throwing errors (some already do).
Adding the WHERE to ON move of the condition,

the query becomes:

SELECT
    i  . InvoicePaymentId,
    i  . PaymentDate,
    i  . InvoicePaymentMethod,
    i  . IsCashPayment,
    ci . CashAmount,
    ci . CashReceived,
    ci . CashBalance
FROM
        BASE_TInvoice AS i              -- "base" table:      invoice:       i
    LEFT OUTER JOIN
        BASE_TCashInvoice AS ci         -- "optional" table:  cash invoice:  ci
    ON
        ci.InvoicePaymentId = i.InvoicePaymentId
        AND ( ci.IsRecordStatus IS NULL OR ci.IsRecordStatus = 1 )
WHERE
      i.IsRecordStatus = 1
  AND i.InvoicePaymentId = 1 ;


How can I always get Timestamp in certain format without having to explicitly define it everytime

For general preferences you have the DateStyle and LC_TIME settings.

But it would be madness to always truncate fractional seconds automatically - hiding part of the relevant information.

A possible syntax shortcut would be to create a tiny function once:

CREATE OR REPLACE FUNCTION my_ts(timestamp)
  RETURNS text LANGUAGE sql IMMUTABLE AS
$$SELECT to_char($1, 'MM/DD/YYYY HH12:MI:SS AM')$$;

Then:

SELECT my_ts(entered) FROM analytics WHERE business_id = 371746;


Stolen unceremoniously from Jonathan Kehayias, this will find all the plans relevant to the current database that contain implicit converts (be aware that this is not fast):

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @dbname SYSNAME
SET @dbname = QUOTENAME(DB_NAME());

WITH XMLNAMESPACES
   (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
   stmt.value('(@StatementText)[1]', 'varchar(max)'),
   t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'varchar(128)'),
   t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]', 'varchar(128)'),
   t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]', 'varchar(128)'),
   ic.DATA_TYPE AS ConvertFrom,
   ic.CHARACTER_MAXIMUM_LENGTH AS ConvertFromLength,
   t.value('(@DataType)[1]', 'varchar(128)') AS ConvertTo,
   t.value('(@Length)[1]', 'int') AS ConvertToLength,
   query_plan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple')
  AS batch(stmt)
CROSS APPLY stmt.nodes('.//Convert[@Implicit="1"]') AS n(t)
JOIN INFORMATION_SCHEMA.COLUMNS AS ic
   ON QUOTENAME(ic.TABLE_SCHEMA)
    = t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'varchar(128)')
   AND QUOTENAME(ic.TABLE_NAME)
    = t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]', 'varchar(128)')
   AND ic.COLUMN_NAME
    = t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]', 'varchar(128)')
WHERE
t.exist('ScalarOperator/Identifier/ColumnReference[@Database=sql:variable("@dbname")][@Schema!="[sys]"]')
 = 1

You might also find these useful:

http://www.sqlperformance.com/2013/04/t-sql-queries/implicit-conversion-costs

http://www.sqlskills.com/blogs/jonathan/implicit-conversions-that-cause-index-scans/


How do I create fractal index using tokudb engine?

The BTREE you see is just a label. If "show create table table_name;" displays engine=TokuDB then you can be sure that Fractal Tree indexes are in use.


Session in "Sql*net message from client", but is active

Looks like we found the culprit. Following the recent app server upgrade, we inadvertently included both ojdbc14 and ojdbc6 jars into our deployment, and evidently, the jvm picked up ojdbc14 for its Oracle DB driver. Since we removed ojdbc14 manually, this problem hasn't come up again in the past 24 hours. I assume ojdbc14 is no longer officially supported, so it could cause all sorts of funny problems.

Our next step is to find out how ojdbc14 sneaked into the deployment in the first place.


Query to insert records from one table into another table based on the column of the first table

You're looking for the PIVOT clause. You can see a working example in this db<>fiddle.

The query is:

SELECT customer_num
  ,p.I AS I_Branch_Code
  ,p.Y AS Y_Branch_Code
  ,p.L AS L_Branch_Code
FROM
(
  SELECT customer_num
    ,Channel_Id
    ,Branch_code
  FROM [table]
)s
PIVOT
(
  MAX(Branch_Code) FOR Channel_Id IN ([I], [Y], [L])
) p


I just realized -- so long as you don't mind locking out the user while you log in --

back up the mysql.user table (well, the user's hashed password, at the very least)
set their password to something you know : UPDATE mysql.user SET password=PASSWORD('new password') WHERE user='username' AND host='hostname';
log in as them
set their password back to what it was : UPDATE mysql.user SET password='saved password hash' WHERE user='username' AND host='hostname';

... you may need to flush privileges; after manipulating the mysql.user table.


Find which objects are using my table column in the given schema

Best is to use FREE tool from Red-Gate SQL Search - quickly find SQL in SSMS. You can even profile it while it is running to get the sql code and use that.

Also, refer to Aaron's post on : Keeping sysdepends up to date in SQL Server 2008.

Phil Factor has a great writeup on finding object dependency at Exploring your database schema with SQL


Foreign Data Wrappers & Windows Authentication

Ok, thanks to the guidance of the community I manage to got it to work... I believe its important for me to mention a few things you should take in account when attempting this (at least under the same environment I been trying).

My setup:

Ubuntu 14.04 <-> PostgreSQL 9.3
Windows Server 2003 <-> SQL Server 2005

Additional components:

SQL Alchemy 0.9.9
Multicorn 1.1.0

Im not gonna go into details on how to install this components since there's plenty of documentation online.

Once this is setup I took a_horse_with_no_name advice on changing SQL Server authentication to mixed authentication and created a new account, for the sake of the example I use postgres:postgres. Additionaly still on SSMS I defined SELECT permissions for user postgres in the table I wanted to get the data from tblTest.

Back in my PostgreSQL I created a new database (pgTest) and setup multicorn:

CREATE EXTENSION multicorn;

Created the server for our FOREIGN DATA WRAPPER:

CREATE SERVER alchemy_srv FOREIGN DATA WRAPPER multicorn options (
    wrapper 'multicorn.sqlalchemyfdw.SqlAlchemyFdw'
);

And then (what it was supposed to be the last step) create the table that is going to get data from MS SQL Server:

CREATE FOREIGN TABLE pgTable (LogTime varchar,
                              LogUser varchar,
                              LogData varchar)
server alchemy_srv OPTIONS   (tablename 'tblTest',
                           db_url 'mssql+pymssql://postgres:postgres@10.0.0.0/RemoteDBName');

This shows no error, but once you throw a simple SELECT * FROM pgTable; I was getting the error I OP up there.

So I search a bit more and stumbled on this Google Groups posts labeled on a response as a BUG.

One of the offered solutions is getting rid of pymssql, as in:

apt-get uninstall python-pymssql; pip install pymssql

After this I tried again to create the table and getting data but nothing happened, i was just getting a blank table.

So I pip install pymssql --upgrade, once again create the table within PostgreSQL and voila! this time worked perfect.

Hopefully this will help someone else in the future, thanks for your guidance!


Scalable way to track who's viewed a post?

I think the complexity of performing this task is small and does not change with size. The same SQL that will find a missing recipient from 100 users will also find a single missing recipient from 100M users.  The time to do so, however, is likely to be linear in the number of posts and the number of users.

I can see two ways to organize the data. One is a "positive" list, the other a "negative". For the positive route the system actively stores what has happened. As a post is dispatched a list is kept of the recipients. For one million users this will be one million separate data items. Each item is marked as "unread". As a user sees a post the corresponding data item is marked "read". This will result in two million writes per post. It is likely most reading would happen within a short interval of the post being sent and tail off rapidly thereafter, so there will be spikes in write activity. The system will have to account for new users joining after the post was sent and users leaving before or after seeing the post.

In the "negative" route you store only read events - as a user sees a post the corresponding event is stored. When you need to check who has read the post the system compares this "read" list to the master list of users and returns the intersection or complement as appropriate. Should a new user join after the post was sent they will show as "not read" even if that post was never sent to them. (This assumes a push model, like email, rather than a pull model, like browsing a website.)

How to store this? In a relational model you would have one row per user/post. For 1M users and 100k posts, and integer IDs for each, this works out at 150GB, probably about 500GB once indexes and overhead are considered. This is not big by modern standards. Data compression or a columnstore would help here, but IIRC PostgreSQL does not provide either out of the box.

It would be possible to reduce the amount of storage at the cost of additional writes. Rather than store the individual user/ post receipts store a post/ userid range i.e.

Post A has been read by users 1 through 10
Post A has been read by users 11 through 11
Post A has been read by users 45 through 78

As new receipts arrive existing ranges can be extended, merged or inserted. Assuming receipts arrive in random order, the number of rows will increase (to at worst COUNT(users)/2 before reducing toward 1 as rows merge). I have a nasty feeling that this would be polynomial in IO, however, something like O(Nx).

If UserIDs are allocated without gaps the checks are easier. In the real world there are likely to be gaps and there will be a lot of look-ups on the user table to see if two receipt ranges can be merged.

The above is symmetrical for "user has read post x through y" and also read vs unread.

Having thought it through some more, I think the algoritm is actually O(log N). For each message receipt the algorithm will have to read where that message lies in the current log and UPDATE, INSERT or DELETE as appropriate. Assuming a BTree index exists these are O(log N) operations (even though there may be several operations per read receipt).

In the best case scenario read receipts would arrive in exactly primary key sequence (massively unlikely). Then the logging table would only ever have one row. In worst case read receipts would arrive in alternate sequece e.g. all the odd numbered keys followed by all the even numbered keys. This will cause the log table to grow to 50% of the number of posts sent.

A rough prototype with read recipts arriving in random sequence saw the logging table grow to a maximum of 25% of the number or posts. So for a sample of one million posts the log table would start with a single row; this would grow to about 250,000 rows; and then shrink to zero rows when the last read receipt was processed. This was consistent across several orders of magnitude.  Some day I'll sit and work out the maths of why this should be so, but not today.


Error Message while executing command in Arbiter for log rotation

As per MongoDB BOL Here Rotate the log file by issuing the logRotate command from the admin database in a mongo shell:

db.adminCommand( { logRotate : 1 } )

You must issue the logRotate command against the admin database in the form:

{ logRotate: 1 }

Note : Your mongod instance needs to be running with the --logpath [file] option.


To Quiesce means to pause/alter an application/device to allow the application/device to achieve a consistent state.

In terms of DB2 the Quiesce command should force all the users off the specified database and should flush all the buffers to disk (been a while since I've used DB2).

While the database is a Quiesced mode there are users who can still access the database such as SYSADMIN and SYSMAINT.

There are several DB2 functions that can be used to determine the status of a table such db2tbst and MON_GET_TABLESPACE_QUIESCER


To answer your questions:

Session ends when session (with mysql) ends and not when stored procedure ends! So the setting of group concat you did appears correct.

You may want to refer about statement caching and local variables scopes in SP.

I simulated your scenario and it worked well for me!

MySQL Version: 5.6.24-72.2-log

mysql> DELIMITER $$
mysql> DROP PROCEDURE IF EXISTS session_test $$
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE PROCEDURE session_test (INOUT str TEXT)
    -> BEGIN
    -> show session variables like 'group_concat_max_len';
    -> set session group_concat_max_len = 10000;
    -> show session variables like 'group_concat_max_len';
    -> select group_concat(ts1) into str from t1;
    -> END $$
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> call session_test(@myvar);
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| group_concat_max_len | 10000 |
+----------------------+-------+
1 row in set (0.01 sec)

+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| group_concat_max_len | 10000 |
+----------------------+-------+
1 row in set (0.01 sec)

Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> select length(@myvar);
+----------------+
| length(@myvar) |
+----------------+
|          10000 |
+----------------+
1 row in set (0.00 sec)

mysql> show warnings;
+---------+------+-----------------------------------+
| Level   | Code | Message                           |
+---------+------+-----------------------------------+
| Warning | 1260 | Row 501 was cut by GROUP_CONCAT() |
+---------+------+-----------------------------------+
1 row in set (0.00 sec)

mysql> show session variables like 'group_concat_max_len';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| group_concat_max_len | 10000 |
+----------------------+-------+
1 row in set (0.00 sec)

You see the last warning cause my group concat length went beyond 10K but that's OK!

You're not doing anything wrong with the variable as such it appears fine to me!

Are you sure you have group concat length of 10k? Please try to exec the select statement from procedure.


Building dependence between regional data (Street -> City -> PostalCode -> Country)

Unfortunately, you can't even derive the country from the postal code. For example, the postal code 50170 can be found in 10 different countries.

And some countries don't have postal codes.

And some countries contain other countries, not provinces (UK contains England).

For autofill, I would suggest using HTML5 geolocation or IP address geolocation to help the user fill in forms (for themselves).

Otherwise, ask them to select the country, then enter the postal code, and you can offer the available next options, depending on the country.

Geonames is not bad (though I don't like how they don't use ISO codes for primary divisions), and it's designed that way because administrative divisions are not hierarchical.


is it possible to get innobackupex to print the filenames it's busy with?

Check the version you used to backup the data is the same as the one you are trying to use to restore. This can bite.


Splitting large database into smaller ones

If we were to break up the accounts into separate db-namespaces the dot-notation used inside of the stored procedures cannot be parameterized
We would have to duplicate the stored procedures (~150 count) for each of the account-namespaces.

That should not be an issue: when calling a procedure in database1 it will have that context so you won't have to change the procedures. You don't even need to change the procedures and tables at all: keep the account_id column in and have it always a fixed value for each account/database. That way you can switch back to multiple tenants per DB.
If you always develop and test against databases with multiple tenants this should be safe (if you dev & test against single-tenant DBs you make it easy for cross-tenant data revealing bugs to be missed).
If you decide later that you will never need/want multi-tenant databases, you can at that stage decide whether it is worth the effort and regression testing required to remove account_id from everything.

In fact, any DB schema change needs to be applied (via automation) to all the DB namespaces. The administrative overhead is slightly scary, even after automating the deployment of changes.

There are many tools to help out here, which is best (or if rolling your own is better) depends very much on the application, scale, and deployment frequency, amongst other factors.

Do you agree that it's not possible to have one common namespace (database) hosting all the stored procedures, then be able to invoke it as shared.sp_get_info() in the context of another (per-merchant-account) database?

If mySQL allows cross-DB references like that (I've not used it much recently, I'm an MS SQL Server guy mainly, which would allow such things (though not in Azure SQL)) then that woul be possible and would work, but I wouldn't recommend it.
Your common DB becomes a single point of failure for all the supporting DBs. It tightly couples them, making it more work to scale by simply shifting some databases onto another server/service as you can't just simply move the desired client DBs and update connections settings in the apps (you have to create a new common DB and make sure each has the right families for procedures). You still have to maintain all the different copies of the same procedures. You still have the problem of keeping things appropriately up-to-date but it opens up a new class of possible errors: if due to a build process problem or human error the procedures for a given client DB get updated before the schema in that DB or don't get updated when the DB does, you have problems some of which might be hard to diagnose. Also if you have to restore a client DB from an old backup, you have extra work to make sure that the procedures are at the right version.
It might feel like a simplification, but you are really adding complication. Keeping the same code in procedures in each DB is cleaner. You'll have potential versioning issues but the common DB won't really remove them, in fact it will add more such concerns.


Need help installing ODAC 122011x64 so I can connect ADW to Power BI

I figured out the solution after reviewing this post. Apparently having over 2TB of free space on a hard drive causes this problem. You have to go into command prompt and create a temporary file to get your free space under 2TBs using something like this:
fsutil file createnew temp_1TB_file 1200000000000

Run the setup.exe again and then delete the temp file after.
Update: After doing all this I still was running into the same issues in Power BI. Then I read the troubleshooting notes from this page more closely and realized that I might have found the problem when it said:
If you downloaded Power BI Desktop from the Microsoft Store, you might be unable to connect to Oracle databases because of an Oracle driver issue.

I uninstalled the PowerBI Desktop that I had installed from Microsoft Store and installed it from the download from its website and tried connecting to Oracle ADW again through PowerBI and IT WORKED!!! So much troubleshooting but it finally works!


Error when creating an index in SQL Server 2012

The problem was with data corruption in the table. Once the issues found in dbcc checktable were resolved (in this case we deleted the affected rows as it's only test data) the index was able to be created.


MySQL query: select max(A) and use MAX(pkey) if multiple max(A) records exist

It looks like what you want is: for each uid, find the MAX(pkey) associated with the MAX(A) value of A.

If that's what you need, try this:

SELECT table.*
FROM (
     SELECT uid, A, MAX(pkey) as pkey
     FROM table t
     WHERE A = (SELECT MAX(A) FROM table WHERE uid = t.uid)
     GROUP BY uid, A
) as maxlog
JOIN table
ON maxlog.pkey = table.pkey

Note that, while this technically only has one JOIN, that's really just semantics - the SELECT MAX(A) subquery is, in effect, a JOIN (and might perform better if written as such).


Selecting Only One Record with Most Recent Date Using Table Join

The simplest way to select the last row (by bill_dt) in each group of rows with the same carrier_cd in Teradata would probably be using QUALIFY:

QUALIFY 1 = ROW_NUMBER() OVER (PARTITION BY carrier_cd ORDER BY bill_dt DESC)

If you're sure there are no ties (i.e. there's only one row with bill_dt = MAX(bill_dt) for each carrier_cd), you could calculate the max date for each carrier you're interested in:

sel carrier_cd, max(bill_dt) as max_bill_dt
from ARTBASE join CARRIERS on ARTBASE.carrier_cd = CARRIERS.carrier_cd
group by 1

...and then join it back to the original table:

sel ARTBASE.carrier_cd, ARTBASE.bill_dt, ARTBASE.bill_amt
from ARTBASE
join ( the above select here ) CARRIERS_STAT
  on ARTBASE.bill_dt = CARRIERS_STAT.max_bill_dt

In general I try to avoid the correlated subqueries, especially in a join condition. I'm not even sure how it works at all in the example you provided.


sp_Blitz says "Page Verification Not Optimal" on MDS database

I would suggest you follow what the sp_blitz recommended. You can change the option to CHEKSUM. Checksum might be able to detect more problem that torn_page.

Torn page allows you to detect whether page was successfully written to disk or not. It would not check what is inconsistency inside the page. While checksum performs more thorough checks. Having said all this you must know there is no replacement of good backup and you must (if possible) run dbcc checkdb every day.

What Paul said, quoting from This SQLServercentral link

  The difference is Unmeasurably small - all it's doing is grabbing the
  first two bits from each of the 16 sectors, storing them in the page
  header, and then writing an alternating bit pattern into the two bits
  in each sector. And the reverse when the page is read again. Almost
  nothing compared to what a page checksum is doing by reading the whole
  page and adding the contents to a 'checksum'.

  However, torn-page detection does not detect corruption within sectors
  - only when a write fails some way through. On 2005 onwards you should always have page checksums enabled for this extra benefit - 1-2% CPU
  shouldn't be an issue (otherwise you've got other problems).

  Some blog posts around this: How to tell if the IO subsystem is
  causing corruptions? and Inside The Storage Engine: Does turning on
  page checksums discard any torn-page protection?.

Following is what BOL document has to say

  Consider the following important points when you use the PAGE_VERIFY
  option: • The default is CHECKSUM.

  • When a user or system database is upgraded to SQL Server 2005 or a
  later version, the PAGE_VERIFY value (NONE or TORN_PAGE_DETECTION) is
  retained. We recommend that you use CHECKSUM

  In earlier versions of SQL Server, the PAGE_VERIFY database option is
  set to NONE for the tempdb database and cannot be modified. In SQL
  Server 2008 and later versions, the default value for the tempdb
  database is CHECKSUM for new installations of SQL Server. When
  upgrading an installation SQL Server, the default value remains NONE.
  The option can be modified. We recommend that you use CHECKSUM for
  the tempdb database.

NOTE: If you enable page checksum it would not be enabled for entire pages of the databases. There is no process as such which puts this option for all pages. When a certain page would be read in memory, changed, and written back to disk checksum would then be enabled for that page. So you would have to perform operation like index rebuild to bring as much page as possible in memory. Paul Randal has Myth section related to Checksum please read it.
Databases that are created on SQL Server 2005 and 2008 will have page checksums enabled automatically unless user changes the setting in the model database.