New Table vs Adding Columns To Existing Table

Instead of arguing normalization rules, I will use predicates and constraints.

[P1] Product number (PRODUCT_ID) named (PRODUCT_NAME) exists.

(c1.1) Product is identified by product number.

(c1.2) For each product that product has exactly one product name; for each product name exactly one product has that product name.

product {PRODUCT_ID, PRODUCT_NAME}  -- P1
    KEY {PRODUCT_ID}                -- c1.1
    KEY {PRODUCT_NAME}              -- c1.2

[P2] Country named (COUNTRY_NAME) with assigned country code (COUNTRY_CODE) and number (COUNTRY_ID) exists.

(c2.1) Country is identified by country number.

(c2.2) For each country, that country is assigned exactly one country name; for each country name, that country name is assigned to exactly one country.

(c2.3) For each country, that country is assigned exactly one country code; for each country code, that country name is assigned to exactly one country.

country {COUNTRY_ID, COUNTRY_NAME, COUNTRY_CODE}  -- P2
    KEY {COUNTRY_ID}                              -- c2.1
    KEY {COUNTRY_NAME}                            -- c2.2
    KEY {COUNTRY_CODE}                            -- c2.3

[P3] Product (PRODUCT_ID) in country (COUNTRY_ID) is assigned price of (AMOUNT) (CURRENCY).

(c3.1) For each country and product, that combination of that country and that product occurs at most once.

(c3.2) For each combination of country and product, that product in that country has exactly one price.

(c3.3) For each price, it is possible that more than one product in a country has that price.

(c3.4) If a product is assigned a price for a country, then that product must exist.

(c3.5) If a product is assigned a price for a country, then that country must exist.

country_price {COUNTRY_ID, PRODUCT_ID, AMOUNT, CURRENCY}  -- P3
          KEY {COUNTRY_ID, PRODUCT_ID}                    -- c3.1

          FK1 {PRODUCT_ID} REFERENCES product {PRODUCT_ID} -- c3.4
          FK2 {COUNTRY_ID} REFERENCES country {COUNTRY_ID} -- c3.5

Note:

[Px]   = predicate  x
(cx.y) = constraint x.y

KEY = PK or AK

PK  = PRIMARY KEY
AKn = ALTERNATE KEY (UNIQUE)
FKn = FOREIGN KEY

So this is basics, as per your first suggestion.

Note that adding pricing for a new country does not involve schema changes, but adding data. What happens to the application code if you were to add a column to the product table instead?

Consider the following questions:

For which countries do we have all product prices defined?
For which countries we do not have any product pricing defined?
Which countries do not have pricing for all products defined -- but do have at least one?
Which product prices are not defined in country X?

These are straightforward queries. An analyst may prepare these queries and the code will not change as you keep adding new countries and products. Now try to formulate these queries for a model where you add columns to the product table. What happens to the query code if you were to add a column to the product table?


How to know when to scale RDS MySQL instance? How to scale it?

You need to scale it up when resource of CPU and disk io is the bottleneck and the cost of optimising the database is higher than scale up.

There are many way to scale it horizontally for sure. Master slave replication is one of the most used way to scale horizontally. You can also run MySQL cluster too.


Troubles with an attribute that depends on the value of another attribute

My first thought off the top of my head is that you could put in the relationship_type attribute and have it be "self" rather than null for the primary. Another possibility is that you could make the "type" include "primary", "dependant spouse", or "dependant child", depending on how you need to access it.

I know that's not anything clever, design-wise. I'm interested to hear what others suggest, also.


How to calculate a percentage over a dimension attribute and get correct aggregates?

Your main problem is that you are storing a measure in your dimension. You will keep running into issues with multiselect and or aggregation unless you store the measure where it belongs, namely in the fact table.

Either change your ETL to copy the max_weight in your fact table, or use a named query in your DMV joining your fact table to your dimension table so you can create a real measure.

Depending on your situation you could also add a new measure group.


Archival strategy for a table with 25 million records in SQL Server 2008 R2 Enterprise

Please see this answer: SQL Server database design for "archived but available" data

As Remus comments, you are probably barking up the wrong tree here. 25M rows is a very small table and the INSERT really shouldn't take long. Perhaps it is worth investigating this first..


ORA-00959: tablespace does not exist but I can see it in select

While creating a common user, any default tablespace, temporary tablespace, or profile specified using the following clauses must exist in all the containers belonging to the CDB:

  DEFAULT TABLESPACE
  TEMPORARY TABLESPACE
  QUOTA
  PROFILE

More

According to the output you have updated it appears that the PAVEL_DATA tablespace doesn't exist in container number 4.

Moreover, the CREATE USER or ALTER USER command with DEFAULT TABLESPACE or DEFAULT TEMPORARY TABLESPACE clause may succeed if the default/temporary tablespace specified don't exist on the PDBS which are currently closed. But you may get errors when you try to open or try to connect these newly opened PDBs using the common user you have just created or altered.

Demonstration:

SQL> create user c##_sales_hr identified by password default tablespace tbs_sales;
create user c##_sales_hr identified by password default tablespace tbs_sales
*
ERROR at line 1:
ORA-65048: error encountered when processing the current DDL statement in
pluggable database ORAPDB1
ORA-00959: tablespace 'TBS_SALES' does not exist

SQL> select tablespace_name, con_id from cdb_tablespaces;

TABLESPACE_NAME                CON_ID
------------------------------ ----------
SYSTEM                         1
SYSAUX                         1
UNDOTBS1                       1
TEMP                           1
USERS                          1
TBS_SALES                      1
SYSTEM                         4
SYSAUX                         4
TEMP                           4
ORAPDB1_TBS1                   4
MGMT_ECM_DEPOT_TS              4

11 rows selected.

SQL> alter session set container=orapdb1;

Session altered.

SQL> create tablespace tbs_sales datafile '+DATA' size 50M;

Tablespace created.

SQL> conn / as sysdba
Connected.
SQL> create user c##_sales_hr identified by password default tablespace tbs_sales;

User created.


This query could answer your question: median value and group by

            SELECT tag, AVG(val) as median
            FROM
            (
              SELECT tag, val,
                  (SELECT count(*) FROM median t2 WHERE t2.tag = t3.tag) as ct,
                  seq,
                  (SELECT count(*) FROM median t2 WHERE t2.tag < t3.tag) as delta
                FROM (SELECT tag, val, @rownum := @rownum + 1 as seq
                      FROM (SELECT * FROM median ORDER BY tag, val) t1
                      ORDER BY tag, seq
                    ) t3 CROSS JOIN (SELECT @rownum := 0) x
                HAVING (ct%2 = 0 and seq-delta between floor((ct+1)/2) and floor((ct+1)/2) +1)
                  or (ct%2 <> 0 and seq-delta = (ct+1)/2)
            ) T
            GROUP BY tag
            ORDER BY tag;

I tried it on this dataset (mainly from here):

            +------+------+
            | tag  | val  |
            +------+------+
            |    1 |    3 |
            |    1 |   13 |

... (see explanation below)

            |    3 |   12 |
            |    3 |   43 |
            |    3 |   15 |
            +------+------+

and the result was:

            +------+---------+
            | tag  | median  |
            +------+---------+
            |    1 | 23.0000 |
            |    2 | 22.0000 |
            |    3 | 15.0000 |
            +------+---------+

Explanation

Inner subqueries will be computed first: sequence is (1)(2)(3)(4).

-- (4) compute the average (of 2 lines or 1 line)

    SELECT tag, AVG(val) as median
      FROM
      (

-- (3) get lines to compute the median value

        SELECT tag, val,
           (SELECT count(*) FROM median t2                    -- +number of lines for the current tag value as ct
              WHERE t2.tag = t3.tag) as ct,
           seq,
           (SELECT count(*) FROM median t2                    -- +number of lines before the current tag value as delta
              WHERE t2.tag < t3.tag) as delta                --     to compute the starting line number of a tag
         FROM (

-- (2) sort dataset by tag and sequence

                SELECT tag, val,
                    @rownum := @rownum + 1 as seq       -- +@rownum enable to create a sequence from 0 by 1
              FROM (

-- (1) sort dataset by tag and value

                    SELECT * FROM median
                    ORDER BY tag, val) t1

-- (2) continue here

              ORDER BY tag, seq
            ) t3 CROSS JOIN (SELECT @rownum := 0) x            -- +use to set @rownum to 0 (no data)

-- (3) continue here

         HAVING (ct%2 = 0                                      -- +when ct is even, select the two lines around the middle
                  and seq-delta between floor((ct+1)/2)
                                and floor((ct+1)/2) +1)
           or (ct%2 <> 0                                       -- +when ct is odd, select the one line in the middle
                  and seq-delta = (floor(ct+1)/2))
      ) T

-- (4) continue here

      GROUP BY tag
      ORDER BY tag;

Dataset:

        after (1)     after (2)           processing (3)
    +------+------+
    | tag  | val  |  ct  delta  seq       seq-delta
    +------+------+
    |    1 |    3 |  15    0     1        1         ct : odd ct%2 <> 0
    |    1 |    5 |  15    0     2        2         floor((ct+1)/2) : 8
    |    1 |    7 |  15    0     3        3
    |    1 |   12 |  15    0     4        4
    |    1 |   13 |  15    0     5        5
    |    1 |   14 |  15    0     6        6
    |    1 |   21 |  15    0     7        7
    |    1 |   23 |  15    0     8        8 ---> keep this line
    |    1 |   23 |  15    0     9        9
    |    1 |   23 |  15    0     10       10
    |    1 |   23 |  15    0     11       11
    |    1 |   29 |  15    0     12       12
    |    1 |   39 |  15    0     13       13
    |    1 |   40 |  15    0     14       14
    |    1 |   56 |  15    0     15       15

    |    2 |    3 |  14    15    16        1         ct : even (ct%2 = 0  )
    |    2 |    5 |  14    15    17        2         floor((ct+1)/2) : 7
    |    2 |    7 |  14    15    18        3         floor((ct+1)/2)+1 : 8
    |    2 |   12 |  14    15    19        4
    |    2 |   13 |  14    15    20        5
    |    2 |   14 |  14    15    21        6
    |    2 |   21 |  14    15    22        7 ---> keep this line
    |    2 |   23 |  14    15    23        8 ---> keep this line
    |    2 |   23 |  14    15    24        9
    |    2 |   23 |  14    15    25        10
    |    2 |   23 |  14    15    26        11
    |    2 |   29 |  14    15    27        12
    |    2 |   40 |  14    15    28        13
    |    2 |   56 |  14    15    29        14

    |    3 |   12 |  3     29    30        1                  ct : odd ct%2 <> 0
    |    3 |   15 |  3     29    31        2 ---> keep        floor((ct+1)/2) : 2
    |    3 |   43 |  3     29    32        3
    +------+------+

Dataset after (3)

    +------+------+------+------+-------+
    | tag  | val  | ct   | seq  | delta |
    +------+------+------+------+-------+
    |    1 |   23 |   15 |    8 |     0 |
    |    2 |   21 |   14 |   22 |    15 |
    |    2 |   23 |   14 |   23 |    15 |
    |    3 |   15 |    3 |   31 |    29 |
    +------+------+------+------+-------+

Outer query will compute the avg(val) group by tag value.

Hope this helps.

But what about median computing when there are null values? See EDIT2 below

Alternative: using a function

    DELIMITER //
    CREATE FUNCTION median(pTag int)
        RETURNS real
           READS SQL DATA
           DETERMINISTIC
           BEGIN
              DECLARE r real; -- result
    SELECT AVG(val) INTO r
    FROM
    (
      SELECT val,
           (SELECT count(*) FROM median WHERE tag = pTag) as ct,
           seq
        FROM (SELECT val, @rownum := @rownum + 1 as seq
              FROM (SELECT * FROM median WHERE tag = pTag ORDER BY val ) t1
              ORDER BY seq
            ) t3
            CROSS JOIN (SELECT @rownum := 0) x
        HAVING (ct%2 = 0 and seq between floor((ct+1)/2) and floor((ct+1)/2) +1)
          or (ct%2 <> 0 and seq = (ct+1)/2)
    ) T;
    return r;
    END//
    DELIMITER ;

But the function will be called for each row:

SELECT tag, median(tag) FROM median; -- my test table is 'median' too...

This query will be "better":

select tag, median(tag)
  from (select distinct tag from median) t;

That's all I can do! Hope it helps!

EDIT2 : about null values in data (column val in the example)

null values show be omited from the source data using a WHERE clause : WHERE val IS NOT NULL, in both 2 subqueriés that count lines and the subquery that gets data.

EDIT3 (LAST EDIT) : change the initialisation of the @rownum position

It should by put at the deepest level : so that it declared the soonest in the excution of the query.

DELIMITER //
CREATE FUNCTION median(pTag int)
    RETURNS real
       READS SQL DATA
       DETERMINISTIC
       BEGIN
          DECLARE r real; -- result
SELECT AVG(val) INTO r
FROM
(
  SELECT val,
       (SELECT count(*) FROM median WHERE tag = pTag and val is not null)     as ct,
       seq
    FROM (SELECT val, @rownum := @rownum + 1 as seq
          FROM (SELECT * FROM median
                       CROSS JOIN (SELECT @rownum := 0) x -- INIT @rownum here
             WHERE tag = pTag and val is not null ORDER BY val
          ) t1
          ORDER BY seq
       ) t3
    HAVING (ct%2 = 0 and seq between floor((ct+1)/2) and floor((ct+1)/2) +1)
      or (ct%2 <> 0 and seq = (ct+1)/2)
) T;
return r;
END//
DELIMITER ;

That is the same for the query.

Test with 2 data sets more :

|    4 | NULL |
|    4 |   10 |
|    4 |   15 |
|    4 |   20 |
|    5 | NULL |
|    5 | NULL |
|    5 | NULL |
+------+------+

39 rows in set (0.00 sec)

+------+--------------+
| tag  | median2(tag) |
+------+--------------+
|    1 |           23 |
|    2 |           22 |
|    3 |           15 |
|    4 |           15 |
|    5 |         NULL |
+------+--------------+
5 rows in set (0.08 sec)


Why is OS authentication considered poor security for Oracle databases?

Consider the following scenario:

There is a Unix user named gaius on the Oracle server with external authentication, so in Oracle there is a corresponding user called ops$gaius. When logged into a shell, I can also log straight into my Oracle schema, and my cron jobs don't need a password embedded in script either.
Remote OS authentication is permitted, on the assumption that the LAN is 100% secure and the clients can be trusted (same as rlogin/rsh used to be normally allowed)
An attacker gets his or her laptop onto the LAN by whatever means, knows that I work there, and creates a local user on their laptop called gaius and runs SQL*Plus as that user
Oracle sees (i.e. OSUSER in V$SESSION) is gaius and logs that remote user in as ops$gaius

That's not only laughably easy to spoof, but putting on my cynic's hat, Oracle can't make any more money selling you their fancy single sign-on product... Which by the way does fulfill all the points you raise as advantages of OS-level auth. Two passwords better than one is entirely spurious; most people will set them to be the same anyway (there's no mechanism in Oracle to prevent this).

The general principle is that it is extremely difficult to defend in software when an attacker has physical access. And never trust the client.


Postgresql Query getting slow execution time

At the key node, it is expecting 83 rows but finding 46,547.  Unless your statistics are way out of date, that means there is a correlation between inventory_id and location_id.  You could try to CREATE STATISTICS on dependencies between those two columns, but it not really clear what it would do instead if it had correct estimates.  Maybe a hash join to the order_items table, rather than poorly cached nested loop, but that would depend on how big the table is.

A more direct route to address this would be to create an index on (inventory_id, location_id, id DESC).  This would allow it to jump in the index to the correct values of inventory_id and location_id, and walk it in "id" order until it collects 10 rows.  The "DESC" is probably not necessary, you could try without it and see if it works.  You could also add "reference_type" to the index just before "id", but it would only be a minor improvement and might also make the index less general for application to other queries.


Perhaps unsurprisingly, you're not the first person to be intrigued by the fancy, fancy pictures in that post. If you look at the comments section below, you can see user @dangerousdave asks...

  nice diagram, what tool did you use?

...to which the poster @joel-brown replies...

  ... I use Visio with custom smart shapes that I built to use the James Martin ERD visual convention and a custom line pattern that gives it a hand-drawn look.

You might try reaching out to Joel at his public contact info and asking if he's willing to share the extensions he used (although the original post is around ~6 years old at the time of this writing so don't be too disappointed if he's using something else by now).


SQL Query taking long to execute especially with offset and fetch for pagination

Using OFFSET...FETCH likely introduces a row goal (in the same way that TOP does so - see Setting and Identifying Row Goals in Execution Plans by Paul White for more information on that subject).  This is why you see such a difference without that part of the query - the lack of a row goal changes the plan entirely.

The row goal leads to SQL Server choosing the series of nested loops joins in the execution plan you shared.  This would normally be fine, but my guess it that the joins are more restrictive than the estimates indicate, and thus it takes a lot longer than expected to get the 10 rows requested.

You can test this theory by adding OPTION (QUERYTRACEON 4138) to the end of the query and running it again.

As far as a proper solution - as Dan Guzman suggested, you could try creating a narrow index on fsams.CTB_SUBSIDY_CARD_HEADER:

CREATE NONCLUSTERED INDEX IX_PaymentLotNo
ON FSAMS.CTB_SUBSIDY_CARD_HEADER (PaymentLotNo)
INCLUDE (OwnerName, TECHID, Id, TotalSubsidy)

Also, don't use SELECT * for your performance-related testing, as it's definitely going to change the final plan that you get (as compared with the column list of the actual query).

There is an implicit conversion going on that could be throwing the query's estimates off - LotNumber is stored as a string in the CTB_SUBSIDY_CARD_HEADER table, which is being converted to an int before being joined to PaymentLotNo in CTB_SUBSIDY_APPLICATION_HISTORY.  You should ALTER these columns to have matching data types if possible.

Side note: you're running SQL Server 2014 RTM, which is out of support.  You should upgrade to the latest service pack.  Also, consider enabling TF 4199 at that time, as the issue you're experiencing could be related to an optimizer bug that has since been corrected.


Try looking here!

  The TIMESTAMP data type is used for values that contain both date and
  time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to
  '2038-01-19 03:14:07' UTC.

You are exceeding the permitted ranges! Also, should activity_time be a duration of some sort? Maybe a begin_timestamp and end_timestamp might be an idea?

You should prefer TIMESTAMP to DATETIME, from here (Date and Time Type Storage Requirements), a timestamp without fractional seconds (i.e. most of them)  requires only 4 bytes (as opposed to 5, but hey!). Also, 99% of dates that we deal with on a day to day basis will be between 1970 and 2038.

This part of the manual should help with initialisations.

Following the small oversight so kindly pointed out by @ypercubeᵀᴹ I had a look at this issue - i.e. of '0000-00-00 00:00:00' being a special "zero" value.

The fruits of my efforts are available here.

DDL:

CREATE TABLE `user_login_info` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_id` int(11) NOT NULL,
  `event_type` int(11) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  -- `activity_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',  -- FAILS
  `test_time1` TIMESTAMP NULL DEFAULT NULL,  -- WORKS
  -- `test_time2` TIMESTAMP DEFAULT '0000-00-00 00:00:00', -- FAILS
  -- `test_time3` TIMESTAMP NOT NULL DEFAULT 0,  -- FAILS
  -- `test_time35` TIMESTAMP NULL DEFAULT 0,
  `test_time4` TIMESTAMP NULL DEFAULT NULL,
  `test_time5` TIMESTAMP NOT NULL DEFAULT '1970-01-01, 00:00:01', -- FAILS with time = 00:00:00
  -- `test_dtime6` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',  -- FAILS
  `test_time6` TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
  -- `test_time7` TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00',
  -- 'test_time8` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',  -- FAILS
  PRIMARY KEY (`id`),
  INDEX my_index (created_at) -- if the TABLE is CREATEd, the INDEX is!
) ENGINE=MyISAM;

It appears to me that there is a problem/bug with MySQL - in that it appears to confuse this special "zero" with the NULL value! It won't work with DATETIME either! I looked at the documentation again and can't appear to find why many of the failing constructs in my DDL won't work. There was a time when MySQL didn't work with multiple TIMESTAMPs - maybe this is relic of that epoch? I even tried SQLFiddle - which will build the schema but then fails - not sure why! I hope this clarifies matters unless @ypercubeᵀᴹ has a better suggestion! I found this helpful.


Can we have OLAP cubes with same OBJECT NAME and ID at different database?

Every object has a Name and ID property that must be unique within the scope of the parent collection. For example, two dimensions can have same name as long as each one resides in a different database.

A database is a scope. A cube is an object. Two cubes can have same name or ID as long as they belong to different databases.

Although you can specify it manually, the IDis typically auto-generated when the object is created. You should never change the ID once you have begun building a model. All object references throughout a model are based on the ID. Thus, changing an ID can easily result in model corruption.

DataSource and DataSourceView objects have notable exceptions to naming conventions. DataSource ID can be set to a single dot (.), which is not unique, as a reference to the current database. A second exception is DataSourceView, which adheres to the naming conventions defined forDataSet objects in the .NET Framework, where the Name is used as the identifier.

Naming Conventions


Best way to filter result set by secondary key

You should just use

row_number() over (partition by SecondaryKey
                   order by DateCreated desc)

this will partition the data by SecondaryKey and then you will get the most recent data (ordered by DateCreated desc)

with CTE as (
 select [PrimaryKey], SecondaryKey, [Data],DateCreated,
        rn = row_number() over (partition by SecondaryKey order by DateCreated desc)
  from dbo.Entry
  )
 select [PrimaryKey], SecondaryKey, [Data],DateCreated
 From CTE where rn = 1 --rn = 1 is most recent data since we are ordering by desc

see SQLFiddle


Why is my database still fragmented after I reorganized and rebuilt it, and updated the statistics in SQL Server?

Bottom line: fragmentation is irrelevant for small indexes. Never bother about it for indexes less than some 1000 pages, or perhaps we should say 10000 nowadays. If you remember cassette tapes, we always have some noise in the background (my analogy, perhaps works better in Swedish).
Tip1: don't use single quotes for column names in SELECT list. It divert from ANSI SQL and it is "weird". :-)
Tip2: you don't have to join all those tables. Use meta-data functions instead.
Tip3: Heaps don't have fragmentation in the same way as indexes do. So either filter out the heaps or learn about forwarding pointers/forwarded records and handle them separately from your indexes. This might be useful, for instance: https://karaszi.com/rebuild-all-fragmented-heaps.
Tip4: Jumping back and forth on disk isn't as costly as it used to be. Assuming you are on faster storage than old-school spinning disks, that is. I.e., that type of fragmentation might not hurt you much. There's still the aspect of read-ahead, though. See for instance this: http://sqlblog.karaszi.com/fragmentation-the-final-installment/.
Tip5: Use something more reasonable to filter on the fragmentation level, like > 5% instead of > 0%.
Here's a query, simplified and with my recommendations above. But, again, perhaps you shouldn't worry about jumping back and forth in the first place?
SELECT
 OBJECT_SCHEMA_NAME(f.object_id) AS Schema_
,OBJECT_NAME(f.object_id) AS Table_
,i.name AS Index_
,f.avg_fragmentation_in_percent
,f.page_count
,i.type_desc
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS f
  INNER JOIN sys.indexes AS i On f.object_id = i.object_id
WHERE f.avg_fragmentation_in_percent > 5
 AND f.page_count > 1000
 AND i.type_desc NOT IN('HEAP', 'CLUSTERED COLUMNSTORE', 'NONCLUSTERED COLUMNSTORE')
ORDER BY avg_fragmentation_in_percent DESC


How to specify trigger execution order under PostgreSQL?

PostgreSQL executes the triggers in alphabetical order by name. So make sure to use names that get them in the order you want. From the docs

  SQL specifies that multiple triggers should be fired in time-of-creation order. PostgreSQL uses name order, which was judged to be more convenient.

BTW your SQL creates the same trigger twice.


Does Extended Event session without target affects server performance?

The overhead of an active event session with no targets depends on the frequency of events. Costs will be significant enough as to impact performance only under extreme cases.

Below are batch requests/sec measurements with no trace as well as unfiltered  sql_batch_completed and sql_statement completed traces. The results of these tests show about a 8% decrease on throughput with the sql_batch_completed trace while the sql_statement_completed trace reduced throughput by a considerable 63%.

Given that it's quite easy to start an existing trace with a right-click in SSMS Object Explorer, my recommendation is to run traces that don't have targets only when you want to actively view live data, even though the impact will be insignificant unless high-frequency events are captured.

Below are the scripts I used for the test.

--script to generate sql_batch_completed and sql_statement completed events
SET NOCOUNT ON;
DECLARE @IterationCount int = 0;
WHILE @IterationCount < 1000
BEGIN
    SET @IterationCount += 1;
END;
GO 1000000

Script to run traces during test execution and measure throughput:

DECLARE @BeginCounterValue int;

--measure throughput with no trace
SELECT @BeginCounterValue = cntr_value
FROM sys.dm_os_performance_counters
WHERE
    object_name = N'SQLServer:SQL Statistics'
    AND counter_name = N'Batch Requests/sec';
WAITFOR DELAY '00:01:00';
SELECT 'no trace' AS TraceName, (cntr_value - @BeginCounterValue) / 60.0 AS BatchRequestsPerSecond
FROM sys.dm_os_performance_counters
WHERE
    object_name = N'SQLServer:SQL Statistics'
    AND counter_name = N'Batch Requests/sec';

--measure throughput with sql_batch_completed trace
CREATE EVENT SESSION sql_batch_completed ON SERVER
ADD EVENT sqlserver.sql_batch_completed(SET collect_batch_text=(1))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF);
ALTER EVENT SESSION sql_batch_completed ON SERVER STATE=START;
SELECT @BeginCounterValue = cntr_value
FROM sys.dm_os_performance_counters
WHERE
    object_name = N'SQLServer:SQL Statistics'
    AND counter_name = N'Batch Requests/sec';
WAITFOR DELAY '00:01:00';
SELECT 'sql_batch_completed trace' AS TraceName, (cntr_value - @BeginCounterValue) / 60.0 AS BatchRequestsPerSecond
FROM sys.dm_os_performance_counters
WHERE
    object_name = N'SQLServer:SQL Statistics'
    AND counter_name = N'Batch Requests/sec';

--measure throughput with sql_statement_completed trace
DROP EVENT SESSION sql_batch_completed ON SERVER;
CREATE EVENT SESSION sql_statement_completed ON SERVER
ADD EVENT sqlserver.sql_statement_completed(SET collect_statement=(1))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF);
ALTER EVENT SESSION sql_statement_completed ON SERVER STATE=START;
SELECT @BeginCounterValue = cntr_value
FROM sys.dm_os_performance_counters
WHERE
    object_name = N'SQLServer:SQL Statistics'
    AND counter_name = N'Batch Requests/sec';
WAITFOR DELAY '00:01:00';
SELECT 'sql_statement_completed trace' AS TraceName, (cntr_value - @BeginCounterValue) / 60.0 AS BatchRequestsPerSecond
FROM sys.dm_os_performance_counters
WHERE
    object_name = N'SQLServer:SQL Statistics'
    AND counter_name = N'Batch Requests/sec';
DROP EVENT SESSION sql_statement_completed ON SERVER;
GO


snychronize multiple databases using database replication

The best match for occasionally disconnected clients and bidirectional replication is merge replication.

Other flavours of replication support bidirectional replication, but transactional replication with updateable subscriptions is deprecated and peer-to-peer transactional replication is more suitable for high-speed low-latency links rather than remote sites.

Merge replication also offers the ability to synchronize over an HTTP connection, using an IIS ISAPI filter.

Merge replication allows you to define partitions of data and let each subscriber access a subset of the data. You could have a central database containing all the data and each subsidiary containing only the data that pertains to that subsidiary. This is accomplished by using join filers and/or static filters.

The only downside to merge replication is its performance toll: each table that you decide to publish has to be monitored for changes by a system-generated trigger and every published table (article) needs to contain a uniqueidentifier column (it gets added automatically in case it's missing).


Before attempting to shrink the logfile, you need to understand if it's a problem or not. It probably isn't.
The log is that size most likely because it needed to grow to that size, meaning that if you shrink it, it will likely grow back to that size in the future - and that log growth will momentarily slow whatever transaction is causing it to grow.
With it only being 4GB and in SIMPLE recovery, I would recommend simply leaving the log as it is, and perhaps adjust the auto growth to fixed 1GB chunks.
If you simply must shrink the log, you can do so with DBCC SHRINKFILE, using the TRUNCATEONLY option. Example:
DBCC SHRINKFILE (Database_log_file_name, 1);
This will truncate the file all the way down to the last open transaction which, if the database is idle or mostly idle, will be the whole thing. But first make sure you've set your auto-growth as described above, and you might even want to re-size the log to what you think you need.
But again...4GB on a 100GB database is not a big deal. I'd just let it stay there.


Lost admin user ; Update not allowed on current user in MySQL

I think the answer of the question can help hackers or something.
You can reinstall your MySQL. Before the reinstall, take a backup if you can or copy your data files.
There is an answer to how can do you recover your data files. If you want to guarantee your data healthy, you can install new MySQL and try that.


The issue was that I had the following in my docker-compose.yml:
command: >-
  mysqld
    --character-sets-dir='/usr/share/mariadb/charsets'
    --character-set-client-handshake=FALSE

This was wrong, as the directory was actually /usr/share/mysql/charsets.


Perhaps you want to remove the DROP privilege at the global user level
UPDATE mysql.user SET drop_priv = 'N' WHERE user='...' AND host='...' and db='...';
FLUSH PRIVILEGES;

Something to keep in mind: The DROP privilege encompasses databases, table, and views. Unfortunately, DROP DATABASE and DROP TABLE fall under the same privilege scope.
For example
I create a user called kumar and a database called kumar
mysql> create database kumar;
Query OK, 1 row affected (0.00 sec)

mysql> grant all on kumar.* to kumar@'%';
Query OK, 0 rows affected (0.06 sec)

mysql> use kumar
Database changed
mysql> create table rolando (a int);
Query OK, 0 rows affected (0.08 sec)

I logged into mysql as kumar and dropped the database kumar
mysql> drop database kumar;
Query OK, 1 row affected (0.07 sec)

So, I recreated it as a SUPER user. I also create a database kumar2
mysql> create database kumar;
Query OK, 1 row affected (0.00 sec)

mysql> use kumar
Database changed
mysql> create table rolando (a int);
Query OK, 0 rows affected (0.07 sec)

mysql> create database kumar2;
Query OK, 1 row affected (0.00 sec)

mysql>

I tried to drop kumar2
mysql> show grants for kumar@'%';
+--------------------------------------------------+
| Grants for kumar@%                               |
+--------------------------------------------------+
| GRANT USAGE ON *.* TO 'kumar'@'%'                |
| GRANT ALL PRIVILEGES ON `kumar`.* TO 'kumar'@'%' |
+--------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select user(),current_user();
+-----------------+----------------+
| user()          | current_user() |
+-----------------+----------------+
| kumar@localhost | kumar@%        |
+-----------------+----------------+
1 row in set (0.00 sec)

mysql> drop database kumar2;
ERROR 1044 (42000): Access denied for user 'kumar'@'%' to database 'kumar2'
mysql>

Thus, kumar@'%' can only drop the kumar database. kumar@'%' cannot drop any other database.
CONCLUSION
The user kumar@'%' can drop the following:

All Views in the kumar database
All Tables in the kumar database
The kumar database itself

You can either allow all three or restrict all three fro this user's access to the kumar database.


How to query a table efficiently without affecting live transactions

Typically, you don't want to be querying tables with high transaction volume at all; that's what a data warehouse is for.

But if you must, you can use SNAPSHOT ISOLATION. Which will query the table as of the last committed transaction before it started. This might give you dirty reads, as it may be out of date by the time the query finished running, but as you're looking at stuff 15-30 minutes in the past it should be ok.

In MySQL (assuming you're using innoDB), you can use START TRANSACTION WITH CONSISTENT SNAPSHOT; as there is no global setting for SNAPSHOT ISLOATION.

You can also look here for more info: https://dev.mysql.com/doc/refman/5.7/en/innodb-consistent-read.html


Update by iterating table in batches faster than whole table in PostgreSQL

I'll try my best to answer your question in brief, but since I'm not really aware of your level of comfort with PostgreSQL, and I don't have a lot of time to go into an in-depth explanation anyways, I'll keep the answers simple, and you can ask for clarification if you'd like more info.

1) Why is it faster in batches?

Due to the structure of PostgreSQL's write ahead log, the amount of shared buffer space in RAM, and the attempt to perform the entire UPDATE in a single transaction, my guess is that you simply don't have enough computing resources to efficiently handle the update to nearly a million records in a single transaction.

PostgreSQL has a well-built concurrency control system, essentially meaning that it has to keep the old copies of your pre-UPDATE rows available during your UPDATE operation.  This is so that, in case another client tries to access these rows while you're updating, in case the update fails, or in case you cancel the update, you don't lose the old information.

If you perform a large enough UPDATE, PostgreSQL will load pages into memory and modify them, but will eventually run out of memory to work with, so it is forced to immediately copy these pages temporarily to disk if it wants to be able to load further pages and continue the transaction.

Rather than being able to amortize the disk writes over a period of time, you've just forced your database into a bottleneck.

2) Scripting the updates

You absolutely can script the updates, by creating a function in PL/pgSQL.  There's a lot to learn about PL/pgSQL, including a lot I probably don't know, but generally speaking, you could do something like this

CREATE OR REPLACE FUNCTION mini_batch_update()
  RETURNS void AS
$BODY$
DECLARE

id_val integer;

BEGIN

  FOR id_val IN 0..2000
  LOOP

  **UPDATE GOES HERE**
  WHERE id > (500 * id_val) AND id <= 500 * (id_val + 1);

  END LOOP;

END
$BODY$
LANGUAGE plpgsql;

I didn't take a lot of time to make this batch function in tip-top form; what I mean is that I simply hard-coded several of the numerical values for simplicity's sake.  In your case, you may want to get more detailed and include: 1) Something that checks for the maximum id value so that you set your bounds appropriately, and 2) even though I  hard-coded batches of 500, you could easily make this a function input parameter.

Sorry I don't have time to test this or make sure it really works well.  Good luck!


How to check if integer array overlaps with int4range?

You can use the ANY and ALL array element quantifiers, together with the @> contains element range operator.

If you want to know if ANY element of the array lies within your range, which is what, according to your comments, is what I interpret you want, you can use:

SELECT
    int4range(5, 8, '[]') @> ANY(ARRAY[1, 3, 5, 7]) AS some_are_in

(in this case, this will return TRUE, because both 5 and 7 are part of the range)

This nearly translates your request (but reversing it): Does the range contain ANY of the elements of the array?

If you would like to know that they're all in, you'd use ALL instead of ANY:

SELECT
    int4range(5, 8, '[]') @> ALL(ARRAY[1, 3, 5, 7]) AS all_are_in

(in this case, this will return false, because 1 and 3 are not part of the range)

You can find these and some other examples at dbfiddle here


Is there a difference between granting SELECT to a user and adding them to the db_datareader role?

Rick Byham has a WIKI post showing the fixed server and fixed database roles and how they map.  You can look here: http://social.technet.microsoft.com/wiki/contents/articles/database-engine-fixed-server-and-fixed-database-roles.aspx

The chart shows that db_datareader role is identical to GRANT SELECT ON [database].  So it is still fine to use, but the recommendation is to move away from those roles to the more granular commands. Some of the other fixed database roles are less clearly defined for most people.  Using the explicit commands results in greater clarity when reporting rights.

Obviously you know how to grant finer grained permissions. I am trying to break loose from the old roles whenever possible, but db_owner (for example) is a hard habit to break.


Executing try catch block in mysql stored procedure

I think you're mixing up Java (other language?) with MySQL's stored procedure language. Look here and here. Check out this by Guy Harrison, the guy who wrote this book.


Insert performance with Geography column

After doing some research, including looking at this question, it appears it isn't directly possible to efficiently bulk load any of the CLR-based types, including geography. 1

You said that adding just the geography column added a significant amount of time to the load process -- this may, in fact, be entirely reasonable, depending on the amount of data that's going into that column.

The most concerning thing is that it's taking so long to load the data without the geography column involved.

When doing large data loads, maximizing throughput is the most important part of optimizing the process, particularly if the client application and database are separated by a network connection. (By the way, I'm going to assume that using a client application to load the data is the best architectural decision for the problem here.)

To maximize throughput means minimizing other types of overhead, such as data preparation on the client, and network roundtrips between servers.

Based on the comments, you have some type of loop happening that reads a row from the source and writes it to the target immediately. Something like this (pseudo-code):

while (more rows to read from source)
{
    read one row from source
    write one row to target
}

This is inefficient primarily because it maximizes network roundtrips and small chunks of I/O on the target, which are both expensive in aggregate.

Probably the easiest way to modify your code to fix this issue is to introduce a delayed-write scheme, where a chunk of rows are read from the source, and flushed to the target all at once. Something like this:

while (more rows to read from source)
{
    read 1..batchSize rows from source
    write 1..batchSize rows to target
}

Even using INSERT ... VALUES with multiple rows at a time (note: there is a 1000-row limit per statement) will significantly improve performance. The test harness I constructed saw a 43x improvement between a batch size of 1 and a batch size of 1,000. 2

A note about parameterization. While this is a best-practice when it comes to application security, for a batch loading process, parameterized statements can cripple performance because of the associated processing time. If you start batching things together, it's still possible to parameterize the whole statement, but the performance gets worse and worse the more parameters you use (there's a limit of something like 2,000 parameters anyway). Assuming the data source values are "safe," I would actually recommend doing your own escaping (replace ' with '') for the string columns and concatenate all values directly into the SQL text as literals. By the way, if you're using .NET, make sure you use a StringBuilder to concatenate strings.

About how to move the geography column around efficiently. Simply batching the INSERTs may be enough. If not, it may be advantageous to attempt loading (or bulk loading) the text definition of the column (I'm assuming you're given a text list of points or something from the data sources) along with the rest of the data into a staging table, then populate the target by doing an INSERT/SELECT to convert the text definition into the CLR type. (Note: I haven't tested this.)

Either way, if you haven't reached network saturation after doing that, and depending on how fast you can read from the data source vs. write to the target, the code could be refactored into a producer/consumer architecture where reads and writes happen asynchronously, and possibly multithreaded on either or both sides. The details of how to do this are out of scope for this site, but I figured it should be mentioned. I don't think you'll have to go to this length to get acceptable performance.

Finally, for completeness of this answer, in terms of indexes, for a one-time load, it's advantageous to create nonclustered indexes after all the data has been loaded into the table. This reduces the number of internal index inserts, much in the same way that batching INSERT statements reduces network roundtrips. It's probably best to create the clustered index, however, before loading, so there isn't a need to rewrite the whole table to give it order (assuming the clustering key is ever-increasing).

1 Well, probably. If you extract Microsoft.SqlServer.Types from the resource database and reference it in an assembly, it should be possible; however, this is wildly unsupported and will probably break the process horribly if Microsoft updates it between versions.

2 This was a single-threaded test inserting constant values of integers and strings, not selecting from a data source; 1,500,000 rows at 201 bytes/row; using INSERT ... VALUES took 771,971ms for a batch size of 1, and 18,120ms for a batch size of 1,000. I also tested the same data with SqlBulkCopy for comparison, and it took 16,576ms for a batch size of 1,000, and 13,829ms for a batch size of 10,000 (this batch size saturated throughput). You can download the source from here -- add the program to a console application; I used .NET 4.0.


Access most certainly supports a network and multiuser operation right out of the box. You don't have to change your code or really do anything special.

However to keep in mind while Access is tremendously easy to use on a network, and many people often utilize access with multiple users on a network, it has its limitations.

There are many issues to consider, but the basic concept is that your network has to be very solid, I can't say the network has to be above average, but Access as a product in a network environment is quite sensitive to poor connections.

So if you're just talking a couple computers on the Office Network and you share a folder on one of the computers or your server, then access can work quite well on this environment.  However if your network is a wide area or has users connecting over the Internet, then such setups tend to be quite problematic.

As a general rule, it's recommended that you split your application into two parts.  You place the program part on each computer, and this task is not require any extra programming skills on your part, but it does help enormously in terms of reliability.

I explain this concept of splitting and how you run access in a multi user environment in the following article of mine:

http://www.kallal.ca/Articles/split/index.htm

And a primer on the different kinds of networks, and some of the obstacles as you may encounter are outlined in the following article of mine:

http://www.kallal.ca//Wan/Wans.html


The size of the nvarchar(255) columns is 510 bytes each. Even two of them will cause a warning and potential issues with insert/update operations.

What you want to do is find a combination of one (or less) of the nvarchar columns plus the int and datetime columns that will give you the most unique values.  Then index on that combination including the other columns in order to create a covering index.

If you need more than one of the nvarchar(255) columns actually indexed in order to create a highly selective index (lots of unique values) then you are down to two options.

If you are certain that you will never have more than ~445 characters (remember these are unicode so there are 2 bytes per character and you need room for the datetime and int columns) in the combination of the 3 nvarchar(255) columns then you can create the index and keep your fingers crossed that you aren't wrong.

If you can't be sure they won't go over that size (and if you can you should think about shrinking the size of the columns) then your best bet is to do what @MichaelJSwart suggested in the comments above and create a hashed column to index on.  The biggest down side to this option is that it will require changing your code.  On an up note while you are creating your hash column(s) and changing your code you can work in an index on the nvarchar(max) column (by hashing it).


You can convert the row to a JSON value and then use a sub-select:

select t.id, x.roles
from the_table t
  cross join lateral (
     select array_agg(col) as roles
     from jsonb_each_text(to_jsonb(t)) x(col,val)
     where x.val::boolean
       and x.col like 'role%'
  ) x


How do I change SQL Server 2012 Availability Group DNS subdomain?

The Availability Group Listener name is a Computer Name Object (CNO) in Active Directory, not just a DNS entry:

http://technet.microsoft.com/en-us/library/ff367878(v=exchg.150).aspx

Its location in AD determines where it gets registered. If you want to create a different one somewhere else, you can create a DNS CNAME (alias) pointing to the real Availability Group Listener name:

http://technet.microsoft.com/en-us/library/ff625726(v=ws.10).aspx


Measuring latency between availability group nodes in real-time

You can technically do it using XEvents but you have to build your own tool to stream the data in near to realtime to your dashboard .. this involves data extraction and streaming.

Workflow will be as below :

Collect AG info:

 USE tempdb
                  IF OBJECT_ID('AGInfo') IS NOT NULL
                      BEGIN
                        DROP TABLE AGInfo
                   END
                  IF OBJECT_ID('LatencyCollectionStatus') IS NOT NULL
                      BEGIN
                        DROP TABLE LatencyCollectionStatus
                      END
                   CREATE TABLE LatencyCollectionStatus(
                        [collection_status] [NVARCHAR](60)  NULL,
                        [start_timestamp] [DATETIMEOFFSET] NULL,
                        [startutc_timestamp] [DATETIMEOFFSET] NULL
                    )
                  INSERT INTO LatencyCollectionStatus(collection_status, start_timestamp, startutc_timestamp) values ('Started', GETDATE(), GETUTCDATE())
                  SELECT
                  AGC.name as agname
                  , RCS.replica_server_name as replica_name
                  , ARS.role_desc as agrole
                  INTO AGInfo
                  FROM
                      sys.availability_groups_cluster AS AGC
                      INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS RCS
                      ON
                      RCS.group_id = AGC.group_id
                      INNER JOIN sys.dm_hadr_availability_replica_states AS ARS
                      ON
                      ARS.replica_id = RCS.replica_id
                      where AGC.name =  N'YOUR AG NAME '-- change here !!

Create XE Session as below :

IF EXISTS (select * from sys.server_event_sessions
                WHERE name = N'AlwaysOn_Data_Movement_Tracing')
                    BEGIN
                    DROP EVENT SESSION [AlwaysOn_Data_Movement_Tracing] ON SERVER
                    END
                CREATE EVENT SESSION [AlwaysOn_Data_Movement_Tracing] ON SERVER ADD EVENT sqlserver.hadr_apply_log_block,
ADD EVENT sqlserver.hadr_capture_log_block,
ADD EVENT sqlserver.hadr_database_flow_control_action,
ADD EVENT sqlserver.hadr_db_commit_mgr_harden,
ADD EVENT sqlserver.hadr_log_block_send_complete,
ADD EVENT sqlserver.hadr_send_harden_lsn_message,
ADD EVENT sqlserver.hadr_transport_flow_control_action,
ADD EVENT sqlserver.log_flush_complete,
ADD EVENT sqlserver.log_flush_start,
ADD EVENT sqlserver.recovery_unit_harden_log_timestamps,
ADD EVENT sqlserver.log_block_pushed_to_logpool,
ADD EVENT sqlserver.hadr_transport_receive_log_block_message,
ADD EVENT sqlserver.hadr_receive_harden_lsn_message,
ADD EVENT sqlserver.hadr_log_block_group_commit,
ADD EVENT sqlserver.hadr_log_block_compression,
ADD EVENT sqlserver.hadr_log_block_decompression,
ADD EVENT sqlserver.hadr_lsn_send_complete,
ADD EVENT sqlserver.hadr_capture_filestream_wait,
ADD EVENT sqlserver.hadr_capture_vlfheader ADD TARGET package0.event_file(SET filename=N'AlwaysOn_Data_Movement_Tracing.xel',max_file_size=(25),max_rollover_files=(4))
                WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)

                ALTER EVENT SESSION [AlwaysOn_Data_Movement_Tracing] ON SERVER STATE = START

Extract XEvent Info:

                BEGIN TRANSACTION
                USE tempdb
                IF OBJECT_ID('#EventXml') IS NOT NULL
                BEGIN
                    DROP TABLE #EventXml
                END

                SELECT
                    xe.event_name,
                    CAST(xe.event_data AS XML) AS event_data
                INTO #EventXml
                FROM
                (
                SELECT
                        object_name AS event_name,
                        CAST(event_data AS XML) AS event_data
                    FROM sys.fn_xe_file_target_read_file(
                                'AlwaysOn_Data_Movement_Tracing*.xel',
                                NULL, NULL, NULL)
                    WHERE object_name IN ('hadr_log_block_group_commit',
                                'log_block_pushed_to_logpool',
                                'log_flush_start',
                                'log_flush_complete',
                                'hadr_log_block_compression',
                                'hadr_capture_log_block',
                                'hadr_capture_filestream_wait',
                                'hadr_log_block_send_complete',
                                'hadr_receive_harden_lsn_message',
                                'hadr_db_commit_mgr_harden',
                                'recovery_unit_harden_log_timestamps',
                                'hadr_capture_vlfheader',
                                'hadr_log_block_decompression',
                                'hadr_apply_log_block',
                                'hadr_send_harden_lsn_message',
                                'hadr_log_block_decompression',
                                'hadr_lsn_send_complete',
                                'hadr_transport_receive_log_block_message')

                ) xe

                IF OBJECT_ID('DMReplicaEvents') IS NOT NULL
                BEGIN
                    DROP TABLE DMReplicaEvents
                END

                SET ANSI_NULLS ON

                SET QUOTED_IDENTIFIER ON

                CREATE TABLE DMReplicaEvents(
                    [server_name] [NVARCHAR](128) NULL,
                    [event_name] [NVARCHAR](60) NOT NULL,
                    [log_block_id] [BIGINT] NULL,
                    [database_id] [INT] NULL,
                    [processing_time] [BIGINT] NULL,
                    [start_timestamp] [BIGINT] NULL,
                    [publish_timestamp] [DATETIMEOFFSET] NULL,
                    [log_block_size] [BIGINT] NULL,
                    [target_availability_replica_id] [UNIQUEIDENTIFIER] NULL,
                    [local_availability_replica_id] [UNIQUEIDENTIFIER] NULL,
                    [database_replica_id] [UNIQUEIDENTIFIER] NULL,
                    [mode] [BIGINT] NULL,
                    [availability_group_id] [UNIQUEIDENTIFIER] NULL,
                    [pending_writes]  [BIGINT] NULL
                )

                IF OBJECT_ID('LatencyResults') IS NOT NULL
                BEGIN
                    DROP TABLE LatencyResults
                END
                CREATE TABLE LatencyResults(
                   [event_name] [NVARCHAR](60) NOT NULL,
                   [processing_time] [BIGINT] NULL,
                   [publish_timestamp] [DATETIMEOFFSET] NULL,
                   [server_commit_mode] [NVARCHAR](60) NULL
                )

                INSERT INTO DMReplicaEvents
                SELECT
                    @@SERVERNAME AS server_name,
                    xe.event_name,
                    AoData.value('(data[@name="log_block_id"]/value)[1]', 'BIGINT') AS log_block_id,
                    NULL AS database_id,
                    AoData.value('(data[@name="total_processing_time"]/value)[1]', 'BIGINT') AS processing_time,
                    AoData.value('(data[@name="start_timestamp"]/value)[1]', 'BIGINT') AS start_timestamp,
                    CAST(SUBSTRING(CAST(xe.event_data AS NVARCHAR(MAX)), 75, 24) AS DATETIMEOFFSET) AS publish_timestamp,
                    AoData.value('(data[@name="log_block_size"]/value)[1]', 'BIGINT') AS log_block_size,
                    NULL AS target_availability_replica_id,
                    NULL AS local_availability_replica_id,
                    NULL AS database_replica_id,
                    NULL AS mode,
                    NULL AS availability_group_id,
                    NULL AS pending_writes
                FROM #EventXml AS xe
                CROSS APPLY xe.event_data.nodes('/event')  AS T(AoData)
                WHERE xe.event_name = 'hadr_log_block_send_complete'

                GO

                INSERT INTO DMReplicaEvents
                SELECT
                    @@SERVERNAME AS server_name,
                    xe.event_name,
                    AoData.value('(data[@name="log_block_id"]/value)[1]', 'BIGINT') AS log_block_id,
                    AoData.value('(data[@name="database_id"]/value)[1]', 'INT') AS database_id,
                    AoData.value('(data[@name="duration"]/value)[1]', 'BIGINT') AS processing_time,
                    AoData.value('(data[@name="start_timestamp"]/value)[1]', 'BIGINT') AS start_timestamp,
                    CAST(SUBSTRING(CAST(xe.event_data AS NVARCHAR(MAX)), 65, 24) AS DATETIMEOFFSET) AS publish_timestamp,
                    NULL AS log_block_size,
                    NULL AS target_availability_replica_id,
                    NULL AS local_availability_replica_id,
                    NULL AS database_replica_id,
                    NULL AS mode,
                    NULL AS availability_group_id,
                    AoData.value('(data[@name="pending_writes"]/value)[1]','BIGINT') AS pending_writes
                FROM #EventXml AS xe
                CROSS APPLY xe.event_data.nodes('/event')  AS T(AoData)
                WHERE xe.event_name = 'log_flush_complete'

                GO

                INSERT INTO DMReplicaEvents
                SELECT
                    @@SERVERNAME AS server_name,
                    xe.event_name,
                    NULL AS log_block_id,
                    AoData.value('(data[@name="database_id"]/value)[1]', 'BIGINT') AS database_id,
                    AoData.value('(data[@name="time_to_commit"]/value)[1]', 'BIGINT') AS processing_time,
                    NULL AS start_timestamp,
                    CAST(SUBSTRING(CAST(xe.event_data AS NVARCHAR(MAX)), 72, 24) AS DATETIMEOFFSET) AS publish_timestamp,
                    NULL AS log_block_size,
                    AoData.value('(data[@name="replica_id"]/value)[1]', 'UNIQUEIDENTIFIER') AS target_availability_replica_id,
                    NULL AS local_availability_replica_id,
                    AoData.value('(data[@name="ag_database_id"]/value)[1]', 'UNIQUEIDENTIFIER') AS database_replica_id,
                    NULL AS mode,
                    AoData.value('(data[@name="group_id"]/value)[1]','UNIQUEIDENTIFIER') AS availability_group_id,
                    NULL AS pending_writes
                FROM #EventXml AS xe
                CROSS APPLY xe.event_data.nodes('/event')  AS T(AoData)
                WHERE xe.event_name = 'hadr_db_commit_mgr_harden'

                GO

                INSERT INTO DMReplicaEvents
                SELECT
                    @@SERVERNAME AS server_name,
                    xe.event_name,
                    AoData.value('(data[@name="log_block_id"]/value)[1]', 'BIGINT') AS log_block_id,
                    AoData.value('(data[@name="database_id"]/value)[1]', 'BIGINT') AS database_id,
                    AoData.value('(data[@name="processing_time"]/value)[1]', 'BIGINT') AS processing_time,
                    AoData.value('(data[@name="start_timestamp"]/value)[1]', 'BIGINT') AS start_timestamp,
                    CAST(SUBSTRING(CAST(xe.event_data AS NVARCHAR(MAX)), 82, 24) AS DATETIMEOFFSET) AS publish_timestamp,
                    NULL AS log_block_size,
                    NULL AS target_availability_replica_id,
                    NULL AS local_availability_replica_id,
                    NULL AS database_replica_id,
                    NULL AS mode,
                    NULL AS availability_group_id,
                    NULL AS pending_writes
                FROM #EventXml AS xe
                CROSS APPLY xe.event_data.nodes('/event')  AS T(AoData)
                WHERE xe.event_name = 'recovery_unit_harden_log_timestamps'

                GO

                INSERT INTO DMReplicaEvents
                SELECT
                    @@SERVERNAME AS server_name,
                    xe.event_name,
                    AoData.value('(data[@name="log_block_id"]/value)[1]', 'BIGINT') AS log_block_id,
                    AoData.value('(data[@name="database_id"]/value)[1]', 'BIGINT') AS database_id,
                    AoData.value('(data[@name="processing_time"]/value)[1]', 'BIGINT') AS processing_time,
                    AoData.value('(data[@name="start_timestamp"]/value)[1]', 'BIGINT') AS start_timestamp,
                    CAST(SUBSTRING(CAST(xe.event_data AS NVARCHAR(MAX)), 73, 24) AS DATETIMEOFFSET) AS publish_timestamp,
                    AoData.value('(data[@name="uncompressed_size"]/value)[1]', 'INT') AS log_block_size,
                    AoData.value('(data[@name="availability_replica_id"]/value)[1]', 'UNIQUEIDENTIFIER') AS target_availability_replica_id,
                    NULL AS local_availability_replica_id,
                    NULL AS database_replica_id,
                    NULL AS mode,
                    NULL AS availability_group_id,
                    NULL AS pending_writes
                FROM #EventXml AS xe
                CROSS APPLY xe.event_data.nodes('/event')  AS T(AoData)
                WHERE xe.event_name = 'hadr_log_block_compression'

                GO

                INSERT INTO DMReplicaEvents
                SELECT
                    @@SERVERNAME AS server_name,
                    xe.event_name,
                    AoData.value('(data[@name="log_block_id"]/value)[1]', 'BIGINT') AS log_block_id,
                    AoData.value('(data[@name="database_id"]/value)[1]', 'BIGINT') AS database_id,
                    AoData.value('(data[@name="processing_time"]/value)[1]', 'BIGINT') AS processing_time,
                    AoData.value('(data[@name="start_timestamp"]/value)[1]', 'BIGINT') AS start_timestamp,
                    CAST(SUBSTRING(CAST(xe.event_data AS NVARCHAR(MAX)), 75, 24) AS DATETIMEOFFSET) AS publish_timestamp,
                    AoData.value('(data[@name="uncompressed_size"]/value)[1]', 'BIGINT') AS log_block_size,
                    AoData.value('(data[@name="availability_replica_id"]/value)[1]', 'UNIQUEIDENTIFIER') AS target_availability_replica_id,
                    NULL AS local_availability_replica_id,
                    NULL AS database_replica_id,
                    NULL AS mode,
                    NULL AS availability_group_id,
                    NULL AS pending_writes
                FROM #EventXml AS xe
                CROSS APPLY xe.event_data.nodes('/event')  AS T(AoData)
                WHERE xe.event_name = 'hadr_log_block_decompression'

                INSERT INTO DMReplicaEvents
                SELECT
                    @@SERVERNAME AS server_name,
                    xe.event_name,
                    AoData.value('(data[@name="log_block_id"]/value)[1]', 'BIGINT') AS log_block_id,
                    NULL AS database_id,
                    AoData.value('(data[@name="total_sending_time"]/value)[1]', 'BIGINT') AS processing_time,
                    AoData.value('(data[@name="start_timestamp"]/value)[1]', 'BIGINT') AS start_timestamp,
                    CAST(SUBSTRING(CAST(xe.event_data AS NVARCHAR(MAX)), 69, 24) AS DATETIMEOFFSET) AS publish_timestamp,
                    NULL AS log_block_size,
                    NULL AS target_availability_replica_id,
                    NULL AS local_availability_replica_id,
                    NULL AS database_replica_id,
                    NULL AS mode,
                    NULL AS availability_group_id,
                    NULL AS pending_writes
                FROM #EventXml AS xe
                CROSS APPLY xe.event_data.nodes('/event')  AS T(AoData)
                WHERE xe.event_name = 'hadr_lsn_send_complete'

                INSERT INTO DMReplicaEvents
                SELECT
                    @@SERVERNAME AS server_name,
                    xe.event_name,
                    AoData.value('(data[@name="log_block_id"]/value)[1]', 'BIGINT') AS log_block_id,
                    NULL AS database_id,
                    AoData.value('(data[@name="processing_time"]/value)[1]', 'BIGINT') AS processing_time,
                    AoData.value('(data[@name="start_timestamp"]/value)[1]', 'BIGINT') AS start_timestamp,
                    CAST(SUBSTRING(CAST(xe.event_data AS NVARCHAR(MAX)), 87, 24) AS DATETIMEOFFSET) AS publish_timestamp,
                    NULL AS log_block_size,
                    AoData.value('(data[@name="target_availability_replica_id"]/value)[1]', 'UNIQUEIDENTIFIER') AS target_availability_replica_id,
                    AoData.value('(data[@name="local_availability_replica_id"]/value)[1]', 'UNIQUEIDENTIFIER') AS local_availability_replica_id,
                    AoData.value('(data[@name="target_availability_replica_id"]/value)[1]', 'UNIQUEIDENTIFIER') AS database_replica_id,
                    AoData.value('(data[@name="mode"]/value)[1]', 'BIGINT') AS mode,
                    AoData.value('(data[@name="availability_group_id"]/value)[1]','UNIQUEIDENTIFIER') AS availability_group_id,
                    NULL AS pending_writes
                FROM #EventXml AS xe
                CROSS APPLY xe.event_data.nodes('/event')  AS T(AoData)
                WHERE xe.event_name = 'hadr_transport_receive_log_block_message'

                DELETE
                FROM DMReplicaEvents
                WHERE CAST(publish_timestamp AS DATETIME) < DATEADD(minute, -2, CAST((SELECT MAX(publish_timestamp) from DMReplicaEvents) as DATETIME))
                COMMIT
                GO


Why is my transaction log file growing so large after disabling replication?

From your comment, you apparently had success re-enabling replication, and then removing it properly using the information contained in the post entitled Disable Publishing and Distribution.

To summarize the steps involved:

Create a new publication with only one article (which we are about to delete anyway). When prompted for a schedule for the snapshot agent, choose any time that is not right now.
Stop all replication-related jobs. For a list of job names, see the
"Agent Security Under SQL Server Agent" section of Replication Agent Security Model.
At each Subscriber on the subscription database, execute sp_removedbreplication to remove replication objects from the database. This stored procedure will not remove replication jobs at the Distributor.
At the Publisher on the publication database, execute sp_removedbreplication to remove replication objects from the database.
If the Publisher uses a remote Distributor, execute sp_dropdistributor.
At the Distributor, execute sp_dropdistpublisher. This stored procedure should be run once for each Publisher registered at the Distributor.
At the Distributor, execute sp_dropdistributiondb to delete the distribution database. This stored procedure should be run once for each distribution database at the Distributor. This also removes any Queue Reader Agent jobs associated with the distribution database.
At the Distributor, execute sp_dropdistributor to remove the Distributor designation from the server.

  NOTE:

  If all replication publishing and distribution objects are not dropped
  before you execute sp_dropdistpublisher and sp_dropdistributor, these
  procedures will return an error. To drop all replication-related
  objects when a Publisher or Distributor is dropped, the @no_checks
  parameter must be set to 1. If a Publisher or Distributor is offline
  or unreachable, the @ignore_distributor parameter can be set to 1 so
  that they can be dropped; however, any publishing and distributing
  objects left behind must be removed manually.


SQL Server self recursive horizontal join

I feel there are two approaches - left to right starting anywhere that has no parent, and right to left for anywhere that has a value. I'm going to go with the latter because it matches the rows to be produced.

You should start by using a recursive CTE to turn your data into:

RowVal, NodeVal, Value, Level
3, 3, 30, 0
3, 1, 30, 1
5, 5, 20, 0
5, 4, 20, 1
5, 2, 20, 2
5, 1, 20, 3
6, 6, 10, 0

There are plenty of examples for doing this...

WITH theTree AS (
    SELECT ID AS RowVal, ID AS NodeVal, Value, 0 AS Level, ID_Parent
    FROM theTable
    WHERE Value IS NOT NULL
    UNION ALL
    SELECT tr.RowVal, ta.ID AS NodeVal, tr.Value, Level + 1 AS Level, ta.ID_Parent
    FROM theTree AS tr
    JOIN theTable AS ta ON ta.ID = tr.ID_Parent
)
SELECT RowVal, NodeVal, Value, Level
FROM theTree
;

Now you need to adjust the Levels, because we started at the leaves.

MAX(Level) OVER (PARTITION BY RowVal) - Level AS NewLevel

Now it's simple dynamic pivot stuff, which you can find very easily. Group by RowVal, Value. A column per NewLevel. And MAX(NodeVal) in each "cell".


The Comment by  "Henrik Staun Poulsen" above pointed me in the right direction.
Half the total space used by my database is a single XML index.

I found a great article on XML Indexes here

https://www.red-gate.com/simple-talk/sql/database-administration/getting-started-with-xml-indexes/

I tried to rebuild the index with

Alter Index <IndexName> on <TableName> Rebuild

It ran for 5 minutes but that didn't reduce the size so I'm probably stuck with it.

I'll post the full query Henrik pointed me to here incase that link disappears in the future. (I hate that)

    ;WITH agg AS
(   -- Get info for Tables, Indexed Views, etc
    SELECT  ps.[object_id] AS [ObjectID],
            ps.index_id AS [IndexID],
            NULL AS [ParentIndexID],
            NULL AS [PassThroughIndexName],
            NULL AS [PassThroughIndexType],
            SUM(ps.in_row_data_page_count) AS [InRowDataPageCount],
            SUM(ps.used_page_count) AS [UsedPageCount],
            SUM(ps.reserved_page_count) AS [ReservedPageCount],
            SUM(ps.row_count) AS [RowCount],
            SUM(ps.lob_used_page_count + ps.row_overflow_used_page_count)
                    AS [LobAndRowOverflowUsedPageCount]
    FROM    sys.dm_db_partition_stats ps
    GROUP BY    ps.[object_id],
                ps.[index_id]
    UNION ALL
    -- Get info for FullText indexes, XML indexes, Spatial indexes, etc
    SELECT  sit.[parent_id] AS [ObjectID],
            sit.[object_id] AS [IndexID],
            sit.[parent_minor_id] AS [ParentIndexID],
            sit.[name] AS [PassThroughIndexName],
            sit.[internal_type_desc] AS [PassThroughIndexType],
            0 AS [InRowDataPageCount],
            SUM(ps.used_page_count) AS [UsedPageCount],
            SUM(ps.reserved_page_count) AS [ReservedPageCount],
            0 AS [RowCount],
            0 AS [LobAndRowOverflowUsedPageCount]
    FROM    sys.dm_db_partition_stats ps
    INNER JOIN  sys.internal_tables sit
            ON  sit.[object_id] = ps.[object_id]
    WHERE   sit.internal_type IN
               (202, 204, 207, 211, 212, 213, 214, 215, 216, 221, 222, 236)
    GROUP BY    sit.[parent_id],
                sit.[object_id],
                sit.[parent_minor_id],
                sit.[name],
                sit.[internal_type_desc]
), spaceused AS
(
SELECT  agg.[ObjectID],
        agg.[IndexID],
        agg.[ParentIndexID],
        agg.[PassThroughIndexName],
        agg.[PassThroughIndexType],
        OBJECT_SCHEMA_NAME(agg.[ObjectID]) AS [SchemaName],
        OBJECT_NAME(agg.[ObjectID]) AS [TableName],
        SUM(CASE
                WHEN (agg.IndexID < 2) THEN agg.[RowCount]
                ELSE 0
            END) AS [Rows],
        SUM(agg.ReservedPageCount) * 8 AS [ReservedKB],
        SUM(agg.LobAndRowOverflowUsedPageCount +
            CASE
                WHEN (agg.IndexID < 2) THEN (agg.InRowDataPageCount)
                ELSE 0
            END) * 8 AS [DataKB],
        SUM(agg.UsedPageCount - agg.LobAndRowOverflowUsedPageCount -
            CASE
                WHEN (agg.IndexID < 2) THEN agg.InRowDataPageCount
                ELSE 0
            END) * 8 AS [IndexKB],
        SUM(agg.ReservedPageCount - agg.UsedPageCount) * 8 AS [UnusedKB],
        SUM(agg.UsedPageCount) * 8 AS [UsedKB]
FROM    agg
GROUP BY    agg.[ObjectID],
            agg.[IndexID],
            agg.[ParentIndexID],
            agg.[PassThroughIndexName],
            agg.[PassThroughIndexType],
            OBJECT_SCHEMA_NAME(agg.[ObjectID]),
            OBJECT_NAME(agg.[ObjectID])
)
SELECT sp.SchemaName,
       sp.TableName,
       sp.IndexID,
       CASE
         WHEN (sp.IndexID > 0) THEN COALESCE(si.[name], sp.[PassThroughIndexName])
         ELSE N'<Heap>'
       END AS [IndexName],
       sp.[PassThroughIndexName] AS [InternalTableName],
       sp.[Rows],
       sp.ReservedKB,
       (sp.ReservedKB / 1024.0 / 1024.0) AS [ReservedGB],
       sp.DataKB,
       (sp.DataKB / 1024.0 / 1024.0) AS [DataGB],
       sp.IndexKB,
       (sp.IndexKB / 1024.0 / 1024.0) AS [IndexGB],
       sp.UsedKB AS [UsedKB],
       (sp.UsedKB / 1024.0 / 1024.0) AS [UsedGB],
       sp.UnusedKB,
       (sp.UnusedKB / 1024.0 / 1024.0) AS [UnusedGB],
       so.[type_desc] AS [ObjectType],
       COALESCE(si.type_desc, sp.[PassThroughIndexType]) AS [IndexPrimaryType],
       sp.[PassThroughIndexType] AS [IndexSecondaryType],
       SCHEMA_ID(sp.[SchemaName]) AS [SchemaID],
       sp.ObjectID
       --,sp.ParentIndexID
FROM   spaceused sp
INNER JOIN sys.all_objects so -- in case "WHERE so.is_ms_shipped = 0" is removed
        ON so.[object_id] = sp.ObjectID
LEFT JOIN  sys.indexes si
       ON  si.[object_id] = sp.ObjectID
      AND  (si.[index_id] = sp.IndexID
         OR si.[index_id] = sp.[ParentIndexID])
WHERE so.is_ms_shipped = 0
--so.[name] LIKE N''  -- optional name filter
ORDER BY UsedGB Desc

11Jun19 Posted a question to Stack Exchange.  Didn’t use the exact queries below but something close.

16May19 This script returns information but it doesn’t add up to anything like the size of the db.

USE [MyArchive] -- replace your dbname
GO
SELECT
s.Name AS SchemaName,
t.Name AS TableName,
p.rows AS RowCounts,
CAST(ROUND((SUM(a.used_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Used_MB,
CAST(ROUND((SUM(a.total_pages) - SUM(a.used_pages)) / 128.00, 2) AS NUMERIC(36, 2)) AS Unused_MB,
CAST(ROUND((SUM(a.total_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Total_MB
FROM sys.tables t
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
GROUP BY t.Name, s.Name, p.Rows
--ORDER BY s.Name, t.Name
Order by Total_MB
GO


Can't promote PostgreSQL warm standby server to start serving data

Ok, made it work by using the -R flag in pg_basebackup. This flag automatically generates the recovery.conf file with the right parameters

From the official doc:

-R
--write-recovery-conf
Write a minimal recovery.conf in the output directory (or into the base archive file when using tar format) to ease setting up a standby server.


MySQL dynamically optimize innodb tables without "file per table" setting

Before answering the question, please click here to see the InnoDB Infrastructure Map

Based on innodb_file_per_table being disabled, let's go through your questions:

  Q 1: How much fragmentation is allowed before it affects performance?

The system tablespace can grow to the limit of the disk volume.

EXAMPLE: I just answered a question about what do to when the system tablespace reaches the limit of an ext3 disk : How to solve "The table ... is full" with "innodb_file_per_table"?

There may still be some wiggle room inside the system tablespace. However, when the wiggle room dwindles to the point that all 1023 undo logs inside the system tablespace are completely filled and can no longer extend, then you must add a new system tablespace file.

Please note that when I say wiggle room, I am referring to the free space within the system tablespace that must accommodate the following:

Data Dictionary
Double Write Buffer (can be disabled but not recommended)
Insert Buffer (Cached Index Changes in System Tablespace instead of the OS)
Rollback Segments (1023 slots)
Undo Logs (referenced from the Rollback Segments)
Please refer Back to the InnoDB Infrastructure Map

  Q 2: Should InnoDB tables even be optimized (some say yes others say no)?

If you run OPTIMIZE TABLE, you basically make the data and index pages contiguous in the system tablespace. This defragments the table and accesses all data and indexes quicker until the fragmentation reappears over time in production use. This can introduce new areas of fragmentation. Again, all that fragmentation can fill up with data and indexes. This will endanger the wiggle room I mentioned before.

  Q 3: How do you test for InnoDB fragmentation if the server does not use the “file per table” option?

Back on Aug 27, 2012, I answered this post : How To Optimize and Repair InnoDB tables? ALTER and OPTIMIZE table failed

I explained there how to get the fragmentation. In essence, you do this:

Goto the OS and run

cd /var/lib/mysql
ls -l ibdata1 | awk '{print $5}'

This gets you the size of ibdata1 in bytes

SELECT (data_length+index_length) InnoDBDataIndexBytes
FROM information_schema.tables WHERE engine='InnoDB';

This gets you the sum total of data and index pages in bytes

Subtract the sum total from ibdata1's total bytes. The difference represents the wiggle room. This space causes fragmentation, but is constantly in use until ibdata1 gets filled.

CAVEAT : When innodb_file_per_table is enabled, I explain how to get the fragmentation of an individual table: Innodb table with many deletes and inserts - is there any disk space wasted?

  Q 4: Is fragmentation the only reason to run “optimize table”?

Yes. It is far more beneficial for MyISAM and for InnoDB tables (innodb_file_per_table being enabled). Do this with innodb_file_per_table off and you will just make the system tablespace grow faster. See my post How can Innodb ibdata1 file grows by 5X even with innodb_file_per_table set?

  Q 5: If I do need to run “optimize table” on an InnoDB table should I run ALTER TABLE mydb.mytable ENGINE=InnoDB; and not ANALYZE TABLE

Running ALTER TABLE mydb.mytable ENGINE=InnoDB; would indeed shrink when innodb_file_per_table is enabled. Again, it is not worth when innodb_file_per_table is disabled.

  Q 6: Can you selectively tell which innodb tables needs optimizing if the server does not use the "file per table" option?

No you cannot. Why? The INFORMATION_SCHEMA becomes totally useless because all the tables are inside one file. I wrote a script to find to uptime_time (last time an InnoDB table was written) of all InnoDB tables. Is there a way to find the least recently used tables in a schema? script only works for innodb_file_per_table. This shows that you cannot ascentain that fragmentation with ease. You could resort to more aggressive techniques like dumping the tablespace map and located segments with unused space : See this blog post : http://www.markleith.co.uk/2009/01/19/innodb-table-and-tablespace-monitors/. This is way too much firepower to deal with. You could just run the OPTIMIZE TABLE to eliminate segment fragmentation, but this brings us back full circle to getting everything out of ibdata1.

SUGGESTION

If you want to remove all data and index pages from ibdata1 and shrink ibdata1 permanently, please read my Oct 29, 2010 StackOverflow post Howto: Clean a mysql InnoDB storage engine?

As you can see, this subject is not new to me

Jan 13, 2013 : How to check growth of database in mysql?
Sep 23, 2012 : MySQL / InnoDB: Does daily optimization improve performance?
Apr 15, 2012 : What happens when InnoDB hits its tablespace autoextend max?
Apr 11, 2012 : How do you remove fragmentation from InnoDB tables?
Mar 25, 2012 : Why does InnoDB store all databases in one file?
Feb 03, 2012 : Scheduled optimization of tables in MySQL InnoDB

EPILOGUE

Running OPTIMIZE TABLE is not the biggest reason that ibdata1 grows quickly. Please see this post from mysqlperformanceblog and learn about the other contributing factors.

Please remember that most who run OPTIMIZE TABLE do so sequentially. You could probably script many of them in parallel. Of course, you need to convert to innodb_file_per_table like I mentioned before.


SQL Server Migration using a DB Mirror (Rolling upgrade)

We used successfully mirroring to migrate databases from SQL 2005 to SQL 2016 (on new hardware). All worked flawlessly. On hardware that was 13-14 years old and using Windows Server 2003.

Can't say anything about MS support, as since we had such an old version, we didn't have any type of support from MS (not even Extended).

If you're curious, here's a short list of steps we used:

Prerequisites

logins
linked servers
jobs
SSIS packages
export sys.messages

Mirroring preparation

setup endpoints and permissions
disable log backups
restore dbs on mirror
mirror setup on both the principal and partner
re-enable backups

Actual migration - the only moment of downtime

manual fail-over, make sure you do it step by step, as it will take a while to get in sync
break mirror and set dbs online
set the databases on the old principal as OFFLINE, so the apps won't connect to it
UPDATE STATS (fully if possible) - can't stress how important this step is
then enable the application logins and you can get on with it

Everything gets a tad more complicated with replications, but you get the point. It works very well. Log shipping will work too, no worries. But since it involves a bit more manual steps, we preferred to reduce the complexity if possible.


Oracle 12c Client to 10g Server (OPENQUERY)

As Oracle support note "Client / Server Interoperability Support Matrix for Different Oracle Versions" ( Doc ID 207303.1 ),  Oracle Client 12.1 does not support connections with Oracle DB servers 10.1 and below.

Connections with Oracle DB 10.2 are still supported, but not recommended.

The recommended option its to use Oracle 10.2 client.


Analysis

You receive the above error message if the sqlservr.exe program is already running or if your privileges are not correct.

Solution

Try stopping the SQL Server () service first and the you should be able to start sqlservr.exe -m without any further issues.

IF the service is not running, check the Processes tab in the Windows Task Manager for a process sqlservr.exe.

IF the the service is not running and no other process can be seen in the Task Manager ensure you start the Command Prompt with Runas Administrator... and you should be able to start the process.

IF you are still unable to start the instance, then you might have to add additional parameters when manually starting your instance.

SQL Server Startup Parameters (2014)

The parameters for manually starting SQL Server are as follows:

C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn>sqlservr.exe /?
usage: sqlservr
        [-a,]                    (adding an L2 buffer pool file)
        [-c] (not as a service)
        [-d file] (alternative master data file)
        [-l file] (alternative master log file)
        [-e file] (alternate errorlog file)
        [-f] (minimal configuration mode)
        [-m] (single user admin mode)
        [-g number] (stack MB to reserve)
        [-k ] (checkpoint speed in MB/sec)
        [-n] (do not use event logging)
        [-s name] (alternate registry key name)
        [-T ] (trace flag turned on at startup)
        [-x] (no statistics tracking)
        [-y number] (stack dump on this error)
        [-B] (breakpoint on error (used with -y))
        [-K] (force regeneration of service master key (if exists))
        [-v] (list version information)

See documentation for details.
2017-09-27 10:21:14.23             SQL Server shutdown has been initiated


Without seeing the plan...

Updating all 92k rows

scans the table start to end
updates all rows (whether value is the same or not)
clustered index leaf = the actual data

= you are actually doing clustered index update

When you updating 3,200 rows means you are doing 96.5% less data changes work (3200 is 3.5% of 92000) so of course it will run faster

Also the transaction size (for rollback) in the transaction log will be a lot bigger.
This needs allocated and may need a log file size increase

Basically, update only the rows you need...


How to create a function(table_name) that return a collection in Oracle?

I haven't syntax checked this but it should put you on the right path.

This should be put in a package but for demonstration purposes...

PROCEDURE GET_COLUMNS(table_name_in IN VARCHAR2, v_query_out OUT sys_refcursor)
IS
v_count NUMBER(9);
CURSOR the_columns IS
Select COLUMN_NAME
from all_tab_columns
where table_name='table_name_in';
v_query VARCHAR2(4000):= 'Select  ';
BEGIN
IF table_name IS NOT NULL THEN
--requires permission to access dba_tables, if not available use ALL_TABLES
--or USER_TABLES
   SELECT count(*)
   INTO v_count
   FROM dba_tables
   where table_name = table_name_in;
--might need the schema name too in a multi schema environment
   IF v_count <> 1 THEN
      RAISE bad_table_name;
   END IF;
ELSE
     RAISE bad_table_name;
END IF;

for all_columns in the_columns LOOP
v_query := vquery || all_columns.COLUMN_NAME ||',';
END LOOP;
--trim off the last comma
v_query := SUBSTR(v_query, 1, LENGTH(v_query) - 1);
v_query := v_query ||' from ' || table_name_in;

OPEN v_query_out for v_query;

EXCEPTION
when bad_table_name THEN
--log an error message
DBMS_OUTPUT.PUT_line('Table name is not accessible or does not exist');
RAISE; --could raise an custom exception if more detailed logging required
END;

This should work on Oracle 9,10 and 11 and could be simplified for 12 by using RETURN_RESULT.


PostgreSQL: Merge rows with the same date and time but different numeric values in one row with average of the values

|1|2|2012-01-01|12:00:00|1.3459|1.3645|

SELECT col1, col2, col3, col4, AVG(col5) col5, AVG(col6) col6
FROM table_name
GROUP BY col1, col2, col3, col4;


how much to keep table_open cache and what settings is available in mysql to release unwanted memory cache and table cache?

Each unit in table_open_cache is one table (not one byte).  Since you have a huge amount of RAM, the 'right' answer is probably to make it a little more than the number of tables in all the databases.

It is limited to open_files_limit, which, in turn, is limited by the OS.  Some OSs have an artificially low 1024, but it can be changed.

Suggest table_open_cache_instances = 16 if your version includes that relatively new feature.

If you would like a lengthier check, please provide SHOW VARIABLES; and SHOW GLOBAL STATUS; after mysqld has been running for more than a day, and preferably with a realistic load.

A common mistake is setting query_cache_size bigger than about 50M.  The code in the QC was not designed to scale.  Anyway, most production servers run better with the QC turned off.

The buffer_pool is the main cache in RAM.  There are a number of lesser ones.  Is this what you mean by "memory cache"?

Will this server be running anything other than MySQL?  If so, you should subtract that from the 672G when posing the question.


How to set a correct value for wiredTiger concurrentTransactions

The read/write ticket values are set to 128 to prevent overwhelming WiredTiger with concurrent requests. They are chosen to cater for the typical server hardware. It's generally not recommended to change these values.

If you see stalling and observed that the read/write tickets to be maxed out, it's extremely likely that this is just a symptom. The cause of the stalling is typically somewhere else.

A couple of examples:

A slow disk can cause write requests to pile up since they can't be serviced fast enough. If the read/write ticket is increased because it appears that the storage engine is hitting the ticket number limit consistently, what actually will happen is that you're putting even more pressure on the disk, which will result in further delay. The correct remediation in this case is to provision a faster disk.
You have a deployment in AWS. For most of the day, read & write progress smoothly. There is an increase in write load, but the system can handle it just fine. Suddenly things start to stall, and you see read/write tickets exhausted for apparently no reason. In most cases like this, the root cause is because the disk has ran through its allotted burst IOPS and is being throttled by AWS. As you can imagine, increasing read/write ticket numbers in this case is not helpful and can make things worse.

In conclusion, you don't really need to tweak read/write tickets. If you find it constantly exhausted, check for possible causes of work piling up and waiting for the storage engine.


How to restore accidentally truncate mysql table

See this link for the answer. Hope this helps.

If you have binary logging enabled, you can replay changes that went into creating the stats table, up to the point where you did the TRUNCATE.

But you may not have binary logs for all changes to that table from the beginning of time. It's common to have binary logs only back a few days. Any data that was created in that table prior to your oldest binary log is not recoverable.


Question about Oracle's interpretation of "host cpu utilization" in v$sysmetric view

The figures in the v$sysmetric view are aggregated from v$osstat over a period of time (either 15 or 60 seconds).

v$osstat figures should closely mirror what you'd see from a tool such as iostat, sar, mpstat, top or nmon, but they will never be identical as it is impossible to synchronise the performance monitoring tool data collection with the data collection performed by the Oracle MMON process. As a result, the aggregated data in v$sysmetric will also never perfectly match monitoring tool output, but should be pretty close.

Busy% is indeed Usr% + Sys%.


Need some new solution to shrink my SQL Server database log file

Why exactly do you need to shrink the log file?  It appears that you are running log shipping, so transaction log backups are being taken and your log file has grown to this size, so the likelihood is that it will just grow to this size again, which will impact performance for users when the file growth is occurring.

Both the options you have specified in your question will break your log shipping configuration, the first one will break your backup chain completely and the second one will break log shipping as you will have transactions in the backup you have just taken that will not be copied across to your secondary.

If you really must shrink the log file, you can simply try running DBCC SHRINKFILE after a log shipping backup has run.  Bear in mind that if there are open transactions in the DB, it either won't shrink by much or at all.  You also won't be able to shrink the file below the initial size specified for the log file either and like I said earlier, if you do shrink it, it will likely grow again which will impact performance.


CHECKDB problem - SQL files (mdfs,ldfs etc.) in Azure Files

DBCC CHECKDB uses a Database Snapshot by default.  Database Snapshots are an NTFS feature.  When storing the database on an SMB share, they aren't available.  Try specifying the TABLOCK option for DBCC CHECKDB.

And Azure Premium Files are intended to support SQL Server database file storage.   In addition to providing shared storage for clusters, they allow you to share storage resources among SQL VMs.  Before the release of Premium Files, Azure Files was not really recommended for SQL Server database storage.

When Azure Shared Disks are GA they may provide a better option for shared storage, but currently the only two options are Storage Spaces Direct, and Azure Premium Files.


First off, just to be clear: SQLCLR / .NET / C# / VB.NET cannot query the database. Only T-SQL can query SQL Server. So in order for SQLCLR code to get data or interact with SQL Server in any way, it must establish a SqlConnection, like any other .NET app, and submit T-SQL, or execute a Stored Procedure.

Yes, you can call a SQLCLR (whether it is C# or VB.NET does not matter) Stored Procedure, or Scalar Function, or Table-Valued Function (TVF), etc from T-SQL. In fact, that is the only way you can call SQLCLR objects. Classes representing the SQLCLR objects, defined in Assemblies, need to have T-SQL wrapper objects defined that point to those classes so that they can be invoked.

Sending complex objects into SQLCLR objects can be accomplished, but unfortunately not via Table Valued Parameters (TVPs). However, you can either create a local temporary table in T-SQL, populate it, and select from it in the SQLCLR object, OR you can package up the complex data in XML and pass that in as a parameter to the SQLCLR object that will traverse it via an XmlReader.

For getting the data back efficiently, you can either:

Issue INSERT statements from the SQLCLR Stored Procedure
Create a T-SQL Stored Procedure that accepts a TVP and call that from the SQLCLR object
return a Result Set from the SQLCLR object that is used in an INSERT statement, as as INSERT INTO ... EXEC for a SQLCLR Stored Procedure, or INSERT INTO ... SELECT for a SQLCLR TVF.

As far as loading you DAL libraries goes, that could work, but there could also be complications. It all depends on what you libraries are doing and what Framework libraries are being referenced. Only a small subset of the .NET Framework libraries are supported in SQL Server's CLR host, so if your libraries reference other libraries that are not "approved", then you will need to load those libraries manually, along with any of their dependent libraries (if there are any). The complicating factors here are that loading unsupported .NET Framework libraries requires that they be set to UNSAFE, which in turn requires that the Database be set to TRUSTWORTHY ON. The other complication is that loading the .NET Framework library might not even be possible in the first place since the CLR host inside of SQL Server only allows pure-MSIL Assemblies, and so you cannot load mixed-mode Assemblies. Some .NET Framework libraries are currently mixed-mode and won't load. Others are currently "pure" and will load, but there is no guarantee that those will not ever be changed into a mixed-mode Assembly in a future .NET Framework update. If/when that happens (and it has happened, such as with System.ServiceModel), then you will need to re-code you Assembly. So it's best to stick with the supported Framework libraries as those are guaranteed to continue working through future upgrades.

Keep in mind that App Domains are per Database, per Owner (of the Assembly). This means that, in terms of any particular SQLCLR object, all sessions / SPIDs will be calling the same code and sharing the same memory. This is why the methods need to be declared as static. And this is why attempting to use non-readonly static class variables requires the Assembly to be marked as UNSAFE: because the value of such a variable can be overwritten by another session / SPID (case in point: SQLCLR assembly throws error when multiple queries run simultaneously).

With respect to this statement in the Question:

  as for performance I assume it would be better or at least not noticeable in comparison.

I wouldn't assume anything here. There are a lot of factors that influence performance in either direction:

some datatypes transfer back-and-forth between SQL Server memory and .NET AppDomain memory faster than others
how you code your algorithms in C# (I recently wrote a simple function for someone who had already gotten another suggestion that used LINQ -- the LINQ version took 375 - 400 ms to do the same thing that my version, with more lines, took only 2 - 9 ms to complete, a 41x - 200x difference!)
how much data you have (sometimes T-SQL is faster with smaller values -- usually strings -- where SQLCLR might be faster with longer values)
are you following best practices? If you are coding a deterministic SQLCLR scalar function and don't mark it as IsDeterministic=true, then you can't get the benefit of it being able to participate in a parallel plan (something T-SQL UDFs can't do).
etc

Hence: don't assume; test!

If you are looking to learn more about working with SQLCLR, you might want to check out a series that I am writing on this topic on SQL Server Central: Stairway to SQLCLR (free registration is required to access their content). There are several articles and more coming, and plenty of examples to illustrate various features, security, etc.

Regarding Azure SQL Database: please note that while support for SQLCLR (SAFE Assemblies only) was added in late 2014, it was recently (and supposedly temporarily) removed. Customers were emailed on April 8th, 2016 of the feature being removed on April 15th, 2016. The most official notice I could find was this thread on reddit:

Customer:

  Is support for CLR Assemblies in Azure SQL going to end any time soon?
  I ask this because I received an email this morning (supposedly) from Microsoft stating that this feature will no longer be supported after April 15th (!!!).

Jan, a PM in the Azure SQL DB team working on performance and Elastic Pools:

  The email you received is indeed real. As stated in the mail, we determined that there is a security risk with user-defined SQL CLR in SQL Database. There have been no known exploits to date. In an effort to help protect the security of our customers, we have proactively suspended the use of user-defined SQL CLR in Azure SQL Database. This means that user-defined SQL CLR will no longer work. We will update you within six month with a timeline for bringing back SQL CLR.

and Tommy, a PM in the Azure SQL DB Security team:

  This issue only applies to the Azure SQL Database service, not SQL Server on-prem or IaaS.


What happens when an automatically generated UUID primary key collides in Postgres?

No, it does not retry and thus throws an duplicate key error.

You can use the following code to test it yourself:

CREATE OR REPLACE FUNCTION random_uuid() RETURNS uuid AS $$
        BEGIN
            IF random() > 0.5 THEN
                RETURN '35d4b5e2-8fb5-4b89-9874-62b572c8fa2b'::uuid;
            ELSE
                RETURN gen_random_uuid();
            END IF;
        END;
$$ LANGUAGE plpgsql;

CREATE TABLE test (id uuid default random_uuid() Primary key, date timestamp);

INSERT INTO test (date) VALUES (now());
INSERT INTO test (date) VALUES (now());
INSERT INTO test (date) VALUES (now());

If you want to avoid collisions you might want to use a v1 (time based) uuid.

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
SELECT uuid_generate_v1();

But this depends on your usecase (do not use that for crypto/security).