Determine the size of a hashed varchar column

Well - it appears that a fellow Stack Exchange member (Richard Marskell - Drackir)  has figured this out here.

I've copied his answer below:

  You should use the binary datatype. You can use binary instead of
  varbinary because the hash function will always return the same number
  of bytes for the same type of hash (e.g. MD5, SHA1, etc.). This will
  cut down on the (slight) overhead required to manage a variable length
  binary (varbinary) column.

  In terms of what size to make it, you can run this query to check the
  length of each hash type:

SELECT  DATALENGTH(HASHBYTES('MD2', 'Testing')) AS [MD2Length],
        DATALENGTH(HASHBYTES('MD4', 'Testing')) AS [MD4Length],
        DATALENGTH(HASHBYTES('MD5', 'Testing')) AS [MD5Length],
        DATALENGTH(HASHBYTES('SHA', 'Testing')) AS [SHALength],
        DATALENGTH(HASHBYTES('SHA1', 'Testing')) AS [SHA1Length],
        /* 2012 only: */
        DATALENGTH(HASHBYTES('SHA2_256', 'Testing')) AS [SHA2_256Length],
        DATALENGTH(HASHBYTES('SHA2_512', 'Testing')) AS [SHA2_512Length];

And it should come out with this:

MD2Length MD4Length MD5Length SHALength SHA1Length SHA2_256Length SHA2_512Length
--------- --------- --------- --------- ---------- -------------- --------------
16        16        16        20        20         32             64


Two queries run much longer when combined into one

You better combine those queries using Derived Tables;

select sigla_partido, receita_valor, despesa_valor
from
 (
     SELECT sigla_partido, SUM(receita_valor) as receita_valor
     FROM prestacao_conta_eleicao_candidato_2014
     NATURAL JOIN prestacao_conta_eleicao_candidato_2014_receita
     GROUP BY sigla_partido
  ) as receita
natural join
 (
     SELECT sigla_partido, SUM(despesa_valor) as despesa_valor
     FROM prestacao_conta_eleicao_candidato_2014
     NATURAL JOIN prestacao_conta_eleicao_candidato_2014_despesas
     GROUP BY sigla_partido
 ) as despesas

Btw, check if the slow query actually returned the correct result.


How do I automatically set meta commands when starting psql?

You are looking for the .psqlrc file. Per documentation:

  psqlrc and ~/.psqlrc

  Unless it is passed an -X or -c option, psql attempts to read and execute commands from the system-wide startup file (psqlrc) and then the user's personal startup file (~/.psqlrc), after connecting to the database but before accepting normal commands. These files can be used to set up the client and/or the server to taste, typically with \set and SET commands.


Ola Hallengren's Backup solution error message after moving files using cmdexec stops job, this disagrees with faq

After further digging (and almost emailing Ola) I found the source of my
confusion.

After querying the CommandLog table (@LogToTable = 'Y') found that while the TSQL job steps were reporting the error, the job actually completed, logged the commands to the table as successful (and of course the files existed on disk.)

It looks like not only do TSQL job steps stop reporting on the first error, which is referenced in a different part of the FAQ.

If you are seeing that the SQL Server Agent History reports a failing job, verify that the same failure is reported in the master.dbo.CommandLog table, and ensure you have @LogToTable = 'Y' specified.

Ola's job is behaving as described in the FAQ, and the reference to TSQL job steps was the hint I needed.


INSERT takes over 5 hours, when inserting more than specific number of rows

My guess would be - if you are not actually being blocked - that you are hitting a threshold above which the data file (and/or log file) has to grow, and that your configuration is not optimized to support this growth. Ensure that:

Your data file growth rate is reasonable, and is a fixed size (not %!) large enough to accommodate this transaction and anything else that may be going on concurrently.
Same for log file.
Instant file initialization is enabled. This will help speed up data file growths but not the often more troublesome log file growths.
You aren't inserting 1.35 million individual rows in a loop or otherwise all as one big transaction. There is something to be said for breaking your transactions into chunks.


SQL server 2012 reports "Missing Index" on column which is covered by "Clustered Index"

It is trying to filter[1] by section_id but not quotation_id - it can't do this by seeking on an index on quotation_id,section_id so it will have to scan that index (which as it is your clustered index, is the whole table). It would be able to use an index on section_id,quotation_id for this because the target columns are first in the index's key.

[1] or possibly sort, as we can't see the query or the full plan we can't know which or why. In the case of sorting it will be wanting the index for slightly different reasons, perhaps to replace a clustered index scan and spool to tempdb for sorting by scanning the non-clustered index and pulling the other data out in order more directly.

  I was under the impression that Clustered indexes are used by query optimiser even when non-clustered index seek is performed

That is correct. It will use seeks on the non-clustered index to find the clustering key value, then use that to find other data needed from the row. Unless it only needs data from columns in the index - in that case there is no need to refer to the clustered index as all the information is already available without.

Note that this is essentially the same if you don't have a clustered index. Instead of containing the clustering key each non-clustered index contains the rowid of each record in the heap instead.

  Does adding the missing index (as reported by SQL server execution planner) is a good idea?

For that query as it stands, quite probably (though with the caveat that we don't know what the query is, or the query plan that was used, so it could be that refactoring that would be more useful). It would likely allow the query to be processed by seeking on that index then performing lookups on the clustered index as needed instead of scanning the whole clustered index, which the planner expects to be a significantly more efficient way around.


Should I add page level compression before adding the primary key, or after?

The clustered index is in fact the table.  On the assumption that your primary key is clustered then I would create a clustered primary key with page level compression rather than trying to do it in two steps.

-- Add primary key
ALTER TABLE             dbo.TableName
ADD CONSTRAINT          PK_TableName
PRIMARY KEY CLUSTERED   (<Columns>)
WITH (DATA_COMPRESSION = PAGE)
;

I would also copy about 100k rows to a temporary (temporary physical not #temporary) table and run some tests.  Try running compression first, clustered key first, try doing them as one step.  See what runs fastest.  I would guess it will be one step personally :).


Use Windows 2016 Storage Replica for SQL Server failover cluster instance

Frequently Asked Questions about Storage Replica specifically mentions using it for a SQL Server FCI.  Business continuity and database recovery - SQL Server also mentions it specifically.

I've also found other non-Microsoft articles (this one, in particular) mentioning it as well.

This, along with the fact that SQL Server doesn't know or care about the underlying storage as long as it presented as cluster storage, indicates that the answer is yes, it will work.


Distinct Children rows ordered by a column of Parent

You need to GROUP BY instead of DISTINCT (the effect is the same) and you need to aggregate the column you want to use for sort order. In this case I used MIN, but you can use whatever makes sense here.

SELECT c.foo, c.bar
FROM Parent p
JOIN Child c on c.parentId = p.id
GROUP BY c.foo, c.bar
ORDER BY MIN(p.createdDate);

Please note that, since you're ordering by a column of the parent table, you may have multiple rows from the child table that have the same value for Parent.createdDate, so the sort order within the same createdDate will be non predictable. You need to add at least one column from the child table to make the sort order predictable.


If you're on SQL Server Enterprise Edition, you can add the ONLINE switch:

CREATE NONCLUSTERED INDEX IX_Table1_URL
ON [dbo].[Table1] ([URL])
WITH (ONLINE=ON);
GO

Most of the index creation will happen without blocking, although not all - read this DBA.se question & answer for details about when even online index creation blocks other queries.

Your server may still be much slower during the index creation, though - creating indexes can be hard work, taxing your SQL Server's storage.


Import issue: Missing columns values from CSV file

Specify the column list so you won't include id.  Add this after then IGNORE clause:

(var1, var2, dates)


Oracle SQL - find the character '&' in a string

My choice for a test would be a simple LIKE:

SELECT id
FROM acuheader
WHERE apar_name LIKE '%&%'
  AND client = 'W5'
  AND apar_id = 'x';


SELECT rows based on indefinite number of filters

This is a special case of relational-division. Here is an arsenal of query techniques:

How to filter SQL results in a has-many-through relation

The special difficulty of your case is to filter on the combination of two attributes, but the principle is the same.
You can make this fully dynamic with plain SQL, without string concatenation and dynamic SQL:

Using same column multiple times in WHERE clause

But performance won't come close to the following solution with dynamic SQL.
For best performance, have this (UNIQUE) multicolumn index:
CREATE [UNIQUE] INDEX ON tags (tag_name, tag_value, game_id);

Maybe your PRIMARY KEY on tags already spans these columns. For best performance you need index columns in the demonstrated order. Create an additional index if the PK does not match or change the column order of the PK unless you need columns in a different order (too). Related:

Is a composite index also good for queries on the first field?
PostgreSQL composite primary key

The basic query technique I chose uses the pattern:
SELECT game_id
FROM   tags t
WHERE (tag_name, tag_value) = ('Event', 'EUR-ASIA Rapid Match')
AND    EXISTS (SELECT FROM tags WHERE game_id = t.game_id AND (tag_name, tag_value) = ('Round', '5'))
AND    EXISTS (SELECT FROM tags WHERE game_id = t.game_id AND (tag_name, tag_value) = ('some_tag', 'some value'))
AND ...

This query is already optimized for performance.
Function
Using a user-defined row type as input like you have in your answer (optional, but convenient for the function design). I chose the name game_tag because tag felt too generic:
CREATE TYPE game_tag AS(
   tag_name  text
 , tag_value text
);

Note the subtle differences in syntax for these two row values:
'(Event,"EUR-ASIA Rapid Match")'::game_tag
('Event', 'EUR-ASIA Rapid Match')

The first one is a string literal for the registered row type game_tag, the second is a ROW constructor on two string literals building an anonymous row, short for:
ROW('Event', 'EUR-ASIA Rapid Match')

Either works for our purpose and gets index support. Just don't confuse the different syntax requirements. Related:

Invalid input syntax for type numeric: “(0.0000000000000000,8)”

The shortcuts for just 1 or 2 parameters are optional but should further improve performance.
CREATE OR REPLACE FUNCTION f_games_by_tags(VARIADIC _filters game_tag[])
  RETURNS table (game_id int) AS
$func$
BEGIN
   CASE cardinality(_filters)
-- WHEN 0 THEN  -- impossible
   WHEN 1 THEN
      RETURN QUERY
      SELECT t.game_id
      FROM   tags t
      WHERE (tag_name, tag_value) = _filters[1];

   WHEN 2 THEN
      RETURN QUERY
      SELECT t.game_id
      FROM   tags t
      WHERE (tag_name, tag_value) = _filters[1]
      AND    EXISTS (
         SELECT FROM tags t1
         WHERE  t1.game_id = t.game_id
         AND   (tag_name, tag_value) = _filters[2]
         );

   ELSE
      RETURN QUERY EXECUTE
        (SELECT 'SELECT game_id FROM tags t WHERE (tag_name, tag_value) = $1[1] AND '
              || string_agg('EXISTS (SELECT FROM tags WHERE game_id = t.game_id AND (tag_name, tag_value) = $1[' || g || '])', ' AND ')
         FROM   generate_series (2, cardinality(_filters)) g)
      USING _filters;
   END CASE;
END
$func$  LANGUAGE plpgsql;

db<>fiddle here
Should be faster by orders of magnitude than what you have in your answer.
Call:
SELECT * FROM f_games_by_tags('(Event,"EUR-ASIA Rapid Match")');
SELECT * FROM f_games_by_tags('(Round,5)', '(Event,"EUR-ASIA Rapid Match")', '(some_tag,"some value")');

You can also pass an actual array to a VARIADIC function. Related:

How to use an array as argument to a VARIADIC function in PostgreSQL?
Use array of composite type as function parameter and access it


Configuring 2017 SQL Server SSISDB Server-wide Default Logging Level does not seem to work

As BCM indicates, that default will apply to new executions. Your job in SQL Agent likely has the earlier default level baked into the execution and so it's overriding the system default. In the Advanced properties, you'd switch it to the new value.
When you manually run a job, this is the syntax of the steps it takes (agent job does the same behind the scenes)
DECLARE @execution_id bigint;

EXEC SSISDB.catalog.create_execution
    @package_name = N'Package.dtsx'
,   @execution_id = @execution_id OUTPUT
,   @folder_name = N'FolderName'
,   @project_name = N'ProjectName'
,   @use32bitruntime = False
,   @reference_id = NULL;

SELECT
    @execution_id;

-- 0 = None
-- 1 = Basic
-- 2 = Performance
-- 3 = Verbose
-- 4 = Runtime Lineage
-- 100 = Custom
DECLARE @var0 smallint = 1;

EXEC SSISDB.catalog.set_execution_parameter_value
    @execution_id
,   @object_type = 50
,   @parameter_name = N'LOGGING_LEVEL'
,   @parameter_value = @var0;

EXEC SSISDB.catalog.start_execution
    @execution_id;
GO

If you use the custom/ 100 logging value, then you also need to specify what custom logging level you want to use. Assuming I created a log level called "New Custom Logging Level", I'd add set this parameter before invoking start_execution
DECLARE @logLevelName SQL_VARIANT = N'New Custom Logging Level'
EXEC [SSISDB].[catalog].[set_execution_parameter_value]
    @execution_id
,   @object_type = 50
,   @parameter_name = N'CUSTOMIZED_LOGGING_LEVEL'
,   @parameter_value = @logLevelName;

MSDN set_execution_parameter_value
Tim Mitchell has a good article on setting the execution parameter values


Enable MySQL trigger for all users in mysql

If you think of the definer as the user that the trigger runs as then it needs the right grants to do whatever the triggers action is.

But it sounds like what you want is for other users to be able to trigger the trigger. In this case all of the users that you need to be able to invoke the trigger need the trigger privilege.

See more info here: http://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html#priv_trigger

But basically for each user you need to grant trigger.


Could not start WAL streaming, replication slot is active

The issue was that the wal_(receiver|sender)_timeout were too low...

I changed the value from one minute to ten minutes and it works.


Use the window function ntile() in a subquery (requires Postgres 8.4 or later).
Then select the segments you are interested in (corresponding to percentiles) and pick the row with the lowest value from it:

SELECT DISTINCT ON (segment)
       the_date, to_char((segment - 1)/ 10.0, '99.9') AS percentile, ans
FROM  (
    SELECT t1.the_date
          ,ntile(1000) OVER (ORDER BY (t2.latency - t1.latency)) AS segment
          ,(t2.latency - t1.latency) AS ans
    FROM   table1 t1
    JOIN   table2 t2 ON t1.id = t2.id
   ) sub
WHERE  segment IN (601, 901, 991, 1000)
ORDER  BY segment, ans;

The Postgres-specific DISTINCT ON comes in handy for the last step. Detailed explanation in this related answer on SO:
Select first row in each GROUP BY group?

To get the 90, 99 and 99.9 percentile I picked the matching granularity with ntile(1000). And added a 60 percentile as per comment.

This algorithm picks the row at or above the exact value. You can add a line to the subquery with percent_rank() to get the exact relative rank of the select row in addition:

 percent_rank() OVER (ORDER BY (t2.latency - t1.latency)) AS pct_rank

Aside: I replaced the column name date with the_date since I am in the habbit of avoiding reserved SQL key words as identifiers, even if Postgres would permit them.


Specify only the columns you want to see.

select (column1+column2) AS column1,
       column3,
       column4,
       column5
from table1

It is bad form to use table1.*, as your SQL code will be vulnerable to changes in the table structure.  It is a good habit to always specify the exact columns you want.


You're hitting two waits that are huge problems:

THREADPOOL: Running out of worker threads
RESOURCE_SEMAPHORE: Running out of memory for queries

There's another telling problem, here:

Long average waits on locking

You didn't post the hardware your server has, so I can't say if that's even close to adequate, or if your server settings are appropriate for it.

What's really lousy is that when queries are being blocked by a lock wait, all of the resources they grab to run (CPU, Memory, etc.) are held for the duration of the lock waits.

That means you're not only waiting on the locks to resolve, but then other queries totally unrelated to the blocking chain start piling up waiting for blocked queries to let go of resources.

If hardware is generally adequate, it's most likely a case of needing to tune queries and indexes to make modification queries as fast as possible so the blocking chains don't pile up and cause resource contention.


SQL Server Upgrade - A new Approach for Upgrading

Since one of the primary tenets of SQL Server, transactional consistency, is difficult to get right when performing a clone operation, I'd recommend against using a clone to perform an upgrade in the way you suggest.  A clone of the underlying machine while the SQL Server service is running and accepting client connections might result in the clone containing invalid data.  If you can create a clone of the machine while the SQL Server service is turned off, your proposed solution might be a viable method of performing an upgrade.  Having said that, I'd tend towards not using a VM/SAN clone since the potential for it to go badly wrong is pretty high.  As any DBA worth their money will tell you, unnecessary risk is unacceptable.

Having said that, using a DNS alias is an excellent way of abstracting away the machine name from a server, and is what Windows Failover Clustering uses to direct clients to the active node in a clustered SQL Server installation.

The high-level steps to effect an upgrade where you have a DNS alias in place look like:

Ensure the DNS alias has a short TTL value that will allow clients to "see" changes to the IP address pointed at by the alias quickly enough to connect to the new machine in the desired period of time.  I'd consider 1 minute.
Create a new machine, or VM, with the latest version of Windows and SQL Server as desired.
Backup the databases on the old server, then restore them to the new server.
Test all software/website/applications against the new server to ensure compatibility.
Once testing is complete, and you're happy with the new server, disable user-connections to the old server to ensure no one makes updates that won't be seen on the new server.
Take a final backup of the databases on the old server, then restore those databases to the new server.
Modify the DNS alias to point to the new server's IP address.

At this point client applications should be able to use the new server without any changes required at the client end.

The steps above assume you have an upgrade window long enough for you to perform a backup-and-restore of the databases involved in the upgrade process.  If you have no upgrade window, i.e. you require 99.999% uptime, you'll need to consider using mirroring, log shipping, or failover clustering to minimize downtime during the cutover to the new machine.


How do you GRANT SELECT on hidden resource database mssqlsystemresource?

Not sure where you've stumbled along the way, but this works for me:

CREATE LOGIN permtest WITH PASSWORD = 'x', CHECK_POLICY = OFF;
GO
USE somedatabase;
GO
CREATE USER permtest FROM LOGIN permtest;
GO

According to this page, the user needs SELECT permission on sys.sql_expression_dependencies, and VIEW DEFINITION on the database.

In my experimentation, the following allowed the user to select from the view, but it returned 0 rows, because they don't have the ability to view definition (which includes dependency chains):

GRANT SELECT ON sys.sql_expression_dependencies TO permtest;

In order to actually see any relationships in somedatabase, I also had to add the following:

GRANT VIEW DEFINITION ON DATABASE::floob TO permtest;

I could not find any way to make that more granular (VIEW/DENY definition worked for individual objects, but without the database-level right, I still couldn't see any rows in the catalog view, and DENY did not prevent the objects from showing up in the catalog view nor did it even prevent me from viewing the definition). I feel like SQL Server would have a hard time resolving that granularity anyway - if you had a view that referenced a table, how should the catalog view look if you have grant on the view and deny on the table, or vice versa?

If you don't want to grant VIEW DEFINITION on the database, then create procedures that use EXECUTE AS OWNER, select (filtered?) rows from the catalog view, and give the users (and of course, that could also be a role) execute permissions on the procedure.

CREATE PROCEDURE dbo.GetDependencies
WITH EXECUTE AS OWNER
AS
BEGIN
  SELECT is_schema_bound_reference --, ...
    FROM sys.sql_expression_dependencies;
END
GO

GRANT EXECUTE ON dbo.GetDependencies TO permtest;


How should I store a large number of dynamically generated heterogeneous tables?

You could use a data-defined schema, which isn't terrific for performance or manageability but comes closer to doing what the database was designed to do.

CREATE TABLE report_result_tab (
   report_result_id NUMBER NOT NULL,
   row_id           NUMBER NOT NULL,
   column_name      VARCHAR2(30) NOT NULL,
   value_number     NUMBER,
   value_varchar    VARCHAR2(4000));

ALTER TABLE report_result_tab
   ADD CONSTRAINT report_result_pk
   PRIMARY KEY (report_result_id, row_id, column_name);

With a table like that, you get a variable number of "columns" by putting the column as part of the key.

Let's say we're reporting on a table like this:

CREATE TABLE employee (
   employee_id NUMBER,
   last_name   VARCHAR2(255));

Then to report on it, with one report having two records, it would look like this:

INSERT INTO report_result_tab (
   report_result_id,
   row_id,
   column_name,
   value_number,
   value_varchar)
VALUES (
   0,             /* report_result_id */
   0,             /* row_id */
   'EMPLOYEE_ID', /* column_name */
   37,            /* value_number */
   NULL);         /* value_varchar */

INSERT INTO report_result_tab (
   report_result_id,
   row_id,
   column_name,
   value_number,
   value_varchar)
VALUES (
   0,             /* report_result_id */
   0,             /* row_id */
   'LAST_NAME',   /* column_name */
   NULL,          /* value_number */
   'Smith');      /* value_varchar */

INSERT INTO report_result_tab (
   report_result_id,
   row_id,
   column_name,
   value_number,
   value_varchar)
VALUES (
   0,             /* report_result_id */
   1,             /* row_id */
   'EMPLOYEE_ID', /* column_name */
   42,            /* value_number */
   NULL);         /* value_varchar */

INSERT INTO report_result_tab (
   report_result_id,
   row_id,
   column_name,
   value_number,
   value_varchar)
VALUES (
   0,             /* report_result_id */
   1,             /* row_id */
   'LAST_NAME',   /* column_name */
   NULL,          /* value_number */
   'Jones');      /* value_varchar */

You'll have to figure out what to put as the COLUMN_NAME in the base data. You could use a numeric COLUMN_ID which would save space. In either case, you'll also have to figure out a mapping to get the user-friendly column names and their sort order.

The method above is advantageous for sparse data, but keeping so many copies of the column IDs is expensive.

I've also seen a much uglier version which could potentially be cheaper on disk space. You could define a particular report type, which is nothing more than a collection of column metadata, then build your table to handle the maximum number of columns of each type.

CREATE TABLE report_result_tab (
   report_result_id NUMBER NOT NULL,
   row_id           NUMBER NOT NULL,
   n00              NUMBER,
   n01              NUMBER,
   n02              NUMBER,
   n03              NUMBER,
   n04              NUMBER,
   /* ... */
   s00              VARCHAR2(4000),
   s01              VARCHAR2(4000),
   s02              VARCHAR2(4000),
   s03              VARCHAR2(4000),
   s04              VARCHAR2(4000),
   /* ... */
   d00              DATE,
   d01              DATE,
   d02              DATE,
   d03              DATE,
   d04              DATE);

INSERT INTO report_result_tab (
   report_result_id,
   row_id,
   n00,
   n01,
   n02,
   n03,
   n04,
   /* ... */
   s00,
   s01,
   s02,
   s03,
   s04,
   /* ... */
   d00,
   d01,
   d02,
   d03,
   d04)
VALUES (
   0,        /* report_result_id */
   0,        /* row_id */
   37,       /* n00 */
   NULL,     /* n01 */
   NULL,     /* n02 */
   NULL,     /* n03 */
   NULL,     /* n04 */
   /* ... */
   'Smith',  /* s00 */
   NULL,     /* s01 */
   NULL,     /* s02 */
   NULL,     /* s03 */
   NULL,     /* s04 */
   /* ... */
   NULL,     /* d00 */
   NULL,     /* d01 */
   NULL,     /* d02 */
   NULL,     /* d03 */
   NULL);    /* d04 */

INSERT INTO report_result_tab (
   report_result_id,
   row_id,
   n00,
   n01,
   n02,
   n03,
   n04,
   /* ... */
   s00,
   s01,
   s02,
   s03,
   s04,
   /* ... */
   d00,
   d01,
   d02,
   d03,
   d04)
VALUES (
   0,        /* report_result_id */
   0,        /* row_id */
   42,       /* n00 */
   NULL,     /* n01 */
   NULL,     /* n02 */
   NULL,     /* n03 */
   NULL,     /* n04 */
   /* ... */
   'Jones',  /* s00 */
   NULL,     /* s01 */
   NULL,     /* s02 */
   NULL,     /* s03 */
   NULL,     /* s04 */
   /* ... */
   NULL,     /* d00 */
   NULL,     /* d01 */
   NULL,     /* d02 */
   NULL,     /* d03 */
   NULL);    /* d04 */


I would recommend to script a loop or cursor to enable this on all of your databases and tables. It will capture all of the columns automatically.

Example of loop CDC Note: As always with code on the internet, evaluate cautiously and promote through personal and dev environments prior to production.

  By default, all of the columns in the source table are identified as captured columns. If only a subset of columns need to be tracked, such as for privacy or performance reasons, use the @captured_column_list parameter to specify the subset of columns.

CDC Configuration MSDN

I think it would be better to run SQL Profiler though to catch the code that is running and re-create it that way however. CDC will show you the end results, not how it gets from A to C.


how to judge sql performance through these two explain?

EXPLAIN estimates row counts, often poorly.  Even so, the counts do not reflect how much stuff is hauled around.  If it is only id, that is not much.  If it is all of * (as in SELECT *), that is a lot more.  This is especially the case when it has to reach into the data to get all the columns.

Using index means that it is cruising through the index BTree.  Without that phrase, it is bouncing between the index BTree and the data BTree (using the PRIMARY KEY).  This bouncing is costly, and that cost is not adequately reflected in EXPLAIN.

Using index may be your only clue, but it cannot be trusted.  Sometimes it helps only a little, sometimes it helps a lot.

limit 700000,100 is an awful thing to do.  It must fetch 700100 rows (of just id or of *) but throw away nearly all of them.

Your complex query is the trick to make OFFSET perform somewhat better.  There is no way to say skip over 700000 rows without looking at them (which is what everyone wants).

Other notes:

Don't use CHAR, especially not with utf8; switch to VARCHAR.  char(120) consumes a fixed 360 bytes, regardless of the text.  The unnecessary bulk is a performance issue, especially if the table is bigger than can be cached.

More discussion of OFFSET.


Need advice on combining tables or leaving as separate tables

My advice is allowing PTO to be a sub-type of event.
If Event is a super-type it will have an additional field: type.
If type=PTO, then there should be an item in the PTO table, with the event_id to link them.
You can read about super-type/sub-type relationships on your favorite database information website, but a sub-type minimizes data duplication, and keeps similar data in one place. So, all events have a start and end date, but not all have a reason like vacations do.
Once the tables have been changed, you could add the start and end date from each PTO row to Event, and add the reason and other PTO data to the new, smaller PTO table. Then you could drop the PTO table once all the data has been moved.


SQL Server restarts, unable to find the reason

One clue would be to look in the system_health extended events session, specifically sql_exit_invoked event.  This will give you the time that SQL Server started to shut down, and you can look in the "shutdown_option" value for additional details.
This will tell you a little bit about how the service was stopped (through T-SQL, or through the services snap-in / configuration manager).
Once you know the when and how, you can dig back in to the SQL Server error log, and / or Windows Event Viewer, from that time period and see if there are more details about what initiated the shutdown.


Why are some views listed as Materialized in MySQL explain statements if Materialized views don't exist in MySQL?

To the outside world (if I am not mistaken), a "materialized view" is a permanent table that is automatically inserted/updated in based on data flowing into some other table.

In MySQL, it has a rather limited meaning.  It refers to certain "derived" tables (eg, FROM ( SELECT ... )).  The Optimizer has multiple ways to deal with such constructs.

Before 5.6, for a single such construct, the Optimizer would simply use that as the first "table" without having to "materialize" anything.
Before 5.6, for multiple derived tables (for FROM (SELECT...) JOIN (SELECT...)) the Optimizer would have to repeatedly re-execute all but the first derived table.
5.6 introduced the automatic ability to create a temp table for one of the derived tables.  Furthermore, the Optimizer became smart enough to discover the optimal index for this temp table and create it.  (A side note:  A suitable rewrite of the entire query can usually perform better.)

There is essentially nothing that you, the programmer, can do to control this version of "materialized view".  (Well, rewrite to avoid the need.)

5.7 and 8.0 have even more performance improvements.  But still no "materialized view" as the outside world knows.


Why does mysql give different result for this summations?


Based on your question it seems like this actually has not been run yet because you are using "probably".

However, there are two branches for this question:

Run the Integrity checks on a backup of the database.

Take a full database backup of the prod server
Restore the database on another server
Run consistency checks on the restored database

Break up the DBCC CHECKDB over multiple days.

Aa mentioned in Consistency Checking Options for a VLDB

It is NOT AN OPTION to run Consistency checks on a mirror database or on an availability group replica as a way of checking the principal or primary. Since these will be running on different I/O systems and corruptions do not propagate between servers. This will need to be run IN ADDITION.

Things to make CHECK DBCC run faster.

Use the physical only option trace flag 2562, and trace flag 2549
reduce memory grant
Disable non-clustered indexes on computed columns ahead of time – There is an issue with the DBCC_OBJECT_METADATA latch that it requires an exclusive lock to run.
TEMPDB performance will greatly impact the performance of your CHECKDB
If you want to change the amount of cores your DBCC command, you can either use:

TRACE FLAG 2528
SET MAXDOP


MySQL How to Script to create indexes on all date columns in database

The larger the table, the longer the ADD INDEX will take, unless you have a current version, which has a rapid add index.

Your code seems promising.  You could complete it by building a Stored Procedure to loop through the constructed ALTER TABLE commands to perform+execute them.

But first, run the stored routine to just SELECT the constructed ALTERs.  Desk check the output.  (And/or show us the output.)


What can cause what appears to be missing data in a single table

Over a duration of about 15 minutes, it appears as if 2798 rows have
  "disappeared" from the table. The primary key simply jumps from
  3561297 to 3564095. During the same time period, I've got debug logs
  from clients of this API which indicates that something went wrong
  during the request. My IIS log notes that any request which causes
  database access during this time period had taken between 30000 and
  40000 ms to complete.

I don't think the rows have disappeared.

Your debug logs show that something went wrong during requests to the database when the gaps happened. If queries are aborted & rolled back the identity values will not roll back with it.

Another example is that the application has a timeout window of 30 seconds. Queries that take longer than that will be cancelled and the insert will be rolled back.

As mentioned earlier, in the case of rollback, the identity value allocated will not be rolled back. Instead, the next identity value in line will be used when retrying the insert.

There could have been other reasons that the insert was failing, such as network loss, inserting values that would be truncated, wrong datatype conversions, ....

Below is a reproducible example that shows two gaps:

CREATE TABLE dbo.identitytable(id int identity(1,1), val varchar(255))
INSERT INTO dbo.identitytable(val)
VALUES('BLA1');

GO
BEGIN TRAN
INSERT INTO dbo.identitytable(val)
VALUES('BLA2');

ROLLBACK TRAN

GO

INSERT INTO dbo.identitytable(val)
VALUES('BLA3');

GO

SELECT * FROM dbo.identitytable;

GO

INSERT INTO dbo.identitytable(val)
VALUES(REPLICATE('B',256));
GO

INSERT INTO dbo.identitytable(val)
VALUES('BLA5');

SELECT * FROM dbo.identitytable;

With the last select producing a result with two gaps:

id  val
1   BLA1
3   BLA3
5   BLA5

If an insert with multiple rows is rolled back then the gap will be bigger too:

BEGIN TRAN
INSERT INTO dbo.identitytable(val)
SELECT 'BLA6'
FROM master..spt_values;
ROLLBACK TRAN

INSERT INTO dbo.identitytable(val)
VALUES('BLA7');

SELECT * FROM dbo.identitytable;

Result

id  val
1   BLA1
3   BLA3
5   BLA5
2546    BLA7

DB<>Fiddle

  But how can a transaction rollback occur, if I do not execute a BEGIN
  TRANSACTION? I thought TSQL only made "auto transactions" if
  IMPLICIT_TRANSACTIONS was set to ON?

Queries without BEGIN TRANSACTION or without the "bad idea" IMPLICIT_TRANSACTION setting enabled are auto-committed transactions in itself.

When canceling/aborting the modification of data due to the reasons specified above, undoing these changes have to be done at the database level. This is done via a rollback. If this did not exist, your data would be in an incorrect state.


What process(s) writes to ALERT log file in Oracle?

Any process can write in the alert log.
Even you can write in the alert log using the PL/SQL DBMS_SYSTEM package.
Trace file names clearly indicate which process they belong to. Trace files are named like ORCL_smon_1234.trc, where ORCL is the name of the instance, smon is the name of the server process, 1234 is the OS PID of the process.


Does a transaction cover the subroutines?

Yes the rollback transaction would rollback all changes made inside the stored procedure except if the stored procedure was itself to issue an unbalanced COMMIT which would raise an error

  Transaction count after EXECUTE indicates a mismatching number of
  BEGIN and COMMIT statements.

but still lead to the transaction being committed. Both cases shown below.

CREATE TABLE T(proc_name sysname)

GO

CREATE PROC proc_1
AS
INSERT INTO T VALUES ('proc_1')

GO

CREATE PROC proc_2
AS
INSERT INTO T VALUES ('proc_2')
COMMIT;

GO

BEGIN TRAN
EXEC proc_1
ROLLBACK
/*Table is empty. Transaction rolled back*/
SELECT * FROM T

GO

BEGIN TRAN
EXEC proc_2
ROLLBACK
/*Table is not empty. Transaction was committed*/
SELECT * FROM T


MYSQL improve query performance when using OR

What about using a union?

SELECT * FROM table WHERE (col1 = '123' AND col2 = '456')
UNION ALL
SELECT * FROM table WHERE (col1 = '456' AND col2 = '123')


How to catch and handle only specific Oracle exceptions?

You have two options:

Refer to the exception directly by number:

BEGIN
    EXECUTE IMMEDIATE 'CREATE SEQUENCE S_TEST START WITH 1 INCREMENT BY 1';
EXCEPTION
    WHEN OTHERS THEN
      IF SQLCODE = -955 THEN
        NULL; -- suppresses ORA-00955 exception
      ELSE
         RAISE;
      END IF;
END;

Other option is to use EXCEPTION_INIT Pragma directive to bind a known Oracle error number to user defined exception;

DECLARE
   name_in_use exception; --declare a user defined exception
   pragma exception_init( name_in_use, -955 ); --bind the error code to the above
BEGIN
    EXECUTE IMMEDIATE 'CREATE SEQUENCE S_TEST START WITH 1 INCREMENT BY 1';
EXCEPTION
    when name_in_use then
       null; --suppress ORA-00955 exception
END;

  BTW Is there any syntax to catch errors by just providing the error-codes?

Yes, I've demonstrated it in the first example

Further reading for variations on this:

Oracle Reference Documentation on Handling PL/SQL Exceptions


postgres npgsql DDL statement success or failure

If a statement fails, you'll get an exception.

There is no way to miss that unless you catch and ignore exceptions, which is never ok.


cant access both phone numbers in my nested table type

Please take a look at http://www.orafaq.com/wiki/NESTED_TABLE.

When you include the nested table it is like a join, so if you want a single row per customer, you will need to do some sort of unpivot (note that there could be more than two phone numbers in the nested table — Oracle needs to know how many columns to return. This will return all the rows:

SELECT t.name.firstname   AS fname,
       e.*
FROM customer t,
TABLE(t.cust_mobiles) e;

Useful hints for unpivoting can be found here: https://oracle-base.com/articles/11g/pivot-and-unpivot-operators-11gr1


If you want to store it as a hierarchical structure, the pattern that would best serve you is the "Closure Table" pattern, which allows for subtree operations (select/update/delete all descendants of x).

A generic example (closure table c and data table d) of what flagging an entry and all children at all levels below it would look like this:

UPDATE d
INNER JOIN c ON c.descendant_id = d.id
SET d.flag=1
WHERE c.ancestor_id = @thetopmostdesiredentry;

Here are some well written resources on how to implement that:

https://www.percona.com/blog/2011/02/14/moving-subtrees-in-closure-table/
https://coderwall.com/p/lixing/closure-tables-for-browsing-trees-in-sql
http://karwin.blogspot.com/2010/03/rendering-trees-with-closure-tables.html

The best place to read about it is Bill Karwin's book "SQL Anti-patterns", which details it as an alternative to less savory patterns.


SQLAnywhere: Cursor getting closed after executing a DDL statement

When opening a cursor, there is the WITH HOLD preventing the issue. So

DECLARE @rowfoo integer
DECLARE @query varchar (32000)

SET @query = 'CREATE OR REPLACE PROCEDURE testprocedure AS
            BEGIN
                print ''Just a dummy text''
            END'

DECLARE test_cursor CURSOR FOR
        SELECT row_num FROM sa_rowgenerator(1, 31, 1)

OPEN test_cursor WITH HOLD
    FETCH NEXT test_cursor into @rowfoo
    WHILE @@FETCH_STATUS = 0
    BEGIN
        execute(@query)
        FETCH NEXT test_cursor into @rowfoo
    END
CLOSE test_cursor
DEALLOCATE test_cursor

Is working well for my case. As an alternative close_on_endtrans option could also be switched either for session or public.


The full object locks which last (usually) for the duration of a transaction and which you can see in pg_locks have info about them stored in shared memory.  But access to that shared memory requires the protection of light-weight locks, which should last for only nanoseconds or microseconds while the memory access is actually occuring.  What you appear to have is contention on those light-weight locks which protect the memory used for the full locks.
I tried to reproduce this contention by having intense traffic through the lock manager, but I can't reproduce this contention.  So I think you are doing something which is not obvious based your description, like maybe using subtransactions.  Could you publish code which does reproduce this problem, by inserting, selecting, and updating the way you do it, but with the rest of your business logic stripped out?
But in any case, allowing there to be over 200 active connections in a machine with only 4 CPUs (for EC2 x86 machines, the number of usable CPU is generally half the number of vCPU) can't be good for contention.  The more active processes you have, the more time they spend fighting over locks rather than doing useful work.  Having a lot of connections can simplify the client book-keeping and it isn't always a problem (like if few are active at any one time), but there is ample evidence here that for you it is a problem.
You should adjust your connection pooler to restrict the number of connections.  If 90 of your 100 connections are usually idle, then they are not useful during the time when they are idle, and are actually harmful during the time they are in use as they cause contention. This creates a "the beatings shall continue until morale improves" situation--the database is slow so let's launch more connections, which create more contention which slow it further. (They are kind of harmful all the time, as they impede the usefulness of your Connections Count monitoring graph). Are the consumers connecting through the same pooler as the producers?


As I understand it sqlite does not have window functions which really simplify these kind of queries (I replaced a couple of identifiers which I suspect is reserved words):

select dt, tm, lat, lng
from (
    select dt, tm, lat, lng
         , row_number() over (partition by dt, lat, lng
                              order by tm asc) rn1
         , row_number() over (partition by dt, lat, lng
                              order by tm desc) rn2
    from ReportAct_tbl
) x
where 1 in (x.rn1, x.rn2)

Without window function (like row_number) it's probably easiste to get the row where its does not exists any rows that date before that time

select dt, tm, lat, lng
from ReportAct_tbl r1
where not exists (
    select 1
    from ReportAct_tbl r2
    where r1.dt = r2.dt
      and r1.lat = r2.lat
      and r1.lng = r2.lng
      and r1.tm < r2.tm
)

To get the last rows we do the same:

select dt, tm, lat, lng
from ReportAct_tbl r1
where not exists (
    select 1
    from ReportAct_tbl r2
    where r1.dt = r2.dt
      and r1.lat = r2.lat
      and r1.lng = r2.lng
      and r1.tm > r2.tm
)

and finally we can combine them as:

select dt, tm, lat, lng
from ReportAct_tbl r1
where not exists (
    select 1
    from ReportAct_tbl r2
    where r1.dt = r2.dt
      and r1.lat = r2.lat
      and r1.lng = r2.lng
      and r1.tm < r2.tm
)
union
select dt, tm, lat, lng
from ReportAct_tbl r1
where not exists (
    select 1
    from ReportAct_tbl r2
    where r1.dt = r2.dt
      and r1.lat = r2.lat
      and r1.lng = r2.lng
      and r1.tm > r2.tm
)


Amazon RDS I/O requests - 1 query = 1 billable I/O?

WARNING : Be very careful with your numbers and what you view as a Query !!!
Why am I giving such a warning ???
Back in August 2011, I wrote post in ServerFault explaining how it is possible for 1 Billion Queries to be executed in 24 days.
Here is that entire post :
MySQL will call for queries internally. In fact, just about anything you do in MySQL is a query.
If you turn on the general log or the slow query log, everything mysqld does gets recorded.
If you have --log-queries-not-using-indexes enabled, everything not involving indexes lands in the slow log.
Let's say you run this query:
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| annarbor           |
| dude               |
| example            |
| garbage            |
| lovesh             |
| mysql              |
| performance_schema |
| replagdb           |
| stuff              |
| test               |
| tostinni           |
| wordpress          |
| zipcodes           |
+--------------------+
14 rows in set (0.06 sec)

Yes, SHOW DATABASES; is a query. In fact, what the information_schema equivalent ???
mysql> select schema_name "Database" from information_schema.schemata;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| annarbor           |
| dude               |
| example            |
| garbage            |
| lovesh             |
| mysql              |
| performance_schema |
| replagdb           |
| stuff              |
| test               |
| tostinni           |
| wordpress          |
| zipcodes           |
+--------------------+
14 rows in set (0.08 sec)

Does the table information_schema.schemata have an index ???
mysql> show create table information_schema.schemata\G
*************************** 1. row ***************************
       Table: SCHEMATA
Create Table: CREATE TEMPORARY TABLE `SCHEMATA` (
  `CATALOG_NAME` varchar(512) NOT NULL DEFAULT '',
  `SCHEMA_NAME` varchar(64) NOT NULL DEFAULT '',
  `DEFAULT_CHARACTER_SET_NAME` varchar(32) NOT NULL DEFAULT '',
  `DEFAULT_COLLATION_NAME` varchar(32) NOT NULL DEFAULT '',
  `SQL_PATH` varchar(512) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

No, it does not. So, SHOW DATABASES; would land in a general log and the slow log (with --log-queries-not-using-indexes enabled)
Therefore, many operations we do not think would constitute a query may just be a query, but internal to mysqld.
If you are using any monitoring tools that are connected to mysqld, this would also run up the counts on queries.
Example:
mysql> show global status like 'uptime'; select * from information_schema.global_status where variable_name='uptime';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Uptime        | 613   |
+---------------+-------+
1 row in set (0.00 sec)

+---------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+---------------+----------------+
| UPTIME        | 613            |
+---------------+----------------+
1 row in set (0.00 sec)

Just retrieving the uptime of mysqld is a query. Internally, how does MySQL count the queries being executed ? Here are two status variables that may shed some light:

Queries : The number of statements executed by the server. This variable includes statements executed within stored programs, unlike the Questions variable. It does not count COM_PING or COM_STATISTICS commands.

Questions : The number of statements executed by the server. This includes only statements sent to the server by clients and not statements executed within stored programs, unlike the Queries variable. This variable does not count COM_PING, COM_STATISTICS, COM_STMT_PREPARE, COM_STMT_CLOSE, or COM_STMT_RESET commands.

Please don't be that concerned if your MySQL Server is being monitored because the monitoring that calls for status variables are running queries internally to retrieve requested data.
1 billion in 24 days is

41.7 million queries per day
1.736 million queries per hour
28,935 queries per minute
482 queries per second

For a MySQL instance that is being monitored, these numbers are not farfetched at all.
If you are using MySQL Workbench, MySQL Administrator, or phpMyAdmin, any page these products generate or update will summon these little status queries and run numbers up quickly.
SUMMARY
If your site does indeed make 8M queries, then an I/O Rate $0.10 per 1 million requests, should be $0.80 (80 cents) a month. If you execute 1 Billion queries in a Month, that $100.00. Please make absolutely sure these numbers jive and GET IT IN WRITING WITH YOUR CFO SITTING NEXT TO YOU !!!
UPDATE 2012-05-02 16:26 EDT
Since it is 800M queries/month, that's $80.00/month


MySQL Workbench Administrator - GUI "Access denied for user root " gotcha

Long story short - the previous night I ran the Java tutorial's SQL script to set the root password. It succeeded. However the GUI was not aware of this new password value, but still it was aware that there was a password, so I got the odd message.

If you see this message in a similar case, just quit the workbench and restart, enter the changed password and all is fine.


Why does PostGIS create an ugly table in my database called "spatial_ref_sys"?

Why, given that it's an extension/"PG library" of sorts, doesn't it at least use its own schema, as to separate itself from my own stuff?

If you want it to use a separate schema, you can put it there:

create schema postgis;
create extension postgis with schema postgis;

Unfortunately you can't move it after the fact, other than by dropping and re-creating, which could be very messy if you are already using it.


Setting the filegroup to read only doesn't actually improve performance. It might appear that way if there are users/jobs that update the data while most users are just reading data (blocking) but that's a misinterpretation of your observation.

A common misconception is that it saves on locking however that would require the database to be read only not just the specific file group (Source1, Source2).

From your description, it looks like nobody else apart from the ETL job should have write access to the table. That means, all users are read only by definition. So what makes you think further making the table read only will help performance? Have you observed other users writing to the database hence causing blocking? If so why not just secure the table and allow only the account used for ETL to write data while all other accounts can only read? Much easier to do, far less intrusive plus it enforces your rule of only ETL can write, all others can only read.


Preventing sysquerymetrics from being created ASE 15.7

Prevent it to be created, I don't think so, it uses system tables, so even if you could, not wise you could impact other processes that might need those tables.
You can drop it if you desire.

To recreate it, check the installmaster script, it should be there the definition of that view.


Multi-Instance Failover Cluster SQL Cluster

Points to note:

A 3 node cluster requires SQL Server Enterprise Edition. You can deploy a 2 node cluster using Standard Edition.
If you are placing an active instance on each node, then each node will need to be licensed. That is 4 cores per node as a minimum so at least 12 SQL Server Enterprise Edition core licenses, possibly more if your load requires it.
You need to thoroughly test performance with all instances running on one node. If you lose two nodes and all instances fail over to a single node, can your hardware cope with the load?


Pull Access schema into a SQL Server table

The way we solved this was to use a template db to auto create views against the access linked tables.  Each view was created as

CREATE VIEW AS
SELECT *
  FROM LinkedServer...Table

Once these views had been created we could pull the column listing from sys.columns joined over to sys.views.

SELECT v.name, c.name
  FROM sys.columns c
       JOIN sys.views v ON c.object_id = v.object_id
 WHERE v.name = @viewname

We could then insert this into the table we needed to work with.


MySQL 5.5.21 ON UPDATE CURRENT_TIMESTAMP incorrect

I don't think you are supposed to use DEFAULT (constant) with ON UPDATE.

According to the MySQL Docuementation

  With an ON UPDATE CURRENT_TIMESTAMP clause and a constant DEFAULT
  clause, the column is automatically updated to the current timestamp
  and has the given constant default value.

CREATE TABLE t1
(
    ts TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP
);

This might just be stomping over with the current datetime.

On the same page, it says

  With an ON UPDATE CURRENT_TIMESTAMP clause but no DEFAULT clause, the column is automatically updated to the current timestamp. The default is 0 unless the column is defined with the NULL attribute, in which case the default is NULL.

CREATE TABLE t1
 (
     ts TIMESTAMP ON UPDATE CURRENT_TIMESTAMP      -- default 0
 );
 CREATE TABLE t2
 (
     ts TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP -- default NULL
 );

I suggest just removing the DEFAULT '0000-00-00 00:00:00'

Give it a Try !!!


CHANGE REPLICATION FILTER REPLICATE-DO-DB is a SLAVE filter to read only (mydb) from the relay log.

If you want to filter the Master to write only (mydb) statements on the binary logs (and replicate only (mydb) to all slaves), you need to use the --binlog-do-db=name variable (Link) on master server.


Configuring Postgres 9.2 streaming replication with Redhat Clustering Suite for High Availability

repmgr is a nice place to go indeed. If you use pacemaker/corosync you shoud check this as well, its a Resource Agent handling repmgr : https://github.com/xmm/repmgr.
Pelase note you might need to either patch the provided Resource Agent or patch repmgr.


SQL Server 2016 Express: System databases

You should be able to move the system database for Express just like any other edition.  Move System Databases contains very straightforward instructions on the steps required to move the system databases.

For future reference, you can specify the storage locations of the system database at install time and save yourself some time.


First of all, Paul mentions this about those "Metadata" fields in the blog post:

  Note that I didn’t include the fields starting with Metadata:. That’s because they’re not part of a page header. During SQL Server 2005 development I did some major work rewriting the guts of DBCC PAGE and to save everyone using it from having to do all the system table look-ups to determine what the actual object and index IDs are, I changed DBCC PAGE to do them internally and output the results.

So the "Metadata: ObjectId" field should always match the actual object_id from sys.objects.

The other part of the question was eventually answered in the linked blog post as follows:

  The m_objId is only the actual object ID for system tables with IDs less than 100, and for tables in databases upgraded from SQL Server 2000. For all other tables, the m_objId is part of the allocation unit ID.

So the m_objId will match for system tables (and upgrades from SQL Server 2000).  But for others, he says it's "part of the allocation unit ID."  That's a bit vague.

Running SQL Server 2017 here, I can create a user table with one index page and get the following for those two values from DBCC PAGE:

m_objId (AllocUnitId.idObj) = 178
Metadata: ObjectId = 901578250

I can confirm that 901578250 matches what's in sys.objects object_id for this table.

So where does 178 come from?

Upon further research, Paul has another post: Inside the Storage Engine: How are allocation unit IDs calculated?

In that, he discusses how the allocation_unit_id (from sys.allocation_units) is calculated from m_objId and another internal id field from the page headers (m_indexId).

So, essentially, after SQL Server 2000, the m_objId value is just a building block used to calculate the ID for the allocation_unit.  It's internal to the page and doesn't really map to something else (not in the same way that "Metadata: ObjectId" does anyway).


Is it a good idea to move high-volume and highly-accessed tables to a separate database?

You will gain a lot more with regards to performance if you move the table to a different harddisk. As long as the "busy" table and the rest are located on the same disk, moving that table into a different "file" (by moving it into a different database) won't change anything with regards to (I/O) performance.

Distributing the I/O load to a different harddisk (and a different harddisk controller) will most probably give you a better performance for the remaining data because it is not affected by the I/O done on the busy table.

To move the table to a different harddisk, you need to create a new tablespace (which of course is located on that disk) and then move that table to that tablespace.


How to prevent daily index fragmentation of 99%

I think you should try higher FILLFACTOR settings on HighScore_RoundGroup_Nidx (e.g. 50 or 40). You can set FILLFACTOR to 0 or 100 for the PRIMARY KEY because it shouldn't fragment If it still does, FILLFACTOR does not help because the reason is that newly allocated pages interleave with other newly allocated pages. This is a well-known SQL Server issue. You could move this index into its own filegroup which would stop this problem.


How to ensure no duplicate records are added when using multiple Node JS processes

It sounds like the business key for an Appointment is (assigneeId, eventId, starttime, endtime).  Create a unique constraint on the business key (assigneeId, eventId, starttime, endtime) would fix the issue.  You need to catch unique constraint error returned by the DB on your app.


how to generate the root CA.pem in mongodb for configuring the ssl
  certificates?

As per MongoDB documentation here The procedure creates both the CA PEM file and an intermediate authority certificate and key files to sign server/client test certificates.

  TESTING PURPOSES ONLY:  This will provides some guidelines for creating test x.509 certificates:

  Do not use these certificates for production. Instead, follow your    security policies.
  For information on OpenSSL, refer to the official OpenSSL docs.    Although this tutorial uses OpenSSL, the material should not be taken
  as an authoritative reference on OpenSSL.

for further your ref here


How do I handle a single `reference` to multiple different types of data?

You should group the fields closest related to each other in the same Table and create additional Tables for the remaining fields as they relate to each other as well. This is called database normalization and is effective in reducing data redundancy, improving maintainability, and helps optimize performance. Your additional Tables (kind of reference Tables such as your first option describes) will store the primary key(s) of the Table(s) they relate to. This is known as a foreign key relationship. Your second design option is called denormalization and is not recommended in most cases (there are some specific cases where some denormalization is helpful).
I don't know your data so I'll use an example for a Car Dealer to convey the idea of normalization. Image a Car Dealer business who sells different brands of Cars across multiple Locations (notice how my objects are already taking shape of the Tables I need). So I'd expect their to be a Locations table, with columns regarding each Location, such as its Address, PhoneNumber and maybe ManagerName, in addition to its primary key column LocationId. I'd also expect a Cars table with columns like Manufacturer, Model, ManufacturedYear, with a primary key column of CarId.
Cool, so far so good. But what if we wanted to know more information about the Managers of each Location and more information about the Manufacturers of each Car. To store that in the database, if we added those fields to the Locations and Cars tables respectively would be bad design because that's denormalized. Information about a Manager object doesn't make a lot of sense in a Locations table, same for information about a Manufacturer in the Car Dealership's Cars table. So we should create two more tables, Managers and Manufacturers.
Managers would now store the ManagerName field instead of the Locations table. (Realistically you'd like store ManagerName in two fields, one for FirstName and one for LastName but this is just a basic example, so bear with me :). Managers would also store things like the Manager's Address and PhoneNunber, and maybe even their YearlySalary, all things that wouldn't make sense in a Locations table. Finally, the primary key would be ManagerId which is what the Locations table would store as a foreign key reference to the Managers table, and this is how you relate the two tables.
Same design as above for the Manufacturers table. It would have columns like ManufacturerName, Address, PhoneNumber, maybe a field called NumberOfCarModels etc, and finally a primary key of ManufacturerId which would be a foreign key in the Cars table.
This is loosely a first level of normalization and hopefully conveys how to structure things. Essentially every main object gets it's own table. You can actually further normalize this (though not always necessary) by having more generic tables for your objects. For example a Contacts table could store all the Addresses and PhoneNumbers of the Locations, Managers, and Manufacturers tables in one place (with a column to denote the ContactType) instead of on each individual table. And the Managers table would likely be an Employees table with a field IsManager to denote which ones are Managers. This next level of normalization reduces redundancy and lightens the other tables even more so (but again it isn't a necessity and just depends on your situation).
Ideally, the performance benefits of normalization come from the fact that when you query a table, you're not getting back 30 extra columns of data you don't need and take longer to load off disk, etc. By designing your tables centralized around your primary objects, and storing only what's truly relevant to those objects in those tables, breaking out the additional fields into their own object tables, you will maximize the performance of querying those tables.


Pre-staging data causes execution plan cost to skyrocket

Costs are based on estimates, even in "actual plans". You cannot compare two query plans side by side and conclude that one of them will require more CPU to execute based on operator or total plan costs alone. I can create a query with a cost in the millions that executes in one second. I can also create a query with a tiny cost that will effectively take forever to execute. For your case, the first query has a cost of only 22 optimizer units because of a poor cardinality estimate after the hash join:

The operators in red execute millions of times, but the query optimizer expects them to only execute a few thousand times. Costs, which are based on estimates, won't reflect that work. The operator in blue is a table spool for which the cardinality estimator expects to insert a single row. It instead inserts a few million. As a result, the operators in black (along with a few others not shown) are inefficient and spill to tempdb.

With the other plan, you put a significant number of rows into tempdb and as a result the cardinality estimate is more reasonable, though it's still not ideal:

The query optimizer expects to need to process many more rows and as a result the query plan has a higher cost. As a very general rule of thumb you may see improved performance with improved estimates, but it doesn't always work out how you'd like. Looking at the plan with a temp table I see a few areas of possible improvement:

Load the full CTE from the original query into the temp table. A query with multiple distinct aggregates can be tough to optimize. Sometimes you'll get a query plan where all of the data is loaded into a spool (into tempdb) and some of the aggregates are applied separately to the spool. All of that work is always done in a serial zone in my experience. If you eliminate all of the joins in the query I believe that you won't get that optimization. The aggregates will just be applied to the temp table. That will save you the work of writing out nearly the same data to tempdb and the entire plan should be eligible for parallelism.
Define the temp table as a heap and write to it with TABLOCK. It looks like right now you have a clustered index which means you aren't eligible for parallel insert.
Consider making the query eligible for batch mode by using one of these tricks. Batch mode aggregates can be significantly more efficient with multiple distinct aggregates.

I would expect some combination of those steps to significantly improve runtime. Please note that I did a quick analysis partially because anonymized plans are difficult to interpret.


Oracle audit: Only 'SESSION REC' in ACTION_NAME column

This means you set AUDIT .. BY SESSION instead of AUDIT .. BY ACCESS. When you use BY SESSION, the ACTION_NAME contains SESSION REC, and the actual actions performed by that session are stored in the SES_ACTIONS column as characters. For example the value ---------S------ means there was a select on the object, as each character represents the following actions in order:

ALTER, AUDIT, COMMENT, DELETE, GRANT, INDEX, INSERT, LOCK, RENAME, SELECT, UPDATE, REFERENCES, and EXECUTE

http://docs.oracle.com/cd/B10501_01/server.920/a96536/ch2205.htm#1307791


CREATE DATABASE ... WITH TEMPLATE seems to lose relations in PostgreSQL

Thanks to @a_horse_with_no_name for leading me to this answer.

The relations appear to be missing because Postgres does not copy the search_path to the new database.  If you explicitly look in the specific schema, all relations are there:

\dt <schema>.*

So, the search_path needs to be explicitly set on the new database:

ALTER DATABASE gcis_rollback2 SET SEARCH_PATH gcis_metadata, public

@Erwin Brandstetter also provides more information on viewing the search_path for roles/dbs.


For me, this is a question of simplicity-of-design vs performance-and-scalability.

If I am designing a non-critical database that won't see a huge amount of traffic, then I typically would go with a single file per database since it is easy to configure and maintain.

However, if your recovery point objective is to be able to recover the database to within several minutes of any failure, and your recovery time objectives are to be able to recover within as short a time as possible, I would carefully plan the filegroups and associated files as:

The main filegroup should only contain the system tables.  This filegroup can then be very quickly restored or replaced if necessary without affecting business data.
A primary filegroup to store business critical data that can be restored quickly enough to meet the recovery time objective and get the business back up-and-running ASAP.  I would create as many files for this filegroup as I have separate and distinct drive subsystems on high-speed devices.  For instance, if I had 3 separate SSD raid arrays, I might create 3 files to spread the load across the arrays evenly.
A secondary filegroup to store historical yet business relevant data that is not critical to get the business up-and-running and yet still needs to be in the database for some reason.  Typically this would be on one or more files, depending on performance and cost requirements.

If there is a requirement to store many file-based pieces of data, for instance images or documents, with relationships to data stored in SQL Server, I would use FILESTREAM, which requires its own filegroup.


What kind of a lock does IN keyword takes in SQL

See File Locking And Concurrency In SQLite Version 3.

There is a single lock for the entire database file.

When the transaction reads the database for the first time, it takes a read lock (a SHARED lock). This happens not when the IN is executed, and not before that when the subquery is executed, but even before that when the database reads the system table to get information about table1.

When the transaction writes to the database for the first time (i.e., when at least one row is actually UPDATEd), it takes a write lock (a RESERVED lock, and later PENDING/EXCLUSIVE when the cached changes are actually written at the end of the transaction).


How to protect Sqlite3 database with password that using in desktop application

Sqlite doesn't come with encryption built in, however you can enable encryption in sqlite by either purchasing the official paid extension here for $2000. Or you can take a chance on one of the free alternative like this one. I have no idea how well the free versions will work, but I imagine the paid one is quite robust and up to date.
You will also need to build a custom qt sql driver by following the instructions here. And finally you must run this sql immediately after opening the database
PRAGMA key='your-secret-key';


MySQL CHAR_LENGTH() alternative in IBM DB2

This page explains how to deal with string lengths when Unicode characters are involved.

CHARACTER_LENGTH is the function you need.

CHARACTER_LENGTH(yourstring,CODEUNITS32);


How to trace the exact query fired in sql server

Though SQL Profiler is not available in Express Edition, still you can leverage trace functionality with any of the below mentioned approaches :

ExpressProfiler available at CodePlex
Or You can install developer Edition and then use profiler to trace the sql statements.
Use Trace Flag 4032 that will log all the sql from all clients to the errorlog.

Note : This will log all the sql from all the clients to the error log, so better turn it off once you are done with tracing. The downside of this trace flag is that it has to be applied at the startup using ;-T4032 and sql server has to be restarted.

    DBCC TRACEON(4032,3605,-1)

4. You can initiate server side trace  as shown below :

/***********************************************/
/* Server Side Trace                           */
/***********************************************/
-- Declare variables
DECLARE @rc INT
DECLARE @TraceID INT
DECLARE @maxFileSize bigint
DECLARE @fileName NVARCHAR(128)
DECLARE @on bit

-- Set values
SET @maxFileSize = 5
SET @fileName = N'C:\SQLTrace'
SET @on = 1

-- Create trace
EXEC @rc = sp_trace_create @TraceID output, 0, @fileName, @maxFileSize, NULL

-- If error end process
IF (@rc != 0) GOTO error

-- Set the events and data to collect
EXEC sp_trace_setevent @TraceID, 41,  1, @on
EXEC sp_trace_setevent @TraceID, 41, 12, @on
EXEC sp_trace_setevent @TraceID, 41, 13, @on
EXEC sp_trace_setevent @TraceID, 41, 14, @on
EXEC sp_trace_setevent @TraceID, 41, 15, @on
EXEC sp_trace_setevent @TraceID, 41, 16, @on
EXEC sp_trace_setevent @TraceID, 41, 17, @on

-- Set Filters
-- filter1 include databaseId = 6 .. **Change your database ID *** HERE !!
EXEC sp_trace_setfilter @TraceID, 3, 1, 0, 6
-- filter2 exclude application SQL Profiler
EXEC sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'

-- Start the trace
EXEC sp_trace_setstatus @TraceID, 1

-- display trace id for future references
SELECT TraceID=@TraceID
GOTO finish

-- error trap
error:
SELECT ErrorCode=@rc

-- exit
finish:
GO

-- to find trace ID

SELECT * FROM :: fn_trace_getinfo(default)

--- to stop the trace

sp_trace_setstatus traceid, 0 --- here 0 is the trace ID which we got from above query

--Load the trace file into a new table in sql server

SELECT * INTO sqlTableToLoad
FROM ::fn_trace_gettable('C:\SQLTrace\trace.trc', DEFAULT)


Indexes in a big table. (About 400 mil of rows)

Since you are optimizing for reads you could simply add one optimal index per query. So if your query is

select price_per_unit from Sales where exclude = 0 and partner = 42

Create the index (exclude, partner) include (price_per_unit).

You say that writes and disk spaces are not of significant concern. So add the optimal indexes to support your reads.


The preemptive_xe_* wait types are associated with Extended Events from what I understand and can find. Considering that and your first sentence:

  We currently use a monitoring tool which shows us our top wait stats either by number of waiting tasks or total wait time.

I would start looking at your monitoring tools as the culprit. However being that your data from Paul's script shows average wait is low I would not put to much concern into it right now.

Your main challenge here that I think you may be overlooking is your user's told you there was a slow down in the system/application. Most will always point to the database/server as the culprit. My general next question when someone tells me that is "what portion of the system is slow". If they tell me things like pages loading slowly, and they are not actually executing any ad-hoc reports or queries, I ask them to check with the server folks or application admins to ensure nothing else is messed up. Then while they are doing that I will go check the database server performance. However as you have shown if the XE wait type is the only one above 10% or so I don't think the issue is with the database server.

If you want to find the culprit of that wait type you can begin querying sys.dm_os_waitting_tasks, this will show you the session_id and wait that is occurring. You can also use sp_WhoIsActive to get similar information a bit more easily. I would bet you are going to find it to be sessions associated with your monitoring tool.


sql server: trigger to update in sum after update

I'd like suggest another approach.

Instead of adding or deleting one record, you can use the same trigger for both insert and delete, -(Do you really need to count records after update?)-, and then you can count all records and set members field.

Just keep in mind that you can insert or delete more than one record, you should consider an INNER JOIN with inserted and deleted tables.

CREATE TABLE Artists (artistName nvarchar(200), members int);
CREATE TABLE Plays   (artistName nvarchar(200), share int);

INSERT INTO Artists VALUES ('Eric', 0), ('Luca', 0);
GO

CREATE TRIGGER trgPlays
ON Plays
AFTER insert, delete, update
AS

    IF EXISTS (SELECT 1 FROM inserted)
    BEGIN
        UPDATE     Art
        SET        members = (SELECT count(*) FROM Plays WHERE artistName = i.artistName)
        FROM       inserted i
        INNER JOIN Artists Art
        ON         Art.artistName = i.artistName
    END
    ELSE IF EXISTS (SELECT 1 FROM deleted)
    BEGIN
        UPDATE     Art
        SET        members = (SELECT count(*) FROM Plays WHERE artistName = d.artistName)
        FROM       deleted d
        INNER JOIN Artists Art
        ON         Art.artistName = d.artistName
    END
GO

SELECT artistName, members
FROM   Artists
GO

artistName | members
:--------- | ------:
Eric       |       0
Luca       |       0

INSERT INTO Plays VALUES ('Eric', 1), ('Eric', 2), ('Luca', 1);
INSERT INTO Plays VALUES ('Eric', 3);
DELETE FROM Plays WHERE artistName = 'Eric' AND share=2;
GO

SELECT artistName, members
FROM   Artists
GO

artistName | members
:--------- | ------:
Eric       |       2
Luca       |       1

dbfiddle here


PostgreSQL array column, querying and grouping by array field

Use the "overlaps" operator:

select *
from  queryables
where array[users_who_like_ids]  && ARRAY['1','78','80'];

Your design is really quesionable: first why are you storing numbers as strings? And secondly why don't you store this as a properly normalized one-to-many relationship?


Invalid object name 'ApexSQL.ApexSQL_SourceControl.Objects'

Found it  - under linked servers triggers.


How to to select data from a large MySQL table? Is there any best solution?

The best solution for pagination is not to use OFFSET.  Instead "remember where you left off".  See http://mysql.rjweb.org/doc.php/pagination

However, there are cases where that is difficult.  In your example of

SELECT * FROM TABLE_NAME
    WHERE CONDITION                -- This
    ORDER BY COLUMN1 LIMIT 0,100;

This works nicely.  The 1st page will take only as long as fetching the 100 rows for it.  And the 1000th page will be delivered as fast as the 1st.

SELECT * FROM TABLE_NAME
    WHERE id > $left_off            -- where you left off
    ORDER BY id LIMIT 100;

But if the 'condition' is too messy, it may have to gather all the rows, and only then figure out where you left off.  JOINs often add to the messiness.

If you would like to give the specific query, plus SHOW CREATE TABLE, we can discuss your situation.

  LIMIT will work once the temporary result table is formulated

It depends on whether a single (possibly 'composite') index can short circuit things.

What you really need to worry about is "OFFSET must step over the skipped rows."  That's why "left off" may work much better.


When PostgreSQL query <Value returns =Values


How to get postgres to resume processes after it hangs when the RAID drive hosting the tablespace times out

You very likely have one or more failing disks, or a failing RAID controller. This would be a really good time to migrate to another host.

Also, RAID 0 across eight disks? That's asking for trouble.

In a case like this PostgreSQL will be waiting in a system call into the kernel, like a pwrite(), fsync() or similar. PostgreSQL isn't being tolerant or intolerant of your raid controller issues. It's just patiently waiting until the kernel finishes doing what PostgreSQL asks it to do and returns control to PostgreSQL. This is exactly what should happen. The problem is that the controller isn't responding so the system call never returns. You should be able to confirm this with strace or ps (though I'm not sure of the exact options on OS X).


We are taking COPY_ONLY (non-truncating) transaction log backups every
  2 hours.

Reference: Copy-Only Backups (SQL Server)

You need to understand the difference between COPY_ONLY and without COPY_ONLY?

A copy-only backup is a SQL Server backup that is independent of the sequence of conventional SQL Server backups. Usually, taking a backup changes the database and affects how later backups are restored. However, occasionally, it is useful to take a backup for a special purpose without affecting the overall backup and restore procedures for the database. Copy-only backups serve this purpose. +

A copy-only log backup preserves the existing log archive point and, therefore, does not affect the sequencing of regular log backups. Copy-only log backups are typically unnecessary. Instead, you can create a new routine log backup (using WITH NORECOVERY) and use that backup together with any previous log backups that are required for the restore sequence. However, a copy-only log backup can sometimes be useful for performing an online restore. For an example of this, see Example: Online Restore of a Read-Write File (Full Recovery Model).

  Our requirement is to take the incremental log backup every 2 hours
  and not cumulative.

Schedule transaction log backup every 2 hours.  Make sure your business stack holders understand the consequence of taking transaction log backup every 2 hours.

Read this answer (What's a good SQL Server backup schedule?) for more a better understanding.  Here is (Transaction Log Backups (SQL Server) ) Microsoft's official standing on this.

  I did not find an option in BACKUP LOG TSQL command. Is there a way to
  do this?

There is enough example at the bottom of this page.

BACKUP (Transact-SQL)

As a side note ( I am advocating this) many Database professionals use Ola Hallengren's solution.

https://ola.hallengren.com/sql-server-backup.html


You can use an inner join between customers and orders where the via has the wanted values and a NOT EXISTS to check, that no unwanted via exists.

SELECT c1.*
       FROM customers c1
            INNER JOIN orders o1
                       ON o1.customer_id = c1.customer_id
       WHERE o1.via IN ('WEB', '')
             AND NOT EXISTS (SELECT *
                                    FROM orders o2
                                    WHERE o2.customer_id = c1.customer_id
                                          AND o2.via = 'POS');

(If the empty via is actually a NULL not an empty string, change o1.via IN ('WEB', '') to coalesce(o1.via, '') IN ('WEB', '') or to o1.via = 'WEB' OR o1.via IS NULL.)

Alternatively you can do it just with EXISTS and NOT EXISTS.

SELECT c1.*
       FROM customers c1
       WHERE EXISTS (SELECT *
                            FROM orders o1
                            WHERE o1.customer_id = c1.customer_id
                                  AND o1.via IN ('WEB', ''))
       AND NOT EXISTS (SELECT *
                              FROM orders o2
                              WHERE o2.customer_id = c1.customer_id
                                    AND o2.via = 'POS');

(Again, if the empty via is actually a NULL not an empty string, change o1.via IN ('WEB', '') to coalesce(o1.via, '') IN ('WEB', '') or to o1.via = 'WEB' OR o1.via IS NULL.)
Again you can use a join and NOT EXISTS.

SELECT c1.*
       FROM customers c1
            INNER JOIN orders o1
                       ON o1.customer_id = c1.customer_id
       WHERE o1.via = 'APP'
             AND NOT EXISTS (SELECT *
                                    FROM orders o2
                                    WHERE o2.customer_id = c1.customer_id
                                          AND o2.via <> 'APP');

Or just use EXISTS/NOT EXISTS.

SELECT c1.*
       FROM customers c1
       WHERE EXISTS (SELECT *
                            FROM orders o1
                            WHERE o1.customer_id = c1.customer_id
                                  AND o1.via = 'APP')
       AND NOT EXISTS (SELECT *
                              FROM orders o2
                              WHERE o2.customer_id = c1.customer_id
                                    AND o2.via <> 'APP');


Logging the Execution times of dynamically constructed mysql stored procedures

You could capture the mysqld's uptime or the DB Server's time at the start and end of your code.

Perhaps something like this

SUGGESTION #1

SELECT variable_value INTO @t1
FROM INFORMATION_SCHEMA.GLOBAL_STATUS
WHERE variable_name='uptime';
<Your Stored Procedure Code>
SELECT variable_value INTO @t2
FROM INFORMATION_SCHEMA.GLOBAL_STATUS
WHERE variable_name='uptime';
SET @RunningTimeSec = @t2 - @t1;
SET @RunningTime = SEC_TO_TIME(@RunningTimeSec);
INSERT INTO sometable ... VALUES (...,@RunningTimeSec,...);

SUGGESTION #2

SET @t1 = UNIX_TIMESTAMP(NOW());
<Your Stored Procedure Code>
SET @t2 = UNIX_TIMESTAMP(NOW());
SET @RunningTimeSec = @t2 - @t1;
SET @RunningTime = SEC_TO_TIME(@RunningTimeSec);
INSERT INTO sometable ... VALUES (...,@RunningTimeSec,...);

GIVE IT A TRY !!!


How can I connect to a network MySQL server using ODBC?

Is FOO a DSN or a server name? on workbench you need the server name, mysql username and the mysql password. Thats all you need to connect to your mysql server using mysql workbench.


PostgreSQL partial index unused when created on a table with existing data

Run ANALYZE after the index has been added. And make sure the column deprovision has statistics. How to verify?

Basic statistics in pg_class:

SELECT relname, relkind, reltuples, relpages
FROM   pg_class
WHERE  oid = 'schema_defs'::regclass;

Data histograms per column in pg_stats (pg_statistics):

SELECT attname, inherited, n_distinct
     , array_to_string(most_common_vals, E'\n') AS most_common_vals
FROM   pg_stats
WHERE  tablename = 'schema_defs'
AND    attname = 'deprovision';

The manual:

  The PostgreSQL query planner relies on statistical information about
  the contents of tables in order to generate good plans for queries.
  These statistics are gathered by the ANALYZE command, which can be
  invoked by itself or as an optional step in VACUUM. It is important to
  have reasonably accurate statistics, otherwise poor choices of plans
  might degrade database performance.

  The autovacuum daemon, if enabled, will automatically issue ANALYZE
  commands whenever the content of a table has changed sufficiently.
  However, administrators might prefer to rely on manually-scheduled
  ANALYZE operations, particularly if it is known that update activity
  on a table will not affect the statistics of "interesting" columns.
  The daemon schedules ANALYZE strictly as a function of the number of
  rows inserted or updated; it has no knowledge of whether that will
  lead to meaningful statistical changes.

In your case, analyzing just the one column would do the job:

ANALYZE table_name (deprovision);

While being at it, it makes no sense to have the index on the column deprovision. Given the predicate WHERE deprovision = 0 it does not carry additional information. You might as well use a constant expression:

CREATE INDEX schema_defs_deprovision ON schema_defs ((true))
WHERE deprovision = 0;

Just a proof of concept. This would not be any more useful. In this special case you wouldn't need an index column at all, but you must provide at least one column or expression. So use the primary key (since it does not change and is indexed anyway, you don't introduce more restrictions / overhead costs) or any other small column (<= 8 bytes) that might be useful for queries.

CREATE INDEX schema_defs_deprovision ON schema_defs (id)
WHERE deprovision = 0;

sqlfiddle.com

The demo fiddles are misleading.

  Partial Index Created Before Inserts (Index working properly)

Your demo table only has 4 rows. Postgres should not be using the index. A similar problem, just the other way round. Postgres does not have statistics on the table immediately after creating it - until the first run of ANALYZE. Then it knows there are only 4 rows and won't touch the index any more.
So why does it work properly on your second demo? The manual:

  For efficiency reasons, reltuples and relpages are not updated
  on-the-fly, and so they usually contain somewhat out-of-date values.
  They are updated by VACUUM, ANALYZE, and a few DDL commands such as
  CREATE INDEX.

Bold emphasis mine. If you create the index after inserting rows, those basic statistics in pg_class are updated. But only those, not the detailed statistics in pg_statistic:

  Entries in pg_statistic are updated by the ANALYZE and VACUUM ANALYZE
  commands, and are always approximate even when freshly updated.

To make Postgres use the partial index (in particular in it's original form that is not useful for anything else) you also need the data histogram in pg_statistic informing the query planner that deprovision = 0 is actually a rare case, so it will pay to use the index.

Autovacuum takes care of this. It schedules VACUUM and ANALYZE automatically. But there is a time frame (depends on settings and load) between writes to the table and the next ANALYZE run. If you run queries immediately after table creation or changes to the table, those latest changes are not reflected in the statistics, yet. Never mind if that does not change statistics in a relevant way. If it does, for instance after a large INSERT or immediately after table creation, run ANALYZE manually to get proper query plans.

Note that temporary tables are not covered by autovacuum at all. You always need to run ANALYZE manually on those if you need it:

Are regular VACUUM ANALYZE still recommended under 9.1?

I don't know how you configured autovacuum and whether / when you run ANALYZE manually. But I noticed in the past that sqlfiddle can be misleading due to missing / outdated statistics.

I would be very interested how ANALYZE is handled behind the curtains on sqlfiddle. It may be best not to do anything special, but some info would be welcome. Maybe one basic webpage per available RDBMS version?

Demo

I created an SQL Fiddle to demonstrate the effects of CREATE INDEX and ANALYZE on the various statistics.

The effects show (at least) on the first run for me. May not be reproducible on later runs, you would have to create a new schema and run again.

First we see neither basic statistics in pg_class:

relname      reltuples  relpages
schema_defs  0          0

Nor any entries for deprovision in pg_statistics at all (no result).
Postgres has no idea what's in the table and defaults to using the index - which is a bad choice!

After CREATE INDEX, we see basic statistics, but still no data histogram in pg_statistics.

After ANALYZE we see both.

With proper statistics, Postgres now uses a sequential scan (good choice, even if there is an index - it would be more expensive for so few rows).


Run Server Agent Job on Schedule only if CPU is idle

Or you can have one job that runs every 5 minutes with 2 steps:

Step #1 -- checks the CPU Usage (PowerShell), just google and there are plenty of scripts out there; If <20% exit with success go to Step 2, Else Exit with Error and finish the job (No Step #2)

Step#2 -- is the actual UPDATE.

Or as Ken suggested.


PostgreSQL: PANIC: heap2_redo: unknown op code 32

http://www.postgresql.org/docs/9.3/static/release-9-3-3.html

  Fixing this required changing the WAL record format for tuple
  freezing. While this is no issue for standalone servers, when using
  replication it means that standby servers must be upgraded to 9.3.3 or
  later before their masters are. An older standby will be unable to
  interpret freeze records generated by a newer master, and will fail
  with a PANIC message. (In such a case, upgrading the standby should be
  sufficient to let it resume execution.)


Login cannot view definition even when granted the permission

Is UAC enabled? UAC can truncate the user's list of group memberships


How to reorganize MS SQL Server database

Edit 1: Aaron noted in a comment that compression can be beneficial here. Good point. Compression options include columnstore indexes (clustered or nonclustered - let's not go into details at this point), row compression and page compression.
As you know, backup and restore will give you a binary copy of the database, so it won't help you.
Shrink will fragment the indexes even more since it move pages towards the beginning of the file without consideration of the page ordering of an index.
So you are left with index reorg or rebuild. Rebuild is more thorough since it creates a new index. But it need space for this. So if you shrink and then rebuild, then the rebuild will create a new index "at the end of the file" possible making the file grow (depending on how much free space you left when you did the shrink).
So, it is a bit of a contradiction to both have stuff nicely aligned and have no free space. What you can hope for is to have free space corresponding to you your largest allocation (index/heap) in the file. I.e., "working space" for the largest one.
(If that isn't acceptable, then you are left with deciding which allocations are too large to have spare room for (remember, you only need spare room for one). For indexes you can drop them, shrink and then create. For the data (clustered index and heap) you'd have to export outside SQL Server, truncate the table, shrink and then import the data.
Edit 2: Nikita added a good point on moving the data to a different filegroup as an option to exporting it outside SQL Server. Good point and definitely worth considering. If you decide to go the "get the data out of the filegroup" route.)
How to go about this? Use Ola's scripts as suggested by Nikita for your B-trees. And for your heaps (I hope you don't have that many) you can for instance use my stored procedure.
So, shrink first, then rebuild your b-trees and heaps. And it can take a very long time, especially the shrink (depends on if you have LOBs, heaps etc).
Come to think about it, we have two options for exporting and importing the data:
BACPAC. This will give you a zipped file containing an XML file describing your tables and BCP files with the data. And then you use a tool to import based on this. You can use BACPACs from SSMS (right-click a database and the databases folder), sqlpackage.exe and of course there are a few Powershell options. BACKPACs are designed to work with Azure SQL database so it will choke on anything what isn't available there, AFAIK.
SSMS Generate Script. You find this if you right-click a database. This will create a .sql file containing DDL and INSERT. Cross your fingers that it does it right. And for 500 GB..., I have my doubts. Anyhow, you can also access this functionality using SSIS, if that is beneficial for you.


You may also need to exclude unique indexes


Mysql error : Slave SQL Could not execute ...on table

The 3rd node can not update record in cron_schedule because the record is missing.
That could happen because some binlog events were lost when the servers crashed.

First try to skip those errors and start replication.
Run this on the faulty slave:

pt-slave-restart

The script skips binlog events that return error and start replication. In some cases a slave is heavily inconsistent and replication never recovers. In that case you need to rebuild the slave. But if replication is restarted and running proceed further.

Now you need to find all inconsistencies and sync data on the master and its slaves.

First run pt-table-checksum on the master(add user/password options if needed):

pt-table-checksum -q

It will show you which tables are inconsistent i.e. have different data on the master and slaves.

Then let pt-table-sync fix the inconsistencies. Run this on the master:

pt-table-sync --execute --replicate percona.checksums --noforeign-key-checks localhost

Afterwards you may want to run pt-table-checksum again to make sure the slaves are in-sync


Is it possible to filter the list of databases in the Object Explorer of the SSMS?

This is now possible if you are using SQL Server Management Studio 2016. You can filter databases by Name, Owner and Creation Date.

I'm using SSMS build number 13.0.15900.1 - you can download a stand-alone version from Microsoft or just use this link: https://msdn.microsoft.com/en-us/library/mt238290.aspx


Trying to find the last time that a value has changed

These two queries rely on the assumption that Taco_value always increases over time.
;WITH x AS
(
  SELECT Taco_ID, Taco_date,
    dr = ROW_NUMBER() OVER (PARTITION BY Taco_ID, Taco_Value ORDER BY Taco_date),
    qr = ROW_NUMBER() OVER (PARTITION BY Taco_ID ORDER BY Taco_date)
  FROM dbo.Taco
), y AS
(
  SELECT Taco_ID, Taco_date,
    rn = ROW_NUMBER() OVER (PARTITION BY Taco_ID, dr ORDER BY qr DESC)
  FROM x WHERE dr = 1
)
SELECT Taco_ID, Taco_date
FROM y
WHERE rn = 1;

An alternative with fewer window function madness:
;WITH x AS
(
  SELECT Taco_ID, Taco_value, Taco_date = MIN(Taco_date)
  FROM dbo.Taco
  GROUP BY Taco_ID, Taco_value
), y AS
(
  SELECT Taco_ID, Taco_date,
    rn = ROW_NUMBER() OVER (PARTITION BY Taco_ID ORDER BY Taco_date DESC)
  FROM x
)
SELECT Taco_ID, Taco_date FROM y WHERE rn = 1;

Examples at SQLfiddle

Update
For those keeping track, there was contention over what happens if Taco_value could ever repeat. If it could go from 1 to 2 and then back to 1 for any given Taco_ID, the queries will not work. Here is a solution for that case, even if it isn't quite the gaps & islands technique that someone like Itzik Ben-Gan may be able to dream up, and even if it isn't relevant for the OP's scenario - it may be relevant to a future reader. It's a little more complex, and I also added an additional variable - a Taco_ID that only ever has one Taco_value.
If you want to include the first row for any ID where value didn't change at all in the entire set:
;WITH x AS
(
  SELECT *, rn = ROW_NUMBER() OVER
    (PARTITION BY Taco_ID ORDER BY Taco_date DESC)
  FROM dbo.Taco
), rest AS (SELECT * FROM x WHERE rn > 1)
SELECT
  main.Taco_ID,
  Taco_date = MIN(CASE
    WHEN main.Taco_value = rest.Taco_value
    THEN rest.Taco_date ELSE main.Taco_date
  END)
FROM x AS main LEFT OUTER JOIN rest
ON main.Taco_ID = rest.Taco_ID AND rest.rn > 1
WHERE main.rn = 1
AND NOT EXISTS
(
  SELECT 1 FROM rest AS rest2
   WHERE Taco_ID = rest.Taco_ID
   AND rn < rest.rn
   AND Taco_value <> rest.Taco_value
)
GROUP BY main.Taco_ID;

If you want to exclude those rows, it's a bit more complex, but still minor changes:
;WITH x AS
(
  SELECT *, rn = ROW_NUMBER() OVER
    (PARTITION BY Taco_ID ORDER BY Taco_date DESC)
  FROM dbo.Taco
), rest AS (SELECT * FROM x WHERE rn > 1)
SELECT
  main.Taco_ID,
  Taco_date = MIN(
  CASE
    WHEN main.Taco_value = rest.Taco_value
    THEN rest.Taco_date ELSE main.Taco_date
  END)
FROM x AS main INNER JOIN rest -- ***** change this to INNER JOIN *****
ON main.Taco_ID = rest.Taco_ID AND rest.rn > 1
WHERE main.rn = 1
AND NOT EXISTS
(
  SELECT 1 FROM rest AS rest2
   WHERE Taco_ID = rest.Taco_ID
   AND rn < rest.rn
   AND Taco_value <> rest.Taco_value
)
AND EXISTS -- ***** add this EXISTS clause *****
(
  SELECT 1 FROM rest AS rest2
   WHERE Taco_ID = rest.Taco_ID
   AND Taco_value <> rest.Taco_value
)
GROUP BY main.Taco_ID;

Updated SQLfiddle examples


User in multiple windows groups mapped to sql logins

If I am understanding your question correctly, then yes the user will be able to run both applications with database connection issues.

If User1 is part of Windows Groups Group1 and Group2, and Group1 is mapped as a SQL Server login, as well as Group2 is mapped to a different SQL Server login.  And if Group1 is mapped to a database user in Database1 and Group2 is mapped as a database user in Database2, then User1 should have access to both Database1 and Database2.  And if Group1 and Group2 are both mapped to TheOnlyDatabase, then the user will have the accumulations of both database users' permissions.

This will return only one of the database users (as expected):

select user_name() as database_user_name

This will return 1 for both queries if the users is a member of both Windows Groups:

select is_member('Domain\Group1') as member_of_group_1

select is_member('Domain\Group2') as member_of_group_2


how do I connect to mysql database from visual studio without requiring SSL connection?

You might want to explicitly add this to the connection string - "SslMode=none"
Your connection should look like this:

  MySqlConnection conn = new MySqlConnection("user id=root;server=localhost;persistsecurityinfo=True;database=edutechsys"); SslMode=none; MySqlCommand cmd = new MySqlCommand("SELECT * FROM
  TblLogin WHERE UserName = '" + TxtUserName.Text.Trim() + "' and
  Password = '" + TxtPassword.Text.Trim() + "'"); conn.Open();
  cmd.ExecuteNonQuery(); DataTable dt = new DataTable();
  MySqlDataAdapter da = new MySqlDataAdapter();


MySQL Update old data after adding new column

You gave away the answer; here are some more details.

create a TEMPORARY MyISAM table with a 2-column PK, second column being AUTO_INCREMENT
INSERT INTO myisam_table (business_id, id) SELECT business_id, id ORDER BY business_id, id -- to get it to fill in review_number.
Do a multi-table UPDATE ... SET your_table.review_number = myisam_table.review_number between the two tables to copy those ids.