Next Steps to Diagnose ODBC SQLAllocHandle on SQL_HANDLE_DBC failed

In this case I linked StorageOnlineOp in ODBC log to process name StorageOnlineOpns.exe. When issue occurred I used Task Manager to right click process and "Create Dump File"

An analysis shows the process was a 32-bit process, nearing it's 2GB address space limit, and was unable to allocate memory for the database connection.

Opening the dmp file in WinDbg, used the following commands to identify problem:

    0:011> lmvm StorageOnlineOpns
Browse full module list
start    end        module name
00170000 002b9000   StorageOnlineOpns   (export symbols)       StorageOnlineOpns.exe
    Loaded symbol image file: StorageOnlineOpns.exe
    Image path: D:\Program Files (x86)\Enterprise Vault\StorageOnlineOpns.exe
    Image name: StorageOnlineOpns.exe
    Browse all global symbols  functions  data
    Timestamp:        Wed May  1 11:23:18 2013 (51806E86)
    CheckSum:         0014CE8C
    ImageSize:        00149000
    File version:     10.0.3.1188
    Product version:  10.0.3.0
    File flags:       0 (Mask 3F)
    File OS:          4 Unknown Win32
    File type:        2.0 Dll
    File date:        00000000.00000000
    Translations:     0409.04b0
    CompanyName:      Symantec Corporation
    ProductName:      Enterprise Vault
    InternalName:     STORAGEONLINEOPNS
    OriginalFilename: STORAGEONLINEOPNS.DLL
    ProductVersion:   10, 0, 3, 0
    FileVersion:      10.0.3.1188
    FileDescription:  StorageOnlineOpns Module
    LegalCopyright:   Copyright (c) 2013 Symantec Corporation. All rights reserved. Use of this product is subject to license terms.

This process is 32-bit so is limited to 2GB address space, this is close to being exhausted:

0:011> !heap -s
Mapping file section regions...
Mapping module regions...
Mapping PEB regions...
Mapping TEB and stack regions...
Mapping heap regions...
Mapping page heap regions...
Mapping other regions...
Mapping stack trace database regions...
Mapping activation context regions...

--- Usage Summary ---------------- RgnCount ----------- Total Size -------- %ofBusy %ofTotal
Heap                                    669          6a04c000 (   1.657 GB)  86.08%   82.83%
<unknown>                               397           85b5000 ( 133.707 MB)   6.79%    6.53%
Image                                   712           58ea000 (  88.914 MB)   4.51%    4.34%
Free                                    406           4d72000 (  77.445 MB)            3.78%
Stack                                   153           3300000 (  51.000 MB)   2.59%    2.49%
Other                                    46             5f000 ( 380.000 kB)   0.02%    0.02%
TEB                                      51             33000 ( 204.000 kB)   0.01%    0.01%
PEB                                       1              1000 (   4.000 kB)   0.00%    0.00%

--- Type Summary (for busy) ------ RgnCount ----------- Total Size -------- %ofBusy %ofTotal
MEM_PRIVATE                            1201          73d79000 (   1.810 GB)  94.06%   90.50%
MEM_IMAGE                               741           5b38000 (  91.219 MB)   4.63%    4.45%
MEM_MAPPED                               87           19cd000 (  25.801 MB)   1.31%    1.26%

--- State Summary ---------------- RgnCount ----------- Total Size -------- %ofBusy %ofTotal
MEM_COMMIT                             1605          6f65a000 (   1.741 GB)  90.45%   87.03%
MEM_RESERVE                             424           bc24000 ( 188.141 MB)   9.55%    9.19%
MEM_FREE                                406           4d72000 (  77.445 MB)            3.78%

--- Protect Summary (for commit) - RgnCount ----------- Total Size -------- %ofBusy %ofTotal
PAGE_READWRITE                          902          68c29000 (   1.637 GB)  85.06%   81.85%
PAGE_EXECUTE_READ                       135           3e04000 (  62.016 MB)   3.15%    3.03%
PAGE_READONLY                           382           2960000 (  41.375 MB)   2.10%    2.02%
PAGE_WRITECOPY                           83            1fc000 (   1.984 MB)   0.10%    0.10%
PAGE_READWRITE|PAGE_GUARD               103             d1000 ( 836.000 kB)   0.04%    0.04%

--- Largest Region by Usage ----------- Base Address -------- Region Size ----------
Heap                                         dd10000            fd0000 (  15.813 MB)
<unknown>                                   7f0e0000            f00000 (  15.000 MB)
Image                                       6a611000            e70000 (  14.438 MB)
Free                                        72f4a000            1c6000 (   1.773 MB)
Stack                                        1980000             fd000 (1012.000 kB)
Other                                       7efb0000             23000 ( 140.000 kB)
TEB                                         7ef19000              1000 (   4.000 kB)
PEB                                         7efde000              1000 (   4.000 kB)

The database connection code is failing due to inability to allocate more memory:

0:011> k
 # ChildEBP RetAddr
00 031ac570 73b217cd ntdll!NtWaitForSingleObject+0x15
01 031ac5b0 73b27696 mswsock!SockWaitForSingleObject+0x3a
02 031ac62c 764b689f mswsock!WSPRecv+0x2af
03 031ac674 739616c7 ws2_32!recv+0x87
04 031ac6c4 5510b4a2 dbnetlib!ConnectionRead+0x429
05 031ac700 5514ceb2 sqloledb!CDataSource::ConnectionRead+0x82
06 031ac754 550f9056 sqloledb!CDBConnection::GetBytesFromWire+0x228
07 031ac7a4 551888e0 sqloledb!CDBConnection::ProcessTDSStream+0x1d6
08 031ac858 55121a4f sqloledb!CStmt::SQLExecRPC+0x61b
09 031ac894 550f88cb sqloledb!CCommand::ExecuteHelper+0x232
0a 031ac920 550ff5c6 sqloledb!CCommand::Execute+0x127c
0b 031ac960 57e5a335 sqloledb!CImpICommandText::Execute+0x16f
0c 031ac9a0 57e52c8b msado15!CConnection::Execute+0x9c
0d 031acbd8 57e5573b msado15!_ExecuteAsync+0x1e1
0e 031acbf4 57e556f7 msado15!ExecuteAsync+0x6f
0f 031acd24 57e54ecf msado15!CQuery::Execute+0x10f5
10 031acd88 57eabd39 msado15!CCommand::_Execute+0x192
11 031acfe0 57ead171 msado15!CCommand::ExecuteWithModeFlag+0x8da
12 031ad00c 5acb4406 msado15!CCommand::Execute+0x5e
WARNING: Stack unwind information not available. Following frames may be wrong.
13 031ad28c 5aca7a1f AdoInterface!CADODataAccess::ExecuteSQLCommand+0x266
14 031ad2b4 5853ff29 AdoInterface!CADODataAccess::ExecuteStoredProcedure+0x1f
15 00000000 00000000 EVFingerprintCatalogueAccessor!CRegistryHelper::IsOpen+0x15bb9
0:011> !gle
LastErrorValue: (Win32) 0 (0) - The operation completed successfully.
LastStatusValue: (NTSTATUS) 0xc0000017 - {Not Enough Quota}  Not enough virtual memory or paging file quota is available to complete the specified operation.

In addition there is was a lock blocking 4 threads. The blocking function was a heap function. This is commonly found in heap corruption issues.

0:004> k
 # ChildEBP RetAddr
00 026eed5c 77a1ebae ntdll!NtWaitForSingleObject+0x15
01 026eedc0 77a1ea92 ntdll!RtlpWaitOnCriticalSection+0x13e
02 026eede8 77a1ece9 ntdll!RtlEnterCriticalSection+0x150
03 026eeed4 77a02bd5 ntdll!RtlpFreeHeap+0x96
04 026eeef4 75b06e8a ntdll!RtlFreeHeap+0x142
05 026eef08 7671d9f6 ole32!CRetailMalloc_Free+0x1c [d:\w7rtm\com\ole32\com\class\memapi.cxx @ 687]
06 026eef1c 7671daad oleaut32!_SafeArrayFreeData+0x1f
07 026eef2c 766dfdf0 oleaut32!_SafeArrayReleaseData+0x37
08 026eef4c 766dfd5c oleaut32!_SafeArrayDestroyData+0x81
09 026eef68 766f0fc3 oleaut32!_SafeArrayDestroy+0xa0
0a 026eef8c 766f133c oleaut32!LPSAFEARRAY_UserFree+0x10e
0b 026eefa0 759f7c1e oleaut32!VARIANT_UserFree+0xae
0c 026eefd8 759f4256 rpcrt4!NdrUserMarshalFree+0x37
0d 026eeff8 75a7083e rpcrt4!NdrPointerFree+0xa8
0e 026ef01c 75a7072a rpcrt4!NdrpFreeParams+0x145
0f 026ef030 75a706e0 rpcrt4!NdrStubCall2+0x4eb
10 026ef428 75bee7e6 rpcrt4!NdrStubCall2+0x42a
11 026ef470 75bee876 ole32!CStdStubBuffer_Invoke+0xb6 [d:\w7rtm\com\rpc\ndrole\stub.cxx @ 1590]
12 026ef4b8 75beedd0 ole32!SyncStubInvoke+0x3c [d:\w7rtm\com\ole32\com\dcomrem\channelb.cxx @ 1187]
13 026ef504 75b08a6b ole32!StubInvoke+0xb9 [d:\w7rtm\com\ole32\com\dcomrem\channelb.cxx @ 1396]
14 026ef5e0 75b08960 ole32!CCtxComChnl::ContextInvoke+0xfa [d:\w7rtm\com\ole32\com\dcomrem\ctxchnl.cxx @ 1262]
15 026ef5fc 75b09532 ole32!MTAInvoke+0x1a [d:\w7rtm\com\ole32\com\dcomrem\callctrl.cxx @ 2105]
16 026ef628 75beeccd ole32!STAInvoke+0x46 [d:\w7rtm\com\ole32\com\dcomrem\callctrl.cxx @ 1924]
17 026ef65c 75beeb41 ole32!AppInvoke+0xab [d:\w7rtm\com\ole32\com\dcomrem\channelb.cxx @ 1086]
18 026ef740 75bef1fd ole32!ComInvokeWithLockAndIPID+0x372 [d:\w7rtm\com\ole32\com\dcomrem\channelb.cxx @ 1724]
19 026ef768 75b0938f ole32!ComInvoke+0xc5 [d:\w7rtm\com\ole32\com\dcomrem\channelb.cxx @ 1469]
1a 026ef77c 75b0934e ole32!ThreadDispatch+0x23 [d:\w7rtm\com\ole32\com\dcomrem\chancont.cxx @ 298]
1b 026ef7c0 760f62fa ole32!ThreadWndProc+0x161 [d:\w7rtm\com\ole32\com\dcomrem\chancont.cxx @ 654]
1c 026ef7ec 760f6d3a user32!InternalCallWinProc+0x23
1d 026ef864 760f77c4 user32!UserCallWinProcCheckWow+0x109
1e 026ef8c4 760f788a user32!DispatchMessageWorker+0x3b5
1f 026ef8d4 001cc7d4 user32!DispatchMessageW+0xf
WARNING: Stack unwind information not available. Following frames may be wrong.
20 026ef938 001cc8a9 StorageOnlineOpns+0x5c7d4
21 026ef974 752634c7 StorageOnlineOpns+0x5c8a9
22 026ef980 7650336a msvcr90!_endthreadex+0xd8
23 026ef98c 77a09902 kernel32!BaseThreadInitThunk+0xe
24 026ef9cc 77a098d5 ntdll!__RtlUserThreadStart+0x70
25 026ef9e4 00000000 ntdll!_RtlUserThreadStart+0x1b


You can grant db_datareader to any user (map to a login) that you want. This will address the "all actual databases".
For the future database, as all new databases are created based on the [model] database, if you create a user in the [model] DB that has the "db_Datareader" role, then when a new DB will be created, it will contains that user as well.
If your new database come from backup restore, then this won't work and you may want to look at something like a trigger.


Database replication from local Postgres to live

What you seem to be asking for is multi-master replication, and there is no built-in or open source solution for that. There are a couple of closed source solutions.
You should consider changing your requirements. Multi-master replication is conceptually difficult, and you usually end up with fewer headaches if you choose a different architecture.


SSPI handshake failed with error code 0x8009030c, state 14

WORKAROUND/SOLUTION
Loopback check can be removed by adding a registry entry as follows:

Edit the registry using regedit. (Start –> Run > Regedit )
Navigate to: HKLM\System\CurrentControlSet\Control\LSA
Add a DWORD value called “DisableLoopbackCheck”
Set this value to 1


PostgreSQL to HDFS to Spark, or PostgreSQL directly to Spark SQL?

This might be too broad of a question but as with any system, the less steps and dependencies you have, the easier it is to support it as long as it meets current and future (or future enough) business goals.

In your use case scenario it doesn't look like you have any use for HDFS and SQOOP.  In a lot of environments they might.  For example they might want to use HDFS as a file store and keep the records handy even after they were processed with Spark.  It could be that they are customer records for example, and are aggregated, this way you would keep the originals handy so you could give each individual customer their own records if they need it.

By going directly into Spark and based on your job, you will mutate the data and you might not be able to get the original back.  By keeping a copy in HDFS you could also always recreate the end result as you have a copy of what made it.

If you don't need the data to persists in the form it was when you pulled it, or don't need to be able to recreate the output by going through the same process you might not need to.  Another item might be that you want a live dev/qa environment.  By putting it in HDFS first, you can follow the same process for all environments using the most recent data.  You might not be able to recreate an issue or scenario if the data get's processed but not maintained/stored.

Those are some of the MANY possible reasons.  It really depends.


Swapping is a 3-step process (for this and many other situations).  Go via a temp id.  Something like:

Update `box_types` `bt` SET `bt`.`box_number` = "-1" where `bt`.`box_type_id` = "3" ;
Update `box_types` `bt` SET `bt`.`box_number` = "5" where `bt`.`box_type_id` = "1" ;
Update `box_types` `bt` SET `bt`.`box_number` = "7" where `bt`.`box_number` = "-1" ;

(If box_number is UNSIGNED, then pick some value that will work:  maybe 0?  Maybe 9999999?)


How do I stop/disable PITR and safely clean WAL segments?

To stop the archiving in 8.2, you set archive_command to empty ('') and restart the server.  The data directory should clean itself up.  The archives on your secondary drive you can just throw away.


Multiple vs Single COUNT on same col + Multiple vs Single col index

This is straightforward enough and more efficient:

SELECT  school,
        CASE
           WHEN students > 1000 THEN 'SMALL'
           WHEN students > 10000 THEN 'MEDIUM'
           WHEN students > 100000 THEN 'LARGE'
        END as size
    FROM ( SELECT school,
                  COUNT(*) as students
              FROM tbl_students
              GROUP BY school
         ) AS cts

It would need INDEX(school) or any index starting with school.


No policy were made on the container since I understood (Is my fault here?) that SAS token already had full access.

Backup to URL requires a SAS Token generated from a Container Access Policy with fixed start and Expiration Dates.  An Storage Account-scoped SAS token will not work.


Why am I getting a snapshot isolation issue on INSERT?

Add an OPTION (LOOP JOIN) hint to the INSERT statement.

Or use a plan guide (or query store) to force the nested loops semi join plan shape.

You might find that OPTION (FAST 1) works as well.

The point is to avoid a merge semi join, where many (potentially all) of the referenced tables' rows are touched by the current transaction. If any parent row with a change (including creation) is encountered, an update conflict error is raised.


Why do PostgreSQL examples say `+ integer '7'`?

Interesting question, and I don't know the answer.  My best guess is that all the other examples use the same way of casting, and someone thought it is better having it consistent.  If you are pissed off irked by this, you can scroll to the bottom of the page and send a complaint using the form linked there.


Actually, the link you're referring to does not say that floating point arithmetic calculations are always deterministic. In fact, in one of the answers it's mentioned that addition is not associative (meaning (a + b) + c does not necessarily equal a + (b + c)), which is also said in this answer.

If stream aggregation happens to process rows of each group in different order -- which SQL Server is usually free to do; if there is no ORDER BY in the appropriate clause, then the optimizer will choose whatever scan or seek or other query operator will be fastest, regardless of what order that performs the additions in -- then this could explain the behaviour you observe.

Addition is always deterministic: you put in the same two floats, you get the same float out.  But adding floats together in a different order can give a different result.


Optimistic locking with postgresql blocking due to locks

Q: However, in the test I'm doing, the transactions are affecting others, in the sense that one transaction might block in a lock.

Concurrent transactions not affecting each other at the serializable isolation level does not mean that any one transaction does not lock any other. Locks are inevitable in databases as soon as concurrent transactions work on the same data.

Not affecting each other means that the result of any particular transaction does not differ from what it would be if other transactions were not working concurrently with the same data.

The time it takes to complete a transaction, or whether it had to wait for locks to be released do not count as results. Results are rows and values that are read or written or returned to the caller.

Q: But, what I would expect is that temp2.py would insert the row, commit and then, when temp1.py tries to commit, it would get an error.

The error concerns a violation of a unique constraint. Constraint checks can be deferred or immediate, independently of the isolation level,
and they're immediate by default.

If the unique column  was declared as id integer PRIMARY KEY DEFERRABLE INITIALLY DEFERRED, the INSERT in transaction #2 would not hang, but transaction #2 would hang at COMMIT time until transaction #1 commits or aborts.

Q: Is it possible to do what I want in postgresql?

If you want the second transaction (the one that inserts in second) to be guaranteed to succeed because it's faster to commit than the other one that inserted first, I don't think it's possible. From the point of view of the serializable isolation logic, it doesn't really matter which transaction fails. Any transaction may fail and the caller must always be prepared to react appropriately according to SQLSTATE (the error code).


How to compare and MERGE two SQL Server environments (not just databases)

Thanks to @HandyD, dbaTools does this and more! Cheers


commit or rollback on a statement based replication, effect on auto increment id

I think you will find an extra "statement" in the binlog that assigns the auto_inc value for the subsequent statement.  This guarantees that the Slave has the same ids for SBR.

For RBR (Row-Based-Replication), there is no issue, since the id is part of the INSERT.

In addition to the possibility of "burning" ("losing") ids, the ids can be replicated out of order.  This because ids are grabbed as needed, but replication is in COMMIT-order.  (This applies to SBR and RBR.)

For multi-master, auto_increment_increment > 1 -- another reason for gaps in the ids.


Oracle dump to csv and keep NULL as empty string

This is how I normally make .csv files.
You should be able to open the file with Excel.

SET TERMOUT OFF
SET FEEDBACK OFF
SET ECHO OFF
SET PAGESIZE 0
SET LINESIZE 2048
COLUMN text format A2048
SET VERIFY OFF
SET TRIMSPOOL ON
SET TAB OFF
SET UNDERLINE OFF
SET TERMOUT ON

SPOOL dump_audit_table.csv

SELECT '"'||timestamp||'","'||PROXY_SESSIONID||'","'||sessionid||'"' text
FROM dba_audit_trail where rownum < 10;

SPOOL OFF
QUIT


Why is this seemingly simple insert trigger function causing deadlocks?

Under heavy concurrent write access, the defense against deadlocks is to process rows in consistent order in all writing queries.

You may have sorted rows properly in all your writing queries to the table tweet- did you? (I know about that table from your related question.)

And you are obviously sorting rows to be "upserted" in url_starting in the trigger function.

That should do it for transactions inserting a single row in tweet with multiple URLs to be upserted in url_starting.

But while inserting multiple rows to tweet, each with an arbitrary array of URLs, rows to be upserted in url_starting are still in inconsistent order for the transaction. URLs are only sorted per row in tweet, not for the whole transaction (or even command). You would need to unnest all URLs of the same INSERT, sort them consistently, and then upsert url_starting. This cannot be achieved with a trigger FOR EACH ROW. I don't think you can solve this at all with your current approach while inserting multiple rows with arbitrary arrays of URLs. Concurrent write access to both tables is inherently conflicting.

Single-row inserts to tweet should be fine (each in its own transaction) - though possibly substantially more expensive. Maybe you can get rid of the trigger completely and reorganize the workflow (with data-modifying CTEs): write a sorted list of URLs to url_starting, then write to tweet ...

Arrays are often problematic in a relational table design. Full normalization might be another approach - replacing the array column with a related table. Not sure if that's the way ...

Or you keep your transactions as short and fast as possible, making deadlocks the rare exception, and prepare your application to retry in case of an error.


MySQL: Process was running fast on DigitalOcean after moving it to my local machine it runs slower

Check the configs on the two machines (my.cnf or wherever)
100% CPU almost always means a missing index and/or a poorly formulated query.
Turn on the slowlog if not already on.  (And have long_query_time=1)
DO probably had SSD drives; you have HDD.  But other clues indicate that I/O is not the problem.
innodb_buffer_pool_size should be about 26G on your 32GB machine; did you set that?
Multiple crawlers implies lots of INSERTs?  Do you batch the inserts or do it one row at a time?
See this for how to provide me data to investigate the slowlog and the tunables further.


What's the term that describes our data processes

For advertising purposes, you could use the term "Business data enhancement engineering"? :-) Personally, I would call what you do "[complex] [business] data processing".

  Thanks. Any suggestion on what terms I should be googling for if I
  want to find new tools for our processes?

Just various combinations of what you already do - etl/data warehousing/complex data processing... without knowing a bit more, I can't really be that helpful. Unless, that is, you give the tools that you're using.

However, tool recommendations are off-topic here (unfortunately in my view, but there you have it). Maybe if you have some more specific question(s) that we could help with? You could also possibly try software recommendations?

Oh, and I would (slightly) revise my comment description of what you do - call it "complex business enhancement data processing" or "business complementation data analysis and processing" (cringe...) :-).

Just a note - if you're dealing with more technical people, steer clear of the "marketing speak" - otherwise, go for it! :-)

  We use hand coded SQL, Python and DSL scripts currently, so no
  specific tools so far. We are evaluating whether to improve our DSL
  tool chain, or to introduce some commercial UI tools.

I would also examine the Open Source arena if I were you - there are some pretty awesome tools/products out there! Best of luck!


You should look at the Execution Plan and see where most of the cost is for the query. Without the results of the Execution Plan it is difficult to offer advice.

I suspect the query optimizer is using the best process possible for the query as it is structured. You might try breaking the query into smaller parts, like query the email field to get the results into a temp table first, then run your main query using the temp table of emails.

The might not make the query run faster, but this might expose information in the Execution Plan that will help you troubleshoot.

Another option is to use include to add columns to your non-clustered index to return all the values in the index scan to maybe speed up the process.


Suppose your table is called ingredients. Try the following:

Step 01) Create an empty delete keys table called ingredients_delete_keys

CREATE TABLE ingredients_delete_keys
SELECT fk,recipe,pkey FROM ingredients WHERE 1=2;

Step 02) Create PRIMARY KEY on ingredients_delete_keys

ALTER TABLE ingredients_delete_keys ADD PRIMARY KEY (fk,recipe,pkey);

Step 03) Index the ingredients table with fk,recipe,pkey

ALTER TABLE ingredients ADD INDEX fk_recipe_pkey_ndx (fk,recipe,pkey);

Step 04) Populate the ingredients_delete_keys table

INSERT INTO ingredients_delete_keys
SELECT fk,recipe,MIN(pkey)
FROM ingredients GROUP BY fk,recipe;

Step 05) Perform a DELETE JOIN on ingredients table using keys that don't match

DELETE B.*
FROM ingredients_delete_keys A
LEFT JOIN ingredients B
USING (fk,recipe,pkey)
WHERE B.pkey IS NULL;

Step 06) Drop the delete keys

DROP TABLE ingredients_delete_keys;

Step 07) Get rid of the fk_recipe_pkey_ndx index

ALTER TABLE ingredients DROP INDEX fk_recipe_pkey_ndx;

OK Here are all the lines in one block...

CREATE TABLE ingredients_delete_keys
SELECT fk,recipe,pkey FROM ingredients WHERE 1=2;
ALTER TABLE ingredients_delete_keys ADD PRIMARY KEY (fk,recipe,pkey);
ALTER TABLE ingredients ADD INDEX fk_recipe_pkey_ndx (fk,recipe,pkey);
INSERT INTO ingredients_delete_keys
SELECT fk,recipe,MIN(pkey)
FROM ingredients GROUP BY fk,recipe;
DELETE B.*
FROM ingredients_delete_keys A
LEFT JOIN ingredients B
USING (fk,recipe,pkey)
WHERE B.pkey IS NULL;
DROP TABLE ingredients_delete_keys;
ALTER TABLE ingredients DROP INDEX fk_recipe_pkey_ndx;

Give it a Try !!!

CAVEAT

Notice that using MIN function helps keep the first pkey entered for fk. If you switch it to MAX function instead, the last pkey entered for fk is kept.


ORA-00904 invalid identifer error when using alias

Aliases cannot be used directly. You have to abstract them out using a nested subquery or derived table.

with t as (
    select
    (select SUM(pest.AREA_HA)
       from WHSE_FOREST_VEGETATION.PEST_INFESTATION_POLY pest
      where ORG_UNIT_NO IN (1904, 1830,1831, 1902))
         as Ha_IN_RKB,
    (select SUM(pest.AREA_HA)
       from WHSE_FOREST_VEGETATION.PEST_INFESTATION_POLY pest)
         as Ha_total
    from dual)
select t.ha_in_rkb, t.ha_total, ROUND(t.Ha_IN_RKB/t.Ha_total,3) from t;


I'm afraid that information is not available. The transaction log file doesn't store information to that level of detail for most statements. You can get the date and time for connection attempts and checkpoints, but not for DDL statements.

Disclaimer: I work for SAP in SQL Anywhere engineering.


I'll take a crack at this.  Note, that I've just listed fields that are necessary.  If you have additional data you want to store for in those tables, feel free to add them (so long as they are normalized).

While you did state that you would use a different database for each region, I designed this so that they could all work in one database.  Right now you may have only 5 regions, but this offers the flexibility to easily add more:

table: region
regionID        varchar(2)
regionName      varchar(50)

Defines data for each realm.  I went with a surrogate key (realmID) because it's possible that realmName might not be unique.  This assumes that each realm can only belong to one region (regionID needs to be a foreign key to regionID on the region table):

table: realm
realmID         int
regionID        varchar(2)
realmName       varchar(50)

Defines data for each user (seller/buyer).  I decided that it would be better if it had a surrogate key (sellerID) because seller/buyers first/lastname combinations may not be totally unique.  Note, you may also want an address table to store multiple:

table: user
userID          int
userFirstName   varchar(20)
userLastName    varchar(20)
userEmail       varchar(100)

Data for each auction.  AuctionID would be a auto-generated ID and the Primary Key.  You would also want foreign keys on realmID (realmID on realm table) and sellerID (userID on user table):

table: auction
auctionID       int
realmID         int
sellerID        int
auctionPrice    decimal
auctionStart    datetime

Data for each auction that has been completed with a foreign key on buyerID (userID on user table):

table: auctionHistory
auctionID       int
buyerID         int
finalPrice      decimal
auctionFinished datetime

I don't think that you'd need a seperate table for "Scan".  That would be a simple query of rows in the auction table that do not have matching rows in the auctionHistory table.  Hopefully, this will help you get started.  If I missed/misread anything, please let me know.


How to set order for constraints to be checked in Postgres

It's hard to imagine a use case where it matters. Maybe a case where one constraint is much more likely to fail and/or the other one(s) are expensive (huge tables?) and you'd rather check the one first to optimize performance? Be that as it may, to answer the question asked:
CHECK constraints are applied alphabetically. The manual:

When a table has multiple CHECK constraints, they will be tested for each row in alphabetical order by name, after checking NOT NULL constraints.

But there is no such rule for UNIQUE constraints.
Slow and sure
A slow and sure way: define competing constraints as DEFERRABLE. The manual:

A constraint that is not deferrable will be checked immediately after every command. Checking of constraints that are deferrable can be postponed until the end of the transaction ...

But if the purpose of this is to optimize performance, this may defy the purpose. The manual warns:

Be aware that this can be significantly slower than immediate uniqueness checking.

There are also a couple of side effects. Like: FK constraints cannot currently point columns with only a deferrable UNIQUE constraint. Read the manual carefully. Related:

Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?

Quick and dirty
Some quick tests indicate that UNIQUE constraints are checked in the order of their creation. Could be bound to the oid in pg_constraint or the oid of the underlying unique index ... But neither can I give any guarantees that this is true, nor does the Postgres project guarantee anything in that direction. I found nothing in the manual or anywhere. Looks like an implementation detail that's not reliable. But for the sole purpose of performance optimization "not reliable" may still be good enough.
To take advantage of this observation create instance_source_uniq before you create instance_name_uniq. (DROP and recreate other UNIQUE constraints that were created earlier.) Check success with a forged INSERT that violates all competing UNIQUE constraints at once. You'll see an error message for the one that's checked first.
To further keep that order intact across dump / restore cycles, rename instance_source_uniq to something like instance_1st_source_uniq, which sorts before all other competing constraint names. pg_dump dumps objects in alphabetical order, so this should result in your favored constraint being created first on restore.

Postgres pg_dump dumps database in a different order every time (Peter Eisentraut is a core developer.)

Again, no guarantees. Whenever instance_1st_source_uniq is dropped and recreated, you may lose the effect. Or if Postgres decides to do things differently. Or if there are exception to the observed behavior.


HA PostgreSQL installation using patroni and pgbouncer

Yes, you can use Consul templates for this. https://github.com/hashicorp/consul-template This will notify pgbouncer that Patroni has elected a new master.
Cheers,
Dave


If I recall correctly, PostgreSQL now zeroes the end of the WAL segment after an archive timeout, before archiving the file. (Update): This is true for 9.4; for prior versions you still need to use  pg_clearxlogtail utility to zero the end of any WAL segment as part of your archive script before you gzip your archive files.

In 9.4 and above you just need to gzip the file, with no need for pg_clearxlogtail.

The change was made commit 9a20a9b, but it's only in tag REL9_4_BETA1, so the change will first appear in 9.4:

commit 9a20a9b
Author: Heikki Linnakangas <heikki.linnakangas@iki.fi>
Date:   Mon Jul 8 11:23:56 2013 +0300

    Improve scalability of WAL insertions.

    This patch replaces WALInsertLock with a number of WAL insertion slots,
    allowing multiple backends to insert WAL records to the WAL buffers
    concurrently. This is particularly useful for parallel loading large amounts
    of data on a system with many CPUs.

    This has one user-visible change: switching to a new WAL segment with
    pg_switch_xlog() now fills the remaining unused portion of the segment with
    zeros. This potentially adds some overhead, but it has been a very common
    practice by DBA's to clear the "tail" of the segment with an external
    pg_clearxlogtail utility anyway, to make the WAL files compress better.
    With this patch, it's no longer necessary to do that.

BTW, you should really be using an archive timeout, rather than manually switching xlogs. Consider having a streaming replica for additional protection.

Update: As Jeff noted, pg_clearxlogtail hasn't been updated for 9.3. The incompatibility is very simple though; the definition of XLogRecPtr changed from

typedef struct XLogRecPtr
{
        uint32          xlogid;                 /* log file #, 0 based */
        uint32          xrecoff;                /* byte offset of location in log file */
} XLogRecPtr;

to

typedef uint64 XLogRecPtr;

when support for platforms with compilers that don't support 64-bit long integers was removed from PostgreSQL in 9.3. So all we need to do is add a macro or function that tests equality in a way that works for both.

The following patch adds support for 9.3, but is completely untested so use entirely at your own risk. If it eats your data then sets fire to your house, well, that's how things are. Patch here, as an attachment to the linked pgfoundry issue.


table partition in order to solve frequent deadlock issues - how would I know if it will work?

Since your problem is a conflict between constant updates and being able to run reports without facing deadlocks, I would recommend you consider using READ_COMMITTED_SNAPSHOT isolation set ON.

This also has overhead, but usually prevents most deadlocking situations. (Nothing is magic, of course.)  You should read the details at:

http://msdn.microsoft.com/en-us/library/ms173763.aspx

Note that there is overhead in tempdb required in order to hold a consistent view of the data, which is needed to avoid the deadlocks.  There is another SNAPSHOT isolation level that can be turned on per transaction.

Since this changes the blocking behavior, you may run into some transactions that need to be tuned or given a new index in order to avoid problems.

Therefore, if you use the READ_COMMITTED_SNAPSHOT isolation level, which works fine for me, you will need to monitor it until you are sure that all is well in your instance.


Should binary files be stored in the database?

Store in the database with a blob

A disadvantage is that it makes your database files quite large and possibly too large to back up with your existing set up. An advantage is integrity and atomicity.
Store on the filesystem with a link in the database

I've come across such horrible disasters doing this, and it scares me that people keep suggesting it. Some of the disasters included:

One privileged user who would rearrange the files and frequently break the links between the paths in the DB and where they now are (but somehow this became my fault).
When moving from one server to another, the ownership of some of the files was lost as the SID for the old machine's administator account (what the old website was running on) was not part of the domain and so the copied files had ACLs that could not be resolved thus presenting users with the username/password/domain login prompt.
Some of the paths ended up being longer than 256 characters from the C:\ all the way to the .doc and not all versions of NT were able to deal with long paths.

Store in the filesystem but rename to a hash of the contents and store the hash on the database

The last place I worked at did this based on my explanation of the above scenarios did this. They thought it was a compromise between the organization's inability to obtain experience with large databases (anything larger than about 40G was ordained to be "too big"), the corporate inability to purchase large hard drives, and the inability to purchase a more modern back up solution, and the need to get away from risks #1 & #3 that I identified above.

My opinion is that storing in the DB as a blob is a better solution and more scalable in a multi-server scenario, especially with failover and availability concerns.


Difference Between Row Level and Page Level Locking and Consequences

Executing the query "" failed with the following error: "The index ""
  (partition 1) on table "" cannot be reorganized because page level
  locking is disabled."

The maintenance plan must be attempting an ALTER INDEX REORGANIZE, which is an online operation. To remove fragmentation (pages not in order), pages must be locked and moved, which is not possible if page locks have been disabled. The only way to defragment without page locks is to lock the entire partition, which is not possible for REORGANIZE as its online only.

  What is the difference between the two locking schemes, and what are
  their real-world (in production) consequences?

You need to grasp what a record and page are to evaluate the impact of disallowing a particular lock type. If you are unfamiliar with SQL Server storage internals, start with Anatomy of a Record and Anatomy of a Page. Put very simply:

rows = records
rows are stored in pages of 8kb

If you were to alter the permitted lock types:

Disable page locks = Row and table locks only
Disable row locks = Page and table locks only
Disable both = Table locks only

There are two scenarios I'm aware of where it can be beneficial to disallow a lock type. Doesn't mean there aren't others, hopefully someone else will step in with examples.

A frequently accessed lookup table, that changes infrequently - By disabling both page and row level locks, all readers will take a shared table lock. This is faster/cheaper rather than the usual intent-shared on the table, followed by intent-shared on a page and finally a shared lock on a specific row or rows.

Preventing a specific deadlock scenario - If you encounter deadlocks caused by concurrent processes acquiring locks that are frequently on the same page, disallowing row locks results in page locks being taken instead. Only one process can then access the page at a time, the other must wait.

The first example is micro-optimisation and unlikely to yield measurable benefit on a typical system. The second will solve that particular deadlock scenario but may introduce unexpected side effects e.g. killing concurrency in a different section of code. Difficult to assess the impact fully, approach with caution!

The default is for both to be enabled and this should not be changed without good cause.


I suppose that you have used the "Edit top x Rows" feature of Management Studio and wanted to update one row. In case that specific row was already deleted by another user/session then you'll receive the upper error.

I think it's better not use the Management Studio's results grid to update data, but use SQL commands. This way you'll work on last fresh data.


One approach is to use SQLBulkCopy class. This allows you to insert many rows in one go from some source DataTable or Reader


why are people advised not to update through views

For a view to be updatable without using INSTEAD OF triggers "SQL Server must be able to unambiguously trace modifications from the view definition to one base table.".

There is no performance disadvantage to Updating these Views as SQL Server will just generate a query plan for the base table affected. One possible disadvantage might be that it adds a layer of obfuscation so unless you are using Views as a security layer it is clearer to just write code that updates the base table directly.

Another one might be if the View contains a one to many JOIN and you update the "one" side with a value from the "many" side it is undeterministic what result you end up with but the same applies to SQL Server's proprietary UPDATE ... FROM syntax. You would need to use MERGE or a scalar correlated sub query to avoid this possible issue.

For Views that are not updatable and require an INSTEAD OF trigger there are performance implications as the inserted and deleted pseudo tables need to be generated from the base table so if possible updating the base tables directly will likely be more efficient.


1. What is the reason for 2 execution plans?

From the sys.query_context_settings (Transact-SQL) documentation:

    There are a number of context settings available in SQL Server that influence the query semantics (defining the correct result of the query). The same query text compiled under different settings may produce different results (depending on the underlying data).

It seems most likely the same query text was submitted from sessions with different context settings. See also Slow in the Application, Fast in SSMS? by Erland Sommarskog.

  2. The optimizer is using the top execution plan, I forced it to use the lower plan but after sometime it goes again to the top one, any reason for that?

Plans will still recompile from time to time based on the normal rules around changes to e.g. underlying objects, statistics, schema. Forcing a query store plan helps ensure the plan used will be at least fundamentally similar to the source plan, as sys.query_store_plan (Transact-SQL) notes:

    Forcing mechanism does not guarantee that exactly this plan will be used for the query referenced by query_id. Plan forcing causes query to be compiled again and typically produces exactly the same or similar plan to the plan referenced by plan_id.

Each (re-)compilation may produce plans with different estimates (including memory grant) depending on any values in the query that are visible to the optimizer at the time of compilation. A specific memory grant cannot be forced using query store or plan guides.

  3. How to fix this problem?

As Dan Guzman's answer suggests, if the plan is very sensitive to specific parameter values, compiling a fresh plan on every execution using an OPTION (RECOMPILE) hint may be the best practical solution.

That said, there are very few tools available to affect update-side execution plans, which can be complex where things like cascading foreign keys and indexed views are involved. If all else fails, you may find that an execution plan without sorts (to order rows in index key order) produces the best result. There is no documented way that I'm aware of to generate plans without these sorts, but you could try forcing the plan obtained with undocumented trace flag 8795 on.

You might also try a suitable MAX_GRANT_PERCENT query hint or simply install (or make available to SQL Server) enough memory to handle your workload.


Try rebuilding all indexes on your table to see if you can reclaim free space due to fragmentation.  If you can move to SQL Server 2016 SP1, you could take advantage of compression.
Are you sure the actual query is taking 2 minutes or is it SSMS that is taking 2 minutes to display the results.  The grid in SSMS is notoriously slow.  Before running your query, run SET STATISTICS TIME ON.  Run your query and look in messages for:

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

That would be the actual amount of execution time for the query.
select convert(datetime,'18/11/2016',103) returns midnight as far as time, so unless those emails were sent at exactly midnight, you won't get a match.  If you want to see emails for that date, use

SELECT id FROM tblEmailsSent
 WHERE  convert(date,[sent],103) = convert(date,'18/11/2016',103)

Posting the xml for the "actual" execution plan "might" provide us with additional information. I wouldn't think the order desc would be causing any real problem - add SET IO STATISTICS ON before you run the query to see how much logical vs physical I/O you're doing.

One thing to do as an experiment would be to change the select to just retrieve ID only - how long does that run.  Add the next column - how long does that run and so on.  Perhaps your LOB reads are really where the slow down is - if that's the case, I'm not sure what you'd be able to do about that.  Starting with a 'minimum number of columns and adding additional columns on each subsequent run may provide some insight as to what is bogging down your query.

Also, check out this Q & A that has some excellent discussion of LOB data - - LOB_DATA, slow table scans, and some I/O questions


Unable to shutdown immediate database - Oracle

Since you do not mind losing these files, you can just simply drop the whole tablespace after making the datafile(s) in it offline as below.
Steps to reproduce:
SQL> create tablespace test datafile '/oradata/RYMIN19/test.dbf' size 100M;

Tablespace created.

SQL> create table bp.t1 tablespace test as select * from dba_objects;

Table created.

SQL> !dd if=/dev/urandom of=/oradata/RYMIN19/test.dbf conv=notrunc bs=8192 count=2 oflag=sync
2+0 records in
2+0 records out
16384 bytes (16 kB, 16 KiB) copied, 0.00262083 s, 6.3 MB/s

SQL> shu immediate
ORA-01122: database file 5 failed verification check
ORA-01110: data file 5: '/oradata/RYMIN19/test.dbf'
ORA-01210: data file header is media corrupt

One way to drop:
SQL> shu abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 2147481656 bytes
Fixed Size                  8898616 bytes
Variable Size             956301312 bytes
Database Buffers         1174405120 bytes
Redo Buffers                7876608 bytes
Database mounted.
SQL> alter database datafile '/oradata/RYMIN19/test.dbf' offline drop;

Database altered.

SQL> alter database open;

Database altered.

SQL> drop tablespace test including contents and datafiles;

Tablespace dropped.

If you have more files in this tablespace, execute the above offline drop for all before dropping the tablespace.
You will need to do some manual cleanup in the filesystem because the database is unable to delete such a file, as shown in the alert log:
2020-12-30T23:33:33.458679+01:00
drop tablespace test including contents and datafiles
2020-12-30T23:33:34.322107+01:00
Setting Resource Manager plan SCHEDULER[0x4D5B]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
2020-12-30T23:33:36.724601+01:00
Errors in file /u01/app/oracle/diag/rdbms/rymin19/RYMIN19/trace/RYMIN19_ora_513540.trc:
ORA-01122: database file 5 failed verification check
ORA-01110: data file 5: '/oradata/RYMIN19/test.dbf'
ORA-01565: error in identifying file '/oradata/RYMIN19/test.dbf'
ORA-27048: skgfifi: file header information is invalid
Additional information: 2
2020-12-30T23:33:36.724680+01:00
Errors in file /u01/app/oracle/diag/rdbms/rymin19/RYMIN19/trace/RYMIN19_ora_513540.trc:
ORA-01259: unable to delete datafile /oradata/RYMIN19/test.dbf
Completed: drop tablespace test including contents and datafiles

Cleanup:
SQL> !ls -l /oradata/RYMIN19/test.dbf
-rw-r-----. 1 oracle oinstall 104865792 Dec 30 23:32 /oradata/RYMIN19/test.dbf

SQL> !rm /oradata/RYMIN19/test.dbf

Shutdown immediate works as expected:
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>


SQL Server Database Suspect with Memory table

Sadly, corruption checking doesn't work for In-Memory OLTP (Hekaton).

Your best bet is to restore from backup at this point, and then maybe upvote the request for Microsoft to get corruption checking to work. Maybe someday you'll be able to check for corruption on your important data before you're stuck getting surprised like this.


How to list all pending friend requests for a given user ID?

how to list all pending requests to uid X? I tried this (X=132)

SELECT GREATEST(uid1, uid2), LEAST(uid1, uid2)
FROM tbl_friendship
WHERE 132 IN (uid1, uid2)
GROUP BY 1, 2
HAVING COUNT(*) = 1

  I'm only interested to find everyone who send friend request to 132. – anta40

SELECT t1.uid2
FROM tbl_friendship t1
WHERE t1.uid2 = 132
  AND NOT EXISTS ( SELECT NULL
                   FROM tbl_friendship t2
                   WHERE (t1.uid1, t1.uid2) = (t2.uid2, t2.uid1) );


I would enable Read-Committed Snapshot Isolation, which does row versioning under the covers by making use of tempdb. Readers will not block writers, which is what you're looking for in this scenario.

This is a per-database level setting, which doesn't require a reboot. However, you must have tempdb configured correctly (appropriate fixed-size file growth, sufficient data files, etc.).

ALTER DATABASE MyDatabase SET READ_COMMITTED_SNAPSHOT ON;
GO

(Read more here: Choosing Row Versioning-based Isolation Levels.)


That shouldn't really be done on a single table, it will cause you all sorts of grief. Do it over two tables. Have your first table however you want, but without the "graph" values (time and load). Instead have simply a "graph_id" value or something similar. e.g. (based on your example):

------------------------
|Heading         |     |
|Test No#        |     |
|Test Date       |     |
|Test Time       |     |
|Maximum Load    |     |
|Maximum Strength|     |
|graph_id        |     |
|Checksum        |     |

Then have another table that stores your graph details (time and load):

------------------------
|graph_id        |     |
|time            |     |
|load            |     |

Access the data via a simple join.


Issues setting up a publisher for transactional replication on a SQL Server with a custom instance

I'm afraid you are not going to make it work. Biztalk uses internally distributed transactions and thus, DTC.
Replication procedures use save points.
These 2 things are not compatible as far as I could read.

References:

https://feedback.azure.com/forums/908035-sql-server/suggestions/33422404-replication-and-availability-groups-with-per-datab
https://am2.co/2018/02/replication-availability-groups-2/
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/466202d8-93f3-41c2-9cc8-a0b7eb594687/cannot-promote-the-transaction-to-a-distributed-transaction-because-there-is-an-active-save-point-in?forum=transactsql


Retrieve by inserting ORDER of Merged table in SQL Server

Databases do not store data "in order" (for the most part).

If you want data out in a specific order, you must supply an appropriate ORDER BY clause.

In your data model:

BASE_Invoice is a transaction.
BASE_Payment is a transaction.

You're missing a table that holds the information for a transaction.  That table will include all columns common to both types of transactions, a transaction ID (PK) for all types of transactions, and information about the transaction that can be used by the ORDER BY clause.

It some cases, the Transaction ID could be used to determine ORDER BY.  In other cases, you may need to include a timestamp column to be used by the ORDER BY clause.


Fastest way to delete matching rows in MySQL?

You have not given much of a table description. Notwithstanding, I will try to answer your question.

For this example, let's use this table:

CREATE TABLE mytable
(
    id INT NOT NULL AUTO_INCREMENT,
    email VARCHAR(255),
    PRIMARY KEY (id)
);

First, try counting all the IDs that have .ac. in the email column.

SELECT COUNT(1) INTO @CompleteCount FROM mytable;
SELECT COUNT(1) INTO @DeleteCount FROM mytable WHERE LOCATE('.ac.',email) > 0;

There are two approaches to doing a DELETE:

APPROACH #1 : DELETE JOIN

If @DeleteCount < 5% of @CompleteCount, use a list of IDs to DELETE from mytable:

CREATE TABLE mytable_delete_id
SELECT id FROM mytable WHERE LOCATE('.ac.',email) > 0;
ALTER TABLE mytable_delete_id ADD PRIMARY KEY (id);
DELETE B.* FROM mytable_delete_id A INNER JOIN mytable B USING (id);
DROP TABLE mytable_delete_id;

This helps take the guess work out of which rows to DELETE by using a SELECT to get the IDs. The JOIN takes care of the DELETE.

APPROACH #2 : CREATE A NEW TABLE

If you have the following conditions:

@DeleteCount > 30% of @CompleteCount
your table does not have foreign keys/constraints

you could  just create a new table and filter out the rows with .ac. in the email column:

CREATE TABLE mytable_new LIKE mytable;
#insert rows which does not contain '.ac.' from mytable
INSERT INTO mytable_new SELECT * FROM mytable WHERE LOCATE('.ac.',email) = 0;
ALTER TABLE mytable RENAME mytable_old;
ALTER TABLE mytable_new RENAME mytable;
DROP TABLE mytable_old;

EPILOGUE

You can do either of these methods without checking table counta ahead of time. They will both work. If you are concerned that .ac. is in a huge number of rows, go with APPROACH #2. If there are not that many occurrences, go with APPROACH #1.


Subquery or multiple left joins, or both?

There are many ways to do this. Most common are with a LEFT JOIN / IS NULL check, NOT IN or NOT EXISTS subquery. Here's a solution with the 3rd option:

SELECT t2.id, t2.name
FROM table2 AS t2
WHERE NOT EXISTS
      ( SELECT *
        FROM table3 AS t3
        WHERE t3.table1_id = @t1_id     -- the t1.id value (choice) that you want to check
          AND t3.table2_id = t2.id
      ) ;


Where does MySQL's "show slave status" information come from?

That slave info is in RAM.
If you are running MySQL 5.6, run RESET SLAVE ALL;
If you are running MySQL 5.5, restart MySQL
If you are running MySQL 5.0/5.1, run the following:
STOP SLAVE;
CHANGE MASTER TO master_host='';

These are the ways to make the SHOW SLAVE STATUS\G disappear from view
GIVE IT A TRY !!!


How to take SQL Server database under this scenario?

If you have access to another server on your network with larger disks, you can do database backup to a network share:

backup database YOURDATABASE
to disk = '\\NETWORKSERVER\SHARENAME\YOURDATABASE_yyyymmdd.bak'
with init

For this to work, the share would need to have Write permissions from the domain account under which your SQL Server is running.

If you don't have access to another server with enough disk space, you may want to get an inexpensive Network Attached Storage type of device (e.g. synology) and perform your backups to it.


You could need brackets.
Try this:

SELECT Actors.FirstName,Actors.LastName
FROM ((Actors
INNER JOIN ActorsMovies  ON Actors.ActorID= ActorsMovies.ActorID)
INNER JOIN GenresMovies ON ActorsMovies.MovieID=GenresMovies.MovieID);


Postgresql pg_hba.conf changes are ignored or not loaded

Figured it out.  This was a long shot, but it may help someone who has exhausted the normal avenues or is not used to postgres.

The server I was working on had another instance running through puppet, and so the config I was modifying was not for the instance I was connecting to.

run find / -name 'postgresql.conf' to see if there are any other locations where you might have a postgres instance.


I have a big confusion between Always On FCI/AG. I know that for FCI you need to have shared disks and in-case of Hardware failure or OS failure or System reboot the instance with db, logins etc will fail-over to another node.

Shared or replicated disks, yes. This is because the Disks "move" between servers. Each potential failover target has SQL Server installed locally and the service is started when the local node owns the resources and is given the online command. The local service starts and mounts the database files which are on the shared or replicated disks. This is why you don't need to change or replicate any SQL Server based items (logins, jobs, etc.).

  For AG i am not clear when db fail-over occur ?

Failover of the Availability Group (not an individual database like Database Mirroring) will happen when the health checks either timeout, fail, or the lease fails to be obtained or renewed. When and under what circumstances is entirely dependent upon the flexible failover policy chosen and the type of issue the instance encounters.

  what if hardware fails or OS fails or System reboots ?

In all of those cases it is entirely dependent upon the configuration of the availability group. If you're set for automatic failover (synchronous commit + synchronized databases + automatic failover set) then in all of those situation the availability group should fail over.

  What instance names i must configure for all nodes ?

Entirely your choice. To make administration easier, I'd just use default instances.

  What about logins we need to take care about ?

Only objects at the database level will be replicated as part of the log stream. System databases cannot currently be put in availability groups. Thus, any server level object or objects not contained in the user databases will need to be replicated. Examples include: Logins, Jobs, Server Level Certificates, Proxies, Server Level Configurations, Linked Server Definitions, Security Audits, etc.


If you go to server properties, in the database settings, you will find the default database data, log and backup locations :


Help needed with several engines use case

So any of those options EXCEPT ElasticSearch will probably work ok for you (equal hardware under the hood considered).
ElasticSearch is quite possibly the opposite use case from what you described. It's primarily best for tokenizing full text (like parsing email messages for example) and searching it efficiently, you mentioned your use case will always read by key.
A NoSQL database system or DynamoDB are actually rather fitting for your use case because of two things you mentioned: always reading by key, and storing the actual data in JSON format which allows for semi-structured, unstructured, and varying structured data. So this would be my first choice (might want to look into read limitations of DynamoDB vs other NoSQL database systems, as I thought I recall it was a little more limited based on how it was designed).
Otherwise MySQL should be able to handle your use case ok as well, being a modern RDBMS, but it wasn't really designed for your use case (serving the data only by the key and storing it all in a JSON format). It's a relational database system and therefore was designed for normalized schemas with Tables and relationships between those Tables.


May be lame to do this but worthy to double check the username and password and ip. "Copy paste the exact connect string in commandline"
Triage more by only keeping DB connection from app/web server and check the DB connectivity  functionality and see if the issue repeats. If yes, do the same from someother host with same app/web interface server version. [with this we can be sure that the host is no problem] .


connect to PostgreSQL server: FATAL: no pg_hba.conf entry for host

Add or edit the following line in your postgresql.conf :

listen_addresses = '*'

Add the following line as the first line of pg_hba.conf. It allows access to all databases for all users with an encrypted password:

# TYPE DATABASE USER CIDR-ADDRESS  METHOD
host  all  all 0.0.0.0/0 md5

Restart Postgresql after adding this with service postgresql restart or the equivalent command for your setup.


The source call leg does not change in the whole process.

You need to remember that Name and Number also stay the same. So, your IN predicate should include those column in the matching:

  (tb.name, tb.number, tb.Src_Call_Leg) IN (
    SELECT
      tb1.name, tb1.number, tb1.Src_Call_Leg
    FROM
      yourTable tb1
    WHERE
      tb1.Dest_Call_Leg LIKE '1000%'
  )

Not all SQL products support tuple comparison, though, and you have not specified what is yours, so the above syntax might not work for you.

Still, there is an alternative that will most likely work: an EXISTS predicate. It will go like this:

  EXISTS (
    SELECT
      *
    FROM
      yourTable tb1
    WHERE
      tb1.Dest_Call_Leg LIKE '1000%'
      AND tb1.name = tb.name
      AND tb1.number = tb.number
      AND tb1.Src_Call_Leg = tb.Src_Call_Leg
  )

And since you have also specified that you want only the final Dest_Call_Leg, you probably need this additional condition in the main WHERE:

  AND tb.Dest_Call_Leg NOT LIKE '1000%'

So, the entire query would look like this:

SELECT
  tb.name,
  tb.number,
  tb.Src_Call,
  tb.Dest_Call_leg
FROM
  yourTable tb
WHERE
  EXISTS (
    SELECT
      *
    FROM
      yourTable tb1
    WHERE
      tb1.Dest_Call_Leg LIKE '1000%'
      AND tb1.name = tb.name
      AND tb1.number = tb.number
      AND tb1.Src_Call_Leg = tb.Src_Call_Leg
  )
  AND tb.Dest_Call_Leg NOT LIKE '1000%'
;

Alternatively, if your SQL product supports analytic functions like LAST_VALUE(), you could implement the request like this:

SELECT
  Name,
  Number,
  Src_Call_Leg,
  Final_Dest_Call_Leg AS Dest_Call_Leg
FROM
  (
    SELECT
      Name,
      Number,
      Src_Call_Leg,
      Dest_Call_Leg,
      LAST_VALUE(Dest_Call_Leg) OVER (
        PARTITION BY Name, Number, Src_Call_Leg
        ORDER BY ID ASC
        ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
      ) AS Final_Dest_Call_Leg
    FROM
      yourTable tb1
  ) derived
GROUP BY
  Name,
  Number,
  Src_Call_Leg,
  Final_Dest_Call_Leg
HAVING
  COUNT(CASE WHEN Dest_Call_Leg LIKE '1000%' THEN 1 END) > 0
;

The derived table calculates the final Dest_Call_Leg for every call. Columns Name, Number, Src_Call_Leg and Final_Dest_Call_Leg will stay the same across every group of rows that belong to the same call – so the main SELECT groups the derived table by those columns and uses HAVING to filter out the groups that do not have an intermediate Dest_Call_Leg that matches the 1000% pattern.


where is the index physically located in MySQL database

For the MyISAM engine, a table's indexes are stored in the .MYI file (in the data directory, along with the .MYD and .frm files for the table).

For InnoDB engine, the indexes are stored in the tablespace, along with the table. If innodb_file_per_table option is set, the indexes will be in the table's .ibd file along with the .frm file.

No, it's not possible to create an index that references more than one table. An index is specific to a table.


How does a tagging article database look like?

1 & 2:use inner join

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;

3:
yes your structure is true


How to use and optimize subquery on 100 million rows

Since is_keyword_checked can take only two possible values, 0 or 1 (and it's not nullable), you can try the rewriting. There is no OR and the index on (is_keyword_checked) will be used:

SELECT *
FROM domain
WHERE domain_ID NOT IN
      ( SELECT domain_ID
        FROM domain_setting
        WHERE is_keyword_checked = 1
      ) ;

or with a NOT EXISTS subquery - which should have similar execution plan:

SELECT d.*
FROM domain AS d
WHERE NOT EXISTS
      ( SELECT *
        FROM domain_setting AS ds
        WHERE ds.is_keyword_checked = 1
          AND ds.domain_ID = d.domain_ID
      ) ;


MySQL create user incorrectly updates user table

mysql_upgrade is your friend

  mysql_upgrade examines all tables in all databases for
  incompatibilities with the current version of MySQL Server.
  mysql_upgrade also upgrades the system tables so that you can take
  advantage of new privileges or capabilities that might have been
  added.


Why does this query not use the index that exists on SURNAME?

Because it has to compile a plan that will work correctly for both the cases where @SURNAME is NULL and when it is not.

In principle it would not be impossible for SQL Server to create such a plan that works in both cases and still uses an index. The dynamic seek mechanism already copes with the case that @SURNAME contains a leading wildcard and so the whole not null part of the index will need to be seeked - but your requirement is not something currently implemented.

The above would only be useful in the event that the index covers the query (as your example does). If the index is non covering then it would not be desirable to do a range seek on the whole index and then require lookups for the whole table.

  Is there a way to get SQL Server to use the index?

If PAT.SURNAME is not nullable you could do

DECLARE @SURNAME VARCHAR(30) = 'test'

SELECT SURNAME
FROM   PATIENTS AS PAT
WHERE  ( PAT.SURNAME LIKE ISNULL(@SURNAME, '') + '%' )

Alternatively you could request a new plan compilation every time, taking into account that execution's parameterised values, with

DECLARE @SURNAME VARCHAR(30) = 'test'

SELECT SURNAME
FROM   PATIENTS AS PAT
WHERE  ( PAT.SURNAME LIKE @SURNAME + '%'
          OR @SURNAME IS NULL )
OPTION (RECOMPILE)

(as long as you are on a reasonably recent version that supports the parameter embedding behaviour.)

Or you could do

DECLARE @SURNAME VARCHAR(30) = 'test'

SELECT SURNAME
FROM   PATIENTS AS PAT
WHERE  ( PAT.SURNAME LIKE @SURNAME + '%'
         AND @SURNAME IS NOT NULL )
UNION ALL
SELECT SURNAME
FROM   PATIENTS AS PAT
WHERE  ( @SURNAME IS NULL )

The plan above will show two table references but hopefully both underneath a filter with a start up predicate so only the relevant branch actually gets executed.

A final option would be to just have two entirely separate queries and choose the correct one with procedural logic.

I suggest reviewing Dynamic Search Conditions in T‑SQL as an informative article on the topic.


What is the right time to use an ENUM versus a normalized types table in MySQL?

When using ENUM to have a range of values, you have to properly plan what values you will use. Once those values are put in place, it can be rather challenging to perform any kind of simple maintenance should you want to redefine the ENUM range.

While there are bait-and-switch tricks you can perform to extend a range for an ENUM, those same tricks become very cumbersome (maybe even impossible) if you want to do other things such as reducing or altering an ENUM range.

Using foreign keys makes a great deal of sense when you know that the types you are establishing can be extended, reduced, or altered. Just by adding new entries into a table with the types needed, keys can be passed around to other tables without worrying about the underlying value being properly represented in the foreign tables. In addition, you would not be worried about those values matching up exactly with the base table definition of the type. Such concerrns would definitely apply if using ENUM.

Foreign Key Constraints would server to protect a row from absorbing invalid types. Hence, this would not be suitable for ENUMs because the constraint checking would have to be done in your application rather than in the database.

Here is what should be considered about ENUM usage:

ENUMs work best when

the type you are representing is local to the table only
it must represent a type that is never changed
it will never likely experience redefinition
its cardinality needs to be low
its portability must always be based on logcial dumps of the table, never physical. Otherwise, bait-and-switch methods of maintenance would have to accompany the use of the ENUM.
you can allow for NULL (Example : game_rating ENUM('EC','E','E10+','T','M','A') NULL This allows a new game to be entered into a table without a default rating)

CAVEAT

OK game_rating may be a bad example since ESRB could create more game ratings. In that case, a FK would be better but the base table would have to have 'unknown' as a default type and define game_rating as NOT NULL.


Convert existing table to partitioned table - SQL Server 2017

You are totally on the wrong idea here. PArtitioning is not really how to do that. Not saying that thee is no sense in it, but generally partitioning is about deleting data fast (i.e. a partition per year MAY bea good idea to get rid of 10 year old data in milliseconds).

All you need to do as simplest solution for your case is:

Add other drive
Make sure it is writable (partition, format etc.
Go into the db setup and add another db file to the filegroup.

You do not gain most of the advantages of multiple drives, but you would not with your solution either. This would require serious reformatting which I doubt you have the space for.

But a file group can have multiple files and SQL Server will automatically use them all. There is no need to partition for your particular problem.


Log Shipping: Bulk Remove Alerts of already deleted primary and secondary databases

Information about databases involved in log shipping are contained in several system tables in the msdb database.  Looking at these tables will help identify whether you've properly reconfigured log shipping.

This first query shows databases that are configured as primaries, where the database no longer exists on the SQL Server instance:

SELECT lspd.primary_database
    , lspd.last_backup_date
FROM msdb.dbo.log_shipping_primary_databases lspd
WHERE NOT EXISTS (
    SELECT 1
    FROM master.sys.databases d
    WHERE d.name = lspd.primary_database
    );

This query will show secondaries for the databases identified above:

SELECT lsps.secondary_server
    , lsps.secondary_database
FROM msdb.dbo.log_shipping_primary_secondaries lsps
    INNER JOIN (
        SELECT lspd.primary_id
        FROM msdb.dbo.log_shipping_primary_databases lspd
        WHERE NOT EXISTS (
            SELECT 1
            FROM master.sys.databases d
            WHERE d.name = lspd.primary_database
            )
        ) p ON p.primary_id = lsps.primary_id;

Use sys.sp_delete_log_shipping_primary_secondary to remove primary databases from the log shipping configuration.

Use sys.sp_delete_log_shipping_secondary_database to remove affected secondaries, if desired, which should stop the alerts you're seeing.

The Microsoft Docs page on monitoring log shipping shows several useful details, including the following tables that are used by the monitor process:

log_shipping_monitor_alert - Stores alert job ID.
log_shipping_monitor_error_detail - Stores error details for log shipping jobs. You can query this table see the errors for an agent session. Optionally, you can sort the errors by the date and time at which each was logged. Each error is logged as a sequence of exceptions, and multiple errors (sequences) can per agent session.
log_shipping_monitor_history_detail - Contains history details for log shipping agents. You can query this table to see the history detail for an agent session.
log_shipping_monitor_primary - Stores one monitor record for the primary database in each log shipping configuration, including information about the last backup file and last restored file that is useful for monitoring.
log_shipping_monitor_secondary - Stores one monitor record for each secondary database, including information about the last backup file and last restored file that is useful for monitoring.

Querying the contents of those tables should help you narrow down the source of the alerts.  The page I mentioned above also contains details about several monitoring stored procedures you can use to query the tables.

The "Transaction Log Shipping Status" report calls the sp_help_log_shipping_monitor stored procedure to get its details - looking at the T-SQL definition of that stored proc may help you resolve where the issue is.


Why low cpu usage on sql server developer edition 2014

SQL Server Developer Edition can use all available cores unless you've specifically configured it otherwise. Whether or not it will use all cores depends on the workload.

If you are running a query like SELECT * FROM dbo.YourTable, it will likely be a single-threaded plan and only use one CPU. You'll need to run multiple instances of such a query to see more CPUs used.

A single query that uses parallelism should be able to use multiple cores. The example below uses all 12 cores of my box, although total CPU averages about 40%.

WITH
     t10 AS (SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t(n))
    ,t1k AS (SELECT 0 AS n FROM t10 AS a CROSS JOIN t10 AS b CROSS JOIN t10 AS c)
    ,t1g AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS num FROM t1k AS a CROSS JOIN t1k AS b CROSS JOIN t1k AS c)
SELECT COUNT(*) AS CountOfRows
FROM t1g;


Turn off listener logging in oracle 12c release 2

This can happen if you've got ADMIN_RESTRICTIONS_LISTENER set to ON in your listener.ora file.

Turning it off should resolve the problem.


timestamp with timezone issue with TZR vs TZH:TZM

You could define your table like this.

CREATE TABLE TEST_A (
    COLUMN_A
        TIMESTAMP(6) WITH TIME ZONE
        DEFAULT SYSTIMESTAMP AT TIME ZONE '+00:00'
        NOT NULL,
        CHECK (EXTRACT(TIMEZONE_REGION FROM COLUMN_A) = 'UNKNOWN')
    );

Table created.

INSERT INTO TEST_A VALUES (TO_TIMESTAMP_TZ('19-JUL-18 01.53.16.381566000 PM +00:00', 'DD-MON-RR HH.MI.SSXFF AM TZH:TZM'));

1 row created.

INSERT INTO TEST_A VALUES (TO_TIMESTAMP_TZ('19-JUL-18 01.53.16.381566000 PM UTC', 'DD-MON-RR HH.MI.SSXFF AM TZR'));

>> INSERT INTO TEST_A VALUES (TO_TIMESTAMP_TZ('19-JUL-18 01.53.16.381566000 PM UTC', 'DD-MON-RR HH.MI.SSXFF AM TZR'))
Error at line 11
ORA-02290: check constraint (SYS_C00241787) violated

Or you create a table with trigger:

CREATE TABLE TEST_B (
    COLUMN_A
        TIMESTAMP(6) WITH TIME ZONE
        DEFAULT SYSTIMESTAMP AT TIME ZONE '+00:00'
        NOT NULL
    );

CREATE OR REPLACE TRIGGER BUIR_TEST_B
    BEFORE INSERT OR UPDATE ON TEST_B
    FOR EACH ROW
BEGIN
    IF EXTRACT(TIMEZONE_REGION FROM :NEW.COLUMN_A) <> 'UNKNOWN' THEN
        :NEW.COLUMN_A := :NEW.COLUMN_A AT TIME ZONE TO_CHAR(:NEW.COLUMN_A, 'tzh:tzm');
    END IF;
END;
/

However, I don't believe this would be a proper solution. You may face issues when you work with time zones having daylight saving times.


Which is the uniqueness level of object_id?

The object_id column is unique per database. Two objects in separate databases can have the same object_id, however separate objects in the same database have always different object_id values.

Every time you drop and create an object, a new object_id value is assigned automatically and there is no way to influence which value is chosen. However, if you alter an object using the ALTER statement, the object_id stays the same.

Because there is no way to influence the value chosen for object_id, there is also no way to script out a database in an object_id preserving fashion, so if you re-create a database using a t-sql script there is no correlation between the previous object_id values and the new ones.

A database backup on the other hand copies the actual data pages one to one, so object_id values will stay unchanged.

In general it is a good idea to stay away from the object_id value in a production system. You cannot use it to detect a change nor a no-change situation. The only thing it should be used for is to filter or join system tables and DMVs. You can also use it to conditionally drop an object like this: IF(OBJECT_ID('dbo.objectname') IS NOT NULL) DROP... For anything else you should consider that value as off limits.


If you want to store exact values then you should use the DECIMAL data type instead of FLOAT.


SQL Server 2008r2 keeps hogging CPU not responding after a while online

SQL Server CPU usage stuck at 50% and no process running is not possible, so I suggest that you troubleshoot this condition better. sp_WhoIsActive is a great tool to capture active sessions on your instance.

Regarding the long restart time, I concur that 8 hours is a disproportionate time for recovery. This can only mean that you're undoing something that has been running for quite a while and has not been committed to disk yet. What that "something" is must be determined before cycling the instance. Again, sp_WhoIsActive is your friend.

Regarding the TOP 101 sort, this is a well known problem: Paul White wrote a nice article on the topic back in 2010.

Basically, SQL Server can use an alternative sort algorithm to sort up to 100 rows, but has to switch to full sorting when more than 100 rows are requested, hence the abnormal performance difference.

Solutions range from "dirty tricks" to rewrite the same query in ways that trick the optimizer to what IMHO is the best solution: avoiding the sort altogether by adding an index.

Reasons why this hangs the machine completely are beyond me and would need additional investigation. Things you could look into are:

tempdb filling due to enormous spills
long blocking chains


Determining sizes for Separated Partitions of programs, data and logs in SQL Server

Question is, what is the recommended way to go on about that, especially with regards to how size should be divided between them? If you wanted to do that, how'd you set it?

The programs itself won't take much space. You need to make sure the default locations are updated to reflect the data, log and backup locations.

Coming to the size of data & log, you need to do some estimates on the size of clustered index + non-clustered indexes and use the potential estimates for data growth over the next n time period. Estimates may NOT match with the actuals but that is the right way to do it for the data file size. The below links will help.

http://msdn.microsoft.com/en-us/library/ms178085.aspx

http://msdn.microsoft.com/en-us/library/ms190620.aspx

For the size of the transaction log, there are some defaults used by few consultants that log should be this many times of the data file but that is NOT a good way to do it. You will either make the log smaller or too big for your needs if you follow that route. The right way is estimate the largest transaction in your database and keep the log with some head room for that and watch for the transaction log size autogrow event. If the log grew after sometime then you need to make the log bigger than that size and watch it for some more time. It takes few iterations but that is the right way to do it. What is the largest transaction? That depends and most likely it may be the index rebuild of your largest table. Hope this is a good starting point for you, let me know if you have more questions/comments on this.


Does Microsoft release its SQL Query Hash Algorithm..?

No, Microsoft does not release the hashing algorithm. Additionally, hashing happens at a different layer than original query text - so even if you had the algorithm, you'd still need to normalize like SQL Server does, first.

  This way I could do quick matches of the hash to be able see where in the codebase a particular query came from.

This assumes there also isn't anything like dynamic sql, SMO, etc., where there isn't an actual plain text query.


One way to do this is using user variables. The following code will sort through the table  (first in reverse) and then through this result set (grouping and) keeping only rows that are wanted:

SELECT
    id,
    MIN(time_stamp) AS start_timestamp,
    start_ts        AS end_timestamp,
    TIMESTAMPDIFF(MINUTE, MIN(time_stamp), start_ts)
                    AS duration_in_mins,
    MIN(state)      AS state
FROM
  ( SELECT
        id, time_stamp, state,
        @st_ts :=
            CASE WHEN state = @prev_state AND id = @prev_id
                     THEN @st_ts
                 WHEN id = @prev_id THEN @prev_ts
                     ELSE time_stamp
            END AS start_ts,
        @prev_ts := time_stamp,
        @prev_state := state,
        @prev_id := id
    FROM sample
       CROSS JOIN
         ( SELECT
             @prev_state := '',  @prev_ts := 0,
             @st_ts := 0,        @prev_id := 0
         ) AS dummy
    ORDER BY id DESC, time_stamp DESC
  ) AS tmptable
GROUP BY
    id, start_ts ;

Test at SQL-Fiddle-1


(Note: Not much of this is relevant to readers using PostgreSQL 9.2 or above from the EDB installers, which now have a greatly simplified default install using the NETWORK SERVICE, though you can still configure other accounts).

  I have used net user postgres postgres
  to reset the password for my database but instead of a success message I am getting "System error 5 has occurred. Access is denied."

You've reset (or tried to reset) the service account password. PostgreSQL won't run as Administrator for security reasons and the installer generally sets it up with a "postgres" user account in PostgreSQL 9.1 and older1. On Windows you can't start a service as a user without saving the password of the user in the registry, so that's what the installer does.

If you change the password for the Windows user account postgres, the PostgreSQL service can no longer start. So don't do that, you'll have to fix the service configuration to store the updated password.

Thankfully I think another mistake prevented you from doing that. It looks like you're probably running your command prompt without using "Run as Administrator" on an unprivileged Windows user account or a machine with UAC, so it isn't running with the access permissions required to change the password for the postgres user.

Before you try to change that password, make sure it's really what you want to do. What's the problem you're trying to solve here? Are you attempting to install a database update or something else that's asking for the password for the postgres Windows user?

Most likely you're just trying to log in to the database. For that, you use the (unfortunately completely unrelated) password stored in the database its self. Since you've lost/forgotten it you'll have to reset it:

Find your pg_hba.conf, usually in C:\Program Files\PostgreSQL\9.1\data\pg_hba.conf
If necessary, set the permissions on it so that you can modify it; your user account might not be able to do so until you use the security tab in the properties dialog to give yourself that right by using an admin override. Alternately, find notepad / notepad++ in your start menu, right click, choose "Run as administrator", then use File->Open to open pg_hba.conf that way.
Edit it to set the "host" line for user "postgres" on host "127.0.0.1/32" to "trust". You can add the line if it isn't there; just insert:

host all postgres 127.0.0.1/32 trust
host all postgres ::1/128      trust # if IPv6 is in use

before any other lines. (You can ignore comments, lines beginning with #).
Restart the PostgreSQL service from the Services control panel (start->run->services.msc)
connect using psql or PgAdmin-III or whatever you prefer
ALTER USER postgres PASSWORD 'postgres'
remove the line you added to pg_hba.conf or change it back
restart PostgreSQL again.

See: How do I reset the postgres password for PostgreSQL on Windows?

1. 9.2 now uses the NETWORKSERVICE account, which doesn't require a password, so this problem goes away.


Should I not use camelCase in my column names?

Use lower case in Postgres. Postgres folds identifiers to lowercase, unless you double-quote your identifiers. To make Postgres operations easier, use lower_snake. If you need to bind to an API, etc, you can create a view with “CamelCase” aliases. For example, we do this with data stored in Postgres that is pulled by Domo.


Is there an effective way to create extended event data that includes stacktraces in SQL Server on a local db instance?

The original Extended Event script from that article has some filters on it ( DBCC_CHECK, INDEX_CREATE and SE_REPL_PARTITION_BY_KEY ) which means they won't fire for just any old spinlocks.  I have adapted the script to remove the filters and have successfully captured the callstacks.  This demo script makes a number of assumptions:

you are not trying to do this on your production server
you know how to use SQLCMD mode ( SSMS > Query > SQLCMD Mode )
you know how to use ostress and have it installed
I'm working with a named instance called sql2012 - you can configure that
I'm using c:\temp\temp.sql as a scratch file - you can configure that

Demo script.  WARNING!! Do not run on production server
-- Spinlock simulation and callstack tracking with Extended Events
:on error exit
USE tempdb
GO

SET NOCOUNT ON
GO

CREATE TABLE spinlock_sim ( id INT PRIMARY KEY )
GO
INSERT INTO spinlock_sim VALUES( 1 )
GO

-- Create the sql file to run
!!bcp "SELECT N'SELECT id FROM spinlock_sim'" queryout c:\temp\temp.sql -S.\sql2012 -T -c

-- Reset spinlock stats (SQL 2012 only)
DBCC SQLPERF( 'sys.dm_os_spinlock_stats', CLEAR );
GO

-- Create the event session
CREATE EVENT SESSION [spin_lock_backoff] ON SERVER
ADD EVENT sqlos.spinlock_backoff( ACTION(package0.callstack) )
ADD TARGET package0.histogram(SET filtering_event_name=N'sqlos.spinlock_backoff',
    source=N'package0.callstack',source_type=(1))
WITH (MAX_MEMORY=51200 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=PER_NODE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

-- Start the session
ALTER EVENT SESSION spin_lock_backoff ON SERVER STATE=START
GO

-- Attempt to create the spinlock contention with ostress
-- REM Parameters -E Windows Authentication -S server -Q query -o output directory -n number of connections -r iterations
-- REM eg 50 connections 200 times
!!"C:\Program Files\Microsoft Corporation\RMLUtils\ostress.exe" -E -S.\sql2012 -dtempdb -ic:\temp\temp.sql -q -oc:\temp\ostress.log -n50 -r200 -mstress
GO

--To view the data
--1. Ensure the sqlservr.pdb is in the same directory as the sqlservr.exe
--2. Enable this trace flag to turn on symbol resolution
DBCC TRACEON (3656, -1)
GO

--Get the callstacks from the bucketize target
select event_session_address, target_name, execution_count, cast (target_data as XML)
from sys.dm_xe_session_targets xst
inner join sys.dm_xe_sessions xs on (xst.event_session_address = xs.address)
where xs.name = 'spin_lock_backoff'
go

-- Look at spinlocks
SELECT * FROM sys.dm_os_spinlock_stats
ORDER BY backoffs DESC
GO

-- Cleanup
DROP TABLE spinlock_sim
GO
DBCC TRACEOFF (3656, -1)
GO
ALTER EVENT SESSION spin_lock_backoff ON SERVER STATE = STOP
GO
DROP EVENT SESSION spin_lock_backoff ON SERVER
GO

If your pdb is not in the right place you will only see hex values instead of method names (like mine), but you get the idea.  Here's what I captured:

HTH


Why can a select statement acquire more than 1 Sch-S lock on one table?

Your query is creating more than one lock because your derived table is getting executed more than once.

Why the sub-query is being executed more than once is another issue?

Check your execution plan.   its quite possible the Estimated Number of executions on TableImported  matches closely to the Estimated Number of rows for OneOfMyTables
There is some discussion around this here

If you could change the the left join to an inner join, it would only scan (or seek) TableImported once.

As for the difference between Staging and prod... or a solution ¯\_(ツ)_/¯  (sorry)


Issues (Other Than Disk Space) For Using Operating System Partition

For a production solution it's better to put OS, data, log, and tempdb on different volumes, even if those volumes share a single disk, storage pool, or SAN array.
This limits the blast area of running out of space, and provides separate visibility to the different IO types through the Windows Logical Disk performance counters.
However, it works fine, and is absolutely supported to put everything on C.


That is because the storage engine is not using ndbcluster. To address this, we should convert the storage engine. See my another post: Unable to Convert table Engine from MyISAM to NDBCLUSTER


Make sure t3.x is indexed.

If you know one test is much cheaper than another, it may be faster to JOIN them in a manner that makes sense, then use the /*+ORDERED*/ hint to force the optimizer to join them in the order you've written them. This is particularly necessary if your inner query contains REGEX expressions since the optimizer prefers to procrastinate evaluating them.


Create table group sub-folders in Management Studio

There is no way to create custom folders directly in SQL Server Management Studio.

Here are a few common strategies:

Group database objects by schema to help one-dimensional organization
Name objects from broad to specific areas

For the 1st option:

billing.Product
billing.Receipt
internal.Employee
internal.Office

Schemas are most primarily used to logical grouping of database objects, and can also provide an extra layer of security when using more than just the out-of-the-box configuration.  And the secondary benefit is, of course, the grouping of these items within SSMS Object Explorer.

For the 2nd route (name objects from broad to specific), it'd be something like this:

dbo.Animal
dbo.AnimalDiet
dbo.AnimalDietFootType
dbo.AnimalHabitat
dbo.Person
dbo.PersonAddress
dbo.PersonAutomobile
dbo.PersonAutomobileArchive

So on an so forth.  You can see that these objects alphabetically order themselves.  It's not just good for visual organization, but a great naming convention in my opinion.


I have a few observations and suggestions:

Do not create a DATE table when all you need is an element (column) for the date of the POST.  Generally speaking, a table with nothing but a meaningless ID and one other column is a good indication that you need to look hard at whether all you really need is the non-key column.

Consider CATEGORY, this also has just its ID and the category description.  Is this a good candidate for an independent table?  There are two things to think about:

Are there likely to be other attributes that depend on the category?  In other words, should category be normalized out of posts?
Is it possible that categories could exist that have zero posts?  In other words, is the category list something that needs to be managed by an administrator or someone other than the person who creates posts?

On another subject:
Your model indicates that a POST can be written by many USERs.  I'm not sure that this is really possible in your system, since you don't say anything about collaborative authorship.  However, one thing that is almost certainly a problem is that each user can only ever author one post.  I'm sure that's not what you meant.

To fix this you either need to move the foreign key in the relationship between USER and POST to the post side or you need to create an intersection entity if posts can have multiple authors.

Similarly...
Your business rules state that posts can have multiple CATEGORY and TAG assignments, however, your model doesn't reflect this.  In your model each post can have exactly one of each of these.

To fix this you need to implement a many-to-many relationship for each of tags and categories.  This will result in an intersection table in each case.

Lastly:
Why is some of your user information segregated into another table with a many-to-many relationship to the main user table?  There may be good reasons to keep password information in a separate table from other user information, but I don't see any reason stated in your business rules.  Also, it isn't likely that password will be many-to-many with your user record.


LOOP in a stored procedure to calculate sum

No loop needed, you can calculate the sums simply by joining by ID and filtering the date with a BETWEEN.

SELECT
    P.ID,
    SUM(D.CAF)
FROM
    Data AS D
    INNER JOIN Parameter AS P ON D.ID = P.ID
WHERE
    D.SDATE BETWEEN P.SDATE1 AND P.SDATE2
GROUP BY
    P.ID


Different runtimes using parameter vs. constant

Ok,

After further analysis and following the recommendation of  Aaron Bertrand and google "parameter sniffing" i found what is causing this its the SQL Server.

This is the "normal" behavior of the SQL Server, and the queries must be analysed and changed individually to resolve its problems.

Here is the link for the best description of what is happening to me:

Parameter Sniffing

Hope this help, as it help me.

Cheers,

Bigster


SQL Server Stored Procedure - Does Raiserror affect performance

I would not worry about performance in this situation.
I would go with raising an error for 2 reasons:

You quit earlier (when you find out user does not exist), and make a clear message.
IMHO raising an error is more readable and makes your code clearer.


In mysql master-slave Replication which server requests the other?

according to this reference: Get MySQL Replication up and running in 5 minutes, Slave must access master to initialize.
slave> CHANGE MASTER TO MASTER_HOST='192.168.0.31',
 -> MASTER_USER='repl_user',
 -> MASTER_PASSWORD='billy',
 -> MASTER_LOG_FILE='',
 -> MASTER_LOG_POS=4;

So, you can setup slave in localhost and master in remote host with accessible IP and port.
This is a client server communication:


Using pg_dump on a view always produces an empty file

Is there any way of using pg_dump against a view. The postgres
documentation suggests it should be possible

This is not an answer to your question per-se, but the PostgreSQL documentation is certainly very ambiguous there, so I submitted a patch to clarify what will be dumped (in particular, only the definitions of views), which has been accepted and will be reflected in the current documentation soon.
As mentioned in the comments, the only way to extract the data generated by a view is via psql's \copy command, or possibly the COPY command itself.


MySQL Master/Slave replication on same VM instance?

It's possible, why not. You have to run two instances of MySQL. One will be a master, second - a slave.

Check this for detailed instructions https://dev.mysql.com/doc/refman/5.5/en/multiple-servers.html

UPDATE:

Usually I use Vagrant for this. Here's a piece of code to setup a master + slave.

Vagrantfile:

Vagrant.configure(2) do |config|
  config.vm.define "db01" do |db01|
    db01.vm.box = "bento/centos-6.7"
    db01.vm.hostname = "db01"
    db01.vm.provision :shell, path: "bootstrap-master.sh"
    db01.vm.network "private_network", ip: "192.168.50.101"
    db01.vm.synced_folder "../", "/home/vagrant/src/twindb"
  end
  config.vm.define "db02" do |db02|
    db02.vm.box = "bento/centos-6.7"
    db02.vm.hostname = "db02"
    db02.vm.provision :shell, path: "bootstrap-slave.sh"
    db02.vm.network "private_network", ip: "192.168.50.102"
    db02.vm.synced_folder "../", "/home/vagrant/src/twindb"
  end

bootstrap-master.sh

#!/usr/bin/env bash

set -e

hostname="`hostname`"

yum -y install https://dev.mysql.com/get/mysql-community-release-el6-5.noarch.rpm

packages="
mysql-community-server"

yum -y install ${packages}

chkconfig mysqld on

function wait_for_mysql() {
    # wait till mysql starts
    timeout=300
    mysql_started="NO"
    while [ ${timeout} -gt 0 ]
    do
        if ! [ "`mysql -e 'SELECT 1'`" = "1" ]
        then
            echo "SUCCESS"
            break
        fi
        sleep 1
        let timeout=$timeout-1
    done
}
cat <<EOF > /etc/my.cnf
[mysqld]
server_id=101
log_bin=mysqld-bin
EOF

/etc/init.d/mysqld start

if [ "`wait_for_mysql`" = "SUCCESS" ]
then
    mysql -u root -e "RESET MASTER"
    mysql -u root -e "CREATE USER 'replication'@'%' IDENTIFIED BY 'bigs3cret'"
    mysql -u root -e "GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%'"
    mysql -u root -e "GRANT ALL PRIVILEGES ON *.* TO root@'%'";
else
    echo "MySQL failed to start"
    exit -1
fi

bootstrap-slave.sh

#!/usr/bin/env bash

set -e

hostname="`hostname`"

yum -y install https://dev.mysql.com/get/mysql-community-release-el6-5.noarch.rpm

packages="
mysql-community-server"

yum -y install ${packages}

chkconfig mysqld on

function wait_for_mysql() {
    # wait till mysql starts
    timeout=300
    mysql_started="NO"
    while [ ${timeout} -gt 0 ]
    do
        if ! [ "`mysql -e 'SELECT 1'`" = "1" ]
        then
            echo "SUCCESS"
            break
        fi
        sleep 1
        let timeout=$timeout-1
    done
}
cat <<EOF > /etc/my.cnf
[mysqld]
server_id=102
log_bin=mysqld-bin
EOF

/etc/init.d/mysqld start

if [ "`wait_for_mysql`" = "SUCCESS" ]
then
    mysql -u root -e "RESET MASTER"
    mysql -u root -e "CHANGE MASTER TO
        MASTER_HOST='192.168.50.101',
        MASTER_USER='replication',
        MASTER_PASSWORD='bigs3cret',
        MASTER_LOG_FILE='mysqld-bin.000001',
        MASTER_LOG_POS=120"
    mysql -u root -e "START SLAVE"
else
    echo "MySQL failed to start"
    exit -1
fi


To see the view's definition, run one of the following:

SHOW CREATE VIEW viewname\G
SELECT * from information_schema.views where table_name='viewname'\G

Here is a quick example:

mysql> show create table mytable\G
*************************** 1. row ***************************
       Table: mytable
Create Table: CREATE TABLE `mytable` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `age` tinyint(4) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select * from mytable;
+----+-----+
| id | age |
+----+-----+
|  1 |  10 |
|  2 |  15 |
|  3 |  20 |
|  4 |   5 |
+----+-----+
4 rows in set (0.00 sec)

mysql> create view mytable_tens as select * from mytable where MOD(age,10) = 0;
Query OK, 0 rows affected (0.04 sec)

mysql> select * from mytable_tens;
+----+-----+
| id | age |
+----+-----+
|  1 |  10 |
|  3 |  20 |
+----+-----+
2 rows in set (0.00 sec)

mysql> show create view mytable_tens\G
*************************** 1. row ***************************
                View: mytable_tens
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=``@`` SQL SECURITY DEFINER VIEW `mytable_tens` AS select `mytable`.`id` AS `id`,`mytable`.`age` AS `age` from `mytable` where ((`mytable`.`age` % 10) = 0)
character_set_client: latin1
collation_connection: latin1_swedish_ci
1 row in set (0.00 sec)

mysql> select * from information_schema.views where table_name='mytable_tens'\G
*************************** 1. row ***************************
       TABLE_CATALOG: def
        TABLE_SCHEMA: johnlocke
          TABLE_NAME: mytable_tens
     VIEW_DEFINITION: select `johnlocke`.`mytable`.`id` AS `id`,`johnlocke`.`mytable`.`age` AS `age` from `johnlocke`.`mytable` where ((`johnlocke`.`mytable`.`age` % 10) = 0)
        CHECK_OPTION: NONE
        IS_UPDATABLE: YES
             DEFINER: @
       SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: latin1
COLLATION_CONNECTION: latin1_swedish_ci
1 row in set (0.02 sec)

Whichever method you choose, you can visibly see the query making up the view. You can sculpt the CREATE OR REPLACE VIEW using whatever is shown.

Hope this helps, and Welcome to the DBA MinionExchange  !!!


Production (20GB+) Database running slowly and timing out

First you need to figure out what the actual problem is. The sys.dm_os_wait_stats DMV can help with that. Most values in there are accumulative, so you need to capture it a few times  over an extended period to see what is actually going on.

One of the things that this DMV can tell you is if you have RAM pressure. The graphic you posted is basically useless as SQL Server uses all memory it can get (if it needs to). So this is only showing that that mechanism is working.

Once you figured out what your biggest problem is, come back here to get more help.

There are a few wait_types that can be considered noise. You can filter many of those out with this query:

SELECT wait_type,
       wait_time_ms / 1000.0 AS wait_in_sec,
       (wait_time_ms - signal_wait_time_ms) / 1000.0 AS resource_wait_sec,
       signal_wait_time_ms / 1000.0 AS signal_sec,
       waiting_tasks_count AS wait_count,
       100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS percentage
    FROM sys.dm_os_wait_stats
    WHERE wait_type NOT IN (
        'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK',
        'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE',
        'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH',
        'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE',
        'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'BROKER_EVENTHANDLER',
        'TRACEWRITE', 'FT_IFTSHC_MUTEX', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP')
     AND waiting_tasks_count >0
     AND wait_time_ms >0
  ORDER BY wait_time_ms DESC;

(based on http://www.sqlskills.com/blogs/paul/survey-what-is-the-highest-wait-on-your-system/)