Why is my MySQL dump file and my phpMyadmin export file different sizes? I am looking to create a cron job to backup my MySQL database. I am using this command:

$ mysqldump --user="User" --password="Password" -A
    --single-transaction > /home/user/Export-`date +"%F"`.sql

This results in a 2.5 MB file size. However, when I use the phpMyAdmin export feature using the same user, I get a 3.7 MB file.

Can someone please explain the differences, and if I'm doing anything wrong?

DISCLAIMER : Not a User of PhpMyAdmin

I can explain what mysqldump does. When you run mysqldump, only data is dumped in SQL statements. Indexed are not copied. Indexes get rebuilt when the SQL is loaded into another server.

Here is an example

I have a query I run to show index and data usage.

Here is that query and its output

mysql> SELECT IFNULL(B.engine,'Total') "Storage Engine",
    -> CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
    -> SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(
    -> FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
    -> SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE(
    -> FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
    -> SUBSTR(' KMGTP',pw+1,1),'B') "Table Size" FROM
    -> (SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,
    -> SUM(data_length+index_length) TSize FROM
    -> information_schema.tables WHERE table_schema NOT IN
    -> ('mysql','information_schema','performance_schema') AND
    -> engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,
    -> (SELECT 2 pw) A ORDER BY TSize;
+----------------+----------------------+----------------------+----------------------+
| Storage Engine | Data Size            | Index Size           | Table Size           |
+----------------+----------------------+----------------------+----------------------+
| BLACKHOLE      |             0.000 MB |             0.000 MB |             0.000 MB |
| MEMORY         |             0.000 MB |             0.000 MB |             0.000 MB |
| FEDERATED      |             0.142 MB |             0.000 MB |             0.142 MB |
| MyISAM         |             8.049 MB |             0.368 MB |             8.417 MB |
| InnoDB         |            10.828 MB |             6.766 MB |            17.594 MB |
| Total          |            19.019 MB |             7.134 MB |            26.153 MB |
+----------------+----------------------+----------------------+----------------------+
6 rows in set (0.62 sec)

mysql>

Notice that my PC has 19.019 MB of data and 7.134 MB. mysqldump will only dump the 19.019 MB of data. The indexes are not exported.

If phpmyadmin uses mysqldump, then I only have one possible explanation. mysqldump uses the option --opt by default. Here is is what --opt does

  --opt               Same as --add-drop-table, --add-locks, --create-options,
                      --quick, --extended-insert, --lock-tables, --set-charset,
                      and --disable-keys. Enabled by default, disable with
                      --skip-opt.

If phpmyadmin summons mysqldump using --skip-opt, then notice --extended-insert is not enabled. With --extended-insert enabled, a batch of rows is inserted using INSERT INTO. Using --skip-opt or --skip-extended-insert will cause each row in every table to have a separate INSERT INTO. Consequently, this would blow up the size of the SQL script. Subsequently, a reload would be longer than usual.

After investigation, I observed the following:
Initial State:

One (1) table that uses four (4) columns structured as integers (max size: 11 digits)
Table contains over 3000 transactions.
Transaction count starts at 1.

Output:

phpMyAdmin Export Tool

At transaction number 2805, phpMyAdmin uses another "INSERT" MySQL command.

MySQL Dump Command

MySQL Dump uses only one (1) "INSERT" MySQL Command.
Conclusion:
The phpMyAdmin Export Tool requires more than one (1) MySQL Command, e.g. "INSERT", thereby causing the increase in file size.
In addition, we understand that each character, e.g. letter, number, increases used space in an output file.
Thank you. Peace.


For "human readable" output, you can use column mode, and turn header output on. That will get you something similar to the sqlplus output in your examples:

sqlite> select * from foo;
234|kshitiz|dba.se

sqlite> .mode column
sqlite> select * from foo;
234         kshitiz     dba.se

sqlite> .headers on
sqlite> select * from foo;
bar         baz         baf
----------  ----------  ----------
234         kshitiz     dba.se

On top of everything already said, you can control the width of your columns shown using .width.

Example

.width 8 0 3 9

Then your output will show your first column with a widht of 8 chars, second column auto-adjusting (see below), third column a width of 3 chars, and the fourth will have a width of 9 chars.

Alternative to .width, use an alias name padded with spaces.

Advantage is that you are adjusting the width on the fly in your query only for
those columns that require extra width, while the rest of the columns keep using
the auto-adjusting width.

All columns will use the auto-adjusting width, that is based on the
sqlite documentation width rule:

  If you specify a column a width of 0, then the column width is automatically
  adjusted to be the maximum of three numbers: 10, the width of the header, and
  the width of the first row of data. This makes the column width self-adjusting.
  The default width setting for every column is this auto-adjusting 0 value.

Let's say your table "my_table" has the columns "name", "age" and "address".
And you are interested in showing:

"name": first 20 characters
"age": auto-adjusting
"address": first 30 characters

Your query will be:

.mode columns
.headers on

CREATE TABLE my_table (name TEXT, age INTEGER, address TEXT);

INSERT INTO my_table VALUES ("short name",
22, "my house");

INSERT INTO my_table VALUES ("my name is very long",
22, "i live in my house somewhere in the planet Earth");

SELECT name AS "name                ",
       age,
       address AS "address                       "
FROM my_table;

Your output:

name                  age         address
--------------------  ----------  ------------------------------
short name            22          my house
my name is very long  22          i live in my house somewhere i


Amazon: How to change database name in Amazon RDS MySQL? I want to change MYSQL database name of the DB instance of Amazon RDS.

How it can be possible?

you can rename the database using the following command but make sure to take backup first

CREATE database new_db_name;
RENAME TABLE db_name.table1 TO new_db_name, db_name.table2 TO new_db_name;
DROP database db_name;

references:
http://www.rndblog.com/how-to-rename-a-database-in-mysql/
https://stackoverflow.com/questions/12190000/rename-mysql-database

Under the RDS "Instances" listing, click the arrow next to the DB Instance name.
You will find additional details on the selected instance
Click on the "Instance Actions" button and click "Modify"
Modify your instance information and save


Query hangs indefinitely - Postgres 9.6 We had an issue in where a query would hang indefinitely with the inclusion of certain IDs in the last WHERE IN statement here:

SELECT 1
FROM items
WHERE id NOT IN (
                 SELECT items_id
                 FROM "items_attribute"
                 WHERE "items_attribute"."attribute_id" IN
                       (88376, 88354, 88380, 74984, 12983, 1205, 10931, 78061)
);

Those IDs that caused the hanging did not seem to present with any data anomaly. Do you see any issues with this query that would cause recursion or any potential issues? We seemed to resolve the issue by making the subselect DISTINCT. The select in question returns 164760 rows. There are no views here, only small indexed tables.

Explain:

Seq Scan on items (cost=2594.08..3816558214.87 rows=164034 width=0)
   Filter: (NOT (SubPlan 1))
   SubPlan 1
     ->  Materialize  (cost=2594.08..25511.56 rows=139699 width=4)
           ->  Bitmap Heap Scan on items_attribute  (cost=2594.08..24267.06 rows=139699 width=4)
                 Recheck Cond: (attribute_id = ANY ('{88376,88354,88380,74984,12983,1205,10931,78061}'::integer[]))
                 ->  Bitmap Index Scan on items_attribute_attribute_id_index  (cost=0.00..2559.16 rows=139699 width=0)
                       Index Cond: (attribute_id = ANY ('{88376,88354,88380,74984,12983,1205,10931,78061}'::integer[]))

Thank you so much for taking a look!!

Try dropping and recreating any indexes on items.id or items_attribute.attribute_id.


Total Memory capped far below available memory We are testing out a new install of SQL Server 2012 on a virtual server and the memory allocated to SQL Server seems to be capped around 16 GB even though much more is available. What could cause this?

I've tried bumping up the SQL minimum server memory setting, but the change had no effect. I've listed some of the server details below.

SQL Server 2012 Enterprise
Windows Server 2008 R2 Enterprise x64
29.93 GHz (8 processors)
77 GB RAM
Min Server Memory: 48 GB
Max Server Memory: 56 GB
Database size: 44 GB

PerfMon info:

Working Set: ~17 GB
Page life expectency: ~7,500
Target Server Memory: ~73 GB
Total Server Memory: ~15.7 GB

SQL Server is not guaranteed to allocate the min server memory. If the load on the server never needs the minimum SQL Server will work with less - BOL http://msdn.microsoft.com/en-us/library/ms178067.aspx

As long as your configurations allow for the use of all memory you have allocated (they do), SQL Server will use what it needs, and generally no more. Since you are running a VM I would make sure you allocate 10% for the OS, remove the minimum as it can cause performance problems and let it run. If your CPU is pegged at 100%, queries are slow, and SQL Server is not allocating all of its available memory then it would be time to further troubleshoot.

One additional thing to check as you are using a VM: Make sure the Hypervisor (e.g. VMware ESX) allows the VM to actually take that amount of memory from the host.

Usually this is not the case per default and stuff like "memory ballooning" comes into place to prevent the virtual OS from using all the VMs memory for its own buffer cache.

While this is usually ok for application servers, it can really hurt on virtual database servers as they think they can allocate a lot of memory more than the hypervisor will actually allow ...


Is adding the ‘tbl’ prefix to table names really a problem? I’m watching some Brent Ozar videos (like this one, for instance) and he suggests not prefixing tables with ‘tbl’ or ‘TBL’.

On the internet I found some blogs saying it adds nothing to documentation, and also that “it takes longer to read it”.

Questions and considerations

It this really a problem? Because I’m prefixing tables with ‘tbl’ since my first dba job (the senior DBA told me to do that for organization).
Is this something that I need to get rid of? I made some tests, copying a really big table and giving it the ‘tbl’ prefix, while keeping the other one without it, and I didn’t notice any performance issue.

I once had a table and it was shiny and beautiful. It held all the financial transactions for an organization. And then we started loading data into it.

In the current month, they can state and restate values as often as they want. In the final 10 days of a month, they'd restate numbers -> run ETL processing -> review reports several times a day. Once the month is complete, then the books are sealed and they can't modify values.

It's amazing how much financial data a financial services firm generates... Something we didn't realize with our test data set was the volume of data was going to make their month end procedures untenable. It took an increasingly long time to delete out the "current month's data" prior to replacing it with the new trial run.

We had to do something to make it faster for processing without breaking the uncatalogued list of "who knows what" that all depends on the MonthlyAllocation table. I decided to play magician and whip the tablecloth out from underneath them. I went old-school and used a Partitioned View. The data already had  an IsComplete flag so I made two tables - each with contrary check constraints: MonthlyAllocationComplete, MonthlyAllocationInComplete

I then created the partitioned view with the same name as the original table: MonthlyAllocation. No process was any the wiser about the physical change we made to the database. No reports broke, none of the analysts with direct access reported any issues with that "table" before or after.

Cool story bro, but where you going?

What if they had a naming convention there, tbl_MonthlyAllocation? Now what? Do we spend lots of man hours going through every ETL, every report, every ad-hoc spreadsheet in the organization and updating them to use vw_MonthlyAllocation? And then of course all those changes go through the Change Board and that's always a quick and painless process.

You boss might ask: What's the reward to the company for all that work again?

The other option becomes we leave this view named as tbl_ and not spend all that time testing, updating and deploying code. Which becomes an amusing anecdote you explain to all the new hires, and those with short attention spans, that have to work with the database as to why you are inconsistent with the naming of objects

Or you don't double encode objects with redundant metadata. The database will happily tell you what is a table, what is a view, what is a table valued function, etc.

Naming conventions are good, just don't paint yourself into a corner with them.

Each side has its advantages and its disadvantages. It’s just up to your team’s or company’s lead to decide on conventions to follow.

We, for one, use the tbl convention. The main reason is that we know in scripts what we can and shouldn’t do. We have varied projects and people who jump in not knowing the schema by heart. Our tables have logical names, so it’s easy to find your way while writing scripts. While doing so, when we find a table, we know immediately (through IntelliSense) that it’s a table.
One can argue that adding the prefix does not add much context. However, removing it means there’s no context.

The only real advantage of not using the prefix is interchangeability. However, I would argue this is a potentially dangerous situation. Sure, your scripts and queries won’t break, but maybe you start relying on that fact too much, while some things just don’t work with views or are ill-advised.

In the end it really just boils down to what your team prefers and how it writes code/scripts.


How to find values from one table missing in another? I am a Junior Developer. I have been asked to create a subquery to solve this problem. If the question is not clear then please let me know so I can provide more details.

I have two tables,Article and Author. I have been tasked with doing the following:

  Select distinct Author values that don't exist in the Author table. The result should not contain any Authors that are NULL or Empty String. The purpose is to find any Author names that ARE in the Article table but NOT in the Author table.

I have Author column in both Tables. Can this problem be solved by joining these two tables?

I have tried

select distinct name
from author
where name NOT IN ( select author
                    from article
                    where author IS NOT NULL)

It doesn't give me the result that I want :(

select author from article where author IS NOT NULL and author <> ''
except
select name   from author

since any comparison the null is false I think below works

select author from article where author <> ''
except
select name   from author

I assume that you have the following tables:

CREATE TABLE Author(AutorID INT, Name VARCHAR(50));
CREATE TABLE Article(ArticleID INT, Article VARCHAR(MAX),Author VARCHAR(50));

Now to achieve your result just run the following query:

SELECT DISTINCT Article.Author WHERE Article.Author NOT IN
(SELECT Author.Name FROM Author) AND Article.Author IS NOT NULL
AND Article.Author != '';


Perform group by with random group expression (randomly partitioning rows) MYSQL Let's say I have a table (with millions of rows)

cat1|cat2|value
A   |   B|   10
...

and I want to group the data by cat1 and also split the grouping into 10 (at least roughly) equal partitions of the data.

The purpose of the random grouping is for the machine learning technique of cross-validation.  (It's similar to splitting subjects into a control group and experimental group.)

What I would imagine to use is the following:

select cat1, r, sum(value)
from t
group by cat1, floor(rand()*10) as r

which doesn't work.

One way which I believe would work would be simply to insert a new column with rand into my table and then group appropriately on that.

Is there any way of doing this grouping otherwise?

Your post piqued my curiosity and I look at the RAND() function - which you can't perform GROUP BYs with. I found this and if your table has a PRIMARY KEY, there is a MySQL pseudo-column (_rowid), much like is found in Oracle, Firebird (and maybe others) then you can issue queries such as this one (see structure and data for comments table (sample) below).

select MOD(_rowid, 3) AS my_field, COUNT(ticket_id)
FROM comments
GROUP BY my_field;

+----------+------------------+
| my_field | count(ticket_id) |
+----------+------------------+
|        0 |                6 |
|        1 |                7 |
|        2 |                7 |
+----------+------------------+

Now, if your PK is some sort of AUTO INCREMENT and "random" with respect to your data, you could perhaps use the _rowid pseudo-column (with MOD) to generate a sufficient degree of randomness for your requirements, and perform your aggregate queries?

---- structure and data in comments table---

CREATE TABLE `comments`
(
  `comment_id` int(11) NOT NULL,
  `ticket_id` int(11) NOT NULL,
  PRIMARY KEY (`comment_id`)
);

mysql> SELECT * FROM comments;
+------------+-----------+
| comment_id | ticket_id |
+------------+-----------+
|          1 |         1 |
|          2 |         1 |
|          3 |         1 |
|          4 |         1 |
|          5 |         2 |
|          6 |         2 |
|          7 |         2 |
|          8 |         2 |
|          9 |         3 |
|         10 |         3 |
|         11 |         3 |
|         12 |         3 |
|         13 |         4 |
|         14 |         4 |
|         15 |         4 |
|         16 |         4 |
|         17 |         5 |
|         18 |         5 |
|         19 |         5 |
|         20 |         5 |
+------------+-----------+

My understanding is that functions, such as rand(), are executed once per column reference to in SQL.  Thus any query which scans the whole table will produce the same "random" value for every row.  I think you're going to have to iterate through your data setting random numbers one at a time, RBAR-stylie.


Environmental Variables for OracleDB 12 in Oracle Linux are Overriding Each Other with SQLPlus Variables I want to set the environmental variables for OracleDB and SqlPlus properly. However, they are overriding each other. I'm doing something wrong, and I would like to get a hand finding where I went wrong.

Setup:
Oracle Linux 7
OracleDb 12.1.0.2.0 (12c)
Sqlplus 12.2.0.1.0

Here is my scenario.
Step 1: First, I set the environmental variables for OracleDB in "One" terminal window, as follows
[myuser]$ export ORACLE_HOME=/u02/app/oracle/product/12.1.0.1/dbhome_1
[myuser]$ export ORACLE_SID=o12c
[myuser]$ export LD_LIBRARY_PATH=/usr/lib:$ORACLE_HOME/lib
[myuser]$ export PATH=$ORACLE_HOME/bin:$PATH

Step 2: Second, I set the Environmental Variables for SQLPLUS in the "One" terminal window used in step 1, as follows:
[myuser]$ ORACLE_HOME=/usr/lib/oracle/12.2/client64
[myuser]$ PATH=$ORACLE_HOME/bin:$PATH
[myuser]$ LD_LIBRARY_PATH=$ORACLE_HOME/lib
[myuser]$ export ORACLE_HOME
[myuser]$ export LD_LIBRARY_PATH
[myuser]$ export PATH

Then, I perform the following command.
[myuser]$ sqplus / as sqldba

Which comes back with the following error.
SQL*Plus: Release 12.2.0.1.0 Production on Tue Jun 27 18:57:44 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

ERROR:
ORA-12545: Connect failed because target host or object does not exist

Enter user-name:

If I enter any user name (currently logged on user included), I get the following error:
Enter user-name: sysdba
Enter password:
ERROR:
ORA-12545: Connect failed because target host or object does not exist

Lastly, if I try to execute the "nolog" command, I get the following.
[oracle@localhost Desktop]$ sqlplus /nolog

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jun 27 19:01:10 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

And, I tried to enter the conn / as sysdba command, and I get the following:
SQL> conn / as sysdba
ERROR:
ORA-12545: Connect failed because target host or object does not exist

If I run the command, "env" I get the last Oracle_home variable set. In the scenario described above. It's SQLPlus Oracle_Home.

I found a solution.

I noticed that the path ORACLE_HOME wasn't working properly. I used a deductive method. I determined the error by executing common commands that normally run by supplying the name of the application (Within OracleDB suite). For example:

First I checked the oratab file, as follows:

 vi /etc/oratab

Which gave me the following output:

orcl:/u02/app/db12c/product/12.1.0/dbhome_1:Y

Then, I started executing common commands. For example, I ran the "Listener Controller application" as follows:

lnsrctl status

Which gave me the following error:

bash: /u02/app/db12c/product/12.1.0/dbhome_1/lnsrctl: No such file or directory

Then, I decided to use the command above by stating the path explicitly.

/$ORACLE_HOME/bin/lsnrctl

Which gave me the following:

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 27-JUN-2017 20:28:22

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.

Then I ran the status of the Lnsrctl, as follows:

LSNRCTL> STATUS

Which gave me the following:

STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                27-JUN-2017 20:28:37
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /u02/app/db12c/diag/tnslsnr/{ServerURL}/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST={ServerURL})(PORT=1521)))
The listener supports no services
The command completed successfully

After the above, I was able to run the commands normally. For instance,

sqlplus / as sysdba

Which gave me the familiar screen of:

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jun 27 20:29:11 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL>

And that was the problem. I'm not really sure if this is a glitch, setup problem, or design feature, but I hope this post can help someone who is going through the same situation. From now on, I have to use the command:

/$ORACLE_HOME/bin/lsnrctl

explicitly.

Thanks for everyone who posted.


How to add linked server SQL Server 2008 R2 I have two SQL Server 2008 located in different cities.

I have physical access to one of them and I can exec SQL Server Management Studio there.

For second server I have following parameters:

remote domain name
remote port
login
password

With these parameters I can connect to second server with SQL Server Management Studio.

But now, I have to create cross server queries. It is possible? How? :)

Do I have to add Linked Server? How?

I see only this window, but where do I writer domain/port/login/password info?

Change the server type to SQL server, put the servername,port number in the Linked Server name; on the Security section, change to "Be made using this security context" and enter the user name/password, assuming they are SQL Authentication credentials.

Create a linked server is quite simple. Follow the below steps-
Step 1 – Connect SQL server, using 'Database Engine' as server type along with proper Server name and authentication.

Step 2 – Into object explorer, navigate to linked server under 'Server Objects'.

Step 3 – Right click on 'Linked Servers' - select 'New Linked Server'. Fill all the required detail in respective dialog.

Step 4 – After filling the entire details click on Ok button, Linked server is configured and it should work. In case if it doesn't respond, once restart the SQL server.

For more detail click here


How to import table's data in MySql from Sql Server? I am trying to export table from SQL Server 2008 R2 TO MySql 5.5. For this I am using SQL Server Import and Export Wizard of Sql Server 2008, but it is giving this error.

Here this error may be occurring because table in Sql Server has a column with data type image and table in MySql this column's data type is LONGBLOB.

Please provide your expert answers. If not possible through SQL Server Import and Export Wizard than please suggest any other method for data transfer.

You could try changing the datatype in SQL Server to VARBINARY(MAX) :

http://wiki.ispirer.org/sqlways/mysql/data-types/longblob

Even if this is just to get your data into SQL Server. It may also be worth noting that IMAGE is being removed in future editions of SQL Server.

try to use following datatype for image in mysql
BLOB        L + 2 bytes, where L < 2^16
MEDIUMBLOB  L + 3 bytes, where L < 2^24


Problem deleting detached partition in DB2 LUW V9.7 Recently in prod environment we are having problems while dropping the detached partitions its giving this error

  """ DB21034E The command was processed as an SQL statement because it
  was not a valid Command Line Processor command. During SQL processing
  it returned: SQL20285N The statement or command is not allowed because
  the table named "schemaname.tablename_JAN1" has detached dependents or
  the asynchronous partition detach operation is not complete. Reason
  code = "2". SQLSTATE=55057"""

I did some research and could find this from IBM saying we need to restart the instance to solve the issue. Link below I think you could have seen many of such incidents of what i described, please help me with this issue thanks in advance

http://www-01.ibm.com/support/docview.wss?uid=swg21515721

When you detach a partition from a table, the detach operation returns quickly, but DB2 then starts a background process to clean up any global indexes.  You can't drop the table containing the detached partition until this process completes.

So, have you verified that the asynchronous cleanup that occurs after you issue the ALTER TABLE ... DETACH is actually finished?

As a suggestion, I would say that is good if you have all indexes for such table also 'partitioned'.
Whan you detach a partion, it's easy to db2 to detach also the partition for indexes of rows that exists in partition table.
And, also avoid create a Primary Key for that table. Create first an Unique index for column (or columns) that are PK, and, then alter table add constraint PK ....
Next time, when you issue the  alter table  detach partition  into , try to open another session to monitor the go through ...
So, in another session issue the command:
db2 list utilities show detail .... and see the detach still running.
(or db2pd  -db   -utilities)
hope it helps ...


How can I backup a large SQL Server database without impacting performance? We've had reports of queries running slowly or timing out early in the morning, and the only job I see running that I think could affect this is our database backup job.

The database itself is about 300GB, and the backup job starts at 4:30am and doesn't finish until a little after 7:00am. The current syntax of our backup job is:

BACKUP DATABASE [DatabaseName]
TO DISK = N'E:\Database Backups\DatabaseName.Bak'
WITH INIT, NOUNLOAD, NAME = N'DatabaseName.Bak',
NOSKIP, STATS = 10, NOFORMAT

E:\ is a partition on the server which holds both the databases and the database backups.

It should also probably be noted that this is a virtual server, not a dedicated standalone server. We started getting complaints about slowdowns during the backup process right after we switched to a virtual server, so I think it may be related.

Is there a way to run this backup job so it doesn't affect the query performance while its running?

We are using SQL Server 2005

Isolate backup I/O from the rest of the I/O on your system. While it may take longer and be more prone to hiccups, backing up over the network instead of to a local disk may help alleviate the direct impact on the instance. Even in a virtual machine you should be able to expose other storage so that SQL Server can write to a different I/O subsystem.
It will be unlikely to make any difference whatsoever but, if this is run from a job, why do you need to use the STATS option? Are you sure you need the other options (NOUNLOAD, NOSKIP, NOFORMAT)? I haven't done any extensive performance testing on the entire matrix of options, but IMHO you should only use the options that you know you need.
Run your full backups at a different time that doesn't interfere with regular user activity. This isn't always possible, but even in 24/7 operations there are peak and lull times.
If you move to 2008+ you can take advantage of backup compression (Enterprise on 2008, Standard or Enterprise in 2008 R2+). If you can't move to 2008 or don't have the adequate edition, there are 3rd party backup tools that will do compression for you, and they're all pretty good at it. Since 2008 I've been able to use native compression, and I have been quite satisfied with both speed and compression % there with no need to further invest in 3rd party. But with 2005 I remember having good success with Red-Gate SQL Backup; Quest LiteSpeed is also quite good, but I have no idea what kind of effort is being put into it since the Dell acquisition.
If your databases are in full recovery, you could go longer between full backups, trading that for the knowledge that if you have to recover to a point in time, you will potentially have more logs to restore. You could combine this option with Ali's suggestion below of taking diffs throughout the week - if you had to recover on Thursday, you would need to restore 1 full, 1 diff, and the logs since the diff.
If you have a lot of data that is stale and unchanging, you could consider hosting that in a different database with a different recovery plan, or at least on different filegroups, and split up your backup operations that way. If you have reference or archive data that can be made read-only, and if you are in simple recovery, you can move it to its own read-only filegroup, back it up once, and not have to include it in your nightly backups ever again. See these MSDN topics:

Performing piecemeal restores

Example: Piecemeal Restore of Only Some Filegroups (Simple Recovery Model)

Try it. it resolved timeout expired problem while large size db.

Private Sub Command1_Click()
On Error Resume Next
Dim con As New Connection
Dim tm As String
con.CommandTimeout = 500'''Command timeout should be 500

 With con
    .ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=dbiBMS;Data Source=192.168.103.4"
    .Open
 End With
 tm = CStr(Time)

con.Execute " backup database dbiBMS to disk='E:\Database_Backup\Test1.bak' with format "

con.Close
MsgBox tm
Exit Sub
x:
MsgBox Err.Description

End Sub


SQL Server 2008 R2 Mirroring issues I have this very frustrating problem that I've been trying to figure out for almost a week now. I have two SQL Server 2008 R2 STD w/SP3 box running on Windows 2012 R2 with two instances each, Server1 is the PRIMARY and Server2 is the MIRROR. Server2 was purchased for the purpose of mirroring so no live DBs yet. But Server1 is already in production. So here's the complete detailed setup for both.

Created a domain\user account and used it for both Servers' SQLService

Principal Server Configuration

Name: SERVER1.domain.com
NIC1 : 172.xx.xx.80
NIC2 : 172.xx.xx.81 (use for mirroring)
Named Instances: INSTANCE1 and INSTANCE2

Mirror Server Configuration

Name: SERVER2.domain.com
NIC1 : 172.xx.xx.82
NIC2 : 172.xx.xx.83 (use for mirroring)
Named Instances: INSTANCE1 and INSTANCE2

NO WITNESS YET

Static A records in DNS server

172.xx.xx.81 dbm-pri.domain.com and 172.xx.xx.83 dbm-mir.domain.com

Here's what's done on both servers:

checked SQL Server Config Manager and made sure TCP is enabled, then added the ports to be used in IPAll field
restarted the SQL Service (at one point of despair I restarted the servers itself, yes even the one running on production)
run netstat and confirmed that the endpoint ports are LISTENING, (sqlserver.exe), also run ping and tracert all tests are good
from both PRINCIPAL and MIRROR, ran telnet FQDN port, confirmed that I can connect from/to both
created the endpoints on both servers

PRINCIPAL

    CREATE ENDPOINT INSTANCE1_EndPoint
       STATE=STARTED
       AS TCP (LISTENER_PORT=6025,LISTENER_IP=(172.xx.xx.81))
       FOR DATABASE_MIRRORING (ROLE=PARTNER)

MIRROR

    CREATE ENDPOINT INSTANCE1_EndPoint
       STATE=STARTED
       AS TCP (LISTENER_PORT=6025,LISTENER_IP=(172.xx.xx.83))
       FOR DATABASE_MIRRORING (ROLE=PARTNER)

granted CONNECT permission to the domain\user account

CREATE LOGIN [domain\user]
FROM WINDOWS
WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO

GRANT CONNECT on ENDPOINT::INSTANCE1_EndPoint TO [domain\user]
GO

backed up and restored the DB and logs with NO RECOVERY
ran alter db set partner on the mirror server

ALTER DATABASE DBName SET PARTNER = 'TCP://dbm-pri.domain.com:6025'

ran alter db set partner on principal server

ALTER DATABASE DBName SET PARTNER = 'TCP://dbm-mir.domain.com:6025'

Error:

  Msg 1418, Level 16, State 1, Line 1
  The server network address "TCP://dbm-mir.domain.com:6025" can not be reached or does not exist...

tested netstat, ping, tracert and telnet on both Servers, all are successful

I've tried re doing this so many times now and I'm lost for ideas

Have you tried using an IP address?  I have seen an issue in R2 in the past whereby SQL Server was unable to resolve an FQDN but was fine with an IP address.

It could be a possibility port number 6025 might be used by another application try changing the port number .


Manually specifying candidate masters in Maxscale Looking over the documentation, I haven't quite been able to find if its possible to manually set the candidate master status of a server in Maxscale.  The reason I am looking for this is because some of the slaves we have hooked up the the master/slave server architecture have inferior hardware to the others, and we don't want them to become the master.  Looking at the documentation, it is possible to set the status of a sever as master or slave, but not specify that we don't want it to become a master.

Is there a setting to do this in Maxscale?  Possibly in maxadmin or a .cnf file or something?

Thanks.

You can use priorities as described here: https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale/maxscale-galera-monitor/


Raid 1 or Raid 1+0 for SQL Server Architecture We are looking at new servers.  Today our SQL Servers are built as follows:

C/D on RAID 1 ,
E : Tempdb on RAID 1,
F:log files on RAID 1 ,
G : Catalog data on RAID 1 ,
H: model data on RAID 1 ,
I : symbols on RAID 1,
J: DB maintenance on RAID 1 ,

So we have a lot of SSD’s, all on RAID 1 with no sharing.

We are considering changing to a large RAID 1+0.  However the concern is if we will end up with performance impacts since there are random reads that happen throughout the day but not as much as writes.

My question is - whether a large 1+0 RAID is suggested / recommended, which would reduce the quantity of SSD’s that we use and allow us to expand a single container easier or stick with single RAID 1’s?

RAID level 10(1+0) has been recommended by microsoft for high reads. Refer the doc: https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms190764%28v%3dsql.105%29

This level is also known as mirroring with striping. RAID 10 uses a striped array of disks that are then mirrored to another identical set of striped disks. For example, a striped array can be created by using five disks. The striped array of disks is then mirrored using another set of five striped disks. RAID 10 provides the performance benefits of disk striping with the disk redundancy of mirroring. RAID 10 provides the highest read-and-write performance of any one of the other RAID levels, but at the expense of using two times as many disks.

RAID levels higher than 10 (1 + 0) may offer additional fault tolerance or performance enhancements. These levels generally are proprietary systems. For more information about these types of RAID systems, contact the hardware vendor.

Microsoft recommeneds raid 10 for SQL server. but before taking about raid 10 I would like to mention going to RAID 0 for SQL server too risky because if a drive failed in RAID 0 your data will be lost the only way to recover it to use a privous backup file. I would like to recommend you to change RAID configuration as soon as possible.

RAID 1 doesn't support host swap without if you have a fail HDD then you have to shutdown your machine, change HDD, then rebuild your RAID. which is not affordable for production server.

Now let us go back to the original topic using RAID 10 for SQL server, here is the main advantages for RAID 10:

there is a disk merroring; if a drive failed then you can swap it and rebuild your RAID without loosing a single byte.
you can perform a hot swap; no need to shutdown your server just unplug, plug and rebuild.
RAID 10 has an excellent performance and high reliability of both read and write data patterns.

Here is the main Disadvantages for RAID 10:
RAID 10 is a mirrored set of RAID 0 drives. RAID 10 requires a minimum of 4 drives, and can survive the failure of 1 drive. Writes have the performance improvements from RAID 0, along with redundancy from being in a mirror set. Once again, note that the redundancy will mean that you can use only 50% of total disk space.
RAID 10 design:

Here is some notes from Microsoft about RAID 10


SQL Server Management Studio 18 won't open (only splash screen pops up) I just installed SSMS 18 GA on a computer with only VS2019 installed, and when I try to open SSMS the splash screen will come up, but then the process exits.

Running ssms with the -log parameter reveals an error message:

  CreateInstance failed for package [Task Scheduler Package]Source:
  'mscorlib' Description: Could not load file or assembly
  'Microsoft.VisualStudio.Shell.Interop.8.0, Version=15.0.0.0,
  Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its
  dependencies. The located assembly's manifest definition does not
  match the assembly reference. (Exception from HRESULT: 0x80131040)
  System.IO.FileLoadException: Could not load file or assembly
  'Microsoft.VisualStudio.Shell.Interop.8.0, Version=15.0.0.0,
  Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its
  dependencies. The located assembly's manifest definition does not
  match the assembly reference. (Exception from HRESULT: 0x80131040)
  File name: 'Microsoft.VisualStudio.Shell.Interop.8.0,
  Version=15.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'
  ---> System.IO.FileLoadException: Could not load file or assembly 'Microsoft.VisualStudio.Shell.Interop.8.0, Version=8.0.0.0,
  Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its
  dependencies. The located assembly's manifest definition does not
  match the assembly reference. (Exception from HRESULT: 0x80131040)
  File name: 'Microsoft.VisualStudio.Shell.Interop.8.0, Version=8.0.0.0,
  Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'

  WRN: Assembly binding logging is turned OFF. To enable assembly bind
  failure logging, set the registry value
  [HKLM\Software\Microsoft\Fusion!EnableLog] (DWORD) to 1. Note: There
  is some performance penalty associated with assembly bind failure
  logging. To turn this feature off, remove the registry value
  [HKLM\Software\Microsoft\Fusion!EnableLog].

  at System.Reflection.RuntimeAssembly.GetType(RuntimeAssembly
  assembly, String name, Boolean throwOnError, Boolean ignoreCase,
  ObjectHandleOnStack type)    at
  System.Reflection.RuntimeAssembly.GetType(String name, Boolean
  throwOnError, Boolean ignoreCase)    at
  System.Activator.CreateInstance(String assemblyString, String
  typeName, Boolean ignoreCase, BindingFlags bindingAttr, Binder binder,
  Object[] args, CultureInfo culture, Object[] activationAttributes,
  Evidence securityInfo, StackCrawlMark& stackMark)    at
  System.Activator.CreateInstance(String assemblyName, String typeName)
  at System.AppDomain.CreateInstance(String assemblyName, String
  typeName)

  System.IO.FileLoadException: Could not load file or assembly
  'Microsoft.VisualStudio.Shell.Interop.8.0, Version=8.0.0.0,
  Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its
  dependencies. The located assembly's manifest definition does not
  match the assembly reference. (Exception from HRESULT: 0x80131040)
  File name: 'Microsoft.VisualStudio.Shell.Interop.8.0, Version=8.0.0.0,
  Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'

  WRN: Assembly binding logging is turned OFF. To enable assembly bind
  failure logging, set the registry value
  [HKLM\Software\Microsoft\Fusion!EnableLog] (DWORD) to 1. Note: There
  is some performance penalty associated with assembly bind failure
  logging. To turn this feature off, remove the registry value
  [HKLM\Software\Microsoft\Fusion!EnableLog].

I tried running "Repair" on SSMS, and tried a repair of C:\ProgramData\Package Cache\{AAA9F15B-AF45-4562-9991-93A848D3A902}v15.0.28307.421\redist\vs2017_isoshell_for_ssms.msi, but the problem persists.

I just overlaid the Microsoft.VisualStudio.Shell.Interop.8.0.dll in C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\PublicAssemblies with the copy from ..\PrivateAssemblies\Interop and the IDE opens. (Thanks to Mitch for discovering the offending DLL name.)

This looks like a popular Q & A today, so I'm glad to know this wasn't just me.

There is another solution to this problem. On the Azure Feedback item SQL Server Management Studio 18 GA (15.0.18118.0) not compatible with .NET Framework 4.8 Erich Signer suggests:

Step 1:
C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\CommonExtensions\Platform\Microsoft.VisualStudio.MinShell.Interop.pkgdef
Rename and make sure this file does not have the .pkgdef extension anymore. Or remove the file.
Step 2:
Remove the following Registry Key (which is created at SSMS 18 Startup using the ".pkgdef" file mentioned above):
HKEY_CURRENT_USER\SOFTWARE\Microsoft\SQL Server Management Studio\18.0_IsoShell_Config\RuntimeConfiguration\dependentAssembly\bindingRedirection\{8BC7AF31-B6DA-4B97-8B36-F0500DECB147}

Based on a comment originally left by omzig


9000 rows affected message, but no rows actually changed I'm having a problem with MySQL. When I run the code below, via phpmyadmin:

UPDATE wp_posts
    SET post_content = REPLACE
        (
        post_content,
        '31.media.tumblr.com/',
        '41.media.tumblr.com/'
        )
    WHERE post_content LIKE '%31.media.tumblr.com/%'
    AND post_content NOT LIKE '%.gif%';

It returns:

  9000 rows affected

If I make a search, it shows that it replaced all the needed things successfully. But if I go and hit the 'EDIT' option in phpmyadmin on one of the rows inside the post_content column, no changes are done inside it; everything stays the same.

What might be the reason?

For me there's something wrong with the code above. Maybe it returns NULL or something and stops the update. If I remove this part:

AND post_content NOT LIKE '%.gif%';

...it does work properly.

The database is a default WordPress one:

Example of data inside post_content:

some text

other text

some other text
...

Is your MySQL on auto-commit? If not, try immediately after the update to run a commit statement.
What I think it is happening is that when you run an update statement from phpMyAdmin you are actually inside a SESSION. If you do not run the statement: COMMIT; when you go outside the SESSION everything gets rolled-back


Index on Persisted Computed column needs key lookup to get columns in the computed expression I have a persisted computed column on a table which is simply made up concatenated columns, e.g.

CREATE TABLE dbo.T
(
    ID INT IDENTITY(1, 1) NOT NULL CONSTRAINT PK_T_ID PRIMARY KEY,
    A VARCHAR(20) NOT NULL,
    B VARCHAR(20) NOT NULL,
    C VARCHAR(20) NOT NULL,
    D DATE NULL,
    E VARCHAR(20) NULL,
    Comp AS A + '-' + B + '-' + C PERSISTED NOT NULL
);

In this Comp is not unique, and D is the valid from date of each combination of A, B, C, therefore I use the following query to get the end date for each A, B, C (basically the next start date for the same value of Comp):

SELECT  t1.ID,
        t1.Comp,
        t1.D,
        D2 = (  SELECT  TOP 1 t2.D
                FROM    dbo.T t2
                WHERE   t2.Comp = t1.Comp
                AND     t2.D > t1.D
                ORDER BY t2.D
            )
FROM    dbo.T t1
WHERE   t1.D IS NOT NULL -- DON'T CARE ABOUT INACTIVE RECORDS
ORDER BY t1.Comp;

I then added an index to the computed column to assist in this query (and also others):

CREATE NONCLUSTERED INDEX IX_T_Comp_D ON dbo.T (Comp, D) WHERE D IS NOT NULL;

The query plan however surprised me. I would have thought that since I have a where clause stating that D IS NOT NULL and I am sorting by Comp, and not referencing any column outside of the index that the index on the computed column could be used to scan t1 and t2, but I saw a clustered index scan.

So I forced the use of this index to see if it yielded a better plan:

SELECT  t1.ID,
        t1.Comp,
        t1.D,
        D2 = (  SELECT  TOP 1 t2.D
                FROM    dbo.T t2
                WHERE   t2.Comp = t1.Comp
                AND     t2.D > t1.D
                ORDER BY t2.D
            )
FROM    dbo.T t1 WITH (INDEX (IX_T_Comp_D))
WHERE   t1.D IS NOT NULL
ORDER BY t1.Comp;

Which gave this plan

This shows that a Key lookup is being used, the details of which are:

Now, according to the SQL-Server documentation:

  You can create an index on a computed column that is defined with a deterministic, but imprecise, expression if the column is marked PERSISTED in the CREATE TABLE or ALTER TABLE statement. This means that the Database Engine stores the computed values in the table, and updates them when any other columns on which the computed column depends are updated. The Database Engine uses these persisted values when it creates an index on the column, and when the index is referenced in a query. This option enables you to create an index on a computed column when Database Engine cannot prove with accuracy whether a function that returns computed column expressions, particularly a CLR function that is created in the .NET Framework, is both deterministic and precise.

So if, as the docs say "the Database Engine stores the computed values in the table", and the value is also being stored in my index, why is a Key Lookup required to get A, B and C when they are not referenced in the query at all? I assume they are being used to calculate Comp, but why? Also, why can the query use the index on t2, but not on t1?

Queries and DDL on SQL Fiddle

N.B. I have tagged SQL Server 2008 because this is the version that my main problem is on, but I also get the same behaviour in 2012.

Why is a Key Lookup required to get A, B and C when they are not referenced in the query at all? I assume they are being used to calculate Comp, but why?

Columns A, B, and C are referenced in the query plan - they are used by the seek on T2.

Also, why can the query use the index on t2, but not on t1?

The optimizer decided that scanning the clustered index was cheaper than scanning the filtered nonclustered index and then performing a lookup to retrieve the values for columns A, B, and C.
Explanation
The real question is why the optimizer felt the need to retrieve A, B, and C for the index seek at all. We would expect it to read the Comp column using a nonclustered index scan, and then perform a seek on the same index (alias T2) to locate the Top 1 record.
The query optimizer expands computed column references before optimization begins, to give it a chance to assess the costs of various query plans. For some queries, expanding the definition of a computed column allows the optimizer to find more efficient plans.
When the optimizer encounters a correlated subquery, it attempts to 'unroll it' to a form it finds easier to reason about. If it cannot find a more effective simplification, it resorts to rewriting the correlated subquery as an apply (a correlated join):

It just so happens that this apply unrolling puts the logical query tree into a form that does not work well with project normalization (a later stage that looks to match general expressions to computed columns, among other things).
In your case, the way the query is written interacts with internal details of the optimizer such that the expanded expression definition is not matched back to the computed column, and you end up with a seek that references columns A, B, and C instead of the computed column, Comp. This is the root cause.
Workaround
One idea to workaround this side-effect is to write the query as an apply manually:
SELECT
    T1.ID,
    T1.Comp,
    T1.D,
    CA.D2
FROM dbo.T AS T1
CROSS APPLY
(
    SELECT TOP (1)
        D2 = T2.D
    FROM dbo.T AS T2
    WHERE
        T2.Comp = T1.Comp
        AND T2.D > T1.D
    ORDER BY
        T2.D ASC
) AS CA
WHERE
    T1.D IS NOT NULL -- DON'T CARE ABOUT INACTIVE RECORDS
ORDER BY
    T1.Comp;

Unfortunately, this query will not use the filtered index as we would hope either. The inequality test on column D inside the apply rejects NULLs, so the apparently redundant predicate WHERE T1.D IS NOT NULL is optimized away.
Without that explicit predicate, the filtered index matching logic decides it cannot use the filtered index. There are a number of ways to work around this second side-effect, but the easiest is probably to change the cross apply to an outer apply (mirroring the logic of the rewrite the optimizer performed earlier on the correlated subquery):
SELECT
    T1.ID,
    T1.Comp,
    T1.D,
    CA.D2
FROM dbo.T AS T1
OUTER APPLY
(
    SELECT TOP (1)
        D2 = T2.D
    FROM dbo.T AS T2
    WHERE
        T2.Comp = T1.Comp
        AND T2.D > T1.D
    ORDER BY
        T2.D ASC
) AS CA
WHERE
    T1.D IS NOT NULL -- DON'T CARE ABOUT INACTIVE RECORDS
ORDER BY
    T1.Comp;

Now the optimizer does not need to use the apply rewrite itself (so the computed column matching works as expected) and the predicate is not optimized away either, so the filtered index can be used for both data access operations, and the seek uses the Comp column on both sides:

This would generally be preferred over adding A, B, and C as INCLUDEd columns in the filtered index, because it addresses the root cause of the problem, and does not require widening the index unnecessarily.
Persisted computed columns
As a side note, it is not necessary to mark the computed column as PERSISTED, if you don't mind repeating its definition in a CHECK constraint:
CREATE TABLE dbo.T
(
    ID integer IDENTITY(1, 1) NOT NULL,
    A varchar(20) NOT NULL,
    B varchar(20) NOT NULL,
    C varchar(20) NOT NULL,
    D date NULL,
    E varchar(20) NULL,
    Comp AS A + '-' + B + '-' + C,

    CONSTRAINT CK_T_Comp_NotNull
        CHECK (A + '-' + B + '-' + C IS NOT NULL),

    CONSTRAINT PK_T_ID
        PRIMARY KEY (ID)
);

CREATE NONCLUSTERED INDEX IX_T_Comp_D
ON dbo.T (Comp, D)
WHERE D IS NOT NULL;

The computed column is only required to be PERSISTED in this case if you want to use a NOT NULL constraint or to reference the Comp column directly (instead of repeating its definition) in a CHECK constraint.

When I tried to perform the same actions, a got the another results.
Firstly, my execution plan for table without indexes looks as following:

As we can see from the Clustered Index Scan (t2), the predicate is used for determine the needed rows to be returned (because of condition):

When the index was added, not a matter if it was defined by WITH operator or no, the execution plan became as following:

As we can see, the Clustered Index Scan is replaced by Index Scan.
As we saw above, the SQL Server use the source columns of the computed column to perform the matching of the nested query. During the clustered index scan all this values can be acquired in the same time (no additional operations needed).
When the index was added, the filtering of the necessary rows from the table (in the main select) is performing according to the index, but the values of the source columns for the computed column comp still need to be gotten (last operation Nested Loop).

Because of this the Key Lookup operation is used - to get the data of the source columns of the computed one.

P.S. Looks like a bug in SQL Server.


SQL server calculated field value Not sure how to start describing this so I get straight to it.

I have a SQL Server 2012 database with a sample table Table_1 with the below (simplified) design:

id: int (PK, ID, NOT NULL)
name: varchar(100) (NOT NULL)
label: varchar(100) (NOT NULL)
description: varchar(max) (NOT NULL)

on my front end form user is asked for a name and description only.

What I want to achieve from the database is to, at the point of creating new record, get the value of the name field, convert it to lowercase and replace any spaces with underscores, and then insert the resulting string into the label column.

For example if user provided name "Sample Product", I want the label to be "sample_product".

How can I achieve this and what is the best approach: triggers or the calculated field property can do this?

Thanks in advance for any help or pointers.

In my experience is better a calculated field than a trigger because a trigger could generate performance issues in the future. Try this piece of code that uses a calculated field:

CREATE TABLE [dbo].[Table_1](
    [id] [int] NOT NULL,
    [name] [varchar](100) NOT NULL,
    [label]  AS (lower(replace([name],' ','_'))),
    [description] [varchar](max) NOT NULL,
CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

INSERT INTO [dbo].[Table_1]
([id]
,[name]
,[description])
VALUES
(1
,'Sample Product'
,'Sample Product Description')

GO

An AFTER INSERT TRIGGER should solve your problem

IF OBJECT_ID('dbo.TestTable', 'U') IS NOT NULL
  DROP TABLE dbo.TestTable;
go

create table TestTable (ID int, Name varchar(100), Label varchar(100), Description varchar(max))
go
create TRIGGER dbo.UpdateLabel ON dbo.TestTable
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE tt
    SET tt.label = replace(lower(i.NAME), ' ', '_')
    FROM dbo.TestTable tt
    JOIN inserted i ON i.id = tt.id
END
GO

INSERT INTO TestTable (ID, NAME, Label, Description)
VALUES (1,'JOHN SMITH',' ','TestDescription')

SELECT *
FROM testtable


Should I nest dependent outer joins in SQL Server? I have heard mixed information on this and am hoping for a canonical or expert opinion.

If I have multiple LEFT OUTER JOINs , each dependent on the last, is it better to nest them?

For a contrived example, the JOIN to MyParent depends on the JOIN to MyChild:
http://sqlfiddle.com/#!3/31022/5

SELECT
    {columns}
FROM
    MyGrandChild AS gc
LEFT OUTER JOIN
    MyChild AS c
        ON c.[Id] = gc.[ParentId]
LEFT OUTER JOIN
    MyParent AS p
        ON p.[id] = c.[ParentId]

Compared to http://sqlfiddle.com/#!3/31022/7

SELECT
    {columns}
FROM
    MyGrandChild AS gc
LEFT OUTER JOIN
    (
    MyChild AS c
    LEFT OUTER JOIN
        MyParent AS p
            ON p.[id] = c.[ParentId]
    )
    ON c.[Id] = gc.[ParentId]

As shown above these produce different query plans in SS2k8

This is absolutely not a canonical answer but I noticed that for the nested loops query plans shown in the SQL Fiddle it was possible to apply the plan from Query 2 to Query 1 with the use of the USE PLAN hint but attempting the reverse operation fails with

  Query processor could not produce query plan because USE PLAN hint
  contains plan that could not be verified to be legal for query. Remove
  or replace USE PLAN hint. For best likelihood of successful plan
  forcing, verify that the plan provided in the USE PLAN hint is one
  generated automatically by SQL Server for the same query.

Disabling the optimizer transformation rule ReorderLOJN prevents the previously successful plan hint from succeeding too.

Experimenting with greater quantities of data shows that SQL Server is certainly capable of transforming (A LOJ B) LOJ C to A LOJ (B LOJ C) naturally as well but I didn't see any evidence that the reverse is true.

A very contrived case where the first query performs better than the second is

DROP TABLE  MyGrandChild , MyChild,  MyParent

CREATE TABLE MyParent
(Id int)

CREATE TABLE MyChild
(Id int PRIMARY KEY
,ParentId int,
Filler char(8000) NULL)

CREATE TABLE MyGrandChild
(Id int
,ParentId int)

INSERT INTO MyChild
                      (Id, ParentId)
SELECT TOP (100000) ROW_NUMBER() OVER (ORDER BY @@SPID),
                     ROW_NUMBER() OVER (ORDER BY @@SPID)
FROM master..spt_values  v1, master..spt_values

INSERT INTO MyGrandChild
                      (Id, ParentId)
OUTPUT INSERTED.Id INTO MyParent
SELECT TOP (3000) Id, Id AS ParentId
FROM MyChild
ORDER BY Id

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT gc.Id       AS gcId,
       gc.ParentId AS gcpId,
       c.Id        AS cId,
       c.ParentId  AS cpId,
       p.Id        AS pId
FROM   MyGrandChild AS gc
       LEFT OUTER JOIN MyChild AS c
         ON c.[Id] = gc.[ParentId]
       LEFT OUTER JOIN MyParent AS p
         ON p.[Id] = c.[ParentId]

SELECT gc.Id       AS gcId,
       gc.ParentId AS gcpId,
       c.Id        AS cId,
       c.ParentId  AS cpId,
       p.Id        AS pId
FROM   MyGrandChild AS gc
       LEFT OUTER JOIN( MyChild AS c
                        LEFT OUTER JOIN MyParent AS p
                          ON p.[Id] = c.[ParentId])
         ON c.[Id] = gc.[ParentId]

Which gives plans

For me Query 1 had an elapsed time of 108 ms vs 1,163 ms for Query 2.

Query 1

Table 'Worktable'. Scan count 0, logical reads 0
Table 'MyChild'. Scan count 0, logical reads 9196
Table 'MyGrandChild'. Scan count 1, logical reads 7
Table 'MyParent'. Scan count 1, logical reads 5

Query 2

Table 'MyParent'. Scan count 1, logical reads 15000
Table 'MyChild'. Scan count 0, logical reads 9000
Table 'MyGrandChild'. Scan count 1, logical reads 7

So it might be provisionally assumed that the first ("unnested") syntax is potentially beneficial as it allows more potential join orders to be considered but I haven't done exhaustive enough testing to have much confidence in this as a general rule.

It may well be entirely possible to come up with counter examples where Query 2 performs better. Try both and look at the execution plans.

there is no such JOIN Type called "Nested Join". it is a another variation of writing the JOIN may be for readability convenience purpose. you can see them as a "Sub Queries" for understanding purpose only.

if you are more concern about code readability then my take is, it's individual's choice what they are conferrable with.

And if you are concern with performance of the query, and "Force JOIN ORDER" hint is not used in the query, then it doesn't matter if query is written with "Nested Join" or All "Outer Join". SQL server come up with the order based on cost of joining two tables and/or result.
SQL Server does the JOIN between two sets of data at a time only.

in fact, imagine that in the second way "nested join" if SQL server decides to do the second part, "MyChild AS c LEFT OUTER JOIN MyParent AS p ON p.[id] = c.[ParentId]" and those table happen to have rows that are going to discard in NEXT LEFT JOIN. in that case SQL server has spent unnecessary resources on doing the OUTER JOIN these two and passing that result to next JOIN.

you can also look similar question asked and answered appropriately here.
Understanding 'Nested join' syntax


MySQL, IF statement, variables, and NULL After struggling for too long with something that should have been simple, I had a question to ask, but while writing it, I kept looking for solutions and I finally found the solution. I am posting it here anyway in case someone else has the same problem I had.

Description: get max value of a field from database. If that value is null, make it 1, otherwise increase it by one. Insert new record with that value.

Code I was trying:

select @nextNumber := max(number)+1 from test;
if @nextNumber == NULL then @nextNumber := 1;
insert into test (number) values (@nextNumber);

Turns out, the if statement can't stand on its own, at least not here.

Will put working code as an answer.

INSERT INTO test (number)
SELECT COALESCE(MAX(number), 1)
FROM test;

UPDATE

If record(s) exists we need in the next value. So:

INSERT INTO test (number)
SELECT 1 + COALESCE(MAX(number), 0)
FROM test;

The code that works:

select @nextNumber := max(number) from test;
select @nextNumber := IF(@nextNumber IS NULL, 1, @nextNumber+1);
insert into test (number) values (@nextNumber);

This inserts 1 the first time, then 2, then 3, etc. Exactly what I want.


What normal form does a surrogate key violate? I have following question:

"What normal form does a surrogate key violate?"

My thought was the 3rd normal form, but I'm not quite sure it's just an assumption i am making. Could someone explain that to me?

Arguably, it doesn't.

Adding a surrogate key is an implementation decision (to respect how the RDBMS works) taken at implementation time. During modelling and normalisation, you should end up with BCNF (slightly stricter and more correct 3NF) without surrogate keys

That is, introducing surrogate keys at the start of the design process is wrong. Even though we all do it...

They violate 3NF.

That's OK when you have a normal model and decide to denormalize here and there judiciously. But not when you model with surrogates to begin with.

How do you know you are violating 3NF ?

A quote from the book "Using SQLite", Jay A. Kreibich, O'Reilly

  The Third Normal Form, or 3NF, extends the 2NF to eliminate transitive
  key depend- encies. A transitive dependency is when A depends on B,
  and B depends on C, and therefore A depends on C. 3NF requires that
  each nonprimary key column has a direct (nontransitive) dependency on
  the primary key.

  ...

  A good way to recognize columns that may break 3NF is to look for
  pairs or sets of unrelated columns that need to be kept in sync with
  each other.

An autoincremental PK in an entity where no natural key exist is not a surrogate.
A surrogate is a autoincremental PK added to a table where a natural key existed. That said, both keys, the surrogate PK and the business key (the key users search) have to be syncronized because if the are not syncronized, the data is lost for the user. That's how you know you are violating 3NF.

One case of that is a problem the bitcoin plataform has where and attacker de-synced the surrogate key and the business key resulting users lost their bitcoins.

A quote from Greg Maxwell, core-developer or Bitcoin:

  ...to database experts: the real problem is that Mt
  Gox (and other exchanges) are using a surrogate transaction id rather
  than a natural key in their databases: "The flaw isn't so much in
  Bitcoin as it is in exchange-systems. Many exchanges use the tx-id to
  uniquely identify transactions, but as it turns out, an attacker can
  change the tx-id without changing the actual transaction, rebroadcast
  the changed transaction (effectively creating a double-spend) and if
  his altered transaction gets accepted into a block instead of the
  legit transaction, the attacker receives his coins and can complain
  with the exchange that he didn't. The exchange will then check their
  db, fetch the tx-id from it, look it up in the blockchain and not find
  it. So they could conclude that the transaction indeed failed and
  credit the account with the coins. ... A simple workaround is to not
  use the tx-id to identify transactions on the exchange side, but the
  (amount, address, timestamp) instead."

Quote from here and here

So ask yourself this:

does this table has single non PK attribute that if changed arbitrarily would prevent the user from retrieving the data ?

If that's so, you are violating 3NF.

But... sometimes one has to denormalize ( violate normal forms knowingly ) for performance or simplicity's sake. For example when a composite natural key is too long ( exceeds three columns according to PDMs design principles ) and you need to create a FK to that table.


Finding missing records from two tables I am trying to compare total number of records for a particular table in two databases. They are from the same database, but both the database are running in two different servers.

Table structure is same on both the tables , but no of records varies on both the tables.

I want to find out the missing records from both these tables.

mysql> select count(*) from dlfilerank;
+----------+
| count(*) |
+----------+
|    38259 |
+----------+
1 row in set (0.02 sec)

mysql> select count(*) from dlfilerank;
+----------+
| count(*) |
+----------+
|    38359 |
+----------+
1 row in set (0.02 sec)

So, dump both tables into two files and then see the difference with diff:

 mysql -h host1 -NB -e "SELECT * FROM db.table ORDER BY id" > t1
 mysql -h host2 -NB -e "SELECT * FROM db.table ORDER BY id" > t2

 diff -u t1 t2

SELECT TABLE_NAME
FROM information_schema.tables
WHERE table_schema = 'db1'
AND `TABLE_NAME` NOT IN  (
    SELECT TABLE_NAME
    FROM information_schema.tables
    WHERE table_schema = 'db2');


How can I improve my table design for different types of an entity? Consider an accounting system as an example. I have an Entity called Client. Client can be of different types, with different fields applicable to different types. I consider creating separate tables for different types of Client, each having fields applicable to the respective type and have one master table referencing all of them and have fields applicable to all types.

Currently, I come up with the following design:

But I don't think my design is efficient enough (or even correct and free of errors). What would you suggest?
Also, if this is important in any way, I am planning to utilize MariaDB.

Sorry, but is an entrepreneur not also an individual? You should record the name etc. in individual, then the additional info in another table. Now you may have one person double. And stuff like address should be linked to "entity" - not even client, you may end up having suppliers there too.

The "Data Model Resource Book" has perfect diagrams for address and contact handling including relationships of entities with each other. There is a lot of detail issues you get seriously wrong in your approach.

I've designed a system like this before in MySQL. I was tracking friends across multiple social networks.  What I think is best for you (works great for me) is to track clients by an enum and their Id. Since MySQL can't do dual foreign key constraints, you have to maintain this relationship in your application.   The client_type_id and client_type should be removed and replaced with a simple enum. This is more efficient


Any way to track who has been executing what queries? who has been on my lawn? I have a database in which most of tables are readable for users in one particular security group.

I know most of the users are from my team, but lately I found that it's been used by more and more people, and most of execution occurs from Excel and/or Access.

Out of curiosity, is there any way for me to track who has been tapping into the SQL server and what query they have been running?

Queries aren't logged by SQL Server, unless they meet some criteria that puts them into the default trace or system_health Extended Events session (some details on what they collect here).

If you can control data access via stored procedures, you can easily add your own logging to those. But that doesn't sound like the case. Otherwise you will have to rely on triggers (for DML, but not SELECT), server-side trace, extended events, auditing, or 3rd party monitoring tools.

You can setup the sql profiler and start recording all your queries into a table. Filter the table for the host and see which host, and possibly which users are doing this.


Converting string to date with timezone adjustment I am attempting to convert stirngs to dates in mysql

Thu Oct 23 16:46:47 2014 +02:00

Im unsure how to handle the +02:00, The rest is simple enough

 update logs
 set date = str_to_date(date_raw, '%a %b %e %T %Y')

This returns

Truncated incorrect datetime value: 'Thu Oct 23 16:46:47 2014 +02:00'

Any ideas?

How about a stored function?

DELIMITER $$

DROP FUNCTION IF EXISTS `ts_from_offset` $$
CREATE FUNCTION `ts_from_offset`(in_ts TINYTEXT) RETURNS datetime
NO SQL
DETERMINISTIC
BEGIN

-- Thu Oct 23 16:46:47 2014 +02:00

-- this function takes an input timestamp value with an offset formatted as above,
-- and converts it to the equivalent MySQL datetime value, expressed in the current session's
-- time zone.  Since this is also the timezone that columns in the TIMESTAMP data type expect,
-- this causes the input value to be stored correctly in the native TIMESTAMP format, which is.
-- UTC under the hood.

-- if you are taking the value here and stuffing it into a non-UTC DATETIME column, you need to have
-- session @@time_zone set to the same zone in which that column should be stored, or use
-- CONVERT(ts_from_offset('input value'),'UTC','Your Desired Time Zone');

-- http://dba.stackexchange.com/questions/83898/converting-string-to-date-with-timezone-adjustment/84041#84041

DECLARE offset_string TINYTEXT DEFAULT NULL;
DECLARE date_string TINYTEXT DEFAULT NULL;
DECLARE offset_sign TINYINT DEFAULT NULL;
DECLARE offset_hours TINYINT DEFAULT NULL;
DECLARE offset_minutes TINYINT DEFAULT NULL;

SET offset_string = SUBSTRING_INDEX(in_ts,' ',-1);

SET in_ts = LEFT(in_ts, LENGTH(in_ts) - 1 - LENGTH(offset_string));

SET offset_sign = IF(SUBSTRING(offset_string FROM 1 FOR 1) = '+', -1, +1); # we need to flip the sign, to "back out" the offset to get a time in UTC
SET offset_hours = CAST(SUBSTRING(offset_string FROM 2 FOR 2) AS SIGNED) * offset_sign;
SET offset_minutes = CAST(SUBSTRING(offset_string FROM 5 FOR 2) AS SIGNED) * offset_sign;
RETURN CONVERT_TZ(DATE_ADD(DATE_ADD(STR_TO_DATE(in_ts,'%a %b %e %T %Y'), INTERVAL offset_hours HOUR), INTERVAL offset_minutes MINUTE),'UTC',@@time_zone);

END $$

DELIMITER ;

Example output...

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

mysql> SELECT ts_from_offset('Thu Oct 23 16:46:47 2014 +02:00');
+---------------------------------------------------+
| ts_from_offset('Thu Oct 23 16:46:47 2014 +02:00') |
+---------------------------------------------------+
| 2014-10-23 14:46:47                               |
+---------------------------------------------------+
1 row in set (0.00 sec)

No warranty, but it seems like this should do the trick.

Assuming v5.6 (and I don't think this has changed), this is probably what you need to know (split up a bit, MySQL's docs tend to write HUGE blocks of text):
http://dev.mysql.com/doc/refman/5.6/en/datetime.html

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.)
By default, the current time zone for each connection is the server's time. The time zone can be set on a per-connection basis.
As long as the time zone setting remains constant, you get back the same value you store. If you store a TIMESTAMP value, and then change the time zone and retrieve the value, the retrieved value is different from the value you stored. This occurs because the same time zone was not used for conversion in both directions.
The current time zone is available as the value of the time_zone system variable.

So, if you store a value (1023) when your session is set to UTC, and retrieve it from a session set to EST, you should get back the same value with -0500 (0523)


Enforcing minimum cardinality in One To many design? If we have the following One to Many database design.

Team

+------+-----------+
| t_id | team_name |
+------+-----------+
| 100  |  germany  |
+------+-----------+

FootballPlayers

+--------+------+-------------+--------+
|  p_id  | t_id | player_name | number |
+--------+------+-------------+--------+
|    1   |  100 |   T Muller  |   11   |
|    2   |  100 |   T Peters  |   8    |
|    1   |  100 |    Messi    |   2    |
|    1   |  100 |   J Cole    |   10   |
+--------+------+-------------+--------+

If a Team must have a minimum of 11 FootballPlayers, how can we enforce this within the database design?

Could I use Triggers? Perhaps One To Many is wrong? ...

Declare a constraint on Players, something like

check (10 < (select count(*) from p_id where t_id = @t_id))

and ensure that your application inserts at least 11 players the first time.

It's not clear this is a good idea, though.  It's one thing to say every player belongs to a team.  It's something else to say all teams have 11 players.  In the first case, you're enforcing referential integrity: what would teamless player be?  (Well, you might have worked with him.  I have.)  In the second case, you're imposing a business rule: does a team cease to exist if player #11 quits?  Is it the case that when player #11 quits, the whole team must be deleted?

There might be such a rule, but it seems more likely (from this distance) that the team continues to exist but becomes ineligible to play.  In that case you might want a view

create view EligibleTeams as
select * from Teams as t where 10 <
    (select count (t_id) from Players where t_id = t.t_id)

Also, is it clear you need t_id and p_id?  Not to me.  I think your keys are the names of the players and the teams.  Definitely you don't want two Ravens or whatever, and the odds that you'll have two players named Roger Staubach on one team are longer than the Cowboys winning the World Series.


SQL server Login failed for user My MS Access Application uses SQL Database as backend.
The permission to SQL database is granted with Windows Authentication mode to a specific user group.
Most of users in the group can get access to SQL database but there are some users reported to fail for login in the database.
The error message:
Connection failed
SQLstate: '28000'
SQL Server Error: 18456
Login failed for user Domain\UserID

I spent sometime searching for the solution but I could not find one with the same problem.
Could anyone give me an advice on this?
Many thanks.

To fix it you need a State of error message - explanation why it failed. It could be found in error message or SQL Error log if failed logins added
Login failed for user - Error 18456 - Severity 14, State 38

If the users are indeed in the group, can you try to delete and readd the group to SQL Server login.


Using third party tools to backup Is there any advantages to use Third Party Tools to make Backups ?

I'm not referring one in particular but I want to know if someone using them to DB greater than 200 GB. Servers are mostly MSSQL 2008R2, Standard and Enterprise Editions.

This is just my input, but I have supported most of the major players in the 3rd party backup vendors. I have supported clients that utilize Idera, RedGate, Dell LiteSpeed, NetBackup (which does not really have compression but is 3rd party), and a few off-the-wall vendors that I can't remember anymore.

The improved compression you may or may not get with 3rd party products have a trade-off....CPU workload. I have seen Dell's (LiteSpeed) product run in SQL Server 2014 environment against a database that is right at 500GB, and using high compression kills the CPU. The result was only a backup file of around 64GB. It ended up causing SQL Server to stop providing resources to the backups since Dell's product uses the VDI API. The compression level had to be dropped down from 7 to about 3, this brought the backup size down to about 82GB. We took a native backup with compression enabled of same database, and it was right at 84GB in size. So is the trade off and cost worth it? test, test, test

I seem to recall a client I had a few years ago was using Idera's product and I had constant issues with the backups for their medical databases that were 1TB+ in size. I think some of it had to do with the disk subsystem the backups were being written to, which again is why you need to test. Overall Idera's GUI interface is not inviting to me, and could use great improvement. I would chose RedGate or Dell over Idera any day of the week.

You also have to remember that most 3rd party backup products are going to use the VDI API to SQL Server. This in and of itself can cause problems as well, frequent to often "BackupVirtualDevice" or "BackupIORequest" errors showing up in the error log. I have some of these be false positives (backup actually did occur) and some where legit failures. Make sure to read through the vendor support article or forums. RedGate and Idera have very good support systems in place and are very good about getting back with help. Dell I have not had to make any support calls with their product so far.

The one last thing I will bring up is the format of the backup taken by the 3rd party product. If you get into a DR situation are you going to have a standby server licensed with the product as well? I believe Dell allows you to install LiteSpeed without a license to do a restore, but it has to be licensed to do any backups. I think most of the vendors also include a utility to convert a vendor specific database to a SQL Server native backup file, in case of emergency.

Database corruption is one of the nightmares in the life of every SQL Server user. There is no perfect way to keep your data safe and sound, but the best thing you can do to protect your database is making scheduled backups.

Of course, if you administer database greater than 200GB to make backups by using T-SQL command is not very convenient. For such cases, it is better to use third party tools. I have some experience with using third party tools and I'm really happy that nowadays we have such software that make the life of DBA easier.

Today market is full of such third party tools, you can find different software with different price and features, for example, Idera, RedGate, and Quest LiteSpeed.

Here I want to add some benefits of using third party tools:

You can schedule database backups
You can send backups to FTP, local or network folder, Dropbox, Amazon S3, Google Drive,....
You can backup different files
You can receive email confirmation
You can protect your backup by encryption

Anyway, third party tools for making a backup is really useful. They will help you to minimize the risk of losing data.


Scheduled Job Succeeds, Does Nothing; Manual Job Succeeds, Performs Requirements I have a job in SQL Server Agent (2012 Instance) that deletes rows from a table.  When I manually run the job, it succeeds without issue and in reviewing the table, does exactly what I want it to do.

When I've set it to an automatic schedule, the history indicates that the job has successfully run each step, yet in reviewing the table, the records are not deleted.  The job is set to enabled, and the history shows that it's run on the schedule, even with no records being removed.  If I run it manually, suddenly I see the correct results in the table.

Each step is run under the same login, for instance EXECUTED as user: NT ACCOUNT\SQLSRVAGENT, whether I run the job manually or it's run on its schedule.  The only difference I see between running it manually or the automated schedule is what invokes the job.  When it's the schedule, it reports The Job was invoked by Schedule 1 (Name) vs. when I run it manually, it reports, The job was invoked by User YouUser.  Note that this standard setup exists on other servers and doesn't create the same issue, so even though this is the only discrepancy I can find between how the job is run (manual vs. schedule), this doesn't look to be the issue.

Any other idea what could be causing this?

I have faced the same problem, It is a login problem, please check on your login user name and password.


Select roads that are between FROM and TO I have a road table:

 create table road
   (road_id int,
    name_ nvarchar2(15),
    from_ nvarchar2(15),
    to_ nvarchar2(15),
    sort int
   );
insert into road (road_id,name_,from_,to_,sort) values (4,'Street A','Street 1','Street 2','1');
insert into road (road_id,name_,from_,to_,sort) values (6,'Street A','Street 2','Street 3','2');
insert into road (road_id,name_,from_,to_,sort) values (5,'Street A','Street 3','Street 4','3');
insert into road (road_id,name_,from_,to_,sort) values (3,'Street B','Street VI','Street VII','600');
insert into road (road_id,name_,from_,to_,sort) values (7,'Street B','Street VII','Street VIII','700');
insert into road (road_id,name_,from_,to_,sort) values (1,'Street B','Street VIII','Street IX','800');
insert into road (road_id,name_,from_,to_,sort) values (2,'Street B','Street I','Street II','100');
insert into road (road_id,name_,from_,to_,sort) values (9,'Street B','Street II','Street III','200');
insert into road (road_id,name_,from_,to_,sort) values (11,'Street B','Street III','Street IV','300');
insert into road (road_id,name_,from_,to_,sort) values (8,'Street C','Street X','Street Y','9');
insert into road (road_id,name_,from_,to_,sort) values (10,'Street C','Street Y','Street Z','10');
commit;
select * from road order by name_, sort;

   ROAD_ID NAME_           FROM_           TO_                   SORT
---------- --------------- --------------- --------------- ----------
         4 Street A        Street 1        Street 2                 1
         6 Street A        Street 2        Street 3                 2
         5 Street A        Street 3        Street 4                 3

         2 Street B        Street I        Street II              100
         9 Street B        Street II       Street III             200
        11 Street B        Street III      Street IV              300

    {Gap in Street B}

         3 Street B        Street VI       Street VII             600
         7 Street B        Street VII      Street VIII            700
         1 Street B        Street VIII     Street IX              800

         8 Street C        Street X        Street Y                 9
        10 Street C        Street Y        Street Z                10

Eventually, I would like to make a tool that allows users to select roads between a from_ and a to_ (the values would be entered in parameters).

To do this, I think I need to make a query that can do something like:

Select roads where the name_ is Street B, between Street II and Street VIII (inclusive).

   ROAD_ID NAME_           FROM_           TO_                   SORT
---------- --------------- --------------- --------------- ----------
         9 Street B        Street II       Street III             200
        11 Street B        Street III      Street IV              300
         3 Street B        Street VI       Street VII             600
         7 Street B        Street VII      Street VIII            700

How can I do this?

select *
from road
connect by (prior to_ = from_ and from_ != 'Street V')
start with name_ = 'Street B' and from_ = 'Street II'
;

   ROAD_ID NAME_      FROM_      TO_              SORT
---------- ---------- ---------- ---------- ----------
         5 Street B   Street II  Street III        200
         6 Street B   Street III Street IV         300
         7 Street B   Street IV  Street V          400

Answer for the updated question:

select road_id, name_, from_, to_, sort
from
(
  select road_id, name_, from_, to_, sort,
  row_number() over (order by sort) as rn from road
  where name_ = 'Street B'
)
connect by (prior rn + 1 = rn and from_ != 'Street VIII')
start with from_ = 'Street II'
;

   ROAD_ID NAME_           FROM_           TO_                   SORT
---------- --------------- --------------- --------------- ----------
         9 Street B        Street II       Street III             200
        11 Street B        Street III      Street IV              300
         3 Street B        Street VI       Street VII             600
         7 Street B        Street VII      Street VIII            700

This works, but I imagine there is a more elegant way to do it.

SELECT
    road_id
FROM
    road
WHERE
    name_= [Enter a street name:]
    AND sort Between
                (select
                    sort
                from
                    road
                where
                    name_ = [Enter a street name:]
                    and from_ = [From:])
                and
                (select
                    sort
                from
                    road
                where
                    name_ = [Enter a street name:]
                    and to_ = [To:]);

It's an local MS Access query with parameters.


SQL SERVER 2016 high CPU I have a brand new server with SQL Server 2016.

It has 24 CPU, about 80go of RAM.

The thing is, sometimes, the CPU gets pretty high (> 70%) with no specific reason at all.

If I look at the 'execute sp_WhoIsActive @get_locks=1' I get about 40 queries running, but without lock, and some of them for more than 30 seconds instead of a few ms.

This append about once a day in production. The only fix I've got so far is changing the option "cost threshold for parallelism" from 90 to 89 and from 89 to 90. Changing this value actually fix the issue in less than 10 seconds and I get a CPU charge getting lower and my user happier.

Do you guys have any idea about the root of this issue? I've thought about plan cache but I don't know what to do with that idea...

Edit : I've disable Intel HyperThreading but the issue is still the same. I've put a task cleaning the cache every hour. Do you have any idea ?

Edit 2 : I have open an issu on Microsoft Support. Apparently there is an issue with page rentention solved with a reboot of SQL Server 2016. I'll let you know ASAP if I have a "real" fixe for this.

Please verify the settings for MAXDOP. Please review suggestions by Microsoft. In most cases the optimal value is the number of dedicated cores, that are available to SQL Server. But maximum value is set to '8'. In some cases that could vary and needs to be tested well to set to higher values.

I assume, that you have two six-core Processors with Hyper-Threating enabled. It is possible, that your queries don't perform very well, with Hyper-Threating enabled.
In some cases the SQL Server Performance is better using 12 'real' cores, than 24 combined with Hyper-Threating.

You should try to disable Hyper-Threating and verify the new settings.

Unfortunately you wrote, that this is your production environment. Are you able to verify settings on similar hardware and similar settings?

Furthermore you should have a look on your I/O subsystem. Are there more than one tempdb?

Additional (I can not comment another comment):

DBCC FREEPROCCACHE will effect all user.
Try using with option recompile at the SQL Query that effects the Performance issue most.


Do I need to reindex my table in mysql after delete? I have a table in mysql with a lot of INSERT and SELECT at every second. And there is a bulk delete of some older data once in a day. Do I need to reindex the table after delete? I want to increase the performance. Can anybody suggest some tips? Using 'innodb' as engine. Do I need to change it? I think its better for concurrent insert and select. pls give your suggestions. Do I need reindexing?

Thanks in advance..

When there is a lot on insert and delete operations on a table, the table will be "fragmented", and the statistics that are used to decide the execution plan of a query will not be accurate. So YES, it is recommended to optimize the table from time to time (Not necessarily after each delete, but when a significant changes happen)
hth

If there is no update and you are deleting old data, you might think of creating daily tables with date on its name. You can create a view to query them and you can easily drop the old table.


Optimize UNION query in MySQL I have a problem with a UNION query in MySQL. We have 10 millions players on our website and we would like to select players with a multi-criterias system. For exemple, selecting US people, men, more than 35 years of age.

We are using "vertical partionning": 1 table per criter. For example:

* user_country
- id_user
- id_country

We would like to do this kind of query:

SELECT id_inscri FROM userdata_langue
WHERE id_langue='43'
  UNION
SELECT id_inscri FROM userdata_sexe
WHERE sexe='2'
  UNION
SELECT id_inscri FROM userdata_nb_jour
WHERE nb_jour>='31'
  UNION
SELECT id_inscri FROM userdata_last
WHERE last<='2013-04-07'
  AND last>='2013-04-03' ;

How do I optimize that?

----- More details

Explain output of the query:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   PRIMARY userdata_langue ref id_langue   id_langue   1   const       398846  Using index
2   UNION   userdata_sexe   ref sexe    sexe    1   const   1667137 Using index
3   UNION   userdata_nb_jour    range   nb_jour nb_jour 2   NULL    5830    Using where; Using index
4   UNION   userdata_last   range   last    last    3   NULL    371614  Using where; Using index
NULL    UNION RESULT    <union1,2,3,4>  ALL NULL    NULL    NULL    NULL    NULL

SHOW CREATE TABLE

Table   Create Table
userdata_langue CREATE TABLE `userdata_langue` (
 `id_inscri` bigint(20) NOT NULL,
 `id_langue` tinyint(3) unsigned NOT NULL,
 PRIMARY KEY (`id_inscri`),
 KEY `id_langue` (`id_langue`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

From the EXPLAIN output, it looks like first table userdata_langue would benefit from an index that contains both id_langue and id_inscri. If there is already an index on that combination, try forcing it.

The second table userdata_sexe could be partitioned on sexe (I assume there are only two options here?). This would speed up the scan required to fetch all those.

I am not sure if mySQL would benefit from moving the order of the UNION DISTINCT components around. It might be worth trying to move component 3 and 4 up to the top of the query.

Finally, consider if you can afford to increase the sort buffer size in InnoDb (see: http://www.mysqlperformanceblog.com/2010/10/25/impact-of-the-sort-buffer-size-in-mysql/) to make sure you can hold everything in memory while mySQL calculates the UNION DISTINCT.

Consider using UNION ALL. It should be significantly faster than UNION, but it will return duplicate rows for players that answers to multiple criteria.

If you can deal with duplicates in your application code, it can be a good way to optimize your query.