Logic Value of Assignment Expression I'm doing the leetcode SQL problems and kind of confused with the code below:

select distinct Num from (
    select
        Num,
        case
            when @prevNum = Num then @count := @count + 1
            when (@prevNum := Num) is not null then @count := 1
        end n
    from Logs, (select @prevNum := NULL) r
    order by Id
) a where n >= 3

Two questions:

First when (@prevNum := Num) is not null what is the logic value of assignment expression in SQL?
I have googled MySQL and Oracle reference book, this forum and I got nothing about it. And this condition is really confusing.

Second the n after case end statement, I know n stands for the alias of case statement, but there are two variables in case end statement here, how do SQL know that n stands for @count but not @prevNum?

Thanks

The question is as follows:

Write a SQL query to find all numbers that appear at least three times consecutively.

+----+-----+
| Id | Num |
+----+-----+
| 1  |  1  |
| 2  |  1  |
| 3  |  1  |
| 4  |  2  |
| 5  |  1  |
| 6  |  2  |
| 7  |  2  |
+----+-----+

For example, given the above Logs table, 1 is the only number that appears consecutively for at least three times.

PROPOSED SOLUTION

Using the IF() function to check for consecutive num values

SET @x = 0;
SET @n = 0;
SELECT num,consecutive FROM
(SELECT
    id,num,
    (@x:=IF(num=@n,@x+1,1)) consecutive,
    (@n:=num) inc
FROM logs) A
WHERE consecutive >= 3;

YOUR SAMPLE DATA

DROP DATABASE IF EXISTS tonychen;
CREATE DATABASE tonychen;
USE tonychen
CREATE TABLE logs
(
  id INT NOT NULL AUTO_INCREMENT,
  num INT NOT NULL,
  PRIMARY KEY (id)
);
INSERT INTO logs (num) VALUES (1),(1),(1),(2),(1),(2),(2);
SELECT * FROM logs;

YOUR SAMPLE DATA LOADED

mysql> DROP DATABASE IF EXISTS tonychen;
Query OK, 1 row affected (0.25 sec)

mysql> CREATE DATABASE tonychen;
Query OK, 1 row affected (0.00 sec)

mysql> USE tonychen
Database changed
mysql> CREATE TABLE logs
    -> (
    ->   id INT NOT NULL AUTO_INCREMENT,
    ->   num INT NOT NULL,
    ->   PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.31 sec)

mysql> INSERT INTO logs (num) VALUES (1),(1),(1),(2),(1),(2),(2);
Query OK, 7 rows affected (0.05 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM logs;
+----+-----+
| id | num |
+----+-----+
|  1 |   1 |
|  2 |   1 |
|  3 |   1 |
|  4 |   2 |
|  5 |   1 |
|  6 |   2 |
|  7 |   2 |
+----+-----+
7 rows in set (0.00 sec)

mysql>

PROPOSED SOLUTION EXECUTED

mysql> SET @x = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @n = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT num,consecutive FROM
    -> (SELECT
    ->     id,num,
    ->     (@x:=IF(num=@n,@x+1,1)) consecutive,
    ->     (@n:=num) inc
    -> FROM logs) A
    -> WHERE consecutive >= 3;
+-----+-------------+
| num | consecutive |
+-----+-------------+
|   1 |           3 |
+-----+-------------+
1 row in set (0.00 sec)

mysql>

GIVE IT A TRY !!!


SQL Server 2008R2 XML Column Index strategy for large tables? I am working with SQL Server 2008 R2 and some XML columns in a very large table. I understand that I cannot use Selective XML Indexes due to our SQL Server version being < 2012. I'm brand spanking new to the idea of indexing XML columns.

The Goals

I have two separate scenarios in which I need to query XML:

In one case, I need to query if a nested value exists across a table with 3 million rows

E.g. columnName.exist('Parent[1]/Child[1]/DifferentChild[1]') = 1'

In another case, I need to extract two values from XML into two columns so that I can match on it, in a table with 4.7 million rows.

E.g. SELECT columnName.value('Parent[1]/FilePath[1]', 'nvarchar(max)') as FilePath, columnName.value('Parent[1]/FileName[1]', 'nvarchar(255)') as FileName

Question

Given the cost of the indexes here and the fixed type of querying, what are indexes I can apply for maximum impact, and are there any pitfalls I should be aware of?

You should have a look at Property Promotion in XML Best Practices for Microsoft SQL Server 2005.

Create a user defined scalar valued function that extracts the value you need and use the function as a computed column in your table. You can persist the column of you like and you can create a regular index on the column. You don't have to persist the column in order to create the index.


Why is my query not using "Missing index tip"? ( SQL Server 2008 R2 ) I know there's a lot of questions like this one, but I think this is different, and none of them could help me.

When I run a query with execution plan, I get this:

But, even with the index created, it's not using it.

The funny thing is, when I create it in my test environment (same database, same query, same index), it uses it.

Why is my production server not using the index I've created?

This is the same query in my test environment with the same index in the same database.

It's a simple query:

select *
from Database
where col1 = 'string'
  and col2 = 'string'

The index I created is:

create index (ix_col1_col2)
on Table (col1, col2)

Edit 1:

With Aaron's answers, I now have this:

I have the same result with * and using all fields.

The server still want me to create the same index it's already using. it's because the 0%? it thinks the query it's not using it?

EDIT 3:

What a strange thing. I used this:

alter index all on table rebuild

and update stats, and now, the query is using the index with 50% CPU.

Is this a good question to help others? or we should close it? since it was a simple problem.

Again thanks Aaron for your help.

Table creation :

CREATE TABLE dbo.table1
    (         [ID]              int primary key
            , [Data]            varchar(20)
            , [IDChamada]       varchar(50)
            , [Evento]          varchar(50)
            , [Resposta]        varchar(10)
            , [IDTarifador]     int
            , [ProximoEvento]   varchar(10))
;

You can bloat the table

UPDATE STATISTICS dbo.Table1 WITH ROWCOUNT = 10000000, pagecount = 10000000

With your given index

create nonclustered index nc_idChamada_evento_Rafael on dbo.table1 ( idchamada,evento)

SQL Server is going to do a KEY LOOKUP to find other columns from your SELECT list

select [ID]
    ,[Data]
    ,[IDChamada]
    ,[Evento]
    ,[Resposta]
    ,[IDTarifador]
    ,[ProximoEvento]
from dbo.Table1
where idChamada = '18/09/15 09:08:19 010A 7E09/00'
    and evento = 'EVENTO_DE_COMPLETAMENTO'

Key Lookup is done on below columns since they are part of select query :

The index you need is

create nonclustered index nc_idChamada_evento_KIN on dbo.table1 (
    idchamada
    ,evento
    ) include (
    [Data]
    ,[Resposta]
    ,[IDTarifador]
    ,[ProximoEvento]
    )

Above index will give you a nice seek :


To answer this, I did the following (you can check out the
SQLFiddle here).

CREATE TABLE Company
(
  cid INT,
  cons_no VARCHAR(2),
  company_name VARCHAR(20),
  phone VARCHAR(15),
  address VARCHAR(20)
);

INSERT INTO Company
VALUES
(1, 'q1', 'Apple', '222222', 'New York'),
(2, 'q2', 'Samsung', '11111', 'Ohio'),
(3, 'q3', 'Nokia', '333333', 'London');

CREATE TABLE Company_Track
(
  id INT,
  cid INT,
  cons_no VARCHAR(2),
  branch_location VARCHAR(20),
  date_opened INT
);

INSERT INTO Company_Track
VALUES
(1, 1, 'q1', 'Stamford', 2010),
(2, 1, 'q1', 'Kingsway', 2011),
(3, 1, 'q1', 'Cornerstone', 2014),
(4, 2, 'q2', 'Madison', 2001),
(5, 3, 'q3', 'Lewiston', 2015);

And then ran the query

SELECT  c.company_name, c.cons_no, t.branch_location
    FROM  Company c
    INNER JOIN  Company_Track t ON c.cid = t.cid;

Which gives the result you want

company_name    cons_no branch_location
Apple           q1      Stamford
Apple           q1      Kingsway
Apple           q1      Cornerstone
Samsung         q2      Madison
Nokia           q3      Lewiston


Oracle 11g Encryption Can somebody please tell me how to encrypt an existing tablespace in 11g? The wallet has been setup and the sqlnet.ora points to it.
SQL> select STATUS  FROM V$ENCRYPTION_WALLET;

STATUS
------------------
OPEN

I alter the tablespace offline ...
alter tablespace USERS offline normal;

But I can only find the 12n and above command for the next part:
ALTER TABLESPACE users ENCRYPTION OFFLINE ENCRYPT;

Many thanks in advance for the 11g command.

Encrypting an existing tablespace became possible in 12.2.
11g documentation:
Creating an Encrypted Tablespace

You cannot encrypt an existing tablespace.

12.1 documentation:
About Creating Encrypted Tablespaces

You cannot change an existing tablespace to make it encrypted.


can we transfer sql database backup from one server to another server directly? I want to transfer sql database from one server to another server directly, instead taking back up of that database and copy that back up in another server and then restore it.

Please tell me, Is there any way to do it?

Your options are pretty limited.

You can create a shell database and then use something like SQL Compare from RedGate to compare the schema and migrate the schema.  Then possibly do a Data Compare to script out the data and migrate it.

Alternatively, you could use the export option in SQL Server to export a bunch of tables which will recreate the tables on the destination.  The downside here is that FKs and other constraints can get muddled.

Another option is to use replication.  Unfortunately, that means you must backup the database and restore it to the destination server.

And then there is the copy database wizard as mentioned by shanky.

My preference is to use backup/restore.


Covering Index Not Being Used Effectively I have a unique composite index on three fields (hash, is_customer, user_id) in a table a.
And queries that go like this:
select `a`.`id`, `hash`, `is_customer`, `user_id`
from `a`
where `hash` = 34605915
limit 1;

When I do explain, it identifies properly as:
Using where; Using index |

However the above query showed up in a slow query log as Full_scan: Yes and query time of over 5.083821 seconds.
The dump of the explain is as follows:
| {
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "a",
      "access_type": "index",
      "possible_keys": ["a_hash_unique"],
      "key": "a_id_hash_is_customer_user_id_unique",
      "key_length": "138",
      "used_key_parts": ["id", "hash", "is_customer", "user_id"],
      "rows": 2639299,
      "filtered": 100,
      "attached_condition": "db.a.hash = 34605915",
      "using_index": true
    }
  }
} |

The InnoDB pool buffer pool size is set to 21G (actual pool data size is about 18.1G) and pool instances as 21. What could be the explanation for the optimized missing this query index? In the process, I think the same issue causes the system to run into deadlocks during DB writes.
Below is the table structure dump:
 a | CREATE TABLE `a` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `anum` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `currency` char(3) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'NGN',
  `hash` char(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `bid` int(10) unsigned DEFAULT NULL,
  `user_id` int(10) unsigned DEFAULT NULL,
  `a_type` int(10) unsigned DEFAULT NULL COMMENT '1=Current, 2=Savings, 3=Fixed, 4=Overdraft, 5=Loan, 6=Joint, 7=Other',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `is_customer` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `a_hash_unique` (`hash`),
  UNIQUE KEY `a_id_hash_is_customer_user_id_unique` (`id`,`hash`,`is_customer`,`user_id`),
  KEY `a_bid_index` (`bid`),
  KEY `a_user_id_index` (`user_id`),
  CONSTRAINT `a_bid_foreign` FOREIGN KEY (`bid`) REFERENCES `b` (`id`),
  CONSTRAINT `a_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6004761 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |

Using index means that an index was "covering" and was used.  Or, at least, when the EXPLAIN was run, it decided that.
I have looked at thousands of EXPLAINs and slowlog entries.  In theory, they always match.  However, there is nothing that forces the EXPLAIN to actually match what happens and is recorded in the slowlog.
I have seen as many as 6 different EXPLAINs for a given query "digest".  But that is easily explained by different cardinality, etc.
Could you provide the full, exact, text of the EXPLAIN and the slowlog entry.  I would like to see if I can spot something.  Also, if available, please provide EXPLAIN FORMAT=JSON SELECT ...
Actually, SHOW CREATE TABLE would probably have the clue.
WHERE `hash` = 34605915

If hash is VARCHAR, then this cannot use the index.  Add quotes to the string "34605915".
Or, if hash is always numeric, then make it an integer datatype, perhaps INT UNSIGNED (32-bit number; 4-bytes).
When mixing char and int, MySQL will convert the varchar to int before comparing.  So, the one case of "varchar_col = integer-constant" cannot use the index effectively.


Cannot retrieve values from LEFT JOIN (2 tables) Here it is my query:

$query_products = "
SELECT
    p.Id
    , p.product_sku
    , p.product_name
    , p.hide_product
    , p.new_style_image
    , p.ext
    , p.product_thumb_image
    , p.product_full_image
    , SUM(s.stoc_final)
    , s.active
FROM
    produse p
    LEFT JOIN stoc_intern s ON p.product_sku=s.cod_website
WHERE
    (
        (" . $product_sku_search . ")
        OR (" . $product_name_search . ")
        OR ("  .$product_culoare_search . ")
    )
    AND p.hide_product != 'd'
GROUP by p.product_sku
ORDER BY SUM(s.stoc_final) DESC
LIMIT " . $items_per_page . "
OFFSET " . $_offset . "";

My problem is: the last field I'm selecting, the s.active one, has in the table the value of "n" but when I display it, it shows empty (echo $s_active);
If I change the field s.active with say other field s.description, it will work.

Also, in the table "stoc_intern" some fields that I select do display well, some do not display a thing (even if table data contains info).

Why is that?

a_horse_with_no_name already provided with the answer.

But to disable this "one of those stupid MySQL "features" that no one can really understand", you can set sql_mode to be more strict about it. Consider the following setup in your my.cnf config file:

[mysqld]
sql_mode = ONLY_FULL_GROUP_BY

See documentation. This will not allow you to reference non-grouped columns unless with an aggregation function.


Incorrect total using SUM of DATEDIFF results When i run a few DateDiff(SECOND, value1, value2) it returns a lists of values

93, 185, 1879  which totals 2157 sum with the calc and with SUM()

However, when i use DateDiff(SECOND, firstValue1, lastValue2) returned 2476

A example of the problem is:

declare @testTable table
(
    DateD float
)
insert into @testTable
select datediff(SECOND, '2015-08-12 22:40:29.847', '2015-08-12 23:11:48.000')
UNION
select datediff(SECOND, '2015-08-12 23:11:48.510', '2015-08-12 23:14:53.000')
UNION
select datediff(SECOND, '2015-08-12 23:20:12.147', '2015-08-12 23:21:45.000')

select sum(DateD)from @testTable

select datediff(SECOND, '2015-08-12 22:40:29.847', '2015-08-12 23:21:45.000')

What is my fault?

Thanks.

Well, quite simply, there are gaps in your time series.

A naive DATEDIFF(MIN, MAX) ignores the fact that there is roughly a 6-minute gap between your second and third rows.

Think about it this way: If you go to school from Monday-Friday, how many days did you attend in June? Not 30, because some of those days in June were Saturday and Sunday (and maybe holidays and PD days, too).


db2 cross platform function/variable? Is there a db2 function/procedure/variable that can be used to determine what platform a data source origins from? sysibm.sysdummy1 exists, so I assume that this is Db2, but some of the SQL does not look right according to LUW syntax. Is there an easy way to figure out via SQL what platform a data source origins from?

It's a very open-ended question, as there is no sure-fire method to determine the server version and platform. You'll need to apply some heuristics.

In Db2 for LUW (and only LUW) since v 10.1 there's a system view dbmcfg that can return the server version:

select value from sysibmadm.dbmcfg where name = 'release'

so if it succeeds you'll be sure it's Db2 for LUW of certain release level. Similarly, since v 9.7 you can query sysibmadm.env_inst_info (or first search for it in syscat.tables).

Another indicator of Db2 for LUW is the presence of some Oracle-compatible views since v.9.7, e.g. syspublic.dual or sysibmadm.all_objects.

A good indicator of Db2 for z/OS would be the presence of mainframe-specific catalog tables, e.g. sysibm.locations or sysibm.syscopy, which go back to v9 at least.

The presence of catalog views in the QSYS and QSYS2 schemas, e.g. QSYS2.TABLES, will indicate Db2 for i (AS/400).

Neither Db2 for z/OS nor Db2 for i have the SYSCAT schema views.


Compare two tables with same structure, but differing membership numbers I have two tables with the same structure and most of the data is the same. I also have a third table that contains ids and names. The ids are connected to the membership numbers discussed below.

The databases are from the current and previous versions of a website. I need a query that searches all three tables for the names and membership numbers, compares the ids and membership numbers, then only displays the records that have membership numbers that do not match. I also need to exclude '0' as an unmatched result.

This matching only needs to happen between the two identically structured tables. The third table is only used to connect a unique name to each of the records returned from the two membership number tables. I have included my updated SQL query below.

SELECT
    u.id,
    u.name,
    a.membership_no,
    j.membership_no
FROM
    n2z7m3_users AS u
LEFT JOIN n2z7m3_kiduka_accounts AS a ON (u.id = a.membership_no)
LEFT JOIN n2z7m3_kiduka_accounts_j15 AS j ON (
    a.membership_no = j.membership_no
)
WHERE a.membership_no NOT IN (
    SELECT
        j.membership_no
    FROM
        n2z7m3_kiduka_accounts_j15 AS j
);

Users table structure and sample data:

+------+----------------+
| id   | name           |
+------+----------------+
| 6000 | Dave Sanderson |
+------+----------------+
| 6001 | Bill Willis    |
+------+----------------+
| 6002 | John Hancock   |
+------+----------------+

Membership numbers table structure and sample data from accounts table one:

+--------------------------------+
| id   | user_id | membership_no |
+----------------+---------------+
| 6000 | 6000    | 6000          |
+----------------+---------------+
| 6001 | 6001    | 6001          |
+----------------+---------------+
| 6002 | 6002    | 6002          |
+----------------+---------------+

Membership numbers table structure and sample data from accounts table two:

+--------------------------------+
| id   | user_id | membership_no |
+----------------+---------------+
| 6000 | 6000    | 6010          |
+----------------+---------------+
| 6001 | 6001    | 6020          |
+----------------+---------------+
| 6002 | 6002    | 6030          |
+----------------+---------------+

The membership_no=6010 from the second accounts table would be the difference from the first accounts table. The user_ids are the same, but the membership_nos are different. This is what I am needing to find. All of the instances where the membership_nos are different using the ids from the users table and user_ids from the accounts tables.

That should give you what you want:

SELECT UT.Name, T1.*, T2.membership_no as 'Unmatching in membership_no Second table'
FROM T1
INNER JOIN UT
ON T1.user_id = UT.user_id
INNER JOIN T2
ON T1.user_id = T2.user_id and T1.membership_no != T2.membership_no


Cannot connect to MySQL sever with MySQL Workbench I am using MySQL 5.6.26 Community Server on a Mac OSX 10.10.4.

The server is running, according to the preference pane utility. Also I can connect to the server using the command line tool with the common login:

./mysql -h localhost -u root -p

However, using the same credentials in MySQL Workbench gives me this error:

Can't connect to MySQL server on '127.0.0.1' (61)

I tested replacing localhost with 127.0.0.1 in the command line login wich gives the same error:

./mysql -h 127.0.0.1 -u root -p
Enter password:
ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (61)

I think there is an issue here with resolving localhost to 127.0.0.1, but I need help with further investigation.

When you specify localhost as the host to connect to, the mysql commandline client tries to connect using a local socket. To enforce a tcp connection you have to either set the host to 127.0.0.1 or use the --protocol=tcp parameter.

Your server is not configured to accept network connections so you have to switch to change your connection method to Socket/Pipe Path and set the path to the socket, something like /var/run/mysqld/mysqld.sock. You can check yours in the config or by running select @@socket; in the commandline connection. \s used as command in the CLI will tell you that too and some more info about your server.


Is it possible to shrink mdf file in replication server Is it possible to shrink the data file of a SQL Server replication target server and continue the replication operation?
Ideally I'm trying to avoid shrinking the data file to reduce down time, then swap the roles if shrinking is possible.
There is 60% free space in the database after a bulk clean up, so shrinking is unavoidable.

With SQL Server transactional replication, data is replicated at the table level, not the database itself. So you can make whatever changes you want to the database. The question is why you want to shrink the database in the first place. Check out ShouldIShrinkMyDatabase.com, and make sure you click through to the linked article. – AMtwo May 21 '20 at 11:20


MongoDB error: self signed certificate in certificate chain I'm setting up for test a dockerized MongoDB which uses SSL. Everything goes fine with configuration, but when I'm trying to connect I get the following error:

SSL peer certificate validation failed: self signed certificate in certificate chain.

Here's how I generate my CA and server/client certificates:

# CA
openssl genrsa -out ca/ca.key 2048
openssl req -x509 -new -key ca/ca.key -days 10000 -out ca/ca.crt

# Server
openssl genrsa -out server/mongodb.key 2048
openssl req -new -key server/mongodb.key -out server/mongodb.csr
openssl x509 -req -in server/mongodb.csr -CA ca/ca.crt -CAkey ca/ca.key -CAcreateserial -out server/mongodb.crt -days 365
cat server/mongodb.key server/mongodb.crt > server/server.pem

# Client
openssl genrsa -out client/cl.key 2048
openssl req -new -key client/cl.key -out client/cl.csr
openssl x509 -req -in client/cl.csr -CA ca/ca.crt -CAkey ca/ca.key -CAcreateserial -out client/cl.crt -days 365
cat client/cl.key client/cl.crt > client/cl.pem

Here's my MongoDB configuration file (disregard the file paths):

net:
  bindIp: 0.0.0.0
  port: 28018
  ssl:
    CAFile: /etc/mongo/ssl/ca/ca.crt
    PEMKeyFile: /etc/mongo/ssl/server/server.pem
    mode: requireSSL
storage:
  dbPath: /mongo/db
  journal:
  enabled: true

And here's how I'm connecting to it:

mongo --host 127.0.0.1 --port 28018 --ssl --sslCAFile mongoconf/config/ssl/server/server.pem --sslPEMKeyFile mongoconf/config/ssl/client/cl.pem

The local mongoconf/config is mapped to etc/mongo in the container.

You have the --sslCAFile pointing at the server.pem file rather than the CA file which seems to be in /etc/mongo/ssl/ca/ca.crt. As a result the cert you have generated for your client connection is not considered valid.

You have a couple of options, you can allow cert-less connections by clients (they are still SSL encrypted, just no cert to validate the client side). This is done by specifying the allowConnectionsWithoutCertificates option on the server and is functionally equivalent to how HTTPS works on the web.

The other option of course is to fix the sslCAFile path to point at the CA cert rather than the server cert.


How do i merge multiple records into a single record I have a select query:

SELECT
  'IT Queue' as Category,
  dbo.Status.Status,
  COUNT(*) AS Count
FROM dbo.Status
  INNER JOIN dbo.CactusQueue_Task ON dbo.Status.Id = dbo.CactusQueue_Task.StatusId
GROUP BY dbo.Status.Status

and it outputs the following:

+-----------+----------------------------+
| Category  | Status              | Count |
+-----------+-----------------------------+
| IT Queue  | Complete            | 1576  |
| IT Queue  | Current             | 2     |
| IT Queue  | Hold                | 7     |
| IT Queue  | In-Progress         | 56    |
| IT Queue  | Killed              | 1     |
| IT Queue  | New                 | 71    |
| IT Queue  | NTD                 | 1     |
| IT Queue  | Testing             | 2     |
+-----------+-----------------------------+

would like to modify the query to select a single record like below:

+-----------+----------+---------+------+-------------+--------+-----+-----+---------+
| Category  | Complete | Current | Hold | In-Progress | Killed | New | NTD | Testing |
+-----------+----------+---------+------+-------------+--------+-----+-----+---------+
| IT Queue  | 1576     |       2 |    7 |          56 |      1 |  71 |   1 |       2 |
+-----------+----------+---------+------+-------------+--------+-----+-----+---------+

How would I go about doing so?

You can use PIVOT and dynamic SQL:

DECLARE @sql NVARCHAR(MAX) = N'', @cols NVARCHAR(MAX) = N'';
DECLARE @category NVARCHAR(20) = N'IT Queue';

SELECT @cols = @cols + STUFF((  SELECT ',' + QUOTENAME([Status])
                               FROM dbo.[Status]
                               GROUP BY [Status]
                               FOR XML PATH('')), 1, 1, '');

SET @sql = N' SELECT *
              FROM (SELECT  ''' + @category + ''' Category,
                            s.[Status],
                            q.StatusId
                    FROM dbo.[Status] s
                    INNER JOIN dbo.CactusQueue_Task q
                        ON s.Id = q.StatusId) AS d
  PIVOT (COUNT(StatusId) FOR [Status] IN (' + @cols + ')) AS p;';

EXEC sp_executesql @sql;


MySQL8 crash recovery My dev MySQL-8 server crashed last night and would not restart. The error is:
[ERROR] [MY-012646] [InnoDB] File ./ibdata1: 'open' returned OS error 71. Cannot continue operation

I tried the classic "start with innodb_force_recovery=1" but that has 0 effect (also removed tried =2 without much success. I also tried removing the ibdata and ib logfiles: it won't start either.
I'm really surprised that these "standard" methods do not work for something that is a mere crash ... but evils is often in the details as we know.
So the last option I tried is re-creating the tables in the database and then replacing the .idb files that are automatically generated with the ones from the crashed server.
The positive is that the MySQL database starts ... but when I want to see the content of the tables I have an error message saying:
Error Code: 1812. Tablespace is missing for table <table>.

I feel I'm nearly there ... any help or suggestion would be welcomed.
Many thanks - Chris

Some update: when I start it with innodb_force_recovery=2, I can start the db and dump my data which is one (big) step forward.
But when I set to innodb_force_recovery=0, it fails with the message below. Is InnoDB not healing itelf when you start with a force recovery?
Thanks - C
InnoDB: End of page dump
InnoDB: Page may be an update undo log page
2021-03-24T10:38:09.631071Z 1 [ERROR] [MY-011899] [InnoDB] [FATAL] Unable to read page [page id: space=4294967279, page number=176] into the buffer pool after 100 attempts. The most probable cause of this error may be that the table has been corrupted. Or, the table was compressed with with an algorithm that is not supported by this instance. If it is not a decompress failure, you can try to fix this problem by using innodb_force_recovery. Please see http://dev.mysql.com/doc/refman/8.0/en/ for more details. Aborting...
2021-03-24T10:38:09.631894Z 1 [ERROR] [MY-013183] [InnoDB] Assertion failure: ut0ut.cc:532 thread 139943235643136
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
10:38:09 UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Thread pointer: 0x5e68130
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 7f4712d95bc0 thread_stack 0x46000
/usr/sbin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x3d) [0x1f70f0d]
/usr/sbin/mysqld(handle_fatal_signal+0x333) [0xfc3603]
/lib64/libpthread.so.0(+0xf5f0) [0x7f472b48d5f0]
/lib64/libc.so.6(gsignal+0x37) [0x7f4729544337]
/lib64/libc.so.6(abort+0x148) [0x7f4729545a28]
/usr/sbin/mysqld() [0xd2e0aa]
/usr/sbin/mysqld(ib::fatal::~fatal()+0xa4) [0x221d614]
/usr/sbin/mysqld(Buf_fetch<Buf_fetch_normal>::read_page()+0x192) [0x227c182]
/usr/sbin/mysqld(Buf_fetch_normal::get(buf_block_t*&)+0x18) [0x227c1d8]
/usr/sbin/mysqld(Buf_fetch<Buf_fetch_normal>::single_page()+0x42) [0x2281882]
/usr/sbin/mysqld(buf_page_get_gen(page_id_t const&, page_size_t const&, unsigned long, buf_block_t*, Page_fetch, char const*, unsigned long, mtr_t*, bool)+0x1cd) [0x228244d]
/usr/sbin/mysqld(trx_undo_lists_init(trx_rseg_t*)+0x262) [0x2217c22]
/usr/sbin/mysqld(trx_rseg_mem_create(unsigned long, unsigned int, unsigned int, page_size_t const&, unsigned long, std::priority_queue<TrxUndoRsegs, std::vector<TrxUndoRsegs, ut_allocator<TrxUndoRsegs> >, TrxUndoRsegs>*, mtr_t*)+0x3b3) [0x21ff0d3]
/usr/sbin/mysqld(trx_rsegs_init(std::priority_queue<TrxUndoRsegs, std::vector<TrxUndoRsegs, ut_allocator<TrxUndoRsegs> >, TrxUndoRsegs>*)+0x7cd) [0x2200b2d]
/usr/sbin/mysqld(trx_sys_init_at_db_start()+0x134) [0x2205b74]
/usr/sbin/mysqld(srv_start(bool, std::string const&)+0x2055) [0x21cc055]
/usr/sbin/mysqld() [0x204860e]
/usr/sbin/mysqld(dd::bootstrap::DDSE_dict_init(THD*, dict_init_mode_t, unsigned int)+0x90) [0x1ca5960]
/usr/sbin/mysqld(dd::upgrade_57::do_pre_checks_and_initialize_dd(THD*)+0x19a) [0x1f3b1ca]
/usr/sbin/mysqld() [0x107fe88]
/usr/sbin/mysqld() [0x246d2bc]
/lib64/libpthread.so.0(+0x7e65) [0x7f472b485e65]
/lib64/libc.so.6(clone+0x6d) [0x7f472960c88d]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0): Connection ID (thread ID): 1
Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.


How to combine 2 SQL statements into one? I have these 2 SQL statements. Each year, I need to add the new one for the new DB. How can I write it so it will just work for the new years instead of me manually editing this each year? Is there a way ONE will work for all? Each years will have 'CTX_' in it. thanks

USE CTX_2015

DBCC SHRINKFILE(ctx_2015_log)
BACKUP LOG ctx_2015 TO DISK='NUL:'
DBCC SHRINKFILE(ctx_2015_log)

USE CTX_2016

DBCC SHRINKFILE(ctx_2016_log)
BACKUP LOG ctx_2016 TO DISK='NUL:'
DBCC SHRINKFILE(ctx_2016_log)

A quick and dirty way would be to have a script for the next X years.
Something like:

IF db_id('ctx_2015_log') is not null
EXECUTE('
BEGIN
    USE select quotename(db_name(db_id(''ctx_2015_log'')))
    DBCC SHRINKFILE(ctx_2015_log)
    BACKUP LOG ctx_2015 TO DISK=''NUL:''
    DBCC SHRINKFILE(ctx_2015_log)
END
');

IF db_id('ctx_2016_log') is not null
EXECUTE('
BEGIN
    USE select quotename(db_name(db_id(''ctx_2016_log'')))
    DBCC SHRINKFILE(ctx_2016_log)
    BACKUP LOG ctx_2016 TO DISK=''NUL:''
    DBCC SHRINKFILE(ctx_2016_log)
END
');

You can build this statement with:

DECLARE @i int =2015
DECLARE @ti varchar (4)
DECLARE @command varchar (max)

WHILE @i<2020
BEGIN
    SET @ti=CONVERT(varchar(4), (@i))
    SET @command=
        'IF db_id(''ctx_'+@ti+'_log'') is not null
        EXECUTE(''
        BEGIN
            USE select quotename(db_name(db_id(''''ctx_'+@ti+'_log'''')))
            DBCC SHRINKFILE(ctx_'+@ti+'_log)
            BACKUP LOG ctx_'+@ti+' TO DISK=''''NUL:''''
            DBCC SHRINKFILE(ctx_'+@ti+'_log)
        END
        '');
        '
    PRINT @command
    SET @i+=1
END


Best Way to Reduce the Size of 8 TB DB? We have a legacy SQL Server 2005 DB that is 8 TB in size. We will be purging data older than 5 years from this DB. After the purge process is complete, I want to be able to shrink the DB or reduce the file size of the DB.

I know shrinking (DBCC shrink..) is not a good option. Hence, was thinking of exporting all tables/objects/data via a SSIS package to a new DB so I can reclaim all the space. Is there any other way of reducing the size of mdf and ndf files?

Alternatively, I could try exporting all data to a new filegroup and deleting the original filegroup. However, most of the tables are heaps and don't have a clustered index. Is it possible to move all data/tables to another filegroup without a clustered index? Also, can I delete the primary filegroup if I move all the data to a different filegroup?

Go with your alternate option - create a new file group, move the data there, and then drop the old file group. That's Paul Randall's advice, even:

So what if you do need to run a shrink? For instance, if you’ve deleted a large proportion of a very large database and the database isn’t likely to grow, or you need to empty a file before removing it?
The method I like to recommend is as follows:
Create a new filegroup. Move all affected tables and indexes into the new filegroup using the CREATE INDEX … WITH (DROP_EXISTING = ON) ON syntax, to move the tables and remove fragmentation from them at the same time. Drop the old filegroup that you were going to shrink anyway (or shrink it way down if its the primary filegroup)
Basically you need to provision some more space before you can shrink the old files, but it’s a much cleaner mechanism.
If you absolutely have no choice and have to run a data file shrink operation, be aware that you’re going to cause index fragmentation and you should take steps to remove it afterwards if it’s going to cause performance problems. The only way to remove index fragmentation without causing data file growth again is to use DBCC INDEXDEFRAG or ALTER INDEX … REORGANIZE. These commands only require a single 8KB page of extra space, instead of needing to build a whole new index in the case of an index rebuild operation.
Bottom line – try to avoid running data file shrink at all costs!


What are 'Range' and 'Radius' queries and what is the difference between them? While searching for easy and effective tools to improve performance of Data fetching for our application, we bumped into "Redis".

In documentation of redis, there is following specific mention of these two types or categories of queries:

  It supports data structures such as strings, hashes, lists, sets, sorted sets with range queries, bitmaps, hyperloglogs and geospatial indexes with radius queries...

This was first time in area of database and data structures, I have read such thing. I dug out internet to know more, but found nothing useful.

What exactly are Range and Radius queries? What is the difference between the two? Any explanation with example will be useful.

Range stands for one-dimensional distance while radius is standing for two-dimensional one.

Let's the data is the timestamp. It is one-dimensional and you can define part of data "in range" or timestamp BETWEEN a AND b. If your data represents the plane of points x:y then you can define part of them as "points closer than radius Z to the given point" or (x-x1)^2 + (y-y1)^2 < Z^2.


SQL Server Trigger call count Is there are way to find how many times trigger is called in a particular session? Our ETL application is inserting records like each row instead of statement (ie) If 100 records are to be inserted I think ETL issuing 100 insert statements instead of inserting in a single batch. So trigger is also called 100 times. I just want to confirm that trigger is called 100 times.

I have a idea to alter the trigger to have counter and store it in a table to find the number of trigger calls.

Is there a native way to find this?

You can set up an Extended Event trace and capture event type 'sp_statement_completed'. Filter the trace by object_type='trigger'. You can also pass your trigger name to narrow down further. Following definition will give you a starting point. Adjust file name, location, size and max_file_size based on your need.

CREATE event session [CaptureTriggerExecution] ON server ADD event sqlserver.sp_statement_completed( WHERE (
  [package0].[equal_uint64]([object_type],(21076))
  AND
  [object_name]=N'''My Trigger'''
)
) ADD target package0.event_file(SET filename=N'CaptureTriggerExecution',
  max_file_size=(10)) WITH (max_memory=4096 kb,
event_retention_mode=allow_single_event_loss,
max_dispatch_latency=30 seconds,
max_event_size=0 kb,
memory_partition_mode=none,
track_causality=ON,
startup_state=OFF)
GO


How to order databases by size? (For use in full backups) Recently, I've been trying to make our company's full backup process more efficient. In order to do this, my team has decided that instructing the backup job to tackle the full backups starting with the smallest databases and ending with the largest is the best way to meet our objectives. To do this, I added a cursor to the existing job to set the database order before running:

DECLARE @db_name VARCHAR(100), @db_id INT, @db_size INT;

PRINT '**********Order Databases by Size**********'
PRINT ' ';

DECLARE DB_by_Size CURSOR FAST_FORWARD FOR
SELECT db.[name], db.database_id, mf.size
FROM sys.databases AS db INNER JOIN sys.master_files AS mf ON db.database_id=mf.database_id
WHERE mf.[type] <> 0 AND db.name <>'tempdb'
ORDER BY mf.size;

OPEN DB_by_Size

FETCH NEXT FROM DB_by_Size
    INTO @db_name, @db_id, @db_size

WHILE @@FETCH_STATUS = 0
BEGIN

    EXEC BackupScripts.dbo.DatabaseBackup @Databases = @db_name,
        @Directory = N'\\*****\Backups',
        @BackupType = N'Full',
        @Compress = N'Y',
        @LogToTable = N'Y',
        @Period = N'Weekly',
        @Execute = N'Y'

    FETCH NEXT FROM DB_by_Size
        INTO @db_name, @db_id, @db_size

END;
CLOSE DB_by_Size;
DEALLOCATE DB_by_Size;
GO

I've been running the job like this for a couple weeks and observing the run times for each database in the command logs:

USE BackupScripts

SELECT *
FROM dbo.commandlog
WHERE StartTime >= 'yy-mm-dd 00:00:01.000' AND StartTime <= 'yy-mm-dd 23:59:00.000' AND CommandType <> 'UPDATE_STATISTICS' AND CommandType = 'BACKUP_DATABASE' AND Command NOT LIKE '%\Nightly\%'
ORDER BY StartTime DESC

When I look at the results for the above query, the longest-running backups aren't happening at the end like I expected. The run times seem to be pretty arbitrary instead of more or less from shortest to longest like I was trying to do. I think this has something to do with the way I'm measuring size (correct me if I'm mistaken here), so I'm thinking mf.size doesn't seem to be a good measure to accomplish what I'm going for.

Does anyone know a better table to pull my database size information from instead of sys.master_files? Or a better way to gauge how long a database will take to run a full backup before it actually runs the job? I've been hunting around for awhile, and can't get an accurate read from the tables I'm finding. Thanks!

I also order my databases by size, and use sys.master_files

This query would show your largest databases first, get rid of the desc and you will see the smallest first.

select
DatabaseName,
sizeGB=REPLACE(CONVERT(VARCHAR(20),CAST( SUM(radhe.sizeGB) AS MONEY),1), '.00','')
from
(
SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name,
(size*8)/1024.0/1024.0 SizeGB
FROM sys.master_files
--WHERE DB_NAME(database_id) = 'AdventureWorks'
) radhe
GROUP BY DATABASENAME
order by SUM(radhe.sizeGB) desc

this query shows all database files and order them by size starting by the largest ones:

select
DatabaseName,
Logical_Name,
Physical_Name,
sizeGB=REPLACE(CONVERT(VARCHAR(20),CAST(radhe.sizeGB AS MONEY),1), '.00','')
from
(
SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name,
(size*8)/1024.0/1024.0 SizeGB
FROM sys.master_files
--WHERE DB_NAME(database_id) = 'AdventureWorks'
) radhe
order by radhe.sizeGB desc

You could also use the following query that uses sys.dm_os_volume_stats:

SELECT * FROM sys.master_files AS f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id);

You could instead use the backup files to calculate which database restore first,
as per these questions below, but you would have to adapt your logic to them:

Having the backup file, how to work out the total disk space required to do the restore?

T-SQL query for date of last full backup, size and location

For further considerations in your restores, please have a look at this beautiful answer:

Running out of disk space mid-restore


Postgres: Performance Issue: Query on enormous data fails to use index This is the schema of task_statuses table
                         Table "public.task_statuses"
     Column     |            Type             | Collation | Nullable | Default
----------------+-----------------------------+-----------+----------+---------
 id             | uuid                        |           | not null |
 updated_at     | timestamp without time zone |           | not null |
 status         | task_status                 |           | not null |
 status_details | json                        |           |          |
Indexes:
    "task_statuses_id_updated_at_key" UNIQUE CONSTRAINT, btree (id, updated_at)
    "idx_task_statuses_id_updated_at" btree (id, updated_at)
Foreign-key constraints:
    "task_statuses_id_fkey" FOREIGN KEY (id) REFERENCES tasks(id) ON DELETE CASCADE

The table however is huge and has 150GB of data in production.
I am trying to run an extremely simple query
 SELECT
            ts.id
        FROM task_statuses ts
        WHERE
            ts.status IN ('succeeded', 'failed', 'cancelled')
        ORDER BY ts.id, ts.updated_at desc LIMIT 1000

It keeps timing out in production. When I remove ORDER BY the query runs successfully. Since, I have index in id and udpated_at, I am not sure why order by is timing out.
explain analyse times out as well.
Here is the explain for the above query.
Limit  (cost=10651159.84..10651276.51 rows=1000 width=24)
  ->  Gather Merge  (cost=10651159.84..10744721.60 rows=801902 width=24)
        Workers Planned: 2
        ->  Sort  (cost=10650159.81..10651162.19 rows=400951 width=24)
              Sort Key: id, updated_at DESC
              ->  Parallel Seq Scan on task_statuses ts  (cost=0.00..10628176.10 rows=400951 width=24)
                    Filter: (status = ANY ('{succeeded,failed,cancelled}'::task_status[]))

Query plan without order by:

https://explain.depesz.com/s/CfIU

Helpful links:

https://explain.depesz.com/s/G3VB

https://explain.depesz.com/s/hJCL

Suggestions or help would be much appreciated.

Your costs are on your WHERE predicate for ts.status.
You can see in the explain it's doing a Seq Scan for 400,951 rows with a cost of 10,628,176.10.
While having an index that is based on the ORDER BY fields in a query can help performance with the sorting, generally you should focus more on indexing based on your predicates (JOIN, WHERE, and HAVING clauses) because it won't have to do a Sequential Scan rather it can use the index to scan or seek even.
In this case if you had an index on the status column instead, your performance would likely be better (regardless sorting on your ORDER BY clause).
The difference in performance you're currently seeing is probably a difference in query plan between when you use and remove the ORDER BY clause that happens to be more efficient altogether. If you ran an explain for the query without the ORDER BY clause, I'm sure you'd see different operations occuring. But again, proper indexing on the status field should give you consistency in performance, either way.


Thought CTEs were syntactic sugar I was asked to test a change to a query, moving a Table Valued Function (TVF) into a CTE (non-recursive).  My understanding was that each CTE reference is executed separately, so there was a possibility of the change leading to tempdb contention...

I was very surprised to see the query performance improve when using the CTE.  IE:

 ;WITH cte AS (
    SELECT t.col FROM dbo.getValues() t)
 SELECT ...
   JOIN cte c ...
 UNION ALL
 SELECT ...
   JOIN cte c ...

Can anyone shed any light on why this is happening?

I think the behavior is that the CTE is "expected" to be evaluated multiple times, but it doesn't always work that way (e.g. it is not guaranteed). There are probably a lot of variables that dictate the behavior one way or the other. Can you share an actual execution plan?


creating server with Microsoft Server Management Studio I am working with a django project and I want to connect it to local server. I downloaded the Microsoft Server Management Studio but got lost on how to create a server and database?

If I am not wrong the object explorer tab only lets you connect to existing server. Is that right? If not how do I create a new server and database in SMSS?

Also I have imported a .bacpac file from the azure cloud and want to duplicate that cloud database in my local machine so that I don't mess up the actual database on cloud while testing. (I followed this link https://stackoverflow.com/questions/5475306/how-do-i-copy-sql-azure-database-to-my-local-development-server)

So how do I create a server/database on local machine using SMSS and load it with the .bacpac file to backup my django site?

Would appreciate the help

SSMS is only a development and management tool.  To install a SQL Server instance you need do download and install SQL Server.  EG SQL Server Developer Edition.  See: https://www.microsoft.com/en-us/sql-server/developer-get-started/

Or provision a SQL Server database in the cloud with Azure SQL Database.
https://azure.microsoft.com/en-us/free/services/sql-database/


Find transactions that are filling up the version store we have enabled the "READ_COMMITTED_SNAPSHOT" for some of our SQL Server 2005 databases.

Now from time to time we see that our TempDB is filling up the harddisk and we suspect the version store to be the culprit.

We monitor the TempDB usage through sys.dm_db_file_space_usage and once we see that the version store is increasing (as reported by  version_store_reserved_page_count) we would like to identity the transactions that are activily using the version store.

I'm using the following statement to find transactions that are using the version store:

SELECT db_name(spu.database_id) as database_name,
       at.transaction_begin_time as begin_time,
       case
         when at.transaction_state in (0,1) then 'init'
         when at.transaction_state = 2 then 'active'
         when at.transaction_state = 3 then 'ended'
         when at.transaction_state = 4 then 'committing'
         when at.transaction_state = 6 then 'comitted'
         when at.transaction_state = 7 then 'rolling back'
         when at.transaction_state = 6 then 'rolled back'
         else 'other'
       end as transaction_state,
       ast.elapsed_time_seconds as elapsed_seconds,
       ses.program_name,
       ses.row_count,
       (spu.user_objects_alloc_page_count * 8) AS user_objects_kb,
       (spu.user_objects_dealloc_page_count * 8) AS user_objects_deallocated_kb,
       (spu.internal_objects_alloc_page_count * 8) AS internal_objects_kb,
       (spu.internal_objects_dealloc_page_count * 8) AS internal_objects_deallocated_kb
FROM sys.dm_tran_active_snapshot_database_transactions ast
  JOIN sys.dm_tran_active_transactions at on at.transaction_id = ast.transaction_id
  JOIN sys.dm_exec_sessions ses ON ses.session_id = ast.session_id
  JOIN sys.dm_db_session_space_usage spu ON spu.session_id = ses.session_id
ORDER BY elapsed_time_seconds DESC
;

But this doesn't help me in identifying how much space each transaction is actually using in the version store.

Is there a way to get the information about the space usage in the version store per transaction (or even better: per statement)?

Edit: The "potential duplicate" (How to identify which query is filling up the tempdb transaction log?) does not take the version store into account (only temp tables, table variables, and space used for sort and hash operations).

In fact the accepted solution will not show anything for transactions that only use the version store (at least not for me)

It doesn't really make sense to track version store by session, or by transaction, or by query. If two different users are making use of the same version of a row/table, who owns it?

You can track this by object, though, which can help you narrow down which modules are causing the churn. Have a look at sys.dm_tran_top_version_generators:

USE [your database];
GO
SELECT obj =
  QUOTENAME(OBJECT_SCHEMA_NAME(p.object_id))
  + '.' + QUOTENAME(OBJECT_NAME(p.object_id)),
  vs.aggregated_record_length_in_bytes
FROM sys.dm_tran_top_version_generators AS vs
INNER JOIN sys.partitions AS p
ON vs.rowset_id = p.hobt_id
WHERE vs.database_id = DB_ID()
AND p.index_id IN (0,1);

And on SQL Server 2008+, you can also figure out which modules reference these tables by adding sys.dm_sql_referencing_entities:

SELECT
  obj = QUOTENAME(OBJECT_SCHEMA_NAME(p.object_id))
  + '.' + QUOTENAME(OBJECT_NAME(p.object_id)),
  referenced_by = QUOTENAME(r.referencing_schema_name)
  + '.' + QUOTENAME(r.referencing_entity_name),
  vs.aggregated_record_length_in_bytes AS size
FROM sys.dm_tran_top_version_generators AS vs
INNER JOIN sys.partitions AS p
ON vs.rowset_id = p.hobt_id
CROSS APPLY sys.dm_sql_referencing_entities
(
  QUOTENAME(OBJECT_SCHEMA_NAME(p.object_id))
  + '.' + QUOTENAME(OBJECT_NAME(p.object_id)), 'OBJECT'
) AS r
WHERE vs.database_id = DB_ID()
AND p.index_id IN (0,1)
ORDER BY size DESC, referenced_by;

This assumes that none of the version store could be created by ad hoc queries. However, it doesn't tell you which of those modules could be causing it - hopefully the naming scheme is logical and helps you narrow it down a bit.

(On 2005 you might be able to go through sysdepends and other old-style dependency views but I'm not 100% sure how reliable that would be.)


Is there a way stop the "." (period) acting as a word breaker in FullText Indexes? I know the period is a word breaker however I want to be able to search the fulltext catalog for a field containing the phrase "ASP.Net" or similar.
Currently my search returns 'ASP' and 'Net' without them occurring consecutively in the text. I want to know if there is a way to stop the period (.) acting as a word breaker for specific searches?

My research so far just suggests I use a LIKE statement however this is not feasible as there are millions of rows with large text fields to search as quickly as possible.

Having followed the link from the comment by Doug Deden:

  It looks like a multi-step process, but there are some promising ideas here: Customize the Behavior of Word Breakers with a Custom Dictionary and Custom WordBreaker for SQL Server Full-text on Stack Overflow.

I've seen that I will need to create a custom dictionary and add the entry 'ASP.Net'. This will ensure normal word breaks aren't applied to this phrase when crawling and/or searching on the full-text index.

Create a custom dictionary (SharePoint Server 2010)


Restore user db from UNC I need to create a job that restores all user databases from UNC to my SQL Server.

All the backups, for 3 days, are located in the same folder and I need to restore the last modified backup.
What is the right way to do this? Build steps to all databases separately?

I need a suggestion on how to build it.

If you can setup a linked server to the source database server you can simply do a restore from the last backup device containing a full database backup by finding the last backup file like this:

Declare @Database sysname = 'a'
Declare @BackupFile NVARCHAR(1024)
select @BackupFile =  bf.physical_device_name from
(select MAX(ISNULL(bs.backup_finish_date, 0)) AS backup_date,
            bm.physical_device_name
            FROM SOURCESERVER.master.dbo.sysdatabases AS sd
          LEFT OUTER JOIN SOURCESERVER.msdb.dbo.backupset AS bs ON sd.name = bs.database_name
          LEFT OUTER JOIN SOURCESERVER.msdb.dbo.backupmediafamily AS bm ON bm.media_set_id = bs.media_set_id
          where sd.name = @database
          GROUP BY
            bm.physical_device_name) bf
Declare @RestoreStatement NVARCHAR(1024) = 'Restore database ['+ @Database +'] from disk = N'''+@BackupFile+''' with replace,recovery'
execute SP_EXECUTESQL @RestoreStatement;

Now if you only have access to the unc path but not the linked server then the @backupfile becomes like this:.

Declare @BackupFile NVARCHAR(1024)
DECLARE @cmdline varchar(2000)
DECLARE @ReturnCode nvarchar(max)
CREATE TABLE #tempTable (cmdShellOutput VARCHAR(500))
SET @cmdline = 'start /B powershell.exe -executionpolicy bypass -NoLogo -command "Get-ChildItem -Path ''\\server\Backup'' -Filter ''*.bak'' | Sort-Object LastWriteTime -Descending | Select-Object -First 1 -ExpandProperty Name"'
INSERT #tempTable
EXEC @ReturnCode = master.dbo.xp_cmdshell @cmdline
select @BackupFile = (select top 1 cmdShellOutput from #tempTable)
DROP TABLE #tempTable


How to optimize performance of backup process? Servers go down during backup The context - So I am an intern (So I'll appreciate a more noob friendly response) and we have several websites on a server of which we do a backup daily, at midnight. We use MongoDB and there's a cron job with a script which does a mongodump to a temp dir on the server, the contents of which are then rsynced onto a remote backup server and the temp dir is deleted. We have several websites on there. The database of each is around 2GB, with the exception of two websites with a DB for 4GB and 5GB respectively. In total, all databases are about 23-24GB. There is also a completely separate server with only one website with about 100GB database.

The problem - We monitor our websites using Pingdom and New Relic and each night, during the backups, we get alerts for both servers. The logs don't say much, so I'm guessing they didn't go down completely, just became very very slow. Haven't observed it myself on any of these websites, but I have on a stage website when taking a manual backup. It did come to a crawl.

I am told this didn't used to happen before, even though the way we're backing up hasn't changed, since I came here. I'm guessing, that's because DBs are now larger or there's more of them.

The question - Is there a way to make the whole backup process more performance efficient, so the servers don't go down or come to a crawl?

My ideas - I've been reading about compact and was wondering if you think this would help? I'll probably try it on the stage servers first. Also, is there any way to not write the dump to a temp dir first, but instead to put it directly on the remote server? I'm hoping this would reduce the I/O operations on the live server and improve performance, though I can't seem to find a way to do it.

Some advice will be greatly appreciated, as the project manager is crying about the servers going down at night.

Thanks.

let us discuss few approaches for backup

If you are using replSet then could be very easy to take backup from   secondary hidden if possible.It will simply makes things easy for you.
3 member replica set 1 primary ,1 secondary ,1 secondary but hidden(can be used for backup)
you can use snapshot using LVM ,as it is system based approach so need manual intervention.for details Click here!
You can go for MMS(MongoDB Management Service) It will use your oplog to create backup.If not using replset then also you can create OPlog click here for details.

Also have a look at strategies comparison  click here


SQL Server 2014 All Administrator Accounts Disabled I made the most ridiculous thing. I accidentally disabled both remote logins and database accesses for the Administrator accounts (including the one with Windows Authentication (Administrator account) and sa account on the production server! Please tell me a way that there is a way to access the server with administrator privileges! :(

Is it possible from an installation or some kind of command line etc?

Again, Administrator account IS disabled for SQL Server (but I have remote connection to server, the account itself is working...)

The Administrator account of the SERVER is AAAAA\Administrator, and the instance is SOMETHING\SOME_INSTANCE.

When I am trying to log in from the remote desktop (on the server itself) with the AAAA\Administrator account, it says its disabled:

You need to start SQL Server in single-user mode.

The documentation Connect to SQL Server When System Administrators Are Locked Out says:

Start the instance of SQL Server in single-user mode by using either the -m or -f options. Any member of the computer's local Administrators group can then connect to the instance of SQL Server as a member of the sysadmin fixed server role.

See the page linked above for further considerations and step-by-step instructions.


Create a query using an enum I have a table where one of the columns is an enum.

mysql> SELECT COLUMN_TYPE FROM information_schema.columns WHERE [...];
+--------------------------------------------------------------+
| COLUMN_TYPE                                                  |
+--------------------------------------------------------------+
| enum('1 DAY','1 WEEK','1 MONTH','3 MONTH','1 YEAR','2 YEAR') |
+--------------------------------------------------------------+
1 row in set (0.00 sec)

I want to use these values in a subsequent query, something like this:

mysql> SELECT LPAD(MONTH(DATE_SUB(NOW(), INTERVAL 1 DAY)), 2, 0) A,
              LPAD(MONTH(DATE_SUB(NOW(), INTERVAL 1 WEEK)), 2, 0) B,
              LPAD(MONTH(DATE_SUB(NOW(), INTERVAL 1 MONTH)), 2, 0) C,
              LPAD(MONTH(DATE_SUB(NOW(), INTERVAL 1 YEAR)), 2, 0) D,
              LPAD(MONTH(DATE_SUB(NOW(), INTERVAL 2 YEAR)), 2, 0) E,
              YEAR(DATE_SUB(NOW(), INTERVAL 1 DAY)) F,
              YEAR(DATE_SUB(NOW(), INTERVAL 1 WEEK)) G,
              YEAR (DATE_SUB(NOW(), INTERVAL 1 MONTH)) H,
              YEAR(DATE_SUB(NOW(), INTERVAL 1 YEAR)) I,
              YEAR(DATE_SUB(NOW(), INTERVAL 2 YEAR)) J\G
*************************** 1. row ***************************
A: 05
B: 05
C: 04
D: 05
E: 05
F: 2018
G: 2018
H: 2018
I: 2017
J: 2016
1 row in set (0.00 sec)

I run these queries from a script and parsing the result of the first query and creating the second query isn't a massive problem. I was just wondering if someone could suggest a way to do this in one go.

There is no way to do what you want. ENUMs are inherently a unique textual representation, stored in a special way. You can't add a dynamic to them that makes them useful in any sense to an INTERVAL.

MySQL doesn't really have ENUM types, it has more of a macro over intergers (a hack). MDEV-4912, MDEV-4913
MySQL also lacks an interval type

For these reasons, the best you can do is probably a lookup table.

See also,

https://dba.stackexchange.com/a/177075/2639


MySQL rapid Data Replication from multiple Databases I am a Freelance Web Developer but not a Database Administrator at all except basic Database Operations like DML etc. Today I got a client asking me to develop a Web based application but there is a Problems concerning Databases and I don't know what strategy would best suit.

Problem Explanation

My Client got 6 different MS SQL and Oracle databases and all these databases are frequently updated with 3 to 4 seconds each updated with 6 different applications. Now I have to develop a web based single application to show data from all these 6 different databases.

Question

My Problem is that what strategy should I choose for my web based application specially when it's about performance

1) Replicate Database from all these 6 different databases into single MySQL database keeping in mind that these are updated every 3 to 4th second?

2) Or my application should directly access these 6 databases?

My Client says that my web application will be generating many different graphs which sometimes can take 30 seconds to generate single graph.

I would request my honor DBAs that please suggest me what would best suit for my application? My client doesn't want that his current 6 databases get slow down or unresponsive when application is accessing to generate graphs or something like that. Client is willing to let me make my choice to choose from above options. Web application should show the most recent data from database so if I choose to replicate then I will have to replicate every 3rd second.

Please kindly also tell that is it possible to tell database to replicate new data to other database every 3rd second and if yes then will it be resource intensive specially when 6 different database will do the same on single database?

Some might say depends how big the databases are; I think regardless of the size, accessing the databases directly instead of having a "local" mySQL database to query would be best, for the following reasons:

No need for disk/memory/maintenance of the mySQL database
If the updates to the various databases happen every few seconds, and the users may only use the web page every few minutes/hours/days, you are executing a lot of queries that are not needed most of the time, which in turn places additional load on the six databases. In other words, I think the client doesn't realize the impact of having a replication scheme.  Even if just a few tables from each database is involved, the synchronization code running every few seconds would be much worse than occasional specific queries (occasional being something running every few minutes/hours).
The code to replicate data from 6 separate databases would be quite complex to do correctly.


Database for building a realtime analytics system I want to build a system similar to Google Analytics (only used for internal use, less traffic and less feature), and mainly focus on

Real time counting of unique URI visit/PV by different dimensions of user demographic information, e.g. User Agent, OS, Country etc
Real time calculation of average user session length (if the different between two request from the same IP is less than 1 minute)

Are there any good database store enable this kind of query in real time?

p.s. I am currently testing InfiDB.

There is a trick to building fast realtime analytics, regardless of the platform.  I've done this with Microsoft Analysis Services, but you can use similar techniques with other platforms as well.

The trick is to have a leading partition that can be populated with near-realtime data and a historical partition (or partitions) that are optimised for fast queries.  If you keep the leading partition small enough it will be quick to query as well.

To manage this, your ETL processing populates the leading partition and you build a supplementary process that periodically converts the partitions to the fast query optimised format.  The exact nature of this process will vary with your platform.

On MS Analysis Services the leading partition is done as a ROLAP partition that reads directly off the table.  Trailing partitions are converted to MOLAP with aggregates.  Other OLAP systems will work similarly.  On Oracle you can create bitmap indexes and materialised view partitions on your trailing partitions to speed up queries.  Some other systems have this type of feature as well, although I'm not aware of MySQL supporting it.

At a guess, I'd say the cheapest mainstream platform that would do this is MS Analysis Services, which is only available bundled with SQL Server and can cannot be purchased separately.  For the partitioning with 2008 R2 you will need Enterprise Edition of SQL Server, which runs to about £22,000 per CPU socket in the UK and a bit less on the other side of the pond.  Microsoft are shipping a new 'Business Intelligence' edition of SQL Server with 2012.  Once this hits RTM the B.I. edition of the product does support partitioned cubes and is substantially cheaper than Enterprise Edition.  Depending on your budget and time constraints you may be able to use that instead.

Another aspect of the problem you will have to tackle is changed data capture - efficiently identifying and pushing new or changed data rows into the ETL process.  Most DBMS vendors' CDC features only work with their own databases, so if you want a CDC solution you may have to go to a third party app or triggers on the source.

Various third parties punt CDC applications that will migrate across database platforms.  A list of CDC products can be seen on the wikipedia entry on the subject.  Note that you may still have issues with integration.  For example IBM Infosphere CDC can only trigger external processes on a per-row basis, rather than per batch, which could cause efficiency problems on large data volumes.
You can create a set of triggers on the source tables that push out the changes into a staging area.  This would require you to have sufficient access to the source database to do this, so it may not be an option on vendor-supported databases.
If the data is from a file (for example a web server log) you would have to write a client side process that monitors the tail of the files for new records.

It is quite likely you will end up having to implement a pull process that polls the data sources.  In this case you have to work out the tolerable latency and write your process so it is efficient enough at detecting changes so it can be run sufficiently quickly.  There is an old saying, sometimes found in embedded systems circles, to the effect of: 'You know they're getting serious about reliability when they start polling'


How can I optimize performance on a MySQL ndbcluster? So, this is what I have:

A simple table classes__to_students:

|class   |student|
---------------
 Math     Alice
 Math     Bob
 Math     Peter
 Math     Anne
 Music    Bob
 Music    Chis
 Music    Debbie
 ...

There's 1000000 classes. Every student attends 500 classes.
(Bad analogy, I know...)
For testing, there's 4000 students in the DB (so there is 2.000.000 rows), but this DB should handle several million students, that's why I'm using a MySQL ndbcluster.

Anyway... a query like this:

SELECT student, COUNT(class) as common_classes
FROM classes_table
WHERE class IN (my_subject_list)
GROUP BY student
ORDER BY common_classes DESC

resulting in something like:

|student |commonClasses|
 Brad     6
 Melissa  4
 Chris    3
 Bob      3
 ...

takes about 1 second with a InnoDB engine on one server, which is ok. On a ndbcluster with 3 datanodes the same query takes up to 10 seconds, which is far too much. I don't know how the above statement is treated internally, but I guess that there is a lot of communication between the nodes, which makes it slow due to latency.
Can someone tell me, what happens in the cluster when I perform this query? How can I make it faster?

Note: this is a question that came up after I posted this question:
https://stackoverflow.com/questions/9095054/how-do-i-compute-a-ranking-with-mysql-stored-procedures
For more informations, have a look there!

First of all, Cluster is a storage engine. It doesn't actually execute queries because it doesn't speak SQL. That is why you use a MySQL server, which parses your queries and sends low-level storage engine API calls to the Cluster data nodes. The data nodes know how to retrieve or store data. Or you can talk to the data nodes directly using the NDB API(s).

MySQL Cluster has various means of executing queries. They boil down to:

Primary key lookup
Unique key lookup
Ordered index scan (i.e., non-unique indexes that use T-trees)
Full table scan

Let's say you have 4 data nodes in your cluster (NoOfReplicas=2). This means you have 2 node groups and each one has half the data. Cluster uses a hash on the primary key (unless you've controlled the partitioning using the 5.1 partitioning features). So for any table, half the rows are in one node group and half the rows are the in other node group.

Now for the 4 types of query execution. You can verify which type of execution is used with EXPLAIN. Here's how each ones works:

Primary key lookup - the MySQL server can calculate the hash on the primary key and know which data node the relevant row resides in. Then the MySQL server contacts that data node and receives the row.
Unique key lookup - the MySQL server cannot know which data node the row might be stored in. So it contacts a pseudo-random node. That data node has a hidden table that maps the unique key values to the primary key. Then the hash on the primary key reveals where the row resides.
Ordered index scans are sent in parallel to all data nodes, where they search their local t-tree.
Full table scans are send in parallel to all data nodes, where they scan their primary fragment.

Summary: primary key lookups are best. If you have more than 2 nodes, throughput goes up because all nodes are actively serving different fragments of data. Ordered index lookup and full table scans are done in parallel, so more nodes leads to better performance.


How to synchronize multiple instances of a stored procedure executing at the same time in SQL Server 2012? Overview:
In my scenario a user uploads an excel file that needs to be written into database, I am allocating each uploaded file a batch_id   that batch_id is written in last column of each row of my table as shown in these pictures

With the help of this batch_id column I will be able to segregate that which rows belong to which document.(for example: if batch_id=1 than these 1000  rows belongs to document 1)

Problem:
Consider two users user1 and user2 executing this stored procedure at the same time while Stored Procedure  is writing batch_id=1 in all the rows which belong to document1,at the same time rows belonging to  document2 will gets inserted thus causing a conflict wether to write batch_id=1 or batch_id=2 in the rest of remaining rows.

Imperfect  Solution:
To Execute Stored Procedure on one document at a time,when its done execute it on document 2 but this will let the user to wait until the current execution finishes which i don't want to  happen.

UPDATE:::::::::::::::

The batch_id value is coming from another table MASTER_CDT in this table batch_id is primary key and auto incremented, when a document gets uploaded first a batch_id is generated this batch_id is then inserted into rows corresponding to that batch, the problem is while batch_id(consider batch_id=1) is getting written into current batch rows if another user uploads document then batch_id is changed to value 2 while rows regarding batch 1 are still missing batch_id value but now batch_id=2 will be written to them therefore causing trouble.

You have not posted any code so I'll make some assumptions based on the additional information you added to your question.  Once a batch_id is assigned for the upload, the application should use that value when inserting related rows rather than retrieve a batch_id again, which may be different due to concurrency.  It seems you may be inserting rows individually and retrieving the batch_id repeatedly, either in code or the proc.

There are a couple of ways to fix the problem.  One is to add a @batch_id parameter to the proc and change the code to pass the value from the related MASTER_CDT insert (i.e. returned with SCOPE_IDEINITY()) for each spreadsheet row insert.

A more elegant solution is to pass the spreadsheet contents as a table-valued parameter like the example below.  Not only will this address the concurrency problem, it will be much more efficient than individual inserts.  Whether or not this is feasible depends on the language/API you are using.

--create table type for TVP
CREATE TYPE dbo.EmployeeSpreadsheetRows AS TABLE(
      employee_id int
    , name nvarchar(100)
    , email varchar(255)
    , phone_number varchar(20)
    , date_of_birth date
    , address nvarchar(100)
    , department nvarchar(100)
    , manager nvarchar(100)
    , validation_message nvarchar(1000)
    );
GO

CREATE PROC dbo.InsertSpreadsheet
      @FileName varchar(255) --values needed for MASTER_CDT table insert
    , @EmployeeSpreadsheetRows dbo.EmployeeSpreadsheetRows READONLY --spreadsheet rows
AS
SET XACT_ABORT ON;

BEGIN TRY
    BEGIN TRAN;
    INSERT INTO dbo.MASTER_CDT(FileName) VALUES(@FileName);
    DECLARE @batch_id int = SCOPE_IDENTITY();
    INSERT INTO EMPLOYEE_SPREADSHEET(
          employee_id
        , name
        , email
        , phone_number
        , date_of_birth
        , address
        , department
        , manager
        , validation_message
        , batch_id
        )
    SELECT
          employee_id
        , name
        , email
        , phone_number
        , date_of_birth
        , address
        , department
        , manager
        , validation_message
        , @batch_id
    FROM @EmployeeSpreadsheetRows;
    COMMIT;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 1 ROLLBACK;
    THROW;
END CATCH;
GO


Continuously arriving data with fast filtering on date in PostgreSQL I have a huge amount of data, which arrives continously. (About 10 million rows/day, ~3000 rows/10 seconds in peak intervals of the day.)

I want to store them in a PostgreSQL database (I use PostGis to process the geographical data, that's why PostgreSQL).

I usually filter these data by timestamp first, and only after this I want to select data based on other properties (so it's rare when I need to work with all of the data).

My first approach was to create a table which has an index on the timestamp (to enable fast filtering on timestamp), and I insert the freshly arrived rows to the table every 10 seconds (so about 500-3000 rows).
This approach worked for the first few days, but after that inserting the data into the DB took more time than the rate in which the data arrived (so I wanted to insert new data every 10 seconds, but inserting took more than 10 seconds). (It's reason is obviously updating the index. I use COPY to insert the data, as recommended here.)

The other ideas I considered were the following, but they make the handling of the data difficult:

Putting the continuously arriving data into a different table, and copy it into the full table e.g. every night. But the time to copy the data will be longer every day, so it's not sustainable after a while.
Putting every day's data into a different table. But in this case it's difficult to create queries which have overlapping intervals, or contain multiple days. (I have to UNION the tables.) But still this solution seems to be the best yet...
Dropping the index before every insert (as written in the documentation), but the index recreation takes more and more time as the table grows, so it's also not a sustainable solution.

Because the data arrives continuously (timestamp is monotonically increasing), if I don't use DB, I could store these data into files (e.g. 1 file/hour), and use binary search on these files according to the timestamps, and that would be much faster than using the DB to store the data (no need to index), and it also scales well (the time I need to insert and query the same amount of data is the same, it doesn't depend on the total data size). So I think it's not impossible to create a solution which meets my requirements.

But obviously, it would result in a much less usable database and it would be much worse in other aspects without PostgreSQL's functions (e.g. further filtering data, PostGIS features).

Storing the data in these files and only loading them to a temporary table for the duration of processing seems to be another possible solution to solve the performance problem, but I think it would be much slower to query (because I have to physically copy the data on the disk) and it seems to be an ugly solution.

What other options do I have to handle this amount of data in a sustainable way (so the complexity of selecting and inserting the same amount of data doesn't depend on the size of the total data amount), but still make querying the data reasonably simple?

As @a_horse_with_no_name suggested, you could do partitioning of the tables, which seems ideal for what you are describing. Something like pg_partman would help significantly with the process:

https://github.com/keithf4/pg_partman

You could also try an unlogged table, with a job to load the table in between bursts, something like this:

http://theplateisbad.blogspot.com/2015/08/row-row-row-your-boat.html

But in the end, I think that partitioning is the best way to go here, so that you are writing to a smaller table with many other sibling tables, the table inheritance is hidden behind PostgreSQL's partitioning machinery, and entire partitions can be eliminated via constraint exclusion depending on timestamp ranges.


MySQL says incorrect parameter count for function I've created even tho it's right I'm migrating my webapp from local to DigitalOcean, and I'm having a problem using a routine I've created. It works fine locally(version 5.5.50) but on the online MySQL(version 5.7.13) it gives me the following error:

  #1582 - Incorrect parameter count in the call to native function 'DISTANCE'

even tho the call is right.

I have deleted the function and it still gives me the same error, therefore I think it's not even being able to find my function.

I've tried to grant all privileges to my user and it's still not working.

The problem is that there was a native function distance() added in MySQL 5.7.5, which takes 2 arguments:

Distance() was added in MySQL 5.7.5.
Distance() is deprecated as of MySQL 5.7.6 and will be removed in a future MySQL release. Use ST_Distance() instead.

It will be removed in a later release, but unfortunately you would need to rename your function. Had you not dropped it, you could have changed the function call in the app to include the schema:

The preceding function name resolution rules have implications for upgrading to versions of MySQL that implement new built-in functions:

If you have already created a user-defined function with a given name and upgrade MySQL to a version that implements a new built-in function with the same name, the UDF becomes inaccessible. To correct this, use DROP FUNCTION to drop the UDF and CREATE FUNCTION to re-create the UDF with a different nonconflicting name. Then modify any affected code to use the new name.

If a new version of MySQL implements a built-in function with the same name as an existing stored function, you have two choices: Rename the stored function to use a nonconflicting name, or change calls to the function so that they use a schema qualifier (that is, use schema_name.func_name() syntax). In either case, modify any affected code accordingly.

[source]


Out of Memory Exception When Creating BACPAC File We're having a frustrating time migrating our on-premises SQL application to Azure. The database is large but not crazy, about 265 GB. SQL Server Management Studio > "Deploy Database to Windows Azure SQL Database" will run for 15 - 20 hours, and then fail with a Out of Memory exception when exporting one of our larger tables.

We have BAK files of our database and data. Is there a way to import this into Azure SQL Database?

Unfortunately, you can't use .back failes in Azure SQL Database (not Managed Instance). I'd sugesst that you should consider to use BCP which is export to local csv or binary files and can import there to Azure SQL Database.


CPU utilization dangerously high due to COUNT queries Our app has recently been receiving traffic up to 10-30 requests per sec. The life cycle of every request is pretty complex. MySQL's load average ranges from 5 (when the requests are 10 per sec) and goes to 20 (30 requests, peak traffic, lasts for a few minutes).

The load average is abysmally high due to extremely high CPU utilization of 200-350%. Now, for every request that we receive, we fire off 3 COUNT queries on a single table. They look pretty similar.Just to show you how one looks like:

SELECT COUNT(DISTINCT COL1,COL2)
FROM conversion_Details c
WHERE (DATE(COL3) BETWEEN '2016-08-01' AND '2016-08-14')
AND (COL4 = 0 OR COL4 = 17)
AND COL5=5
AND COL2 = 'SOMETHING'

As you can see, for 1 count query, we have 1 table and 5 columns involved.
In total,we have 3 varchar columns, 2 int columns, and 1 timestamp column.
I would like to add that all of these columns are already indexed, and the table contains about 1 million entries.

Now imagine 2 more queries like this one. These 3 queries are fired in parallel. If I comment these queries out,the load average comes down to less than 1.

Is there anything I can do to optimize my database further? MySQL is currently running on 8GB ram, 4 core, CentOS.

You can save some cpu cycles by using the same datatype as the column in your date range. So instead of:

WHERE (DATE(COL3) BETWEEN '2016-08-01' AND '2016-08-14')

assuming col3 is a timestamp, use something like:

where col3 >= timestamp('2016-08-01 00:00:00')
  and col3  < timestamp('2016-08-15 00:00:00')

You might need to use the unix_timestamp instead of timestamp conversion so that 'between' will work, but you can always do a >= and a < instead of using between.

There's also no need to include Col2 in your select, since col2 will always have the same value in each execution.

So, the query would become:

SELECT COUNT(DISTINCT col1)
FROM conversion_Details c
WHERE col3 >= timestamp('2016-08-01 00:00:00')
  AND col3  < timestamp('2016-08-15 00:00:00')
  AND col4 IN (0, 17)
  AND col5 = 5
  AND col2 = 'SOMETHING' ;

A covering index would help to improve efficiency further. Try this one (order matters):

ALTER TABLE conversion_Details
  ADD INDEX covering_index_52431
    (col5, col2, col4, col3, col1) ;


Copying 500 GB SQL Server database from Production to Dev instance We need to copy 500 GB database from our PRODUCTION server to a DEV server. We only have a window of 5 hours.

What would be the quickest way to achieve this?

There are two options you can do that will not cause any downtime to your production environment:

1) If you only want to grab the database structure itself (you don't need the data) you can simply script out the production database. Then simply run that on your dev server to create the database.

2) If you need the database and the data simply grab the latest backup of that database and restore it to your dev server. You will find that this is the most common method used to keep the development servers matched up to production data.


How to add a check constraint in Postgres without locking the table? I would like to add a check constraint to a very large table. Something like:

ALTER TABLE "accounts" ADD CONSTRAINT "positive_balance" CHECK ("balance" >= 0);

Unfortunately PostgreSQL 9.3 blocks reads or writes until the constraint check has been completed. I verified this by starting a transaction, running the ALTER TABLE, then opening a second transaction and checking that I couldn't read or write from the table until the first transaction completed.

Is there any way I can add this CHECK constraint without locking the table?

You can create a NOT VALID CHECK constraint, which will enforce the constraint going forward, but will not check the entire table for validation upon creation.  At some later date, you can attempt to VALIDATE the constraint (when a lock on the table is ok)

Please review the documentation - Quote below:

  ADD table_constraint [ NOT VALID ]

  This form adds a new constraint to a table using the same syntax as CREATE TABLE, plus the option NOT VALID, which is currently only allowed for foreign key and CHECK constraints. If the constraint is marked NOT VALID, the potentially-lengthy initial check to verify that all rows in the table satisfy the constraint is skipped. The constraint will still be enforced against subsequent inserts or updates (that is, they'll fail unless there is a matching row in the referenced table, in the case of foreign keys; and they'll fail unless the new row matches the specified check constraints). But the database will not assume that the constraint holds for all rows in the table, until it is validated by using the VALIDATE CONSTRAINT option.


Representing a Many-to-Many Relationship Between Students and Departments Suppose I have two tables

Table A=Student

Table B=Department

Table A:

CREATE TABLE student
(
StudentId INT IDENTITY PRIMARY KEY,
Name VARCHAR(255),
DepartmentId VARCHAR(255),
);
INSERT INTO student VALUES ('nav',1);
INSERT INTO student VALUES ('Angra',2);
INSERT INTO student VALUES ('navjot',3);

Table B

CREATE TABLE department
(
ID INT PRIMARY KEY,
DeptName VARCHAR(255),
studentId int unique foreign key references Student(StudentId)
);

INSERT INTO dbo.department VALUES (101, 'CSE', 1);
INSERT INTO dbo.department VALUES (102, 'Mech', 2);
INSERT INTO dbo.department VALUES (103, 'Civil', 1);

Now the question is:

Can I insert upper values(Values that i given above insert into table B) in  Table B,
I know most of you will say no.

But suppose  I have requirement to do it how it is possible  without removing unique constraint.

Note: Actually In first table I have DepartmentId=1,
I want that more than one row should insert in table b having studentid =1

(against one Departmentid [In Table A], I can add multiple StudentId [table B] in table B)

Output will be like this:

----------------------------------
ID      DeptName       studentId |
101     CSE               1      |
102     Mech              2      |
103     Civil             1      |
---------------------------------

In Sql not allowed duplicate data in Unique key.
but you can create combination of two column of unique key like
CONSTRAINT UC_Person UNIQUE (ID, StudentId)
This will solve your problem.

CREATE TABLE student
(
StudentId INT IDENTITY PRIMARY KEY,
Name VARCHAR(255),
DepartmentId VARCHAR(255),
);
INSERT INTO student VALUES ('nav',1);
INSERT INTO student VALUES ('Angra',2);
INSERT INTO student VALUES ('navjot',3);
select * from student
select * from department
drop table student
drop table department

CREATE TABLE department
(
ID INT PRIMARY KEY,
DeptName VARCHAR(255),
studentId int foreign key references Student(StudentId)
**CONSTRAINT UC_Person UNIQUE (ID,StudentId)**
);

INSERT INTO dbo.department VALUES (101, 'CSE', 1);
INSERT INTO dbo.department VALUES (102, 'Mech', 2);
INSERT INTO dbo.department VALUES (103, 'Civil', 1);


Does RCSI use less or same space than SNAPSHOT? Why? Trying to better understand row-versioning based transaction isolations - RCSI (Read Committed Snapshot Isolation and SNAPSHOT) - in SQL Server...

MSDN online article "Choosing Row Versioning-based Isolation Levels" states:

*"For most applications, read committed isolation using row versioning is recommended over snapshot isolation for the following reasons:

It consumes less tempdb space than snapshot isolation"*

which contradicts to the statement from Kimberly L. Tripp, Neal Graves's "SQL Server 2005 Row Versioning-Based Transaction Isolation":

"Row Versioning in Read Committed Using Row Versioning
For queries that are running under read committed using row versioning, row versions are necessary only for the duration of each SELECT statement in the transaction. However, row versions are maintained until the end of the transaction. This is an important point to consider for tempdb storage. The storage impact will be the same as it would be for snapshot isolation."

So, why does RCSI use less (or same) space?

UPDATE:
Probably I formulated the original question bad but it implies that there are RTFM explanations and confirmations for both contradictory "theories" and I'd like to understand:
Which is correct?
Why is the one is really correct?
and
Why isn't another really correct (Why is another wrong?)?

SUBQUESTION:

In case of multiple updates of the same record in a single transaction, how many versions are being stored?

Read committed snapshot isolation (statement-level snapshot isolation) potentially uses less storage than snapshot isolation (transaction-level snapshot isolation) because usually the row versions aren't required for as long a period of time after they've been generated.

For transaction-level snapshot isolation, the versions have to stick around for the entire duration of a transaction, which could be quite a long time (or quite a long time, as seen here). Of course, statements can run for a long time, too, but it's usually the case that a transaction will take longer.

To emphasise the point: A transaction running under snapshot isolation is guaranteed to see committed data as it existed at the start of the transaction. This means row versions have to be maintained until the whole transaction completes. Where the same transaction is running under RCSI, row versions can potentially be released after each statement. Overall, snapshot isolation tends to result in a larger version store size than RCSI on the same workload.

The same versions are generated with either or both settings enabled. I believe Kimberly was referring to storage impact in the sense of write rate, not storage used, so the two statements you mention aren't contradictory; just a little unclear or unspecific.


Relationships between tables I have 2 birds per pair IE "Maleringnumber"and "FenaleRingNumber" in tblPair
If I open a Form for tblBird I want to see all fields in the table AS WELL as "PairedStatus" of the male and the female. The tables in the pic are in a query. How do I link the tblBird and the tblPair

Your tblPair (Please tell me this is a classroom assignment. I would shutter to think business operational databases had tables names prepended with "tbl"!) is almost a good intersection table which is what is normally used to connect two entities in a relationship like the one you describe.

A "pair" is made up of two Birds, one male and one female. The dates when the pairing was made and ended and the cage the pair are located are tracked.

Here are some of my impressions of your schema. Some are outside the scope of your question, but may affect the ultimate resolution of that question.

You seem to have simplified the schema to include just data that may illustrate the relationship you are asking about. That is good, but there doesn't seem to be any purpose for RingNumber. And RingNumber would appear to be unique so why is there a RingNumberID? Did you simplify away their purpose? So let's just use BirdID for the duration and stipulate that it is an integer.

Let's also simplify the SexID as a simple char(1) field containing either 'M' or 'F'. Here is the new Birds table:

create table Birds(
  ID         int       auto_generate primary key,
  SexID      char( 1 ) check( SexID in( 'F', 'M' )),
  SpecieID   int       not null references Species( ID ),
  AddDate    date      not null, -- Date bird was added to database
  constraint UQ_Birds_FKAnchor unique( ID, SexID, SpecieID )
);

The CageNumber, RemovalDate and RemovalReason are not necessary for this discussion and probably shouldn't be in this table anyway.

What you want is a way to relate a male bird with a female bird of, I assume, the same species. This should be done in such a way to make it impossible to pair two male or two female birds or to include a bird in a new pairing if already in an existing pairing.

This could be done with an intersection table.

create table Pairs(
    MaleID     int  not null
    MaleSex    char( 1 ) check( MaleSex = 'M' ),
    FemaleID   int  not null,
    FemaleSex  char( 1 ) check( FemaleSex = 'F' ),
    SpecieID   int not null,
    Status     char( 1 ),
    PairedDate date default( NOW ),
    CageNumber int  not null,
    constraint UQ_Pairs_Male unique( MaleID ),
    constraint UQ_Pairs_Female unique( FemaleID ),
    constraint FK_Pairs_MaleBird foreign key( MaleID, MaleSex, SpecieID )
      references Birds( ID, SexID, SpecieID ),
    constraint FK_Pairs_FemaleBird foreign key( FemaleID, FemaleSex, SpecieID )
      references Birds( ID, SexID, SpecieID )
);

As you can see, very close to your existing table. You don't show the constraints of your table, and those are critical.

The first field must contain the ID of a bird defined as male ('M') in the Birds table because the second field may only contain an 'M' value. (This is also why there is a unique constraint which includes the ID field even though ID is unique all by itself.) So also with the third and fourth fields defining a female bird. The unique constraints guarantee no bird may be in more than one pairing and each pairing must be with the same species.

It looks like you want to be able to end the pairing, thus making the birds available for other pairing. In that case, add the Status field to the unique constraints. This will make it possible to have the same bird in different relationships but only once for each value of Status. I assume Status is something like "Ongoing" and "SoOverEachOther". But there could be more, like "GettingToKnow", "JustFriends", "HeadOverHeels" and so forth. This works either way.

This table defines a relationship between a male and a female bird of the same species. Each attribute in this table must define the relationship itself. Thus the single species identifier which applies to each bird, the date the pairing took place and the CageNumber identifying the honeymoon suite.

To display each pair, join the Pairs table twice with the Birds table to get the details of each bird. Rather simple really.


Python script leaves the database in restoring mode I am attempting to use python to automate some of our database restores.  When I run the SQL query the database restores and comes back up like normal, but when I run the python script that uses the same SQL query the database is left in the restoring mode.
Any help with what I'm doing wrong would be greatly appreciated.
import pyodbc

#Set these Variables
sourceserver = 'MySourceServerName'
destinationserver = 'MyDestinationServerName'
dbname = 'test'

sourceserverplusport = sourceserver + ',1433'
destinationserverplusport = destinationserver + ',1433'

def ExecSQL(serverplusport, dbnameIN, sql):
    try:
        connexecsql = pyodbc.connect('Driver={SQL Server};'
                                         'Server=' + serverplusport +';'
                                         'Database=' + dbnameIN + ';'
                                         'Trusted_Connection=yes;')

        #Set AutoCommit
        if connexecsql is not None:
           connexecsql.autocommit = True

        print(sql)
        cursor = connexecsql.cursor()
        cursor.execute(sql)
    finally:
        cursor.commit
        cursor.close
        connexecsql.commit
        connexecsql.close

sqlcommand = r"""IF @@SERVERNAME = 'MyDestinationServerName'
RESTORE DATABASE test FROM DISK = '\\MyBackupPath\test.BAK' WITH FILE = 1, MOVE N'test' to N'X:\Database\MDF\test.mdf',  MOVE N'test_log' to N'Y:\Database\LDF\test_log.ldf',  NOUNLOAD,  REPLACE, RECOVERY, STATS = 5;"""

ExecSQL(destinationserverplusport, "master", sqlcommand)

If I add one more ExecSQL command at the end of the script to run RESTORE DATABASE test WITH RECOVERY; at the end returns the error:

The database cannot be recovered because the log was not restored."

Including the REPLACE with the RECOVERY removes the error but leaves the database in restoring still.

Pyodbc won't progress past info messages automatically, and RESTORE generates a lot of them. So you must process them with cursor.nextset() or else you're actually aborting the RESTORE by running cursor.close() before it actually completes.
Eg
cursor.execute(sql)
while cursor.nextset():
    pass
cursor.close()


Mysql is larger than wamp Wamp which includes mysql,php,apache is only 24.7 MB but the only Mysql is 207 MB here.Why?

In a WAMP environment, a developer is probably more focused on

Coping PHP/Perl/Python
Configuring Apache to get the WebServer going
Using MySQL to store data, far less concern for the MySQL Utilities and other Bells and Whistles
Getting Windows to coordinate the use of Apache, PHP, and MySQL (If you use IIS instead of Apache, that means the environment is a WIMP).

WAMP/LAMP environments was set up with this mind. MySQL would come with the bare essentials for installing MySQL and storing and backing up data. Such bare essential utilities may include:

mysql : Command-Line Client Program
mysqldump : For taking Textual Backups

MySQL comes with utilities for doing

Replication
Table Repair
Load Testing (such as mysqlslap)
Reporting on Stats
things like these (See all the Major Utilities for MySQL)

If a serious Developer wants to be just as serious at prepping and optimizing the database as a Developer/DBA, then MySQL has everything a DBA could want in that bigger download. Downloading both would be just fine in that instance.


What happens to existing MySQL databases when I upgrade my Ubuntu distro? I am in the midst of upgrading my Ubuntu distro from 10.4 to 12.4. Fearfully, it asked me for root password on MySQL when I was upgrading the distro.

I used to have databases on that server, would they be erased? If so, how could I get them back?

Your databases will remain untouched (though of course I can't be liable :) ).

The reason are are being asked for a password could be:

Your new Ubuntu version uses a newer version of MySQL (it does). It needs root password so as to run the mysql_upgrade utility, which upgrades internal mysql schema to fit new version
You new installation includes new packages/dependencies, for which a new schema should be created. Such packages could be phpmyadmin, cacti, bugzilla, wordpress or any others which may require use of a database schema.

You should be in particular comfortable with this upgrade since this is from one LTS (Long Term Support) version to another. Not that I would expect database problems with other upgrades, but still, LTS versions are expected to be more "trusted".

Just to conclude, I am using Ubuntu. Usually installing MySQL from external binaries, but sometimes from package. I've never heard of a "data gone lost" problem due to an upgrade.