Is it possible to index a computed column used in ORDER BY? I am having problems with my mysql server, because of not properly written queries. I did not use indexes, because I do not know how to add them on computed columns, in a query that has SELECT COUNT(*) AS b ... ORDER BY b.

Looks like it is impossible, so if it is indeed impossible, how could I re-manage my queries?

SELECT COUNT(downloaded.id) AS downloaded_count
    , downloaded.file_name
    ,uploaded.*
FROM `downloaded` JOIN uploaded
ON downloaded.file_name = uploaded.file_name
WHERE downloaded.completed = 1
AND uploaded.active = 1
AND uploaded.nsfw = 0
AND downloaded.datetime > DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY downloaded.file_name
ORDER BY downloaded_count DESC LIMIT 30;

EXPLAIN

+----+-------------+------------+------+---------------+-----------+---------+--------------------------+------+----------------------------------------------+
| id | select_type | table      | type | possible_keys | key       | key_len | ref                      | rows | Extra                                        |
+----+-------------+------------+------+---------------+-----------+---------+--------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | uploaded   | ALL  | file_name_up  | NULL      | NULL    | NULL                     | 3139 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | downloaded | ref  | file_name     | file_name | 767     | piqik.uploaded.file_name |    8 | Using where                                  |
+----+-------------+------------+------+---------------+-----------+---------+--------------------------+------+----------------------------------------------+

UPDATED:

with ORDER BY;

Showing rows 0 - 29 ( 30 total, Query took 0.1639 sec)

without ORDER BY;

Showing rows 0 - 29 ( 30 total, Query took 0.0064 sec)

UPDATED #2:

table: uploaded (Total 720.5 KiB)

CREATE TABLE IF NOT EXISTS `uploaded` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sid` int(1) NOT NULL,
  `file_name` varchar(255) NOT NULL,
  `file_size` varchar(255) NOT NULL,
  `file_ext` varchar(255) NOT NULL,
  `file_name_keyword` varchar(255) NOT NULL,
  `access_key` varchar(40) NOT NULL,
  `upload_datetime` datetime NOT NULL,
  `last_download` datetime NOT NULL,
  `file_password` varchar(255) NOT NULL DEFAULT '',
  `nsfw` int(1) NOT NULL,
  `votes` int(11) NOT NULL,
  `downloads` int(11) NOT NULL,
  `video_thumbnail` int(1) NOT NULL DEFAULT '0',
  `video_duration` varchar(255) NOT NULL DEFAULT '',
  `video_resolution` varchar(11) NOT NULL,
  `video_additional` varchar(255) NOT NULL DEFAULT '',
  `active` int(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  FULLTEXT KEY `file_name_keyword` (`file_name_keyword`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3328 ;

table: downloaded (Total 5,152.0 KiB)

CREATE TABLE IF NOT EXISTS `downloaded` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `file_name` varchar(255) NOT NULL,
  `completed` int(1) NOT NULL,
  `client_ip_addr` varchar(40) NOT NULL,
  `client_access_key` varchar(40) NOT NULL,
  `datetime` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=31475 ;

There are several things you can do to improve the efficiency of the query.

First, index the tables. OK, it's not trivial to do this. And any indexing should be considered in relation with all the queries you are running in the database. Lets pretend that this is the only query or that it is the most crucial one.

We'll break this task into the 2 tables involved. The first table, uploaded, appears - besides the join - only in the select list (all its columns) and in the 2 WHERE conditions:

AND uploaded.active = 1
AND uploaded.nsfw = 0

Two simple equality conditions, so the immediate thought is to make an index on these two columns or better include the joining column (this is a technicality, as the table is using the MyISAM engine): (active, nsfw, file_name)

ALTER TABLE uploaded
  ADD INDEX active_nsfw_fname_IX
    (active, nsfw, filename) ;

Then we go to the downloaded table. This is more complicated. Its columns do not appear in the select list (only an aggregate result, the count) but they are used in the WHERE, GROUP BY clauses and (the computed count) in the ORDER BY / LIMIT. To complicate matters, one of the conditions is a range condition (>) and not equality:

WHERE downloaded.completed = 1
  AND downloaded.datetime > DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY downloaded.file_name
ORDER BY downloaded_count DESC LIMIT 30

If both were equalities, I'd blindly add an index on (completed, datetime, file_name) but in this case, I would first try an index on (completed, file_name, datetime), i.e. first the column from the equality check, then the column in the grouping and finally the other.

ALTER TABLE downloaded
  ADD INDEX comp_fname_dt_IX
    (completed, file_name, datetime) ;

Another improvement that is often possible in queries that have ORDER BY and LIMIT is to first do this in a derived table and then join the other tables. We can't really do this exactly here but we can try to put this in the derived table.
Notice that from the uploaded table only the columns in the active_nsfw_fname_IX index are used
And that for the downloaded table the columns of the comp_fname_dt_IX index are aligned with the order that queries are (logically) executed (WHERE - GROUP BY - SELECT):

( SELECT COUNT(CASE WHEN d.datetime > DATE_SUB(NOW(), INTERVAL 7 DAY) THEN 1 END)
             AS downloaded_count
       , d.file_name
  FROM downloaded AS d JOIN uploaded AS u
    ON d.file_name = u.file_name
  WHERE d.completed = 1
    AND u.active = 1
    AND u.nsfw = 0
  GROUP BY d.file_name
  ORDER BY downloaded_count DESC LIMIT 30
)

The query finally becomes:

SELECT dc.downloaded_count
     , dc.file_name                 -- you can remove this column from the results
     , up.*                         -- as uploaded has a file_name column
FROM
    ( SELECT COUNT(CASE WHEN d.datetime > DATE_SUB(NOW(), INTERVAL 7 DAY) THEN 1 END)
                 AS downloaded_count
           , d.file_name
      FROM downloaded AS d JOIN uploaded AS u
        ON d.file_name = u.file_name
      WHERE d.completed = 1
        AND u.active = 1
        AND u.nsfw = 0
      GROUP BY d.file_name
      ORDER BY downloaded_count DESC LIMIT 30
    ) AS dc
    JOIN uploaded AS up
      ON dc.file_name = up.file_name
ORDER BY downloaded_count DESC ;                 -- no need for LIMIT here

Testing time! I suggest you add these 2 indexes and then time the queries, both your original one and the one above.

And to answer your question, no it is not possible to index the aggregated column. MySQL has neither computed columns nor materialized views which is what is needed in this case (only MariaDB has implemented persistent, computed columns but that wouldn't help you in this query.)

So, the only way is to try by indexing and rewriting the query to reduce as much as possible the number of rows that will be ordered by - and that's what I essentially tried to accomplish above.

You will still see a "filesort" in the EXPLAIN but it's totally different (in terms of performance) to sort 100 than 10k rows.


Restore non-cdb backup into pdb database I haven't found any information on is it possible to restore a Oracle non-cdb 19.8 backup into a container-database PDB (still 19c) , or not.
The method i have found is not suitable for my situation (convert non-cdb to pdb using DBMS_PDB.DESCRIBE - that seems to imply that the database type is converted + all data is copied during this operation, which is not the same as restoring from a backup).
*Export/import/Golden Gate are not viable options for me.
Has anyone had a similar challenge and what was the best way to get a non-cdb to get into pdb form.

Good example shown here
"Creating PDB from a non-CDB"
https://shivanandarao-oracle.com/2015/10/02/creating-pluggable-databases-part-2/#CreatingPDBfromanonCDB


First field from my return table is being eaten up Another problem with some PostgreSQL stored procedure code...

Same from this question:

I have this function

drop function uptimes(inicio date, fim date);
create or replace function uptimes(inicio date, fim date)
    returns table (id integer, entrada timestamp with time zone,
    saida timestamp with time zone, erro boolean, uptime interval) as
$$
begin
    create temporary table localdata (id_local integer,
      entrada_local timestamp with time zone,
      saida_local timestamp with time zone, erro_local boolean) on commit drop ;
    insert into localdata select * from uptime u
    WHERE u.saida>=inicio and u.entrada<=fim order by u.entrada ;

    alter table localdata add uptime_local interval;
    update localdata set saida_local=fim where saida_local>fim;
    update localdata set entrada_local=inicio where entrada_local<inicio;
    update localdata set uptime_local=saida_local-entrada_local;
    return query select * from localdata;
end;
$$
language plpgsql;

The idea is to fetch some data from a table and treat it by replacing the entrada field for any field that is lower than the inicio parameter by the inicio.

But with it, my query returns

  id  |        entrada         |         saida          | erro |     uptime
------+------------------------+------------------------+------+----------------
 1538 | 2015-10-14 05:02:15-03 | 2015-10-14 05:04:57-03 | f    | 00:02:42
...

and commenting the

    update localdata set entrada=inicio where entrada<inicio;

they returns

  id  |        entrada         |         saida          | erro |     uptime
------+------------------------+------------------------+------+----------------
 1537 | 2015-10-12 07:04:15-03 | 2015-10-14 05:01:56-03 | f    | 1 day 21:57:41
 1538 | 2015-10-14 05:02:15-03 | 2015-10-14 05:04:57-03 | f    | 00:02:42
...

What I want is to replace 2015-10-12 07:04:15-03 to 2015-10-14 00:00:00, that is passed as parameter in inicio

Any suggestions? Maybe I passed some logic error. :(

The short answer: use date_trunc('day', ...) to set the time part of a timestamp to 00:00:00

The long answer: the whole function is needlessly complicated. There is no need to create a table each time and then modify the columns or even update that table.

This can all be done with a single select statement.

If I understood everything correctly, the query you are looking for is something like this:

with adjusted as (
  select id,
         case
            when entrada < inicio then inicio
            else entrada
         end as entrada,
         case
           when saida > fim then fim
           else saida
         end as saida,
         erro
  from uptime u
  WHERE u.saida >= inicio
    and u.entrada <= fim
)
select id,
       date_trunc('day', entrada) as entrada
       date_trunc('day', saida) as saida
       erro
       saida - entrada as updatime
from adjusted
order by u.entrada;

Note that I "truncated" the columns entrada and saida in the final select which means that the calculation of the uptime does include the time part.

So the whole function can be simplified to:

create or replace function uptimes(inicio date, fim date)
    returns table (id integer, entrada timestamp with time zone,
                   saida timestamp with time zone, erro boolean, uptime interval)
as
$$
    with adjusted as (
      select id,
             case
                when entrada < inicio then inicio
                else entrada
             end as entrada,
             case
               when saida > fim then fim
               else saida
             end as saida,
             erro
      from uptime u
      WHERE u.saida >= inicio
        and u.entrada <= fim
    )
    select id,
           date_trunc('day', entrada) as entrada
           date_trunc('day', saida) as saida
           erro
           saida - entrada as updatime
    from adjusted
    order by u.entrada;
$$
language sql;

It is also a good idea to use some kind of prefix for the parameter names, so that they are easier distinguishable from the column names in the query. e.g. p_inicio instead of just inicio


Should I maintain relationships diagrams in the back-end and the front-end? I’m developing an Access 2010 database application. It is split into a back-end, holding all the tables, and a front-end, holding all the queries, forms, etc.
I find the Relationships tool helps provide an extremely valuable visual resource (an ERD), in the back-end, and know that Access uses it to enforce integrity.

So far, however, I’ve never used the same tool in the front-end. I have many more queries than tables but have never seen them in form of an entity-relationship diagram. Have you ever found it useful to diagram your queries using the Relationships tool? Any advice on what practice has worked well for you?

I have never used the relationships tool for anything other than understanding the relationships between base tables on the back end database.  I could see however where it would be very useful on the front end if you have many users of your application who write a lot of ad-hoc queries and you provide them queries stored on the front end database which they can use as an abstraction layer.  On the other hand, if the users only interact with the database via forms I would think the work to add the queries to the relationship tool and connect them up would not be worth it.


Source several files at once with psql I want to execute several sql scripts sequentially with psql as a single transaction to set up my database schema. What is the best way to do this? In the past I know I had a master script that I ran psql against that included the other files however I don't remember the syntax of this script.

Your script could look like this:

BEGIN;
\i file1.sql
\i file2.sql
COMMIT;

Or you could do something like this:

cat file1.sql file2.sql | psql -1 -f -


How to avoid tons of lower(column) in WHERE clause for several LIKE comparisons? I've got to filter data in a string column and thus need to compare the column using NOT LIKE with a bunch of strings.

I'm using SQL Server and my code looks like this:

SELECT history.date,
       history.new_value,
       res.resource_name
FROM   item_history history,
       owned_resource   res
WHERE  (history.attr_name = 'resource_contact' OR history.attr_name = 'Kontakt-UUID')
       AND res.inactive = 0
       AND history.com_par_id = res.own_resource_uuid
       AND lower(history.new_value) NOT LIKE '%tp für%'
       AND lower(history.new_value) NOT LIKE '%rücklieferung%'
       AND lower(history.new_value) NOT LIKE '%rückläufer%'
       AND lower(history.new_value) NOT LIKE '%stoerreserve%'
       AND lower(history.new_value) NOT LIKE '%zentrallager%'
       AND lower(history.new_value) NOT LIKE '%bhs-pool%'
       AND lower(history.new_value) NOT LIKE '%lager halle%'
       AND lower(history.new_value) NOT LIKE '%lager logistik%'
       AND lower(history.new_value) NOT LIKE 'reserve %'
       AND lower(history.new_value) NOT LIKE '%igeko%bhs%'
       AND lower(history.new_value) NOT LIKE '%service%ecg%'
       AND lower(history.new_value) NOT LIKE '%multifunktionsdrucker%'
       AND lower(history.new_value) NOT LIKE 'nn%gisa%raum%'
       AND lower(history.new_value) NOT LIKE '%citrix%admins%'
       AND lower(history.new_value) NOT LIKE '%personalwesen%'
       AND lower(history.new_value) NOT LIKE '%etagendrucker%'
       AND lower(history.new_value) NOT LIKE '%schulungsraum%'
       AND lower(history.new_value) NOT LIKE '%team%raum%'
       AND lower(history.new_value) NOT LIKE  '%beratungsraum%'
       AND lower(history.new_value) != 'reserve'
)

I guess the performance is not the best calling "lower()" over and over. Also, as a programmer, my nails are rolling up seeing so much redundant code.
Unfortunately I did not find a nice way to use a variable or something.
(I want to add that I cannot simply add a new computed column, which would be a good way here, I'm only authorized to read data.)

Any advise to make the code smarter would be appreciable?

I would use join here instead of old writing way
Regarding "lower" are you sure that this is needed?
Did you check collation of new_value?
If it has CI(case insensitive) in collation name it you can avoid using lower.

If you do, you can write something like this

WITH cte
    AS ( SELECT history.date
               ,history.new_value
               ,res.resource_name
               ,LOWER(history.new_value) AS new_value
         FROM   item_history history
         JOIN   owned_resource res
                ON (   history.attr_name = 'resource_contact'
                       OR history.attr_name = 'Kontakt-UUID' )
                   AND res.inactive = 0
                   AND history.com_par_id = res.own_resource_uuid )
SELECT *
FROM   cte
WHERE  new_value NOT LIKE '%tp für%'
       AND new_value NOT LIKE '%rücklieferung%'
       AND new_value NOT LIKE '%rückläufer%'
       AND new_value NOT LIKE '%stoerreserve%'
       AND new_value NOT LIKE '%zentrallager%'
       AND new_value NOT LIKE '%bhs-pool%'
       AND new_value NOT LIKE '%lager halle%'
       AND new_value NOT LIKE '%lager logistik%'
       AND new_value NOT LIKE 'reserve %'
       AND new_value NOT LIKE '%igeko%bhs%'
       AND new_value NOT LIKE '%service%ecg%'
       AND new_value NOT LIKE '%multifunktionsdrucker%'
       AND new_value NOT LIKE 'nn%gisa%raum%'
       AND new_value NOT LIKE '%citrix%admins%'
       AND new_value NOT LIKE '%personalwesen%'
       AND new_value NOT LIKE '%etagendrucker%'
       AND new_value NOT LIKE '%schulungsraum%'
       AND new_value NOT LIKE '%team%raum%'
       AND new_value NOT LIKE '%beratungsraum%'
       AND new_value != 'reserve';


Can filtered indexes help improve queries that are based on a time entered or should this be avoided? I'm just learning about the new filtered indexes in MS SQL 2008 and I'm trying to understand where they would hurt and where they would help.

I can see that adding a filtered index to an employees table to index only the current employees would be a good idea.

But what about applying a couple filtered indexes to a large table based on time. For example, say I have a table that has an enteredOn date/time field and the table has many years worth of data. If queries are typically done based on date and some other field such as enteredby would it be good to have a few filtered indexes that have a where clause limiting the index to a timespan? In my example the query would be something like

select enteredOn, Description, ...
from myTable
where enteredOn > '2011/01/01' and enteredBy = "My, User"

Would having an filtered index for each year be reasonable or would that cause to much of a problem when the below was queried for instead?

select enteredOn, Description, ...
from myTable
where enteredOn > '2009/06/01' and eneteredOn < '2010/06/01' and enteredBy = "My, User"

BitOff,

It depends on a few factors, there is no one answer for every scenario. For example, you may want to partition and archive some old data onto a dedicated set of disks becvause it is accessed infrequently. I doubt you would consider swapping such a partition for a filtered index. And I doubt you want to create a partition for all the values of NULL in a specific column as opposed to using a filtered index.

You should read the partitioning whitepaper by Kimberley Tripp at http://msdn.microsoft.com/en-us/library/ms345146%28v=sql.90%29.aspx, and the filtered index design guidelines found at http://technet.microsoft.com/en-us/library/cc280372.aspx.

Those two links should help point you in the right direction as far as all the design considerations you need to take into account.


How do I limit a SQL stored procedure to be run by one person at a time? I have a stored procedure that basically selects values from one table and inserts them to another, a sort of archiving. I want to avoid multiple people from doing that at the same time.

While this procedure is running, I don't want anyone else to be able to start it, however I don't want serialization, the other person to run the procedure after I am done with it.

What I want is for the other person trying to start it to get an error, while I am running the procedure.

I've tried with using sp_getapplock, however I can't manage to completely stop the person from running the procedure.

I also tried finding the procedure with sys.dm_exec_requests and blocking the procedure, while this does work, i think it's not optimal because on some servers I don't have the permissions to run sys.dm_exec_sql_text(sql_handle).

What is the best way for me to do this?

To add to @Tibor-Karaszi's answer, setting a lock timeout doesn't actually produce an error (I've submitted a PR against the docs).  sp_getapplock just returns -1, so you have to check the return value.  So like this:

create or alter procedure there_can_be_only_one
as
begin
begin transaction

  declare @rv int
  exec @rv = sp_getapplock 'only_one','exclusive','Transaction',0
  if @rv < 0
   begin
      throw 50001, 'There is already an instance of this procedure running.', 10
   end

  --do stuff
  waitfor delay '00:00:20'

commit transaction
end


Subquery can't find column from superquery's join I'm running into some trouble with SQL: Basically I'm trying to get a result set back that contains a sum of ALL questions asked to employees (grouped by company) and also add the "onetime_items" which are manually added items in a different table.

I currently have this SQL statement (I'm using MySQL):

SELECT
CONCAT_WS(
    ', ', count(DISTINCT CONCAT(emailaddress, '_', e.id)),
    (
        SELECT GROUP_CONCAT(items SEPARATOR '; ') as OneTimeItems
        FROM (
            SELECT CONCAT_WS(
                ': ', oi.item_name, SUM(oi.item_amount)
            ) items
            FROM onetime_item oi
            WHERE oi.company_id = e.company_id
            AND oi.date BETWEEN '2015-12-01'
            AND LAST_DAY('2015-12-01')
            GROUP BY oi.item_name
        ) resulta
    )
) as AllItems,
e.id,
LEFT(e.firstname, 1) as voorletter,
e.lastname
FROM question q
LEFT JOIN employee e ON q.employee_id = e.id
WHERE 1=1
AND YEAR(created_at) = '2015'
AND MONTH(created_at) = '12'
GROUP BY e.company_id

Now I get the following error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'e.company_id' in where clause

The dates used are dummy dates and the 1=1 at the bottom where clause is because I'm generating the where statement based on user input values.

All columns DO exist in the table employee and the left join works ( I tried entering an id manually instead of using the column reference and it worked, I got the right result back)

I'm also sorry but I'm not allowed to post a table schema or anything related to the DB structure online.

Any idea as to why the reference to e.company_id fails?

EDIT:
This is the result set I need:

5, Het is je verjaardag: 1; Skivakantie: 1; Telefonische leadvergoeding: 8

The construction is as follows:
5 = leads
comma separated to get the rest.
Leads are the unique combination from the Questions table, and the rest of the results are constructed from onetime_items.

EDIT 2: SQL fiddle keeps throwing an error at me so I'm just going to post a small made up DB with a little bit of made up data: http://pastebin.com/cCveVtGr

The cause of the problem was identified by @Phil in the comments:

  Probably because it's nested too deep

You have 2 layers of nesting and the reference of table e cannot "see" through these 2 layers in MySQL.

Correlated inline subquery can usually be converted to derived tables and then LEFT joined to the other tables in the FROM clause but they have to be turned into uncorrelated (in MySQL. In other DBMS, you could use a LATERAL join or the similar OUTER APPLY.

A first rewrite to get the job done:

SELECT
    CONCAT_WS(
        ', ', count(DISTINCT CONCAT(q.emailaddress, '_', e.id)),
        dv.OneTimeItems
    ) as AllItems,
    e.id,
    LEFT(e.firstname, 1) as voorletter,
    e.lastname
FROM question q
LEFT JOIN employee e ON q.employee_id = e.id
LEFT JOIN
    (
        SELECT company_id,
               GROUP_CONCAT(items SEPARATOR '; ') AS OneTimeItems
        FROM (
            SELECT oi.company_id,
                   CONCAT_WS(
                ': ', oi.item_name, SUM(oi.item_amount)
            ) items
            FROM onetime_item oi
            WHERE oi.date BETWEEN '2015-12-01'
                              AND LAST_DAY('2015-12-01')
            GROUP BY oi.company_id, oi.item_name
        ) resulta
        GROUP BY company_id
    ) AS dv
    ON dv.company_id = e.company_id
WHERE 1=1
AND YEAR(q.created_at) = '2015'
AND MONTH(q.created_at) = '12'
GROUP BY e.company_id ;

Test in SQLfiddle.

Unrelated to the issue comments:

There is GROUP BY e.company_id while the select list has e.id,  LEFT(e.firstname, 1), e.lastname. All these will give arbitrary result from a (more or less random) employee for each company - or even in extremely rare cases arbitrary results from 2 or 3 different employees! MySQL allowed (before 5.7) such bad use of group by that could cause erroneous results. It has been fixed in 5.7 and the default settings would reject this query.
The condition:

YEAR(created_at) = '2015' AND MONTH(created_at) = '12'

cannot make use of indexes. It's better to rewrite with either BETWEEN if the column is of DATE type of with an inclusive-exclusive range condition, which works flawlessly with any datetime type (DATE, DATETIME, TIMESTAMP) of any precision:

-- use only when the type is DATE only
date BETWEEN '2015-12-01' AND LAST_DAY('2015-12-01')

or:

-- use when the type is DATE, DATETIME or TIMESTAMP
created_at >= '2015-12-01' AND created_at < '2016-01-01'


How to find who changed a table value in SQL Server 2014? We have a few tables on a database called mydb which is being used by applications to store, or retrieve records. Somehow, someone changed a value on the test table and that caused a serious issue.

We are receiving constant data from different sources, when the data is being updated on the table, a trigger fires and checks for the specific value, if the values is 360 then it will update the rows, else it will do nothing.

Now I have two questions:

I want to know who changed that value 4 days ago?
I dont see any successful login (user login) in the SQL server error log. I assume that someone has used a generic user account named user. Now who used that account to login?

The value on the table is supposed to be 350, but it was changed to 800.

Any tips?

SQL Server does not keep track of who does what without you setting it up, now is a great time to set this up because you'll catch it the next time. You can try and read the Transaction Logs to see what happened (minus the principal account used to make the change), but otherwise you'll want something set up in the future.

The best approach to see who changed what and when is putting triggers on your tables when they change, you can audit the information by inserting what changed into the audit table. I add fields such as getdate and suser so I can see who modified and when it was modified.

An even lighter version is logging sp_whoisactive or other monitoring tools using DMV's. This will not catch everything.

CT / CDC is another option, however they will not reveal who made the change. You can make an alteration to the triggers so it would also use suser, but it will only reveal the account running CT / CDC.

If upgrading to 2016 or higher is a viable option, I would recommend looking at temporal tables combined with SQL Auditing. Temporal tables would give you the in-depth analysis of what changed and the SQL Audit would give you insight into who made said changes. Here's an excellent answer by Nic on stack exchange to set up the SQL Audit.


Why tmp tables created when connected to one database I am wondering why temp tables created while switching to database. When I login to the mysql server, I have queried current session temp tables as follows:

mysql> show status like '%tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 6     |
| Created_tmp_tables      | 0     |
+-------------------------+-------+

3 rows in set (0.00 sec)

mysql> \u database_name
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show status like '%tmp%';

+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 6     |
| Created_tmp_tables      | 3     |
+-------------------------+-------+
3 rows in set (0.00 sec)

Any idea why were 3 temp tables created ?

Look at the message

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Table info is being read from the INFORMATION_SCHEMA. Why ?

According to the MySQL Documentation on auto rehashing for the mysql client:

  --auto-rehash

  Enable automatic rehashing. This option is on by default, which
  enables database, table, and column name completion. Use
  --disable-auto-rehash to disable rehashing. That causes mysql to start faster, but you must issue the rehash command or its # shortcut if
  you want to use name completion.

  To complete a name, enter the first part and press Tab. If the name is
  unambiguous, mysql completes it. Otherwise, you can press Tab again to
  see the possible names that begin with what you have typed so far.
  Completion does not occur if there is no default database.

For your session, internal temp tables with info from INFORMATION_SCHEMA is needed for hitting the tab character in the event multiple columns or multiple tables have the same prefix characters.

CAVEAT

Please note that the INFORMATION_SCHEMA database is comprised of temp tables to begin with : See my post How is INFORMATION_SCHEMA implemented in MySQL?.

Therefore, it should be no surprise that more temp tables are created to hold table and column info for a database you just switched to. This is particularly true if you are not a super user like root@localhost and your grants only go as high as the database level.


Usage of foreign key when the referenced table data will not change I have to design a table where user has 10 fields and 6 are dropdown. Some are single selections and some multiple. Dropdown data is supposed to change very rarely.

For e.g., the user is presented with days of the week and can select any number of days. It is going to be a one to many table UserDayPref(user_id, day). Now I have 3 choices:

Create table Days and make day field in UserDayPref a foreign key to Days table .
Make day field as string and save the name of the day.
Map Name of the day to an integer and save the integer.

I am inclined to implement the 3rd option for this and all other dropdown fields. I am pretty sure data will edited rarely.

Is this the right way to do it? Or going with Foreign Keys is the right way?

Even If you want to go with the 3rd Option you can have a Foreign Key.

As in your example you are discussing about the days of the week they will never change.

In a case other then Days of the week where there is a probability of change, in my opinion you must go for a foreign-Key in order to maintain your Database Integrity.


CouchDB and document versioning I'm currently working on a wiki-esque application using CouchDB and am trying to implement a document versioning scheme.  The way I see it there are two ways of doing this:

Store each version as a separate document
Store older versions as attachments to a single document.

Right now, I've got a form of #1 working.  When a user edits a document and saves it, the back-end first copies the previous revision to a new document and then saves the new version.  Each document has a 'history' array that contains data on each version (the document _id of the old version, a timestamp, the editor, etc.).

Since this history array could get pretty lengthy for a frequently updated document, I have a view that fetches a document sans history during a normal read (and another view for fetching the history).

My question is this:  I feel uneasy about my current approach and have been thinking about changing to the 'attachment' method.  But I'm not sure.  I'm hoping someone who knows CouchDB better than I (I've only been at this for a couple weeks -- and this is my first project using CouchDB... and NoSQL) can tell me what the pros and cons are of each approach.  Or is there perhaps some other versioning scheme that I'm overlooking?

Storing only changes will be a good idea, because storing older documents as separate documents or attachments to the final revision of database will create an overhead to database server.

When ever you change a key value in your document, add a new key named _h_i_s_<key_name>. In the newly created( or created during last update), append objects like below after every edit/update:-

{
key_name: "Hello",
_h_i_s_key_name:{time_of_update:value_of_key_name_before_update},
....
}

or

    {
    key_name: "Hello",
    _h_i_s_key_name:[{time:time_of_update,value:value_of_key_name_before_update}, {time:time_of_last_update,value:value_of_key_name_before_last_update}],
    ....
    }

This approach will save a lot of disk space and replication bandwidth in long run.


Error 2003: Setting up MySQL Replication Trying to setup MySQL Replication using this guide here : http://wpguru.co.uk/2013/05/how-to-setup-mysql-masterslave-replication-with-existing-data/ I was able to do this on two random VMs running the same version of MySQL and it worked perfectly fine. I am trying to do this in a production environment and it doesn't seem to be working. The Slave is unable to connect to the master even though I can connect just fine using the mysql client.

140821 11:53:11 [ERROR] Slave I/O: error connecting to master 'repl@<host-ip>:7' - retry-time: 3306  retries: 86400, Error_code: 2003

The Slave is running 5.5.38-0ubuntu0.14.04.1 and the Master Server is running 5.5.34-0ubuntu0.12.04.1-log. I can telnet from the slave to the master. I can also connect using mysql -u repl -p -h <master-host-ip> just fine. I am currently stuck at this point and out of ideas.

I found the problem. The slave was trying to connect to port 7 instead of port 3306 for some reason. I had to manually specify that as part of CHANGE MASTER TO command and add MASTER_PORT=3306 and it works perfectly now.


Correct way to perform backup of Cassandra/Scylladb What's the correct(advisable) method to backup a cassandra or scylladb so that we can restore it on development environment with ease?

For Scylla, please look at the following official documentation:

Backup your Data

  Full Backup Snapshots are taken using nodetool snapshot.
  Enabling the incremental backup (disabled by default) will create a hard-link from each SSTable, right after it is flushed, to a backups directory.
  For a complete point in time backup you need: a snapshot plus incremental backups and commit logs since that time of the snapshot. Make sure to clean unnecessary incremental backup, Scylla will not do it for you.

Restore from a Backup and Incremental Backup

  Restoring a keyspace from backup requires all snapshot files of the tables, and incremental backup files taken after the snapshot - if available. Before restoring from backup, you should truncate the table data, making sure existing data will not override the restored one.

See the linked documentation for detailed instructions.


Postgresql - Segmentation fault (core dumped) I'm working on postgresql code (server side).
I installed a version from source code following the official tutorial:
http://www.postgresql.org/docs/8.4/interactive/install-short.html

I did same modification on code, so I need to install again postgresql, but this time my version.

I renamed the /usr/local/pgsql/ in /usr/local/pgsql2/ and I did:

make distclean
./configure
make
sudo make install
sudo mkdir /usr/local/pgsql/data
sudo chown darkcoffeelinux /usr/local/pgsql/data

until now everything went well, but with the command

/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data/

I got this error:

The files belonging to this database system will be owned by user "darkcoffeelinux".
This user must also own the server process.

The database cluster will be initialized with locale en_US.UTF-8.
The default database encoding has accordingly been set to UTF8.
The default text search configuration will be set to "english".

fixing permissions on existing directory /usr/local/pgsql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 24MB
creating configuration files ... ok
creating template1 database in /usr/local/pgsql/data/base/1 ... ok
initializing pg_authid ... Segmentation fault (core dumped)
child process exited with exit code 139
initdb: removing contents of data directory "/usr/local/pgsql/data"

Additional information: I'm using kubuntu, postgresql 8.4.15

I think the permissions are correct because otherwise you wouldn't have gotten here.

My guess is that a modification you have made has caused the problem.  This is particularly the case given that it is a segmentation fault.  What you really need to do is look at the call stack at the time the core was dumped and see if you can isolate where in the code this is happening, and then back track to see what modifications you have made may have caused this.  Given that you have modified the code, the first step is to check and see if you can reproduce as kgrittn says, but I am willing to bet that this will only show up with your modified code.

The next step is likely to be to run initdb inside a debugger and review the conditions at time of crash.  At this point standard C debugging practices are really your best bet.


Compare content of databases in Oracle We have a request to build an ASP.Net application that will be used as an interface to launch commands on Oracle databases.

We need to find a way to compare the content of 2 databases. We prefer not to code this process. Instead we would like to rely on Oracle to do it (we would like to call Oracle commands).

What is the best approach for this?

We are using Oracle 10.x

Any help will be appreciated

This is what you could do

for each table in each database in each environment add a column called LAST_MODIFIED_DATE TIMESTAMP(6)
add a before insert or update trigger to set LAST_MODIFIED_DATE=CURRENT_TIMESTAMP for each table
be sure the timezone of each database is correctly set in the NLS Parameters or you will be getting odd answers.  I assume each database is in the same timezone
create a table in a logging schema to record the list of changed values, something along the lines of

CREATE TABLE delta_changes
( P_KEY NUMBER(20)
table_name VARCHAR2 (250),
id NUMBER(10),
change_date TIMESTAMP(6),
logging_date TIMESTAMP(6) DEFAULT CURRENT TIMESTAMP(6));

and create a sequence for the primary key and a pre insert trigger on delta_changes
create a database link between the two databases
create a packaged procedure along these lines
create a job to run the packaged procedure on a schedule of your choice

PROCEDURE  get_changes(table_name_in := NULL)    is

CURSOR all_changes is
SELECT table_name
FROM dba_tables
WHERE owner =  your_schema;
v_execute  VARCHAR2(1000);
BEGIN

IF table_name_in IS NULL THEN

FOR EACH t_name in all_changes LOOP
v_execute := 'INSERT INTO  delta_changes (table_name,id,change_date,logging_date)
VALUES ('||t_name.table_name||',(SELECT ID, LAST_MODIFIED_DATE, CURRENT_TIMESTAMP FROM '||t_name.table_name||', '||t_name.table_name||'@TheOtherDatabase WHERE '||t_name.table_name||'.primary_key = '||t_name.table_name||'.primary_key||'@TheOtherDatabase  and t_name.table_name.LAST_MODIFIED_DATE > t_name.table_name.LAST_MODIFIED_DATE@TheOtherDatabase;';
EXECUTE IMMEDIATE v_execute;
END LOOP;
ELSE
v_execute :='INSERT INTO  delta_changes (table_name,id,change_date,logging_date)
VALUES ('||table_name_in||',(SELECT ID, LAST_MODIFIED_DATE, CURRENT_TIMESTAMP FROM '||table_name_in||', '||table_name_in||'@TheOtherDatabase WHERE '||table_name_in||'.primary_key = '||t_name.table_name||'.primary_key'||'@TheOtherDatabase  and t_name.table_name.LAST_MODIFIED_DATE > t_name.table_name.LAST_MODIFIED_DATE@TheOtherDatabase;';
END IF;
--add exception handling here-log all errors to another table and RAISE error again
END GET_CHANGES;

This solution has a number of ways it could be improved such as:

use bind variables
which users should have execute on this packaged procedure?
or use Oracle advanced queues to log the changes on one database and then extract them to compare on the other
please excuse my syntax as I'm doing this by memory


AlwaysOn Availability Groups back up schedules We have AlwaysOn Availability Groups primary and secondary replicas where the secondary replica is preferred for back up jobs to take place as long as it is available. I created back up jobs on both replicas and scheduled the jobs at the secondary replica.

My question is: - Do I have to schedule the jobs at the primary replica and then disable them until fail-over occurs and then manually enable jobs, or what is the best way to deal with these please?

My question is: - Do i have to schedule the jobs at primary replica and then disable them until fail-over occurs and then manually enable jobs or what is the best way to deal with these please?

No, you should just add a check with the new function sys.fn_hadr_backup_is_preferred_replica

if sys.fn_hadr_backup_is_preferred_replica('your db name') <> 1
begin
    print 'This is not preferred backup replica .....'
        -- If this is not the preferred replica, exit (without error).
end
else
begin
    print 'This is the preferred replica .. continue with backup'
        -- If this is the preferred replica, continue to do the backup.
        -- here goes the backup command .....
end

Alternatively, you can use SQL Server Maintenance Solution - by Ola hallengren which supports AlwaysON as well.


Some records are missing while selecting from table I am trying to match the records having MAX(WORKITEM_ID) but some PROCESS_INSTANCE_ID are not coming though they are present in WORKITEM table.

select WI.WORKITEM_ID as WORKITEMID,
       PI.PROCESS_INSTANCE_ID PIID,WS.WS_LABEL as WSNAME

from  WORKITEM WI,PROCESSINSTANCE PI,TASKASSIGNMENT_V4TA,
       SDI_WIDGET_WORKSTEP WS

where WS.WS_NAME = WI.WORKSTEP_NAME and
      TA.PROCESS_INSTANCE_ID = PI.PROCESS_INSTANCE_ID and
      PI.status in ('PI_ACTIVATED','PI_COMPLETED')and
      WI.PROCESS_INSTANCE_ID = PI.PROCESS_INSTANCE_ID
      and WI.status in ('I_ASSIGNED','I_AVAILABLE')
      and WI.WORKITEM_ID =
(select MAX(WORKITEM_ID)from (select WITEM.WORKITEM_ID from WORKITEM WITEM
 where **WITEM.PROCESS_INSTANCE_ID = WI.PROCESS_INSTANCE_ID**) as WORKITEMID)

You didn't state which version of SQL Server you are using, but I'll assume it is 2005 or later, and therefore, will suggest using a CTE . And, as Skagalak mentioned, I've rewritten the joins.

I wasn't sure whether you wanted the max value from the whole WORKITEM table or if you wanted to group it by PROCESS_INSTANCE_ID. The example below groups the  MAX(WORKITEM_ID). If you don't want that, just remove  the GROUP BY clause from the CTE.

CAVEAT: I've checked for syntax, but since I couldn't test against a data set I give no guarantees that this will give the desired results. It is merely an example of how you might want to structure the query.

;WITH cte AS (SELECT MAX(WORKITEM_ID) AS max_workitem_id, PROCESS_INSTANCE_ID
                  FROM WORKITEM
                  GROUP BY PROCESS_INSTANCE_ID
                 )
    SELECT WI.WORKITEM_ID AS WORKITEMID,
           PI.PROCESS_INSTANCE_ID PIID,WS.WS_LABEL AS WSNAME
    FROM WORKITEM AS WI
        INNER JOIN PROCESSINSTANCE AS [PI] ON WI.PROCESS_INSTANCE_ID = [PI].PROCESS_INSTANCE_ID
        INNER JOIN TASKASSIGNMENT_V4 AS TA ON TA.PROCESS_INSTANCE_ID = [PI].PROCESS_INSTANCE_ID
        INNER JOIN SDI_WIDGET_WORKSTEP AS WS ON WS.WS_NAME = WI.WORKSTEP_NAME
        INNER JOIN cte ON WI.WORKITEM_ID = cte.max_workitem_id AND cte.PROCESS_INSTANCE_ID = WI.PROCESS_INSTANCE_ID
    WHERE [PI].status IN ('PI_ACTIVATED','PI_COMPLETED')
          AND WI.status IN ('I_ASSIGNED','I_AVAILABLE')


SQL Server Deadlock on two updates due to index lock order I have two UPDATEs - one locks the CI first and then the NCI (on status) because the status column is also being updated.  The other already owns a U lock on the NCI because it knows it is changing and then tries to get a U lock on the CI.

What is the easiest way to force these to serialize?  It seems odd to use a TABLE-level hint since this is an internal indexing issue - there is only one table involved - will UPDLOCK, HOLDLOCK automatically just apply to all indexes needed on that table and thereby force it to be serialized?

Here are the queries:

UPDATE htt_action_log
SET status = 'ABORTED', CLOSED = GETUTCDATE()
WHERE transition_uuid = '{F53ADDDA-E46B-4726-66D8-D7B640B66597}'
AND status = 'OPEN';

That one X locks the row in the CI (on CREATED column) and then attempts to X lock on the NCI which includes the status column.

UPDATE htt_action_log
SET status = 'RUNNING {36082BCD-EB52-4358-E3D3-4D96FD5B9F0F} 1360094342'
WHERE action_uuid = (SELECT TOP 1 action_uuid
                     FROM htt_action_log
                     WHERE transition_uuid = '{F53ADDDA-E46B-4726-66D8-D7B640B66597}'
                         AND status = 'OPEN'
                     ORDER BY action_seq)

This one U locks the same NCI - for the nested query I guess, then goes to lock the CI for the update.

Thus the order produces the deadlock.

Easiest solution is to force the two queries to completely block - i.e. serialize.  What's the easiest way to force that, just put WITH (UPDLOCK, HOLDLOCK) on the references to the table (one in the first and two in the second)?

DDL:

Note client has more indexes on this table which should be affected by this update, but are not mentioned in the deadlock graph.

CREATE TABLE [dbo].[HTT_ACTION_LOG](
    [ACTION_UUID] [varchar](128) NOT NULL,
    [TRANSITION_UUID] [varchar](128) NOT NULL,
    [STATUS] [varchar](128) NOT NULL,
    [CREATED] [datetime] NOT NULL,
    [CLOSED] [datetime] NULL,
    [ACTION_SEQ] [int] NOT NULL,
    [ACTION_TYPE] [varchar](15) NOT NULL,
    [ACTION_NAME] [varchar](50) NOT NULL,
    [ACTION_RESULT] [varchar](8000) NULL,
    [PENDING_SINCE] [datetime] NULL,
    [ACTION_SQL] [varchar](8000) NULL,
    [ERROR_OK] [int] NULL,
    [ERROR_COND] [varchar](2048) NULL,
    [RETRY] [varchar](128) NULL,
 CONSTRAINT [PK_HTT_ACTION_LOG_1] UNIQUE NONCLUSTERED
(
    [ACTION_UUID] ASC
)
)

CREATE CLUSTERED INDEX [IK_HTT_ACTION_LOG_2] ON [dbo].[HTT_ACTION_LOG]
(
    [CREATED] ASC
)

CREATE NONCLUSTERED INDEX [IK_HTT_ACTION_LOG_1] ON [dbo].[HTT_ACTION_LOG]
(
    [TRANSITION_UUID] ASC,
    [STATUS] ASC
)
INCLUDE ( [ACTION_UUID],
[ACTION_SEQ])

CREATE NONCLUSTERED INDEX [IK_HTT_ACTION_LOG_4] ON [dbo].[HTT_ACTION_LOG]
(
    [ACTION_UUID] ASC,
    [STATUS] ASC
)

CREATE NONCLUSTERED INDEX [missing_index_11438530_11438529_HTT_ACTION_LOG] ON [dbo].[HTT_ACTION_LOG]
(
    [TRANSITION_UUID] ASC,
    [ACTION_TYPE] ASC
)
INCLUDE ( [ACTION_NAME])

CREATE NONCLUSTERED INDEX [missing_index_7207590_7207589_HTT_ACTION_LOG] ON [dbo].[HTT_ACTION_LOG]
(
    [STATUS] ASC
)
INCLUDE ( [CREATED],
[PENDING_SINCE],
[ACTION_NAME])

CREATE NONCLUSTERED INDEX [missing_index_8535421_8535420_HTT_ACTION_LOG] ON [dbo].[HTT_ACTION_LOG]
(
    [TRANSITION_UUID] ASC
)
INCLUDE ( [ACTION_UUID],
[STATUS])

ALTER TABLE [dbo].[HTT_ACTION_LOG] SET (LOCK_ESCALATION = AUTO)

ALTER TABLE [dbo].[HTT_ACTION_LOG]  WITH CHECK ADD  CONSTRAINT [FK_HTT_ACTION_LOG_1] FOREIGN KEY([TRANSITION_UUID])
REFERENCES [dbo].[HTT_TRANSITION_LOG] ([TRANSITION_UUID])

ALTER TABLE [dbo].[HTT_ACTION_LOG] CHECK CONSTRAINT [FK_HTT_ACTION_LOG_1]

ALTER TABLE [dbo].[HTT_ACTION_LOG] ADD  DEFAULT ('OPEN') FOR [STATUS]

ALTER TABLE [dbo].[HTT_ACTION_LOG] ADD  DEFAULT (getutcdate()) FOR [CREATED]

ALTER TABLE [dbo].[HTT_ACTION_LOG] ADD  DEFAULT ((0)) FOR [ERROR_OK]

The optimal index for those two queries is not far from the existing definition of the IK_HTT_ACTION_LOG_1 index (add ACTION_UUID as an INCLUDE to the improved index below):

CREATE INDEX nc1
ON dbo.HTT_ACTION_LOG
(
    TRANSITION_UUID,
    STATUS,
    ACTION_SEQ
);

The first query is:

UPDATE dbo.HTT_ACTION_LOG
SET [STATUS] = 'ABORTED',
    CLOSED = GETUTCDATE()
WHERE
    TRANSITION_UUID = '{F53ADDDA-E46B-4726-66D8-D7B640B66597}'
    AND [STATUS] = 'OPEN';

Giving the following execution plan:

The second query can be expressed this way:

UPDATE ToUpdate
SET [STATUS] = 'RUNNING {36082BCD-EB52-4358-E3D3-4D96FD5B9F0F} 1360094342'
FROM
(
    SELECT TOP (1)
        hal.[STATUS]
    FROM dbo.HTT_ACTION_LOG AS hal
    WHERE
        hal.transition_uuid = '{F53ADDDA-E46B-4726-66D8-D7B640B66597}'
        AND hal.[STATUS] = 'OPEN'
    ORDER BY
        hal.ACTION_SEQ ASC
) AS ToUpdate;

Giving this execution plan:

Both queries now access the same resources in the same order, while locking many fewer rows on the read side of the plan. The execution engine will automatically take UPDLOCKs when reading the new index, providing the serialization you are looking for.


Merge Replication Subquery Filters I am using merge replication with SQL 2012. I have a number of articles in my publication and will have to come up with quite an elaborate set of filters.

If I add a filter on my article it can contain a sub query, but I noticed it doesn't pick up on changes properly.

For instance Table1, and Table2.

Filter on Table1 is

select * from Table1 where id in (select id from Table2)

If more records are added to Table2 I would want the contents of Table1 to be changed accordingly when the sync process next occurs.

I found out that this is not working. Is it because when I tried it Table2 was not an article in my merge replication publication, or is it because merge replication doesn't support subqueries like this and recognising when a filter has changed.

What it would mean is that the sync process would have to scan each filter, and work out what tables the subquery relates to and figure out whether these have changed. Does it do something like this?

UPDATE:

I have tried a number of things, but they don't work.

1) Subquery - doesn't work. Here it confirms that this won't work
    http://support.microsoft.com/kb/324362
2) Subquery calling a function - doesn't work
3) Subquery calling a function which takes a guid parameter which I change every time - doesn't work

So the only other option mentioned is a view, but I cannot get the view to work either.

The first problem is that if I replicate a view it just gets replicated as a view and the underlying table needs to be there. But Simon I read your other post and that is not what you are saying.

I think what you are saying is that I should be able to select a table in my replication publication, and then filter it based on a view like this,

select cols from vwUserFilter

But when I look in management studio I see a filter for my table which looks like this,

SELECT <published_columns> FROM [dbo].[Berm] WHERE

And that part of my filter is fixed, I cannot edit [dbo].[berm] and replace it with a view.

So what I think you are suggesting is that I can change the above to,

SELECT <published_columns> FROM [dbo].[BermView]

And the BermView is a view which filters the Berm table. Is that right, and if so how?

It should work ok. Try using a view for your filter instead. See this article here


Newer Version of SQL Server Less Stable? Our database architect told a client of ours that SQL Server 2014 is a poor choice over 2012 because it lacks the performance and stability advantages that 2012 has.  Everything I've read contradicts this.  Aaron Bertrand says the exact opposite and all the white papers I've read from MS agree too - 2014 is enhanced and resolves some of the AG issues people had with 2012.

Is there any major disadvantage to choosing 2014 over 2012 that I'm missing?

You obviously don't need my confirmation but I can't think of a single possible reason why I would suggest to a client they go with 2012 over 2014. Particularly for support / end of life reasons, but also because of additional features and enhancements to existing capabilities.

Some reasons you might hear:

Performance degradation. Sure, this can happen, but it can also happen when going to 2012 or making any other major change (some of the reasons can also be attributed to migration without upgrade, failover, etc). Regressions are an expected part of change, and it's easy to blame an upgrade (just ask the VSTS team).
Higher cost. I've seen a few claims that 2014 costs more than 2012 for the same configuration, but haven't observed any licensing changes that would cause that to happen except that standby secondaries now require software assurance to be licensed. The other aspect is if someone without SA planned poorly and bought 2012 and now wants 2014 - yeah, that's going to cost them.

Generally worse performance? Less stability? No, both seem laughable to me. Ask your drunk friend for some real evidence, and maybe keep him distanced from clients, too. :-)


sql server - NoSync subscriptions will need to be dropped and recreated I am having this error message from the sql server replication monitor:

  Error messages: The subscription(s) have been marked inactive and must
  be reinitialized. NoSync subscriptions will need to be dropped and
  recreated. (Source: MSSQLServer, Error number: 21074) Get help:
  http://help/21074

The publication is SupplierDB.

I have just generated an snapshot, as you can see on the picture below.

I have followed the answers and comments and not sure what else,
my subscription is now working, however, it shows up twice now, and I can't find a way to get rid of the one that is not working.

I got it double for some reason.

You can update the registry of the Subcriber

here you will see the actual status

select * from [Publish_Database]..syssubscriptions

here the update for all articles subscribes

update [Publish_Database]..syssubscriptions set status=2 where srvname='server'

After that the snapshot should become effective again


Adding LIMIT changes PostgreSQL plan from good to a bad one I have a query on a typical EAV schema:

SELECT contacts.id
FROM contacts
         LEFT OUTER JOIN (SELECT DISTINCT contacts_values.company_id AS company_id, contacts_values.id AS id
                          FROM contacts_values
                          WHERE contacts_values.field_id = '\x000000000000000000000000'
                            AND contacts_values.field_name = 'facebook'
                            AND nimble_contact_value_normalize(nimble_skip_long_values(contacts_values.value)) =
                                nimble_contact_value_normalize('http://www.facebook.com/jon.ferrara')) AS anon_1
                         ON anon_1.company_id = contacts.company_id AND anon_1.id = contacts.id
         LEFT OUTER JOIN (SELECT DISTINCT contacts_values.company_id AS company_id, contacts_values.id AS id
                          FROM contacts_values
                          WHERE contacts_values.field_id = '\x000000000000000000000000'
                            AND contacts_values.field_name = 'last_name'
                            AND nimble_contact_value_normalize(nimble_skip_long_values(contacts_values.value)) =
                                nimble_contact_value_normalize('Ferrara')) AS anon_2
                         ON anon_2.company_id = contacts.company_id AND anon_2.id = contacts.id
         JOIN contacts__aggregated AS contacts__aggregated_1
              ON contacts__aggregated_1.company_id = contacts.company_id AND
                 contacts__aggregated_1.contact_id = contacts.id AND contacts__aggregated_1.field_name = 'names'
WHERE contacts.company_id = '\x4c2118ad54397f271b000000'
  AND (anon_1.id IS NOT NULL OR anon_2.id IS NOT NULL)
ORDER BY contacts__aggregated_1.value ASC
LIMIT 30 OFFSET 0;

My problem is that the LIMIT clause in this query makes the planner choose a bad plan with nested loops: https://explain.depesz.com/s/Mute. Running the same query after SET ENABLE_NESTLOOP TO OFF I am getting a much more efficient plan: https://explain.depesz.com/s/b5kn. Removing the LIMIT from the query results in a similar plan: https://explain.depesz.com/s/wDqE.

One thing that concerns me is that the cost of the LIMIT node in the bad nested loop plan is just a fraction of the cost of its subnode. But for the better merge join plan LIMIT node has the same cost as its subnode. How could it be this way? And what can I do to make the planner pick up a better plan?

We are running PostgreSQL 10.10.

Tell PostgreSQL not to use the index by using

ORDER BY contacts__aggregated_1.value + 0 ASC

The problem is that PostgreSQL thinks it will be fastest if it scans contacts__aggregated in the ORDER BY order using the index, uses a nested loop to add rows from the other tables and filters out values that don't satisfy the conditions.

This is often a good strategy if not many values get filtered out.

It produces a bad plan, however, if

there are fewer rows that satisfy the filter condition than PostgreSQL estimates
the rows that satisfy the filter condition all have high value

One of these things must have happend. Look for high Rows removed by filter in the EXPLAIN (ANALYZE, BUFFERS) output.


Nested Loop has very low estimates due to skewed data On SQL Server 2016 SP2 we have a query that has a very low estimate on the nested loop operator. Due to the low estimate this query also spills to tempdb.

If I'm correct SQL Server 2014+ uses Coarse Histogram Estimation to calculate the estimated number of rows on a join.
But when I execute the query, SQL Server uses the density vector to calculate the number of estimated rows.
Is SQL Server only using the Coarse Histogram Estimation if there is no where clause?

Normally I would use filtered statistics to improve estimations when I have a table with skewed data. But in this case that doesn't seem to work.

Is there a way to improve the estimations on the nested loop?

Using following code you can reproduce the data:

create table MyTable
(
    id int identity,
    field varchar(50),
    constraint  pk_id primary  key clustered (id)
)
go

create table SkewedTable
(
    id int identity,
    startdate datetime,
    myTableId int,
    remark varchar(50),
    constraint  pk_id primary  key clustered (id)
)

set nocount on

insert into MyTable select top 1000 [name] from master..spt_values
go

insert into SkewedTable select GETDATE(),FLOOR(RAND()*(1000))+1,REPLICATE(N'A',FLOOR(RAND()*(40))+1)
go 1000

insert into SkewedTable select GETDATE(),FLOOR(RAND()*(1000))+1,REPLICATE(N'A',FLOOR(RAND()*(40))+1)
go

CREATE NONCLUSTERED INDEX [ix_field] ON [dbo].[MyTable]([field] ASC)
go

CREATE NONCLUSTERED INDEX [ix_mytableid] ON [dbo].[SkewedTable]([myTableId] ASC)
go

--95=varchar in sys.messages
set nocount off

;with cte as
(
    select GETDATE() as startdate ,95 as myTableId, REPLICATE(N'B',FLOOR(RAND()*(40))+1) as remark
    union all
    select * from cte
)
insert into skewedtable select top 40000 * from cte
option(maxrecursion 0)
go

update statistics mytable with fullscan
go

update statistics skewedtable with fullscan
go

Normally I would use filtered statistics to improve estimations when I have a table with skewed data. But in this case that doesn't seem to work.

You should find the following filtered statistic helpful:

CREATE STATISTICS [stats id (field=varchar)]
ON dbo.MyTable (id)
WHERE field = 'varchar'
WITH FULLSCAN;

This gives the optimizer information about the distribution of id values that match field = 'varchar', giving a much better selectivity estimate for the join:

The execution plan above shows exactly correct estimates with the filtered statistic, leading the optimizer to choose a hash join (for cost reasons).

This distribution information is much more important than the exact method used by the estimator to match the join histograms (fine or coarse alignment), or even the general assumptions (e.g. simple join, base containment).

If you can't do that, your options are broadly as outlined in answer to your previous question Sort spills to tempdb due to varchar(max). My preference would probably be an intermediate temporary table.


Full Text Search in SQL 2008 When I attempt to search in the full-text catalog which is my created earlier, I haven't been finding any word within database.

For example:

My word: Where
CONTAINSTABLE(TABLO,*,' "*Where*" ')

Here is no problem.

But,

CONTAINSTABLE(TABLO,*,' "*here*" ')

if I wrote a thing like above the line,no rows returning.What should I do?

I don't believe that full text search will match based on the back end of the word being passed in.  You could have it match "Wher*" but not "*here".  This is called prefix matching.

  < prefix term> ::= {"word*" | "phrase*" }

Trying to match based on "*here" would be a VERY expensive match for the full text engine to do.


MySQL: sum time ranges exluding overlapping ones I need to sum up the time which results from multiple time ranges. For example - we have enter / exit ranges of some office:

Query has to:

Exclude overlapping ranges (8:00 - 10:00)
Exclude "missing" part (12:00 - 14:00)

Expected result in this case is 8:00 (8:00 to 12:00 + 14:00 to 18:00)

Example table structure:

DAY        | TIME_ENTER | TIME_EXIT
2016-01-01 | 08:00      | 10:00
2016-01-01 | 08:00      | 12:00
2016-01-01 | 14:00      | 18:00

Expected result:

DAY        | TOTAL
2016-01-01 | 08:00

Table structure:

CREATE TABLE Registry
 (`Date` DATE,
  `Enter` TIME,
  `Exit` TIME);

Example inserts

INSERT INTO Registry
VALUES
 ('2016-01-01', '08:00', '09:00'),
 ('2016-01-01', '08:00', '12:00'),
 ('2016-01-01', '14:00', '18:00')

Original

You want to sum up all time ranges, except those that fit inside other time ranges on the same day:

SELECT DATE_FORMAT(DAY, '%Y-%m-%d')
        ,TIME_FORMAT(SEC_TO_TIME(TIME_TO_SEC(
            SUM(TIMEDIFF(TIME_EXIT,TIME_ENTER))
          )), '%H:%i') AS Total
FROM Table1 AS t
WHERE NOT EXISTS (
  SELECT *
  FROM Table1
  WHERE DAY = t.DAY
  AND TIME_ENTER <= t.TIME_ENTER AND TIME_EXIT >= t.TIME_EXIT
  # not the same row; if you have an ID column, use it instead
  # eg AND NOT ID = t.ID
  AND NOT (TIME_ENTER = t.TIME_ENTER AND TIME_EXIT = t.TIME_EXIT)
)
GROUP BY DAY;

SqlFiddle

Edit: the above solution won't always work, as pointed out by ypercube. The following solution should cover all scenarios.

Revised solution

SELECT DATE_FORMAT(DAY, '%Y-%m-%d')
        ,TIME_FORMAT(SEC_TO_TIME(TIME_TO_SEC(
            SUM(TIMEDIFF(TIME_EXIT,TIME_ENTER))
          )), '%H:%i') AS Total
FROM (
  SELECT   DISTINCT t.DAY
        ,(SELECT MIN(TIME_ENTER) FROM Table1
          WHERE TIME_EXIT BETWEEN t.TIME_ENTER AND t.TIME_EXIT) AS TIME_ENTER
        ,(SELECT MAX(TIME_EXIT) FROM Table1
          WHERE TIME_ENTER BETWEEN t.TIME_ENTER AND t.TIME_EXIT) AS TIME_EXIT
  FROM Table1 t
  ) AS o
GROUP BY DAY;

SqlFiddle


Log shipping and log file: Can the log on the master or slave every be truncated/shrunk? SQL Server 2008, standard log shipping setup (well, using redgate, but it is a pretty normal flow).

On the replica, the log file is pretty big.

Wondering if on the backup/replica servers it is OK to shrink the log in some way?

Thanks!

The log file on the replica follows exactly the file on the master. They can't be different. You can shrink the master and the replica will automatically change to match.


Display phpmyadmin export templates phpmyadmin no longer displays the export templates section after an update

Do you know how to display this section?

Current phpmyadmin version
4.9.5deb2

phpMyAdmin can use a special database, called the phpMyAdmin Configuration Storage, to enable a whole range of advanced features, including the export templates. Along with this, there is usually an additional user, the controluser, added to give phpMyAdmin permissions to work with the database in a shared environment. While phpMyAdmin makes efforts to work around any of these through a zero-configuration feature, it's suggested that to use these features, the database and tables must exist, and the controluser and table names must be set in the configuration file, config.inc.php.
Since you are using the Debian packaged version, the installer should have attempted to create (or migrate) all of this for you automatically. Perhaps there was an authentication problem or some other error during installation. You should try to run the post-install script again with dpkg-reconfigure -plow phpmyadmin.
For a user who has installed their own phpMyAdmin outside the package manager, I would suggest following the installation steps from the documentation; create the control user, import the sql/create_tables.sql, and add the configuration directives to config.inc.php. But in this case, the package manager should do all of that for you and attempting to manage things by hand could cause difficulty.


What happens when you modify (reduce) a column's length? Lets say I have two columns of type NUMBER (without precision, and scale) and VARCHAR(300). I saw that these columns are way too large for my data, so I want to modify them to NUMBER(11) and VARCHAR(10). So If I run this SQL statement:

ALTER TABLE FOO
    MODIFY(BAR NUMBER(10));

Will I be able to do that on nonempty column?
If so, what If there is one value greater than NUMBER(10), will oracle tell me about it?
Will column default values remain unchanged if previously defined?
Will column nullable option remain unchanged?
Will primary, foreign, unique key on that column remain unchanged?
Will constraints involving that columns remain unchanged?
Will indexes on that columns remain unchanged?

Is there any official documentation answering my questions?

The Oracle Administrators Guide says the following:

Use the ALTER TABLE...MODIFY statement to modify an existing column
definition. You can modify column data type, default value, column
constraint, column expression (for virtual columns) and column
encryption.
You can increase the length of an existing column, or decrease it, if
all existing data satisfies the new length. You can change a column
from byte semantics to CHAR semantics or vice versa. You must set the
initialization parameter BLANK_TRIMMING=TRUE to decrease the length of
a non-empty CHAR column.
If you are modifying a table to increase the length of a column of
data type CHAR, realize that this can be a time consuming operation
and can require substantial additional storage, especially if the
table contains many rows. This is because the CHAR value in each row
must be blank-padded to satisfy the new column length.

The Oracle SQL Language Reference has much more detail including the following:

You can change the data type of any column if all rows of the column contain nulls. However, if you change the data type of a column in a materialized view container table, then Oracle Database invalidates the corresponding materialized view.
You can always increase the size of a character or raw column or the precision of a numeric column, whether or not all the rows contain nulls. You can reduce the size of a data type of a column as long as the change does not require data to be modified.The database scans existing data and returns an error if data exists that exceeds the new length limit.
You can modify a DATE column to TIMESTAMP or TIMESTAMP WITH LOCAL TIME ZONE. You can modify any TIMESTAMP WITH LOCAL TIME ZONE to a DATE column.
If the table is empty, then you can increase or decrease the leading field or the fractional second value of a datetime or interval column. If the table is not empty, then you can only increase the leading field or fractional second of a datetime or interval column.
For CHAR and VARCHAR2 columns, you can change the length semantics by specifying CHAR (to indicate character semantics for a column that was originally specified in bytes) or BYTE (to indicate byte semantics for a column that was originally specified in characters). To learn the length semantics of existing columns, query the CHAR_USED column of the ALL_, USER_, or DBA_TAB_COLUMNS data dictionary view.

There is additional information and restrictions in the above documentation.  Here is a demonstration of attempting to reduce the precision of a Number column and reduce the length of a Varchar2.  You can try other changes so you will know what will happen.
--Setup.
DROP TABLE FOO;
CREATE TABLE FOO (BAR Number, BAR2 VARCHAR2(300));
INSERT INTO FOO (SELECT Level, RPAD(to_char(Level),10*Level,to_char(Level))
   FROM DUAL CONNECT BY Level <=20);
COMMIT;
SELECT * FROM FOO;

--Reduce Number to Number(10).
ALTER TABLE FOO MODIFY (BAR NUMBER (10));

--Reduce Varchar2(300) to Varchar2(100) (data would be truncated).
ALTER TABLE FOO MODIFY (BAR2 VARCHAR2(100));

--Reduce Varchar2(300) to Varchar2(200) (no data would be truncated).
ALTER TABLE FOO MODIFY (BAR2 VARCHAR2(200));

The alter statements have the following output:
ALTER TABLE FOO MODIFY (BAR NUMBER (10))
Error report:
SQL Error: ORA-01440: column to be modified must be empty to decrease precision or scale
01440. 00000 -  "column to be modified must be empty to decrease precision or scale"

ALTER TABLE FOO MODIFY (BAR2 VARCHAR2(100))
Error report:
SQL Error: ORA-01441: cannot decrease column length because some value is too big
01441. 00000 -  "cannot decrease column length because some value is too big"

table FOO altered.

Reduce precision by creating a new column.
ALTER TABLE FOO ADD (BAR3 NUMBER(10));
UPDATE FOO SET Bar3 = Bar;
ALTER TABLE FOO DROP COLUMN BAR;
ALTER TABLE FOO RENAME COLUMN BAR3 TO BAR;


Transactional Replication - Snapshot metrics I have an application that is using Transactional Replication. We are using snapshots to initialize the subscriptions. All publishers and subscribers are using SQL Server 2008.

Due to the way our deployment works, I end up having to reinitialize the subscribers every time we do a build. This is time consuming and annoys the business, so I have to make sure that our developers are aware of the impact their changes will have on the size of the snapshots and the time it will take to apply them. I also want to measure the impact of settings changes that I make on the publications and agents (bcp threads, batch size, etc).

The metrics I want to capture are:

The size of the snapshot generated for each publication.
The time it took to generate the snapshot.
The time it took to apply the snapshot at the subscriber.

Is it possible to get this information by querying distribution?  I realize that I could get snapshot size by looking at the filessystem after it is generated, but I would prefer to avoid that if possible.

The metrics I want to capture are:

  The size of the snapshot generated for each publication.
  The time it took to generate the snapshot.
  The time it took to apply the snapshot at the subscriber.

  Is it possible to get this information by querying distribution?

You cannot get such metrics using T-SQL. You can check the replication status using T-SQL though.

Depending of how large/big your publication database is (for small databases in same network, you should not even look at the time/size as it will be super fast), you can follow below steps to get a ROUGH estimate (ball park figure) of the size and time of snapshot to get generated and apply at the subscriber :

Note: I put emphasis on Rough estimate as depending on what you are replicating e.g. store procedures, indexes, constraints, etc there will be additional files generated as well.

Select top 10 (depends on you - how many you choose) articles with highest row counts from the publication database and BCP OUT the data. This will give a better judgement for space requirement of snapshot agent. This would be resource intensive operation and hence should be scheduled during quiet activity of the server.

e.g. bcp publisher_database.article_name out largeTable1.dat -T -c

Note: Above will give you an estimated time taken by BCP and the size of the BCP file.

So above point will answer you 1st and 2nd points.
Now to get the time it took to apply the snapshot to the subscriber, you have to run bcp in from the publisher server specifying the SUBSCRIBER_SERVER_NAME

e.g. bcp subscriber_database.article_name in -S SUBSCRIBER_SERVER_NAME\instance_name largeTable1.dat -T -c

This way you get an estimate of how long will it take to apply snapshot to subscriber.


Messages in SQLCMD script output are not in order I am running a batch file that launches a SQLCMD script that creates an Agent job from TFS Release Manager.

The script assigns SQLCMD parameters to variables, lists the variables using selects and displays some feedback in the running of the script throughout the script.

It looks something like this:

SET NOCOUNT ON

DECLARE
    @Var1 NVARCHAR(100) = '$(Var1)'
    ,@Var2 NVARCHAR(100) = '$(Var2)'

SELECT '@Var1' AS VarName,@Var1 AS VarValue
UNION ALL
SELECT '@Var2',@Var2

PRINT 'Do the first thing'
--delete sql agent job

PRINT 'Do the next thing'
--create sql agent job

PRINT 'Do the last thing'
--add steps

Every so often, in the output, I see the PRINT messages in the middle of the SELECT output.

Is there a way to guarantee the order of these messages?

PRINT output can be buffered.  A great way around this is to use RAISERROR (@msg, 0, 1) WITH NOWAIT; instead.

For example:

DECLARE @msg nvarchar(1000);

SET @msg = 'Do the first thing';
RAISERROR (@msg, 0, 1) WITH NOWAIT;

SET @msg = 'Do the second thing';
RAISERROR (@msg, 0, 1) WITH NOWAIT;

SET @msg = 'Do the third thing';
RAISERROR (@msg, 0, 1) WITH NOWAIT;


Retain column statistics on paragraph-style columns? Specific to Microsoft SQL Server...

If a query has a search predicate against a paragraph-style text column, it will create a system-created statistic to answer the query.

Got it.

My question is if those should be removed later as they can't be of very much help since they are based purely on the first few characters of the text string and not actually useful in the search which will be a scan against the table no matter what.  Later "grooming" operations via auto-stats update, or a self-developed-stats-grooming process will encounter these and may try and update them.
To complicate it, this column is typically "wide" and can often be "deep" (say, a bajillion rows) and cause a large IO hit to accomplish even a small sample scan to update the stat.

My thinking leads me to say they should be removed.  What do others say?

I am not asking if queries against these types of data are advisable.  They happen.  I am just doubting the usefulness of these auto-generated stats and their justification to exist unless you happen to have an text column with the first few characters actually useful in finding the rows you need - enough so that you'd consider create an index on that column - and I expect that is unusual.

What say you?  Keep these albatrosses or get rid of them?

First of all, and perhaps most importantly, albatrosses are not unlucky.  They are in fact considered a good omen.  In the poem, the young sailor shoots it with his crossbow, and it is this reckless act that is considered unlucky.

Second of all, SQL Server uses a technique called trie trees, for string statistics.  These are not well documented but get a brief mention in 'Microsoft SQL Server 2012 Internals' in the 'String Statistics' section.  You cannot delete trie trees or control them.

If you delete the ordinary column statistics associated with a column they will be get recreated next time someone queries that column, unless you explicitly disable them which is inadvisable.  In the same way, the sailor is forced to wear the dead albatross around his neck as punishment : )

I think Sean's suggestion of using Full Text Indexing is a good one.  In summary do not shoot albatrosses, do not disable auto-stats.

Let's do a simple walkthru using 'The Rime of the Ancient Mariner' as an example.  I downloaded the text from Project Gutenberg available here, cleaned it up a bit and loaded it up in to a SQL Server table.  For a simple Like query with wildcards, I get a full clustered index scan with an estimated rowcount of 1.55 and actual rowcount of 7:

Looking at the statistics for the column, we can see a lot of stats that probably wouldn't make that much sense for other queries, which I think was your point:

Despite this, the estimated versus actual rowcount (1.55 versus 7) is pretty reasonable and is in the same order of magnitude, but we will get effectively a  table scan because of the wildcards.

Switching on full-text indexing, we can get more accurate rowcounts and start to interrogate the full-text index for other information it stores about the documents, eg

SELECT *
FROM dbo.documentText
WHERE CONTAINS ( documentText, 'albatross' )
GO

SELECT *
FROM sys.dm_fts_index_keywords( DB_ID(), OBJECT_ID('dbo.documentText') )

SELECT *
FROM sys.dm_fts_index_keywords_by_document( DB_ID(), OBJECT_ID('dbo.documentText') )

SELECT TOP 10 display_term, COUNT(*)
FROM sys.dm_fts_index_keywords_by_document( DB_ID(), OBJECT_ID('dbo.documentText') )
GROUP BY display_term
ORDER BY 2 DESC

We now get index seeks and potentially this could make a large performance difference on a bigger table.

Full walkthru script available here.

PS No albatrosses were harmed in the making of this post.


Biggest wait WriteLog I have been given a server (SQL Server 2008R2 64 bit) where our application is running. There has been a gradual decrease in the performance of the application. Now it has come to a point where performance has become a serious concern. I started to investigate and went through the following steps.

Checked the Processor and Memory usage on the sql server (nothing is maxed out) plenty of free resources.
Index fragmentation was minimum yet rebuilt the indexes and updated statistics.
No changes has been made to the code in application (application code/sql server code) hence poor performance because of the poorly written code is unlikely to be the cause of overall poor performance of the application.
Finally got Paul Randal’s script Wait statistics, or please tell me where it hurts. The result of the script shows that sql server has to wait a lot when writing to log file. The biggest wait type is WRITELOG.

I used the SQLIO tool to measure the disk Read/Write performance and it seems as fast as any other disk.

I know for a fact I suspect that it is more likely that there is something wrong with the log write process, but I am running out of places to look for, can someone please advise me where else I should look for issues.

What can be the possible causes of slow log writes? or any advice or pointers in the right direction are much appreciated. Thank you.

╔═══════════════╦═════════════╦═══════════╗
║   WaitType    ║   Wait_S    ║ WaitCount ║
╠═══════════════╬═════════════╬═══════════╣
║ IO_COMPLETION ║ 4850500.20  ║ 4553514   ║
║ WRITELOG      ║ 25291893.90 ║ 795877    ║
╚═══════════════╩═════════════╩═══════════╝

After doing a lot more investigation this is what I have found.

It fixed the issue (significant performance gain and WRITELOG has an average wait time of 0.0126 which was initially 14.681)

Apparently the issue was with the Number of Virtual Log files in my physical log file.

There is a job scheduled to rebuild indexes every night, the job creates 36GB of logs, and until few weeks ago someone had add a job to shrink log file on weekly basis. Log file was being shrunk to 500MB.

Since it is a very busy server the log file would grow in size and it was set to auto grow by 3 percent. Each time it grew it added more and more VLFs. As a result my 35.5GB log file had 1600 VLFs.

To resolve the issue I did the following:

-- To get the number of VLFs in a physical Log file.
DECLARE @count INT, @DBname SYSNAME;
------------------------------------------------------------
SET @DBname = N'TEST_DB'
------------------------------------------------------------

DBCC LOGINFO(@DBname)
     SET @count = @@ROWCOUNT;

SELECT @DBname AS DatabaseName , @count Total_VLFs;
GO

The above query returned the databasename and number of VLFs (1600)

To reduce the number of VLFs this is what I did:

USE TEST_DB;
GO

DBCC SHRINKFILE (TEST_DB_log, 0, TRUNCATEONLY)
GO

DBCC SHRINKFILE (TEST_DB_log  , 0)
GO

ALTER DATABASE TEST_DB
  MODIFY FILE ( NAME       = TEST_DB_log
              , SIZE       = 36864MB
              , FILEGROWTH = 4096MB)
GO

This reduced the number of VLFs from 1600 to 20. The end-users has seen a sudden improvement in the application performance.

Now one thing I am not sure about is, if 20 VLFs is the right number of VLFs for a 36GB log file. But it seems to have a good impact on the performance of the database.


How to get a column of the select to be "cumulative" (like in excel)? This simple query (written in MySQL) is counting the number of WP posts published every day. But I need another information. I want to know the cumulative number of post for each date returned by the query.

If there are 100 posts published on this blog at date X I want the query to show 100. If I publish another post at date X + 1, I want to read X+1 => 101 posts

How can I achieve this objective?

SELECT DATE(post_date)    AS date,
       concat(post_title) AS titles,
       COUNT(*)           AS count
FROM   wp_posts
WHERE  post_status = 'publish'
AND    post_parent = 0
AND    post_type = 'post'
GROUP  BY DATE(post_date)
ORDER  BY DATE(post_date)

If I understand you correctly, you want to have another field in your query which shows the total count for all posts, regardless of dates then.
You could achieve this like so :

 SELECT DATE(post_date) AS date,
           concat(post_title) titles, COUNT(*) AS count,
           (select count(*) from wp_posts
            WHERE post_status = 'publish'
            AND post_parent = 0
            AND post_type = 'post') as CumulativeCount
    FROM  wp_posts
    WHERE post_status = 'publish'
    AND post_parent = 0
    AND post_type = 'post'
    GROUP BY DATE(post_date)
    ORDER BY DATE(post_date)


Very small MySQL table keeps crashing I have a MyISAM MySQL table that keeps crashing. The table has only 16 rows and is very rarely written to (maybe once a month). I have a medium-sized site that typically has about 500 pageviews per hour at peak times, and each pageview queries this table 1-2 times.

How can I figure out why this particular table keeps crashing, and how I can prevent it from crashing again in the future?  After it crashes, most of the pages on my site break.

I have access to the mysql logs, but I'm not sure what exactly to look for.

EDIT: here are a couple snippets from the error log:

120317 12:35:18 [ERROR] /usr/libexec/mysqld: Incorrect key file for table './db/tablename.MYI'; try to repair it

and then later:

120318  4:29:00 [ERROR] /usr/libexec/mysqld: Table './db/tablename' is marked as crashed and should be repaired

The first—and most important—thing you can do before repairing a corrupted table is to back it up. Never try running a repair operation on a table without backing it up; if you fail, your data might be in worse shape than originally and may even become un-salvageable. Backing up the original table file allows you the option of trying a different technique if your first attempt doesn't work.

Next, shut down the MySQL server. This is because myisamchk makes changes to the table file itself, so you don't want other users accessing or otherwise manipulating the file during the repair operation. Remember, don't just terminate the server with a call to "kill"; instead, use the mysqladmin SHUTDOWN command to ensure that MySQL closes all open tables gracefully and doesn't further compound your problem.

myisamcheck will be enough to get details regarding what is causing table to crash for more details you can refer this http://dev.mysql.com/doc/refman/5.0/en/myisam-check.html


What's the purpose of the cmd column in the sp_who stored procedure result set? When executing sp_who I get this

I want to know what's the purpose of cmd column. What parameter does it represent?
Thanks for your time!

From the documentation for sp_who (Transact-SQL):

╔════════╦═══════════╦═══════════════════════════════════════════════════╗
║ Column ║ Data type ║                    Description                    ║
╠════════╬═══════════╬═══════════════════════════════════════════════════╣
║ cmd    ║ nchar(16) ║ Database Engine command ( Transact-SQL statement, ║
║        ║           ║ internal Database Engine process, and so on)      ║
║        ║           ║ executing for the process.                        ║
╚════════╩═══════════╩═══════════════════════════════════════════════════╝


T-SQL insert with output expression insert Table1
OUTPUT inserted.ID, s.[Id]
INTO @Inserted
select
s.[Field1],
s.[Field2],
from
@TableVariable s

why is s.[Id]  marked by red line ? Is it non-correct syntax to save value in @Inserted table variable ?

s is not valid
at that point you only have inserted

if you want the fields then try

OUTPUT inserted.ID, inserted.[Field1], inserted.[Field2]

instead of line 2 in your query.  You can find more here:

OUTPUT Clause (Transact-SQL)


Mongodb creating historical cluster replicaset I've a use case which I'm trying to implement.. There is a replicaset(3 nodes) which is primary production cluster, I want to set up a historical cluster another replicaset(3 nodes) which replicates data from primary production replicaset.. But we periodically purge transaction data from it,  I don't want purge to replicate since historical replicaset should hold entire data for reporting..

What are the ways to handle it..?  Do we have selective replication in mongo or should I replicate oplog manually and skip deletes..

Thanks for suggestions!

MongoDB does not (as at 4.0) have a supported feature or tool for selective replication or syncing between two distinct deployments.

I would recommend upgrading from 3.4 to a newer version of MongoDB (ideally 4.0) and building a sync solution using the Change Streams API. You can choose which events to replicate to a historical archive or deployment based on the event type, namespace, or other criteria. Change streams are available for replica sets and sharded clusters using the WiredTiger storage engine and replication protocol version 1. MongoDB 3.6 is the first version to add the Change Streams API and includes support for watching individual collections. In MongoDB 4.0 the change stream support was extended to enable watching all non-system collection changes at a database or deployment scope.

If you are unable to upgrade from MongoDB 3.4 in the near future, you could also consider the approach of directly tailing the replication oplog. This would be a less robust solution than the Change Streams API, but there are some third party tools such as  mongo-connector that may be helpful. The Change Streams API also uses the oplog but adds a supported API, only includes majority-committed operations (that won't rollback), and scales to support sharded clusters. The oplog format is internal and subject to change between releases of MongoDB.


SQL Server : differential backup not updating I'm using this script to create a differential backups for my database :

 BACKUP LOG myDatabase TO DISK = 'myDatabaseBackup.dif'

But what amazes me is that the generated file does not change in size, even if I insert more information or create new tables in my database. I expect the size to change because the full backup size changes if I add more data into my database.

Full backup script :

BACKUP LOG myDatabase TO DISK = 'myDatabaseBackup.bak'

I run the above scripts manually (every hour) because I'm using SQL Server Express, and apparently it does not have the Maintenance Plan feature.

The extension of a backup file is just to have a notation and easy identification. The script you are using is for Log backup and not for Differential backup.

Differential backup backs up the extents that had changes since the last full backup. Below is the t-sql for that :

BACKUP DATABASE AdventureWorks TO DISK = 'C:\AdventureWorks.DIF' WITH DIFFERENTIAL
GO

The backup that you are taking is log backup and in addition to letting you restore the backed-up transactions, a log backup truncates the log to remove the backed up log records from the log file. This is very important to have a point in time recovery and has to be scheduled to run frequently.

Please go through below link to understand how backups work :

https://technet.microsoft.com/en-us/library/2009.07.sqlbackup.aspx


PostgreSQL ntile() partition i have a point grid with population values. each point has an id and population value. i also have an state_id which say in what state the point is.

now i want to calculate the percentile ntile(100) for each state.

   SELECT  id, population, state_id,
   ntile(100) OVER(ORDER BY car20) as percentile
   FROM avi_threshold01

when i use this, i think it calculates the ntile over all points and states.

If you want percentiles per state, then use PARTITION BY state_id in the OVER clause.

And that GROUP BY looks spurious at least. I think it needs to be removed if you want percentiles. And group by the PK is a no-operation anyway.

   SELECT
       id, population, state_id,
       ntile(100) OVER (PARTITION BY state_id ORDER BY car20) AS percentile
   FROM
       avi_threshold01 ;


Sysadmin rights to local administrator How I can give the sys_admin rights to the local administrator for the SQL Server 2014 database while I installed it using the domain admin account, not local administrator account? Below is the requirement of a manual.

  SQL Server installation

  During the installation of AlarmInsight
  components it is required to use local Administrator login. The SQL
  Server Installation can also be done with local Administrator login by
  giving required access rights. If another login is used to install SQL
  Server, the local Administrator has to be given sys_admin rights in
  the SQL Server Database.

I had to reread the vendor requirements a couple of times, but I take the instructions to mean the local administrator account only requires elevated permissions during installation.  Run one of the following blocks prior to the app install to set this up.

-- Add the Local Administrators Group
USE [master]
GO
CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [BUILTIN\Administrators]
GO

-- Or Add only the Local Administrator User - Dynamic SQL to capture servername
DECLARE @hostname NVARCHAR(128), @createStmt NVARCHAR(MAX)
SELECT @hostname = CAST(SERVERPROPERTY(N'ComputerNamePhysicalNetBIOS') AS NVARCHAR(128))

SET @createStmt = N'USE [master];' + CHAR(13) + CHAR(10) +
    N'CREATE LOGIN [' + @hostname + N'\Administrator] FROM WINDOWS WITH DEFAULT_DATABASE=[master];' + CHAR(13) + CHAR(10) +
    N'ALTER SERVER ROLE [sysadmin] ADD MEMBER [' + @hostname + N'\Administrator];'

PRINT @createStmt
EXECUTE (@createStmt)
GO

When the installation is complete, be sure to back out these permissions as they are not the most secure, by running one of the following:

-- Drop Local Administrators group
USE [master]
GO
DROP LOGIN [BUILTIN\Administrators]
GO

-- Or Drop the explicitly defined Local Administrator login
DECLARE @hostname NVARCHAR(128), @createStmt NVARCHAR(MAX)
SELECT @hostname = CAST(SERVERPROPERTY(N'ComputerNamePhysicalNetBIOS') AS NVARCHAR(128))

SET @createStmt = N'USE [master];' + CHAR(13) + CHAR(10) +
    N'DROP LOGIN [' + @hostname + N'\Administrator];'

PRINT @createStmt
EXECUTE (@createStmt)
GO


Multiple indices on a table I have run SQL Profiler and I can see that I'm "missing" some indexes (non-clustered) on a table. How is it best to handle indexes that can be a "subset" of another index?

Index 1

UserId, InstanceId, Created

Index 2 (Subset of Index 1)

InstanceId, Created

Is index 2 necessary in this case?

Can't really found the query that caused Index 2

Query (using Entity framework):

SELECT 1 AS [C1], [Limit1].[InputForm] AS [InputForm], [Project2].[UserId] AS [UserId]
FROM (SELECT [Distinct1].[UserId] AS [UserId]
    FROM (SELECT DISTINCT
        [Extent1].[UserId] AS [UserId]
        FROM [dbo].[Dat_GameCompletionData] AS [Extent1]
        WHERE ([Extent1].[InstanceId] = @p__linq__0) AND ([Extent1].[Created] >= @p__linq__1)
    )  AS [Distinct1] ) AS [Project2]
OUTER APPLY  (SELECT TOP (1) [Project3].[InputForm] AS [InputForm]
    FROM ( SELECT
        [Extent2].[InputForm] AS [InputForm],
        [Extent2].[Created] AS [Created]
        FROM [dbo].[Dat_GameCompletionData] AS [Extent2]
        WHERE ([Extent2].[InstanceId] = @p__linq__0) AND ([Extent2].[Created] >= @p__linq__1) AND ([Project2].[UserId] = [Extent2].[UserId])
    )  AS [Project3]
    ORDER BY [Project3].[Created] DESC ) AS [Limit1]

I wouldn't say that index 2 is a subset of index 1.

An index that had the columns UserId, InstanceId would be.

This could still be useful to queries that do not require the created column as they would be accessing a more narrow index which would require less IO and memory.

Your best bet is to check if your indexes are actually being used by queries. The scripts below will help with this.

SELECT  d.name as [Database]
        , OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME]
        , I.[NAME] AS [INDEX NAME]
        , USER_SEEKS
        , USER_SCANS
        , USER_LOOKUPS
        , USER_UPDATES

FROM    SYS.DM_DB_INDEX_USAGE_STATS AS S
JOIN    SYS.INDEXES AS I ON I.[OBJECT_ID] = S.[OBJECT_ID]
                            AND I.INDEX_ID = S.INDEX_ID
JOIN    sys.Databases d on s.database_id = d.database_id

WHERE    OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1

ORDER BY USER_SEEKS + USER_SCANS + USER_LOOKUPS + USER_UPDATES DESC

SELECT  d.name
        , t.name
        , OBJECT_NAME(A.[OBJECT_ID]) AS [OBJECT NAME]
        , I.[NAME] AS [INDEX NAME]
        , A.LEAF_INSERT_COUNT
        , A.LEAF_UPDATE_COUNT
        , A.LEAF_DELETE_COUNT

FROM    SYS.DM_DB_INDEX_OPERATIONAL_STATS (NULL,NULL,NULL,NULL ) A
JOIN    SYS.INDEXES AS I ON I.[OBJECT_ID] = A.[OBJECT_ID]
JOIN    sys.tables t ON i.object_id = t.object_id
JOIN    sys.databases d ON a.database_id = d.database_id
                        AND I.INDEX_ID = A.INDEX_ID

WHERE   OBJECTPROPERTY(A.[OBJECT_ID],'IsUserTable') = 1

ORDER BY    A.LEAF_INSERT_COUNT + A.LEAF_UPDATE_COUNT + A.LEAF_DELETE_COUNT DESC


Create table with constraint based on another column Say a database has the following tables:

movie(id, title, rent_period)
customer(id, name)

I would like to create the following table:

CREATE TABLE rental (
   movie_id int,
   customer_id int,
   rent_date date,
   return_date date,
   FOREIGN KEY (movie_id) REFERENCES movie(id),
   FOREIGN KEY (customer_id) REFERENCES customer(id)
   PRIMARY KEY rental_pk (movie_id, customer_id)
)

Can I make make the return_date field be populated automatically as rental(rent_date) + movie(rent_period)?

I would do this using a trigger on your rental table. Trigger before insert - set the return_date column value to be the rent_date value in the data being saved, plus look up the value of movie.rent_period based on the movie_id value being saved.


Access Sharing database over Network vs Mysql multiple users Something no one mentions and I find important to stress is that Access has a file locking mechanism for sharing the database on a network share which is orders of magnitude more efficient than any network database out there and therefore MySQL is no different.

The reason is the following, a file locking mechanism permits all clients of an Access database to cache somehow the database file and therefore to execute queries on it as if it were a local file. This of course means very little data is sent over the network in the case for instance of a SELECT which returns 5000 records, because the select is actually returned from the cached file.

A traditional client server model such as Mysql instead means that a query for 5000 records will involve a lot of TCP packets between server and clients to return the whole 5000 records from server to client, that will involve a high latency between the query and the completion of the return of results.

A file locking mechanism of course has its limits which usually incur when more than 5 users share a database heavily with a lot of modifications to it, in that case the locking mechanism (which in Access can be very selective up to row locking) will cause more delay than say a client server database which could theorically allow a lot more of concurrency.

I came therefore to the conclusion that if you need to share a database on a LAN with a couple of users therefore Access has few competitors, if instead you need to host a database on the internet or with tens/hundreds of users of course Mysql is better.

I also notice many other databases tried implementing the file locking mechanism but most failed in stability or weren't able to make it work as efficiently as Microsoft with Access which must have spent a good load of time and money on it. SQLite and H2 database locking mechanisms for instance are either very low performance or are highly not recommended for risks of corruption of the database:

  SQLite uses POSIX advisory locks to implement locking on Unix. On
  Windows it uses the LockFile(), LockFileEx(), and UnlockFile() system
  calls. SQLite assumes that these system calls all work as advertised.
  If that is not the case, then database corruption can result.

http://www.sqlite.org/lockingv3.html

  This database file locking mechanism uses native file system lock on
  the database file. No *.lock.db file is created in this case, and no
  background thread is started. This mechanism may not work on all
  systems as expected. [...] This feature is relatively new. When using
  it for production, please ensure your system does in fact lock files
  as expected.

http://h2database.com/html/advanced.html#file_locking_serialized

Can anyone shed some light on how or why is Access so efficient and whether there are any serious competing products which follow the same file locking shared on a network approach and which implement it in an efficient way?

When the own vendor says the following:

  "While Microsoft Jet is consciously (and continually) updated with
  many quality, functional, and performance improvements, it was not
  intended (or architected) for the high-stress performance required by
  24x7 scenarios, ACID transactions, or unlimited users, that is,
  scenarios where there has to be absolute data integrity or very high
  concurrency."

  "When you need unlimited users, 24x7 support, and ACID transactions,
  Microsoft strongly recommends that you use Microsoft SQL Server with
  Internet Information Server (IIS). "

I wouldn't have many expectations for integrity and performance for multiple users over a network.