Why does changing the declared join column order introduce a sort? I have two tables with identically named, typed, and indexed key columns. One of the them has a unique clustered index, the other one has a non-unique.

The test setup

Setup script, including some realistic statistics:

DROP TABLE IF EXISTS #left;
DROP TABLE IF EXISTS #right;

CREATE TABLE #left (
    a       char(4) NOT NULL,
    b       char(2) NOT NULL,
    c       varchar(13) NOT NULL,
    d       bit NOT NULL,
    e       char(4) NOT NULL,
    f       char(25) NULL,
    g       char(25) NOT NULL,
    h       char(25) NULL
    --- and a few other columns
);

CREATE UNIQUE CLUSTERED INDEX IX ON #left (a, b, c, d, e, f, g, h)

UPDATE STATISTICS #left WITH ROWCOUNT=63800000, PAGECOUNT=186000;

CREATE TABLE #right (
    a       char(4) NOT NULL,
    b       char(2) NOT NULL,
    c       varchar(13) NOT NULL,
    d       bit NOT NULL,
    e       char(4) NOT NULL,
    f       char(25) NULL,
    g       char(25) NOT NULL,
    h       char(25) NULL
    --- and a few other columns
);

CREATE CLUSTERED INDEX IX ON #right (a, b, c, d, e, f, g, h)

UPDATE STATISTICS #right WITH ROWCOUNT=55700000, PAGECOUNT=128000;

The repro

When I join these two tables on their clustering keys, I expect a one-to-many MERGE join, like so:

SELECT *
FROM #left AS l
LEFT JOIN #right AS r ON
    l.a=r.a AND
    l.b=r.b AND
    l.c=r.c AND
    l.d=r.d AND
    l.e=r.e AND
    l.f=r.f AND
    l.g=r.g AND
    l.h=r.h
WHERE l.a='2018';

This is the query plan I want:

(Never mind the warnings, they have to do with the fake statistics.)

However, if I change the order of the columns around in the join, like so:

SELECT *
FROM #left AS l
LEFT JOIN #right AS r ON
    l.c=r.c AND     -- used to be third
    l.a=r.a AND     -- used to be first
    l.b=r.b AND     -- used to be second
    l.d=r.d AND
    l.e=r.e AND
    l.f=r.f AND
    l.g=r.g AND
    l.h=r.h
WHERE l.a='2018';

... this happens:

The Sort operator seems to order the streams according to the declared order of the join, i.e. c, a, b, d, e, f, g, h, which adds a blocking operation to my query plan.

Things I've looked at

I've tried changing the columns to NOT NULL, same results.
The original table was created with ANSI_PADDING OFF, but creating it with ANSI_PADDING ON does not affect this plan.
I tried an INNER JOIN instead of LEFT JOIN, no change.
I discovered it on a 2014 SP2 Enterprise, created a repro on a 2017 Developer (current CU).
Removing the WHERE clause on the leading index column does generate the good plan, but it kind of affects the results.. :)

Finally, we get to the question

Is this intentional?
Can I eliminate the sort without changing the query (which is vendor code, so I'd really rather not...). I can change the table and indexes.

Is this intentional?

It is by design, yes. The best public source for this assertion was unfortunately lost when Microsoft retired the Connect feedback site, obliterating many useful comments from developers on the SQL Server team.

Anyway, the current optimizer design does not actively seek to avoid unnecessary sorts per se. This is most often encountered with windowing functions and the like, but can also be seen with other operators that are sensitive to ordering, and in particular to preserved ordering between operators.

Nevertheless, the optimizer is quite good (in many cases) at avoiding unnecessary sorting, but this outcome normally occurs for reasons other than aggressively trying different ordering combinations. In that sense, it is not so much a question of 'search space' as it is of the complex interactions between orthogonal optimizer features that have been shown to increase general plan quality at acceptable cost.

For example, sorting can often be avoided simply by matching an ordering requirement (e.g. top-level ORDER BY) to an existing index. Trivially in your case that could mean adding ORDER BY l.a, l.b, l.c, l.d, l.e, l.f, l.g, l.h; but this is an over-simplification (and unacceptable because you do not want to change the query).

More generally, each memo group may be associated with required or desired properties, which may include input ordering. When there is no obvious reason to enforce a particular order (e.g. to satisfy an ORDER BY, or to ensure correct results from an order-sensitive physical operator), there is an element of 'luck' involved. I wrote more about the specifics of that as it pertains to merge join (in union or join mode) in Avoiding Sorts with Merge Join Concatenation. Much of that goes beyond the supported surface area of the product, so treat it as informational, and subject to change.

In your particular case, yes, you may adjust the indexing as jadarnel27 suggests to avoid the sorts; though there is little reason to actually prefer a merge join here. You could also hint a choice between hash or loop physical join with OPTION(HASH JOIN, LOOP JOIN) using a Plan Guide without changing the query, depending on your knowledge of the data, and the trade-off between best, worst, and average-case performance.

Finally, as a curiosity, note that the sorts can be avoided with a simple ORDER BY l.b, at the cost of a potentially less efficient many-to-many merge join on b alone, with a complex residual. I mention this mostly as an illustration of the interaction between optimizer features I mentioned previously, and the way top-level requirements can propagate.


Limit results to the first 2 ranking rows In SQL Server 2008, I am using RANK() OVER (PARTITION BY Col2 ORDER BY Col3 DESC) to return data set with RANK. But I have hundreds of records for each partition, so I will get values from rank 1, 2, 3......999. But I want only up to 2 RANKs in each PARTITION.

Example:

ID   Name    Score    Subject
1    Joe      100      Math
2    Jim      99       Math
3    Tim      98       Math
4    Joe      99       History
5    Jim      100      History
6    Tim      89       History
7    Joe      80       Geography
8    Tim      100      Geography
9    Jim      99       Geography

I want the result to be:

SELECT Subject, Name, RANK() OVER (PARTITION BY Subject ORDER BY Score DESC)
FROM Table

Subject        Name      Rank
Math           Joe        1
Math           Jim        2
History        Jim        1
History        Joe        2
Geography      Tim        1
Geography      Jim        2

I want only rank 1 and 2 in each category. How do I do this?

select * from (
SELECT Subject, Name, RANK() OVER (PARTITION BY Subject ORDER BY Score DESC) as RN
FROM Table
) a
where a.RN <= 2


Does MySQL have a more private and complete catalog other then INFORMATION_SCHEMA? Most databases provide the standardized INFORMATION_SCHEMA, however most also provide something else,

In PostgreSQL we have the system catalog which is pg_catalog
In SQL Server, you have the System Base Tables

Does MySQL expose anything other than INFORMATION_SCHEMA? Specifically, the question was inspired by trying to find if there is a table that is responsible for internal function resolution, like pg_catalog.pg_proc which tells me for instance all the GIS functions my database has. If that was in MySQL I could address this comment. But, that aside, I just want to know if there anything more than INFORMATION_SCHEMA. That is the question.

It funny you asked this. Over 7 years ago (Jul 11, 2011), I answered the post What is the point of the TABLE_CATALOG column in INFORMATION_SCHEMA.TABLES?.

All I mention there is MySQL trying to be SQL-92 compatible, while PostgreSQL does make actual use of the information_schema (it being localized to whatever database you connected, or as I say in the post "The collapse is automatic").

Due to its open storage engine infrastructure, MySQL's exposure of functions allows for installing your own. For example, in the docs you have this:

mysql> CREATE FUNCTION metaphon RETURNS STRING SONAME 'udf_example.so';
mysql> CREATE FUNCTION myfunc_double RETURNS REAL SONAME 'udf_example.so';
mysql> CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME 'udf_example.so';
mysql> CREATE FUNCTION sequence RETURNS INTEGER SONAME 'udf_example.so';
mysql> CREATE FUNCTION lookup RETURNS STRING SONAME 'udf_example.so';
mysql> CREATE FUNCTION reverse_lookup
    ->        RETURNS STRING SONAME 'udf_example.so';
mysql> CREATE AGGREGATE FUNCTION avgcost
    ->        RETURNS REAL SONAME 'udf_example.so';

I have seen statements like these when installing Percona Server, and Percona supplies three commands to run (like these) to expose parts of Percona Toolkit.

Anyway, going back to your actual question: INFORMATION_SCHEMA gives you nothing than what you already know. Everything else is pluggable.


Out of range errors while with insert statements I'm having an issue regarding every third insert line of the database I'm trying to create.

Some background, I have a huge .JSON file and used software (sqlizer) to turn it into input statements, however I'm having an issue as you can see here:

Every third statement or so, I run into a range error, that I'm not sure why is happening since I didn't set a range.

Here is a sample from my insert statements:
http://pastebin.com/j7A4ZJV4

Does anyone know what I can do to fix this issue? Keep in mind I'm very new to MySQL so forgive me if I've done something tremendously dumb.

Thank you

The values you're trying to insert are above the max allowed for a signed INT column in MySQL:

3088727691 > 2147483647

You can either switch it to an unsigned INT to make the max value 4,294,967,295 or a signed BIGINT to give you the room to hit up to 9,223,372,036,854,775,807.


update json value to null As I understand it, if you select json_value or json_query specifying a key or object that does not exist in your json document, then in strict mode, you will receive an error. This allows you, for example, to confirm whether the key was specified in the document.

If an example key-value pair is included in the document thus:

"Test":null

... this will, in strict mode, return 'NULL'. In other words, you now know the key is defined in the document and its value is null.

Now imagine your document contains:

 "Test":"Some string"

And you query:

 select json_modify(@json, '$.Test', null);

This will return a json string lacking the "Test" key.

How do you properly set a key value to null, as opposed to removing it from the json document?

(The above example may seem nonsensical, but I can imagine setting a value to null before passing the json document on to the next query or system, and requiring that your "Test" key exist in the document.)


Syntax shorthand for updating only changed rows in UPSERT I have a query which upserts into Postgres when a key conflicts only if there are changes to one of the other columns (to avoid unnecessary inserts and returned rows where nothing was actually changed):
INSERT INTO public.test_upsert (some_id, a, b, note)
VALUES
  ('c', 1, true, 'asdf')
ON CONFLICT (some_id)
DO UPDATE SET
  a = excluded.a,
  b = excluded.b,
  note = excluded.note
WHERE
  test_upsert.a IS DISTINCT FROM excluded.a OR
  test_upsert.b IS DISTINCT FROM excluded.b OR
  test_upsert.note IS DISTINCT FROM excluded.note
RETURNING *;

My question is: is there shorthand for this? I basically want to either insert a new record update the rows when any of the columns being inserted are distinct from the existing columns. Writing out each column individually gets quite verbose, although it is more explicit.

There is no provision in SQL to say "all columns except this one". (You can do something like that with jsonb or hstore.) Related answer with details:

Select columns inside json_agg

On second thought, you don't need this. You might as well update all columns. Postgres writes a new row version anyway, nothing lost.
INSERT INTO test_upsert AS t (some_id, a, b, note)  -- list is optional
VALUES ('c', 5, true, 'asdf')
ON     CONFLICT (some_id)
DO     UPDATE
SET    (some_id, a, b, note) = ROW (excluded.*)  -- ROW syntax
WHERE  (t.*) IS DISTINCT FROM (excluded.*)       -- again, compare whole row
RETURNING *;

You still have to spell out the column list once for the target list of SET. That's required. The rest can be shortened.
You might even omit the target list for the INSERT while targeting all columns anyway, but in most cases it's good form to spell it out, more robust against future changes.
The table alias is an optional additional syntax shorthand. Note that the AS key word is required for a table alias for the target of an INSERT - unlike most places for a table alias.

The manual on INSERT.

Or even shorter, yet:
...
WHERE  t IS DISTINCT FROM excluded
...

We can as well just use the table name (or alias) for the (well-known!) composite type. The only corner-case disadvantage of this: if there is also a column of the same name "t" (or "excluded"), it takes precedence due to Postgres syntax rules. (t.*) IS DISTINCT FROM (excluded.*) cannot be mistaken and is the slightly safer form. Related answer:

Convert a row to a nested JSON document containing all relations


Should I create a maintenance plan for my database or use jobs? I tried creating a Maintenance Plan on my SQL Server 2008 database and was impressed by some of the things that are available out of the box.  However, after it was created, it seemed difficult to customize.  This left me wondering if I should have just created SQL jobs with the activities I wanted done.  Are there any benefits to using the maintenance plan over manually creating SQL jobs?

I can't think of any benefits, but I am also biased towards using my own jobs for maintenance.

I think that MPs are very good for new or accidental DBAs, as they allow for some maintenance activities to be done (which is better than having none at all). But as most DBAs progress in their experience they tend to start developing their own custom scripts instead.

HTH


What sort of database setup is used for an App Store or Google Play Store? I'm trying to build a desktop App Store, and it's just a small project for demonstration purposes, I'll be using java to create the application. In essence it's just a mock App Store that stores information about customer & applications. Note: We will only be working with sample data, so no new data will be added.

Now there is the option to use the JDBC but we're a team of four people, and there is a question of how other team members will have access to the data.

The second option I have in my mind is to connect to an online MySQL database, which would require installing the MySQL connector.

There is the option of using an embedded database, so the database will move with the application, in a sense, at least that's what I think.

I'm completely new to the database scenario, and I would really much appreciate any rational guidance for what database setup I should consider.

Many Thanks

If it's just for a demonstration and won't be deployed to any users, and embedded database, for example SQLite, is a good choice. Not that there's anything wrong with running a local or shared instance of PostgreSQL or MySQL, but it will be marginally more work to set up.


RID vs INCLUDE on a large field I have a table that stores notes

create tblNote(
  Id int identity(1,1),
  ParentId  int ,
  ParentType varchar(32),
  NoteType varchar(32),
  Note varchar(max),
  CreatedBy varchar(25),
  CreatedDate  datetime,
  .
  .
  .
  <other metadata about the note>
)

I have done a lot of reading recently about how MSSS handles indexes (2005 and forward).

I have a clustered index on ID

[ I have considered changing the clustered index to parentId, parentType since that is reasonably narrow and it is static. ]

The overwhelming percentage of queries against this table are going to be along the lines of

select NOTE, createdDate, createdBy
from tblNote
where parentId = 12 and parentType = 'RFQ'

The question I want to ask today (though any feedback is welcome) is this:

The NC index I could add is:

create index  idx_nc_note_parent(
        parentId ,
        parenttype
    )
    include (createdby, createdDate)

This would be useful in creating little lists of the notes where we might include who and when type info.

I am hesitant to include a varchar(max) field.  It seems like it would really hurt the amount of the index that would be cached  (Is this reasonable or unreasonable)

Assuming I dont include the NOTE field, a RID Lookup will be necessary to actually fetch the note content if it is requested.

While I have read quite a bit about how expensive RID lookups are, it still has to be better to have this index as opposed to doing a table scan, RIGHT?

[apologies for the code block, i have added the 4 spaces, but maybe i did it wrong? ]

Since you said that most queries would generally return few rows, letting the query use a RID lookup (key lookup in this case as the table has a clustered index) is perfectly fine for retrieving a potentially large field. For a highly-available system, I couldn't recommend putting a LOB type in an index anyway as this prevents online rebuilds (for versions of SQL Server before 2012). Also, you need to be very careful that the query plan always sticks to a seek-type plan and not tip into a table scan which could be very expensive. This is a case where I might use a table hint (or a plan guide if the query can't be modified) even if it isn't absolutely necessary.

Another option is to recreate the clustered index on the combination of parentId and parentType if this combination of values is static and generally increasing over time. It would be better if parentType was an integral type, though, and you may want to look into changing that anyway to save storage space if the base table is, or will become, large. Considering this change also involves looking at how it might affect indexing for the other classes of queries that run against this table.

If either of those two methods aren't fast enough for the workload, look into implementing a data caching solution using something like AppFabric, which scales much more readily than running a SQL query every time you need data. This can be a huge payoff; the cost is added complexity.


How can Amazon RDS grant CREATE EXTENSION to their RDS_SUPERUSER role? I've been trying for some time to allow ordinary, non-superuser users to create extensions.
While reading across RDS documentation, I find that their internal "master user", rds_superuser, is capable of creating extensions, even though it is not an actual superuser, as is well documented.
Further searching leads me to this table:
|Database Engine|System privilege|Database role|
|---------------|----------------|-------------|
|PostgreSQL     |`CREATE ROLE, CREATE DB, PASSWORD VALID UNTIL INFINITY, CREATE EXTENSION, ALTER EXTENSION, DROP EXTENSION, CREATE TABLESPACE, ALTER < OBJECT> OWNER, CHECKPOINT, PG_CANCEL_BACKEND(), PG_TERMINATE_BACKEND(), SELECT PG_STAT_REPLICATION, EXECUTE PG_STAT_STATEMENTS_RESET(), OWN POSTGRES_FDW_HANDLER(), OWN POSTGRES_FDW_VALIDATOR(), OWN POSTGRES_FDW, EXECUTE PG_BUFFERCACHE_PAGES(), SELECT PG_BUFFERCACHE`|RDS_SUPERUSER

When I try executing grant create extension to any user, I get a syntax error.
How is this grant possible?

CREATE EXTENSION is not a grantable privilege. It is hard-coded who can create extensions.
In PostgreSQL, this is restricted to superusers, and from v13 on to users with the CREATE privileges on the database (if the extension is a "trusted" one).
Note, however, that RDS is different from PostgreSQL, and godJeff only knows how they hacked up the code.


What's the difference between fetch and fetch next? I've got a cursor sending out pager messages, and occasionally it sends out duplicates. The syntax looks like this:

    declare @col1 varchar(255),
    @col2 varchar(255)

    declare c cursor local for

    select column1, column2
    from table
    where flippity = 'floppity'

    open c
    fetch  from c into @col1, @col2
    While @@FETCH_STATUS = 0
    begin

    sp_do_your_thing @col1, @col2

    fetch next from c into @col1, @col2
    end
    close c
    deallocate c

Question: if I'm getting duplicates, could it be the fetches? do they both need to be fetch next, or neither?

There is no difference, and omitting NEXT will not lead to duplicates or different results in any way. NEXT is just an explicit declaration of the default behavior. From the syntax diagram:

FETCH
      [ [ NEXT | PRIOR | FIRST | LAST
                | ABSOLUTE { n | @nvar }
                | RELATIVE { n | @nvar }
           ]
           FROM

NEXT is not the only option, and most of the other options are only available with certain types of cursors. It's hard for me to get into this habit, because I exclusively use fast-forward, single-pass, "firehose" cursors, but best practice is to include NEXT explicitly.

I would look at the query, as I suspect it will also be returning duplicates when there is no cursor involved. Or, whatever the stored procedure is doing, is making it seem like there are duplicates. Another possible explanation is if the query is using WITH (NOLOCK) / READUNCOMMITTED and you are reading the same row twice in an allocation order scan due to data movement.


Update Sql Server from Mysql We have a website that stores form data in a web hosted MySQL database. Whenever form data is entered into the MySQL server tables I need update all necessary tables on our local SQL Server database.

I have seen several articles that describe how to update MySQL using SQL Server triggers but nothing that goes the other way.

If I create a linked server and linked tables in SQL Server can I create a trigger on a MySQL table from the SQL Server management studio?

You can't "see" the SQL Server from within MySQL like you can in the reverse direction with a linked server.

The only way I can think of doing this is to create a MySQL trigger that calls a User Defined Function that executes an external application and passes the new data to it. The external application would have to connect to the SQL Server database and push the data to it.

DELIMITER @@

CREATE TRIGGER Test_Trigger
AFTER INSERT ON MyTable
FOR EACH ROW
BEGIN

DECLARE cmd CHAR(255);
DECLARE result int(10);
SET cmd=CONCAT('/usr/bin/php ', 'home/sync/send_to_sql_server.php f1=', NEW.f1, ' f2=', NEW.f2);
SET result = sys_exec(cmd);

END;
@@
DELIMITER ;


Schema check or "DATE as separate table" I'm dusting of my SQL, and would love to know if I've got my mental ER-diagram right.

http://sqlfiddle.com/#!2/c5c59

Basically, I've got 10-15 different datasets (actividades) from several locations (islas) over the span of a few months. My first instinct is to separate the many-to-many relationship between Islas and Actividades into an intermediary table, then reference(/index?) each dataset by location.

If this is the correct approach, I'm then wondering whether there is any benefit to have a separate date table (I guess this would be kind of like a calender). ie: the join between location, date and dataset would give one result, querying a date and location would give a list of all the activities that day and querying a single date would give all datasets from all locations.

Or should I just datestamp each datapoint (of each dataset) and deal with this in my queries.

I hope that was legible, thanks for your help!

It would have been great if your sqlfiddle data would have been in English...

If the activities aer bound to a single location, you could use your current approach.
But if there's a chance that one activity can be referenced to more than one location you should use a separate table to show such a reference (using foreign keys to the primary keys of your activity and location tables).

A calendar table usually is very helpful if you'll need to return any results for a consecutive date range including date values without any corresponding values (e.g. activities).


SQL Server - "The connection must be in an opened state." I run various queries against a SQL Server 2012 instance, and occasionally, the following error message will pop up:

  The connection must be in an opened state.
  Parameter name: connection (SQLEditors)

Although it usually happens while I've left that particular tab idle in SSMS for a while (~10-45 minutes), it also sometimes happen in between running queries nearly back-to-back. The connection state at the bottom will always say "Connected" before I run the query (and obviously immediately change to "Disconnected" when that error shows up).

The database's AutoClose option is disabled, and I also have the "Disconnect after the query executes" option disabled locally. There aren't any scheduled jobs I can find that might be routinely disconnecting queries. Help? Ideas?

This is a very interesting error. Please note there is no error code of any kind associated with it. This means it has to do with SSMS rather than SQL Server.

And this specific error is telling you that the SSMS no longer has active connection to the server. It is possible that this might be the case of faulty switch or similar, but I doubt it.

My recommendation is to check settings pertaining to power saving options of the computer you use in general or your NIC in particular.

In other words - Windows powers off you network adapter while on idle to save power. Might want to check adaper settings as well - my had all sorts of options like "EEE" or "Green Gigabit" enabled...

Addendum

Having similar issues lately when working via wireless connection we - my IT department and I - tracked the issue down to wireless adapter on my laptop and speculated that reason might be some undocumented or inaccessible setting linked to power consumption. It improved only after relentlessly updating drivers to latest possible version using both Intel and Dell updating tools.

Until then same symptoms: connection says it's live, but running queries randomly generated above error. Windows says connection is live, SSMS concurs, no - literally: zero - entries in Windows logs even remotely touching on networking. All is well when using wired LAN.


Invalid column name 'database_id' I loaded the stored procedure into master and ran:

dbo.sp_BlitzFirst @ExpertMode = 1

I got:

Setting up configuration variables
Msg 207, Level 16, State 1, Line 5
Invalid column name 'database_id'.
Now starting diagnostic analysis
Capturing first pass of wait stats, perfmon counters, file stats
Beginning investigatory queries
Finished running investigatory queries
Waiting to match @Seconds parameter
Capturing second pass of wait stats, perfmon counters, file stats
Analyzing changes between first and second passes of DMVs
Analysis finished, outputting results
Msg 207, Level 16, State 1, Line 5
Invalid column name 'database_id'.

kit version: SQL-Server-First-Responder-Kit-20161210

Microsoft SQL Server Enterprise Edition (64-bit) version 10.50.6000.34

Here are the two stored procedures that need to be updated.

sp_BlitzWho.sql and

sp_BlitzFirst.sql


What is the syntax to sp_recompile logon trigger? I would like to recompile my logon trigger using sp_recompile. But I can't figure out the syntax.

E.g. these don't work:

sp_recompile '[Tr_Logon]'

sp_recompile '[Tr_Logon]' ON ALL SERVER ;)

I think I found it:

sp_refreshsqlmodule (Books Online)

  Updates the metadata for the specified non-schema-bound stored procedure, user-defined function, view, DML trigger, database-level DDL trigger, or server-level DDL trigger in the current database.

EXEC sys.sp_refreshsqlmodule
    @name = 'ddl_trig_name',
    @namespace = 'SERVER_DDL_TRIGGER';


"Pausing" log shipping to allow normal DB use. Is this process valid? I am in the process of migrating a server to AWS.  Right now it is just for Dev/QA work, but will eventually be turned into our production server.  It is a 2 node AG.  Server is all up and running, but our data is over 2 months old, and our team would like to periodically (every 5 days) get new data into the DB from the existing Prod DB, and I have been asked to use log shipping to do so.  The database needs to be usable, so keeping log shipping intact isn't an option since it will be read only or no recovery.  We need to test how it works for eventually populating the nodes for go live, so 2 birds with one stone so to speak.  The DB is 1.2TB, so transferring it over the NW to do a backup and restore normally is a bit time consuming.  Would the below work, or does anyone have a better way to do this?

remove the DB from the AG to allow it to be added as a secondary LS destination(i have not found info that an AG primary can be used as a LS secondary.)
Restore the most recent backup of prod DB to the destination server, leaving it in restoring mode.
Add the DB as a secondary and let the LS copy and restore jobs bring it up to sync.  choose the no recovery option on the DB.
once the DB is synced, disable the restore job and bring the database online using restore DB with recovery command.
add DB back to the AG so they can test everything for the next 5 days.

So five days later, I would need to copy over a new backup since we only keep 7 days of log backups in order to have all the log backups needed to get new data.  Is there a way to disable the log backup cleanup for a specific secondary?  I don't want to bloat the live prod server with weeks log log backups...

Would this process work, or does anyone have a different way to do this?

Once you bring the log shipped database online you would have to restore a new full backup to restart log shipping, regardless of how long it has been. This is because you've recovered the database and cannot then restore log backups to that database.

There is no way to incrementally refresh the Dev/QA database from production with performing a full backup restore, except possibly there are third-party tools that can do this for you. You're better off working on a solution to automate the refresh of your Dev/QA environment and have this process run outside normal operating hours to reduce the impact on your team.


Maximum possible memory usage: 16.2G (874% of installed RAM) Why the MySQL is saying that can use about 16GB of RAM even if my server has only about 2GB?

# ./mysqltuner.pl

 >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.34-cll
[OK] Operating on 32-bit architecture with less than 2GB RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 457M (Tables: 1361)
[--] Data in MRG_MYISAM tables: 53M (Tables: 36)
[--] Data in InnoDB tables: 792M (Tables: 1133)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 0B (Tables: 2)
[!!] Total fragmented tables: 175

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 54s (2K q [37.537 qps], 325 conn, TX: 1M, RX: 354K)
[--] Reads / Writes: 90% / 10%
[--] Total buffers: 1.7G global + 18.6M per thread (800 max threads)
[!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
[!!] Maximum possible memory usage: 16.2G (874% of installed RAM)
[OK] Slow queries: 0% (9/2K)
[OK] Highest usage of available connections: 1% (13/800)
[OK] Key buffer size / total MyISAM indexes: 128.0M/78.9M
[!!] Key buffer hit rate: 73.9% (1K cached / 297 reads)
[OK] Query cache efficiency: 42.4% (388 cached / 915 selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 148 sorts)
[OK] Temporary tables created on disk: 21% (114 on disk / 537 total)
[OK] Thread cache hit rate: 94% (17 created / 325 connections)
[OK] Table cache hit rate: 99% (2K open / 2K opened)
[OK] Open file limit used: 1% (2K/200K)
[OK] Table locks acquired immediately: 100% (1K immediate / 1K locks)
[OK] InnoDB data size / buffer pool: 792.7M/1.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries

And buffer related settings

mysql> SHOW VARIABLES LIKE '%buffer%';
+------------------------------+------------+
| Variable_name                | Value      |
+------------------------------+------------+
| bulk_insert_buffer_size      | 8388608    |
| innodb_buffer_pool_instances | 1          |
| innodb_buffer_pool_size      | 1073741824 |
| innodb_change_buffering      | all        |
| innodb_log_buffer_size       | 8388608    |
| join_buffer_size             | 16777216   |
| key_buffer_size              | 134217728  |
| myisam_sort_buffer_size      | 8388608    |
| net_buffer_length            | 16384      |
| preload_buffer_size          | 32768      |
| read_buffer_size             | 131072     |
| read_rnd_buffer_size         | 262144     |
| sort_buffer_size             | 2097152    |
| sql_buffer_result            | OFF        |
+------------------------------+------------+
14 rows in set (0.00 sec)

Staring at the mysqltuner output, I see something disturbing
[--] Total buffers: 1.7G global + 18.6M per thread (800 max threads)

The 800 is the max_connections.
The 18M is a sum that includes read_buffer_size + sort_buffer_size + join_buffer_size

What is 800 * 18M ?
mysql> select 18 * 1024 * 1024 * 800 / power(1024,3);
+----------------------------------------+
| 18 * 1024 * 1024 * 800 / power(1024,3) |
+----------------------------------------+
|                                14.0625 |
+----------------------------------------+
1 row in set (0.00 sec)

That's 14G your DB Server cannot handle.
RECOMMENDATION
Add this to my.cnf
[mysqld]
sort_buffer_size = 1M
join_buffer_size = 1M
read_buffer_size = 1M
max_connections = 100

then restart mysql and run mysqltuner.pl again. You will see lower numbers.
Give it a Try !!!


what is the correct name for "rich" views? I was reading about this the other day that with a good DB you can create a view of data, that is automatically updated and cached

say you have:

time user was online
this is measured with 2 datetimes, start and end date
many entries
what is the total time?

this would be an example of a "rich" view

a view that selects all entries, and adds up the dates, and provides them in a view

when a value is updated, automatically the view is updated

now I don't know the word for it.

What is the proper term for it?

I know mysql does not support it (at least that is what I think)

Materialized view.

Overview of Materialized Views

Materialized Views


Select rows from table having specific values depending on another values in the same table I need to be able to select all Userpin that have tr_type 7 only but if there is tr_type 7 and 8 in other row ignore  7 and 8 example:

Userpin  TrDate       Time1      Tr_type
4555     12-02-2020    7:30         7
4555     12-02-2020    14:30        8
6985     12-02-2020    7:34         7
9854     12-02-2020    7:15         7
8542     12-02-2020    7:16         7
8542     12-02-2020    14:40        8

Using this data set I am looking for Query To Get
that data with userpin that have only tr_type equal 7 in current day
 and i want the result like that  :

Userpin  TrDate       Time1      Tr_type
6985     12-02-2020    7:34         7
9854     12-02-2020    7:15         7

i used that query to get data in current day

select UserPIN ,TrDate,Time1,Tr_Type
from mytable
where UserPIN in (6985,9854,4555,8542,4062,7070,4189,4197)
and TrDate=CAST(GETDATE() AS DATE)

This seems like a simple thing to query, but I am finding myself up against a wall. What's the best way to accomplish this in a reasonably efficient way?

SELECT t1.*
FROM mytable t1
WHERE t1.Tr_type = 7
  /* AND another conditions by Userpin list, TrDate, etc. */
  AND NOT EXISTS ( SELECT NULL
                   FROM mytable t2
                   WHERE t1.Userpin = t2.Userpin
                     /* AND another conditions */
                     AND t2.Tr_type = 8 )


OS killed some of databse demons I have 2 HP servers with CntOS 6 as OS and DB2 10.5 as Database. there 2 servers are HADR. standby server suddenly rebooted and when i checked log it said that OS had killed 4 db2 demons.

IBM.ConfigRM
ctcasd
RMCD_INFO_1_ST
GS_STOP_ST

why this happens? why OS killed these demons?
there is no explaination in diag or other log file to be checked.

This might happen due to exhausted virtual memory on your server. OOM killer kills processes to free some memory in this case.

You can try to collect some information next time when it happens again with db2fodc -detect functionality using either predefined script or your own.

See the Threshold collection examples at the db2fodc - DB2 first occurrence data collection command link.


Execution Plan : Table Insert Cost 90% vs Clustured Index Insert Cost 86% Situation : We have table It has millions of records. Table has 1 Clustured Index and 1 Non Clustured Index. It was found that Clustured Index is not used by any of SQL Query. But Insert Execution plan has 86% cost on Clustured Index.

Action: If I convert Clustured Index to Non Clustured Index, Execution plan gives me Table Cost 90%.

Which one is better for performance, If I convert clustered index to non clustered index, will it be helpful.

Thanks, Virul

The decision about whether to use a heap or a clustered index for your underlying table structure is a lot more complicated than the speed of insertion, although that certainly can be a factor.

When you insert a new row, it is typically going to be quicker to get the row into the heap, but there are a lot of things going on that can affect both the estimate and the actual cost.

In your case, it sounds like you have additional indexes in play, and the maintenance of these would be weighing into those estimates. If you look in the Properties (hit F4), you will see the list of objects affected by Insert, and if you have made a non-clustered index instead of your clustered one, you are probably doing more work in your Table Scan, as there is one extra object to maintain.

If performance isn't actually a problem, I'd suggest leaving things as they are. If you're wanting to tune the pants off it, you're already getting deeper than you're likely to get in a forum like this. Heaps can be good, but they can also be awful. Clustered Indexes have drawbacks too, so your decision is a lot more complicated.

As to why it's higher - check the object list.


Comments in EXPDB parameter file Is there a way to put comments in a data pump export parameter file?

Yes, by using # at the start of a line.

Actually everything after the # sign will be ignored, so this can be used after a parameter as well.

This is a bit hidden in the manual:

  Table names specified on the command line cannot include a pound sign (#), unless the table name is enclosed in quotation marks. Similarly, in the parameter file, if a table name includes a pound sign (#), then the Export utility interprets the rest of the line as a comment, unless the table name is enclosed in quotation marks

Quoted from:  http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_export.htm#sthref137


Is it imperative that I backup my index files? I have a few different instances (SQL 2000, 2005) with indexes placed in a separate filegroup. Do i need to ensure that this group is backed up, or will it be rebuilt by a restore of of the database?

The first point to consider that a clustered index is actual table data.  So in that alone, I'd say yes.  Your whole database should be backed up and able to restore (there are a few exceptions to that, like read only filegroups that are subject to no DML).

The second point is that restoring a database does not rebuild or reorganize any indexes.

So yes, you should be backing up all of your data, indexes included.


SQL Server Nested views, how deep is too much? I stumbled on a problem using views. The content management system my program interfaces with, defines a view which calls another view on the system. That is 2 levels to begin with. Before I stumble on views, I thought nested views were casual, but after my code blew up and i googled around, i begin to pick up words like "sin" and "vulgar" and "bad" which are associated with nested views.
Well I have two-level nested views to begin with. In my application level, I define my 3rd level and that's when it gives me time out error. Is 3 level too much? The tables are not terribly big -- between 5,000 and 100,000 records, while my view itself only produces 2,000 record. Is this suppose to break the camel's back?

It's not the number of levels of views that is the problem, it is the complexity of the resulting query.  Having a lot of nested views may contribute to the complexity of the query but a great deal depends on what those views are actually doing.
Having several layers of nested views often creates problems when it means that you are doing unnecessary or duplicative work.  If an underlying view, for example, joins tables A, B, and C when you only need information from A and B, the join to C means that the database may have to do unnecessary work (depending on the database engine and the exact definition, the optimizer may be able to work out that the join to C is unnecessary and eliminate it but this doesn't usually happen).  If a first level view joins A and B and then a second level view joins to B again because it wants to add some additional columns from B, you've now forced the database to do duplicate work and join to B twice.  The same thing happens when you have predicates in different levels of views that force the database to do work that it doesn't have to do.
Broadly, if you have a well-designed series of views where each level has some defined responsibility, you're much more likely to have reasonable performance than if you have an incoherent set of views that have grown up organically over time with different developers adding things to different views to meet that day's requirements.  Imagine you have a bug tracking system with the following objects

A bug table with all issues,
A vw_bug view on top of that that acts as an abstraction layer in case someone wants to refactor the bug table
A vw_current_bug view on top of vw_bug that that filters out soft-deleted rows
A vw_open_bug view that filters down to open bugs
A vw_bug_overview view that joins vw_open_bug to all the lookup tables to present information for the overview page

That's four layers of views with each one providing a well-defined abstraction layer.  That's likely to be OK from a performance standpoint.
Having several levels of views can also create problems for the database when it makes it difficult or impossible for the optimizer to figure out the best place to apply a predicate (Google "predicate pushing" for tons of additional information on this).  If you   have a number of different levels of views but you always specify a where table_a_primary_key = :1 predicate when you query the outer view, the optimizer has to figure out when to apply that predicate.  If table_a is the driving table in the inner view, ideally the optimizer figures out that it wants to push that predicate down through all the layers of views and apply it to that driving table before it does the work of the joins.  Depending on the database engine, version, and the complexity of the code in your views, however, it may be difficult or impossible for the optimizer to figure out it wants to push the predicate or to push it there without theoretically changing the results.  Different database engines and different versions of those engines will have very different behaviors in this respect.
Broadly speaking, this is no different than the challenges you'd have when you have a single view that happens to implement relatively complex logic.  Different database engines have different ways to let you circumvent these sorts of issues-- Oracle hints can force the optimizer to push a predicate, Oracle lets you define a pipeline table function that acts like a view but lets you pass in a parameter that you apply where you want, SQL Server table functions act in much the same way, etc.
Those aren't the only two potential issues with having a bunch of levels of views but those are the two big ones in my experience.  As with any query, the more complex it gets, the more difficult it is for the optimizer to exhaustively search every possible query plan and the more likely it is that you're going to end up with a less optimal plan.  The more complex the query, the more likely you're going to hit a corner case where the optimizer can't correctly work out the cardinality of various steps because it doesn't know how to combine the statistics correctly.
On the other hand, a well-designed set of views can provide useful abstractions that make it easier to define queries correctly.  In the bug tracking system from earlier, vw_current_bug provides a useful abstraction layer so that you don't have to filter out soft-deleted rows in hundreds of queries.  There may be cases where this abstraction layer has a performance cost but that may be a small cost that you're happy to pay to avoid duplicating code.


Table Partition Function Empty Partitions I plan to partition my table monthly along a date column, and have read at few places, that it is recommended (in fact a best practice) to keep empty partitions at both "end" of the range.
https://techcommunity.microsoft.com/t5/premier-field-engineering/oops-i-forgot-to-leave-an-empty-sql-table-partition-how-can-i/ba-p/370563

As a naive approach I would create my partition function like this:

CREATE PARTITION FUNCTION MyPf(DATE)
AS RANGE RIGHT FOR VALUES (
'1900-01-01',
'2019-10-01','2019-11-01',...,...,...,'2022-08-01',
'9999-12-31');

CREATE PARTITION SCHEME MyPs AS PARTITION MyPf
ALL TO (MySingleFileGroup)

I can guarantee that no data older than 2019-10-01 will be inserted to the table, and I plan to keep SPLIT partitions once I reached 2022-08-01, and keep doing so up until 9999-12-31.
I also plan to regularly TRUNCATE old partitions and MERGE the old partitions range.

Did I miss anything obvious regarding the best practice with this setup? My only goal is to be able to split and merge without moving data around.

Thank you!

I can guarantee that no data older than 2019-10-01 will be inserted to
  the table, and I plan to keep SPLIT partitions when I am reaching
  2022-08-01

As long as no existing data qualifies for the new partition boundary range, no data movement is required for the SPLIT.

  I also plan to regularly TRUNCATE old partitions and MERGE the old
  partitions range.

Similarly, the merged partition will be empty due to the preceding TRUNCATE so no rows will need be moved to accommodate the new boundaries.


Restore All Databases Script I am migrating SQL Server DBs to a new instance.

I have been told that it is possible to dynamically build a RESTORE script from available backups in the system catalog.

Does anyone know of a sample script to do this?

Thanks!

I, personally, use sp_restorecriptgenie by Paul Brewer. Here's the link to the SCC article that has it. Under 'The Procedure'

http://www.sqlservercentral.com/articles/Restore+database/95839/

Basically, once you build the stored procedure, just run exec sp_restorescriptgenie and the script will go out and get all backups for every DB (even transaction logs) and give you a script to generate them. Also generates the DBCC CHECKDB commands to check the DBs after restoring.

I actually just used this script about an hour ago so I can do a test restore and verify my backups.


Database design decision help I currently have a database design that I have been looking at and I am wondering if its the best one.

I have the following tables:

Each request has a current status. I need to keep track of all the status changes. RequestStatus currently keeps all these records, with the current record being marked as being the current one (to remove the need to find the one with the most recent date).

Looking at it I am beginning to think that I should have a StatusID column on Request changing the relationships to:

I know this breaks proper normalisation but what I really need is just an audit trail or status changes which is only ever accessed via Request - I will never need to access RequestStatus via Status.

Thoughts?

UPDATE:

There will be 6 statuses total: Pending, Approved, Rejected, Cancellation Requested, Cancellation Approved and Cancellation Rejected.

Most Requests will have 1 or 2 statuses total with the majority being 2 e.g. Pending then either Approved or Rejected or straight to approved (for automatically approved requests).

If I were to do this, I'd use SQL Server, with 3 tables.

USE tempdb; /* this is a test-bed */

IF COALESCE(OBJECT_ID('dbo.RequestStatusHistory'), 0) <> 0
BEGIN
    DROP TABLE dbo.RequestStatusHistory;
END
IF COALESCE(OBJECT_ID('dbo.Requests'), 0) <> 0
BEGIN
    DROP TABLE dbo.Requests;
END
IF COALESCE(OBJECT_ID('dbo.Statii'), 0) <> 0
BEGIN
    DROP TABLE dbo.Statii;
END

CREATE TABLE dbo.Statii
(
    StatusID INT NOT NULL
        CONSTRAINT PK_Statii
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , StatusDescription VARCHAR(50)
);

INSERT INTO dbo.Statii(StatusDescription)
VALUES ('Pending')
    , ('Approved')
    , ('Rejected')
    , ('Cancellation Requested')
    , ('Cancellation Approved')
    , ('Cancellation Rejected');

CREATE TABLE dbo.Requests
(
    RequestID INT NOT NULL
        CONSTRAINT PK_Requests
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , RequestName VARCHAR(30) NOT NULL
    , CurrentStatusID INT NOT NULL
        CONSTRAINT FK_Requests
        FOREIGN KEY
        REFERENCES dbo.Statii(StatusID)
    , CurrentStatusDate DATETIME NOT NULL
        CONSTRAINT DF_Requests_CurrentStatusDate
        DEFAULT (GETDATE())
);

CREATE TABLE dbo.RequestStatusHistory
(
    RequestStatusHistoryID INT NOT NULL
        CONSTRAINT PK_RequestStatusHistory
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , RequestID INT NOT NULL
    , StatusID INT NOT NULL
    , StatusDate DATETIME NOT NULL
        CONSTRAINT DF_RequestStatusHistory_StatusDate
        DEFAULT (GETDATE())
);
GO

I'd create a couple of stored procedures, however you could easily do these in the application code if desired.  Both procedures use a single atomic statement that negates the need for an explicit transaction since either the entire transaction will commit, or it will entirely roll back.

I tend to recommend using stored procedures for DML operations wherever possible since this allows you to make database level DDL changes without the requirement of deploying new binaries everywhere.

The first proc simply inserts a new row into the dbo.Status table, and returns a resultset including the new RequestID, the CurrentStatusID (which is set to 1), and the CurrentStatusDate which is set to the current date and time using GETDATE():

IF COALESCE(OBJECT_ID('dbo.StatusInsert'), 0) <> 0
BEGIN
    DROP PROCEDURE dbo.StatusInsert;
END
GO
CREATE PROCEDURE dbo.StatusInsert
(
    @RequestName VARCHAR(30)
)
AS
BEGIN
    INSERT INTO dbo.Requests (RequestName, CurrentStatusID, CurrentStatusDate)
    OUTPUT inserted.RequestID, inserted.CurrentStatusID, inserted.CurrentStatusDate
    VALUES (@RequestName, 1, GETDATE()) ;
END;
GO

The second proc allows us to do 2 operations in one atomic unit.  First, it updates the CurrentStatusID and CurrentStatusDate columns in dbo.Requests.  Then, using the OUTPUT clause, it inserts the old values from dbo.Requests into dbo.RequestStatusHistory.

IF COALESCE(OBJECT_ID('dbo.StatusUpdate'), 0) <> 0
BEGIN
    DROP PROCEDURE dbo.StatusUpdate;
END
GO
CREATE PROCEDURE dbo.StatusUpdate
(
    @RequestID INT
    , @NewStatusID INT
)
AS
BEGIN
    UPDATE dbo.Requests
    SET Requests.CurrentStatusID = @NewStatusID
        , Requests.CurrentStatusDate = GETDATE()
    OUTPUT deleted.RequestID, deleted.CurrentStatusID, deleted.CurrentStatusDate
    INTO dbo.RequestStatusHistory(RequestID, StatusID, StatusDate)
    WHERE Requests.RequestID = @RequestID
END;
GO

Insert some sample data:

EXEC dbo.StatusInsert 'Request #1';
WAITFOR DELAY '00:00:00.500';
EXEC dbo.StatusInsert 'Request #2';
WAITFOR DELAY '00:00:00.500';
EXEC dbo.StatusUpdate 1, 2;
WAITFOR DELAY '00:00:00.500';
EXEC dbo.StatusUpdate 1, 3;
WAITFOR DELAY '00:00:00.500';
EXEC dbo.StatusUpdate 2, 4;

Show the results:

SELECT *
FROM dbo.Requests;

SELECT *
FROM dbo.RequestStatusHistory;

When the client wants to see status history, you could provide those details using a reasonably flexible stored procedure such as:

IF COALESCE(OBJECT_ID('dbo.RequestHistory'), 0) <> 0
BEGIN
    DROP PROCEDURE dbo.RequestHistory;
END
GO
CREATE PROCEDURE dbo.RequestHistory
(
    @RequestID INT = NULL
    , @RequestName VARCHAR(30) = NULL
)
AS
BEGIN
    ;WITH ReqHistory
    AS
    (
        SELECT r.RequestID
            , r.RequestName
            , StatusDescription = s.StatusDescription
            , StatusDate = r.CurrentStatusDate
        FROM dbo.Requests r
            INNER JOIN dbo.Statii s ON r.CurrentStatusID = s.StatusID
        UNION ALL
        SELECT r.RequestID
            , r.RequestName
            , s.StatusDescription
            , rsh.StatusDate
        FROM dbo.Requests r
            INNER JOIN dbo.RequestStatusHistory rsh ON r.RequestID = rsh.RequestID
            INNER JOIN dbo.Statii s ON rsh.StatusID = s.StatusID
    )
    SELECT *
    FROM ReqHistory rh
    WHERE (rh.RequestID = @RequestID OR @RequestID IS NULL)
        AND (rh.RequestName = @RequestName OR @RequestName IS NULL)
    ORDER BY rh.RequestName
        , rh.StatusDate;
END
GO

Running the RequestHistory proc against the sample data:

EXEC dbo.RequestHistory @RequestName = 'Request #2';


How recording of every change of a row in a database is generaly stored? In a project I'm working on, every change to the rows in some tables of the database must be tracked for further audit or rollback. It must be easy to find who modified the row, from which IP address and when, and be able to restore previous version.

The similar thing is used for example by Stack Exchange. When I change someone else's question, it is possible to find that I changed it, and to rollback the changes.

What is the general technique used to store every change to an object in a database, given that my current schema has mostly the same properties (below) that an average business app?

The objects have a relatively small size: there might be some nvarchar(1000) for example, but not huge blobs of binary data, this one being stored directly on disk, and accessed directly, and not through Microsoft SQL filestream,
The database load is pretty low and the whole database is handled by one virtual machine on a server,
The access to the previous versions doesn't have to be as fast as the access to the latest version, but still has to be up-to-date¹ and not too slow².

<tl-dr>

I thought about the following cases, but I have no real experience with those sorts of scenarios, so I would hear other's opinions:

Store everything in the same table, distinguishing the rows by ID and version. IMO, it is seriously stupid, and will hurt soon or later on performance level. With this approach, it is also impossible to set a different security level to the latest items and to the versions trace. Finally, every query would be more complicated to write. Actually, to access the up-to-date data, I would be forced to group everything by ID and retrieve, in each group, the last version.
Store the latest version in one table, and, at every change, copy the obsolete version to another table in another schema. The flaw is that every time, we store every value, even if it didn't change. Setting unchanged values to null is not a solution, since I must also track when the value is changed to null or from null.
Store the latest version in one table, and the list of changed properties with their previous values in another table. This seems having two flaws: the most important one is that the only way to sort heterogeneous types of previous values in the same column is to have a binary(max). The second one is that it would be, I believe, more difficult to use such structure when displaying the previous versions to the user.
Do the same thing as in two previous points, but store the versions in a separate database. Performance-wise, it might be interesting in order to avoid slowing down the access to the latest versions by having the previous versions in the same database; still, I believe that it's a premature optimization and must be done only if there is a proof that having older and latest versions in the same database is a bottleneck.

</tl-dr>

¹ For example, it would be unacceptable to store the changes into a log file, as it is done for HTTP logs, and flush the data from the log to the database at night when the server load is lowest. The information about different versions must be available immediately or nearly immediately; a few seconds delay is acceptable.

² The information is not accessed very frequently and only by specific group of users, but still, it would be unacceptable to force them to wait for 30 seconds for the list of versions to display. Again, a few seconds delay is acceptable.

The normal way do do audit logging of this sort is to have a shadow table and log changes with triggers on the base table you are auditing.  The other tables can be placed on a different physical disk if you need to to that for performance, and you can put indexes on them if you need to support quick retrieval of the data.

The tables will have roughly the same structure as your original tables, but will have a datetime column for when the change took place and a marker for whether the row was inserted, changed or deleted.  Sequencing the versions can be done by the time stamp.

The change date can be done by making the datetime column not null with a default of getdate(); an audit user column will capture the user with a not null column defaulted to Suser_Sname().  Assuming the actual user is being impersonated in the session this will capture the identity of the user making the change.

The database has no way to be aware of the IP address connecting to a web server.  The application will have to explicitly capture and log the IP address with the transaction.

If you have a large number of tables you wish to audit, you can use the metadata from the system data dictionary to generate the triggers programmatically.

This solution is by far the best for several reasons:

It captures any changes to the table, not just those made by the application.
The audit tables can be put on a different set of disks to reduce the I/O load on your primary tables.
You can use a view based on a union of the table and audit log table to that will show the entire history including the current version.
You can index the audit log tables as necessary so that the audit users can query them responsively.  As usual, index selection is a tradeoff between query performance and update overhead.


How are these tempdb tables automatically created after each SQL Server restart? I have a session state management implementation using SQL Server. There is an ASPState database, and ASPStateTempApplications & ASPStateTempSessions tables in the tempdb database.

When I restart the SQL Service, tempdb database recreates, but these tables recreate too. I want to know: how do these tables recreate automatically each time?

I looked for start-up parameters and jobs, but did not find any.

Thanks for your input. Actually, I found the answer:

Those tables were created by a stored procedure that is set to execute on startup of the database engine service, configured via the sp_procoption system stored procedure.


Parallel Redo Message Spam yesterday i had several Problems in a Windows Service with a Function that already works and runs several Months without a Problem.
But yesterday i got alot of Errors in my Service. The Errormessages were a Combination of mostly these Twos.
The connection does not support MultipleActiveResultSets.
Connection Timeout Expired.  The timeout period elapsed during the post-login phase.

I did not have changed anything in the past 2 Months and had never Problems with this Service.
While searching the Problem i stumbled over these SQL-Server Log Messages
Parallel redo is shutdown for database '' with worker pool size [1].

Parallel redo is started for database '' with worker pool size [1].

Starting up database ''

Theses Messages appeared also in the Past, periodically they occured.
But yesterday they happened in a extremly high Frequency. They have started around 5 Hours after the Backup Process and lasts for about 7 Hours.
I'll had nearly 300'000 Events. These 3 Messages together occured around 3-4 Times per Second and suddenly they stopped.
Im not sure anymore if they stopped after i ended the Service, but as this Message have stopped to occur in this High Frequency, my Service also had no Problems anymore. Im not sure if these 2 Instances (Sql-Server and Windows Service) were rocking each other up.
I'll kinda understand what leads to this Messages, but not what why it were spammed like this.
Any Ideas on how to prevent this and / or ways of investigating this further?
All the Best Daniel

Those "Starting up database" messages occur when the database starts up (is opened / accessed by a user) for the first time.  Normally you'll only see this message once (when the instance starts up), or if something happens to cause the database to shut down / close (like an availability group failover).
If you are seeing that message a lot, one likely reason is that the database AUTO_CLOSE option is set to ON.  This means the database is shut down / closed very quickly once there are no connections to that database.
Your service probably uses best practice patterns of opening a connection, doing some work, and then closing the connection.  This would lead to the database shutting down / starting up a lot if that's the only thing accessing the database.
You can, and probably should, turn this off to make sure the database remains open and online even when it's not being actively used:
USE master;
GO
ALTER DATABASE YourDatabaseName SET AUTO_CLOSE OFF;
GO

The parallel redo messages are also normal, and generally only occur when the database is starting up.  See this Q&A for more information on that one: Messages about parallel redo
You mentioned this problem is intermittent.  It's possible some other person or process is changing this setting periodically.  Or that the service application you mentioned has somewhat variable performance that might lead to more or less DB shutdowns (depending on how quickly it opens and closes connections).


oracle schema grants and tablespaces I have a schema with its tablespace where are application's data are located. Now I need a separate schema which will have an access only to select the data. I wish to create a set of views for that schema.

My questions are:

is it possible to create schema at the same tablespace and limit its grants just to some views (without giving an access to the original tables) ?
is it possible to create separate tablespace for that schema and create views and grants that will work across both of them ?
if both are possible than which one should be used and why ?

Thank you in advance.

update:

I created 3 schemas:

A is the one which has tables FOO

 grant select on FOO to B

B is to create the view

create or replace view BAR as
select * from A.FOO

C is suppose to be able to select * from B.BAR but if try to (as B):

grant select on BAR to C

I'm getting an error:

Error: ORA-01720: grant option does not exist for 'A.FOO'

Basicaly I wish to achieve 3 levels:

A (original table) < B (view definition) < C (select only from the view)

But it seems that it requires me to grant C direct access to read the original table which fails all the idea.

is it possible to create schema at the same tablespace and limit its grants just to some views (without giving an access to the original tables) ?

Yes. And if your concern is limited to just read-only access to those tables then you don't even need to create separate views, you can simply create new schema and grant select on those tables.

  is it possible to create separate tablespace for that schema and create views and grants that will work across both of them ?

Yes. If you just create views on this schema then you even don't need to worry to manage separate tablespace because  views(is a named and validated SQL query) are stored in the Oracle data dictionary.

  if both are possible than which one should be used and why?

It's up to you. In my opinion, if you place schema in to the separate tablespace then you can transport it to another database with ease. Also it makes possible to perform point-in time recovery. Additionally, we can place such tablespace to different disks as per the IO performance requirements.

Update

You have to have granted select on FOO with grant option to user B in order for user 'B' to grant select on view to user 'C' as demonstrated below.

SQL> conn a/a
Connected.

SQL> grant select on t1 to b with grant option;

Grant succeeded.

SQL> conn b/b
Connected.

SQL> create or replace view v_t1 as select * from a.t1;

View created.

SQL> grant select on v_t1 to c;

Grant succeeded.

SQL> conn c/c
Connected.
SQL> select * from b.v_t1;

    ID
----------
     1


Oracle SecureFiles LOBs Licencing We argued in our DBA team on whether an Oracle Database with SecureFiles LOBs needs to be an Enterprise Edition. Can I use Oracle Secure File Lobs in a Standard Edition?

Securefile LOBs are free to use in any edition.

Only the extra features of Securefile LOBs require buying the Advanced Compression or Advanced Security option, and these options are available only in Enterprise Edition.

These features are:

Advanced Compression: Securefile compression and deduplication.

Advanced Security: Securefile encryption.

Licensing Information


Schema to make a Payment History/Viewing History On some websites I have seen two history of events: Payment History and View/Watched History.

I want to have both payment and viewing histories in my app, but I got stuck when I started thinking about the database schema.

As a basic example, the easiest way to do this for the view history would be:

Have one table called view_history
Have these four columns, at least: id, user_id, timestamp, view_id

So when a user with an ID of 80 visits a view with an ID of 22, this should be added to the table.

When this user_id 80 views the View History view, it will loop through the view_history table and displays only the rows that match the user_id = 80.

This is the easiest way of doing this, but I'm afraid it is also the worst. Log tables tend to get big pretty quickly and I assume looping through this whole table wouldn't be ideal performance wise.

With that in mind, can you suggest the best approach to this?

Thank you!

Edit: I apologize if this wasn't the right place to post this question.

Yes, logs tend to become big.  But that is not necessarily a problem.  (More in a minute.)

I would be tempted to treat View History as if it were another page, and log it in the same table.

The log does not really need an id.  PRIMARY KEY(user_id, view_id, timestamp) (all the columns) serves two purposes:  Having a PK; and facilitating View History because the PK starts with user_id.  One drawback:  If the user views the same page twice in one second, the INSERT would get "duplicate key" error.  So I would do INSERT IGNORE to avoid the error (and lose the record of the extra page load).

The best way to handle purging old log entries (should you decide to do such), is to PARTITION BY RANGE using the timestamp.  The PK is already prepped for that.  If you want a 30 day retention period, use 32 partitions and do DROP PARTITION nightly.  If you want a bigger retention, then use weekly partitions.  The details of "why", plus sample code for doing the DROP and REORGANIZE are in my Partition Maintenance blog.

Assuming that you use InnoDB for the log, it would be beneficial to have enough RAM so you can set innodb_buffer_pool_size bigger than the size of one partition.

(All InnoDB and MyISAM indexes are BTrees, so the comment about Heap is irrelevant.)


moving binary database folder is causing issues with innodb tables so I have a mix of myisam and innodb tables on a database. (its a pretty big db 30gb) I copied the folder of the database from one server to the next /var/lib/mysql/databasename well the myisam tables are opening The innodb tables are listed but when I click on any of them on the left tab (phpmyadmin) I get this error: #1146 - Table 'databasename.users' doesn't exist this would be for the users table. Other innodb tables are having the same issue. I would like to know how to remedy this issue. I logged in to mysql directly through ssh as root, when I do : SHOW TABLES; the table users and the other innodb tables are all listed. When I do SHOW COLUMNS FROM users; I get :

mysql> show columns from article;
ERROR 1146 (42S02): Table 'sitecontent.article' doesn't exist

any ideas please ? (issue resides in innodb tables only!)

You cannot copy a database folder that has InnoDB tables because InnoDB storage engine maintains a symbiotic relationship between the InnoDB tables' physical files (frm and .ibd) and the data dictionary.

The data dictionary is inside the system tablespace file known as ibdata1.

You cannot copy a data folder and expect it to be independent of ibdata1.

SHOW TABLES; works because it only scans for .frm files and does not access metadata.

You could do one of two things

mysqldump the database and load the mysqldump into the target server
Run FLUSH TABLES;, disconnect the .ibd files from ibdata1 before copying to the other server. Then, reattach the .ibd files on the target server

Recover MySQL database from data folder without ibdata1 from ibd files
Other Posts

GIVE IT A TRY !!!


Instance and database (Oracle vs SQL Server) I have been using Oracle up to now, where:

instance -> process + memory
database -> physical datafiles

Normally one instance maps to one database. Inside a database, there can
be many tablespaces (system , users etc).

However, I am not so sure about SQL server. It seems to me that for SQL Server, one instance = multiple databases? Or one instance = multiple schemas?

In Oracle, one database contains the system, sysaux, temp tablespaces and the user tablespace. In short, both system and user are in the same database.

In SQL Server, it seems like there is one system database and that can serve multiple user databases?

I'm a DBA on Oracle and SQL Server.

The confusion is due to a mismatch of semantic and an extra level in SQL Server's hierarchy but there's more than that!

Note that we will talk only about single instance database in both instances (pun intended).

Instances

In Oracle, one instance will have an amount of memory allocated to it (SGA/PGA) and processes spawned (smon,pmon dw0X and so on). There are system views to monitor all of the stuff that will be created and used (V$ and dba_ views).

This is the structure we are familiar with in oracle.

And you already mentioned it, the database is where the physical files are. Typically moving to RAC makes the difference glaring.

In SQL Server, the instance works the same way:
A bunch of memory is allocated and there are system views (sys.) to monitor all of that stuff.

The instance has 4 system databases:

system where the system views are.
msdb database which also holds system related information. Typically that's where you will find your backups related data. A bit of maintenance is required on it at least to flush old backups records.
model, the aptly named database which is used as a model for every new database you will create. It's a good idea to modify the data file settings (auto extend, initial size, backup mode) so all new database benefit from it.
tempdb developed below.

The system database holds meta data information for every other database on SQL Server.

Temp space

In Oracle:

Temp tablespace (create temporary tablespace).
In oracle the temp tablespace is a spill zone for operations which can't fit in the PGA (process memory, default at 200M (up to 11g), your hash join for example if bigger than 200M spills in temp tablespace, generally that's a cause of slow performing query).

In SQL Server, there is a tempdb database located in the "system databases" section and it's very actively used, much more than in oracle. Place it on very fast disks (SSD or fiber comes to mind)

Thus comes the 2nd large difference:

Objects

In SQL Server:

The objects (table,views), are not tied to a user as in oracle, but rather to a "database".
The "database" holds (not own), the objects. At the creation time of the database, a data file is mentioned to store these objects.

In oracle:

A user creates objects which will land in a schema of the same name. Naming the users carefully becomes important.

And here comes the 3rd big difference:

Security

In oracle:

Users will be created and they will be given the right at the very least to create sessions so they can access objects they have been allowed to.
Or, they will have the right to create objects in their own schema (resource role), which will then create a schema in that name.

You may chose, and it's a good practice, to create a tablespace and make it the  default for each users able to create objects. If that's not done, a default tablespace is used, generally "USER_DATA".

In SQL Server:

Since the objects are not owned by a particular user in a schema, users must be first created, then privilege assigned. Typically, whoever will be the main user to do the object creation will be granted the "owner" role on a given database.

That user in SQL Server would be the equivalent in oracle of a schema owner but there may be more than one owner.

Security is where SQL Server shines over Oracle: SQL Server security can be tied to Active Directory which manages the desktop users. These desktop users can be tied to a user group say "read only". It's possible to add that active directory group to SQL Server and give it a "read only" access to your database.

When a user leaves your company, the Active Directory Admin delete that user and access to the database is lost as well.

Oracle can use LDAP but let's face, it's not very often well tied to the windows desktop users.

The 4th big difference.

Semantic

SQL Server has an extra layer. In a SQL Server database, objects may be organized in schemas. Schemas may have owners too. It introduces more fine grain security as well but while it's used, it's not common. I would say it's also good practice to group your objects by schema early in the development stage to benefit later on from extra security abilities when needed.

Logs / Archived Logs

Finally archived logs work differently in oracle and SQL Server.
SQL Server has a log file created for each database, while oracle has archive log mode turned on at the instance level.

This is where the line blurs. For some aspects, SQL Server's databases really are databases. In other instances they look more like schemas.

The archiving in SQL server is set with the option backup mode set to full. Then they are archived by performing a log backup. The log backup will require a full backup to take place before it starts working.

In Oracle, archived logs are archived automatically, then we back them up to a remote location.

Summary

Oracle:

Instance->User1->Schema1(named User1)->Tablespace1
                                     ->Tablespace2
        ->User2->Schema2(named User2)->Tablespace3
        ->User3->Granted "read only"->schema2.table1

SQL Server:

Instance->Database1->Schema1
        ->Database2->Schema2
        ->User1
        ->User2

        ->User1->Granted "owner"->Database1
        ->User2->Granted "read only"->Database2->Schema2

These are the things that come to my mind when I get to compare both.
I would love to hear more from others and add on/correct this answer.

Also I don't have a particular preference for either.
I think they both do a great job and I enjoy working with both very much.


Mariadb always writes tmp disk to a file I'm trying to understand why Mariadb (v10.4.12) is writing most of the temp tables to disk.

Created_tmp_disk_tables 196,8k
Created_tmp_tables 367.8k

There's 188G of RAM in the system with ~50% of it unused. So it's not like that should be a problem.

I enabled performance schema and I see that there are queries with small datasets with GROUP BY statements that have 100% rate on writing tmp tables to disk (events_statements_summary_by_digest.SUM_CREATED_TMP_DISK_TABLES === events_statements_summary_by_digest.SUM_CREATED_TMP_TABLES)

For example - one of those queries have:
* 160 rows when grouped
* 2150 rows without grouping
* each row is about ~20 bytes - so it's quite tiny

I have no TEXT fields in the query only some integer fields that are indexed.

Query does have multiple INNER JOINs in it. But all the ON conditions etc are done on integer (PK) fields - no string used anywhere.
One joined table does have TEXT field in the schema but it's not used in the query itself. I can't see how that could influence this.

How could I get more info on why are these tmp tables written to disk?

There are lots more different queries that are behaving same way and I can't understand why is that so.

I'm not putting any schema / query examples here currenlty as I'm interested understanding how to debug this myself and I really don't want to publish any info about production systems schema etc.

Read the manual where it mentions using MEMORY for temp tables and when it will switch to MyISAM, either because of limitations such as TEXT or because of exceeding LEAST(tmp_table_size, max_hash_table_size).  Further note that VARCHARs become CHAR for calculating when to spill to disk.  (This is fixed in 8.0; I doubt if it is yet in 10.4.)  Example: VARCHAR(250) utf8mb4 counts as 1000 bytes.

See EXPLAIN SELECT.  If it says Using temporary, that is a clue.  However, that phrase is usually on the first line of the EXPLAIN not necessarily on the line that triggered the need.

Each of DISTINCT, GROUP BY, and ORDER BY may need a temp table.  If the query involves a JOIN, then it is more likely that one of those will trigger the need for a temp table.

Do not fear temp tables; some queries simply require such.  A simple example is any query with GROUP BY something ORDER BY something_else.

Granted, the above does not say whether the temp table is on disk or in memory.

If that does not suffice, please provide a specific case.  Show us the query and SHOW CREATE TABLE and EXPLAIN SELECT .... We should be able to help you change so that it does not need a disk-based temp table or explain why it must use such.

To check to see if a particular query uses disk temp table(s), do something like:

SHOW SESSION STATUS LIKE 'Created_tmp_disk_tables';
SELECT ...
SHOW SESSION STATUS LIKE 'Created_tmp_disk_tables';

That is a counter, hence the need to check before and after.

The slowlog might also give you a clue.


How to get actually allocated tablespace size by each postgres database? Postgres tablespace is cluster-wide. Therefore, how do I caculated how much space allocated by each database in postgres tablespace ?

To get the disk space occupied by a database (nicely formatted):

SELECT pg_size_pretty(pg_database_size('mydb'))

Disk space occupied by a tablespace:

SELECT pg_size_pretty(pg_tablespace_size('mytblspace'))

Find out more about database object size functions in the manual:


FastTrack Data Warehouse -E startup parameter We are running a data warehouse based on the Fast Track architecture. One of the recommendations with Fast Track is to enable the -E parameter at SQL Server startup (increases the number of extents that are allocated for each file in a filegroup).

I've just discovered that our instance does not have this option enabled. If I enable it now, how can I ensure that existing database files are created with the additional extents?

The option only takes effect during bulk loads to tables, so you would need to re-load your data which seems a bit pointless, or accept it's only relevant for new data loads.  The KB Article is fairly clear:

  The -E option must be used before the original data load because it does not affect existing data, only new allocations.

I would also say this is one of those 'Formula 1' tuning options that I would not enable unless I had already proved it had a sufficiently positive effect using a representative test of my actual business data and did not have a negative impact on other key workloads.  The recommendation also comes with a caveat:

  ...data warehouse applications that have a limited number of users running index or data scans.

Is this you?

NB The KB Article also ends with a strongly worded warning:

  Because the -E option may bring performance benefits only under the specific circumstances described earlier, we do not recommend that you use this option on a regular basis. Running SQL Server with the -E option set on may decrease the performance of multiuser workloads with frequent allocations because of increased CPU consumption.


Failed to update SQL Server 2008 R2 RTM to SP3 I'm trying to update SQL Server 2008 R2 RTM to SP3 by running SQLServer2008R2SP3-KB2979597-x64-ENU.exe, on Windowns Server 2008 R2 SP1.

It failed with the following error in the log,

  2015-11-26 10:24:01 SQLEngine: : Version-copying file from 'd:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Install\sqlscriptdowngrade.dll' to 'd:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\sqlscriptdowngrade.dll'.
  2015-11-26 10:24:01 SQLEngine: : Failed to version-copy file 'd:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Install\sqlscriptdowngrade.dll' to 'd:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\sqlscriptdowngrade.dll'. Exception data is: System.UnauthorizedAccessException: Access to the path 'd:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\sqlscriptdowngrade.dll' is denied.
  at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
  at System.IO.File.InternalCopy(String sourceFileName, String destFileName, Boolean overwrite)
  at Microsoft.SqlServer.Configuration.SqlEngine.Globals.VersionCopy(ServiceContainer context, String source, String target).
  2015-11-26 10:24:01 Slp: Configuration action failed for feature SQL_Engine_Core_Inst during timing ConfigRC and scenario ConfigRC.
  2015-11-26 10:24:01 Slp: Access to the path 'd:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\sqlscriptdowngrade.dll' is denied.
  2015-11-26 10:24:01 Slp: The configuration failure category of current exception is ConfigurationFailure
  2015-11-26 10:24:01 Slp: Configuration action failed for feature SQL_Engine_Core_Inst during timing ConfigRC and scenario ConfigRC.
  2015-11-26 10:24:01 Slp: System.UnauthorizedAccessException: Access to the path 'd:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\sqlscriptdowngrade.dll' is denied.
  2015-11-26 10:24:01 Slp:
  at Microsoft.SqlServer.Configuration.SqlEngine.Globals.VersionCopy(ServiceContainer context, String source, String target)
  2015-11-26 10:24:01 Slp:
  at Microsoft.SqlServer.Configuration.SqlEngine.SqlEngineSetupPrivate.Patch_ConfigRC(EffectiveProperties properties)
  at Microsoft.SqlServer.Configuration.SqlEngine.SqlEngineSetupPrivate.Patch(ConfigActionTiming timing, Dictionary`2 actionData, PublicConfigurationBase spcb)
  at Microsoft.SqlServer.Configuration.SqlConfigBase.SlpConfigAction.ExecuteAction(String actionId)
  at Microsoft.SqlServer.Configuration.SqlConfigBase.SlpConfigAction.Execute(String actionId, TextWriter errorStream)
  2015-11-26 10:24:01 Slp: Exception: System.UnauthorizedAccessException.
  2015-11-26 10:24:01 Slp: Source: Microsoft.SqlServer.Configuration.SqlServer_ConfigExtension.
  2015-11-26 10:24:01 Slp: Message: Access to the path 'd:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\sqlscriptdowngrade.dll' is denied..

I get the exact same error every time I run setup and even after rebooting.

I checked the path d:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\ it's owned by SYSTEM, and regardless of whether I ran the setup as admin, in TaskManager it's still showing that setup100.exe is running under by account. I tried and can't rename the file, unless if I take over the ownership of the folder, though I worried that doing so will break SQL Server.

Any idea what went wrong?

EDIT: I've compared the same folder on another DB server with the same installation (not scheduled to update SP3 yet), I can rename the sqlscriptdowngrade.dll file after an admin prompt. The permission for the folder is the same for both machine.

I saw a SQL update in Windows Update pulled from our WSUS server, Security update for SQL Server 2008 R2 Service Pack 3 and not realizing that it's an cumulative update, it managed to update the SQL server to 10.50.6220 (SP3 = 10.50.6000) without any error. I'm guessing probably some issue with the original SP3 setup. I'll update here again if I face any issue when we schedule to update the other SQL server.


PostgreSQL - Design database to avoid frequent update on large dataset We are trying to optimize out the performance on a database with these numbers and query requisites:

200-400k network segments identified by a unique ID
each network segment have a state with a limited number of dynamic attributes (i.e. an average speed). A single state of dynamic attributes could be stored in 8 bytes
the segment state will change every 3 minutes, H24, 7/7
Is it possible to query for the state of a group of segments (sometimes all segments) in a particular date range or just the actual situation.
It could be requested a spatial query to find all segments "around me" in a particular date (normally "at now")

With these requisites, we arrived at this solution (with a strong drawback, explained below).

[A] TABLE main_segments_history(
      id_segment integer NOT NULL,
      day date NOT NULL,
      day_slices bigint[],
      CONSTRAINT main_segments_history_pk PRIMARY KEY (id_segment,day)
)

[B] TABLE current_segment_release_state(
      id_segment integer NOT NULL,
      release_date timestamptz,
      ... all other attributes ...
      CONSTRAINT currsegm_release_state_pk PRIMARY KEY (id_segment,release_date)
)

Explaining the [A] table:

It is partitioned on field "day" with partition_manager (pg_partman). Each partition is one month
The day_slices array is a one-dimensional array of 480 elements, representing the granularity of each 3-minute-slice of the full day

Explaining the [B] table:

It's just the current release state for each segment

There is a back-end process that elaborate the network.
It inserts or updates the states of each segment, every 3 minutes.
In other words this process will insert new rows on day start and will update the inner array every 3 minutes.

The advantages of this solution:

A limited number of rows for each month partition table
Good performance when joining the static data of the segment (i.e. the geometry)
The little redundancy of the current release is very good to respond at real-time requests
Space-safer: only ~12 GB of stored data for each partition (1 month) -

The drawbacks:

constraint_exclusion. When querying on date range there is a need of using that feature/parameter of PostgreSQL. That is using constant values in a precompiled query that spans over multiple partitions. Example:

constraint exclusion OK (will only search on February and March 2017):

SELECT * FROM main_segments_history
         WHERE day BETWEEN '2017-01-01' AND '2017-02-03'

constraint exclusion KO (will search on all partition tables):

SELECT * FROM main_segments_history m
         JOIN sometable s ON s.id=m.id_segment
         WHERE day BETWEEN s.day_from AND s.day_to

The UPDATE is evil.

We turned OFF the autovacuum for performance issues, doing it with a batch every night.
Consider that in this way we have almost between 90M and 190M UPDATES each day, this is also the number of rows completely rewritten by postgreSQL (as you certainly know an UPDATE will flag the row deleted and then a NEW row is inserted) every day.

More, the UPDATE is a great time-consuming operation creating often a delay on the writes.

We first investigate at the possibility of using a LINK-TO-DATA design, using for example a table as a container of segments id with a data_id to a BIG TABLE of segment states, but we discarded when we just count the number of rows each month to handle with: ~2.880.000.000 with an amount of space of ~3GB a day. Not so good.

What do you think about? Do you have any solution to optimize this system?

The only way to avoid updates to your tables is just not to do them. Given the fact that you're just logging data, I would suggest a daily table, where you store the data for one day. Instead of having 190 M updates/day, you may have 190 M (smaller) inserts/day; and as many updates as there are segments.

 CREATE TABLE main_segments_history
 (
       id_segment integer NOT NULL,
       day date NOT NULL,
       day_slices bigint[],
       CONSTRAINT main_segments_history_pk PRIMARY KEY (id_segment, day)
 ) ;

 CREATE TABLE dayly_segments
 (
      id_segment integer NOT NULL,
      day date NOT NULL,
      id_slice integer NOT NULL,
      slice bigint,
      PRIMARY KEY (id_segment, day, id_slice)
 ) ;

This would simulate the data for one day (and 200 segments; instead of the millions you might have):

 INSERT INTO
     dayly_segments
     (id_segment, day, id_slice, slice)
 SELECT
     id_segment, '2017-01-01', id_slice, (random()*1e7)::bigint
 FROM
     generate_series (1, 200) AS s1(id_segment)
     CROSS JOIN generate_series (1, 20*24) AS s2(id_slice) ;

I assume you have some time with low activity, when you run the vacuuming processes. At that time, you can move data from the dayly_segments table to the main_segments_history, and put all the data as an array. This way, you're never updating main_segments_history.

This is, basically, what you would do:

 -- Move segments from dayly_segments to main_segment_history
 INSERT INTO
     main_segments_history
     (id_segment, day, day_slices)
 SELECT
     id_segment, day,
     (SELECT array_agg(slice)
      FROM (SELECT slice
              FROM dayly_segments s1
             WHERE s1.id_segment = s0.id_segment AND s1.day = s0.day
          ORDER BY id_slice) AS s2)
 FROM
     (SELECT DISTINCT
         id_segment, day
     FROM
         dayly_segments s0
     WHERE
         day = '2017-01-01'
     ) AS s0 ;

 -- Delete them from original
 DELETE FROM
     dayly_segments
 WHERE
     day = '2017-01-01' ;

 -- At this point, you should also...
 VACUUM dayly_segments ;

dbfiddle here

Assumptions:

You never "miss" any $current_value. That is, there are no holes in your arrays.
id_slice can be assigned the result of any increasing sequence. You could aso use, instead of an integer value, just a time value.


Table frequency metric Count (minimum 2 hits every 5 seconds using a datestamp within 24 hours) I need to find out how many times a table has an insert within a 24 hour period per 5 seconds, if the table is inserted at least twice (2x) or more for each 5 seconds of the day, it should be counted. So for every 5 seconds (using a datestamp), how many times was the table inserted at least 2x  within a 24 hour period per 5 seconds.  The max the count can be 17280, and that's only if there was 2 or inserts for each 5 seconds.

Since your day is divided into 5-second buckets, you'll need to do the same to your timestamp in the table.  Since you're counting the 5-second buckets and not the actual number of updates, you'll need to use either a CTE or derived table.  I'll use the CTE only because I think it's easier to read.

WITH UpdateIntervals AS (
  SELECT
      DATEADD(SECOND, ROUND(DATEPART(SECOND, UpdateTimestamp)/5, 0)*5,
        DATEADD(MINUTE, DATEDIFF(MINUTE, 0, UpdateTimestamp), 0)) AS Interval,
      COUNT(*) AS UpdateCount
  FROM dbo.YourTable
  WHERE UpdateTimestamp BETWEEN @ReportStartTime AND @ReportStopTime
  GROUP BY DATEADD(SECOND, ROUND(DATEPART(SECOND, UpdateTimestamp)/5, 0)*5,
           DATEADD(MINUTE, DATEDIFF(MINUTE, 0, UpdateTimestamp), 0))
)
SELECT COUNT(*)
FROM UpdateIntervals
WHERE UpdateCount >= 2;

You could just as easily use HAVING COUNT(*) > 1 inside the CTE and then a simple COUNT(*) in the query with no where condition, but to me, this makes it clear what I intended to do when I come back and read this 6 months later.  I'm counting the number of updates per interval, then counting intervals with at least 2 updates.

The nasty-looking DATEADD formula isn't as bad as it looks.  First, it truncates your update timestamp down to the minutes level by calculating the number of minutes between 0-time and your timestamp, then adding them back to 0-time.  The seconds part of your timestamp is then rounded down to the previous multiple of 5, and the rounded value added back to the timestamp to give you 5-second buckets.


Group and count array elements using intarray I am working on a Postgres 9.4 project with the intarray extension enabled. We have a table that looks like this:

items
-------------------------------------
id    name                  tag_ids
--------------------------------------
1     a car                 {1,4}
2     a room to rent        {1}
3     a boat                {1,2,4,11}
4     a wine                {2}
5     emily                 {3}

I'd like to group the tag ids if possible. Like get a count of all the elements that have a tag_id of '{1,2,4,11}'

tag_id  count
1       3
2       2
4       2
11      1

Is this possible? I would think an intersection like this:

select * from items where tag_ids && '{1,2,4,11}'

But I need to group by the array elements inside the intersection result. If I group by tag_ids, it is just the unique value.

How would I do it?

Keep the basic query you already have to cheaply identify rows that have any relevant array elements using an index.
Then unnest only the intersection (tag_ids & '{1,2,4,11}') in a LATERAL join. Finally, aggregate:
SELECT tag_id, count(*) AS ct
FROM   items i
     , unnest(tag_ids & '{1,2,4,11}'::int[]) tag_id
WHERE  tag_ids && '{1,2,4,11}'::int[]
GROUP  BY tag_id
ORDER  BY count(*) DESC;

Once again, the intersection operator & of the intarray module is instrumental.
Previous related answer:

Order result by count of common array elements

Without intarray
If you don't have intarray installed, or for any other array type, we need another join instead:
SELECT tag_id, count(*) AS ct
FROM  (
   SELECT *
   FROM   items
   WHERE  tag_ids && '{1,2,4,11}'::int[]
   ) i, unnest(tag_ids) t(tag_id)
JOIN unnest('{1,2,4,11}'::int[]) x(tag_id) USING (tag_id)
GROUP BY tag_id
ORDER BY count(*) DESC;

Subtle difference: the intersection operator folds duplicates to produce distinct elements, while this query does not. Only matters if there can be duplicate array elements ...
SQL Fiddle  demonstrating both and with an additional row that has a mix of matching and not matching elements to demonstrate the need of the intersection or additional join to eliminate unwanted elements.


SQL Server 2012 CPU usage spikes to 100% I am using SQL Server 2014 in AWS and the AWS image configuration is 4 cores and 16GB RAM(m3.xlarge). I am running the following simple query,

SELECT * FROM user_table WHERE user_id = '10'

user_table contains 1000k records, user_id is primary key. When the above simple query is executed from my application through EJB hibernate, the CPU spikes to 10% for a moment and again it backs to normal.

So my use case is, 100 users will concurrently try to hit the application, so in a fraction of second 100 times the above query will try to execute in a fraction of second. So the CPU usage spikes to 100%. Once all the query execution is completed the CPU usage is back to normal at 1%.

Why it so? whether I need to increase my AWS instance type?
What should I have to do in-order to make SQL server to handle 100 or more concurrent hits without making high CPU usage? If my query is so complex then there might have a chance to get spike but my query is simple and straight forward.
Is there is any bench mark metrics available for SQL server 2014?
Any solution to make support for concurrent hits with SQL server by low CPU usage consumption?
One more information my data file size is around 32.2GB and log
file size is around 894mb for my database.
My DB has the isolation level of READ_COMMITTED_SNAPSHOT is set to ON.
But when I tried by setting READ_COMMITTED_SNAPSHOT to OFF, there is a difference of 20% performance improvement but not that much considerable performance improvements.
Similarly I faced another issue I posted over here. Consider this too for analysis(Some hint may arise from the question in the link).

What is the exact type of the user_id column?

It is very likely not a VARCHAR column therefore the rules of data type precedence dictate that the comparison must be done by casting the user_id to the type of the '10' literal, thus negating the possibility of an index seek and forcing a full scan. This is a very common mistake.

I recommend reading How Data Access Code Affects Database Performance (do a mental translation for Java - Hibernate) and How to analyse SQL Server performance.