SQLite: Using count in triggers to update another column UPDATE: Fixed. Not 100% sure why sqlfiddle.com didn't like my syntax, but using https://dbfiddle.uk/?rdbms=sqlite_3.27 worked. I did change the last WHERE clause to be based on the id column, so that it only updates the current record, and increments instead of storing max() on records for username.
I'm trying and failing to do something similar as described here (but in SQLite):
Using count in triggers to update another table.
I assume a trigger is the best way to achieve what I want, but am open to alternate suggestions. My SQL experience is... limited, to say the least.
My goal is to have one column with an identifier, say username and another column that is a count of the times username is in the database at the time of the record insertion.
id | username | u_cnt | etc
1  |  MarkC   |   0   | ...
2  |  PeterB  |   0   | ...
3  |  MarkC   |   1   | ...

Here's a sample of what I've been trying:
create trigger count_user
after insert on rec_info
begin
  update rec_info set u_cnt = ( select count(*) from rec_info where username = new.username );
end

I've been bashing my head against the docs at https://sqlite.org/lang_createtrigger.html#syntax_restrictions_on_update_delete_and_insert_statements_within_triggers
but I can't seem to figure out what's wrong with the count_user statement. Depending on where I'm testing it I'll get one of the following
near ")": syntax error or could not prepare statement (1 incomplete input), neither of which are that useful to me.
I've also tried making u_cnt a generated/computed column ( https://sqlite.org/gencol.html ), but trying to declare the column with:
u_cnt int as (count(username)) stored,

Which gives me misuse of aggregate function count() and trying to add HAVING or GROUP BY led me down a rabbit hole that gave me and headache and didn't seem to lead to progress.

Your error i can't reproduce, but i think you want this for your trigger.
MAX(u_cnt) + 1 can also be used as long as the insert only uses 0 for u_cnt

CREATE TABLE rec_info
    ("id" INTEGER, "username" TEXT(6), "u_cnt" TEXT(6))
;

create trigger count_user
after insert on rec_info
begin
  update rec_info
      set u_cnt = ( select COUNT(*) from rec_info  where username = new.username)
  WHERE username = new.username;
end

INSERT INTO rec_info
    ("id", "username", "u_cnt")
VALUES
    (1, 'MarkC', '0')
;

INSERT INTO rec_info
    ("id", "username", "u_cnt")
VALUES
    (2, 'PeterB', '0')
;

INSERT INTO rec_info
    ("id", "username", "u_cnt")
VALUES
    (3, 'MarkC', '0')
;

INSERT INTO rec_info
    ("id", "username", "u_cnt")
VALUES
    (4, 'MarkC', '0')
;

SELECT * FROM rec_info

id | username | u_cnt
-: | :------- | :----
 1 | MarkC    | 3
 2 | PeterB   | 1
 3 | MarkC    | 3
 4 | MarkC    | 3

db<>fiddle here


Why do connections to my database fail, but only sometimes? I am having some trouble diagnosing the cause of a problem I am having with Microsoft SQL Server 2008 R2.

There is an application written in C#.net that connects to the database.

Occasionally the application will throw this error:

Cannot open database "<database name>" requested by the login. The login failed.
Login failed for user '<user name>'.

I can successfully connect to the database through SQL Server Management Studio by copying and pasting the login information from my connection string in Web.config

Looking at the SQL Server log, this is the error that comes up:

Source      Message

Logon       Login failed for user '<user name>'. Reason: Failed to open the explicitly specified database.
Logon       Error: 18456 Severity: 14 State: 38.

Looking up that error code online doesn't provide any solutions. The recommendation is to run a trace using SQL Server Profiler and wait for the problem to happen.

I ran the trace and noticed that the following happens every time the error occurs (some lines removed for privacy reasons, click to enlarge):

When the last item happens, this error message opens:

I looked in the SQL Server Agent Jobs tree in SQL Server Management Studio and noticed some jobs in there that I created but don't use any more. I removed them and the error still comes up.

The only job left in there is:

syspolicy_purge_history

Which I assume are required to some extent.

Does anyone know how to fix this problem? What steps can I take to find and resolve the issue?

Detailed Version Info:

Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)   Jun 28 2012 08:36:30   Copyright (c) Microsoft Corporation  Web Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

Is the error you see on that last line always preceded by the same set of jobs, or does the error happen in different places?

I've thought about this for the last half hour and everything I think of I can refute.  Do you have any known networking issues on this server?  I've seen something kind of like this with VMWare's networking stack being flaky and dropping connections.  But then that doesn't make sense because the SQL box is showing an error.

Even though I ask about networking, I can come up with refutations to that, too.  This is a tough one.

If the jobs listed above are the culprit, then I would need to see what they are doing be sure what was going on.


Index Reorganize During Database Full Backup Index maintenance usually starts 3 hours before the database full backup and ends before the full backup start.

The database is using the Simple Recovery model.

Last weekend the index maintenance took more than 5 hours and the full backup was running at the same time.

We have noticed that since the full backup started the transaction log File started to grow every 30 Seconds until it filled the disk and both full backup and maintenance failed due to insufficient disk space for log file.

Why, when the backup has started while index reorganize is running, does this happen to the transaction log file?

When a database backup occurs, it also includes portions of the transaction log.

I'm quoting from Paul Randal's post More on how much transaction log a full backup includes. (Highlighting mine)

  Now, the reason that the transaction log is read is so that the
  restore operation can recover the database so it is transactionally
  consistent as of the point in time when the read data operation
  completed.

  So, (ignoring replication) the minimum LSN of the transaction log
  that’s included in the full backup is MIN (LSN of last checkpoint, LSN
  of oldest active transaction). This ensures that recovery can REDO log
  records to bring pages up-to-date and UNDO log records for
  transactions that had not committed.

Your index maintenance is changing data pages that your backup may have already read and included in your backup.  Once a data page is read by the backup, it is not read again.

In order for SQL Server to recover the database so it is transactionally consistent as of the point in time when the read data operation completed, the log cannot be truncated until those transaction log records have been included in the backup and the log would continue to grow and possibly run out of space.


Why doesn't pg_dump include all database SETtings? I'm trying to have this included in the script generated by pg_dump.

ALTER DATABASE foo SET plv8.start_proc = 'bar';

I'm using the following command

pg_dump -s -n public -d development > foo.sql

The dump file includes some SETtings, but it doesn't include the one above.

What am I doing wrong?

Database (and also role) settings are stored in pg_db_role_setting. Like pg_database, there is only one copy of pg_db_role_setting per cluster. So those settings are not stored in database. They are stored globally.

Try to add --create parameter to your command. With this, database create  script will be added at the beginning of the dump file with the configuration.


Is there a way to limit log file usage for a transaction in SQL Server? This question is a follow up on these two:

Auto rollback Explicit transactions after X amount of time
Database file …_log has a max file size set to x MB. If it runs out of space, the database will stop working

From the second one I find out that an careless SQL user might leave a very long running transaction and lead to allocating huge amount of disk for the log file.

The first one indicates that killing a transaction after a certain period of time can be achieved through a job, although this approach is not recommended.

If I am sure that none of the transactions opened against a database should not allocate more than a certain amount of log, is there a way to find out how much log is allocated for a SPID, so that can I kill it?

Shortly put, I am interested in capping max log file usage for a transaction.

Question: Is there a way to limit log file usage for a transaction in SQL Server?

If I am sure that none of the transactions opened against a database
  should not allocate more than a certain amount of log,

The problem of long-running transaction is NOT that it generates too many log records, but in that it prevents the log of being truncated.

So even if there would be the way to limit log records generation per transaction, it could not save you from uncontrolled log growth.

It's enough to write begin tran update myTbl set col =... where key_field = ...
that will generate 8-10 log records (depending on server version), but then leave SSMS open and go to a vacation for one week, and if no one kills that open transaction ALL THE LOG generated from that moment will accumulate in a log file independently of recovery model. The log cannot be truncated in this case and it will grow accomodating all the log record generated during the week.


Need help with SQL query in Teradata I have a table with the following columns

member_number
step_count
social_activity -- (which contains both Challenges and Posts)
high_fives

Basically all the columns 2-4 are counts for members.

I want to create a report where I can display member_number, step_count, social_activity (where it is 'challenges'), social_activity (where it is 'posts') and high_fives.

I wrote the query

select member_number, step_count, social_activity
from ABC
group by member_number

But I want the query to get the separate count for social_activity ('challenges') and social_activity ('posts').

Can some one help me with this?

Seems like you need a conditional sum based on CASE:

select member_number,
   sum(case when social_activity = 'challenges' then step_count else 0 end) as challenges,
   sum(case when social_activity = 'posts' then step_count else 0 end) as posts
from ABC
group by member_number


Searching a big table for a few results without scanning the whole table I need to search a very big PostgreSQL table (500+M rows), I want to limit the returned search results, but using the "limit" keyword cant prevent the search over the whole set of data(is it correct?)

Imagine my search result contains 1M rows, but I need only the first 100 records in the search result! does PostgreSQL database have to create this 1M search result rows temporarily in memory and then give me my needed 100 results?

or is there any way to tell PostgreSQL to stop searching as soon as it find 100 records?

Here is my table, of course not populated with 500M records yet!

CREATE TABLE con
(
  id bigserial NOT NULL,
  tag1 integer NOT NULL DEFAULT 0,
  tag2 integer NOT NULL DEFAULT 0,
  ref1 integer NOT NULL DEFAULT 0,
  ref2 integer NOT NULL DEFAULT 0,
  CONSTRAINT con_pkey PRIMARY KEY (id)
)

and the explain analyze for a test query:

explain analyze SELECT * FROM con where tag1 = '64813' and tag2 = '80'

Seq Scan on con  (cost=0.00..3215204.72 rows=2470 width=112) (actual time=0.016..36970.528 rows=7505 loops=1)
   Filter: ((tag1 = 64813) AND (tag2 = 80))
  Rows Removed by Filter: 152519685
Total runtime: 36972.921 ms

but using the "limit" keyword cant prevent the search over the whole set of data(is it correct?)

Correct; it'll just limit how many results are returned.

For some kinds of query it can also limit how many are scanned in the first place, but you can't rely on that in the general case.

  does PostgreSQL database have to create this 1M search result rows temporarily in memory and then give me my needed 100 results?

Create, yes. In memory, not necessarily. It'll often spill to disk, or discard unneeded results as it goes so it only keeps the top 100. It depends on the details of the query.

  or is there any way to tell PostgreSQL to stop searching as soon as it find 100 records?

Write a query where that is possible. You haven't shown the query, or schema, so this part is impossible to usefully answer.

In general, if you have an ORDER BY on some kind of search relevance field then the whole data set must be searched then filtered for the top-n results.

In short: "It depends". Specifically, it depends on the schema, available indexes, and on exactly what you're searching for and what results you want.

This:

SELECT id
FROM mytable
WHERE somefield > 100
ORDER BY somefield DESC
LIMIT 100;

would only scan the needed rows if there was an index on mytable(somefield DESC).

This:

SELECT id, title
FROM mytable
WHERE title LIKE '%something%'
ORDER BY calculate_relevance(title, 'something')
LIMIT 100;

would always scan the whole table, both because an infix text pattern match (LIKE '%blah%') can't use a b-tree index, and because you can't create an expression index on a function taking a literal parameter like the case of the imaginary calculate_relevance function above.

So... it depends totally on what you're searching for and how.

Is this answer too hand-wavey and general for you? So's the question. Supply specifics, and you'll get more specific answers. A http://sqlfiddle.com/ of the schema with sample data, plus your PostgreSQL version, a table of the expected results, and a description of what you want to find is usually the minimum for this kind of thing.

Update: A few problems here.

There's no index on tag1 and/or tag2 so Pg must do a seqscan of the table, searching it until it finds 100 results. This is extremely inefficient.
There's no ORDER BY, so PostgreSQL can return whatever results it feels like for a LIMIT and return them in any order.
Your schema shows signs of being unnecessarily denormalized. tag1, tag2, etc usually suggest bad table design. I strongly recommend that you study relational database design and normalization.

In this specific case, you'd be able to avoid the full table scan if you CREATE INDEX con_tag1_tag2_idx ON con(tag1, tag2). But that'll only work for searches that use tag1 and optionally also tag2. It won't help (much) with searches for just tag2, searches for ref1, etc. (Thanks Erwin for the important note on multicolumn index use with the first column not included, it was a surprise to me).

Don't create lots of indexes. Each one uses disk space and takes disk I/O to insert, update and delete.

The answer is often proper normalization.

Overall I think you need to buy/borrow a couple of good introductory books on relational database design and basic to intermediate SQL, preferably PostgreSQL-focused, and do some study.


Mysql VS MariaDb Same query performance I have the same database on 2 servers. One is with Cpanel running mysql other one its CWP with mariadb. Both databases are identical, tables have the same index, mysql variables are identical but the performance its way different:

SELECT
    DISTINCT item_id
FROM
    site_plugin_products_cache_filters
WHERE
    value_id IN (32)

On MySql I get 1,939 rows (1.284 s)
On MaridDb I get 1,939 rows (0.097 s)

Any idea why this happens ?

When i get to sub-queries the differences are way bigger ( 0.01s vs 100+ seconds )

SELECT
    DISTINCT item_id
FROM
    site_plugin_products_cache_filters
WHERE
    value_id IN (32) AND
    item_id IN (
        SELECT item_id FROM
            site_plugin_products_cache_cats
        WHERE
            cat_id=1
    )

Or

SELECT  DISTINCT item_id  FROM site_plugin_products_cache_cats
WHERE cat_id IN (362)  AND item_id != 2519 ORDER BY rand()
LIMIT 4

4 rows (0.053 s)  vs 4 rows (0.103 s)

Seems WHM / CPanel comes by default with query_cache_size=0 and this was killing the performance. After set to an optimal value all its normal :)


How to use a view to span two tables that are identical We have table A that receives data from our shop floor and we currently have it so that the data is moved from table A to table B after 5 years. I want to streamline this but need help creating a view. I can change the stored procedure to move the data after one month, but need to be able to point to a view with the combined data of table A and B. I tried the below but get the following error: "Column name 'PARTNOAUX' in view or function 'Table_Full_' is specified more than once."

CREATE VIEW Table_Full AS
SELECT *
FROM [A].[dbo].[AUX] AS S
Inner Join [B].[dbo].[AUX]AS T
ON S.DATETIMEAUX=T.DATETIMEAUX;

You'll need to use a union instead. If your data is indeed distinct, use a union all for better performance -- the step to eliminate duplicates is eliminated.

create view table_full as
select *
from [A].[dbo].[AUX]
union all
select *
from [B].[dbo].[AUX];


Can't modify FormatString on cube ( SSAS ) I'm trying to create a cube, to know a little more about SSAS and as a DBA, be prepared for anything ( almost ).

When I'm changing SalesAmmount's FormatString to Currency, it doesn't change the values to currency. It keeps the big number values.

What am I missing? I tried to find some "currency language" or another option with no success.

( The Canada is a little motivation with my objective to move to Canada :) )

What you are seeing is that the new cube browser control in Data Tools doesn't support format strings.

OLAP clients are free to respect those formats or not, so some clients just don't respect the format string.

Testing in Excel should however display the formatted values correctly.


Why can't RDBM's cluster the way NoSQL does? One of the big plusses for nosql DBMS is that they can cluster more easily.  Supposedly with NoSQL you can create hundreds of cheap machines that store different pieces of data and query it all at once.

My question is this, why can't relational DBMS do this like mysql or sql server?  Is it that the vendors just haven't figured out a technical way to do this with their existing product, or is there some issue with the relational model that prevents this from being feasible?  What is so great about the NoSQL way of storing and accessing data (key/value, documents, etc) that makes clustering easier, if this is true at all?

Distributed Database Systems 101

Or, Distributed Databases - what the FK does 'web scale' actually mean?

Distributed database systems are complex critters and come in a number of different flavours.  If I dig in to the depths of my dimly remembered studies on this at university I'll try to explain some of the key engineering problems to building a distributed database system.

First, some terminology

ACID (Atomicity, Consistency, Isolation and Durability) properties: These are the key invariants that have to be enforced for a transaction to be reliably implemented without causing undesirable side effects.

Atomicity requires that the transaction complete or rollback completely.  Partially finished transactions should never be visible, and the system has to be built in a way that prevents this from happening.

Consistency requires that a transaction should never violate any invariants (such as declarative referential integrity) that are guaranteed by the database schema.  For example, if a foreign key exists it should be impossible to insert a child record with a reverence to a non-existent parent.

Isolation requires that transactions should not interfere with each other.  The system should guarantee the same results if the transactions are executed in parallel or sequentially.  In practice most RDBMS products allow modes that trade off isolation against performance.

Durability requires that once committed, the transaction remains in persistent storage in a way that is robust to hardware or software failure.

I'll explain some of the technical hurdles these requirements present on distributed systems below.

Shared Disk Architecture: An architecture in which all processing nodes in a cluster have access to all of the storage.  This can present a central bottleneck for data access.  An example of a shared-disk system is Oracle RAC or Exadata.

Shared Nothing Architecture: An architecture in which processing nodes in a cluster have local storage that is not visible to other cluster nodes.  Examples of shared-nothing systems are Teradata and Netezza.

Shared Memory Architecture: An architecture in which multiple CPUs (or nodes) can access a shared pool of memory.  Most modern servers are of a shared memory type.  Shared memory facilitates certain operations such as caches or atomic synchronisation primitives that are much harder to do on distributed systems.

Synchronisation: A generic term describing various methods for ensuring consistent access to a shared resource by multiple processes or threads.  This is much harder to do on distributed systems than on shared memory systems, although some network architectures (e.g. Teradata's BYNET) had synchronisation primitives in the network protocol.  Synchronisation can also come with a significant amount of overhead.

Semi-Join: A primitive used in joining data held in two different nodes of a distributed system.  Essentially it consists of enough information about the rows to join being bundled up and passed by one node to the other in order to resolve the join.  On a large query this could involve significant network traffic.

Eventual Consistency: A term used to describe transaction semantics that trade off immediate update (consistency on reads) on all nodes of a distributed system for performance (and therefore higher transaction throughput) on writes.  Eventual consistency is a side effect of using Quorum Replication as a performance optimisation to speed up transaction commits in distributed databases where multiple copies of data are held on separate nodes.

Lamport's Algorithm: An algorithm for implementing mutual exclusion (synchronisation) across systems with no shared memory.  Normally mutual exclusion within a system requires an atomic read-compare-write or similar instruction of a type normally only practical on a shared memory system.  Other distributed synchronisation algorithms exist, but Lamport's was one of the first and is the best known.  Like most distributed synchronisation mechanisms, Lamport's algorithm is heavily dependent on accurate timing and clock synchronisation beteen cluster nodes.

Two Phase Commit (2PC): A family of protocols that ensure that database updates involving multiple physical systems commit or roll back consistently.  Whether 2PC is used within a system or across multiple systems via a transaction manager it carries a significant overhead.

In a two-phase commit protocol the transaction manager asks the participating nodes to persist the transaction in such a way that they can guarantee that it will commit, then signal this status.  When all nodes have returned a 'happy' status it then signals the nodes to commit.  The transaction is still regarded as open until all of the nodes send a reply indicating the commit is complete.  If a node goes down before signalling the commit is complete the transaction manager will re-query the node when it comes back up until it gets a positive reply indicating the transaction has committed.

Multi-Version Concurrency Control (MVCC): Managing contention by writing new versions of the data to a different location and allowing other transactions to see the old version of the data until the new version is committed.  This reduces database contention at the expense of some additional write traffic to write the new version and then mark the old version as obsolete.

Election Algorithm: Distributed systems involving multiple nodes are inherently less reliable than a single system as there are more failure modes.  In many cases some mechanism is needed for clustered systems to deal with failure of a node.  Election algorithms are a class of algorithms used to select a leader to coordinate a distributed computation in situations where the 'leader' node is not 100% determined or reliable.

Horizontal Partitioning: A table may be split across multiple nodes or storage volumes by its key.  This allows a large data volume to be split into smaller chunks and distributed across storage nodes.

Sharding: A data set may be horizontally partitioned across multiple physical nodes in a shared-nothing architecture.  Where this partitioning is not transparent (i.e. the client must be aware of the partition scheme and work out which node to query explicitly) this is known as sharding.  Some systems (e.g. Teradata) do split data across nodes but the location is transparent to the client; the term is not normally used in conjunction with this type of system.

Consistent Hashing: An algorithm used to allocate data to partitions based on the key.  It is characterised by even distribution of the hash keys and the ability to elastically expand or reduce the number of buckets efficiently.  These attributes make it useful for partitioning data or load across a cluster of nodes where the size can change dynamically with nodes being added or dropping off the cluster (perhaps due to failure).

Multi-Master Replication: A technique that allows writes across multiple nodes in a cluster to be replicated to the other nodes.  This technique facilitates scaling by allowing some tables to be partitioned or sharded across servers and others to be synchronised across the cluster.  Writes must be replicated to all nodes as opposed to a quorum, so transaction commits are more expensive on a multi-master replicated architecture than on a quorum replicated system.

Non-Blocking Switch: A network switch that uses internal hardware parallelism to achieve throughput that is proportional to the number of ports with no internal bottlenecks.  A naive implementation can use a crossbar mechanism, but this has O(N^2) complexity for N ports, limiting it to smaller switches.  Larger switches can use more a complex internal topology called a non-blocking minimal spanning switch to achieve linear throughput scaling without needing O(N^2) hardware.

Making a distributed DBMS - how hard can it be?

Several technical challenges make this quite difficult to do in practice.  Apart from the added complexity of building a distributed system the architect of a distributed DBMS has to overcome some tricky engineering problems.

Atomicity on distributed systems: If the data updated by a transaction is spread across multiple nodes the commit/rollback of the nodes must be coordinated.  This adds a significant overhead on shared-nothing systems.  On shared-disk systems this is less of an issue as all of the storage can be seen by all of the nodes so a single node can coordinate the commit.

Consistency on distributed systems: To take the foreign key example cited above the system must be able to evaluate a consistent state.  For example, if the parent and child of a foreign key relationship could reside on different nodes some sort of distributed locking mechanism is needed to ensure that outdated information is not used to validate the transaction.  If this is not enforced you could have (for example) a race condition where the parent is deleted after the its presence is verified before allowing the insert of the child.

Delayed enforcement of constraints (i.e. waiting until commit to validate DRI) requires the lock to be held for the duration of the transaction.  This sort of distributed locking comes with a significant overhead.

If multiple copies of data are held (this may be necessary on shared-nothing systems to avoid unnecessary network traffic from semi-joins) then all copies of the data must be updated.

Isolation on distributed systems: Where data affected on a transaction resides on multiple system nodes the locks and version (if MVCC is in use) must be synchronised across the nodes.  Guaranteeing serialisability of operations, particularly on shared-nothing architectures where redundant copies of data may be stored requires a distributed synchronisation mechanism such as Lamport's Algorithm, which also comes with a significant overhead in network traffic.

Durability on distributed systems: On a shared disk system the durability issue is essentially the same as a shared-memory system, with the exception that distributed synchronisation protocols are still required across nodes.  The DBMS must journal writes to the log and write the data out consistently.  On a shared-nothing system there may be multiple copies of the data or parts of the data stored on different nodes.  A two-phase commit protocol is needed to ensure that the commit happens correctly across the nodes.  This also incurs significant overhead.

On a shared-nothing system the loss of a node can mean data is not available to the system.  To mitigate this data may be replicated across more than one node.  Consistency in this situation means that the data must be replicated to all nodes where it normally resides.  This can incur substantial overhead on writes.

One common optimisation made in NoSQL systems is the use of quorum replication and eventual consistency to allow the data to be replicated lazily while guaranteeing a certain level of resiliency of the data by writing to a quorum before reporting the transaction as committed.  The data is then replicated lazily to the other nodes where copies of the data reside.

Note that 'eventual consistency' is a major trade-off on consistency that may not be acceptable if the data must be viewed consistently as soon as the transaction is committed.  For example, on a financial application an updated balance should be available immediately.

Shared-Disk systems

A shared-disk system is one where all of the nodes have access to all of the storage.  Thus, computation is independent of location.  Many DBMS platforms can also work in this mode - Oracle RAC is an example of such an architecture.

Shared disk systems can scale substantially as they can support a M:M relationship between storage nodes and processing nodes.  A SAN can have multiple controllers and multiple servers can run the database.  These architectures have a switch as a central bottleneck but crossbar switches allow this switch to have a lot of bandwidth.  Some processing can be offloaded onto the storage nodes (as in the case of Oracle's Exadata) which can reduce the traffic on the storage bandwidth.

Although the switch is theoretically a bottleneck the bandwidth available means that shared-disk architectures will scale quite effectively to large transaction volumes.  Most mainstream DBMS architectures take this approach because it affords 'good enough' scalability and high reliability.  With a redundant storage architecture such as fibre channel there is no single point of failure as there are at least two paths between any processing node and any storage node.

Shared-Nothing systems

Shared-nothing systems are systems where at least some of the data is held locally to a node and is not directly visible to other nodes.  This removes the bottleneck of a central switch, allowing the database to scale (at least in theory) with the number of nodes.  Horizontal partitioning allows the data to be split across nodes; this may be transparent to the client or not (see Sharding above).

Because the data is inherently distributed a query may require data from more than one node.  If a join needs data from different nodes a semi-join operation is used to transfer enough data to support the join from one node to another.  This can result in a large amount of network traffic, so optimising the distribution of the data can make a big difference to query performance.

Often, data is replicated across nodes of a shared-nothing system to reduce the necessity for semi-joins.  This works quite well on data warehouse appliances as the dimensions are typically many orders of magnitude smaller than the fact tables and can be easily replicated across nodes.  They are also typically loaded in batches so the replication overhead is less of an issue than it would be on a transactional application.

The inherent parallelism of a shared-nothing architecture makes them well suited to the sort of table-scan/aggregate queries characteristic of a data warehouse.  This sort of operation can scale almost linearly with the number of processing nodes.  Large joins across nodes tend to incur more overhead as the semi-join operations can generate lots of network traffic.

Moving large data volumes is less useful for transaction processing applications, where the overhead of multiple updates makes this type of architecture less attractive than a shared disk.  Thus, this type of architecture tends not to be used widely out of data warehouse applications.

Sharding, Quorum Replication and Eventual Consistency

Quorum Replication is a facility where a DBMS replicates data for high availability.  This is useful for systems intended to work on cheaper commodity hardware that has no built-in high-availability features like a SAN.  In this type of system the data is replicated across multiple storage nodes for read performance and redundant storage to make the system resilient to hardware failure of a node.

However, replication of writes to all nodes is O(M x N) for M nodes and N writes.  This makes writes expensive if the write must be replicated to all nodes before a transaction is allowed to commit.  Quorum replication is a compromise that allows writes to be replicated to a subset of the nodes immediately and then lazily written out to the other nodes by a background task.  Writes can be committed more quickly, while providing a certain degree of redundancy by ensuring that they are replicated to a minimal subset (quorum) of nodes before the transaction is reported as committed to the client.

This means that reads off nodes outside the quorum can see obsolete versions of the data until the background process has finished writing data to the rest of the nodes.  The semantics are known as 'Eventual Consistency' and may or may not be acceptable depending on the requirements of your application but mean that transaction commits are closer to O(1) than O(n) in resource usage.

Sharding requires the client to be aware of the partitioning of data within the databases, often using a type of algorithm known as 'consistent hashing'.  In a sharded database the client hashes the key to determine which server in the cluster to issue the query to.  As the requests are distributed across nodes in the cluster there is no bottleneck with a single query coordinator node.

These techniques allow a database to scale at a near-linear rate by adding nodes to the cluster.  Theoretically, quorum replication is only necessary if the underlying storage medium is to be considered unreliable.  This is useful if commodity servers are to be used but is of less value if the underlying storage mechanism has its own high availability scheme (for example a SAN with mirrored controllers and multi-path connectivity to the hosts).

For example, Google's BigTable does not implement Quorum Replication by itself, although it does sit on GFS, a clustered file system that does use quorum replication.  BigTable (or any shared-nothing system) could use a reliable storage system with multiple controllers and partition the data among the controllers.  Parallel access would then be achieved through partitioning of the data.

Back to RDBMS platforms

There is no inherent reason that these techniques could not be used with a RDBMS.  However lock and version management would be quite complex on such a system and any market for such a system is likely to be quite specialised.  None of the mainstream RDBMS platforms use quorum replication and I'm not specifically aware of any RDBMS product (at least not one with any significant uptake) that does.

Shared-disk and shared-nothing systems can scale up to very large workloads.  For instance, Oracle RAC can support 63 processing nodes (which could be large SMP machines in their own right) and an arbitrary number of storage controllers on the SAN.  An IBM Sysplex (a cluster of zSeries mainframes) can support multiple mainframes (each with substantial processing power and I/O bandwidth of their own) and multiple SAN controllers.  These architectures can support very large transaction volumes with ACID semantics, although they do assume reliable storage.  Teradata, Netezza and other vendors make high-performance analytic platforms based on shared-nothing designs that scale to extremely large data volumes.

So far, the market for cheap but ultra-high volume fully ACID RDBMS platforms is dominated by MySQL, which supports sharding and multi-master replication.  MySQL does not use quorum replication to optimise write throughput, so transaction commits are more expensive than on a NoSQL system.  Sharding allows very high read throughputs (for example Facebook uses MySQL extensively), so this type of architecture scales well on read-heavy workloads.

An interesting debate

BigTable is a shared-nothing architecture (essentially a distributed key-value pair) as pointed out by Michael Hausenblas below.  My original evaluation of it included the MapReduce engine, which is not a part of BigTable but would normally be used in conjunction with it in its most common implementations (e.g. Hadoop/HBase and Google's MapReduce framework).

Comparing this architecture with Teradata, which has physical affinity between storage and processing (i.e. the nodes have local storage rather than a shared SAN) you could argue that BigTable/MapReduce is a shared disk architecture through the globally visible parallel storage system.

The processing throughput of a MapReduce style system such as Hadoop is constrained by the bandwidth of a non-blocking network switch.1  Non-blocking switches can, however, handle large bandwidth aggregates due to the parallelism inherent in the design, so they are seldom a significant practical constraint on performance.  This means that a shared disk architecture (perhaps better referred to as a shared-storage system) can scale to large workloads even though the network switch is theoretically a central bottleneck.

The original point was to note that although this central bottleneck exists in shared-disk systems, a partitioned storage subsystem with multiple storage nodes (e.g. BigTable tablet servers or SAN controllers) can still scale up to large workloads.  A non-blocking switch architecture can (in theory) handle as many current connections as it has ports.

1 Of course the processing and I/O throughput available also constitutes a limit on performance but the network switch is a central point through which all traffic passes.


Does AWS Aurora suffer from the same limitations that MySQL suffer from? Specifically, MySQL has a limitations max row size as 64kB. I would like to know if Amazon Aurora, which is supposedly a drop in replacement for MySQL suffer the same limitation.

There is no limitation of 64KB on the size of a MySQL row.  There are limitations, but almost never does anyone hit them.  Can you point to where you got that limit?

There are a couple of obscure limits in InnoDB, such as having dozens of big VARCHARs, but they are easily worked around.

Here are the main limits for MySQL:  http://mysql.rjweb.org/doc.php/limits

Edit

At first glance the manual page on row size says there is a 64K limit on row size, but reading further it says "BLOB and TEXT columns count from one to four plus eight bytes each toward the row-size limit because their contents are stored separately from the rest of the row."  Since LONGBLOB and LONGTEXT columns can contain up to 4GB each, the maximum total row size (including such columns) is at least 4GB.

Furthermore, "stored separately" is dependent on the ENGINE involved; that quote does not cover all the cases.

The bottom line is that it is possible to create a table with more than 64KB of data.  (I don't know about exceeding 4GB.)  Doing so may require using TEXT instead of VARCHAR(65535) (which is virtually identical in semantics), etc.

I have made many tables (MyISAM and InnoDB) where the "row size" was bigger than 64KB, both in definition and in fact.  I have multiple cases of MEDIUMTEXT or MEDIUMBLOB that work fine, back at least as far as version 4.0.


Automatically schema backup in Oracle I am working with an Oracle Database and I have a user (called XY) with permissions R/W. I would like to to do automatic backups of the user schema XY. I don't own the server and I don't know if the provider does backup of my schema. Probably does,but I would like to do myself the automatic Backup. I use as Oracle Client the tool SQL Developer.

I know that I can do Extras-> Database Copy but it is not automatic.

Can I achieve this with this client? If so, how?If it is not possible, what alternative do I have?

Thanks!

If you will have access to server's filesystem, or you can ask for this, you can use crontab utility. To verify whether you have access to cron, type in the following

crontab -e

If you will have an OS user,and if you can use the crontab utility to schedule a job, you can try something like this:

*/30 * * * * ~/bin/dump.bsh 1>~/bin/log/dump.log 2>&1

This simple entry make a logical backup every half hour.
The dump.bsh file:

#!/bin/bash
PATH=/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:$ORACLE_HOME/bin
. $HOME/.bash_profile

now=`date +"%Y-%m-%d_%H-%M"`
expdp user/passwd directory=data_pump_dir dumpfile=lbackup$now.dmp schemas=schema_to_backup

You need to set Oracle env variables (here in the .bash_profile) since cron passes only a minimal set of env variables to your jobs.

Otherwise, go and have a look at this SQL Developer page. In the exports (expdp) section there is also a reference to automatic jobs.


Round robin vs Demand Parallelism partitioning This is related to having a common key driver on top of the query with a nested loops, and the parallelism for the rows coming out of driver being either demand type or round robin type. I would have assumed demand partitioning would perform better but I get opposite results.

I started the queries within the same try at the same time in SQL Server. When the queries running I monitored dm_exec_query_profiles dmv constantly. I noticed the Round robin versions start a lot faster, they are inserting a lot more rows a lot faster into the Table Insert portion in the dmv also they pick up a lot more rows quicker from the driver side parallelism portion. Thinking logically demand partitioning should be more advantageous since our SQL server is usually over 50-60% cpu, litespeed backups running, has 64 cores etc. I was able to balance rows processed on threads a lot better with round robin partitioning but also the data within partitions are so out of balance I noticed some threads in demand partitioning only processes 1 record from the driver whereas the average records from the driver is around 196. With the demand partitioning I order the rows within partitions descending vs in round robin I try to balance the rows better.

Should I always use round robin instead, why does round robin starts processing rows a lot faster than demand partitioning, can I do more optimization for demand partitioning?

The query plans are in the One Drive link, I couldn't find another way of posting them here (pastetheplan only accepts xml, which does not capture the extra information like wait stats & duration captured by Plan Explorer).

  CompareRoundRobinToDemand_DM_2_5114.pesession
   CompareRoundRobinToDemand_RRB_2_3046.pesession

started at the same time, Round Robin was considerably faster

  CompareRoundRobinToDemand_DM_3_5228.pesession
   CompareRoundRobinToDemand_RRB_3_4367.pesession

started at the same time, Round Robin was faster again

  CompareRoundRobinToDemand_DM_4_4813.pesession
   CompareRoundRobinToDemand_RRB_4_3577.pesession

started at the same time, Round Robin was faster again

Thank you in advance.

Query Plans

I am able to balance round robin rows almost perfect whereas for demand I just order the driver in rows descending order, balancing the rows in demand is an option in my procedure but did not produce better results. In my observation demand performs better when overall CPU usage is less and Round robin performs better when server is busier. I noticed demand creates one extra thread compared to round robin and also overall cpu utilization in demand is slightly higher than similar RRB version.

i am aware of imbalance of row distribution in demand. the memory grant is also on purpose, it's not using that much memory at the end. the first 16 records passed from driver is the same in demand vs round robin, but round robin starts processing them a lot faster for some reason. I want to understand why. I also want to understand when it's more beneficial to use demand or round robin. It seems like when server is idle demand works faster and when server has existing load round robin is faster, thats an observation so far, does that have a basis I dont know.

Should I always use round robin instead

Only if you find it produces "better" results for your particular workload, or for particular queries within that workload.

In general, I would say no.  My personal experience is that Demand (D) partitioning tends to have fewer problems than Round Robin (RR). The particular plan you shared does not seem to have any of the more common features that can cause performance problems with RR, but it is tough to assess at a distance.

  why does round robin starts processing rows a lot faster than demand partitioning

If I were to hazard a guess, I would say the D partitioning threads are checking and yielding the processor more frequently. A thread that uses less of its quantum on average (by checking and yielding more often) is a good scheduling citizen, but it may be punished for that good behaviour (before SQL Server 2016). The screenshot you shared does seem to show the D plan getting less CPU time than the concurrent RR execution.

D pulls single rows across the exchange whereas RR pushes whole packets - and yield checks occur at exchanges (among many other places). A very detailed analysis would be required to confirm this (or any other) theory.

  can I do more optimization for demand partitioning?

The plan seems reasonable enough from what I can see.

It is interesting that the screenshot shows a merge join whereas the uploaded plans all use hash join. Trying a co-located merge join was one of the first suggestions that came to mind - as explained in my article Improving Partitioned Table Join Performance.

You could also check on thread distribution across schedulers and perhaps experiment with different thread placement strategies. I would certainly be checking that the D plan was allocating threads as optimally as the RR version (e.g. no concurrent same-branch threads sharing the same scheduler).

Perhaps also look at presenting the demand rows in a different order. What you have now may be based on a sensible expectation, but that's not to say it is necessarily optimal.

In any case, I would be very wary of drawing general conclusions about D vs RR based on this one example.

  I noticed demand creates one extra thread compared to round robin

This is an illusion due to a timing difference. In the RR plan, the Index Scan completes very quickly, with its rows stuffed into packets on the exchange. The thread running the scan thus terminates quickly.

In the D plan, individual rows are pulled on demand across the exchange and fully processed before a new row is pulled across. So, the Constant Scan thread hangs around for longer.

Both plans have two concurrent branches, with DOP (16) threads reserved for each. Both use 17 threads as reported in the ThreadStat showplan element (16 for the main branch, 1 for the serial index scan/constant scan).


How to connect each SQL instance to get SQL server version updated into the temp table? -- Trying to get SQL Server verion with CMS information. Run this in SQLCMD mode

-- Create temp table for Instance
CREATE TABLE #Instance_list(Server_Group_Name nvarchar(12), Server_Name nvarchar(50), Instance_Description nvarchar(512))
GO
-- Get Instance list from CMS
INSERT INTO #Instance_list
SELECT CONVERT(nvarchar(12), groups.name) AS Server_Group_Name
     ,CONVERT(nvarchar(50), svr.server_name) AS Server_Name
     , CONVERT(nvarchar(512),svr.description) as Instance_Description

FROM msdb.dbo.sysmanagement_shared_server_groups_internal groups
INNER JOIN msdb.dbo.sysmanagement_shared_registered_servers_internal svr
ON groups.server_group_id = svr.server_group_id
GO
-- Add SQL version column
ALTER TABLE  #Instance_list ADD Instance_Version nvarchar(512)
-- Loop each instance
DECLARE @Instance nvarchar(50)
DECLARE Instance_List_cursor CURSOR FOR
SELECT [Server_Name] from #Instance_list
OPEN Instance_List_cursor

 FETCH NEXT FROM Instance_List_cursor
 INTO @Instance

 WHILE @@FETCH_STATUS = 0
 BEGIN
 -- How to connect each SQL instance to get SQL server version updated in to the temp table?
PRINT @Instance
DECLARE @SQLVERSION nvarchar(512)
--:Connect @Instance

SET @SQLVERSION = (SELECT @@VERSION FROM sys.dm_os_sys_info)
PRINT @SQLVERSION
--:CONNECT CMS_INSTANCE
-- UPDATE #Instance_list SET [Instance_Version] = @SQLVERSION WHERE Server_Name = @Instance

 FETCH NEXT FROM Instance_List_cursor INTO @Instance
 END

CLOSE Instance_List_cursor
DEALLOCATE Instance_List_cursor

SELECT * FROM #Instance_list
GO

DROP TABLE #Instance_list

How to connect each SQL instance to get SQL server version updated into the temp table?

Thanks in advanced.

Just use dbatools - Get-DbaCmsRegServerGroup

  Gets list of Server Groups objects stored in SQL Server Central Management Server (CMS).

If you have multiple servers to connect, you can use foreach to loop through each servers and store the result into a variable.

You can even write to a database using ConvertTo-DbaDataTable and Write-DbaDataTable


Should I create an enumeration or individual flags? I'm creating a new database table which will be used to store pictures of products. I'd like to identify the product "type" represented in the picture.  Say we have 3 general categories for all the products:

tables,
chairs,
and lamps.

Am I better off creating 3 boolean columns, is_table, is_chair, and is_lamp?

Or am I better off creating an enumeration, and then using a single column for that enum:

CREATE TYPE product_type AS ENUM ('table', 'chair', 'lamp' );

(Note that with the engineering product pictures we're storing, it is guaranteed a product/picture will never qualify as 2 types.  It must always be 1 very specific type.)

You should normalize this and create a reference table, that way you can use a simple data type for the type indicator and also be future proof if you need to add types. It also makes for easier queries, plus this structure will maintain the constraint of each product only being of one type at a time.

so (assuming this is an Oracle db):

   Create table Product_types
   (id int,
    name varchar2(100),
    constraint pk_product_types primary key  (id)
    );

create table products
    (id int,
     product_type int,
     somecolumn varchar2(100),
     anothercolumn varchar2(100),
     somedatecolumn date,
     constraint fk_products_product_type foreign key (product_type) references product_types(id)
     );


What is the right way to manage users data? I'm trying to create a simple register login script with user profiles, I created a table with the following structure:

+------------+---------------+------+-----+---------+----------------+
| Field      | Type          | Null | Key | Default | Extra          |
+------------+---------------+------+-----+---------+----------------+
| id         | int(11)       | NO   | PRI | NULL    | auto_increment |
| username   | varchar(32)   | NO   |     | NULL    |                |
| password   | varchar(32)   | NO   |     | NULL    |                |
| first_name | varchar(50)   | NO   |     | NULL    |                |
| last_name  | varchar(50)   | NO   |     | NULL    |                |
| email      | varchar(1024) | NO   |     | NULL    |                |
| active     | int(1)        | NO   |     | 0       |                |
+------------+---------------+------+-----+---------+----------------+

How should I store other data like:

country, user level, registration date, domain name, email activation key, last login date, and so on ...

I have 2 options (maybe there are more I'm not aware of):

creating these as columns in this table
creating another table that is related by the user id

My questions are:

which one is better if later I want to add more data to be stored ?
If I take the first approach with one table is there going to be a problem when adding more columns later if we already have active users ?
and if we take the second approach how should I structure the data table ?

And thank you.

UPDATE:

What do you think about the WordPress approach in this matter ? WordPress has a usermeta table with this structure ?

+------------+---------------------+------+-----+---------+----------------+
| Field      | Type                | Null | Key | Default | Extra          |
+------------+---------------------+------+-----+---------+----------------+
| umeta_id   | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| user_id    | bigint(20) unsigned | NO   | MUL | 0       |                |
| meta_key   | varchar(255)        | YES  | MUL | NULL    |                |
| meta_value | longtext            | YES  |     | NULL    |                |
+------------+---------------------+------+-----+---------+----------------+

So what do you think about this way of storing users data ? should this be considered in an app that will have lots of users ?

This is a good "there's more than one way to do it" question.

Start by thinking about your data usage:

Think about which fields are going to be read and written more often.

For example, first name and last name are probably written once and are rarely changed, but depending on your app, they could be read very often.

Last login date could be written frequently, and you might want to keep every login date.

Things like activation key and registration date, depending on how authentication works in your app, might be written once and then very rarely read.

You should also think about what data is pulled at the same time, and try to denormalize to make those reads faster. For example, if you are always pulling username and password and first name together (but no other contact info), put firstname in the login table, too. That is, think about avoiding joins if this data will be large, and be kind to yourself.

Also think about which fields might be in the "where" clause of your queries, and index on those fields.

Here's one way I might do it:

Contact Table
(one insert, rare updates, many reads)

+------------+---------------+------+-----+---------+----------------+
| Field      | Type          | Null | Key | Default | Extra          |
+------------+---------------+------+-----+---------+----------------+
| user_id    | int(11)       | NO   | PRI | NULL    | auto_increment |
| first_name | varchar(50)   | NO   |     | NULL    |                |
| last_name  | varchar(50)   | NO   |     | NULL    |                |
| address    | varchar(50)   | NO   |     | NULL    |                |
| city       | varchar(50)   | NO   |     | NULL    |                |
| state      | varchar(50)   | NO   |     | NULL    |                |
| country    | varchar(50)   | NO   |     | NULL    |                |
| email      | varchar(1024) | NO   |     | NULL    |                |
+------------+---------------+------+-----+---------+----------------+

Login Table
(one insert, maybe updates, many reads; need first_name to be echoed on login)

+------------+---------------+------+-----+---------+----------------+
| Field      | Type          | Null | Key | Default | Extra          |
+------------+---------------+------+-----+---------+----------------+
| user_id    | int(11)       | NO   | PRI | NULL    |                |
| username   | varchar(32)   | NO   |     | NULL    |                |
| password   | varchar(32)   | NO   |     | NULL    |                |
| first_name | varchar(50)   | NO   |     | NULL    |                |
+------------+---------------+------+-----+---------+----------------+

Login History Table
(many writes, multiple entries per user_id)

+------------+---------------+------+-----+---------+----------------+
| Field      | Type          | Null | Key | Default | Extra          |
+------------+---------------+------+-----+---------+----------------+
| user_id    | int(11)       | NO   | MUL | NULL    |                |
| last_login | timestamp     | NO   | MUL | NULL    |                |
+------------+---------------+------+-----+---------+----------------+

Signup Table
(one write, rarely read)

+------------------+---------------+------+-----+---------+----------------+
| Field            | Type          | Null | Key | Default | Extra          |
+------------------+---------------+------+-----+---------+----------------+
| user_id          | int(11)       | NO   | PRI | NULL    |                |
| registration_date| timestamp     | NO   |     | NULL    |                |
| activation_key   | varchar(50)   | NO   |     | NULL    |                |
+------------------+---------------+------+-----+---------+----------------+


Insert in to table without duplicate records TSQL Team,
Need some assistance with TSQL
table1
ID |Name| pin
1    a     101
2    b     201
3    c     301
4    d     401

table 2
ID|Name|pin
3   c   301
4   d   401
5   e   501
6   f   601

I want to insert to table1 from table2 only those values which are not duplicates (for all columns in table 2).
Final output in table1
ID | Name | pin
1    a      101
2    b      201
3    c      301
4    d      401
5    e      501
6    f      601

Appreciate your response. Thanks

INSERT INTO table1 (ID, Name, pin)
SELECT table2.ID, table2.Name, table2.pin
FROM table2
LEFT JOIN table1 ON t1.Name=t2.Name
WHERE t1.Name IS NULL

or
INSERT INTO table1 (ID, Name, pin)
SELECT ID, Name, pin
FROM table2
WHERE NOT EXISTS ( SELECT NULL
                   FROM table1
                   WHERE t1.Name=t2.Name )

If table1.ID value is an identity and must not be inserted but generated then replace ID in SELECT with NULL.


I run sp_who on the database, and there is a "TASK MANAGER" process. What is it? I have a sporadic problem, when upgrading SQL Server in a single-user mode, some other application somehow logs into the database and kicks my process out.

Last time when that happened, I ran sp_who, and I could see that a process that took control of the database is "TASK MANAGER". EDIT By "took control" I mean that my process crashed with exception Database '...' is already open and can only have one user at a time. while it was running database upgrade code. SqlConnection was NOT closed or disposed.

Anyone could tell me what is this process, what is it's purpose, and how in the world it could get into a database, which is in single-user mode, and there is an active connection?

Any SPIDs below 50 are system processes. "TASK MANAGER" is one of these. I'm not sure exactly what it does but it is initiated only by the server itself, dealing with system level jobs. Seeing this on the database "sp_who" is nothing to worry about.

I highly doubt this process is the reason why you are getting kicked out of the DB running in single user mode, but if you are curious what the process might be doing you could run dbcc inputbuffer(the_spid_of_process) to check if it is running any commands.


PostgreSQL surrogate keys: use sequence/bigint or OID? I'm looking at a need for surrogate keys in tables I'm designing on a PostgreSQL-derived DBMS (Citus). Would OIDs suffice? Is there a downside to using them instead of creating a bigint field and a sequence?

There are a couple of misunderstandings floating around here.

OIDs were included with every row by default in very old versions of Postgres. The default was soon changed to not include OID columns in user-defined tables.

Quoting the Postgres 8.1 documentation:

  default_with_oids (boolean)

  This controls whether CREATE TABLE and CREATE TABLE AS include an OID
  column in newly-created tables, if neither WITH OIDS nor WITHOUT OIDS
  is specified. It also determines whether OIDs will be included in
  tables created by SELECT INTO. In PostgreSQL 8.1 default_with_oids is
  disabled by default; in prior versions of PostgreSQL, it was on by default.

Bold emphasis mine. What @Shire quoted is related, but OIDs are not even considered for a PK in modern Postgres.

The obvious reason why @a_horse (correctly) suggested bigserial (and not just serial) is in the question:

  Is there a downside to using them instead of creating a bigint field and a sequence?

Bold emphasis mine again. Typically, a serial column (implemented with a 4-byte integer) is good enough for most tables - unless you expect more than 2 billion (2^31 - 1 = 2.147.483.647) rows over the lifetime of the table.


Improve sql server lookup table performance We have a sql server database (on azure) with one large table c1billion rows. Each row consists of a key char32 which is an md5 hash and a value int. All queries select multiple values from the table using a key in clause. Sometimes we have 1000+ items in the in clause and the query can be very slow.
What are the top things we can do to improve query performance in terms of table structure (partitioning?) and query structure (maybe chunking queries)?

I assume you mean a SQL Server instances hosted in an Azure VM (as opposed to a SQL Azure instance).

Use BINARY(16) for MD5 hash. Is half the size of CHAR(32)
Must have a clustered index on the hash
Choose an appropriate hosting plan. Read Premium Storage: High-Performance Storage for Azure Virtual Machine Workloads
Follow best practices. Read Performance Guidance for SQL Server in Azure Virtual Machines and Performance Best Practices for SQL Server in Azure Virtual Machine

Other than that, approach this as any other performance investigation. Measure. Read How to analyse SQL Server performance. The solution will depend a lot on what your measurements find.


Over use/correct use of schemas? Having asked This question on Stackoverflow, I wondered where what I have done is correct/best practise.

Basically, every object that I create is going into a schema with the schema name reflecting a usage. For example, I have the schemas Audit and Admin (amongst others).

This in turn leaves no objects in dbo. Is this ok? Is there anything else that I need to do?

Schemas are not only a great security tool (which is reason enough to use them), but they are also perfect for logical separation.  And it seems as though this is what you are practicing.

Even if the current requirement doesn't need special security, say down the road all of the database objects that are related to Auditing should be secure to a database role.  If these objects were scattered throughout the dbo schema, then you would have to explicitly deny permissions on the individual objects.  But with the Audit schema, you do a single deny and you're set.

I personally practice the use of schemas.  Like all things in databases, though, there is a happy medium.  I wouldn't create a schema for each granular aspect of the data layer.  There is such thing as too many schemas and separation.  But I'm guessing you aren't anywhere close to that.


Can I remove "/*!50100 PARTITION BY HASH..." to success with import? I'm trying to import a database and get this message (Swedish lang phpMyadmin):

As you see, I get the message:

  #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AUTO_INCREMENT=140059' at line 10

So I guess the problem is:

/*!50100 PARTITION BY HASH ( `feeditemsID` + YEAR(`feeddate`)) PARTITIONS 3 */

That is right before the auto_increment code. Here is the whole code:

CREATE TABLE IF NOT EXISTS `feeditems` (
   `feeditemsID` int(10) unsigned NOT NULL AUTO_INCREMENT,
   `city` varchar(100) DEFAULT NULL,
   `companyID` int(10) unsigned NOT NULL DEFAULT '0',
   `fucID` int(11) NOT NULL DEFAULT '0',
   `feedID` int(10) unsigned NOT NULL DEFAULT '0',
   `title` varchar(255) NOT NULL,
   `description` text NOT NULL,
   `url` text NOT NULL,
   `feeddate` datetime NOT NULL,
   `region` varchar(100) DEFAULT NULL,
   `jobtype` int(11) NOT NULL DEFAULT '0',
   `jobinfomisc` text NOT NULL,
   `joblastdate` varchar(255) NOT NULL,
   `jobcontactname` varchar(255) NOT NULL,
   `jobcontactphone` varchar(255) NOT NULL,
   `jobcontactmail` varchar(255) NOT NULL,
   `jobcontactinfo` text NOT NULL,
   `jobworkers` varchar(255) NOT NULL,
   `jobActive` int(1) NOT NULL DEFAULT '0',
   `logo` varchar(50) DEFAULT NULL,
   `infokus` int(1) NOT NULL DEFAULT '0',
   `deleted` int(1) NOT NULL DEFAULT '0',
   PRIMARY KEY (`feeditemsID`,`feeddate`),
   KEY `deleted` (`deleted`),
   KEY `companyID` (`companyID`),
   KEY `feedID` (`feedID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH ( `feeditemsID` + YEAR(`feeddate`))
PARTITIONS 3 */ AUTO_INCREMENT=331451 ;

So the question is, can I just remove that part? Or what can be wrong? I'm not that good at stuffs like this. Would be happy for help here.

When code starts with something like this

/*!50100

the code following till */ is executed only, when MySQL is installed in a version above 5.0.100

The error here is, that you're mixing table options with partition options.

From the manual:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]
...

table_option:
    ENGINE [=] engine_name
  | AUTO_INCREMENT [=] value
  | AVG_ROW_LENGTH [=] value
  | [DEFAULT] CHARACTER SET [=] charset_name
...

partition_options:
    PARTITION BY
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY(column_list)
        | RANGE(expr)
        | LIST(expr) }
    [PARTITIONS num]
    [SUBPARTITION BY
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY(column_list) }
      [SUBPARTITIONS num]
    ]
    [(partition_definition [, partition_definition] ...)]

So, try it like this:

CREATE TABLE IF NOT EXISTS `feeditems` (
   `feeditemsID` int(10) unsigned NOT NULL AUTO_INCREMENT,
   `city` varchar(100) DEFAULT NULL,
   `companyID` int(10) unsigned NOT NULL DEFAULT '0',
   `fucID` int(11) NOT NULL DEFAULT '0',
   `feedID` int(10) unsigned NOT NULL DEFAULT '0',
   `title` varchar(255) NOT NULL,
   `description` text NOT NULL,
   `url` text NOT NULL,
   `feeddate` datetime NOT NULL,
   `region` varchar(100) DEFAULT NULL,
   `jobtype` int(11) NOT NULL DEFAULT '0',
   `jobinfomisc` text NOT NULL,
   `joblastdate` varchar(255) NOT NULL,
   `jobcontactname` varchar(255) NOT NULL,
   `jobcontactphone` varchar(255) NOT NULL,
   `jobcontactmail` varchar(255) NOT NULL,
   `jobcontactinfo` text NOT NULL,
   `jobworkers` varchar(255) NOT NULL,
   `jobActive` int(1) NOT NULL DEFAULT '0',
   `logo` varchar(50) DEFAULT NULL,
   `infokus` int(1) NOT NULL DEFAULT '0',
   `deleted` int(1) NOT NULL DEFAULT '0',
   PRIMARY KEY (`feeditemsID`,`feeddate`),
   KEY `deleted` (`deleted`),
   KEY `companyID` (`companyID`),
   KEY `feedID` (`feedID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=331451
/*!50100 PARTITION BY HASH ( `feeditemsID` + YEAR(`feeddate`))
PARTITIONS 3 */;


Azure SQL | Granting Security Groups DB Role permissions I am trying to grant a Security Group within our company's Azure environment that is found in Azure AD. I have successfully granted users' rights to the db_datareader, writer, owner roles. But I am trying to add a Group to one of these roles and the error I get below.
I am an AD Admin of the SQL Server in Azure, and I confirmed that the Security Group is synced and found in our Azure AD environment. I have also confirmed running the commands below against the actual database and the master db.
I know that within Azure SQL you can run the T-SQL command to add a user by their UPN, this works fine:
CREATE USER [bob@contoso.com] FROM EXTERNAL PROVIDER;
ALTER ROLE [db_datawriter] ADD MEMBER [bob@contoso.com];

But when running the below it provides an error
ALTER ROLE [db_datawriter] ADD MEMBER [groupname@contoso.com];

Msg 15151, Level 16, State 1, Line 1
Cannot add the principal 'groupname@contoso.com', because it does not exist or you do not have permission.

Do I also need to create a Group for the Sec Group as well? As in:
CREATE GROUP [groupname@contoso.com] FROM EXTERNAL PROVIDER; -- "GROUP" is not correct syntax.

or
CREATE USER [groupname@contoso.com] FROM EXTERNAL PROVIDER;

This article did not provide any details I could find on this topic but adding users.

After more reviewing of this question, I was able to find that the syntax to add Security Groups in Azure SQL is below
CREATE USER [Group Display Name] FROM EXTERNAL PROVIDER;

You can confirm with one of Microsoft's articles
To create a contained database user representing an Azure AD or federated domain group, provide the display name of a security group:
CREATE USER [ICU Nurses] FROM EXTERNAL PROVIDER;
And here is a second article that covers this topic. There is no CREATE GROUP syntax. To add a Sec Group it's done by using the CREATE USER syntax.
After which, you can proceed to run the ALTER ROLE command and use the display name of the Group to add the Group to the database role.


SQL server CPU 100% - Turn on Optimize for Adhoc Workload Experiencing high CPU the more connections there are, no blocking or long running queries on the server.

Highest wait types are:
SOS_SCHEDULER_YIELD & CX_PACKET

Cache Usage %:

UsrTab   0.01
Default  0.01
Rule     0.04
Trigger  0.06
Prepared 0.64
Proc     0.7
View     2.05
Adhoc    95.65

Adhoc queries are excessive and have seemed to increase since use of ORM functionality has increased.

I would like to turn on  Optimize for Adhoc Workload but want to know the pitfalls, also what are the implications of turning this on in a production environment?

Will enabling this option help reduce CPU load?

You can turn on "Optimize for Adhoc Workloads" with a high confidence.

The only potential effect this has, is when a plan is compiled the second time, it takes a few microseconds longer. This is because the first time a plan is cached, it only records the stub.

After that, the full plan is already cached, and you'll see the benefits.

Aside: if you're seeing high CPU as a result of a new OR/M, I'd look at something called implicit conversion.

What happens is that on string data types (CHAR, VARCHAR, NCHAR, NVARCHAR), you might be seeing Unicode vs ASCII conversions between the database and your application code.

The best way to look for this is to make sure your ORM matches the string data types correctly. If it's VARCHAR in the database, make sure the ORM doesn't submit parameters as NVARCHAR.

More reading here: Convert Implicit and the related performance issues with SQL Server.


Are there DBMS that allow a Foreign Key that References a View (and not only base tables)? Inspired by a Django modeling question: Database Modeling with multiple many-to-many relations in Django. The db-design is something like:

CREATE TABLE Book
( BookID INT NOT NULL
, BookTitle VARCHAR(200) NOT NULL
, PRIMARY KEY (BookID)
) ;

CREATE TABLE Tag
( TagID INT NOT NULL
, TagName VARCHAR(50) NOT NULL
, PRIMARY KEY (TagID)
) ;

CREATE TABLE BookTag
( BookID INT NOT NULL
, TagID INT NOT NULL
, PRIMARY KEY (BookID, TagID)
, FOREIGN KEY (BookID)  REFERENCES Book (BookID)
, FOREIGN KEY (TagID)   REFERENCES Tag (TagID)
) ;

CREATE TABLE Aspect
( AspectID INT NOT NULL
, AspectName VARCHAR(50) NOT NULL
, PRIMARY KEY (AspectID)
) ;

CREATE TABLE TagAspect
( TagID INT NOT NULL
, AspectID INT NOT NULL
, PRIMARY KEY (TagID, AspectID)
, FOREIGN KEY (TagID)   REFERENCES Tag (TagID)
, FOREIGN KEY (AspectID)  REFERENCES Aspect (AspectID)
) ;

and the issue is how to define the BookAspectRating table and to enforce referential integrity, so one cannot add a rating for a (Book, Aspect) combination that is invalid.

AFAIK, complex CHECK constraints (or ASSERTIONS) that involve subqueries and more than one table, that could possibly solve this, are not available in any DBMS.

Another idea is to use (pseudocode) a view:

CREATE VIEW BookAspect_view
  AS
SELECT DISTINCT
    bt.BookId
  , ta.AspectId
FROM
    BookTag AS bt
  JOIN
    Tag AS t  ON t.TagID = bt.TagID
  JOIN
    TagAspect AS ta  ON ta.TagID = bt.TagID
WITH PRIMARY KEY (BookId, AspectId) ;

and a table that has a Foreign Key to the above View:

CREATE TABLE BookAspectRating
( BookID INT NOT NULL
, AspectID INT NOT NULL
, PersonID INT NOT NULL
, Rating INT NOT NULL
, PRIMARY KEY (BookID, AspectID, PersonID)
, FOREIGN KEY (PersonID)   REFERENCES Person (PersonID)
, FOREIGN KEY (BookID, AspectID)
    REFERENCES BookAspect_view (BookID, AspectID)
) ;

Three questions:

Are there DBMS that allow a (possibly materialized) VIEW with a PRIMARY KEY?
Are there DBMS that allow a FOREIGN KEY that REFERENCES a VIEW (and not only a base TABLE)?
Could this integrity problem be solved otherwise - with available DBMS features?

Clarification:

Since there is probably no 100% satisfying solution - and the Django question is not even mine! - I'm more interested in a general strategy of possible attack on the problem, not a detailed solution. So, an answer like "in DBMS-X this can be done with triggers on table A" is perfectly acceptable.

In Oracle, one way to enforce this sort of constraint in a declarative fashion would be to create a materialized view that is set to refresh fast on commit whose query identifies all the invalid rows (i.e. BookAspectRating rows that have no match in BookAspect_view).  You can then create a trivial constraint on that materialized view that would be violated if there are any rows in the materialized view.  This has the benefit of minimizing the amount of data that you have to duplicate in the materialized view.  It can cause problems, however, since the constraint is only enforced at the point that you're committing the transaction-- many applications aren't written to expect that a commit operation might fail-- and because the constraint violation can be somewhat hard to associate with a particular row or a particular table.


Query to determine if items have tax I have the following tables

Item

ID
Name

Item taxes

ID
percent
item_id

An item can have more than one tax

I want to write a query that will give me all items with no tax. This query needs to be as fast as possible.

I was thinking

select id from Item where Id NOT in (select item_id from Item_taxes)

Is this the best way?

SELECT id
FROM item
LEFT JOIN item_taxes on item.id=item_taxes.item_id
WHERE item_taxes.item_id IS NULL

EDIT

Please refer to to the comment by @ypercube as it adds more explanation and clarification.


Duplicate an Oracle database user We'll be having external auditors coming over to perform a review on our Oracle database. They will be running a tool to perform the review and for that, they require a user ID that can connect to the database and grab information from it.

We have an existing user in the database for this. However, it is a production ID and we cannot afford to have it locked out. We would like to clone / duplicate this ID including its roles and rights.

Is there a way we can do so in Oracle?

User creation:

select dbms_metadata.get_ddl( 'USER', 'PHIL' ) from dual;

Default role:

select dbms_metadata.get_granted_ddl( 'DEFAULT_ROLE', 'PHIL' ) from dual;

System grants:

select dbms_metadata.get_granted_ddl( 'SYSTEM_GRANT', 'PHIL' ) from  dual;

Object grants:

select dbms_metadata.get_granted_ddl( 'OBJECT_GRANT', 'PHIL' ) from dual;

Role grants:

select dbms_metadata.get_granted_ddl( 'ROLE_GRANT', 'PHIL' ) from dual;

Quotas:

select dbms_metadata.get_granted_ddl( 'TABLESPACE_QUOTA', 'PHIL' ) from dual;

If any of the above have no output, you'll get an exception that looks something like this:

SQL> select dbms_metadata.get_granted_ddl( 'TABLESPACE_QUOTA', 'PHIL' ) from dual;
ERROR:
ORA-31608: specified object of type TABLESPACE_QUOTA not found
ORA-06512: at "SYS.DBMS_METADATA", line 4018
ORA-06512: at "SYS.DBMS_METADATA", line 5991
ORA-06512: at line 1

no rows selected

SQL>

Then do a search and replace on the output to change the username.


Support to SQL Server 2000 from Visual Studio 2013(SQL Server 2012)? I can't access to a database created by SQL Server 2000 from Visual Studio 2013, I asked this before in StackOverflow, but I don't get an useful answer:
https://stackoverflow.com/questions/21065565/when-i-create-a-connection-to-the-datasource-of-an-old-application-throw-me-an

(SELECT @@VERSION = Microsoft SQL Server  2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
)

After a search in google I found some workarounds with a similar problem:
http://n8v.enteuxis.org/2009/12/getting-microsoft-visual-studio-2005-to-talk-to-microsoft-sql-server-2008/
http://support.microsoft.com/kb/954961/en-us

But two solutions send me to download Microsoft Visual Studio 2005 Service Pack 1 Update for Microsoft SQL Server 2008 Support.

I can't install that, because I don't have Visual Studio 2005 and I need "VS2013 - Olds (2000)".

I have installed SQL Server 2008 (which I don't use today) and SQL Server 2012.

Don't exist a 'Microsoft Visual Studio 2013 Update for Microsoft SQL Server Olds'. What can I do?

Are these backup files from SQL Server 2000?  If so, you really should try to get these converted to SQL Server 2008/2012 formats.  A blog post from Ahmed Ibrahim walks through a set of scripts that you can run in SQL Server 2008 or 2012 that will allow you to restore these backups into 2008/2012 and convert them.  This will allow you to access easily from VS2012/2013.


Composite Primary Key in multi-tenant SQL Server database I'm building a multi-tenant app (single database, single schema) using ASP Web API, Entity Framework, and SQL Server/Azure database. This app will be used by 1000-5000 customers. All the tables will have TenantId (Guid / UNIQUEIDENTIFIER) field. Right now, I use single field Primary Key which is Id (Guid). But by using just the Id field, I have to check if the data supplied by the user is from / for the right tenant. For example, I have a SalesOrder table which has a CustomerId field. Every time users post/update a sales order, I have to check if the CustomerId is from the same tenant. It gets worse because each tenant might have several outlets. Then I have to check TenantId and OutletId. It's really a maintenance nightmare and bad for performance.

I'm thinking to add TenantId to the Primary Key along with Id. And possibly add OutletId, too. So the Primary Key in the SalesOrder table will be: Id, TenantId, and OutletId. What is the downside of this approach? Would the performance hurt badly using a composite key? Does the composite key order matter? Are there better solutions for my problem?

Having worked on a large-scale, multi-tenant system (federated approach with customers spread across 18+ servers, each server having identical schema, just different customers, and thousands of transactions per second per each server), I can say:

There are some folks (a few, at least) who will agree on your choice of GUID as the IDs for both "TenantID" and whatever entity "ID". But no, not a good choice. All other considerations aside, that choice alone will hurt in a few ways: fragmentation to start with, vast amounts of wasted space (don't say disk is cheap when thinking about enterprise storage — SAN — or queries taking longer due to each data page holding fewer rows than it could with either INT or BIGINT even), more difficult support and maintenance, etc. GUIDs are great for portability. Is the data generated in some system and then transferred to another? If not, then switch to a more compact data type (e.g. TINYINT, SMALLINT, INT, or even BIGINT), and increment sequentially via IDENTITY or SEQUENCE.
With item 1 out of the way, you really do need to have the TenantID field in EVERY table that has user data. That way you can filter anything without needing an extra JOIN. This also means that ALL queries against client-data tables are required to have the TenantID in the JOIN condition and/or WHERE clause. This also helps guarantee that you don't accidentally mix data from different customers, or show Tenant A data from Tenant B.

  I'm thinking to add TenantId as primary key along with Id. And possibly add OutletId too. So primary key(s) in sales order table will be Id, TenantId, OutletId.

Yes, you should have your clustered indexes on the client-data tables be composite keys, inclusive of TenantID and ID **. This also ensures that TenantID is in every NonClustered index (since those include the Clustered Index Key(s)) which you would need anyway since 98.45% of queries against client-data tables will need the TenantID (the main exception is when garbage collecting old data based on CreatedDate and not caring about TenantID).

No, you would not include FKs such as OutletID to the PK. The PK needs to uniquely identify the row, and adding in FKs would not help with that. In fact, it would increase chances for duplicated data, assuming that OrderID was unique for each TenantID, as opposed to unique per each OutletID within each TenantID.

Also, it is not necessary to add OutletID to the PK in order to ensure that Outlets from Tenant A don't get mixed up with Tenant B. Since all user-data tables will have TenantID in the PK, that means TenantID will also be in the FKs. For example, the Outlet table has a PK of (TenantID, OutletID), and the Order table has a PK of (TenantID, OrderID) and an FK of (TenantID, OutletID) which references the PK on the Outlet table. Properly defined FKs will prevent Tenant data from getting intermixed.

  Does the composite key order matter?

Well, here is where it gets fun. There is some debate as to which field should come first. The "typical" rule for designing good indexes is to pick the most selective field to be the leading field. TenantID, by its very nature, will not be the most selective field; the ID field is the most selective field. Here are some thoughts:

ID first: This is the most selective (i.e. most unique) field. But by being an auto increment field (or random if still using GUIDs), each customer's data is spread out throughout each table. This means that there are times when a customer needs 100 rows, and that requires almost 100 data pages read from disk (not fast) into the Buffer Pool (taking up more space than 10 data pages). It also increases contention on the data pages since it will be more frequent that multiple customers will need to update the same data page.

However, you typically do not run into as many parameter sniffing / bad cached plan issues as much since the statistics across the different ID values is fairly consistent. You might not get the most optimal plans, but you will be less likely to get horrible ones. This method essentially sacrifices performance (slightly) across all customers to gain the benefit of less frequent problems.
TenantID first: This is very much not selective at all. There might be very little variation across 1 million rows if you only have 100 TenantIDs. But the statistics for these queries is more accurate since SQL Server will know that a query for Tenant A will pull back 500,000 rows but that same query for Tenant B is only 50 rows. This is where the main pain-point is. This method greatly increases the chances of having parameter sniffing issues where the first run of a Stored Procedure is for Tenant A and acts appropriately based on the Query Optimizer seeing those statistics and knowing it needs to be efficient getting 500k rows. But when Tenant B, with only 50 rows, runs, that execution plan is no longer appropriate, and in fact, is quite inappropriate. AND, since the data is not being inserted in the order of the leading field, this method creates a lot more page splits (hence more fragmentation) in a short period of time than the other approach.

However, for the first TenantID to run a Stored Procedure, the performance should be better than in the other approach since the data (at least after doing index maintenance) will be physically and logically organized such that far fewer data pages are needed to satisfy the queries. This means less physical I/O, fewer logical reads, less contention between Tenants for the same data pages, less wasted space taken up in the Buffer Pool (hence improved Page Life Expectancy) etc.

There are two main costs to getting this improved performance. The first is not so difficult: you must do regular index maintenance to counteract the increased fragmentation. The second is a bit less fun.

In order to counteract the increased parameter sniffing issues, you need to separate the execution plans between Tenants. The simplistic approach is to use WITH RECOMPILE on procs or the OPTION (RECOMPILE) query hint, but that is a hit on performance that could wipe away all of the gains made by putting TenantID first. The method that I found worked best is to use parameterized Dynamic SQL via sp_executesql. The reason for needing the Dynamic SQL is to allow for concatenating the TenantID into the text of the query, while all other predicates that would normally be parameters are still parameters. For example, if you were looking for a particular Order, you would do something like:

DECLARE @GetOrderSQL NVARCHAR(MAX);
SET @GetOrderSQL = N'
  SELECT ord.field1, ord.field2, etc.
  FROM   dbo.Orders ord
  WHERE  ord.TenantID = ' + CONVERT(NVARCHAR(10), @TenantID) + N'
  AND    ord.OrderID = @OrderID_dyn;
';

EXEC sp_executesql
   @GetOrderSQL,
   N'@OrderID_dyn INT',
   @OrderID_dyn = @OrderID;

The effect this has is to create a reusable query plan for just that TenantID that will match the data volume of that particular Tenant. If that same Tenant A executes the stored procedure again for another @OrderID then it will reuse that cached query plan. A different Tenant running that same Stored Procedure would generate a query text that was different only in the value of the TenantID, but any difference in the query text is enough to generate a different plan. And the plan generated for Tenant B will not only match the data volume for Tenant B, but it will also be reusable for Tenant B for different values of @OrderID (since that predicate is still parameterized).

The downsides to this approach are:

It is a little more work than just typing in a simple query (but not all queries need to be Dynamic SQL, just the ones that end up having the parameter sniffing problem).
Depending on how many Tenants are on a system, it does increase the size of the plan cache since each query now requires 1 plan per TenantID that is calling it. This might not be an issue, but is at least something to be aware of.
Dynamic SQL breaks the ownership chain, which means read/write access to tables cannot be assumed by having EXECUTE permission on the Stored Procedure. The easy but less secure fix is just to give the User direct access to the tables. This is certainly not ideal, but that is usually the trade-off for quick and easy. The more secure approach is to use Certificate-based security. Meaning, create a Certificate, then create a User from that Certificate, grant that User the desired permissions (a Certificate-based User or Login cannot connect to SQL Server on its own), and then sign the Stored Procedures that use Dynamic SQL with that same Certificate via ADD SIGNATURE.

For more information on module signing and Certificates, please see: ModuleSigning.Info

Please see UPDATE section towards the end for additional topics related to the issue of dealing with mitigating statistics issues resulting from this decision.

** Personally, I really dislike using just "ID" for the PK field name on every table as it is not meaningful, and it is not consistent across FKs since the PK is always "ID" and the field in the child table has to include the parent table name. For example: Orders.ID -> OrderItems.OrderID. I find it much easier to deal with a data model that has: Orders.OrderID -> OrderItems.OrderID. It is more readable, and cuts down on the number of times you will get the "ambiguous column reference" error :-).

UPDATE

Would the OPTIMIZE FOR UNKNOWN Query Hint (introduced in SQL Server 2008) help with either ordering of the composite PK?

Not really. This option does get around parameter sniffing issues, but it merely replaces one problem with another. In this case, rather than remembering the statistical info for the parameter values of the initial run of the stored procedure or parameterized query (which is definitely great for some, but potentially mediocre for some, and potentially horrible for some), it uses a general statistic of data distribution to estimate row counts. This is hit-or-miss as to how many (and to what degree) queries will be affected positively, negatively, or not at all. At least with parameter sniffing some queries were guaranteed to benefit. If your system has Tenants with widely varied data volumes, this could potentially hurt performance for all queries.

This option accomplishes the same thing as copying input parameters to local variables and then using the local variables in the query (I have tested this but no room for that here). Additional info can be found in this blog post: http://www.brentozar.com/archive/2013/06/optimize-for-unknown-sql-server-parameter-sniffing/. Reading the comments, Daniel Pepermans came to a conclusion similar to mine regarding the use of Dynamic SQL that has limited variation.
If ID is the leading field in the Clustered Index, would it help / suffice to have a Non-Clustered Index on (TenantID, ID), or just (TenantID) to have accurate statistics for queries that process many rows of a single tenant?

Yes, it would help. The large system that I mentioned working on for years was based on an index design of having the IDENTITY field as the leading field because it was more selective and reduced parameter sniffing issues. However, when we needed to operations against a good portion of a particular Tenant's data, the performance did not hold up. In fact, a project to migrate all data into new databases had to be put on hold because the SAN controllers got maxed out in terms of throughput. The fix was to add Non-Clustered Indexes to all tenant data tables to be just (TenantID). No need to do (TenantID, ID) since ID is already in the Clustered Index so the internal structure of the Non-Clustered Index was naturally (TenantID, ID).

While this did solve the immediate issue of being able to do TenantID-based queries much more efficiently, they still weren't as efficient as they could have been if it were the Clustered Index that were in that same order. And, now we had yet one more index on every table. That increased the amount of SAN space we were using, increased the size of our backups, made the backups take longer to complete, increased the potential for blocking and deadlocks, decreased performance on INSERT and DELETE operations, etc.

AND we were still left with the general inefficiency of having a Tenant's data spread out across many data pages, intermixed with many other Tenant's data. As I mentioned above, this increases the amount of contention on these pages, and it fills up the Buffer Pool with lots of data pages that have 1 or 2 useful rows in them, especially when some of the rows on those pages were for clients that were inactive but hadn't been garbage collected yet. There is much less potential for re-use of the data pages in the Buffer Pool in this approach, so our Page Life Expectancy was pretty low. And that means more time going back to disk to load more pages.


Simple Backup Plan Question I'm using Microsoft SQL Server 2016 Standard (64-bit). I have the following backup plan (9 steps long) ...
What I want is just one full (simple) backup everyday.  Do I need to do step 8 (Backup Transaction Log)? Or can I just run some kind of query like "TRUNCATE Transaction Log" after the backup is done? And am I missing any steps, or running something I shouldn't?
"Management" - "Maintenance Plans" - "My_Maint_Plan"

Check Database Integrity
Shrink Database
Reorganize Index
Rebuild Index
Update Statistics
Clean Up History
Backup Database (Full)
Backup Database (Transaction Log)
Maintenance Cleanup Task

This plan creates 2 files daily ... .BAK (1.7 gig), and .TRN (8 gig) ... Do I really need the 8 gig TRN file?

If you don't want point-in-time restore for your databases, switch them to SIMPLE recovery, and simplify your maintenance to:

Check Database Integrity
Update Statistics
Backup Database (Full)


Database design of Room Booking system in a Hotel Intro and System Description

I am currently designing a Database for a Room Booking system in a Hotel.

Client fills a Form / Request with following information about Room:

amount of people in room
rating of a room
check in and check out date

Administrator has a dashboard with list of Forms from Clients, he will assign each Room for every Client manually.
After this User gets a Bill.

Database Design

This is actually a Sketch of my DB. I'll have following tables:

Users
Forms
Rooms
Bills

I am not considering table Passwords which contains Password's Hashes.

Questions

I would like to hear from you what is missing in my DB Design, what do you think about overall logic and correctness of relations between tables.

Without knowing exact and whole requirements we cannot design a complete data model. Let us assume the following business rules, it is based on your question.

Each Customer can request for one or more Room.
Each Room can be requested by one Customer
Each Admin can assign one or many Room.
Each Room must be assigned by one Admin.
Each Room must be belongs to one Room Type.
Each Room Type can contain one or many Room.
Each Booking must be belongs to one Date.
Each Date can contain one or more Booking.
Each Room Type can contain one or many Rent.
Each Rent must belongs to one Room Type.
Each Booking must include one or many Room.
Each Room can be included in one Booking.
Each Customer can hold one or many Booking.
Each Booking must belongs to one Customer.
Each Rent can be included in one or many Bill.
Each Bill must include one Rent.
Each Bill Payment must include one Payment Type.
Each Payment Type can contain one or many Bill Payment.
Each Date can contain one or many Bill Payment.
Each Bill Payment must belongs to one Date.
Each Room can be rated one or many Room Rating.
Each Room Rating must belongs to one Room.
Each Rating can contain one or many Room Rating.
Each Room Rating must belongs to one Rating.

Based on the above rules we have designed the following data model.

This model is a subset of Hotel Booking system. After the normalization process (check my blog), you will have more detailed and complete model.
In the above model Room rent is calculated based on the room type such as Single Room, Doubles Room, Family Room or Meeting Hall rather than number of people in the room.
And Room rent is changed from time to time, so we have included the from date and to date to have history of room rents. Also customer can use many payment types such as credit card, debit card, cash payment, so we are included payment type entity.
This model is based on assumption. I hope this model will help you in someway.
Thank you.


Restoring Postgres from only base backup taken with wal-e I have a postgres base backup taken via wal-e. Is it possible to restore and start the DB with just the base backup? I cannot use the restore_command in recovery.conf since the wal files are archived to deep glacier.

After copying the base backup with wal-e backup-fetch command and starting the DB, im getting the following logs:

2019-11-05 11:28:26 UTC [7417]: user=,db=,app=,client= LOG:  database system was interrupted; last known up at 2019-10-31 22:13:32 UTC
2019-11-05 11:28:26 UTC [7417]: user=,db=,app=,client= LOG:  creating missing WAL directory "pg_xlog/archive_status"
2019-11-05 11:28:26 UTC [7417]: user=,db=,app=,client= LOG:  invalid checkpoint record
2019-11-05 11:28:26 UTC [7417]: user=,db=,app=,client= FATAL:  could not locate required checkpoint record
2019-11-05 11:28:26 UTC [7417]: user=,db=,app=,client= HINT:  If you are not restoring from a backup, try removing the file "/var/lib/postgresql/9.6/main/backup_label".
2019-11-05 11:28:26 UTC [7415]: user=,db=,app=,client= LOG:  startup process (PID 7417) exited with exit code 1
2019-11-05 11:28:26 UTC [7415]: user=,db=,app=,client= LOG:  aborting startup due to startup process failure
2019-11-05 11:28:26 UTC [7415]: user=,db=,app=,client= LOG:  database system is shut down

Is it possible to start the DB and bring it to consistent state with only the base backup?

I have a postgres base backup taken via wal-e.

So I would suggest that your question would be best addressed to the people who created wal-e.  I've never used it myself, but ...

  Is it possible to start the DB and bring it to consistent state with only the base backup?

Short answer: No.

The base backup takes a "snapshot" of the database.
That snapshot, taken from a running database, it unlikely to be consistent in itself.

Anything and everything that happens in the database after that snapshot is taken is written to the WAL files so, in order to do any meaningful database recovery, you need those as well.