Status shows InnoDB: 128 rollback segment(s) are active waiting for purge to start

only for refer

in windows server mysql 5.6

The reason why a crash recovery is performed even after a "normal shutdown" is because windows service manager does not wait long enough before killing the service forcefully. 60 seconds might not be enough to flush all dirty pages to disk.
 There's a MySQL feature request to improve that. https://bugs.mysql.com/bug.php?id=45547

The question then is how to make mysqld shutdown quicker so the service manager doesn't timeout after 60 seconds of waiting .
 Answer is :

o) Set in my.ini a small value for this. innodb_max_dirty_pages_pct=10 # (default value is 75)
 o) Manually run STOP SLAVE; before the shutdown.

 o) Manually shutdown the mysqld using "mysqladmin shutdown -uroot -p" before rebooting server. Instead of using windows service stop command..


The software installer is reading the %userprofile% or %homepath% environment variables to create the user database, you will have to change the installation script or temporary change the environment variables while running the installer.

This has nothing to do with Express itself as it is creating the databases where it's asked to do so by the installation script.
Now if you want to have the database in the home directory you are better off by using SQL Server Express LocalDB. I would consider this software a failure.


single select vs multiple selects using limit and offset

Okay, first thing, I need to pick at this a little bit:

  We went down the route of making these reads readUncommitted since the
  operations on the data are absolutely not mission critical.

Using READ UNCOMMITTED/NOLOCK should only be considered when the accuracy of the results is not critical, because that's what the transaction isolation level affects. I understand that the operation is not mission-critical, but that should not be the driver behind selecting a less restrictive isolation level.

  In other words - would 20 select statements each retrieving 1000
  records be more desirable than 1 select statement retrieving 20000
  records in terms of reducing locking etc.

If you continue to use READ UNCOMMITTED, the locking overhead will be the same between the two methods (i.e., no locks will be taken). However, running multiple statements instead of one can affect other things, such as query compilation (which uses CPU), network chattiness, and the complexity of your application. In this case, I think the operation of sending a mailer would be relatively infrequent (I could be wrong), so the latter factor may be the most important to consider.

If you choose to switch back to a higher isolation level for accuracy reasons, that is an entirely different ball game in terms of locking.


How to get the current line number from an executing stored procedure

This question actually came up 2.5 years ago on StackOverflow, and I posted an Answer there:

SQL print line number in comment of dynamically created stored procedure?

The relevant portion of that Answer is copied below:

You can use TRY / CATCH with a forced error as the CATCH block can return the line number that the error occurred on via the ERROR_LINE() function. The full construct, formatted for readability, is:

DECLARE @Line INT = -1; -- default to an invalid line #

-- some code

BEGIN TRY
    ;THROW 50000, 'Line#', 1; -- all 3 values are arbitrary, but required
END TRY
BEGIN CATCH
    SET @LineNumber = ERROR_LINE();
END CATCH

-- some code

Now, to get the @LineNumber variable to populate with the line number that it is being set on, you can reduce that construct to a single line as follows:

BEGIN TRY;THROW 50000,'',1;END TRY BEGIN CATCH;SET @Line=ERROR_LINE();END CATCH

Please note that the THROW command started in SQL Server 2012. If you are using SQL Server 2005, 2008, or 2008 R2, then you need to use RAISERROR() function instead of THROW.


Methods to identify/recognise 'bad' parameter sniffing in SQL Server

Identifying parameter sniffing is hard work! It may not be apparent using monitoring tools or queries, because it typically requires some analysis of other executions of the query plan.

This is where the DMVs, and a plan cache analysis tool like sp_BlitzCache can help. You can download it here. Full disclosure: I work for Brent Ozar, and contribute to the project.

It ONLY uses DMVs that would be available by your guidelines to perform analysis of the plan cache, etc. It's also free. If you can't install the stored proc, the relevant bit of code to attempt to identify parameter sniffing is here:

 parameter_sniffing = CASE WHEN AverageReads > @parameter_sniffing_io_threshold
                                      AND min_worker_time < ((1.0 - (@parameter_sniffing_warning_pct / 100.0)) * AverageCPU) THEN 1
                                 WHEN AverageReads > @parameter_sniffing_io_threshold
                                      AND max_worker_time > ((1.0 + (@parameter_sniffing_warning_pct / 100.0)) * AverageCPU) THEN 1
                                 WHEN AverageReads > @parameter_sniffing_io_threshold
                                      AND MinReturnedRows < ((1.0 - (@parameter_sniffing_warning_pct / 100.0)) * AverageReturnedRows) THEN 1
                                 WHEN AverageReads > @parameter_sniffing_io_threshold
                                      AND MaxReturnedRows > ((1.0 + (@parameter_sniffing_warning_pct / 100.0)) * AverageReturnedRows) THEN 1 END

The variables are configurable if you use the stored procedure. If you want to roll your own, how you assign the values is up to you. What we look at is if the average of certain plan attributes are wildly exceeded or, uh... underceeded by the min/max values of those attributes.

These are usually good indicators of parameter sniffing. Thinking about a 'small' value plan that performs a key lookup 5 times (and works well for most values over a number of executions). If a 'large' value comes along next and uses the cached plan, forcing the key lookup to execute hundreds of thousands of times, it would use more cpu/do more reads/run longer than average.

Hope this helps!


Never assume or "belief" the cause for something. Test it.

In your case run an EXPLAIN PLAN on the statements in question and check if the plan with views is more expensive than the corresponding SELECT using the base tables.

You can also use SQL*Plus' autotrace feature to check out logical IO done by both queries.

If that still doesn't give enough information you can turn on tracing and examine the output of the TKPROF formatting utility of the trace file.


SQL Server: SET NOCOUNT ON still returning data

You are looking at the wrong part of SQL Servers's output. NOCOUNT only controls the extra "row(s) affected" messages that are output after SELECT/INSERT/UPDATE/DELETE/MERGE operations, not the rowsets output of any SELECTs that don't have a destination specified. You can see this if you set SSMS to show output as text:

SET NOCOUNT OFF
SELECT test='NoCount is OFF'
SET NOCOUNT ON
SELECT test='NoCount is ON'
SET NOCOUNT OFF
SELECT test='NoCount is OFF again'
-- now lets try with operations that store the rowset instead of sending as output
SET NOCOUNT OFF
PRINT 'A rows affected count should follow'
SELECT test='NoCount is OFF' INTO #temptable
SET NOCOUNT ON
PRINT 'But there won''t be one for this statement'
SELECT test='NoCount is ON' INTO #temptable2
SET NOCOUNT OFF
PRINT 'A rows affected count should follow'
SELECT test='NoCount is OFF again' INTO #temptable3

will produce the output:

test
--------------
NoCount is OFF

(1 row(s) affected)

test
-------------
NoCount is ON

test
--------------------
NoCount is OFF again

(1 row(s) affected)

A rows affected count should follow

(1 row(s) affected)
But there won't be one for this statement
A rows affected count should follow

(1 row(s) affected)

or if you leave SSMS set to show output in grids, you'll get the grids in the results tab and the following in the messages tab:

(1 row(s) affected)

(1 row(s) affected)
A rows affected count should follow

(1 row(s) affected)
But there won't be one for this statement
A rows affected count should follow

(1 row(s) affected)

There is no way (that I know of) to stop all rowsets being output by a stored procedure that wants to output them.

You can use the form INSERT <table> EXEC <procedure> <params> to put the output into a temporary table (then just drop the table or let it be dropped as your session ends) to hide the first set of results, but this has a number of significant limitations:

The table must already exist, you can't do the equivalent of SELECT <stuff> INTO <newtable> FROM ..., which adds a chunk of extra code (to create the table)
As with any other INSERT either the table must have the right number of columns or you need to specify the destination columns, and in either case they must, of course, be of compatible types
INSERT <table> EXEC ... can not be nested, so this will break if the procedure any dependencies it may have also use INSERT <table> EXEC ...
It can only capture one result set, so won't work as you desire if the called procedure returns more than one

Another thing to note about NOCOUNT is that the called stored procedure may override the setting you specify by using SET NOCOUNT {ON|OFF} itself so it might output counts ignoring your setting and you'll need to check/reset it after each call to make sure it is how you want it (see Artashes's answer for how to use @@OPTIONS to check).


Why does creating database snapshot fail unless user is db_owner or sysadmin? MSDN suggests it should work

In order to be able to create a database snapshot, you will need CREATE DATABASE permissions as well as db_owner on the database you want to snapshot. I have tested this and it works perfectly fine.

The other solution is to grant dbcreator server role to the login that needs this privilege. This works too.

It seems that with 'Any user who can create a database can create a database snapshot' this BOL means login having dbcreator server role.


You're committing one of the cardinal (excuse the pun) sins of database schema design here - you're using the Entity-Attribute-Value model. Check out the writings of Joe Celko or Bill Karwin on this issue. It's also called the OTLT (One True Lookup Table) or MUCK (Massively Unified Code Key - there's a reason that particular acronym was coined!). You would IMHO be far better off if you redesigned your table structure.

What you should be doing is something like this - and your performance should be more than acceptable.

CREATE TABLE MyStuff
(
  selector int,
  username VARCHAR(15),
  password VARCHAR(15),
  firstname VARCHAR(15),
  lastname VARCHAR(15),
  PRIMARY KEY(selector)
);

mysql>
mysql> INSERT INTO MyStuff VALUES(1234597951, 'person', 'PPassword', 'John', 'Smith');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> SELECT *  FROM MyStuff WHERE selector = 1234597951;
+------------+----------+-----------+-----------+----------+
| selector   | username | password  | firstname | lastname |
+------------+----------+-----------+-----------+----------+
| 1234597951 | person   | PPassword | John      | Smith    |
+------------+----------+-----------+-----------+----------+
1 row in set (0.00 sec)

mysql>


I am not sure why the procedure uses image parameters instead of varbinary(max), or why you aren't passing the parameter values to sp_executesql directly. How about:

ALTER PROCEDURE [dbo].[transact_image_update]
  @transact_recordID_int int,
  @image1_bin varbinary(max),
  @image2_bin varbinary(max),
  @transact_referenceNo_str nvarchar(25),
  @userID_last uniqueidentifier,
  @tableName nvarchar(50)
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @sql nvarchar(max);

  SET @sql = N'UPDATE dbo.'
    + QUOTENAME(@tableName) + N'
    SET [image1_bin] = @i1,
            [image2_bin] = @i2,
            [userID_last] = @u
    WHERE transact_recordID_int = @t
    AND [transact_referenceNo_str] = @s
    AND locked_bol = 0;';

  EXEC sys.sp_executesql @sql,
    N'@i1 varbinary(max), @i2 varbinary(max), @u uniqueidentifier,
    @t int, @s nvarchar(25)',
    @image1_bin, @image2_bin, @userID_last,
     @transact_recordID_int, @transact_referenceNo_str;
END


Why does dm_os_memory_clerks have MEMORYCLERK_SQLCLR values on one NUMA node?

Unfortunately, there is not a whole lot of information out there on the specifics of SQLCLR memory usage. I did, however, find the following two resources helpful as a starting point:

Memory Usage in SQL CLR
SQL Server: Clarifying The NUMA Configuration Information

From those and looking through various DMVs and the MSDN pages explaining (well, it's not much explaining, but technically it's more than nothing) how those DMVs kinda sorta work, I put together the queries shown below. From running those on a few different systems, it seems that SQLCLR memory allocation is concentrated on one particular memory_node_id, but there is still some allocation on other nodes, at least in terms of Proc Cache. So for now, unless someone has evidence or information to the contrary, what you are experiencing is "expected".

This query reports the base info:

SELECT domc.*
FROM   sys.dm_os_memory_clerks domc
WHERE  domc.[type] LIKE '%CLR%'
AND    domc.[memory_node_id] <> 64;

You will notice that by not specifying one particular [type], there are a few more that can take up some memory. Total 4 types that I have seen are:

MEMORYCLERK_SQLCLR,
MEMORYCLERK_SQLCLRASSEMBLY
CACHESTORE_CLRPROC
CACHESTORE_CLRUDTINFO

If you are wondering why I am filtering out [memory_node_id] of 64, run this:

SELECT * FROM sys.dm_os_nodes;

and you will see that node_id of 64 is DAC (Dedicated Administrator Connection).

The following will give you the totals for the two important fields. Please note that the fields changed in SQL Server 2012 so you need to pick the one that is appropriate for the version of SQL Server it is being used on:

-- SQL Server 2012, 2014, and 2016:
SELECT SUM(domc.pages_kb) AS [TotalPagesKb],
       SUM(domc.virtual_memory_committed_kb) AS [TotalVirtualMemoryKb]
FROM   sys.dm_os_memory_clerks domc
WHERE  domc.[type] LIKE '%CLR%'
AND    domc.[memory_node_id] <> 64;

-- SQL Server 2005, 2008, and 2008 R2:
SELECT SUM(domc.single_pages_kb + domc.multi_pages_kb) AS [TotalPagesKb],
       SUM(domc.virtual_memory_committed_kb) AS [TotalVirtualMemoryKb]
FROM   sys.dm_os_memory_clerks domc
WHERE  domc.[type] LIKE '%CLR%'
AND    domc.[memory_node_id] <> 64;

The next query shows the relationship between "memory_clerks" and "memory_objects":

SELECT domc.*, N' █ ' AS [ █ ], domo.*
FROM   sys.dm_os_memory_clerks domc
LEFT JOIN sys.dm_os_memory_objects domo
        ON domo.page_allocator_address = domc.page_allocator_address
WHERE  domc.[type] LIKE N'%CLR%'
AND    domc.[memory_node_id] <> 64;

The following queries are a more focused version of the first query (and more like the query in the Question), but the fields changed in SQL Server 2012 so you need to pick the one that is appropriate for the version of SQL Server it is being used on:

-- SQL Server 2012, 2014, and 2016:
SELECT domc.memory_node_id,
       domc.[type],
       domc.pages_kb,
       domc.virtual_memory_committed_kb
FROM   sys.dm_os_memory_clerks domc
WHERE  domc.[type] LIKE '%CLR%'
AND    domc.[memory_node_id] <> 64;

-- SQL Server 2005, 2008, and 2008 R2:
SELECT domc.memory_node_id,
       domc.[type],
       (domc.single_pages_kb + domc.multi_pages_kb) AS [pages_kb],
       domc.virtual_memory_committed_kb
FROM   sys.dm_os_memory_clerks domc
WHERE  domc.[type] LIKE '%CLR%'
AND    domc.[memory_node_id] <> 64;

UPDATE:

I did some additional testing on a dual-processor system by creating 2 databases and, using a static variable, loaded 80 MB into each of the databases. I created two databases instead of one to see if an AppDomain had to remain on one memory node, if it would be able to at least place another AppDomain on another memory node in order to spread things out. The results:

All 160 MB across both DBs (and hence across both AppDomains) went onto memory node 0
All 160 MB went into virtual memory, not physical memory. It was only the [virtual_memory_committed_kb] field in sys.dm_os_memory_clerks that increased. And the "Commit Size" column in "Task Manager" also reflected this increase.


SQL Server database level audit filtering does not work

In my case, MYDOMAIN\myuser was a server principal, not a database principal. Only the latter ones can be used in database audit definitions.

Database principals can be either database users, or roles (see documentation). I created a new database role to be used in the audit definition. Then I created the logins for the users in question and chose the apt role membership under User mapping.

Hint: For some reasons, it seems necessary to define the role prior to adding the logins for the users. Adding existing logins as role members did not work for me.


Allow only unique combination of values for a composite primary key

Since you use MariaDB 10.2, you can add a CHECK constraint, enforcing that the first id is less than the second (id_1 < id_2). CHECK constraints were added in version 10.2.1.

This has the limitation that you'll have to provide the ids for the INSERT statements in correct order (you won't be able to insert the (3,2) combination, even if (2,3) is not there):

ALTER TABLE compare
  ADD CONSTRAINT ids_unique_combination_chk
    CHECK (id_1 < id_2) ;

Tested at dbfiddle.uk.

If you don't want to have to hassle with your INSERT statements and procedures, you can use two VIRTUAL (or PERSISTENT) columns and a UNIQUE constraint.

Virtual columns are available in MariaDB since version 5.2 and several limitations were lifted in 10.2.1:

ALTER TABLE compare
  ADD small_id BIGINT AS (LEAST(id_1, id_2)) VIRTUAL,
  ADD big_id   BIGINT AS (GREATEST(id_1, id_2)) VIRTUAL,
  ADD CONSTRAINT ids_unique_combination_uq
    UNIQUE (small_id, big_id);

Tested at dbfiddle.uk, too: fiddle-2.


Query for total downtime of site using datestamps and status columns

Would this work.  I just had the query give the totals for each year.  You could easily filter by date if you wanted to restrict it for just this year, or a specific year you are looking at.

Also, this assumes the final record in the table is an up time.  If you end with the site being down, then that last downtime isn't included.  You should get your site back up before worrying about calculating downtime :).

WITH TAB
AS
(
    SELECT * ,
           Row_Number() OVER (Partition BY Site ORDER BY DateTime) AS RowNum
    FROM @Table
)
SELECT UT.Site , DATEPART(Year, UT.DateTime) as Year,
       Sum(DateDiff(second, DT.DateTime, UT.DateTime)) as TotalDownTimeSeconds
FROM       Tab AS DT
INNER JOIN Tab AS UT
    ON DT.SiteStatus = 'Down'
    AND UT.RowNum = DT.RowNum + 1
    AND UT.Site = DT.Site
WHERE DT.Site = 'ZooToys'
GROUP BY UT.Site, DATEPART(Year, UT.DateTime);

If you want to get the down time for the past 60 days, add to the where clause:

UT.DateTime > DateAdd(day, -60, getdate())


Looking up the documentation, two things to mention:

not sure whether a space is allowed before @ (not declared explicitly, but the user string takes the user_name@host_name form - and since this is a string, one can expect that the space is not allowed)
and, better, if you want to grant privileges to a user on all hosts, you can simply omit the @hostname part completely.


A low rent approach would be to open a command window (Start -> Run => cmd) and navigate to the folder with all the SSIS packages. cd \my\ssis\folder\

The find command looks for a specific string (case sensitive by default).

C:\my\ssis\folder > find "myCaseSensitiveTableName" *.dtsx

or

C:\my\ssis\folder > find /i "myCaseInsensitiveTableName" *.dtsx

That should be a good starting point for looking through your packages and hopefully the table name(s) you're looking for do not collide with the internal xml of an SSIS package. If that's the case, and you can assume OLE DB Destinations, a find for OpenRowset should be beneficial.


Sql server sqllogship.exe...full docs and/or source code?

I am not sure of any other tool which you are referring to, but i prefer to use this sql logshipping app and yes its documented, refer to below article!

https://msdn.microsoft.com/en-us/library/bb283327.aspx

Not surprisingly, the sqllogship utility is specific to log shipping, which must be enabled and working in order to use the tool. The utility lets you perform copy, restore, and backup operations for an existing log shipping configuration.


MySQL should include a number of starting point configuration files for my.cnf that can adjust the memory usage. The files are named my-small.cnf, my-medium.cnf, my-large.cnf, and my-huge.cnf.

There is an online MySQL memory usage calculator at http://www.mysqlcalculator.com/. By comparing the starting point files and using the memory usage calculator you should be able to adjust your my.cnf configuration file to accomodate your server's memory constraints.

For more information about what each parameter does, you can reference the MySQL documentation available online. Here is a link to the MySQL documentation for MySQL server 5.1: http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html


The ~/.my.cnf file is usually used for client options (for example changing the default port or host).

mysqld will likely start as a different user and will not read your .my.cnf file.  It is recommended to set these options in /etc/my.cnf instead.


How to prevent Deadlock on SELECT queries?

I can't say much about your deadlocks without the queries or a deadlock graph.
Is there any reason why you specify an exclusive table lock?
SQL Server is very good at escalating locks from row or page to table itself.

  Is there's an option in MSSQL to do a write lock in a manner to not block the selects?

Yes there is, you can enable READ COMMITTED SNAPSHOT ISOLATION LEVEL.
With this isolation level you can always read what's committed. If another query is writing at that moment you will see the latest snapshot of what was written.
Please read through the MSDN page and see if RCSIL will work for your app.


Clarification for database creation and relations

Just for a start:

Usually, this should become clear from normalizing the data. You need a separate table for each entity. And you require an intermediate table for many to many (m:n) relations.

With hotel and room/reservation, it's unlikely that a specific room/reservation relates to more than a single hotel. For meals, you need to decide, what exactly defines one. If all you care is, e.g., whether or not cheese sandwiches are served in some particular hotel, it'd look like m:n. You will need to ask you(r domain expert), what will be true for the lifetime of the system, you are working on.

Foreign keys will be needed, no matter, whether you have intermediate tables or not - the difference is, where they are stored.

Looking at your current schema: Two things come to mind. Either you are storing optional attributes in the intermediate tables - and you take the shortcut, if no optional attribute is filled in. Or you do actually have multiple relations between these entities. You would need to check your data to differentiate between the two. I'd usually vote for consistency - thus using one approach for storing a particular type of fact.

Update regarding "hotel_meal": With all this detail in the meal record, I'd doubt one dish to be offered in more than one hotel (unless you have hotel chains in your system, or several hotels' kitchen is run by the same caterer).
You could check:

SELECT
  meal_id
  , COUNT(hotels_id)
FROM meal_hotel
GROUP BY meal_id
HAVING COUNT(COUNT(hotels_id)) > 1;

If it returns any record, you have a reason for an intermediate table - if you decide the records returned do actually make sense in your domain (hotel chains, same caterer - you should know it).

Update II regarding "hotel_meal": If no meal is ever served in more than a single hotel, you should not need the additional table; just have the hotel_id as foreign key in the meal table. Just as with rooms and reservations…
To list all meals for a particular hotel:

SELECT * FROM meals WHERE hotel_id=1;

Feel free to come back with more detail.


Copying a restoring database in SQL Server 2012

If you want to (1) maintain your log shipping environment and (2) want to have a read/write copy occassionally you will need to maintain 2 databases, TargetDB and Target2DB.

Question, could you just restore the backup from the Server A SourceDB to Server B Target2DB?  That might take a little longer, but there are fewer moving parts running all the time.

If read/only access would be sufficient, you can create a database snapshot for reading purposes. This creates a point in time snapshot of the database, supported by the .SNP file that maintains the needed data to support the snapshot, despite other changes coming in.

CREATE DATABASE TargetDB_Read
  ON (NAME = 'datafile', FILENAME = 'F:\TargetDB_Read.SNP')
  AS SNAPSHOT OF TargetDB

Note: You can create several snapshots if needed at different points in time. In that case, name the snapshot for the time it is created.  E.g. TargetDB_0800.)  See:
http://technet.microsoft.com/en-us/library/ms175876.aspx

Of course if you bring Target2DB online so as to do Read/Write, once you are done you will need to restore a Full backup of the SourceDB as the new Target2DB, then catch up with changes through restoring Differential and Log backups until you are once again ready for log shipping.


MongoDB Ops Manager - CPU Time - No data available

You must have munin-node installed, configured and reachable (on port 4949) by the MMS agents in order for CPU and other system data to be collected.  This is all covered in the documentation.


How many records should be deleted when batch delete is used?

The only correct answer here is "It Depends". There are so many factors that come into play that you will just have do many runs with different parameters try to find what are the optimal settings for your system, and you can also play with locking hints if your table has indexes that are optimal for the search condition.

There are some rules of thumb.. if you have a clustered index on an identity column, which is not fragmented and you are deleting records from a range thats old you can safely ask the engine to lock the extends that the old records are using and delete ~64Kb at a time without fearing of blocking readers. This can help you but is not exact science and offrow data will make this different:

SELECT OBJECT_NAME(i.OBJECT_ID) AS tableName
, i.name AS indexName
, SUM(p.row_count) AS rows
, SUM(p.in_row_data_page_count) AS dataPages
, SUM(p.row_count) / SUM(p.in_row_data_page_count) AS rowsPerPage
FROM sys.indexes AS i
JOIN sys.dm_db_partition_stats AS p
ON i.OBJECT_ID = p.OBJECT_ID
And i.index_id = p.index_id
WHERE OBJECT_NAME(i.OBJECT_ID) Not Like 'sys%' and i.type = 1
GROUP BY OBJECT_NAME(i.OBJECT_ID)
, i.name
, i.type_desc
HAVING SUM(p.in_row_data_page_count) > 100
ORDER BY rowsPerPage;

This will give you average number of rows in the clustered index. You are safe to lock on a single page which will be fast and give you good concurrency. That number times 8 will most likely give you an EXTENT lock with the same benefits and after that you'll have to play with the numbers to see when you will start to lock the table. Also it helps to have READ_COMMITED_SNAPSHOT active.


No it is not possible to automatically shard the collection. This is what you have to do per collection.

Ref:

https://docs.mongodb.com/manual/reference/command/shardCollection/
https://docs.mongodb.com/manual/sharding/
https://docs.mongodb.com/manual/reference/command/shardCollection/#dbcmd.shardCollection

You will need to run shardCollection command in order to shard a new collection.  The collection must have an index whose prefix is the shard key.
The following operation enables sharding for the people collection in the records database and uses the zipcode field as the shard key:

use admin
db.runCommand( { shardCollection: "records.people", key: { zipcode: 1 } } )

  Shards a collection to distribute its documents across shards. You
  must run enableSharding on a database before running the
  shardCollection command. The shardCollection command must be run
  against the admin database.

A database can have a mixture of sharded and unsharded collections. Sharded collections are partitioned and distributed across the shards in the cluster. Unsharded collections are stored on a primary shard. Each database has its own primary shard.


Safe to use the same temporary table name in parallel transactons?

documentation means:

if you have permanent table TABLE_A
and you create a temporary table with the same name TABLE_A,

a permanent table will be not visible for your code.

But you could request data from the permanent table by add schema to name - schema_name.table_name.

Answering to your question - yes, it is safe.


Generic Trigger on MYSQL to replicate data on other table

One idea would be to write:

CREATE TRIGGER `after_insert_log_competency`
AFTER INSERT ON `competencies`
FOR EACH ROW
   INSERT INTO history_competencies SELECT *, 'CREATE' FROM competencies WHERE id=NEW.id;

That doesn't work well for deletions, as the row wouldn't exist anymore. Also, you are doing an extra eq_ref for each write.

But be careful, because if you ALTER competencies without altering history_competencies, all your INSERTS, etc. on competencies will fail due to the trigger failing.

If you do very frequently ALTERs, and all you do is inserting and selecting those new fields, you could serialize them to a single BLOB. That is a very denormal form, with a lot of drawbacks (no constraints, no indexes, difficult to update a single field), but it may be interesting in some cases.


Parameter Sniffing - Running query in code vs in management studio

But in my case, I ran the stored proc with exactly the same parameter values from Management Studio and C#. Management Studio was fast, C# was slow. I would expect both to use the cached plan, so how could it be fast in one place and slow in another?

Welcome to parameter sniffing   It's not actually quite that simple.

It's possible that the SQL Server instance already had a compiled plan for the query when you ran your test, that was compiled for different parameters.  If this is a test system, and you won't be disrupting others, you could run DBCC FREEPROCCACHE on the instance to make sure you don't have any cached plans before running your test.

There could even be more than one plan depending on whether whitespace, capitalization, SET options, etc were different between the C# batch and the one you ran in SSMS (See Aaron Bertrand's post  Multiple Plans for an "Identical" Query for details).  You can use the queries in Aaron's post to see if you have different plans for the C# code version and the SSMS version.  Then you could compare them (in particular the SET options and the queryhash, both of which can be found by right-clicking on the SELECT operator in the plan and going to "Properties") to see what's different.

For an extremely detailed treatment on this issue, see Slow in the Application, Fast in SSMS? by Erland Sommarskog.  There's also a great resource on this site for identifying and troubleshooting the issue by Erik Darling here on the site: Why is my query suddenly slower than it was yesterday?

I hope that helps!  Let me know if you're still lost after reading those resources, it can all be a little confusing.


You can only tnsping to a database that is defined in your tnsnames.ora or OID. If I understand well then FIHOST is a server. To see if that is up you must use a common ping.


High memory usage in Masters with MariaDB and Vitess

max_connections which is set to 15758.

Far too high.  Try 200.
Do those interesting mem spikes correspond to big queries?  Or garbage collection in Vitess?  "Every week" sounds like a backup by the host?
Do increase the buffer_pool to 2G, but not until you get the memory under control.
That the heck are those 12 things in the first image?


Easiest way to find the storage engine being used currently.

Inside mongo console type

db.serverStatus().storageEngine

It returns the storage engine being used currently

{ "name" : "wiredTiger" }

Once it is confirmed that wiredTiger is being used then type

db.serverStatus().wiredTiger

to get all the configuration details of wiredTiger.


MySQL HA_ERR_LOCK_WAIT_TIMEOUT ignoring innodb_lock_wait_timeout (deleting most of a table)

Do not use OFFSET when looping through to do DELETEs; it is likely to get slower and slower, and maybe hit even your increased timeout.

Try not to put all the deletes in a single transaction.  Instead make it so that if one delete dies, rerunning the deletes will still work 'correctly'.

Don't delete more than 1000 rows at a time.

More discussion of "big deletes":  MySQL Big DELETEs

Deleting 95% of a table

Now that you have explained more of what is going on, I recommend rebuilding the table by creating a new table and copying over the 5%.  (This technique applies for any percentage, but its benefits are dubious if copying more than 50%.)

Note that innodb_file_per_table needs to have been on before the bloated table was build, else you won't recoup the disk space.

If that would tie up the table too long, then walk through the PRIMARY KEY in chunks of 1K rows, deleting about 95% of each chunk.  (As discussed elsewhere in that link.)  You won't be able to return the disk space to the OS (without a locking OPTIMIZE TABLE), but the "freed" space will eventually be used by new INSERTs.


Splitting a result row into multiple rows

As Mark mentioned, UNPIVOT would be a good choice here.  I often find it's easier to throw the original query into a temp table then unpivot that.  You could use that approach, CTEs or views depending on performance.  All the datatypes need to line up for it to work, so I'm casting everything to MONEY in my example, something like this:

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp

SELECT
       vs.MonthName AS Month,
       vs.Region,
       vs.Category,
       CAST (MIN (a.TotalAmount) AS MONEY) AS ActualAmount,
       CAST (MIN (a.TotalCases)  AS MONEY) AS ActualCases,
       CAST (MIN (ve.BudgetAmount) AS MONEY) AS BudgetAmount,
       CAST (MIN (ve.BudgetCases)  AS MONEY) AS BudgetCases
INTO #tmp
FROM
    dbo.VarianceSetup AS vs
    LEFT OUTER JOIN dbo.Actuals
       AS a ON vs.MonthNum=a.Month
                AND vs.RegionId=a.SalesRegionId
                AND vs.CategoryId=a.ProductCategoryId
                AND a.Year=2015
    LEFT OUTER JOIN dbo.VarianceReportEntries
       AS ve ON a.Month=ve.Month
             AND a.SalesRegionId=ve.SalesRegionId
             AND a.ProductCategoryId=ve.ProductCategoryId
             AND ve.Year=2015
GROUP BY
        vs.MonthName,
        vs.Region,
        vs.Category;
GO

SELECT [Month], Region, Category, ActualAmount, ActualCases, BudgetAmount, BudgetCases
FROM #tmp
UNPIVOT ( [Type] For [Value] In ( [ActualAmount], [ActualCases], [BudgetCases], [BudgetAmount] ) ) upvt


By what policy are SQL Server 2014 updates installed automatically?

You might be using Windows or Microsoft update on the server and that is set to Install updates automatically (recommended).
Windows Server Update Services (WSUS) enables administrators to manage the distribution of updates and hot-fixes released for Microsoft products to computers in a corporate environment.
You should change the setting to either

Check for updates but let me choose whether to download and install them
or

Never check for updates --> This means that you own and are responsible for checking any updates, downloading them , testing and installing them on the box.

A good read - Stop Automatically Updating Production Servers


Based on your sample data, the data you're actually querying for ID = 1396779 is different

No amount of trims, isnulls etc will change Schmoe into Frost or Jack into Joe.
Ergo, data is different.

Otherwise, you have some DB or Server confusion going on. A report based on the DataWarehouse can't give correct data if your query against the DataWarehouse is wrong.

Note that a cross database Synonyms or View, or a table in a different schema (your query doesn't use dbo.PCDB_VISIT for example) could mislead you. Or you have some client alias that means you are pointing at the wrong server from SSMS but the Report is correct.

Try this to make sure that you are where you think you are

DECLARE @ID int;
SET @ID = 1396779;
SELECT @@SERVERNAME, DB_NAME(), ID, 'LName' FName, LName Field FROM PCDB_VISIT PV


How can I have flexible PIVOT column names?

You'll need dynamic SQL for this. Something like (untested with your columns and queries because you left those pretty vague):

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);

SET @columns = N'';

SELECT @columns += N', ' + QUOTENAME(cName) FROM dbo.OtherTable;

SET @sql = N'
SELECT ' + STUFF(@columns, 1, 2, '') + '
FROM
(
  ...
) AS raw
PIVOT
(
  AGGREGATE(aColumn) FOR x IN ('
  + STUFF(@columns, 1, 1, '')
  + ')
) AS p;';

PRINT @sql;

EXEC sys.sp_executesql @sql;

Note that this is adapted from an article of mine on the same site as the link from your question:

Script to create dynamic PIVOT queries in SQL Server


Disk Size Considerations for a SQL Server 2008 R2 database

You will need exactly the same disk space for your mirrored (secondary) database as for the primary database. They are the same and the secondary database will have the same growth rate as your primary database and should be provisioned the same.

SP_SPACEUSED will show you how much space your primary database is now using (you should make it larger and not rely on autogrowth), and you can grab growth information from the default trace to give you some limited information about how often the database has grown, you can then infer the growth rate from the autogrowth parameters.

DECLARE @path NVARCHAR(260);

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

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


Mostly, this seems to be a misunderstanding. According to your query plan, you are retrieving rows=1410288 and the query itself is not that slow. It does not "take 224.9 seconds":

Execution time: 697.753 ms

You could probably improve the performance of your query some more by increasing the locality of clustered data, i.e., CLUSTER (or pg_repack) your table based on your index domain_id_btree (that appears under the different name of test_index_9 in your query plan). Details here:

Configuring PostgreSQL for read performance

If you do that (arriving at a favorable physical order of rows) and if you mostly just INSERT rows (UPDATE / DELETE is rare) and since there seem to be many rows per domain_id you might also benefit from a BRIN index (new in Postgres 9.5).

(It's remarkable that your current index is used at all, given that you retrieve ~ 6% of all rows. This indicates that your data is mostly clustered on domain_id already - or cost settings / statistics in your DB are inaccurate.)

But that's only going to reduce the 700 ms Postgres needs to execute the query. The rest of your reported 224.9 seconds are spent somewhere else, probably network transfer and processing in client.

Now, if you could retrieve fewer rows or less data per row ...


To the question you asked:

I wouldn't rely on disk queue alone. In fact I rarely even ever look at disk queue lengths unless I'm getting in deep with a problem. It is best to look at your disk's latency. Those are the Avg. Disk Sec/Read (or  /Write and /Transfer) counters. That tells you what your disk latency is from Windows' perspective. So the time that the request was taking after sent to the disk and brought back.. Disk Queuing nowadays doesn't tell you a lot because most IO subsystems are able to handle a disk queue depth and have multiple spindles doing work in your RAID group often. Finally - In this case - your disk queue length doesn't even look that bad. From here it looks like the max it was in the time of this screenshot (for the average length) was 1.377. That's nothing on most SQL Server systems. Look at your actual latency. Also I don't look at % Disk Time.. I look at the idle time instead.  That is a more reliable counter and you just have to do a little math to read it.. The more idle, the less activity.

To The General Question Behind Your Question

I'll ask this one by starting with a question - Why did you go right to your IO? There could be any number of things causing your slowdown. And to answer that exhaustively here is tough but a high level of some things to look at/consider:

Are you experiencing blocking? I would download SP_Whoisactive and have a look at that while you are getting these errors. Do you see blocking? Do you see the query behind the request(s) that are timing out? What is the duration?

Have you analyzed your SQL Server Wait Stats to see what your chief cause of waits are?
Do you know which query or queries are causing the timeouts? If so can you look at those and see if there is any room for tuning?

There could be many other things here. This could be on the connection or network. It could be blocking, it could be a need for index tuning and query tuning, it could be that you expect the queries to take longer than the default 30 second timeout, etc.

But I'd try and gather more data and then choose a path to go down. This is an old whitepaper but it is very useful to performance tuning by waits. While there will be new wait type Tom didn't mention in this paper, it still very much applies and will help you out.


Explanation

First all the JOINing is done.  This inflates the number of rows.  Then you do a GROUP BY and SUM.  Hence, the "unbelievably large numbers".

Solution 1

A solution is usually to do the GROUP BY and SUM over just one table.  If that can't be done, then find the ids for that one table, dedup them (via DISTINCT or GROUP BY), then do the grouping and summing.

Give this a try...

Rip out all mention of tables other than a.  See if the grouping and summing works correctly.
Make that a subquery and put it as a 'table' in with the rest of the tables:

SELECT ...
    FROM ( SELECT ... SUM... FROM profit_log WHERE ... GROUP BY ... ) AS a
    JOIN b ... -- as you have the rest

Solution 2

Another approach is to use subqueries for the other stuff...

SELECT a.`market_id`,
    SUM(a.`total_purchase`) as acc_purchase,
    SUM(a.`total_profit`) as acc_profit,
    SUM(a.`company_profit`) as acc_company_profit,
    ( SELECT ((b.`total_purchase` - c.`total_purchase`)/ c.`total_purchase`)
        FROM `profit_log` as b on a.`market_id` = b.`market_id`
    ) * 100 as growth,
    ...
FROM `profit_log` as a
WHERE a.`date` >= '2016-05-01'
  AND a.`date` < ('2016-05-05' + INTERVAL 1 DAY)
  AND a.`market_id` IN (0,20)
GROUP BY a.`market_id`

Need indexes

InnoDB tables really need a PRIMARY KEY.

Also you need this; it should increase performance significantly:  INDEX(market_id, date)


Replication is missing a specific set of rows on subscriber

The reason the round trip is not occurring is because your subscription from Server B to Server A has its @loopback_detection property set to TRUE, which is the default setting.

As we can see from sp_addsubscription, @loopback_detection specifies if the Distribution Agent sends transactions that originated at the Subscriber back to the Subscriber.  If set to true, the Distribution Agent does not send transactions that originated at the Subscriber back to the Subscriber.  If set to false, the Distribution Agent sends transactions that originated at the Subscriber back to the Subscriber.

I'm aware that the documentation states that this property is used with bidirectional transactional replication, however, I have reproduced your scenario and it appears to be being enforced with plain vanilla transactional replication as well.  Dropping the subscription and adding it back with @loopback_detection set to FALSE alleviated the problem and allowed for the round trips to occur.

Technically your topology can be considered Bidirectional Transactional Replication since both servers are publishing and subscribing to and from each other, even though the articles are different.

Keep in mind that you will need to drop and add the subscription back with @loopback_detection set to FALSE since sp_changesubscription does not allow you to change the @loopback_detection property on the fly.

I hope this helps.


Get distinct values through direct query of an index

I've never used SQLite so bear with me here. But it seems as if this problem is common among many RDBMS platforms.

When you select distinct values from your column you end up scanning all rows in the index:

This can be a great strategy if there aren't many rows in the table or if the column doesn't have very many duplicate values. But if you have millions of rows for each distinct value then you'll scan millions of rows just to return a single unique value. For data sets like that, it can sometimes be better to get the first distinct value, then skip to the next value, and so on. This can be accomplished via recursion in some platforms. You can also run one query at a time with each getting the next distinct value. For example, you could get the first value with this query:

SELECT MIN(chromosome) FROM dbsnp;

Then get the next value with this query (substituting the filter with the values of the first query):

SELECT chromosome FROM dbsnp WHERE chromosome > 'TEST_1' ORDER BY chromosome LIMIT 1;

And the next:

SELECT chromosome FROM dbsnp WHERE chromosome > 'TEST_2' ORDER BY chromosome LIMIT 1;

And so on. For these queries I'm getting index seeks:

For a relatively small data set, the single distinct query takes about 320 ms and the series of LIMIT 1 queries only took 4 ms. You'll of course need to write more code to use this solution, but it might be worth a shot.

db fiddle


How I can find out which views use other views in their sql code that generates them?

You can get which view uses another view with the following query:
select
    pg_views.viewname ,
    pg_views2.viewname as view_used from pg_views, pg_views as pg_views2
where
    pg_views.definition ilike '%'||pg_views2.viewname||'%'
    and pg_views.viewname <> pg_views2.viewname
    and pg_views.schemaname = 'public' and pg_views2.schemaname='public'
;

And In case you want to search a specific list of views:
select
    pg_views.viewname ,
    pg_views2.viewname as view_used from pg_views, pg_views as pg_views2
where
    pg_views.definition ilike '%'||pg_views2.viewname||'%'
    and pg_views.viewname <> pg_views2.viewname
    and pg_views.schemaname = 'public'
    and pg_views2.schemaname='public'
    and pg_views2.viewname in ('view_a','view_b','view_c')
;

In case that you need which materialized view uses another materialized view then you can use this query:
select
    pg_matviews.matviewname ,
    pg_matviews2.matviewname as view_used from pg_matviews, pg_matviews as pg_matviews2
where
    pg_matviews.definition ilike '%'||pg_matviews2.matviewname||'%'
    and pg_matviews.matviewname <> pg_matviews2.matviewname
    and pg_matviews.schemaname = 'public'
    and pg_matviews2.schemaname='public'
;

The whole idea is to use either the pg_views for normal views or pg_matviews join them together and search which view used another view into their sql code.
For example in order to see which view uses another materialized view:
select
    pg_views.viewname ,
    pg_matviews.matviewname as view_used
from pg_views, pg_matviews
where
    pg_views.definition ilike '%'||pg_matviews.matviewname||'%'
    and pg_views.schemaname = 'public'
    and pg_matviews.schemaname='public'
;

It was tested upon postgresql 11.


Why does the varchar datatype allow unicode values?

But the Trademark(™) and Registered(®) symbols are Unicode characters.

You are wrong here. Your strings contain only ascii characters.
Here is a simple test that shows you that your characters are all ascii (+ some extended ascii with ascii codes between 128 and 255):
declare @VarcharUnicodeCheck table
(
col1 varchar(100)
)

insert into @VarcharUnicodeCheck (col1) values ('MyCompany')
insert into @VarcharUnicodeCheck (col1) values ('MyCompany™')
insert into @VarcharUnicodeCheck (col1) values ('MyCompany░')
insert into @VarcharUnicodeCheck (col1) values ('MyCompanyï')
insert into @VarcharUnicodeCheck (col1) values ('MyCompany')

select *,
        right(col1, 1)as last_char,
        ascii(right(col1, 1)) as_last_char_ascii
from @VarcharUnicodeCheck;

Here you can clearly see that all your characters are 1-byte encoded:

Yes they are not pure ascii characters but they are Extended ASCII.
Here I show you real Unicode character Trademark(™) and its code and binary representation:
declare @t table (uni_ch nchar(1), ascii_ch char(1));
insert into @t values (N'™', '™');

select unicode(uni_ch) as [unicode of ™],
       ascii(ascii_ch) [ascii of ™],
       cast(uni_ch as varbinary(10)) as [uni_ch as varbinary],
       cast(ascii_ch as varbinary(10)) as [ascii_ch as varbinary]
from @t;

Finally, you can see that Trademark(™) Unicode character has 8482 code and not 153:
select nchar(8482), nchar(153)


Using the descriptions of the attributes given in the figure, convert the ERD shown in Figure P6.2 into a dependency diagram that is in at least 3NF

Start with learning third normal form

http://en.wikipedia.org/wiki/Third_normal_form.

The key part of the third normal form is that every non-key attribute should rely on the key.

For example, fIrst name and last name should only be present in one table so as not to repeat this information for every row in which they attend a presentation. Keep separating attributes out until there is not data repeated in more than one place.

If you program, you might know the adage: DRY or Don't Repeat Yourself. This holds true for data as well as for programming as if you need to change one of the attendees names, you don't want to have to find and replace it in more than one place.


PostgreSQL has indexes on expressions (since version 7 I think!), check the documentation: Indexes on Expressions.

So, you could easily add an index on (column_a + column_b), with almost exactly the same syntax as in Oracle. The only difference would be the extra parentheses around the expression:

CREATE INDEX idx
  ON Example_tab
  ((column_a + column_b)) ;

Note in case the above and the documentation isn't clear:
An expression can be as complicated as one wants and can include any number of operators or function calls.

MySQL does not have such indexes but they have a similar concept that can be used instead. MariaDB introduced VIRTUAL (computed) columns in their 5.2 version and the core MySQL just added them (called GENERATED) in their latest 5.7. See: Generated columns in MySQL.

So - in both MariaDB and MySQL - you can add a computed column and then an index on it. The syntax is similar but there are small differences. For MariaDB, the column can be either VIRTUAL or PERSISTED. If you want it indexed, it has to be PERSISTED:

ALTER TABLE Example_tab
   ADD COLUMN a_plus_b AS
       (column_a + column_b) PERSISTED,
   ADD INDEX idx
       (a_plus_b) ;

For MySQL the column can be defined as either VIRTUAL or STORED. If you want it indexed, for versions before 5.7.8, it has to be STORED. For versions 5.7.8+ in can be either stored or not (in which case, the computed values are only stored once, in the index). See details on Indexes on Virtual columns:

ALTER TABLE Example_tab
   ADD COLUMN a_plus_b AS
       (column_a + column_b),     -- optionally: STORED,
   ADD INDEX idx
       (a_plus_b) ;


How to get mongodump --quiet to still report errors?

As you already said

  I've tried

mongodump --quiet

As per mongodb documentation Here

  Runs the mongodump in a quiet mode that attempts to limit the amount
  of output.

This option suppresses:

output from database commands
replication activity
connection accepted events
connection closed events

mongodump is an useful tool to backup Mongo database. Apart from taking a cold backup, it can also take hot backup. i.e You can connect to a running instance of MongoDB and take a backup even when users are using the database.
There are two way you can mongdodump

Backup by Shutting down Mongod Instance
Backup without Shutting down Mongod Instance

For further your ref from @RAMESH NATARAJAN Blog How to Backup and Restore MongoDB using mongodump and mongorestore


The option log_queries_not_using_indexes is probably the cause.

From the MySQL Server Option

  If you are using this option with the slow query log enabled, queries
  that are expected to retrieve all rows are logged. See Section 5.2.5,
  “The Slow Query Log”. This option does not necessarily mean that no
  index is used. For example, a query that uses a full index scan uses
  an index but would be logged because the index would not limit the
  number of rows.

So, your query is a "not using index query" and a "full scan query"...

mysql> explain select * from yourTable LIMIT 10 OFFSET 100\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: yourTable
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 124386
        Extra:
1 row in set (0.01 sec)


I'm currently evaluating a similar setup and there are a couple of solutions.

What you're looking at here is basically a master-master replication scenario, where both databases are writable and changes in each are replicated to the other.

There is Bucardo, which is a postgresql-only replication system written in Perl and there are a number of tutorials for setting it up, check the Odoo community forums, too.

Then there's SymmetricDS, which does the same for a number of different databases and is written in Java.

Since your're dealing with master-master replication, you have to account for the occasion of both databases changing the same rows in the same table at the same time, so you'll have to configure conflict-resolution strategies (basically which change wins, but it can be a lot more involved like calling an external handler to figure out what to do).


Can/Should rarely used columns be placed in a different table for performance?

(re: MySQL/MariaDB)  It depends.

For small columns, say under 1KB, don't bother with the hash.
For large columns, say over 7KB, it may not matter -- InnoDB puts large columns "off-record" in a block separate from the main part of the record.
For medium-sized columns, say 1KB-7KB, the column is likely to be kept "on record", thereby making any action on the row fetch the bulky column, too.  This is even if you don't say SELECT *.
Regardless of what you do, avoid using SELECT *; instead, spell out the columns you need.  (* will bite you if you ever add a column to the table.)
Pack the hash into BINARY(16) for MD5, etc., rather than a VARCHAR of twice the size.
The time taken to fetch a row is more than unpacking the columns.  That is, "how many columns" is not a metric to worry about.  (No, I don't have numbers to back that up; just decades of experience.)

The hash (whether md5, sha1, etc) is a bigger nuisance.  It is very 'random', thereby leading to cache misses when inserting a new hash or looking up by the hash.  This issue comes into play when the BTree with the hash is bigger than can be cached in the buffer_pool.  To phrase it another way, use of a hash on a billion-row table will slow down all accesses to the speed of the disk.  And it will slow down other processes due to cache misses.

InnoDB blocks are 16KB; the underlying OS or drive block size does not factor into this discussion.

In the opposite direction, consider putting frequently updated columns (clicks, Likes, views) in a separate table.  (But that's another Q&A.)


SQL Server 2012 Index Reorganize Logfile Growth

In full recovery model, internal log truncation depends on you taking a log backup. If you wait three hours in between them, you'll have three hours of transactions sitting in there.

Index maintenance, as you've noted, is fully logged. If you truly perform some maintenance on every index in your database (or even most of them -- it's hard to say without knowing at what threshold you reorganize indexes), it's entirely possible for your log file to need to accommodate every one of those in between log backups.

Let's say in the 30 GB database you reorganize indexes at 5% fragmentation (a common tactic), and most of them have hit that level. You'll need to perform some action on all of those. In the other databases, if you have the same threshold, they may have far fewer indexes to touch. This could explain the discrepancy.

I understand that not much is under your control here, but if I had my DBA druthers, I'd want to

Take more frequent log backups
Perform less aggressive maintenance
Use Ola Hallengren's free scripts

Using Ola's scripts is a no-brainer. You can set very specific levels to reorg or rebuild, which indexes to skip or include, and at what size you want to perform some activity. I get that this isn't your question, but should you choose to share this with management, I'd be a bad person if I didn't include it.

The other plus side? All the actions that Ola's scripts run get logged to a table, so you can look at what actually happened. That's the most important thing to have around for forensics if you wanna find out why something happened.

Hope this helps!


Determining whether a MySQL column is ever used

Mind the Gap

Let me give you a third option if the ids have a lot of gaps...

One significant cause of "running out of id values" is queries that leave gaps in AUTO_INCREMENT values.  An INSERT statements where row is not inserted first allocates an auto-inc value, then fails to use it.  INSERT IGNORE will do this silently.

Sometimes a useful workaround is to change the code such that it does not do the INSERT if it is not going to succeed.  Normalization is perhaps a normal case for this.  Here is a technique for batch normalization that avoids the problem.

(OK, this suggestion is not useful if you can't get to the source code.  But it could be useful to other readers; it has saved me a few times.)

General log

By turning on the "general log" for a period of time, you can collect all queries.  Then you can search for the table or "id", etc.  It will be tedious.  It will not be 100% safe, since some queries may not have been executed during that time.

innodb_autoinc_lock_mode

innodb_autoinc_lock_mode did not exist until 5.5, so that is not an option.

(Again, I mention this for other readers.)


Slow-running query on clustered MySQL environment

You may want to consider if you really need the MySQL Cluster, it may be overkill for your purposes and may not justify the complexity. A pair of MySQL Master-Master or Master-Slave instances with replication are good enough for many shops and will deliver better performance in this scenario.

If you have to stay with your current architecture, you can add Index on column "somecol", first try HASH, then B-TREE index. These should help, still performance would not be as good as a simple host instance.


How can I optimize searching a column for a sub-string in SQL Server?

If you know the exact string, then using a binary collation for your search can help.

WHERE [value] LIKE '%{substring}%' COLLATE Latin1_General_BIN

, because it won't have to do case conversions and the like. This can make it a few times faster, but not lightning fast.

Another option is to consider blowing up your table and indexing that - using an indexed view over a john between a table of numbers and your table, leveraging the substring function.

For example, if you have a table of at least 1000 numbers, you could make a row for each character. Yes, it'll cost space, but it will quickly let you find every "H" in there.

Problem is - it's still not quite what you need, because there will be a lot of "H"s. Better might be to grab three-letter combinations. So if you're looking for 'Hotel', you know that either "Hot", "ote" or "tel" must be in the three-letter combo table. You could make an inline table-valued function to handle this. Naturally, when you search for "Hot", the next block for that obj_id must be like 'el%', and so on, plus you should check that your main table still successfully satisfies the main predicate.

It's an idea... if you can put up with the complexity of working around it like this.


Enabling InnoDB on an existing MySQL production server

InnoDB should already available. You can check with

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

or

mysql> show variables like 'have_innodb';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_innodb   | YES   |
+---------------+-------+
1 row in set (0.00 sec)

mysql>

Make sure skip-innodb is not in /etc/my.cnf

Since you are using the InnoDB Plugin, you need to activate new features to greatly enhance InnoDB Performance. For example, did you know there are new options for making InnoDB use multiple CPUs?  See my past posts on this:

May 26, 2011 : About single threaded versus multithreaded databases performance
Sep 12, 2011 : Possible to make MySQL use more than one core?
Sep 20, 2011 : Multi cores and MySQL Performance

This is important because there are occasions when InnoDB in older versions of MySQL is faster than newer versions. I wrote about this as well:

Jun 19, 2011 : How do I properly perform a MySQL bake-off?
Oct 05, 2011 : Query runs a long time in some newer MySQL versions
Nov 24, 2011 : Why mysql 5.5 slower than 5.1 (linux,using mysqlslap)

Additional Reference from MySQL Performance Blog : http://www.mysqlperformanceblog.com/2011/10/10/mysql-versions-shootout/


I have found the solution.

There is a variable called innodb_print_all_deadlocks which logs all the deadlocks in to the error log.


Does having a big table that I do not query cause the whole DB to be slower?

In my experience, a big table should not slow down the whole database. it can slow down the queries on same table though but that too will depend on the structure of table and indexes.

You dont have to store the PDF in database, I'd recommend that you eliminate the BLOB column and make it a CHAR. then store the PDF files on disk and their names into database. Here is how I'd have done this. My table would be:

CREATE TABLE logs (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    file_name CHAR(64) UNIQUE NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX USING BTREE (file_name)
) ENGINE = INNODB;

and then create a SHA256 sum of PDF file and use it as filename.

Some obvious benefits:

This table is a fixed width table (See this), hence faster in all ways.
file_name column is a SHA256 hash of file which is always 64 characters in length and unique
You have option to save PDF files in any directory on disk and reconstruct the path from this data, for example if you store the files at /home/username/html/storage/log_reports/pdf and categorise them by month or week of creation you have the created_at timestamp which you can use.

so splitting the data by days of month in a given year is

/home/username/html/storage/log_reports/pdf
    |
    |-- 2014
    |    |
    |    |-- May
    |        |
    |        |-- 5th
    |             |-- 4355a46b19d348dc2f57c046f8ef63d4538ebb936000f3c9ee954a27460dd865.pdf
    |             |-- 53c234e5e8472b6ac51c1ae1cab3fe06fad053beb8ebfd8977b010655bfdd3c3.pdf

and so on.

Example case:

CREATE TABLE logs (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    file_name CHAR(64) UNIQUE NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX USING BTREE (file_name)
) ENGINE = INNODB;

Insert some random data

INSERT INTO
  logs (file_name, created_at)
VALUES
  ('4355a46b19d348dc2f57c046f8ef63d4538ebb936000f3c9ee954a27460dd865', TIMESTAMP('2014-5-10')),
  ('53c234e5e8472b6ac51c1ae1cab3fe06fad053beb8ebfd8977b010655bfdd3c3', TIMESTAMP('2014-5-9')),
  ('1121cfccd5913f0a63fec40a6ffd44ea64f9dc135c66634ba001d10bcf4302a2', TIMESTAMP('2014-5-5')),
  ('7de1555df0c2700329e815b93b32c571c3ea54dc967b89e81ab73b9972b72d1d', TIMESTAMP('2014-5-1'));

Recreate the path to file:

SELECT CONCAT(DATE_FORMAT(created_at, '%Y/%D/%M/'), file_name, '.pdf') from logs where id = 3;

output:

2014/5th/May/1121cfccd5913f0a63fec40a6ffd44ea64f9dc135c66634ba001d10bcf4302a2.pdf

Then you can just prepend the path to storage directory and send the file for download or attachment.

Here is an example fiddle on this


How to make a FULLTEXT search with ORDER BY fast?

The first problem here is something you cannot control. What is it ???

The Query Optimizer's reaction to a FULLTEXT index. Why ?

(Now imagine the start of a STAR WARS movie with scrolling words...)

  A very long time ago, I discovered that FULLTEXT indexes will nullify the MySQL Query Optimizer's use of other indexes

Jun 10, 2011 : Mysql fulltext boolean ignore phrase
Oct 25, 2011 : FULLTEXT index ignored in BOOLEAN MODE with 'number of words' conditional
Mar 18, 2012 : Why is LIKE more than 4x faster than MATCH...AGAINST on a FULLTEXT index in MySQL?
AND MORE LIKE IT !!!

SUGGESTION

Refactor the Query so that the MATCH ...AGAINST collects keys only

EXAMPLE #1

SELECT a.id FROM a
WHERE
MATCH (`search1`,`search2`) AGAINST ('aaaa' IN BOOLEAN MODE)
ORDER BY a.id DESC
LIMIT 5;

should become something like

SELECT N.id FROM
(SELECT id FROM a WHERE
MATCH (`search1`,`search2`) AGAINST ('aaaa' IN BOOLEAN MODE)) M
INNER JOIN a N USING (id)
ORDER BY N.id DESC LIMIT 5;

EXAMPLE #2

SELECT a.id,a.popularity FROM a
WHERE
MATCH (`search1`,`search2`) AGAINST ('aaaa' IN BOOLEAN MODE)
ORDER BY a.popularity DESC
LIMIT 5;

should become something like

SELECT N.id,N.popularity FROM
(SELECT id FROM a WHERE
MATCH (`search1`,`search2`) AGAINST ('aaaa' IN BOOLEAN MODE)) M
INNER JOIN a N USING (id)
ORDER BY N.popularity DESC LIMIT 5;

CONCLUSION

The main idea: Collect the keys using MATCH ...AGAINST and join it back to the source table


Sql Server Maintenance Plan - Best Practices on Tasks and Scheduling

Josh,

This is a very common task for all DBAs and the right answer is NOT the same for every one and for each server. As lot of other things, it depends on what you need.

Most definitely you don't want to run "Shrink Database" as already suggested. Its EVIL to performance and the below ref will show you why. It causes disk and as well as index fragmentation and this can lead to performance issues. You are better off by pre-allocationg a big size for the data and log files so that autogrowth will NOT kick-in.

I didn't understand your #2. selected tables full backup. Can you elaborate more on this?

Coming to Index reorganize, update statistics and index rebuilds, you need to be careful on how you do this otherwise you will end up using more resources and also end up with performance issues.

When you rebuild indexes the statistics of the indexes are updated with fullscan but if you do update statistics after that, then those will be updated again with a default sample (which depends on several factors, usually 5% of the table when table size > 8 MB) which may lead to performance issues. Depending on the edition you have, you may be able to do online index rebuilds. The right way of doing this activity is check the amount of fragmentation and depending on that either do index rebuild or index reorganize + update statistics. And also you may want to identify which tables need to update stats more frequently and try to update stats more often.

Maintenance Plans are OK but its hard to get the best out of them doing these customizations unless you can login to SSIS and tweak the MP's. that's why I prefer NOT to use them and use Ola Hallengren's free scripts that are more robust than MP's. Also, I would recommend to catch up on the referenced article by Paul Randal on this topic.

Ref: http://technet.microsoft.com/en-us/magazine/2008.08.database.aspx

This is NOT a comprehensive answer to your question but a good starting point. HTH and let us know if you have any additional questions/comments.


Don't use "--full".

Do you get any messages, other than the one you show, while vacuuming?

What is the output of select datname, age(datfrozenxid) from pg_database;


Understanding DBCC CHECKDB, Snapshots, and SQL Server Standard Edition

You can refer to Paul Randal's blog (the guy who wrote DBCC CHECKDB) :
CHECKDB From Every Angle: Complete description of all CHECKDB stages :

For SQL Server 2005, CHECKDB uses an internal database snapshot to provide the required transactional consistency.
So when CHECKDB starts in SQL Server 2005, the first thing it does is work out whether it can run online – if so it creates a hidden database snapshot of the source database (i.e. CHECKDB’s target database). As the database snapshot is hidden, there is no control over where the snapshot files are placed – in fact they are created as alternate streams of the files comprising the source database.
From SQL Server 2014 onward, alternate streams are not used, although the database snapshot is created in the same location as the existing database.

As a side note, since you are using Standard edition, DBCC CHECKDB will not perform parallel checking of objects as opposed to Enterprise edition wherein it honors the MAXDOP setting configured for the server instance.
Also, BOL has a clear writeup on CHECKDB Internal Database Snapshot


In MySQL multi-column unique constraints are implemented such that they allow multiple null values. You can make use of this by using nulls to represent the non-owners:

create table account_membership (
  account_id int not null references accounts (id),
  user_id int not null references users (id),
  is_owner boolean null check (is_owner in (1, null)),
  unique key (account_id, is_owner)
)

The above will allow multiple null values in is_owner for each account_id, but only one 1.

Note that this won't be portable as other databases treat nulls in unique constraints differently (i.e. only allow one null value).


Backing up & restoring 10-20 SQL Server databases to a ~synchronous state?

I need to backup up 10-20 SQL Server dbs used simultaneously by a single enterprise app, while they are online, in such a way as to restore them to a state that is largely synchronized across all dbs

What you are looking for is a consistent backup across all your customer databases, you should use FULL backups along with Marked Transactions (emphasis in bold added):

  When you make related updates to two or more databases, related databases, you can use transaction marks to recover them to a logically consistent point. However, this recovery loses any transaction that is committed after the mark that was used as the recovery point. Marking transactions is suitable only when you are testing related databases or when you are willing to lose recently committed transactions.

Make sure that you take adhoc transaction log backup with COPY_ONLY, else your recovery will be a pain, since any adhoc transaction log backup without COPY_ONLY will break the log chain. As a precaution, you can restrict users to use only COPY_ONLY backups.

  I need a solution for SQL Server versions 2008 R2 and later. Db sizes is up to 50 GB per db and the time to backup all of them is likely over 1-2 hours.

Marked transactions will work for your situation. The only thing to make parallel backups is to  STRIPE them, but then you end up making sure that you don't lose your stripes of backup. To make them faster, you can play with BUFFERCOUNT and MAXTRANSFERSIZE.

You should use backup compression as well as enable Instant file initialization.

Refer to

Using Marked Transactions (Full Recovery Model)
How to increase SQL Database Full Backup speed using compression and Solid State Disks ?


As usual in these cases, "It depends".
Depends, as a minumum, on:

How performant is your PC in general (especially: how much RAM, how many CPUs, are your disks SSD, etc).
How often do you practice with SQL Server.

Personally, I have a situation like yours and I don't feel any kind of slowness. Starting/stopping services can be boring when you are in a hurry, but if there are situations in which you need "all the power" of your PC, you can safely stop or pause SQL Server related services: it is not a DB server, then you know when you can stop it.
An idea could be to make a script to stop/start all SQL related services.


Does a UNIQUE constraint implicity contain the primary key?

Yes and no.

Yes in that the PK column(s) are tacked onto each UNIQUE index and the INDEX implicitly generated by each FOREIGN KEY.

No in that the PK column(s) are not part of the uniqueness constraint implied by UNIQUE.

For example:

PRIMARY KEY(id),
UNIQUE(x)

is like

PRIMARY KEY(id),
INDEX(x,id) and a uniqueness constraint on only `(x)`.

FOREIGN KEY(y) will generate INDEX(y) unless it finds an existing index starting with y.


Query on Linked Server in SQL Active-Passive

No extra configuration is needed for the linked server to work on
DB2 and that's one of the Benefits of a Failover Cluster
Instance:

  In case of a failure (hardware failures, operating system failures,
  application or service failures), or a planned upgrade, the resource
  group ownership is moved to another WSFC node. This process is
  transparent to the client or application connecting to SQL Server and
  this minimize the downtime the application or clients experience
  during a failure. [...]

  Zero reconfiguration of applications and clients during failovers

Yes, it will work automaticaly on DB1 when it fails back and the reason is that:

  Only one of the nodes in the FCI owns the WSFC resource group at a
  time.

In fact you must use that virtual ip address or network
name
in order for the failover to work transparently:

  An FCI runs in a WSFC resource group with one or more WSFC nodes. When
  the FCI starts up, one of the nodes assume ownership of the resource
  group and brings its SQL Server instance online. The resources owned
  by this node include:

  Network name
  IP address
  Shared disks
  SQL Server Database Engine service
  SQL Server Agent service
  SQL Server Analysis Services service, if installed
  One file share resource, if the FILESTREAM feature is installed

This image from Derek Seaman's IT Blog might help you understand it better:

You never configure your applications (or linked servers) to reference DB1 (NodeA) or DB2 (NodeB), you must always reference the virtual ip address or network name (VirtualNode1) and it shall transparently address the active node of the cluster.


Oracle database uses wrong parameter file

You can not or at least you should not. The right pfile (or spfile) should contain ORACLE_SID in it's name.

Theoretically you  can start the database using:

SQL> startup pfile='<path to init.ora>'

But I won't recommend it.


Moving a large MariaDB database between MariaDB servers

At my job, we do this several times per week. I'm part of a team who manages about 9000 MySQL database instances, and we're constantly moving them from server to server as they grow.
NOTE: You cannot just copy files from a MySQL / MariaDB datadir while the server is running. You are virtually guaranteed to corrupt your database. Some subset of the data is in log files or in RAM, and it won't be copied with consistency.
We do it this way:

Use Percona XtraBackup to make a physical backup. It doesn't take long, even for a multi-hundred-GB database instance.

Transfer this backup to the other host.

Restore the backup on that new host.

Configure replication on the new host, to subscribe to changes on the original host. This allows it to "catch up" to all the changes that occurred while you were transferring the file and restoring them.

Wait for the new instance to catch up.

Set the original instance to be read-only, so applications can't make any new changes. Then let the replica catch up with respect to the final few seconds of changes (this might take no time at all).

Update applications to use the new server instead of the old one. This is done either by changing configuration in the app, or else just use DNS to give the name of the original server to the new server.

This is all assuming you want the whole MySQL instance to be moved to a new server. If you have individual schemas in the original server that you want to move, that's a bit more complicated.


Is mongodb upsert the right option for write intensive use case?

To answer you question, and this is my opinion not hardwired:

1. Is it wise to keep the documents embedded and perform upserts every time (considering the load) rather than keeping the documents in 2 separate collections and perform joins to report the data to client applications?

Ans: MongoDB is designed to handle embedded document structure, so don't worry if you are using upserts to update/ insert documents. Also two separate collection will increase your index overhead.

2. Considering the load, I suspect that too much of upsert would created high IO and crash the system.

Ans: If you add an correct index, then the upsert can become faster: after all the index is used to 'find' the document. The caveat is in the field(s) the index operates on and the fields that you're updating. If the updated portion is part of the index, you will have a performance impact on updating the document. If the updated portion is not part of the index, you will not incur a penalty for writing in the existing document. If the document is added though, you will have a minor performance impact, since the index collection is update.

Please feel free to revert back your opinion on this.


When you execute

select * from  BITAND_TEST;

you get 1,44115188075856E17.  It is not the same value you entered, you have lost some precision. Try this

select TO_CHAR (BITMAP, '9999999999999999999999') from  BITAND_TEST;

to see the difference. I get

 144115188075855870


Distinct counts from large table are too slow

Would it be improved if I created an index on subscription_id and listing_id together?

Possibly yes, an index on (subscription_id, listing_id) (columns in this order!) might very well allow a cheaper index-only scan. You did not provide enough information to say more.

If it's all about this particular subscription_id, then a partial index would help a lot more:

CREATE INDEX ON apps (listing_id) WHERE subscription_id = 1298;

If there are more than a few duplicate listing_id per subscription_id, use
this adapted query with a recursive CTE:

WITH RECURSIVE rcte AS (
   (
   SELECT listing_id
   FROM   apps
   WHERE  subscription_id = 1298
   ORDER  BY listing_id
   LIMIT  1
   )

   UNION ALL
   SELECT a.listing_id
   FROM   rcte r
   JOIN   LATERAL (
      SELECT a.listing_id
      FROM   apps a
      WHERE  a.subscription_id = 1298
      AND    a.listing_id > r.listing_id
      ORDER  BY a.listing_id
      LIMIT  1
      ) a ON a.listing_id IS NOT NULL
   )
SELECT count(*) FROM rcte;

Requires index from above to be fast. For many duplicates it can be faster by orders of magnitude. The point is to work around a still missing (up to Postgres 12) index skip scan. See:

Fast way to count distinct column values (using an index?)
Optimize GROUP BY query to retrieve latest row per user

If, OTOH, there are no duplicate listing_id, then get rid of DISTINCT to make it a lot faster.


LGWR is switching frequently in every 3,4,7 minutes. what should i do

The output size of archivelogs are maximum 90mb to 100mb.

Its not necessary to have archived log file should be same.

  during night session logs get filled upto 180mb out of 200mb. but during morning session logs get maximum to 90mb to 100mb

Its not necessary to have log switch occur when online redo log file is 100% full. There is also an algorithm inside Oracle which determines the timing for the log switch.
After the log switch, the archiver process will copy only the actual information from the
redo log files. Since the redo logs are not 100% full after the log switch this results in uneven, smaller files than the original redo log files.

Some of the factors which may enforce frequent log switch are listed below:
Online redo log file size
LOG_BUFFER_SIZE parameter.
Current load of the database
 Logfile space reservation algorithm

Details are given in an Oracle knowledge based document.(MOS account required)
Archived redolog is (significant) smaller than the redologfile. (Doc ID 1356604.1)


The optimizer does not always consider index-union plans (like the one shown in your second graphic) to resolve disjunctions (OR predicates) unless a FORCESEEK or INDEX hint is specified.  This is a heuristic* based on some practical considerations:

Index union is not often enough a good plan selection for general queries.
The number of ways indexes can be combined grows exponentially.

Using a hint changes the way the optimizer searches the space of possible plans.  It disables some of the general heuristics and pursues a more goal-orientated strategy.

The optimizer's usual primary goal is to find a good plan quickly.  It does not exhaustively search for the 'best' plan (even relatively simple queries could take years to compile if it did).

Joins with multiple conditions separated with OR have long been problematic.  Over the years, the optimizer has added new tricks like converting them to equivalent UNION forms, but the transformations available are limited, so it is quite easy to come unstuck.

As far as the query plan is concerned:

The first row from DispatchLink causes a full scan of the Dispatch table
The result of the scan is stored in an internal tempdb worktable (the Table Spool)
The join checks every row from the worktable against the full OR predicate
The next row is fetched from DispatchLink and the process repeats from step 3

If there are 25,000 rows in the Dispatch Link table, the spool will be fully scanned 25,000 times.  This is a disaster of course (and without index intersection, the best the optimizer can do is run the whole thing on multiple threads).

Percentage costs in query plans are only the optimizer's estimates.  They never reflect actual execution costs, and are subject to the optimizer's model and will usually bear little resemblance to the 'true' cost of executing the plan on your specific hardware.

Costing numbers are there to be informative, but they should not be taken literally.  The particular model the optimizer uses happens to produce pretty good plans for most queries on most systems across the world - that does not mean the model approximates anyone's reality, just that it happens to work reasonably well in practice.

Changing the design so that (Dispatch, Contract) pairs are stored in rows rather than repeated across columns will make the whole index-intersection problem go away.  Relational designs with useful constraints and indexes almost always get the best out of the optimizer.

* This can be overridden with undocumented trace flag 8726


Trigger on PostGIS line layer occurs twice

You are using an AFTER trigger - thus any action will have already taken place and the UPDATE will actually run twice: once the for the "original" statement and then again from within your trigger.

It seems to be, you actually want a BEFORE trigger. And there you just assign values to the NEW row, rather than UPDATEing the table itself.

So I think what you are looking for is something like this:

CREATE or REPLACE FUNCTION test.test_track_history_tracker()
  RETURNS trigger AS
$new_test_track_history_tracker$
BEGIN
  -- INSERT

  IF (TG_OP = 'INSERT') THEN
     INSERT INTO test."Conduite_test_history"
        (diametre, type, materiau, origininfo, dtreno, dtpose, reparateur, dt_dmd, id_sig, gid_org, created, created_by, modified, etat, geom)
     VALUES
         (NEW.diametre, NEW.type, NEW.materiau, NEW.origininfo, NEW.dtreno, NEW.dtpose, NEW.reparateur, NEW.dt_dmd, NEW.id_sig, NEW.gid, current_timestamp,
         current_user, FALSE, 'CREATION', NEW.geom);
     raise notice 'Insert happened';

     -- continue with the INSERT
     RETURN NEW;

  -- UPDATE
  ELSEIF (TG_OP = 'UPDATE') THEN
     -- this IF is equivalent to your WHERE statement
     IF new.deleted IS NULL THEN
       new.deleted := current_timestamp;
       new.deleted_by := current_user;
       new.modified := true;
       new.eta := 'MODIFICATION ARCHIVEE';
     END IF;

     INSERT INTO test."Conduite_test_history"
         (diametre, type, materiau, origininfo, dtreno, dtpose, reparateur, dt_dmd, id_sig, gid_org, created, created_by, modified, etat, geom)
     VALUES
         (NEW.diametre, NEW.type, NEW.materiau, NEW.origininfo, NEW.dtreno, NEW.dtpose, NEW.reparateur, NEW.dt_dmd, NEW.id_sig, NEW.gid, current_timestamp,
         current_user, FALSE, 'MODIFICATION COURANTE', NEW.geom);
     raise notice 'Update happened';

     RETURN NEW; -- continue with the UPDATE
  -- DELETE
  ELSEIF (TG_OP = 'DELETE') THEN

     -- this is correct: it will turn the DELETE into an UPDATE
     UPDATE test."Conduite_test_history"
        SET deleted = current_timestamp, deleted_by = current_user, etat = 'SUPPRESSION'
     WHERE deleted is NULL and gid_org = OLD.gid;
     raise notice 'Delete happened';

     RETURN NULL; -- this will prevent the actual DELETE in t
  END IF;
END;
$new_test_track_history_tracker$
LANGUAGE plpgsql;

And then create a BEFORE trigger using that trigger function:

--ADD TRIGGER
DROP TRIGGER IF EXISTS trg_test_track_history_tracker ON test."Conduite_test";
CREATE TRIGGER trg_test_track_history_tracker
  BEFORE
       INSERT OR UPDATE OR DELETE ON test."Conduite_test"
  FOR EACH ROW EXECUTE PROCEDURE test.test_track_history_tracker();


UPDATE on column referenced in sub-query

Can you create indexes? Are TRANSFORMED_TYPE and/or TRANSFORMED_KEY unique?
The DB hast to compare xxx rows from "where workSet.MARK = 1" with yyy rows from "where source.SELECTABLE = 0 and source.MARK = 1" in subquery(FTS).
So you can calculate yourself: xxx * yyy rows = zzz rows to compare with full table scans.

This is faster but with indexes could perform even better.

update CE_WORKSET_READ_ENTRY workset
set workSet.MARK = 0,
workSet.SELECTABLE = 0
where workSet.MARK = 1 and
exists (
select 1
from CE_WORKSET_READ_ENTRY source
where
source.TRANSFORMED_TYPE = workSet.TRANSFORMED_TYPE
and source.TRANSFORMED_KEY =workSet.TRANSFORMED_KEY
and source.SELECTABLE = 0
and source.MARK = 1);

If TRANSFORMED_TYPE and/or TRANSFORMED_KEY are unique then you can remove the subquery.


Do I need to rebuild indexes after importing a .bacpac into a database?

There is no such magic in SQL Server!

Although, IMO it would be a great if Microsoft provide such options in the future.

So, you have to do rebuilding indexes on your own.

The good starting point is a set of scripts provided by Ola SQL Server Backup, Integrity Check, and Index and Statistics Maintenance.


Print, transaction and cursor performance in Transact-sql

You could declare a table variable, insert to that rather than printing and dump it in toto at the end of your process:

declare @message table (i int identity(1,1), words varchar(100));

The identity column will keep things in chronological order.  @tables' contents survive a rollback so are preferable over #tables or normal tables for this purposes.

I haven't profiled this suggestion; I throw it out there for what it's worth.


Select two aggregate functions on the same column using different criteria - performance

There is a trick you can use to filter aggregates:

select
 --...
 , sum(case when CONDITION THEN 1 ELSE 0 END) AS FilteredCount
 , sum(case when CONDITION THEN SomeCol ELSE 0 END) AS FilteredSumOfSomeCol

If you can fit your aggregates into this scheme you can avoid the need for repeated query parts like in the current code. It looks like you can use this trick.


Online rebuild consuming lot of log space

The clustered index is the table. It includes all columns. It is (basically) impossible for the clustered index to be smaller than any one non-clustered index. You probably looked at only the clustered key column size or only the non-leaf level when you read that 7GB size.
So if you rebuild the clustered index, then you rebuild all columns - including the compression setting. Rebuild of the clustered index in the end copies the data to a new location and after its done, it removes the data from the old location.
If you were on 2017, you could do resumable index rebuild, which allow you to pause the rebuild, empty the log and then resume the rebuild again.


It's a bit difficult to determine exactly what you want from the way your question is worded, but it sounds like this post may give you the needed information

Identify when a SQL Server database was restored, the source and backup date

From the post:

  After restoring a database your users will typically run some queries
  to verify the data is as expected. However, there are times when your
  users may question whether the restore was done using the correct
  backup file. In this tip I will show you how you can identify the
  file(s) that was used for the restore, when the backup actually
  occured and when the database was restored.

Here's the script from that post:

SELECT [rs].[destination_database_name]
    ,[rs].[restore_date]
    ,[bs].[backup_start_date]
    ,[bs].[backup_finish_date]
    ,[bs].[database_name] AS [source_database_name]
    ,[bmf].[physical_device_name] AS [backup_file_used_for_restore]
FROM msdb..restorehistory rs
INNER JOIN msdb..backupset bs ON [rs].[backup_set_id] = [bs].[backup_set_id]
INNER JOIN msdb..backupmediafamily bmf ON [bs].[media_set_id] = [bmf].[media_set_id]
ORDER BY [rs].[restore_date] DESC


Iterating over the output of a SQL query with PowerShell

PowerShell offers the ability to do things in many different ways, so this is just the method I would do in solving the problem at hand, based on the information provided.

SMO offers the ability to check the table class to more efficiently find the database in question that contain the table and column specified; instead of trying to use INFORMATION schema views that some folks have a case against ever using them.

I would break this up into sections that will make it more easy to debug. I am assuming you are not worried what UNC path is tied to which server, just making sure the UNC path exist and the file has been moved. So I would first pull all the UNC paths from any database I find the table and column exist.

#Load assembly
[void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')

$serverList = 'server1','server2','server3\instance1','server3\instance2'

$searchTableName = 'File'
$searchColumnName = 'Location'

$uncPaths =
foreach ($s in $serverList)
{
    $srv = new-object microsoft.sqlserver.management.smo.server $s

    # The first "where" filters for the database with the specified table name
    # The second "where" filters for the column to exist within the table
    # The "-ExpandProperty Parent" is to go backwards in order to output just
    #   the database name that contains the table and column
    $dbList = $srv.Databases | select -ExpandProperty Tables |
        where {$_.name -match $searchTableName} |
        select -ExpandProperty Columns | where {$_.name -match $searchColumnName} |
        select -ExpandProperty Parent | select -ExpandProperty Parent |
        select -ExpandProperty name -unique

    # If $dbList count is greater than zero means it found a database
    if ($dbList.Count -gt 0)
    {
        #now we need to pull the location paths
        foreach ($d in $dbList)
        {
            $qry = "SELECT [Location] FROM $($searchTableName)"

            # The "-ExpandProperty" can also aid in outputting an object as just a string value
            Invoke-Sqlcmd -ServerInstance $s -Database $d -query $qry | select -ExpandProperty Location
        }
    }
}

# this variable should now contain all the UNC paths you need
$uncPaths

Now in regards to changing the path to the old location I would do something like this when I am iterating through the $uncPaths variable:

foreach ($f in $uncPaths)
{
    $oldPath = $f.Replace("Current","Old")
}


Run "USE" with linked server alternatives

You can use EXEC to run dynamic SQL in a different database or different server. Also please try to use the catalog views directly instead of all of these messy metadata functions.

DECLARE @sql NVARCHAR(MAX) = N'SELECT ''Identity property OFF''
      FROM sys.columns AS c
      INNER JOIN sys.tables AS t
      ON c.[object_id] = t.[object_id]
      WHERE c.is_identity = 0
      AND t.name = N''Table_1''
      AND c.name = N''column1'';';

EXEC mylinkedserver.testdb.sys.sp_executesql @sql;

Even better, parameterize it:

DECLARE @t SYSNAME = N'Table_1', @c SYSNAME = N'column1';

DECLARE @sql NVARCHAR(MAX) = N'SELECT ''Identity property OFF''
      FROM sys.columns AS c
      INNER JOIN sys.tables AS t
      ON c.[object_id] = t.[object_id]
      WHERE c.is_identity = 0
      AND t.name = @t
      AND c.name = @c;';

EXEC mylinkedserver.testdb.sys.sp_executesql
  @stmt = @sql,
  @params = N'@t SYSNAME, @c SYSNAME',
  @t = @t,
  @c = @c;


Mysql Select table records which have not been updated since last month

SELECT studentid FROM
(
    SELECT MAX(addedate) lastdate,studentid
    FROM scoredata GROUP BY studentid
) A
WHERE lastdate < ( DATE(NOW()) - INTERVAL 4 WEEK );

Query Breakdown

The subquery gets the last date each student entered a score
The WHERE clause outside the subquery checks to all lastdate values older than 4 weeks

Make sure you index studentid,addedate. If you didn't then run

ALTER TABLE scoredata ADD INDEX studentid_addedate_ndx (studentid,addedate);

Give it a Try !!!


I suggest taking a look at processlist while the DB is dumping. There could be locked MyISAM tables causing queue of statements waiting for locks to be released.


Time

This is really bad:

AND
to_timestamp(floor((extract('epoch' from l.time::timestamp) / 600 )) * 600) =
to_timestamp(floor((extract('epoch' from s.time::timestamp) / 600 )) * 600)

Instead, try to rewrite that in terms of l.time, ignoring epoch. I have no idea of the interval you want.

AND l.time BETWEEN s.time AND (s.time + '10 minutes');

Or whatever. If you're trying to assign arbitrary timestamps into 10 minutes intervals or whatever, there is no need to cast back to timestamp. You may consider writing your own function for convenience,

CREATE FUNCTION myIntervalExtract(ts timestamp with time zone)
RETURNS int
AS $$
  SELECT floor( extract(epoch FROM l.time::timestamp) / 600 )::int * 600;
$$ LANGUAGE sql
IMMUTABLE;

Now you can create a functional index on both myIntervalExtract(l.time) and myIntervalExtract(s.time) using the above. That will satisfy the join so long as you join

FROM l
JOIN s ON myIntervalExtract(l.time) = myIntervalExtract(s.time)

GIS

ST_Contains(ST_Buffer()..)

This is also an antipattern. You'll want to rewrite that to ST_DWithin. you should also aim not to have ST_Transform in your join conditions. Instead, consider using geography on the table.

FROM t1 JOIN t2 ON ST_DWithin(t1.polygeog, t2.geogpoint, distance_in_meters)


How can I differentiate between a Multivalued Attribute and an Entity?

Ask yourself if the thing under consideration continues to have an existence if all the other things in your model go away. If so it's an entity, if not it's an attribute.  Does the thing you are considering have a way to distinguish it from all other items in its class i.e. a primary key? Then it's an entity.

The answer you come to may depend on the context in which you are working. For one computer application a particular value may be treated as an attribute. In another it may be an entity. This is not a contradiction; it is a natural outcome of simplifying the real world into a model and in doing so selecting the important parts and excluding the others.

If you intend to implement your model you should recall that first normal form requires that each attribute hold only one value.


Grant select on tables with a specific naming pattern

There is no inbuilt method for granting permissions on tables matching a pattern. You need to grant it to the individual tables.

It would be quite easy to generate the required script with a query against sys.tables for names like 'VVC[_]%' though.

Instead of using a VVC prefix you could create a VVC schema. Then you can grant select permissions on the schema. This would also cater for tables added in the future to the same schema.


An easy way to convert SQL directly to XML, for example:

SQL> select dbms_xmlgen.getxml('select * from hr.employees where employee_id = 100')
       as xml from dual;

XML
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <EMPLOYEE_ID>100</EMPLOYEE_ID>
  <FIRST_NAME>Steven</FIRST_NAME>
  <LAST_NAME>King</LAST_NAME>
  <EMAIL>SKING</EMAIL>
  <PHONE_NUMBER>515.123.4567</PHONE_NUMBER>
  <HIRE_DATE>17-JUN-03</HIRE_DATE>
  <JOB_ID>AD_PRES</JOB_ID>
  <SALARY>24000</SALARY>
  <DEPARTMENT_ID>90</DEPARTMENT_ID>
 </ROW>
</ROWSET>

If you have a fixed format of your XML, you can describe it in XSLT and use XMLTRANSFORM to take care of the rest.