Cycles or multiple cascade paths with on delete set null: really?

If this error message did not occur, and ON DELETE SET NULL were to set a column to null, any child-rows referencing the old value would no longer have a valid parent row.  Cascading the update to null for all child rows would be required to meet relational integrity requirements.  However, cascading might result in a never-ending loop, which is the bit that is not supported.  This has nothing to do with a self-reference, and is only about the possibility for SQL Server to follow a never-ending loop.

Consider this setup:

DROP TABLE IF EXISTS dbo.CascadeDeletes;
GO

CREATE TABLE dbo.CascadeDeletes
(
    PrimaryID int NOT NULL
        CONSTRAINT PK_CascadeDeletes
        PRIMARY KEY CLUSTERED
    , SubordinateID int NULL
        CONSTRAINT FK_CascadeDeletes_SubID
        FOREIGN KEY
        REFERENCES dbo.CascadeDeletes (PrimaryID)
) ON [PRIMARY];

Insert some sample data; effectively every row is the parent to the next row.

INSERT INTO dbo.CascadeDeletes (PrimaryID, SubordinateID)
VALUES (1, NULL)
    , (2, 1)
    , (3, 2)
    , (4, 3);

Now, update the first row so it's "parent" is the last row:

UPDATE dbo.CascadeDeletes
SET SubordinateID = 4
WHERE PrimaryID = 1;

Consider that a recursive CTE on this table would enter an endless loop:

;WITH rCTE AS
(
    SELECT cd.PrimaryID, cd.SubordinateID
    FROM dbo.CascadeDeletes cd
    WHERE cd.PrimaryID = 1
    UNION ALL
    SELECT ce.PrimaryID, ce.SubordinateID
    FROM dbo.CascadeDeletes ce
        INNER JOIN rCTE ON ce.SubordinateID = rcte.PrimaryID
)
SELECT *
FROM rCTE

Traversing the tree from primary to subordinate is now a never-ending loop, resulting in this error:

  Msg 530, Level 16, State 1, Line 27
  The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

And deleting any row from the table becomes impossible.

DELETE FROM dbo.CascadeDeletes
WHERE dbo.CascadeDeletes.PrimaryID = 1;

  Msg 547, Level 16, State 0, Line 27
  The DELETE statement conflicted with the SAME TABLE REFERENCE constraint "FK_CascadeDeletes_SubID". The conflict occurred in database "tempdb", table "dbo.CascadeDeletes", column 'SubordinateID'.

If SQL Server would allow us to define the table so that ON DELETE SET NULL was in effect, deleting any row would update other rows referenced by the deleted row to NULL.  Quite clearly, this does not produce an "endless" loop because only a single set of rows will ever be updated.  i.e. if the row with PrimaryID = 2 is deleted, the only other row to be cascade-updated would be the row with PrimaryID = 3.  No endless loop.

Even though in our simple example, there is no endless loop that could be encountered, I expect the CREATE TABLE and ALTER TABLE code-paths generate an error 1785 for every ON UPDATE or ON DELETE action, whenever the pattern looks like it could be cyclical, that is not NO ACTION, simply to err on the side of caution.

One might consider this a bug.

However, luckily, it is pretty easy to work around this bug with the "old way" of enforcing RI; that is with a trigger:

CREATE TRIGGER CascadeDeletes_OnDeleteSetNull
ON dbo.CascadeDeletes
INSTEAD OF DELETE
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE dbo.CascadeDeletes
    SET SubordinateID = NULL
    FROM dbo.CascadeDeletes cd
        INNER JOIN deleted d ON cd.SubordinateID = d.PrimaryID;
    DELETE
    FROM dbo.CascadeDeletes
    FROM dbo.CascadeDeletes cd
        INNER JOIN deleted d ON cd.PrimaryID = d.PrimaryID;
END

Now, when you delete a row from our table, as in:

DELETE FROM dbo.CascadeDeletes
WHERE dbo.CascadeDeletes.PrimaryID = 1;

The row is deleted, and related rows have their SubordinateID set to NULL:

╔═══════════╦═══════════════╗
║ PrimaryID ║ SubordinateID ║
╠═══════════╬═══════════════╣
║         2 ║ NULL          ║
║         3 ║ 2             ║
║         4 ║ 3             ║
╚═══════════╩═══════════════╝


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

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

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

Try this:

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

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

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

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


SPI postgresql - insert data into table from postgres.c

I found the error.
SPI functions must be inside the transaction and in my case between start_xact_command and finish_xact_command.


Is there a TSQL command or another way to rename AOAG (Always On Availability Group)?

I do not believe that there is a way to rename it (safely). You will need to rebuild the AG.

If you do it during an outage window where you can eliminate or minimize data changes then you should be able to do it without having to perform new backups / restores.


Automysqlbackup fix error "Skipping the data of table mysql.event. Specify the --events option explicitly"

In answer to the Server Fault question How to avoid mysqldump warning “Skipping the data of table mysql.event”:

  User Shane on a bike wrote:

    Modify or Create your ~/.my.cnf and create a mysqldump section by adding the following

[mysqldump]
events
ignore-table = mysql.events


How to manage RAM consumption in MongoDB?

If your base document with all the data is 300 bytes, and worst case scenario for storage usage scenario, your indexes are all 300 bytes each but just sorted differently, would get you 900 bytes of memory used per document for indexes and 300 bytes for the base.

1440 events * 1200 bytes (Base document + 3 indexes each at 300 bytes) = 1728000 bytes

1296000 bytes / 1024 = 1687.5 kilobytes

1265.625 kb / 1024 = 1.64 megabytes

So if you are firing off 1440 events per day per device, each single device will take up 1.64 mb of memory plus a bit more for metadata overhead.  If you are firing off that many events for 1,000,000 devices it will be:

1647949.21875 mb / 1024 = 1609.32 gb

so roughly a total of 1.8 TB of ram will be needed to fully fit this into memory without utilizing any compression.

You can deal with the RAM issue by scaling out in shards or finding a few very expensive systems that can handle that much RAM (note I haven't heard of one and I'm not sure how many CPU's you'd need, this isn't realistic).  You can shard but then you'd typically want 3 hosts per shard and it comes with it's own operational overhead.  It also eats up a lot of power an data center space.

You can compromise and just deal with the fact that you will read from disk, if your app is OK with that.  Here are some other solutions.

There are all flash SANs out there that measure returns in microseconds, with RAM delivering data in nanoseconds.  It will be an order of magnitude slower than RAM but it is still insanely fast and might be just fine for your reqs.  The problem with these is you need top of the line Fiber Channel HBAs to have the throughput.

There are FusionIO cards that could work very well.  These will give you a PCI-E bus to connect to so throughput is no problem and you don't need expensive HBAs.  They aren't cheap though and if your app is very time sensitive it still isn't good enough.  FusionIO will typically give you a loaner card to test and they always make me drool.  They have a marketing page on it here.


How does SELECT FOR UPDATE behave with views in PostgreSQL?

Short answer

The following rows will be locked for update in the three cases:

a row with "ID" = :ID in table "A" and all rows in table "B"
a row with "ID" = :ID in table "B" and all rows in table "A"
a row with "ID" = :ID1 in table "A" and a row with "ID" = :ID2 in table "B"

The results are as one can expect after reading this statement in the documentation:

  If a locking clause is applied to a view or sub-query, it affects all tables used in the view or sub-query.

How to test similar cases

Install the extension pgrowlocks..

create extension if not exists pgrowlocks;

Run two instances of psql. In the first one start a transaction and execute a query.

psql #1:

begin;

select *
from "AB"
where "AID" = 3 and "BID" = 103
for update;

You can see locked rows in the other instance of psql.

psql #2:

select * from pgrowlocks('"B"');

 locked_row | locker | multi |  xids   |     modes      |  pids
------------+--------+-------+---------+----------------+--------
 (0,4)      |  88577 | f     | {88577} | {"For Update"} | {8068}
(1 row)

Do not forget to close the transaction at the end (before a next test).

psql #1:

rollback;

The first column of the above result is ctid. You can select the original row with the query:

select *
from "B"
where ctid = '(0,4)';


MySQL transaction size - how big is too big?

One bottleneck to be aware of is the InnoDB Log Buffer. The size is set by innodb_log_buffer_size. Here is what the MySQL Documentation says about it:

  The size in bytes of the buffer that InnoDB uses to write to the log files on disk. The default value is 8MB. A large log buffer enables large transactions to run without a need to write the log to disk before the transactions commit. Thus, if you have big transactions, making the log buffer larger saves disk I/O.

The InnoDB Log Buffer should not be be confused with the InnoDB Buffer Pool. The major difference between them is their purpose. The InnoDB Log Buffer will basically record short-term changes that get written to the redo logs (ib_logfile0, ib_logfile1). The InnoDB Buffer Pool (sized by innodb_buffer_pool_size) caches data and index pages that are to be committed (if the pages are dirty) and eventually written) to disk. Once committed, the changes pages remain in RAM until removed via LRU rules.

Big transactions must funnel through the Log Buffer. As mentioned, a larger log buffer will reduce disk I/O. Only a large commit would present a bottleneck.

You may want to look into other InnoDB options to configure.

innodb_flush_method : This dictates how flush is handled and whether or not to allow the OS to cache writes to InnoDB (You must experiment between O_DSYNC and O_DIRECT)
innodb_read_io_threads (Use the Max : 64)
innodb_write_io_threads (Use the Max : 64)
innodb_io_capacity (use higher values like 5000 or 10000, but the hardware will top it out for you, In other words, let the hardware decide the IO capacity once you assign a huge number)
innodb_thread_concurrency : Default value is 0. I learned firsthand from Ronald Bradford at Percona NYC 2011 to leave this number at 0. It lets InnoDB decide the best number of concurrency tickets it could handle)

I have other posts about optimizing InnoDB for further research

Optimizing InnoDB default settings
How do you tune MySQL for a heavy InnoDB workload?
Insert-heavy InnoDB table won't use all my CPU
How to improve InnoDB DELETE performance?
MySql - Clean ibdata1


autocommit on long transaction after connection failure

It will automatically rollback.

But...  If you do not catch the "go away", but automatically reconnect and proceed with the other statements in the transaction, you could have a mess -- whether it is autocommit or not.

So, always check for errors and take evasive action.


select query not returning expected result

If any of the columns is null the result of the concatenation using || is also null.

You can use concat() or even concat_ws() ("concat With Separator") to make Postgres treat null values like empty strings:

select concat_ws('||', column2, column3, column4)
from tablename
order by column1 asc;

Note that concat_ws() will not append the separator if a value is "missing". If you do need it, you have to use concat()

select concat(column2, '||', column3, '||', column4)
from tablename
order by column1 asc;

Details in the manual:

9.4. String Functions and Operators

Regarding the last part of your question, you can use a select statement for the \copy command. For details please see the manual.


Defragmenting indexes when those indexes are stored on Flash/SSD is close to pointless.  I'd recommend performing the reindex far less frequently.  See Paul's answer here regarding page density and fill factor for mitigating the effects of index fragmentation.

Updating statistics, on the other hand, will provide important clues to the query optimizer.  If auto-update-stats is disabled for the given database, ensure you are updating statistics via a nightly job.

Ensure you run DBCC CHECKDB as often as possible to ensure corruption does not negatively affect your RPO.  You may consider offloading DBCC CHECKDB to a non-production instance.  This consists of automated backup-and-restore to the non-production instance, then automatically running DBCC CHECKDB on that non-production instance.  You'll probably need to license the machine where you run DBCC CHECKDB since it would be dealing with production data, however I'd check with your Microsoft rep to be certain.

If you're seeing SQL Server creating memory dumps, that indicates a problem with SQL Server that should be brought to the attention of Microsoft Tech Support - they may either have a hotfix available to resolve the issue, or they may offer some further advice about how to resolve that issue.  The problem may indeed be a bug that needs to be fixed.


Retrieving data of vehicle drivers that have not been assigned to a job

I would start with the list of available drivers. As you appear to want them as a CSV string, it makes sense to use grouping and concatenate the names with GROUP_CONCAT(). Use an outer join of jobs to the cross-product of dates and drivers, then, to get the available drivers for each day, group-concatenate the driver name only if it has no matching job:

SELECT
  listofdays.job_date,
  GROUP_CONCAT(IF(j.job_id IS NULL, d.driver_name, NULL)) AS available_drivers
FROM
  (
    SELECT
      '2016-09-19' + INTERVAL seq.seq DAY AS job_date
    FROM
      seq_0_to_999999 AS seq
    WHERE
      seq.seq <= TIMESTAMPDIFF(DAY, '2016-09-19', '2016-09-25')
  ) AS listofdays
  CROSS JOIN t_drivers AS d
  LEFT JOIN t_jobs AS j ON listofdays.job_date = DATE(j.collection_datetime)
                     AND d.driver_id = j.driver_id
GROUP BY
  listofdays.job_date
;

The next, and last, step would be to use the above as a derived table and outer-join t_jobs to it once more – this time to get the job details (and you would also need to outer-join t_drivers and t_vehicle to get details from those tables as well):

SELECT
  sub.job_date,
  j.job_id,
  IFNULL(d.driver_name, '') as job_driver,
  IFNULL(v.vehicle_reg, 'no') as job_vehicle,
  j.collection_town,
  j.collection_postcode,
  j.delivery_town,
  j.delivery_postcode,
  j.job_status,
  sub.available_drivers
FROM
  (
    SELECT
      listofdays.job_date,
      GROUP_CONCAT(IF(j.job_id IS NULL, d.driver_name, NULL)) AS available_drivers
    FROM
      (
        SELECT
          '2016-09-19' + INTERVAL seq.seq DAY AS job_date
        FROM
          seq_0_to_999999 AS seq
        WHERE
          seq.seq <= TIMESTAMPDIFF(DAY, '2016-09-19', '2016-09-25')
      ) AS listofdays
      CROSS JOIN t_drivers AS d
      LEFT JOIN t_jobs AS j ON listofdays.job_date = DATE(j.collection_datetime)
                         AND d.driver_id = j.driver_id
    GROUP BY
      listofdays.job_date
  ) AS sub
  LEFT JOIN t_jobs AS j ON sub.job_date = DATE(j.collection_datetime)
  LEFT JOIN t_drivers AS d ON j.driver_id = d.driver_id
  LEFT JOIN t_vehicles AS v ON j.vehicle_id = v.vehicle_id
ORDER BY
  job_date ASC,
  job_order ASC
;


MySQL: initialize the Data Directory (--data-dir) for Operating System user other than mysql

From the website here, you can just modify this line

shell> bin/mysqld --initialize --user=mysql

and change it to:

shell> bin/mysqld --initialize --user=other_username

This user (who is NOT a user in the database server sense, but rather in the Operating System (OS) sense) only determines permissions on the underlying file system - i.e. who owns the data-dir for example, it has nothing to do with MySQL database users. For clarity, I would use different names for OS users and MySQL users.

With compilation of the source, you get even more fine-grained control over your system, but if all you want is to change the owner of the files on the underlying OS file system, then do it this way!

You can use --initialize-insecure and then login as root and change the password straight away, logging in as root with no password:

shell >mysql --user=root

and then

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';
mysql> flush privileges;

Logout and then login with

shell >mysql -u root -p

After -p type nothing else and press Return. You should then be prompted for the password (MyNewPass) that you have just entered.


Database restore without deleting old database

I'm assuming you want a second copy of your DB, but with another name... since you want to "restore it without deleting"? You can definitely do this.

Instead of selecting your current DB, right click on the "Database" item in the object explorer window
In the context popup window select "Restore Database...".
In the window that pops up, enter in a 'To Database' name for your copied DB, i.e. the new name for the DB copy. So if your original was TradeWind, call this one TradeWindCopy for example.
Make sure the 'From database' radio button is selected, and in the dropdown select the backup you just made (tick the required check box). You could also restore 'from device' if you wanted - in that case you need to select a ".bak" file to restore from.
Click the OK button and your DB backup is now restored as the new DB with the name you set in step 3.

Good luck.


Error after SQL-Server Update: The prepared statement with handle xyz was not found

You can potentially find the error message through Extended Events. Create and start the following session:

CREATE EVENT SESSION FailedQueries
ON SERVER
 ADD EVENT sqlserver.error_reported
 (
   ACTION
   (
     sqlserver.client_app_name,
     sqlserver.client_hostname,
     sqlserver.nt_username,
     sqlserver.sql_text
    )
  )
  ADD TARGET package0.asynchronous_file_target
  (
    SET FILENAME = N'C:\temp\FailedQueries.xel',
    METADATAFILE = N'C:\temp\FailedQueries.xem'
  );
GO

ALTER EVENT SESSION FailedQueries ON SERVER STATE = START;
GO

To make sure it is working, run a query that will definitely raise an exception:

SELECT 1/0 FROM sys.objects;

Check the session to make sure this event populated:

;WITH event_data AS
(
  SELECT data = CONVERT(XML, event_data)
    FROM sys.fn_xe_file_target_read_file(
   'C:\temp\FailedQueries*.xel',
   'C:\temp\FailedQueries*.xem',
   NULL, NULL
 )
),
results AS
(
  SELECT
    [host]      = data.value('(event/action[@name="client_hostname"]/value)[1]','nvarchar(4000)'),
    [app]       = data.value('(event/action[@name="client_app_name"]/value)[1]','nvarchar(4000)'),
    [date/time] = data.value('(event/@timestamp)[1]','datetime2'),
    [query]     = data.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)'),
    [error]     = data.value('(event/data[@name="error_number"]/value)[1]','int'),
    [state]     = data.value('(event/data[@name="state"]/value)[1]','tinyint'),
    [message]   = data.value('(event/data[@name="message"]/value)[1]','nvarchar(250)')
  FROM event_data
)
SELECT * FROM results
WHERE error NOT IN (5701,5702,5703)
ORDER BY [date/time] DESC;

Then you can periodically check the output of this query (maybe adding a filter like AND [message] LIKE N'%prepared statement%'). Hopefully that gives you some additional clues.


The correct syntax is:

   GRANT LahmanDBDev
   TO BudSelig;


executing command prompt command in MySql trigger

system is one of MySQL's internal commands, interpreted by the mysql command-line tool rather than by the SQL engine, as stated in the manual:

mysql sends each SQL statement that you issue to the server to be executed. There is also a set of commands that mysql itself interprets. [...]
system    (\!) Execute a system shell command.

[...]

You can't run the system command inside a stored program (and that includes a trigger), because the entire body of the stored program is sent to the server and the server won't recognise system.
However, you can get an output file with SELECT ... INTO OUTFILE FILE:
  SELECT field_1 INTO OUTFILE '/tmp/abc.txt'
  FIELDS TERMINATED BY ','
  LINES TERMINATED BY '\n';


Clarification on Clustered index scan and Clustered Index Seek

Execution plan still shows ClusterIndex Seek why?

The initial seek down the b-tree is to find the first row where CustomerID >= 1.

From that point on, the storage engine remembers the current scan position, and returns the next row in index order that qualifies each time a row is requested by a parent plan operator. The scan comes to an end as soon as a row is encountered that does not match the predicate CustomerID <= 70000.

The effect is that the 'seek' is an initial seek, followed by a partial ordered scan of the index.

This is usually more efficient than scanning the whole index, even where 90% of the rows are expected to qualify. The key point to get straight in your mind is that a separate b-tree seek is not performed for each row.


You can use the QUALIFY statement to restrict the number of rows against a large table.

SELECT CUSTOMERID
     , FULLNAME
  FROM MYDB.LARGE_TABLE AS MYTBL
QUALIFY ROW_NUMBER() OVER (ORDER BY CUSTOMERID)
        BETWEEN 1 and 30000000;

edit - corrected syntax of ROW_NUMBER()


According to AskTom, "you can get the client ip address if you audit connect, it is in the comments column of the audit trail, you have to parse it out.". That's from 2003 but I think it is still the case:

select comment$text from sys.aud$

or possibly:

select comment_text from dba_audit_trail;


Schema Dump Without User Mapping Passwords

The only thing I can think of is to create a single-purpose non-super user and grant them read access to all the objects in the database.  Then run pg_dump connected as that user. Perhaps not the most elegant solution.


MySQL replication fails due to duplicate keys after master is restarted

Don't stop a master while any slaves are connected to it. If you need to stop it, then stop the slaves first and the master at the end. This way, you won't have any errors like the one you described.


Problem when extracting the structure of DB2 (Back up Structure)

GRANT statements will only be extracted if they were ever explicitly issued against database objects, e.g. GRANT EXECUTE ON FUNCTION myfunc TO USER whoever. Implicit privileges will not be extracted, because technically they were never GRANTed.


You need to use dynamic SQL for this. The following example will work (as does a quick check to see that the database exists, you could add additional checks as well).

DECLARE @DateString AS VARCHAR(256) ,
    @ExecuteText NVARCHAR(4000);
SET @DateString = CAST(YEAR(GETDATE()) AS VARCHAR(4)) + RIGHT('0'
                                                              + CAST(MONTH(GETDATE()) AS VARCHAR(2)),
                                                              2) + RIGHT('0'
                                                              + CAST(DAY(GETDATE()) AS VARCHAR(2)),
                                                              2);
DECLARE @DB AS VARCHAR(40);
SET @DB = 'DB01_' + @DateString;

IF EXISTS ( SELECT  name
            FROM    sys.databases
            WHERE   name = @DB )
    BEGIN
        SET @ExecuteText = N'ALTER AVAILABILITY GROUP AG1 ADD DATABASE ' + @DB
            + ';';
        EXEC sys.sp_executesql @ExecuteText;
    END;
ELSE
    BEGIN
        RAISERROR(N'Database: %s does not exist', 16, 1, @DB);
    END;
GO


The process to upgrade/patch Always On Availability Groups is documented extensively in the official SQL Server docs. Typically this is done as a rolling upgrade, where you move from secondaries to the primary SQL Server. Depending on your setup you would do the following:

Patch remote secondaries
Patch local secondaries
Perform a failover of the primary
Patch former primary

You don't typically do them all the same time, you roll through the servers. When I patch our servers, I normally do the secondaries one day and then the following, I'll perform the failover and finish patching the former primary.


This is just an idea.

test=# CREATE INDEX items_idx ON items (person_id);
CREATE INDEX

test=# SELECT person_id, fname,
    (SELECT array_to_string(ARRAY(SELECT title FROM items WHERE items.person_id = people.person_id ), ',')) AS titles
 FROM people;
 person_id | fname |     titles
-----------+-------+----------------
         1 | Bob   | Cat,Dog,Monkey
         2 | Jim   | Elephant
         3 | Geoff |
(3 rows)

I don't know what application language  you use (PHP, Ruby, or others), but all languages can easily parse this result.

I made dummy data and done EXPLAIN. The result is shown below:

test=# EXPLAIN
SELECT person_id,
fname,
(SELECT array_to_string(ARRAY(SELECT title FROM items WHERE items.person_id = people.person_id ), ',')) AS titles
 FROM people;
                                       QUERY PLAN

--------------------------------------------------------------------------------------
---
                                        QUERY PLAN

--------------------------------------------------------------------------------------
-----
 Seq Scan on people  (cost=0.00..984256.31 rows=9990 width=8)
   SubPlan 2
     ->  Result  (cost=98.50..98.51 rows=1 width=0)
           InitPlan 1 (returns $1)
             ->  Bitmap Heap Scan on items  (cost=19.81..98.50 rows=455 width=16)
                   Recheck Cond: (person_id = people.person_id)
                   ->  Bitmap Index Scan on items_idx  (cost=0.00..19.70 rows=455 widt
h=0)
                         Index Cond: (person_id = people.person_id)
(8 rows)

This query uses Bitmap Index scan, so it can be run very fast.

Please don't forget to create index of person_id in the items table.


Can SQL Server 2014 server reported drive space properties change overnight when database growth is set to Unlimited?

I ran the following query accessing the Default Trace log to find when the database did an Autogrow:

DECLARE @path NVARCHAR(260);
SELECT
   @path = REVERSE(SUBSTRING(REVERSE([path]),
   CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1
SELECT
   DatabaseName,
   HostName,
   ApplicationName,
   [FileName],
   SPID,
   Duration,
   StartTime,
   EndTime,
   FileType = CASE EventClass WHEN 92 THEN 'Data' ELSE 'Log' END
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass IN (92,93)
ORDER BY StartTime DESC

This showed the database autogrew by 10% during the time the change in drive space was noticed.  So the database simply did what it was supposed to do: grow by 10% when it was needed.


What is shared between two instances on the same server?

SQL Server instances have separate sets of binaries for each installation.  For the base engine, you will have separate SQL Server and SQL agent services that can run under separate accounts.  They each have their own configurations and users.  This is also true for other components like Analysis Services and Reporting Services if you install them.

There are shared components, including Management Studio, Integration Services, and the SQL Browser service.  You can find a full listing of shared components here.

Note, you will have several considerations to make when maintaining different instances:

SQL Version consistency (each instance can be of a different SQL version)
Memory Settings - Each instance will maintain a memory pool separate from the other instances.  You will want to plan out your Max Memory settings for each instance so that the different instances won't compete with each other.
Possible CPU contention - All instances will make use of the same CPU cores.  You can manage the processor affinity mask if you want to reserve threads for different instances, but I haven't had much of a need for that.

Overall, you'll need to keep in mind that all your instances will be competing for resources on the same server, so balance those resources accordingly.


SQL Server Replication, is it possible to have "upload only" syncronization on some articles?

You can add a filter like: 0 = 1, and the article becomes upload only


DTUTIL performance slower in SQL Server 2012?

The answer to this is yes, we had to convert the package to SQL 2012 format.  For now we keep our packages in SQL 2008 format in source control, and at deploy time migrate the page to 2012 format.  Once done, the performance is on par.


Tiered permissions to generate conditional data access

It isn't too hard to create table-driven access rules if you can base the rules on something that is in the data already, like a country code.

You want to beware the power of multiplication.  Don't try to create access rules based on the cross-product of two or three or more factors.  The management of such a rule set will quickly become impractical due to the shear amount of data.

Instead, keep each access rule separate (i.e. country, department) and treat each as a gate that the user has to get through before they get access.  This will be a bit of work when setting up your data access code, but it is the best way to operate the system in the long term.


Any data change action of this size if executed at once will cause a significant logging overhead and will require a lot of additional log space. That in turn will slow down the execution causing an even bigger impact on the rest of the system.

The best way to handle this is to break the delete down into smaller chunks. If you delete between 1000 and 10000 rows at a time and then wait for a few seconds the impact on other transactions should be small. Make sure each delete runs in a separate transaction so that all locks are released during the wait time. However, keep in mind that this will stretch out the delete over several hours or even days.

Also, even though it might sound counter intuitive, make sure there is an index supporting each delete so that MySQL does not have to execute a full table scan each time.


Create Materialized View from foreign table

PostgreSQL databases cannot "see" each other by default. This is a good thing.

You can make one pg database see another by using the postgres_fdw Foreign Data Wrapper: https://www.postgresql.org/docs/current/static/postgres-fdw.html

Steps:

Add the extension
Create the foreign server
Create the user mapping
Create the foreign table. See https://www.postgresql.org/docs/current/static/sql-importforeignschema.html if you have 9.5+
Create the materialized view pointing to the foreign table


Because the application requires changes to be written to a mongo node, the config is changed of node3 to leave the Replica set and act as a single node.

A normal replica set failover situation involves one or more members being unavailable, subject to the fault tolerance of your replica set configuration. As long as a majority of configured voting members in the replica set are healthy, they should be able to automatically elect a new primary without manual intervention.

If a majority of members are unavailable and you need to manually reconfigure the replica set to recover write availability, the correct process to follow is a forced reconfiguration with the surviving member(s). This process will ensure that the replica set version information is updated so when former members rejoin they will detect the new configuration and resume syncing if possible.

In this disaster scenario, you are effectively reconfiguring your replica set to have a single member (node3), and later re-adding other members to rebuild the replica set.

  Now node3 is accepting writes. This is fine so far.

This is actually not fine, as far as replication goes. If you restart a mongod that is part of a replica set without the replSet parameter and start writing in standalone mode, the standalone data will diverge from the other members of the replica set. Any standalone writes are not noted in the replication oplog, and there is no way to reconcile local changes with other writes that may have happened in the replica set.

  When re-enabling the replica set, node3 isn't set as master but as secondary, therefore it loses the writes and tries to catch up with node1 or node2, which is a master. This is the part where I lose my data.

If you later rejoin that mongod to the same replica set, the assumption will be that no writes have happened outside of the oplog. The member may also have an older view of the replica set configuration if there have been changes since the member was offline. Replication will try to sync to a common point in the oplog of a current replica set  member (assuming there is one), but you will have introduced data inconsistency via direct updates in standalone mode. Local writes will not be rolled back (or replicated) because the oplog has no record of the changes. This can lead to data loss and other conflicts, because node3 breaks the requirement that all members have the same data (aside from oplog entries not applied yet due to replication lag).

If you follow the forced reconfiguration approach noted earlier, you would end up re-adding former members to your new replica set configuration and would not lose any data.


The problem was that the screening part of the query was out of sync with the current database.

The corrected version uses:

context::json as contextobj

(like my effort)

WHERE typecode = 'IMAGERY'
    AND context != '  '
    AND r.createdon > '1/1/2016'

I didn't have this part.


Postgres psql requires " FROM database.table" in sql statement

....FROM database.table

That is not a database, that is a schema.  (With the weird and confusing name of "database").
You can refer to things with 3 name components, databasename.schemaname.tablename, but this is rarely done as then the first component must be the same as the name of the database you are connected to.  Which makes it pretty useless, other than as a way to maintain compatibility with other database software.
In one database, your tables are in a schema which is in the search_path, and in the other database they are not.  Maybe that is because the search_path differs between databases, or maybe because the tables are actually in differently named schemas.  You can use show search_path to see what search_path is in effect in each database.


How to remove an installed AE certificate from server using PowerShell Scripts?

It is just Remove-Item to remove the certificate, and then include -DeleteKey to also drop the private key.

Based on your command it should be something like this as you have to pass in the thumbprint of the certificate to the remove command, so we need to look it up first:

Get-ChildItem -Path Cert:\LoaclMachine\My -Recurse -DocumentEncryptionCert |
    Where-Object Subject -eq 'AlwaysEncryptedCert' | Remove-Item -WhatIf


How to free used space from disabled index?

No. Script out the disabled and unused indexes then drop them.  Save the script of index create statements in your flavor of Source control just in case you need them.


Use following command to get the backup data.

pg_dump --inserts -f filename.dump your_database

This commands generates the sql script that can be used to restore schema as well as data to other sql databases. The --inserts options tells it to use inserts instead of default copy commands.

Here is the manual - http://www.postgresql.org/docs/current/interactive/app-pgdump.html


How to fetch last row order by date and group by id

If I'm following you correctly, for each row in products, you want the most recent matching row in history_products. This query should do that:

SELECT prod.products_id, prod.products_date, prod.products_link
       history_products.history_products_name, history_products.history_products_update,
       history_products.history_products_stock, history_products.history_products_status
  FROM history_products
         JOIN (
               SELECT products.products_id, products.products_date, products.products_link,
                      MAX(STR_TO_DATE(history_products.history_products_update, "%d-%m-%Y %h:%i:%s%p")) as update_date
                 FROM products JOIN history_products
                                 ON products.products_id=history_products.history_products_id
                WHERE products.products_author_id=:products_author_id
                   && products.products_store_id=:products_store_id
                GROUP BY products.products_id, products.products_date, products.products_link
              ) prod ON (    history_products.history_products_id = prod.products_id
                         AND STR_TO_DATE(history_products.history_products_update, "%d-%m-%Y %h:%i:%s%p") = prod.update_date
                        )
;

Here's how it works:

In the subquery referred to as prod, we select the necessary columns from the products table, plus the maximum update date for that product.
Then, we connect back to history_products again, this time to pick up the details of the history_products row for the current products_id value, for the row where the history_products_update value matches the most recent update date.
NOTE: this query does assume that, for any given row in products, there will be one and only one history_products row with a given history_products_update date. If there is more than one history record for a specified date, then additional information would need to be available to determine which history_products record was actually the last one.

Since the ORDER BY in your original question appeared to have more to do with selecting the proper history_products row than with sorting the results, I have not included a sort order for the results. If the results should show the most recently updated product first, through the least recently updated product, you can add the following ORDER BY before the closing semi-colon:

ORDER BY prod.update_date DESC

NOTE: code not tested.


How to tell when DBO was changed (and if possible, by whom)

This is in the default trace. Whether you use sp_changedbowner or the proper, modern syntax, it will show up as the latter:

alter authorization on database::[foo] to [bar]

You can retrieve the information this way:

DECLARE @path NVARCHAR(260);

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

SELECT * -- whittle down to the meaningful trace columns
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass = 152
ORDER BY StartTime DESC;

Now, the event might have happened a long time ago, so it won't necessarily still be in the trace.


Oracle: Inserting data using a database link

If you want to have a database link on DB1 that points to DB2, DB1 would need to be able to resolve whatever TNS alias you specify in the database link definition.  Assuming that you are using tnsnames.ora files everywhere for TNS name resolution, that would mean that the tnsnames.ora file on DB1 would need to have an entry that points to DB2.  The TNS alias you specify in your CREATE DATABASE LINK statement would need to reference that TNS alias (which could be different than the alias for DB2 that you have created on your local machine).  If you have limited access to the machine where DB1 resides, you would most likely need the DBA for DB1 to ensure that the TNS alias gets created appropriately.


Postgresql select row with minimum value by certain column?

If you're not worrying about ties, then use the PostgreSQL extension for DISTINCT ON

SELECT DISTINCT ON (id) id, student, grade, subject
FROM table
ORDER BY id, grade;


Since batch inserts and LOAD DATA are much more efficient than lots of single-row inserts, your request is backward.

Rethink the real goal, and how to express it.


Missing Oracle Schema in SSMA Object Explorer

Try this

1) Open the project

2) Remove the checkbox on the Schemas branch in the Oracle Metadata Explorer
Right-click on the Schemas branch in the Oracle Metadata Explorer and select Refresh from Database.  This will automatically put a checkbox on ALL schemas.
Let the refresh run.  This step can take a good amount of time.

3) Save the project


It is because it may not have a value at that pariticular field/place so first replace the value providing box with:

iif(IsNothing(sum(field)),"0",(sum(field))

Then replace row summary field with the below expression:

=iif(cInt(ReportItems!textbox1name.value)=0,100,(ReportItems!textbox2.value/iif(cInt(ReportItems!textbox1name.value)=0,100,ReportItems!textbox1name.value)*100))


In the terminology used to to talk about conceptual design of database, this is called inverse relationship. So you could say:

  master has an inverse relationship with table detail.

See for instance: Fundamentals of database systems, by R. Elmasri, S. Navathe, Addison Wesley, ISBN: 978-0-13-608620-8, Chapter 11, Object and Object Relational Databases.


Using screen or tmux to handle this task will mean that if the SSH connection is lost the session will continue. When you log back in you will find the mysql -u xxx -p session is still active.

nohup works too but "screen and tmux are a sysadmin's best friends" as jynus commented.


Complex MySQL Query returning almost all the correct rows

Something like

SELECT DISTINCT
o.orderid,
i.invoiceid,
i.subinvoicenumber,
NULL AS oistockingorderid,
po.purchaseorderid,
subponumber,
po.paidvia,
dropshipfee,
i.taxstate
FROM tblorders o
    JOIN tblinvoices i ON o.orderid=i.orderid
    #ordertype of 0 means the order came from the website
    JOIN tblpurchaseorders po
      ON o.orderid=po.orderid
      AND IF(o.ordertype<>0, subinvoicenumber=subponumber, subinvoicenumber=0 AND subponumber>=0)
    LEFT JOIN tblallocations a ON a.orderid=o.orderid

UNION

SELECT DISTINCT
o.orderid,
i.invoiceid,
i.subinvoicenumber,
stockingorderid AS oistockingorderid,
NULL AS purchaseorderid,
subponumber,
po.paidvia,
dropshipfee,
i.taxstate
FROM tblorders o
    JOIN tblinvoices i ON o.orderid=i.orderid
    #ordertype of 0 means the order came from the website
    LEFT JOIN tblpurchaseorders po
      ON o.orderid=po.orderid
      AND IF(o.ordertype<>0, subinvoicenumber=subponumber, subinvoicenumber=0 AND subponumber>=0)
    LEFT JOIN tblallocations a ON a.orderid=o.orderid

The second SELECT is just the same as your original.
In the calculated fields you can omit the COALESCE from grosscost, and use the two halves separately (according to the non-NULL ID).


Consistency in non-persistent computed columns within transactions

There's a few different questions here.

Q: Can I force GETUTCDATE() to stay the same during a transaction?

No. If you have a long-running transaction, the time is going to change while you work. This has nothing to do with your isolation level, and more to do with the fact that life doesn't have a Pause button. (Yet. I've tried pointing Dr. Horrible's Freeze Ray at my computer, but that just made my solid state drives become even more solid.)

Q: Can I force non-deterministic functions to return the same result every time?

By definition, no. If you could, they would be deterministic.

Q: If I need dates/times to stay static during a transaction, what do I do?

If you only need a single value, create a variable, and set its value at the beginning of your transaction. In sp_BlitzFirst, for example, I set a @StartSampleTime variable, and then keep using that throughout the code.

If you need multiple rows, select the data into a permanent object like user table or a temp table, and repeatedly refer to them during the life of the transaction.


PgAdmin III - Number of lines returned during insertion

I am adding 1k inserts by connection to the base, so I don't think it's related.

I think it is related. You are probably adding exactly 1001 rows in each batch.

All these numbers are multiples of 1001.


Azure SQL very slow INSERT in stored procedure

This probably will not help but could you give it a try

INSERT INTO ItemProperty (PropertyId, Discriminator, ItemId)
SELECT @PropertyId, @Discriminator, Id
  FROM Item
  JOIN ItemType
    ON ItemType.ID = Item.ItemTypeId
   AND ItemType.ItemGroupId = @ItemGroupId


DEFINITIONS

First, you need to know what these status variables mean

Key_read_requests : The number of requests to read a key block from the MyISAM key cache.
Key_reads : The number of physical reads of a key block from disk into the MyISAM key cache. If Key_reads is large, then your key_buffer_size value is probably too small. The cache miss rate can be calculated as Key_reads/Key_read_requests.

OBSERVATIONS

40 reads from disk out of 350000 reads from the MyISAM Key buffer has a Cache hit Ratio of 100 X (1.0 - (40.0 / 350000.0)) = 99.98857 %.
1000 reads from disk out of 350000 reads from the MyISAM Key buffer has a Cache hit Ratio of 100 X (1.0 - (1000.0 / 350000.0)) = 99.7143 %.
I have discussed this aspect of Performance tuning before : How to improve MySQL Server Performance..??

CONJECTURE

When your Key_reads started increasing, it simply indicated you read new data and the index pages accessed have been freshly loaded into the MyISAM Key Cache. Such Key_reads are unavoidable. Once that spike stopped, then either your read requests have drastically reduced or subsequent queries are now using the key blocks you last read into the MyISAM Key Cache. High Key reads indicate that your queries are frequently reading index blocks from disk, loading them into the Key Cache, bumping out index blocks from the MyISAM Key Cache and subsequent queries are not accessing them.

CONCLUSION

Since your cache hit ratio (> 99.00 %) is excellent, I would say you are fine because your Key_reads are significantly low and infrequent.


merge like rows then update fk from another table to match the new values

You could update the second table first, then delete the duplicate (and now unreferenced) rows from the first table.

The (PARTITION BY name, dob, record_num) is what identifies rows as duplicates. If more or less columns are needed to identify then, adjust accordingly.

It would be good to put the two statements in a transaction to avoid weird effects / errors if other sessions access the table (inserting new rows or deleting between the 2 statements may result in the 2nd one to fail or having unreferenced rows in the end:

WITH ids AS
( SELECT dup_id = id,
         good_id = MIN(id) OVER (PARTITION BY name, dob, record_num)
  FROM tb1
)
UPDATE t2
SET t2.id = i.good_id
FROM tb2 AS t2
  JOIN ids AS i
    ON i.dup_id = t2.id
WHERE i.dup_id <> i.good_id ;

WITH ids AS
( SELECT dup_id = id,
         good_id = MIN(id) OVER (PARTITION BY name, dob, record_num)
  FROM tb1
)
DELETE d
FROM tb1 AS d
  JOIN ids AS i
    ON i.dup_id = d.id
WHERE i.dup_id <> i.good_id ;

Tested a rextester.com

The 2nd statement could have been written more simply but I find the above 1st way slightly more readable as the 2 statements have almost identical FROM and WHERE clauses.

DELETE i
FROM
      ( SELECT dup_id = id,
               good_id = MIN(id) OVER (PARTITION BY name, dob, record_num)
        FROM tb1
      )               -- the ids CTE rewritten as a derived table
      AS i
WHERE i.dup_id <> i.good_id ;


MySQL Handler_read_rnd_next == tablescans without indexes?

You should enable slow-query-log and log-queries-not-using-indexes to in order to manifest what queries are skipping on the indexes.

This is just a personal preference, but I usually the --processlist option of mk-query-digest. I wrote a automation script for it long ago : MySQL general query log performance effects based on Percona's video about mk-query-digest being a replacement for  the slow log.

Once the queries are made known, you will have run EXPLAIN on all selects. Any query whose EXPLAIN plan fails to choose an index is guilty of doing the table scans.


SqlServer Passing parameters within Nesting procedures

You need to add the @job_par parameter to your calling procedure so that you can then pass that down the line. Using your example:

CREATE PROCEDURE jolly
    (
      @job_par VARCHAR(100) = 'p%'
    )
AS
    SELECT  *
    FROM    emp
    WHERE   job LIKE @job_par;
GO

CREATE PROCEDURE test1
    @proc_name VARCHAR(30) ,
    @job_par VARCHAR(100)
AS
    EXEC @proc_name @job_par;
GO

EXEC test1 @proc_name = 'jolly', @job_par = 'ACCOUNT';


Can SQL Server OpenQuery/OpenRowSet to an LDAP Linked Server be used for Authentication?

I'm not sure why you'd need a linked server to LDAP to validate credentials? SQL Server has Active Directory (Windows) authentication built into it, so you should be able to leverage that as long as you add the appropriate Login object in SQL Server for either the AD User or AD Group that user is a part of, so they have the ability to connect.
Unless I'm missing something like you're trying to connect to LDAP of a different domain than that of the SQL Server instance, but then we'd more details on your goal.


Dealing with the corruption:

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

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

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

The actual question

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

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

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

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


How to sort category by path and sort order?

The problem is you need to extract the sort order for the root items and make that same order apply to the sub-items in the tree without re-ordering the sub-items.  In Oracle you can do this with a windowing function as follows:

SELECT RPAD('- ',Length(Path)-1,'- ') || label, levelx, path, sort_order
   , min(sort_order) OVER (PARTITION BY FirstLevel) Sort_Order2
FROM (
   SELECT id, parent_id, label, levelx, path, sort_order
      , substr(path,2,1) FirstLevel
   FROM t1 WHERE Label IS NOT NULL
)
ORDER BY Sort_Order2, Path;

I don't know if you can do something like that in MYSQL, so here is a version using a GROUP BY and self join that works in MySQL 5.5.28.

SELECT Label
FROM T1 a
JOIN
   (SELECT SUBSTR(Path,2,1) FirstLevel, MIN(Sort_Order) FirstSort FROM T1
      WHERE Label IS NOT NULL GROUP BY SUBSTR(Path,2,1)) b
   ON substr(a.Path,2,1) = b.FirstLevel
ORDER BY FirstSort, Path;

(SQL Fiddle)


How can Amazon RDS grant CREATE EXTENSION to their RDS_SUPERUSER role?

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


You can do that with a column check:

ALTER TABLE table ADD
  CHECK ((column->'id') is not null and (column->'name') is not null);


I will move all pdb data files,undo and create temp file into new directory.You can skip redo log files as they are fixed ,in Oracle Developer Day VM total size of Redo group files is 600M.

To move data files online execute sql command

  alter database move datafile 'source' to 'target' reuse;

            [oracle@localhost ~]$ rman target /

            Recovery Manager: Release 18.0.0.0.0 - Production on Wed May 1 11:20:30 2019
            Version 18.3.0.0.0

            Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.

            connected to target database: ORCLCDB (DBID=2760656746)

            RMAN> report schema
            2> ;

            using target database control file instead of recovery catalog
            Report of database schema for database with db_unique_name ORCLCDB

            List of Permanent Datafiles
            ===========================
            File Size(MB) Tablespace           RB segs Datafile Name
            ---- -------- -------------------- ------- ------------------------
            1    840      SYSTEM               YES     /u01/app/oracle/oradata/ORCLCDB/system01.dbf
            3    530      SYSAUX               NO      /u01/app/oracle/oradata/ORCLCDB/sysaux01.dbf
            5    270      PDB$SEED:SYSTEM      NO      /u01/app/oracle/oradata/ORCLCDB/pdbseed/system01.dbf
            6    360      PDB$SEED:SYSAUX      NO      /u01/app/oracle/oradata/ORCLCDB/pdbseed/sysaux01.dbf
            7    5        USERS                NO      /u01/app/oracle/oradata/ORCLCDB/users01.dbf
            8    100      PDB$SEED:UNDOTBS1    NO      /u01/app/oracle/oradata/ORCLCDB/pdbseed/undotbs01.dbf
            9    380      ORCL:SYSTEM          YES     /u01/app/oracle/oradata/ORCLCDB/orcl/system01.dbf
            10   1210     ORCL:SYSAUX          NO      /u01/app/oracle/oradata/ORCLCDB/orcl/sysaux01.dbf
            12   72       ORCL:USERS           NO      /u01/app/oracle/oradata/ORCLCDB/orcl/users01.dbf
            13   2        ORCL:APEX_1581590175460392 NO      /u01/app/oracle/oradata/ORCLCDB/orcl/APEX_1581590175460392.dbf
            14   50       UNDOTBS2             YES     /u01/app/oracle/oradata/ORCLCDB/undotbs2.dbf
            15   50       ORCL:UNDOTBS2        YES     /u01/app/oracle/oradata/ORCLCDB/orcl/undotbs2.dbf

            List of Temporary Files
            =======================
            File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
            ---- -------- -------------------- ----------- --------------------
            1    33       TEMP                 32767       /u01/app/oracle/oradata/ORCLCDB/temp01.dbf
            2    62       PDB$SEED:TEMP        32767       /u01/app/oracle/oradata/ORCLCDB/pdbseed/temp012018-10-15_15-43-55-439-PM.dbf
            3    62       ORCL:TEMP            32767       /u01/app/oracle/oradata/ORCLCDB/orcl/temp01.dbf

            RMAN> exit

            Recovery Manager complete.
            [oracle@localhost ~]$  mkdir -p /u02/oradata/pdb/orcl -- creating new directories for pdb files
            [oracle@localhost ~]$ sqlplus sys/oracle@orcl as sysdba --login into pdb database as sysdba

            SQL*Plus: Release 18.0.0.0.0 - Production on Wed May 1 11:26:29 2019
            Version 18.3.0.0.0

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

            Connected to:
            Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
            Version 18.3.0.0.0

            SQL> alter database move datafile '/u01/app/oracle/oradata/ORCLCDB/orcl/system01.dbf' to '/u02/oradata/pdb/orcl/system01.dbf' reuse;

            Database altered.

            SQL> alter database move datafile '/u01/app/oracle/oradata/ORCLCDB/orcl/sysaux01.dbf' to '/u02/oradata/pdb/orcl/sysaux01.dbf' reuse;

            Database altered.

            SQL> alter database move datafile '/u01/app/oracle/oradata/ORCLCDB/orcl/users01.dbf' to '/u02/oradata/pdb/orcl/users01.dbf' reuse;

            Database altered.

            SQL>  alter database move datafile '/u01/app/oracle/oradata/ORCLCDB/orcl/APEX_1581590175460392.dbf' to '/u02/oradata/pdb/orcl/APEX_1581590175460392.dbf' reuse;

            Database altered.

            SQL>  alter database move datafile '/u01/app/oracle/oradata/ORCLCDB/orcl/undotbs2.dbf' TO '/u02/oradata/pdb/orcl/undotbs2.dbf' reuse;

            Database altered.

        SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/oradata/pdb/orcl/temp01.dbf' SIZE 65011712  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;

        Tablespace altered.

    SQL> alter database tempfile '/u01/app/oracle/oradata/ORCLCDB/orcl/temp01.dbf' drop including datafiles;  -- drop tempfile on old directory

    Database altered.

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name ORCLCDB

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    840      SYSTEM               YES     /u01/app/oracle/oradata/ORCLCDB/system01.dbf
3    530      SYSAUX               NO      /u01/app/oracle/oradata/ORCLCDB/sysaux01.dbf
5    270      PDB$SEED:SYSTEM      NO      /u01/app/oracle/oradata/ORCLCDB/pdbseed/system01.dbf
6    360      PDB$SEED:SYSAUX      NO      /u01/app/oracle/oradata/ORCLCDB/pdbseed/sysaux01.dbf
7    5        USERS                NO      /u01/app/oracle/oradata/ORCLCDB/users01.dbf
8    100      PDB$SEED:UNDOTBS1    NO      /u01/app/oracle/oradata/ORCLCDB/pdbseed/undotbs01.dbf
9    380      ORCL:SYSTEM          YES     /u02/oradata/pdb/orcl/system01.dbf
10   1210     ORCL:SYSAUX          NO      /u02/oradata/pdb/orcl/sysaux01.dbf
12   72       ORCL:USERS           NO      /u02/oradata/pdb/orcl/users01.dbf
13   2        ORCL:APEX_1581590175460392 NO      /u02/oradata/pdb/orcl/APEX_1581590175460392.dbf
14   50       UNDOTBS2             YES     /u01/app/oracle/oradata/ORCLCDB/undotbs2.dbf
15   50       ORCL:UNDOTBS2        YES     /u02/oradata/pdb/orcl/undotbs2.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    33       TEMP                 32767       /u01/app/oracle/oradata/ORCLCDB/temp01.dbf
2    62       PDB$SEED:TEMP        32767       /u01/app/oracle/oradata/ORCLCDB/pdbseed/temp012018-10-15_15-43-55-439-PM.dbf
4    62       ORCL:TEMP            32767       /u02/oradata/pdb/orcl/temp01.dbf


What is '/Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization' telling me?

Here is a sample output of XML from sys.dm_os_ring_buffers where WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'. I will explain each item from this XML.

<Record id = "38446" type ="RING_BUFFER_SCHEDULER_MONITOR" time ="2313297649">
<SchedulerMonitorEvent>
    <SystemHealth>
        <ProcessUtilization>2</ProcessUtilization>
        <SystemIdle>91</SystemIdle>
        <UserModeTime>310468750</UserModeTime>
        <KernelModeTime>35781250</KernelModeTime>
        <PageFaults>7004</PageFaults>
        <WorkingSetDelta>0</WorkingSetDelta>
        <MemoryUtilization>100</MemoryUtilization>
    </SystemHealth>
</SchedulerMonitorEvent>

ProcessUtilization Indicates the amount of CPU SQL Server was using at the time of the snapshot.
SystemIdle Amount of Idle CPU that nothing is using. Available for any process that requires CPU.
(100-ProcessUtilization-System Idle) CPU being used by processes other than SQL Server.
UserModeTime Indicates the amount of CPU worker thread (Running in user mode) used during the period it did not yield. You need to divide this value by 10,000 to get time in milliseconds
KernelModeTime Indicates the amount of CPU worker thread (Running in Windows kernel) used during the period it did not yield. You need to divide this value by 10,000 to get time in milliseconds.
PageFaults - Number of page faults at the time of the snapshot. A page fault occurs when a program requests an address on a page that is not in the current set of memory-resident pages.
WorkingSetDelta Difference in working set between last and current snapshot.
MemoryUtilization Indicates the percentage of memory SQL Server is using based on max server memory (MB) setting. 100% is normal in this case as SQL OS is based on a greedy algorithm. It will consume all memory unless it is forced to give up memory due to external factors.

  When I run it against one of my servers I'm getting 1, can someone
  explain this value?

Means you are using 1% of total CPU (guessing all core are visible to SQL operating system) at the time of the snapshot. You want to collect this for over a period of time and set your baseline.  That way at any time you know if your running high or low compare to your baseline.

Some additional resource:

One of the best article by Denzi Ribeiro about troubleshooting high CPU issue using sys.dm_os_ring_buffers
How to understand the RING_BUFFER_SCHEDULE_MONITOR by Jack Vambas


SQL Server Always-On Availability Groups LeaseTimeout

If you'd like to know why, the answer will be in the cluster log. If you need help looking over it, please upload the primary node's (at the time) cluster log.

Clustering makes the decisions when it comes to FCIs and AGs with SQL Server. The cluster is responsible for metadata updates and changes, health checking, resource placements, etc., and most people don't change the by default cluster behavior which is to restart the resource after a period of time.

Since in your case it connected back up one minute later, the best guess without the cluster log is that this is what occurred, especially since you've stated that the default clustering settings have not been changed.

More Info 1: https://docs.microsoft.com/en-us/previous-versions/orphan-topics/ws.11/dn281898(v=ws.11)?redirectedfrom=MSDN

More Info 2: https://support.microsoft.com/en-us/help/228923/cluster-resources-can-be-configured-to-restart-automatically

  so does restarting the resource mean restarting the SQL instance?

No, it restarts the AG Resource which is not a SQL Server instance.

  What I dont understand is, if its set to restart the SQL service before trying a failover (when it comes to a leasetimeout) - wouldn't a restart of the service/resource cause a failover anyway?

No, because it's not the instance. The availability group tied to that resource will stop synchronizing and will go into a resolving state but the instance itself will not have any issues.


Is there an easier way to show why this query is always equivalent to bitor?

In short, you want to prove that:

bitand(x,y) + bitor(x,y) = x + y

Split the x and y numbers into the respective bits. Now, lets prove first that the same stands for bits a and b (a and b can only have the values 0and 1):

bitand(a,b) + bitor(a,b) = a + b

But for bits these are trivial:

bitand(a,b) = least(a,b)
bitor(a,b) = greatest(a,b)

Therefore (assuming a <=b without any loss of generality):

bitand(a,b) + bitor(a,b) = least(a,b) + greatest(a,b) = a + b

So, back to x and y and splitting them:

x = a0 * 1  +  a1 * 2  +  a2 * 4  + ... +  an * 2^n
y = b0 * 1  +  b1 * 2  +  b2 * 4  + ... +  bn * 2^n

x + y = (a0+b0) * 1  +  (a1+b1) * 2  +  ... +  (an+bn) * 2^n

But we can do the same for bitand and bitor:

bitand(x,y) = c0 * 1  +  c1 * 2  +  c2 * 4  + ... +  cn * 2^n
bitor(x,y)  = d0 * 1  +  d1 * 2  +  d2 * 4  + ... +  dn * 2^n

bitand(x,y) + bitor(x,y) = (c0+d0) * 1  +  (c1+d1) * 2  +  ... +  (cn+dn) * 2^n

Considering also that:

bitand(ai,bi) = ci
bitor(ai,bi) = di

we apply our previous results to find:

x + y = (c0+d0) * 1  +  (c1+d1) * 2  +  ... +  (cn+dn) * 2^n

and we have a proof.


Grouping date's as 1 count if difference is 5 days

If I understand your question, the following is the query that you are looking for.

SQL> select * from mytest;

        ID DT
---------- ---------
        22 15-MAY-17
        22 15-MAY-17
        22 15-MAY-17
        22 16-MAY-17
        22 22-MAY-17
        23 23-MAY-17
        23 25-MAY-17
        23 30-MAY-17

8 rows selected.

SQL> select id, count(diff)+1 count
     from(
          select id, dt - lag(dt) over(order by dt) diff
          from mytest
          )
     where diff >= 5
     group by id;

        ID         COUNT
---------- -------------
        22             2
        23             2

Here is the fiddle.

For more about LAG()

Documentation: LAG


How to improve Procedure Cache Hit Ratio?

Let me summarize (and round!) the important data points in your spreadsheet:

      Total                                     Use Count 1
      ---------------------------------------   -----------------------
      Total Plans   Total MBs   Avg Use Count   Total Plans   Total MBs
      -----------   ---------   -------------   -----------   ---------
Adhoc      55,987       3,054               3        38,314       2,036
Proc          709       1,502           1,549           135         527

So the first row shows the bad stuff, taking up about 2/3 of your plan cache (things which are mostly only ever used once, with a few very minor exceptions). You need to try and get rid of as much of these as you can. The second row shows the good stuff. These are the things you want in your plan cache (plans with a high amount of reuse). The rest of the data is largely irrelevant IMHO. One other point though: you say that access is exclusively through stored procedures, but if those procedures use dynamic SQL, those statements are cached as AdHoc plans, not Proc plans.

On 2008 or greater, I would say turn on optimize for ad hoc workloads and move on to the next problem - this would take the amount of MBs your single-use plans currently occupy down to next to nothing. Unfortunately, on 2005, your options are quite limited, aside from refactoring those stored procedures to use statement-level OPTION (RECOMPILE) and/or less / no dynamic SQL, or turning on forced parameterization at the database level - which tries to get better plan reuse out of similar queries by treating literals as parameters for plan matching purposes. I hesitate to even mention plan guides because they're not for the timid and - as I discuss later on in this answer - I'm not sure it's worth going down that path unless you know your plan cache is definitely the source of your performance issue.

I asked about @@VERSION because, before SP2, the algorithm for the amount of memory that could be allocated to the plan cache was relatively loosey-goosey. As of SP2 they tightened that up quite a bit (the change is documented and explained in this post and this post). In your case, the plan cache is relatively full, so it is not surprising you're getting cache misses. 26 GB = an upper limit of 5.8 GB; I see ~4.5 GB in the spreadsheet but there may be some calculation or configuration difference here that I don't know about.

This MSDN article talks about the optimize for ad hoc workloads server setting added in 2008, and mentions trace flag 8032, which will allow you to allocate more memory to your caches (presumably in the absence of setting this setting at the server level, which I now recommend to all of our customers, or at least the 99% that are no longer on 2005). I have never tested this trace flag on 2005 SP3 or SP4, and honestly not even sure when it was introduced. I also don't know if it will solve your problem or just shift it, since I think even if you had some % more RAM allocated to caches, you'd still be filling it and having lots of cache misses because of the nature of your stored procedures.

Or, of course, if there is even a problem to solve that relates directly to the plan cache at all. Just because your cache hit ratio isn't as high as you might expect does not mean that it is causing your problem, and of course the converse is that even at 100% cache hit ratio - which doesn't seem realistic given that so many of your plans are single-use and ad hoc - your users may still be suffering from performance problems caused by something else entirely.

My suggestion is to look for better smoking guns than plan cache hit ratio. Get more specifics about your users' performance complaints. Are all queries always slow? Certain queries? Certain times of day / week / business cycle? Are only reporting queries slow? Take a serious read through this admittedly dry and long document on SQL Server best practices - in particular, the section on waits and queues, which can help you formulate a logical approach to identifying, diagnosing and solving performance issues. Making some number on a dashboard look better - a number that you don't even know directly contributes to the problem - might be very satisfying, but if it doesn't solve your users' performance issues, then it hasn't really gotten you anywhere.

These may also be useful in reading up on compilation / recompilation and plan cache reuse. Some of these are focused on 2008 (particularly those about the ad hoc workloads setting), but much of the information is still useful for 2005 and/or to better understand the benefits of upgrading (hint, hint).

http://technet.microsoft.com/en-us/library/ms181055(v=sql.90).aspx
http://www.sqlperformance.com/2013/08/t-sql-queries/parameter-sniffing-embedding-and-the-recompile-options
http://technet.microsoft.com/en-us/library/ee343986(v=sql.100).aspx
http://www.sqlskills.com/blogs/kimberly/plan-cache-and-optimizing-for-adhoc-workloads/
http://www.sqlskills.com/blogs/kimberly/plan-cache-adhoc-workloads-and-clearing-the-single-use-plan-cache-bloat/
http://www.sqlskills.com/blogs/kimberly/clearing-the-cache-are-there-other-options/
http://www.sqlskills.com/blogs/kimberly/exec-and-sp_executesql-how-are-they-different/
http://www.sqlskills.com/blogs/kimberly/prepared-statements-and-caching/
http://www.sommarskog.se/query-plan-mysteries.html
http://www.sommarskog.se/dynamic_sql.html


Reliability of Scimore embedded database

I've worked a few with it, and until now, it works like a charm, no concurrency problems, fast and depending on clients, lite as you want it to be.
I do not know if it would hace issues at electrical shutdowns while saving data
but i'm using it for technical data (Engineering data)

Cheers!


Azure SQL Server outdated version - how can I upgrade it to current version?

Azure SQL Database is automatically updated, but it uses a different version numbering scheme from “box” SQL Server.  It went from “11” to “12” several years ago during a major breaking change, and will stay on “12” indefinitely.
It always has the latest features and fixes for the SQL Server engine and is ahead of SQL Server 2019.  By the time a new version of SQL Server is released, the code has been running in production in Azure SQL Database for a long time.
You control the database compatibility level of your databases to opt-in to breaking changes and query optimizer upgrades.  The database compatibility levels map to the major releases of SQL Server.  Over time the default compatibility level for new Azure SQL Databases increases up as new compatibility levels are introduced.


Consider using a table-valued parameter to pass many rows of data as a single proc call, or alternatively, bulk copy directly into the table.  These techniques will improve insert throughput by orders of magnitude compared to singleton inserts, even if those are multi-threaded.

An incremental clustered index will provide the best insert performance against spinning media.


Why don't your input parameters match the type for the table? Why would you want to keep the wrong types there and perform any casts or conversions at all (whether implicit or explicit)? Why are you converting anything to FLOAT, of all things? To address specific questions:

  My query says [low] is being converted from int to numeric but that doesn't seem to be what the cluster index seek details is showing, is it?

The convert of low is happening in the output, not in the seek predicate (the predicate is what is used to find matching rows and/or eliminate non-matching rows).

  Is there a way to tell in this specific example how much "Better" it would be if I did the conversions Explicitly? I would think I could just do a "cast" in the query and insert a few numbers where the variables are, is that correct?

There's no way to make the execution plan show you how much better a different plan would be, except to generate that different plan and compare. You can use this comparison to document how much better it would be if the interface were correct (and two other ways would be to keep the interface but (a) perform explicit converts in the query - not of the column, but of the variables or (b) use local variables of the right type and assign them the values of the parameters). So you could show them 3 different ways to solve the problem, and show evidence that all 3 are better than the current version.

My recommendation is to fix the procedure the right way. First let's look at the actual types you care about:

USE master;
GO
SELECT t.name, c.max_length/CASE
  WHEN t.name LIKE N'n[cvt]%' THEN 2 ELSE 1 END
FROM sys.all_columns AS c
INNER JOIN sys.types AS t
ON c.system_type_id = t.system_type_id
AND c.system_type_id = t.user_type_id
WHERE EXISTS
(
  SELECT 1 FROM sys.all_objects AS o
    INNER JOIN sys.schemas AS s
    ON o.[schema_id] = s.[schema_id]
    WHERE o.[object_id] = c.[object_id]
    AND o.name = N'spt_values'
    AND s.name = N'dbo'
)
AND c.name IN (N'number',N'type');

Results:

number    int     4
type      nchar   3

So the interface to your stored procedure should be:

USE yourdb;
GO
ALTER PROCEDURE dbo.some_name
  @1 INT,
  @2 NCHAR(3),
  @3 NUMERIC(4, 0)
AS
BEGIN
  SET NOCOUNT ON;

  SELECT CONVERT([float], [low] / @3, 0) -- don't think you want float here
    FROM [master].[dbo].[spt_values]
    WHERE [number] = @1
    AND [type] = @2;
END
GO

Implicit conversions between varchar and nvarchar can be particularly bad (especially in the opposite scenario as yours - parameter is nvarchar and column is varchar), but there really is no reason to allow for a 8000-character parameter of any type when the longest string possible in the table is 3 characters...


Your query would look like this
SELECT Name
FROM ARTICLE
WHERE Id_Article_CATEGORY IN (SELECT ID FROM Article_category WHERE Name = 'Laptop')
AND ID IN (SELECT id_ARTiLCE FROM Article_Property WHERE id_PROPERTY IN (SELECT ID FROM Property WHERE (Name =  'SSD' AND Unit  >= 512) OR (Name = 'weight ' AND Unit < 2)))
AND price <= 2000;

article_property is a bridge table, you need there two columns atleast id-article and id_property to link both tables. when you have a property you need to get its id and see if there are articles that have this property.
that is valid for all bridge tables in your eer.
at this point you start with a schema and change it when the need arises. Your structure is flexible yur programming too, you you can realize almost anything.
if you find a weak pint you can change the structure by adding columns, removing is more complicated, because tigger functions and so on have to be checked.
you should still go back and examine existing e-commerce solutions, for your basic needs there a some tutorials to implement it, also they have a gui in php or python with a functioning framework and even when you want to implement your own look for laravel and other frameworks,that help you with a lot of functions, there is no need to invent the wheel all over again


MySQL Call Stored Procedure along with an UPDATE statement

No, you can't do it directly.
Convert your stored procedure into a function and use the return and some user defined variables for the update.
you also can call the stored procedure in a function, and youse the return values.


Assuming you also only want B when an entity has two types, or if they have A and C you don't care which one you get:

;WITH x AS
(
  SELECT type, entity, rn = ROW_NUMBER() OVER
    (
     PARTITION BY entity
     ORDER BY CASE type WHEN 'B' THEN 1 ELSE 2 END
    )
  FROM dbo.tablename
)
SELECT type, entity
  FROM x
  WHERE rn = 1;

If you care about the order of A and C in the case where those are the only two (in this example A will be returned):

     ORDER BY CASE type WHEN 'B' THEN 1 WHEN 'A' THEN 2 ELSE 3 END


How to keep the data warehouse synchronised with the source systems?

Change tracking offers the functionality you're looking for. When switched on, SQL Server creates additional internal tables.  Changes to your data tables are noted in these internal tables.  System functions allow you to pull changes out of these internal tables and transfer only changed rows.

A related technology - Change Data Capture - allows you to see the "before" and "after" values of changes.


How to find statement executed by sp_executesql in [sys].[dm_exec_query_stats]?

Your problem is caused by the ST.[dbid] = DB_ID() in your WHERE clause.

I've taken a basic example of what I believe you are trying to do from AdventureWorks:

USE AdventureWorks;
GO

DECLARE @sql NVARCHAR(2000);

SET @SQL = 'SELECT TOP 10 * FROM Person.Person';

SET @SQL = @SQL + '--' + REPLACE('F2B287FD-1419-48DB-9881-4AA77B99450E', '-', '');

EXEC sys.sp_executesql @sql;

When you try and get the dbid value from the dm_exec_sql_text() DMV, it's actually NULL for this query, so you are immediately filtering it out.

However, by using the dm_exec_plan_attributes() DMV, WHERE attribute = 'dbid' you can get the correct DBID() value for your current database.

This is my current go-to query to retrieve this information.

  SELECT
    deqs.plan_handle AS PlanHandle
  ,deqs.query_hash AS QueryHash
  ,dest.dbid
  ,DB_NAME(CAST(depa.value AS SMALLINT)) AS DatabaseName
  ,OBJECT_SCHEMA_NAME(dest.objectid, CAST(depa.value AS INT)) AS SchemaName
  ,OBJECT_NAME(dest.objectid, CAST(depa.value AS INT)) AS ObjectName
  ,MAX(last_execution_time) AS LastExecuted
  ,deqs.creation_time AS PlanCreationTime
  ,SUM(deqs.execution_count) AS Executions
  ,SUM(total_elapsed_time) AS SumDuration
  ,CAST(100.0 * SUM(total_elapsed_time)
              / SUM(SUM(total_elapsed_time)) OVER() AS NUMERIC(5,2)) AS PctDuration
  ,SUM(total_worker_time) AS SumCPU
  ,CAST(100.0 * SUM(total_worker_time)
              / SUM(SUM(total_worker_time)) OVER() AS NUMERIC(5,2)) AS PctCPU
  ,SUM(total_logical_reads + total_logical_writes + total_physical_reads) AS [SumIO]
  ,CAST(100.0 * SUM(total_logical_reads + total_logical_writes + total_physical_reads)
              / SUM(SUM(total_logical_reads + total_logical_writes + total_physical_reads)) OVER() AS NUMERIC(5,2)) AS PctIO
  ,Q.statementtext
  ,deqs.query_plan_hash AS QueryPlanHash
  ,SUM(total_rows) AS RowsAffected
  ,MAX(statement_start_offset) statement_start_offset
  ,MAX(statement_end_offset) statement_end_offset
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
CROSS APPLY sys.dm_exec_plan_attributes(plan_handle) AS depa
CROSS APPLY (VALUES (SUBSTRING(dest.text, (deqs.statement_start_offset/2)+1,
                          ((CASE deqs.statement_end_offset WHEN -1 THEN DATALENGTH(dest.text)
                                ELSE deqs.statement_end_offset
                              END - deqs.statement_start_offset)/2)+1))) AS Q(statementtext)
WHERE deqs.last_execution_time > DATEADD(dd, -1, GETDATE())
      AND depa.attribute = 'dbid'
      AND Q.statementtext LIKE '%F2B287FD141948DB98814AA77B99450E%'
GROUP BY
      dest.text, Q.statementtext, deqs.plan_handle, deqs.creation_time,
        dest.objectid, deqs.query_hash, deqs.query_plan_hash, depa.value, dest.dbid;


Why SQL Server uses Non-Clustered but not Clustered Index?

The optimizer has a choice between two main strategies:

Scan the table (the clustered index) checking every row to see if LoanNum = 2712.
Scan & Lookup

Scan the nonclustered index to find rows where LoanNum = 2712
Look up the column data for the matched rows not covered by the nonclustered index.

The key point is that the nonclustered index is smaller, so scanning it is expected to be cheaper. This might seem counterintuitive because the clustered index definition has the same keys, and the nonclustered index has included columns, but the point is that the clustered index includes all columns stored in-row - the leaf (lowest) level of the clustered index literally is the in-row data.

For a small number of expected matches, the cost saving from scanning the smaller index is more than enough to compensate for the key lookups.

Incidentally, you may find that removing the WHERE 1 = 1 from your query causes the optimizer to choose the clustered index scan. The (redundant) constant-to-constant comparison prevents SQL Server parameterizing the query, so estimates are based on statistical information about LoanNum 2712. If the query is parameterized, SQL Server will use the average distribution of LoanNum values, which might result in a higher number of expected rows, and a change of plan choice.

See also:

Effective Clustered Indexes by Michelle Ufford
How (and why) does TOP impact an execution plan?


Access SQL Server Express LocalDB from local area network

LocalDB cannot be accessed remotely, it's by design and documented.

Here is a description of LocalDB.

Here is a list of LocalDB features and limitations.

I don't see how installing SQL Server can be a problem. 120 MB is not that big nowadays even for small applications. You can embed the setup in your application and ship SQL Express with your setup kit. If you need a SQL Server Edition with a small footprint, consider using SQL Server Compact Edition.


Why am I getting "Snapshot isolation transaction aborted due to update conflict"?

When deleting from the parent table, SQL Server must check for the existence of any FK child rows that refer to that row. When there is no suitable child index, this check performs a full scan of the child table:

If the scan encounters a row that has been modified since the delete command's snapshot transaction started, it will fail with an update conflict (by definition). A full scan will obviously touch every row in the table.

With a suitable index, SQL Server can locate and test just the rows in the child table that could match the to-be-deleted parent. When these particular rows have not been modified, no update conflict occurs:

Note that foreign key checks under row versioning isolation levels take shared locks (for correctness) as well as detecting update conflicts. For example, the internal hints on the child table accesses above are:

PhyOp_Range TBL: [dbo].[Child]
    Hints( READ-COMMITTEDLOCK FORCEDINDEX DETECT-SNAPSHOT-CONFLICT )

Sadly this is not currently exposed in execution plans.

Related articles of mine:

The SNAPSHOT Isolation Level
Data Modifications under Read Committed Snapshot Isolation


MySql global variable disconnect_on_expired_password

This should give you a good idea as to which mysql user(s) is/are causing the error:

SELECT user, host FROM mysql.user WHERE password_expired = 'Y';
To change disconnect_on_expired_password: Edit /etc/my.cnf, find the mysqld section, add this line:

disconnect_on_expired_password=OFF

Then restart mysqld.


"Heap Blocks: " Number meaning on Query Plan for a bitmap heap + index scan

4MB was enough to hold references to 2964 heap blocks (plus sub-references to 3531 tuples falling within those blocks).  It doesn't hold the blocks themselves.  The blocks themselves would be held in shared_buffers (Or cycled through shared_buffers if it were insufficient to hold them all), not in work_mem.


Any way of optimizing joins between 2 databases with different collations?

If you can add columns to tables, you can try persisted computed columns, and index those. For example:

ALTER TABLE dbo.DeliveryDetail ADD DeliveryNoteNo_Collated COLLATE whatever PERSISTED;

CREATE INDEX IX_DeliveryDateNo_Collated ON dbo.DeliveryDetail(DeliveryNoteNo_Collated);

You're basically pre-baking converted versions of your fields once on insert (and kept updated for you automatically), and indexing those.

There are two really big drawbacks. First, these are new columns in your table: they'll take up more space, the indexes will take up more space, and if your apps are doing inserts without explicitly listing their column names, their inserts will fail.

Second, if the app's connection options aren't set correctly, your delete/update/insert operations can actually fail. NUMERIC_ROUNDABORT has to be set OFF, and ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, and QUOTED_IDENTIFIER must all be set to ON. You'll definitely want to test for this in development first, and for bonus points, check sys.dm_exec_sessions in production to check everybody's session settings.

For more info:

Books Online page on computed columns
MSSQLTips demo on using computed columns


File 1 belongs to the SYSTEM tablespace.
Objects with such low id as 39 are dictionary objects. For example in my database it is an index of OBJ$ (I_OBJ4). ORA-08102 also points us towards an index, which seems to be corrupt based on the error you get.

Find the index name by:

select object_name from dba_objects where object_id = 39;

Unfortunately you will not be able to rebuild this index (ORA-00701), but you can recreate it:

shutdown immediate
startup upgrade
alter session set nls_length_semantics=byte;
@?/rdbms/admin/utlmmig


This would be legal syntax - using ISO format for dates (YYYY-MM-DD), which is always a good idea as it does not depend on the current setting of datestyle. Your example only works with datestyle = 'DMY'.
 '{"[2018-03-19,2020-12-29)","[2018-06-21,2020-12-29)"}'

You can always ask Postgres to show you:
test=# SELECT ARRAY ['[2018-03-19,2020-12-29)'::daterange ,'[2018-06-21,2020-12-29)'];
                         array
-------------------------------------------------------
 {"[2018-03-19,2020-12-29)","[2018-06-21,2020-12-29)"}
(1 row)

The error msg you report does not seem to be generated by Postgres.

argument formats can't be mixed

Are you, by any chance, using an outdated version of pgAdmin III? See:

pgAdmin argument formats can't be mixed


There are three separate parts to the query. I'll label them so I can reference each more clearly:

SELECT DISTINCT cname             -- Part A
FROM account AS cust_account
WHERE NOT EXISTS ( SELECT type    -- Part B
                   FROM account
                   EXCEPT
                   SELECT type    -- Part C
                   FROM account
                   WHERE account.cname = cust_account.cname )

First the inner query. Part C lists all type values for account rows owned by the customer currently being considered by the outer query (Part A). Conceptually you can think of the DBMS reading all rows that match the outer query then stepping through them one at a time. For each outer row it executes the inner query. (In reality likely something more time-efficient happens but it will be logically equivalent to this.)

Part C is sometimes called a correlated sub-query because it is matched (correlated) with a value coming from the outer query. In this case it is matching on cname. It is the WHERE clause that tells the DBMS to match Part C to Part A. cust_account is an alias defined in the outer query. Because a single table - account - is used three times in the statement the alias allows us to specify exactly which of these three we want to reference. It is the alias which allows us to say "match Part C to Part A."

Part B lists all type values from table account. This part has no WHERE so it lists all accounts for all users, not just the one currently being considered by the outer query. This part is not correlated.

The EXCEPT takes the results of the query above it (Part B) and removes those rows which occur in the query below it (Part C). Since Part B is "all types that are in the database" and Part C is "all types the current cname owns" then "B except C" is "all types except those which the current cname owns." If we have a list of all types, and we remove those which the cname owns, what is left is those types which the cname does not own. So if the inner query has any rows (for the current cname) we know there is at least one type of account that cname does not own.

EXISTS() returns true if the inner query has row(s). So NOT EXISTS() returns true if the inner query is empty. As we have just shown, it will be empty if the current cname owns all types of accounts. So the outer query lists all cname which have all types of account - as requested in the problem.


Don't use merge, it has always had performance problems on large tables.  This has been an on going issue since the key word merge was put into SQL Server.  See Microsoft Connect and one other site below:

https://connect.microsoft.com/SQLServer/feedback/details/635778/not-matched-and-matched-parts-of-a-sql-merge-statement-are-not-optimized

http://www.sqlservercentral.com/Forums/Topic1465931-391-1.aspx

OK, looking at your code, looks like you're trying to do everything in a single pass (which is often a good idea).
In this case, you're using the WHEN NOT MATCHED predicate, which requires a Full Outer Join to include matched rows and unmatched rows in a single pass.
The individual DELETE and UPDATE statements do not suffer from this issue, so they actually perform better.

Hope this helps
Gianluca


How to completely get rid of replication subscriptions?

Take a look at this article: https://www.mssqltips.com/sqlservertip/2710/steps-to-clean-up-orphaned-replication-settings-in-sql-server/

Try dropping the old subscriptions, all publications and then removing replication from the DB, i.e.

exec sp_dropsubscription --on all subscriptions

then

exec sp_droppublication --on all publications

then

exec sp_replicationdboption

Also, take a look at this question: How to fix a messed up replication on MS SQL Server


Conversion of a varchar data type to a datetime data type resulted in an out-of-range value

I checked your profile and saw that you are in the UK. If your sql server is set to use the dateformat dmy then that explains your issue. Without using the 'T' instead of the space in the datetime string, Sql Server won't recognize it as ISO8601 format.

Try this:

select count(*)
  from dbo.profile
  where [created] between convert(datetime,'2014-11-01T00:00:00.000')
                      and convert(datetime,'2014-11-30T23:59:59.997');

Querying using dates and/or datetimes can be tricky, to make sure you are getting what you are looking for I recommend reading:

Aaron Bertrand's Bad habits to kick : mis-handling date / range queries
Robyn Page's SQL Server DATE/TIME Workbench

edit: to clarify the out of range value in your error message would be from interpreting the month as 30 and the day as 11.


Can SQL Server 2008 / 2012 have execute permission in a function for a user, but not for a table used in the function?

In SQL Server you can define the execution context.  This way the user only has rights to execute the function, but when it is executed, it runs in the context specified.

http://msdn.microsoft.com/en-us/library/ms188354(v=sql.100).aspx

Functions (except inline table-valued functions), Stored Procedures, and DML Triggers
{ EXEC | EXECUTE } AS { CALLER | SELF | OWNER | 'user_name' }

The following example would execute the procedure as the owner of the procedure, generally dbo, rather than the user calling it.

CREATE PROCEDURE dbo.usp_Demo
WITH EXECUTE AS OWNER
AS
SELECT user_name();
GO


How to set Password for sqlplus /as sysdba

You'll need to decide whether you want to use OS authentication or use a password file. See http://docs.oracle.com/cd/E11882_01/server.112/e25494/dba006.htm#i1006628 to help you decide.

Will users administer the database by first logging in to the database server using eg ssh? Then you can use OS authentication.

Best practice for OS authentication: No-one should be logging in as the oracle OS user. Each DBA should have their own OS account and login as that. Their OS account should be a member of the OS DBA group (remember that only members of the OS DBA group can login using sqlplus / as sysdba).

If you want to allow remote administration, then you'll either need a secure connection to the database (just like SSH provides that to the server when administering locally) or you'll need to use a password file.


What you had originally was a correct syntax - for tables, not for schemas.  As you did not have a table (dubbed 'relation' in the error message), it threw the not-found error.

I see you've already noticed this - I believe there is no better way of learning than to fix our own mistakes ;)

But there is something more.  What you are doing above is too much on one hand, and not enough on the other.

Running the script, you

create a schema
create a role
grant SELECT on all tables in the schema created in (1.) to this new role_
and, finally, grant all privileges (CREATE and USAGE) on the new schema to the new role

The problem lies within point (3.)  You granted privileges on tables in replays - but there are no tables in there!  There might be some in the future, but at this point the schema is completely empty.  This way, the GRANT in (3.) does nothing - this way you are doing too much.

But what about the future tables?

There is a command for covering them: ALTER DEFAULT PRIVILEGES.  It applies not only to tables, but:

  Currently [as of 9.4], only the privileges for tables (including views and foreign tables), sequences, functions, and types (including domains) can be altered.

There is one important limitation, too:

  You can change default privileges only for objects that will be created by yourself or by roles that you are a member of.

This means that a table created by alice, who is neither you nor a role than you are a member of (can be checked, for example, by using \du in psql), will not take the prescribed access rights.  The optional FOR ROLE clause is used for specifying the 'table creator' role you are a member of.  In many cases, this implies it is a good idea to create all database objects using the same role - like mydatabase_owner.

A small example to show this at work:

CREATE ROLE test_owner; -- cannot log in
CREATE SCHEMA replays AUTHORIZATION test_owner;
GRANT ALL ON SCHEMA replays TO test_owner;

SET ROLE TO test_owner; -- here we change the context,
                        -- so that the next statement is issued as the owner role

ALTER DEFAULT PRIVILEGES IN SCHEMA replays GRANT SELECT ON TABLES TO alice;

CREATE TABLE replays.replayer (r_id serial PRIMARY KEY);

RESET ROLE; -- changing the context back to the original role

CREATE TABLE replays.replay_event (re_id serial PRIMARY KEY);

-- and now compare the two

\dp replays.replayer
                                   Access privileges
 Schema  │   Name   │ Type  │       Access privileges       │ Column access privileges
─────────┼──────────┼───────┼───────────────────────────────┼──────────────────────────
 replays │ replayer │ table │ alice=r/test_owner           ↵│
         │          │       │ test_owner=arwdDxt/test_owner │

\dp replays.replay_event
                               Access privileges
 Schema  │     Name     │ Type  │ Access privileges │ Column access privileges
─────────┼──────────────┼───────┼───────────────────┼──────────────────────────
 replays │ replay_event │ table │                   │

As you can see, alice has no explicit rights on the latter table.  (In this case, she can still SELECT from the table, being a member of the public pseudorole, but I didn't want to clutter the example by revoking the rights from public.)


Are you using one of the default traces in Profiler or are you creating it manually?

If I were in the same situation, I would just save the trace to a regular text file and then import that file into a SQL Server table.

USE mydb
GO

SELECT * INTO mytracetable FROM ::fn_trace_gettable('c:\my_trace.trc', default)
GO

At least that way you are certain to get the same columns everytime.   As to the BULK INSERT statements, you could try this filter.

Column Filter for TextData with the Not Like set to %BULK INSERT%.


Merge Replication from SQL 2008 R2 to SQL 2005 Express: SQL Server version (10.50) is not supported

Try to change setting,

Step 1: Launch the DFS Management Console (on the cluster node ‘PrimaryNode’).

Step 2: Click on the ‘New Replication Group…’ action.

Step 3: Select the type of replication group.

Step 4: Select the name and domain for the replication group.

Step 5: Specify the branch office file server (replication member)

Step 6: Select the folders to replicate from the branch office server.

Step 7: Specify the hub server (other replication member)

Step 8: Specify the path to the replicated folder on the hub server.

Step 9: Configure the replication schedule and bandwidth utilization.

then you apply and hope your problem will be solve.

http://www.techyv.com/questions/2008-r-2-replication-errors


Connection to MySQL fails via PHP while command-line works fine

Found the answer in the MySQL manual, Pluggable Authentication:

  As of MySQL 5.5.7, the server authenticates clients using a plugin. Selection of the proper account row from the mysql.user table is based on the user name and client host, as before, but the server authenticates the client by determining from the account row which authentication plugin applies for the client.

The MySQL package creates a root user using the auth_socket authentication plugin, so only connections via a socket are permitted, and the username of the Linux account is checked as well.

The mysql command uses a socket by default, and runs as root in my script, so it was allowed to connect.

According to the PHP manual, a PDO MySQL connection to localhost also uses a socket; but PHP is run as an unprivileged user, so the connection is refused by the auth_socket plugin.

I fixed my script by adding this line:

mysql --user=root -e \
"UPDATE mysql.user SET plugin='mysql_native_password'; FLUSH PRIVILEGES";

And it now works fine.