Can we replicate or restrict specific databases in MongoDB In MySQL, we are having replicate-do-db/replicate-ignore-db during replication, where we can replicate or restrict specific databases from Master to Slave.

In the similar way, is there any specific configuration or work around in MongoDB replication to restrict specific databases and collections during data sync.

If not, is there any other way to sync only specific database or collections from Master to Slave.

I am using MongoDB 3.6

Replica sets in MongoDB are designed for data redundancy and failover, so all data-bearing members of a replica set currently contain the same databases & collections as at MongoDB 4.2. A member with a partial data set would not be able to fulfil either of these key design goals for a replica set deployment.

There is a relevant server issue you can watch & upvote: SERVER-1559: Ability for a replica set node to only have a subset of the databases or collections. This is currently on the development backlog but there is an alternative approach you can use in the interim.

If you want to support non-standard replication/sync scenarios, I would look into writing a small program to replicate desired changes using the MongoDB Change Streams API. Change stream events are based on the replication oplog and allow you to filter and process relevant events matching on criteria such as namespace, operation type, or field values. You can also modify change stream output using additional aggregation stages (for example, projecting a subset of fields or adding calculated fields). Most importantly, the Change Streams API gives you a supported upgrade path to future versions of the MongoDB server.

In MongoDB 3.6 you can only open change streams at a collection level. Since you are interested in changes at a database or deployment level, it would be worth upgrading to MongoDB 4.0 which includes more comprehensive change stream support.


CPU usage among Numa nodes in sql server I need to understand the below scenario

On Sql server 2014 with compatibility mode of 2008R2, having 80 logical processors with 20 distributed amongst 4 numa nodes. I. E numa node 1 has 0-19 , numa node 2 has 20-39 and so on.

While checking the cpu usage, we believe workload should be equally distributed amongst all of them. Sometime i see like equal distribution to some extent amongst all numa nodes to their processors.

But some time lets say during maintainence window like backup or index main jobs or runs, why is that lets say numa node 0 and 1 will not have any equal usage compares to 2 and 3 or vice versa. As far as i think it should be equally distributed.

Note we are running enterprise core edition.

Is this expected or something missing in the settings of these cores?

Update@ As said by Randolph, some process can go single threaded, i got it, but why out of the 80 they are using 80-90 % of cpu on few processors while other sitting idle. Cant the workload be equally divided when say there are 10-15 with high usage showing while remaining 50 odd processors sitting idle

The short answer is that the behavior you indicate is expected. A single thread by definition only exists on a single logical processor, so for a large single-thread operation, you will not see it equally spread across all nodes. It can't be since it only exists in one place.

The longer answer is that SQL Server backup operations use one thread per data file for reading, and one thread per backup file being written to. The reader thread will create a set of buffers to store information which will subsequently be read from by the writing thread.

For instance if your database uses one file on the D:\ drive, and you are backing up to a single file on the E:\ drive, you will have one thread reading from D:\ and storing that information in buffer, and another thread reading from information in the buffers and writing it to the single file on the E:\ drive.

If you back up your database to 4 files, you will have 5 threads: one reading from D:\ and putting information into the buffers, and four getting information from buffers and writing to the separate files on E:.

In NUMA architecture, the design is that these related threads will exist on the same node to optimize memory access - after all, the four writer threads want information from the buffers that the associated reader thread is using. It only makes sense to keep them on the same node.

For further reading see this post by Shanky_621.


I looking for a solution to design database for property real estate I looking for right method how can I design part of database schema for my  real estate website

I have table "proprety_real_estat" contain  Country, city, town and type of property  (screenshot 01)

screenshot 01

my problem:

I want to add many others fields home features checklist below

Wifi (yes/no)
TV(yes/no)
Gas(yes/no)
Electric(yes/no)
Garage(yes/no)
Parking(yes/no)
Balcony(yes/no)
...
...
and many others (screenshot 02)

screenshot 02

my quesitons :
01- if I add all this fields in the same table "proprety_real_estat" ??
02- if I make in JSON file after that I will save the path of this file in field ??

else if there are a better solution than mine ??

thank you in advance

Depends what do you want to optimize for.

For smallest space consumption the easiest way is to create a field where you store the features as bits. For example:

Cuisine - 1
Internet - 2
TV - 4

Then having internet and TV would be 110 = 6. But this is going to be hard to search for and requires some magic to do bitwise operations to extract the features.

A middle ground would be to use SET and store the features which the real estate has aka. values that are set to yes. The downside is that each time you need a new feature it requires schema change and

  A SET column can have a maximum of 64 distinct members. A table can
  have no more than 255 unique element list definitions among its ENUM
  and SET columns considered as a group. For more information on this
  limit, see Section C.10.5, “Limits Imposed by .frm File Structure”.

(from http://dev.mysql.com/doc/refman/5.7/en/set.html)

The most flexible and probably the best performing option for search is to have a feature table where you store all the possible features. And there's a table to store what property has what feature. The downside of this approach is space. Assuming your using InnoDB every row will have a 13 bytes overhead + the two columns (property_id, feature_id) ~ 6 bytes. So you can expect something around 20 bytes * number of properties * avg number of features per properties. (To compare the first option will need number of unique features bits * number of properties)

create table feature (
    id smallint unsigned PRIMARY KEY AUTO_INCREMENT,
    name varchar(64) NOT NULL,
    UNIQUE KEY (name)
);

create table property_feature (
    property_id int unsigned NOT NULL,
    feature_id smallint unsigned NOT NULL,
    PRIMARY KEY (property_id, feature_id)
);

As per your example assuming 100000 properties the theoretical calculations would be:

30 bits * 100000 = 366kb
20 bytes * 100000 * 18 = 35MB (still not something to worry about though)

Note: in reality you would probably store the 30 bits in an unsinged int so it will take 4 bytes. So on disk it would consume around 390kb.


Force View's query plan to update? I have a View whose query plan appears to be cached, is there a way to force the View's plan to be recalculated on each access?

It turns out my problem is due to the VIEW containing aggregate functions.

What looks like a cached query plan is really the planner (by design) not pushing a WHERE clause applied outside the VIEW into the VIEW.

Problem explained.


Strategy for Repair Database on SQL Server 2008 R2 Standard (one guy pushed green button instead of blue, three racks left as was his original target, probably green is nicer then blue, on this SAN are saved database files)

after this accident I'm run consistency check against databases
but one of databases returns consistency error

  Failed:(-1073548784) Executing the query "DBCC CHECKDB(N'XxxXxxx')  WITH NO_INFOMSGS
      " failed with the following error: "Extent (1:511776) in database ID 9 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
      Extent (1:511784) in database ID 9 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

  .
     .
     .

  Extent (1:994048) in database ID 9 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
      The Index Allocation Map (IAM) page (0:0) is pointed to by the previous pointer of IAM page (1:3318703) in object ID 0,
      index ID -1, partition ID 0, alloc unit ID 72057594374717440 (type Unknown), but it was not detected in the scan.
      CHECKDB found 32446 allocation errors and 0 consistency errors not associated with any single object.
      CHECKDB found 32446 allocation errors and 0 consistency errors in database 'XxxXxx'.

  repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (XxxXxx).".
     Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly,
      or connection not established correctly.

UAT looks like as with success, no data missing
exception is (reason why I asked this question) from allocation for a new space on HDD

my question is

may I run standard repair databases (will be my 1st. attempt)
search for details, and run this repair against concrete table(s)
built database from sets of backups and then to import a new data (is possible, in this database there are stored data snapshots)

EDIT

for future readers, there were some issues in my case, (DBCC CHECKTABLE passed with success without any exceptions)

SQL Server (both 2008_R2/2012_R2) repair only part of errors by using DBCC CHECKDB(DbName, REPAIR_ALLOW_DATA_LOSS) ....
I have to runs this process four times (number of allocated GAMs decreased), last run  fixed this problem,
interesting is fact that both SQL Servers 2008_R2 and 2012_R2 had the same processes with the same exceptions, number of exceptions, numbers of repairs required, very similair performances (one enviroment with two diff instances)

Seems like you have a serious database corruption as CHECKDB points it out

  repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB

The only feasible options are :

Restore from backup or
Repair with data loss => Might leave the database in a transactionally inconsistent state.

In both cases, you will have data loss (provided you have good full and T-log backups before that guy pushed green button instead of blue).

I would go for option 1 => Restore from a know latest good backup !

Refer to IAM pages, IAM chains, and allocation units and IAM page corruption examples

  UAT looks like as with success, no data missing

If you regularly have UAT in sync with PROD, best is to use that dataset to restore PROD - unless you are masking PROD data in UAT.


How to avoid duplicating information in a series of nested SELECT statements? I'm working on an SQL query that puts together customer history based on past purchases (the industry is venue ticketing). The report will be LARGE, something like 80-90 columns, with one column per event or category of event per record in the output CSV.

The database structure requires that I use nested select statements to get the necessary data into columns for each record--each column containing a quantity of tickets for that type of event. I know how to do this in theory, but each embedded select statement is so large that the full report would blow through the 8,000 character limit on my DB interface. One of these statements looks like this:

(select count (*)
FROM
guest ig, event2 e, eventseat es, "order" o
WHERE ig.guestid = g.guestid
and ig.guestid = o.guestid
and o.orderid = es.orderid
and e.eventid = es.eventid
and e.incometype = 'T'
and e.eventtype in ('SS', 'BMF')
and es.status in ('2','4')
and es.price <> '0.00'
and es.price <> '5.00'
and e.year = '2010'       <-- THESE LINES ARE THE ONLY ONES THAT
and e.run in ('SS-DANCE') <-- CHANGE PER SELECT STATEMENT
) as 'SS-Dance',

That's about 400 characters, which only leaves enough room for <20 iterations in a single query without going over the character limit.

Is there a way to reduce this or generalize it so that I don't need to waste all those characters on duplicated information in each SELECT?

I've put together an example at the following link which contains a simplified version of my DB structure and some sample data. Objective is to get the same result data without using so many characters per nested SELECT.

http://sqlfiddle.com/#!3/fc316/2

;WITH x AS
(
  SELECT g.guestid, e.[year], e.run, c = COUNT(*)
  FROM dbo.guest AS g
  INNER JOIN dbo.[order] AS o
  ON g.guestid = o.guestid
  INNER JOIN dbo.eventseat AS es
  ON o.orderid = es.orderid
  INNER JOIN dbo.event2 AS e
  ON e.eventid = es.eventid
  WHERE e.incometype = 'T'
  AND e.eventtype in ('SS', 'BMF')
  AND es.status in ('2','4')
  AND es.price NOT IN ( '0.00', '5.00')
  AND e.[year] = '2010'
  AND e.run IN ('SS-DANCE', 'SS-FILM', 'SS-OPERA')
  GROUP BY g.guestid, e.[Year], e.run
),
y AS
(
  SELECT * FROM x PIVOT (MAX(c) FOR run IN
  ([SS-DANCE],[SS-FILM],[SS-OPERA])) AS z
)
SELECT y.guestid, y.[year], g.[first], g.[last],
  [SS-DANCE] = COALESCE(y.[SS-DANCE], 0),
  [SS-FILM]  = COALESCE(y.[SS-FILM], 0),
  [SS-OPERA] = COALESCE(y.[SS-OPERA], 0)
FROM y
INNER JOIN dbo.guest AS g
ON y.guestid = g.guestid;

I'm not sure if my where clause is going to work for you as written. This currently assumes you only care about these three types (dance, film and opera) and you only care about the year 2010. If you need to cross years you can remove that WHERE clause. If you only care about dance in 2010 and film in any year you'll need to write a more complex set of where clauses.

Some comments:

try to avoid reserved words like order, year, first and last. These only complicate matters because they need to be enclosed in square brackets (which are preferable over "double quotes" mostly for readability and unambiguity).
always use dbo. (or whatever schema prefix is appropriate).
as I suggested above, stick to explicit INNER JOIN syntax. FROM x, y, z is for the birds.


Is "Read only routing" the only way to force the connection with ApplicationIntent=ReadOnly to connect secondary replica? I have no problem with setup "Read-only routing".

My point is if we use "Read-only routing". We need to add the "ApplicationIntent=ReadOnly" into our application connection string. That's beyond our DBA's role.

Is there any other way to let one application such as SAS to connect to the listener and the SQL AG will point it to the secondary?

Many thanks,

Is there any other way to let one application such as SAS to connect to the listener and the SQL AG will point it to the secondary?

Unfortunately no, not within SQL Server.

You must specify ApplicationIntent=ReadOnly in the connection string in order for the connection to be eligible for read-only routing, as is documented:

  Requirements and Recommendations for Client Connection-Strings

  For a client application to use read-only routing, its connection string must satisfy the following requirements:

  ...
  Set the application intent attribute/property to readonly.
  ...


How to Insert all PDF files from a folder into a varbinary(max) column in SQL using a Query I am trying to create a query script for SQL Server that will convert pdf file into varbinary data and store it into the SQL Table column which is varbinary(max). I also have to make sure it loops so it can do that for the next pdf file in that folder. This folder will only contain pdf files.

DECLARE @pdf VARBINARY(MAX)

SELECT @pdf = BulkColumn
FROM OPENROWSET(BULK N'C:\Users\......\YourFile.pdf', SINGLE_BLOB) AS Document;

SELECT @pdf, DATALENGTH(@pdf)

INSERT INTO dbo.YourTable(PDFContents) VALUES(@Pdf)
GO

How to put .pdf file contents into varbinary(max) colunm

So it sounds like you do not have to parse the pdf, just import the entire thing into one column. If so, you could try using SSIS. Create a data flow task and use the Import Column transformation, this should allow you to import the pdf as an image; you'd just need to specify the path to your files. As far as processing several files in the folder, create a ForEach Loop for the data flow task. And for the automation part, just schedule the ssis package via sql jobs to run at specific times or on a regular schedule.


Change a partitioned global non-unique index that is the primary key to a non-partitioned unique index One of the largest tables in our system has its primary key setup as a globally ranged partitioned (in huge, useless, multi-billion value ranges) non-unique index.  Its values are just a sequence value and they are definitely unique.  I am wanting to change the index to just a normal global (the table is partitioned), unique index.

Can I do that without dropping the constraint and index and then rebuilding - during which time the table would be without this vital index?

You can do this with DBMS_REDEFINITION, but you'll need double the space occupied by the table.

If I read your question correctly, this should do it:

[oracle@node1 ~]$ sqlplus phil/phil

SQL*Plus: Release 11.2.0.2.0 Production on Sun Jan 6 18:47:12 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set echo on
SQL> @part
SQL>
SQL> drop table PARTITIONED_INTERIM;

Table dropped.

SQL>
SQL> drop table partitioned;

Table dropped.

SQL>
SQL> create table partitioned
  2  (
  3  pk number,
  4  blah  varchar(40),
  5  blah2 varchar(40)
  6  )
  7  partition by range (pk)
  8  (partition p1 values less than (1000),
  9   partition p2 values less than (10000),
 10   partition p3 values less than (30000),
 11   partition p4 values less than (MAXVALUE)
 12   );

Table created.

SQL>
SQL> create index pk_idx
  2  on partitioned (pk)
  3  global partition by range (pk)
  4  (partition p1 values less than (1000),
  5   partition p2 values less than (10000),
  6   partition p3 values less than (30000),
  7   partition p4 values less than (MAXVALUE)
  8   )
  9  ;

Index created.

SQL>
SQL> ALTER TABLE partitioned ADD CONSTRAINT ppk PRIMARY KEY (pk) USING INDEX pk_idx;

Table altered.

SQL>
SQL> select index_name, uniqueness , table_name, partitioned
  2  from user_indexes
  3  where table_name = 'PARTITIONED';

INDEX_NAME                     UNIQUENES TABLE_NAME                     PAR
------------------------------ --------- ------------------------------ ---
PK_IDX                         NONUNIQUE PARTITIONED                    YES

SQL>
SQL> EXEC dbms_redefinition.can_redef_table('PHIL', 'PARTITIONED');

PL/SQL procedure successfully completed.

SQL>
SQL> create table PARTITIONED_INTERIM as ( select * from PARTITIONED where 1=0 );

Table created.

SQL>
SQL> exec DBMS_REDEFINITION.start_redef_table(uname =>     'PHIL',orig_table => 'PARTITIONED',int_table => 'PARTITIONED_INTERIM');

PL/SQL procedure successfully completed.

SQL>
SQL> create unique index partitioned_interim_pk on partitioned_interim (pk) global online;

Index created.

SQL>
SQL> exec dbms_redefinition.sync_interim_table(uname => 'PHIL',orig_table => 'PARTITIONED',int_table => 'PARTITIONED_INTERIM');

PL/SQL procedure successfully completed.

SQL>
SQL> exec dbms_redefinition.finish_redef_table(uname => 'PHIL',orig_table => 'PARTITIONED',int_table => 'PARTITIONED_INTERIM');

PL/SQL procedure successfully completed.

SQL>
SQL> select index_name, uniqueness , table_name, partitioned
  2  from user_indexes
  3  where table_name = 'PARTITIONED';

INDEX_NAME                     UNIQUENES TABLE_NAME                     PAR
------------------------------ --------- ------------------------------ ---
PARTITIONED_INTERIM_PK         UNIQUE    PARTITIONED                    NO

SQL>
SQL>
SQL>

Note that you'll have to deal with other indexes/objects etc too, using DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS() and DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT().


When should I use a new table? If a database is to hold a large amount of eg tickets for concerts, should I make a new table for each concert, then keep a table connecting venues with concerts?

I would like each ticket ID for each concert to start from 1, not be a running total of every single ticket.

Or should I keep them in one table and have 2 seperate columns for (ticket ID) AND (ticket number for that event)?

Your second option is a better solution. Add a new column to your tickets table, called ticket_concert_number. The logic to begin at 1 for each new concert is something that's probably best controlled in your application. You should also have a column called concert_id so you know which concert the ticket is for.

If you create a new table for each concert, you'll quickly fill your database with many many tables with relatively few records. This could lead to performance problems (when compared to a single table that's properly indexed). And if the table structure changes, you need to decide if you're going to propagate the structural changes to all of the existing tables, and if you don't, you'll need to be aware of the fact that you will have to query all the old tickets tables differently depending on what "version" of the original structure they were based on. And then you also have to consider how your application will even find the correct table, when the tables are being generated for every new concert.


SELECT COUNT(*) on a view is orders of magnitude slower than SELECT * on the same view The View
CREATE VIEW [dbo].[vProductList]
WITH SCHEMABINDING
AS

SELECT
     p.[Id]
    ,p.[Name]
    ,price.[Value] as CalculatedPrice
    ,orders.[Value] as OrdersWithThisProduct
FROM
    products as p
    INNER JOIN productMetadata as price ON p.Id = price.ProductId AND price.MetaId = 1
    INNER JOIN productMetadata as orders ON p.Id = orders.ProductId AND orders.MetaId = 2

For the sake of simplicity, assume that productMetadata has columns ProductId, MetaId, Value has ~87m rows, and there are about 400k rows in the products table.
General queries against this view work perfectly:
SELECT * FROM vProductList WHERE CalculatedPrice > 500

Query results in 2-4 seconds (over a vpn and remote, so I'm good with that).
Changing the above to a count is equally fast:
SELECT COUNT(*) from vProductList WHERE CalculatedPrice > 500

runs in about the same time as the raw select, which again I'm OK with. There are roughly 10k products meeting this criteria.
I've run into two separate instances where things get really odd and take FOREVER.
First
Doing a query against one of the columns from the base table in the view:
SELECT * FROM vProductList WHERE Name = 'Hammer'

This query takes a little while to run (20-30 seconds) and returns ~30k results; however, a slight change to said query:
SELECT COUNT(*) FROM vProductList WHERE Name = 'Hammer'

takes thirteen MINUTES to return a count stating ~30k .
Second
Doing a WHERE IN subquery
SELECT * FROM vProductList WHERE Id IN (SELECT ProductId FROM TableThatHasFKToProductId and ColumnInTable = 'Yes')

This query returns ~300k rows and takes two minutes to return (much of that time is simply spent downloading the data into SSMS I believe); however, changing that to a SELECT COUNT(*) results in a query that takes twenty minutes.
SELECT COUNT(*) FROM vProductList WHERE Id IN (SELECT ProductId FROM TableThatHasFKToProductId and ColumnInTable = 'Yes')

Why is it that SELECT * is faster than SELECT COUNT?
I am using the Total Execution Time provided by SSMS for all timings listed here.
Execution plans

Plan for SELECT 1 FROM v WHERE IN (...)
Plan for SELECT COUNT(0) FROM v WHERE IN (...)

Note: I tried using PasteThePlan but it kept telling my the plan was invalid xml.

From the execution plans provided, for the COUNT case the optimizer chooses a local/global aggregation strategy around the final join:

Unfortunately, the optimizer overestimates the effectiveness of the local aggregation. It estimates 136 rows driving the Nested Loops join, whereas 366,115 are encountered at runtime.

The 366,115 index lookups might not be too much of an issue for a local SQL Server instance, but the wait stats included in the plan show the I/O (and possibly memory) limitations of your current Azure SQL Database configuration:

The plan for SELECT 1 shows an exclusively hash and merge join strategy, which produces better results in this case with the very limited memory and/or I/O capabilities.

You might well see better performance for the first query with an OPTION (HASH JOIN, MERGE JOIN) hint, but the fundamental issue is the poor cardinality/data distribution estimate driven by the large number of joins.

Don't be misled by the cost percentages shown against each plan operator - these numbers are currently derived from the optimizer's estimate of cost (using an abstract model). The numbers do not reflect runtime conditions or costs.

Large deviations between estimated and actual row counts can often lead to problems. This is especially true for an underestimate that causes the optimizer to choose a strategy that does not scale up well on a particular hardware setup.


SQLCMD mode: environment variable syntax? I assume the best way to set my SSMS (SQL Server 2005) output file path during runtime of T-SQL, is to use SQLCMD mode.

How do I use environment variables (e.g. %systemroot%) in the code? The following bombs:

:set mypath %systemroot%
:out $(mypath)"\the_result.txt"
select * from myTab

In other words, when using SQLCMD not from the command line, but inside my T-SQL code.
I just need to sort out the syntax.

(And also perhaps, though forum protocol is probably to ask it as separate questions:

Is there an environment variable for the folder of this .sql / the working folder?
How do I prevent the line counts to go to my output file also?
)

Thanks!

The sqlcmd syntax for setting variables is :setvar.  You can access environment variables from sqlcmd mode (using the $(variableName) syntax) however I find these case-sensitive and it's  a bit fussy about what you can and can't do.  Try this, hopefully it makes sense:

:setvar mypath "$(SystemRoot)"

:out $(SystemRoot)\test.txt     -- will work

print '$(mypath)'   -- oops won't work
print '$(SystemRoot)'   -- should work

go

:out stdout


Implementing audit trail ms sql using stored procedure My application would be like having category and under category, products will be there.

Three types of admin will be there. super admin (more than one admin will be available), product admin (more than one product admin) and user.

Super admin and product admin can add/delete/update product. User can view that.

Super admin and product admin can change the user permission.

Super admin can add users.

For this I need to add audit trail.

I have referred some sites like best way to implement an audit trail in sql-server?

http://www.codeproject.com/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL

http://techbrij.com/audit-trail-microsoft-sql-server-quickly

My understanding from this.
This will write the audit trail whatever (based on our setting) action done to the table
For each table we need to have separate shadow table.
I have planned for the following implementation

I have audit trail types with the predefined message. For example I am having the following tables AuditTrailActions and AuditTrailMessages. My message template would be like the following,

  Product {ProductName} [{PersonId}] has been created by {Username}
  [{PersonId}]

Whenever the product is added successfully, I will call a stored procedure from there will call audit trail messages table to get the message and replace the necessary tags and update in AuditTrail Table

I will do like the same thing for all the actions.

Will be a good implementation?
If not can anyone suggest good implementation for my need?

Some native options:

Change Data Capture
SQL Server Audit (if you also want to audit reads)

Both are Enterprise Edition features.


Unit of COST in an Oracle execution plan Is there a unit for cost in an Oracle execution plan?

I mean if the cost of an operation is 50 then can I map this number to CPU cycles or utilisation percentage? What does this number stand for?

Is there any way I can deduce how many seconds or ms this operation is going to take to complete?

Unfortunately the answers are as follows:

  Is there a unit for cost in an Oracle execution plan?

Not really.

  I mean if the cost of an operation is 50 then can I map this number to CPU cycles or utilisation percentage?

Nope.

  What does this number stand for?

It's defined like this: (see the glossary)

    A numeric internal measure that represents the estimated resource usage for an execution plan. The lower the cost, the more efficient the plan.

That's about all you'll get in terms of definition of that number.

  Is there any way I can deduce how many seconds or ms this operation is going to take to complete?

No. It's a value that, in my experience, should only be used to compare different plans for the same query, and then with a bit (well, a lot) of salt. If one plan has a cost of 10, and another a cost of 1M, chances are the first will be faster. If the costs are close, chances are they'll have similar runtime behavior, but that's not always the case.

The The Oracle Optimizer - Explain the Explain Plan (PDF) document has a few words on that value, but:

    The optimizer’s cost model accounts for the IO, CPU, and network resources that will be used by the query. [...]
    The cost is an internal unit and is only displayed to allow for plan comparisons.

How does Oracle calculate the cost in an explain plan? on Stack Overflow has some good input related to that cost value (including a "formula" for Oracle 9i), and good advice from Justin Cave: the cardinality estimates are more meaningful, and you can actually compare them with reality.


Question on Auditing 12C I have the following configuration information:

audit_sys_operations
TRUE

audit_file_dest
D:\ORACLE\ADMIN\DBSERVER\ADUMP

audit_trail
DB

SQL> spool off;

This tells me that audit data for the SYS user is being saved in a database file. If the SYS user has full access to DB, does this not defeat the purpose of audit SYS files being stored on the DB server?

AUDIT_SYS_OPERATIONS

  The audit records are written to the operating system's audit trail.

SYS audit entries are not stored in the database, but on the host in text files (or the event log on Windows).

But yes, you are correct, if you are SYS, you have full access to the database, and the host, with the user the database runs as. So yes, you can manipulate audit rules and data as well.

For that reason (and performance-wise), usually I suggest using AUDIT_SYSLOG_LEVEL, and configuring syslog to forward all entries to a remote server.


I Want to Become a Database Administrator - How Can I Start? I just took a database course in my college and found it fascinating. I want to learn more in this field, but I feel a bit lost with how to start.

How do I become a database administrator? What are some tips and tricks to get into the career field? I see a lot of job postings asking for 5 or more years of experience and am not sure how to start.

Are there entry level jobs I should look for? What kind of skills, traits or certifications would help me out or help me get more experience? I'm mostly interested in MySQL.

I have a feeling that this may be off topic and/or opinion based and may get closed, but I'll answer anyway for now. This is from a Microsoft SQL Server perspective, but it really applies well across the board in my experience.  I've been a DBA or a DBA consultant for about 15 years now and I've hired and been on the hiring team for junior, mid-level and senior DBAs. Others may have differing experiences in other answers..
On Certifications
I don't look at them. Especially when I am hiring someone who is relatively new. If you want to use a certification process to properly and effectively study to learn and to find where your gaps in knowledge are - then definitely. If you think the certification will make you hirable in and of itself - it just doesn't typically hold true. But it is a catch-22, some recruiters will ask for those in job requirements. But then so many people just pass low-level certs with brain dumps and a complete lack of testing. When I see a resume with little experience and a lot of certifications, I get nervous.
I guess the takeaway on this topic is: Certifications can help, but they are seldom most important or even top of criteria
On Becoming a DBA
My first question back to someone who wants to become one is why? In fact if you interviewed and told me that you really wanted to become one I'd want to hear why. Most of the best DBAs I know fell into it from development, system administration or other career paths. This does not mean that you won't be successful if you want to be one. I just like to know why.
If it is because you have the right mindset, you like troubleshooting, you like to keep things in order, you like to make sure systems are alive, data is protected and you are drawn to the career field? Then that is great.
What Skills Are Important?
To a new DBA? I want to see you have a proficiency in the basics of being a DBA. You need to know what the most important job is (and back it up with a why and how you will do it). My personal opinion is Recovery - because without the ability to recover you don't have a DBA and how I'd do it - I'd focus on the restore end and work hard at making sure that everything we did had an eye towards a possible restore. The implementation, the testing, the best practices in place, etc.. Now is that the only right answer? I don't know. If you said security and you explained it right, I'd be interested in hearing more.
Basically, though, I want to see that you have the right character traits (below) and the right basic knowledge that you should have at your skill level and experience. Starting out? I want to see that you can backup and restore.. That you can do some basic investigation into current activity. I want to know that you understand some of the basic building blocks of performance (what resources are important and why, how DB Design and development affect things) and have a lot of the character traits I describe below in place or heading there.
Character Traits
I wrote a blog post about this (Six reasons I won't hire you) awhile back. I won't regurgitate everything I said there here- but basically a good DBA is someone who has:

Great Troubleshooting skills - if you are a scatter brain and try 65 things to fix one problem and don't even remember 2 of the things you tried - I am going to pass on you if I can root that out. You have to be a calm troubleshooter with a methodology and an ability to discover and work out problems. This isn't just DB problems, but it should be verified in life and any problems one may encounter.
willingness/desire to learn and grow - I want you to be always striving to learn the next thing. Spend some of your after work time reading books, going to user groups and community events. It is a tight market out there - I want the people who are continuing to improve themselves.
Common Sense - I am starting to think you can't teach this.. Have some before you interview.
A little touch of paranoia - you are the DBA or want to be. You are about to manage a lot of important sensitive data. I want to see you be fair and easy to get along with, not arrogant, but I want someone with a bit of paranoia. I want someone who doesn't just trust by default or without verifying. You are asking to have the keys to a database environment that is important.

So How Do you Start?
Entry level jobs. Perhaps getting a job as a developer or general IT admin with some DB skills on the job. Going to community events and user groups and learning and applying yourself. Trying some volunteer or helping gigs with non profits or start ups. Basically get near databases. Work out a career path where you can do something with data.
I started out working as a support rep. Dealing with all sorts of questions, but SQL Server questions were one of the types. I studied hard, became a go to person for tough SQL problems and moved up from there. Then I worked as a Jr. DBA someplace and the rest is history.
Patience
I was just discussing this question with a friend on twitter and they mentioned "you don't get there overnight" - That is great advice. I see a lot of people starting out who want to be the senior DBA right now. So you need a little patience and humility thrown in there.
The first years of a DBA career are learning, figuring out which way you want to go and a lot of support time doing tasks that are basic. While you learn in these tasks and disciplines and prove yourself, more and more tasks will be added and more responsibility granted. If you stick with it, work hard, keep things online, build the right character and grow in your skills continually, you will get to senior DBA and do more fun stuff (well combined with more meetings and time spent with project managers) - but it is a marathon and not a sprint, in the DBA world.
These answers are opinions, and that is why this whole thread not survive, but if it helps you out - copy and paste it and give some of the advice a whirl. Best of luck!


Retrieving n rows per group I often need to select a number of rows from each group in a result set.

For example, I might want to list the 'n' highest or lowest recent order values per customer.

In more complex cases, the number of rows to list might vary per group (defined by an attribute of the grouping/parent record). This part is definitely optional/for extra credit and not intended to dissuade people from answering.

What are the main options for solving these types of problems in SQL Server 2005 and later? What are the main advantages and disadvantages of each method?

AdventureWorks examples (for clarity, optional)

List the five most recent recent transaction dates and IDs from the TransactionHistory table, for each product that starts with a letter from M to R inclusive.
Same again, but with n history lines per product, where n is five times the DaysToManufacture Product attribute.
Same, for the special case where exactly one history line per product is required (the single most recent entry by TransactionDate, tie-break on TransactionID.

Let's start with the basic scenario.

If I want to get some number of rows out of a table, I have two main options: ranking functions; or TOP.

First, let's consider the whole set from Production.TransactionHistory for a particular ProductID:

SELECT h.TransactionID, h.ProductID, h.TransactionDate
FROM Production.TransactionHistory h
WHERE h.ProductID = 800;

This returns 418 rows, and the plan shows that it checks every row in the table looking for this - an unrestricted Clustered Index Scan, with a Predicate to provide the filter. 797 reads here, which is ugly.

So let's be fair to it, and create an index that would be more useful. Our conditions call for an equality match on ProductID, followed by a search for the most recent by TransactionDate. We need the TransactionID returned too, so let's go with: CREATE INDEX ix_FindingMostRecent ON Production.TransactionHistory (ProductID, TransactionDate) INCLUDE (TransactionID);.

Having done this, our plan changes significantly, and drops the reads down to just 3. So we're already improving things by over 250x or so...

Now that we've levelled the playing field, let's look at the top options - ranking functions and TOP.

WITH Numbered AS
(
SELECT h.TransactionID, h.ProductID, h.TransactionDate, ROW_NUMBER() OVER (ORDER BY TransactionDate DESC) AS RowNum
FROM Production.TransactionHistory h
WHERE h.ProductID = 800
)
SELECT TransactionID, ProductID, TransactionDate
FROM Numbered
WHERE RowNum <= 5;

SELECT TOP (5) h.TransactionID, h.ProductID, h.TransactionDate
FROM Production.TransactionHistory h
WHERE h.ProductID = 800
ORDER BY TransactionDate DESC;

You will notice that the second (TOP) query is much simpler than the first, both in query and in plan. But very significantly, they both use TOP to limit the number of rows actually being pulled out of the index. The costs are only estimates and worth ignoring, but you can see a lot of similarity in the two plans, with the ROW_NUMBER() version doing a tiny amount of extra work to assign numbers and filter accordingly, and both queries end up doing just 2 reads to do their work. The Query Optimizer certainly recognises the idea of filtering on a ROW_NUMBER() field, realising that it can use a Top operator to ignore rows that aren't going to be needed. Both these queries are good enough - TOP isn't so much better that it's worth changing code, but it is simpler and probably clearer for beginners.

So this work across a single product. But we need to consider what happens if we need to do this across multiple products.

The iterative programmer is going to consider the idea of looping through the products of interest, and calling this query multiple times, and we can actually get away with writing a query in this form - not using cursors, but using APPLY. I'm using OUTER APPLY, figuring that we might want to return the Product with NULL, if there are no Transactions for it.

SELECT p.Name, p.ProductID, t.TransactionID, t.TransactionDate
FROM
Production.Product p
OUTER APPLY (
    SELECT TOP (5) h.TransactionID, h.ProductID, h.TransactionDate
    FROM Production.TransactionHistory h
    WHERE h.ProductID = p.ProductID
    ORDER BY TransactionDate DESC
) t
WHERE p.Name >= 'M' AND p.Name < 'S';

The plan for this is the iterative programmers' method - Nested Loop, doing a Top operation and Seek (those 2 reads we had before) for each Product. This gives 4 reads against Product, and 360 against TransactionHistory.

Using ROW_NUMBER(), the method is to use PARTITION BY in the OVER clause, so that we restart the numbering for each Product. This can then be filtered like before. The plan ends up being quite different. The logical reads are about 15% lower on TransactionHistory, with a full Index Scan going on to get the rows out.

WITH Numbered AS
(
SELECT p.Name, p.ProductID, h.TransactionID, h.TransactionDate, ROW_NUMBER() OVER (PARTITION BY h.ProductID ORDER BY h.TransactionDate DESC) AS RowNum
FROM Production.Product p
LEFT JOIN Production.TransactionHistory h ON h.ProductID = p.ProductID
WHERE p.Name >= 'M' AND p.Name < 'S'
)
SELECT Name, ProductID, TransactionID, TransactionDate
FROM Numbered n
WHERE RowNum <= 5;

Significantly, though, this plan has an expensive Sort operator. The Merge Join doesn't seem to maintain the order of rows in TransactionHistory, the data must be resorted to be able to find the rownumbers. It's fewer reads, but this blocking Sort could feel painful. Using APPLY, the Nested Loop will return the first rows very quickly, after just a few reads, but with a Sort, ROW_NUMBER() will only return rows after a most of the work has been finished.

Interestingly, if the ROW_NUMBER() query uses INNER JOIN instead of LEFT JOIN, then a different plan comes up.

This plan uses a Nested Loop, just like with APPLY. But there's no Top operator, so it pulls all the transactions for each product, and uses a lot more reads than before - 492 reads against TransactionHistory. There isn't a good reason for it not to choose the Merge Join option here, so I guess the plan was considered 'Good Enough'. Still - it doesn't block, which is nice - just not as nice as APPLY.

The PARTITION BY column that I used for ROW_NUMBER() was h.ProductID in both cases, because I had wanted to give the QO the option of producing the RowNum value before joining to the Product table. If I use p.ProductID, we see the same shape plan as with the INNER JOIN variation.

WITH Numbered AS
(
SELECT p.Name, p.ProductID, h.TransactionID, h.TransactionDate, ROW_NUMBER() OVER (PARTITION BY p.ProductID ORDER BY h.TransactionDate DESC) AS RowNum
FROM Production.Product p
LEFT JOIN Production.TransactionHistory h ON h.ProductID = p.ProductID
WHERE p.Name >= 'M' AND p.Name < 'S'
)
SELECT Name, ProductID, TransactionID, TransactionDate
FROM Numbered n
WHERE RowNum <= 5;

But the Join operator says 'Left Outer Join' instead of 'Inner Join'. The number of reads is still just under 500 reads against the TransactionHistory table.

Anyway - back to the question at hand...

We've answered question 1, with two options that you could pick and choose from. Personally, I like the APPLY option.

To extend this to use a variable number (question 2), the 5 just needs to be changed accordingly. Oh, and I added another index, so that there was an index on Production.Product.Name that included the DaysToManufacture column.

WITH Numbered AS
(
SELECT p.Name, p.ProductID, p.DaysToManufacture, h.TransactionID, h.TransactionDate, ROW_NUMBER() OVER (PARTITION BY h.ProductID ORDER BY h.TransactionDate DESC) AS RowNum
FROM Production.Product p
LEFT JOIN Production.TransactionHistory h ON h.ProductID = p.ProductID
WHERE p.Name >= 'M' AND p.Name < 'S'
)
SELECT Name, ProductID, TransactionID, TransactionDate
FROM Numbered n
WHERE RowNum <= 5 * DaysToManufacture;

SELECT p.Name, p.ProductID, t.TransactionID, t.TransactionDate
FROM
Production.Product p
OUTER APPLY (
    SELECT TOP (5 * p.DaysToManufacture) h.TransactionID, h.ProductID, h.TransactionDate
    FROM Production.TransactionHistory h
    WHERE h.ProductID = p.ProductID
    ORDER BY TransactionDate DESC
) t
WHERE p.Name >= 'M' AND p.Name < 'S';

And both plans are almost identical to what they were before!

Again, ignore the estimated costs - but I still like the TOP scenario, as it is so much more simple, and the plan has no blocking operator. The reads are less on TransactionHistory because of the high number of zeroes in DaysToManufacture, but in real life, I doubt we'd be picking that column. ;)

One way to avoid the block is to come up with a plan that handles the ROW_NUMBER() bit to the right (in the plan) of the join. We can persuade this to happen by doing the join outside the CTE.

WITH Numbered AS
(
SELECT h.TransactionID, h.ProductID, h.TransactionDate, ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY TransactionDate DESC) AS RowNum
FROM Production.TransactionHistory h
)
SELECT p.Name, p.ProductID, t.TransactionID, t.TransactionDate
FROM Production.Product p
LEFT JOIN Numbered t ON t.ProductID = p.ProductID
    AND t.RowNum <= 5 * p.DaysToManufacture
WHERE p.Name >= 'M' AND p.Name < 'S';

The plan here looks simpler - it's not blocking, but there's a hidden danger.

Notice the Compute Scalar that's pulling data from the Product table. This is working out the 5 * p.DaysToManufacture value. This value isn't being passed into the branch that's pulling data from the TransactionHistory table, it's being used in the Merge Join. As a Residual.

So the Merge Join is consuming ALL the rows, not just the first however-many-are-needed, but all of them and then doing a residual check. This is dangerous as the number of transactions increases. I'm not a fan of this scenario - residual predicates in Merge Joins can quickly escalate. Another reason why I prefer the APPLY/TOP scenario.

In the special case where it's exactly one row, for question 3, we can obviously use the same queries, but with 1 instead of 5. But then we have an extra option, which is to use regular aggregates.

SELECT ProductID, MAX(TransactionDate)
FROM Production.TransactionHistory
GROUP BY ProductID;

A query like this would be a useful start, and we could easily modify it to pull out the TransactionID as well for tie-break purposes (using a concatenation which would then be broken down), but we either look at the whole index, or we dive in product by product, and we don't really get a big improvement on what we had before in this scenario.

But I should point out that we're looking at a particular scenario here. With real data, and with an indexing strategy that may not be ideal, mileage may vary considerably. Despite the fact that we've seen that APPLY is strong here, it can be slower in some situations. It rarely blocks though, as it has a tendency to use Nested Loops, which many people (myself included) find very appealing.

I haven't tried to explore parallelism here, or dived very hard into question 3, which I see as a special case that people rarely want based on the complication of concatenating and splitting. The main thing to consider here is that these two options are both very strong.

I prefer APPLY. It's clear, it uses the Top operator well, and it rarely causes blocking.


DO INSTEAD in postresql Rules I have a simple table like this :

CREATE TABLE gateway_text (
  id         BIGSERIAL                                  NOT NULL PRIMARY KEY,
  text       TEXT                                       NOT NULL,
  hash       char(40)                                   UNIQUE,
  created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT now()  NOT NULL
);

CREATE RULE "gateway_text_on_duplicate_ignore" AS ON INSERT TO "gateway_text"
  WHERE EXISTS(SELECT 1 FROM gateway_text WHERE hash=NEW.hash)
  DO INSTEAD SELECT * FROM gateway_text WHERE hash=NEW.hash;

SQL Fiddle

As you can see, the rule is for when the first select is passed (the hash is already in database) so the first insert, is not the one DO INSTEAD supposed to run for.
But the problem is, every time I run insert, I get the select result, even for the first insert of a hash.
In my case this is OK, very OK actually, but I don't know if this is correct or this is a bug? can I simply accept this as normal and relay on this?

Yes, the SELECT executing in addition to the INSERT is the behavior that should be expected.

Based on the qualification (the where exists()... clause) and the DO INSTEAD clause,
the RULE produces a sequence of queries looking like this, in pseudo-code:

IF NOT EXISTS (SELECT 1 FROM gateway_text WHERE hash=NEW.hash)
 THEN INSERT...

IF EXISTS (SELECT 1 FROM gateway_text WHERE hash=NEW.hash)
 THEN SELECT * FROM gateway_text WHERE hash=NEW.hash;

In the first step, NEW.hash is not in the table yet so the INSERT is executed (or if it's in the table already, then it's not executed, but that's not the case you're asking about).

In the second step, NEW.hash is now in the table so the SELECT is executed too.

See  Rules on INSERT, UPDATE, and DELETE in PostgreSQL documentation for more details.

The (wrong) idea that the SELECT should not be executed when the INSERT gets executed is a common misinterpretation of how RULEs work, because intuition is misleading here.
When looking at the rule, we're tempted to think that it does this:

    IF EXISTS (SELECT 1 FROM gateway_text WHERE hash=NEW.hash)
     THEN
       SELECT * FROM gateway_text WHERE hash=NEW.hash
     ELSE
       INSERT...

But it doesn't. To achieve this, a procedural language like plpgsql should be used.


Should a locked query increment its CPU and IO counts? When I create a test case in a session #1:

create table ##t (a int)
begin tran
insert into ##t select 1

and in another session #2 run

select * from ##t

and yet in another session run

sp_who2 active

then no CPU and IO counters are increasing on the blocked session #2. This makes sense, but is this always the case for a blocked statement?

The answer here is "it depends" - While actually blocked? A session should not use any resources. It is in a state basically saying "I sure would like to do my work, hey SQL Server may I have a lock on such and such a resource"  so while that resource is locked - the session is basically not able to do anything except wait for resource availability. Once that has been cleared it can then start running or join the runnable queue waiting for its scheduler time.

Now you could still see IO and CPU increasing on a session with many queries or steps that is blocked in different spots. So you could look and see it is currently blocked, but see the CPU increased or IO - that would be because it got past a block did some work for a statement but was then blocked again.

I'll often diagnose blocking with a tool that shows query duration and CPU/IO metrics for a session but maybe missed tracking blocks if an execution of a query is higher than normal duration wise but remains within its normal limits for CPU and IO. It isn't only blocking that will present that way but it often is - so it becomes a tool to say "hey let's look and see if we see any blocking".

I might also suggest you have a look at sp_whoisactive. A great script which will be much more informative than SP_Who2.


Always-On Backup strategy is it possible to perform differential backup from primary and full and t-log from secondary in Always-On? Is there any data inconsistency issue while restoring these backups file?

Your main problem here would be maintaining what backup was relevant to what, and you would have to continue taking full backups on your primary server in order to reset the differential base and prevent you diff backups from becoming as large as the fulls.

Consider it this way:

Full backup taken on primary (once)
Differential taken every night
Full copy_only backup taken on the secondary every Saturday
Log backups taken on secondary every 30 minutes

In this situation you are never clearing your differential base, this means that the differential backup will get larger and larger over time (you can only run copy_only backups of databases from secondary replicas). Eventually the number of pages changed in the database will reach a sufficiently high number that the differential is as large as the full backup. Not an ideal situation.

I would either go with running full backups nightly, off of your secondary, or take once a week (or more often as needed) full backups off your primary, and use differentials on the other days.


How to copy a database using scripts only, no bak, no mdf, no ldf Too often I face a terrible situation:  I have a little time to take the backup of a customer db and download it for studying problems on it.

The problem is that the database contains many BLOBs (as sql server filestream).

Typically tabular data ("mdf") is 5% of the total data.

To study most issues I do not need BLOBs.

In a world with infinite bandwidth I could simply take a backup and download it, but this conflicts with "a little time" in the real world.

To get rid of BLOBs I also do:

create a copy of the  DB at the customer server
set all blobs to NULL
run CHECKPOINT to force filestream data clearing from disk
take backup after some time (proportional to BLOBs total size)
download a small backup

BUT point (4) takes time.

So what I would like to achieve is:

a) create an empty database with a filestream filegroup on the customer's server

b) with a script, create the schema (at least tables, views, sp, sf) on the copied DB

c) with a script copy all the data but not the BLOBs

d) take backup and download

With my knowledge I can somehow manage (b) using the Tasks/Generate Scripts feature of Management Studio.

Then manually I should write queries like this to fill the tables:

INSERT INTO CopiedDB.dbo.Table1
SELECT * from MainDB.dbo.Table1

It is a very long procedure to be done manually even because the insert order must be sorted out manually (because of FK relationships).

Is there a way to create this script in  a smart way avoiding the manual way?

Disable all your foreign keys before you start the transfer and then reenable them afterwards.

You might also want to think about your indexes, it's usually much quicker to disable the indexes before copying data and then rebuild them afterwards.

https://sqlundercover.com/2017/09/25/copying-data-from-one-table-to-another-to-disable-indexes-or-not-to-disable-indexes-thats-the-question/

Have a look at the below scriopt this is part of a much larger script so you might want to check that it does what you want but it should be ok.  Just assign the relevant database names to the @SourceDatabase and @DestinationDatabase variables.

It'll deal with your indexes and constraints, disabling them before and reenabling them after the transfer.  As I said, I've pulled this out of the much bigger process and tweaked it slightly so make sure you test it out.

SET NOCOUNT ON

DECLARE @SourceDatabase SYSNAME
DECLARE @DestinationDatabase SYSNAME

SET @SourceDatabase = 'SourceDB'
SET @DestinationDatabase = 'DestinationDB'

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

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

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

--Check for any objects that are currently disabled
RAISERROR ('Check For Currently Disabled Indexes', 0, 1) WITH NOWAIT

SELECT SCHEMA_NAME(tables.schema_id) + '.' + OBJECT_NAME(indexes.object_id) AS TableName, indexes.name AS IndexName
INTO #DisabledIndexes
FROM sys.indexes
JOIN sys.tables ON indexes.object_id = tables.object_id
WHERE is_disabled = 1

RAISERROR ('Check For Currently Disabled FKs', 0, 1) WITH NOWAIT
SELECT SCHEMA_NAME(schema_id) + '.' + OBJECT_NAME(parent_object_id) AS TableName, name AS FKName
INTO #DisabledFKs
FROM sys.foreign_keys
WHERE is_disabled = 1

RAISERROR ('Check For Currently Disabled Triggers', 0, 1) WITH NOWAIT
SELECT SCHEMA_NAME(tables.schema_id) + '.' + OBJECT_NAME(tables.object_id) AS TableName, triggers.name AS TriggerName
INTO #DisabledTriggers
FROM sys.triggers
JOIN sys.tables ON triggers.parent_id = tables.object_id
WHERE is_disabled = 1

--Switch Database To BulkLogged
RAISERROR ('Switch Database To BulkLogged', 0, 1) WITH NOWAIT

EXEC ('ALTER DATABASE [' + @DestinationDatabase + '] SET RECOVERY BULK_LOGGED')

--Disable all nonclustered indexes
RAISERROR ('Disable all nonclustered indexes', 0, 1) WITH NOWAIT

DECLARE DisableIndexes CURSOR STATIC FORWARD_ONLY
FOR
    SELECT 'ALTER INDEX ' + indexes.name + ' ON ' + schemas.name + '.' + tables.name + ' DISABLE' AS DisableIndexes
    FROM sys.tables
    JOIN sys.schemas ON tables.schema_id = schemas.schema_id
    JOIN sys.indexes ON indexes.object_id = tables.object_id
    WHERE tables.type = 'U'
    AND tables.name NOT LIKE 'sys%'
    AND tables.name NOT LIKE 'mspub%'
    AND tables.name NOT LIKE 'mspeer%'
    AND indexes.type_desc = 'NONCLUSTERED'

DECLARE @DisabledIndexCmd VARCHAR(MAX)

OPEN DisableIndexes

FETCH NEXT FROM DisableIndexes
INTO @DisabledIndexCmd

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC(@DisabledIndexCmd)

    FETCH NEXT FROM DisableIndexes
    INTO @DisabledIndexCmd
END

CLOSE DisableIndexes
DEALLOCATE DisableIndexes

--Disable Constraints and Triggers
RAISERROR ('Disable Constraints and Triggers', 0, 1) WITH NOWAIT

DECLARE DisableConstraints CURSOR STATIC FORWARD_ONLY
FOR
    SELECT 'ALTER TABLE ' + schemas.name + '.' + tables.name + ' DISABLE TRIGGER all' AS DisableTriggers,
    'ALTER TABLE ' + schemas.name + '.' + tables.name + ' NOCHECK CONSTRAINT all' AS DisableConstraints
    FROM sys.tables
    JOIN sys.schemas ON tables.schema_id = schemas.schema_id
    WHERE tables.type = 'U'
    AND tables.name NOT LIKE 'sys%'
    AND tables.name NOT LIKE 'mspub%'
    AND tables.name NOT LIKE 'mspeer%'

DECLARE @DisableConstraintsCmd VARCHAR(MAX)
DECLARE @DisableTriggersCmd VARCHAR(MAX)

OPEN DisableConstraints

FETCH NEXT FROM DisableConstraints
INTO @DisableTriggersCmd, @DisableConstraintsCmd

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC(@DisableTriggersCmd)
    EXEC(@DisableConstraintsCmd)

    FETCH NEXT FROM DisableConstraints
    INTO @DisableTriggersCmd, @DisableConstraintsCmd
END

CLOSE DisableConstraints
DEALLOCATE DisableConstraints

--begin data transfer
RAISERROR ('begin data transfer', 0, 1) WITH NOWAIT

DECLARE InsertCmds CURSOR STATIC FORWARD_ONLY
FOR
    WITH ColumnList (TableID, SchemaName, ColumnList)
    AS
    (SELECT tables.object_id, SCHEMA_NAME(tables.schema_id),
                        STUFF((SELECT ',' + QUOTENAME(all_columns.name)
                        FROM sys.all_columns
                        WHERE tables.object_id = all_columns.object_id
                        AND system_type_ID NOT IN (34,35)
                        FOR XML PATH('')),1,1,'') AS txt
    FROM sys.tables)

    SELECT CASE WHEN MAX(CAST(is_identity AS INT)) = 1
        THEN 'SET IDENTITY_INSERT ['+ @DestinationDatabase + '].[' + SchemaName + '].[' + OBJECT_NAME(TableID) + '] ON; INSERT INTO [' + @DestinationDatabase + '].[' + SchemaName + '].[' + OBJECT_NAME(TableID) + '](' + ColumnList + ') SELECT ' + ColumnList + ' FROM [' + @SourceDatabase + '].[' + SchemaName + '].[' + OBJECT_NAME(TableID) + ']; SET IDENTITY_INSERT ['+ @DestinationDatabase + '].[' + SchemaName + '].[' + OBJECT_NAME(TableID) + '] OFF;'
        ELSE 'INSERT INTO [' + @DestinationDatabase + '].[' + SchemaName + '].[' + OBJECT_NAME(TableID) + '] WITH (TABLOCK) (' + ColumnList + ') SELECT ' + ColumnList + ' FROM [' + @SourceDatabase + '].[' + SchemaName + '].[' + OBJECT_NAME(TableID) + ']'
        END
    FROM ColumnList
    JOIN sys.all_columns ON ColumnList.TableID = all_columns.object_id
    GROUP BY SchemaName,TableID,ColumnList

DECLARE @InsertCmd VARCHAR(MAX)

OPEN InsertCmds

FETCH NEXT FROM InsertCmds
INTO @InsertCmd

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC(@InsertCmd)

    FETCH NEXT FROM InsertCmds
    INTO @InsertCmd
END

CLOSE InsertCmds
DEALLOCATE InsertCmds

--Rebuild all nonclustered indexes
RAISERROR ('Rebuild all nonclustered indexes', 0, 1) WITH NOWAIT

DECLARE RebuildIndexes CURSOR STATIC FORWARD_ONLY
FOR
    SELECT 'ALTER INDEX ' + indexes.name + ' ON ' + schemas.name + '.' + tables.name + ' REBUILD' AS DisableIndexes
    FROM sys.tables
    JOIN sys.schemas ON tables.schema_id = schemas.schema_id
    JOIN sys.indexes ON indexes.object_id = tables.object_id
    WHERE tables.type = 'U'
    AND tables.name NOT LIKE 'sys%'
    AND tables.name NOT LIKE 'mspub%'
    AND tables.name NOT LIKE 'mspeer%'
    AND indexes.type_desc = 'NONCLUSTERED'

DECLARE @RebuildIndexCmd VARCHAR(MAX)

OPEN RebuildIndexes

FETCH NEXT FROM RebuildIndexes
INTO @RebuildIndexCmd

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC(@RebuildIndexCmd)

    FETCH NEXT FROM RebuildIndexes
    INTO @RebuildIndexCmd
END

CLOSE RebuildIndexes
DEALLOCATE RebuildIndexes

--Enable Constraints and Triggers
RAISERROR ('Enable Constraints and Triggers', 0, 1) WITH NOWAIT

DECLARE EnableConstraints CURSOR STATIC FORWARD_ONLY
FOR
    SELECT 'ALTER TABLE ' + schemas.name + '.' + tables.name + ' ENABLE TRIGGER all' AS DisableTriggers,
    'ALTER TABLE ' + schemas.name + '.' + tables.name + ' CHECK CONSTRAINT all' AS DisableConstraints
    FROM sys.tables
    JOIN sys.schemas ON tables.schema_id = schemas.schema_id
    WHERE tables.type = 'U'
    AND tables.name NOT LIKE 'sys%'
    AND tables.name NOT LIKE 'mspub%'
    AND tables.name NOT LIKE 'mspeer%'

DECLARE @EnableConstraintsCmd VARCHAR(MAX)
DECLARE @EnableTriggersCmd VARCHAR(MAX)

OPEN EnableConstraints

FETCH NEXT FROM EnableConstraints
INTO @EnableTriggersCmd, @EnableConstraintsCmd

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC(@EnableTriggersCmd)
    EXEC(@EnableConstraintsCmd)

    FETCH NEXT FROM EnableConstraints
    INTO @EnableTriggersCmd, @EnableConstraintsCmd
END

CLOSE EnableConstraints
DEALLOCATE EnableConstraints

--Switch Database To Full recovery model
RAISERROR ('Switch Database To Full recovery model', 0, 1) WITH NOWAIT

EXEC ('ALTER DATABASE [' + @DestinationDatabase + '] SET RECOVERY FULL')

--Disable any objects that were previously disabled
RAISERROR ('Disable any objects that were previously disabled', 0, 1) WITH NOWAIT

DECLARE DisableIndexes CURSOR STATIC FORWARD_ONLY
FOR
    SELECT 'ALTER INDEX ' + IndexName + ' ON ' + TableName + ' DISABLE' AS DisableIndexes
    FROM #DisabledIndexes

OPEN DisableIndexes

FETCH NEXT FROM DisableIndexes
INTO @DisabledIndexCmd

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC(@DisabledIndexCmd)

    FETCH NEXT FROM DisableIndexes
    INTO @DisabledIndexCmd
END

CLOSE DisableIndexes
DEALLOCATE DisableIndexes

--Disable Constraints and Triggers

DECLARE DisableConstraints CURSOR STATIC FORWARD_ONLY
FOR
    SELECT 'ALTER TABLE ' + TableName + ' NOCHECK CONSTRAINT ' + FKName AS DisableIndexes
    FROM #DisabledFKs
OPEN DisableConstraints

FETCH NEXT FROM DisableConstraints
INTO @DisableConstraintsCmd

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC(@DisableConstraintsCmd)

    FETCH NEXT FROM DisableConstraints
    INTO @DisableConstraintsCmd
END

CLOSE DisableConstraints
DEALLOCATE DisableConstraints

--Disable Triggers

DECLARE DisableConstraints CURSOR STATIC FORWARD_ONLY
FOR
    SELECT 'ALTER TABLE ' + TableName + ' DISABLE TRIGGER ' + TriggerName AS DisableIndexes
    FROM #DisabledTriggers
OPEN DisableConstraints

FETCH NEXT FROM DisableConstraints
INTO @DisableConstraintsCmd

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC(@DisableConstraintsCmd)

    FETCH NEXT FROM DisableConstraints
    INTO @DisableConstraintsCmd
END

CLOSE DisableConstraints
DEALLOCATE DisableConstraints

--Reseed Identity Columns
RAISERROR ('Reseeding Identity Columns', 0, 1) WITH NOWAIT

--drop table #ReseedCommands

DECLARE @Reseed VARCHAR(MAX) =
'SELECT ''DBCC CHECKIDENT ('''''' + schemas.name + ''.'' +  tables.name + '''''', RESEED, '' + CAST(IDENT_CURRENT(schemas.name + ''.'' + tables.name) AS VARCHAR) + '')''
FROM ' + @SourceDatabase + '.sys.all_columns
JOIN ' + @SourceDatabase + '.sys.tables ON all_columns.object_id = tables.object_id
JOIN ' + @SourceDatabase + '.sys.schemas ON tables.schema_id = schemas.schema_id
WHERE is_identity = 1'

DECLARE @ReseedCmd VARCHAR(8000)

CREATE TABLE #ReseedCommands
(command VARCHAR(8000))

SELECT @Reseed

INSERT INTO #ReseedCommands
EXEC (@Reseed)

DECLARE ReseedCursor CURSOR STATIC FORWARD_ONLY
FOR SELECT command FROM #ReseedCommands

OPEN ReseedCursor

FETCH NEXT FROM ReseedCursor
INTO @ReseedCmd

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC (@ReseedCmd)

    FETCH NEXT FROM ReseedCursor
    INTO @ReseedCmd
END

CLOSE ReseedCursor
DEALLOCATE ReseedCursor


How To Strip Hebrew Accent Marks I need a Char Encoding Trick to Strip Hebrew Accent Marks.

Sample Before

  בְּרֵאשִׁ֖ית בָּרָ֣א אֱלֹהִ֑ים אֵ֥ת הַשָּׁמַ֖יִם וְאֵ֥ת הָאָֽרֶץ

Sample After

  בראשית ברא אלהים את השמים ואת הארץ

The trick here is to realize that these characters that you see in the question with the "accents" aren't really the characters (i.e. "These aren't the droidscharacters you are looking for" ;-) ). The "accents" are various types of notations indicating things like:

vowels (lines and dots that are typically under the letters):

  base letter "ה" = "h"; "הֶ" = "heh" and "הָ" = "hah"
pronunciation (dots that are usually inside or above letters):

  "בּ" = "b" vs "ב" = "v", or "שׂ" = "s" vs "שׁ" = "sh"
punctuation
cantillation (how it should be sung)

The actual Hebrew letters are what is shown in the stripped down version (i.e. the end result of what is being requested here). What we are referring to here as "accents" are known as diacritical marks. The Wikipedia article on Hebrew diacritics has a lot of good information about these marks, including the following image and caption:

Gen. 1:9 And God said, "Let the waters be collected".
Letters in black, pointing in red, cantillation in blue

Getting from those base characters to what the first line (with the vowels, etc) shows is a matter of adding one or more "accents". Unicode (UTF-16 in SQL Server, though default interpretation only handles the UCS-2 / Basic Multilingual Plane (BMP) code points) allows for some characters to overlay another non-overlay character when adjacent to them. These are known as Combining Characters.

Meaning:

SELECT DATALENGTH(N'מַ֖'); -- character taken from original given text

Returns:

6

not 2 as most people would expect from seeing a single, double-byte character. So maybe we try to find what character is there by doing:

SELECT UNICODE(N'מַ֖');

which returns:

1502

Of course, the UNICODE and ASCII functions only return the INT value of the first character of whatever string they are given. But a value of 1502 only covers 2 bytes, which leaves 4 bytes unaccounted for. Looking at the binary/hex values of that same Hebrew "character":

SELECT NCHAR(1502), CONVERT(BINARY(2), UNICODE(N'מַ֖')), CONVERT(VARBINARY(10), N'מַ֖');

we get:

מ
0x05DE  0xDE05B7059605

Now, 0x05DE is the hex representation of 1502, and the 1502 is only the "מ". The next part can be separated into three 2-byte sets: DE05 B705 9605. Now, Unicode string values are stored in Little Endian, which means the byte-order is reversed. If we switch each of those three sets we get:

05DE (the base character) 05B7 0596 (the unaccounted for 4 bytes).

Ok. So what happens if we remove that base character?

SELECT REPLACE(N'מַ֖' COLLATE Hebrew_BIN2, NCHAR(1502) COLLATE Hebrew_BIN2, '');

That returns the two remaining characters (not easy to see here so I have made the following line a header in order to increase the font size; you can also run the above REPLACE to see them):

Removing the מ from the מַ֖ leaves two characters at the bottom: ַ֖

Hence, we need to strip out each individual code-point that is one of these "extra" combining characters (found at: http://unicode-table.com/en/search/?q=hebrew) and that will leave us with the base characters. We can do that via:

CREATE FUNCTION dbo.RemoveHebrewAccents (@txeTwerbeH NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
WITH SCHEMABINDING
AS
BEGIN

  WITH base (dummy) AS
  (
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
  ), nums AS
  (
    -- we will want to generate code points 1425 - 1479
    SELECT TOP (55) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS [Num]
    FROM   base b1
    CROSS JOIN base b2
  )
  SELECT @txeTwerbeH = REPLACE(
                               @txeTwerbeH COLLATE Hebrew_BIN2,
                               NCHAR(1424 + nums.[Num]) COLLATE Hebrew_BIN2,
                               ''
                              )
  FROM   nums;

  RETURN @txeTwerbeH;
END;

And then we can test it with the original text as follows:

DECLARE @Hebrew NVARCHAR(200) = N'בְּרֵאשִׁ֖ית בָּרָ֣א אֱלֹהִ֑ים אֵ֥ת הַשָּׁמַ֖יִם וְאֵ֥ת הָאָֽרֶץ';

SELECT dbo.RemoveHebrewAccents(@Hebrew);

Returns:

בראשית ברא אלהים את השמים ואת הארץ

Additional notes:

Technically, there is a set of code-points between 64298 and 64334 that do have some vowels and pronunciation "accents" built into the character. If those need to be handled, that can be a second step in the function to do a simple replacement of those characters.
It seems that these accent, punctuation, etc code-points only match when using a binary collation. Even using Hebrew_100_CS_AS_KS_WS_SC did not match them. But the following did work: Hebrew_BIN, Hebrew_BIN2, Latin1_General_BIN, and Latin1_General_BIN2. In the function I ended up using Hebrew_BIN2. Please note that when using binary collations, unless you have a specific need to use the older _BIN collations, you should only be using the newer _BIN2 collations.
For anyone who is curious, the Hebrew sample text is actually Bereishis 1:1 (that is also the first word on the right-side as Hebrew is read right-to-left; in English it would be "Genesis 1:1" though that is not a direct translation of the word, just the name of the first book of the Torah / Bible; the direct translation is "in the beginning"):

  In the beginning of God's creating the heavens and the Earth

2015-01-19: I found some great resources which explain both Combining Characters and the Hebrew character set:

http://en.wikipedia.org/wiki/Combining_character (also linked towards the top of this answer)
http://www.alanwood.net/unicode/combining_diacritical_marks.html
http://www.alanwood.net/unicode/hebrew.html
http://people.w3.org/rishida/scripts/pickers/hebrew/  (play with all of the characters in the Hebrew character set; very cool!)


Does enabling pg_stat_statements reduce disk space? Recently I was informed that since enabling pg_stat_statements, the available disk space is dropping fast. I don't think it's caused by that, but I wonder: Does enabling pg_stat_statements reduce disk space? And by how much?

pg_stat_statements shouldn't use much disk space.

It uses two files:

pg_stat_statements.stat persists pg_stat_statement's data across server restarts.

It is written upon shutdown and read on server startup. The file is deleted as soon as it has been read, so it should not be present when the server is up.
pg_stat_tmp/pgss_query_texts.stat contains the text of the queries being tracked.

This file will grow as new statements are added, but if it becomes too big, pg_stat_statements will perform a “garbage collection” and rewrite it.

If you suspect that there is a disk space problem with pg_stat_statements, check pg_stat_tmp/pgss_query_texts.stat. But I don't think this file can be a problem unless you set pg_stat_statements.max to an astronomically high value and run lots of different statements.


FULLTEXT Search (InnoDB) - Am I doing it right? Goal: I wish to do autocomplete using ~130K global city names.

Setup: I have created a 'cities' table as follows:

CREATE TABLE IF NOT EXISTS cities (
          id INT(11) NOT NULL AUTO_INCREMENT,
          name VARCHAR(199) NOT NULL,
          PRIMARY KEY  (id),
          FULLTEXT (name)
          )$charset_collate;

My autocomplete code would fetch the 'name' entries and suggest them to user as user types name.

I'm using following query to fetch results:

SELECT name
FROM cities
WHERE MATCH (name) AGAINST (\''.$searchQuery.'*\' IN BOOLEAN MODE);

Question: Is this the right approach to take full advantage of FULLTEXT? Am I missing out on anything? Can my query be improved to fetch better results?

Thanks!

Will you want San Jose to come up when the user types jo?  If not, then FULLTEXT is not useful.  Instead, a simple LIKE '$searchQuery%' is all you need.

Because of how many rows you have (130K), consider

SELECT DISTINCT -- to avoid returning dups.  (There are perhaps 328 San Jose's in the world.)
Don't auto complete if (1) the user has typed only 1 letter, and (2) if the select returns "too many" results.
Another thought is to ORDER BY POPULATION DESC.
Add a LIMIT to the SELECT.
Think about what to do with accents.


multiple calls to procedure optimization I have multiple python scripts running statistical tests and saving the results in a MySQL database.

I have chosen to create a procedure, making these scripts do lot's of calls, and only commiting in the end. (So that no info got lost, sometimes internet goes down and script gets corrupted, and other bugs)

The thing is, with this I can get multiple scripts running at the same time with some locking mechanisms, but after 3 or 4 scripts running, my server gets all 4 cores at 99/100% ...

Using htop it shows lots of mysqld processes and they all consume lots of CPU.

Anyone has any idea on how to optimize the CPU usage from mysql?

[my thoughts right now are instead of making multiple calls, create a multi-line insert, reducing the number of calls to mysql, but i'm not sure this would reduce CPU usage...]

The query

if( select exists(select id from frequency
                     where `title` like _title
                       and `subset` like _string) ) = 0 THEN
    insert into frequency(`title`, `subset`, `amount`)
                   values(_title, _string, _amount);
else
    update frequency set `amount` = amount+1
                     where `title` like _title
                       and `subset` like _string;
end if;

High CPU usually implies SELECT with an inadequate index.

You mentioned inserts -- Yes, INSERTing 100 row in a single statement runs about 10 times as fast as 100 separate INSERT statements.  This is largely because of various overhead things that involve the CPU.  So, that should cut back on the CPU.

If you are doing any "normalization", consider doing it before entering the big transaction.  Ditto for anything else that might be "idempotent".


Follow up to certificate sharing question Follow up to the post Sharing certificates encryped by password between DBs and instances

I created a certificate with a password

CREATE CERTIFICATE testcert
ENCRYPTION BY PASSWORD = 'test123'
WITH SUBJECT = 'Certificate for stored procedures using dynamic SQL',
START_DATE = '2010-01-01',
EXPIRY_DATE = '2100-01-01'

I then removed the private key

ALTER CERTIFICATE testcert REMOVE PRIVATE KEY

Finally, I attempt to backup the certificate

BACKUP CERTIFICATE testcert TO FILE = 'd:\mssql\testcert.cer'
WITH PRIVATE KEY ( DECRYPTION BY PASSWORD = 'S3creT!' ,
FILE = 'd:\mssql\testcert.pvk' ,
ENCRYPTION BY PASSWORD = 'test123' );

And I get this error:

  Msg 15246, Level 16, State 1, Line 1 Cannot dump the private key of
  certificate 'testcert' because the private key cannot be found.

Which seems normal since I dropped the private key.

1st Question: Using the method explained in the previous post, does this mean that to backup the certificate I would use this statement?

BACKUP CERTIFICATE testcert TO FILE = 'd:\mssql\testcert.cer'

2nd Question: What would you do to then to restore the certificate to another server and database?

Example :

CREATE CERTIFICATE testcert2
ENCRYPTION BY PASSWORD = 'test123',
FROM FILE = 'd:\mssql\testcert.cer'
WITH PRIVATE KEY ....

Thanks.

When you backup and restore a certificate for the purpose of moving the certificate you shouldn't have to include any clause related to the private key. I explained in the linked post the reasoning why the value of private key derives primarily from it's uniqueness and any operation that duplicates a private key dillutes its value:

it can no longer be used as proof of identiy (ie. signature) since multiple copies exists throughout the world and non-repudiation is busted
it can no loger be used as a secure means to achieve privacy (ie. encrypt with corresponding public key) because since multiple copies exists, a copy may had been compromised

Just to drive the point home, a certificate is a 'an electronic document which uses a digital signature to bind a public key with an identity' and the correct terminology is public key certificate. In other words a certificate does not have a private key. I myself am carefull to say 'certificate and the associated private key'.

The few operations when is acceptable to backup and restore a certificate and the associated private key are key escrow  and backup for recovery purpose (not for move).

Now after this long dissertation, the answers:

yes, use BACKUP CERTIFICATE testcert TO FILE = 'd:\mssql\testcert.cer'
Use CREATE CERTIFICATE testcert2 FROM FILE = 'd:\mssql\testcert.cer'


How to take Snapshot of Restoring Database I have set up log shipping with my database to make a backup. The backup copy of the database is in a status called '(Restoring...)'.

I understand this is to be expected? Is there a good way I can tell whether the log shipping is actually working?

I have run the stored procedures sp_help_log_shipping_monitor_primary, and sp_help_log_shipping_monitor_secondary which are all good.

What I had in mind was trying to understand what the restoring mode is. If there is a way of backing up and restoring that database so I can take a separate copy of it to check?

In an emergency, how do you take it out of the restoring mode?

Its a bit round about. But first you have the option in the configuration to change the resulting database to 'standby mode'.

This results in a destination database which is grey, and is marked as 'Standby / Read-Only'. You can look at the tables and query it etc.

Take the database offline (TestReplication is what mine is called), and copy the mdf and ldf files. Put it back online again.

Create a new database called 'TestReplicationNew'. Take it offline, and copy the mdf and ldf files from the last step over top of the TestReplicationNew mdl and ldf files.

Take it back online and you have a read write database that you can test works.

If I don't do it like this I get an error,

cannot attach a database that was being restored


Completely uninstall Oracle server on Linux I have searched lot in Google for how to uninstall Oracle on Linux, but I can't figure it out.

I think that I need to reinstall the server because I get a lot of errors when I try to startup the server.

Does any one know how to completely remove all data belong to an Oracle server?

Oracle provides extensive documentation about how its database software can be uninstalled:

Removing Oracle Database Software - 11.2 - this is the new method for uninstallation, using the deinstall tool. This includes instructions on how to download the tool if your install failed and you can't use it directly from there.
Removing Oracle Database Software - 11.1 - this has step-by-step, part by part clean removal instructions for the various parts of the product you could have installed. These methods still work on 11gR2.

If there's anything of any value in the databases on that machine, a full backup is a must. Ideally make that data available on other database(s) on a separate machine before you start uninstalling anything – that way you're sure you won't actually be losing data even if you re-image the OS. (Re-installing the OS is sometimes the most efficient removal tool if you have good procedures/tooling for that.)

But if you're just having issues starting a database, a re-install isn't usually the right thing to do. Fixing those errors is usually much more productive – you'll actually learn something about how the software works along the way, and that's valuable.
To get help with that, if your searches in the Web and the Oracle support site have not helped so far, you could post a question here that includes:

The exact error message you get when you try starting up your database (or whatever action it is you're currently stuck with)
The relevant logs from your alert.log file (located usually in $ORACLE_BASE/diag/rdbms/<lowercase db name>/<SID/trace/alert_<SID>.log)
What changes you made since the last time the database worked
Anything else that you have noticed looks off

The more drastic option for uninstallation is simply to stop all services, and remove (delete) all the files that were installed. Be very careful if you go that route, as usual - don't nuke unrelated files. And make sure you have backups you know you can restore if there's anything useful in the database(s) you have installed.
The main directories/files that need cleaning up are:

$ORACLE_BASE and anything under it
$ORACLE_HOME and anything under it (you might have several)
/etc/ora* - this is the last thing to delete. /etc/oratab contains a list of your Oracle homes, so use it as a reference.
Scan your system for files belonging to the Oracle owner and dba group and remove what is now unnecessary.


mysqld_safe version different than mysqld? Is it a problem that mysqld socket has a different version than the mysqld server?
I noticed this in my mysqld log during startup

120420 19:30:06 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
120420 19:30:06  InnoDB: Initializing buffer pool, size = 8.0M
120420 19:30:06  InnoDB: Completed initialization of buffer pool
120420 19:30:06  InnoDB: Started; log sequence number 0 44233
120420 19:30:06 [Note] Event Scheduler: Loaded 0 events
120420 19:30:06 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.1.61'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution

Yet, when I show variables I get this

  SHOW VARIABLES LIKE "%version%";

+-------------------------+-------------------------------------------+
| Variable_name           | Value                                     |
+-------------------------+-------------------------------------------+
| innodb_version          | 5.5.30                                    |
| protocol_version        | 10                                        |
| slave_type_conversions  |                                           |
| version                 | 5.5.30-cll                                |
| version_comment         | MySQL Community Server (GPL) by Atomicorp |
| version_compile_machine | x86_64                                    |
| version_compile_os      | Linux                                     |
+-------------------------+-------------------------------------------+
7 rows in set (0.00 sec)

Based on golimar's questions I ran a

  ps aux

and I see this

  mysql      633 31.4 33.5 11942788 5452172 ?    SNl  08:18 101:31
  /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql
  --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/host.reviewcritical.com.err --open-files-limit=4096 --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock --port=3306

  root     32674  0.0  0.0 108292  1576 ?        SN   08:18   0:00
  /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql
  --socket=/var/lib/mysql/mysql.sock --pid-file=/var/run/mysqld/mysqld.pid --basedir=/usr --user=mysql

As we all know, mysqld_safe and mysqld are very different

mysqld : The database server instance daemon

mysqld_safe : Control program that examines and sets the environment for mysqld to execute. The mysqld executable is actually launched in a loop. When mysqld terminates, the mysqld_safe program will examine the return results and decide whether

mysqld terminated normally (intentional shutdown), leaves mysqld_safe
mysqld terminated abnormally (crash or kill -9 of mysqld)

Loop back, mysqld fails on retry, leaves mysqld_safe
Loop back, mysqld starts up, stays in the mysqld_safe loop

Why is it important to have mysqld and mysqld_safe using the same MySQL version?

Let me illustrate it this way: Percona Server sometimes has additional features in mysqld_safe for manipulating the OS. For example, I have seen numactl --interleave=all in a Percona Server mysqld_safe. If that line was not there, the mysqld for Percona Server may run into issues with memory and swapping.

The same scenario could possibly be the case for Oracle's (ugh, still hate saying that) mysqld and mysqld_safe. There could be improvements from one major release to another that would be removed if the mysqld_safe was older.

Rather than exploring the possibilities of using a old mysqld_safe and a new mysqld (or vica versa), please make your life simple and reinstall MySQL 5.5.30 from scratch.

Before doing so, please run

updatedb
locate mysqld_safe

in Linux and see if there are two lingering. If there are, get the paths straightened out. Otherwise, you may have to reinstall MySQL 5.5.30.


Get rows that have ALL join criteria I have three tables growth_spaces, equipment and growth_space_equipment which links the two tables together. I'm trying to write a query to get all growth spaces that have all of the equipment that I give as an array. For example if the array were ('Mist Propagation, Automated Curtain) I only want growth spaces that have both of these equipment (not ones that have either).

How would I go about writing this in PostgreSQL?

http://sqlfiddle.com/#!15/97b86/2

You could use LEFT JOIN LATERAL to get a list of all equipment names sorted alphabetically for each growth space, and compare it with another sorted list of elements:

SELECT gs.*
FROM growth_spaces gs
LEFT JOIN LATERAL (SELECT ARRAY(
  SELECT e.name
  FROM growth_space_equipment gse
  LEFT JOIN equipment e ON (gse.equipment_id = e.id)
  WHERE gse.growth_space_id = gs.id
  ORDER BY e.name) AS names) e ON (true)
WHERE e.names = ARRAY(SELECT UNNEST('{Mist Propagation,Automated Curtain}'::varchar[]) ORDER BY 1)

A variant of this is comparing a list of IDs rather than the texts, which would perform better for large sets:

SELECT gs.*
FROM growth_spaces gs
LEFT JOIN LATERAL (SELECT ARRAY(SELECT equipment_id FROM growth_space_equipment WHERE growth_space_id = gs.id ORDER BY 1) AS eq_ids) e ON (true)
WHERE e.eq_ids = ARRAY(SELECT id FROM equipment WHERE name IN ('Mist Propagation', 'Automated Curtain') ORDER BY 1)


Improving concurrency in MongoDB via sharding Our current setup:

Cluster of ~500 ec2 c1.xLarge worker nodes with 8 threads on each running scientific simulations
Results temporarily stored at runtime on each worker node's local MongoDB, using ephemeral data storage.
Once a worker node finishes it's simulation jobs, it will stream the results to a master node (m2.2xlarge) which has a MongoDB instance using EBS Raid-0 for storage. Writing on the fly directly to the master DB was causing socket errors.
Data across the cluster is not necessarily independent, so the incoming data is coming in via upserts and $push, not inserts.
The worker node is terminated once it's data has been received by the master.
A typical run will generate ~100GB of data on the master, ~200,000,000 records.

Our problem is that we encounter severe write latency, causing our worker nodes to sit idly waiting on the master node write locks and page faults. We have been looking into setting up a sharded environment, and are looking for suggestions on how best to configure this for our use case.

Increasing our available memory via sharding seems like a logical step, but leaving a cluster of ec2 instances up can be significantly more costly than a single machine. It would be nice if we could use a sharded cluster at runtime, then replicate the results to a persistent single instance for durability.

EDIT: For a given run, our end goal is to have all results in a single collection on the monolithic mongo instance.

You are correct, with MongoDB the way to engineer around a write contention issue is to shard.

Your environment sounds like it's fairly bursty, in that you're not continually ingesting data and instead ingesting it in fairly discrete chunks. With this in mind, you could go with a collector/distributor model such as this:

     ------>{ workers }<------
     |                       |
[Shard01]               [Shard02]
    |                      |
    ---->[Persistant]<------

The workers would upsert/push their results into the sharded collection/database, and once the job was completed a batch-process then uses something like db.copyDatabases() to push the result set at the monolithic (and cheaper to run) single instance Mongo. As the copy database process can push all of its updates in one run, it should experience much fewer write-lock problems.


Weird error on creating table and selecting rows from table I have db and I am trying to create table called "sales_invoice_grid"

I am having bellow scenarios:

error 1: while creating the table "sales_invoice_grid" manually, it gives the message that it already exists.

error 2: When select rows from "sales_invoice_grid" it gives the message that, the table is not exist

Your problem arises from suffering metadata corruption, in particular, a difference between MySQL/filesystem contents and InnoDB internal data dictionary, which are stored separately.

I cannot give you concrete advice without knowing more about the filesystem, MySQL and InnoDB point of view, and also looking at your error log, but a general "catch all" solution I have most successfully applied in the past is to move/backup all the good data away* (e.g. dump, rename to a different database, backup orphan files), *and then drop the database, which will sync the InnoDB data dictionary and MySQL/filesystem state (to non-existence), then recreate and move back/reimport the original tables. The RENAME TABLE should be almost an instant operation, so it should not take a lot of time. Any operation will not work on the problematic table- that is likely to be lost unless the .ibd file still exists can be backup up before dropping the database.

To prevent this from happening in the future, you probably want to upgrade to MySQL 8, where data dictionary is unified on InnoDB storage, and this duality should be more rare.


Distributing and managing Postgres SCHEMAs with an EXTENSION? Postgres permits distribution and management with CREATE EXTENSION. Is it more proper to write internal EXTENSIONS to manage third-party provided data, or to use scripts that DROP and CREATE SCHEMA? What are the pro's and con's to both?

Right now I tend to write,

./mySchema.psql
./mySchema/01_schema.sql
./mySchema/02_datatypes.sql
./mySchema/10_tables.sql

Etc.

And, I install all of that into SCHEMA mySchema. I'm wondering if I should change my flow and upgrade to use CREATE EXTENSION.

It may make sense to distribute to third party PG users as EXTENSIONS, but it doesn't make sense to bundle their data as EXTENSIONS.

it's more work,
it won't be backed up by pg_dump

Because it won't be backed up by pg_dump, you'll have to add to your development repository their data rather than just the initial load scripts. For the added complexity of backing things up, it doesn't seem advisable to do this.


Failover Cluster - Add Witness Disk? I'm trying to build SQL Server 2012 Cluster on top of Windows Cluster 2008R2.

Can anyone please suggest.. do we need quorum disk in setting up SQL cluster 2012 on top of windows cluster 2008r2

The choice on whether or not to go with an additional non-node vote (I don't want to say "witness disk" here, because an additional vote can conversely come from a file share as well with the node and file share majority quorum model) depends on how many cluster nodes you have.

If an even number of nodes, then add your additional non-node vote (whether it's a witness disk or a file share).  But if it's an odd number of nodes then you will gain no benefit out of a witness disk or file share voter.

Take a look at this TechNet reference on quorum model recommendations.

It is also worth noting that with the entrance of Windows Server 2012 R2, the capability of a dynamic witness has been introduced.  I realize you aren't using this version of Windows Server, but for the sake of a complete answer, here is an excerpt explaining this new feature in Windows Server 2012 R2.  This dynamic witness would be the exception to my answer:

  In Windows Server 2012 R2, if the cluster is configured to use dynamic quorum (the default), the witness vote is also dynamically adjusted based on the number of voting nodes in current cluster membership. If there are an odd number of votes, the quorum witness does not have a vote. If there is an even number of votes, the quorum witness has a vote.


unable to copy binary string from table to notepad I have stored 391603 characters JSON document in the column, when I am trying to copy it and past in notepad full file is not coming. I can only see 43679 characters.

how to resolve this..

in SQL Server Management Studio, go to the "Tools" menu, click on "Options", then navigate to "Query Results", "SQL Server", "Results to Grid", and modify the "Non XML data" option to whatever setting you desire.

Aaron has a great post about how to chunk output so you can see it all, regardless of how large a piece of text you're dealing with at MSSQLTips
However, in SSMS 18, I am able to get substantially larger amounts of text to copy-and-paste from the results window using the option I detailed above.  There are at least a couple of caveats you might need to know about before it starts working.

After you change the "Maximum Characters Retrieved" for Non-XML Data, it only takes effect for new query windows.  i.e. you must open a new query window before you can get more than 65535 characters to copy-and-paste.

If you are testing this by generating a series of characters with replicate, ensure you use an explicit "max" data type, as in varchar(max) or varbinary(max).  If you run SELECT REPLICATE('X', 131072) for example, the resulting string will only be 8,000 characters in length because the implicit conversion is to a varchar type, not a varchar(max) type.  Running this, however, will generate a string that is 131072 characters in length:
SELECT [string] = REPLICATE(CONVERT(varchar(max), 'X'), 131072)
    , [length] = LEN(REPLICATE(CONVERT(varchar(max), 'X'), 131072))

The results:

I modified the "Maximum Characters Retrieved" value to 131072, then opened a new query window and pasted in the code above, hit F5, and got the results you see above.  I then cvopied the value shown in the "string" column to the clipboard, then pasted it into another new query window, where I confirmed the length of the string with the following code:
SELECT LEN(CONVERT(varchar(max), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX(due to the 30,000 character limitation on questions,  not all the "X"s are shown here) XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'))

╔══════════════════╗
║ (No column name) ║
╠══════════════════╣
║           131072 ║
╚══════════════════╝


A question on SARGability I just need to confirm that I understand something correctly:

I recently viewed an SO question in which a user posted an answer in Linq like:

from p in db.table where p.column.AddMinutes(1) > DateTime.Now select p

To those unfamiliar with Linq, I would expect the output of that statement (not tested it in fairness) to be:

SELECT *
FROM table t
WHERE DATEADD(min, 1, t.column) >= GETDATE()

I posted an reply to this saying that the datetime manipulation should be on the variable (in this case GETDATE()) so in fact the statement should reflect something like:

SELECT *
FROM table t
WHERE t.column >= DATEADD(min, -1, GETDATE())

In my reply, the bits i'm now unsure of, assume the following:

Indexes will not be used because of the manipulation of the column
The query plans will be different partly because of the above (not tested, assuming so)
Because of the above, the 1st query will actually perform worse than the 2nd.

My question:

Have I missed anything out in my reasoning? Am I correct? Lastly, does any body have any good articles on SARGability?

correct
not necessarily - it depends on whether a suitable index exists and whether the CBO decides to use it. For example if the table is small or the the statistics lead the optimizer to believe that the filter will be true for a large percentage of the results, it may consider the cost of a FTS to be lower
this is not guaranteed - it is even possible that the 2nd query will perform worse - but your reasoning is fundamentally sound. For example, in an edge case when a FTS would actually be faster, the CBO may choose an index scan instead based on its best guess of the cost - which is always only an estimate of course

A bit of googling turned up this SO question and this interesting looking article on SARGability