Durability of storage in second generation google cloud sql How durable should I expect data for a Google Cloud SQL instance to be? Just a single instance, with the high-availability option disabled.

AWS explicitly mention that Amazon Aurora data is stored in six copies over multiple availability zones. Is any such information available for Google Cloud SQL?

You may not find any such information for GCP because Aurora is a storage engine native to Amazon. It can be used with MySQL or PostgreSQL.
You only have three(3) options here:
OPTION #1
You need to read the Amazon Documentation on Aurora and try to locate the same storage options and features in CloudSQL. Thoroughly scour the GCP Documentation for any new storage options for CloudSQL similar to Aurora.
OPTION #2
You should also focus on what Google provides for CloudSQL High Availability

Overview of the High Availability Configuration
Configuring an Instance for High Availability
Instance Locations

OPTION #3
You may want to consider just customizing a solution with ComputeEngine instances with MySQL manually installed and create frequent snapshots of your data folder (which should contain InnoDB tables only along with binary logs). Additional instances (at least 1) for setting up MySQL Replication slaves may be need to still have additional HA.


SQL Server 2012 and DBM Why would MSFT would deprecate mirroring from 2012.. I understand alwaysON will replace it but then SE users are left with nothing..no alwaysON or dbm from future releases.. Just analyzing from end users POV.

There is a difference between - Features not supported in NEXT version of sql server vs features not supported in a FUTURE version of sql server.

Mirroring is still available in SQL Server 2012, 2014 and 2016. It has been put into the future deprecated list, but as of now, it is still available.

AlwaysON (as it was with mirroring when it was first introduced in sql server 2005) is an enterprise feature and in sql server 2016 (CTP2.1) it supports upto 2 nodes in standard edition as well.


PostgreSQL concurrent increment of counter I need to maintain a statistical table for a project, composed by a list of items and their usage (Think about something like a website in which you would like to count pageviews). Each time an item is instanced I need to increment usage of the specific item.

My first implementation is:

statistics(
  id      integer NOT NULL,
  name    character varying(255) NOT NULL,
  usage   integer NOT NULL DEFAULT 0,
);

UPDATE statistics
  SET usage = usage + 1
WHERE name = '<name>';

My concerns are about performance and concurrency. The update process will be instantiated by several tens (maybe 80-120) devices and could occur several times per second, so my questions are:

1) will this method preserve concurrency? (ie if more than one devices request the update "at same time", will every request be counted?)

2) can you suggest a best way to achieve the result? I expect to have load in writing the updates, while the reads would me much more frequent. Does it exist a specific function to increment values? I'm looking at "sequence" but I'm not sure if that is the right way...

Thank you a lot in advance for any advice

The second update would wait for the previous update on the same rows to be comitted, but will then see the committed value.

Assume two concurrent transactions updating the same row with an initial value of 0

Time Transation 1  T1 value    Transaction 2  T2 value
--------------------------------------------------------------
1    update ...    1                          0
2                  1            update ..     "undefined"
                                (waits)
3    commit        1                          2
4                  1            commit        2
5                  2                          2

"T1 Value" and "T2 Value" means the value that that transaction sees.

If you want to make sure you catch situations where there are "incompatible" changes (e.g. one transaction setting the usage column to a specific value, rather than just incrementing it) you can put all transactions into the "serializable" isolation level. But you will need to prepare for error handling then.

Updates to different names can run concurrently without any waits (because different rows are affected).

SELECTs will never be blocked but will only see comitted values.


getting error installing oracle 11gR2: cannot access node I'm installing Oracle 11.2 on dedicated hosted server (centOS 5.5). I've gone through the Oracle Quick Installation guide diligently. I believe I've set everything up correctly, although I'm not a DBA and this is my first time doing such a thing. I'm at the point where I've started the Oracle Installer, and made it step 8 of 11, which is the Prerequisite Checks for "typical" install. I'm getting many errors here, they all point to the same thing (e.g. can't access node). Here's the first error, which is for physical memory:

Physical Memory - This is a prerequisite condition to test whether the system has at least 1GB (1048576.0KB) of total physical memory.

Expected Value

 : n/a

Actual Value

 : n/a

 List of errors:

 - PRVF-7531 : Physical memory check cannot be performed on node "host2"  - Cause:  Could not perform check of physical memory on the node indicated.  - Action:  Ensure ability to access the node specified and view memory information.

My hosted server has address of www.host2.domainname.com.

UPDATE1: I've observed that the oracle user cannot ping its own server:

oracle@host2 [~]# ping host2
ping: icmp open socket: Operation not permitted

The root user can ping host2.

Is there some way I can change permissions/etc. to enable oracle user to ping the server? (I'm assuming this is a sympton of the problem, but let me know if I'm on the wrong track here).

UPDATE2: (is anyone reading this?) I've learned that to make the oracle user able to ping, I can adjust the permissions of the /bin/ping file from

-rwxr-xr-x, to
-rwsr-xr-x

However, there are various places that strongly warn against doing this, such as:

http://forums.cpanel.net/f5/ping-icmp-open-socket-operation-not-permitted-14173.html
[http://nixshell.wordpress.com/2007/04/21/suid-shell-scripts-setting-the-sticky-bit/][1]

I haven't changed this permission, not sure if this is the right approach. I'm thinking that the oracle installation instructions are very detailed, and if this was needed, it would be documented to do so. Any oracle experts out there that can offer any advice?

UPDATE 3: I suspect the "server hardening" performed by the hosting company upon provisioning the server has had some effect on the Oracle database installation process, resulting in this error. I implemented chmod 4755 /bin/ping, as well as chmod 4755 /bin/ping6 (logged in as root). I verified that the oracle user can successfully ping host2 now. Then, I ran the Oracle universal installer. The same thing happened -- all of the pre-requisite checks fail (every one of them for the same reason stated above, cannot connect to node: host2). In desperation, I selected "Ignore All" checkbox, and continued the installation. Hey, the install completed successfully! The database runs OK from an initial check.

I have no idea if changing permissions on the ping and ping6 files were necessary or useful here (e.g. I didn't try to complete the install before changing their permissions, so I don't know if this was actually needed; of course I'm reluctant to revert them back in case things break). Are there any experienced Oracle users that can comment/advise on whether I need the /bin/ping file to be -rwsr-xr-x, or can I change back to -rwxr-xr-x?

Also, the list of server hardening is here:

http://www.liquidweb.com/services/serversecure-linux.html

I'd be interested if anyone has an opinion if any of these hardening rules will impact the use of Oracle database. So far, I've found a workaround for limited permissions for the /tmp directory, which allowed me to complete the installation (but doesn't exist thereafter). Does Oracle db need /tmp directory thereafter? etc.

It looks like the hardening was a little to hard. Oracle does need some access to the server. You can revert back the chmods for ping, Oracle rdbms does not need it. /tmp/ is not required, a usable location for tmp is, mostly for scripting. The rdbms has it's own tricks and storage for sorts.

Never install Oracle as root; use the root.sh at the end of the installation to do the system tweeks required. On some platforms there is a root pre script that should run before installation. The Oracle installation scripts refuse to run using the root account and can be run using any other account.

The access to some server config files has been made a little too tight for the install user to check the prerequisites. If you know they are ok, it's ok. Proving they are ok is a little easier when support issues arise. Check the group ownership of the critical config files and add the Oracle installation user to that group to give it read access.


Difference between full and level 0 rman backups? Suppose we take a full backup, and after that we take a level 0 backup, both using RMAN and Oracle 10g.

I'm confused in the use of the level 0 backup and full backup. I found that the difference between both is only that level 0 backup is used for incremental backups but full backup isn't.

What are benefits of that full backup which we have take before the level 0 backup? How will Oracle use the full backup and the incremental backup?

When we perform recovery, will Oracle use that full backup which was taken before the level 0 backup?

The RMAN backup concepts guide (link for 11gR2, but the concept hasn't changed):

  The only difference between a level 0 incremental backup and a full backup is that a full backup is never included in an incremental strategy.

Full and level zero backups copy all blocks that have data (and more if you're doing image copies), so they are both "full" in that sense.
 But if you want to do cumulative or differential backups, the starting point for those must be a level zero incremental backup.


Compressing partition - what about index? To save space on disk I want to compress some partitions of my tables:

alter table TEST_TAB move partition TEST_TAB_2014 compress for all operations;

I have also indexes on this partition.
Should I recreate indexes after compression, or is it not necessary?

This online compression is extra cost option. And moreover usually online compression has worse compression ratio, then offline one. It's because the 1st one work on row level while the latter one works on block level. So if you really want to spare some space in the database and your old data are mostly read-only you should use:
ALTER TABLE table_name MOVE partition TEST_TAB_2014 COMPRESS;

And then rebuild(not recreated) all indexes manually, preferably with reasonable parallel option.
PS: you should also read something about various compression options Oracle offers. Unfortunately it is not simple from both technical and also licensing perspective.
Briefly, Oracle supports these compression options:

BASIC, offline, block level. Usually the bigger the tablespace's block size the better compression ratio. This one is used only when moving tables/partitions, or when using direct path load (/* +APPEND */ hint)

OTLP, online, row level, being part of advanced compression extra cost option.

ALTER TABLE … COMPRESS FOR OLTP  This approach will enable OLTP Table
Compression for all future DML -- however, the existing data in the
table will remain uncompressed.
ALTER TABLE … MOVE COMPRESS FOR OLTP  This approach will enable OLTP
Table Compression for future DML and also compress existing data.

COLUMNAR, works on column level, this one can also used for LOB compression. You can use it only on Exatada or when you use some SAN/NAS storage from Oracle.


Lock Escalation and Count Discrepancy in lock_acquired Extended Event I'm trying to understand why there is a discrepancy in lock count in sys.dm_tran_locks and sqlserver.lock_acquired extended event in certain cases. Here is my repro script, I'm using the StackOverflow2013 database on SQL Server 2019 RTM, compat level 150.
/* Initial Setup */
IF OBJECT_ID('dbo.HighQuestionScores', 'U') IS NOT NULL
DROP TABLE dbo.HighQuestionScores;

CREATE TABLE dbo.HighQuestionScores
(
    Id INT PRIMARY KEY CLUSTERED,
    DisplayName NVARCHAR(40) NOT NULL,
    Reputation BIGINT NOT NULL,
    Score BIGINT
)

INSERT dbo.HighQuestionScores
        (Id, DisplayName, Reputation, Score)
SELECT u.Id,
       u.DisplayName,
       u.Reputation,
       NULL
FROM dbo.Users AS u;

CREATE INDEX ix_HighQuestionScores_Reputation ON dbo.HighQuestionScores  (Reputation);

Next I update the table statistics with a large fake row count
/* Chaotic Evil. */
UPDATE STATISTICS dbo.HighQuestionScores WITH ROWCOUNT = 99999999999999;
DBCC FREEPROCCACHE WITH NO_INFOMSGS;

Then I open a transaction and update Score for Reputation, say 56
BEGIN TRAN;
UPDATE dbo.HighQuestionScores
SET Score = 1
WHERE Reputation = 56 /* 8066 records */
AND 1 = (SELECT 1);
/* Source: https://www.erikdarlingdata.com/sql-server/helpers-views-and-functions-i-use-in-presentations/ Thanks, Erik */
SELECT *
FROM dbo.WhatsUpLocks(@@SPID) AS wul
WHERE wul.locked_object = N'HighQuestionScores'
ROLLBACK;

I get a bunch of page locks (despite having an index on Reputation). I'm guessing the bad estimates really did a number on the optimizer there.

I also double checked using sp_whoisactive and it too returns the same information.
<Object name="HighQuestionScores" schema_name="dbo">
  <Locks>
    <Lock resource_type="OBJECT" request_mode="IX" request_status="GRANT" request_count="1" />
    <Lock resource_type="PAGE" page_type="*" index_name="PK__HighQues__3214EC072EE1ADBA" request_mode="X" request_status="GRANT" request_count="6159" />
  </Locks>
</Object>

Meanwhile I also have an extended event running on sqlserver.lock_acquired separately. When I look at the grouped data I see 8066 page locks instead of initial 6159

I definitely do not a see a lock escalation (verified using sqlserver.lock_escalation event), so I guess my question is why is the extended event showing a discrepancy with a higher number of lock count?

The XE is reporting a page lock being acquired each time a row is updated (one event for each of the 8066 rows affected by the update).  However, these rows are only stored on 6159 unique pages, which explains the discrepancy.
I don't have StackOverflow2013 on this machine, but get a similar experience with SO2010:

1368 rows updated (and that many XEvents fired)
958 page locks

You can see the same pages being locked repeatedly in the XE output if you sort by resource_0:

Using DBCC PAGE:
DBCC TRACEON (3604); -- needed for the next one to work
GO

DBCC PAGE (StackOverflow2010, 1, 180020, 3);
GO

I can see that page 180020 has 163 records on it (m_slotCnt = 163):
Page @0x000002C278C62000

m_pageId = (1:180020)               m_headerVersion = 1                 m_type = 1
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 174   m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594049331200
Metadata: PartitionId = 72057594044350464                                Metadata: IndexId = 1
Metadata: ObjectId = 1525580473     m_prevPage = (1:180019)             m_nextPage = (1:180021)
pminlen = 24                        m_slotCnt = 163                     m_freeCnt = 31
m_freeData = 7835                   m_reservedCnt = 0                   m_lsn = (203:19986:617)
m_xactReserved = 0                  m_xdesId = (0:0)                    m_ghostRecCnt = 0
m_tornBits = 1894114769             DB Frag ID = 1

And that 3 of those match the update criteria (I pasted the output into notepad++ and searched for "Reputation = 56"):

Here's the first match, as an example:
Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 39
Memory Dump @0x000000EDB2B7821D

0000000000000000:   30001800 86540000 38000000 00000000 6041c2e4  0...T..8.......`AÂä
0000000000000014:   c3020000 04000801 00270045 00720069 006300    Ã........'.E.r.i.c.

Slot 9 Column 1 Offset 0x4 Length 4 Length (physical) 4

Id = 21638

Slot 9 Column 2 Offset 0x1f Length 8 Length (physical) 8

DisplayName = Eric

Slot 9 Column 3 Offset 0x8 Length 8 Length (physical) 8

Reputation = 56

Slot 9 Column 4 Offset 0x0 Length 0 Length (physical) 0

Score = [NULL]

Slot 9 Offset 0x0 Length 0 Length (physical) 0

KeyHashValue = (e1caffa60313)
Slot 10 Offset 0x244 Length 43

I believe this behavior is due to the pipelined nature of execution plans, and the way this specific XE is implemented.


Redshift: How to fix serializable isolation violation(1023) caused by concurrent MERGE operations? My use case is to extract, transform and load data incrementally  and in real time from x number of Lambda functions. I expect multiple Lambda functions to be running concurrently and Redshift to stay alive for read queries.

Since Redshift doesn't enforce primary key(s) constraints, I'm using aws documentation Merge examples - Example of a merge that replaces existing rows to enforce unique rows. This method works fine when there is only 1 instance of lambda function running.

-- Start a new transaction
begin transaction;

-- Delete any rows from SALES that exist in STAGESALES, because they are updates
-- The join includes a redundant predicate to collocate on the distribution key
–- A filter on saletime enables a range-restricted scan on SALES

delete from sales
using stagesales
where sales.salesid = stagesales.salesid
and sales.listid = stagesales.listid
and sales.saletime > '2008-11-30';

-- Insert all the rows from the staging table into the target table
insert into sales
select * from stagesales;

-- End transaction and commit
end transaction;

-- Drop the staging table
drop table stagesales;

But as soon as > 1 lambda functions are running concurrently and accessing the same table, I'll receive:

"ERROR: 1023 DETAIL: Serializable isolation violation on table in Redshift" when performing operations in a transaction concurrently with another session.

How should I modify this example to allow it to run in a concurrent environment?

"Redshift is good at parallelism, not so good at concurrency. It's a feature."

Option 1

Is there more than one table you are loading to?

  One strategy is to enforce no more that one concurrent lambda per destination table.

You can do that with one primary lambda, limit it to 1 concurrent invocation. That primary can then in turn invoke a secondary lambda multiple times, each secondary handling a single destination table by passing on a filtered event message. You only need two lambda functions deployed, and only the secondary one is allowed multiple concurrent invocations. There are variations on that approach which involve queues or pubsub solutions. The performance gain here is in loading multiple tables concurrently rather than a single table.

Option 2

  Just use update rather than a delete then insert.

The challenge is that if two sessions both delete from the sales where saletime > somedate and then both insert records to the same table, then there is no order in which they could run serially which would produce the same result as when run concurrently. It's also ambiguous what you would want to happen when they run concurrently when the delete uses a wide date range like that.

  This could still fail if two sessions both open transactions that try
  to update the same rows.

If that is the case then you are abusing Redshift, effectively trying to use it for write transactions like an app database, doing OLTP in an analytical OLAP system, and you should consider a different datastore that is optimized for writes, perhaps something like Cassandra, or Postgres. You can also use something like Snowflake with separate dedicated write compute cluster and read cluster over the same data. Even with those alternatives it is just like every other OLAP system, optimized for bulk loads not small transactions, you don't get MVCC or fancy transaction isolation modes simultaneously with distributed parallel read performance. If you have budget then there are some hybrid options like MemSQL that allow a bit of both, but have their own compromises. For example there's no large cheap data storage on S3 / Google Buckets / Azure Blob storage, RAM is fast but scaling RAM is not cheap.

Option 3

  Do more specific deletes by filtering to a specific set of primary key values.

If you deleted specific rows e.g. based on a list of primary key values, then the two sessions would be unlikely to clobber each other in the way it does with the date filter.

Most ELT/ETL tools - e.g. stitch, fivetran, hevo, too many others - that load to Redshift will dump to S3, then COPY to a staging table and then uses insert when it is certain they are new rows or update when it is not certain. If you split out the known inserts and do them first then you can minimize the impact of the less performant updates.

When you do updates in Redshift, it will mark the rows for deletion (which will happen later in a vacuum operation) and insert the rows you are updating.

So it is already going to do implicitly, what you are trying to do explicitly. I do like the idea of running the delete then the insert though, because it gets to the point of what is going to happen anyway, it's just that you have now surfaced the serializability challenge with that strategy. In fact I have worked with a system that does exactly that, however it runs on a single EC2 and writes serially to Redshift, and despite the best practice recommendations it is fast, but uses a significant amount of IO and CPU on the Redshift cluster. Bulk loading with COPY is cheaper, especially if you split files on S3 into a sympathetic number of files in ratio to your count of cluster nodes, and use a manifest; the only way to really achieve parallel loads.

If you have multiple sources updating the same destination table then I would consider landing the data in separate tables before combining them.

If you have frequent changes coming from the same source to the same destination, then know that Redshift has a single commit queue and forces everything to be serialized, there is no performance gain to be had from attempting concurrent writes. To get the concurrent sessions to run, you either have to lock the table explicitly, a locking means one of them waits... which means they end up running serially. The other way you can get them to run without the error is organize your explicitly coded transactions to run serially, i.e. just limit it to a single invocation, in the knowledge that there isn't any real write concurrency anyway, so it's not going to lose any performance.

Redshift is just not optimized for that kind of write transaction isolation, the way that Postgres or other OLTP RDBMS are.

For more see How do I resolve the error "ERROR: 1023 DETAIL: Serializable isolation violation on table in Redshift"?


SUM of ever increasing column I have a table of "transactions" where each transaction has an amount:
http://sqlfiddle.com/#!15/42849/1

Records in the table are never REMOVE'ed or UPDATE'ed. Only new transactions are added.

I wish to calculate the SUM of the amounts. The calculation doesn't have to be 100 percent up to date for every request.

On a dataset of about a million rows this takes about 400 ms on my database. This is way too slow for my application and I am trying to find the best solution for speeding this up.

What I have tried so far

Materialized view: Adds complexity of having to have a cronjob running which updates the view every X seconds.
Caching on the application server: Every X request will be slow when the cache needs an update.
Storing results of queries on an old subset: Store the SUM of previous request and use these to calculate the correct total. Adds complexity.

Question

Does PostgreSQL provide a solution for speeding this type of query up?

Update 1

The SUM query is just a basic sum on a single column so I don't believe that this query in itself can get any faster. The solution is probably to do some kind of caching/precalculation or similar. Does PostgreSQL have any features in this regard?

Update 2

Table in question:

CREATE TABLE transactions
(
  id bigserial NOT NULL,
  amount bigint NOT NULL
);

Query in question:

SELECT SUM(amount) FROM transactions;

Update 3

I found that I actually need a "type" as well.

Updated table:

CREATE TABLE transactions
(
  id bigserial NOT NULL,
  amount bigint NOT NULL,
  type int NOT NULL
);

Updated query:

SELECT SUM(amount) FROM transactions GROUP BY type;

SQL Fiddle:
http://sqlfiddle.com/#!15/77e67/2

Here's one idea that you can evaluate:

CREATE TABLE last_transaction
(    last_id bigserial NOT NULL
,    cumulative_amount bigint NOT NULL
);

INSERT INTO last_transaction (last_id, cumulative_amount) VALUES (-1,0);

The current amount should be something like:

SELECT coalesce(SUM(t.amount),0) + coalesce(lt.cumulative_amount,0)
FROM transactions t
RIGHT JOIN last_transaction lt
    ON t.id > lt.last_id
GROUP BY lt.cumulative_amount;

On a regular basis you can refresh last_transaction similar to:

update last_transaction
    set last_id = (select max(id) from transactions)
      , cumulative_amount = (select sum(amount) from transactions);

The version of PostgreSQL in your fiddle does not support (perhaps no version does?)

set (last_id, cumulative_amount) = (select ...)

Just an idea, that may or may not fit your needs.

Edit: added type

If a type is to be included (consider naming it transaction_type or something similar) we can extend last_transaction:

CREATE TABLE last_transaction
(    type int not null
,    last_id bigserial NOT NULL
,    cumulative_amount bigint NOT NULL
,        constraint pk_last_transaction primary key (type)
);

INSERT INTO last_transaction (type, last_id, cumulative_amount)
SELECT distinct type, -1, 0
FROM transactions;

To get the current_amount we need to add type to the GROUP BY clause as well as to the ON clause.

SELECT lt.type
     , coalesce(SUM(t.amount),0) + coalesce(lt.cumulative_amount,0)
FROM transactions t
RIGHT JOIN last_transaction lt
    ON t.id > lt.last_id
   AND t.type = lt.type
GROUP BY lt.type, lt.cumulative_amount;

To do a full refresh (according to @Andriy M suggestion) of last_transaction:

UPDATE last_transaction AS lt
    SET last_id = t.last_id
      , cumulative_amount = t.cumulative_amount
FROM (
    SELECT TYPE
         , MAX(id)
         , SUM(amount)
    FROM transactions
    GROUP BY TYPE
) AS t (type, last_id, cumulative_amount)
WHERE t.type = lt.type;

I have yet to examine @YperSillyCubeᵀᴹ suggestion.

I added about a million rows to the transaction table and what I believe would be relevant indexes, but the plan in sqlfiddle looks kind of disappointing.


Duration measure in fact table I have a fact table with the user reservations with StartDate and EndDate.
I am thinking what is the proper way of serving the duration queries in the reports. Should I calculate DurationDays and DurationHours at the Fact ingestion or this is better to be done at the report run time? Or maybe I should have a separate dimension for durations?

You should calculate this during your ETL process or create a calculated column on your fact table.  Then, aggregations will be easier and queries will be somewhat faster than calculating the values at runtime.


export data from oracle 10g I installed Oracle 11g on my computer.
When I export data from Oracle 10g, I get the following error code. I have translated the error in parenthesis.

 EXP-00008: 遇到 ORACLE 错误 904  （encouter oracle error 904）
 ORA-00904: "POLTYP": 标识符无效   (POLTYP: is not valid )
 EXP-00000: 导出终止失败           (export fail)

Does someone know why? I believe it is ok to export from Oracle 10g using Oracle 11g client.

Unfortunately not, you cannot export from a 10g database with the 11g client. You can, though, use a dump file from a 10g database (exported with the 10g client) to import into an 11g database (with the 11g client).

Alternatively you can use the newer datapump functionality, and if you create a database link from 11g to 10g you can even transfer the data directly between them without needing to create an intermediate dump file at all.


Can an index on one table be used in the query for another table? I am working in SQL Server (2014).

I have a table that contains an XML column. There are a well known set of queries against that column that I would like to materialize as persisted computed columns for querying purposes. However, I don't want to add those columns to the table for various reasons.

My question is, is there some way to create a second table that contains indexes/keys that will be used by the optimizer for the first table? Is this achievable with hints or some other mechanism?

Selective XML Indexes does just that for you behind the scenes.

It allows you to specify an XPath expression in the index and when you use that expression in a query SQL Server uses a system table to retrieve the value you are looking for.

Example:

create table dbo.T
(
  ID int identity primary key,
  XMLDoc xml not null
);

go

create selective xml index SIX_T on dbo.T(XMLDoc) for
(
  Node1 = '/root/node1/text()' as sql int
);

go

insert into dbo.T(XMLDoc) values('
<root>
  <node1>1</node1>
</root>');

go

select T.XMLDoc.value('(/root/node1/text())[1]', 'int')
from dbo.T;

The query plan for the XML query shows a table scan of T and a nested loops join with a seek on the primary key into the system table that holds the value you are looking for.


Awful execution plans after migrating from MySQL 5.5.11 to MariaDB 5.5.41 We just migrated our web site database from a server running MySQL 5.5.11 to one running MariaDB 5.5.41. Everything is working mostly fine, except there's at least one query from our storefront that's generating pretty terrible execution plans on the new server, causing scans of a table that's around 6 GB.

It's a query with two InnoDB tables and a relatively simple join between them, on two columns that are indexed (PK in catalog_category_flat_store_1, but not a covering index on core_url_rewrite):

SELECT
    `main_table`.`entity_id`,
    main_table.`name`,
    main_table.`path`,
    `main_table`.`is_active`,
    `main_table`.`is_anchor`,
    `url_rewrite`.`request_path`
FROM `catalog_category_flat_store_1` AS `main_table`
    LEFT JOIN `core_url_rewrite` AS `url_rewrite`
        ON url_rewrite.category_id=main_table.entity_id
        AND url_rewrite.is_system=1
        AND url_rewrite.product_id IS NULL
        AND url_rewrite.store_id='1'
        AND url_rewrite.id_path LIKE 'category/%'
WHERE (main_table.is_active = '1') AND (main_table.include_in_menu = '1') AND (main_table.path like '1/2/5/204/225/%') AND (`level` <= 5) ORDER BY `main_table`.`position` ASC

catalog_category_flat_store_1 has 627 rows, and core_url_rewrite has 8,687,266 rows (kill me).

The EXPLAIN shows me that catalog_category_flat_store_1 is "Using index condition; Using where; Using filesort", and core_url_rewrite (the huge table) is "Range checked for each record (index map: 0xF4)", despite allegedly using the index on category_id as its key.

On the old server, catalog_category_flat_store_1 is "Using where; Using filesort", and core_url_rewrite doesn't have anything listed for "Extra", and appears to be using the correct index.

I noticed MariaDB has a lot of additional options for optimizer_switch, and I'm wondering if any of these options are causing the different optimizer behavior, so I figure it's worth testing. The problem is that I don't know what the values should be to make MariaDB act like MySQL.

Here's the old server, where the query runs just fine:

index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on

And here's the new server, where the exact same query can end up running for ages:

index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off

P.S. Greetings from Magento core_url_rewrite hell.

Well, this is a bit of voodoo/shotgun debugging, but I've got it functioning okay for the time being. I set the extended_keys option to on, and also created a covering index on the 8-million-row table in question. Now I'm getting an execution plan that's even better than the acceptable plan the old server was coming up with (which was only using Magento's default indexes).

Still, it all seems a bit hand-wavey, so I'll leave this open for now in case anybody has a better recommendation or explanation.


I can not navigate through the objects in sql server managemente studio - lock never goes away Whenever I try to navigate through the objects in sql server management studio object explorer I get the error message

Lock request time out period exceeded. (Microsoft SQL Server, error:
1222).

It is impossible to list columns in tables, show code of views and procedures, etc.
It's a SQL Server 2016.
How to look for the cause of this ?
There is a lot of ETL running on the server, can that be the cause ? How to pinpoint the cause ?

Someone's got an open transaction involving the system tables.
Look at sys.dm_tran_locks.
While SSMS is waiting you can examine sys.dm_os_waiting_tasks to see which session it's blocked by.


Composite index performance with varchar columns I have large fact tables with composite non-clustered indexes, something like this:

NONCLSUTERED INDEX (OrderDate,OrderType,ClientKey,ItemKey,CustomerKey)

The columns ending with "Key" are varchar(50) columns, but they store strings of 20-30 characters.

Do I need to worry in long term, that the varchar columns in the index are actually varies in length?
Will I get more page splits, slower performance when millions of rows merge into these tables?
https://stackoverflow.com/questions/59667/what-are-the-use-cases-for-selecting-char-over-varchar-in-sql

Does the Sql Server engine have harder times to figure out the plan using that index since the columns are varies in length?
I read that CHAR is better since its fixed size, and its easier for the db engine to calculate with, hence it performs better.
Is it that significant?

Does it worth to hash these "Key" columns individually with lets say SHA2_256 function, so they become fixed size BINARY(32)?

NONCLSUTERED INDEX (OrderDate,OrderType,HASHEDClientKey,HASHEDItemKey,HASHEDCustomerKey)

I did my test to answer at least the performance question, and doesn't see any performance benefit with hashkeys over varchar.
In fact its a big overhead to calculate the hashes and store the extra 32 bytes of each next to the original "Key" column.

What I cannot test is how these varchar indexes will behave when i have millions of rows merging into the tables each day?

Which one would you choose for both performance and maintenance point of view?

While some RDBMSs might be tuned to interact better with fixed-length strings (I seem to recall that maybe DB2/MVS did better with fixed-length strings, but I learned this in 1996 and it was mainframe DB2, so not sure if that even applied to DB2 on Unix or Windows), I would be shocked if using CHAR(50) did anything but decrease performance as compared to VARCHAR(50) given that most values are only using 20-30 bytes. Using CHAR(50) takes up more space, which reduces the number of rows that fit on the data page, which increases the number of data pages, which takes up more space in the buffer pool (i.e. RAM) when querying the data, plus takes longer to backup and restore. AND, if I am not mistaken, it will also increase the memory grants (i.e. RAM requested per query) as it will request 50 bytes per field instead of 25 (I believe it requests 50% the max size of variable length fields). For a more thorough look at the down-stream effects of using more space than you need, please see: Disk Is Cheap! ORLY?.

That being said, between VARCHAR(50) and CHAR(50), I would choose: neither ! Given the names of the columns, I am guessing that these are actually dimensions? Filtering on binary/numeric values is much faster than filtering on strings (there is one exception that I will mention in a moment). Depending on how many potential values there will be over time for clients, customers, and items, I would use either INT or, if truly needed, then BIGINT as the keys for the Client, Customer, and Item dimensions, and store those numeric key values here.

At bare minimum, I would change the collation on the VARCHAR columns to be Latin1_General_100_BIN2 since you clearly aren't needing case or accent insensitivity, else the idea of using a SHA-256 hash would not even be an option. That should at least be able to filter almost as fast as an INT or BIGINT (I say "almost" only due to, again, the effect of storing 20 - 30 byte values for 3 columns instead of 4 or 8 byte values for those same 3 columns, when accounting for millions/billions of rows).

I would strongly advise against using any type of hash value here. Not only would you be bloating the table (i.e. decreasing performance), but you would then lose any meaning of those values, and would hence either need to also store the non-hashed values for those 3 columns in the fact table (i.e. crazy) or require a lookup back to the dimension table (in which case you gained nothing over using INT or BIGINT at 4 or 8 bytes plus no calculation overhead!).


Error creating database in SQL Server Management Studio. (Could not load file or assembly 'Microsoft.SqlServer.Sqm) I'm trying to create a new database in SSMS, but I get this error:

  Could not load file or assembly 'Microsoft.SqlServer.Sqm, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified. (SqlMgmt)

Close Management Studio.

Find the file Microsoft.SqlServer.Sqm.dll located in one of the following folders:

  C:\Program Files\Microsoft SQL Server\110\Shared\
  C:\Program Files\Microsoft SQL Server\110\Tools\Binn\VSShell\Common7\IDE\
  C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\VSShell\Common7\IDE\

Wherever you find it, make sure it also exists in the other two locations.

I don't know that this will solve your issue, so please let me know - if not I will delete this. This was just too complex to add as a comment.


Creating a job scheduled based on a specific timezone I have an Oracle database hosted in Europe. I need to create a scheduled job in the database to send email. I need to send email daily at 17:30:00 in India standard time (IST).

I know how to send email and create jobs. But don't know how to create a job to execute on my local time (IST).

How can I do this?

The following creates a job to execute daily at 17.30 local time specified by country.

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
   job_name           =>  'update_sales',
   job_type           =>  'STORED_PROCEDURE',
   job_action         =>  'OPS.SALES_PKG.UPDATE_SALES_SUMMARY',
   start_date         =>  '07-AUG-13 17.30.00 PM India/Delhi',
   repeat_interval    =>  'FREQ=DAILY',
   end_date           =>  '07-AUG-14 17.30.00 PM India/Delhi',
   auto_drop          =>   FALSE,
   job_class          =>  'batch_update_jobs',
   comments           =>  'My new job');
END;
/


SQL Server: grant all 'public' users the permission to create tables I have a set of users who have the 'public' role.

According to the default settings, they can read, update, insert data inside existing tables.

How can I change the database settings to that they can as well create and ALTER tables of schema dbo?

If I click on "Alter role" for the database>decurity>Roles, there is a section 'extended properties" but I am not sure how to use this?

Thank you

Ensure you are in the correct database and then issue the following command:

GRANT CREATE TABLE TO public
GO

If you already have objects that require public to ALTER the table then you can add the following for each existing table:

GRANT ALTER ON OBJECT::dbo.your_table_name TO public
GO

Reference: GRANT (Transact-SQL)


Modeling a product database and dealing with inheritance So, to get better at designing databases, I'm attempting to model a database system after the AR15 Builder.

Here is what I have so far:

While it makes sense logically, I'm afraid there may be too much inheritance... Might there be a better way to handle this? Any tips appreciated; thanks in advance!

You might like to look here - they give a lot of sample data models.

There are two in particular which might be of use to you. Here is a BOM (Bill Of Materials) which looks as if it might be a good fit for your business (manufacturing machine guns). Take a look at the "Manufacturing" section (no. 13).

You appear to have a table for all your different components Barrel, Buttstock, Sight and Scope amongst others. I'm not so sure that they're all necessary? But, as Einstein said, things should be made as simple as required, but no simpler.

What's your RDBMS? It's always good on this site when asking a question to provide your database server.

Take the tour of the site, visit the help centre and check out the "Help us to help you" blog. p.s. welcome to the dba.stackexchange.


What does numeric operations mean in a sum in sql expression? SUM(rating IN (4.5, 5))/COUNT(*)*100 AS pct_5_star,
SUM(CASE WHEN rating IN (4.5, 5) THEN rating ELSE 0 END) AS in_5_star

I don't know why I am finding it difficult to understand what exactly is happening in the above statement and what exactly is /COUNT(*)*100 doing here.

COUNT(*)*100 in this scenario seems to be how the developer dynamically gets the percentage based on how many records are returned.

Maybe it's better read this way:

((
    SUM(rating IN (4.5,5)) --SUM of ratings of 4.5 and 5
    /  --divided by
    COUNT(*)  --The total number of records
  )
*100) --Multiplied by 100 to make it readable as a percentage
AS pct_5_star

So if the query returns 100 records, it divides the SUM by 100 then multiplies thatresult by 100 to get an easily readable number.

if the query returns 150 records, it divides the SUM by 150 then multiplies that result by 100 to get an easily readable number.


How to wait for PostgreSQL to be startable / restorable? I'm testing a PostgreSQL 8.2.1 to 9.2 upgrade on a virtual machine running a custom Linux distro. The upgrade procedure is as follows:

Start the pg service
Vacuum all DBs (not sure if this is needed)
Backup with pg_dumpall
Stop the pg service
Move away the directory where the data is stored (/var/pg; it's a simple, single-server setup)
Install PostgreSQL 9.2
initdb
Start the server
Restore the dumped data
reindexdb all DBs
Recreate the referential_constraints view
Vacuum all DBs (AFAIK required after this upgrade)

This procedure works fine on one host, backing up and restoring without a hitch. On another machine with a different database points 1 through 7 work fine, but the server won't start unless I add a sleep 1 after initdb, and even then the dumped data can't be restored because "the database system is starting up". What are the standard ways to deal with this, except for these terrible hacks:

sleeping for some generous amount of time before either operation,
looping until it works or until a generous timeout is reached, or
looping until it accepts a trivial query or a timeout is reached.

Edit: The "solution" didn't work after all. What does it take to make sure the database is ready to run a restore?

initdb doesn't return until it's finished, so there shouldn't be any pause needed between it and server startup.  There have been bugs in PostgreSQL where it completed without flushing everything to disk first though.  I don't know of any left right now, but the nature of bugs is that you don't always know about them.

If you use the pg_ctl command to start the database, use the "-w" parameters for that to wait until startup is finished before returning.  It doesn't do anything fancy--it just does the "is it ready yet?" loop for you.

Note that if you get a server crash with a lot of data that needs to be replayed before the server can start, the timeout set by "-t" on the pg_ctl waiting might be too low.

There is no reason to VACUUM the source databases before doing a pg_dump of them.  While it might speed the dump up a bit, the vacuum itself will take longer than that improvement.


How to set a correct value for wiredTiger concurrentTransactions I have a single instance in server with 7 Processor and 15 Gb.
In value default for wiredTigerConcurrentReadTransactions and wiredTigerConcurrentWriteTransactions is 128.

But is value default, how to calculate the ideal value for ConcurrentTransactions ?

[Reference]

https://www.datadoghq.com/blog/monitoring-mongodb-performance-metrics-wiredtiger/

https://docs.mongodb.com/manual/reference/parameters/#param.wiredTigerConcurrentReadTransactions

The read/write ticket values are set to 128 to prevent overwhelming WiredTiger with concurrent requests. They are chosen to cater for the typical server hardware. It's generally not recommended to change these values.

If you see stalling and observed that the read/write tickets to be maxed out, it's extremely likely that this is just a symptom. The cause of the stalling is typically somewhere else.

A couple of examples:

A slow disk can cause write requests to pile up since they can't be serviced fast enough. If the read/write ticket is increased because it appears that the storage engine is hitting the ticket number limit consistently, what actually will happen is that you're putting even more pressure on the disk, which will result in further delay. The correct remediation in this case is to provision a faster disk.
You have a deployment in AWS. For most of the day, read & write progress smoothly. There is an increase in write load, but the system can handle it just fine. Suddenly things start to stall, and you see read/write tickets exhausted for apparently no reason. In most cases like this, the root cause is because the disk has ran through its allotted burst IOPS and is being throttled by AWS. As you can imagine, increasing read/write ticket numbers in this case is not helpful and can make things worse.

In conclusion, you don't really need to tweak read/write tickets. If you find it constantly exhausted, check for possible causes of work piling up and waiting for the storage engine.


SQL Server : trigger FOR DELETE I want to create a trigger, which does not delete a customer if his name is Peter. How can I do that?

CREATE TRIGGER trgCUSTOMER ON customer
FOR DELETE
AS
BEGIN
    IF (SELECT name FROM deleted) not like 'Peter'
    BEGIN
        delete from customer where name = (select name from deleted);
    END
END;

You have two options:

1) Rollback the DELETE in case you're deleting the row "Peter". You do this with an AFTER trigger. Be aware that it will rollback the whole transaction in case yuo're deleting multiple rows among which at least one is "Peter".

CREATE TRIGGER TR_CUSTOMER_DEL ON customer
FOR DELETE
AS
BEGIN

    SET NOCOUNT ON;

    IF EXISTS (
        SELECT *
        FROM deleted
        WHERE name = 'Peter'
    )
    BEGIN
        ROLLBACK;
        THROW 50001, 'Cannot delete Peter', 1;
    END
END;

2) You override the way SQL Server does DELETEs on the table, using an INSTEAD OF trigger. In this case, you silently ignore DELETEs for the row "Peter".

CREATE TRIGGER TR_CUSTOMER_IO_DEL ON customer
INSTEAD OF DELETE
AS
BEGIN

    SET NOCOUNT ON;

    DELETE c
    FROM customer AS c
    INNER JOIN deleted AS d
        ON c.customer_id = d.customer_id
    WHERE d.name <> 'Peter';

END;

Which one is best for you highly depends on your business logic.


How to "properly" back up a MySQL/MariaDB database? I have a small (<10GB) database backing an Intranet WSGI website. In the Apache logs I frequently see timeout errors whose timestamp coincides with the daily backup cron job. I'm running incremental backup on /var/lib/mysql using duplicity. My questions:

1) Is this a coincidence, or is it possible to have interference here?

2) Is there a better way to do a database backup?

Host is RHEL7

You should use mysqldump, for example if you want to backup all databases:

# mysqldump --all-databases > dump.sql

You might need to add options for username, password, etc. Read the online documentation on mysqldump (MySQL Reference Manual) for other options.

If you just copy the database files while the server is running, it is very likely that you will be unable to restore databases using those files.


CDC vs replication vs anything else In my product we need to create a solution for the reporting team to extract and process the data. We need to send them incremental data changes from our DB. I am evaluating various solutions. Looked into CDC and Replication and not sure which one to use or to build a custom one.

While CDC is easy to configure and need lesser infrastructure than replication, but the change tables are in the same DB the performance may slowdown depending on how frequently the reporting team queries the db.

Replication seems more salable but it needs lot of infrastructure. Also looks like there has to be separate instance of the DB in the same server or use another server.

Will a custom solution help here? Is there a way to create a copy of the database and read the logs from source DB and push incremental data to a destination DB through a job.

CDC isn't really designed as a way to keep a reporting database up to date, it's more for tracking changes.

What exactly is the end goal here, are you just going to be taking some select tables\data off to another server to report against or are you looking for a way to take an entire database?

You've got a couple of options, if you're just interested in a handful of tables then you probably want to look at replication.  Replication doesn't really need much infrastructure, just another SQL Server to replicate to.

If you want to have a complete copy of the database for reporting purposes, the easiest option that you've got is log shipping although be aware that the reporting database will be unavailable while the log shipping process plays in the logs.

Another option that you've got is to use an availability group with a secondary node acting as a read only reporting copy.


Create an Azure Synapse temp table from a SELECT with ORDER BY I have the following statement that works when I omit the ORDER BY clause, or if I run it as a select and omit the create table part, but I need both to ensure my generated key is sequenced correctly
Any ideas?

Msg 104381, Level 16, State 1, Line 18
The ORDER BY clause is invalid in views, CREATE TABLE AS SELECT, INSERT SELECT, SELECT INTO, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

CREATE TABLE #demo
WITH (DISTRIBUTION = ROUND_ROBIN)
AS
SELECT
       ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS ID,
       schemas.name as [schema],
       tables.name as [table],
       columns.column_id as [ordinal],
       columns.name as [column],
       types.name as [type]
FROM SYS.COLUMNS
  inner join sys.types
          on  types.system_type_id = columns.system_type_id
  inner join sys.tables
          on  tables.object_id = columns.object_id
  inner join sys.schemas
          on  schemas.schema_id = tables.schema_id
order by schemas.name,
       tables.name,
       columns.column_id

I would imagine this would work for you:
CREATE TABLE #demo
WITH (DISTRIBUTION = ROUND_ROBIN)
AS
SELECT
    ROW_NUMBER() OVER (
        ORDER BY S.[name], T.[name], C.column_id) AS ID,
    S.[name] as [schema],
    T.[name] as [table],
    C.[column_id] as [ordinal],
    C.[name] as [column],
    TY.[name] as [type]
FROM sys.columns AS C
JOIN sys.types AS TY
    ON TY.system_type_id = C.system_type_id
JOIN sys.tables AS T
    ON T.[object_id] = C.[object_id]
JOIN sys.schemas AS S
    ON S.[schema_id] = T.[schema_id];

The key difference is moving the ordering to the ROW_NUMBER window function, to determine the order in which numbers are assigned.


How to switch master server and warm standby (with streaming replication) in PostgreSQL? I would like to know what is the way to switch the role of 2 servers when using warm standby?

I have 2 servers : S_1 and S_2, and at a time T, S_1 is the master server and S_2 is a warm standby with streaming replication.

At a later moment, I would like to switch S_2 become the master server and S_1 the warm standby.

What do I have to do to switch roles ?

Keep in mind that currently you cannot demote a master to a standby.  Doing so will corrupt your database!

The basic documentation for doing this is at: http://www.postgresql.org/docs/9.1/static/warm-standby-failover.html

However the basic thing is you can't switch these other than:

Turn off primary
Promote secondary
Rebuild former primary as new secondary off the new primary.


ASP.NET & SQL Server 2012 timeouts once a month We have an intranet site which works fine, except one day a month.

There are slow queries, unoptimized tables, tables with ~8 million records, etc. but daily usage is fine, although some functionality could be faster. The database server has 180GB RAM, 16 cores; it's a very fast server. The website is on another very decent server, with a very fast connection between.

Employees need to create a lot of invoices on the last day of each month and that's when it goes sour. On the last day of each month, people get a lot of time outs, a lot of pages which only run SELECT FROM TABLE will fail to load because of timeouts.

While people are getting the timeouts, I turned on Activity Monitor, and the CPU is at 20%, there are 300 calls per second, which is really not much at all for that server. On daily usage it's 100-200 calls per second and it works fine. I ran sp_who2, sp_who3, they don't report any locks. I have also enabled deadlock report, to report anything for more than 20 secs and it doesn't report anything.

Some queries have 7000 different plans, although that's bad it doesn't explain timeouts. We have a lot of SELECT queries without a NOLOCK but that doesn't explain why pages with SELECT only time out.

We get very low physical reads, since RAM is 180GB, most of the data is in RAM, so slow disk is not the problem. Network is not the problem, since two servers (database server and web site server) have a very fast connection between them. We backup the database every 15 mins so every 15 mins I see a high I/O activity, but that happens on other days as well without a problem.

The only oddity I noticed was that when I ran sp_who there were 300 Waiting_Command state connections, that's much lower on other days.

Do you reckon there's anything else I could check? The problem is we only have a few hours once a month when the time outs occur so we have very limited time to check what's going on, at other times it's OK. I wrote a JMeter script to reproduce the problem but it's not the same as 300 people creating invoices at the same time. We're thinking of contracting a DBA but I'm not sure even if a DBA could do anything with what happens once a month for a few hours.

Any ideas?

Update:

I checked sys.dm_os_wait_stats every few minutes but I couldn't see anything out of the ordinary. But as @AaronBertrand suggested I'll look into that.
We use datareader and datasets but we don't do any heavy processing with the reader results.
It happens on the last working day of each month so it's a different day every month.
I'm new here and I was told this has been happening every month for the past 10 years at least. 10 years ago we probably had fewer employees but also slower servers.
There's nothing else that runs on the last day of each month.
We have 15 mins backups, report generation that runs every 15, every 30 minutes etc. but they don't cause a problem on other days.
Both datareader and datasets are used, but datareader iterations are used to get total of all items, etc., just quick loops, nothing like reading a file for each record.
There doesn't seem to be anything else running on either servers.
This happens on last working day of each month so it's a different day each month.

It might be related with leaking connections exhausting the connection pool.
If the connections are not released properly they will remain active and will not be returned to the connection pool until the next Garbage Collection executes. If a lot of connections are opened in a short time the connection pool might fill up before the GC runs, and the following connection requests will end up waiting for a free connection in the pool, eventually timing out.

https://msdn.microsoft.com/en-us/library/8xx3tyca(v=vs.110).aspx


What SSMS and/or Visual Studio tools sync SQL Server environments? I need to sync table structures, stored procedures, triggers, data, etc. between environments. There are dev, QA, and production environments. I am in a Microsoft-centric environment.

What tools within SSMS and/or Visual Studio can sync SQL Server machines/databases? Thanks.

You can purchase SQL Compare from Red-Gate.


SSMS command to disconnect after query has finished In SQL Server Management Studio, is there any way (for example a SQLCMD command or something) to get the SQL script window to automatically disconnect as soon as the query has finished executing?

Easy one ...
Think about why do you want to do it ? Any specific reasons ?
From BOL :
To change the options for the current queries, click Query Options on the Query menu or the shortcut menu of the SQL Server Query window. Under Execution, click Advanced.

Disconnect after the query executes
When this check box is selected, the connection to SQL Server is terminated after the query completes. This check box is cleared by default.


Non-yielding issue on SQL Server We currently have an issue with some queries/jobs causing non-yielding at a specific point in time.
There no are mini dumps which are generated when this happens.
Is there a way to know which query was causing the non-yielding on a specific scheduler in the past?

Look in your Wait Stats (preferably Query Store Wait Stats, Session Wait Stats, and otherwise System Wait Stats) for preemptive waits.
Sessions enter preemptive waits when calling into code that doesn't participate with SQL Server's cooperative scheduler, which is a common cause of a session refusing to yield a scheduler (CPU Core).


Problem of SUM of the SUM When I execute my query I have always the same problem :

 year_created|month_created|loc_id_1|loc_id_2|loc_id_3|Total
------------------------------------------------------------
  2016       |    7        |  0     | 0.40   |  0      0.40
  2016       |    7        |  0.50  | 0      |  0      0.50
  2016       |    7        |  0     | 0      |  0.30   0.30

How I can combine to have a single line :

 year_created|month_created|loc_id_1|loc_id_2|loc_id_3|
-------------------------------------------------------
  2016       |    7        |  0.50  | 0.40   |  0.30

This is my query :

SELECT
   MONTH(created_at) as month_created,
   YEAR(created_at) as year_created,
   1 - SUM(price)/SUM(order_total) as Total,
   CASE loc_id WHEN 1 THEN  1 - SUM(price)/SUM(order_total) ELSE 0 END ) AS 'loc_id_1',
   CASE loc_id WHEN 2 THEN  1 - SUM(price)/SUM(order_total) ELSE 0 END ) AS 'loc_id_2',
   CASE loc_id WHEN 3 THEN  1 - SUM(price)/SUM(order_total) ELSE 0 END ) AS 'loc_id_3'
   FROM data_price
   WHERE YEAR(created_at) = YEAR(NOW())
  GROUP BY year_created, month_created
  ORDER BY year_created asc, month_created desc

Problem is that the SUM(order) can be 0 in the CASE statement. Create a sub-query that delivers the price and order totals per loc_id and then use this to do the calculation if the sum of order is not 0:

SUM(CASE loc_id WHEN 1 THEN price ELSE 0 END) AS 'loc_id_1_price',
SUM(CASE loc_id WHEN 1 THEN order ELSE 0 END) AS 'loc_id_1_order'

Then the SQL look something like (check if this is possible in vertica):

SELECT year_created, month_created, 1 - total
       1 - CASE loc_id_1_order WHEN 0 THEN 0 ELSE loc_id_1_price/loc_id_1_order END as loc_id_1,
       1 - CASE loc_id_2_order WHEN 0 THEN 0 ELSE loc_id_2_price/loc_id_2_order END as loc_id_2,
       1 - CASE loc_id_3_order WHEN 0 THEN 0 ELSE loc_id_3_price/loc_id_3_order END as loc_id_3
FROM
  (SELECT
     MONTH(created_at) as month_created,
     YEAR(created_at) as year_created,
     SUM(price)/SUM(order_total) as Total,
     SUM(CASE loc_id WHEN 1 THEN price ELSE 0 END) AS 'loc_id_1_price',
     SUM(CASE loc_id WHEN 1 THEN order ELSE 0 END) AS 'loc_id_1_order',
     SUM(CASE loc_id WHEN 2 THEN price ELSE 0 END) AS 'loc_id_2_price',
     SUM(CASE loc_id WHEN 2 THEN order ELSE 0 END) AS 'loc_id_2_order',
     SUM(CASE loc_id WHEN 3 THEN price ELSE 0 END) AS 'loc_id_3_price',
     SUM(CASE loc_id WHEN 3 THEN order ELSE 0 END) AS 'loc_id_3_order'
   FROM data_price
   WHERE YEAR(created_at) = YEAR(NOW())
   GROUP BY year_created, month_created)
ORDER BY year_created asc, month_created desc


SELECT COUNT(*) to html table This may be a very simple but it's still not clear to me. I'm writing a SQL Agent job to generate output in html format.

I need to get results of a query SELECT COUNT(*) FROM TABLE_NAME. I'm able to get output generated with actual data in html but I just need count from table.

What is the easiest way to do it?

Here is part of my query:

SET @tableHTML = N'
' +
        N'' +
        N'
COLUMN_NAME	' +
        N'
' +
        ISNULL(CAST ( ( SELECT  td = [COLUMN_NAME],''
                        FROM dbo..TABLE_NAME
                    FOR XML PATH('tr'), TYPE
        ) AS NVARCHAR(MAX) ),'') +
        N'' +
        N'
'

Can someone please help me to get output as count instead of individual row in output.

I have couple staging tables for a process and need to get notification for those tables with data in it. First table I only need row count. There is only one column in it. And second table has 4 Columns and I got expected output for it in sql job. Actually I just need to add one line with total rows from table to output in mail. I hope this will help to understand the question better.

It doesn't need to be in HTML but my second table needs to be included in output makes it easy to generate proper table output. I'm not much familiar with sql stuff yet. Still learning.

I think it is a VERY bad plan to be generating HTML via SQL.  There are many many ways this can go wrong, without even mentioning the maintenance nightmare that this will inevitably cause.

All that being said your solution might be as simple as adding the following:

+ CAST((SELECT COUNT(*) FROM dbo.TABLE_NAME) AS VARCHAR(20)) +

The reason why I think you are having problems is because SQL Server requires you to explicitly convert your integers to strings.


Perfmon and SQL Server Clustering I'll be going onsite to run some performance tests for a client.  They have a Windows Failover Cluster, which has a clustered instance of SQL Server.  Let's assume for this post that my client's Windows Failover Cluster has two nodes, as in the image below.

I'll be running a server-side trace (via a tsql script generated from SQL Profiler)--I'm quite comfortable doing this.  After I connect to EkTechready14.Redmond.com\MySqlInstance via SSMS and start the trace, I am of the opinion that it doesn't matter which node is active when the trace is running.  Do you agree?
I'll also be running Perfmon to gather some garden variety stats.  Maybe for Perfmon, it does matter which node is active.  Should I run Perfmon from the client access point (EKTechready14.Redmond.com)?  Or from the active node (either MSDEPLOY60-01 or MSDEPLOY60-02)?

Use a server-side trace, not Profiler. Both have an impact on throughput, Profiler much more so. ClearTrace is a great tool for offline analysis of the trace files.

To answer question 1), you connect to the instance not the node. Question 2), you obviously need to gather data from the node the instance is currently running on.


Bit column vs multiple join tables I asked a question earlier about how I could define a many to many relationship between users and FOSes (fields of study). My original question was how could we know whether the user was the instructor or the user was the student.

My original idea was two junction tables, one called user_instructor_fos and one called user_student_fos.
Another solution was to use a bit column inside a single join table called is_instructor. If the user was the instructor this value would be set to one.

I can see how both solutions would work I was just wondering what the pros and cons of each solution are and if there is a best practice.

I'd have role table that contains "student", "instructor", etc. Then join table would look like user_role(user_id,role_id, fos_id, date_from, date_thru) . Date_from and date_thru may or may not make sense for your task....


Postgres planer JOIN removal I have two tables, one contains a lot of rarely update data, and one contains a little of frequently update data. Every record in second table has corresponding record in the first, like following:

data_static
----------
id                  | integer                     | not null
....
Indexes:
    "data_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "data_dynamic" CONSTRAINT "idcheck" FOREIGN KEY (id) REFERENCES data_static(id) ON DELETE CASCADE

Table two:

data_dynamic
------------+-----------------------------+-----------
id         | integer                     | not null
...
Indexes:
    "data_dynamic_new_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "idcheck" FOREIGN KEY (id) REFERENCES data_static(id) ON DELETE CASCADE

When I execute count with join in this case (right join) planer works perfectly:

explain select count(d.id) from data_static s right join data_dynamic d on s.id = d.id;
                                                     QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=239.10..239.11 rows=1 width=4) (actual time=6.261..6.261 rows=1 loops=1)
   ->  Seq Scan on data_dynamic d  (cost=0.00..207.48 rows=12648 width=4) (actual time=0.013..2.437 rows=10128 loops=1)
 Total runtime: 6.311 ms
 (3 rows)

But when I execute inner join it scans whole bit table and this take a lot more time:

explain select count(d.id) from data_static s inner join data_dynamic d on s.id = d.id;

                                       QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Aggregate  (cost=35530.42..35530.43 rows=1 width=4) (actual time=1629.596..1629.596 rows=1 loops=1)
   ->  Hash Join  (cost=365.58..35498.80 rows=12648 width=4) (actual time=26.418..1625.962 rows=10128 loops=1)
         Hash Cond: (s.id = d.id)
         ->  Seq Scan on data_static s  (cost=0.00..32478.87 rows=252787 width=4) (actual time=14.185..1435.169 rows=252787 loops=1)
         ->  Hash  (cost=207.48..207.48 rows=12648 width=4) (actual time=7.939..7.939 rows=10128 loops=1)
               Buckets: 2048  Batches: 1  Memory Usage: 357kB
               ->  Seq Scan on data_dynamic d  (cost=0.00..207.48 rows=12648 width=4) (actual time=0.012..3.759 rows=10128 loops=1)
Total runtime: 1629.956 ms
(8 rows)

It's not very realistic example but I wondering why planer works in this way and how it can be fixed?

If you're testing with 50,000 rows in data_dynamic, I'd expect your inner join to return 50,000 rows. It seems to be returning about 10,000. There's a foreign key constraint—this seems unusual to me.

I loaded a bunch of random data, and ran this.

analyze data_dynamic;
analyze data_static;
explain analyze
select count(d.id) from data_static s inner join data_dynamic d on s.id = d.id;

And here's what my box returned. (PostgreSQL 9.0.2)

Aggregate  (cost=14941.95..14941.96 rows=1 width=4) (actual time=477.633..477.633 rows=1 loops=1)
  ->  Merge Join  (cost=8409.72..14816.94 rows=50000 width=4) (actual time=237.807..456.682 rows=50000 loops=1)
        Merge Cond: (s.id = d.id)
        ->  Index Scan using data_static_pkey on data_static s  (cost=0.00..91190.58 rows=3000000 width=4) (actual time=0.016..195.279 rows=350001 loops=1)
        ->  Index Scan using data_dynamic_pkey on data_dynamic d  (cost=0.00..2342.23 rows=50000 width=4) (actual time=0.010..60.933 rows=50000 loops=1)
Total runtime: 477.706 ms

So it looks like the query planner is capable of coming up with a good plan for these tables and this quantity of data.

Does this mean your problem is either with hardware or with PostgreSQL configuration? I'm not sure it's that simple.

Later . . .

I was right; it's both simpler and not that simple.

Based on these snippets from PostgreSQL mailing lists, I'm going to say that the query planner simply doesn't yet support removing inner joins.

From the pgsql-performance mailing list, 02-Mar-2011

  The planner doesn't currently make any deductions whatsoever from the
  presence of a foreign key constraint; and even if it did, I'm not sure
  that this would help it decide that a join order constraint could
  safely be dropped.

And from pgsql-hackers, 12-Dec-2010

  I'm not going to argue that careful analysis isn't needed before doing
  something like this - and, in particular, if we ever get inner-join
  removal, which I'm still hoping to do at some point, . . .


I want a select to give me id's that are not in a consecutively order I have an Oracle db with a table like this:

+----+-------+
| ID | INDEX |
+----+-------+
| 1  |   0   |
| 1  |   1   |
| 2  |   4   |
| 2  |   5   |
| 2  |   6   |
| 3  |   0   |
| 3  |   1   |
| 3  |   5   |
| 3  |   6   |
| 4  |   0   |
| 4  |   1   |
| 4  |   2   |
+----+-------+

I'd want a select to give me all the ID's with INDEX that does not start with 0 or are not in the correct order. From my example I'd want to get 2,3.
THANKS!

create table index_test(id number, idx number);

   insert into index_test (id, idx) values (1,0);
   insert into index_test (id, idx) values (1,1);
   insert into index_test (id, idx) values (1,2);
   insert into index_test (id, idx) values (1,3);
   insert into index_test (id, idx) values (1,4);
   insert into index_test (id, idx) values (2,0);
   insert into index_test (id, idx) values (2,1);
   insert into index_test (id, idx) values (2,2);
   insert into index_test (id, idx) values (2,4);
   insert into index_test (id, idx) values (2,5);
   insert into index_test (id, idx) values (3,1);
   insert into index_test (id, idx) values (3,2);
   insert into index_test (id, idx) values (3,3);
   insert into index_test (id, idx) values (3,4);
   insert into index_test (id, idx) values (3,5);
   insert into index_test (id, idx) values (3,6);

select id from index_test group by id having max(idx)+1!=count(*) or min(idx)!=0;


How can I speed up this slow simple query? SELECT * FROM `game`
WHERE (hometeam=29 OR awayteam=29)
  AND `sportid`=1
  AND`date` BETWEEN '2012-07-01' AND '2013-06-30'
  AND deleted=0

This simple query takes 0.5-1.1 seconds (without caching). The table holes 1.6M rows, so I think it should be much faster (<0.1 second). In the explain below, it shows it's searching through half of the table. You can see I've tried various index variations with no luck, it always wants to use the sportid index.

id | select_type | table | type | possible_keys                                                                            | key     | key_len | ref   | rows   | Extra
1  | SIMPLE      | game  | ref  | date,hometeam,awayteam,sportid,deleted,sportid_2,sportid_3,sportid_4,sportid_5,sportid_6 | sportid | 1       | const | 800368 | Using where

SHOW CREATE TABLE game

CREATE TABLE `game` (
 `gameid` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `sportid` tinyint(1) unsigned NOT NULL DEFAULT '1',
 `hometeam` smallint(5) unsigned NOT NULL,
 `awayteam` smallint(5) unsigned NOT NULL,
 `homescore` tinyint(4) unsigned NOT NULL,
 `awayscore` tinyint(4) unsigned NOT NULL,
 `stadiumid` smallint(5) unsigned NOT NULL,
 `neutral` tinyint(1) unsigned NOT NULL DEFAULT '0',
 `deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
 `date` date NOT NULL DEFAULT '0000-00-00',
 `time` time NOT NULL DEFAULT '00:00:00',
 `confid` smallint(5) unsigned DEFAULT NULL,
 `srs-predict` tinyint(3) unsigned NOT NULL,
 `srs-teamid` smallint(5) unsigned NOT NULL,
 `crs-predict` tinyint(3) unsigned NOT NULL,
 `crs-teamid` smallint(5) unsigned NOT NULL,
 `winner` smallint(5) unsigned DEFAULT NULL,
 `forfeit` tinyint(1) DEFAULT NULL,
 `playoff` tinyint(1) unsigned DEFAULT NULL,
 `H1` tinyint(3) unsigned DEFAULT NULL,
 `H2` tinyint(3) unsigned DEFAULT NULL,
 `H3` tinyint(3) unsigned DEFAULT NULL,
 `H4` tinyint(3) unsigned DEFAULT NULL,
 `H5` tinyint(3) unsigned DEFAULT NULL,
 `H6` tinyint(3) unsigned DEFAULT NULL,
 `H7` tinyint(3) unsigned DEFAULT NULL,
 `H8` tinyint(3) unsigned DEFAULT NULL,
 `A1` tinyint(3) unsigned DEFAULT NULL,
 `A2` tinyint(3) unsigned DEFAULT NULL,
 `A3` tinyint(3) unsigned DEFAULT NULL,
 `A4` tinyint(3) unsigned DEFAULT NULL,
 `A5` tinyint(3) unsigned DEFAULT NULL,
 `A6` tinyint(3) unsigned DEFAULT NULL,
 `A7` tinyint(3) unsigned DEFAULT NULL,
 `A8` tinyint(3) unsigned DEFAULT NULL,
 `reportedby` int(10) unsigned DEFAULT NULL,
 `lastscandate` date DEFAULT NULL,
 PRIMARY KEY (`gameid`),
 KEY `winner` (`winner`),
 KEY `date` (`date`),
 KEY `hometeam` (`hometeam`),
 KEY `awayteam` (`awayteam`),
 KEY `sportid` (`sportid`),
 KEY `lastscandate` (`lastscandate`),
 KEY `deleted` (`deleted`),
 KEY `sportid_2` (`sportid`,`awayteam`,`date`),
 KEY `sportid_3` (`sportid`,`hometeam`,`date`),
 KEY `sportid_4` (`sportid`,`awayteam`,`date`,`deleted`),
 KEY `sportid_5` (`sportid`,`hometeam`,`date`,`deleted`),
 KEY `sportid_6` (`sportid`,`hometeam`,`awayteam`,`date`,`deleted`)
) ENGINE=InnoDB AUTO_INCREMENT=1969140 DEFAULT CHARSET=utf8

Try adding two indexes:

ALTER TABLE game
  ADD INDEX deleted_sportid_hometeam_date_IX          -- choose names
    (deleted, sportid, hometeam, date),
  ADD INDEX deleted_sportid_awayteam_date_IX          -- for the indexes
    (deleted, sportid, awayteam, date)  ;

and then running this version:

SELECT * FROM game
WHERE hometeam = 29
  AND sportid = 1
  AND date BETWEEN '2012-07-01' AND '2013-06-30'
  AND deleted = 0

UNION ALL

SELECT * FROM game
WHERE awayteam = 29
  AND sportid = 1
  AND date BETWEEN '2012-07-01' AND '2013-06-30'
  AND deleted = 0 ;

You can still try your original query after having added the 2 indexes and the efficiency will probably be improved (but not much, depends on the selectivity of the deleted - sportid combination.) But I don't think the optimizer is smart enough to understand that this (rewriting) is equivalent to that (original query) and that it could use both indexes, one for the 1st and the other for the 2nd part, so you will only have a small efficiency gain.

So, the "why is it faster this way" has mainly to do with the OR condition and with the limited ability of the optimizer to rewrite queries in equivalent forms. This rewriting for example would never be produced anyway by an automated optimizer (because I assumed that hometeam and awayteam are never the same, which the optimizer cannot guess.) If home team and away team could be the same, then we'd have to use UNION (not UNION ALL).

B-tree indexes are super-good for conditions with AND, only. When there are ORs or otherwise complicated conditions, they are not so good. Occasionally - like in this case - there is a way to rewrite a query so it has only ANDs. The last part of the execution (the UNION ALL) can be executed in MySQL by running the two subqueries one after the other and showing all results from both so there is no calculations or delay there.


Query to see Index Fragmentation on all databases without using SP or temp tables I am trying to compile a index fragmentation report on all databases, in a multi-server environment. The query will be executed via an openquery command, so I would like to make it as simple as possible, without using stored procedures, temporary tables, etc.

I am aware the follow can bring back all indexes on all databases

select * from sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, 'SAMPLED')

but as soon as we join it to sys.indexes, sys.objects, we restrict out system wide indexes and rather focus on indexes in the currently executing database.

Is there a way around this, so I can get the following output for all databases:

Database Name, Index Name, Index Type, Number of Pages, Percentage of Fragmentation.

It's not possible to achieve without using temp table as the DMVs/DMFs that we are using are database scoped and requirement is server scoped, and i don't see any difficulties or complication using temp table as follows:

Declare @Tbl table (ServerName varchar(128), DBName varchar(128), SchemaName varchar(128), TableName varchar (100), IndexName varchar (100), FragPercent float, IndexType tinyint, IsPrimaryKey bit);

Insert into @Tbl
exec SP_MSforeachdb @command1 =
            'use [?];
                select  @@Servername,
                        DB_NAME(),
                        sc.name as SchemaName,
                        object_name (s.object_id) as TableName,
                        I.name,
                        s.avg_fragmentation_in_percent,
                        I.type,
                        I.is_primary_key
                from sys.dm_db_index_physical_stats (DB_ID (), NULL, NULL, NULL, ''LIMITED'') as S
                    join sys.indexes as I on s.object_id = I.object_id and s.index_id = I.index_id
                    join sys.objects as O on s.object_id = O.object_id
                    join sys.schemas as sc on O.schema_id = sc.schema_id
                where o.type = ''U'' and avg_fragmentation_in_percent > 20 and (I.name is not null)
                ORDER BY  avg_fragmentation_in_percent DESC'
select * from @Tbl
go


Database restore time increased after we changed from SQL Server 2014 to SQL Server 2016 I have a 2 TB database (200 GB backup) that I am restoring every night on a secondary server of a AlwaysOn group, to erase confidential information and then backup the clean database and send it to test environment. During the restore there is no other statement running on the SQL Server, except the redo from the primary (which mostly concern reindex).
Before the SQL Server were in 2014 edition, the rate was about 180 MB/s to 200 MB/s for the restore. I have upgraded in-place to a 2016 edition (CU12) a few weeks ago, and now the rate to restore the database is below 90 MB/s.
I have limited time to do the full process of restoring and cleaning the database before teams get to work on it on the morning, and since the migration the processing time no longer makes it possible to respect the schedules.
I have checked disk graph and configuration with system teams, and we didn't found anything conclusive. Database instant file initialization is already enable on the server, and disk block are in 64K. I am restoring from one disk E:\ (where backup are stored) to another disk F:\ (where I work on the cleanup).
Has this problem ever happened to someone after a migration (rate when restoring slower than before)? I haven't found anything like that in official documentation or forums.
EDIT: Resource Governor is disabled. Max memory is 480 GB. Nothing in the error log. No significant queue on the disk during the restore.

For those who may have the same problem, we noticed that when we did the CHECKDB we had an error with PFS pages (https://www.sqlskills.com/blogs/paul/pfs-corruption-after-upgrading-from-sql-server-2014) and thus the solution was to correct these error with a DBCC CHECKDB(database_name, REPAIR_ALLOW_DATA_LOSS).
We also move the disk from 7K to 10K to upgrade performances.


How to diagnose a hanging MySQL UPDATE? I have a simple UPDATE statement running on a MySQL 5.6.34 database that's seemingly hanging. The statement only updates a single row with a timestamp and looks like:

UPDATE mytable SET last_updated='2017-05-31 02:13:43' WHERE id=123;

Yet after an hour, this was still running.

Running SHOW FULL PROCESSLIST; doesn't show any other processes accessing the table, so I don't think it's a deadlock. I tried killing and re-running the process, but it still hangs.

What other reasons would cause a simple UPDATE statement like this to hang? How do I diagnose this?

Just set slow_query_log = 1 (if >=5.6.1) or
log_slow_queries = 1(if <5.6.1) and
slow_query_log_file = /path/to/logfile and long_query_time = 0 in my.cnf, then restart mysql and have a look at the logfile. I won't keep it on on production envs, such a logging is a performance killer and may result in bad things if you are on heavy load. It may also get quickly very big as long_query_time 0 results in logging everything, so "/path/to" should have plenty of free space.


SQL Server was unable to communicate with the LaunchPad service I have run the below code:

execute sp_configure 'external scripts enabled';

and it is giving me the output as:

name                    minimum maximum config_value    run_value
external scripts enabled    0    1       1              1

I have also started the launchpad services, still when I am running the below code:

exec sp_execute_external_script @language =N'R',
@script=N'OutputDataSet<-InputDataSet',
@input_data_1 =N'select 1+1 as SUMOutput'
with result sets (([SUM Output] int not null));
go

It is throwing the error:

  Msg 39011, Level 16, State 1, Line 7 SQL Server was unable to
  communicate with the LaunchPad service. Please verify the
  configuration of the service.   Msg 11536, Level 16, State 1, Line 7
  EXECUTE statement failed because its WITH RESULT SETS clause specified
  1 result set(s), but the statement only sent 0 result set(s) at run
  time.

I have SQL Server 2016 editon and the SQL Server Management Studio is 2017.
i have the following snippet of my services.

I had the same issue and I solved it by installing the Service Pack on top of the media installed R.

I think it is a related to install an old version of LaunchPad on an already updated SQL Server.

As per this Q&A

SQL Server LaunchPad "The service did not respond in a timely fashion" + No Log in ExtensibilityLog

My timeline.

My sql install was a semi-updated SP1.
My media was a stock SP1.
I installed R, wouldn't work (Restarted, same)
LaunchPad wouldn't start (Restarted, same)
Made sure the SQL user account was part of the R group
LauchPad would start but R wouldn't work. (Restarted, same)
Updated to SP2 (Restarted)
R worked.
Updated to SP2 CU4 (Restarted)
R worked.

My testing script

    EXEC sp_execute_external_script
                      @language =N'R',
                      @script=N'OutputDataSet<-InputDataSet',
                      @input_data_1 =N'SELECT 1 AS hello'
                      WITH RESULT SETS (([Hello World] INT));
                        GO


Postgres table size is not growing after adding row I couldn't see any growth in table size when I added a row in the relevant table. I see the same memory space even after adding 5 rows. Could anyone please help me and explain what I'm doing wrong?

PostgreSQL tables are stored in 8kB blocks. A new row will be added to the first best block that has enough free space. Only if there is no block with free space, the table will be extended.
Keep inserting more rows, and eventually the table will grow.


Best index for this scenario There is a table in our database

tblCustomText(
  ParentID int NOT NULL,
  CustomFieldID int NOT NULL,
  Value char(50) NOT NULL,
  Log_CreateDate datetime NOT NULL
)

(ParentID+CustomFieldID) is unique.

Read operations are like: SELECT Value WHERE ParenID=@p1 AND CustomFieldID = @p2 (sometimes instead of WHERE, JOIN is used)

Write operations: 85% is INSERT, 13% is UPDATE, 2% DELETE. (By ParentID+CustomFieldID)

Now, I'd like to know what indexes should be defined for this table? (It contains ~6M rows)

Seems like the following would be sufficient:

CREATE TABLE dbo.tblCustomText
(
  ParentID int NOT NULL,
  CustomFieldID int NOT NULL,
  Value char(50) NOT NULL,
  Log_CreateDate datetime NOT NULL,
  PRIMARY KEY CLUSTERED (ParentID, CustomFieldID)
);

You may also find later that you are querying by date, so you may want a non-clustered index on the Log_CreatedDate column.


Return results that contain or do not contain punctuation Is there a way within MySQL to return results using a LIKE statement that contain or do not contain punctuation characters such as apostrophes and hyphens?

For example if I have one of the following queries:

SELECT * FROM some_table WHERE some_value LIKE "%this-thing%";

or

SELECT * FROM some_table WHERE some_value LIKE "%person's thing%"

Is there a way in which I could obtain the same results as the queries below but with a single LIKE clause?

SELECT * FROM some_table WHERE (some_value LIKE "%this-thing%") OR (some_value LIKE "%this thing%");

or

SELECT * FROM some_table WHERE (some_value LIKE "%person's thing%") OR (some_value LIKE "%persons thing%");

Basically a function that when searching would return a result if either version of the text exists ("person's thing" or "persons thing"), something like:

EDIT

Is there a function within mysql which would return only alpha numeric characters within a column? For example if the column value where "something's & other-stuff" then it would be returned as "somethingsotherstuff"? So a query would look something like:

SELECT * FROM table WHERE magic_function(column_name) LIKE '%words%'

EDIT x2

For the time being I have resolved this by chaining together REPLACE() and replacing all special characters in the column value with "" before doing the comparision. Value being compared has also been stripped of all special characters.

I had a similar situation a while ago. It was a project in Ireland for a membership system for a cultural organisation and one of the requirements was that the system handle Irish names.

Irish names can be very complex with O, O', Ó, Ó', Mac, Mc', Mc (all with or without spaces after) and in some cases names like Ua hÚigínn (not a typo - the small h followed by upper case Ú is correct in Irish). As you can see, there are also accented characters (a, e, i, o and u can take acute accents). The problem is that many people use many different variants of these names with/without apostrophes, accents and alternative spellings.

You can look on these like your differing spellings in your some_value field!

What we did was to create a "shadow" (some_value_search) column and search on that! On INSERT or UPDATE, we eliminated apostrophes, accents, hyphens and spaces (in the names) using a TRIGGER. Obviously, your phrases need spaces, but you could turn person's thing into PERSONS THING and thing-thing into THINGTHING.

Then you display your data using the original some_value field, but always search on some_value_search.

This was in the days before computed/calculated/generated fields. But MySQL (as of 5.7.6) has them and you should be able to eliminate a lot/most/all of any trigger code using them.

col_name data_type [GENERATED ALWAYS] AS (expression)
  [VIRTUAL | STORED] [NOT NULL | NULL]
  [UNIQUE [KEY]] [[PRIMARY] KEY]
  [COMMENT 'string']

Your (expression) above could be (REPLACE(REPLACE(field, '-', ''), ''', '') to eliminate spaces and hyphens. However, thanks to MySQL's lack of a [TRANSLATE()][3] function, the code for making a string all-capitals is horrible (26 nested REPLACEs!). If you need to do anything complex, there are always TRIGGERs, but the above solution is fairly standard nowadays!


Calculate the growth of deltas in SQL Server Is there a way to calculate the growth per year of deltas in database?

I would like to know if my current server is enough or should I switch to bigger server and required to check the growth over the years, is it doable?

Thanks.

There are plenty of posts on the Internet with various solutions for this.  A quick search of 'Sql Server Track Database Growth' found this one at the very top.  I haven't vetted it, so due diligence applies.  To summarize:

First create a table to keep the size information of each database with a date on which the information is collected:

CREATE TABLE DatabaseFileSize
(
[database_id] INT,
[file_id] INT,
[file_type_desc] NVARCHAR(120),
[name] NVARCHAR(128),
[physical_name] NVARCHAR(520),
[state_desc] NVARCHAR(120),
[size] INT,
[max_size] INT,
[growth] INT,
[is_sparse] BIT,
[is_percent_growth] BIT,
[collect_date] DATETIME
)
GO

Create a stored procedure to populate this table with the current database size information:

CREATE PROC usp_util_CollectDatabaseSize
AS
BEGIN
SET NOCOUNT ON
INSERT INTO DatabaseFileSize
(
[database_id],
[file_id],
[file_type_desc],
[name],
[physical_name],
[state_desc],
[size],
[max_size],
[growth],
[is_sparse],
[is_percent_growth],
[collect_date]
)
SELECT
[database_id],
[file_id],
[type_desc],
[name],
[physical_name],
[state_desc],
[size],
[max_size],
[growth],
[is_sparse],
[is_percent_growth],
GETDATE()
FROM sys.master_files
SET NOCOUNT OFF
END
GO

Now we need to populate the table over a period of time, so we need to create a SQL Agent job to execute this stored procedure and schedule this job to run everyday.

USE [msdb]
GO
/****** Object:  Job [Collect_Database_Size_Info]    Script Date: 02/13/2013 23:47:43 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 02/13/2013 23:47:43 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Collect_Database_Size_Info',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'Domain\User', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [DBSize]    Script Date: 02/13/2013 23:47:44 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'DBSize',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC usp_util_CollectDatabaseSize',
@database_name=N'AuditDB',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'DailyDBSize',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20130213,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959,
@schedule_uid=N'445fb175-517b-4221-b6b8-6c6877dc7c24'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

Now we have the information over the period of time and we can run our below query to make a report out of this data.

--DB Size Growth Trend
SELECT
DB_NAME(database_id) AS DatabaseName,
collect_date AS CollectionDate,
((SUM(size))*8)/1024 AS DBSizeInMB
FROM DatabaseFileSize
GROUP BY database_id,collect_date
ORDER BY DatabaseName,collect_date

--DB File Size Growth Trend
SELECT DB_NAME(database_id) AS DatabaseName,
(CASE file_type_desc
WHEN 'ROWS' THEN 'Data'
WHEN 'LOG' THEN 'Log'
END) AS FileType,
physical_name AS PhysicalPath,
collect_date AS CollectionDate,
((SUM(size))*8)/1024 AS FileSizeInMB
FROM DatabaseFileSize
GROUP BY database_id,physical_name,file_type_desc,collect_date
ORDER BY DatabaseName,collect_date

If you want to look back in time for database growth, you can use the information in Erin Stellato's blog post Trending Database Growth From Backups

This technique relies on information in msdb.dbo.backupset which is pruned by various maintenance plans and possibly other processes, so you may only be able to go back a few months. Here's the main query from that post:

SELECT
[database_name] AS "Database",
DATEPART(month,[backup_start_date]) AS "Month",
AVG([backup_size]/1024/1024) AS "Backup Size MB",
AVG([compressed_backup_size]/1024/1024) AS "Compressed Backup Size MB",
AVG([backup_size]/[compressed_backup_size]) AS "Compression Ratio"
FROM msdb.dbo.backupset
WHERE [database_name] = N'AdventureWorks'
AND [type] = 'D'
GROUP BY [database_name],DATEPART(mm,[backup_start_date]);


Delete rows from a large table with joins and aggregating (resulting in 1 % less rows in the big table) I got a bit stuck on performance, so I thought about asking a bit of help. I have a working query, but unfortunatelly it feels rather slow and I know it's not as performant as it could be made (easily?). The situation is to delete rows from a table which contain around one million rows, by doing a join to another table likewise having around a million rows worth of data (these tables have about the same amount of rows). The result after removing rows from SomeList is that there's about 1 % less rows

The table structure is as follows

CREATE TABLE SomeList
(
    Id INT IDENTITY(1,1) PRIMARY KEY,
    TimeData DateTime NOT NULL
);

CREATE TABLE SomeListAuxData
(
    Id INT IDENTITY(1,1) PRIMARY KEY,
    CountData INT NOT NULL,
    SomeListId INT FOREIGN KEY REFERENCES SomeList(Id) ON DELETE CASCADE
);

And the query to delete as follows

DELETE
FROM SomeList
WHERE TimeData < @someTime AND Id = @someListId AND
(
    SELECT COUNT(*)
    FROM SomeListAuxData
    WHERE SomeList.Id = SomeListAuxData.SomeListId
) > 0
AND
(
    SELECT MAX(CountData)
    FROM SomeListAuxData
    WHERE SomeList.Id = SomeListAuxData.SomeListId
) < @someValue;

Specifically this seem wasteful since I'm doing two subqueries, but I'm not sure how to go about the COUNT(*) and MAX(CountData) parts if I'd try joining, for instance.

This query is made in a program code where the someListIds are looped with same the @someValue (e.g. 2000) and @someTime (e.g. '2013-11-07 09:00:00.000') dates, so that too is rather slow. The query could be done for all the SomeList rows at once.

<edit: Also, I just learned that the code calling does have a list of pairs of type (@someListId, @someDate), which means the date isn't a constant. The input comes from the user interfaces and typically there may be even hundreds of such pairs.

Also, maybe scheduling a job at night may do the job. Though I'll need to checks Craig's advice which seem to be valid too. :)

<edit: I clarified what I meant by the "1 %" in my question. The point to make was that I don't think the data warrants collecting the non-removable rows to a #temptable, truncating SomeList and then moving the data from #temptable" back to SomeList. Craig's good point answered (partially) a different question, nevertheless it may be applicable. Sorry for my sloppy writing.

<edit 2013-11-08: The join conditions corrected as suggeted in comments.

Since you are already referencing the SomeList table from the SomeListAuxData table, why not add "ON DELETE CASCADE" to the foreign constraint definition on SomeListAuxData?

CREATE TABLE SomeListAuxData
(
    Id INT IDENTITY(1,1) PRIMARY KEY,
    CountData INT NOT NULL,
    SomeListId INT FOREIGN KEY REFERENCES SomeList(Id) ON DELETE CASCADE
);

This way, if you delete record with an id of 1 in the SomeList table, any record in SomeListAuxData referencing record 1 will be deleted as well.

As to deleting a % of rows at a time, I like to use a loop. This one deletes 10,000 records at a time, until there are none left.

delete top (10000) from SomeList WHERE TimeData < @someTime AND Id = @someListId
  WHILE @@ROWCOUNT > 0
  BEGIN
        waitfor delay '00:00:05' -- add in a delay optional

        delete top (10000) from SomeList WHERE TimeData < @someTime AND Id = @someListId

  END

Update 14:09

Using the TOP operator, you can also specify a percent of rows.

DELETE TOP(1) PERCENT
FROM SomeList
WHERE TimeData < @someTime AND Id = @someListId AND
(
    SELECT COUNT(*)
    FROM SomeListAuxData
    WHERE SomeListAuxData.Id = SomeListAuxData.SomeListId
) > 0
AND
(
    SELECT MAX(CountData)
    FROM SomeListAuxData
    WHERE SomeListAuxData.Id = SomeListAuxData.SomeListId
) < @someValue;


10 TB database backup / restore Question We have a very big production database which is 10 TB. We are considering migrating it to a cloud service, but I have to understand the risks and estimate the duration of the whole process. I backup the database once per month using Native SQL Server 2014 SP2 backup solution. The duration is almost 720 minutes - 12 h. The compression ratio is 5, meaning the backup size is almost 2 TB. We can't afford differential backups because accumulated, these take as much time and space as the Full, so we do T-log backups (~200 Mb each) instead each 10 minutes ~ 144 files per day. The BIG question is having all these stats, does anyone have any idea how long it would take to restore a 2 TB backup containing 10 TB data? Would it be much longer than the Full backup duration?

This is simple and quick to test.

Take that 2TB backup file, copy it to Azure using AzCopy.  Provision a SQL Server using the "Free License: SQL Server 2017 Developer on Windows Server 2016" image configured with 12-15 TB of SSD storage.  Remote desktop to the SQL VM and restore the backup directly from Blob Storage, or copy it to a local disk and then restore it.

Just remember to shut down the VM when you're not testing, and destroy it and the disks when you're done to minimize the charges.