PostgreSQL Backup and restore, sizes are different I Have a Wordpress database in my postgreSQL, in phppgadmin this database is shown with the size of 273 MB.

I've ran sudo -u postgres pg_dump -Fp -f wordpress.sql wordpress to do the database backup, the result is a file with just 18MB.

I've changed the name of the original database to Wordpress2 and Then:

I've created a new database called Wordpress and did a restore sudo -u postgres psql wordpress < wordpress.sql  and the result is a database with size of 19 MB, there is something missing?

Below is a print of phppgadmin, with wordpress as the imported backup and wordpress2 as the original database.

AutoVacuum

postgres=# select setting from pg_settings where name = 'autovacuum';
 setting
---------
 on
(1 row)

Indexes Space

postgres=# select pg_size_pretty(sum(pg_relation_size(indexrelid))) from pg_index;
     pg_size_pretty
    ----------------
     2600 kB
    (1 row)

Database Sizes
Original

postgres=# select pg_size_pretty(pg_database_size('wordpress2'));
 pg_size_pretty
----------------
 273 MB
(1 row)

Restored

postgres=# select pg_size_pretty(pg_database_size('wordpress'));
 pg_size_pretty
----------------
 20 MB
(1 row)

I Copied the original database

CREATE DATABASE newdb WITH TEMPLATE wordpress2 OWNER postgres;

And performed a vacuum full in this "newdb"

vacuumdb -f -d newdb -z -v

Result is the "newdb" decreased from 273MB to 17MB

postgres=# select pg_size_pretty(pg_database_size('newdb'));
 pg_size_pretty
----------------
 17 MB
(1 row)

I Will answer my own question,

From: https://stackoverflow.com/questions/13505785/postgresql-database-size-is-less-after-backup-load-on-heroku

The reason of this is its MVCC system. Every time you UPDATE any record in a database it creates another "version" of this record instead of rewriting the previous. This "outdated" records will be deleted by VACUUM process, when there will be no need in them.

I've search in PostgreSQL docs and I found:
Unlike traditional database systems which use locks for concurrency control, PostgreSQL maintains data consistency by using a multiversion model (Multiversion Concurrency Control, MVCC). This means that while querying a database each transaction sees a snapshot of data (a database version) as it was some time ago, regardless of the current state of the underlying data. This protects the transaction from viewing inconsistent data that could be caused by (other) concurrent transaction updates on the same data rows, providing transaction isolation for each database session.

The main advantage to using the MVCC model of concurrency control rather than locking is that in MVCC locks acquired for querying (reading) data do not conflict with locks acquired for writing data, and so reading never blocks writing and writing never blocks reading.

From: https://stackoverflow.com/questions/924993/postgresql-database-size-increasing
If the dead tuples have stacked up beyond what can be accounted for in max_fsm_pages, a regular VACUUM will not be able to free everything. The end result is that the database will grow larger and larger over time as dead space continues to accumulate. Running a VACUUM FULL should fix this problem. Unfortunately it can take a very long time on a large database.

If you're running into this problem frequently, you either need to vacuum more often (autovacuum can help here) or increase the max_fsm_pages setting. When running VACUUM VERBOSE it will tell you how many pages were freed and give you a warning if max_fsm_pages was exceeded, this can help you determine what this value should be. See the manual for more information.

From: postgres backup / restore: restored database much smaller?

Maybe it's just index bloat. VACUUM FULL does not help with index bloat, on the contrary, as said in the doc for 8.4:

  The FULL option is not recommended for routine use, but might be
  useful in special cases. An example is when you have deleted or
  updated most of the rows in a table and would like the table to
  physically shrink to occupy less disk space and allow faster table
  scans. VACUUM FULL will usually shrink the table more than a plain
  VACUUM would. The FULL option does not shrink indexes; a periodic
  REINDEX is still recommended. In fact, it is often faster to drop all
  indexes, VACUUM FULL, and recreate the indexes.

(in more recent versions, this advice has disappeared because VACUUM FULL has been reimplemented differently).

See Routine reindeing and the REINDEX command.

The easiest way to reindex is to connect to the database with the db user that owns it and issue:

REINDEX database test_db;

Ideally, it should be done immediately after VACUUM FULL and the database should shrink to its lowest possible size at this point in time.


AWS RDS MySQL database created but cannot create indexes or change collation AWS RDS MySQL 5.6.x issue:

Created a MySQL database on the AWS RDS platform.  Created a dozen tables, imported data to all tables and can SELECT from all tables.

Granted all privileges to user on database schema.

grant all privileges on mysqldb.* to 'username'@'%';

However, cannot create indexes or change collation.  The following commands just hang and never return:

alter schema mysqldb default character set utf8 default collate utf8_general_ci;
create index ix_mytable on mytable(mycol);

And there are no error messages reported in the error log on the AWS RDS dashboard or from within MySQL Workbench.

I have a local MySQL database (from which I exported the table data and moved the data to AWS RDS) on which the above-mentioned SQL works without issues.

Any ideas?

Solution:

a. create a user-defined parameter group and use it in the existing database instance instead of the default parameter group.

b. change the character set to utf-8.

I can now create indexes and change the table and database collation.

So the issue was the lack of the user-created parameter group and a parameter group with the correct character set (i.e. utf-8).


Ola Index and Update stats I wanted to ask what is the proper way to run the index maintannace ola script.
Do you include @UpdateStatistics = 'Y', and @OnlyModifiedStatistics = 'Y'?

The reason I ask is that wherever I read, everyone says not to update statistics after an index rebuild.  From what I am seeing, with those two options highly fragmented or indexes that could not be reorganized, that were rebuild would have their stats updated (excluding column stats) and than updating stats again would update the stats.

Would it be better to update stats in a different job, ran at a different time/day or is it fin to include those options?

The parameters you're looking at are only used if indexes are reorganized.

Depending on the level of fragmentation involved, which you provide when you run the task, the IndexOptimize stored procedure will either rebuild indexes (which do not require statistics updates), or reorganize (which do require statistics updates).

There is a third scenario, where you can only update statistics, using the following code (from Ola's site):

EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y';

Note that the Fragmentation options are explicitly set to NULL in this scenario.


Why is the subquery apparently correlated? How to avoid it? I have a table orders and a table orderpositions. The orderpositions (basically the items bought with the order) are linked via orderid (indexed) to the orders primary key.

For a given set of orders I need the orderpositions. Because the conditions with which the orders are determined are dynamic I need to use a subquery instead of a JOIN, so I wrote:

SELECT *
FROM orderpositions
WHERE orderpositions.orderid IN
(
    SELECT id FROM orders WHERE userid=118
);

(The sub-query can be something entirely different, perhaps with nested JOINS and whatnot, but will always return a list of order IDs, of which I need the linked orderpositions.)

Now this runs significantly slower than the JOIN equivalent (which is trivial in this example, but not in others). I have read about correlated queries and believe that my approach is very slow because the subquery is executed once for each row in the orderpositions table.

As a test I have tried to create a temporary table, insert the order IDs into it and then JOIN on that, which is also very fast. But I would like to avoid the overhead of the temporary table and firing off multiple queries.

Is there any way to force the sub-query to be uncorrelated?

Your suppositions are correct, MySQL versions up to 5.5 only can execute those kind of subqueries (semi-joins) in that particular way. The subquery is not correlated in reality (there is no real dependency), but the DEPENDENT SUBQUERY that you may get from explain means that it is executing it as if it was one.

Please note that in some scenarios, that execution plan could be the most optimal one (checking the existence of the rows one by one), but in many others, like yours, it will probably not. This is a very well known query plan limitation of MySQL <= 5.5, as you can see on these slides I created about query optimization:

Query optimization with MySQL 5.6: Old and new tricks - Slide 54 (That subquery in 5.5)
Query optimization with MySQL 5.7 and MariaDB 10: Even newer tricks - Slide 64 (A common 5.5 performance problem)

You can either upgrade to 5.6 (or use >= MariaDB 5.3) where there are more options available for its execution; or manually convert the query to a JOIN or a scalar subquery if you want to stick with MySQL <=5.5. The 5.6 and the current in-development 5.7 contain a great number of optimizations that make the life easier for the developer.


Why is the System tablespace in manual segment space management mode? Why has Oracle kept the SYSTEM tablespace in manual segment space management mode? I searched on forums but didn't find the answer.

Because Oracle says so :). The official documentation explicitly states that:

  Restrictions on Automatic Segment-Space Management This clause is
  subject to the following restrictions:

  You can specify this clause only for a permanent, locally managed tablespace.
  You cannot specify this clause for the SYSTEM tablespace.

I don't know why, there are some hypothesis here.


Why does the MS SQL Server change tracking table store all DML operations that occurred within the transactiton? I have setup up SQL Server Change Tracking on a table t1 on MS SQL2008R2. Within a transaction, I insert a row into it and update it 'n' times. When I examine the rowcounts in the change tracking tables, it shows 'n' number of rows whereas the syscommitab shows just 1 row. Since the change tracking mechanism is a synchronous mechanism and per documentation, is expected to track only "net change" which occurred in a transaction, why is the change tracking table showing 'n' rows instead of just 1 ? Here is the SQL that was used to examine the rowcount.

SELECT  sct1.name as CT_schema,
    sot1.name as CT_table,
    ps1.row_count as CT_rows,
    ps1.reserved_page_count*8./1024. as CT_reserved_MB,
    sct2.name as tracked_schema,
    sot2.name as tracked_name,
    ps2.row_count as tracked_rows,
    ps2.reserved_page_count*8./1024. as tracked_base_table_MB,
    change_tracking_min_valid_version(sot2.object_id) as min_valid_version
FROM sys.internal_tables it
JOIN sys.objects sot1 on it.object_id=sot1.object_id
JOIN sys.schemas AS sct1 on
sot1.schema_id=sct1.schema_id
JOIN sys.dm_db_partition_stats ps1 on
it.object_id = ps1. object_id
and ps1.index_id in (0,1)
LEFT JOIN sys.objects sot2 on it.parent_object_id=sot2.object_id
LEFT JOIN sys.schemas AS sct2 on
sot2.schema_id=sct2.schema_id
LEFT JOIN sys.dm_db_partition_stats ps2 on
sot2.object_id = ps2. object_id
and ps2.index_id in (0,1)
WHERE it.internal_type IN (209, 210)
and (sot2.name='t1' or sot1.name='syscommittab')

Above SQL courtesy of https://www.brentozar.com/archive/2014/06/performance-tuning-sql-server-change-tracking/

Change Tracking keeps watch over every single DML operation; it has no concept of a transaction. So if you change a row 5 times, whether in a transaction or not, you're going to get 5 rows. From Microsoft's documentation:

  In change tracking, the tracking mechanism involves synchronous tracking of changes in line with DML operations so that change information is available immediately.

It doesn't contain any more complicated logic than that (like only recording the net change). You can of course determine net changes by using the Change Tracking functions instead of querying the CT table directly. You can manually collapse duplicate rows in the change tracking table by grouping in your query, or maybe Change Data Capture might be more what you're after?

If you want something that is transaction-aware and only records net changes, you can look at Temporal Tables. You're going to have to move to a version of SQL Server produced this decade, though (2016 or 2017).


The EXECUTE permission was denied on the object xp_availablemedia One of my colleagues has Public role on SQL Management Studio but db_owner on a couple of DB. While trying to CREATE back-up, the following message pops up

I have now added db_backupoperator on the DB, still the following error

The reason is that the user has enough permissions to perform a backup, but not enough permissions to enumerate the filesystem on the server, which is what SSMS does behind the scenes.

This behaviour is described in this support page, which suggests adding the login to the sysadmin role.

To work around it, add a backup device with sp_addumpdevice and let the user backup to that device.


DBCC CHECKDB unfixable corruption: Indexed view contains rows that were not produced by the view definition TL;DR: I've got an unfixable corruption in an indexed view. Here are the details:

Running

DBCC CHECKDB([DbName]) WITH EXTENDED_LOGICAL_CHECKS, DATA_PURITY, NO_INFOMSGS, ALL_ERRORMSGS

on one of my databases produces the following error:

  Msg 8907, Level 16, State 1, Line 1 The spatial index, XML index or
  indexed view 'ViewName' (object ID 784109934) contains
  rows that were not produced by the view definition.  This does not
  necessarily represent an integrity issue with the data in this
  database. (...)

  CHECKDB found 0 allocation errors and 1 consistency errors in table 'ViewName'.

  repair_rebuild is the minimum repair level (...).

I do understand that this message indicates that the materialized data of the indexed view 'ViewName' is not identical with what the underlying query produces. However, manually verifying the data does not turn up any discrepancies:

SELECT * FROM ViewName WITH (NOEXPAND)
EXCEPT
SELECT ...
from T1 WITH (FORCESCAN)
join T2 on ...

SELECT ...
from T1 WITH (FORCESCAN)
join T2 on ...
EXCEPT
SELECT * FROM ViewName WITH (NOEXPAND)

NOEXPAND was used to force use of the (only) index on ViewName. FORCESCAN was used to prevent indexed view matching from happening. The execution plan confirms both measures to be working.

No rows are being returned here, meaning that the two tables are identical. (There are only integer and guid columns, collations do not come into play).

The error cannot be fixed by recreating the index on the view or by running DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS. Repeating the fixes also did not help. Why does DBCC CHECKDB report this error? How to get rid of it?

(Even if rebuilding fixed it my question would still stand - why is an error reported although my data checking queries run successfully?)

Update: The bug has been fixed in some releases. I can no longer reproduce it in SQL Server 2014 SP2 CU 5. The 2014 SP2 KB contains a fix without KB article: Creating non-clustered index causes DBCC CheckDB With Extended_Logical_Checks to raise corruption error. The two connect bugs about this have been closed:

https://connect.microsoft.com/SQLServer/feedback/details/847233/creating-non-clustered-index-causes-dbcc-checkdb-with-extended-logical-checks-to-raise-corruption-error
https://connect.microsoft.com/SQLServer/feedback/details/795478/unfixable-dbcc-checkdb-error-that-is-also-a-false-positive-and-otherwise-strange

The query processor can produce an invalid execution plan for the (correct) query generated by DBCC to check that the view index produces the same rows as the underlying view query.

The plan produced by the query processor incorrectly handles NULLs for the ImageObjectID column. It incorrectly reasons that the view query rejects NULLs for this column, when it does not. Thinking that NULLs are excluded, it is able to match the filtered nonclustered index on the Users table that filters on ImageObjectID IS NOT NULL.

By producing a plan that uses this filtered index, it ensures that rows with NULL in ImageObjectID are not encountered. These rows are returned (correctly) from the view index, so it appears there is a corruption when there is not.

The view definition is:

SELECT
    dbo.Universities.ID AS Universities_ID,
    dbo.Users.ImageObjectID AS Users_ImageObjectID
FROM dbo.Universities
JOIN dbo.Users
    ON dbo.Universities.AdminUserID = dbo.Users.ID

The ON clause equality comparison between AdminUserID and ID rejects NULLs in those columns, but not from the ImageObjectID column.

Part of the DBCC generated query is:

SELECT [Universities_ID], [Users_ImageObjectID], 0 as 'SOURCE'
FROM [dbo].[mv_Universities_Users_ID] tOuter WITH (NOEXPAND)
WHERE NOT EXISTS
(
    SELECT 1
    FROM   [dbo].[mv_Universities_Users_ID] tInner
    WHERE
    (
        (
            (
                [tInner].[Universities_ID] = [tOuter].[Universities_ID]
            )
            OR
            (
                [tInner].[Universities_ID] IS NULL
                AND [tOuter].[Universities_ID] IS NULL
            )
        )
        AND
        (
            (
                [tInner].[Users_ImageObjectID] = [tOuter].[Users_ImageObjectID]
            )
            OR
            (
                [tInner].[Users_ImageObjectID] IS NULL
                AND [tOuter].[Users_ImageObjectID] IS NULL
            )
        )
    )
)
OPTION (EXPAND VIEWS);

This is generic code that compares values in a NULL-aware fashion. It is certainly verbose, but the logic is fine.

The bug in the query processor's reasoning means that a query plan that incorrectly uses the filtered index may be produced, as in the example plan fragment below:

The DBCC query takes a different code path through the query processor from user queries. This code path contains the bug. When a plan using the filtered index is generated, it cannot be used with the USE PLAN hint to force that plan shape with the same query text submitted from a user database connection.

The main optimizer code path (for user queries) does not contain this bug, so it is specific to internal queries like those generated by DBCC.


It just dawned on me. You must be using MySQL 8.0

I just heard about this headache not too long ago.

The SHOW CREATE PROCEDURE for MySQL 8.0 does not show a body column anymore.

The design of INFORMATION_SCHEMA.ROUTINES is also different for MySQL 8.0.

Someone wrote about this 4 months ago (See No more mysql.proc in MySQL 8.0). This paradigm will take getting used to. Personally, I am not comfortable with it, but we need to just get used to it.

You also gotta wrap your head around INFORMATION_SCHEMA.PARAMETERS, which itemizes each parameter passed to a stored procedure.

Just to skip all this other yucky stuff, MySQL 8.0 FAQ says the following:

  Stored procedures are stored in the mysql.routines and
  mysql.parameters tables, which are part of the data dictionary. You
  cannot access these tables directly. Instead, query the
  INFORMATION_SCHEMA ROUTINES and PARAMETERS tables. See Section 25.19,
  “The INFORMATION_SCHEMA ROUTINES Table”, and Section 25.12, “The
  INFORMATION_SCHEMA PARAMETERS Table”.

  You can also use SHOW CREATE FUNCTION to obtain information about
  stored functions, and SHOW CREATE PROCEDURE to obtain information
  about stored procedures. See Section 13.7.6.9, “SHOW CREATE PROCEDURE
  Syntax”.

I will leave it to you to reserch the rest of this.


Need a faster method to concatenate rows into column(s)--currently using CTEs Currently, we are using a view which I created that pulls data from a number of tables.  The major factor about this is getting the view to specifications involves a mix of CTEs and string concatenating to combine multiple rows into one column (multiple times).
While the view works, users have recently complained about it being too slow.
Currently, the view setup looks something like this:

;
With thing1 AS
(
SELECT DISTINCT
            /* huge block of code with joins and such
            that gets the individual fields and concatenates
            personnel names to one row */
)
,
thing2 AS
(
    SELECT  TaskName,
            Company,
            Lease,
            TaskBegin,
            TaskEnd,
            Field,
            Personnel,
            Invoice,
            InvDate,
            InvTot,
            ROW_NUMBER() OVER (Partition by TaskName ORDER BY [TaskName] ASC) rn
    FROM thing1
)

SELECT  TaskName,
        Company,
        Lease,
        Field,
        ISNULL(Personnel, '') as 'Personnel',
        TaskBegin,
        TaskEnd,
        ISNULL (STUFF( (SELECT ', ' + Invoice
                FROM thing1
                WHERE (thing1.TaskName = thing2.TaskName)
                AND thing2.rn = 1
                ORDER BY Invoice
                FOR XML PATH('')), 1, 1, ''), '') AS 'Invoice',
        ISNULL (STUFF( (SELECT ', ' + InvDate
                FROM thing1
                WHERE (thing1.TaskName = thing2.TaskName)
                AND thing2.rn = 1
                ORDER BY Invoice
                FOR XML PATH('')), 1, 1, ''), '') AS 'InvoiceDate',
        ISNULL (STUFF( (SELECT ', ' + InvTot
                FROM thing1
                WHERE (thing1.TaskName = thing2.TaskName)
                AND thing2.rn = 1
                ORDER BY Invoice
                FOR XML PATH('')), 1, 1, ''), '') AS 'InvoiceTotal'
    FROM thing2 WHERE rn = 1

GO

So it works as intended.  Problem is, once I get to that final SELECT that concatenates the Invoice, InvDate, and InvTot, the query goes from not needing a single second (that is, a straight SELECT from thing2) to taking up to 7 seconds to run its course.  (Average time is about 5 seconds.)  I'm fairly confident that part of the blame lies in the ever-growing database--this view loads much faster on the test environment, which has far fewer records in it.

My question is thus:  Is it possible to go about this in a way that is faster and less resource-intensive on the DB?

Looks like the issue in your code is multiple access to the CTEs and all the complicated logic you have in there. You should try to do everyting in a single pass with UNPIVOT/PIVOT. I did something similar here:
http://spaghettidba.com/2011/10/13/concatenating-multiple-columns-across-rows/
In my example you will find the concatenation of sentences in different languages (not exactly what you're after), but it seems to me that you could use the same technique.


TempDB Autogrowth - Does SQL need a restart? I have a case where i'm running into an issue with the tempdb not being large enough. Here is my error:

Could not allocate a new page for database 'TEMPDB' because of insufficient disk space in filegroup 'DEFAULT'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

The tempDB spans 16 files each with an initial size of 4096 MB and Autogrowth is set to none.  I have 118 GB free space on the SSD's

I want to turn Autogrowth on however this is in production.  Does SQL need a restart to pickup the new setting?

Yes the executing stored procedure will be investigated to see why it's storing so much data in table variables.

As far as a restart goes, no you don't need to.  However there's several things to think about before growing TempDB, some of the main ones are:

-Resize all of them to the same size.  TempDB uses a round robin proporitional fill algorithm which will create hotspots on physical files which are smaller in size.  Paul Randal goes through how to properly do this here.

-There's logical and physical contention.  Logical usually arises when you have to few physical files to spread the IO requests too.  Check your metrics to see how fast your current disks are performing and benchmark it again after you get new disks.  Then you'll have an idea what your current workload threshold is.  Review the DMVs to use here.  Read more about detecting logical contention here.

If you don't care about all this nit picky performance info and this is a environment where root cause analysis, very high uptime, perf. expectations are not critical; you could just throw hardware at it but make sure you review the info around tempdb.
Edit: Aaron Bertrand gave some great things to check in the of another answer here, which I will copy here:
You should set trace flag 1117 globally but also add it to the startup parameters so it is persisted.


Which indices should I create? Given this table:

CREATE TABLE [dbo].[visit](
    [visit_id] [uniqueidentifier] NOT NULL,
    [group_id] [uniqueidentifier] NOT NULL,
    [visitor_company] [nvarchar](50) NOT NULL,
-- Other columns
PRIMARY KEY CLUSTERED
(
    [visit_id] ASC -- per @Mark Sinkson's comment, I agree that this is a poor choice for a clustered index, but this is a different can of worms
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

And given that the application executes these two queries frequently:

SELECT * -- not literally *; just don't want to complicate my question
FROM [dbo].[visit]
WHERE group_id = @group_id

SELECT DISTINCT (visitor_company)
FROM [dbo].[visit]
WHERE group_id = @group_id

Does a non-clustered index on group_id, visitor_company optimize both queries?
Or should I create a non-clustered index on group_id as well?

EDIT 1: This is not homework. I'm a full-stack developer (not a DBA). I'm torn between Options 2 and 3 and I'd like some experts to weigh in.

EDIT 2: Constraint: I can't change visit_id to an int.

At first I'd suggest to have group_id and visit_id in INT or at least BIGINT.
If that table has only 3 columns it might worth to create not-unique clustered index by group_id and unique constraint on visit_id:

CREATE TABLE [dbo].[visit](
    [visit_id] [uniqueidentifier] NOT NULL,
    [group_id] INT NOT NULL,
    [visitor_company] [nvarchar](50) NOT NULL
);
GO
CREATE CLUSTERED INDEX CLIX_Visit ON [dbo].[visit]([group_id]);
GO
ALTER TABLE [dbo].[visit] ADD CONSTRAINT UQ_Visit UNIQUE ([visit_id]);
GO


Is there a neat way to build a user posts/likes/comments query? Say we have table users and three tables posts, comments, likes, which are many-to-one to users. I want to build some report query for all users, even if they have 0 posts, comments or likes.
Is there a way of doing this more elegant, than

select
  users.id,
  users.name,
  ifnull(u1.post_score,0),
  ifnull(u1.comments_score,0),
  ifnull(u1.likes_score,0)
from users left join (
select id,sum(post_score),sum(comments_score),sum(likes_score) from
(
(select
  user.id,
  posts.score as post_score,
  0 as comments_score,
  0 as likes_score
from users inner join posts on posts.user_id = users.id)
union all
(select
  user.id,
  0 as post_score,
  comments.score as comments_score,
  0 as likes_score
from users inner join comments on comments.user_id = users.id)
union all
(select
  user.id,
  0 as post_score,
  0 as comments_score,
  1 as likes_score
from users inner join likes on likes.user_id = users.id)
) t1
group by id ) u1
on u1.id = users.id

? Thank you.
P.S. MySQL

SELECT u.id,
       u.name,
       COALESCE(p.cnt, 0) posts,
       COALESCE(c.cnt, 0) comments,
       COALESCE(l.cnt, 0) likes
FROM users u
LEFT JOIN ( SELECT user_id, COUNT(*) cnt
            FROM posts
            GROUP BY user_id ) p ON  u.id = p.user_id
LEFT JOIN ( SELECT user_id, COUNT(*) cnt
            FROM comments
            GROUP BY user_id ) c ON  u.id = c.user_id
LEFT JOIN ( SELECT user_id, COUNT(*) cnt
            FROM likes
            GROUP BY user_id ) l ON  u.id = l.user_id


What are some known hazards with cyclical replication between two MySQL master servers? If I were to set up cyclical replication between a MySQL 5.6 master server and a MySQL 5.1 master server, what are some things that could potentially go wrong?

Also, the 5.6 MySQL does not have GTID enabled.

Thanks IS

Cringe, gag, etc.

5.1 and 5.6 differ by 2 major versions.  Replication is 'guaranteed' to work one direction, not the other.

Don't turn on GTIDs in 5.6; 5.1 will not like it.

Don't use any features new to 5.5 or 5.6.

Simply, don't do it.  Get the 5.1 upgraded ASAP.


For 768M RAM server is 617M allocation in my.cnf good or bad? I have a write-intensive web app, and I bought a VDS with 768M RAM

Is this my.cnf good for my purposes?

I specified bulk_insert_buffer_size to 126M and some other parameters as below:

[mysqld]
bulk_insert_buffer_size=126M
set-variable = max_connections=500
safe-show-database
max_user_connections=200
key_buffer_size = 16M
query_cache_size = 50M
tmp_table_size = 50M
max_heap_table_size  = 200M
thread_cache_size = 4
table_cache = 80
thread_concurrency = 8
innodb_buffer_pool_size = 100M
innodb_log_file_size = 25M
query_cache_limit = 50M
innodb_flush_log_at_trx_commit = 2

This depends on the amount of MySQL Data and what storage engines your are using
When it comes to MyISAM and InnoDB, they cache differently. I wrote a post about this back on April 14, 2011.
Since your machine only has 768M of RAM you have to do a nice balancing act depending on which storage engine you are using the most.
All MyISAM
Please run this query
Query #1
SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.4999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1))
recommended_key_buffer_size FROM
(SELECT LEAST(POWER(2,32),KBS1) KBS
FROM (SELECT SUM(index_length) KBS1
FROM information_schema.tables
WHERE engine='MyISAM' AND
table_schema NOT IN ('information_schema','mysql')) AA ) A,
(SELECT 2 PowerOf1024) B;

This query will tell you how big a MyISAM Key Buffer you data needs in MB. Of course, the amount installed RAM takes precedence, so use common sense. You should run this query:
Query #2
SELECT
    DataSize/POWER(1024,2) DataMB,
    IndexSize/POWER(1024,2) IndexMB,
    (DataSize/IndexSize) DataToIdexRatio
FROM
(
    SELECT SUM(data_length) DataSize,SUM(index_length) IndexSize
    FROM information_schema.tables
    WHERE engine='MyISAM' AND
    table_schema NOT IN ('information_schema','mysql')
) A;

If the DataToIndexRatio is less than 3, look over all the indexes. You may have indexes that are too big or just too many indexes. If you remove all unused indexes, this will raise the DataToIndexRatio and then you can run Query #1, and get a smaller recommendation. Again used common sense since you only have 768M. Personally, I would not exceed 256M. Therefore, you should choose either 256M or the result of Query #1, whichever is smaller.
If you have no InnoDB Data whatsoever, you added this to my.cnf (my.ini)
[mysqld]
skip-innodb

and restart mysql. That way, mysqld will startup faster because it not have to check for InnoDB crash recovery.
You mentioned your app is write intensive. A mix of INSERTs, UPDATEs, and DELETE will cause table and index fragmentation in a hurry.If this is the case, you need to convert the row format of the MYiSAM tables to Fixed. Why?
The book MySQL Database Design and Tuning recommends using ROW_FORMAT=FIXED on pages 72,73. This will internally convert all VARCHAR fields to CHAR. It will make the MyISAM table larger, but executed SELECTs against it will be much faster. I can personally attest to this. I once had a table that was 1.9GB. I changed the format with ALTER TABLE tblname ROW_FORMAT=FIXED. The table ended up 3.7GB. The speed of the SELECTs against it was 20-25% faster without improving or changing anything else.
All InnoDB
Please run this query
Query #3
SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.49999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_innodb_buffer_pool_size
FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables
WHERE engine='InnoDB') A,
(SELECT 2 PowerOf1024) B;

This query will tell you how big an InnoDB Buffer Pool you data and indexes need in MB. Of course, the amount installed RAM takes precedence, so use common sense. You should run Query #2 and apply the same DataToIndexRatio rule even more strictly because secondary indexes in InnoDB tend to be more bloated than their MyISAM counterparts.
If you are running MySQL 5.5, please tune InnoDB for Multiple CPU engagement:

Possible to make MySQL use more than one core?
About single threaded versus multithreaded databases performance
Multi cores and MySQL Performance

Because you only have 768MB of RAM, I again recommend 256MB for the innodb_buffer_pool_size or the result of Query #3, whichever is smaller.
You should resize the InnoDB Log Buffers. For example, if the innodb_buffer_pool_size you choose is 256M, you need to resize the log files to 25% of that number, 64M like this:
STEP 01) Shutdown mysql

service mysql stop for Linux
net stop mysql for Windows

STEP 02) Delete ib_logfile0 and ib_logfile1
STEP 03) Add this to my.cnf (my.ini)
[mysqld]
innodb_log_file_size = 64M

STEP 04) Startup mysql (log files are recreated to the new size)

service mysql start for Linux
net start mysql for Windows

InnoDB and MyISAM Mixed
I would apply the above rules and split up 128MB for key_buffer_size and 128MB for innodb_buffer_pool size.
SUMMARY
I choose 256M because regardless of the Storage Engine Mix you have, you have a write intensive app. You will need to do good caching for InnoDB as well as MyISAM indexes.
On the flip side, I only set aside 256MB of one or both storage engines because the OS will need as much RAM as possible for the following aspects:

Operating System
Query Cache
DB Connections (Buffers Per Connections,Max Connection Count)
File Handles (open_files_limit, innodb_open_files (if using innodb_file_per_table))

Have fun configure all these aspects !!!


Storing PLC Data in SQL Server database I'm developing a software solution that requires storing raw data points originating from many PLCs (programmable logic controller, used to control industrial machines such as conveyor belts and robots) into a SQL Server database. My main concern is how to properly store large amounts of raw numeric data.

Each data point has the following properties:

Datetime stamped
May be a: boolean, integer, floating point, or a string
A data point may be stored for each second all year (~31,536,000 data points per year)

Each PLC I'm collecting data from may have as many as 500 data points, and I want to plan on supporting thousands of PLCs. Meaning, I'll have at least 15,768,000,000,000 data points per year (31,536,000 points per year * 500 points * 1000 plcs). I currently have a table for each data type (bool, int, float, string). In an effort to reduce the number of records and storage, I am storing 4 data points per record [ID, DateTime, DataGroupID, Value1, Value2, Value3, Value4].

I have this implemented, and it's working well (fast queries with my largest table at ~72 million records) though it is isn't very flexible (Changing data types requires moving data from table to table, regrouping data requires table/column changes, etc.)

So, my thought to improve flexibility is to keep the data type specific tables, but to simply store each data point as its own record. Some brief math of the max size of a bigint (the data type of record ID), showed that I have plenty of IDs available. Though the storage requirements, and query performance is still a concern. Should I be concerned about anything using this method? Is there a better option?

The root of my concern is that I simply have not experienced gigantic databases/tables, so while I know that a few million records is small, I still don't know what an over-the-top table would look like. In addition, the storage of the data is the foundation for my software, so updating requires a lot of work to make it happen.

Let's start with the basics:

Should you store 16 trillion raw data points per year in a relational database? Probably not. Think about what you're going to do with it afterwards, and what those queries will look like. Relational databases are great for data that has relationships to other tables and needs to be inserted/updated/deleted frequently, or read by common reporting tools, but it's not quite as good of a fit here.

If you don't do it in an RDBMS, where do you do it? Meet the time series database, something designed for this exact purpose. Examples include Graphite and InfluxDB.

If you had to do it, what should you look out for? Any kind of maintenance will be extremely challenging - index rebuilds, stats updates, backups, CHECKDB, etc. Instead, consider a dynamic sharding design where data is split into smaller volumes. In the data warehousing realm, for example, large tables are typically split by date range (2017Q3, 2017Q2, 2017Q1, etc) so that if you need to add new columns for new incoming data, you only have to modify the current table. For reporting purposes, you can union all the tables together into a view - but just be warned that any kind of ad-hoc queries on a trillion-row table can prove challenging. (Heaven help you if someone wants an order-by and there's no supporting index - goodbye, tempdb drive space.)


GUID stored in a varchar field I have inherited several databases that use GUIDs as PKs.  Instead of all the datatypes being uniqueidentifier most are varchar(50) and some varchar(100). Fields are true GUID some where created by

  myID = 'xxx'+convert(varchar(40),newID())

In general its a bit of a mess

What are the performance implications for this design and is it worth re-working the tables to convert the datatypes.  Tables are generally around 1/2 M records with a couple table in the 2-4M record range.

The impetus of this question is that I am trying (unsuccessfully) to optimize an proc that joins 24 tables and views and the server is not handling it very well.

Thanks for any insight

In general, it is a bad idea to have long clustered index keys. For that it is not important if the key is made up of several short columns or few long columns. This makes the maintenance of every index as well as the read access of every index on that table a lot more expensive that it has to be. As the PK is by default enforced by a clustered index, you most likely have a very long clustered index key.

Also in general it is a good idea to have you clustered index key be monotonously increasing. As your column is valued based on NEWID it will distribute new rows all over the table causing fragmentation. The extra long key will make this even worse.

And finally, in general it is a good idea to not spend too much time tuning performance preemptively. If you do new development follow best practices (like the two recommendations above). With existing software, fix only the stuff that is causing a headache.

Now it looks like you have that headache already. Therefore I would start by migrating those primary keys to either identity columns (preferred) or to uniqueidentifier columns that default to newsequentialid().

But this is a difficult change to do if you can't take downtime. I that case I would start by adding new indexes to support you current strongest headache (read: stored procedure). Than work on cleaning up the mess afterwards.


How do I copy a DB dump file to a remote computer? I want to create a copy of my database from one server to another (my laptop to a server for example). Ive been looking online about how to do it and found this article:http://www.postgresqltutorial.com/postgresql-copy-database/ However I am confused when it says

  "Second, copy the dump file to the remote server"

I know how to pg_dump and pg_restore but now sure what they mean by copy the dump file?

If you are using Windows and are using RDP to connect to the server you can just select the dump file, press CTRL-C, switch to the server connection and browse to a folder where you would like to copy the file to and press CTRL-V.

If your laptop and the server are on the same network, you can create a file share on either your laptop or on the server (or use an existing one) and use Windows Explorer to copy-paste the files over.

There is also a copy command if you want to do it from a DOS box or batch file.

If you are using Linux, again, it depends on how you can connect to your server. There are options to mount a remote file share using NFS or Samba and copy files over, or you can use scp to copy files over an SSH connection.

This way you can have a "copy" of the dump file on the remote server, and use pg_restore to restore that copy of the dump file.


Check which condition has met - Sql Server In a simple scalar function, I got several "IFs" separated each of them with a unique condition.

Is there any way that I can check which condition was satisfied without having to change the function to log my conditions?

IF ( SOME CONDITION) RETURN 1;

IF ( SOME OTHER CONDITION ) RETURN 1;

What I need to do is check if the condition met was the first or the second.

EDIT:
I know this looks terrible but I also need to get the SQL code that made the condition work. I have no idea how this is possible but I'm hoping any solution.

Turn your function into an in-line table-valued function, and then pass your extra info in other columns. You can easily call it using CROSS APPLY in your FROM clause, and then use the result in WHERE predicates, SELECT lists, wherever you like. Plus being an ITVF, it’ll perform much better.


How to get the customer payment count for each row in MYSQL I have a payment system, and want to display the counter of each customer payment (first, second, third ...) and if it's the fist payment, display (New). How can i do this in one query ?

SELECT c.customer_name,
       p.payment_datetime,
       p.amount
FROM tbl_payments p
         LEFT JOIN tbl_customers c ON p.customer_id = c.customer_id
ORDER BY p.payment_datetime DESC;

+---------------+-------------------+--------+
| customer_name | payment_datetime  | amount |
+---------------+-------------------+--------+
| Cus1          | 2015              | 200$   |
| Cus2          | 2016              | 300$   |
| Cus1          | 2017              | 140$   |
| Cus1          | 2018              | 500$   |
+---------------+-------------------+--------+

The output must be

+---------------+-------------------+--------+--------+--------------------------------+
| customer_name | payment_datetime  | amount | status |                                |
+---------------+-------------------+--------+--------+--------------------------------+
| Cus1          | 2015              | 200$   | Old(1) | 1 -> First payment for "cus1"  |
| Cus2          | 2016              | 300$   | New    | New -> "cus2" have one payment |
| Cus1          | 2017              | 140$   | Old(2) | 2 -> Second payment for "cus1" |
| Cus1          | 2018              | 500$   | Old(3) | 3 -> Third payment for "cus1"  |
+---------------+-------------------+--------+--------+--------------------------------+

And on executing this query

SELECT c.customer_name,
       p.payment_datetime,
       p.amount
FROM tbl_payments p
         LEFT JOIN tbl_customers c ON p.customer_id = c.customer_id
WHERE p.payment_datetime > '2015' AND p.payment_datetime < '2019'
ORDER BY p.payment_datetime DESC;

Get the result :

+---------------+-------------------+--------+--------+--------------------------------+
| customer_name | payment_datetime  | amount | status |                                |
+---------------+-------------------+--------+--------+--------------------------------+
| Cus2          | 2016              | 300$   | New    | New -> "cus2" have one payment |
| Cus1          | 2017              | 140$   | Old(2) | 2 -> Second payment for "cus1" |
| Cus1          | 2018              | 500$   | Old(3) | 3 -> Third payment for "cus1"  |
+---------------+-------------------+--------+--------+--------------------------------+

You can use a CASE expression that checks for the existence of another payment of the same customer for each payment. If such a payment exists get the count of previous payments plus 1 and use that in the string indicating and "old" payment. Otherwise return the string indicating a "new" payment.

SELECT c1.customer_name,
       p1.payment_datetime,
       p1.amount,
       CASE
         WHEN EXISTS (SELECT *
                             FROM tbl_payments p2
                             WHERE p2.customer_id = p1.customer_id
                                   p2.payment_id <> p1.payment_id) THEN
           concat('Old(',
                  (SELECT count(*) + 1
                          FROM tbl_payments p2
                               WHERE p2.customer_id = p1.customer_id
                                     AND (p2.payment_datetime < p1.payment_datetime
                                           OR p2.payment_datetime = p1.payment_datetime
                                              AND p2.payment_id < p1.payment_id)),
                  ')')
         ELSE
           'New'
       END status
       FROM tbl_payments p1
            LEFT JOIN tbl_customers c1
                      ON p1.customer_id = c1.customer_id
       WHERE p.payment_datetime > '2015'
             AND p.payment_datetime < '2019';


Why isn't INFORMATION_SCHEMA not displayed in the MySQL Workbench UI by default? I am running MySQL Workbench 6.3 CE.  The following is the default look of the UI:

I can query the INFORMATION_SCHEMA database, but it is not shown by default under the SCHEMAS section in the navigator panel on the left.  Why is this, and how can I get this database to show up on the said panel?

Click on

Edit -> Preferences -> SQL Editor and then check the box "Show Metadata and Internal Schemas"

Click on Refresh icon on the right side of Schemas in the left side bar.

I am using 6.3CE. Older versions may have something similar.


What column is this alias referring to? I've read the following code a few times trying to figure out what it is doing.

  "A right outer join is that which returns the unmatched rows from the right tables with matched rows from the left table."

Ok, but the column "item_cnt" does not exist in the tables, then what column is it serving as an alias? I'm preparing for 1z0-047 so any help would be appreciated.

You executed the following query to display PRODUCT_NAME and the number of times the product has been ordered:

SELECT p.product_name, i.item_cnt
FROM (SELECT product_id, COUNT (*) item_cnt
FROM order_items
GROUP BY product_id) i RIGHT OUTER JOIN products p ON i.product_id = p.product_id;

I thought that the column should exist. But here is the Oracle docs page with all example schemas. If I search with ctrl+f for item_cnt, I find nothing: Oracle example schemas.

Ok, but the column "item_cnt" does not exist in the tables, then what column is it serving as an alias?

The column item_cnt may not exist in the base tables (we believe you, we didn't go and search the linked documents.)

But this part of the code:

FROM
     (SELECT product_id, COUNT (*) item_cnt
      FROM order_items
      GROUP BY product_id) i

provides a derived table. The column exists during the time that the query runs, in the derived table i. The (SELECT product_id, ... item_cnt ...) AS i defines a derived table, it names it i and this derived table has two columns, product_id and item_cnt (and item_cnt is an alias for the expression COUNT(*). There is no count(*) column either in any of the tables!)

So, later in the code, you can use them. The i.product_id is used in the ON condition and the i.item_cnt in the SELECT list.

(And it's a bit confusing that it seems to appear earlier and not later. That's because the SELECT part of a query is logically executed after the FROM part.)

FROM (...) i RIGHT OUTER JOIN products p ON ...

For the query execution, the two tables (aliased i and p) have no difference. It doesn't matter that one is a base table and the other a derived table. They can be used the same way. (and as far as we know, the products may not be a base table either. It can very well be a viewed table, usually referred as a view.)


Enabling TLS and TLS-MA simultaneously for different clients of a Oracle DB We have a requirement where we want to enable TLS-MA for some of the clients connecting to a specific Oracle database while the other clients can continue to use TLS with server certificate.

We are using Oracle 12.c in our environment.
Clients are connecting using the jdbc thin driver

I am an Oracle noob and i am not able to understand the documentation here
Will it work if i create 2 listeners; one with
SSL_CLIENT_AUTHENTICATION=TRUE
and another with
SSL_CLIENT_AUTHENTICATION=FALSE
Any pointer to reference documentation will be great.

SSL_CLIENT_AUTHENTICATION is a sqlnet.ora parameter - effectively a property of the database itself - not a listener parameter. You can only have one value per database instance (stand-alone or container), so there is no way to have the parameter set differently for separate clients. What you are attempting cannot be done as far as I know.


Unit testing with InnoDB transactions So, I'm running some unit tests against some InnoDB tables in MySQL.

Each unit test is wrapped in a transaction:

setUp:

SET autocommit = 0;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;

tearDown:

ROLLBACK;

Each test does one or more queries: SELECT, INSERT, UPDATE etc.

The first time I run the tests, they all pass. But if I run them again immediately, I start getting failures. If I wait a while and then run the test a third time, the failures go away.

Any insights to what might be causing this and how I could prevent it?

Perhaps it is order of doing things. IMHO a combination of SERIALIZABLE isolation and every other transaction test may be the problem.

For starters, what does SERIALIZABLE mean?

According to MySQL 5.0 Certification Study Guide

page 421 Bulletpoint 4 says

  SERIALIZABLE completely isolates the effects of one transaction from
  others. It is similar to REPEATABLE READ with the additional
  restriction that rows selected by one transaction cannot be changed by
  another until the first transaction finishes.

The next paragraph says that with SERIALIZABLE, one transaction cannot modify rows if another has merely read them.

SUGGESTIONS

You should use the default REPEATABLE READ isolation level
You should check the sequence of events and make sure you did not miss a ROLLBACK in between tests


Percona XtraDB cluster backup solution We have a 3 node percona xtradb cluster and two slaves attached to it. One of the slaves we want to use it for taking backups. We have GTID based replication setup between the slave and the Xtradb cluster. My question is if we are adding a new node to the xtradb cluster can we use the backups from the slave and restore on the new node. Also would we be able to avoid SST and do IST instead. I am not sure how that part works since the backups are from a slave. Any help will be appreciated.

If you are bothered by being down to essentially one node during an SST, then make it a 4-node cluster.

If you are worried about the heavy disk I/O, well, you have not avoided that -- the restore will be heavy I/O.

If you think you can always avoid SST by trying to trick it into IST, you will probably be mistaken.  Galera sometimes does SST when you thing it should be able to do IST.


Modelling a relationship involving books, book parts and reviews I would like to model something similar to the following scenario in a database built on MySQL:

I am managing reviews of texts. These texts can either be books or parts of books (e. g. different articles). There may be multiple reviews per text. It can also be guaranteed there is an identifying relationship between from an article to the containing book.

My approach was the following:

The table Review has an ID as a primary key, a name and a foreign key to the primary keys of Book and Book Part. Book and Book Part have primary keys and some custom attributes for each (see below). Additionally, there is an identifying 1-to-n relationship between Book and Book Part.

Is this a viable approach?

Edit: I created a picture for the model.

Description of attributes

Review
------
author - author of the review
rating - an integer describing how well the author liked the text
idBook - book the review is about
idBook_Part - book part the review is about

Book
----
author - author of the book
year - year the book was published

Book Part
---------
author - author of the book part
start page - page where the book part starts in the book
end page - page where the book part ends in the book
idBook - book that contains the part

Note: The attributes of Book and Book Part are intentionally not supersets of each other.

To make sure Review contains either an idBook or an idBook_Part I have written a MySQL trigger for INSERT and UPDATE statements that checks that exactly one of them is NULL.

References to literature are appreciated as well.

Alternative to your approach (exclusive arcs) which you may want to consider is to create a common parent table for Book, and BookPart.  For instance, ReviewableEntity:

ReviewableEntity
 (re_id INT NOT NULL ,
  re_type NOT NULL enum('book','part'),
  PK (re_id)

 );
 Book
  (
  re_id INT NOT NULL ,

  ... book attributes
   PK (re_id),
   FK (re_id) REFERENCES ReviewableEntity(re_id),
  )

 BookPart
  (
    re_id INT NOT NULL ,

  ... book part attributes
   PK (re_id),
   FK (re_id) REFERENCES ReviewableEntity(re_id),
  )

Now Review can have just one foreign key to ReviwableEntity .

There is a challenge to enforce that book details are always stored in book table, and book parts in BookPart table which can be solved in multiple ways .
  1. Create  stored procedures that insert/update data properly and deny direct manipulation of data with REVOKE
  2. Carry over re_type attribute to detail tables (Book and BookPart)
  3. Ignore the problem on db level, and deal with it by administrative means,
 e.g. force developers to insert into proper table(surprisingly , for this  particular case it's not the worst approach )  .

Update.

Compared to exclusive arcs a parent table has the following advantages :
  1. Adding new reviewable type is an easy and cheep operation (creating a new table + modifying enum) which can be performed online even for very large table.  Exclusive arc approach requires adding column to existing table and modifying code that enforces non-nullability of only one field
 2. Other tables can reference ReviewableEntity without knowing exact type
 3. Extracting common attributes from detail tables and storing them in common parent will allow queries to hit only one (parent) table thus eliminating joins.

On the other hand, exclusive arc is a way simpler design, and doesn't require extra table .

Also, your choice may be influenced by RDMBS features.  From what I remember, mysql didn't have CHECK constraints, so enforcing on db level NOT NULL for only one of IdBook, IdBookPart  will require trigger .

Another option (which I personally don't like) is to have join tables , book_part_review( IdBookPart, IdReview), and book_review(IdBook, IdReview).


SQL Server Backup Process Tuning I am working for a small company that has 20 full time web sites. And another 20-30 micro sites. We are having issues with the sites freezing every day after the SQL Server Agent backup is running. When the back up service was stopped we see no issues. Is this something common?

We are using SQL Server 2005 and the backups run on Sunday's and Wednesdays. I am also going to look into other things within the database itself, but this is what stood out.

-Edit-
I ran a perfmon on the machine for 3 days 5/27 to 5/31:

SQLServer:SQL Statistics\Batch Requests/sec
Average 355.412275
Median  306.610812
Min 108.9369962
Max 916.6332837
Std Deviation   141.7791552

SQLServer:General Statistics\User Connections
Average 83.14025501
Median  77
Min 52
Max 147
Std Deviation   19.27016231

SQLServer:Buffer Manager\Page life expectancy
Average 33.72386157
Median  21
Min 0
Max 246
Std Deviation   36.53737617

Looks like the maintenance backup that runs is not causing a problem while its running. It runs at 3am and on Sundays and Wednesdays and it completes successfully in about 2 hours.
I ran the SQL Server Profile:

I found one of the sp's that runs alot periodically takes a long time to execute.

Most of the time the Duration is 976 (microseconds) but sometimes its a big difference:
13922851
13025390
13021484
13019531
13018554
13017578
13016601

I am reading this correctly, right? 13 seconds?

If I run the same query from my machine in executes in a fraction of a second.

-Edit-

I have been looking at blocking and I haven't come up with much yet. I was just looking for recompile blocking in the SQL Server Profiler, but I did not see any events of that type (sp:recompile). I am still investigating blocking issues.

I also have noticed when I ran my last Profiler trace that there were a high number of sp_reset_connection events. Some of these sp_reset_connection 's were over 9 seconds long. Is this a normal type of behavior?

CHeck to see if you are having the backups stored on the same physical location as your data and log files. If you are trying to write all activity to one disk, that is going to be a likely bottleneck.

Also, if you are using a 3rd party tool (such as Litespeed) for backup compression, there is a chance you are consuming more CPU than you expected, and that could also result in less than optimal performance.

HTH


Copy a SQL Server database from one server to another on demand I have two servers, Prod and Test, both running SQL Server 2008 RTM.

Is it possible to have a PowerShell- or VBScript that could copy a database from one server to another?

The copy should of course include all contents of the database, with the exception of privileges so I don't lock myself out from editing the destination.

The destination database should be cleared/reset or completely overwritten, so both source and destination are identical.

Additionally, the connection to the source should be read-only and (if possible) only able to initiate the copy process without actually having access to the data.

I am slightly familiar with PowerShell, so if this only means connecting and starting a task it should be doable. Or do I have to look for advanced solutions?

Thank you.

I have two servers, Prod and Test, both running SQL Server 2008 RTM. Is it possible to have a PowerShell- or VBScript that could copy a database from one server to another?

You have couple of options :

Use Ola's solution to do backup on PROD server and then schedule the script in SQL Agent job to a. do backup, b. copy the backupfile to test server and c. restore the backup on the test server.
use PowerShell to do the job.

Use PowerShell To Copy A Database To Another Instance
backup and restore your databases using SQL Server PowerShell and SMO.
Backup and Restore SQL Server with the SQL Server 2012 PowerShell cmdlets

  The copy should of course include all contents of the database, with the exception of privileges so I don't lock myself out from editing the destination.

The database backup includes all the data in the database when the FULL backup was taken including the database users. When you restore the database on Test server, make sure to SYNC Orphan users.

  The destination database should be cleared/reset or completely overwritten, so both source and destination are identical.

When you restore the database, you have to use REPLACE option to overwrite the files with the new ones.

  I am slightly familiar with PowerShell, so if this only means connecting and starting a task it should be doable. Or do I have to look for advanced solutions?

You can schedule the script to run at specific time or just run it on demand. Refer to the links that I have suggested above.


MySql Invoice and Subscription Schema I have an ecommerce db built and I'm adding subscriptions. See the simplified structure below.

orders
id, customer_id

order_items
id, order_id, product_id

invoices
id, order_id

subscriptions
id, customer_id, product_id, run_date

The issue I'm having is the invoices table requires an order_id but a subscription just generates an invoice. I could put the original order_id in the subscription table to track the lineage but then it gets confusing as the invoices table would have duplicate order ids. I tried removing the order and order_items and keeping the invoices table and adding an invoice_items table to bypass the order ids altogether but I thought it makes more sense to keep them because people can also buy regular products like t-shirts and those are technically an order. Is there a recommended way to structure the tables so that invoices are generated correctly? Should orders be for single purchase products and invoices only for subscriptions?

The fact that you are finding it difficult to explain indicates that there is an issue with the design, the expectations, or the perceived requirements.

Are Subscriptions automatically paid for, such as through a credit card?  Is that why an Invoice seems unnecessary?  Or some other reason.

Your current state is:

Customers can purchase your products in one of two ways:

Orders require Invoices. Perhaps you need to bill them so as to be paid.
Subscriptions have no Invoices. Perhaps subscriptions are automatically paid through a credit card, PayPal, etc.

Since a Customer decides how to interact with you, that Customer may have several Subscriptions and also may place Orders. And I would think that the customer could cancel the Subscription whenever he wanted to.

Therefore, both you and your customer have reason to keep a complete trail of orders and fulfillments (whether Invoice or Subscription).  That means that you do potentially want an Invoice of some sort even for Subscriptions.

Here are two ways to handle this, namely treat them as the same thing or as two different things.

Subscriptions create an Invoice for the fulfillment of a subscription instance.  The subscription Invoice could have a different scheme (e.g. SUB-Number-Date) for identifying the subscription Invoice than you would use for an Invoice for an Order (e.g INV:InvoiceNumber).  [I would guess that the subscription Invoice would be marked as already paid.]
Create a Subscriptions log with a header for the subscription and a log to track each fulfillment.  That allows you to track the details of each time Customers have their Subscriptions fulfilled. In the case that a subscription is cancelled early, it could be used to refund the customer the unused sum.

How your organization views your Customers (person or businesses) will help you determine what is the best approach for your business.

What would I do?  I would tend would separate the Order Invoice from the more complex Subscription Invoice.  When needed a summary of the totality of Orders, Subscriptions, and (returns, reshipments, etc) can be produced for your company and for your customer.


SQLAgentReaderRole and SQL AD ID login case affecting ability to edit jobs I have AD ID users on a SQL 2008 server that can't edit their own jobs.  They can create them, but in order to edit them, they have to delete them and then re-add them.  The issue is explained here: http://blogs.msdn.com/b/sqlserverfaq/archive/2009/06/08/owner-of-the-job-will-not-be-able-to-modify-edit-the-job-in-sql-server.aspx

I did further research and figured out in my particular case it's because I added AD ID users using the CREATE LOGIN [domain\user] syntax.  Using that method, the case of the AD ID is what you specify in the brackets.

Is there anyway to create users with the correct Windows AD ID cases so that I'm not having to figure it out then modify the logins for each AD ID user?

Furthermore, I was using this script to get the correct casing for a particular user:

execute as login = 'usa\johndoe'
SELECT SUSER_SNAME()
revert;

But it was returning all lowercase for me, yet all uppercase when I asked the user to run SELECT SUSER_SNAME() and give me the result.  The correct is all uppercase (well, in order for that user to be able to edit jobs).

I'm at a loss and very frustrated.

--
I've found the best (only?) way to get the correct case is using the SQL interface to add a user, clicking search, typing the AD ID, then clicking check names.  For the example above, it returns all uppercase, despite that script example returning all lowercase.

Is there anyway to create users with the correct Windows AD ID cases
  so that I'm not having to figure it out then modify the logins for
  each AD ID user?

Built in, you already found it -- use the directory searching interface in the GUI. Alternatively, you could use SQL CLR (as mentioned), or write a PowerShell script. You could certainly write a routine to clean up an instance using either method.

Note that the GUI rewrites the domain name, even if it's incorrectly cased when entered, but not the login name itself. (You can see this by manually typing something in, then hitting the Script button.)

  But it was returning all lowercase for me, yet all uppercase when I
  asked the user to run SELECT SUSER_SNAME() and give me the result. The
  correct is all uppercase (...).

Under impersonation, SUSER_SNAME() returns the principal name as entered. If you try the test again with a different casing, it will return the exact name you typed in. The only reason the impersonation works using an incorrectly-cased name is because the Windows collation says the comparison of the two is a match.

Not under impersonation, there are two cases: (a) group login: the principal name is returned from what Windows supplies, which is always correctly-cased, and (b) individual login: the principal name is returned from what is entered for the name in SQL Server (it doesn't go out to Windows to double-check).

Now, having said all that, try as I might, I cannot replicate this problem using 2008 R2 management tools. If you're still running 2008 (non-R2) management tools, one option would be to try upgrading the tools, because this appears to be a bug in Management Studio itself.


MongoDB Implicit And I'm trying to understand mongodb queries, but one of my queries is returning a record that it shouldn't.

I am trying to call

db.user.find({'referral.money':{'$ne':0, '$ne':null}})

to get all users who have some cents in their referral data. Some users have referral data which doesn't include "money" and others don't have any referral data at all.

The problem is that after executing that command, the following record is returned:

{
    "_id" : ObjectId("58362414af5d810d1c9ec7f0"),
    "user_id" : 22,
    "referral" : {
        "_cls" : "ReferralProgramA",
        "end_dtm" : ISODate("2016-12-23T23:19:48.014Z"),
        "money" : 0
    }
}

If I'm correct it shouldn't be returned since "money" is obviously 0.

My collections and records are created with python's mongoengine hence the "_cls" fields for inheritance.

I'm making the find({}) call with a raw statement so mongoengine isn't messing it up( I think ).

The problem is that your query object uses the same key ($ne) twice, so the second value is overwriting the first. Most languages do not support duplicate keys in a standard object / hash / dictionary representation.

The outcome is more evident if you evaluate your query object in the mongo shell:

> query = {'referral.money':{'$ne':0, '$ne':null}}
{
  "referral.money": {
    "$ne": null
  }
}

However, you could use a different operator with equivalent outcome in an implicit $and.

For your example query, $exists: true is equivalent to $ne: null so the following would work:

 db.user.find({'referral.money':{'$ne':0, $exists: true}})

Alternatively you could rewrite the query to use an explicit $and:

 db.user.find({$and: [{'referral.money': {'$ne':0}}, {'referral.money':{'$ne':null}} ]})

Note: if you are storing currency values as floating points you should be wary of rounding with floating point numbers. The MongoDB manual has examples of how to Model Monetary Data.


Using DateTime Variable In Where Clause I am trying to pull all data where a sale date is >= to a certain datetime.  When I execute my query I get an error of:

  Msg 241, Level 16, State 1, Line 15
  Conversion failed when converting date and/or time from character string.

What should I alter to get his query operational?
Below is sample DDL

Declare @datetime datetime = GetDate(), @sql varchar(max)

Create Table #dinonuggets
(
  saledate datetime
  ,item varchar(500)
)

Insert Into #dinonuggets Values
('2016-03-30 12:02:00.000','chair')
,('2016-03-04 08:12:48.000','stove')
,('2016-03-07 09:37:39.000','washer')

Set @sql = 'Select * FROM #dinonuggets WHERE saledate >= '+@datetime+' '
Print @sql
Exec (@sql)

My expected output would be
03/30/2016 12:02:00.000 chair
 as that is the only data that meets the criteria

Your issue relates to the way you are building your dynamic SQL. If you look at the output from your PRINT statement, there are no quotes around your @datetime.  Additionally, selecting from a #temp table within dynamic SQL isn't going to do what you want it to do. #dynonuggets is going to be out-of-scope for your dynamic SQL. If you really do need to work with a dynamic table, you'll need to make a global temporary table, named ##dynonuggets.

I'll assume, DynoNuggets is a real table, and you have to use dynamic SQL, and your example is just simplified for your question.

You could add quotes, but the more appropriate solution is to use sp_executesql to parameterize your dynamic SQL.

Try this:

DECLARE @datetime datetime = GETDATE(),
        @sql nvarchar(max),
        @params nvarchar(1000)

CREATE TABLE DinoNuggets
(
  saledate datetime
  ,item varchar(500)
)

INSERT INTO DinoNuggets VALUES
('2016-03-30 12:02:00.000','chair')
,('2016-03-04 08:12:48.000','stove')
,('2016-03-07 09:37:39.000','washer')

SET @sql = N'SELECT * FROM DinoNuggets WHERE saledate >= @ParamValue';
SET @Params = N'@ParamValue datetime';
PRINT @sql;
EXEC sp_executesql @sql, @params, @ParamValue = @datetime


Update row count on corrupt table I have a corrupt table, 1 dodgy page about 1.6 million rows into a 2.9 million row table. I want to attempt this:

ALTER DATABASE [CorruptDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DBCC CHECKDB (CorruptDB, REPAIR_ALLOW_DATA_LOSS) WITH TABLOCK;

..but I want to know how I can be certain the number of rows before and after the data loss. If I do:

UPDATE STATISTICS [corrupt_table] WITH FULLSCAN;

It fails as there is of course a torn page half way down. The same goes for:

SELECT COUNT(*) FROM [corrupt_table];

I suppose my question is, will a non-fullscan UPDATE STATISTICS definitely update the number of rows correctly?

I know it's only one torn page, that should equate to about 100 rows in this table, I just want to know for sure.

Note: This is not on a production system, it's just local on my laptop (2012 Std x64) and the actual issue has been resolved (with a restore) - I've taken a copy of the DB to play with.

Dealing with the corruption:

First: Any chance you have a good backup from before the page went bad?  If so then you may be able to do a page restore rather than repair data loss.  This would certainly be your best bet.  Remember that in most cases the corruption that caused the error on disk does not affect your transaction logs. So as long as your full backup has a good page then you can restore over the bad page from the full backup, then apply all of the transaction logs (restoring only that single page of course) from that point on.  Of course this only works if you have full recovery.
 https://msdn.microsoft.com/en-us/ms175168.aspx?f=255&MSPPError=-2147217396

Also you might take a look at what was done here: http://stevestedman.com/2015/04/a-weekend-full-of-database-corruption/

If you look at Brent Ozar's answer he used a comparison of the non-clustered index vs the clustered (all of the damage was on the clustered index like I'm guessing yours is if it's a single page) to find the actual damage.  He was also then able to recover the missing information by the same method (clustered index vs non-clustered).

The actual question

You might try using sys.partitions for your counts. They are almost always correct in my experience.  No guarantees given your corruption but it should be good, particularly if you compare multiple indexes.

SELECT * FROM sys.partitions WHERE object_id = OBJECT_ID('db.schema.table')

If the corruption is in a single page (or if there is no corruption in at least one of the non-clustered indexes) then you will have at least one index where there is no problem and sys.partitions should be correct.  Of course you can also do this to force your count to look at the uncorrupted index.

SELECT COUNT(1) FROM dbo.table WITH (INDEX = [ix_table])


How I can corrupt a data base page and again restore the same I want to corrupt a database page, and restore the same page.

How do I do that?

I love this question!

To demo, I'm going to use a local copy of the Fitness database from the Stack Overflow data dump.

First, make sure your database is in FULL recovery, and take a full and log backup.

BACKUP DATABASE [FITNESS]
TO  DISK = N'D:\Backup\FITNESS.bak'
WITH FORMAT, INIT, STATS = 1, COMPRESSION, CHECKSUM
GO

BACKUP LOG [FITNESS]
TO  DISK = N'D:\Backup\FITNESS.trn'
WITH FORMAT, INIT, STATS = 1, COMPRESSION, CHECKSUM
GO

Next, head into your database, and grab a page to corrupt. You should be able to use this query on 2012 plus. It creates a simple dynamic block with DBCC WRITEPAGE

USE FITNESS

SELECT TOP 1
    N'DBCC WRITEPAGE ('
  + CAST([dddpa].[database_id] AS NVARCHAR(10)) + N', '
  + CAST([dddpa].[allocated_page_file_id] AS NVARCHAR(10)) + N', '
  + CAST([dddpa].[allocated_page_page_id] AS NVARCHAR(10)) + N', '
  + N'0' + N', '
  + N'4' + N', '
  + N'0x0000008A'
  + N', 1) WITH NO_INFOMSGS' AS [cmd]
FROM
    [sys].[dm_db_database_page_allocations](DB_ID(), NULL, NULL, NULL, 'DETAILED') AS [dddpa]
WHERE [dddpa].[allocated_page_page_id] > 250;

After that, you'll need to set your database to SINGLE_USER and corrupt the page

ALTER DATABASE FITNESS SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC WRITEPAGE ( INSERT YOUR DBCC WRITEPAGE COMMAND HERE )

ALTER DATABASE FITNESS SET MULTI_USER WITH ROLLBACK IMMEDIATE

Remember to take note of which page you're overwriting. For me, it was page 280. Now I can run a restore sequence like this:

USE master

RESTORE DATABASE FITNESS
PAGE='1:280'
FROM DISK = N'D:\Backup\FITNESS.bak'
WITH NORECOVERY;

RESTORE LOG FITNESS
FROM DISK = N'D:\Backup\FITNESS.trn'
WITH NORECOVERY;

BACKUP LOG [FITNESS]
TO  DISK = N'D:\Backup\FITNESS_1.trn'
WITH FORMAT, INIT, STATS = 1, COMPRESSION, CHECKSUM, NORECOVERY

RESTORE LOG FITNESS
FROM DISK = N'D:\Backup\FITNESS_1.trn'
WITH NORECOVERY;

RESTORE DATABASE FITNESS
WITH RECOVERY;

If you want more practice, I highly recommend Steve Stedman's database corruption challenge.

Enjoy!


Cleaning Oracle In-Active Sessions There a are huge number of in-active sessions persisting in our database.How can we clean these in-active sessions?

Database - Oracle 10g R2 with ASM

DB Size - 5TB
SGA - 78GB
PGA - 10GB
Maximum Sessions allowed - 1200
Average number of In-Active sessions - 150
Maximum In-Active Sessions - 900

I'm not sure that there is a problem.

A session in V$SESSION has a status of "INACTIVE" if it is not executing a SQL statement at that particular instant in time.  If a user opens a dedicated session in a client-server application and is using the application intensively, it would not be at all unusual for the session to be "INACTIVE" 99% of the time because the vast majority of the time is spent waiting for the human to read and process the data on the screen.  The fact that a session is "INACTIVE" doesn't imply that it wasn't "ACTIVE" a few seconds ago or that it won't be "ACTIVE" again a few seconds from now.  The LAST_CALL_ET column in V$SESSION shows you the number of seconds since a session was last in an "ACTIVE" status if the session is currently in an "INACTIVE" status.  That may give you some idea whether you are dealing with sessions that have been active for a while or not.

If you have a three-tier application, your middle tier servers will generally maintain a pool of database connections.  The middle tier opens a number of connections and keeps them open more or less indefinitely.  When users need to interact with the database, they get a connection from the pool, run a query, and return the connection to the pool.  At peak workloads, connection pooling can concentrate a large number of application users into a relatively smaller number of database connections which will mean that the sessions spend a larger fraction of their time in an "ACTIVE" status.  But during periods of low usage, the middle tier probably maintains a bunch of "INACTIVE" sessions.

In either of these cases, having a number of "INACTIVE" sessions is not a bad thing.  And "cleaning up" those inactive sessions will cause errors in the applications that have opened them.

If you have a large number of inactive sessions because the client processes have failed (i.e. the client application crashed and the database is unaware of that fact), you could use the sqlnet.expire_time parameter on the server's sqlnet.ora file to periodically send a probe packet to the client machine.  That may negatively impact performance but it will allow the database to more quickly discover that the client process has failed.

If you have a large number of inactive sessions because your middle tier servers have created too many connections to the database in their connection pools, you would need to talk with the middle tier admins to ask them to reduce the number of connections they open in their connection pools.  If they have recently added more servers to the middle tier farm, they may need to reduce the number of connections each server maintains in its connection pool.

If your problem is that too much RAM is being allocated to all the dedicated server connections that need to be open (particularly common if you've got a lot of client server applications), you could configure Oracle to support shared server connections since that reduces the amount of memory that each connection requires.


Slow queries to remote PostgreSQL db I am running a Drupal site that is backed by Postgres. This is my first time running something with Postgres; I come from the MySQL world.

For doing dev work on the site, I connect to a VPN and tunnel my traffic through a machine that has an IP that is allowed in pg_hba.conf.

The queries are coming back very slow. My logs tell me that loading the homepage on the dev server:

  Executed 86 queries in 84.26 ms

And on my local machine (with the remote connection):

  Executed 87 queries in 22368.05 ms

Queries are 80% SELECT, with a few INSERT INTOs.

I have done some rudimentary tuning also on the db server, hoping to speed things up for the remote connection (the tuning did not, but it certainly made the dev server's queries come back faster!).

On the server's db config files, I have turned of ssl and only allowed connections from localhost and ips within the VPN. I am authenticating db users via md5.

Could the hold up be going through this VPN? Or could it have to do with the fact that the db is being accessed from two different places with the same username/password?

If the only difference is local vs remote, then the problem has to be your round trip times.  The actual internal query execution is not aware of the type of connection used.  However I also wonder if you are also running your dev system on a different database.  In that case, caching could be very different and could also make a significant difference.

I see a few ways to solve this problem.  The first is just to have a dev copy of the database for the site on your dev system.  This data could then be sanitized so that if your dev system is compromised or lost (supposing it is a laptop for example) the data exposure is minimal.  Then you can test performance on a system which can do caching specific to your dev work and will not have the round trip.

The second approach is to build a much faster tunnel, and to possibly throw a lot more RAM at the db server.


Error attaching database after deleting NDF file I have deleted .ndf file by mistake from a SQL Server 2008 database. While trying to attach a previous copy, SSMS raises this error:

  Attach database failed for Server 'MDSWIN34'.
  (Microsoft.SqlServer.Smo)

  ADDITIONAL INFORMATION:

  An exception occurred while executing a Transact-SQL statement or
  batch. (Microsoft.SqlServer.ConnectionInfo)

  The operating system returned error 38(Reached the end of the file.)
  to SQL Server during a read at offset 0000000000000000 in file
  'C:\Program Files\Microsoft SQL
  Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\template.ndf'. Additional
  messages in the SQL Server error log and system event log may provide
  more detail. This is a severe system-level error condition that
  threatens database integrity and must be corrected immediately.
  Complete a full database consistency check (DBCC CHECKDB). This error
  can be caused by many factors; for more information, see SQL Server
  Books Online. (Microsoft SQL Server, Error: 823)

What is the reason for this? Have I lost all of my data?

Based on all of the information you've provided so far, I'd have to say that, yes, you've lost all of your data. From what it sounds like, I can only guess that something like this happened:

your disk filled up
you saw this big .ndf file
you tried to delete it (presumably because you didn't realize it was a secondary data file)
you couldn't delete it, because it was in use
you stopped SQL Server (or detached the database)
now you could delete the file - yay?

The problem is, the .ndf file is a crucial part of your database. The fact that you couldn't have possibly deleted it without detaching the database or shutting down SQL Server should have been a clue.

Have you not taken any backups of this database since adding the .ndf file? Preferably from as close to the point that you deleted the file as possible?

Now, if you don't have a database backup (or a simple file copy of the .ndf and all the other files in a consistent state, after a clean detach), it is possible that not ALL hope is lost. You may be able to recover the file using some kind of undelete software or taking it to a data recovery service. Provided that the location on disk where the file used to live has not been zeroed out, formatted or re-occupied. You might get lucky, but odds are what you've done here is essentially the same as dropping a donut in the toilet. You might be able to get part of it back, but you still might not want it.

Unfortunately this is called learning why backups are important, the hard way. And also serves as a good warning to others that they shouldn't be deleting files on their system if they don't know what they are for.


Reseed a newly created table I have create a new table on SQL Server 2008.

This table has an identity column, defined like this:

[ID] [int] IDENTITY(1,1) NOT NULL

However when I insert any data, the first record starts from 0, instead of 1.

I want the records to start from 1. Why does this not work?

If I delete the record and reseed it to 0, it works.

Delete from [tablename]
DBCC CHECKIDENT('[tablename]', RESEED, 0)

However it does not work on newly created tables.

Even if I delete and reseed a newly created table, it still starts adding record from 0, instead of 1.

Can you anyone please suggest what I am doing wrong.

Thanks

This behaviour is documented, but counter-intuitive:

  Current identity value is set to the new_reseed_value. If no rows have
  been inserted into the table since the table was created, or if all
  rows have been removed by using the TRUNCATE TABLE statement, the
  first row inserted after you run DBCC CHECKIDENT uses new_reseed_value
  as the identity. Otherwise, the next row inserted uses
  new_reseed_value + the current increment value.

It was acknowledged as a bug (on the now-defunct Microsoft Connect site), but not one that will be fixed. Workarounds include avoiding using the RESEED option on newly-created tables, or to use a higher value that will work in all cases but may result in gaps. Gaps are all but inevitable with IDENTITY anyway. If you must do the reseed, wrap it in a check like:

IF EXISTS
(
    SELECT 1
    FROM sys.identity_columns
    WHERE
        [object_id] = OBJECT_ID(N'dbo.T1', N'U')
        AND last_value IS NOT NULL
)
BEGIN
    DBCC CHECKIDENT('dbo.T1', 'RESEED', 0)
END;

To be clear, creating a table and adding a row will result in the first identity value being used:

-- Tested on SQL Server 2008 SP3 CU8
-- build 10.0.5828
CREATE TABLE dbo.T1 (id int IDENTITY(1, 1));
INSERT dbo.T1 DEFAULT VALUES;
SELECT id FROM dbo.T1 AS t;


TOP (100) PERCENT when designing a view When designing a view (adding Order By clause) the wizard in SSMS automatically adds

  TOP (100) PERCENT

What do we need it for? Are there any performance consideratons regarding  TOP (100) PERCENT?

Thank you

ORDER BY is not allowed on VIEWS unless you set a TOP() clause.

The wizard adds it automatically just to avoid the error.

Have a look at CREATE VIEW on MS Docs.

  The SELECT clauses in a view definition cannot include the following:

  An ORDER BY clause, unless there is also a TOP clause in the select list of the SELECT statement

    Important:
    The ORDER BY clause is used only to determine the rows that are returned by the
    TOP or OFFSET clause in the view definition. The ORDER BY clause does not
    guarantee ordered results when the view is queried, unless ORDER BY is also
    specified in the query itself.

  The INTO keyword
  The OPTION clause
  A reference to a temporary table or a table variable.

You should add ORDER BY clause in your query:

SELECT   F1, F2, F3, F4
FROM     YOUR_VIEW
ORDER BY F1, F2


Avoid duplication when using other table in WHERE and JOIN clause in Postgres I have 3 tables (devices, shops, device_shops_versions). devices can have many device_shops_versions.

device_shops_versions table:

+----+---------+-----------+---------+---------------------------+
| id | version | device_id | shop_id | updated_at                |
+----+---------+-----------+---------+---------------------------+
| 1  | 113     | 1         | 1       | 2014-05-05 17:03:25.04055 |
| 2  | 702     | 1         | 1       | 2015-05-05 17:03:25.04055 |
| 3  | 410     | 2         | 1       | 2014-05-30 09:29:44.88214 |
| 4  | 440     | 4         | 2       | 2013-06-30 08:28:42.98214 |
+----+---------+-----------+---------+---------------------------+

1) I want to return all the devices using device_id that are in device_shops_versions for a specific shop_id.

2) I want to display the most recent version of every device in device_shops_versions - with the most recent updated_at.

I should have something like:

+----+--------------+---------+-----------+---------+--------------------------+
| id | device_model | version | device_id | shop_id | updated_at               |
+----+--------------+---------+-----------+---------+--------------------------+
| 1  | 'iphone'     | 702     | 1         | 1       | 2015-05-05 17:03:25.040  |
| 2  | 'test'       | 410     | 2         | 1       | 2014-05-30 09:28:44.982  |
+----+--------------+---------+-----------+---------+--------------------------+

My query looks like this but I'm not sure I'm correct and also I would like to know if I can avoid the duplication :

SELECT *
FROM devices
INNER JOIN
  (SELECT device_shops_versions.device_id,
          MAX(device_shops_versions.updated_at)
   FROM device_shops_versions
   GROUP BY device_id ) dcv ON devices.id = dcv.device_id
WHERE devices.id IN
    (SELECT device_shops_versions.device_id
     FROM device_shops_versions
     WHERE device_shops_versions.shop_id = 1);

Use DISTINCT ON, it's considerably shorter, simpler and probably also faster:

SELECT DISTINCT ON (device_id) *
FROM   devices_shop_versions
WHERE  shop_id = 1
ORDER  BY device_id, updated_at DESC NULLS LAST;

Detailed explanation:

Select first row in each GROUP BY group?

If your table is big, you have many rows per device_id and you need to optimize performance, a recursive CTE might be faster, yet. Detailed instructions:

Optimize GROUP BY query to retrieve latest record per user

Make it a subquery to integrate in a bigger query:

SELECT mv.id, d.device_name, u.user_name, mv.version, mv.device_id, mv.shop_id, mv.updated_at
FROM  (
   -- query from above
   ) mv
JOIN   devices d on d.id = mv.device_id
JOIN   users   u on u.id = d.user_id
-- ORDER BY ???;  -- optional

Demo on rextester.

It's typically cheaper to join to more tables after excluding irrelevant rows.


How to disable instance recovery in ORACLE? I want my DB to stop instance recovery during opening, how to do it?

I want it because it crashes when trying to do instance recovery, I am testing some cases and want to disable it

Is there any possibility?

Instance Recovery is necessary to get the database back to a consistent state after shutdown abort or some other abnormal shutdown event. Oracle is never going to let you open the database in an inconsistent state, so no, there is no possibility that you can disable it. If Oracle is crashing during Instance Recovery you need to get in touch with Oracle Support or revert to a backup.
The concepts guide has essential reading about instances and more particularly what Instance Recovery is and why it is sometimes needed:

If an instance of an open database fails, either because of a SHUTDOWN ABORT statement or abnormal termination, then the following situations can result:

Data blocks committed by a transaction are not written to the data files and appear only in the online redo log. These changes must be reapplied to the database.

The data files contains changes that had not been committed when the instance failed. These changes must be rolled back to ensure transactional consistency.

Instance recovery uses only online redo log files and current online data files to synchronize the data files and ensure that they are consistent.

If the Instance Recovery crash is caused by a corrupt datafile you could mount the instance, drop the offending datafile and then open the database - but you will lose the tablespace that file is in.


Microsoft Access constrain view by date in outer query I'm working on a query in Microsoft access and it does not appear to allow me to filter the inline view by a date that exists in the table outside of the inline view.    A simplified version that exibits the problem is below

select a.orderid, a.ocustomerid
from orders AS a
         INNER join (
SELECT b.orderid, b.ocustomerid
FROM orders AS b
WHERE b.opromisedshipdate = a.opromisedshipdate
) AS c ON c.orderid = a.orderid

Access returns an error looking for the parameter a.opromisedshipdate.

Any idea how I can achieve this filter?

In the real world query I'm doing this to provide aggregated order information (the inline view) next to non aggregated order information but I need the aggregated information to only go up to the date that is a part of the current order.

Thanks in advance.

How about:

SELECT a.orderid,
       a.ocustomerid
FROM   orders AS a
       INNER JOIN (SELECT b.orderid,
                          b.ocustomerid
                          b.opromisedshipdate
                   FROM   orders AS b
                   ) AS c
       ON c.orderid = a.orderid
WHERE  c.opromisedshipdate = a.opromisedshipdate


Replication of only updates and inserts I have two sql servers, in the first I want to keep the data from the last three months and in the second I want to keep all the data with the purpose of getting historical reports.
I know how to do a transactional replica but I don't know how to replicate only the inserts and updates, I don't want to replicate the deletions.
Is it possible to do this with replication between servers or do you have to program it yourself through triggers?

In the Article Properties, you can set the publication to not replicate deletes.
On the Article Properties page, just scroll down to Delete delivery format and change it to "Do not replicate..."
Note that if you're not replicating deletes, you risk a scenario where a row or update errors and causes issues. This will only happen if a deleted row has a value that is reused on the primary key or other unique constraint. If you're using an IDENTITY column for PK (and no other unique indexes on the replicated data), this would only happen only if the ID is reseeded or someone uses IDENTITY_INSERT. It's a more common problem to encounter when using a natural key. If you anticipate this being a problem, you can put instead of triggers on the replicated copy to convert inserts to upserts


SQL Server Database Experimentation Assistant Analysis Reports I am using the Database Experimentation Assistant v2.6 to capture a workload using extended events (.xel) on SQL Server 2017 that is installed on an Azure VM.  I'm then replaying it on a Azure SQL Database Managed Instance.  The capture and the replay work great. I'm getting multiple .xel files from the replay, 15 from the VM and 7 from the Managed Instance.

My issue is when I create the analysis report it only allows me to select 1 .xel file in each of the the "Trace for Target 1 SQL Server" and "Trace for Target 2 SQL Server" fields to compare.  My expectation is that I can run one report for the entire replay by selecting the directories the files are in but this does not appear to be an option?  When I try to select just the directory it will not allow me to exit the directory window.  Below is the screen I'm referring to :

I've looked through multiple articles but the only ones I've found, including this one from Microsoft, refer to selecting only one file and normally a .trc file not a .xel file.

How can I compare multiple .xel files at once?

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

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

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


Bad Windows Authentication sign on I do not have install privileges on my machine.

Another user who has admin privileges installed it, but told the system to use Windows Authentication for the sign on.

Is there a way they can change the User Instances from one Windows Authentication to another without having to reinstall?

Yes. Someone who has administrative rights (aka admin rights) to the SQL instance can change this by using SQL Server Management Studio.