Yes, use the statistics collector. The manual:

  PostgreSQL's statistics collector is a subsystem that supports
  collection and reporting of information about server activity. [...]
  It can also count calls to user-defined functions and the total time
  spent in each one.

  [...]

  The parameter track_functions enables tracking of usage of
  user-defined functions.

Bold emphasis mine.

track_functions (enum)

  Enables tracking of function call counts and time used. Specify pl to track only procedural-language functions, all to also track SQL and
  C language functions. The default is none, which disables function
  statistics tracking. Only superusers can change this setting.

  Note: SQL-language functions that are simple enough to be "inlined" into the calling query will not be tracked, regardless of this setting.

So this is off by default. If all relevant functions are LANGUAGE plpgsql, it's enough to set it to pl in postgresql.conf and reload. No need to restart the server for this setting.

track_functions = pl

Reload with pg_ctl reload. Or on a typical Debian(-related) installation:

sudo -u postgres pg_ctlcluster 12 main reload

Postgres starts to gather information. After an adequate period of time, check track records. Careful though, some important functions may not be called in weeks. Your call ...

How to access gathered statistics? The manual again:

  When statistics collection is enabled, the data that is produced can
  be accessed via the pg_stat and pg_statio family of system views.
  Refer to Chapter 27 for more information.

Basically:

SELECT * FROM pg_stat_user_functions;

Or you can easily look it up with pgAdmin (or another GUI). In pgAdmin (3 or 4), select the function and look at the "Statistics" tab top right: "Number of Calls".

Aside:
There are no true "stored procedures" before Postgres 11, just functions. The term "procedure" is often used synonymously.


Delete Duplicates Leaving One of Two Identical Records

Without knowing the structure, generically, you can GROUP BY the fields that have been duplicated and store it in a separate table for check purposes, then substitute the old table with the new one.

So, imagine that I have a table my_table with the fields a and b duplicated. I can do:

CREATE TABLE my_table_new (id int primary key auto_increment, a blob, b blob);

SET @i := 0;

INSERT INTO my_table_new SELECT @i := @i + 1, a, b FROM my_table GROUP BY a, b;

The exact query will depend on the older structure, the fields you want to group by, the order in which you want to insert, etc. This example creates an integer primary key from 1 to number of non-duplicate records.


What consequence deleting idb file will have on my mysql database?

That would be most unwise. Never fiddle with MySQL files directly.

If you want to delete those tables use SQL:

DROP TABLE pub.vues, pub.log


Login / Password for Oracle Enterprise Manager in VM template?

There is a Readme for each Oracle Template download. Here is the related part from the Readme for your template:

5. O/S passwords: The passwords for the "root" and "oracle" O/S users are "welcome1"

6. All Enterprise Manager Cloud Control passwords are "welcome1"


New query taking over 60% of running time

The expression where DATE_PART('day', now() - fecha ) < 5 can not make use of an index on the column fecha.

Rewrite the expression so that it can use an index:

where fecha > current_date - 5


What is the difference between Federated and Decentralized Data Warehouse?

A decentralised data warehouse is essentially a collection of data warehouses maintained by individual regions or business units but made available centrally.  These may be on the same physical server, share reporting tools, or be made available across the organisation in some other way.  There may also be centralised components such as master data management.  This is normally done because centralised data warehouses get unwieldy beyond a certain size of organisation.  A data warehouse has to be responsive to change and if it is too unresponsive then individual departments will start building their own solutions.

You can see this in investment banks, where the tendency is to do data warehouses to meet specific requirements (e.g. a particular regulatory initiative or some type of financial reporting) rather than to build a centralised warehouse across the whole business.  A company the size of a large bank is too complex to do the requirements for a fully centralised EDW in a reasonable length of time.

A federated data warehouse adds a master consolidation layer across the decentralised data warehouses.  Typically this will only house a narrow vertical slice of the data, as its purpose is to consolidate key metrics across the whole business for company or group level reporting, rather than to provide a generalised MI platform for all departments.  The departments are left to produce their own EDW or MI systems but are required to furnish the data sets needed to populate the central consolidation layer.

This architecture gives you the best of both worlds.  Central management can see their metrics across the whole organisation, and the departments can arrange M.I. solutions to meet their needs.  Central management only need to impose the data requirements needed by their analytics and MI as feeds provided by the departmental systems.  If they need more in-depth reporting or analysis on a specific department then this can be furnished by the departmental systems.

The article linked below discusses federated data warehouses in more depth.

http://www.zentut.com/data-warehouse/federated-data-warehouse-architecture/

This article discusses data warehouse topologies in more depth.

https://www.ibmbigdatahub.com/blog/data-warehouse-architectures-multinational-organizations-part-1

https://www.ibmbigdatahub.com/blog/data-warehouse-architectures-multinational-organizations-part-2


How to generate time in milliseconds in PostgreSQL stored procedure?

I understand one could use something like EXTRACT on CURRENT_TIMESTAMP when selecting rows

You don't need extract in order to get the data:

select *
from your_table
where timestamp_column >= timestamp '2007-08-24 18:34:01';

will happily use an index on moment_in_time and will be very efficient - in fact there will be no difference in performance compared to

select *
from your_table
where milliseconds_column >= 1187906400000;

Now look at the two statements and tell me for which one you immediately understand what the query will return.

If you do need those milliseconds in your application then you can always do something like this:

select some_col,
       other_col,
       extract(epoch from timestamp_column) * 1000 as millis
from your_table
where timestamp_column >= timestamp '2007-08-24 18:34:01';

And again, I'm almost 100% certain that you won't see any performance difference to:

select some_col,
       other_col,
       milliseconds_column
from your_table
where  milliseconds_column >= 1187906400000;

Plus: if you do store the milliseconds and you want to display the real date, you always need to apply an additional conversion:

select some_col,
       other_col,
       to_char(timestamp_column, 'yyyy-mm-dd') as the_date
from your_table
where timestamp_column >= timestamp '2007-08-24 18:34:01';

vs.

select some_col,
       other_col,
       to_char(to_timestamp(milliseconds_column / 1000), 'yyyy-mm-dd) as the_date
from your_table
where  milliseconds_column >= 1187906400000;

The second one one be slower than the first one, but it's more typing and makes the code harder to read.

Edit:

   how to create a stored procedure that will convert this timestamp into the milliseconds I want (and the reverse function too)?

You don't need to write your own function:

convert timestamp to milliseconds: extract(epoch from current_timestamp) * 1000 + extract(milliseconds from current_timestamp)
convert milliseconds to timestamp: to_timestamp(1187980443530 / 1000)

So to wrap it up:

Go with a timestamp column and don't worry about performance.


How to use psql with no password prompt?

You have four choices regarding the password prompt:

set the PGPASSWORD environment variable. For details see the manual: http://www.postgresql.org/docs/current/static/libpq-envars.html
use a .pgpass file to store the password. For details see the manual: http://www.postgresql.org/docs/current/static/libpq-pgpass.html
use "trust authentication" for that specific user: http://www.postgresql.org/docs/current/static/auth-methods.html#AUTH-TRUST
use a connection URI that contains everything: http://www.postgresql.org/docs/current/static/libpq-connect.html#AEN42532


MSSQL Geography STIntersects returns 1 when 0 is expected

The reason you are having a problem is that the polygon is wound the wrong way which results in a polygon that covers the entire world except for your area of interest.

Try

DECLARE @t_point geography;
DECLARE @t_polygon geography;
set @t_polygon = geography::STPolyFromText('POLYGON((1 1, 5 0, 5 5, 1 1))', 4326);
set @t_point = geography::Point(0, 0, 4326);

It is a bit strange, but given that with geographies we are dealing with a sphere, there needs to be a way to determine which side of the boundary is the interior of the polygon.  With SQL Server, this is done by making the exterior boundary be defined as anti clockwise.

Geometries don't care which way they are wound, but prefer anti clockwise.  There is a function for geographies ReorientObject that change the direction of the object.


Postgres How to securely initialize the database?

Your SQL script looks good, except that you shouldn't use

CREATE USER ... PASSWORD '...';

to give a user a password. The problem is that the password goes across the line in clear text and might end up in the server log.

Either hash the password on the client side and send it that way, or use psql's \password command to change the password interactively.

You should not use the postgres database for user data. It is intended for administrative purposes only (a place to connect if you want to run CREATE DATABASE or DROP DATABASE).

You can use the public schema; the only problem I see is that if you create an extension, the extension's objects end up in the same schema as the application objects, which I believe is not nice. I prefer to create a schema for the application and leave public for extension owned objects.

I am not sure what you mean when you say that you cannot get it to work with db_admins. I assume that you want to have a group of administrators so that each of them can ALTER and DROP the objects the others created.

That is not possible, because only the object owner (and a superuser) can do that. If you want several administrative users, you could proceed as follows:

Only db_admins (a NOLOGIN role) can create objects in the schema.
The administrative users are created with NOINHERIT and belong to db_admins.
Whenever an administrative user wants to create or modify an object, he or she has to first run:

SET ROLE db_admins;

This is a bit cumbersome, but a solution similar to not allowing remote logins as root on UNIX and using su to become root instead.


How to recover performance_schema after restoring from partial innobackupex backup?

The solution requires running mysql_upgrade on the server that has been restored. As per the documentation on https://dev.mysql.com/doc/refman/5.5/en/performance-schema-build-configuration.html

  If you install MySQL over a previous installation that was configured without the Performance Schema (or with an older version of the Performance Schema that may not have all the current tables), run mysql_upgrade after starting the server to ensure that the performance_schema database exists with all current tables. Then restart the server.


I see two competing/conflicting requirements:

  RAM/memory based database

and

  more than one executable to be able to access the DB concurrently

They are competing/conflicting because modern operating systems normally enforce strict segregation for RAM allocated to specific processes (there are exceptions, but in the main this holds).

One option here is to copy your database to a RAM disk. That would allow separate apps to treat the database as if it were a file. The downside here is that the contents of a RAM disk won't survive a reboot of the host machine, and so you need a very robust system to keep your database saved to a real hard disk as well.

But really, if you want multiple apps sharing the same db, you're much better off using a database server. Enforcing safe, concurrent access is one of two main reasons databases exist as distinct entities from flat file stores (the other is efficient joins).


Having a PostgreSQL database exposed to the internet

It's not really about it being a bad idea in general, more that there are very few instances where you really need to expose your database to a public interface.

Much of security is about reducing the number of attack surfaces you expose. If your application does not absolutely have to expose your database to the public then you can hide it away behind a firewall and have another level of protection from any potential exploit that might appear in the future.

I would not consider "remote administration" a good enough reason to expose postgres. As you say; you already need SSH access to your box so you could easily keep postgres behind a firewall and gain access via that without any problems.

RE: SSL connections. This would be a must unless you were happy with third-parties just reading your data while you send it over the wire. The considerations here are the same as any traffic over the public internet.


Is it possible to run multiple versions of MySQL Workbench?

I don't see why not. Are you encountering errors installing two versions at once if you specify different install paths? You may have to use different versions of the MySQL installer as well.

It does look like Workbench 8 is compatible MySQL 5.5, though (and I can confirm because I have been using it). The documentation says some queries will not work like showing the processlist (although I just tested this and I can SHOW FULL PROCESSLIST of my MySQL 5.5 server with MySQL workbench 8.0.12.)


ORA-09925: Unable to create audit trail file

Try this:
connect to SQL*Plus, then do:

show parameter audit_file_dest

then exit SQL*Plus and do:

cd <path displayed above>
ls -ld .

What are the permissions?  Ownership?

Perhaps a mounted filesystem has gone missing, and you don't have write permissions on the underlying mountpoint?


The error you are receiving is because you have a trailing end; in your dynamic SQL version that is out of place (and doesn't exist in the non-dynamic version that works):

end;' --> should be just '

Your dynamic SQL version is missing the required number of single quotes (so when you fix 1., you'll still have dates that look like integers).
This seems like a tidier approach, mostly because it avoids a lot of the single-quote-spaghetti, looping constructs, repetitive conversions, and changing the number of partitions requires only one change in the TOP() argument:

DECLARE @sql NVARCHAR(MAX) = N'CREATE PARTITION FUNCTION
   DatePartitionFunction (datetime)
   AS RANGE RIGHT FOR VALUES (';
;WITH c(rn) AS
(
  SELECT TOP (4) CHAR(39) +
    CONVERT(CHAR(8), DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY name)-1,
    GETDATE()), 112)  + CHAR(39)
  FROM sys.all_columns
)
SELECT @sql += STUFF((SELECT ',' + rn FROM c
  ORDER BY rn FOR XML PATH,
  TYPE).value(N'.[1]',N'varchar(max)'),1,1,'');
SET @sql += N');';

PRINT @sql;

Why you would then need to nest that in dynamic SQL, I'm not sure, but you could:

DECLARE @sql NVARCHAR(MAX) = N'
   DECLARE @pf NVARCHAR(MAX) = N''CREATE PARTITION FUNCTION
   DatePartitionFunction (datetime)
   AS RANGE RIGHT FOR VALUES ('';

;WITH c(rn) AS
(
  SELECT TOP (4) CHAR(39) +
    CONVERT(CHAR(8), DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY name)-1,
    GETDATE()), 112)  + CHAR(39)
  FROM sys.all_columns
)
SELECT @pf += STUFF((SELECT '','' + rn FROM c
  ORDER BY rn FOR XML PATH,
  TYPE).value(N''.[1]'',N''varchar(max)''),1,1,'''');
SET @pf += N'');'';

PRINT @pf;';

EXEC sys.sp_executesql @sql;

Please avoid lazy shorthand like DD. It takes an extra microsecond to type DAY, but the benefit is well worth it: it makes the code that much clearer and more self-documenting, and avoids fostering that shortcut for other dateparts, which can change the meaning and, therefore, the results (like y and w).


Powershell script executing within SQL Server Agent

Your first problem is that Putty/PSCP want to store the host key for each user for security reasons (stop Fred from storing a fake host key that can be used to con George into trusting a fake server). The -batch option won't override that as it's seen as a flaw in the security process.

So it's fine when you run it interactively, as you can accept the key for your account. When you run it via SQL Agent, then it wants to store it for the user running the SQL Agent service.

If you are running SQL Agent under a 'normal' user account, then one way around this is to log in interactively with that account, run the pscp command and accept the host key. This will then be stored for future runs.

The other option is to look at using another tool for the SCP functionality. Personally I'm a fan of winscp ( http://winscp.net/ ) for this. WinSCP allows you to specify the host key as part of the connection string (here is a PowerShell example - http://winscp.net/eng/docs/library#powershell ).


Improving row estimates for query with jsonb_object_keys()

A super-user can do:
alter function jsonb_object_keys ( jsonb) rows 2;

But this will change the estimate for all uses of jsonb_object_keys in that database, not just that one use of it.
If you don't like that, you could duplicate the function and change the duplicate:
CREATE OR REPLACE FUNCTION jsonb_object_keys2(jsonb)
 RETURNS SETOF text
 LANGUAGE internal
 IMMUTABLE PARALLEL SAFE STRICT ROWS 2
AS $function$jsonb_object_keys$function$;


Local MySQL instance running extremely slow compared to remote version

You have at least one bug in your function that allows it to enter an infinite loop on bad input data.  Care always has to be taken with loops in stored programs, to be certain this doesn't happen, regardless of the input.  I haven't analyzed this thoroughly, so there may be other cases that can break it, but here's the one I spotted.

You're using the input variable strValue internally, and it's a TINYTEXT, which is limited to 2^8-1 (255) characters.  But then, you're appending a trailing space.

SET strValue = CONCAT(strValue, " ");

What happens when your input value is >= 255 characters?

mysql> SELECT SOUNDEX_SPLITTED(REPEAT('a',255),',');
-- nothing happens

You have an infinite loop, because you never find the trailing space you're expecting, so this...

WHILE spaceLast < strLength DO

...remains true forever, because this...

SET strValue = CONCAT(strValue, " ");

...did not actually add a space.  It couldn't, because there's no room.  So the value was truncated back to its original, without a trailing space.

Actually, it's not true that nothing happens.  There's a warning generated, if the input is actually larger than 255 characters, and truncated at the input of the function... but you'll never see it, because it won't be visible until after the function runs.

+---------+------+-----------------------------------------------+
| Level   | Code | Message                                       |
+---------+------+-----------------------------------------------+
| Warning | 1265 | Data truncated for column 'strValue' at row 1 |
+---------+------+-----------------------------------------------+

If the input data is exactly 255 characters, there's a similar warning that should be generated within the function when you try to concatenate that single space, but Oracle has mangled the proper handling of the diagnostics area within running programs in a misguided attempt to make the right errors show up at the right time.  Even if it were there, though, you wouldn't see it, since the function is still running in an endless loop.

I'm not suggesting the following is the correct fix, but will easily demonstrate the problem (works with MySQL 5.5 and up):

SET strValue = CONCAT(strValue, " ");
-- verify that we actually did just now add a trailing space
IF strValue NOT RLIKE '[[:space:]]$' THEN
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'string overflow';
END IF;

Now...

mysql> SELECT SOUNDEX_SPLITTED(REPEAT('a',255),',');
ERROR 1644 (45000): string overflow

A better solution might be to use an internal variable that's of a longer type than the input, and copy the input plus the trailing space into that new variable, or modify the code to find the end in a different way, so that the trailing space isn't required.


SQL server Database statistics degrading after execution

Starting at the beginning:

SQL Server uses statistics objects to generate cardinality estimates for the result set steps during the optimization phase. Cardinality estimates are used to generate a query plan, which is the logical plan by which SQL Server will physically access and retrieve the data in order to satisfy the request. Generally speaking, more accurate statistics lead to a better plan because SQL Server chooses the logical and physical operators to use based on these cardinality estimates. Good estimates give SQL Server a better chance at choosing the best operation for the amount of data it expects to process.

A SQL Server Statistics object consists of a header, a density vector, and a histogram. The density vector and the histogram are used under different circumstances when estimating cardinality.

Modifications (via INSERT, UPDATE, and DELETE) necessarily change the data profile (INSERT and DELETE by modifying the number of rows in the table itself, and UPDATE by modifying the number of rows that satisfy a condition) so any of these "writes" can ultimately affect the optimization and planning phases of query execution. For a minor number of writes, this generally isn't an issue as a few rows here-and-there won't necessarily change the operators that SQL Server uses. A notable exception is if the written data are inserts that "add on" to the existing data set (e.g. an IDENTITY column, or inserting the next day's data). This is called an ascending key and can be problematic because the values exist outside of the boundaries of the histogram and (up until SQL Server 2014) SQL Server has no knowledge of the number of rows that would satisfy a request with the new key as a filter value. Sometimes you'll hear about statistics that have taken many modifications as "stale."

Because you've not shared the nature of your scheduled synchronization task, I can't tell you why updating statistics helps, in your case. It could be ascending key, in which case the update will include the new key values in the histogram. Or it could be that you've squeezed in enough data to affect the cardinality of the result set of an extant value, but your cached plan is not fit for this new amount of data. It also depends on the queries with which you're experiencing this performance degradation. Are they queries on the new data, old data, or all data? Additionally, I've seen some poor SQL generated by Entity Framework, which adds layers of complexity to the optimization and planning phase so that when the poor SQL is combined with "stale" statistics, you get a bad plan that degrades over time (if you're lucky). So, calls to scrap EF are not bad advice, but let's stay focused on the statistics portion.

Depending on your version of SQL Server, updating statistics might cause plan cache invalidation. If you're running 2005->2008R2, invalidation will only occur for relevant plans if the AUTO_UPDATE_STATISTICS database option is enabled on the database. Additionally, for versions >= 2012, you'll get an invalidated plan after a stats update if there were any modifications to the stats objects. What this means is that blindly running a stats update (especially in 2012 and newer) won't give you a better plan if you've not modified any data.

So how can you check to see if you've modified any data? Here's a query that I use (of particular interest to you would be the ModificationCounter column):

select
    SchemaName = sch.name,
    TableName = t.name,
    StatName = s.name,
    IndexName = i.name,
    StatLeadingColumn = index_col(quotename(sch.name)+'.'+quotename(t.name),s.stats_id,1),
    StatColumns = sc.StatColumns,
    IsFiltered = s.has_filter,
    StatFilter = s.filter_definition,
    IsAutoCreated = s.auto_created,

    StatLastUpdated = sp.last_updated,
    ModificationCounter = sp.modification_counter,
    StatRows = sp.rows,
    StatRowsSampled = sp.rows_sampled,
    StatUnfilteredRows = sp.unfiltered_rows,
    StatSteps = sp.steps
from sys.stats s
join sys.tables t
    on s.object_id = t.object_id
join sys.schemas sch
    on t.schema_id = sch.schema_id
cross apply sys.dm_db_stats_properties(s.object_id,s.stats_id) sp
cross apply (
                select
                stuff(
                        (
                            select ', '+c.name
                            from sys.stats_columns scol
                            join sys.columns c
                                on scol.object_id = c.object_id
                                and scol.column_id = c.column_id
                            where scol.object_id = s.object_id
                                and scol.stats_id = s.stats_id
                            for xml path(''), type
                        ).value('.','nvarchar(max)')
                        ,1,2,''
                    ) StatColumns
            ) sc
left join sys.indexes i
    on s.object_id = i.object_id
    and s.name = i.name

In order to run the above, you're going to need to be a member of the db_owner or db_ddladmin fixed database role. The sysadmin server role can obviously run this as well, but I don't generally advocate for randomly adding folks to that server role.

To check to see if a filter value is in the histogram, you can run

DBCC SHOW_STATISTICS('dbo.TableName','StatisticsName') WITH HISTOGRAM;

That will output the histogram step boundary values (RANGE_HI_KEY). If the filter is greater than the last value for this column, then it's outside of the boundaries of the table and you are at risk for cardinality estimates that are quite incorrect.

There's also a caveat to the AUTO_UPDATE_STATISTICS behavior: it updates based on the number of rows in the table.

If a table has 0 rows, the statistics will be "stale" (invalidated) when any rows are added.
If the table has <= 500 rows (when statistics were last collected), the statistics will be invalidated when data has changed on the leading column of the statistic by more than 500.
If the table has > 500 rows (when statistics were last collected), statistics will be invalidated when there are modifications to the leading column of (500 + 20% of table cardinality).

When these thresholds are met, the statistic is invalidated and the relevant query plan(s) are invalidated. The next time a query is executed, SQL Server will see the invalidation and attempt to load statistics to come up with a new plan. If the statistics are also invalidated, SQL Server will sample the data at the default rate before proceeding to the next step in optimization and planning. You can work around this by enabling the AUTO_UPDATE_STATISTICS_ASYNC database option, but then you're stuck with the invalid query plan for another execution.

Trace Flag 2371 has also been suggested as a potential fix. This changes the threshold above from a stepped linear threshold to a logarithmic threshold, triggering an update when the modification counter hits SQRT(1000*Table Cardinality) (buried in this whitepaper).


How to get something written to MySQL error log without restart

Bad queries and other invalid actions typically only write to the error log when there's not a client connection to write the error to... such as when a thread that isn't a client thread encounters an error... or from the event scheduler.

From within a scheduled event, SIGNAL can be used to throw errors and warnings that will be written to MySQL's error log.  And, of course, you can "schedule" an event to run only once, right now.

mysql> USE test;
Database changed
mysql> CREATE EVENT test_error_log ON SCHEDULE AT NOW() ON COMPLETION NOT PRESERVE
       DO SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'hi from the error log';
Query OK, 0 rows affected (0.01 sec)

$ sudo tail /usr/local/mysql/data/hostname.err
--8<-- snip --8<--
150121 14:25:06 [ERROR] Event Scheduler: [sqlbot@%][test.test_error_log] hi from the error log

Alternately, there's a 3rd party user-defined function log_error() that can write to the error log: https://github.com/mysqludf/lib_mysqludf_log

That's assuming you're not "really" wanting to trigger MySQL to politely close and reopen the log file... this is done with FLUSH ERROR LOGS;.


Postgres 10.5: Granting full schema access to a user that isn't the owner

Ok figured this out after doing a few more searches.

Essentially you need to grant same role that eventually will own the object to the user you're using.

So in my case: postgres is the user that I want to be my "pseudo-superuser" and schemaadmin is the user role that will own the objects.

GRANT schemaadmin TO postgres;

After that, everything that schemaadmin owns, postgres can modify.


Can't get this query to work (left outer join) Postgres

It looks like this is just a typo.  Your subquery is returning fk_fc_id but your join is referencing fc_fk_id.  It seems like you just need to alter the query:

select fulllist.fk_fc_id
from
(
    select distinct fk_fc_id
    from data_item
) as fulllist
left outer join
(
    select temp.fk_fc_id, count(*) as number
    from
    (
        select *
        from data_item
        where di_item_value not like 'Null'
            and di_timestamp > '2013-11-01 00:00:00'
    )   as temp
    group by fk_fc_id
)   as lj
    on lj.fk_fc_id=fulllist.fk_fc_id;

It also seems like you would want to include the number in the final select:

select fulllist.fk_fc_id, coalesce(lj.number, 0) number
from
(
    select distinct fk_fc_id
    from data_item
) as fulllist
left outer join
(
    select temp.fk_fc_id, count(*) as number
    from
    (
        select *
        from data_item
        where di_item_value not like 'Null'
            and di_timestamp > '2013-11-01 00:00:00'
    )   as temp
    group by fk_fc_id
)   as lj
    on lj.fk_fc_id=fulllist.fk_fc_id;


Merge statement with two conditions in same CLAUSE alternative in Postgres

As far as I know, Postgres (10) doesn't allow MERGE statement. You can simulate it in this way:

create table new_data (id int, status int, description text);
insert into new_data values
(1, 20, 'Description of level 1'),
(2, 10, 'Description of level 2'),
(3, 20, 'Description of level 3'),
(4, 10, 'Description of level 4'),
(5, 20, 'Description of level 5');

create table destination (id int, status int, description text);
insert into destination values
( 1, 20,  'Description of level 1'),
( 2, 10,  'Description of level 2'),
( 3, 20,  'Description of level 3'),
( 4, 10,  'Description of level 4'),
( 5, 20,  'Description of level 5'),
( 6, 10,  'Description of level 6'),
( 7, 20,  'Description of level 7'),
( 8, 10,  'Description of level 8'),
( 9, 20,  'Description of level 9'),
(10, 10, 'Description of level 10');

delete from destination d
using new_data n
where n.id = d.id
and   n.status = 10;

update destination d
set    description = 'Updated'
from   new_data n
where  d.id = n.id;

insert    into destination
select    n.id, n.status, n.description
from      new_data n
left join destination d
on        n.id = d.id
where     d.id is null
and       n.status <> 10;

2 rows affected

3 rows affected

✓

select *
from   destination
order by id;

id | status | description
-: | -----: | :----------------------
 1 |     20 | Updated
 3 |     20 | Updated
 5 |     20 | Updated
 6 |     10 | Description of level 6
 7 |     20 | Description of level 7
 8 |     10 | Description of level 8
 9 |     20 | Description of level 9
10 |     10 | Description of level 10

dbfiddle here

Using INSERT - ON CONFLICT DO

begin;

    insert into destination
    select n.id, n.status, n.description
    from   new_data n
    on conflict (id)
    do update
    set description = 'Updated';

    delete from destination
    where  id in (select id from new_data where status = 10);

commit;

dbfiddle here

Using CTE

WITH
  u AS
( update destination d
  set    description = 'Updated'
  from   new_data n
  where  d.id = n.id
    and  n.status is distinct from 10
),
  d AS
( delete from destination d
  using new_data n
  where n.id = d.id
    and n.status = 10
),
  i AS
( insert    into destination
  select    n.id, n.status, n.description
  from      new_data n
  left join destination d
  on        n.id = d.id
  where     d.id is null
)
select 1 ;

dbfiddle here


MySQL: Delete all rows older than 30 days, but only if count more than one

Any attempt to do this delete in a single query is likely to take hours, maybe days.  If that is OK, then the code by @ypercube is probably good enough.

Otherwise, I would recommend tackling it in stages.

First find the items to delete.  This will be relatively fast and non-invasive.

CREATE TEMPORARY TABLE t
    ( PRIMARY KEY(item_id) )
    SELECT item_id,
           MAX(timestamp) AS keeper
        FROM parsed
        WHERE timestamp < ...
        GROUP BY item_id
        HAVING COUNT(*) > 1;

We will use the PRIMARY KEY for efficiently walking through t.  keeper is the row not to delete.

Now walk through t in chunks, deleting from parsed.  But first, let's look at the DELETE:

DELETE parsed
  FROM parsed
  JOIN t
  WHERE parsed.item_id = t.item_id
    AND parsed.timestamp != t.keeper;

(Please verify that I got the multi-table syntax correct.)

Then put in a loop and add an AND.  In pseudo code:

SELECT @a := MIN(item_id) from t;
loop:
    SELECT @z := item_id FROM t ORDER BY item_id LIMIT 100, 1;
    DELETE ...
      AND t.item_id BETWEEN @a and @z;
    SELECT @a := @z;
go back to loop

(There's still an endcase to deal with -- You need to exit the loop when when the last chunk has less than 100 items, and do the last few items.)  More discussion.


How to retrieve / read SQL Image Data which is stored in the database?

Download and Upload images from SQL Server via ASP.Net MVC
FILESTREAM MVC: Download and Upload images from SQL Server

For a winforms or a WPF applicaiton things are simpler, just assign the BLOB stream to the image StreamSource property.


Pulling data on SSL expiration from SQL server

Is the data present in the expiry_date column of sys.certificates in each DB or in master.sys.certificates, to be pulled out and reported upon?


Script Contents of SQL Server Database From a Batch File

I wrote an open source command line utility named SchemaZen that does this. It's much faster than scripting from management studio and it's output is more version control friendly. It supports scripting both schema and data.

To generate scripts run:

schemazen.exe script --server localhost --database db --scriptDir c:\somedir

Then to recreate the database from scripts run:

schemazen.exe create --server localhost --database db --scriptDir c:\somedir


Is data retrieved from SQL Server compressed for transmission?

The data you are looking to compress is that sent over the wire via TDS. There is some minor compression here but nowhere near the type of compression you get with page/row compression, backup compression or ColumnStore compression.

It has been asked for before:

http://connect.microsoft.com/SQLServer/feedback/details/412131/enable-network-compression-compress-tds-stream

http://connect.microsoft.com/SQLServer/feedback/details/377479/wan-compression-option

The items are still open, so maybe there is some hope. There is no way to control this via the connection string that I've ever seen.

In the meantime there are some products that claim to do this, e.g.

http://www.nitrosphere.com/products/nitroaccelerator/

http://toonel.net/tcpany.htm

You can also potentially configure the network between your SQL Server and the application servers to support compression (and other things like encryption) but you are beyond my scope here, and I'm not sure if this would be supported by every single feature of SQL Server.

And to be honest, I'm not convinced this is the place you want to focus on optimizing. Compressing this stream might actually slow things down and outweigh the benefits of sending fewer bytes. I'd rather plunk the money down on better network connectivity between server and client(s) than to spend time investing in this type of work and testing whether it has any actual benefits - and not being able to do that until afterward. From from 10/100 to gig fiber has a known and predictable impact on network I/O.

I am not sure about the format of the bytes sent over the wire; you will have to set up some kind of packet sniffer for that (or maybe someone has already done that and will chime in).

As for the impact of compression, unless you are on Fusion-IO or other high-end SSD-type solutions, you are almost certainly I/O bound currently, and not CPU-bound. So as long as you have CPU overhead, you should see faster performance with compression enabled (but this won't change network performance, since the data is uncompressed before transmission). I say that knowing nothing about your servers, your application, your data or your usage patterns - you could very well have an edge case where compression actually hurts performance, or where the data just isn't a good candidate for good compression ratios.


The most powerful design tool is still natural language, predicates, and constraints. So, when in doubt use a plain text editor. Simply focus on logic, as opposed to jargon (this-that relationship ..). The following is one possible version of this story:
-- Machine type TYP exists.
--
mtyp {TYP}
  PK {TYP}

-- data sample
  (TYP)
-------------------
  ('dell alienware')
, ('macbook pro')
, ('thinkpad')
, ('hp spectre')

-- Operating system OS exists.
--
osys {OS}
  PK {OS}

-- data sample
  (OS)
-------------
  ('windows')
, ('mac os')
, ('linux')
, ('unix')

-- Operating system OS is available for machine type TYP.
--
mtyp_osys {TYP, OS}
       PK {TYP, OS}

FK1 {TYP} REFERENCES mtyp {TYP}
FK2 {OS}  REFERENCES osys {OS}

-- data sample
  (TYP, OS)
-------------------------------
  ('dell alienware', 'windows')
, ('macbook pro'   , 'mac os')
, ('thinkpad'      , 'linux')
, ('thinkpad'      , 'windows')
, ('hp spectre'    , 'windows')

-- Machine number MCH# is of machine type TYP.
--
machine {MCH#, TYP}
     PK {MCH#}
     SK {MCH#, TYP}

FK1 {TYP} REFERENCES mtyp {TYP}

-- data sample
  (MCH#, TYP)
-------------------------------
  (1, 'dell alienware')
, (2, 'dell alienware')
, (3, 'dell alienware')
, (4, 'macbook pro')
, (5, 'thinkpad')
, (6, 'thinkpad')
, (7, 'hp spectre')

-- Operating system OS is installed
-- on machine number MCH#, machine type TYP.
--
install {MCH#, TYP, OS}
     PK {MCH#, TYP}

FK1 {MCH#, TYP} REFERENCES machine   {MCH#, TYP}
FK2 {TYP, OS}   REFERENCES mtyp_osys {TYP, OS}

-- data sample
  (MCH#, TYP)
-------------------------------
  (1, 'dell alienware', 'windows')
, (2, 'dell alienware', 'windows')
, (3, 'dell alienware', 'windows')
, (4, 'macbook pro'   , 'mac os')
, (5, 'thinkpad'      , 'windows')
, (6, 'thinkpad'      , 'linux')

-- at this point nothing is yet installed on machine # 7

Note:
All attributes (columns) NOT NULL

PK = Primary Key
AK = Alternate Key   (Unique)
SK = Proper Superkey (Unique)
FK = Foreign Key

Using suffix # to save on screen space.
OK for SQL Server and Oracle, for others use _NO.
For example, rename MCH# to MCH_NO.

Here is another example of design using natural language, predicates, and constraints to keep a DB in high NF.


nvarchar(max) converting to varchar and table optimization

{ This is a little lengthy, perhaps, but your actual problem(s) cannot be solved by looking at execution plans. There are two main issues, and both are architectural. }

Distractions

Let's start with the items that are not your major problem areas. These are things that should be looked into as it definitely helps improve performance to use the datatypes that you need and not just a general, one-size-fits-most datatype. There is a very good reason why the different datatypes exist, and if storing 100 characters in NVARCHAR(MAX) had no negative impact on queries (or any other aspect of the system), then everything would be stored as NVARCHAR(MAX). However, cleaning up these areas won't lead to true scalamability.

to MAX, or not to MAX

  I am working with a table that has all character types set to nvarchar some of them are nvarchar(max).

Ok. This is not necessarily a bad thing, though most often there is at least one field that is of a numeric type as an ID. However, there are certainly valid cases for the scenario described so far. And there is nothing inherently bad about MAX fields since they will store the data on the data page (i.e. in row) if the data can fit there. And in that situation it should perform as well as a non-MAX value of that same datatype. But yes, a bunch of MAX type fields is a sign of sloppiness in the data modeling and is far more likely to have most (or all) of that MAX data stored in separate data pages (i.e. off row) that need an extra lookup, hence less efficient.

VARCHAR vs NVARCHAR

  We are converting all these to varchar...

Ok, but why exactly (yes, I know that info and comments that follow this statement add clarity, but I am going in order to preserve the conversational aspect for a reason). Each datatype has its place. VARCHAR is 1 byte per character and can represent 256 characters (most of the time) as defined on a single code page. While character values 0 - 127 are the same between code pages, character values between 128 and 255 can change:

;WITH chars ([SampleCharacters]) AS
(
  SELECT CHAR(42) + ' '   -- *
       + CHAR(65) + ' '   -- A
       + CHAR(126) + ' '  --
   -------------------------------
       + CHAR(128) + ' '  -- €
       + CHAR(149) + ' '  -- •
       + CHAR(165) + ' '  -- ¥, Y, ?
       + CHAR(183) + ' '  -- ·, ?
       + CHAR(229) + ' '  -- å, a, ?
)
SELECT chr.SampleCharacters COLLATE SQL_Latin1_General_CP1_CI_AS AS [SQL_Latin1_General_CP1_CI_AS],
       chr.SampleCharacters COLLATE SQL_Latin1_General_CP1255_CI_AS AS [SQL_Latin1_General_CP1255_CI_AS],
       chr.SampleCharacters COLLATE Thai_CI_AS_KS_WS AS [Thai_CI_AS_KS_WS],
       chr.SampleCharacters COLLATE Yakut_100_CS_AS_KS AS [Yakut_100_CS_AS_KS],
       chr.SampleCharacters COLLATE Albanian_CS_AI AS [Albanian_CS_AI]
FROM   chars chr;

Please note that it is possible for VARCHAR data to take up 2 bytes per character and represent more than 256 characters. For more information on Double-Byte Characters Sets, please see the following answer: Storing Japanese characters in a table .

NVARCHAR is stored as UTF-16 (Little Endian) and is either 2 or 4 bytes per character, which can represent the full Unicode spectrum. So, if your data will need to ever store more characters than can be represented by a single code page, then switching to VARCHAR won't truly help you.

Prior to converting to VARCHAR, you need to make sure that you are not storing any Unicode characters. Try the following query to see if there are any rows that cannot be converted to VARCHAR without losing data:

SELECT tbl.PKfield, tbl.SubType
FROM   dbo.[Listings] tbl
WHERE  tbl.SubType <> CONVERT(NVARCHAR(MAX), CONVERT(VARCHAR(MAX), tbl.SubType))

To clarify how NVARCHAR works: the max length of an NVARCHAR field is the number of 2-byte characters. Hence, NVARCHAR(50), will allow for a maximum of 100 bytes. How many characters will fit into that 100 bytes depends on how many 4 byte characters there are: none will allow you to fit in all 50 characters, all characters being 4-bytes will only fit 25 characters, and many combinations between.

Another thing to consider regarding space taken up by VARCHAR vs NVARCHAR: starting in SQL Server 2008 (Enterprise and Developer editions only!) you can enable Row or Page compression on Tables, Indexes, and Indexed Views. For situations where much of the data within an NVARCHAR field can actually fit within VARCHAR without any data loss, compression will allow for characters that do fit into VARCHAR to be stored as 1 byte. And only characters that require either 2 or 4 bytes will take up that space. This should remove one of the larger reasons that people often choose to stick with VARCHAR. For more info on Compression, please see the MSDN page for Creating Compressed Tables and Indexes. Please note that data in MAX datatypes that is being stored off-row is not compressible.

Real Areas of Concern

The following areas should be addressed if you want this table to be truly scalable.

Problemo Numero Uno

  ...and specifying a character width based upon the actual usage in production. The production data uses a range of 2 characters up to 900 characters of actual used width for any given column. We are going to add padding of 10% when applicable.

Uh, what? Have you added all of those values up? Given how many MAX fields you have, it is possible that 1 or more of those fields has 900 characters, and even though that should equate to 1800 bytes, the value stored on the main data page is just 24 bytes (not always 24 as the size varies related to several factors). And that could be why there are so many MAX fields: they couldn't fit in another NVARCHAR(100) (taking up to 200 bytes), but they did have room for 24 bytes.

If the goal is to improve performance, then converting the full strings to codes is, on some levels, a step in the right direction. You are drastically reducing the size of each row which is more efficient for the buffer pool and disk I/O. And shorter strings take less time to compare. That's good, but not awesome.

If the goal is to dramastically improve performance, then converting to codes is the wrong step in the right direction. It still relies upon string-based scans (with 30 indexes and 140 columns, there should be a lot of scans, unless most of the fields are not used for filtering), and I assume that those will be case-insensitives scans at that, which are less efficient than if they were case-sensitive or binary (i.e. using a case-sensitive, or binary, collation).

Additionally, converting to string-based codes ultimately misses the point of how to properly optimize a transactional system. Are these codes going to be entered in on a search form? Having people use 'S' for [SubType] is far less meaningful than searching on 'Single Family'.

There is a way to retain your full descriptive text while both reducing the space used and greatly speeding up queries: create a lookup table. You should have a table named [SubType] that stores each of the descriptive terms distinctly and has a [SubTypeID] for each one. If the data is part of the system (i.e. an enum), then the [SubTypeID] field should not be an IDENTITY field as the data should get populated via a release script. If the values are entered by end users, then the [SubTypeID] field should be an IDENTITY. In both situations:

[SubTypeID] is the Primary Key.
Most likely use INT for [SubTypeID].
If the data is internal / system data, and you know that the max number of distinct values will always be under 40k, then you might could get away with SMALLINT. If you start numbering at 1 (either manually or via IDENTITY seed), then you get a max of 32,768. But, if you start at the lowest value, -32,768, then you get the full 65,535 values to use.
If you are using Enterprise Edition, then enable Row or Page Compression
Descriptive text field can be called either [SubType] (same as the table name), or maybe [SubTypeDescription]
UNIQUE INDEX on [SubTypeDescription]. Keep in mind that indexes have a max size of 900 bytes. If the max length of this data in Production is 900 characters, and if you do need NVARCHAR, then this might work with compression enabled, OR use VARCHAR only if you definitely do NOT need to store Unicode characters, ELSE enforce uniqueness via an AFTER INSERT, UPDATE trigger.
[Listings] table has [SubTypeID] field.
[SubTypeID] field in [Listings] table is Foreign Key, referencing [SubType].[SubTypeID].
Queries can now JOIN the [SubType] and [Listings] tables and search on the full text of [SubTypeDescription] (case-insensitive, even, same as current functionality), while using that ID to perform a very efficient search on the indexed FK field in [Listings].

This approach can (and should) be applied to other fields in this table (and other tables) that behave similarly.

Problemo Numero Dos

  A large overhaul of this table that consists literally 140 (nvarchar) columns, 11 being MAX. I am dropping 30 indexes and recreating them afterwards.

If this is a transactional system and not a data warehouse, then I would say that (again, generally), 140 columns is too much to handle efficiently. I highly doubt that all 140 fields are used at the same time and/or have the same use cases. The fact that 11 are MAX is irrelevant if they need to contain more than 4000 characters. BUT, having 30 indexes on a transactional table is again a bit unwieldy (as you are clearly seeing).

Is there a technical reason why the table needs to have all 140 fields? Can those fields be split into a few smaller groups? Consider the following:

Find the "core" (most important / frequently used) fields and put them into the "main" table, named [Listing] (I prefer to keep with singular words so that the ID field can easily be just TableName + "ID").
[Listing] table has this PK: [ListingID] INT IDENTITY(1, 1) NOT NULL CONSTRAINT [PK_Listing] PRIMARY KEY
"secondary" tables are named as [Listing{GroupName}] (e.g. [ListingPropertyAttribute] -- "attributes" as in: NumberOfBedrooms, NumberOfBathrooms, etc).
[ListingPropertyAttribute] table has this PK: [ListingID] INT NOT NULL CONSTRAINT [PK_ListingPropertyAttribute] PRIMARY KEY, CONSTRAINT [FK_ListingPropertyAttribute_Listing] FOREIGN KEY REFERENCES([Listing].[ListingID])

notice no IDENTITY here
notice PK name is the same between "core" and "secondary" tables
notice PK and FK to "core" table is the same field

"core" [Listing] table gets both [CreatedDate] and [LastModifiedDate] fields
"secondary" tables only get [LastModifiedDate] field. The assumption is that all secondary tables get their rows populated at the same time as the "core" table (i.e. all rows should always be represented across all "secondary" tables). Hence the [CreatedDate] value in the "core" [Listing] table would always be the same across all "secondary" tables, on a per-row basis, so no need to duplicate it across the "secondary" tables. But they can each be updated at different times.

This structure does increase the number of JOINs that many queries will need, though one or more Views can be created to encapsulate the more frequently used JOINs, for coding convenience. But on the plus side:

when it comes to DML statements, there should be much less contention since the "core" table should get most of the updates.
most updates will take less time since they are modifying smaller rows.
index maintenance on each of the new tables (both "core" and "secondary" tables) should be faster, at least on a per-table basis.

Recap

The current model is designed to be inefficient, and it seems to be fulfilling that design goal (i.e. it be slow). If you want the system to be fast, then the data model needs to be designed to be efficient, not merely less-inefficient.


How to reuse dataset in that stored procedure

This is what i would do without knowing the table structure.

Otherwise I might create the temporary table with the correct columns first, and insert in it afterwards (and not use select * to insert but instead select the needed columns from the emp table).

I used a temporary table instead of a table variable as to (in most cases) improve query performance.

 SELECT *
 INTO  #FullTimeEmpResult
 FROM emp WHERE TYPE='FullTime';

 SELECT * FROM #FullTimeEmpResult WHERE salary >5000;

 SELECT * FROM #FullTimeEmpResult WHERE YearOfExperience> 5;

 DROP TABLE #FullTimeEmpResult;


If I had to guess it's the object id for sys.objects. So

SELECT * FROM sys.objects WHERE object_id = <obj_id>

Specifically look at the name column (object name) and the type column (type of the object, for example U for user table, P for stored procedure etc).


How to select functions that belong in a given extension in PostgreSQL?

All credit for this answer goes to LaurenzAlbe who hinted the solution in the comment. The query that lists functions created by an extension is

SELECT e.extname, ne.nspname AS extschema, p.proname, np.nspname AS proschema
FROM pg_catalog.pg_extension AS e
    INNER JOIN pg_catalog.pg_depend AS d ON (d.refobjid = e.oid)
    INNER JOIN pg_catalog.pg_proc AS p ON (p.oid = d.objid)
    INNER JOIN pg_catalog.pg_namespace AS ne ON (ne.oid = e.extnamespace)
    INNER JOIN pg_catalog.pg_namespace AS np ON (np.oid = p.pronamespace)
WHERE d.deptype = 'e'
ORDER BY 1, 3


You have to remove that attribute via MySQL

APPROACH #1

This only works for MySQL 5.7

ALTER USER root@localhost MAX_CONNECTIONS_PER_HOUR 0;

If you have other root users (like root@'127.0.0.1' and root@'%'), you will have to run a separate ALTER USER for each root user.

APPROACH #2

UPDATE mysql.user SET max_connections = 0 WHERE user='root';
FLUSH PRIVILEGES;

Log out and login to MySQL and you should notice the difference

NOTES

If you are logged out, you have to come back in an hour and try again.

If you can't wait one hour and you can restart mysql, you could enable --skip-grant-tables

service mysql restart --skip-grant-tables --skip-networking

Next, you can login and perform APPROACH #2

Then, restart mysql normally

service mysql restart

UPDATE 2016-11-25 14:49 EDT

With Windows, it's a little more involved.

Assuming you installed MySQL in Windows using the MSI Installer, there is no my.ini.

Here is where it gets crazy, but here we go ...

STEP 01

Login to the Windows Command Line as Administrator and run

C:\> net stop mysql

STEP 02

Find the folder where mysqld.exe is located on the Windows.

Usually, it would be something like C:\ProgramData\MySQL\MySQL Server 5.6

Start mysqld and login (no password needed)

cd "C:\ProgramData\MySQL\MySQL Server 5.6"
start mysqld --skip-grant-tables --skip-networking
mysql

STEP 03

Apply APPROACH #2

UPDATE mysql.user SET max_connections = 0 WHERE user='root';
FLUSH PRIVILEGES;
exit

STEP 04

Shutdown mysqld manually

mysqladmin shutdown

STEP 05

Start mysqld as a service

net start mysql

GIVE IT A TRY !!!


How to manage stored procedure dependencies in SSDT?

The order in which Stored Procedures are created in SQL Server should not, in most cases**, cause any errors. If dependent objects do not exist at the time of CREATE PROCEDURE or ALTER PROCEDURE, you should merely get a warning about SQL Server not being able to register the dependency in the meta-data (i.e. sys.sql_expression_dependencies).

If you are getting an actual error, then most likely you have a certain build option set to do this. Go to Project Properties and then to the Build tab. There is a check-box for "Treat Transact-SQL warnings as errors". You probably have that checked. You can either un-check that option OR if you like having it to catch other warnings, then trying specifying the warning in the "Suppress Transact-SQL warnings:" text field.

** Linked Servers / OPENQUERY / OPENROWSET, by default, attempt to validate the remote schema unless you alter the Linked Server definition to specify LazySchemaValidation.


Normalized Data Store - Confused with prefixes to use

There is always at least something else you should also know and almost equally, always something else you should be consciously putting a stop to.  Specifically in the context of data warehousing, which is a relatively fledgling sector, leveraging relatively new technologies.

In regards to what I've seen in the real world, walking into a company for the first time and seeing what I'm understanding about your design would be genuinely tear-inducing:  Tears of joy and relief.  From the outset, you are well on your way to beginning what appears to be a well thought-out ( well engineered ) ETL / data warehousing system.  As with the implementation of any software product, your mileage may vary as the solution grows and is consumed by the business, but fundamentally, you are on The Right Track™ ( and yes, you know what a natural key is ).

I've found there to be a number of challenges with these type of solutions, which I will touch upon to reinforce some of your decisions and perhaps lend some insight into the road ahead of you.  Firstly, the number of times I've found myself in a predicament on account of a developer ( even fellow database administrators / data professionals ) misunderstanding the context of a control column ( using, for example running a process against the DateInserted column, a mere time stamp of insertion, over the DateReceived or similarly named column, intending to relate a row to a particular date of occurance ), that while I agree completely with the cautions @Aaron Bertrand raises, I feel that the prefixes for your control columns could actually be leveraged as a sort of flag to help prevent their misuse.  Obvious should be obvious of course, but much like writing code in general, explicit is preferable.  That said, I would almost certainly leave such prefixes out of the indexes and such ( probably even keys - PK types can and should stay in my opinion, but unless there's a real threat of DWD_SubCategories and DWF_SubCategories existing in the same schema, they really are just fluff ).  I think the concern about the DWD and DWF prefixes is valid, but they'll be living in the [NDS] catalog and would serve to indicate intent, making it completely fine to use the nomenclature in that manner.

The second ( and perhaps most infuriating ) challenge is one of cross-training your coworkers.  All of the software engineering, usage flags and design practice rules are completely for naught if your striving-for-paycheque-over-excellence colleagues get involved and do their less than very best ( or to be fair, are even just simply having a bad day ).  Do keep in mind that large projects generally have many fingers in the pot, so it is imperative that those fingers are behaving well.

The last thing I'll touch on here is to always keep in mind the actual value of any ETL system to a business.  Of the Extract, Transform and Load paradigm, the first and final letters have absolutely no business value, so you will want to work on making the development and maintenance of both the Extract and Load processes as minimal as possible - the "real" work will be done in the Transform phase, so you will want to automate the E and L steps as much as possible so that you can focus on making ( and keeping ) your solution valuable to the business unit by actively working on the transforms.

All of that said, I've only had the opportunity to work on a handful of different warehousing solutions so perhaps a more knowledgeable user could step in and remove my foot from my mouth if I need correcting.  As I said initially, this is one of those areas where one can always learn or unlearn something, and I am absolutely no exception.

Oh, one more thing ( and probably the most important ) - Unit Test!  Once your E and L are working as intended and you've had the opportunity to put a few domains through your T solution, get somebody to vet the results.  If they're good, save the result set somewhere, so that when you make changes ( and you will, without a doubt ) you can ensure you haven't broken something, somewhere else.  Again, automate this process as much as you possibly can ( it's another 0-value process to the business, until they go without it at least ;) ).  I generally set up a separate schema or catalog for this purpose.

Hopefully some of what I've said will be useful to you!

As an update, @Aaron Bertrand's schema separation seems like it would be quite a good way to avoid unnecessary prefixing as well, so certainly consider that ( I know I will haha ).


Combining multiple rows into a single row with multiple columns

SELECT t1.post_id, t1.meta_value, t2.meta_value
FROM postmeta t1, postmeta t2
WHERE t1.post_id = t2.post_id
  AND t1.meta_key = '_wp_attachment_metadata'
  AND t2.meta_key = '_thumbnail_id'

or

SELECT post_id,
       MAX(CASE WHEN meta_key = '_wp_attachment_metadata'
                THEN meta_value END),
       MAX(CASE WHEN meta_key = '_thumbnail_id'
                THEN meta_value END)
FROM postmeta
GROUP BY post_id

PS. First query skips post_id, if it have no one (any) of meta-keys, second query gives NULL for according output field in such case.


Insert with function result - prevent duplicates

In my test scenario I think I am able to solve this problem by writting:

  insert into XXXX with (tablock, xlock) (A,B,C,D,E) .....

At least now I am not able to replicate duplicated entries, but I am not sure if I am creating some problems where I am not aware.

If you think this is not a solution, please let me know.


The real reason why you can't do as you ask (limit the memory) is because MongoDB doesn't manage the memory it uses directly - it lets the OS do it.  MongoDB just memory maps all its data and then has the OS page it in and out of memory as needed.  As a result, there is no direct management of the amount used possible until MongoDB implements this in a completely different way, or the OS allows it (not possible in Linux since the 2.4 days).

The only way to truly segregate resources at present is to use a virtualization solution and isolate MongoDB in its own VM.  Yes, there are overheads involved (though hypervisors have gotten a lot better), but at the moment that is the price to be paid for that level of resource control.

In terms of the OOM Killer, even with no other processes on the host, as long as your data set and indexes overall exceed available memory, MongoDB can hit OOM Killer issues.  This is because of how the data gets paged out of memory - if there is no memory pressure (nothing else wants Resident memory), and you keep adding/touching new data and indexes, then eventually it will grow to consume all available RAM.  Hence the recommendation to always configure some swap when running MongoDB:

https://docs.mongodb.com/manual/administration/production-notes/#swap

Of course, LRU data will be paged out first, other processes can take up the res mem also, but the concept still applies unless you load your data set into memory and then it stays static.  Best thing to do if you are worried is get it into MMS and track the usage over time:

http://mms.mongodb.com

Update: August 2015

Since I wrote this answer things have moved on somewhat and the information is a little out of date.  For example, Linux now has cgroups and related technologies (Docker containers for example) that have matured to the point that they allow you to better isolate and limit the resources (including memory) consumed by any process in a production environment, even one that uses memory mapping like MongoDB.

Additionally, with the advent of new storage engines beyond MMAP like WiredTiger in MongoDB 3.0+ you can use built-in functionality to limit the cache size for MongoDB.  Hence, the RAM requirements now really do depend on how you choose to configure MongoDB, what environment you run it in, and what storage engine you choose.


How to gracefully handle MySQL deadlocks involving SAVEPOINT?

First, some observations/comments/requests:

Please provide SHOW ENGINE INNODB STATUS;, in case it sheds any further light on the issue.
I usually find that SAVEPOINT can be avoided by rethinking the entire transaction.
Since SAVEPOINT seems to be critical to the problem, and it seems to be "broken", please file a bug at http://bugs.mysql.com .
Deadlocks will happen, no matter what you do to avoid them.  It is best to "live with them".  That is, catch them, then rerun the entire transaction.  (SAVEPOINT complicates the code for such.)

On the constructive side, I would recommend improving the many:many OU table by the tips in http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table -- In particular, its id is both wasteful and possibly contributing to the problem.


Determining Homogeneity by Common Foreign Keys

For the table A (with B_id) and table B (with id)
Count of Primary Keys By Foreign Key
SELECT FK,COUNT(1) PKCount FROM
(SELECT IFNULL(B.id,0) FK
FROM A LEFT JOIN B ON A.B_id = B.id) K
GROUP BY FK;

If FK is 0, then PKCOUNT is a count of Primary Keys that do not have a Foreign Key
To be 100% homogenous, FK should never show up as zero(0) (In a Perfect World).
Count of Foreign Keys Used By Primary Keys
SELECT B.id,IFNULL(FK_Count,0) FKCount
FROM B LEFT JOIN
(SELECT B_id id,COUNT(1) FK_Count FROM A GROUP BY B_id) K
USING (id) GROUP BY B.id;

If FKCount is 0, then B.id is a Foreign Key Not Used By Any Primary Key
To be 100% homogenous, FKCount should never show up as zero(0) (In a Perfect World).


You don't say what DB you are using, but that syntax looks like MS SQL Server.

Also you do not properly state the table structure - the function refers to VehicleID which doesn't appear in you sample data and you don't say what the data types or what indexes and keys are present. All of this could be significant.

You are pulling a bunch if data into a table variable and processing it there which could be a problem - if there are many rows per vehicle this will hit disk and cause slowness due to that IO.

With SQL Server 2012 or later this is quite easy to do using window functions:

SELECT ID, DATEDIFF(MINUTE
            , TrackTime
            , LEAD(TimeTrack) OVER (ORDER BY TimeTrack)
            )
FROM Tracking
WHERE VehicleID = @VehicleID
AND TrackTime BETWEEN @starttime AND @endtime
AND Speed > @speedlimit
ORDER BY TimeTrack

For this to be efficient you'll at very least need an index covering VehicleID. Better still either one covering VehicleID & TrackTime (perhaps including speed & ID), or have VehicleID & TrackTime be your clustered index (though for a definite recommendation we need to know a lot more about the other queries the table sees: picking your clustered index to optimise this query might show others).

I'd your indexes are right this should pass over the data once, with and index seek rather than a scan (or worse, a table scan) and no spooling to disk unless the data per vehicle is really massive.

If you want results for multiple vehicles then you need to tell the lead function to split by VehicleID so it becomes LEAD(TimeTrack) OVER (PARTITION BY VehicleID ORDER BY TimeTrack), and add VehicleID to the main ordering clause too.


how to execute unix commands within nzsql?

The documentation for nzsql states that to shell-out you just have to use blackslash pling, followed by the command:

\! command

If you need to know how to test for file size in a shell script, you can use something similar to:

if [[ -f $file ]] && [[ -s $file ]]; then
  echo "File exists and is greater than 0 bytes" ;
  rm -f $file
fi

-f checks that it exists, -s checks if it's greater than 0 bytes.


Optimisation and Performance Mysql , Zend Framework 2 and Doctrine 2?

Start with the database and work your way up. Check out the queries you run using the command line before sending them through a framework. You can activate the general log to check every query that's sent to the database if you want to check exactly what, exactly, is arriving at your server after having used the framework. Frameworks can be good (relieving you of tedious chores), but they can be the source of (unpleasant) surprises.

You don't mention what version of MySQL you are using, but if 5.5 and above, you can make use of the Performance Schema (P_S). The P_S is MySQL's (embryonic) implementation of an Oracle Wait Interface style system for instrumenting the database. It tells you where time is being spent and this allows you to concentrate on the major problems and address them with hardware (if necessary - i.e. more/better RAID if disk-bound) or query tuning (if one/some queries are taking a lot of time). You should try and use at least 5.6 for this (is there any reason not to?).

If you're having problems with specific queries, you can post back here (new question) with DDL (SHOW CREATE TABLE my_table) and sample data (INSERT INTO my_table VALUES(...)). You can use SQLFiddle and even provide a download (you can hash sensitive data).

Sorry not to be able to provide anything more specific, but your question is quite broad and general - as I say, when you run into specific issues, post back here and we might be able to help.

[EDIT in response to OP's comment]

I'm assuming that this doctrine framework generates queries, but the answer is no, you won't have to generate indexes through the framework. My experience (not huge) with frameworks is that they will generate queries which give you the data you want.

The indexes are normally left up to the developer because many/most/nearly all of these frameworks are designed to work with multiple RDBMSs (this is not necessarily a good thing!), so they do the bare minimum (some would say the lowest common denominator) of SELECT FROM, INSERT INTO and UPDATE which is fairly standard SQL. However, the syntax for creating indexes varies from server to server (as for many other things), so the frameworks in general don't handle this well.

Ensure that your framework is giving you the correct data, then see if adding indexes can help speed up your particular application, because no framework can possibly anticipate all use cases (table sizes, RAM, my.cnf... the list goes on).

All I can say is that now if you're having a problem with a particular query, post a new question here giving the details I mentioned. Check the performance of the framework generated query and then check with your own choice of indexes. Don't forget that a system is made up of many queries and that modifying one may adversely affect others. Indexes can provide enormous benefits, but they do have downsides such as slowing down UPDATEs and INSERTs. Ideally, after the first stages above, you should have some sort of system for replaying your new queries with a simulated system load - see here. Good luck with your system.


Where are Statistics physically stored in SQL Server?

Found them.

Create a table with a simple stats object.

CREATE DATABASE splunge;
GO
USE splunge;
GO
CREATE TABLE dbo.foo(bar INT, munge INT);
GO
CREATE STATISTICS x ON dbo.foo(bar);
CREATE STATISTICS y ON dbo.foo(munge);
GO
INSERT dbo.foo SELECT s1.[object_id], s2.[object_id]
  FROM sys.objects AS s1
  CROSS JOIN sys.objects AS s2;
GO
UPDATE STATISTICS dbo.foo;
GO

Connect using the DAC (ADMIN:Server[\instance]).
Run the following queries:

DBCC SHOW_STATISTICS('dbo.foo', 'x') WITH STATS_STREAM;
DBCC SHOW_STATISTICS('dbo.foo', 'y') WITH STATS_STREAM;

SELECT name, imageval
  FROM sys.stats AS s
  INNER JOIN sys.sysobjvalues AS o
  ON s.object_id = o.objid
  AND s.stats_id = o.subobjid
WHERE
  s.object_id = OBJECT_ID('dbo.foo');

You will note that imageval for each stats object isn't the same as the stats blob, but it does contain the stats blob - it's just offset. On my system it yielded this for x (I've obviously truncated a fair bit of bits):

0x0100...bunch of chars...000007000000C4E1BE00EEA0...rest the same
                            0x07000000C4E1BE00EEA0...rest the same

And this for y:

0x0100...bunch of chars...430007000000C7E1BE00EEA0...rest the same
                            0x07000000C7E1BE00EEA0...rest the same

The same was true for index-based statistics.

You could probably do further validation of this with a series of queries using DBCC commands. First, find out the pages that are involved with the clustered index on sys.sysobjvalues (substitute your database name):

DBCC IND('splunge', 'sys.sysobjvalues', 1);

The result will list a bunch of pages, you're interested in the ones of PageType = 1. With a new database, you should be able to find this info on one of the pages with the highest PagePID values. E.g. on my system this was page 281, so then I looked closer at that page:

DBCC TRACEON(3604);

DECLARE @dbid INT = DB_ID();

DBCC PAGE(@dbid, 1, 281, 3);

DBCC TRACEOFF(3604);

Sure enough, I found the data in slot 17:

(On larger databases, you might have to do a lot more hunting and pecking, since there's no guarantee that even a new stats object will end up on a new(er) page.)

Go ahead and try this at home, but there is a reason you need to connect with the DAC for this. I'd be curious to know, of course, what you are going to do with this information that you couldn't do with DBCC SHOW_STATISTICS output.

Note that this of course does not try to decode the STATS_STREAM to provide histogram or other information, and I could not find any evidence that the tabular output of DBCC SHOW_STATISTICS ... WITH HISTOGRAM is stored anywhere in table format. Joe Chang has some information about decoding if that's what you're after. I don't think it's something you'd want to do in a query - just use DBCC.


Data Extraction into a Sandbox

CREATE DATABASE IF NOT EXIST sms_sandbox;
CREATE TABLE IF NOT EXISTS sms_sandbox.mo_sms LIKE DB2.mo_sms;
CREATE TABLE IF NOT EXISTS sms_sandbox.mt_sms LIKE DB2.mt_sms;
#
# Drop All Indexes From the Sandbox
#
ALTER TABLE sms_sandbox.mo_sms
    DROP INDEX `ix_carrier`,
    DROP INDEX `ix_fecha_sms`,
    DROP INDEX `ix_fecha_carrier_keyword`,
    DROP INDEX `ix_msisdn`,
    DROP INDEX `ix_sms_proceso`,
    DROP INDEX `ix_sms_proceso_state`,
    DROP INDEX `ix_id_user`,
    DROP INDEX `ix_fecha_sms_user`,
    DROP INDEX `ix_varios`;
ALTER TABLE sms_sandbox.mt_sms
    DROP INDEX `ix_carrier`,
    DROP INDEX `ix_fecha_sms`,
    DROP INDEX `ix_fecha_carrier_keyword`,
    DROP INDEX `ix_msisdn`,
    DROP INDEX `ix_sms_proceso`,
    DROP INDEX `ix_id_user`,
    DROP INDEX `ix_fecha_sms_user`;
ALTER TABLE sms_sandbox.mo_sms AUTO_INCREMENT=1;
ALTER TABLE sms_sandbox.mt_sms AUTO_INCREMENT=1;
#
# Load the Data From 2013-04-19
#
INSERT INTO sms_sandbox.mo_sms
SELECT * FROM DB2.mo_sms
WHERE fecha_sms >= '2013-04-19 00:00:00'
AND fecha_sms <= '2013-04-19 23:59:59';
INSERT INTO sms_sandbox.mt_sms
SELECT * FROM DB2.mt_sms A INNER JOIN sms_sandbox.mo_sms B USING (id_MO);

Data Insertion From the Sandbox

#
# Get Maximum id_MO from DB1.mo_sms
# Increment all id_MOs by the Max Value
#
SELECT MAX(id_MO) INTO @Max_id_MO FROM DB1.mo_sms;
UPDATE sms_sandbox.mo_sms SET id_MO = id_MO + @Max_id_MO;
UPDATE sms_sandbox.mt_sms SET id_MO = id_MO + @Max_id_MO;
#
# Load the New Data into DB1
#
ALTER TABLE DB1.mo_sms DISABLE KEYS;
INSERT INTO DB1.mo_sms SELECT * FROM sms_sandbox.mo_sms;
ALTER TABLE DB1.mo_sms ENABLE KEYS;
ALTER TABLE DB1.mt_sms DISABLE KEYS;
INSERT INTO DB1.mt_sms
(`sms_proceso`,`msisdn`,`texto_sms`,`brand_id`,
`fecha_sms`,`comando_id`,`alias_desc`,`shortcode_id`,
`id_user`,`tipo_sms`,`id_MO`,`state`,`anio_sms`,`mes_sms`)
SELECT
    `sms_proceso`,`msisdn`,`texto_sms`,`brand_id`,
    `fecha_sms`,`comando_id`,`alias_desc`,`shortcode_id`,
    `id_user`,`tipo_sms`,`id_MO`,`state`,`anio_sms`,`mes_sms`
FROM sms_sandbox.mt_sms;
ALTER TABLE DB1.mt_sms ENABLE KEYS;

WARNING : Practice this on a Staging Server, Please.

SUGGESTION

All the mt_sms tables should have an index on id_MO

ALTER TABLE mt_sms ADD INDEX (id_MO);

Give it a Try !!!


Multiply several discrete values in multiple columns and then sum

This query produces your desired result for every customer in the table:

SELECT customer_id AS customer
     , string_agg(DISTINCT product_id::text, ', ') AS products
     , sum(cost * CASE p.item
                     WHEN 'BASE'  THEN 1
                     WHEN 'Foo'   THEN b.foo
                     WHEN 'Bar'   THEN b.bar
                     WHEN 'Blarg' THEN b.blarg
                  END) AS revenue
FROM   bought_products b
LEFT   JOIN price p USING (product_id)
-- WHERE  p.item = ANY ('{BASE,Foo,Bar,Blarg}')
GROUP  BY 1;

db<>fiddle here

We only need to join once. The magic happens in the CASE expression to multiply depending on the Item.

I made it a LEFT [OUTER] JOIN to keep every product in the result even without any matching price rows. Use [INNER] JOIN to remove those instead. And/or activate the commented WHERE clause to the same effect.

The row with ID 1 in the Price table stands out, btw, as the related row in Bought_Products has Foo IS NULL.

The relational design seems a bit mysterious. I hope you have good reasons.


I've changed the 'impossible dates' and I've set a rextester sample.

First group by RoomId and BookingId and calculate percentage.

(
 SELECT      b.BOO_RoomId, bd.BIL_BookingId,
             COUNT(*) as NumNights,
             COUNT(*) * 100 / (SELECT COUNT(*) FROM ___BillableDatas) AS Percent
 FROM        ___BillableDatas bd
 LEFT JOIN   ___Bookings B
 ON          b.BOO_Id = bd.BIL_BookingId
 GROUP BY    b.BOO_RoomId, bd.BIL_BookingId
) co

Then LEFT JOIN with rooms table and show all values:

SELECT      r.ROO_Name,
            COALESCE(NumNights, 0) NumNights,
            COALESCE(Percent, 0) Percent
FROM        ___Rooms r
LEFT JOIN   (
            SELECT      b.BOO_RoomId, bd.BIL_BookingId,
                        COUNT(*) as NumNights,
                        COUNT(*) * 100 / (SELECT COUNT(*)
                                          FROM ___BillableDatas
                                          WHERE BIL_Date >= '2017-02-01' AND BIL_Date < '2017-02-23'
                                          ) AS Percent
            FROM        ___BillableDatas bd
            LEFT JOIN   ___Bookings B
            ON          b.BOO_Id = bd.BIL_BookingId
            WHERE       BIL_Date >= '2017-02-01' AND BIL_Date < '2017-02-23'
            GROUP BY    b.BOO_RoomId, bd.BIL_BookingId
            ) co
ON          R.ROO_Id = co.BOO_RoomId
;

+----------+---------------+-----------+---------+
| ROO_Name | BIL_BookingId | NumNights | Percent |
+----------+---------------+-----------+---------+
| Room 1   |       1       |     5     | 71,4286 |
| Room 2   |       2       |     2     | 28,5714 |
| Room 3   |       0       |     0     |  0,0000 |
+----------+---------------+-----------+---------+


Easy way to change Column ID (primary key) of a table to Auto increment

My preferred method for a non-empty table is to use the ALTER TABLE ... SWITCH method which is a meta-data-only operation.

This is a simple test-bed setup, consisting of the initial table, named dbo.SwitchTest1, and the target table containing an IDENTITY, named dbo.SwitchTest2:

USE tempdb;
IF OBJECT_ID('dbo.SwitchTest1') IS NOT NULL
DROP TABLE dbo.SwitchTest1;
IF OBJECT_ID('dbo.SwitchTest2') IS NOT NULL
DROP TABLE dbo.SwitchTest2;
GO
CREATE TABLE dbo.SwitchTest1
(
    ID INT NOT NULL
        CONSTRAINT PK_SwitchTest1
        PRIMARY KEY CLUSTERED
    , SomeVal VARCHAR(50) NOT NULL
);
CREATE TABLE dbo.SwitchTest2
(
    ID INT NOT NULL
        CONSTRAINT PK_SwitchTest2
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , SomeVal VARCHAR(50) NOT NULL
);

INSERT INTO dbo.SwitchTest1(ID, SomeVal)
VALUES (1, 'This is a test');

Here, we use the ALTER TABLE ... SWITCH method to replace the data in dbo.SwitchTest2 table with data from dbo.SwitchTest1, while maintaining the definition of dbo.SwitchTest2 including the IDENTITY column.  Although not strictly necessary, you probably want to rename any constraints included in the definition of dbo.SwitchTest2 after the fact to match what they used to be called in dbo.SwitchTest1.  I've done that below for the primary key.

BEGIN TRANSACTION
BEGIN TRY
    ALTER TABLE dbo.SwitchTest1 SWITCH TO dbo.SwitchTest2;
    DROP TABLE dbo.SwitchTest1;
    EXEC sp_rename @objname = 'dbo.SwitchTest2'
        ,@newname = 'SwitchTest1', @objtype = 'object';
    EXEC sp_rename @objname = 'PK_SwitchTest2'
        , @newname = 'PK_SwitchTest1', @objtype = 'object';
    EXEC sp_recompile @objname = 'dbo.SwitchTest1';
    DBCC CHECKIDENT('dbo.SwitchTest1');
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION
END CATCH

In the code above, I've also added a call to sp_recompile - this causes stored procedures, triggers, and user-defined functions to be recompiled the next time that they are run. It does this by dropping the existing plan from the procedure cache forcing a new plan to be created the next time that the procedure or trigger is run.  This is A Good Thing™ since the object_id for the table dbo.SwitchTest1 table has changed.  Also, I'm running DBCC CHECKIDENT('<tablename>'); to cause the IDENTITY() column to be updated with the next valid value to be used by an INSERT operation.

Here, we're inserting a value into the SomeVal column without specifying a value for the ID column, which is now an IDENTITY column:

INSERT INTO dbo.SwitchTest1 (SomeVal)
VALUES ('This is another test');

And the results:

SELECT *
FROM dbo.SwitchTest1;


Bad news: rolling work forward in SQL Server is multi-threaded, but rollbacks are single-threaded. If you had lots of cores doing work for 11 hours, it's entirely possible that it could take dozens of hours (or several days) to do the rollback.

Try using sp_BlitzFirst @ExpertMode = 1 (disclaimer: I'm one of the authors of that open source tool), and look at the file stats section. It'll show you which data & log files have had physical reads & writes in that time span. That'll show you if your rollback is continuing to move along.

You mentioned "impacting other processes" - keep in mind that your rollback speed can be reduced by other processes running on the server. You may also want to keep an eye on your drive free space and transaction log sizes. Your transaction was a logged operation, and SQL Server will need that transaction log to stay online even after you've taken log backups. It's very conceivable that you'll run out of drive space during the rollback.

Given how big that rollback was, and depending on your RPO/RTO goals, I'd consider failing over to your disaster recovery servers or restoring from backups. I hate to lecture you now, but this is a good time to learn the lesson that you should try everything in a development environment first to measure how long it'll take. You would have been better prepared to identify that you can't move a multi-terabyte object from one filegroup to another without a lot of planning.


Rate of transactions reduced when mirror is synchronized

There are two modes for database mirroring:

High safety (synchronous) - every delete/update/insert (DUI) operation is written to the primary's transaction log, sent across the network to the mirror, written to the mirror's transaction log, and then confirmation is sent back to the primary. Transactions are inherently slower, but in a perfect world, you won't lose data when you fail over from the primary to the mirror.
High performance (asynchronous) - DUI operations are only written to the primary's transaction log, then the transaction is immediately committed. Later, the primary sends transactions to the mirror - but that traffic doesn't slow down your transactions. However, if you fail over, you can lose data.

The key is in the names: high safety is safe (but slow), and high performance is faster (but less safe.)

You've noticed that when you suspend the mirror, things get faster - that's because your mirroring is set up with high safety.

If your business is willing to lose data when the primary fails, then you can keep doing mirroring, but switch it from high safety to high performance. (You can change this at the database level at any time.)

If your business is NOT willing to lose data, then it's time to start troubleshooting which layer of mirroring is causing you problems:

Slow log file writes on the mirror, or
Slow network connection between the primary and the mirror, or
Lots of tiny transactions in the app rather than doing larger batches

Troubleshooting that is kinda beyond the scope of what I can do in an answer here though.


Gathering Read Routing information using SMO is inaccurate in SQL Server 2016

The issue was fixed in SSMS 17.3, however you'll need to load the "applocal" assembly and not the one from the GAC. In addition, in order to be "backwards compatible", the ReadOnlyRoutingList property will stay a string and act/behave just like it did before. The newly added LoadBalancedReadOnlyRoutingList (and a getter/setter with it) will have the correct representation you're looking for.

  Connect is dead...

Long live Connect!

Code Repro (cut up for readability) To See New Properties and Methods:

#Load the Applocal assembly from SSMS 17.4
[System.Reflection.Assembly]::LoadFrom('C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\Microsoft.SqlServer.SMO.dll')

#Create a new server instance hitting the listener named SQL2016Listen
$SI = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "SQL2016Listen"

#Get the list of Availability Replicas for an Availability Group called "SeedingTest"
$ARs = $SI.AvailabilityGroups | where {$_.Name -eq "SeedingTest"} | select AvailabilityReplicas

#Grab the first replica in the list of replicas and show all of the members that have "load" somewhere in the name
$ARs.AvailabilityReplicas[0] | get-member | where {$_.name -like "*load*"} | select name


Order questions depending on answer votes

Something like:

SELECT q.*, SUM(a.votes_up) - SUM(a.votes_down) AS TotalVotes
FROM Questions AS q
JOIN Answers AS a ON a.QuestionID = q.QuestionID
GROUP BY q.QuestionID, q.Question, q.AnyOtherFieldFromThatTable
ORDER BY TotalVotes DESC;

I would encourage you to include q.QuestionID (or whatever you call that field) in your GROUP BY clause, for performance reasons.


Does sp_droparticle @force_invalidate_snapshot=1 invalidate the entire snapshot?

The idea of a snapshot is a point-in-time copy of the entire publication, so once you make a change that affects the snapshot, it's no longer valid in its entirety.

From here:

  Dropping an article from a publication involves dropping the article and creating a new snapshot for the publication. Dropping an article invalidates the current snapshot; therefore a new snapshot must be created.

Once you call sp_droparticle and invalidate the snapshot, the publication cannot be subscribed to again until a new snapshot is generated.


Oracle dbms_scheduler: Can I create a job that will not overlap with itself?

yes, you can, this in fact is default behaviour. See Database Administrator’s Guide chapter 29 Scheduling Jobs with Oracle Scheduler

  Immediately after a job starts, the repeat_interval is evaluated to
  determine the next scheduled execution time of the job. While this
  might arrive while the job is still running, a new instance of the job
  does not start until the current one completes.


How to grant select on a view ... based on IP adress?

You could use the session information function inet_client_addr(), with limited reach. It reports the IP the server is receiving the current connection from. This is only useful with direct connections to the database, though.

As proof of concept:

SELECT CASE WHEN inet_client_addr() << '192.168.1.32/30'::inet
         THEN 'foo'
         ELSE 'bar'
       END AS odd_column

<<.. "is contained within" operator.

As @Craig mentioned in a comment, consider the security_barrier option for a view like that:

CREATE VIEW my_odd_view WITH (security_barrier) AS ...

Details in the manual here and here.

The whole idea is rather dubious, though. The proper way would be to use distinct roles and distinct views with grants only for privileged roles.


I'm leaving this as an answer, even though it's mostly an Extended Comment®.

You don't mention the index definitions, or the types of columns you're trying to index. You say the table is 200 columns, but unless you're creating a clustered index, that shouldn't matter. Unless you're trying to create a nonclustered index on 200 columns. If so, please reconsider.

Given that you're doing this across many different versions, it's likely that they're all on different hardware as well. In the case of creating indexes, hardware and existing indexes will have a dramatic impact on how long it takes to complete. It's not like you can tune the query behind index creation. Likewise, the ability for Enterprise Edition to parallelize index creation can be an important factor.

Aside from hardware, system concurrency can play a part as well, either via blocking or overall resource usage. You don't mention if these are issues, so again it's a blind spot for anyone trying to answer.

In newer versions of SQL Server, you can choose to sort index creation in tempdb. This can help, as long as tempdb isn't Full Metal Potato© across your different environments.

Finally, you don't mention the recovery model of your databases. Since CREATE INDEX can be minimally logged, it may be worth switching to SIMPLE or BULK LOGGED recovery model if possible. This will depend on your RPO and RTO goals, and if they can be dismissed for a window to create the indexes.

A quick comment on step 2 that relates a bit to the comment above: If you create a table with no indexes, then do your data load and create indexes, it will likely be faster. Especially in SIMPLE or BULK LOGGED, you can use the TABLOCK hint to potentially get minimal logging on the insert as well as the index creation.

Your question will probably be closed as too broad, but I hope you find this Extended Comment® helpful.


differences in the authentication scheme - could it be because of Kerberos?

When the auth_scheme is SQL, that means the user is using SQL authentication, which does not, and cannot use Kerboros.

When a user that is using Windows authentication connects, authentication will first try to use Kerberos.  If Kerberos fails for some reason, authentication will fall back to NTLM.  Probably the most common cause of Kerberos failure is that the correct SPNs are not present in Active Directory (see Register a Service Principal Name for Kerberos Connections.


The binlog is a history of the writes to the database.  It is vital for replication.  It can also be used in certain "incremental" backup techniques.

The transaction log (usually in files iblog1 and iblog2) is vital to InnoDB for multiple reasons:  ROLLBACK, crash recovery, and possibly more.

Manual page about binlog
non-InnoDB logs

Redo log

There are lots of web pages discussing these files from various viewpoints.

There are many ways to do "backup".


Identity/Primary Key columns pattern for performance in SQL?

On tables with a large number of rows being inserted having a ever-increasing clustered index generally improves write performance as it ensures that new records are added together "at the end" of the table. (Note that clustered indexes and primary keys are not the same thing, you can actually have a different primary key from your clustered index. Its the clustered index which matters here, not the primary key).

If you have another auto-incrementing column in your table then you could use this as your clustered index and keep your primary key the same, however your clustered index needs to be unique (so you can't use a DATETIME), which generally means an auto-incrementing column, which may as well be your primary key.

That said, you should run some performance / stress tests and try it for yourself. In the past I worked on a system which intentionally clustered on a GUID on a frequently updated table, the theory being that spreading out the writes actually reduced lock contention and improved performance (I wasn't part of that change so to be honest I was always kind of skeptical). Try it out and see which is faster with your data.

To answer your second question, looking up a user by its username should be very fast with proper indexing - its possible that clustering the the username could improve performance, however its going to be very marginal.


How can I check for the lock count and ignore all locks which are only N seconds old?

You can't... but I think you're asking the wrong question.

PostgreSQL has many different kinds of locking. The three main ones visible to users are:

Row locks
Heavyweight locks
Advisory locks

A row lock is a lock taken by UPDATE, DELETE, SELECT ... FOR UPDATE, SELECT ... FOR SHARE, etc, indicating a claim on a row. You cannot see these in the pg_locks view and they don't consume resources. This is what "a single DML statement can easily obtain millions of locks" refers to.

Heavyweight locks are what people usually mean when they say "lock" without qualifying with the lock type. They're seen in pg_locks. There are many kinds: locks on relations (tables, views, etc); locks to allow one transaction to wait on another, etc. They're only "heavyweight" compared to PostgreSQL's internal lightweight locking mechanism (LWLocks); you don't have to worry unless you start exceeding max_locks_per_transaction.

Advisory locks are a utility locking mechanism for applications. They're done with function calls. They're visible in pg_locks.


Without seeing code, it is pretty hard to say conclusively what is happening.  Although, most likely the IDENTITY value is being cached, causing gaps in the value after SQL Server is restarted.  See https://stackoverflow.com/questions/17587094/identity-column-value-suddenly-jumps-to-1001-in-sql-server for some good answers and info about that.

A simple INT field can hold values up to 2,147,483,647.  You can actually start the identity value at -2,147,483,648, giving a full 32 bits of values.  4 Billion distinct values.  I doubt very much you're going to run out of values to use.  Assuming your application is consuming 1,000 values for each actual row added, you'd need to be creating nearly 12,000 rows per day every day to run out of IDs in 6 months assuming you started the IDENTITY value at 0, and were using an INT.   If you were using a BIGINT, you would have to wait 21 million centuries before you ran out of values if you wrote 12,000 rows per day, consuming 1,000 "values" per row.

Having said all that, if you wanted to use BIGINT as the identity field data type, there is certainly nothing wrong with that.  That'll give you for all intents-and-purposes, a limitless supply of values to use.  The performance difference between an INT and a BIGINT is practically non-existent on modern 64-bit hardware, and highly preferable over for-instance using NEWID() to generate GUIDs.

If you wanted to manage your own values for the ID column, you could create a key table, and provide a pretty bulletproof way of doing that using one of the methods shown in the answers on this question:  Handling concurrent access to a key table without deadlocks in SQL Server

The other option, assuming you're using SQL Server 2012+, would be to use a SEQUENCE object to get ID values for the column.  However, you'd need to configure the sequence to not cache values.  For example:

CREATE SEQUENCE dbo.MySequence AS INT START WITH -2147483648 INCREMENT BY 1 NO CACHE;

In answer to your boss' negative perception of "high" numbers, I would say what difference does it make?  Assuming you use an INT field, with an IDENTITY, you could in fact start the IDENTITY at 2147483647 and "increment" the value by -1.  This would make absolutely no difference to the memory consumption, performance, or disk space used since a 32 bit number is 4 bytes, no matter if it is 0 or 2147483647.  0 in binary is 00000000000000000000000000000000 when stored in a 32-bit signed INT field.  2147483647 is 01111111111111111111111111111111 - both numbers take precisely the same amount of space, both in memory, and on disk, and both require precisely the same amount of CPU operations to process.  It is far more important to get your application code designed correctly than to obsess about the actual number stored in a key field.

You asked about the pros and cons of either (a) using a larger-capacity ID column, such as a BIGINT, or (b) rolling your own solution to prevent ID gaps.  To answer these concerns:

BIGINT instead of INT as the data-type for the column in question.  Using a BIGINT requires double the amount of storage, both on-disk, and in-memory for the column itself.  If the column is the primary key index for the table involved, each and every non-clustered index attached to the table will also store the BIGINT value, at twice the size of an INT, again both in-memory and on-disk.  SQL Server stores data on disk in 8KB pages, where the number of "rows" per "page" depends on the "width" of each row.  So, for instance, if you have a table with 10 columns, each one an INT, you'd be approximately able to store 160 rows per page.  If those columns where instead BIGINT columns, you'd only be able to store 80 rows per page.  For a table with a very large number of rows, this clearly means I/O required to read and write the table will be double in this example for any given number of rows.  Granted, this is a pretty extreme example - if you had a row consisting of a single INT or BIGINT column and a single NCHAR(4000) column, you'd be (simplistically) getting a single row per page, whether you used an INT or a BIGINT.  In this scenario, it would not make much appreciable difference.
Rolling your own scenario to prevent gaps in the ID column.  You'd need to write your code in such a way that determining the "next" ID value to use does not conflict with other actions happening to the table.  Something along the lines of SELECT TOP(1) [ID] FROM [schema].[table] naively comes to mind.  What if there are multiple actors attempting to write new rows to the table simultaneously?  Two actors could easily obtain the same value, resulting in a write-conflict.  Getting around this problem requires serializing access to the table, reducing performance.  There have been many articles written about this problem; I'll leave it to the reader to perform a search on that topic.

The conclusion here is: you need to understand your requirements and properly estimate both the number of rows, and the row width, along with concurrency requirements of your application.  As usual, It Depends™.


Each node holds 50% of data on 2 nodes with replication_factor=1

Replication Factor 1. Assuming that the cluster is balanced. There is 1 copy of your data (1 replica) in the cluster. Since you have 2 nodes and one replica, then each node will have 1/2 of the data of half a replica each. When you shut down 1 node, you lose access to 1/2 of the data.

Replication factor 2. There are 2 replicas of the data in the cluster. 2 nodes and 2 replicas -> each node can have 1 full replica. When you shut down 1 node, the other node still has a complete replica.