Enabling write caching for TempDb data drives

Trace flag 1117 deals with uniform auto-growth across multiple data files(https://www.brentozar.com/archive/2014/06/trace-flags-1117-1118-tempdb-configuration/), while 1118 deals with mixed versus dedicated extents. These trace flags are not limited to use on tempdb, so it will affect your entire instance.

I'd look at this article as well.

As far as write caching goes, dependent on the configuration of your storage array, it will probably delay the writes to disk briefly in cache until they commit to disk (ymmv though, I've seen issues with power outages affecting cache, thus losing the writes before they were committed to disk).

Have you examined the queries to know how they're using tempdb, and if they can be tuned to use it more efficiently?

I'd also look at how your storage layer is architected for your databases.  Do you have tempdb isolated on separate storage than your user database files?

Is the storage latency behavior repeatable each time the query is run?


http://dev.mysql.com/doc/refman/5.6/en/innodb-autocommit-commit-rollback.html

  If a session that has autocommit disabled ends without explicitly committing the final transaction, MySQL rolls back that transaction.

There is a process that monitors active sessions. If any are found to have disconnected, the sessions are cleaned up. This includes rollback of uncommitted transactions.


I'm assuming that you want to create this extension in another server. I'm guessing that the server didn't get the "postgresql contrib" package installed. This package contains the "standard" extensions that are available from the PostgreSQL source code.

I think you need to get the system administrator to install the required package for your OS.

For Debian and Ubuntu it's the "postgresql-contrib-9.1" package, for other platforms it has a similar name.


Creating a single recid stream for multiple tables

You don't need a "reservoir", rather a way to generate a unique number.

Usually it is sufficient to give that task to one table you already have, and use AUTO_INCREMENT there, plus LAST_INSERT_ID().

But it you really need a sequence generator:

http://forums.mysql.com/read.php?10,616636,617041 (Simulating sequences)
http://dev.mysql.com/doc/refman/5.7/en/information-functions.html (search for "sequence")
http://forums.mysql.com/read.php?10,252807,262654 (A sequence solution)


While I don't do a lot of work in MySQL, at the least I can suggest a simplification of the query. In the FROM clause of the main query, JOIN the target tables directly, instead of making a subquery from each of the tables to join:

FROM
    caf_student_cards AS caf
    LEFT JOIN students AS student ON caf.user_id = student.user_id
    LEFT JOIN utilizations AS util ON caf.user_id = util.user_id
    LEFT JOIN system_users AS users ON caf.user_id = users.user_id

NOTE: You'll lose your renaming of util.resource_id, so you'll also need to change:

    util.room,

to:

    util.resource_id as room,

I can't think of any potential benefits of doing things the way you have (certainly not when your subquery is doing a SELECT *), and I've seen references that lead me to believe MySQL may be materializing the subqueries (meaning it's creating a copy of the table with the columns you list), which is likely to cause unnecessary extra work and resource consumption.


Is it require to fill all tables inside of the database?

BAD PRACTICE ALERT!

You need to delimit each string. Not the whole lot.

Note where the single quotes are:

("Insert Into dbo.PrnInf(PrnName, PrnSurName, PrnEgn )     Values
('"+txtPrnName.Text+"', '"+txtPrnSurName.Text+"','"+txtPrnEgn.Text+"')", conn);

However, this can lead to SQL Injection. So...

GOOD PRACTICE!

Parameterise your query

SqlCommand cmd = new SqlCommand
    ("Insert Into dbo.PrnInf(PrnName, PrnSurName, PrnEgn )     Values
    (@PrnName, @PrnSurName, @PrnEgn)", conn);
cmd.Parameters.AddWithValue("@PrnName", txtPrnName.Text);
cmd.Parameters.AddWithValue("@PrnSurName", txtPrnSurName.Text);
cmd.Parameters.AddWithValue("@PrnEgn", txtPrnEgn.Text);
cmd.ExecuteNonQuery();


Does the length of the fields play a role in index access in MS-SQL?

Length of the fields does not determine if the index is used for a query in SQL Server. But it could affect how performant that index is when it is used to serve the data. It's the same idea why indexing a UUID field is a little less performant than indexing an INT, one reason being the difference in the number of bytes that the values use when indexed.
When and how an index is used is solely based on which columns and their order in how you define in the index, the columns you use in your predicates (JOIN, WHERE, HAVING clauses), the current statistics on those columns at the time you run your query, and how you are comparing those columns in the predicates (e.g. in your linked thread you're using a function in one of your predicates which likely is preventing an index seek from happening and instead an index scan occurs).
Things like using functions in predicates, or type conversions when comparing two different data types could cause a sub-optimal plan, and definitely cause performance problems like cardinality estimate issues.


WHERE attribute having two relation lines in the same SQL

Use a subquery that has the checklist id that has both tags

SELECT
    B.id checklist_id,
    B.name checklist_name,
    D.name tag_name
FROM
(
    SELECT AA.checklistId,COUNT(1) tagcount
    FROM checklist_tag AA
    INNER JOIN tag BB ON AA.tagId = BB.id
    WHERE BB.name IN ('tag 1', 'tag 2')
    GROUP BY AA.checklistId HAVING COUNT(1)=2
) A
INNER JOIN checklist B     ON A.checklistId = B.id
INNER JOIN checklist_tag C ON A.checklistId = C.checklist_id
INNER JOIN tag D           ON C.tag_id = D.id;


How to copy database from server to local machine in SQL Server Management studio?

You probably want a local development copy of the database so that your queries and schema changes don't affect production data. Please check with your management first to make sure you don't have policies against developers having access to (or their own copies of) production data, which may be sensitive for a variety of reasons depending on your industry.

Assuming you are okay to do so, back up the database from the server, and restore it on your workstation. Make sure to use COPY_ONLY for the backup, to avoid interfering with any existing backup/recovery plans.

BACKUP DATABASE dbname TO DISK = 'E:\somefolder\file.bak'
  WITH INIT, COPY_ONLY;

Copy that file from the server to your machine.

Then restore it on your machine; you will likely need to use WITH MOVE in order to put the files in a valid location:

RESTORE DATABASE dbname FROM DISK = 'C:\temp\file.bak'
  WITH REPLACE, RECOVERY,
  MOVE 'dbname_data' TO 'C:\...\dbname.mdf',
  MOVE 'dbname_log' TO 'C:\...\dbname.ldf';


PostgreSQL: create user who can create new roles?

https://www.postgresql.org/docs/9.5/static/sql-createrole.html

  Be careful with the CREATEROLE privilege. There is no concept of inheritance for the privileges of a CREATEROLE-role. That means that even if a role does not have a certain privilege but is allowed to create other roles, it can easily create another role with different privileges than its own (except for creating roles with superuser privileges). For example, if the role "user" has the CREATEROLE privilege but not the CREATEDB privilege, nonetheless it can create a new role with the CREATEDB privilege. Therefore, regard roles that have the CREATEROLE privilege as almost-superuser-roles.


If else with 5 possible cases PostgreSQL

Despite the long comments, it's still not clear if you need one query for each case or a single query that includes all five of them. So, we'll take them separately:

config->'external_http_port' is null

select * from cameras
where config->'external_http_port' is null ;

config->'external_host' is null
(omitting the select):

where config->'external_host' is null ;

config->'snapshots'->>'jpg' is null

where config->'snapshots'->>'jpg' is null ;

all three are null (you got that right!):

where config->'external_http_port' is null
  and config->'external_host' is null
  and config->'snapshots'->>'jpg' is null ;

two out of three are are null (and the third isn't)

where (config->'external_http_port' is null)::int
      + (config->'external_host' is null)::int
      + (config->'snapshots'->>'jpg' is null)::int
      = 2 ;

And if you want all five cases in one query, i.e. any of the five conditions to hold, this can be written (using a complicated condition, similar to the 5th case above):

    where (config->'external_http_port' is null)::int
          + (config->'external_host' is null)::int
          + (config->'snapshots'->>'jpg' is null)::int
          >= 1 ;

or more simply:

    where config->'external_http_port' is null
       or config->'external_host' is null
       or config->'snapshots'->>'jpg' is null ;


How do you select the rolling, most recent value for each person for each month in a range where that most recent value is less than 6 months old?

2015-11-22 is not older than 6 months compared to 2015-05-01 unless you truncate the response date to month also.

> select '2015-11-22'::date + interval '6 months';
+---------------------+
| ?column?            |
|---------------------|
| 2016-05-22 00:00:00 |
+---------------------+

> select date_trunc('month', '2015-11-22'::date) + interval '6 months';
+---------------------------+
| ?column?                  |
|---------------------------|
| 2016-05-01 00:00:00+02:00 |
+---------------------------+

So depending the logic what you consider expired the query changes a bit. I've included both version. In comment the date and active condition as per the example.

Using Lateral join:

with users as (
    -- I assume you have user table so this can be omitted
    -- first and last are used to limit the join
    select user_id, min(date) as first, max(date) as last from responses group by user_id
), boundaries as (
    select
        date_trunc('month', min(date)) as low
        --, date_trunc('month', max(date)) as high
        -- If you want to use the high in generate_series as upper boundary
    from responses
)
select user_id, tick as "date", score
from
    generate_series(
        (select low from boundaries),
        date_trunc('month', current_date + interval '1 month'), interval '1 month'
    ) as tick
join users on tick.tick between users.first and users.last + interval '6 months'
-- # If you omit the users CTE and you have a users table
-- cross join users
left outer join lateral (
    select score from responses
    where users.user_id = responses.user_id
    -- # Proper 6 months calculation
    --and responses.date between tick.tick - interval '6 months' and tick.tick
    -- # As example showed
    and responses.date < tick.tick and date_trunc('month', responses.date) + interval '6 months' > tick.tick
    order by responses.date desc limit 1
) a on true
order by 1, 2;

Using Window functions:

with boundaries as (
    select
        date_trunc('month', min(date)) as low
    from responses
)
select distinct responses.user_id, tick as "date", first_value(score) over (partition by responses.user_id, tick.tick order by responses.date desc)
from
    generate_series(
        (select low from boundaries),
        date_trunc('month', current_date + interval '1 month'), interval '1 month'
    ) as tick
join responses on (
    -- # Proper 6 months calculation
    -- responses.date between tick.tick - interval '6 months' and tick.tick
    -- # As it was in the example
    responses.date < tick.tick and
    date_trunc('month', responses.date) > tick.tick - interval '6 months'
)
order by 1, 2;


Performance of Non Clustered Indexes on Heaps vs Clustered Indexes

To check your request I created 2 tables following this scheme:

7.9 million records representing balance information.
an identity field counting from 1 to 7.9 million
a number field grouping the records in about 500k groups.

The first table called heap got a non clustered index on the field group. The second table called clust got a clustered index on the sequential field called key and a nonclustered index on the field group

The tests were run on an I5 M540 processor with 2 hyperthreaded cores, 4Gb memory and 64-bit windows 7.

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
Apr  2 2010 15:48:46
Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

  Update on 9 Mar 2011: I did a second more extensive benchmark by running the following .net code and logging Duration, CPU, Reads, Writes and RowCounts in Sql Server Profiler. (The CommandText used will be mentioned in the results.)

  NOTE: CPU and Duration are expressed in milliseconds

  1000 queries
  zero CPU queries are eliminated from the results
  0 rows affected are eliminated from the results

int[] idList = new int[] { 6816588, 7086702, 6498815 ... }; // 1000 values here.
using (var conn = new SqlConnection(@"Data Source=myserver;Initial Catalog=mydb;Integrated Security=SSPI;"))
            {
                conn.Open();
                using (var cmd = new SqlCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "select * from heap where common_key between @id and @id+1000";
                    cmd.Parameters.Add("@id", SqlDbType.Int);
                    cmd.Prepare();
                    foreach (int id in idList)
                    {
                        cmd.Parameters[0].Value = id;

                        using (var reader = cmd.ExecuteReader())
                        {
                            int count = 0;
                            while (reader.Read())
                            {
                                count++;
                            }
                            Console.WriteLine(String.Format("key: {0} => {1} rows", id, count));
                        }
                    }
                }
            }

  End of Update on 9 Mar 2011.

SELECT performance

To check performanc numbers I performed the following queries once on the heap table and once on the clust table:

select * from heap/clust where group between 5678910 and 5679410
select * from heap/clust where group between 6234567 and 6234967
select * from heap/clust where group between 6455429 and 6455729
select * from heap/clust where group between 6655429 and 6655729
select * from heap/clust where group between 6955429 and 6955729
select * from heap/clust where group between 7195542 and 7155729

The results of this benchmark are for the heap:

rows  reads CPU   Elapsed
----- ----- ----- --------
1503  1510  31ms  309ms
401   405   15ms  283ms
2700  2709  0ms   472ms
0     3     0ms   30ms
2953  2962  32ms  257ms
0     0     0ms   0ms

  Update on 9 Mar 2011:
   cmd.CommandText = "select * from heap where group between @id and @id+1000";

  721 Rows have > 0 CPU and affect more than 0 rows

Counter   Minimum    Maximum Average  Weighted
--------- ------- ---------- ------- ---------
RowCounts    1001      69788    6368         -
Cpu            15        374      37   0.00754
Reads        1069      91459    7682   1.20155
Writes          0          0       0   0.00000
Duration   0.3716   282.4850 10.3672   0.00180

  End of Update on 9 Mar 2011.

for the table clust the results are:

rows  reads CPU   Elapsed
----- ----- ----- --------
1503  4827  31ms  327ms
401   1241  0ms   242ms
2700  8372  0ms   410ms
0     3     0ms   0ms
2953  9060  47ms  213ms
0     0     0ms   0ms

  Update on 9 Mar 2011:
   cmd.CommandText = "select * from clust where group between @id and @id+1000";

  721 Rows have > 0 CPU and affect more than 0 rows

Counter   Minimum    Maximum Average  Weighted
--------- ------- ---------- ------- ---------
RowCounts    1001      69788    6056         -
Cpu            15        468      38   0.00782
Reads        3194     227018   20457   3.37618
Writes          0          0       0       0.0
Duration   0.3949   159.6223 11.5699   0.00214

  End of Update on 9 Mar 2011.

SELECT WITH JOIN performance

cmd.CommandText = "select * from heap/clust h join keys k on h.group = k.group where h.group between @id and @id+1000";

The results of this benchmark are for the heap:

873 Rows have > 0 CPU and affect more than 0 rows

Counter   Minimum    Maximum Average  Weighted
--------- ------- ---------- ------- ---------
RowCounts    1009       4170    1683         -
Cpu            15         47      18   0.01175
Reads        2145       5518    2867   1.79246
Writes          0          0       0   0.00000
Duration   0.8215   131.9583  1.9095   0.00123

The results of this benchmark are for the clust:

865 Rows have > 0 CPU and affect more than 0 rows

Counter   Minimum    Maximum Average  Weighted
--------- ------- ---------- ------- ---------
RowCounts    1000       4143    1685         -
Cpu            15         47      18   0.01193
Reads        5320      18690    8237   4.97813
Writes          0          0       0   0.00000
Duration   0.9699    20.3217  1.7934   0.00109

UPDATE performance

The second batch of queries are update statements:

update heap/clust set amount = amount + 0 where group between 5678910 and 5679410
update heap/clust set amount = amount + 0 where group between 6234567 and 6234967
update heap/clust set amount = amount + 0 where group between 6455429 and 6455729
update heap/clust set amount = amount + 0 where group between 6655429 and 6655729
update heap/clust set amount = amount + 0 where group between 6955429 and 6955729
update heap/clust set amount = amount + 0 where group between 7195542 and 7155729

the results of this benchmark for the heap:

rows  reads CPU   Elapsed
----- ----- ----- --------
1503  3013  31ms  175ms
401   806   0ms   22ms
2700  5409  47ms  100ms
0     3     0ms   0ms
2953  5915  31ms  88ms
0     0     0ms   0ms

  Update on 9 Mar 2011:
   cmd.CommandText = "update heap set amount = amount + @id where group between @id and @id+1000";

  811 Rows have > 0 CPU and affect more than 0 rows

Counter   Minimum    Maximum Average  Weighted
--------- ------- ---------- ------- ---------
RowCounts    1001      69788    5598       811
Cpu            15        873      56   0.01199
Reads        2080     167593   11809   2.11217
Writes          0       1687     121   0.02170
Duration   0.6705   514.5347 17.2041   0.00344

  End of Update on 9 Mar 2011.

the results of this benchmark for the clust:

rows  reads CPU   Elapsed
----- ----- ----- --------
1503  9126  16ms  35ms
401   2444  0ms   4ms
2700  16385 31ms  54ms
0     3     0ms   0ms
2953  17919 31ms  35ms
0     0     0ms   0ms

  Update on 9 Mar 2011:
   cmd.CommandText = "update clust set amount = amount + @id where group between @id and @id+1000";

  853 Rows have > 0 CPU and affect more than 0 rows

Counter   Minimum    Maximum Average  Weighted
--------- ------- ---------- ------- ---------
RowCounts    1001      69788    5420         -
Cpu            15        594      50   0.01073
Reads        6226     432237   33597   6.20450
Writes          0       1730     110   0.01971
Duration   0.9134   193.7685  8.2919   0.00155

  End of Update on 9 Mar 2011.

DELETE benchmarks

the third batch of queries I ran are delete statements

delete heap/clust where group between 5678910 and 5679410
delete heap/clust where group between 6234567 and 6234967
delete heap/clust where group between 6455429 and 6455729
delete heap/clust where group between 6655429 and 6655729
delete heap/clust where group between 6955429 and 6955729
delete heap/clust where group between 7195542 and 7155729

The result of this benchmark for the heap:

rows  reads CPU   Elapsed
----- ----- ----- --------
1503  10630 62ms  179ms
401   2838  0ms   26ms
2700  19077 47ms  87ms
0     4     0ms   0ms
2953  20865 62ms  196ms
0     4     0ms   9ms

  Update on 9 Mar 2011:
   cmd.CommandText = "delete heap where group between @id and @id+1000";

  724 Rows have > 0 CPU and affect more than 0 rows

Counter   Minimum    Maximum Average  Weighted
--------- ------- ---------- ------- ---------
RowCounts     192      69788    4781         -
Cpu            15        499      45   0.01247
Reads         841     307958   20987   4.37880
Writes          2       1819     127   0.02648
Duration   0.3775  1534.3383 17.2412   0.00349

  End of Update on 9 Mar 2011.

the result of this benchmark for the clust:

rows  reads CPU   Elapsed
----- ----- ----- --------
1503  9228  16ms  55ms
401   3681  0ms   50ms
2700  24644 46ms  79ms
0     3     0ms   0ms
2953  26955 47ms  92ms
0     3     0ms   0ms

  Update on 9 Mar 2011:

  cmd.CommandText = "delete clust where group between @id and @id+1000";

  751 Rows have > 0 CPU and affect more than 0 rows

Counter   Minimum    Maximum Average  Weighted
--------- ------- ---------- ------- ---------
RowCounts     144      69788    4648         -
Cpu            15        764      56   0.01538
Reads         989     458467   30207   6.48490
Writes          2       1830     127   0.02694
Duration   0.2938  2512.1968 24.3714   0.00555

  End of Update on 9 Mar 2011.

INSERT benchmarks

The last part of the benchmark is the execution of insert statements.

insert into heap/clust (...)
 values (...),
        (...),
        (...),
        (...),
        (...),
        (...)

The result of this benchmark for the heap:

rows  reads CPU   Elapsed
----- ----- ----- --------
6     38    0ms   31ms

  Update on 9 Mar 2011:

string str = @"insert into heap (group, currency, year, period, domain_id, mtdAmount, mtdAmount, ytdAmount, amount, ytd_restated, restated, auditDate, auditUser)
                    values";

                    for (int x = 0; x < 999; x++)
                    {
                        str += string.Format(@"(@id + {0}, 'EUR', 2012, 2, 0, 100, 100, 1000 + @id,1000, 1000,1000, current_timestamp, 'test'),  ", x);
                    }
                    str += string.Format(@"(@id, 'CAD', 2012, 2, 0, 100, 100, 1000 + @id,1000, 1000,1000, current_timestamp, 'test') ", 1000);

                    cmd.CommandText = str;

  912 statements have > 0 CPU

Counter   Minimum    Maximum Average  Weighted
--------- ------- ---------- ------- ---------
RowCounts    1000       1000    1000         -
Cpu            15       2138      25   0.02500
Reads        5212       7069    6328   6.32837
Writes         16         34      22   0.02222
Duration   1.6336   293.2132  4.4009   0.00440

  End of Update on 9 Mar 2011.

The result of this benchmark for the clust:

rows  reads CPU   Elapsed
----- ----- ----- --------
6     50    0ms   18ms

  Update on 9 Mar 2011:

string str = @"insert into clust (group, currency, year, period, domain_id, mtdAmount, mtdAmount, ytdAmount, amount, ytd_restated, restated, auditDate, auditUser)
                    values";

                    for (int x = 0; x < 999; x++)
                    {
                        str += string.Format(@"(@id + {0}, 'EUR', 2012, 2, 0, 100, 100, 1000 + @id,1000, 1000,1000, current_timestamp, 'test'),  ", x);
                    }
                    str += string.Format(@"(@id, 'CAD', 2012, 2, 0, 100, 100, 1000 + @id,1000, 1000,1000, current_timestamp, 'test') ", 1000);

                    cmd.CommandText = str;

  946 statements have > 0 CPU

Counter   Minimum    Maximum Average  Weighted
--------- ------- ---------- ------- ---------
RowCounts    1000       1000    1000         -
Cpu            15       2403      21   0.02157
Reads        6810       8997    8412   8.41223
Writes         16         25      19   0.01942
Duration   1.5375   268.2571  6.1463   0.00614

  End of Update on 9 Mar 2011.

Conclusions

Although there are more logical reads going on when accessing the table with the clustered & the nonclustered index (while using the nonclustered index) the performance results are:

SELECT statements are comparable
UPDATE statements are faster with a clustered index in place
DELETE statements are faster with a clustered index in place
INSERT statements are faster with a clustered index in place

Of course my benchmark was very limited on a specific kind of table and with a very limited set of queries, but I think that based on this information we can already start saying that it is virtually always better to create a clustered index on your table.

  Update on 9 Mar 2011:

As we can see from the added results, the conclusions on the limited tests were not correct in every case.

The results now indicate that the only statements which benefit from the clustered index are the update statements. The other statements are about 30% slower on the table with clustered index.

Some additional charts where I plotted the weighted duration per query for heap vs clust.

As you can see the performance profile for the insert statements is quite interesting. The spikes are caused by a few data points which take a lot longer to complete.

  End of Update on 9 Mar 2011.


ODBC can't connect to internal MySQL without external network

For each new client connection, the server uses the client IP address to check whether the client host name is in the host cache. If not, the server attempts to resolve the host name. First, it resolves the IP address to a host name and resolves that host name back to an IP address. Then it compares the result to the original IP address to ensure that they are the same.

  https://dev.mysql.com/doc/refman/5.7/en/host-cache.html

(Docs here are 5.x, but this behavior is present pre-5.x).

Assuming loss of the external network also means the MySQL server can no longer reach the DNS server, then the lack of timely DNS responses will stall the handshaking logic until they get back some kind of timeout error... by which point, ODBC has already given up and thrown this error.

The solution is to disable the reverse DNS lookups with each client connection by starting the server using --skip-name-resolve.  Unless you have users in the mysql.user table with actual hostnames in the Host column, the default behavior is of no real value.


Migrating Data from SQL Server 2005 to 2016

Max gave a decent answer which I will upvote once I'm done typing this alternate view up.

I am not a fan of restoring system databases when doing an upgrade migration and I prefer doing migrations over in place upgrades as I discussed in this lengthy answer to another question.

Basically I like to start "fresh" when I do a migration. I find that playing with system database migrations and upgrades through restore to sometimes cause frustrations with the restores and it can carry over potential sins.

You also asked about indexes, stored procedures, views. Those items at the database level should all live inside of a user database. So when you restore database X to the new server, all of the database objects (Tables, Users, Views, Procs, Functions, etc) will be there as well.

What exist in the system databases are jobs, logins, alerts, linked servers, encryption keys, etc. Instance level items.

I like to review those and migrate over what I need using various scripts - lately that is the DBATools.Io powershell scripts. I like using their script to copy sql logins especially, because it handles the SQL authenticated users keeping their passwords and security identifiers the same so the database users from those logins will work. They also have an entire SQL Server migration command which runs their sub commands to copy over the items I typically would copy over.

I do not believe Max to be wrong with that answer  hence the upvote. I just have had more success and more luck and feel more comfortable migrating to new instead of trying to restore over system databases between versions. I would say that I've honestly can't remember the last time I did a version upgrade migration and didn't do it this way instead of restoring the system databases.


creating trigger which will insert into table from view

SQL*Developer

SQL*Developer has a bad habit of interpreting the :new and :old identifiers of a trigger as run-time BIND variables.  Usually, a window will pop up asking for a value for the BIND variable new and/or old.

Workaround

One workaround is to edit the TRIGGER directly by going trough Connections.

A simple "recompile" works.


The reason the performance is better is that the smaller data types have much less working set - see the execution plan (http://www.red-gate.com/our-company/about/book-store/assets/sql-server-execution-plans.pdf)

I expect you get the largest benefit from the nvarchar(4000) to nvarchar(50) - that's a reduction of 80x - and nvarchar(4000) can use up to 8K! of space.  For a key, that is not a good idea.

In addition, the fact that there are no foreign keys probably mean you don't have a very good indexing strategy either.  If you did have indexes (even for ridiculously large columns), you would probably find they could outperform the cast since it probably wouldn't spool as much.

In general, you don't want any operations on your keys in the join if at all possible, especially for large data sets.


Why does PostgreSQL ignore auto_explain.log_analyze = 0 in is postgresql.conf file and how can I turn on auto_explain for all sessions?

Hmm, in my desperation I further remembered I could try this:
ALTER SYSTEM auto_explain.log_min_duration = '0s';

and it would accept it. It would also recognize if I am using bogus values. Hm, apparently now also acted on it. Is there some sort of stored settings from ALTER SYSTEM that always overrides what postgresql.conf contains? That could be a reasonable answer.
I noticed more strange behavior that after a restart now it does work initially. The ALTER SYSTEM does not need to be invoked again. But it takes some time to begin the auto_explain logging, the very first command was not logged, but then it was.


Move local SQL Server DB to hosted SQL Server

First, you need to find out from the hosted provider the username/password of a user with either the sysadmin fixed role, or at minimum CREATE DATABASE rights (intermediate: dbcreator fixed role).  This will let you run the restore, if SQL can access the .bak files.

Second, you need to change ALL the passwords for ALL the users on your hosted SQL Server.  Long (25+ character) random passwords stored in KeePass or similar are ideal.

Next, you need to remove all domain users from the restored database; they're worthless now.  You need to change your apps - whatever uses the database - to use the new SQL user and password rather than whatever was previously used.

Next, configure backups on the hosted site.

Next, RESTORE a backup from the hosted site - make sure it works!!!

Optionally, configure the hosted instance to force SSL connections; talk to your hosting provider about certificate generation and installation, then use sys.dm_exec_connections.encrypt to validate (it does take a service restart).

Optionally, figure out how to pull backups from the hosted site local, in case the hosted site has serious issues (possibly legal issues, for instance) and you lose everything there.


Is there a practical limit on the number of PostgreSQL TYPES?

Practical, no. All internal types are represented by an oid. That's an unsigned four-byte integer.

SELECT oid, *
FROM pg_type;

PostgreSQL uses OIDs as an internally unique ID. When you exhaust them you're out.

Creating 1,000 types: NOT A PROBLEM.
Creating 4.3 billion types: PROBLEM.

Update

As a side note, I don't see how you can expose the types usefully to a user. PostgreSQL has a type jsonb. It's indexable and stored as binary, like Mongo's BSON. I would be using that.


These are likely left over from Microsoft's Database Tuning Advisor. You should be able to get information about the sessions with this stored procedure:

EXEC sp_DTA_help_session

After identifying the sessions that you do not need you can remove them with this stored procedure supplying the session id using one as an example:

EXEC sp_DTA_delete_session 1


When SELECTing an expression that is an expression based index does the expression get processed?

No, not currently for that example, for a few reasons.

First, the planner doesn't even entertain indexes unless they seem potentially useful to a WHERE or JOIN condition.  (This is to reduce planning time.) Since your example doesn't have either of those, it doesn't even consider the index.  You could fix this by adding where (first_name || ' ' || last_name) is not null to your query.

Second, the planner thinks it needs the inputs to the expression (even though it doesn't), and unless the inputs are present it won't use the index for an index-only scan.  You could fix this by adding the expression inputs to the index ON people ((first_name || ' ' || last_name),first_name, last_name) but that is certainly an unattractive work-around.

Finally, it might not realize that it can avoid recomputing the expression if the query is complex (although that isn't a problem for your example):

  An additional caveat, if the goal is to avoid recalculating f(x), is
  that the planner won't necessarily match uses of f(x) that aren't in
  indexable WHERE clauses to the index column. It will usually get this
  right in simple queries such as shown above, but not in queries that
  involve joins. These deficiencies may be remedied in future versions
  of PostgreSQL.


Index isn't used when value is a result of a subquery or from a wrapper query

Change the order of the columns in the index to support the B-tree search for the bank_account_id and filtering by type. Once filtered, all records are read ordered by id. By including the created_at column last, you have all the data the query needs in the index and it won't access the table at all.
CREATE INDEX "nc_bank_account_events_bank_account_id"
ON bank_account_events (bank_account_id, type, id, created_at);


What is effective way to delete duplicate records based on multiple columns in MySQL?

Consider enforcing this combination of columns as a unique constraint at the database level. That won't be possible until the existing duplicates are cleaned out. But it will help prevent this problem from creeping back into the data.

You should define the 4 columns as a non-unique index now since this process will involving lots of comparisons using them. You should define the 4 columns as a single index listing them in the same order you plan to list them in your query. 70M records is gonna take a long time to query. But with an index on the column in question it may drop to a feasible time frame. And you may end up needing to use FORCE INDEX anyway.

https://www.percona.com/blog/2014/01/03/multiple-column-index-vs-multiple-indexes-with-mysql-56/

Is there a preferred strategy in terms of picking the version of the record to keep? Assuming you want to keep the most recent on file, for example, you can use a date column (I'll call it cdate) or the value of an auto-increment column (I'll call it id).

You may want to create a new table users_test or something similar using an otherwise unchanged CREATE TABLE statement. Then shunt over a more manageable portion of your data to experiment on there. Especially if you already know a few of the duplicate records so that you can be sure to include them. Then you can start out with something like this:

SELECT
    t1.id,
    t2.id,
    t1.cdate,
    t2.cdate,
    t1.email,
    t2.email,
    t1.alt_email,
    t2.alt_email,
    t1.mobile,
    t2.mobile,
    t1.alt_mobile,
    t2.alt_mobile
    # Include whatever other columns may be helpful
FROM
    # Or users_test if it exists
    users t1
    JOIN users t2 ON (
        # We want t2 to always contain a duplicate but older record
        t1.cdate > t2.cdate
        AND
        t1.email = t2.email
        AND
        t1.alt_email = t2.alt_email
        AND
        t1.mobile = t2.mobile
        AND
        t1.alt_mobile = t2.alt_mobile
    )
;

Sanity check the results. You want to make damn sure your data matches your expectations before running a DELETE statement in production. But once you are happy with the results you can safely collect t2.id values from that query. Those will all be records with a newer duplicate row.

If you don't have a date to compare use id. If you would rather keep the oldest record on file switch the > to an <. Or you may have a completely different strategy in mind for picking the version of the record to keep. That's left as an exercise for the reader.

Adding an index on these columns will slow down writes. So if it is vitally important that writes to the users table in production are super fast then you may not be able to define the index (unique or not) in production. But with this method you can still dump the whole thing to a staging environment, set up the index there, collect the IDs, and then use just the ID list in DELETE statements in production.

DELETE FROM users WHERE id in (/* list of t2.id values here */);

Personally I find slightly slower writes a small price to pay for the potential read boost and data sanitation that comes along with defining natural keys as UNIQUE constraints. But I've also never dealt with a project with 70M rows in the users table. My experiences may not mean much for your particular situation.

If this proves unworkable I can think of two other strategies worth investigating.

Use a cloned table similar to user_test discussed above. Move some much smaller subset of user records into it. Join the two different tables rather than the self join on users. Comparing 10k rows to 70M rows should be much faster than comparing 70M to 70M. But checking all the records will require running 7,000+ iterations. You'd want to use the full users table in the t2 position. And you could potentially pick up some optimizations depending on how you chunk your rows into the table for comparison.

Or you could rename your existing users table to something like users_old. Then you can create a new users table with the unique constraint in place across those 4 columns. Then use a combination of INSERT INTO … SELECT … and INSERT … ON DUPLICATE KEY UPDATE … statements to populate the new table using values from the old table.

INSERT INTO users
SELECT * FROM users_old
ON DUPLICATE KEY UPDATE
some_column = VALUES(some_column),
some_other_column = VALUES(some_other_column), …
;

You can leave the columns from the UNIQUE constraint out of your UPDATE clause because you know they wouldn't change anyway. And this strategy goes out the window is enforcing a UNIQUE constraint in production isn't an option for you.


High Availability for MariaDB and Postgres : replication or active-passive (SAN)

(Relating to MySQL only...)

In my opinion, a Galera-based solution is the best HA available today.  MariaDB includes Galera in the latest releases; PXC also has it; or you can tack Galera onto Oracle's MySQL.

"Replication" is an integral part of Galera, but there are low-level differences
Automatically recovers from single-point-of-failure
3 node (server) minimum
Uses quorum voting to discover what has failed, and avoid "split-brain"
If the 3 nodes are in 3 different datacenters, no single natural disaster can take out the 'system'
Read scaling (multiple nodes providing such)

Load balancing, proxying requests, etc, are related topics.


Can I exchange partition between two table with partition with same name?

Have you seen this?  https://community.oracle.com/thread/889338?start=0&tstart=0

It states you can only exchange partitions from a partitioned table to a non-partitioned table, or vice-versa.

You'd need to temporarily exchange the partition into an
interim, non-partitioned, table, then into the target partitioned table.

There are some interesting wrinkles to the broadly stated rule above; see the following excerpt taken from the Oracle documentation:

  exchange_partition_subpart

  Use the EXCHANGE PARTITION or EXCHANGE SUBPARTITION clause to exchange the data and index segments of:

  One nonpartitioned table with:

  one range, list, or hash partition
  one range, list, or hash subpartition

  One range-partitioned table with the range subpartitions of a range-range or list-range composite-partitioned table partition
  One hash-partitioned table with the hash subpartitions of a range-hash or list-hash composite-partitioned table partition
  One list-partitioned table with the list subpartitions of a range-list or hash-list composite-partitioned table partition

  In all cases, the structure of the table and the partition or subpartition being exchanged, including their partitioning keys, must be identical. In the case of list partitions and subpartitions, the corresponding value lists must also match.


VS 2012 SSIS Data Flow Designer Source Assistant Error: [SSIS.Pipeline]: Component ", clsid {4CAC ... Could not be created

Repairing and/or Re-installing Microsoft Connection Manager for Oracle by Attunity, VS 2012, and SSDT 2012 followed by a reboot enabled me to finally see the Oracle Source and Oracle Destination SSIS components in the SSIS Toolbox window under the Common category.

While using Source Assistant component still causes the same error, dragging over an Oracle Source component succeeds.  Configuring the component to set the Connection Manager, Data Access mode ("Table" in my case) and "Name of the Table" work great now.

Clicking on the Preview button proved it's working by showing me actual data from my Oracle Source database.


Is using a JSON data type here appropriate?

Simply no, with a single index on UserID you can easy and fast request the groups and delete also simlpy user from  groups.

That is all posible with json, but needs much more code and so can cause much more problems.

As long you don't need it in your application and also dont change it much, json is good to store a lot of data, but that is xml also.


SHOW GRANTS only gives you back whatever you are connected as, which was root@localhost.
root@localhost and root@'%' and completely different users. Just do
SHOW GRANTS FOR root@localhost;
SHOW GRANTS FOR root@'%';

root@localhost lets you connect from the DB Server via mysql.sock (the socket file)
root@'%' lets you connect via TCP/IP, but you must explicitly connect with that protocol. Otherwise, mysqld connects you as root@localhost. This is true even if you attempted to connect using 127.0.0.1.

SUGGESTION #1
For the sake of security, you should use netblocks instead of '%'. For example, if you web servers at 10.1.2.20, 10.1.2.30, and 10.1.2.40, you should create
GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.1.2.%'
IDENTIFIED BY PASSWORD '*fdfgdgdggfggfgfg' WITH GRANT OPTION;

instead of having a remote root.
SUGGESTION #2
If you really want the remote root, here is what you do
UPDATE mysql.user SET host='%'
WHERE user='root' AND host='localhost';
FLUSH PRIVILEGES;

That will do what you want, but remote root is not recommended.
SUGGESTION #3
Since root@'%' already exists, then remove the root@localhost.
DELETE FROM mysql.user WHERE user='root' AND host='localhost';
FLUSH PRIVILEGES;

This will leave root@'%' as the only root user, but you must connect explicitly with TCP/IP.


Error when installing Oracle 11.2.0.3 in Windows Server 2008 R2 64 bits

Did you uncompressed each zip file into its own folder in C:\Software\Oracle 11.2.0.3 64 bits\oracle\ or into the same folder there?  Almost every reference I can find indicates that this problem is caused by the files not being extracted into the same folder.

http://shethedba.wordpress.com/2009/02/02/11g-installation-error-message-file-not-found-wfmlrsvcappear/
http://www.oraerp.com/Thread-WFMLRSVCApp-ear-file-missing-11g-Installation
https://forums.oracle.com/forums/thread.jspa?threadID=937887
https://stackoverflow.com/questions/11547101/wfmlrsvcapp-ear-file-not-found
http://lucatarrini.net/?p=230

If you have access to My Oracle Support, there is an article covering this topic - 468771.1.


What's the difference between a cursor and a trigger?

Generally, a cursor is used to iterate through a result set that was returned by a SELECT statement.

Cursors can be used in stored procedures, stored functions, and triggers.

A trigger is a stored program executed automatically to respond to a specific event like an insert, update, or delete.

As you can see, these are very different concepts and have very different purposes.

I suggest you review these links for the basics on both of these concepts:
Triggers, Cursors


Importing huge table exhausts UNDO extents in Oracle RDS (ORA-01628)

IF: ORA-1628 Reported During Import (Doc ID 1955310.1)

Skip indexes and statistics
If the table already exists, drop it or use table_exists_action=replace.
use CONTENT=ALL instead of DATA_ONLY (redundant with previous point)


Performance: order of tables in joined statement

SQLite automatically chooses the estimated optimal join order; the table order in the query has no effect.

There are two ways to optimize this query.
If the filter on uniqueidentifier2 removes most table1 records from the result, then it would be fastest to look up table2 records with matching uniqueidentifier2 values first, then to look up the corresponding table1 records, and then to sort the result.
This would require the following indexes:

CREATE INDEX t2_uid2_idx ON table2(uniqueidentifier2);
-- CREATE INDEX t1_pk ON table1(PK);  -- primary keys have this automatically

If most table1 records will show up in the result, then it would be more efficient to go through table1 in the proper order and look up correspondig table2 records.
This would require the following indexes:

CREATE INDEX t1_vc1_idx ON table1(varchar1);
CREATE INDEX t2_FK_uid2_idx ON table2(FK, uniqueidentifier2);

(Having uniqueidentifier2 in the second index optimizes for this particular query, but might be not worth the storage and update overhead if you have many other queries.)

To check how queries are actually implemented, execute EXPLAIN QUERY PLAN.


In phpmyadmin you see the size of every table at the end . after you select a database.

if you want to do this in a query do, you need only the put in your own database name instead of testdb

SELECT SUM(Table_Size_MB) 'database_size_MB'
    FROM
        (SELECT table_name AS "Table",
        ROUND(((data_length + index_length + DATA_FREE) / 1024 / 1024), 2) AS "Table_Size_MB"
        FROM information_schema.TABLES
        WHERE table_schema = "testdb"
        ORDER BY (data_length + index_length) DESC) tablessizes;


Is there any disadvantages if i shift from myisam to innodb in mysql 5.7?

Oracle's goal seems to be to get rid of MyISAM.  Version 5.7 has virtually all the improvements to eliminate the 'desire' to use MyISAM.  The next version, 8.0, is attempting to finish the task -- including getting rid of the MyISAM tables from the mysql database.  (cf "Data Dictionary").

The only thing that remains is disk space.  Any InnoDB table will continue to take 2x-3x the disk space of a comparable MyISAM table.  But disk space is cheap and SSDs are prevalent, so this is not a big deal.

I strongly encourage to make the shift.  See my article for some rare gotchas that might bite you in the conversion.  (Which reminds; I should update it to say more about the status of 8.0.)


Paul White has a really detailed post about a way to get minimal logging for INSERT...SELECT statements that target clustered tables with secondary indexes.  Check out the full article here:
Minimal Logging with INSERT…SELECT and Fast Load Context
The main thing you're probably missing is that you need to enable trace flag 610.
Read the whole thing to see make sure you get past all the conditions and edge cases - especially around estimated number of rows to be inserted, existing number of pages in indexes, and the estimated data size of rows being inserted.


Solved with

[mysqld]
skip-host-cache
skip-name-resolve


Yes, shared_buffer is not allocated per-session or per-user. It is shared amoung all sessions, and it is allocated only once.
When parameter is not shared, it is clearly stated, i.e., on page https://www.postgresql.org/docs/9.6/runtime-config-resource.html you may see that on parameter temp_buffers description, it is stated that they are "temporary buffers used by each database session".


Update column with the sum of column in previous rows in MySQL

Let make the table called nuoji and load it with the above data

USE test
DROP TABLE IF EXISTS nuoji;
CREATE TABLE nuoji
(
  id int not null auto_increment primary key,
  amount INT NOT NULL,
  balance INT NOT NULL,
  account INT NOT NULL,
  KEY (account)
) ENGINE=MyISAM;
INSERT INTO nuoji (amount,balance,account) VALUES
(100,100,1),(100,100,2),(-40,-40,1),(100,60,1);
SELECT * FROM nuoji;

Let's run that code and Make the Data:

mysql> USE test
Database changed
mysql> DROP TABLE IF EXISTS nuoji;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE nuoji
    -> (
    ->   id int not null auto_increment primary key,
    ->   amount INT NOT NULL,
    ->   balance INT NOT NULL,
    ->   account INT NOT NULL,
    ->   KEY (account)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO nuoji (amount,balance,account) VALUES
    -> (100,100,1),(100,100,2),(-40,-40,1),(100,60,1);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM nuoji;
+----+--------+---------+---------+
| id | amount | balance | account |
+----+--------+---------+---------+
|  1 |    100 |     100 |       1 |
|  2 |    100 |     100 |       2 |
|  3 |    -40 |     -40 |       1 |
|  4 |    100 |      60 |       1 |
+----+--------+---------+---------+
4 rows in set (0.00 sec)

mysql>

Here is the Query You Need

SET @rt=0; SET @ndx=0; SET @given_account=1;
UPDATE nuoji A INNER JOIN  (SELECT * FROM
(SELECT (@ndx:=@ndx+1) ndx,id,amount,
(@rt:=@rt+amount) newbalance FROM nuoji
WHERE account=@given_account) BB) B USING (id)
SET A.balance=B.newbalance;

Here is that Query Executed:

mysql> SET @rt=0; SET @ndx=0; SET @given_account=1;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE nuoji A INNER JOIN  (SELECT * FROM
    -> (SELECT (@ndx:=@ndx+1) ndx,id,amount,
    -> (@rt:=@rt+amount) newbalance FROM nuoji
    -> WHERE account=@given_account) BB) B USING (id)
    -> SET A.balance=B.newbalance;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 3  Changed: 0  Warnings: 0

mysql> SELECT * FROM nuoji;
+----+--------+---------+---------+
| id | amount | balance | account |
+----+--------+---------+---------+
|  1 |    100 |     100 |       1 |
|  2 |    100 |     100 |       2 |
|  3 |    -40 |      60 |       1 |
|  4 |    100 |     160 |       1 |
+----+--------+---------+---------+
4 rows in set (0.00 sec)

mysql>

That's it !!!

If other accounts are messed up in the same way, set @given_account, and then execute the code.

Here is That Code Formatted:

SET @rt = 0;
SET @ndx = 0;
SET @given_account = 1;
UPDATE
    nuoji A INNER JOIN
    (
        SELECT * FROM
        (
            SELECT
                (@ndx:=@ndx+1) ndx,id,
                amount,(@rt:=@rt+amount) newbalance
            FROM nuoji
            WHERE account=@given_account
        ) BB
    ) B USING (id)
SET A.balance=B.newbalance;

Give it a Try !!!


Reconnect mirroring server SQL Server 2008 R2

Sometimes restarting the mirroring endpoint on the principal server will resolve this.

To do this use the following T-SQL.

ALTER ENDPOINT Endpoint_Name STATE = STOPPED

ALTER ENDPOINT Endpoint_Name STATE = STARTED

To get the name of the endpoint.

SELECT * FROM sys.endpoints
WHERE type = 4

See KB 2490051 for more details.


Using WITH together with CREATE TEMPORARY TABLE AS SELECT ... in Postgres?

The WITH goes after the CREATE TABLE

CREATE TEMPORARY TABLE foo
AS
WITH a AS (
SELECT 1 foo, 2 bar
), b AS (
SELECT 4 bar, 5 baz
)
SELECT * from a JOIN b ON (a.bar=b.bar)

Note the above will result in "ERROR: column "bar" specified more than once" - but I assume you are using better (distinct) column names in the real query.


Things you can do to reduce (you can never eliminate) deadlocks in a multi-user relational database:

Reduce the duration of transactions.

Any deadlock is the result of two processes competing for the same resources, so the fewer simultaneous processes are running, the less risk of a deadlock there is. This does not just apply to hour-long ETL jobs, it could very well apply to millisecond OLTP transactions if there are many enough.

Performance tune your long-running queries or tweak your server infrastructure for better throughput.

Also, I've seen examples where a process can hold a transaction unneccessarily. For instance, in some ETL processes, there's often just a single process loading data and you may not need transactional integrity at all - if it fails, just truncate everything and reload it again.

Remember that a regular SQL statement, even without a BEGIN/COMMIT TRANSACTION also implicitly creates a transaction (which commits as soon as the statement completes).

Try placing locks in the same order.

The textbook example on how to create a lock is:

Process 1 places a lock on object A
Process 2 places a lock on object B
Process 1 requests a lock on object B, but has to wait on process 2
Process 2 requests a lock on object A. Now process 1 and 2 are waiting on each other - a deadlock.

If every process accesses all the objects in the same order, the example above doesn't result in a deadlock:

Process 1 places a lock on object A
Process 2 requests a lock on object A, and has to wait for process 1
Process 1 places a lock on object B
Process 1 completes, releasing its locks on A and B.
Process 2 can now lock object A
Process 2 places a lock on object B
Process 2 completes.

Try different isolation levels

The isolation level determines how aggressively SQL Server takes out and holds locks. Obviously, the more aggressive locks are taken, the higher the risk of a deadlock.

Add retry logic to the application

By definition, you can't avoid deadlocks with 100% certainty. The application needs to have a built-in retry logic that re-runs the transaction if it is the victim of a deadlock.

Further reading

Some blog posts I've written on the subject:

How to create, find and eliminate deadlocks
On isolation levels


How to update DynamoDB item by searching on GSI primary key?

What I found out is I need to get the item by GSI and then using primaryKey and sortKey update the item and cannot update item using GSI only.


MySQL version appears to be inconsistent?

Answering my own question, in case other might benefit.

The explanation, it turns out, was that although the upgrade succeeded, the restart of the daemon had failed silently. It took a kill -9 and systemctl start mysql to sort that out.


How to Apply a Title-Case RegEx Condition for Object Names with Policy Based Management

SQL Server's implementation of the Like operator only supports a really cutdown kind of regEx which includes basic character matching and wildcards, but does not include Number of repeats like {1} or the other kind regEx anchors like \b or \s.  It is also generally speaking, not case-sensitive.

That said, you can sometimes spoof these kind of things with Like.  Say you wanted to find 3 alpha characters and two numbers you could do something like:

[a-z][a-z][a-z][0-9][0-9]

You can also force case-sensitve matches by using a case-sensitive collation, eg

DECLARE @s VARCHAR(10) = 'orderDetail'

-- Check case;
SELECT
    CASE
        WHEN UPPER( LEFT( @s, 1 ) ) = LEFT( @s, 1 ) COLLATE SQL_Latin1_General_CP1_CS_AS THEN 1
        ELSE 0
    END

SET @s = 'OrderDetail'

-- Check case;
SELECT
    CASE
        WHEN UPPER( LEFT( @s, 1 ) ) = LEFT( @s, 1 ) COLLATE SQL_Latin1_General_CP1_CS_AS THEN 1
        ELSE 0
    END

However, isn't TitleCase more of a philosophical position than a policy?  How would you determine whether TitleCase or Titlecase is correct?  In fact they both are.

Your particular regEx pattern would also match 'orderDetails' and the 'Details' secton of 'orderDetails'.  What you could do is add the start and end word anchors, eg

^[A-Z]{1}[a-z]+[A-Z]?[a-z]*$

but as described, this won't work with Like and anyway and I'm not convinced it's worthwhile.  All you really want to do (apart from the dbo schema check, which is easy and worthwhile), is check the first character is upper case.  So in theory you could do something like this with the extremely powerful PBM ExecuteSql function:

ExecuteSql('Bool', 'SELECT CASE WHEN UPPER( LEFT( @@ObjectName, 1 ) ) = LEFT( @@ObjectName, 1 ) COLLATE SQL_Latin1_General_CP1_CS_AS THEN 1 ELSE 0 END')

I think I got this to work in the short time I spent on this and besides, you should probably be using camelCase : )

Another interesting example:
https://www.simple-talk.com/blogs/2013/09/16/sql-server-policy-based-management-creating-a-custom-condition/

HTH


what's result for sql server log backup twice?

When you run a log backup, you don't specify full or differential, you just backup the log.

Log backups back up transactions that have taken place since the last log backup, or if a log backup doesn't exist, since the last database backup.

Recovery Point and Recovery Time objectives will direct how often you want to take log backups.

If, for instance, you do a full backup on Monday morning, then do log backups every 5 minutes, you could restore to Monday at 1:45pm by restoring the full backup and every log backup that has taken place up to 1:45pm.


Convert column from string of numbers to a subtotal on select statment

This is definitely not a good data model.

If you are stuck with it you could use

SELECT ID,
       X.value('sum(/x/text())', 'FLOAT') as Value
FROM YourView
CROSS APPLY (VALUES(CAST('<x>' + REPLACE(VALUE, ';', '</x><x>') + '</x>' AS xml))) V(X)

Online Demo


In a MySQL Master/Slave replication, what would happen if I write to the master table and in slave table is drop?

If the Slaves do not find 'DROP TABLE' command in the Master's binary logs, it cannot run the DROP TABLE either DROP TABLE command has not been run on Master.


Shirking of Log file on Primary Server of Transaction Log shipping

I have a database with size 200 GB Total(mdf+ldf) and Log file(ldf) with 50 GB and 99% unallocated space in ldf file i.e. not being used at all.

Leave it as it is, the space would eventually be utilized without any autogrowth and that would really help your SQL Server transactions, so just DONT shrink.

FYI

You can shrink log files on primary server
DONT change the recovery model this would break logshipping. Yes you can  shrink without changing recovery model
The log file will eventually again grow so there is no sense in shrinking.
You may use NO truncate but normal shrinking would just do fine. This is just to answer your queries I would still say do not shrink.

Further reading Why Does Transaction Log file Growing and Runs out of the Space


Single disk for Data, System, User, Temp and Backup when installing Fail Over Cluster?

MSDTC has to be installed on a separate drive while rest can be installed on another drive.

EDIT

MSDTC is needed on separate drive because once you start installing SQL Server, the drive where MSDTC is installed won't be selectable and you will see message on screen something like "Cluster Disk 2 is already in use by MSDTC".

Secondly, according to folloing article MSDTC is needed when you are using SSIS, which I am actually using.
http://technet.microsoft.com/en-en/library/ms189910.aspx


Security: writing to a file from MySQL CLI

I think you are confused about what's happening here.  You ran the client on the server machine as root.  You probably weren't running the client as root on the "remote" machine.  Even if your query had succeeded in appending to /etc/password, you would have only modified your local password file, not the one on the server.

The pager is always local to where the mysql client is running.  It has nothing to do with the MySQL server.

If the user on the local machine (where the client is running) has write access to a local file, then they can write to the file via the pager option, just as they could write to the file with anything else.  There's nothing here that has anything to do with the MySQL server or permissions.

mysql> \P cat >> foo.bar
PAGER set to 'cat >> foo.bar'
mysql> select NOW();
1 row in set (0.00 sec)

mysql> quit
Bye

Now, by making the file so it can't be written...

sqlbot@dev:~$ chmod 000 foo.bar
sqlbot@dev:~$ mysql

mysql> \P cat >> foo.bar
PAGER set to 'cat >> foo.bar'
mysql> select NOW();
sh: cannot create foo.bar: Permission denied
1 row in set (0.00 sec)

mysql> quit
Bye

Run the client as root on the remote machine and you'll see that you won't get permission denied, but you also will not have modified anything on the server -- just your local password file.

Also, yeah, don't create a 'root'@'%' account in MySQL.


Perl vs Ksh for Unix Database Administration

The end of your post is really the important bit:

  information regarding what best suits the needs of database administrators.

So let's start there.

What are your needs as a DBA? You generally have two realms of operation: system level maintenance, and database level maintenance (whereby all actions are merely maintaining the system for better performance ... look, don't get pedantic, go with me here). So you have two realms to consider.

ksh - The Korn Shell is one of the older Unix operational shells, used to control the server. The goal of ksh is to manage a server, and allow one to do all the things that the traditional Bourne shell (not the Bourne Again SHell that most people are familiar with, this is older vintage) allowed, with a few differences (this shell had better support for things like arithmetic and arrays, making it easier to program in some regards). It is the default shell on AIX.

perl - Perl is a high-level general purpose programming language. It is designed to be run on top of a shell, and was designed primarily with reporting in mind. It combined features of shell scripting and C, as well as regex technologies to attempt to make it better than all of the previous. A lot of admins around the planet like perl because it makes system management easier, because of the combination of shell capabilities and report generation speed up (it being a programming language). Perl scripts can be run as simply as shell scripts, and there is even a convention in the Unix world of having the first line indicate what will run the script (the familiar hashbang line). So running perl scripts is as easy as running a shell script.

The difference, then, is do you need a full-fledged programming language, or do you need a basic shell script? The answer then becomes use the one that gives what you need, as both are highly useful to you. If you want stats, that's impossible to give, we each have our own style. Just agree on something internally and call it that (if you're going to set a standard, make it perl as that has more features, and you never want to get your hand caught because you set an unrealistic standard. Perl is also more extensible in the future). Better to restrict yourself to two or three languages in this case.

Back at the beginning I mentioned that you have two options: system level and database level.

If your purpose is primarily system level, then we've answered all the questions here (use the one that suits your needs). If you also need to program into the database then you really need to just standardize on perl or get really used to command line syntaxes, because Perl has modules for every conceivable database on the planet somewhere, and if one doesn't exist, it can be written (example: DBI -> DBD:DB2 -> DB2 client).

As an aside, I do Microsoft SQL Server and C#, so I live in the Windows world, and I use PowerShell over DOS Batch files. PowerShell is in many ways similar to perl, and gives me a lot more flexibility than DOS batch programming (but there are plenty of admins in the world still using old DOS scripts to manage their systems). While the DOS Batch system allows for arrays, loops, variables and all the rest, it's much easier to program in a programming language instead of a shell script, so I'm able to be much more productive.

At the end of the day, the real question is what features do you need in your scripting environment, and we can't help you with that. But I will always opt for more clarity, maintainability and extensibility, over "this is what shipped in the box".


PolyLang WP Update to replace one string to another

Basically, you constuction to get the right ID was doubles and UPDAT has no fROM clause
There WHERE clause you should try first in a SELECT if you get all IDs
UPDATE wp_posts
SET post_content = REPLACE(post_content, 'str1', 'str2')
WHERE ID IN (SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(description, '"ar";i:', -1), ';',1) FROM wp_term_taxonomy WHERE taxonomy = 'post_translations')


Does setting column property to NOT NULL affect table size?

From the MySQL documentation (emphasis mine):

  Declare columns to be NOT NULL if possible. It makes SQL operations faster, by enabling better use of indexes and eliminating overhead for testing whether each value is NULL. You also save some storage space, one bit per column. If you really need NULL values in your tables, use them. Just avoid the default setting that allows NULL values in every column.

So, on the contrary, NOT NULL saves disk space, rather than costing more.


Selecting a View consumes 4GB memory but executing SQL statement of View only 70 MB

The 'actual' execution plan explains the issue - the estimated number of rows coming into the SORT is 5.3 million, so Sql Server allocates a large block of memory to hold it.  However, the 'actual' number of rows is much less.

A sort operation needs to spool all the data and it estimates that there is 3.7GB to spool. Additionally the amount of memory needed to actually sort is more than the actual size of data itself.


Possible to rewrite this query as a join?

You can get what you want with the following JOINed query, which ressembles very much your original one:

SELECT
    e.id, e.created_at, e.updated_at, e.user_id, e.type, e.follower, e.follower_pk,
    max(e2.updated_at) AS last_follow_date
FROM
    events e
    LEFT JOIN events e2
        ON      e2.user_id = e.user_id
            AND e2.type = 'follow'
            AND e2.updated_at < e.updated_at
            AND e.type = 'unfollow'
WHERE
    e.user_id = 1
GROUP BY
    e.id
ORDER BY
    e.id, e.updated_at ;

With regard to performance, your queries will benefit from an index such as:

-- Index to improve performance
CREATE INDEX idx_events_user_id_type_updated_at
    ON events (user_id, type, updated_at) ;

The three columns user_id, type and updated_at appear in the JOIN condition. The order in which you want them is user_id (you make an = comparison, and is supposed to have high cardinality --i.e.: very many values--), type (you make an = comparison, cardinality = 2), updated_at checked with an inequality... MySQL is not good enough to use it as part of the index seek, but will make the index a covering one, and that will save from checking the original table.

Alternative: a slight twist on your original query, might save some time:

SELECT
   e1.*,
   case when type = 'unfollow' then
      (SELECT MAX(e2.updated_at)
       FROM   events e2
       WHERE
                  e2.user_id = e1.user_id
              AND e2.type = 'follow'
              AND e2.updated_at < e1.updated_at
       )
   else NULL end AS last_follow_date
FROM   events e1
WHERE  e1.user_id = 1;

You can check everything at dbfiddle here


You can use NOT EXISTS for this purpose.

SELECT DISCTINCT
    prod_id
FROM
    products p1
WHERE
    cat_id IN (10, 11)
    AND NOT EXISTS (SELECT 1
                    FROM   products p2
                    WHERE  p1.prod_id = p2.prod_id
                           AND cat_id = 12);


msdb.dbo.sysjobhistory data gets deleted whenever SQL Server services are restarted

My response is based on possible reasons of problem.
Make sure you have configured History setting properly and the job to purge history scheduled properly. To validate these settings:

Right click on SQL Server Agent and got to Properties.

Go to History tab.

Make sure setting is configured properly.

The job named syspolicy_purge_history used to execute above settings.

So right click on the job in Jobs folder in SQL Server Agent and check Schedule of the Job.

It should be scheduled according to your requirement.

Thanks!


What happens if agent job owner account is deactivated in Active directory?

Let us say we are talking about Windows login which part of AD and was owner of a job. Below is more detailed answer with couple of scenarios.

1. The login was removed from AD but not from SQL Server( someone forgot to remove it) and the login is member of sysadmin fixed server role

In above scenario the job will "still run". The reason is since job was added as administrator in SQL Server there is entry of this owner with SID in sys.server_principal which SQL Server knows, and since the login was member of sysadmin role no "impersonization" will be done and hence job will succeed.

2. The login was removed from AD and from SQL Server.

In this scenario the job will fail which is pretty much obvious

3. The login was removed from AD but not from SQL Server( someone forgot to remove it) and the login is NOT member of sysadmin fixed server role.

In this case job will fail because since owner is not sysadmin impersonization will be done and SQL Server will look for this account in Windows AD, since the account was removed the job will fail.

For SQL Server admin login like SA, even if SA is disabled and is owner of some job when you run the job it would succeed.

Tibor Karaszi has more detailed explanation on this.


Error when restoring up a database backup: Cannot find file ID 1 on device

Your code is for Restoring a database not to create a backup file. you have to use the following code:

BACKUP DATABASE METERIAL_MANAGEMENT_2
TO DISK = 'D:\DB_Backup\MMS.BAK'

Read more about Creating a full backup using T-SQL in the following TechNet article


Can I tell SQL Server not to place data in the buffer cache?

No. SQL Server must bring pages into memory in order to work with them. There are no configuration options to directly affect how long pages remain in memory, or the various policies used to manage buffer pool usage.

If your process involves changing data, you may be able to influence the impact on the buffer pool to some extent (ha!) by issuing a manual checkpoint. This will force modified (dirty) pages to be written to disk, freeing them up for reuse.

Resource governor currently only supports limiting workspace memory (used by sorts, hashing operations, and parallel buffers). It does not provide a way to limit the use of the buffer pool for cached data and index pages.

Generally, you will have to rely on SQL Server making good decisions around space usage in the buffer pool. The ETL job(s) likely benefit from the current arrangement, so you might well find a negative performance impact were you able to limit its use of the buffer pool.

If you are certain that the vast majority of the buffer pool is being consumed by finished ETL operations, you might find it worthwhile to explicitly flush dirty pages and clear buffer pool at the end of the ETL process:

CHECKPOINT;
DBCC DROPCLEANBUFFERS;

This is usually not indicated for a production system, but you may find it helps in this specific situation. With no dirty pages and an empty buffer pool, SQL Server will begin to acquire pages for the new workload at its maximum rate.

In particular, the buffer pool being lower than the server memory target will enable ramp-up reads - where whole extents are read in whenever a page is requested from persistent storage. This is effective by default in Enterprise Edition and can be enabled for other editions (even Express) using global trace flag 840, see:

FIX: An application query that reads lots of data may be slow, and the SQL Server service may take a long time to start in SQL Server 2005

From that link:

  This hotfix exposes trace flag 840. When trace flag 840 is turned on, SQL Server can perform larger I/O extent reads to populate the buffer pool when SQL Server starts. The larger I/O extent reads populate the buffer pool faster. Additionally, the larger I/O extent reads improve the initial query compilation and the response time when SQL Server starts.

Although that text mentions SQL Server starting, it also applies to buffer pool repopulation as I described above.

You can of course also help this process along by running dummy queries against structures you want to bring into memory at the end of the ETL process.


Index maintenance is an I/O intensive workload. Under these circumstances please consider to scale to premium tiers prior to run index maintenance tasks, and when the workload finishes scale down to previous tier. You can automate that using T-SQL. That should reduce maintenance time.

Additionally, compacting large objects data (image, text, ntext, varchar(max), nvarchar(max), varbinary(max), and xml) can save disk space but it can increase the time it takes to maintain indexes. If you have a restricted time window for reindexing you can set  LOB_COMPACTION to OFF.

Hope this helps.


One way to achieve this is to ensure the subquery is correlated i.e. it depends in some way on the outer query. Using an AdventureWorks example:
SELECT
    E.BusinessEntityID,
    R.DepartmentID
FROM HumanResources.Employee AS E
CROSS APPLY
(
    SELECT TOP (1)
        D.DepartmentID
    FROM HumanResources.Department AS D
    ORDER BY
        NEWID(),
        CHECKSUM(E.BusinessEntityID, D.DepartmentID) -- Correlation
) AS R;

db<>fiddle demo
Without the correlation, SQL Server recognises that the inner query logically only needs to be run once.
The example above uses CHECKSUM(E.BusinessEntityID, D.DepartmentID) to ensure the intended correlation remains after query optimization. If we just add E.BusinessEntityID, the optimizer realizes its value is constant per iteration, removes the dependency, and we're back where we started.
The semantics of the example are also slightly different from the query in the question. It does not return NULL if lookupTable is empty.


I try to insert a record with null sample_id

`sample_id` int unsigned NOT NULL,

The sample_id can't be null.  You will probably find NEW.sample_id = 0 inside the trigger if you don't specify a value on insert, since you have declared the column as NOT NULL... so you need to test for that instead:
IF NEW.sample_id = 0 THEN
...

...or change your table definition to allow null values.

Update (for the benefit of future visitors):
MySQL Bug #6295 (yes, from 9 years ago) is resolved in MySQL 5.7.1 and apparently changes this behavior so that NOT NULL validation is not imposed until after the BEFORE trigger is processed.  This behavior will likely make its way into MariaDB at some point also.  If the original poster of this question is using the = 0 test that currently works, it's possible that this behavior change could cause that logic to fail since the timing (or occurrence, at all) of the coercion to from NULL to 0 might also change.

If a column is declared as NOT NULL, it is not permitted to insert NULL into the column or update it to NULL. However, this constraint was enforced even if there was a BEFORE INSERT (or BEFORE UPDATE trigger) that set the column to a non-NULL value. Now the constraint is checked at the end of the statement, per the SQL standard. (Bug #6295, Bug #11744964)
— http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-1.html

See also: http://dev.mysql.com/worklog/task/?id=6030


PostgreSQL query takes a long time (as it's not using the index)

The entire query is planned up front, and at the time of planning it doesn't know what value will be found in CHECKPOINT1_PKEVENT.  It makes the generic assumption that the inequality will match 1/3 of the rows, which is obviously quite wrong.  When faced with this situation, I usually just have the client software run the queries separately, stuffing the result of one into a parameter for the other, assuming I can tolerate the prospect that the value may have changed between the two executions.
That said, it should probably be doing an index only scan anyway.  Do you have a very high setting for random_page_cost?  What are you other planner settings?  Has the table EVENT been vacuumed recently?


Restoring database from a server that uses data compression

Correct, an error will be raised when attempting to RESTORE.

Same thing goes if other Enterprise features, such as table partitioning, are used. For those finding this answer in search, usage of Enterprise features are listed in sys.dm_db_persisted_sku_features.

What I would recommend is to use Developer Edition in your development and testing environments, if you don't support Standard in production (it sounds like your app is in-house only). This edition includes the Enterprise engine; it just isn't licenced to be used in production, and will be able to restore the Enterprise backups just fine.


is it possible to extract 'LifeCycle Status' using emcli ( Oracle Enterprise Manager )

It seems that it's not possible, but, for the record, here's to get get the information via SQL.

SELECT CASE INSTR ( cm_target_name, '_' )
WHEN 0 THEN cm_target_name
    ELSE SUBSTR ( cm_target_name, 0, INSTR ( cm_target_name, '_') - 1 )
END database_name, property_value
FROM sysman.cm$em_tprops_ecm_view
WHERE cm_target_type = 'oracle_database'
AND property_name = 'orcl_gtp_lifecycle_status';


Is there a function or system view that returns all of the available
  property names?

No. There isn't even something that returns the names of all of the available built in functions (such as OBJECTPROPERTY itself).

These property values are documented for OBJECTPROPERTYEX even though they do also appear to work for OBJECTPROPERTY and probably should be documented there (the return type is int so there is no reason that they shouldn't be supported there and it is more convenient to work with that function without needing a cast from sql_variant.)

For entirely undocumented property values (such as minlen for INDEXPROPERTY) I'm not aware of any way of determining these.


Changing Database Owner Removed User Mapping

The database owner is mapped to the dbo user in the database, so when you change the database owner, you change which login is mapped to the dbo user.

The previous database owner may loose access to the database.

eg

use master
drop database mydatabase
go
create database mydatabase
go
use mydatabase
go
select sid
from sys.database_principals
where principal_id = 1
go
alter authorization on database::mydatabase to sa
go
select sid
from sys.database_principals
where principal_id = 1

outputs

sid
-------------------
0x0105000000000005. . .

(1 row affected)

sid
-------------------
0x01

(1 row affected)


Possible INDEX on a VARCHAR field in MySql

SUGGESTION #1 : Standard Indexing
CREATE TABLE mytable
(
    id int not null auto_increment,
    myfield varchar(255) not null,
    primary key (id),
    key (myfield)
);

If you index like this, you can either look for the whole string or do left-oriented LIKE searches
SUGGESTION #2 : FULLTEXT Indexing
CREATE TABLE mytable
(
    id int not null auto_increment,
    myfield varchar(255) not null,
    primary key (id),
    fulltext (myfield)
);

You can effectively use searches for individual keywords as well as whole phrases. You will need to define a custom stopword list because MySQL will not index 543 words.
Here are my other posts from the past two years on FULLTEXT indexes

May 23, 2011 : Optimizing mysql fulltext search (StackOverflow)
Oct 25, 2011 : FULLTEXT index ignored in BOOLEAN MODE with 'number of words' conditional
Jan 26, 2012 : Mysql fulltext search my.cnf optimization
May 07, 2012 : MySQL EXPLAIN doesn't show 'use index' for FULLTEXT

SUGGESTION #3 : Hash Indexing
CREATE TABLE mytable
(
    id int not null auto_increment,
    myfield varchar(255) not null,
    hashmyfield char(32) not null,
    primary key (id),
    key (hashmyfield)
);

If you are looking for one specific value and those values could be lengths well beyond 32 characters, you could store the hash value:
INSERT INTO mytable (myfield,hashmyfield)
VALUES ('whatever',MD5('whatever'));

That way, you just search for hash values to retrieve results
SELECT * FROM mytable WHERE hashmyfield = MD5('whatever');

Give it a Try !!!


Adding a constant IF not in an other table

Is this what you are looking for?

    select *
    FROM Comment cm left join [Delete] del
     on del.[Target]=cm.Id
    where cm.[Target] = '".$_GET[''pp'']."'
    and Type = 'comment'
    and del.[Target] is null
    and deleted=0


For merge replication, do I need to schedule index maintenance on all sites or just publisher?

The somewhat sledgehammer approach and "best practice" answer would be yes run index maintenance on all sites.

With that said, Index Fragmentation is only truly an issue if it's causing you a noticeable performance penalty. You don't "have to" perform maintenance if there's no real value in doing so.

The performance of each database/instance in the replication topology could be evaluated independently to determine if index maintenance would prove to be beneficial. High fragmentation as a metric in isolation does not necessarily mean that index maintenance would result in a noticeable performance improvement.

By taking into consideration the size of the Index, query data access patterns, storage configurations and query response times, you can establish if index maintenance would prove beneficial and deliver a more refined index maintenance strategy. Assuming it's worth the effort......


Postgres: Sum from multiple joins resulting in values being more than expected

Join against the results of the aggregation:

select inv.*
from invoices inv
  left join (
    select invoice_id, COALESCE(SUM(amount + discount), 0) as total_payments
    from invoice_payments
    group by invoice_id
  ) ip on ip.invoice_id = inv.id
  left join (
    select invoice_id, coalesce(sum(amount),0) as total_credit
    from invoice_credit_notes
    group by invoice_id
  ) icn on icn.invoice_id = inv.id
where coalesce(ip.total_payments,0) + coalesce(icn.total_credit,0) < ROUND(inv.grand_total, 0))


In the first query, only the customer_id needs to be saved from the customers into the hash table, because that is the only data needed to implement the semi-join.

In the second query, all of the columns need to be stored into the hash table, because you are selecting all of the columns from the table (using *) rather than just testing for existence of the customer_id.


How to find out the oldest full backup still existing on disk?

In this test I am checking the backups for a database called JunoReporting

first I create a function to test the physical existence of the file

USE MASTER
GO

create FUNCTION dbo.fn_FileExists(@path varchar(8000))
RETURNS BIT
AS
BEGIN
     DECLARE @result INT
     EXEC master.dbo.xp_fileexist @path, @result OUTPUT
     RETURN cast(@result as bit)
END;
GO

then I use it to
get the latest Full backup still on Disk

--=============================================
-- get the latest Full backup still on Disk
--=============================================

select
  a.backup_set_id AS [LATEST Full backup still on Disk]
  ,a.media_set_id
  ,a.backup_start_date
  ,a.backup_finish_date
  ,a.type
  ,backup_size=a.backup_size/1024.00
  ,a.is_copy_only
  ,compressed_backup_size= a.compressed_backup_size
  --,b.media_set_id
  ,b.physical_device_name
  ,[File Exists]= CASE WHEN master.dbo.fn_FileExists(b.physical_device_name) = 1 THEN 'Yes' ELSE 'No' END
  FROM msdb.dbo.backupset a
  INNER JOIN msdb.dbo.backupmediafamily b
         ON a.media_set_id = b.media_set_id

  WHERE 1=1
  and a.backup_set_id = (
                            SELECT MAX(f.backup_set_id)
                                FROM msdb.dbo.backupset f
                          INNER JOIN msdb.dbo.backupmediafamily b
                                  ON f.media_set_id = b.media_set_id
                               WHERE 1=1
                                 AND f.[type] = 'D'
                                 AND f.DATABASE_NAME='Junoreporting'
                                 AND master.dbo.fn_FileExists(b.physical_device_name) = 1
)

and also to
get the OLDEST Full backup still on Disk

--=============================================
-- get the OLDEST Full backup still on Disk
--=============================================

select
  a.backup_set_id AS [OLDEST Full backup still on Disk]
  ,a.media_set_id
  ,a.backup_start_date
  ,a.backup_finish_date
  ,a.type
  ,backup_size=a.backup_size/1024.00
  ,a.is_copy_only
  ,compressed_backup_size= a.compressed_backup_size
  --,b.media_set_id
  ,b.physical_device_name
  ,[File Exists]= CASE WHEN master.dbo.fn_FileExists(b.physical_device_name) = 1 THEN 'Yes' ELSE 'No' END
  FROM msdb.dbo.backupset a
  INNER JOIN msdb.dbo.backupmediafamily b
         ON a.media_set_id = b.media_set_id

  WHERE 1=1
  and a.backup_set_id = (
                            SELECT MIN(f.backup_set_id)
                                FROM msdb.dbo.backupset f
                          INNER JOIN msdb.dbo.backupmediafamily b
                                  ON f.media_set_id = b.media_set_id
                               WHERE 1=1
                                 AND f.[type] = 'D'
                                 AND f.DATABASE_NAME='Junoreporting'
                                 AND master.dbo.fn_FileExists(b.physical_device_name) = 1
)

and also
Get all the backups after the latest backup

--=============================================
-- Get all the backups after the latest backup
--=============================================

select
  a.backup_set_id
  ,a.media_set_id
  ,a.backup_start_date
  ,a.backup_finish_date
  ,a.type
  ,backup_size=a.backup_size/1024.00
  ,a.is_copy_only
  ,compressed_backup_size= a.compressed_backup_size
  --,b.media_set_id
  ,b.physical_device_name
  ,[File Exists]= CASE WHEN master.dbo.fn_FileExists(b.physical_device_name) = 1 THEN 'Yes' ELSE 'No' END
  FROM msdb.dbo.backupset a
  INNER JOIN msdb.dbo.backupmediafamily b
         ON a.media_set_id = b.media_set_id

  WHERE 1=1
  and a.backup_set_id >= (  -- the last full backup

                            SELECT MAX(f.backup_set_id)
                              FROM msdb.dbo.backupset f
                             WHERE 1=1
                               AND f.[type] = 'D'
                               AND f.DATABASE_NAME='Junoreporting'
                               AND master.dbo.fn_FileExists(b.physical_device_name) = 1

                        )

  --and a.[type] = 'L'
  AND a.DATABASE_NAME='Junoreporting'
  AND b.[Device_Type] = 2
  AND a.is_copy_only = 0
order by backup_set_id desc


Implications of adding a trigger on production database

To create a trigger on a table a Sch-M lock must be taken on that table. SQL Server allows a single schema modification lock (Sch-M) on any locked object, so a transaction which creates the trigger must wait to acquire a Sch-M lock on the target object.

While a process is waiting to acquire a Sch-M lock it is blocking other processes which can try to begin using the table.

You should try to create the trigger during your maintenance window when the table is free from other locks.

The Sch-M lock is Evil - this is on indexes rebuilding but lock mechanics is the same.


You can use dbms_metadata for this.

select dbms_metadata.get_ddl('INDEX', index_name, owner)
from all_indexes
where owner in ('USER_1', 'USER_2', 'USER_3', 'USER_4', 'USER_5');


The files are created when you first install MySQL.  It is clumsy to change the size after that.  In particular, the size cannot be changed dynamically.

How big is the setting now?  How many rows with the one transaction be affecting?  It may not be worth it.

If you do choose to make a change; see How to safely change MySQL innodb variable 'innodb_log_file_size'?
Be sure to read the Comments, too.

Not Dynamic

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_log_file_size says that innodb_log_file_size is not dynamic, meaning that your SET cannot work.  You can change the value in my.cnf, but then you have the issues discussed above.  A note from the 5.6.8 Changelog:

  On startup, MySQL would not start if there was a mismatch between the
  value of the innodb_log_file_size configuration option and the actual
  size of the ib_logfile* files that make up the redo log. This behavior
  required manually removing the redo log files after changing the value
  of innodb_log_file_size. The fix causes MySQL to write all dirty pages
  to disk and re-create the redo log files during startup if it detects
  a size mismatch. (Bug #14596550)


It just works, like it should. It doesn't lock the table, so start testing.


My issue was that the auto_extend for one of my tablespaces had been set to off. Once I changed that I was able to run my commands


The reason for the error is that your code compares an array to an integer, i.e. the array in the column with any of the values in the array [1,3].

From the comments, it seems that the wanted result is when the two arrays (column and given array) have at least one common item, i.e. they overlap. The operator for "overlap" is &&:

SELECT
    *
FROM
    company_employee_contacts
WHERE
    corporate_complaint_type_ids && ARRAY[1,3] ;

For more operators on arrays, see the documentation: Array Functions and Operators

The idea of using ANY is good but you need a "double ANY" and this syntax would work (if it was valid):

WHERE       -- not valid syntax
    ANY(corporate_complaint_type_ids) = ANY(ARRAY[1,3]) ;

You could unfold both arrays or one of the two (using unnest() function) and combine it with ANY. This would be equivalent to the overlaps operator and work - although I see no reason to use something so complicated:

-- unfold with unnest and ANY
WHERE
    EXISTS
    ( SELECT 1
      FROM unnest(corporate_complaint_type_ids) AS u
      WHERE u = ANY(ARRAY[1,3])
    ) ;

-- or the reverse
WHERE
    EXISTS
    ( SELECT 1
      FROM unnest(ARRAY[1,3]) AS u
      WHERE u = ANY(corporate_complaint_type_ids)
    ) ;

-- or both with unnest
WHERE
    EXISTS
    ( SELECT 1
      FROM unnest(corporate_complaint_type_ids) AS u1,
           unnest(ARRAY[1,3]) AS u2
      WHERE u1 = u2
    ) ;


No DAC connection could be established on the default port. Make sure DAC is enabled

From Using a Dedicated Administrator Connection:

  By default, the connection is only allowed from a client running on the server. Network connections are not permitted unless they are configured by using the sp_configure stored procedure with the remote admin connections option.

From that latter page:

  SQL Server provides a dedicated administrator connection (DAC). The DAC lets an administrator access a running server to execute diagnostic functions or Transact-SQL statements, or to troubleshoot problems on the server, even when the server is locked or running in an abnormal state and not responding to a SQL Server Database Engine connection. By default, the DAC is only available from a client on the server. To enable client applications on remote computers to use the DAC, use the remote admin connections option of sp_configure.

I think the page you read is just a little light on the details, that's all.

As for why a local DAC connection might not work, it's possible that it's because you're running SQL Server Express, which requires TF 7806 to enable. Another possibility is the network protocols that are enabled.


Don't tune with percentages. Your server will always have 100% of its waits on something - but there just may not be much load. Your server is the classic example.

Say you only run 1 query per hour. All of the query's data is already cached in memory, and there's no one else competing for locks. That query has several parallel tasks in it, and they all need to get CPU time.

That query's just going to work for a while, then finish. If you looked at signal waits, it might seem like 90-100% of the time, WHEN IT WAS WAITING, it was waiting to get on CPU. But it just wasn't waiting that much.

Instead, look at wait time per core, per second (or per hour) as described here:

https://www.brentozar.com/archive/2015/03/how-to-measure-sql-server-workloads-wait-time-core-second/

(Disclaimer: I wrote that.) That way, you can see if the server's really doing any work at all. I bet in your server, you're only waiting a few minutes per hour - meaning, you just don't need to bother tuning that server overall. It doesn't mean every QUERY is fast - it just means you can't do server-level tuning, and you have to switch to query-level tuning. (You have to ask why the query is slow rather than asking why the server is slow.)


Performance issues on large CSV import with before INSERT trigger

My suggestions are roughly in order of the performance benefit they will provide.

(1) (Similar to what a_vlad says) Rethink the use of a Trigger.  I would load the csv into a temp table, then do the timezone lookup in a single query.  Only then copy the data into the main table.  Other tips on High speed ingestion .  Tips on chunking .  Yes, 500K rows at once is likely to cause some kind of trouble.

(2) Add to timezones:

INDEX(area_code, timezone)

(3) This is inefficient:

WHERE area_code = (SELECT LEFT(NEW.contact , 3));

change it to

WHERE area_code = LEFT(NEW.contact , 3);

(4) The first of these is redundant and can be dropped:

 KEY `FK_campaign_id_idx` (`campaign_id`),
 KEY `idx_004` (`campaign_id`,`call_status`,`test_call`),

(5) What is the distinction between contact and contact_name?

(6) Low-cardinality indexes (eg, idx_active_call) are essentially never chosen by the Optimizer, so could be dropped.