MySQL `innodb_log_file_size`: how big is too big?

You have to find the right tradeoff between recovery time in the event of a crash and how much write throughput your database will be expected to support.

Percona has a great post discussing a similar scenario to yours, with a 64GB buffer pool, and about 2/5ths of the way into the article you can see a nice comparison he does between a 2GB and 15GB logfile.

15GB allows innodb enough log space to 'smoothly' flush pages out of the log, whereas with 2GB there is more pressure to keep the log from filling up, which ultimately hurts performance as pages have to be evicted. In the benchmarks they cite, performance is 20% lower as a result.

TL;DR you can't really go wrong with a larger log file, esp. if you have a slave to failover to in the event of a crash, but you can certainly hurt performance with one that is too small.


How should I handle unknown primary keys?

As you don't know if there's any logic in the keys you can't tell if a huge or negative number will lead to a key violation.
Without knowledge of the application I'd suggest importing the previous pupils in the new software to get a regular id is the way to go.


What are the benefits vs. drawbacks to using a clustered index in SQL Server

This is a complicated topic, I will try to be brief.

Benefit: Performance boost if implemented correctly

Drawback: requires understanding of clustered/nonclustered indexes and storage implications

Heaps (tables without clustered indexes) are fine for loading but can lead to poorly performing queries especially when dealing with millions of records as in your case.  Even though you sorted your data prior to inserting, SQL Server does not know this and will simply put new records wherever there is room.
If there are not even any non-clustered indexes, queries against the heap will result in full table scans.

Assuming you are pre-sorting the data by datetime, and assuming that your date column is datetime, clustering on this column will result in extremely fast queries as long as they filter against the date.  There are drawbacks, however, one of which being SQL Server will addding a 4 byte uniquifier to the index.  Also any additional indexes defined will use the clustered index as the key, so you are in effect adding 12 bytes (8 bytes for datetime + 4 bytes for uniquifier) to the storage requirements for every value in the index.

I would add synthetic key instead.  Make it a self incrementing integer value.  Make it the primary key of the table and SQL Server will by default add a clustered index to that column.  No uniquifier will be needed, so additional indexes will only require 4 bytes of storage for the key.

Note:
 varchar foreign keys can lead to poor performance as well.  Change the base table to have an integer primary key instead.


What is the expected performance impact when dropping a large table

Deleting in batches and then optimizing is really the worst thing you can do.  Each delete will be performed as a transaction and each optimize will effectively recopy what remains of the giant table.

Think of it like this.  You have a 5 gig table, delete 0.5 gigs, optimize (copy 4.5 gig file), delete 0.5 gigs (copy a 4.0 gig file) etc..

And really the overhead associated with each optimize is much more than just copying that much data on a filesystem since it's tantamount to inserting all those rows into a new table and all the transactional overhead that comes along with it.

Dropping a table should be the most efficient way to do it

Edit:  Here is some thorough benchmarks though

http://www.mysqlperformanceblog.com/2011/04/20/drop-table-performance/


MySQL partition by date doesn't pruning the query

BY RANGE(MONTH(date)) does not prune.

Even if id did prune, it would probably not provide any performance benefit over having a non-PARTITIONed table with a suitable index.  May we see your queries?

BY RANGE(TO_DAYS(date)) does prune.  But you need to add a new partition periodically, and (optionally) DROP an old partition.

Blog on partitioning.


Postgres, benefit of named schema va public schema?

what is the benefit of a non-'public' schema.

A schema provides a standardized level of logical organization. If you've never used them, it's like asking what's the value of directories. You don't technically need to have them, but you can use them to

Organize & Componentize
Control access
Reduce namespace-collisions

Also worth mentioning, the query planner can plan across schema boundaries. In the same sense that tools that operate on files can work across directories.

  Perhaps more to the point of the Question you could explain that there is nothing special about the schema named public, just an arbitrarily chosen name for the default schema. – Basil Bourque 1 min ago

^ Valid point too. If you don't use your own customized schema, you're still using schema. It's just named public and placed in your search_path by default.


Find duplicate records across one-to-many relationship tables

One way to do this (check the SQLfiddle):

select
    p1.id as id1,
    p2.id as id2
from people p1
  join people p2
    on  p1.first_name = p2.first_name
    and p1.last_name = p2.last_name
    and p1.id < p2.id
where not exists
    ( select 1
      from
      ( select *
        from attributes a1
        where a1.person_id = p1.id
      union all
        select *
        from attributes a2
        where a2.person_id = p2.id
      ) g
      group by field, field_value
      having count(*) <> 2
   ) ;

and another:

select
    p1.id as id1,
    p2.id as id2
from people p1
  join people p2
    on  p1.first_name = p2.first_name
    and p1.last_name = p2.last_name
    and p1.id < p2.id
where not exists
    ( ( select field, field_value
        from attributes a1
        where a1.person_id = p1.id
      union
        select field, field_value
        from attributes a2
       where a2.person_id = p2.id
      )
    except
      ( select field, field_value
        from attributes a1
        where a1.person_id = p1.id
      intersect
        select field, field_value
        from attributes a2
        where a2.person_id = p2.id
      )
    ) ;

Intersect has higher precedence than except/minus, at least in Postgres and SQL Server. Just to be safe, you can use parentheses, to ensure the precedence.


How to configure MySQL master master replication but use only one server for writing?

To use only one server for writing you point your application at only one server. You can do this explicitly in your application's database connection configuration. Or you can have a load balancer only forward connections to one node.

If you want read-write splitting either you have to code that into your application or you need an advanced load balancer like MaxScale that is fully transaction aware.


Can't set log_bin_trust_function_creators=1 on machine startup - MySQL 8.0

Put it under mysqld, there it should be found and set
[mysqld]
log-bin-trust-function-creators = 1


How to increase the number of activity in pg_stat_activity?

pg_stat_activity has one row per server process.
See PostgreSQL documentation in table 28.1:

pg_stat_activity One row per server process, showing information related to the current activity of that process, such as state and current query.

and later down:

The pg_stat_activity view will have one row per server process, showing information related to the current activity of that process.

If you want more, you need to increase the number of connections (backends) which default at 100, see this other part of the documentation

max_connections (integer)
Determines the maximum number of concurrent connections to the database server. The default is typically 100 connections, but might be less if your kernel settings will not support it (as determined during initdb). This parameter can only be set at server start.


THROW not including name of calling procedure unless custom error-message is specified

Code the CATCH like this:

BEGIN CATCH
    DECLARE @msg nvarchar(4000) = ERROR_MESSAGE()
    DECLARE @errno int = 50000 + ERROR_NUMBER();
    DECLARE @state int = ERROR_STATE();
    THROW @errno, @msg, @state;
END CATCH

That allows you to pass all the parameters into the THROW statement so it can send them back to the caller.

I tested that like this:

USE tempdb;
IF OBJECT_ID('dbo.usp_division_err', 'P') IS NOT NULL
DROP PROCEDURE dbo.usp_division_err;
GO
CREATE PROCEDURE dbo.usp_division_err AS
SET NOCOUNT ON;
BEGIN TRY
    EXEC('select 1/0');
END TRY
BEGIN CATCH
    DECLARE @msg nvarchar(4000) = ERROR_MESSAGE()
    DECLARE @errno int = 50000 + ERROR_NUMBER();
    DECLARE @state int = ERROR_STATE();
    THROW @errno, @msg, @state;
END CATCH
GO
EXEC dbo.usp_division_err;

The results:

  Msg 58134, Level 16, State 1, Procedure dbo.usp_division_err, Line 10 [Batch Start Line 16]
  Divide by zero error encountered.

To obtain the native error number you just need to subtract 50000 from the reported error number.  Since the parameters for THROW are optional, there must be two code-paths inside THROW, one that reports the procedure name, and one that doesn't.  I'll leave it to the reader to decide which code-path does that.


Byte ordering for multibyte characters in SQL Server versus Oracle

What you are seeing is Little-Endian encoding that SQL Server uses to store Unicode characters (more precisely, it uses UCS-2 LE).

More on Little-Endian here: Difference between Big Endian and little Endian Byte order

I don't know how it was possible that

  When I use DUMP in Oracle or convert to VARBINARY in SQL Server the
  data matches exactly except for the bytes for this character

All the Unicode characters stored in SQL Server, converted to binary, are "inverted", I mean, to see the real codes you should divide them in groups of 2 bytes and invert the order within every pair.

Example:

declare @str varchar(3) = 'abc';
declare @str_n nvarchar(3) = N'abc';

select cast(@str as varbinary(3));
select cast(@str_n as varbinary(6));

The result is

0x616263

0x610062006300

As you see in case of Unicode characters bytes are inverted:
"a" is represented as 0x6100 and not as 0x0061.

The same story is about 0x25E6 that is real Unicode code while in binary representation in SQL Server you see it as 0xE625, i.e. inverted.


Why can't I connect to SQL server 2017 LocalDB shared instance?

Seems to be a bug in LocalDb v2017 and higher. I tested all available versions, starting from v2012 SP4. Up to and including v2016 SP2, the friendly share name "(localdb)\.\SHAREDNAME" is accepted in the connection request. When using v2017 or higher, the request always results in an "instance not found" error.

I've also observed that querying all installed LocalDb versions with sqllocaldb.exe v results in an error message for v2016 and higher:

  Windows API call "RegGetValueW" returned error code: 0.

Not sure whether this behavior might be linked to the original issue.

UPDATE

Applying the registry fix linked in the question solves the version query.

After some more digging, I think I may have discovered the cause for the connection failure. When SQLLocalDb up to v2016 is installed on the local machine, the share feature works as follows:

In the registry, a new key is created in Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server Local DB\Shared Instances\{SharedName}. This key contains a string value  InstanceName, which stores the pipe name, as visible in the following screenshot:

When the corresponding instance is started, it always picks this pipe name. The name stays persistent, i.e. when the instance is restarted, the pipe name is reused.

This logic is destroyed when LocalDb v2017 or higher is installed: When an instance with a shared name is started, the pipe name in the registry is ignored. Instead, a completely different pipe name appears when querying the instance via sqllocaldb i {instance}.

This is probably also the reason why the connection can't be established, since the pipe names don't match. Editing the registry value to match the actual pipe name solves the issue, but only until the instance is restarted.


Is "CREATE INDEX` in MySQL a Linear Operation?

Index creation is essentially a sort operation, so is at best has a growth complexity of the order n log n on average (you might find it does better in some cases, and is not likely to do much worse).

If all your relevant data pages fit into RAM and are already in RAM, and the index will fit also, and your DBMS does not force index pages to be written before the creation is complete (so index blocks are not updated on disk multiple times during the operation), then the speed of writing the resulting index to disk will be more significant than the time taken to perform the sort - so you might find you get closer to a linear relationship between number of rows and the time the index creation takes - but if you assume the worse case you are less likely to be unpleasantly surprised!

Remember that unless you are not going to stop access to the production database during the operation any index create will be competing for IO bandwidth and/or locks with other activity, so you should try to account for this if you are doing your timing estimation tests on another system even if it is identically configured.


PostgreSQL: Find row with JSON object attribute in array

You need to unnest the array elements and combine that with an EXISTS condition

select t.*
from arr_test t
where exists (select *
              from jsonb_array_elements(t.data -> 'my_arr') as x(o)
              where x.o ->> 'serial' = 'AAA');

Alternatively you can write that with a contains operator @>:

select t.*
from arr_test t
where exists (select *
              from jsonb_array_elements(t.data -> 'my_arr') as x(o)
              where x.o @> '{"serial": "AAA"}');

Try it on dbfiddle


Deleting data from some but not all tables

Dropping and then recreating all of the foreign keys shouldn't be a problem because you should have them all in your source control system. If that's not the case then you have much bigger problems.

You can generate DROP/CREATE scripts through SSMS. If you right-click on your database then you can go to Tasks->Generate Scripts... and select the tables that you need through the wizard. You'll have to do a lot of cut and paste to get just the FKs. Alternatively, you can dig down to the FKs in the object tree, right-click each one, and generate a script from that menu.

In either case, it sounds like your deploy process for your production system is in some bad shape.


Count nested joined table records only if the table associated via bridge table have two records with given names

Please consider this... not a ready-made solution but a suggestion which should lead you in a proper direction.

  In the first part for contact_compliants, it has IN which means
  either one of the names for nested will increment count. which is not
  my requirement. I want to count only when when both the names matches.

Both? Then your whole idea with JOIN + count(*) will not work.

The problem is clearly visible if you replace the whole SELECT list with *:

SELECT * FROM main_table AS main
INNER JOIN bridge_table_1 AS bt_1 ON bt_1.main_table_id = main.id
INNER JOIN nested_table_1 AS nes_1 ON bt_1.nested_table_1_id = nes_1.id
INNER JOIN bridge_table_2 AS bt_2 ON bt_2.main_table_id = main.id
INNER JOIN nested_table_2 AS nes_2 ON nes_2.id = bt_2.nested_table_2_id
WHERE main.is_active = 'true';
 id | is_active |  name  | main_table_id | nested_table_1_id | id |  name   | main_table_id | nested_table_2_id | id |         name
----+-----------+--------+---------------+-------------------+----+---------+---------------+-------------------+----+-----------------------
  1 | t         | main 1 |             1 |                 1 |  1 | Contact |             1 |                 1 |  1 | nested table second 1
(1 row)

See? The FROM clause sees only one row. All the further FILTER tricks are a no-op. This stems from SQL conceptual workflow which is pretty well described in SELECT statement Description.

Looks like you need outer joins to catch the data you need. But then it will explode the rowcount of your result set.

So you need to rethink the whole query, especially this part which calculates contact_compliants. Maybe try to use EXISTS subqueries instead of JOINs?.

SELECT
  (SELECT count(*) FROM ...
   WHERE EXISTS (...)
   AND EXISTS (...)) AS contact_compliants,
  ... /* more subqueries here */
FROM main_table AS main
WHERE main.is_active;

Side note: I'm not sure if your schema is optimal, maybe the design is too convoluted and you can simplify it?


How can I tell if a SQL Server backup is compressed?

Maybe instead of backing up over and over again to the same file, you should consider using WITH INIT and a new file always. I think it is simpler to manage multiple backup files, all with their own timestamp embedded into the filename, and be able to archive/purge each file individually. When you keep dumping backups to the same file, it just gets bigger and bigger, and harder to manage IMHO. Never mind that you no longer have to care that

Also I am not sure why you would ever be turning backup compression on and off. Have you found a case where disabling it is better? Do you have real use cases where on an edition that supports compression you are taking one-off backups without compression, and using the same file? Why?

Anyway you can always do something very simple like:

BEGIN TRY
    BACKUP DATABASE x TO DISK = 'c:\wherever\x.bak' WITH COMPRESSION, ...;
END TRY
BEGIN CATCH
    BACKUP DATABASE x TO DISK = 'c:\wherever\x.bak', ...;
END CATCH

The error bubbles up immediately before any work is done.

But I still think it is much better to just not use the same file over and over again in the first place. IMHO.


Postgres full text search with unaccent and inflection (conjugation, etc.)

AFAIK, you cannot do what you want with current PostgreSQL full text configurations (dictionaries and parsers/lexers), although some workarounds might do something close to the trick.

I don't know Polish, but I've had similar problems with Spanish (that also has conjugations, etc.), and with the fact that people has got completely accostumed to the fact that Google is able to ignore accents, and they just frequently ignore them as well.

Dictionaries

You can have several dictionaries for PostgreSQL, which can do different things, but basically simplify in some fashion your texts. What you want to do if you want to convert words to its lexemes is to use an ISpell dictionary:

  [...] which can normalize many different linguistic forms of a word into the same lexeme. For example, an English Ispell dictionary can match all declensions and conjugations of the search term bank, e.g., banking, banked, banks, banks', and bank's.

However, this dictionary (at least in Spanish) will only recognize properly accented words, or it won't be able to find out to which lexeme a certain (possibly accented) word corresponds to. This is because the entries in the ISpell dictionaries, such as the Polish ISpell dictionary are written with all the proper accents (or diacriticals) [as it should].

The ISpell Polsih dictionary is composed of two files, one called pl_PL.dic (encoded as ISO-8859-2, as far as I've been able to guess) and one called pl_PL.aff. The first one contains lexemes + rules that apply to them, the second one contains the meanings of those rules. The ISpell software interprets those files to figure out how to transform words into its lexemes [and also how to check whether a spelling is correct or not].

The entries on the .dic file look like:

abecadło/UV
abecadłowy/bxXyY
[...]
Abisyńczyk/NOqsT
abisyński/XxYbyc

The .aff file gives rules for the meanings of the "U" and "V" and all the other letters than follow the / sign. Some if this rules (which I am far from knowing) will thell the software how suffixes or prefixes work for the word abecadło. For instance:

SFX U   ło        le        [^astz]ło

As there is no word like abecadlo or abisynski in this dictionary, if you enter those texts into your search, the dictionary will not return any lexeme.

Possible workaround: Manipulate the dictionary file and duplicate all lines with accented characters with the unaccented equivalent. You probably would need to do something similar with the .aff part of the dictionary.

Probably, you would also need to use a Synonym dictionary to make the accented and unaccented versions of all words have the same meaning.

This is a brute force approach, and, in practice you'd be inventing a "new version of the Polish language, where accented letters are equivalent to their unaccented counterparts". [Don't tell the makers of dictionaries who made them to make sure people would spell correctly ;-)].

I think this approach does have very many risks. I know I wouldn't do it in either Spanish or Catalan, because the presence or absence of a diacritical mark can radically change the meaning of a word ("año" doesn't have much to do with "ano", in Spanish; and considering them synonyms is extremely delicate).

You'll have to evaluate whether this applies to Polish or not.

Alternative: you can just use a combination of a Simple Dictionary and the "filtering" unnacent module. You won't get lexemes, and the transformations this combination is able to do are not so sophisticated... but you'll get the same result when you search for abecadło or abecadlo.

In my case, I ended up settling for this solution.

Second alternative: If you need a text search which has the capability to ignore accents, allow for small misspellings and have a lot of sophisticated possibilities, consider using a solution out of the database, such as Apache Solr. It's obviously a very different approach, and you need some process to make it synchronized with the database.


How can I dynamically back up all SSAS databases on a given instance?

This can in fact be done. There are probably a few ways to do it, and here is a fairly straightforward example. For this solution, you will use a combination of:

A SQL Agent job with a step for each instance that needs backed up (i.e. A step for the development server, the qa server, and for production).
One dynamic SSIS package that is called in each step of the job.
An Execute Script Task that uses the Analysis Management Objects (AMO).

Create the SSIS Package

Variables

VariableName|Scope|Type

BackupDir | Package level or Foreach level | String
DatabaseId | Package level or Foreach level | String
DatabaseName | Package level or Foreach level | String
InstanceForDatasource | Package level | String

Intended for use in the Set Values tab of the SQL Agent job if more than one instance will be backed up. This variable will be used to build the ConnectionString.

ConnectionString | Package level | String

Set the EvaluateAsExpression property to true.
Set the Expression property as follows: Data Source="+ @[User::InstanceForDatasource] +";Provider=MSOLAP.4;Integrated Security=SSPI;
Please note that this approach can be expanded upon to have the entire ConnectionString be dynamic and dictated by the calling SQL Agent job step if necessary.

XMLAScript | Package or Foreach level | String

Connection Managers

Use a real connection in design time so that the metadata plays nice.

Creating the connection managers now isn't required, but it makes it easier for later. For each task in the process, you will have the appropriate connection manager available in the drop-down without the need to create any on-the-fly.

ADO.NET

Create a new ADO.NET connection manager that uses the Microsoft OLE DB Provider for Analysis Service
Using the Property Expressions Editor, set the ConnectionString property as follows: [User::ConnectionString]

OLEDB

Create a new OLEDB connection manager that uses the Microsoft OLE DB Provider for Analysis Service
Using the Property Expressions Editor, set the ConnectionString property as follows: [User::ConnectionString] + "Format=Tabular;"

Analysis Services

Create a new Analysis Services connection
Using the Property Expressions Editor, set the ConnectionString property as follows: [User::ConnectionString] = "Impersonation Level=Impersonate;"

Create the Foreach Container

Here, you will create a Foreach based on the Catalogs schema rowset. This will get us the DatabaseName for each database in the instance and the DatabaseName will be put into its corresponding variable.

Create an Execute Script Task

Set the script to use Visual Basic.

Set the ReadOnlyVariables and ReadWriteVariables as follows:

ReadOnlyVariables: User::ConnectionString,User::DatabaseName,User::InstanceForDatasource
ReadWriteVariables: User::BackupDir,User::DatabaseId,User::XMLAScript

Edit the Script

Add a reference to the Analysis Management Objects (AMO) assembly.

Right-click on the project name (should be the top-most item in the Project Explorer in the Visual Studio window that opened when you clicked Edit Script) and select Add Reference.
Add a reference to the Analysis Services Objects component. The dll for AMO should be located in <SQL Server InstallationDrive>:\Program Files\Microsoft SQL Server\<SQL Server Version>\SDK\Assemblies.

In the script header, add an Imports statement to use AMO: Imports Microsoft.AnalysisServices
Replace the contents of Public Sub Main() with the following script:

Public Sub Main()
    '
    Dim ASServer As New Microsoft.AnalysisServices.Server()
    Dim AsDatabase As New Microsoft.AnalysisServices.Database
    Dim ASConn As String = ""
    Dim ASDatabaseName As String = ""
    Dim ASDatabaseId As String = ""

    'Create a variable that uses the dynamic ConnectionString variable
    ASConn = Dts.Variables("ConnectionString").Value

    'What database are we working with in this iteration of the Foreach?
    ASDatabaseName = Dts.Variables("DatabaseName").Value

    'Use the Analysis Services AMO to get the DatabaseId for this DatabaseName.
    '   It is necessary to get the DatabaseId because the XMLA backup command requires the DatabaseId,
    '   and if at any point the database is renamed, the DatabaseId will differ from the DatabaseName.
    Try
        'Establish the connection to SSAS.
        ASServer.Connect(ASConn)

        'Get the database.
        AsDatabase = ASServer.Databases.FindByName(ASDatabaseName)

        '***ONLY IF the EstimatedSize > 0***.
        ' --> If it is 0, it could be a corrupted database which causes the backup process to stop.
        ' --> In that case, we will leave the DatabaseId variable blank to be a visiblie indicator that the db is corrupt.
        '***ONLY IF the number of cubes > 0***.
        ' --> The databse could be corrupt or backup could hang if there are no cubes.
        If AsDatabase.EstimatedSize > 0 And AsDatabase.Cubes.Count > 0 Then
            'Get the DatabaseId
            'Retrieve the DatabaseId from the Databases collection.
            ASDatabaseId = ASServer.Databases.FindByName(ASDatabaseName).ID
        Else
            Dts.Events.FireWarning(0, "Verifying database", "The estimated size and/or number of cubes in the database does not meet the requirements. Estimated Size= " & AsDatabase.EstimatedSize & "; " & "Number of cubes= " & AsDatabase.Cubes.Count, String.Empty, 0)
        End If
    Catch ex As Exception
        'Couldn't connect. Do not error out because maybe the next iteration will succeed.
        Dts.Events.FireWarning(0, "Establishing SSAS Connection", "Unable to connect to the SSAS Server with ConnectionString= '" & ASConn & "'. Error: " & ex.ToString, String.Empty, 0)
    End Try

    'Print info for output while testing/debugging.
    Dts.Events.FireInformation(0, "Establishing SSAS Connection", "Connection established. ConnectionString= '" & ASConn & "'.", String.Empty, 0, True)

    'Put the DatabaseId into the SSIS variable.
    Dts.Variables("DatabaseId").Value = ASDatabaseId

    'If we were able to retrieve the DatabaseId, then generate the backup directory and filename and the XMLA script.
    If ASDatabaseId <> "" Then

        'Print info for output while testing/debugging.
        Dts.Events.FireInformation(0, "Retrieving DatabaseId", "DatabaseId retrieved for " & ASDatabaseName & ". DatabaseId= '" & ASDatabaseId & "'.", String.Empty, 0, True)

        'Get timestamp for the backup file
        Dim timeStamp As String = Now.Year & _
                                    Right("0" & Now.Month, 2) & _
                                    Right("0" & Now.Day, 2) & _
                                    Right("0" & Now.Hour, 2) & _
                                    Right("0" & Now.Minute, 2) & _
                                    Right("0" & Now.Second, 2)

        'Get the filename and location for the backupfile
        Dim filePath As String = "\\<servershare>\" & Dts.Variables("ServerForDatasource").Value & "\" & ASDatabaseName
        Dim fileName As String = ASDatabaseName & "_" & timeStamp & ".abf"

        'Put the filepath into the SSIS variable
        Dts.Variables("BackupDir").Value = filePath

        'Print info for output while testing/debugging
        Dts.Events.FireInformation(0, "Retrieving full backup path", "BackupPath= '" & filePath & "\" & fileName & "'.", String.Empty, 0, True)

        'Build XMLA script
        Dim xmlaScript As String = ""
        xmlaScript = "<Backup xmlns=""http://schemas.microsoft.com/analysisservices/2003/engine"">" & _
                            "<Object><DatabaseID>" & ASDatabaseId & "</DatabaseID></Object>" & _
                            "<File>" & filePath & "\" & fileName & "</File>" & _
                        "</Backup>"

        'Put the XMLA script into the SSIS variable
        Dts.Variables("XMLAScript").Value = xmlaScript

        'Print info for output while testing/debugging.
        Dts.Events.FireInformation(0, "Generate XMLAScript", "XMLAScript= '" & xmlaScript & "'.", String.Empty, 0, True)
    Else
        'Print info for output while testing/debugging.
        'If DatabaseId is empty, we will not perform the rest of the tasks in the Foreach container.
        Dts.Events.FireWarning(0, "Retrieving DatabaseId", "Unable to retrieve the DatabaseId for Database= '" & ASDatabaseName & "'.", String.Empty, 0)
    End If

    ' Return success
    Dts.TaskResult = ScriptResults.Success
End Sub

Create a File System Task (to create the Backup Directory)

It is important to set UseDirectoryIfExists to true to avoid an error if the backup directory already exists.

Edit the precedence constraint between the Execute Script Task and the File System Task

This precedence constraint will handle a missing DatabaseId. The DatabaseId would be missing if a connection to the SSAS server could not be established in the vbscript, or if the database is corrupted. You do not want to back up a corrupted database. It will bring the backup to a halt.

Example of Expression: `@[User::DatabaseId] !=""

Create an Analysis Services Execute DDL Task

Your final Control Flow should look like this:

Create the SQL Agent Job

Add a step for each SSAS instance that will be backed up.
Each step should be configured to execute the SSIS package.
For each step, click the Set values tab and set the value of InstanceForDatasource to the instance name for the step.

Example syntax for Property Path: \Package.Variables[User::InstanceForDatasource].Properties[Value]
(More on using the Set values tab)

Notes:

To make the entire backup directory dynamic, you would just have to add another variable for that and account for it in the vbscript.
Again, if necessary, the entire connection string variable could be set by the SQL Agent job instead of just the instance name.


How to model when you could have many (thousands of) attributes to a table (product)?

What's the question? Does this make sense? Yea, sure.

1. A product could have more than one attribute Fine
2. A product could have more than one of the same attribute. A shirt is both 'brown' and 'green' Um. If you say so. Sounds a bit strange but that's up to you, I guess.
3. All Attributes and its Values have to be pre-defined (loaded from text file) Again, sounds a bit strange that numerical fields like weight and length have to have pre-defined values. I personally go for a table of pre-defined values and storing non-predefined values separately, but it makes the structure a LOT more complex. Up to you if you want (or need) to do it this way, really.
4. Once the data is loaded (once every few months), there is heavy SELECTing. So the model needs to be read-optimized. If it's properly indexed then the fact that you've got a squillion rows shouldn't impact on performance so much.

Example query to get the product name and attributes for a product:

SELECT
Product.ProductID, Product.Name,
Attribute.AttributeID, Attribute.Name,
ProductAttributes.AttributeValueID,
AttributeValue.AttributeValueID, AttributeValue.Value,

FROM
Product
JOIN ProductAttributes ON Product.ProductID = ProductAttributes.productID
JOIN AttributeValue ON ProductAttributes.AttributeValueID = AttributeValue.AttributeValueID
JOIN Attribute ON AttributeValue.AttributeID = Attribute.AttributeID

WHERE Product.ProductID = 1234

Index-wise, make sure you've got the usual primary key indexes. You'll also need an index on ProductAttributes.productID. You'll need an index on Attribute.name if you're planning on referring to this field in a query condition (eg get products which have a value for attribute 'weight')

You don't really need the AttributeID in the ProductAttributes table, as you've got the AttributeValueID, whose table then links to ProductAttributes. But having it there could conceivably act as a short cut if you were frequently getting a list of Attributes for a product without their values:

SELECT
Product.ProductID, Product.Name,
Attribute.AttributeID, Attribute.Name
FROM
Product
JOIN ProductAttributes ON Product.ProductID = ProductAttributes.productID
--THIS JOIN IS NOW NOT REQUIRED...  JOIN AttributeValue ON ProductAttributes.AttributeValueID = AttributeValue.AttributeValueID
--THIS JOIN CAN BE REPLACED BY THE ONE BELOW...  JOIN Attribute ON AttributeValue.AttributeID = Attribute.AttributeID
JOIN Attribute on ProductAttributes.AttributeID = Attribute.AttributeID
WHERE Product.ProductID = 1234

@Colin'tHart - you may be right about it not being a good design, but you'll need to explain why for your argument to have any weight.


The fastest, simplest solution is to restore your last good backup. If that isn't an option you can try a workaround to make MSSQL think the database already exists:

http://sqlskills.com/BLOGS/PAUL/post/Disaster-recovery-101-hack-attach-a-damaged-database.aspx

Then you can run DBCC and see if it can repair the database, but even in that case you may still have data loss. You could also call Microsoft (maybe you already have a support contract), but the bottom line is that if you have a damaged database and no backup then there's not much that anyone can do.


Database cannot be upgraded because it is read-only or has read-only files. Make the database or files writeable, and rerun recovery

I just found a post that answered this question:

This may not work in all cases, but I was trying to install the Semantic Search Database and got the same error.

You have to run SQL Server Management Studio as an administrator, and it works.


Should I update it to the latest SP + CU (SP3 CU4)?

When a new SQL Server service pack is released, Microsoft provides only 12 months of full support for the previous service pack.

Support is limited for older SPs:

  For customers on supported products with service pack versions that
  have left full support, Microsoft offers commercially reasonable
  support as follows:  Commercially reasonable support incidents will be
  provided through Microsoft Customer Service and Support and Microsoft
  managed support offerings (such as Premier Support). If the support
  incident requires escalation to development for further guidance,
  requires a nonsecurity update, or requires a security update,
  customers will be asked to upgrade to a fully supported service pack.

. . .

  Microsoft recommends that you apply the latest Cumulative Update (or
  one released in the last one year) for the corresponding release. The
  support team might require you to apply a specific Cumulative Update
  that addresses a specific problem when troubleshooting an issue.

https://support.microsoft.com/en-us/help/17138/service-pack-lifecycle-policy

So cross-referencing the release history here, yes you should apply SQL Server 2014 SP3 CU4.


Will deadlocks occur when deleting rows from a table that is being added to by another process?

The rows to be archived are selected by a column that has the Identity property

In this scenario, you're inserting rows at the end of the index, and deleting rows from the beginning.  So there shouldn't be overlapping locks that would lead to deadlocks.

This assumes that your archive process won't ever run concurrently.  Multiple concurrent archive operations could conceivably deadlock with each other, since they'll be operating on the same range of rows.  You should make sure that doesn't happen by, for instance, scheduling this as a SQL Server Agent job.

Just make sure not to delete too many rows at once from the "live" table after archiving them, as this could lead to lock escalation (which sounds like it would be undesirable in your situation).  Check out Michael Swart's excellent article about that topic of doing batch processing in a way that promotes performance and concurrency: Take Care When Scripting Batches

Even though your main table is a heap, you should still be okay with regards to deadlocks, as all your queries will tend to seek on the nonclustered PK and delete using the RID from there.  It seems possible that you could experience page level blocking (or deadlocks) within the heap itself, depending on a lot of factors, but it would be hard to know for sure without seeing the workload in action.

  Should I set the DEADLOCK_PRIORITY to LOW to ensure that if a deadlock does occur that the archival process will be killed and not the running application?

Yes, that's a reasonable step to take.  And as you mentioned is a good way to make sure the archive workload is re-prioritized with regards to concurrency.

  Should I also set TRANSACTION ISOLATION LEVEL?

The default isolation level (READ COMMITTED) should be okay in this situation.


all of the databases recovery models changed

Recovery model is changed by a specific ALTER DATABASE statement, and as far as I've seen, there's never been a bug that would change it.

Some common sources of that statement include:

Backup software - some 3rd party backup software will put the database in simple recovery model, especially virtualization backup tools that just do snapshot backups rather than log backups.
Data warehouse load scripts - I've seen ETL processes that would flip the database into simple recovery model to eliminate the need for transaction log backups. (Not saying that's a good idea, just saying that I've seen it.)
Log space reduction scripts - sometimes folks try to shrink their transaction logs, and run into difficulty, so they flip the database into simple and then flip it back.


PQgetvalue() is a client-side function that does not create anything, it just returns the value of a field inside a resultset.

The server-side equivalent, say select SomethingLikePQgetvalue('cats' || ' dogs')
would simply be select 'cats' || ' dogs'. Or if it isn't, please give an example showing when it would differ.

If the goal is to reinject the value as a literal into another SQL statement, then the SQL function quote_literal can be used.


What version of MySQL are you using? MySQL 8 supports LATERAL which you need to be able to refer to i from the derived table:

SELECT i.*, si.child, si.parent_id
FROM items as i
LEFT JOIN LATERAL (SELECT *
                   FROM sub_items
                   WHERE parent_id = i.id
                   LIMIT 2) as si
    ON i.id = si.parent_id;

Concider adding an ORDER BY to your sub-select.

Other ways to do something similar is to use a window function such as:

row_number() over (partition by ... order by ...) as rn

and filter rn at the outer level.

Window functions are however only supported in MySQL 8, but you can mimic them using variables:

SELECT i.*, si.child, si.parent_id, si.rn
FROM items as i
LEFT JOIN (
    SELECT c.*,
        @row_number:=CASE WHEN @parent_id = parent_id
                          THEN @row_number + 1
                          ELSE 1
                     END AS rn,
        @parent_id := parent_id
    FROM sub_items as c
    CROSS JOIN (select @row_number := 1) as x
    CROSS JOIN (select @parent_id := -1) as y
    ORDER BY parent_id
) as si
    ON i.id = si.parent_id
    AND si.rn <= 2
;

The idea is to order the sub-query by parent_id, increment row_num by 1 for each row, and as soon as parent_id changes, reset row_num to 1.

You can read more about the idea at for example: mysql-row_number


selecting duplicate values with a condition from a mysql table

select * from Table1 t
where not exists(
  select 1 from Table1 p where p.FD = t.FD
      and p.FA != t.FA and p.FA = 1
);

Or as a LEFT JOIN (might be better optimized on 5.5)

select t.* from Table1 t
left join Table1 p on p.FD = t.FD
      and p.FA != t.FA and p.FA = 1
where p.`Key` is null;

http://sqlfiddle.com/#!9/39664/4


MySQL: What points should we consider when create composite index

But, how we will decide the ordering of columns if we have many columns and many different SQL queries.

More selective columns should go first.

  What are the other points which should be focused when create composite index?

Not necessary all fields on an index may be used. For example, if you have index (a, b, c) and WHERE clause looks like

WHERE a = 5 and b < 10 and c = 6

then only a and b fields may be used. A field with < or > will be the last one that's used.

  What is the role of cardinality and modality of a column in creating index and composite indices?

Cardinality of a set is number of element in the set. The higher cardinality of a field values set the closer the fields should come in the composite index.


Difference in MYSQL Master Slave DISK SPACE

The tables on the Primary and the Replica are maintained separately.  All InnoDB tables are B+Trees.

A block might need splitting on one but not the other.
A long-running SELECT on one might lock rows in such a way that there are more block splits.
Small blocks may get recombined, but not necessarily with the same frequency, etc, on both machine.
Were all the same ALTERs done on both machines?
Which columns of SHOW TABLE STATUS show the most difference?

If it is a few percent difference, the above things might be the reason.
If it is 50%, something strange is going on; we need more details.  For starters, which one is larger.


Slow query on large table with 50M plus data (especially with larger offset)

Try this index:

create index IX_base_voter_home_street_address_1_asc
on base_voter(home_street_address_1 asc)
include(id, name_voter, home_address_city, deleted_at)
where deleted_at is null


Is it possible to get the name of a procedure by it's plan handle or query plan hash if the execution plan is already cleared from the plan cache?

If (and that's a big IF) you're running SQL Server 2019 (CTP 2.4) then you can get the last known ACTUAL execution plan for a previously cached query plan:

SELECT qps.query_plan
FROM   sys.dm_exec_procedure_stats AS ps
       CROSS APPLY sys.dm_exec_query_plan_stats(deps.plan_handle) AS qps
WHERE  ps.object_id = OBJECT_ID('dbo.spMyProcedure');


In SQL Server, you can use TOP 1 and order by newid() to get a random row returned. As in:

SELECT TOP 1 id FROM ids WHERE datetime IS NULL ORDER by newid()


SQL Server Database Experimentation Assistant Analysis Reports

I contacted Microsoft support in regards to my question.  They informed me that even though you only select 1 .xel file all .xel files in the directory are processed and you can verify it in the log.

DEA Information: 0 : DiagnosticEvent, 05/08/2020 16:32:47, Data: {"Message":"Processing file 1 of 7
DEA Information: 0 : DiagnosticEvent, 05/08/2020 16:33:20, Data: {"Message":"Processing file 2 of 7
.
.
.
DEA Information: 0 : DiagnosticEvent, 05/08/2020 16:36:03, Data: {"Message":"Processing file 7 of 7

They are planning on making this more transparent in a future release.


Create trigger base on time and insert to another table

It could go something like this. Grab the old data into a temp table. Insert it into the history table then delete from the live table. I'll leave the error handling as an exercise for the reader.

Begin Transaction archive

Select id, value, [datetime]
  Into #historical_to_archive
  From Live_Table
  Where [datetime] < DateAdd(Day, -7, GetDate());

Insert Into History_Table (id, value, [datetime])
Select id, value, [datetime]
  From #historical_to_archive;

Delete
  From Live_Table
  Where id In (Select id From #historical_to_archive);

Drop Table #historical_to_archive;

Commit Transaction archive


LOCK error after creating a clustered index

Follow the How to troubleshoot SQL Server lock related problems and errors steps.

make sure locks option is 0, see Keep the Locks Configuration Option Default Value
make sure lock escalation disabling trace flags are not in place, see Lock Escalation
check your memory consumption, run DBCC MEMORYSTATUS, see How to use the DBCC MEMORYSTATUS command to monitor memory usage
make sure you do not have open transactions with many locks, see sys.dm_tran_locks and sys.dm_tran_database_transactions

There are millions of tables with clustered indexes out there that do not run into lock exhaustion issues. There is something more at play here.


Why does SQL Server estimate less rows will be emitted from a join after inserting some rows?

It's important to remember that you aren't guaranteed consistency as you change queries or the data within the tables. The query optimizer may switch to using a different method of cardinality estimate (such as using density as opposed to histograms) which can make two queries appear to be inconsistent with each other. With that said, it certainly seems like the query optimizer is making an unreasonable choice in your case, so let's dig in.

Your demo is too complicated so I'm going to work off of a simpler example which I believe shows the same behavior. Starting data prep and table definitions:

DROP TABLE dbo.T1 IF EXISTS;
CREATE TABLE dbo.T1 (FromDate DATE, ToDate DATE, SomeId INT);

INSERT INTO dbo.T1 WITH (TABLOCK)
SELECT TOP 1000 NULL, NULL, 1
FROM master..spt_values v1;

DROP TABLE dbo.T2 IF EXISTS;
CREATE TABLE dbo.T2 (SomeDateTime DATETIME, INDEX IX(SomeDateTime));

INSERT INTO dbo.T2 WITH (TABLOCK)
SELECT TOP 2 NULL
FROM master..spt_values v1
CROSS JOIN master..spt_values v2;

Here's the SELECT query to investigate:

SELECT *
FROM T1
INNER JOIN T2 ON t2.SomeDateTime BETWEEN T1.FromDate AND T1.ToDate
WHERE T1.SomeId = 1;

This query is simple enough so that we can work out the formula for the cardinality estimate without any trace flags. However, I'm going to try to use TF 2363 as I go to better illustrate what's going on within the optimizer. It's not clear if I'll be successful.

Define the following variables:

C1 = number of rows in table T1

C2 = number of rows in table T2

S1 = the selectivity of the T1.SomeId filter

My claim is that the cardinality estimate for the above query is as follows:

When C2 >= S1 * C1:

  C2 * S1
  with a lower bound of S1 *
  C1

When C2 < S1 * C1:

  164.317 * C2 *
  S1 with an upper bound of
  S1 * C1

Let's go through some examples, although I'm not going to go through every single one that I tested. For the initial data prep we have:

C1 = 1000

C2 = 2

S1 = 1.0

Therefore, the cardinality estimate should be:

  2 * 164.317 = 328.634

The impossible-to-fake screenshot below proves this:

Using the undocumented trace flag 2363 we can get a few clues about what's going on:

Plan for computation:

  CSelCalcColumnInInterval

      Column: QCOL: [SE_DB2].[dbo].[T1].SomeId

Loaded histogram for column QCOL: [SE_DB2].[dbo].[T1].SomeId from stats with id 2

Selectivity: 1

Stats collection generated:

  CStCollFilter(ID=3, CARD=1000)

      CStCollBaseTable(ID=1, CARD=1000 TBL: T1)

End selectivity computation

Begin selectivity computation

Input tree:

...

Plan for computation:

  CSelCalcSimpleJoinWithUpperBound (Using base cardinality)

      CSelCalcOneSided (RIGHT)

          CSelCalcCombineFilters_ExponentialBackoff (AND)

              CSelCalcFixedFilter (0.3)

              CSelCalcFixedFilter (0.3)

Selectivity: 0.164317

Stats collection generated:

  CStCollJoin(ID=4, CARD=328.634 x_jtInner)

      CStCollFilter(ID=3, CARD=1000)

          CStCollBaseTable(ID=1, CARD=1000 TBL: T1)

      CStCollBaseTable(ID=2, CARD=2 TBL: T2)

End selectivity computation

With the new CE we get the usual 16% estimate for a BETWEEN. This is due to exponential backoff with the new 2014 CE. Each inequality has a cardinality estimate of 0.3 so BETWEEN is calculated as 0.3 * sqrt(0.3) = 0.164317. Multiply the 16% selectivity by the number of rows in T2 and T1 and we get our estimate. Seems reasonable enough. Let's bump up the number of rows in T2 to 7. Now we have the following:

C1 = 1000

C2 = 7

S1 = 1.0

Therefore, the cardinality estimate should be 1000 because:

  7 * 164.317  = 1150 > 1000

Query plan confirms it:

We can take another peek with TF 2363 but it looks like the selectivity was adjusted behind the scenes to respect the upper bound. I suspect that CSelCalcSimpleJoinWithUpperBound prevents the cardinality estimate from going above 1000.

Loaded histogram for column QCOL: [SE_DB2].[dbo].[T1].SomeId from stats with id 2

Selectivity: 1

Stats collection generated:

  CStCollFilter(ID=3, CARD=1000)

      CStCollBaseTable(ID=1, CARD=1000 TBL: T1)

End selectivity computation

Begin selectivity computation

Input tree:

...

Plan for computation:

  CSelCalcSimpleJoinWithUpperBound (Using base cardinality)

      CSelCalcOneSided (RIGHT)

          CSelCalcCombineFilters_ExponentialBackoff (AND)

              CSelCalcFixedFilter (0.3)

              CSelCalcFixedFilter (0.3)

Selectivity: 0.142857

Stats collection generated:

  CStCollJoin(ID=4, CARD=1000 x_jtInner)

      CStCollFilter(ID=3, CARD=1000)

          CStCollBaseTable(ID=1, CARD=1000 TBL: T1)

      CStCollBaseTable(ID=2, CARD=7 TBL: T2)

Let's bump T2 to 50000 rows. Now we have:

C1 = 1000

C2 = 50000

S1 = 1.0

Therefore, the cardinality estimate should be:

  50000 * 1.0 = 50000

The query plan again confirms it. It's much easier to guess at the estimate after you've already figured out the formula:

TF output:

Loaded histogram for column QCOL: [SE_DB2].[dbo].[T1].SomeId from stats with id 2

Selectivity: 1

Stats collection generated:

  CStCollFilter(ID=3, CARD=1000)

      CStCollBaseTable(ID=1, CARD=1000 TBL: T1)

...

Plan for computation:

  CSelCalcSimpleJoinWithUpperBound (Using base cardinality)

      CSelCalcOneSided (RIGHT)

          CSelCalcCombineFilters_ExponentialBackoff (AND)

              CSelCalcFixedFilter (0.3)

              CSelCalcFixedFilter (0.3)

Selectivity: 0.001

Stats collection generated:

  CStCollJoin(ID=4, CARD=50000 x_jtInner)

      CStCollFilter(ID=3, CARD=1000)

          CStCollBaseTable(ID=1, CARD=1000 TBL: T1)

      CStCollBaseTable(ID=2, CARD=50000 TBL: T2)

For this example the exponential backoff appears to be irrelevant:

  5000 * 1000 * 0.001 = 50000.

Now let's add 3k rows to T1 with a SomeId value of 0. Code to do so:

INSERT INTO T1 WITH (TABLOCK)
SELECT TOP 3000 NULL, NULL, 0
FROM   master..spt_values v1,
       master..spt_values v2;

UPDATE STATISTICS T1 WITH FULLSCAN;

Now we have:

C1 = 4000

C2 = 50000

S1 = 0.25

Therefore, the cardinality estimate should be:

  50000 * 0.25 = 12500

Query plan confirms it:

This is the same behavior that you called out in the question. I added irrelevant rows to a table and the cardinality estimate decreased. Why did that happen? Pay attention to the bold lines:

Loaded histogram for column QCOL: [SE_DB2].[dbo].[T1].SomeId from stats with id 2

Selectivity: 0.25

Stats collection generated:

  CStCollFilter(ID=3, CARD=1000)

      CStCollBaseTable(ID=1, CARD=4000 TBL: T1)

End selectivity computation

Begin selectivity computation

Input tree:

...

Plan for computation:

  CSelCalcSimpleJoinWithUpperBound (Using base cardinality)

      CSelCalcOneSided (RIGHT)

          CSelCalcCombineFilters_ExponentialBackoff (AND)

              CSelCalcFixedFilter (0.3)

              CSelCalcFixedFilter (0.3)

Selectivity: 0.00025

Stats collection generated:

  CStCollJoin(ID=4, CARD=12500 x_jtInner)

      CStCollFilter(ID=3, CARD=1000)

          CStCollBaseTable(ID=1, CARD=4000 TBL: T1)

      CStCollBaseTable(ID=2, CARD=50000 TBL: T2)

End selectivity computation

It seems as if the cardinality estimate for this case was calculated as:

C1 * S1 * C2 * S1 / ( S1 * C1)

Or for this particular example:

  4000 * 0.25 * 50000 * 0.25 / (0.25 * 4000) = 12500

The general formula can of course can be simplified to:

C2 * S1

Which is the formula that I claimed above. It seems like there's some cancellation going on that shouldn't be. I would expect the total number of rows in T1 to be relevant to the estimate.

If we insert more rows into T1 we can see the lower bound in action:

INSERT INTO T1 WITH (TABLOCK)
SELECT TOP 997000 NULL, NULL, 0
FROM   master..spt_values v1,
       master..spt_values v2;

UPDATE STATISTICS T1 WITH FULLSCAN;

The cardinality estimate in this case is 1000 rows. I will omit the query plan and the TF 2363 output.

In closing, this behavior is pretty suspicious but I don't know enough to declare if it's a bug or not. My example doesn't match your repro exactly but I believe that I observed the same general behavior. Also I would say that you get a bit lucky with how you chose your initial data. There seems to be a fair amount of guessing going on by the optimizer so I wouldn't get too hung up on the fact that the original query returned 1 million rows which matched the estimate exactly.


SQL Record Transfer from one db to another instance having same table structure

You can use SET IDENTITY_INSERT my_table ON before performing the insertion of the data in you local table and on finish use SET IDENTITY_INSERT my_table OFF and your column will take the following value.

Example:

SET IDENTITY_INSERT [dbo].[table] ON

GO

INSERT INTO [dbo].[table] ([CustomerID] ,[FirstName]) VALUES (1 ,'qqq');

INSERT INTO [dbo].[table] ([CustomerID] ,[FirstName]) VALUES (2 ,'eee');

SET IDENTITY_INSERT [dbo].[table] OFF


Alternatives to Linked Servers (Allow Inline SQL with Parameters)

No, you shouldn't use OPENQUERY IMHO. How about this construct, which allows you to use sp_executesql and parameters:

DECLARE @someParam INT = 5;

EXEC LinkedServerName.master.sys.sp_executesql
  @stmt = N'SELECT @i, @@SERVERNAME, @@VERSION;',
  @params = N'@i INT', @i = @someParam;


PostgreSQL execute a default query when no row are returned

You could use COALESCE() .. it returns the first non-null value in the list passed to it..

COALESCE((QUERY TO GET SCORE FOR USERID),(QUERY TO GET DEFAULT SCORE))

EDIT

oh - you added "list" requirement ... see @ypercube's answer, then.

EDIT2

If the place_ids exist in a separate table, and they only DON'T EXIST in the table you're querying to get scores (which, imo, would be considered a 'good design decision'), then you should include that table in your query .. COALESCE() as used in @ypercube's answer should work fine in that case.

SELECT
  u.user_id,
  u.place_id,
  COALECSE(u.score, d.score) AS score
FROM
  place_list AS pl
LEFT JOIN
  user_place_score AS u ON pl.place_id = u.place_id
LEFT JOIN
  default_place_score AS d
    ON d.place_id = u.place_id ;

Above taken and edited from @ypercube's answer.


Oracle TKPROF Insert data doesn't match text results

Those values are just fine. They are just truncated in the tkprof output and displayed as seconds instead of microseconds.

Time for SQL executions is typically measured in microseconds.

See V$SQL for example:

  ELAPSED_TIME

  NUMBER

  Elapsed time (in microseconds) used by this cursor for parsing,
  executing, and fetching. If the cursor uses parallel execution, then
  ELAPSED_TIME is the cumulative time for the query coordinator, plus
  all parallel query slave processes.

Tkprof also displays time in microseconds at the row source operations section (and displays the unit as us).

I did not find any explicit reference for describing all the columns of TKPROF_TABLE, stating the time unit to be microsecond, but its quite logical.


You'll want to add another table Season, and another table SeasonPlayer with an FK to player, an FK to team and an FK to Season.

Then drop the FK from Team to Player in your current schema


Fail-over or switching the Reporting server database

You can use AlwaysOn Availability Groups to replicate the SSRS Databases to other servers, and reference the Availability Group Listener in place of the server names to allow for failover between servers.

Be aware that Availability Groups do not replicate server level objects such as logins or jobs. If you have any subscriptions in SSRS these will be scheduled jobs that will need to replicated outside the availability group, and you will need to put a mechanism in place to stop them firing when their server is not the primary node.

Here are a number of blog posts that go into further detail about setting up SSRS onto an Availability Group:
https://marcosfreccia.com/2018/06/25/reporting-services-databases-availability-groups/
https://jbswiki.com/2017/09/06/reporting-services-using-alwayson-availability-group/
http://techtalklive.org/ttlblog/making-sql-server-reporting-services-highly-available/


Column does not exist in UNION ALL query

With the help of many people whom gave me a lot of help, I finally got a working solution:

SELECT PlayerName_fkey, SumScoresSimpleTotal, SumScoresDoubleTotal, SumScoresTotal, rank() over (order by SumScoresTotal desc) AS OverallRank

FROM (

    SELECT COALESCE(t1.PlayerName_fkey, t2.PlayerName_fkey) as PlayerName_fkey, coalesce(SumScoresSimple, 0) as SumScoresSimpleTotal, coalesce(SumScoresDouble,0) as SumScoresDoubleTotal,
    (coalesce(SumScoresSimple, 0) + coalesce(SumScoresDouble, 0)) as SumScoresTotal
    FROM (

    SELECT PlayerName_fkey, ScoreSimple, sum(ScoreSimple) over (partition by PlayerName_fkey) as SumScoresSimple

    FROM (

        SELECT PlayerName_fkey, score, ScoreRank, SUM(score) as ScoreSimple
        FROM (
            -- Select the best four results in the season for each player in single play
            SELECT PlayerName_fkey, score, tournamentNumber, rank() over (PARTITION BY PlayerName_fkey order by Score desc) AS ScoreRank
            FROM (
                -- Select, for each player, the score for each tournament they played in single play
                SELECT PlayerName_fkey, score, TournamentNumber
                     FROM Result LEFT JOIN Player ON Player.name=Result.PlayerName_fkey
                     WHERE Category='Juvénile' AND Gender='Féminin' AND TypeOfPlay='Simple' AND score > 0 ) tBestIndividualScoresWithDuplicates
                GROUP BY PlayerName_fkey, score, tournamentNumber) tBestIndividualScores
        WHERE ScoreRank <= 4
        GROUP BY PlayerName_fkey, score, ScoreRank)tinside1) t1

        FULL OUTER JOIN

    (SELECT PlayerName_fkey, ScoreDouble, sum(ScoreDouble) over (partition by PlayerName_fkey) as SumScoresDouble

    FROM (

        SELECT PlayerName_fkey, score, ScoreRank, SUM(score) as ScoreDouble
        FROM (
            -- Select the best four results in the season for each player in single play
            SELECT PlayerName_fkey, score, tournamentNumber, rank() over (PARTITION BY PlayerName_fkey order by Score desc) AS ScoreRank
            FROM (
                -- Select, for each player, the score for each tournament they played in single play
                SELECT PlayerName_fkey, score, TournamentNumber
                     FROM Result LEFT JOIN Player ON Player.name=Result.PlayerName_fkey
                     WHERE Category='Juvénile' AND Gender='Féminin' AND TypeOfPlay='Double' AND score > 0 ) tBestIndividualScoresWithDuplicates
                GROUP BY PlayerName_fkey, score, tournamentNumber) tBestIndividualScores
        WHERE ScoreRank <= 4
        GROUP BY PlayerName_fkey, score, ScoreRank) tinside2) t2

    ON t1.PlayerName_fkey = t2.PlayerName_fkey

    GROUP BY t1.PlayerName_fkey, t2.PlayerName_fkey, SumScoresSimple, SumScoresDouble

    ORDER BY SumScoresTotal DESC

The colasesce functions are particularly important here to avoid empty entries for both the player names and the scores.


Choosing the right algorithm in HashBytes function

The HASHBYTES function only takes up to 8000 bytes as input. Because your inputs are potentially larger than that, duplicates in the range of the field that gets hashed will cause collisions, regardless of the algorithm chosen. Carefully consider the range of data you plan to hash -- using the first 4000 characters is the obvious choice, but may not be the best choice for your data.

In any event, because of what a hash function is, even if the inputs are 8000 bytes or less, the only way to ensure 100% correctness in the results is to compare the base values at some point (read: not necessarily first). Period.

The business will dictate whether or not 100% accuracy is required. This will tell you that either (a) comparing the base values is required, or (b) you should consider not comparing the base values -- how much accuracy should be traded off for performance.

While hash collisions are possible in a unique input set, they are infinitesimally rare, regardless of the algorithm chosen. The whole idea of using a hash value in this scenario is to efficiently narrow down the join results to a more manageable set, not to necessarily arrive at the final set of results immediately. Again, for 100% accuracy, this cannot be the final step in the process. This scenario isn't using hashing for the purpose of cryptography, so an algorithm such as MD5 will work fine.

It would be extremely hard for me to justify moving up to a SHA-x algorithm for "accuracy" purposes because if the business is going to freak out about the miniscule collision possibilities of MD5, chances are they're also going to freak out that the SHA-x algorithms aren't perfect either. They either have to come to terms with the slight inaccuracy, or mandate that the query be 100% accurate and live with the associated technical implications. I suppose if the CEO sleeps better at night knowing you used SHA-x instead of MD5, well, fine; it still doesn't mean much from a technical point of view in this case.

Speaking of performance, if the tables are read-mostly and the join result is needed frequently, consider implementing an indexed view to eliminate the need to compute the entire join every time it's requested. Of course you trade off storage for that, but it may be well worth it for the performance improvement, particularly if 100% accuracy is required.

For further reading on indexing long string values, I published an article that walks through an example of how to do this for a single table, and presents things to consider when attempting the full scenario in this question.


Always On HAG Primary Database Log Files

Can you quantify 'large'?  Are things breaking?  What's the reason you want to shrink it?

Regardless, you won't be able to switch the DB to SIMPLE mode if it's in an AG.  You'll get the following:

  Msg 1468, Level 16, State 2, Line 1 The operation cannot be performed
  on database "MyDBName" because it is involved in a database mirroring
  session or an availability group. Some operations are not allowed on a
  database that is participating in a database mirroring session or in
  an availability group.

  Msg 5069, Level 16, State 1, Line 1 ALTER
  DATABASE statement failed.

What I would do is go find out if transaction log backups are being taken and their frequency.  in FULL recovery model, the log file isn't automatically truncated and cleared, you'll need to take log backups for that to happen. (In SIMPLE recovery, the log is automatically cleared so there's no need to maintain it.

If you're taking log backups, you may need to take them more frequently as the log file could be filling up between log backups.

https://www.sqlshack.com/beginners-guide-sql-server-transaction-logs/
https://technet.microsoft.com/en-us/library/ms345583(v=sql.105).aspx


FindNextFile is a Windows API call, so what's happening is the backup process is enumerating all the files in the FILESTREAM folder and backing them up. The error message indicates that the operating system cannot read the file system; this indicates, at least to me, that the file/folder structure on disk is corrupt. There would be a different error returned if the file/folder was simply not found. This may mean the I/O subsystem has problems. I would run chkdsk in Windows on that volume.

From the DBCC CHECKDB documentation, it doesn't look like FILESTREAM checks go beyond ensuring there is linkage between the database and the file system. Running chkdsk has the ability to do a low-level check of the disk contents itself, which is probably what's needed in this scenario. It certainly wouldn't hurt to run CHECKDB on the database anyway (read: regularly) either.


In slow_log tables, sql_text column showing "throttle: index not used' warning(s) suppressed" instead of exact query

Unfortunately, you are not going to get those queries from mysql.slow_log. Why ?

There are two options evidently being used

log_queries_not_using_indexes
log_throttle_queries_not_using_indexes

According to MySQL Documentation (The Slow Query Log, Paragraph 9)

  To include queries that do not use indexes for row lookups in the statements written to the slow query log, enable the log_queries_not_using_indexes system variable. When such queries are logged, the slow query log may grow quickly. It is possible to put a rate limit on these queries by setting the log_throttle_queries_not_using_indexes system variable. By default, this variable is 0, which means there is no limit. Positive values impose a per-minute limit on logging of queries that do not use indexes. The first such query opens a 60-second window within which the server logs queries up to the given limit, then suppresses additional queries. If there are suppressed queries when the window ends, the server logs a summary that indicates how many there were and the aggregate time spent in them. The next 60-second window begins when the server logs the next query that does not use indexes.

What this shows is that your log_throttle_queries_not_using_indexes is set so that SQL text is suppressed. You can verify this with

mysql> SELECT @@global.log_throttle_queries_not_using_indexes;

Since it is a global dynamic variable, you must login as root@localhost and run

mysql> SET GLOBAL log_throttle_queries_not_using_indexes = 0;

This should expose new queries entering mysql.slow_log, but you will never see what those previous slow queries were.

You must run this

mysql> SELECT @@global.log_output;

If you see FILE,TABLE that means there is a text version of the slow query log. You will have to get your SysAdmin to get you the desired slow log info (Starbucks GiftCard might be required for this one. If the slow log file contains the same messages, take your Starbucks GitftCard back.)


SQL Server AlwaysOn to run updates and schema changes in 24/7 environment

1.When and how do we install Microsoft Updates?

Using AOAGs could potentially help with this, depending on how much downtime is considered to be "too much". There is still a blip associated with switching the primary to a synchronous secondary as the clients will be disconnected (this may or may not be an issue in your environment) as the move happens. They will need to reconnect using the listener name (so no connection string changes should be required) and be using a library that supports this.

  1.For Windows Updates, bring one server down, install windows updates, bring it back up, and then update the second instance.

That's the basics of how it works. Remove automatic failover (synchronous), install the updates/patches, reboot (in this scenario).

  2.How do we apply schema changes?

AOAGs aren't going to help you in this respect. The transaction log is basically replayed from the primary to all of the secondary's. The secondary's could be readable (if you allow them to be) but won't be able to have any write activity (including ddl changes) made to them. A synchronous secondary is a mirror copy of the primary.

Could these changes be made when the application changes are made to minimize downtime? It may make more sense to use a load balanced front end using something such as an F5 and keeping the data in sync through something such as peer to peer replication than use AOAGs. I'm not a replication expert, but it definitely sounds like you will need two distinct systems that have some type of load balanced front end so that changes can be made to a single system at a time to move resources in and out while keeping them in sync.

  2.For schema changes, bring one server down, apply schema changes, bring it back up, and then apply same changes to the second instance.

This can't be done this way (or any way, really) with AOAGs.


Server default collation change or not change

I worked out this issue and share it here, in case someone needs it in future.

The default collation setting is determined by the server locale. For most English speaking countries is it : Latin1_General_CI_AS

If the server locale setting has been left as default which is English (United States), the collation at time of MS SQL Management Studio would be set to SQL_Latin1_General_CP1_CI_AS.

You can check the collation for each local in the following url:

http://msdn.microsoft.com/en-us/library/ms143508.aspx


What Kondybas explained about the appropriate operator, but with legal syntax:

SELECT etype, a1.provider AS provider1, a2.provider AS provider2
FROM   A a1
JOIN   A a2 USING (etype)
WHERE  a1.provider < a2.provider;

[INNER] JOIN requires a join condition, with either ON or USING.

To keep events with only a single provider in the result, use a LEFT [OUTER] JOIN:

SELECT a1.etype, a1.provider AS provider1, a2.provider AS provider2
FROM   A a1
LEFT   JOIN A a2 ON a1.etype = a2.etype
                AND a1.provider < a2.provider;

The second condition has to move to the join condition as well in this case. See:

Sort by number of related rows in referencing table


The solution was to create a clustered index on the table which immediately shrank the data size down to 13MB.  I found this out thanks to the following post on StackOverflow: https://stackoverflow.com/questions/3336934/reduce-sql-server-table-fragmentation-without-adding-dropping-a-clustered-index

  The point is, you have substantial fragmentation on the Heap. You keep calling it a "table", but there is no such thing at the physical data storage or structure level; a table is a logical concept, rendered physically as:

  either the Heap plus all Nonclustered Indices plus Text/Image chains
  or the Clustered Index plus all Nonclustered Indices plus Text/Image chains.

  Heaps get badly fragmented; the more interspersed (random) Insert/Deletes/Updates there are, the more fragmentation.

  There is no way to clean up the Heap, as is. MS does not provide a facility (other vendors do).


MariaDB / MySQL client table-formatting like `psql`?

Sadly, no, but there are a few options which may help

--skip-column-names, -NDo not write column names in results.
--skip-line-numbers, -L Do not write line numbers for errors. Useful when you want to compare result files that include error messages.
--silent, -s Silent mode. Produce less output. This option can be given multiple times to produce less and less output. This option results in nontabular output format and escaping of special characters. Escaping may be disabled by using raw mode; see the description for the --raw option.

May be worth nothing that while silent is almost nice,

SELECT 1 AS "myCol1", 2 AS "myCol2";
myCol1  myCol2
1       2

You're actually losing the tabulated display and just getting a \t.

SELECT 10000000000000 AS "myCol1", 2 AS "myCol2";
myCol1  myCol2
10000000000000  2


Restore of database failed .bak file SQL Server 2008

You need to check the actual size of the database files which you can check by running

RESTORE FILELIST ONLY FROM <backup_device>

This will give you information about the files contained in the backup and their sizes in bytes

You need the same space available on the server as the database file size, the backups can be compressed.

Now you need to free up more space on c: on the server but I would recommend that you add drives to your server and that you would look at storage best practies


To do a refresh of a "on premise" postgres database to a azure postgres database or to do a backup and restore?

In general most DB as a service providers make it difficult to do replication to/from them to your own box so replication, at least native postgres replication, likely won't work.

That leaves you with going through replication solutions that are not so tighly integrated such as Slony, Bucardo, pgpool.

My advice would be to first describe your needs and then use that information to decide which approach would be easiest to maintain, which in my opinion is far more important than easy to install.

Do you need to have the data up to date at all times? Then you want some means of replication.
Can the data be behind hours or a day? You can use a process to get the data from the primary to the Azure machine without replication.
Does the data gets updates or is it insert only? Insert only tables are far easier to replicate.
Do you have, or can setup, insert_date and last_update dates/timestamp fields? If you have a way to know what is new and/or updated since your last migration you can do your own process with the advantage that it will work regardless of what provider you use. One, potentially big, disadvantage is that you have to manage this yourself for every single table. If your environment is mostly mature and you are not adding new tables often, this is one potential method.
Is the data for some, or all, of the tables coming from some external source? In a case like that you can double load, were each source gets loaded on both places. Biggest disadvantage is that you have to replicate your processes for the load and monitoring as well as coming up with a way to make sure there are no discrepancies.

Usually the deciding factor for this type of designs are how far behind the data can be and everything, for the most part, usually is based on that.

The other important factor is size. If you are talking a handful of MBs then the process is trivial, no matter which way you go. If you are talking Terabytes then incremental or replication is an absolute must.


SQL Server options that influence log buffer flushing or commits-per-second performance?

both running identical hardware (VMs)

If the performance of this minimal test is that drastically different, and you have thoroughly confirmed that the software (and instance / database level settings - for example the checkpoint-related settings like indirect checkpoint, recovery interval, target recovery time, etc might affect a benchmark like this one) is the same, then the statement about the hardware being the same is probably not exactly right.
You mentioned WRITELOG is your highest wait.  With this workload, WRITELOG will almost inevitably be the highest wait, since that's all you're doing.  Is the difference in WRITELOG waits between the two servers large enough to account for the 10x difference in transactions per second?
If that wait is significantly higher on the slow server, then it's possible that, despite the same hardware specs being specified at the VM level, the "path" to the I/O system is slower on the second VM.  Or the underlying storage is slower.

Check out the sys.dm_io_virtual_file_stats DMV to see if the slow server is experiencing slower writes or more frequent / longer stalls than the fast server.
Check the SQL Server error log to see if there are any "SQL Server has encountered X occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [M:\SomeDataFile.mdf] in database [SomeDatabase] (5)." error messages.

If you're seeing slowness reported in those places, you'll need to work with the VM administrator -  to see if there is a difference in the underlying storage, or the access to the storage (if it's going out to a SAN).


Does simple recovery model not log bulk inserts?

INSERT can be minimally logged in some cases.  See: Prerequisites for Minimal Logging in Bulk Import.
Note if you switch to SIMPLE recovery your log chain is broken, and you'll need to start with a new full backup after switching back to the FULL recovery model.
If you switch to BULK_LOGGED then inserted pages are copied into the next log backup. Switching to BULK_LOGGED does not break the chain — that's why it exists.
See Bulk Import Optimizations (Minimal Logging) by Sunil Agarwal for more details. Some quotes:

Under FULL recovery model, as the name implies, all operations are fully logged. Under minimal logging, the SQL Server does not log individual rows and logs only the page and extent allocations. So if each page can fit 100 rows, we are trading 100 log records for 1 log record, a significant improvement.

When the bulk import transaction commits ... the SQL Server flushes all the data pages (a subset of these pages are likely to have been flushed as part of checkpoint) to disk to guarantee the atomicity of the transaction.

When the [next] transaction log backup is taken, the SQL Server also backs up the data pages, including index pages when applicable, as part of the transaction log. This enables a database restore by restoring the database backup followed by zero or more transaction log backups. The only drawback of minimal logging is that it does not support point-in-time recovery ... If this is of concern, you can do transaction log backup before/after bulk operations.

So you typically switch back to FULL recovery model, and take a log backup immediately after performing the minimally logged operation.


Reference a non primary key in another table in postgresql

Postgres supports foreign keys to unique indexes (a primary key is a special case of a unique key). From the documentation

  The referenced columns must be the columns of a non-deferrable unique or primary key constraint in the referenced table


Is it normal to constantly have 30+ connections to ‘master’ database in MS SQL Server 2012?

30 system processes is absolutely not unheard of, and actually sounds better than normal.

However, you should not be using sysprocesses, which is deprecated and only exists for backward compatibility reasons - see the important note right at the top of the doc. (If you're using it solely for the database column, see this recent question, and the comments.)

You should be looking at sys.dm_exec_sessions - this view has a column called is_user_process that allows you to identify your processes and distinguish them from system processes (the old < 50 trick is no longer reliable on modern versions of SQL Server).

Just as an example, on my local dev machine - where I can assure you nothing is going on - I have the following rows:

sys.sysprocesses                                    62
sys.dm_exec_sessions                                68
sys.dm_exec_sessions where is_user_process = 1      26

The majority of those 26 user processes are just connections to the instance (which is on a VM guest) held open by Visual Studio Code on the host.

The other 42 are normal, sleeping, background system processes like LOG WRITER, LOCK MONITOR, XE DISPATCHER, 'CHECKPOINT', etc. It's not like you can get rid of these.

You could also compare the output of the undocumented sys.sp_who2:

EXEC sys.sp_who2;                       -- 64 rows in my case
EXEC sys.sp_who2 @loginame = N'active'; -- 40 rows in my case

Or limiting to actual running processes in sys.dm_exec_requests.

But for deeper insight into what processes are doing on your system, you're better off using something like Adam Machanic's sp_whoisactive or a proper monitoring tool (disclaimer: I work for one of those).


Concurrent Transactions vs Simultaneous Transactions

Your assignment question is poor and the expected answer probably incorrect. Feel free to send the lecturer our way to discuss!
From the book:

Explain the difference between concurrent transactions and
simultaneous transactions. How many CPUs are required for simultaneous
transactions?

I guess the expected differentiation is:

Concurrent transactions = multiple transactions active at the same time.
Simultaneous transactions = multiple transactions executing at the same time.

Naff question in my opinion.

Is my understanding correct that in a database, simultaneous
transactions don't really happen

Transactions do of course run concurrently and simultaneously. The question is whether or not simultaneous commits can occur, which is what @gbn is addressing in his answer.
@gbn is correct that two simultaneous commits cannot occur if you consider the transaction to be committed at the point the WAL entry is hardened to disk. From a crash recovery perspective, the transaction is definitely committed at this point. In the event of a failure occurring, this transaction would be rolled forward (redo), not rolled back (undo). As two writes to disk cannot occur at the same time, we could argue that two simultaneous commits cannot occur.
However, you could also argue that a transaction is committed at the point the RDBMS is notified by the IO subsystem that the WAL write has completed successfully AND has released any locks it was holding for the duration of the transaction. Therefore, in a multi-cpu environment simultaneous commits can and will occur.
The mechanics of how this can occur for SQL Server is explained in Performance Tuning Waits and Queues, section titled 'Execution Model (simplified)'. Again for the SQL Server folk, some background reading on the WRITELOG wait type is useful to understand the concepts.


Time out errors - MS access front end with SQL backend

The Timeout Expired error you're seeing is very likely due to how Microsoft Access fetches rows from SQL Server.  If, for instance, the form in question uses data-binding to populate controls on the form with data from SQL Server, and there is no filter on the form that selects a SINGLE row of data, Access will keep the results open, and "page" through the rows one single row at a time.  This will prevent others from updating any of the rows in the table.

For a system used by 1,000 users, you should never ever use Access as a front-end, as tempting as that may be.  Use Access  (it you must) as a rapid design prototyping tool, then build out the real solution using Visual Studio or some other enterprise-level development software.


How to quote arrays in postgres copy statement to inser into JSON field

JSON uses double quote " to delimite keys and strings:
[{"id": "NfKA3", "u": "https://somewebsite.com"}]

CSV uses double quote to escape double quotes "" means "
1,"some text here", 22, "[{""id"": ""NfKA3"", ""u"": ""https://somewebsite.com""}]"

Look at: https://stackoverflow.com/questions/17808511/properly-escape-a-double-quote-in-csv


Is there a way to check the content of SYS_LOB0000119621C00011$$?

Yes you can first look at dba_lobs view to check which table/column is associated with this segment.

  What could be the reason for this growth if this is not coming from
  the users?

Maybe one huge file has been inserted in your table. You can use DBMS_LOB.GETLENGTH(column_name) to find the size of each lob.

select filename, DBMS_LOB.GETLENGTH(filecontent) from fileentry order by 2;


First, if the idea is that users subscribe to products, so that they can hear about new reviews on that product, then you definitely want the (many-to-many) relation to be between user and product, not user and reviewproduct. You're not subscribing to a review, you're subscribing to a product.

Regarding how do you track the new reviews: My first thought would be to keep a timestamp on the user-product subscription entity. When you want to know if there are new reviews for a product, you just query for all reviews created after the timestamp, and then you update the timestamp (or you update it whenever the user views them, or whatever your requirements are exactly).

(Side note: Not sure why you're calling reviews “reviewproduct”. I'd just call it “review”, or if you need to differentiate it from some other kind of review, maybe “productreview”.)


There are three answers and some great advice is included in each answer in part. That said I wanted to add a bit more from another perspective.

Talking about database files here...

Right Sizing Is Best

As most have said or hinted at - it is far better to "right-size" your database for current and future needs. TomTom is right to point out that there is a performance hit there, but KookieMonster is also right to point out that Instant File Initialization (IFI because I'm lazy) helps that.. Even with IFI - I prefer to find out about the sizing needs of my databases at their deployment and in their possible futures as best as I can. And then I try to size for that plus an "overage" based on what I know about the project/about the potential to grow.... Note: This is not an exact science, and there will be times (many times) that you get this wrong, that's okay.. Autogrowth is a good thing.. I just prefer to try and stay on top of it.

Why? Because I don't want to have that awkward conversation with a SAN admin when I start running out of space. And I don't want a SAN admin to have to make that choice to do some ugly things behind the scenes to give me space. I like to preallocate, watch my free space used in the database over time and use

Auto-Growth As an Emergency Helper

Autogrowth for data files is not evil, especially with IFI. But I like to monitor space used inside of a DB file and use the ability to automatically grow as a band-aid. This way I stay on top of the growth of the databases I'm responsible for as a DBA. Your monitoring regiment should include checking for used space and looking at that.

What Setting Is Best?

So with this in mind, it almost shouldn't matter what you use because you are helping manage the growth yourself. If you see your space used curve steeper than you like, you can look at your calculations and preallocate more space in one big growth. Even still, I am not a big fan of percentages. To me it is non-deterministic and a sign that someone isn't managing the growth in a lot of situations. I just prefer to have that level of control, and I pick a space that I feel is appropriate based on the needs of the database.

Big Caveat

"It Depends" - if your database is small and likely not going to be a big boon on disk ever then I wouldn't cry about keeping it at a percentage or even paying a bit less attention to it's setting. If I go to a client and see a 750GB database still at default growth percentages and no log file management, I cry a little inside. If I go to a client with a 1.25GB database that's been around for 3 years and still set to the defaults? I mention it in my report, but I have the whole "There are best practices.. And then there are situations where you are fine either way" conversation with them.. Now if IFI was disabled, and they had a valid reason to keep it disabled? I'd still probably say something with more seriousness to the smaller database, and I'd really say something like "Let's go crazy and grow this 4 times to preallocate some space" and risk "wasting" 2 to 3 GB of their disk space.

Transaction Log Files

Are a different matter. Log files do not/cannot take advantage of IFI. And they don't like autogrowth because of a little thing called VLF fragmentation (that link takes you to a lot more links all about the topic). I prefer to right size my transaction logs, watch them, and reevaluate what that right size is. I keep autogrowth on (a production transaction log file filling up also makes me cry inside.. actually outside too). And I right size them in "chunks" as per the guidance on the link provided for VLF.


mongo2.6: db.serverStatus().connections nothing happened?

The userAdminAnyDatabase role does not grant access to run the serverStatus command. Your db.serverStatus() command is returning an authorization error and you are then referencing a non-existent connections property which results in the "no results to show" message.

You can confirm this by running db.serverStatus(), which should return something similar to the following if the current user is not authorized:

{
     "ok" : 0,
     "errmsg" : "not authorized on admin to execute command { serverStatus: 1.0 }",
     "code" : 13
}

To allow the serverStatus command you need to grant either a built-in role that includes this privilege (clusterMonitor or hostManager in MongoDB 2.6) or create a user-defined role if you want a more narrow scope.

For example, to create and grant a custom role to run serverStatus:

use admin
db.createRole({
    role: "serverStatus",
    privileges: [
        { resource: { cluster: true }, actions: [ "serverStatus" ] },
    ],
    roles: []
})

db.grantRolesToUser("superAdmin",["serverStatus"])


I can't confirm whether SQL Server needs SSLv3 for secure connections ... but I can tell you for sure that the MS14-066 patch introduced all sorts of problems in https traffic with reports all over the web for browsers that stopped working, SQL Server connections that all of a sudden started using high CPU and so on ... all in all a pretty buggy patch and we're all waiting on Microsoft to come clean and tell us how to safely undo it while still protecting our systems from the mystery bug that the patch supposedly fixed.

For more details see this:

http://odetodata.com/2014/11/microsoft-patch-ms14-066-leads-to-https-problems-with-iis-and-google-chrome/


Does number of rows affect Update Statistics performance if you are sampling with n rows?

Update statistics is a single threaded task (You can run multiple instances of it in parallell though) so duration of the statistics run will be correlated with the number of records the task has to scan in order to build the statistics and selecting the rows to sample.

Even if you are only sampling a fixed number of rows the population you are sampling from is larger therefore the scan will be more time consuming.

There are some interesting details about effects and use of tempdb in statistics scanning in Erin Stellato's post about statistics but almost all you want to know is contained in this Robert Sheldon's article


Generic log-table for CRUD operations that support both single and composite Primary Keys

Your issue sounds like it colud make use of a dimensional model, with the objects to be tracked as Dimension(s) and the events to be measured as Facts, especially if this table needs to be human readable and exposed to end users. Check out this resource for further reading BACK TO DIMENSIONAL MODELING BASICS and The 10 Essential Rules of Dimensional Modeling


Database replication without locking tables

this is the meaning of the parameters you pointed:

server-id: its mean the server has unique ID in-order to communicate with other servers, the ID range should be 1-2^32, and if not specified its mean the server ID will be 0 (stand alone) and will not communicate with other servers.
binlog-format: its a binary log format, it has three values, STATEMENT causes logging to be statement based, ROW causes logging to be row based, and MIXED causes logging to use mixed format.
log-bin: this will enable binary logging (https://dev.mysql.com/doc/refman/5.7/en/replication-options.html)
datadir: where you will store your data.
innodb_flush_log_at_trx_commit: Controls the balance between strict ACID compliance for commit operations and higher performance that is possible when commit-related I/O operations are rearranged and done in batches. You can achieve better performance by changing the default value but then you can lose up to a second of transactions in a crash. (Is it safe to use innodb_flush_log_at_trx_commit = 2)
sync_binlog: this parameter is use to sync binlog from one server to another.
relay-log: The relay log, like the binary log, consists of a set of numbered files containing events that describe database changes, and an index file that contains the names of all used relay log files.(https://dev.mysql.com/doc/refman/5.7/en/slave-logs-relaylog.html)
log-slave-updates: this option needs to be enabled in order for the auto-positioning mechanism to be active.
read-only: this option will make slave is used for read-only no update (other than the ones come from master server) is allowed, to make sure there will be no conflict


Insert Multiple Column based on existing table aggregated

INSERT [fd_movements_dates](Date, Cnt)
SELECT (CONVERT(date, MsgTimestamp)) as Date
    ,COUNT(1) AS Cnt
FROM [fd_movements]
GROUP BY (CONVERT(date, MsgTimestamp))


Cursor Based Pagination for Timestamp field with non-unique values

ORDER BY deadline ASC, id ASC will be "deterministic" (assuming id is the PRIMARY KEY for the table).  And include INDEX(deadline, id).

More discussion of pagination and the evils of OFFSET:  http://mysql.rjweb.org/doc.php/pagination

If there is any chance of adding or deleting rows while the user is paging through the list, items can be skipped or duplicated in the paginated list.  "Remembering where you left off" (see link) solves the problem.  However, it is a little tricky to say > when two columns are involved.


Rock-Paper-Scissors-Spock-Lizard statistics DB design, is there a better way to achieve this?

To increase efficiency, you could run the query and get all 3 results at once:

SELECT COUNT(*) AS cnt, result
FROM match
GROUP BY result

That should give you 3 rows in this situation, which you can deal with in the application layer however you want.

As by the example above, you would get something like the following:

Cnt | Result
10 | 0
15 | 1
25 | 2

I would personally say do the following with structure:

The Matches table holds one record per match, with references to each other table.

The Player table holds one record for each player (Whether they are a computer or user), with a reference to the PlayerType table

The PlayerTypes table holds the ID and the type of player (User, or computer etc)

The ResultTypes table holds a record for each possible result of a match, with the TypeName column being the text for that result (IE 'Won', 'Loss' etc)

I've not added any foreign keys etc - should be fairly easy to work out from the names though. http://sqlfiddle.com/#!9/865078

The below query would then give you your result:

SELECT COUNT(Matches.ID), ResultTypes.TypeName
FROM Matches
INNER JOIN ResultTypes ON Matches.ResultID = ResultTypes.TypeID
GROUP BY ResultTypes.TypeName


An alternative dynamic SQL approach (which also includes schema, often important!):

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N',' + QUOTENAME(s.name) + N'.' + QUOTENAME(v.name)
FROM sys.schemas AS s
INNER JOIN sys.views AS v
ON s.[schema_id] = v.[schema_id]
WHERE v.[name] LIKE N'%DoNotNeed[_]%';

SET @sql = N'DROP VIEW ' + STUFF(@sql, 1, 1, N'') + N';';

PRINT @sql;
-- EXEC sys.sp_executesql @sql;

This will produce a command like the following, which is legal (you don't need a DROP VIEW command for every view):

DROP VIEW [dbo].[view1], [dbo].[view2], [dbo].[view3], ...;

When you believe it is correct (and note that PRINT might not output the entire command if it exceeds 8k), uncomment the EXEC.

Note that whatever solution you use, you might run into difficulties dropping certain views (for example, they could be referencing by objects that use WITH SCHEMABINDING). In which case you may want instead to use something a little more elaborate:

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'
BEGIN TRY
  DROP VIEW ' + name + N';
  PRINT N''   Dropped ' + name + N'!'';
END TRY
BEGIN CATCH
  PRINT N''Could not drop ' + name + N'!'';
  PRINT ERROR_MESSAGE();
END CATCH'
FROM
(
  SELECT name = QUOTENAME(s.name) + N'.' + QUOTENAME(v.name)
   FROM sys.schemas AS s
   INNER JOIN sys.views AS v
   ON s.[schema_id] = v.[schema_id]
   WHERE v.[name] LIKE N'%DoNotNeed[_]%'
) AS x;

PRINT @sql;
-- EXEC sys.sp_executesql @sql;

Parts of this solution can be complicated if you have views with ' in their names. Don't do that.


Change column values but keep order in MySQL

This sounds like an activity that a college teacher might come up with to see if someone understands math and has a little bit of imagination, as this sort of thing would almost never be required outside of an educational setting ... and I say this as someone who works in academia.
That said, one way this could be accomplished would be with a subquery that first determines the "proper" id. An UPDATE statement can then be used to match on the "proper_id" to fix the data. Here is the sub-query:
SELECT CASE WHEN id % 2 = 0 THEN id - 1 ELSE id + 1 END as proper_id,
       name
  FROM Student
 ORDER BY id

I'll leave the rest to you


GROUP BY rewrite to subqueries with easily interchangeable AND OR conditions

First clean the join syntax:

SELECT * FROM content as c
inner join tag_map as t on c.id=t.id
inner join extrainfo as e on c.id=e.id
WHERE
#AND OR conditions here
#conditions can be very different but I would like
#to use the same query structure
#just change conditions here as necessary
GROUP BY c.id

Now substitute your and/or clauses in place of the rem'd out lines above. Then look into indexing those columns (indexes should cover all the fields in the conditions).

These simple steps will speed up significantly. If you need further speed ups, please post before and after speedup results along with complete details of existing indexes.