How soon after updating expire_logs_days param and restarting sql will old binlogs get deleted?

It will be handled immediately on mysql startup.

You do not have to wait for a mysql restart.

First, set expire_logs_days to be 10 in /etc/my.cnf

[mysqld]
log-bin
server-id=1
binlog-do-db=foodb1
binlog-do-db=foodb2
expire_logs_days=10

Next, log in to mysql and run this

PURGE BINARY LOGS BEFORE (date(now()) + interval 0 second - interval 10 day);

Note the query's date and time

mysql> select date(now()) + interval 0 second - interval 10 day;
+---------------------------------------------------+
| date(now()) + interval 0 second - interval 10 day |
+---------------------------------------------------+
| 2012-12-11 00:00:00                               |
+---------------------------------------------------+
1 row in set (0.00 sec)

mysql>

Thus, the PURGE BINARY LOGS command will delete all binlogs whose datetime stamp predates 2012-12-11 00:00:00.

Finally, run this command

SET GLOBAL expire_logs_days = 10;

That's it. No restart needed for installing expire_logs_days.

As for the replication parameters, yes restart is required.


This seems to work:

Update companies c
inner join (select a.id as company_id, sum(case when b.hot_tip = 1 then ((@maxrank-c.rank)/(@maxrank-1)) * (b.score-3) else 0 end) as cc_score
from companies a
left join table2 b
on a.id = b.company_id
left join table3 c
on b.table3_id = c.id
group by a.id) x ON x.company_id = c.id
set c.c_c_score = x.cc_score;


sqlplus / as sysdba
create pfile from spfile;
exit

Now edit C:\oraclexe\app\oracle\product\11.2.0\server\database\initXE.ora. Remove the line with local_listener, you use the default 1521 port on your local machine, you do not need that line. Once done:

sqlplus / as sysdba
create spfile from pfile;
startup

The problem is that your hostname is ASUS-ALEXLUTINNOIR, but only the first 15 characters was put in the local_listener parameter (ASUS-ALEXLUTINN).  The database can't resolve that hostname when starting up. In such a simple configuration you do not need the local_listener set, just leave it blank (remove), so the database will register itself to the listener running on the default port (1521).


A (simple?) solution to the "logical decoding / sequence" problem in Postgres?

Your proposed solution is fine so long as you only need to track inserts and you don't need to be scalable. You can use an id-generator table you query with UPDATE ... RETURNING to implement it very simply; search for "gapless sequence".

Since it destroys write concurrency, it'll only be able to grow to a limited point.

It also won't help you track updates or deletes. Unless you start recording them with triggers that insert into a queue table. At which point you might as well use logical decoding. Or Londiste.

Logical decoding is indeed channel based. You can't step in at some arbitrary point. It's designed to ensure that you receive every change exactly once. You can skip over changes, though, or filter them at the output plugin level. The only restrictions really are that:

you cannot "rewind" once you confirm receipt of changes (but you can intentionally replay the same changes multiple times before you confirm; see the peek functions)
the initial slot position is always the current server's insert position or slightly after it. You can't create a slot that starts in the past.

Personally I think you should just be using one of the json based logical decoding plugins for this, but I don't know the full picture of what you're trying to achieve.


is there a way to configure Azure SQL databases for high-insert scenarios?

Table compression is now supported in V12. You can compress the table to reduce the IO (as you mentioned CPU is not a problem). Try to batch the requests as each commit has overhead. You are just taling about 20 * 4 = 80 bytes per row and 80 * 4k = 320k data which is 0.5MB. This should be doable if tune the app in both S2 & S3. As you mentioned you are not hitting DTU limits, concurrent connections help too.


Replicaton - The current transaction cannot be committed and cannot be rolled back to a savepoint

I re-connected to sql-server using the credentials of my replication agent - which is a sysadmin on my publication server, and db_owner on my subscription database at my subscription server.

1 - right click on the ssms icon

2 - on the SSMS option above, press shift + right click and choose the Run as a different user

3 - add the credentials of the different user - in my case it was my replication agent account

and it all worked fine after that, as you can see on the picture below:


20-40 ms performance difference during peak traffic on amazon RDS

20-40ms is about a thousand miles of latency.  Where are the client and server, relative to each other?  Is Amazon moving the Server around?  Or the Client?

innodb_buffer_pool_size is set reasonably by Amazon (I think).  Do not raise it without raising the amount of RAM?  There is no benefit in making the buffer_pool much bigger than the entire dataset (data & indexes).

For an 8GB VM (which is probably only 7.5GB), 6G is about the max that is safe -- assuming that only MySQL is running in that VM.

Turning on the QC is usually a waste on production systems (except on Aurora).  Also, do not set query_cache_size bigger than about 50M -- else it could slow things down.

20% CPU -- percent of one core?  Of all cores?  20% of all cores seems high.  What is your slowest query?  Also SHOW CREATE TABLE.


It's hard without the tables and data to test on but I believe this will work for you.  I added the subquery T1 as a RIGHT OUTER JOIN to T2, since I've moved the table to the "right" side of the query.  I moved it to the right to avoid any issues with LEFT OUTER JOINing the whole rest of the query.  I aliased it as RCHECKS (RIGHT OUTER JOIN version of CHECKS).

Also why do you have a HAVING clause?  Those particular statements will work just fine in the WHERE clause and save you quite a bit of time.  The HAVING clause is specifically for checks against aggregates.  My guess is if you move the checks into the WHERE clause you query will go much faster.  I've included a modified version of the query at the bottom using a WHERE clause and no subquery at all.

SELECT
     t2.CHECKID
    ,t2.CHECKNAME
    ,t2.Startdate
    ,t2.Enddate
    ,t2.StatusID
    ,t2.Month
    ,t2.Year
FROM
(SELECT
        RCHECKS.CHECKID
        , RCHECKS.CHECKNAME
        , STATUSCHANGES.DATETIME AS Startdate
        , MIN(STATUSCHANGES_1.DATETIME) AS Enddate
        , STATUSCHANGES.STATID AS StatusID
        , CHECKS.CHECKNAME
        , STATUS.STATUSNAME
        , DATEPART(MONTH,STATUSCHANGES.DATETIME) as Month
        , DATEPART(YEAR,STATUSCHANGES.DATETIME) as Year
    FROM STATUSCHANGES
    INNER JOIN STATUSCHANGES AS STATUSCHANGES_1
        ON STATUSCHANGES.CHECKID = STATUSCHANGES_1.CHECKID
        AND STATUSCHANGES.DATETIME < STATUSCHANGES_1.DATETIME
    INNER JOIN CHECKS
        ON STATUSCHANGES_1.CHECKID = CHECKS.CHECKID
    INNER JOIN STATUS
        ON STATUSCHANGES.STATID = STATUS.STATUSID
    RIGHT OUTER JOIN CHECKS RCHECKS
        ON CHECKS.CHECKID = STATUSCHANGES.CHECKID
    GROUP BY
        RCHECKS.CHECKID,
        RCHECKS.CHECKNAME,
        STATUSCHANGES.DATETIME,
        STATUSCHANGES.STATID,
        CHECKS.CHECKNAME,
        STATUS.STATUSNAME
    HAVING
        (STATUSCHANGES.STATID IN (3))
        AND DATEPART(Q,STATUSCHANGES.DATETIME) = 2
    ) AS t2
    ORDER BY t2.Startdate

    SELECT
        RCHECKS.CHECKID
        , RCHECKS.CHECKNAME
        , STATUSCHANGES.DATETIME AS Startdate
        , MIN(STATUSCHANGES_1.DATETIME) AS Enddate
        , STATUSCHANGES.STATID AS StatusID
        , DATEPART(MONTH,STATUSCHANGES.DATETIME) as Month
        , DATEPART(YEAR,STATUSCHANGES.DATETIME) as Year
    FROM STATUSCHANGES
    INNER JOIN STATUSCHANGES AS STATUSCHANGES_1
        ON STATUSCHANGES.CHECKID = STATUSCHANGES_1.CHECKID
        AND STATUSCHANGES.DATETIME < STATUSCHANGES_1.DATETIME
    INNER JOIN CHECKS
        ON STATUSCHANGES_1.CHECKID = CHECKS.CHECKID
    INNER JOIN STATUS
        ON STATUSCHANGES.STATID = STATUS.STATUSID
    RIGHT OUTER JOIN CHECKS RCHECKS
        ON CHECKS.CHECKID = STATUSCHANGES.CHECKID
    WHERE STATUSCHANGES.STATID IN (3)
      AND DATEPART(Q,STATUSCHANGES.DATETIME) = 2
    GROUP BY
        RCHECKS.CHECKID,
        RCHECKS.CHECKNAME,
        STATUSCHANGES.DATETIME,
        STATUSCHANGES.STATID,
        CHECKS.CHECKNAME,
        STATUS.STATUSNAME
    ORDER BY STATUSCHANGES.DATETIME


How to set-up a distributed data storage system for The Real Junkfood Project

Generally speaking I would recommend a full fledged relational database.  This will give you everything you need, and allow you to grow gracefully.

As far as what exact engine to use that depends on personal preference/budgetary constraints/hosting requirements/compatability with the application layer/etc.  Personally I would probably use the Microsoft stack (SqlServer database, C# for the application layer, etc) because that is what I'm most familiar with, and would be most efficient in coding.  Others may choose other platforms based on X,Y,Z reasons.  The truth is any mainstream relational database engine will support your needs.

I wouldn't recommend csv files because that is highly likely to become unmanageable. I would overwhelmingly recommend Sql Lite over csv files given only these two options.  Both are lightweight, but Sql Lite at least offers some database functionally. Here is a good link for what SqlLite is good for. Basically good: local storage and/or replacing CSV/custom data storage files. Not designed for: Replacing client/server SQL database engines (SqlServer, Oracle, PostgreSQL, etc).

I also wouldn't recommend Google Fusion Tables because I believe it'll be harder to manage as you grow.  I could be wrong but that is my general experience with those types of solutions.  Also Fusion Tables is an experimental app, so I would be worried about what happens if the "experiment" fails...

I wouldn't recommend NoSql because I don't think you will gain much if anything, and relational type reporting will be complicated.


Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

Finally figured this out!

We reference many of our very active SQL Servers and Instances with SQL aliases and canonical names.  This allows us to perform parallel upgrades and switch machines in and out without code interruption.  The SQL aliases are all configured to reference the canonical names for the same reason.  The standard SPNs we had registered are for the machine names.

EXAMPLE
SQL Server SSxyz\xyzInstance listening on port 12345 has registered SPNs:

MSSQLSVC/SSxyz.mydomain.com:12345
MSSQLSVC/SSxyz:12345
MSSQLSVC/SSxyz:xyzInstance

These are all registered to the service account that owns MSSQLSVC on that machine and delegation is set for each to pass credentials.

The server and instance also have canonical name cxyz\xyzInstance.  Finally, the SQL Server has a 32bit and 64bit alias of xyzAlias, pointing to canonical name cxyz\xyzInstance.

During a double hop scenario from my local pc to xyzAlias to abcAlias, Kerberos authentication fails and falls back to NTLM because the alias and SPN cannot be resolved.  This is why the logs were showing NTLM.

The fix was to add a few more SPNs to register the canonical name of the server to the actual name.  Then the SQL service is registered with another SPN. like this:

setspn -s host/cxyz SSxyz
setspn -s host/cxyz.mydomain.com SSxyz
setspn -s MSSQLSVC/cxyz:12345 sqlserviceaccountname
setspn -s MSSQLSVC/cxyz:xyzInstance sqlserviceaccountname

Once registered, each SPN is configured for delegation.
These appear as unique SPNs and allow the SQL alias to resolve to the actual machine name. The double hop authentication logged is kerberos and everyone is happy!  Except the AD guys...they're going to have a lot of SPNs to register in a couple of hours.


MySQL 5.6: Slave_IO thread stops working while replicating

Everything looks fine, what is making you think replication breaks? Is there any active writes being made to the master? If you did show master status\G on the master, as well as show processlist; are you seeing any active queries or any queries that are locked?

If you posted some of the binlog or taken a look at it, it may help you see, if replication is being broken, what exact query is breaking it. You can access the binlog by doing
mysqlbinlog binlog.file

For more reference on binlog


After checking pgbouncer source code in stats.c file, I realized that values shown in the log are all average values for that logging period.

Avg number of requests is mostly zero because there's less than 60 queries in a logging period which is about 1 minute.


Creating a table with many optional fields

Your second option is more flexible, but I'm not sure why you're worried about "tons more tables". Usually this would be done with a single table:

contact_types
-------------
  id (PK)
  name

contact_details
---------------
  id
  contact_type_id (FK to contact_types.id)
  value

organization_contacts
---------------------
  id (PK)
  contact_detail_id (FK to contact_details)
  organization_id (FK to organizations)

populated like this:

contact_types
-------------
ID  | name
----+-----------
1   | web_url
2   | facebook_url
3   | phone_1

contact_details
---------------
id  | type_id  | value
----+----------+-------
1   | 1        | www.stuff.com
2   | 3        | (111) 111-1111
3   | 2        | facebook.com/?profileid=stuff

organization_contacts
---------------------
id | contact_detail_id  | organization_id
---+--------------------+----------------
1  | 1                  | 1
2  | 2                  | 1
3  | 3                  | 1

This schema only has 3 tables (not "tons") and you can have as many contact types as you want. The joins aren't that complicated. Your contact_details table will be large as there is 1 record per piece of contact info, but unless you have "tons" of contact info for each organization and "tons" or organizations, this probably won't be too big a problem. ;)

You could also have a contact_details table which stores all of the fields. Something like this:

contact_details
---------------
  id (PK)
  main_email
  secondary_email
  web_url
  facebook_url
  linkedin_url
  myspace_url
  street_address_line_1
  street_address_line_2
  street_address_line_3
  city
  prov_state
  country
  postal_code
  phone_num_1
  phone_num_2
  fax_num

This structure is much simpler but is more static. If you don't plan to change your set of contact data very much, and you think that most records will have most (or above a certain threshold of) fields filled out, I suspect this would perform better.


How do I connect to a database which is a linked server via ODBC?

You have created a linked server, not a linked database, so there is no list of databases to select from. You need to either use a "four-part name" or use the OPENQUERY() function.

Four-part names describe an object on another server. Usually a four-part name would look like "servername.databasename.schemaname.tablename", but for Oracle you apparently need to leave the database name out. A query using a four-part name for Oracle might look like this:

SELECT name FROM OracleSvr..joe.titles WHERE id = 101

where "OracleSvr" is the name of the linked server.

Alternatively, here is an example of a statement that uses OPENQUERY. It should do the same thing, some people find this style easier to read.

SELECT * FROM OPENQUERY (OracleSvr, 'SELECT name FROM joe.titles WHERE id = 101')

OPENQUERY is documented here.

No matter which style of query you choose, you would connect via your Python program to the SQL Server and then run the query.

You can do this via ODBC, you don't need to specify the database name when setting up the connection, you do it at query time as described above.


From your question it's unclear whether you only want to search for common text only at the end of strings, or anywhere in the strings. Nonetheless, at the core this seems to build on the Longest Common Substring Problem (wikipedia) in computer science.

I don't know of a readily-available function in PostgreSQL to find longest common substrings; so I suspect that the best solution will be to implement what you need in a procedural language similar to https://stackoverflow.com/questions/30315934/sql-find-longest-common-string-between-rows.

Doing it purely in (set-based) SQL will be rather complicated - ending up something like https://blogs.oracle.com/sql/finding-the-longest-common-starting-substring-using-sql. That particular solution only searches on substrings at the beginning of the field but could probably be modified to do what you want. The idea of using ts_vector to solve this more efficiently in SQL is interesting too - I hope you report back if you find anything useful!


If you only want online operations you should consider setting tokudb_disable_slow_alter=ON as per https://www.percona.com/doc/percona-server/5.7/tokudb/tokudb_variables.html#tokudb_disable_slow_alter.


Update statistics more often and stop defragmenting indexes

Statistics are used by the Query Optimizer to help estimate row counts for operations so that it can determine the most efficient means of accomplishing that operation.

Since you are using GUIDs as the Primary Key in these tables, it is highly doubtful that you are doing any range operations / multi-row operations. Since GUIDs have no inherent order, you are really only ever doing single-row operations. And since these are Primary Keys, they are "unique" and any value only exists once, or not at all, hence your operations are either going to affect 1 row when filtering on the PK, or all rows if not filtering on anything (I am ignoring other filters for the moment since those would use statistics for another index, or an auto-created statistic on a column, neither of which are impacted by this particular issue). In this sense, merely updating statistics isn't going to help much (or at least shouldn't).

At the same time, since operations against these PKs are singleton operations (i.e. seeks), those are not greatly impacted by fragmentation. In this sense, rebuilding the Clustered PKs is not going to have a terribly noticeable affect on those operations. HOWEVER, rebuilding (at least occassionally) still has merit.

So my advice is this:

The easier but less overall effective path:

Since your system is currently suffering from a rampant page-split debacle, you need to free up that wasted / unused space as there is a lot of wasted space across many data pages that are not only filling up the Buffer Pool, but its also making backup/restore operations take longer. So, try doing index REORGANIZE operations for a few weeks and then a REBUILD. I'm not sure what Edition of SQL Server you are using, but if not using Enterprise then REBUILD operations are offline, yet REORGANIZE operations are online.

Doing online operations not only reduces the need for the maintenance window, but they can be done more often throughout the week. You can even stagger which indexes get rebuilt when and perhaps pick the top N index, based on fragmentation level, and REORGANIZE a certain number of them each night.
The harder but much more effective path:

Even if the REORGANIZE and occasional REBUILD plan helps, you are still really just using a bucket to scoop water out of a boat that has a hole in it. But your system is growing and so that "hole" is getting bigger. All of this REBUILD / REORG stuff is merely an effort to live with a bad situation. It might work for a while, it might even work forever, but there could easily come a point when it does not work. The absolutely better approach is to actually fix the bad situation, not just sweep it under the rug.

That absolutely better approach is to remodel the tables to use INT / BIGINT columns for the Clustered PKs. If the app code uses the GUIDs, or if the GUIDs are known by external systems and hence referenced by external systems, then you can still do this major modification: just keep the GUID values in one table, create a NonClustered Index on the column, and then do a lookup on those values at the beginning of operations to translate them to the internal INT / BIGINT values. This will shrink the size of all but a few tables (especially when considering that NonClustered Indexes have the Clustered Index keys duplicated in them, hence most of your NonClustered Indexes are 16 bytes larger than the sum of the index keys, when they could be just 4 or 8 bytes larger if the Clustered Key was INT or BIGINT, respectively). Smaller tables take up less space in the Buffer Pool, hence pages stay cached for longer and queries are more efficient. Backup and restore operations are also faster. Index REORG and REBUILD operations are also faster.

I have suggested approach #2 before to you in this answer:   Best fill factor for GUID clustering key. So, while approach #1 might help, I don't think your situation will drastically improve until moving forward with approach #2. And I do fully understand that it is much easier said than done since I don't have to deal with time / resource constraints and other complicating factors. BUT, I will say that if management is not keen on pursuing such a large project, they need to consider the value-proposition of having a faster system, spending less money on disk space (data files, log files, and backups), reduced down-time / maintenance window, reduced recurring support time due to you needing to "fix" this ever-worsening situation at least once per year, etc, etc, etc. In the long-term, this approach is actually cheaper. This is your best "maintenance plan" that provides for the best performance :-). And it can be accomplished in phases, so it doesn't have to be done in one massive release.


TSQL trigger on actual changes - update field

Instead of:

Update a row instead of inserting it when you see that the amount hasn't changed.

Why not:

Just insert the row. If you then find previous rows where there are multiple consecutive rows with the same value, delete all but the most recent one.

Create this trigger against a test version of your table please:
CREATE TRIGGER dbo.hist_arch_test ON dbo.history_archive_test_copy
FOR INSERT AS
BEGIN
  IF (@@ROWCOUNT > 0 AND UPDATE(amount))
  BEGIN
    ;WITH x AS
    (
      SELECT *, delta = ABS
           (
             /* amount from *next* row */
             LEAD(a.amount, 1) OVER
             (
               PARTITION BY a.country_id, a.product_id, a.flow_date
               ORDER BY a.update_date
             )

             -  /* minus */

             /* amount from *current* row */
             a.amount
           )
      FROM dbo.history_archive_test_copy AS a
      WHERE EXISTS
      (
        SELECT 1 FROM inserted AS i
          WHERE i.country_id = a.country_id
            AND i.product_id = a.product_id
            AND i.flow_date  = a.flow_date
      )
    )
    SELECT *, status = CASE delta WHEN 0 THEN 'mark as delete' ELSE '' END
    FROM x ORDER BY country_id, product_id, flow_date, update_date;
    -- when happy, delete the select, uncomment the delete:
    -- DELETE x WHERE delta = 0;
  END
END

I didn't test all possible permutations of existing values but this should clean up any old junk not directly related to the most recent row/series too.


It looks like you can index your data according to thier SQL documentation for CREATE INDEX. Sample code from their docs:
CREATE INDEX [IF NOT EXISTS] index_name ON table_name ( column_name_to_be_indexed );
GridDB's specifications and rules on index creation:

For a table, an index with the same name as an existing index in the table cannot be created.

If a transaction under execution exists in a table subject to processing, the system will wait for these to be completed before creating the data.

An index cannot be created on a column of BLOB type and ARRAY type.

Up to 16 columns can be indexed.

Time series table does not allow a primary key in a composite index.

My recommendation would be to index on the fields you plan to most commonly use in predicates (JOIN, WHERE, and HAVING clauses) in your queries, as a starting point.


Google Big Query Invalid time zone: +0000

Community wiki answer originally left by the question asker as a comment:

The fix was removing the timestamp formatting from FORMAT_TIMESTAMP

TIMESTAMP(FORMAT_TIMESTAMP("%Y-%m-%d", PARSE_TIMESTAMP("%Y%m%d", ga.date )))


What is the purpose of sqlbulkcopy's useinternaltransaction property?

If I understand you correctly:

You'd use BatchSize to break the load into chunks eg 10k rows
UseInternalTransaction determines if each batch is a transaction

So:

BatchSize = 0 -> UseInternalTransaction is irrelevant
BatchSize > 0 -> Apply UseInternalTransaction is specified

FYI, to monitor a SQL Server load in a non-blocking fashion

select count(*) from Mytable WITH (nolock)


Finding exact statement causing error 3930

In Profiler, click "show all events" and go to the Errors and Warnings listing.  You should be able to check Exception and User Error Message.  The Exception class will show you the actual error and User Error Message will show you the message displayed (e.g., "Incorrect syntax near ..." or whatnot).

You mention a message going into the error log; there's also an ErrorLog event in the Error Message category, so it wouldn't hurt to include that as well.

You would need to include the SQL:BatchStarting and possibly SQL:BatchCompleted as well to get the command being run at that time.

You might also look at Remus Rusanu's answer to an earlier question of this nature to see if that might help solve the problem.


What happens when there is no available physical memory left for SQL Server?

when there is no physical memory left for data, then SQL Server moves the already existing data into TEMPDB

The article you linked to is misleading at best, and incorrect in some places.  I think the author was attempting to over-simplify some complicated things, and in doing so went a little too far.

SQL Server doesn't move data from memory (the buffer pool) into tempdb in that way.  It uses a "least recently used" caching strategy (in general), so if there is memory pressure, and new data needs to be pulled into memory, SQL Server will kick out the LRU data from the buffer pool to accommodate the new data.  This behavior is often monitored by a perfmon counter called "Page Life Expectancy" (PLE):

  The definition of PLE is the expected time, in seconds, that a data file page read into the buffer pool (the in-memory cache of data files pages) will remain in memory before being pushed out of memory to make room for a different data file page. Another way to think of PLE is an instantaneous measure of the pressure on the buffer pool to make free space for pages being read from disk. For both of these definitions, a higher number is better.

During query execution, SQL Server can use tempdb for certain operations.  This is usually done if estimates are bad, but low available memory can influence this behavior.

Some of the operations that can "spill" to tempdb in this way are hashing rows (for joins or aggregates, etc), sorting rows in memory, and buffering rows during parallel query execution.

User queries can also explicitly use tempdb (with global or local temporary tables), and implicitly use tempdb (with snapshot or read committed snapshot isolation levels).

Neither of these situations really seem to fit the statement you quoted.

  when there is not enough physical memory left, operating system can use the PAGE FILE and move data from the physical memory to it

This can definitely happen, and is outside of SQL Server's control for the most part.  There is a knob you can turn to try to prevent some types of OS-level paging, namely turning on "Lock Pages in Memory" (LPIM):

  This Windows policy determines which accounts can use a process to keep data in physical memory, preventing the system from paging the data to virtual memory on disk.

So what can we prevent from being paged to disk?

Prior to SQL Server 2012, pages that were allocated through a component called the "Single Page Allocator" were locked in memory (could not be paged).  This included the buffer pool (database pages), procedure cache, and some other areas of memory.

See Fun with Locked Pages, AWE, Task Manager, and the Working Set… for details, especially the section "4. Now I know that SQL Server on x64 can use “Locked Pages”, what exactly is locked?"  Additional related reading can be found here: Great SQL Server Debates: Lock Pages in Memory

In SQL Server 2012 and later, there is no "Single Page Allocator" (the single and multi-page allocators were merged, per An In-depth look at memory – SQL Server 2012/2014).  The details of what, exactly, can and cannot be paged is not documented in detail anywhere that I've seen.  You can use a query like this to see what is locked:

select osn.node_id, osn.memory_node_id, osn.node_state_desc, omn.locked_page_allocations_kb
from sys.dm_os_memory_nodes omn
inner join sys.dm_os_nodes osn on (omn.memory_node_id = osn.memory_node_id)
where osn.node_state_desc <> 'ONLINE DAC'

Per the same MS Support article, you could also use DBCC MEMORYSTATUS to see how much memory is "locked."

As a side note, you can see evidence of SQL Server's working set being paged by the OS in the error log.  There will be messages that look like this:

  2019-09-02 10:19:27.29 spid11s A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 329 seconds. Working set (KB): 68780, committed (KB): 244052, memory utilization: 28%.


Is there a generally accepted correlation between logical reads and the amount of data returned by a query?

The missing piece is the

1) The number of logical reads of pages with no relevant data

2) The number of relevant result rows on each page

And these two are determined by your indexes.  If this table has no relevant indexes, then you will have to scan each page, and you will have to read a lot of completely or mostly irrelevant pages.  Even if your index supports a query plan where you read only pages (mostly) full of relevant rows, if those rows are very wide, then there may not be very many (A,B) pairs per page.

If you had an index on (B,A) on this table then SQL could seek to the first row where B=1 and start scanning rows until it hits a row with A>SysUtcDateTime().  This would only require ~4 Logical IOs to get to the first page, and then every Logical IO would result in ~700 (A,B) pairs.


Restore Database wizard won't open in SSMS 2014

I have seen that behavior myself in both SSMS 2012 and SSMS 2014. What it typically means is that the backup / restore history for the database is very long and it is taking a long time for the wizard to come up, because it needs to read the entire history.

Make sure you have a maintenance plan that truncates the history. To find out, run this query:

SELECT TOP 1 backup_start_date
FROM msdb.dbo.backupset WITH (NOLOCK)
ORDER BY backup_set_id ASC

That query will be quick, since it's sorted by the primary clustered key of the table. If you've got more than a month or two of history in there, it's probably time to clear it out. (Keep in mind that clearing the history will involve locks, so it can be a slow process if you're doing backups at the same time, or if your MSDB is on slow storage.)


MySQL Coalesce date missing results, why?

Because it converts the datetimes to strings and does the comparisons with the string values. And '2019-12-19 00:00:00' <= '2019-12-19' is false in text comparison.


SQL Server 2014 Not recognizing 64 bit drivers for Office

When attempting to reproduce your issue, I received the following error:

  the 'microsoft.ace.oledb.12.0' provider is not registered on the local
  machine

I downloaded the 2007 Office System Driver:  Data Connectivity Components
to resolve the error.  When I choose to export data to Excel and choose a file name of .xls, the export will error at 65,536 rows.  If I choose to export data to Excel and select a file name of .xlsx, the export will complete without error.  This is because in Excel 2010 there is a 1048576 row limit per sheet, and in Excel 97 there is a 65536 row limit per sheet.


The execution speed of a database server depends on a great many factors, for example

concurrent load, what else is happening - that is if the database is
busy doing something else your query may have to wait
CPU speed, if the basic computing power of the database is slow your query will be executed slower than it would be otherwise
available disk IO bandwidth, if
the data is not cached in RAM and has to be fetched from a disk the
speed of the disk will affect performance
structure of the data, is it indexed to allow the DB to find your row more quickly?
network speed, how long does it take for the query to arrive at the DB and for the result to return to you app?

However to seek a single row out of 800,000 rows on a single table assuming the presence of an index on the column "id" that is specifying the row of data you want, it is likely to take a tiny fraction of a second on modern hardware. In fact the network round trip to your database server is likely to take longer than the database operation.


I found the issue through screenshare with a user. The ODBC DSNs on some client machines were set up to use Windows Authentication, and the ODBC implementation in the applications was rather simplistic, so when running the application it was using our Windows logins. It worked for me since I am sysadmin on the database server.

The ODBC DSN setup has been changed to use SQL Authentication with a username and password, and now it works.


What is the actual behavior of compatibility level 80?

From the docs:

  Sets certain database behaviors to be compatible with the specified version of SQL Server.
  ...

  Compatibility level provides only partial backward compatibility with earlier versions of SQL Server. Use compatibility level as an interim migration aid to work around version differences in the behaviors that are controlled by the relevant compatibility-level setting.

In my interpretation, compatibility mode is about behavior and parsing of syntax, not for things like the parser saying, "Hey, you can't use ROW_NUMBER()!" Sometimes the lower compatibility level allows you to continue getting away with syntax no longer supported, and sometimes it prevents you from using new syntax constructs. The documentation lists several explicit examples, but here are a few demonstrations:

Passing built-in functions as function arguments

This code works in compatibility level 90+:

SELECT *
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL);

But in 80 it yields:

  Msg 102, Level 15, State 1Incorrect syntax near '('.

The specific problem here is that in 80 you aren't allowed to pass a built-in function into a function. If you want to stay in 80 compatibility mode, you can work around this by saying:

DECLARE @db_id INT = DB_ID();

SELECT *
FROM sys.dm_db_index_physical_stats(@db_id, NULL, NULL, NULL, NULL);

Passing a table type to a table-valued function

Similar to the above, you can get a syntax error when using a TVP and trying to pass it to a table-valued function. This works in modern compat levels:

CREATE TYPE dbo.foo AS TABLE(bar INT);
GO
CREATE FUNCTION dbo.whatever
(
  @foo dbo.foo READONLY
)
RETURNS TABLE
AS
  RETURN (SELECT bar FROM @foo);
GO

DECLARE @foo dbo.foo;
INSERT @foo(bar) SELECT 1;
SELECT * FROM dbo.whatever(@foo);

However, change the compatibility level to 80, and run the last three lines again; you get this error message:

  Msg 137, Level 16, State 1, Line 19
  Must declare the scalar variable "@foo".

Not really any good workaround off the top of my head, other than upgrading the compat level or getting the results a different way.

Using qualified column names in APPLY

In 90 compatibility mode and up, you can do this without problem:

SELECT * FROM sys.dm_exec_cached_plans AS p
  CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t;

However, in 80 compatibility mode, the qualified column handed to the function raises a generic syntax error:

  Msg 102, Level 15, State 1Incorrect syntax near '.'.

ORDER BY an alias that happens to match a column name

Consider this query:

SELECT name = REVERSE(name), realname = name
FROM sys.all_objects AS o
ORDER BY o.name;

In 80 compatibility mode, the results are as follows:

001_ofni_epytatad_ps   sp_datatype_info_100
001_scitsitats_ps      sp_statistics_100
001_snmuloc_corps_ps   sp_sproc_columns_100
...

In 90 compatibility mode, the results are quite different:

snmuloc_lla      all_columns
stcejbo_lla      all_objects
sretemarap_lla   all_parameters
...

The reason? In 80 compatibility mode, the table prefix is ignored entirely, so it is ordering by the expression defined by the alias in the SELECT list. In newer compatibility levels, the table prefix is considered, so SQL Server will actually use that column in the table (if it is found). If the ORDER BY alias is not found in the table, the newer compatibility levels are not so forgiving about ambiguity. Consider this example:

SELECT myname = REVERSE(name), realname = name
FROM sys.all_objects AS o
ORDER BY o.myname;

The result is ordered by the myname expression in 80, because again the table prefix is ignored, but in 90 it generates this error message:

  Msg 207, Level 16, State 1, Line 3
  Invalid column name 'myname'.

This is all explained as well in the documentation:

  When binding the column references in the ORDER BY list to the columns defined in the SELECT list, column ambiguities are ignored and column prefixes are sometimes ignored. This can cause the result set to return in an unexpected order.
  For example, an ORDER BY clause with a single two-part column (<table_alias>.<column>) that is used as a reference to a column in a SELECT list is accepted, but the table alias is ignored. Consider the following query.
  SELECT c1 = -c1 FROM t_table AS x ORDER BY x.c1
  When executed, the column prefix is ignored in the ORDER BY. The sort operation does not occur on the specified source column (x.c1) as expected; instead it occurs on the derived c1 column that is defined in the query. The execution plan for this query shows that the values for the derived column are computed first and then the computed values are sorted.

ORDER BY something not in the SELECT list

In 90 compatibility mode you can't do this:

SELECT name = COALESCE(a.name, '') FROM sys.objects AS a
UNION ALL
SELECT name = COALESCE(a.name, '') FROM sys.objects AS a
ORDER BY a.name;

Result:

  Msg 104, Level 16, State 1
  ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

In 80, though, you can still use this syntax.

Old, icky outer joins

80 mode also allows you to use the old, deprecated outer join syntax (*=/=*):

SELECT o.name, c.name
FROM sys.objects AS o, sys.columns AS c
WHERE o.[object_id] *= c.[object_id];

In SQL Server 2008 / 2008 R2, if you're in 90 or greater, you get this verbose message:

  Msg 4147, Level 15, State 1
  The query uses non-ANSI outer join operators ("*=" or "=*"). To run this query without modification, please set the compatibility level for current database to 80, using the SET COMPATIBILITY_LEVEL option of ALTER DATABASE. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backward-compatibility modes.

In SQL Server 2012, this is no longer valid syntax at all, and yields the following:

  Msg 102, Level 15, State 1, Line 3
  Incorrect syntax near '*='.

Of course in SQL Server 2012 you can no longer work around this problem using compatibility level, since 80 is no longer supported. If you upgrade a database in 80 compat mode (by in-place upgrade, detach/attach, backup/restore, log shipping, mirroring, etc.) it will automatically be upgraded to 90 for you.

Table hints without WITH

In 80 compat mode, you can use the following and the table hint will be observed:

SELECT * FROM dbo.whatever NOLOCK;

In 90+, that NOLOCK is no longer a table hint, it's an alias. Otherwise, this would work:

SELECT * FROM dbo.whatever AS w NOLOCK;

But it doesn't:

  Msg 1018, Level 15, State 1
  Incorrect syntax near 'NOLOCK'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.

Now, to prove that the behavior is not observed in the first example when in 90 compat mode, use AdventureWorks (making sure it's in a higher compat level) and run the following:

BEGIN TRANSACTION;
SELECT TOP (1) * FROM Sales.SalesOrderHeader UPDLOCK;
SELECT * FROM sys.dm_tran_locks
  WHERE request_session_id = @@SPID
  AND resource_type IN ('KEY', 'OBJECT'); -- how many rows here? 0
COMMIT TRANSACTION;

BEGIN TRANSACTION;
SELECT TOP (1) * FROM Sales.SalesOrderHeader WITH (UPDLOCK);
SELECT * FROM sys.dm_tran_locks
  WHERE request_session_id = @@SPID
  AND resource_type IN ('KEY', 'OBJECT'); -- how many rows here? 2
COMMIT TRANSACTION;

This one is particularly problematic because the behavior changes without an error message or even an error. And it's also something that the upgrade advisor and other tools might not even spot, since for all it knows, that's a table alias.

Conversions involving new date/time types

The new date/time types introduced in SQL Server 2008 (e.g. date and datetime2) support a much larger range than the original datetime and smalldatetime). Explicit conversions of values outside the supported range will fail no matter what the compatibility level, for example:

SELECT CONVERT(SMALLDATETIME, '00010101');

Yields:

  Msg 242, Level 16, State 3The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value.

However, implicit conversions will work themselves out in the newer compatibility levels. For example this will work in 100+:

SELECT DATEDIFF(DAY, CONVERT(SMALLDATETIME, SYSDATETIME()), '00010101');

But in 80 (and also in 90), it yields a similar error as above:

  Msg 242, Level 16, State 3The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Redundant FOR clauses in triggers

This is an obscure scenario that came up here. In 80 compatibility mode, this will succeed:

CREATE TABLE dbo.x(y INT);
GO
CREATE TRIGGER tx ON dbo.x
FOR UPDATE, UPDATE
------------^^^^^^ notice the redundant UPDATE
AS PRINT 1;

In 90 compatibility and higher, this no longer parses, and instead you get the following error message:

  Msg 1034, Level 15, State 1, Procedure tx
  Syntax error: Duplicate specification of the action "UPDATE" in the trigger declaration.

PIVOT/UNPIVOT

Some forms of syntax won't work under 80 (but work just fine in 90+):

SELECT col1, col2
FROM dbo.t1
UNPIVOT (value FOR col3 IN ([x],[y])) AS p;

This yields:

  Msg 156, Level 15, State 1
  Incorrect syntax near the keyword 'for'.

For some workarounds, including CROSS APPLY, see these answers.

New built-in functions

Try using new functions like TRY_CONVERT() in a database with compatibility level < 110. They are simply not recognized there at all.

SELECT TRY_CONVERT(INT, 1);

Result:

  Msg 195, Level 15, State 10
  'TRY_CONVERT' is not a recognized built-in function name.

Recommendation

Only use 80 compatibility mode if you actually need it. Since it will no longer be available in the next version after 2008 R2, the last thing you want to do is write code in this compat level, rely on the behaviors you see, and then have a whole bunch of breakage when you can no longer use that compat level. Be forward thinking and don't try to paint yourself into a corner by buying time to continue using old, deprecated syntax.


Change database used for SQL Server Integration Services logging

Blowing Kin's comment up to an answer...

Logging in the package deployment model (2005, 2008/2008R2 and potentially 2012/2014/2016 depending on how the project is defined) is done through an explicit request within each package.

A package looks something like this. At the bottom, you see 3 entries under Connection Managers: msdb, SO_34033143 and NotMsdb. The middle is a flat file connection manager, the other two are OLE DB connection managers. All a Connection Manager does is define the specifics of the entity the package is going to work with: this database on that server logging in as user123. A CSV named XYZ.csv, etc.

It's important to note that the name given to a Connection Manager may have no bearing on what it is actually connecting to. For example, the one named "msdb" is actually pointing to tempdb because I'm lazy.

If you right click in the grey background between the Data Flow Task and the Connection Managers, you should get a context sensitive menu and pick Logging

In the resulting screen, you should see a menu pop up that has on the right hand side "SSIS Log Provider for SQL Server" checked. The third column indicates the connection manager that is being used to log SSIS.

What you'll want to do and again, this is for every package, go in and change the connection manager from the thing that is pointing to msdb and either use an existing connection manager or add a dedicated connection manager into the packages.

Personally, in my pre-2012 environments, I advocated for an SSISConfig/SSIS_Config database. I used that for logging and configuration across a given tier/class of servers (dev/stage/prod).

If you're handy with scripting, you can programatically open every SSIS package and change the logging provider but it's not for the faint of heart. I don't believe I have that code online but if you're super desperate, ping me via comment and I'll see if I can find that hard drive.


How to determine the size of my tables in the SQL Server database

If you don't want to write a script, you can also open the much underused 'Object Explorer Details' in SSMS (Shortcut key F7).

From the Top-Level, open the Tables folder to get a list of all the tables in your database.

You may need to customise the columns to see the Space Used. This can be done by right clicking on the header row and choosing the columns you wish to display.

There's plenty more data like this available in Object Explorer Details.


Community wiki answer:

You're basically right.

It will also help if you define your separate tables with a certain amount of free space (see fillfactor) so that PostgreSQL can perform Heap-only tuples updates.

See Increase the speed of UPDATE query using HOT UPDATE (Heap only tuple) by Anvesh Patel.


Does Log File contain both committed and uncommitted transactions?

SQL Server uses write-ahead logging so that changes (committed or not) are always written to the log file prior to being written to any data file.

Records written to the transaction log do not necessarily reflect the entire page being modified.  Log records typically consist of compensation data that allow transactions to be rolled forward and back.  COMMIT flushes the log buffer along with a record of the commit to guarantee durability.  See the documentation.

In addition to commit, log buffers are flushed when they become full and during checkpoint operations.  The log may contain uncommitted transactions and it doesn't matter how long transactions run.  During crash recovery, all transactions from the last completed checkpoint are rolled forward from log records and then uncommitted transactions are rolled back. The database is then consistent, containing only committed transactions.

The above was converted from comments made by Dan Guzman


Use a unique function-based index:

CREATE UNIQUE INDEX countryname_unique ON country (LOWER(countryname));

Much better than messing with collations.


Using MySQL workbench, how to prevent new foreing key columns when modeling

When you create a relationship you can use one of the tools reachable with the buttons in the vertical toolbar (or the hotkeys 1-6). The last one () is what you are looking for. It let's you pick the columns to be used for a relationship instead of creating new columns for it.


Restore RMAN Level 0 without any archivelogs?

Of course. A consistent full backup created in MOUNT mode does not require any archivelogs to be successfully restored.

An online backup is inconsistent and requires archivelogs for restoring a consistent state.


SSMS 2008 / 2014 showing different values for TempDB file sizes

To be certain you are configuring tempdb file sizes as intended, you should use T-SQL instead of the SSMS user interface.

To resize files, you can use something like this:

ALTER DATABASE tempdb
MODIFY FILE (NAME=tempdev, SIZE=1GB, GROWTH=1GB);

You'd need to execute this once for each logical file in tempdb.  NAME=tempdev_2, NAME=tempdev_3 etc.

I'd suggest using a T-SQL query to obtain information about the file sizes, as in:

SELECT DatabaseName = d.name
    , DBFileName = mf.name
    , DBInitialSize = mf.size
    , Growth = mf.growth
    , GrowthIsPercentage = mf.is_percent_growth
    , FileType = mf.type_desc
FROM master.sys.master_files mf
    INNER JOIN master.sys.databases d ON mf.database_id = d.database_id
WHERE d.name = 'tempdb'
ORDER BY d.name, mf.name;

As mentioned in the comments, you should certainly look into the possibility of using Trace Flag 1117 and 1118, if you are concerned about tempdb performance; ostensibly this is the root-cause of your questions about tempdb.

Aaron Bertrand has a great article about using the SSMS Interface vs T-SQL at:

https://sqlblog.org/2009/10/14/bad-habits-to-kick-using-the-visual-designers


It depends on what you understand with "sharding" - a fully automated, declarative way of (automatically) distributing data across different servers?

Then the answer is no, there is nothing built-in.

There are some products/extensions that do this like BDR, Postgres-XL  or Citus (which is the successor of an extension named pg_shard)

If you are just looking for some way to distribute data over multiple servers, this can be implemented using foreign data wrappers combined with inheritance. But that involves many manual steps.

A setup could look like this:

Say you have a "sales" table that you want to distribute over several countries. For each country you have your own Postgres server that only stores the country specific data. Something like this:

create table customer
(
   id integer,
   .... other columns
);

The for each shard node you need to create the same table in the Postgres server for that shard.

Then create foreign servers for each node:

create server server_one foreign data wrapper postgres_fdw options (host 'host_one', port '5432', dbname 'postgres');
create server server_two foreign data wrapper postgres_fdw options (host 'host_two', port '5432', dbname 'postgres');
create server server_three foreign data wrapper postgres_fdw options (host 'host_three', port '5432', dbname 'postgres');

Then on the master server you create one foreign table for each country as a child table of the master table.

create foreign table customer_one() INHERITS (customer) server server_one options ( table_name 'customer' );
create foreign table customer_two() INHERITS (customer) server server_two options ( table_name 'sales' );
create foreign table customer_three() INHERITS (customer) server server_three options ( table_name 'customer' );

In order to tell Postgres that each child table has a distinct set of rows, you create the necessary check constraints:

alter table customer_one add constraint check_id check (id > 0 and id <= 1000);
alter table customer_two add constraint check_id check (id > 1000 and id <= 2000);
alter table customer_three add constraint check_id check (id > 2000 and id <= 3000);

You need to make sure that the ranges in the check constraints do not overlap.

Now if you query the master table, e.g. like this:

select *
from customer
where id = 100;

you will see that Postgres retrieves that data from the first server and that the condition is pushed down to the remote server.

You will also need to write triggers to allow DML statements on the master table that would insert into the approriate "shards".


mysql index on varchar and date field for improve query speed of select and insert query

MyISAM and Many insert queries ?

I definitely recommend to use InnoDB instead of MyISAM. (Don't forget to create backup before change storage)

ALTER TABLE gsm_smslog220717 ENGINE = InnoDB;

If most of your queries for gsm_smslog220717 table like that, you can create composite index for speed up Select query.

ALTER TABLE `gsm_smslog220717` ADD INDEX `affiliateid` (`affiliateid`, `subdt`, `status`);


Yes that behaviour is something that I should report to postgres as use of different flags doesnt seem to have an effect and it just doesnt work. To solve it use:

create DB

createdb -h HOST -U USER -W DB_NAME

restore and in case of Error Stop

egrep -v 'EXTENSION.*plpgsql' DUMPFILE.sql | psql -h HOST -U USER -W  -d DB_NAME -v ON_ERROR_STOP=1


Can I pass a single record from a recordset to a module?

Direct answer regarding Recordsets:

For Recordset objects used by Access (DOA -the default used by Access, or ADO) the short answer is "No".  Regrettably there is no concept of a "Row" object in these older data models, so you cannot pass all values of a particular row independently of the Recordset.

You could pass an entire Recordset object once it is positioned at a particular record.  A function could retrieve the necessary values (via the Fields collection) then leave the Recordset unaltered so that the calling function could continue looping through all the rows.

UPDATED Recommendation after comments

Honestly, I would try eliminating the stored calculated values if the system proves efficient enough to do all calculations dynamically.  The only way to determine this is to try it.  If it proves much too slow (which can honestly become much more annoying than the current system), then I would revert back to a stored, calculated value with a recalculate button and all.

Since the calculated value is not dependent upon other database or external queries, it should be efficient enough to use a parameterized function as you have already written.  The benefit to such a function is its flexibility: It can be called from another function and method, from within a query, or from a form control element.

(FYI: Consideration of additional database and/or external queries is important, because if it were dependent upon other query services, these can often require proper connection management, and/or take a long time, etc.  This often complicates the solution and can justify the alternatives.)

You could also create another function that takes just the primary key (e.g. ID) for a given record. It then queries the database tables directly just for the given ID value, then calls the original function using values retrieved from the recordset.  Such a function can be useful when the full record is not immediately available or convenient.  But be careful since using this in a query of thousand or records can freeze Access since it is repeatedly opening and closing a recordset for each function call, often much slower than passing parameters.

'* Assume the original function is RedemptionEstimate(p1, p2, ...)
Public Function RedemptionEstimateFromID(vID as Variant)
  If Not IsNumeric(vID) Then
    '* Avoid problems with null (and SQL injection concerns)
    '* Consider other error handling code
    RedemptionEstimateFromID = Null
    Exit Sub
  End If

  Dim db As Database
  Dim rs As Recordset2
  On Error Goto catch

  Set db = CurrentDb '* Not critical for a single recordset, but good habit if performing multiple queries
  Set rs = db.OpenRecordset("SELECT * FROM DataTable WHERE ID=" & vID)
  If rs.EOF Then
    '* Assuming ID is unique primary key, rs has only one record
    '* Call original function, passing values from query as parameters
    RedemptionEstimateFromID = RedemptionEstimateFrom(rs!Col1, rs!Col2, ...)
  Else
    '* ID not found... Consider other error handling code
    RedemptionEstimateFromID = Null
  End If
  rs.Close '* Would be closed automatically, but good habit to close explicitly

  Exit Function
catch:
  '* ID not found... Consider other error handling code
  Debug.Print "Error " & err.number & ": " & err.Description
  RedemptionEstimateFromID = Null
End Function

When displaying on a form with thousands of records, it can be a serious waste to calculate dynamic values for all records in the query.  Instead, the value can be calculated just for the displayed record(s).  One drawback to this can be that such a field can't be used to filter the query.  To calculate the value for the currently-displayed record, add a new control to the form and its Control Source property, add a call to your function passing from values as parameters:  = RedemptionEstimate([Col1], [Col2], ...).

Regarding alternatives:

Indeed, this approach is discouraged for volatile values.  However, it can also be a valid technique if it is known that values seldom change.  In that case, a static table field can be much more efficient and easier to work with than repeatedly recalculating values dynamically.  This technique requires a well-established and reliable update process and schedule.  It might seem annoying now, but if you could automate such a process that runs outside of normal times it could still be a reasonable approach.  Consider answering these questions rather than basing it off the "annoyance" factor :)

Do these values change for all records on the same schedule?
Does recalculating values dynamically slow down queries and related user interfaces?
Could an update process be automated to run outside times that effect other workflows?
Do you often experience outdated values for particular records with the current process?
Do such outdated values have seriously negative effects?

To answer this question fully and properly requires that you first specify the requirements and behavior of the form.  Do you need to update the source-table values or only view them?  Will the form load only a few records at a time or all 10,000 that you mentioned?  How long does it take to calculate the "RedemptionEstimates"--for a single row, for the entire set of records?

Creating a temporary table is a viable alternative, but if you plan on updating values back to the original table, you must ensure proper indexes and relationships are defined, etc. Worst case you need to then write extra code to update the tables.  But I'd wait to recommend details until you answer the other questions.

Other Comments:

Passing values into a function from a query is a legitimate technique.  Perhaps 13 parameters is a lot and it is good that you're exploring possibilities.  However, you should be able to resolve the #Error values with proper null handling and debugging. Don't give up on this technique since it might prove to be the best solution.

Sometimes it is necessary to explicitly loop through each row of a recordset, but don't get caught up in loops.  The proper technique is to bind a record source to a form or report.  There is no need to loop through and explicitly "print" each record on a form.  Access is built to do such tasks for you.  If you specify a Row Source for the form (or report), the form will automatically open the query and create its own Recordset behind the scenes.

(I discourage explicitly creating a Recordset then binding it to the form yourself. Access technically allows this, but it is buggy.  Trying to create and update an empty field in an existing recordset also complicates things and I think requires ADO Recordsets instead of the default DAO.  In other words, I suggest not going down this path.)


There are a few parts to implement proper security in your environment:

SSIS packages are encrypted by default when referring to sensitive information, you can set with their own password
SSIS can also be signed by a certificate
There is a master key, that ideally would be in a KM outside the SQL Server
There is a database key
There is the user that you would use for running the package, which you should grant specific access and deny access to anything the package shouldn't touch

There is nothing that says you would have the keys on the same server; but even if you have them in the same server, it doesn't mean they would have the same security user. Best practice would be to have a separate KM server.


MySQL Commited data not seen to select query

I try to make an hypothesis about the second question:

  Given the commit happens before the select (commit is at 14:16:06 and
  select is at 14:16:07) how is it that the select does not return the
  row inserted by the transaction?

Transactions are managed by Spring. So it would be possible that before running the select spring has raised a start transaction or it has already used the connection for running an another query.

I start a first session where I simulate an insert into a table t:

session1> create table t (i int auto_increment primary key);
Query OK, 0 rows affected (0,32 sec)

session1> insert into t values();
Query OK, 1 row affected (0,00 sec)

session1> select * from t;
+---+
| i |
+---+
| 1 |
+---+
1 row in set (0,00 sec)

session1> start transaction;
Query OK, 0 rows affected (0,00 sec)

session1> insert into t values();
Query OK, 1 row affected (0,00 sec)

I create a new session, session2, where autocommit is set to 0. Into this new session, a transaction is implicitly started when running a select.

session2> set autocommit = 0;
Query OK, 0 rows affected (0,00 sec)

session2> select * from t;  -- this starts a transaction
+---+
| i |
+---+
| 1 |
+---+
1 rows in set (0,00 sec)

Move to session1 to commit the insert.

session1> commit;

Now move again to session2:

session2> select * from t;
+---+
| i |
+---+
| 1 |
+---+
1 row in set (0,00 sec)

Session2 cannot see the row just inserted. If a commit is raised in session2 we can see new row inserted in session1

session2> commit
1 row in set (0,00 sec)

session2> select * from t;
+---+
| i |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0,00 sec)

The general log looks like :

150804 14:04:10     2 Query select * from t

150804 14:04:30     1 Query start transaction
150804 14:04:39     1 Query insert into t values ()
150804 14:04:44     1 Query commit
150804 14:04:51     2 Query select * from t

150804 14:05:07     2 Query commit
150804 14:05:10     2 Query select * from t

The first row is related to session 2. It is when session 2 opens the transaction.

I don't know if this is what happen in your case. You could check in your general log if the connection_id 36 was used for other queries. Let we know.


What is the "other miscellaneous information" in the Shared Global Area (oracle database)?

That "other stuff" is everything else that the database needs to run.

That's a whole lot of things, most of which you'll never need to care about at all. If you're curious to see what the different memory areas are in your system, you can query the v$sgainfo view and take a peek. Warning: there's a lot of them (1058 entries on a 12c database I'm connected to right now).

For instance, you'll find a "resize operation history" bucket which should hold the information about dynamic resizing operations (which you can query via v$sga_resize_ops view).

Not all of these are documented (mostly because you don't actually care about the majority), but you'll find information about some of them scattered around the web (and occasional bugs related to some of them) if you're curious.

If you're interested about Oracle's memory architecture, the Concepts Guide's Memory Architecture sections is quite detailed already, you'll rarely need to mess with things that aren't mentioned there.


Create a new table that have all the columne in all your 5 tables, then create the needed SQL statment like SELECT * FROM Tb1 INTO TableWithAllColumns
eventually add some WHERE to exclude the duplicate rows from the insert.

This will work if your table doesent have similar rows.
If you need to merge similar rows together, there is no a simple  way to tho this.
The best you can do is find, or write, a tool that can compare all the value in all the columns and act in the right way.


If you are completely deleting all the values from the lookup table, have you considered just truncating them?  That would save on TLOG activity and automatically reseed the values.
You can't substitute parameters like that in queries, at least not for object names.  You would need to do this dynamically.
Please see an example for you below.  I beefed up the sanity check for you for edge cases you may not have considered... hopefully the below is clear.

CREATE PROCEDURE DBA.ReseedIdentity
(
    @TableName SYSNAME
    , @IdentityColumn SYSNAME
)
AS
BEGIN

/** Sanity Checking **/
IF @TableName IS NULL OR @IdentityColumn IS NULL
BEGIN
    ;THROW 50000, 'Must specify table name and Identity column name.', 1;
END

IF OBJECT_ID(@TableName) IS NULL
BEGIN
    ;THROW 50000, 'Tablename does not exist.', 1;
END

IF NOT EXISTS (SELECT TOP (1) 1 FROM sys.columns AS C WHERE C.name = @IdentityColumn AND C.object_id = OBJECT_ID(@TableName))
BEGIN
    ;THROW 50000, 'Identity column is not found on specified table.', 1;
END

/** Reset Identity
    Assumes Identity column is an INT
    **/
DECLARE @SQLCommand NVARCHAR(4000)

SET @SQLCommand = N'
    DECLARE @MaxID INT;
    SET @MaxID = COALESCE((SELECT MAX(' + QUOTENAME(@IdentityColumn) + ') FROM ' + QUOTENAME(@TableName) + '), 1);

    DBCC CHECKIDENT(' + QUOTENAME(@TableName, '''') + ', RESEED, @MaxID);
    ';

EXEC sp_executesql @SQLCommand;

END


You may use the psql variable table like this:

ALTER TABLE :table ADD COLUMN...

psql will interpolate :table and send this query:

  ALTER TABLE realtablename ADD COLUMN...

to the SQL interpreter,
when it's called with -v table=realtablename

There's a pre-condition, though, that the table's name doesn't require quoting.

The SELECT may be written as:

SELECT pgr_createTopology(:'table', 0.00001, 'geom', 'gid');

then :table will be interpolated to a literal string, so the query actually produced would be like:

SELECT pgr_createTopology('realtablename', 0.00001, 'geom', 'gid');

However, this technique can't really be used for more complicated things like the CREATE INDEX mentioned, as it doesn't need the variable's value but another value based on it.

Personally for that sort of thing, I would embed the SQL script inside a shell script and let the shell do the interpolating. Example:

#!/bin/bash
# ...
tablename="$1"
psql [options] <<EOF
ALTER TABLE ${tablename} ADD COLUMN "source" integer;
-- ...
CREATE INDEX ${tablename}_source_idx ON $tablename("source");

EOF

The nice thing with the shell is that it has no problem concatenating with ${tablename}_source_idx ,whereas psql will not accept :"tablename"_source_idx or (as far as I know) offer a simple alternative.

Anyway, if $tablename can contain problematic characters such as separators or quotes, either double or single or both, none of the above is  good enough. As your SQL statements need the table's name both as a literal string and as an identifier, the problem is not as easy as it would seem.

The ultimate solution, to cover for any syntactic hazard in the table's name, could be to define and call a temporary plpgsql function that uses dynamic SQL functionalities.

/* inside SQL script */
CREATE FUNCTION pg_temp.create_stuff(tablename text) returns void AS $$
BEGIN
  EXECUTE format('ALTER TABLE %I ADD COLUMN source INTEGER', tablename);
  EXECUTE format('ALTER TABLE %I ADD COLUMN target INTEGER', tablename);

  --
  EXECUTE format('SELECT pgr_createTopology(%L, 0.00001, ''geom'', ''gid'')',
    tablename);
  --
  EXECUTE format('CREATE INDEX %I ON %I(source)',
   tablename||'_source_idx', tablename);
  EXECUTE format('CREATE INDEX %I ON %I(target)',
   tablename||'_target_idx', tablename);
END
$$ language plpgsql;

/* still inside SQL script, call function with table as string literal */
select pg_temp.create_stuff(:'table');

I don't suggest a DO block because they don't take input parameters.


Create an "alias" database with the name you want to use.
Create synonyms for the objects by querying the system tables to create a simple script, here for example for user tables.
select
  'Create Synonym '
   +quotename(object_schema_name(id, db_id()))+'.'
   +quotename(o.name)+
   ' For [SourceDB].'
   +quotename(object_schema_name(id, db_id()))+'.'
   +quotename(o.name)+';'
from sysobjects o where type = 'U'


From a logical ERD perspective, the appropriate design is clearly the entity supertype/subtype pattern.  What you're wrestling with is how to implement this from a physical perspective.

Like a lot of data modeling issues, there is no hard and fast rule.  You are looking at a compromise.  Do you want a fair bit more complexity in your application logic and queries (supertype/subtype) or do you want the risk that your application logic might not properly enforce your constraints and that a Car record in your consolidated vehicle table might get a non-null value in the anchor_weight column?

The kinds of things that you should consider in deciding how to trade off these alternatives are:

How many subtype dependent columns are there?
How likely is it that new subtypes will be defined in the future - or that new subtype dependent columns will be needed?
How serious would it be if inappropriate data (column values) were recorded for some records?
Where do you stand philosophically? (e.g. "Null columns are evil", "Application-enforced constraints are evil", "1:1 relationships are evil", "Inner joins are evil", etc.)
How many queries do I have which target only one subtype?
Do I have any programming environment constraints which make supertype/subtype physical models harder to work with (e.g. heavy use of ORMs or simplistic tools like Access).

At the end of the day, you are going to make a practical decision based on what is most important to you.  Whatever you decide will have pros and cons - but they will be your pros and cons.


How to list all past remote login information

You can login with (IP,Port Number) to remote server.By default the SQL Server don't log the logins.

If you have pretty clean logs then you shall not get login history data.
if you have configure SQL Login Audit before pretty clean log. Then some point of time there will be changes that to get the 'SQL Login Audit' details through TSQL like

EXEC xp_readerrorlog;

It will give only that 'SQL login audit details' which you had configure SSMS. like

1) Failed logins only
2)Successful logins only
3)Both Failed and successful logins

As per MSDN BOL https://msdn.microsoft.com/en-us/library/ms176013.aspx For remote server login details of current user you can find out from TSQL.

SELECT login_name ,COUNT(session_id) AS session_count
FROM sys.dm_exec_sessions
GROUP BY login_name;

for ref https://www.mssqltips.com/sqlservertip/1735/auditing-failed-logins-in-sql-server/ and https://msdn.microsoft.com/en-us/library/cc280728.aspx


How do I list all columns for a specified table that begin with a specific string

You can use INFORMATION_SCHEMA views for this purpose:

create table tbl
(
    id serial,
    img1 text,
    img2 text,
    foo int,
    bar int,
    img14 text
);

select table_catalog, table_name, column_name
from   information_schema.columns
where  table_catalog = 'postgres'
and    table_name = 'tbl'
and    column_name like 'img%';

table_catalog | table_name | column_name
:------------ | :--------- | :----------
postgres      | tbl        | img1
postgres      | tbl        | img2
postgres      | tbl        | img14

db<>fiddle here


Removing all Schemas and Objects from a database, excluding specific Schemas and Objects

I've cracked it! Thought I'd share in case anyone else was interested too.

CREATE TABLE #Safe
    ( [Schema] NVARCHAR(50), [Table] NVARCHAR(100) )

INSERT INTO #Safe
SELECT SCHEMA_NAME(schema_id) AS [schema], [name]
    FROM sys.tables
        WHERE SCHEMA_NAME(schema_id) IN ('1', '2')
        OR [name] LIKE ('1_%')
        OR [name] LIKE ('2_%')
        OR [name] = 'DatabaseVersion'

DECLARE cur_delete CURSOR FOR
SELECT SCHEMA_NAME(schema_id) AS [schema], [name]
    FROM sys.tables
        WHERE SCHEMA_NAME(schema_id) NOT IN (SELECT [Schema] FROM #Safe)
        OR [name] NOT IN (SELECT [Table] FROM #Safe)

OPEN cur_delete

DECLARE @Schema NVARCHAR(50)
DECLARE @Table sysname
DECLARE @SQL NVARCHAR(MAX)

FETCH NEXT FROM cur_delete
INTO @Schema, @Table;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQL = 'DROP TABLE [' + @Schema + '].[' + @Table + '];';
    PRINT @SQL
    -- EXEC sp_executesql @SQL;

FETCH NEXT FROM cur_delete INTO @Schema, @Table
END

CLOSE cur_delete
deallocate cur_delete

DROP TABLE #Safe

Found this post: Drop all tables from database command apart from named few

It included a Cursor that, with some modification, I was able to get working for my needs.


Why is a table showing up twice in pg_class?

Turns out I had the same table (same name) within two different schemas.  So each had an entry in pg_class.
Work around from the wiki page is SELECT * FROM pg_class WHERE oid = 'schema_name.table_name'::regclass;


Here is the problem:

You update the table
It triggers the update trigger: updateInvisibility
In the trigger you update the table again so it triggers the updateInvisibility trigger

and then it keeps doing that so you end up triggering the trigger many times in a sort of recursive fashion without even realizing. SQL server will allow this call nesting to be 32 levels deep and then it throws this error:

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)

which is pretty clear.
T-SQL Solution
The code-only solution is to check the nesting level and leave if bigger than 1 (1 is the first time it is called by the update):
IF TRIGGER_NESTLEVEL() > 1 RETURN;

Like this:
ALTER TRIGGER [dbo].[updateInvisibility]
ON [dbo].[table]
AFTER UPDATE
AS
BEGIN
    IF TRIGGER_NESTLEVEL() > 1 RETURN;

    -- Do your work...
END

Alternative
As pointed out in a comment by RBarryYoung, you can prevent this problem in the first place by disabling recursive triggers.


FYI, unless specifically directed, all TSQL statements are presumed Snap-Shots.

1st Question:

So Think of it as your SSRS is looking at your data as if it were a View/Select statement.

2nd Question:

It could, pending on the volume of calculation (SSRS) and volume of users.  Think you would have to have well over 10% CPU/RAM utilization from SSRS and over 50% CPU/RAM utilization from general queries in order for you to notice an effect, albeit hardly noticeable (like 1-10 nSecs).


In order to grant the CREATE VIEW privilege, your user needs the CREATE VIEW privilege WITH ADMIN OPTION, or the GRANT ANY PRIVILEGE system privilege. Otherwise you have insufficient privileges.

http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_9013.htm#i2094944

  To grant a system privilege, one of the following conditions must be
  met:

  You must have been granted the GRANT ANY PRIVILEGE system privilege.
  In this case, if you grant the system privilege to a role, then a user
  to whom the role has been granted does not have the privilege unless
  the role is enabled in user's session.
  You must have been granted the system privilege with the ADMIN OPTION.
  In this case, if you grant the system privilege to a role, then a user
  to whom the role has been granted has the privilege regardless whether
  the role is enabled in the user's session.


Second index on junction table with composite primary key: include both or one column

You should go for the first one.

With the second option you will end up with the first one anyway but this is implicit and so not necessarily obvious to all potential consumers.

(SQL Server always adds the clustered index key to all non clustered indexes - if they are not declared as unique they will become part of the key)


Stopping multiple server side SQL Server traces immediately

Below are a couple of T-SQL examples that will stop and delete server-side SQL traces or Extended Event traces you can customize for your particular situation.

SQL Trace:

DECLARE @sql nvarchar(MAX) = (
    SELECT 'EXEC sp_trace_setstatus ' + CAST(id AS varchar(10)) + ', 0;
EXEC sp_trace_setstatus ' + CAST(id AS varchar(10)) + ', 2;
'
    FROM sys.traces
    WHERE path LIKE '%\yourtracefilenamepattern%.trc'
    ORDER BY id
    FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)');
PRINT @sql;
--EXEC(@sql);

Extended Event trace:

DECLARE @sql nvarchar(MAX) = (
    SELECT 'ALTER EVENT SESSION ' + QUOTENAME(name) + ' ON SERVER STATE = STOP;
DROP EVENT SESSION ' + QUOTENAME(name) + ' ON SERVER;
'
    FROM sys.dm_xe_sessions
    WHERE name LIKE '%yourtracenamepattern%'
    ORDER BY name
    FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)');
PRINT @sql;
--EXEC (@sql);


Have you tried using an IP address?  I have seen an issue in R2 in the past whereby SQL Server was unable to resolve an FQDN but was fine with an IP address.


Check if database backup using pg_dump is in progress

The only thing I can think of is to check pg_stat_activity.application_name and see if any session with pg_dump (or pg_restore) is currently active.

application_name is a value supplied by the client connecting to the database. So in theory this is not something that can be trusted completely - but I am not aware that pg_dump allows you to overwrite this. And if you don't use that information for security purposes this should be OK.


How to disable Oracle OCI client transparent reconnect

It seems that Transparent  Application Failover (TAF) is enabled for your connection. If TAF is enabled the Oracle client will
reconnect you to the database or an alternate database if the connection between the client and the server is disrupted.
You use an OCI client and for this it is possible to register a
failover callback function
that is called after the client reconnects.
In this callback function you can issue the 'ALTER SESSION' command you need. But maybe you don't want to use TAF.
So first the DBA should check if your session uses TAF. An appropriate query can be found in the
Net Services Administrator's Guide
SELECT MACHINE, FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER, COUNT(*)
FROM V$SESSION
GROUP BY MACHINE, FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER;

With the following sample outpu
MACHINE              FAILOVER_TYPE FAILOVER_METHOD FAILED_OVER   COUNT(*)
-------------------- ------------- ----------      ---           ----------
sales1               NONE          NONE            NO            11
sales2               SELECT        PRECONNECT      NO             1

FAILOVER_TYPE NONE tells us, that no TAF is configured. FAILOVER_TYPE different to NONE tells us that TAF is configured for these sessions.
If your the session uses TAF then you should find out, where it is configured. It can be configured on client side or on server side.
You can check your 'tnsname.ora' file or use your 'tnsping' utlity to check your client configuration. If you find something similar to this  example from
Net Services Administrator's Guide
sales.us.example.com=
 (DESCRIPTION=
  (LOAD_BALANCE=on)
  (FAILOVER=on)
  (ADDRESS=
       (PROTOCOL=tcp)
       (HOST=sales1-server)
       (PORT=1521))
  (ADDRESS=
       (PROTOCOL=tcp)
       (HOST=sales2-server)
       (PORT=1521))
  (CONNECT_DATA=
     (SERVICE_NAME=sales.us.example.com)
     (FAILOVER_MODE=
       (TYPE=select)
       (METHOD=basic))))

then TAF is configured because of FAILOVER_MODE=(TYPE=select). If this is the case you should configure an appropriate connect string without TAF.
But even if no TAF is configure on client side it could be configured on server side for this service. So do the following query
select NETWORK_NAME, FAILOVER_METHOD, FAILOVER_TYPE
from DBA_SERVICES
where NETWORK_NAME is not null;

The following example output shows that TAF will be used for (SERVICE_NAME=sales.us.example.com) even if it is not configured on the client.
NETWORK_NAME             FAILOVER_METHOD      FAILOVER_TYPE
------------------------ -------------------- --------------------
sales.us.example.com     BASIC                SELECT
sales3.us.example.com    NONE                 NONE

In this case TAF will be used for (SERVICE_NAME=sales.us.example.com) even if TAF is not configured on the client. So you  have to use a service that does not have configured TAF, in out example 'sales3.us.example.com'
is such a service.
Service can be created with the DBMS_SERVICE package but in most cases they are created by some additional tools like Oracle Real Application Cluster.
One can display the services created by the RAC for a database 'salesdb' by executing the following command on the database server
srvctl config service -d salesdb

The following output (that depends on the database version)  shows that RAC will create a TAF service after start of the database
Service name: sales.us.example.com
Service is enabled
Server pool: SALESDB_sales.us.example.com
Cardinality: 2
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Failover type: SELECT
Failover method: BASIC
TAF failover retries: 5
TAF failover delay: 2
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: BASIC
Edition:
Preferred instances: sales2-server,sales2-server
Available instances:
Service is enabled on instances: SALESDB1,SALESDB2

you can create a new service 'sales3.us.example.com' without TAF with the following command
srvctl add service -d salsedb -s sales3.us.example.com


Normally I would do somehthing like this using T-SQL, even if Powershell was running that T-SQL, eg

SELECT *
INTO dbo.copyTable
FROM dbo.originalTable
WHERE 1 = 2
GO

CREATE CLUSTERED COLUMNSTORE INDEX _CS ON dbo.copyTable
GO

However I did get the following Powershell to work as a simple example:

$instance = ".\sql2014"
$server = New-Object "Microsoft.SqlServer.Management.Smo.Server" $instance
$database = $server.Databases[$dbName]

$tblName = "cs_test"
$originalTable = $database.Tables[$tblName]

$newTable = New-Object ("Microsoft.SqlServer.Management.Smo.Table") ($database, "copyTable", "dbo")

# Loop through columns adding each one to new table
foreach ($col in $originalTable.Columns)
{
    $newCol = New-Object ("Microsoft.SqlServer.Management.Smo.Column") ($newTable, $col.Name, $col.DataType)
    $newCol.Nullable = $col.Nullable
    $newTable.Columns.Add($newCol)
}

# Add columnstore index
$idx = New-Object ("Microsoft.SqlServer.Management.Smo.Index")($newTable, "idx") ##,IndexType.ClusteredColumnStoreIndex)
$idx.IndexType = "ClusteredColumnStoreIndex"
$newTable.Indexes.Add($idx)

# Create the table
$newTable.Create()


What is the query to get the process limit on Oracle 11g?

select max(maxval) as PROCESS_LIMIT
from dba_hist_sysmetric_summary
where METRIC_NAME in ('Process Limit %');


How Much Resources Does the BLACKHOLE Storage Engine Consume？

If a relay slave (a.k.a. Distribution Master) is using nothing but the BLACKHOLE storage engine for application tables, you really should not have to tune anything for MyISAM or InnoDB since there are no data or index pages to cache.

You will have a nominal amount of disk I/O (mostly reads) since the .frm file for every BLACKHOLE is still accessed for metadata purposes. This same behavior also happens to MEMORY tables. I wrote a post about this. I helped the person asking the question realize that dropping and creating MEMORY tables created disk I/O.

I presume you are not going to drop and add tables all that often since the relay slave needs to remain all BLACKHOLE. Nevertheless, CPU and Memory should not be a big factor on a relay slave. You should be concerned with the following on the Relay Slave:

Sufficient Disk Space for Binary and Relay Logs
Fast Enough Disk for Writing
Setting sync_binlog=1 in my.cnf for flushing binlogs thoroughly
Rotation of Binary Logs (setting a proper expire_logs_days)
Avoid replicating the following:

storage-engine specific ALTER TABLE statement that BLACKHOLE can't work with
ALTER TABLE statements not accepted by BLACKHOLE, InnoDB, and MyISAM
creating tables with ENGINE=InnoDB or ENGINE=MyISAM because you want all tables in the relay slave to remain BLACKHOLE.

I have addressed this scenario before

Aug 20, 2012 :  How to use MySQL Server 5.6 for GeoIP replication?
Jun 01, 2011 : What can we do in MySQL 5.0 Replication to address bandwidth concerns?

UPDATE 2014-08-11 11:47 EDT

Additional Clarification : A pure Relay Slave (Distribution Master) should never have MyISAM and InnoDB tables present. Suppose you run CREATE TABLE (...) ENGINE=InnoDB; on the Master. The Relay Slave will attempt to create the InnoDB table on the Relay Slave. If the Relay Slave has skip-innodb configured, it could possibly end up as a MyISAM table. You want to avoid that because it will increase disk I/O and InnoDB or MyISAM activity.

If the Relay Slave has both skip-innodb and default-storage-engine=BLACKHOLE configured, you would have to run a create table with identifying the storage engine (CREATE TABLE (...);).


MySQL slow server in a Billing application

Add an index to the users table. This should decrease the response time when the users table is queried. If you can share an example SQL statement used to query the users table, I can provide a more specific recommendation.

The columns in parenthesis should be the columns used in the WHERE clause of your SQL query.

CREATE INDEX index_name
ON table_name (column1, column2, ...);


The clause is NOLOGIN, not NONLOGIN. (I suggest copy/paste for quotes.)

The clause is for "group" roles, which used to actually be GROUP before Postgres 8.1 - which is still supported. See CREATE GROUP in the manual. Related:

Why did PostgreSQL merge users and groups into roles?

Group roles are very useful to bundle privileges and then grant / revoke the whole package to LOGIN roles. See:

Grant privileges for a particular database in PostgreSQL

Or to act as demons within the database:

Allow insertion only from within a trigger


Select records as groups based on sequence and date

Simple approach (can split sets)
Chain DELETE and INSERT with a data-modifying CTE for best performance and to be safe:
WITH del AS (
   DELETE FROM tbl t
   WHERE  udate < (now() - interval '1 year')::date
   AND    EXISTS (  -- is not the greatest
      SELECT 1 FROM tbl
      WHERE  compid = t.compid
      AND    seq    > t.seq
   RETURNING compid, seq, udate, utime
   )
INSERT INTO archive(compid, seq, udate, utime)
SELECT * FROM del;

This is assuming all columns to be defined NOT NULL (table definition is missing).
Depending on data distribution, there may be faster solutions.
I formulated the predicate Udate < (now() - interval '1 year')::date to be sargable. You should have an index on Udate (at least).
Ignoring Utime. You should replace date and time with a single timestamp column: udatetime timestamp. Smaller, more efficient.
Avoid splitting sets
To keep rows older than a year if at least 1 row in the same set (compid, seq) is still younger than a year:
WITH del AS (
   DELETE FROM tbl t
   USING (
      SELECT compid, seq
      FROM   tbl d
      GROUP  BY 1,2
      HAVING max(udate) < (now() - interval '1 year')::date
      AND    EXISTS (  -- is not greatest seq
         SELECT 1 FROM tbl
         WHERE  compid = d.compid
         AND    seq    > d.seq
         )
      ) d
   WHERE  t.compid = d.compid
   AND    t.seq    = d.seq
   RETURNING t.compID, t.seq, t.udate, t.utime
   )
INSERT INTO archive(compid, seq, udate, utime)
SELECT * FROM del;

This first groups by compid, seq and checks if the maximum date in the group is old enough to be moved.
SQL Fiddle.


Parameter Sniffing: Why Does This Become An Issue?

Basically what happens is, when SQL Server sees a query that it needs to compile, it is going to use the first-time-called parameters to generate the execution plan.  This may or may not be a good thing, but it is what happens.

For instance, say you have a table of fruit (100 rows).  There are 98 rows that are Apple, and only 2 rows that contain the fruit Orange.  If you query that table for Apple, then the plan will most likely compile with a Scan.  This is a good thing, as it is optimized for that Apple query.  But then when you want to query for the Orange, that Scan is inefficient.  But it is the stored plan that is being used.

The fact of the matter is that it happens.  It happens all the time.  It's typically not an outstanding issue, but in some cases it can be a pretty big problem.  A solution to an ongoing bad parameter sniffing problem is that you can utilize the OPTIMIZE FOR query hint to force SQL Server to use certain parameter values when generating the execution plan upon initial compilation.


Get a user's rank from an equation, and then store it in the table

OK - this should be easy for the first part, if you're willing to be a bit adventurous :-).

MariaDB has had computed columns (otherwise known as virtual or generated columns) for quite a while now. Otherwise, the latest milestone release of MySQL (5.7.7) also has them.

If you want to do this with a current GA version, you'll have to use TRIGGERs.

However, if you want to update the RANK (see a lovely post here) on each update of games_won/games_lost, then you'll have to call a PROCEDURE which will update RANK. MySQL's Stored Procedure programming language isn't sophisticated enough for this on its own, but see here for some ideas. It says that there can be rollback issues, but since you're updating only one table, it should be easier than most - good luck :-). BTW, I'd use float/double for games_won/_lost - otherwise, you might have problems with integer division.


pre login initialization =29237; handshake=26244

By default, SQL uses port 1433 and 1434 (if you are using named instance or the DAC connection).

You can validate which port is used using SQL Server Configuration Manager


Where can I find the SQL standard document?

The ISO SQL 2008 Standard document ISO/IEC 9075-1:2008 Information technology -- Database languages -- SQL -- Part 1: Framework (SQL/Framework) is now freely available from the ISO website.


Postgres error [column must appear in the GROUP BY clause or be used in an aggregate function] when sub query is used

The feature of Postgres to be able to use the primary key of a table with GROUP BY and not need to add the other columns of that table in the GROUP BY clause is relatively new and works only for base tables. The optimizer is not (yet?) clever enough to identify primary keys for views, ctes or derived tables (as in your case).
You can add the columns you want in the SELECT into the GROUP BY clause:
SELECT e.empname, array_agg(p.phonenumber) AS phonenumbers
FROM
(SELECT * FROM employee ORDER BY empname LIMIT 3 OFFSET 0) AS e
LEFT OUTER JOIN phones AS p ON e.empid = p.empid
GROUP BY e.empid, e.empname
ORDER BY e.empname ;

or use a subquery (and transfer the GROUP BY there):
SELECT e.empname,
       (SELECT array_agg(p.phonenumber)
        FROM phones AS p
        WHERE e.empid = p.empid
       ) AS phonenumbers
FROM
(SELECT * FROM employee ORDER BY empname LIMIT 3 OFFSET 0) AS e
ORDER BY e.empname ;

which could also be written as:
SELECT e.empname,
       (SELECT array_agg(p.phonenumber)
        FROM phones AS p
        WHERE e.empid = p.empid
       ) AS phonenumbers
FROM employee AS e
ORDER BY e.empname LIMIT 3 OFFSET 0 ;

Since you are in version 9.3+. you can also use a LATERAL join:
SELECT e.empname,
       p.phonenumbers
FROM
   (SELECT * FROM employee ORDER BY empname LIMIT 3 OFFSET 0) AS e
LEFT JOIN LATERAL
   (SELECT array_agg(phonenumber) AS phonenumbers
    FROM phones
    WHERE e.empid = phones.empid
   ) AS p ON TRUE
ORDER BY e.empname ;


How to export an image column to files in SQL Server?

This is the solution I came up with:

Enable xp_cmdshell with

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

If needed create a directory with xp_cmdshell to get the needed permissions.

EXEC master..xp_cmdshell 'mkdir C:\exportdir'

Use BCP with queryout

EXEC master..xp_cmdshell 'BCP "SELECT column_of_type_image FROM **your_db WHERE id = 1 " queryout "C:\exportdir\yourfile.pdf" -T -N'

**your_db must be the fully qualified table name, i.e [Yourdb].[YourSchema].[YourTable]