SSISDB Maintenance job cleaning data very slow on SQL Server 2014

In order to do it quickly, you will need to delete the SSISDB and recreate it.  But I don't recommend doing that.  The cleanup job is interfering with your execution of SSIS Packages (or vice-versa).

My approach when I'm trimming back the retention period is to walk backwards a day at a time.  The script below will do that, just change the @DesiredRetention to what you want and it will walk backwards to that point.  You can stop and start this script multiple times (to allow for it to run when packages are not) and it will pick up where it left off.

DECLARE @DesiredRetention INT = 30;
DECLARE @CurrentRetention INT = (SELECT property_value FROM SSISD.internal.catalog_properties WHERE property_name = 'RETENTION_WINDOW');

WHILE @CurrentRetention > @DesiredRetention
BEGIN

    PRINT 'Removing History older than: ' + CAST(@CurrentRetention AS VARCHAR(10));
    PRINT GETDATE();

    --Change the desired retention period...
    EXEC [SSISDB].[catalog].[configure_catalog] @property_name=N'RETENTION_WINDOW', @property_value=@CurrentRetention;

    --Run the Cleanup Job
    EXEC [SSISDB].[internal].[cleanup_server_retention_window];

    --Decrement the value.
    SET @CurrentRetention = @CurrentRetention - 1;

END

--Make sure to set the desired retention period...
EXEC [SSISDB].[catalog].[configure_catalog] @property_name=N'RETENTION_WINDOW', @property_value=@DesiredRetention;


It's "Most Likely" a Permissions Issue
If you're performing a network SQL Server backup to a file share you'll most likely need to do one or more of these three common (A,B,C) tasks below:
A.> SQL Server Service
If you're running your backup from a SSMS query window, you need to make sure that the SQL Server service is using an Active Directory account that has permissions to write to the share location you're writing to.
B.> SQL Server Agent Service
If you're running the backup from the SQL Agent with a schedule job, you need to make sure that the SQL Agent is using an AD account that has permissions to write to the target share.
C.> Trusted Connection with sqlcmd
If you're running sqlcmd you need to use the -E and make sure the account that's running sqlcmd (the account running the cmd window) has permissions to write to the target share.
SQL Server Configuration Manager
If you need to change your SQL Server services to use an AD account, you'll need to apply the AD account using SQL Server Configuration Manager (SSCM). SSCM configures other things on the Windows Server besides just adding the AD account. After you change the associated service account, you'll need to restart that service for the account to take affect.
Workgroups instead of AD
If you're using a Windows Workgroup environment instead of AD, you'll want to make sure that both the Windows SQL Server and the target Windows box, have separate but identically named accounts on them with the same passwords. Run the SQL service you're using for the backup with this account.
Target Server Permissions
If you're unsure that the SQL Service/Agent accounts has the right permissions, just recreate the share on the target Windows machine and give that account at least read/write access to the share location using the folder's properties --> Share tab --> Share ... button.
Note
All that being said, if you're configuring backups for an AlwaysOn configuration there are  separate gotchas that apply depending on which SQL Server is primary and which one is secondary and how you have backups configured. But, based on the video you presented, I don't think you're having issues with AlwaysOn backups.


MariaDB Galera Cluster - Cert Deps Distance issue

wsrep-cert-deps-distance

Most likely the 0 on one node was the only node being written to. So there are limited galera status changes there as it does not apply in the same was as the other nodes do.


SQLServer In-Memory database and log file size unexpected increase

OK, I didn't find a clear explanation, but found how to fix that.

It was because file used to store in-memory objects was full and could not allocate more space. So no more checkpoints, and logs and growing more and more. This is happening even though there is no in-memory objects in the database (and therefore no reason for the data file to grow).

Allowing file to grow more is not working, you need to add another file to your filegroup.

So, knowing that, it's easy to fix.

add another file into the filegroup for in-memory
checkpoint (or, if you can afford it, restart SQLServer service)
shrink log file to a more reasonable size

Conclusion of the story : carefully monitoring data size is even more important with in-memory.


PostgreSQL optimizes the query so that the left join is completely ignored, so that doesn't factor in the question at all.

You didn't provide EXPLAIN (ANALYZE, BUFFERS) results, but either you don't have an index on test1.rank, or PostgreSQL considers the condition not selective enough.

One thing you can try is to create an index on test1.rank and VACUUM test1, then perhaps you can get a faster index-only scan.


How to set lower case for identifiers in Toad?

I found the following on Quest.com.  It works, but may have undesirable side effects.

  Go to View | Toad Options in the main menu.
  On the left-hand side of the options window go to Editor | Behavior.
  Under the Language section, click on the Syntax highlighting button.
  In the new window, under styles on the left hand side, select "Indentifer" and select Lowercase for Capitalization effect on the
  right hand side.
  Under styles on the left hand side, select "Toad_user_table" and select Lowercase for Capitalization effect on the right hand side..
  Click on Apply and OK.


Change in bind_ip value is not reflecting

tcp  0  0   localhost:27017  0.0.0.0:*    LISTEN

As per @Jay Gordon blog documented here This output means that users are only permitted to access our MongoDB instance on port 27017 via IP 127.0.0.1.

Starting in MongoDB 3.6, mongos bind to localhost by default. See  Default Bind to Localhost.

The hostnames and/or IP addresses and/or full Unix domain socket paths on which mongos should listen for client connections. You may attach mongos to any interface. To bind to multiple addresses, enter a list of comma-separated values.

EXAMPLE

localhost,/tmp/mongod.sock

  Note: When possible, use a logical DNS hostname instead of an ip address, particularly when configuring replica set members or sharded
  cluster members. The use of logical DNS hostnames avoids configuration
  changes due to ip address changes.

To bind to all IPv4 addresses, enter 0.0.0.0.

To bind to all IPv4 and IPv6 addresses, enter ::,0.0.0.0 or alternatively, use the net.bindIpAll setting.

Enable Access Control and Enforce Authentication

Enable access control and specify the authentication mechanism. You can use the default MongoDB authentication mechanism or an existing external framework. Authentication requires that all clients and servers provide valid credentials before they can connect to the system. In clustered deployments, enable authentication for each MongoDB server.

See Authentication and  Enable Auth.

WARNING:
Before you bind to other ip addresses, consider enabling access control and other security measures listed in Security Checklist to prevent unauthorized access.

If you would like to make this available to other systems on your network, you'll want to bind the local IP associated with the private network. To determine network interface configuration easily, we can just run an ifconfig from the command line:

For Example

To determine network interface configuration easily, we can just run an ifconfig from the command line:

ubuntu@ip-172-16-0-211:~$ ifconfig
eth0      Link encap:Ethernet  HWaddr 0e:5e:76:83:49:3e
          inet addr:172.16.0.211  Bcast:172.16.0.255  Mask:255.255.255.0
          inet6 addr: fe80::c5e:76ff:fe83:493e/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:9001  Metric:1
          RX packets:65521 errors:0 dropped:0 overruns:0 frame:0
          TX packets:7358 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000
          RX bytes:94354063 (94.3 MB)  TX bytes:611646 (611.6 KB)

We have the IP we want to make part of the list of acceptable network addresses we can listen from. I will open the /etc/mongodb.conf file and edit it to reflect the additional network IP:

The file's changes will be:

# network interfaces
net:
  port: 27017
  bindIp: 127.0.0.1,172.16.0.211

After modifying bindIP under "net" from just 127.0.0.1 to include private IP address 172.16.0.211, we should be able to restart and see it listening from netstat on both now:

ubuntu@ip-172-16-0-211:~$ sudo service mongod stop
ubuntu@ip-172-16-0-211:~$ sudo service mongod start
ubuntu@ip-172-16-0-211:~$ sudo netstat -plnt | egrep mongod
tcp        0      0 172.16.0.211:27017      0.0.0.0:*               LISTEN      2892/mongod
tcp        0      0 127.0.0.1:27017         0.0.0.0:*               LISTEN      2892/mongod

For further your ref here  and here


Find below the wordings from MongoDB official documentation

Insert Only

mongorestore can create a new database or add data to an existing database. However, mongorestore performs inserts only and does not perform updates. That is, if restoring documents to an existing database and collection and existing documents have the same value _id field as the to-be-restored documents, mongorestore will not overwrite those documents.

  To answer your question, no the documents won't be overwritten.


Please look carfully at session 1.

Lock table v3_zone_date WRITE;
SET AUTOCOMMIT=0;
LOAD DATA INFILE '/data10/select_into.outfile/v3_zone_date.out' INTO TABLE v3_zone_date
FIELDS TERMINATED BY  ',';
COMMIT;
SET AUTOCOMMIT=1;

should be

Lock table v3_zone_date WRITE;
LOAD DATA INFILE '/data10/select_into.outfile/v3_zone_date.out' INTO TABLE v3_zone_date
FIELDS TERMINATED BY  ',';
UNLOCK TABLES;

No need to have these lines

SET AUTOCOMMIT=0;
COMMIT;
SET AUTOCOMMIT=1;

because MySQL 5.0 Certification Study Guide, Chapter 29, Page 418 Bulletpoint #1 says

  UNLOCK TABLES implicitly commits only if you have explicitly locked
  tables with LOCK TABLES. SET AUTOCOMMIT = 1 implicitly commits only
  if autocommit mode wasn't already enabled.

LOCK TABLES will hold up any transaction-based row locks that are trying to be acquired.

Since the tables are locked independently, you must just need to increase the size of your InnoDB Buffer Pool (innodb_buffer_pool_size) to make more room for row locks across all InnoDB tables.


Unable to install pl/python for Postgresql12

Using set at the command prompt does not give feedback upon success.  To verify it was actually set, you would run echo %PYTHON_HOME%.  But it looks like you have a space after c:\ that should not be there.
Also, this only sets the variables for the particular cmd session you run it in, so it works only if you are starting PostgreSQL from that command prompt.  That isn't how you usually run PostgreSQL on Windows, you normally run it as a service.  So you have would have to use the GUI interface to "edit the system environment variables", not use set from the command line.


Create a function like the one below;
    CREATE OR REPLACE FUNCTION public.explain_this (
      l_query text,
      out explain text
    )
    RETURNS SETOF text AS
    $body$
    BEGIN
      RETURN QUERY EXECUTE 'explain (ANALYZE FALSE) ' || l_query;
    END;
    $body$
    LANGUAGE 'plpgsql'
    VOLATILE
    RETURNS NULL ON NULL INPUT
    SECURITY DEFINER
    COST 100 ROWS 1000;

And then arrange privileges;
  ALTER FUNCTION public.explain_this(l_query text, out explain text)
    OWNER TO some_user_with_select_only_privileges;

  GRANT EXECUTE
    ON FUNCTION public.explain_this(l_query text, out explain text) TO user_to_execute;


Suggested max len

First, I will mention some common strings that are always hex, or otherwise limited to ASCII.  For these, you should specify CHARACTER SET ascii (latin1 is ok) so that it will not waste space:

UUID CHAR(36) CHARACTER SET ascii -- or pack into BINARY(16)
country_code CHAR(2) CHARACTER SET ascii
ip_address CHAR(39) CHARACTER SET ascii -- or pack into BINARY(16)
phone VARCHAR(20) CHARACTER SET ascii -- probably enough to handle extension
postal_code VARCHAR(20) CHARACTER SET ascii -- (not 'zip_code') (don't know the max

city VARCHAR(100) -- This Russian town needs 91:
    Poselok Uchebnogo Khozyaystva Srednego Professionalno-Tekhnicheskoye Uchilishche Nomer Odin
country VARCHAR(50) -- probably enough
name VARCHAR(64) -- probably adequate; more than some government agencies allow

Why not simply 255?
There are two reasons to avoid the common practice of using (255) for everything.

When a complex SELECT needs to create temporary table (for a subquery, UNION, GROUP BY, etc), the preferred choice is to use the MEMORY engine, which puts the data in RAM.  But VARCHARs are turned into CHAR in the process.  This makes VARCHAR(255) CHARACTER SET utf8mb4 take 1020 bytes.  That can lead to needing to spill to disk, which is slower.
In certain situations, InnoDB will look at the potential size of the columns in a table and decide that it will be too big, aborting a CREATE TABLE.


How to cast in oracle -Spatial Database?

Use an up-to-date client and tool. I remember some old clients and tools that do not support all datatypes. For example, if I remember correct, Toad 9.0 for Oracle does not support the TIMESTAMP (or maybe it was caused by and old Oracle client, I do not use Toad, just seen this once at a client on-site). Below works just fine from 12.1.0.2 SQL*Plus or SQL Developer (4.1.5.21).

SQL> desc oe.customers
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 CUSTOMER_ID                               NOT NULL NUMBER(6)
 CUST_FIRST_NAME                           NOT NULL VARCHAR2(20)
 CUST_LAST_NAME                            NOT NULL VARCHAR2(20)
 CUST_ADDRESS                                       OE.CUST_ADDRESS_TYP
 PHONE_NUMBERS                                      OE.PHONE_LIST_TYP
 NLS_LANGUAGE                                       VARCHAR2(3)
 NLS_TERRITORY                                      VARCHAR2(30)
 CREDIT_LIMIT                                       NUMBER(9,2)
 CUST_EMAIL                                         VARCHAR2(40)
 ACCOUNT_MGR_ID                                     NUMBER(6)
 CUST_GEO_LOCATION                                  MDSYS.SDO_GEOMETRY
 DATE_OF_BIRTH                                      DATE
 MARITAL_STATUS                                     VARCHAR2(20)
 GENDER                                             VARCHAR2(1)
 INCOME_LEVEL                                       VARCHAR2(20)

SQL> select CUST_GEO_LOCATION from oe.customers where rownum = 1;

CUST_GEO_LOCATION(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_OR
--------------------------------------------------------------------------------
SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(-91.66643, 41.977151, NULL), NULL, NULL)


In my professional experience, such bizarre queries being required are often indicative of an unnormalized schema (assuming this isn't a random one-off query you need to do). Perhaps you would be better served by giving us your actual table/column names and then letting us offer suggestions on how you can restructure your data.

BUT, just to prove to you that I've been around the block a few times, here is the actual answer to your question:

SET @a = 1, @max_count = 2, @counter = 0;
SELECT a,b,c, count FROM (
   SELECT
          IF(@counter <= @max_count,
             @counter := @counter + 1,
             @counter := 1) AS count
        , a
        , b
        , c

     FROM ( SELECT a,b,c
            FROM t1
            WHERE a = @a
            ORDER BY a,b,c
          )
       AS t1
)
AS t
HAVING count <= @max_count;

+------+------+------+-------+
| a    | b    | c    | count |
+------+------+------+-------+
|    1 |    1 |    2 |     1 |
|    1 |    1 |    3 |     2 |
|    1 |    2 |    3 |     1 |
|    1 |    2 |    4 |     2 |
|    1 |    3 |    5 |     1 |
+------+------+------+-------+
5 rows in set (0.00 sec)

You must modify the value of @a in the SET statement to the "row group" which you want. You can then ignore the count column that appears in the resultset. Note that the SET statement MUST be run each and EVERY time you want to run the actual query.


SSAS Tabular model table load from query with #temp table fails

The SQL query can be converted to a stored procedure and then executed as the source SQL statement for the SSAS dimension/fact tables.  As with a typical stored procedure, you can then use temp tables without any problems.  A stored procedure can be called from SSAS by using an EXEC statement with the result set then used in the same manner as one from a view or table would be.

EXEC Schema.SP_Name


Selecting random rows from database initially while preserving that order later

You'll want to hold an actual random number for each user then you can call

rand($usersrandomnumber)

to generate your static random generator start point

the $usersrandomnumber acts as a starting seed

see the mysql documentation here:
https://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_rand

it will involve you storing the user's seed in a table somewhere, or cookie /local/server storage but will get you what you're after


Query without having to specify table's schema

If you want to go back to using the dbo schema like you were in SQL Server 2000, you can move the table back into the dbo schema:

ALTER SCHEMA dbo TRANSFER erpadmin.tablename;

An alternative if you like having the non-dbo schema is to set your user's default schema to erpadmin then if you do not specify a schema, it will use that as default. (Members of the sysadmin fixed server role ignore the DEFAULT_SCHEMA and use dbo by default.)

ALTER USER erpadmin WITH DEFAULT_SCHEMA = erpadmin;

The two part name you have (schema.table) is a good habit to get into though, so you can be explicit with which table you are referring to.  Some features require you to use a two-part name, Indexed Views are one example.


First of all, innodb_file_per_table is enabled by default for MySQL 8.
If you want to know what tables are sitting in ibdata1, run the following query
select * from information_schema.innodb_tables;

This will inform you about each table and what tablespace it resides in
EXAMPLE
I spun of a vagrant lab that runs MySQL 8.0.17
I loaded the KJV of the Bible and added a fulltext index
Here is the output of the query I mentioned
mysql> select * from information_schema.innodb_tables;
+----------+-----------------------------------------------------+------+--------+------------+------------+---------------+------------+--------------+
| TABLE_ID | NAME                                                | FLAG | N_COLS | SPACE      | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS |
+----------+-----------------------------------------------------+------+--------+------------+------------+---------------+------------+--------------+
|     1025 | mysql/db                                            |  161 |     25 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1026 | mysql/user                                          |  161 |     54 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1027 | mysql/default_roles                                 |  161 |      7 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1028 | mysql/role_edges                                    |  161 |      8 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1029 | mysql/global_grants                                 |  161 |      7 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1030 | mysql/password_history                              |  161 |      7 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1031 | mysql/func                                          |  161 |      7 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1032 | mysql/plugin                                        |  161 |      5 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1054 | mysql/help_topic                                    |  161 |      9 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1055 | mysql/help_category                                 |  161 |      7 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1057 | mysql/help_relation                                 |  161 |      5 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1036 | mysql/servers                                       |  161 |     12 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1037 | mysql/tables_priv                                   |  161 |     11 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1038 | mysql/columns_priv                                  |  161 |     10 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1056 | mysql/help_keyword                                  |  161 |      5 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1040 | mysql/time_zone_name                                |  161 |      5 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1041 | mysql/time_zone                                     |  161 |      5 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1042 | mysql/time_zone_transition                          |  161 |      6 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1043 | mysql/time_zone_transition_type                     |  161 |      8 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1044 | mysql/time_zone_leap_second                         |  161 |      5 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1045 | mysql/procs_priv                                    |  161 |     11 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1046 | mysql/component                                     |  161 |      6 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1047 | mysql/slave_relay_log_info                          |  161 |     12 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1048 | mysql/slave_master_info                             |  161 |     31 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1049 | mysql/slave_worker_info                             |  161 |     16 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1050 | mysql/gtid_executed                                 |  161 |      6 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1051 | mysql/server_cost                                   |  161 |      7 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1052 | mysql/engine_cost                                   |  161 |      9 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1053 | mysql/proxies_priv                                  |  161 |     10 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1058 | sys/sys_config                                      |   33 |      7 |          1 | Dynamic    |             0 | Single     |            0 |
|     1101 | bible/verses                                        |   33 |      8 |         44 | Dynamic    |             0 | Single     |            0 |
|     1102 | bible/fts_000000000000044d_being_deleted            |   33 |      4 |         45 | Dynamic    |             0 | Single     |            0 |
|     1103 | bible/fts_000000000000044d_being_deleted_cache      |   33 |      4 |         46 | Dynamic    |             0 | Single     |            0 |
|     1104 | bible/fts_000000000000044d_config                   |   33 |      5 |         47 | Dynamic    |             0 | Single     |            0 |
|     1105 | bible/fts_000000000000044d_deleted                  |   33 |      4 |         48 | Dynamic    |             0 | Single     |            0 |
|     1106 | bible/fts_000000000000044d_deleted_cache            |   33 |      4 |         49 | Dynamic    |             0 | Single     |            0 |
|     1107 | bible/fts_000000000000044d_00000000000000cd_index_1 |   33 |      8 |         50 | Dynamic    |             0 | Single     |            0 |
|     1108 | bible/fts_000000000000044d_00000000000000cd_index_2 |   33 |      8 |         51 | Dynamic    |             0 | Single     |            0 |
|     1109 | bible/fts_000000000000044d_00000000000000cd_index_3 |   33 |      8 |         52 | Dynamic    |             0 | Single     |            0 |
|     1110 | bible/fts_000000000000044d_00000000000000cd_index_4 |   33 |      8 |         53 | Dynamic    |             0 | Single     |            0 |
|     1111 | bible/fts_000000000000044d_00000000000000cd_index_5 |   33 |      8 |         54 | Dynamic    |             0 | Single     |            0 |
|     1112 | bible/fts_000000000000044d_00000000000000cd_index_6 |   33 |      8 |         55 | Dynamic    |             0 | Single     |            0 |
|     1113 | bible/books                                         |   33 |      5 |         56 | Dynamic    |             0 | Single     |            0 |
|     1150 | ft_db/ft_test_innodb                                |   33 |      6 |         93 | Dynamic    |             0 | Single     |            0 |
|     1151 | ft_db/fts_000000000000047e_being_deleted            |   33 |      4 |         94 | Dynamic    |             0 | Single     |            0 |
|     1152 | ft_db/fts_000000000000047e_being_deleted_cache      |   33 |      4 |         95 | Dynamic    |             0 | Single     |            0 |
|     1153 | ft_db/fts_000000000000047e_config                   |   33 |      5 |         96 | Dynamic    |             0 | Single     |            0 |
|     1154 | ft_db/fts_000000000000047e_deleted                  |   33 |      4 |         97 | Dynamic    |             0 | Single     |            0 |
|     1155 | ft_db/fts_000000000000047e_deleted_cache            |   33 |      4 |         98 | Dynamic    |             0 | Single     |            0 |
|     1156 | ft_db/fts_000000000000047e_0000000000000107_index_1 |   33 |      8 |         99 | Dynamic    |             0 | Single     |            0 |
|     1157 | ft_db/fts_000000000000047e_0000000000000107_index_2 |   33 |      8 |        100 | Dynamic    |             0 | Single     |            0 |
|     1158 | ft_db/fts_000000000000047e_0000000000000107_index_3 |   33 |      8 |        101 | Dynamic    |             0 | Single     |            0 |
|     1159 | ft_db/fts_000000000000047e_0000000000000107_index_4 |   33 |      8 |        102 | Dynamic    |             0 | Single     |            0 |
|     1160 | ft_db/fts_000000000000047e_0000000000000107_index_5 |   33 |      8 |        103 | Dynamic    |             0 | Single     |            0 |
|     1161 | ft_db/fts_000000000000047e_0000000000000107_index_6 |   33 |      8 |        104 | Dynamic    |             0 | Single     |            0 |
+----------+-----------------------------------------------------+------+--------+------------+------------+---------------+------------+--------------+
55 rows in set (0.00 sec)

mysql> desc information_schema.innodb_tables;
+---------------+---------------------+------+-----+---------+-------+
| Field         | Type                | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------+-------+
| TABLE_ID      | bigint(21) unsigned | NO   |     |         |       |
| NAME          | varchar(655)        | NO   |     |         |       |
| FLAG          | int(11)             | NO   |     |         |       |
| N_COLS        | int(11)             | NO   |     |         |       |
| SPACE         | bigint(21)          | NO   |     |         |       |
| ROW_FORMAT    | varchar(12)         | YES  |     |         |       |
| ZIP_PAGE_SIZE | int(11) unsigned    | NO   |     |         |       |
| SPACE_TYPE    | varchar(10)         | YES  |     |         |       |
| INSTANT_COLS  | int(11)             | NO   |     |         |       |
+---------------+---------------------+------+-----+---------+-------+
9 rows in set (0.00 sec)

mysql>

Please note that any table whose space value is 0, resides in the ibdata1. It says to in the MySQL 8.0 Docs about INFORMATION_SCHEMA.INNODB_TABLES;
At the moment, there are no tables in ibdata1.
Let's see If I can inject one into ibdata1.
mysql> use bible
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>
mysql>
mysql> set global innodb_file_per_table = 'OFF';
Query OK, 0 rows affected (0.00 sec)

mysql> create table injection (id int not null auto_increment, name varchar(20), primary key (id));
Query OK, 0 rows affected (0.04 sec)

mysql> insert into injection (name) values ('Hello, World !!!');
Query OK, 1 row affected (0.01 sec)

mysql> select * from injection;
+----+------------------+
| id | name             |
+----+------------------+
|  1 | Hello, World !!! |
+----+------------------+
1 row in set (0.00 sec)

mysql>

OK, now let's check information_schema
mysql> select * from information_schema.innodb_tables where space = 0;
+----------+-----------------+------+--------+-------+------------+---------------+------------+--------------+
| TABLE_ID | NAME            | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS |
+----------+-----------------+------+--------+-------+------------+---------------+------------+--------------+
|     1162 | bible/injection |   33 |      5 |     0 | Dynamic    |             0 | System     |            0 |
+----------+-----------------+------+--------+-------+------------+---------------+------------+--------------+
1 row in set (0.00 sec)

mysql>

TADA !!!
OK, so now go run
select * from information_schema.innodb_tables where space = 0;

If you don't see any rows returned, then you have nothing in ibdata1.
WHAT IF I STILL HAVE TABLES IN ibdata1?
If you are starting brand new in MySQL. You should not have this problem from the beginning. Notwithstanding, let's say you have this situation in MySQL 8.0. How to you do yank the table out of ibdata1 ???
Let's try ALTER TABLE ... ENGINE=InnoDB
Unfortunately, you cannot perform a NULL ALTER like before:
mysql> select * from information_schema.innodb_tables where space = 0;
+----------+-----------------+------+--------+-------+------------+---------------+------------+--------------+
| TABLE_ID | NAME            | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS |
+----------+-----------------+------+--------+-------+------------+---------------+------------+--------------+
|     1162 | bible/injection |   33 |      5 |     0 | Dynamic    |             0 | System     |            0 |
+----------+-----------------+------+--------+-------+------------+---------------+------------+--------------+
1 row in set (0.00 sec)

mysql> set global innodb_file_per_table = 'ON';
Query OK, 0 rows affected (0.00 sec)

mysql> alter table bible.injection engine=InnoDB;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from information_schema.innodb_tables where space = 0;
+----------+-----------------+------+--------+-------+------------+---------------+------------+--------------+
| TABLE_ID | NAME            | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS |
+----------+-----------------+------+--------+-------+------------+---------------+------------+--------------+
|     1163 | bible/injection |  161 |      5 |     0 | Dynamic    |             0 | System     |            0 |
+----------+-----------------+------+--------+-------+------------+---------------+------------+--------------+
1 row in set (0.00 sec)

mysql>

You could get away with this in MySQL 5.7 and back. For MySQL 8.0, the temp table used maintains the table's current tablespace metadata.
Let's try CREATE TABLE LIKE
mysql> create table bible.injection2 like bible.injection;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from information_schema.innodb_tables where space = 0;
+----------+------------------+------+--------+-------+------------+---------------+------------+--------------+
| TABLE_ID | NAME             | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS |
+----------+------------------+------+--------+-------+------------+---------------+------------+--------------+
|     1163 | bible/injection  |  161 |      5 |     0 | Dynamic    |             0 | System     |            0 |
|     1164 | bible/injection2 |  161 |      5 |     0 | Dynamic    |             0 | System     |            0 |
+----------+------------------+------+--------+-------+------------+---------------+------------+--------------+
2 rows in set (0.00 sec)

mysql> select @@global.innodb_file_per_table;
+--------------------------------+
| @@global.innodb_file_per_table |
+--------------------------------+
|                              1 |
+--------------------------------+
1 row in set (0.00 sec)

mysql>

OK Ouch !!!. That pesky tablespace metadata gets dragged around like luggage.
Let's try ALTER TABLE ... TABLESPACE=innodb_file_per_table
Here we go ...
mysql> create table bible.injection2 like bible.injection;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from information_schema.innodb_tables where space = 0;
+----------+------------------+------+--------+-------+------------+---------------+------------+--------------+
| TABLE_ID | NAME             | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS |
+----------+------------------+------+--------+-------+------------+---------------+------------+--------------+
|     1163 | bible/injection  |  161 |      5 |     0 | Dynamic    |             0 | System     |            0 |
|     1164 | bible/injection2 |  161 |      5 |     0 | Dynamic    |             0 | System     |            0 |
+----------+------------------+------+--------+-------+------------+---------------+------------+--------------+
2 rows in set (0.00 sec)

mysql> select @@global.innodb_file_per_table;
+--------------------------------+
| @@global.innodb_file_per_table |
+--------------------------------+
|                              1 |
+--------------------------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE bible.injection2 TABLESPACE=innodb_file_per_table;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from information_schema.innodb_tables where space = 0;
+----------+-----------------+------+--------+-------+------------+---------------+------------+--------------+
| TABLE_ID | NAME            | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS |
+----------+-----------------+------+--------+-------+------------+---------------+------------+--------------+
|     1163 | bible/injection |  161 |      5 |     0 | Dynamic    |             0 | System     |            0 |
+----------+-----------------+------+--------+-------+------------+---------------+------------+--------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE bible.injection TABLESPACE=innodb_file_per_table;
Query OK, 0 rows affected (0.18 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from information_schema.innodb_tables where space = 0;
Empty set (0.00 sec)

mysql>

TADA !!!
CONCLUSION
If you suspect any tables are in ibdata1, find them with
select * from information_schema.innodb_tables where space = 0;

Then just run
ALTER TABLE mydb.mytable TABLESPACE=innodb_file_per_table;

Please remember: The default innodb_file_per_table is ON.
No need to mysqldump and reload.
If you are attempting to shrink ibdata1, you will have load data into a brand new installation. You cannot just delete ibdata1 like you could in MySQL 5.5 and back. MySQL 5.6 + has innodb tables in the mysql schema database. So, you cannot play the same file manipulation games. MySQL 8.0 now demands a lot more respect from a DBA.


SQL Cluster IP should this be the same as the WFC cluster IP?

No you will need separate IP for Windows Server Failover Cluster (WSFC) and Failover Cluster Instance (FCI, what you mentioned as SQL Cluster).


Reducing the size of a 500 Gb database on standard edition

Here's my answers in the order that I'd do them:

1. Remove tables you no longer need. Go into SSMS, Object Explorer, click on the tables folder for your database, and then open Object Explorer Details by clicking View, Object Explorer Details. From there, you can right-click on columns to show data size and index size, and you can sort by either one. I find that often, folks left backup tables they didn't know about.

2. Remove unused indexes. Use sp_BlitzIndex in the First Responder Kit (disclaimer: I'm one of the authors) to remove nonclustered indexes that aren't being used. Bonus points for removing duplicate and near-duplicate indexes.

3. Check the server's bottlenecks. From the First Responder Kit again, run sp_BlitzFirst @SinceStartup = 1 to get your wait stats since startup. If the top wait is PAGEIOLATCH, that means reading data pages from the data file. In that case, try maxing out your memory. 2008R2 Standard Edition takes 64GB just for the buffer pool, and can use more memory than that for other purposes. Make sure your server has at least 96GB RAM. (I'm not saying it's always a good idea to throw hardware at the problem, but my desktop has 64GB of RAM, and the rest of these changes are going to be more and more disruptive.)

If your top wait stat is anything other than PAGEIOLATCH, then focus on the top wait type instead, because reducing the database size may not help performance.

4. Implement desperately needed indexes. When your database won't fit in RAM, the index designs become much more important. You'll see the list of desperately needed missing indexes from sp_BlitzIndex in the prior step, but also consider running sp_BlitzCache @SortOrder = 'reads' (again, from our First Responder Kit) to identify the queries doing the most logical reads. Often, their plans will have missing indexes right there, and again, you can implement those to reduce the amount of data you need to read from disk.

5. Double-check storage speeds. In an earlier step, I recommended running sp_BlitzFirst @SinceStartup = 1. Now that you've pruned indexes, tuned queries, and right-sized RAM, it's time to circle back to that same command again - only this time, instead of looking at your wait stats, look at the PHYSICAL READS section. That identifies which data files you've been reading from, and what their average latency is. If your average read latency is over, say, 100ms, it's time to start asking questions about storage performance.


MySQL - Alter Table Fails As Workbench Tries To Create Existing Table

If you have a lot of records in your table, take a look at my answer to a related question here.

With respect to MySQL Workbench, I have always found the tool to be excellent.

Having said that, what MySQL Workbench 'may' be doing in the background is creating an empty temporary table with the extra field, copying over all the records, the deleting your old Patient table and renaming the temp table to Patient and then having problems creating the "new" Patient table. You'd have to check the source code to find out for sure what's going on - or maybe run SHOW TABLES; continuously against your system while the update was being performed.

Where the message "The table "Analysis' is full" is coming from, I don't have a clue - does it exist in your schema? Do you have spare disk space for your MySQL datadir, or is it getting tight?

In any case, if a GUI tool fails, the first thing to do is to is to run the command from the mysql command line tool (another reason to prefer the command line), just to ensure that you're really dealing with a MySQL problem and not an issue with Workbench. Do you have the output of the error log? That might also be helpful.


structuring media uploads (users, images, videos) using sequelize.js

I am leaning towards Option 1 for this because I actually think that as you add more media types option 2 is going to get unmanageable when it comes to querying, especially when you are trying to get all of a user's media, it would just require too much eager loading of the other tables, or a lot of join tables.

With option one, each piece of media has it's own unique id, so if you set up the association between Users, Media, and Likes so that:
Likes belongTo users
Users can hasMany Likes
Media can hasMany Likes
Likes hasOne media (so each individual like is a representation of it's userId and the mediaId it was associated to)

then you would be able to query your Likes table for all Likes with a certain userId (to show all the media a user has liked), or for all Likes for a certain mediaId (ex. to show the amount of likes for a specific image).

You wouldn't even necessarily have to specify the media type when querying likes because you'd have your mediaId or UserId depending on what you are trying to query.


Model Entity Representation of an Organigram

I think you need to do as follows. Create a Person entity with data fields such as id, name, address and create another entity Person-Relationship with data fields such as pr-id, rel-type with instance of grandfather, father, etc...The physical data model might look as follows,

pr_type
{
     pr_id(pk), pr_type
}

person
{
     person_id(pk), pr_id(fk),  person_name
}

Here, pr_type data field may contain the entities such as great grandfather, grandfather, father, son, etc...

I hope this will give some understanding. Thanks.


Is it possible to write a select * view that includes new columns added to table?

The basic problem for your idea is that a view (or materialized view) uses "early binding", meaning the query is parsed at the time of creation. SELECT * is expanded to the list of columns visible at that time. Postgres registers a row type for the view. Later additions to the table are not cascaded to any views. (And existing columns cannot be deleted due to the registered dependency, unless you delete the view first.)

A function taking a polymorphic input type is a limited workaround, but only for registered row types. Example:

How to create function(table_name) that returns a set of the table

For your case: SELECT a + b AS ab, * FROM tbl, just use the simple query as given. If a + b is just a placeholder for a more complex calculation, I would suggest to encapsulate the calculation in a function like:

CREATE OR REPLACE FUNCTION f_ab(a int, b date)  -- use actual data types
  RETURNS int AS   -- use actual data type
$func$
SELECT a + b  -- placeholder for more complex calculation
$func$  LANGUAGE sql STABLE;

And adapt your query to:

SELECT f_ab(a, b) AS ab, * FROM tbl;


SQL Server 2014 complex Stored Procedure

Because you're essentially asking for the condition of joins to be changed through a lookup ("if my postalcode is in a surcharge area, change my carrier code joins to this, otherwise this"), I would recommend that you split this update into two sets. Here's some pseudocode to help get you started:

DECLARE @OrderID nvarchar(10)='B4'
DECLARE @mytable TABLE (CurrentSeed, Prefix, Suffix, ShippingService, CarrierCode)

-- Update Surchargeable Areas with Defaults; this assumes only ONE default for a postal code
UPDATE ...
OUTPUT ... INTO @mytable
FROM Sales                  s
JOIN ShippingAddress            ss  on  s.shippingAddressID=ss.ShippingAddressID
JOIN SurchargeAreas     ssa on  ss.PostalCode=ssa.PostalCode
JOIN dbo.Carriers           cc  on cc.carriercode=ssa.carriercode
      and cc.[Default] = 1  -- terrible practice using a SQL keyword as a column name, by the way!!
WHERE s.OrderID=@OrderID;

-- Update non-Surchargeable Areas
UPDATE ...
OUTPUT ... INTO @mytable
FROM Sales                  s
JOIN ShippingAddress            ss  on  s.shippingAddressID=ss.ShippingAddressID
JOIN Carriers cc ON cc.ShippingService = s.ShippingService
       AND cc.carriercode = s.carriercode
LEFT JOIN SurchargeAreas        ssa on  ss.PostalCode=ssa.PostalCode
AND cc.[Default] = 0
WHERE s.OrderID=@OrderID
          AND ssa.ID IS NULL;


Is a UNION query equivalent with or without DISTINCT in subqueries?

No, they are not necessarily "treated the same by the database engine." A test below shows that you might get different query plans.

In many cases, the difference between query plans may not matter for you. But in some (likely rare) cases it could matter significantly. For example, if SQL Server has a very poor cardinality estimate for one branch of your UNION, calculating the unique rows within that branch could result in a spill that would not have happened if the query only calculated the final set of unique rows.

SELECT @@version
-- Microsoft SQL Server 2017 (RTM-CU1) (KB4038634) - 14.0.3006.16 (X64)

SELECT create_date FROM sys.databases
UNION
SELECT create_date FROM sys.databases

SELECT DISTINCT create_date FROM sys.databases
UNION
SELECT DISTINCT create_date FROM sys.databases


Cannot set READ_COMMITTED_SNAPSHOT to OFF on Azure SQL

Azure SQL Database default database wide setting is to enable read committed snapshot isolation (RCSI) by having both the READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION database options set to ON. You cannot change the database default isolation level. However, you can control the isolation level explicitly on a connection. One way to do this, you can use any one of the following statements in Azure SQL Database before you BEGIN TRANSACTION:

SET TRANSACTION  ISOLATION LEVEL  SERIALIZABLE
SET TRANSACTION  ISOLATION LEVEL  SNAPSHOT
SET TRANSACTION  ISOLATION LEVEL  REPEATABLE READ
SET TRANSACTION  ISOLATION LEVEL  READ COMMITTED
SET TRANSACTION  ISOLATION LEVEL  READ UNCOMMITTED

SET TRANSACTON ISOLATION LEVEL controls the locking and row versioning behavior of Transact-SQL statements issued by a connection to SQL Server and spans batches (GO statement). All of the above works exactly the same as SQL Server.

In addition, the above statement "SET TRANSACTION ISOLATION LEVEL READ COMMITTED" will set the isolation level to read committed snapshot isolation (RCSI). This isolation level is different from read committed (RC). In other words, the default behavior of “SET TRANSACTION ISOLATION LEVEL READ COMMITTED" in on-premise SQL Server is RC, but in Azure SQL Database is RCSI. If you want to use exact RC (not RCSI) behavior in Azure SQL Database, you have to set the lock hint to the SQL statement.


What event information can I get by default from SQL Server?

There is quite a bit of valuable information that SQL Server tracks for you by default. Since SQL Server 2005 there has been a "default trace" that runs in the background, and since SQL Server 2008 there has been an Extended Events session automatically running, called system_health.
You can also find certain information from the SQL Server error log, the SQL Server Agent log, Windows event logs, and additional logging from things like SQL Server Audit, Management Data Warehouse, Event Notifications, DML Triggers, DDL Triggers, SCOM / System Center, your own server-side traces or Extended Events sessions, or third-party monitoring solutions (like those made by SentryOne). You can also optionally enable a so-called "Blackbox trace" to assist in troubleshooting.
But for this post I'm going to focus the scope on things that are generally enabled most everywhere: the default trace, Extended Events sessions, and the error log.
Default Trace
The default trace is usually running on most systems, unless you have disabled it using sp_configure. As long as it is enabled, this can be a rich source of valuable information. The following lists the trace events that are captured:
DECLARE @TraceID INT;

SELECT @TraceID = id FROM sys.traces WHERE is_default = 1;

SELECT t.EventID, e.name as Event_Description
  FROM sys.fn_trace_geteventinfo(@TraceID) t
  JOIN sys.trace_events e ON t.eventID = e.trace_event_id
  GROUP BY t.EventID, e.name;

You can get into more detail by joining to sys.trace_columns to see which events come with which data, but I'm going to skip that for now, since you can see what you have when you actually query the trace data for specific events. These are the events that are available on my system (you should run the query on yours to be sure they match, though this is still the same set of events through SQL Server 2019 CTP 2.4):
EventID  Event_Description
-------  ----------------------------------------------
18       Audit Server Starts And Stops
20       Audit Login Failed
22       ErrorLog
46       Object:Created
47       Object:Deleted
55       Hash Warning
69       Sort Warnings
79       Missing Column Statistics
80       Missing Join Predicate
81       Server Memory Change
92       Data File Auto Grow
93       Log File Auto Grow
94       Data File Auto Shrink
95       Log File Auto Shrink
102      Audit Database Scope GDR Event
103      Audit Schema Object GDR Event
104      Audit Addlogin Event
105      Audit Login GDR Event
106      Audit Login Change Property Event
108      Audit Add Login to Server Role Event
109      Audit Add DB User Event
110      Audit Add Member to DB Role Event
111      Audit Add Role Event
115      Audit Backup/Restore Event
116      Audit DBCC Event
117      Audit Change Audit Event
152      Audit Change Database Owner
153      Audit Schema Object Take Ownership Event
155      FT:Crawl Started
156      FT:Crawl Stopped
164      Object:Altered
167      Database Mirroring State Change
175      Audit Server Alter Trace Event
218      Plan Guide Unsuccessful

Note that the default trace uses rollover files and so the data available to you will only go back so far - the date range of available data depends on how many of the above events are being captured and at what frequency. If you want to ensure that you keep a longer history, you can set up a job that periodically archives away the currently inactive files associated with the trace.
Examples
In the question I asked a couple of questions that I have found. Here are example queries for pulling that specific information from the default trace.

Question: When was the last time an auto-grow happened in the AdventureWorks database, and how long did it take?

This query will pull all of the AutoGrow events in the AdventureWorks database, for both log and data files, that are still in the default trace log files:
DECLARE @path NVARCHAR(260);

SELECT
   @path = REVERSE(SUBSTRING(REVERSE([path]),
   CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;

SELECT
   DatabaseName,
   [FileName],
   SPID,
   Duration,
   StartTime,
   EndTime,
   FileType = CASE EventClass WHEN 92 THEN 'Data' ELSE 'Log' END
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass IN (92,93)
AND DatabaseName = N'AdventureWorks'
ORDER BY StartTime DESC;

Question: Who deleted the dbo.EmployeeAuditData table and when?

This will return any DROP events for an object named EmployeeAuditData. If you want to make sure that it only detects DROP events for tables, you can add a filter: ObjectType = 8277 (the full list is documented here). If you want to restrict the search space to a specific database, you can add a filter: DatabaseName = N'db_name'.
DECLARE @path NVARCHAR(260);

SELECT
   @path = REVERSE(SUBSTRING(REVERSE([path]),
   CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;

SELECT
  LoginName,
  HostName,
  StartTime,
  ObjectName,
  TextData
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass = 47    -- Object:Deleted
AND EventSubClass = 1
AND ObjectName = N'EmployeeAuditData'
ORDER BY StartTime DESC;

There is a complication here, and it is very edge case but thought it prudent to mention anyway. If you use multiple schemas and may have the same object name in multiple schemas, you won't be able to tell which one this is (unless its counterpart(s) still exist). There is an outside case that UserA might have dropped SchemaB.Tablename while UserB might have dropped SchemaA.Tablename. The default trace does not track the schema of the object (nor does it capture TextData for this event), and the ObjectID included in the trace is not useful for a direct match (because the object was dropped and no longer exists). Including that column in the output in this case might be useful to cross-reference against any copies of the table with the same name that still exist, but if the system is in this much disarray (or if all such copies have been deleted) there still might not be a reliable way to guess at which copy of the table was dropped by whom.
Extended Events
The following was the list of data you could cull from the system_health session in SQL Server 2008 and 2008 R2 (that list is more complete in modern versions):

The sql_text and session_id for any sessions that encounter an error with severity >=20
The sql_text and session_id for any sessions that encounter a "memory" type of error such as 17803, 701, etc (we added this because not all memory errors are severity >=20)
A record of any "non-yielding" problems (you have sometimes seen these in the ERRORLOG as Msg 17883)
Any deadlocks that are detected
The callstack, sql_text, and session_id for any sessions who have waited on latches (or other interesting resources) for > 15 seconds
The callstack, sql_text, and session_id for any sessions who have waited on locks for > 30 seconds
The callstack, sql_text, and session_id for any session that have waited for an extended period of time for "external" waits or "pre-emptive waits".

From Use the system_health event session (MSDN), the list is somewhat expanded in SQL Server 2012 (and remains the same for SQL Server 2014):

The sql_text and session_id for any sessions that encounter an error that has a severity >=20.
The sql_text and session_id for any sessions that encounter a memory-related error. The errors include 17803, 701, 802, 8645, 8651, 8657 and 8902.
A record of any non-yielding scheduler problems. (These appear in the SQL Server error log as error 17883.)
Any deadlocks that are detected.
The callstack, sql_text, and session_id for any sessions that have waited on latches (or other interesting resources) for > 15 seconds.
The callstack, sql_text, and session_id for any sessions that have waited on locks for > 30 seconds.
The callstack, sql_text, and session_id for any sessions that have waited for a long time for preemptive waits. The duration varies by wait type. A preemptive wait is where SQL Server is waiting for external API calls.
The callstack and session_id for CLR allocation and virtual allocation failures.
The ring_buffer events for the memory broker, scheduler monitor, memory node OOM, security, and connectivity.
System component results from sp_server_diagnostics.
Instance health collected by scheduler_monitor_system_health_ring_buffer_recorded.
CLR Allocation failures.
Connectivity errors using connectivity_ring_buffer_recorded.
Security errors using security_error_ring_buffer_recorded.

In SQL Server 2016, two more events are captured:

When a process is killed using the KILL command.
When SQL Server shutdown has been started.

(The documentation hasn't been updated yet, but I blogged about how I discover these and other changes.)
To get the more cryptic configuration applicable for your specific version, you can always run the following query directly, but you'll have to interpret the names and parse the predicates to match up to the more natural language lists above:
SELECT e.package, e.event_id, e.name, e.predicate
  FROM sys.server_event_session_events AS e
  INNER JOIN sys.server_event_sessions AS s
  ON e.event_session_id = s.event_session_id
 WHERE s.name = N'system_health'
 ORDER BY e.package, e.name;

If you are using Availability Groups, there are also two new sessions that you will find running: AlwaysOn_failover and AlwaysOn_health. You can see the data they collect with the following query:
SELECT s.name, e.package, e.event_id, e.name, e.predicate
  FROM sys.server_event_session_events AS e
  INNER JOIN sys.server_event_sessions AS s
  ON e.event_session_id = s.event_session_id
 WHERE s.name LIKE N'AlwaysOn[_]%'
 ORDER BY s.name, e.package, e.name;

These event sessions use ring buffer targets to store the data, so - like the buffer pool and the plan cache - older events will get phased out, so you won't necessarily be able to pull events from the date range you want.
Example
In the question I posed this fictitious question:

How many memory-related errors have happened today?

Here is a sample (and probably not very efficient) query that can pull this information from the system_health session:
;WITH src(x) AS
(
  SELECT y.query('.')
  FROM
  (
    SELECT x = CONVERT(XML, t.target_data)
      FROM sys.dm_xe_sessions AS s
      INNER JOIN sys.dm_xe_session_targets AS t
      ON s.[address] = t.event_session_address
      WHERE s.name = N'system_health'
  ) AS x
  CROSS APPLY x.x.nodes('/RingBufferTarget/event') AS y(y)
)
SELECT
  x, ts = CONVERT(DATETIME, NULL), err = CONVERT(INT, NULL)
INTO #blat FROM src;

DELETE #blat WHERE x.value('(/event/@name)[1]', 'varchar(255)') <> 'error_reported';

UPDATE #blat SET ts = x.value('(/event/@timestamp)[1]', 'datetime');

UPDATE #blat SET err = x.value('(/event/data/value)[1]', 'int');

SELECT err, number_of_events = COUNT(*)
  FROM #blat
  WHERE err IN (17803, 701, 802, 8645, 8651, 8657, 8902)
  AND ts >= CONVERT(DATE, CURRENT_TIMESTAMP)
  GROUP BY err;

DROP TABLE #blat;

(This example borrows loosely from Amit Banerjee's introductory blog post on the system_health session.)
For more information on Extended Events (including many examples where you can query for specific data), see this 31-part blog series by Jonathan Kehayias:
https://www.sqlskills.com/blogs/jonathan/an-xevent-a-day-31-days-of-extended-events/
Error Log
SQL Server by default keeps the current plus 6 most recent error log files (but you can change this). Plenty of information is stored there, including startup information (how many cores are in use, whether lock pages in memory is set, authentication mode, etc) as well as errors and other scenarios severe enough to be documented (and not captured elsewhere). One recent example was someone looking for when a database was taken offline. You can determine this by scanning through each of the most recent 7 error logs for the text Setting database option OFFLINE:
EXEC sys.sp_readerrorlog 0,1,'Setting database option OFFLINE';
EXEC sys.sp_readerrorlog 1,1,'Setting database option OFFLINE';
EXEC sys.sp_readerrorlog 2,1,'Setting database option OFFLINE';
EXEC sys.sp_readerrorlog 3,1,'Setting database option OFFLINE';
EXEC sys.sp_readerrorlog 4,1,'Setting database option OFFLINE';
EXEC sys.sp_readerrorlog 5,1,'Setting database option OFFLINE';
EXEC sys.sp_readerrorlog 6,1,'Setting database option OFFLINE';

I covered some other details in this recent answer, and there is also some good background information at toadworld and also in the official documentation.
One group of "errors" the error log does track by default - and can make important information fall off the tail much quicker - is every successful backup message. You can prevent these from filling up the error log with noise by enabling trace flag 3226.


How to insert or update using single query?

You can try this

IF EXISTS(select * from test where id=30122)
   update test set name='john' where id=3012
ELSE
   insert into test(name) values('john');

Other approach for better performance is

update test set name='john' where id=3012
IF @@ROWCOUNT=0
   insert into test(name) values('john');

and also read this bad habits to kick on schema prefix


Same query on same same table, different response time

For MySQL to handle your query efficiently, you need to create a compound index. Without an index, MySQL must examine every row in your table to count the ones you want. That means it has to read every row from the SSD. That takes time.

Your filtering criteria are ...

WHERE column_4 = 1
  AND column_8 is null
  AND column_3 > CURDATE() - INTERVAL 30 DAY

You need an index that can satisfy your query efficiently. Because two criteria are matches to constant values, those two  columns should come first. Then, the last criterion filters a range of values. Starting with some date in the past it filters records.

So, your index should be on (column_4, column_8, column_3). You create it like this:

 CREATE INDEX tocount ON malicious_table (column_4, column_8, column_3)

You can think of these indexes (they use an indexing technique called BTREE) as lists of data in order. So, the query planner can random-access the index to the first eligible row, and then scan the index sequentially until the last eligible row. That's far more efficient than reading the whole table.

You mentioned your query time is unpredictable. You're right that's unpleasant for your users. It's hard to know exactly why, even for an expert with access to your server. But this general observation applies: If others are using the same server for other things, their operations and yours may interfere with each other in unpredictable ways.

Pro tip Notice that the index I've given you is designed to match the query you gave. A different query might need a a different index. For example,  if your query were

  SELECT COUNT(*), MAX(column_13), SUM(column_7)
    FROM malicious_table
   WHERE column_4 = 1
     AND column_8 is null
     AND column_3 > CURDATE() - INTERVAL 30 DAY

it would be satisfied with this covering index:

CREATE INDEX tosummarize ON malicious_table
    (column_4, column_8, column_3, column_7, column_13);

The index I first suggested is made redundant by this index because this one contains the same columns in the same order as that one.

In general, you should avoid creating lots of one-column indexes in hopes of making queries faster. As a database grows, it generally needs indexes to be added (or sometimes dropped) to match the data. The cool thing about indexes is you can add and drop them without affecting your data or programs. Here's a good reference:  https://use-the-index-luke.com/

Edit: Performance variations between queries can be due to many hard-to-discern factors. Is your MySQL server running on a virtual machine on a busy VM host? Does your MySQL server process serve applications other than yours?  How busy is your system? Are lots of other processes INSERTing or UPDATing rows in your table concurrently with your counting queries? If so, the variability may be due to moment-to-moment changes in that concurrent workload. Bluntly, good luck figuring out that one in any detail! You may want to move on to other database performance issues.

There is one thing to try. Precede your counting query with this transaction isolation level setting:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

This reduces the chance that your counting query locks up waiting for other operations on the table to complete. Read this for more details: https://stackoverflow.com/questions/7937472/setting-transaction-isolation-level-of-mysql Your query will still return a useful result, but it might not pick up changes to the table that happen concurrently to your counting request. It is probably acceptable for your application to miss a few most-recent rows in this count.

And, version 5.6 of MySQL is quite old. It was first released in February 2013. There have been bug and security fixes since, Still, many dozens of years of programmer effort have been spent on later versions since 5.6 came out. Most of them have been spent improving performance and reliability. Upgrade if you can.


PostgreSQL: When should I use fillfactor < 100 for tables on SSD?

You're addressing the performance problems that arise from random access, which as you point out is solved well enough by eliminating the cost of random access altogether with an SSD. However, postgres does offer "Heap Only Tuple (HOT) updates" (some notes here and here and here), which may benefit you from having a fillfactor less than 100% regardless of the physical medium in the case that you make updates moderately to frequently (the more frequent, the more the savings) and those updates do not affect any indexed columns.

Imagine if the fillfactor were exactly 100% and over time your table's data fills exactly one page. On the next update (assume it's of just one row), a copy of the updated row is made to the next page and the original row is left on the current page; if the transaction is committed the pre-update version of that row is marked as dead to be removed by a vacuum and the post-update version is marked as live. Now, let's say that update only affected a column "data_value" for a specific PK value, like update table_name set data_value = 1 where pk = 3. In this case, the heap location of the data changed pages, so the index supporting the PK constraint has to be updated to reflect that change. Regardless of the physical medium, that's a potential performance hit. Moreover, that dead tuple is left lingering until vacuum reclaims the space.

With HOT updates, as long as you don't update an indexed column, the database can avoid having to update index entries by keeping the post-update tuple on the same page as the pre-update table, which can speed up your updates. On top of that, HOT updates provide a bit of automatic maintenance and keep the page freer of those dead rows, reducing the need for vacuuming and auto-vacuuming.

Also, even in the case of data access, you might still benefit from having your data localized. Even if a random page read from an SSD takes a negligible amount of time, having to perform many reads is still (many * negligible time) more expensive than just reading the minimum amount of pages required to hold the data.


SQLCMD is not working in pre-deployment script in sql project

Community wiki answer:

You need to switch to SQLCMD mode in Visual Studio via the menu SQL > Execution Settings.

You can also have all SQL files open in SQLCMD mode by default if you dig into Options > SQL Server Tools > Query Execution.


Mysql 5.7 innodb_buffer_pool_size - zero I/O?

Your ulimit -a report indicates Open Files is limited by the OS to 1024.
From your OS Command Prompt, ulimit -n 32000 would increase this limit dynamically.
Shutdown then restart your instance would make the additional file handles available and reduce opened_tables, opened_table_definitions, opened_files.
For this change to persist over OS restarts, follow this url - https://glassonionblog.wordpress.com/2013/01/27/increase-ulimit-and-file-descriptors-limit/

but use 32000 rather than 500000 used in their example.

Suggestions to consider for your my.cnf [mysqld] section
Rate Per Second - RPS
innodb_lru_scan_depth=100  # from 1024 to conserve 90% of CPU cycles used every second for the function
innodb_buffer_pool_dump_pct=90  # from 25 (percent) to reduce warmup bp time required
innodb_io_capacity=2000  # from 2500 to extend life of your NVME device
read_buffer_size=192K  # from 1M to reduce handler_read_rnd_next RPS of 160,774

REMOVE innodb_io_capacity_max to allow MySQL to
auto calc the max as innodb_io_capacity*2
Many more opportunities exist to improve your configuration.  View profile, Network profile for contact and free downloadable Utility Scripts to assist with performance tuning, specifically our findfragtables.sql will be helpful.
Monitoring SHOW GLOBAL STATUS LIKE 'innodb_data_reads'; divided by uptime for read rate per second should be your primary consideration for when you need more RAM to support your innodb_buffer_pool.  The provided SHOW GLOBAL STATUS data indicates RPS of 16.  Many people are OK with up to 100 RPS.  Less RPS is desirable, when funds are available. 0 RPS is likely unnecessary due to the way innodb only ages out of RAM the least recently used data.


Weird result when grouping and self-joining a table

It's because your price column is unsigned.

CASTing the values works ok:

SELECT
 ii.invoice_item_id,
 pii.invoice_item_id,
 ii.previous_invoice_item_id,
 cast(ii.price as signed) - cast(pii.price as signed ),
 ii.price,
 pii.price
FROM test ii
JOIN test pii ON pii.invoice_item_id = ii.previous_invoice_item_id
GROUP BY ii.invoice_item_id


out of all columns in a table, how can I find the one datatype that is cheapest and I could convert all other columns datatypes into?

Perhaps something like this will suit you:

SELECT
    Unpivoted.*
INTO #Unpivoted
FROM sys.dm_os_host_info AS HI
CROSS APPLY
(
    VALUES
        (N'host_platform', CONCAT(HI.host_platform, NULL)),
        (N'host_distribution', CONCAT(HI.host_distribution, NULL)),
        (N'host_release', CONCAT(HI.host_release, NULL)),
        (N'host_service_pack_level', CONCAT(HI.host_service_pack_level, NULL)),
        (N'host_sku', CONCAT(HI.host_sku, NULL)),
        (N'os_language_version', CONCAT(HI.os_language_version, NULL)),
        (N'host_architecture', CONCAT(HI.host_architecture, NULL))
) AS Unpivoted
    (ColumnName, TheValue);

SQL Server will automatically work out the minimal length and string type of the CONCAT results.

If you need to preserve the underlying type information, including collation, and can live with the more limited set of data types that can be held in sql_variant, use that:

SELECT
    Unpivoted.*
INTO #Unpivoted
FROM
(
    SELECT
        host_platform = CONVERT(sql_variant, HI.host_platform),
        host_distribution = CONVERT(sql_variant, HI.host_distribution),
        host_release = CONVERT(sql_variant, HI.host_release),
        host_service_pack_level = CONVERT(sql_variant, HI.host_service_pack_level),
        host_sku = CONVERT(sql_variant, HI.host_sku),
        os_language_version = CONVERT(sql_variant, HI.os_language_version),
        host_architecture = CONVERT(sql_variant, HI.host_architecture)
    FROM sys.dm_os_host_info AS HI
) AS HI
UNPIVOT
(
    TheValue FOR ColumnName IN
    (
        host_platform,
        host_distribution,
        host_release,
        host_service_pack_level,
        host_sku,
        os_language_version,
        host_architecture
    )
) AS Unpivoted;


The USING clause expects a valid TNS entry or alias. Use this:

CREATE DATABASE LINK OLD_FOO
CONNECT TO FOO IDENTIFIED BY "pa$$w0rD"
USING '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = old.example.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE)))';


How often does the Impala StateStore refresh?

It is my understanding that impala does not automatically refresh the metadata.  If you create new tables etc through impala (1.2 and higher) it will update the other impala nodes, however if you create new tables etc in hive you will have to manually refresh the impala metadata.

You can manually refresh the metadata on either a table basis with 'REFRESH table_name' or you can do this for all tables with 'INVALIDATE METADATA'.  Also, note that if you update the files on HDFS you will have to refresh the table to pickup the new data as well.


How to enable odbc connnections in new sql server setup?

By default when Express Edition is installed the TCP/IP protocol is disabled. You need to go into SQL Server Configuration Manager and enable that protocol.


Should I use a Column naming convention or rely on column descriptions?

Pick a standard, make sure it makes sense, and document it. There have been plenty of debates about this (e.g. whether the identity column in the contacts table should be called ID or ContactID, or whether the contacts table should be called dbo.Contacts or the unnecessarily verbose dbo.tblContacts), and you're never going to find agreement among your peers. Which standard you choose is not important; what is important is that you use it consistently, make it well-known, and get agreement from your colleagues that this is the way.
Your columns should be self-documenting (and not cryptic, abbreviated or obfuscated) where practical. I would NOT rely on the extended properties, but that's just me (google/bing for problems with extended properties and you will see plenty of complaints I would guess). External documentation / data dictionary is much more valuable to me - if your database goes south and so does your backup, it makes it that much easier to rebuild. It also makes it easier to describe your schema to junior developers etc. without giving them direct access to everything.


Predicting the primary keys exhaustion in MySQL based on rate of insertion

Find tables that have an AUTO_INCREMENT column.
Discover the datatype of that column, including SIGNED vs UNSIGNED.
Use that to establish the drop-dead limit.
SELECT MAX(id) FROM tbl; to see where you are now.

At that point, you can go one of two directions.

Plan A:  Compute what percent of the limit the id already is, and sort on that.

Plan B:  Check MAX(id) a week later, then compute how many weeks before the limit will be hit.

When you do find a table that is threatening to hit the limit, find out if the ids are being "burned".  Do this by comparing COUNT(*) versus MAX(id).  Either the table is really big (compared to the datatype used), or there are a lot of gaps.

If there are a lot of gaps, then dig into what the code does.  There are many flavors of INSERT that "burn" ids.  REPLACE burns a id because it is effectively DELETE + INSERT.  INSERT IGNORE, if the row is present, burns the id that it otherwise would use.  IODKU burns when it updates.  Etc.

If you are burning ids due to Normalization, see this.


Should I use bigint or varchar(20) as primary key, performance-wise, for Postgres?

Definitely store as a bigint: this uses 8 bytes, while an 18 character string uses 19 bytes.


How can I generate scripts for all functions in pgAdmin?

There is no explicit switch to just export functions. I did some research since it's a nice question.

I can think of some ways to do this:

Using pgAdmin, you get the code that makes up the function when you right-click on the function, and choose "Scripts->Ceate". You could copy&paste the contens and put it into a sql file that you can import like any other file
Exporting the schema and importing only the functions you need (tedious, since you need the exact name and arguments of the function. This blog post explains it:

pg_dump -Fc -s | pg_restore -P 'funcname(args)'
Do some querying in the schema/pg_proc and export the results. This could be another function that just exports the functions...


Max connections SQL Server Express could have?

It seems with Windows 7, you can have up to 20 concurrent connections but no more.

If you need more, you'll have to put your SQL Server installation onto a Windows Server OS.

Also see this other SO question on the same topic - SQL Server Express does not limit the number of max connections (which have a 32'768 limit on any edition of SQL Server)


Yes, WE8ISO8859P1 is a subset of AL32UTF8, though a small bit of conversion might be needed (which CSALTER will deal with & CSSCAN will inform you of when you do a preliminary scan).

A must-read for Oracle 10.2 is here.

Doing a full export/import will be more time-consuming than using csscan/csalter.

Another good, albeit old, read is the Oracle Character Migration Best Practices white paper.


It looks like you are creating a New Server Instance Profile, not a New Server Instance.

If you installed MySQL already, then go to the DOS Prompt and run

C:\> net start mysql

or

Click Start
Click Control Panel
Click Administrative Tools
Double Click Services
Right Click on MySQL and Choose Start Service

To verify that MySQL is running on the Windows 7 machine,

Right Click on Bottom Task Bar and Click Start Task Manager
Click on Processes Tab
Scroll around and Look for the process mysqld

Once you have MySQL running on the Windows machine, you can go to to create a Server Instance Profile. The key here is to install MySQL first.


Not able to install PostgreSQL 9.4 on Ubuntu

14.04 shipped with PostgreSQL 9.3, so that's what you're going to get if you use Ubuntu's repositories.

If you want PostgreSQL 9.4 or newer releases you need to add a 3rd party package repository, like the official PostgreSQL repositories from http://apt.postgresql.org/ . See the instructions there for details.

(You should also probably read https://help.ubuntu.com/community/PostgreSQL)


How can MongoDB elect a new primary when a datacenter has failed?

You need another replica set member (or arbiter) in a 3rd data center. But if you cannot extend to a 3rd center, then add the 5th member of the replica set to your main data center. Your backup data center will remain with two members.

If backup data center goes down, main center will stay up and replica set operations will continue.

If main center goes down, replica set operations will be read only and you will need to perform manual intervention.

References:

Deploy a Geographically Redundant Replica Set

Replica Sets Distributed Across Two or More Data Centers


SQL Server: Simple database restore with overwrite

Unfortunately not. SQL Server will always attempt to restore to exactly what is recorded in the backup file, unless you explicitly specify otherwise (with MOVE in this case).

The REPLACE option only works if you're replacing files owned by the database you're restoring (which makes sense, You may have said you're fine killing DB_A by restoring over it, but SQL doesn't really know how you feel about killing DB_B as well).

If this is something you'll be doing regularly then, then write a small sql script with everything stated, save it and use that every time you want to do the restore. This is how I do our overnight restores for dev/UAT/Training from prod.


Selecting the average of the top percent of duration, grouped by minute

Start by working out the duration per minute:

select sum(duration) as dpm,  datediff(minute, '2015-06-5 13:00', time) as mins_since_start
from table
where time >= '2015-06-5 13:00'
and time < '2015-06-5 14:00'
group by datediff(minute, '2015-06-5 13:00', time)

Now get the top 5 percent of this.

select top (5) percent sum(duration) as dpm,  datediff(minute, '2015-06-5 13:00', time) as mins_since_start
from table
where time >= '2015-06-5 13:00'
and time < '2015-06-5 14:00'
group by datediff(minute, '2015-06-5 13:00', time)
order by dpm desc

Then wrap it up and get the

select AVG(Q.dpm) Avg5Perc
FROM
(select top (5) percent sum(duration) as dpm,  datediff(minute, '2015-06-5 13:00', time) as mins_since_start
from table
where time >= '2015-06-5 13:00'
and time < '2015-06-5 14:00'
group by datediff(minute, '2015-06-5 13:00', time)
order by dpm desc
) Q;

This then gives you the average of the top five percent. I prefer to use datediff like this because it means you're not restricted to a single hour.


Why Isn't the SQL Server Agent Service Set to Automatic Startup by Default?

During the install process for SQL 2008 R2 it gives you the option to change the start-up type.

I can see a slight desire to not have it start automatically depending on what you might be using it for. I have some setups that the user has some Agent jobs that clean up data or immediately start processing data. If I am troubleshooting, or in general the server goes down, I may not want all that processing to start back up immediately after bring the server back online.

In most instances though you will see the Agent set to automatic start up.


How to find who changed a table value in SQL Server 2014?

SQL Server does not keep track of who does what without you setting it up, now is a great time to set this up because you'll catch it the next time. You can try and read the Transaction Logs to see what happened (minus the principal account used to make the change), but otherwise you'll want something set up in the future.

The best approach to see who changed what and when is putting triggers on your tables when they change, you can audit the information by inserting what changed into the audit table. I add fields such as getdate and suser so I can see who modified and when it was modified.

An even lighter version is logging sp_whoisactive or other monitoring tools using DMV's. This will not catch everything.

CT / CDC is another option, however they will not reveal who made the change. You can make an alteration to the triggers so it would also use suser, but it will only reveal the account running CT / CDC.

If upgrading to 2016 or higher is a viable option, I would recommend looking at temporal tables combined with SQL Auditing. Temporal tables would give you the in-depth analysis of what changed and the SQL Audit would give you insight into who made said changes. Here's an excellent answer by Nic on stack exchange to set up the SQL Audit.


Insert Timestamp on value change from other table

Logically (based on the names of your tables), a particular item of evidence cannot be checked out to more than one person at once. We'll assume, then, that no more than one EvidenceTimeStamp table record for a given CaseID and EvidenceNum should have a NULL Evidence_Returned_Timestamp.

In that case, you'd need a trigger on your Outbound Ship Table with something like the following in it:

UPDATE ets
   SET ets.Evidence_Returned_Timestamp = GETDATE()
  FROM EvidenceTimeStamp ets
         INNER JOIN inserted I ON (    ets.CaseID = i.CaseID
                                   AND ets.EvidenceNum = i.EvidenceNum
                                  )
 WHERE i.Outbound_Item_Recevied = 1
   AND ets.Evidence_Returned_Timestamp IS NULL
;

(SQL Server syntax; modify as needed for other platforms)

This will work whether you're modifying/inserting one record or many in the Outbound Ship Table. However, this will update all rows for the given CaseID and EvidenceNum pair(s) that currently have NULL in Evidence_Returned_Timestamp, so if you can have more than one open row for a specific CaseID and EvidenceNum, you'll need some other way to find the right row(s) to update.

(To clarify: If you've got open entries for case 123, evidence numbers 2 and 7, and case 456, evidence numbers 3 and 7, and you updated an Outbound Ship item for case 123, evidence num 7, then the other three open entries are fine.  However, if you had two EvidenceTimeStamp rows with case 123, evidence number 7, then both of them would have their Evidence_Returned_Timestamp set.)

NOTE: Every table should have a column (or group of columns) that is guaranteed to uniquely identify every row in the table and it (or they) really should be the primary key. You may not want to set that up as a clustered index, but you enforce uniqueness by making that the primary key.


How to detect sudden bottleneck in mysql with 2000%CPU

You should use SHOW FULL PROCESSLIST in MySQL console to display all running queries.


How do I create a CLR UDF with external name specifying namespace, class, and subclass?

As far as I can tell (I did some testing of my own), this is not supported.

The documentation hints at this:

  If the class has a namespace-qualified name that uses a period (.) to separate namespace parts, ...

Since there's no mention of a class-qualified name, I'm assuming SQL Server will traverse the hierarchy only by namespaces, with the last name being the class itself. There wasn't any way I could find to delimit the namespace and class name portions of the fully-qualified class name either.

What I wonder, though, is why your code is structured this way? The method you need to specify must be static, so nesting it within an extra class scope seems like you're (ab)using a class in place of a namespace.


Can PostgreSQL use nulls in its indexes?

PostgreSQL certainly can use an index for IS NOT NULL. I don't see any query planner assumptions about that condition, either.

If the null fraction for the column (pg_statistic.stanullfrac) is low enough to suggest that the index is usefully selective for the query, PostgreSQL will use an index.

I can't figure out what you're trying to say with:

  If this is correct, is my understanding that an index on a column defined as "NOT NULL" not be used in a query which uses that column?

Certainly an index won't get used for an IS NOT NULL condition on a NOT NULL column. It'd always match 100% of rows, so a seqscan will almost always be much faster.

PostgreSQL won't use an index if the index doesn't filter out a large proportion of rows for a query. The only likely exception is when you're asking for a set of columns covered by a single index, in an order matching that of the index. PostgreSQL might do an index-only scan then. E.g. if there's an index on t(a, b, c) and you:

select a, b FROM t ORDER BY a, b, c;

PostgreSQL might use your index, even though no rows are filtered out, because it only has to read the index and can skip reading the heap, avoid doing a sort, etc.


What if the lowest granularity level can legitimately be null?

I agree.  Your business users would not like "AllOrders" and "SizedOrders" fact tables.

I suggest creating a synthetic row in OrderDetailUnits for each OrderDetail where you have entries which are legitimately null.  I recommend populating *NotApplicable* or something like that for each dimension which is in the OrderDetailUnits.

While not exactly parallel with what you're doing, I suggest reading up on Late Arrriving Dimensions to get some ideas.


MySQL memory usage slowly increasing then crash - what's causing this?

What else is running on the machine?  There seem to be 2 or 3 jumps in memory usage.  MySQL (usually) has one jump or gradual rise.

Nothing looks wrong in my.cnf.  But change innodb_buffer_pool_size to 5G; that will probably keep it from crashing as much.

What OS are you using?  If it has "swappiness", set that to 1, not 0.


Estimated Rows Higher than Actual Despite Updating Statistics

Cardinality estimates are just numbers. They don't matter unless you have a performance problem. You state in your question that there's a difference between the actual number of rows and estimated number of rows at some steps in the query plan. That's certainly true, but it's pretty uncommon to see non-trivial query plans for which estimates match the actual number of rows exactly. I wouldn't focus on a difference of a thousand rows unless you've identified that difference to be the root cause of a query performance issue.

Let's consider updating statistics: why might you see inaccurate cardinality estimation even after updating statistics? Statistics often don't fully describe the column's data. They could be sampled, 201 steps could be insufficient for some data sets, density isn't a good metric if there's skew in the data, and so on. Join cardinality estimation and combining filters on multiple columns is difficult even with perfect statistics objects because histograms are only available on one column. SQL Server often doesn't have information about the level of correlation between columns, so it needs to make a guess.

If you'd like to learn more I recommend taking a look at Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator by Joe Sack. Here's a quote on one of the many differences between the legacy CE and the new CE:

  Multiple Join Conditions

  For joins with a conjunction of equality predicates, the legacy CE
  computes the selectivity of each equality predicate, assumes
  independence, and combines them. In contrast, the new CE estimates the
  join cardinality based on multi-column frequencies computed on the
  join columns.

The CEs use different models. One model may be a better fit than the other for your data or even for just a single join. In this case, it has very little to do with statistics. Updating statistics just isn't enough to result in good estimates for all cases.

On the other hand, if you have a specific performance that you'd like help with, you'll need to give more information. I don't see anything alarming in the query plan that you posted. What is the performance problem that you have with the query?


How do I fix the naming of columns in Metabase?

You can disable "Friendly Table and Field Names" in Admin -> Settings -> General:

After you do it, go to Databases -> your_db_name and trigger "Sync database schema now" and "Re-scan field values now" actions:


From Doc Online:

The view referenced by table_or_view_name must be updatable and reference exactly one base table in the FROM clause of the view.
For example, an INSERT into a multi-table view must use a column_list that references only columns from one base table.

https://docs.microsoft.com/en-us/sql/t-sql/statements/insert-transact-sql?view=sql-server-ver15


"ERROR:malformed array literal" when using json_to_record with a JSON array element in Postgres 9.4

Just a slight variation to Chris's answer:

SELECT a, translate(b, '[]', '{}')::text[] AS b, d
FROM json_to_record('{"a": 1, "b": ["hello", "There"], "c": "bar"}')
AS x(a int, b text, d text);

The idea is the same: massage the JSON array into an array - in this case, through an array literal.  In addition to a bit cleaner looking code (though I love it, regex usually does not help much in this regard :), it seems slighly faster, too:

CREATE TABLE jsonb_test (
    id serial,
    data jsonb
);

INSERT INTO jsonb_test (id, data)
SELECT i, format('{"a": %s, "b": ["foo", "bar"], "c": "baz"}', i::text)::jsonb
FROM generate_series(1,10000) t(i);

SELECT a, string_to_array(regexp_replace(b, '\[*\"*\s*\]*','','g'),',') AS b, d
FROM jsonb_test AS j,
LATERAL json_to_record(j.data::json) AS r(a int, b text, d text);

-- versus

SELECT a, translate(b, '[]', '{}')::text[] AS b, d
FROM jsonb_test AS j,
LATERAL json_to_record(j.data::json) AS r(a int, b text, d text);

On this dataset and on my test box, the regex version shows and average execution time of 300 ms, while my version shows 210 ms.


Fix transaction log file after database update

Short answer:

Just leave the log file as big as it typically needs to be, and stop worrying about it.

Longer answer:

First, why do you want to keep shrinking the file? If it's just going to grow again (and keep in mind that shrink and grow operations are expensive, especially for the log), then what did you gain? What did you use all that freed up space for in the meantime? Please read this page, in full, this article, and this blog post, and then come back with further questions. You're not seeing 8 million people on the Internet tell you not to shrink for no reason: it's a really bad and pointless exercise in almost all cases. There are exceptions, but this does not sound like one of them.

  Sometimes I need to execute the shrink multiple times

I've addressed the primary reason for this in this answer. Essentially, SQL Server has to have two checkpoints or two log backups in order to wrap around to the reusable portion of the log.  (This has to do with the number and arrangement of the VLFs in the log file, and where the current LSN points. We could post a book here but the easy answer is to just do what you're doing or run an additional log backup before starting.)

Another possibility is that the log can't be reused for a variety of reasons. Next time you try to shrink the file and it doesn't shrink on first try, check sys.databases.log_reuse_wait_desc. For example, if you're also using replication on this database, this can be caused by that activity, an un-replicated transaction, etc.

But usually it's just the wrap-around thing. So next time, issue two manual log backups - I don't see why you need to wait for the 15 minute interval (unless additional, smaller log backups in between will throw off any manual processes you have around your AGs). Or (as per above) just stop performing this pointless work - especially since that work is done over and over on every replica. Once again, this is useless effort you're putting in with the net result of: you freed up a little disk space for a little while.


The double quote " is actually specifying a field or an identifier.  Use single quotes to specify your string filters:

SELECT *
FROM information_schema.TABLES
WHERE TABLE_SCHEMA='mysql'
AND TABLE_NAME='servers'
;

Problem #2 could be you are USEing the wrong database.

For example, if your table servers is in the schema mysql and you are currently using INFORMATION_SCHEMA and you try to DESC SERVERS; you will receive an error.  To get around this you can fully qualify your table name (in this example DESC mysql.servers) or USE <schema_name_here> and then issue your DESC SERVERS; command.

Try explicitly spelling it out: DESCRIBE mysql.servers;  Also, make sure that you are using the correct case for your table names.  Servers, SERVERS, and servers are all different in MySQL.


I get ORA-13605 when trying to run SQL Tuning Advisor from SQL Developer

The solution is very simple. The string of the query is just too long.

I'm trying to optimize a stored procedure using dynamic sql with a parameter to decide if the sql is executed or output.

I just copied the output into a fresh sql developer pane and tried to use the tuning advisor.

The problem is that the generated output has a lot of trailing blanks, I just have to remove them.


Not sure if you ever got this solved, but I was able to find it in the GAC:

C:\Windows\assembly\GAC_MSIL\Microsoft.DataTransformationServices.Controls\...\Microsoft.DataTransformationServices.Controls.DLL

Let me know if that did the trick for you


How to determine if an ALTER statement will be a metadata-only operation O(1) or a size of data operation O(n)?

Generally there are three different ways SQL Server can proceed when it comes to table alteration.
1. Metadata only changes
examples - dropping a column, changing a not nullable column to nullable one or adding a nullable column to the table.
2. Alteration requires changing the metadata only, but SQL Server needs to scan the table to make sure it confirms to the new definition.
example - changing a nullable column to not nullable.
In this case SQL Server will need to scan all the data rows in the table to make sure that there are no null values stored in that particular column.
3. Alteration requires changing all the rows in the table in addition to the metadata.
example - change a fixed-length char column to varchar. In this case SQL Server will need to move the data from fixed to variable length section of the row.

I read about table alteration in the SQL Server Internals book by Dmitri Korotkevitch sometime back and found it very helpful to understand this topic.

Though as suggested by Kin in the last answer, there is nothing better than being able to test it out and assess the impact before going into production with the change. Hope this helps.


Mysql enable remote connection in centos,my.cnf does not have bind-address to comment out

The MySQL server listens on a single network socket for TCP/IP connections. This socket is bound to a single address, but it is possible for an address to map onto multiple network interfaces. The default address is 0.0.0.0.

If the address is 0.0.0.0, the server accepts TCP/IP connections on all server host IPv4 interfaces.
If the address is ::, the server accepts TCP/IP connections on all server host IPv4 and IPv6 interfaces. Use this address to permit both IPv4 and IPv6 connections on all server interfaces.

Source: http://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_bind-address

You can check your current bind address by executing:

$ mysql -b -e "SHOW GLOBAL VARIABLES like 'bind_address'"
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| bind_address  | 127.0.0.1 |
+---------------+-----------+

on the command line, or SHOW GLOBAL VARIABLES like 'bind_address'; on a mysql client.


sp_AskBrent not logging queries when saving to table vs. sp_WhoIsActive

Thanks for using our free scripts. You should really be using sp_BlitzFirst now, not AskBrent. That aside, what your command is missing is the table names for various performance counters. This is the 'full' command, which you can pare down to whichever result sets you're interested in.

EXEC sp_BlitzFirst @OutputDatabaseName = 'DBATools', @OutputSchemaName = 'dbo', @OutputTableName = 'BlitzFirstResults', @OutputTableNameWaitStats = 'BlitzFirstResults_WaitStats', @OutputTableNameFileStats = 'BlitzFirstResults_FileStats', @OutputTableNamePerfmonStats = 'BlitzFirstResults_PerfmonStats'


Unfortunately not, you cannot export from a 10g database with the 11g client. You can, though, use a dump file from a 10g database (exported with the 10g client) to import into an 11g database (with the 11g client).

Alternatively you can use the newer datapump functionality, and if you create a database link from 11g to 10g you can even transfer the data directly between them without needing to create an intermediate dump file at all.


Following sample may be useful to build query which you want to write.

USE tempdb
GO

CREATE TABLE t_Ids (id VARCHAR(10) PRIMARY KEY )
GO

INSERT INTO t_Ids VALUES('id1'),('id2'),('id3'),('id4')
GO

CREATE VIEW v_V1
AS
SELECT 'id1' id, 2 value UNION
SELECT 'id1' id, 1 UNION
SELECT 'id1' id, 3 UNION
SELECT 'id2' id, 1
GO

CREATE VIEW v_V2
AS
SELECT 'id3' id, 'London' city UNION
SELECT 'id3' id, 'Paris' UNION
SELECT 'id1' id, 'London'
GO

SELECT t.id, ISNULL(SUM(v1.value), 0) AS agg_value,  COUNT(DISTINCT v2.city) cnt_city
FROM t_Ids AS t
LEFT OUTER JOIN v_v1 AS v1 ON t.id = v1.id
LEFT OUTER JOIN v_v2 AS v2 ON t.id = v2.id
GROUP BY t.id
GO

DROP TABLE t_Ids
GO

DROP VIEW v_v1, v_v2
GO