Can/Should rarely used columns be placed in a different table for performance? I have a table that contains data (blobs). Data is often read, rarely inserted, and never modified.

For all of this data, I compute hashes that I also store in the DB. These hashes are only checked when inserting new data, therefore, they are hardly used in normal, day-to-day routine.

My question is, is it worth the trouble to make a separate table for the hashes, is it reasonable to expect any performance gain for putting them aside? Or can I confidently just add an extra column to the table for the hashes, even though the hashes are rarely used?

(Although I would be interested in a general answer, I must point out that I use MariaDB and sqlite).

(re: MySQL/MariaDB)  It depends.

For small columns, say under 1KB, don't bother with the hash.
For large columns, say over 7KB, it may not matter -- InnoDB puts large columns "off-record" in a block separate from the main part of the record.
For medium-sized columns, say 1KB-7KB, the column is likely to be kept "on record", thereby making any action on the row fetch the bulky column, too.  This is even if you don't say SELECT *.
Regardless of what you do, avoid using SELECT *; instead, spell out the columns you need.  (* will bite you if you ever add a column to the table.)
Pack the hash into BINARY(16) for MD5, etc., rather than a VARCHAR of twice the size.
The time taken to fetch a row is more than unpacking the columns.  That is, "how many columns" is not a metric to worry about.  (No, I don't have numbers to back that up; just decades of experience.)

The hash (whether md5, sha1, etc) is a bigger nuisance.  It is very 'random', thereby leading to cache misses when inserting a new hash or looking up by the hash.  This issue comes into play when the BTree with the hash is bigger than can be cached in the buffer_pool.  To phrase it another way, use of a hash on a billion-row table will slow down all accesses to the speed of the disk.  And it will slow down other processes due to cache misses.

InnoDB blocks are 16KB; the underlying OS or drive block size does not factor into this discussion.

In the opposite direction, consider putting frequently updated columns (clicks, Likes, views) in a separate table.  (But that's another Q&A.)


MySQL slave not able to execute long running update query I am running a Master-slave replication servers of MySQL. As part of clean up of old data, i ran a delete query which deletes huge number of records from the database. It ran fine on Master server but on slave server, it is giving me following error:

  Slave SQL thread retried transaction 10 time(s) in vain, giving up. Consider raising the value of the slave_transaction_retries variable.

The slave server machine is less powerful than master one. How can I get past this?

The query is single line delete query. I am running MySQL 5.6.

You need to stop replication, make the Slave have the same specs as the Master, then start replication.

Make sure the Slave has no incoming connections. Otherwise, that will make the SQL thread on the Slave compete with incoming connections that are running SELECT queries against the same table you are running DELETE.

If you cannot reroute the incoming connections, you will have to rerun the DELETE in chunks (perhaps 5000 rows at a time) on the Slave locally.

As a last resort, rebuild the Slave (after scaling up the Slave's hardware and configs).


Clustered Index 'Seek predicate' and 'predicate' on the same column I have a clustered index primary key column and I'm doing a range query on it.
The problem is the scan only uses the first range part for the seek predicate, leaving the other side of the range as a residual predicate.
which causes to read all the rows up to the @upper limit

I'm using two parameters for the range:

declare
    @lower numeric(18,0) = 1000,
    @upper numeric(18,0) = 1005;

select * from messages
where msg_id between @lower+1 and @upper;

In that case the actual execution plan shows:

predicate: messages.msg_id >= lower
seek predicate: messages.msg_id < @upper
rows read: 1005

Table definition (Simplified):

CREATE TABLE [dbo].[messages](
    [msg_id] [numeric](18, 0) IDENTITY(0,1) NOT NULL,
    [col2] [varchar](32) NOT NULL,
 CONSTRAINT [PK_Message] PRIMARY KEY CLUSTERED
(
    [msg_id] ASC
)

More information

When used with constants instead of variables both of the predicates are 'Seek'
Have tried Option (Optimize for (@lower=1000)), without success

Starting with your original query:

declare
    @lower numeric(18,0) = 1000,
    @upper numeric(18,0) = 1005;

select * from [messages]
where msg_id between @lower+1 and @upper;

The 1 that you added has a data type of integer by default. When adding an integer value to a numeric(18,0) value SQL Server applies the rules of data type precedence. int has a lower precedence so it gets converted to a numeric(1,0). Your query is equivalent to the following:

declare
    @lower numeric(18,0) = 1000,
    @upper numeric(18,0) = 1005;

select * from [messages]
where msg_id between @lower+CAST(1 AS NUMERIC(1, 0)) and @upper;

A different set of rules around Precision, scale, and Length is applied to determine the data type of the expression involving @lower. It isn't safe to just use NUMERIC(18,0) because that could be overflowed (consider 999,999,999,999,999,999 and 1 as an example). The rule that applies here is:

╔═══════════╦═════════════════════════════════════╦════════════════╗
║ Operation ║          Result precision           ║ Result scale * ║
╠═══════════╬═════════════════════════════════════╬════════════════╣
║ e1 + e2   ║ max(s1, s2) + max(p1-s1, p2-s2) + 1 ║ max(s1, s2)    ║
╚═══════════╩═════════════════════════════════════╩════════════════╝

For your expression, the resulting precision is:

max(0, 0) + max(18 - 0, 1 - 0) + 1 = 0 + 18 + 1 = 19

and the resulting scale is 0. You can verify this by running the following code in SQL Server:

declare
@lower numeric(18,0) = 1000,
@upper numeric(18,0) = 1005;

SELECT
  SQL_VARIANT_PROPERTY(@lower+1, 'BaseType') lower_exp_BaseType
, SQL_VARIANT_PROPERTY(@lower+1, 'Precision') lower_exp_Precision
, SQL_VARIANT_PROPERTY(@lower+1, 'Scale') lower_exp_Scale;

This means that your original query is equivalent to the following:

declare
    @lower numeric(19,0) = 1000 + 1,
    @upper numeric(18,0) = 1005;

select * from [messages]
where msg_id between @lower and @upper;

SQL Server can only use @lower to do a clustered index seek if the value can be implicitly converted to NUMERIC(18, 0). It is not safe to convert a NUMERIC(19,0) value to NUMERIC(18,0). As a result the value is applied as a predicate instead of as a seek predicate. One workaround is to do the following:

declare
    @lower numeric(18,0) = 1000,
    @upper numeric(18,0) = 1005;

select * from [messages]
where msg_id between TRY_CAST(@lower+1 AS NUMERIC(18,0)) and @upper;

That query can process both filters as seek predicates:

My advice is to change the data type in the table to BIGINT if possible. BIGINT requires one fewer byte than NUMERIC(18,0) and benefits from performance optimizations not available to NUMERIC(18,0) including better support for bitmap filters.


Old Query methods success() purge() In the Project was used old version of mysql++, recently it was updated.
After compilation I got some error messages:
«class mysqlpp::Query» has no member named «success»
«class mysqlpp::StoreQueryResult» has no member named «purge»
Does anyone know what should be used instead this ?

I found this:

  Removed several “smelly” methods:

  purge(): was an internal implementation detail, not something for end user code to call

  Removed success() in Connection, Query

from
Incompatible Library Changes

But I have not idea how to replace it.


Can I use Backup/Restore to clone a database? Running out of ideas I need to clone a database in a single instance of SQL Server 2012.

I have tried the Copy Database tool in SSMS, but it fails with file access error of which I've spent all day reading around to no avail.  I'm running out of time, and I need to get this database cloned, ensuring that the clone is entirely independent of the original.

The source database, DB1 needs to retain much of its data, so Generate Scripts is also no good.

Also my version of SQL does not have the Schema Compare tool.

Can I do a full backup, but then restore to a different database name?  I tried it on a dev machine, but some aspect such as it retaining the original logical name concern me (I might not need to be concerned - please advise!).  Or are there any other ways to do this?

Yes, backup & restore is a perfectly reasonable way to copy a database.  You will need to use the MOVE option to re-name the files.  Here is an example using a backup of a database called DBA and restoring it to DBA_Test

RESTORE DATABASE [DBA_Test]
FROM  DISK = N'DBA_backup_2015_01_11_001025_4030475.bak'
WITH  FILE = 1,
MOVE N'DBA' TO N'D:\DATA\DBA_test.mdf',
MOVE N'DBA_log' TO N'E:\LOG\DBA_test_log.ldf',
NOUNLOAD,  STATS = 5

Kin's concern in the link mentioned by @JohnM is valid.  You will be creating an additional database on the instance that will be competing for resources (depending on it's usage) with your other databases.  However if you are doing this to create another production database that you need anyway this isn't really a big deal.

I'd only be concerned if you are using it to create a test environment on a production server.  In that case you need to decide if the risks of bringing down your production database while working with your test environment outweigh the costs of another server/instance.  For example if you are testing and blow up your test transaction log you will still cause problems for your production database.

If you need a cheap environment remember the Express is free and if you need a full blown copy the Developer license is usually only around $50-60.  Of course that license is a single person license only, not for production use, but it is equivalent in functionality to a full Enterprise license.


Multiple parents and multiple children in product categories I am making a ecommerce site. In this site I want to categorise the items into three different layers

primary category             sub category           sub category

>>electronic             >>smart phone          samsung
  cameras                       tablets              nokia
                                laptop               apple
                                headphone

In the above table, I want to display the sub category after the customer selects the primary one. At the same time the 'samsung' also comes under 'camera'. Like this a parent can have any number of children and one child can have many parents. In the future we may change the primary and secondary.

What is the best solution for this?
Which model will adopt our category: tree or nested?

You should consider Postgres, as it does hierarchical querying better than Mysql. If using Mysql, consider using a closure table to aid querying.

I think you are actually confusing Brand and sub-sub-category. You should have a brand table too.

create table category (
  id serial primary key,
  name text not null,
  parent_id int null references category(id)
);

create table brand (
  id serial primary key,
  name text not null unique
);

create table brand_category (
  brand_id int not null references brand(id),
  category_id int not null references category(id),
  primary key (brand_id, category_id)
);

/* the product specification, not the actual product asset instance: */
create table product (
  id serial primary key,
  name text not null,
  brand_id int not null references brand(id)
);

create table product_category (
  product_id int not null references product(id),
  category_id int not null references category(id),
  primary key (product_id, category_id)
);

example:

/* null parent_id means top-level category */
insert into category values

(1, 'Electronics', null),
(2, 'Cameras', null),

(3, 'Smart Phones', 1),
(4, 'Tablets', 1),
(5, 'Laptops', 1),
(6, 'Headphones', 1);

insert into brand values
(1, 'Samsung'),
(2, 'Nokia'),
(3, 'Apple');

/* samsung brand in both smartphones and cameras: */
insert into brand_category values
(1, 3),
(1, 2),
(2, 3),
(3, 3);

insert into product values
(1, 'Samsung Model X', 1);

/* add samsung model x as a smart phone: */
insert into product_category values
(1, 3);


Slow MySQL query with Group by I have a query that is taking a long time to execute minutes and more. The two tables orders and tickets have around 150k records. This is the query:

SELECT
  orders.id,
  count(distinct tickets.id) as attached_tickets
from
  orders
  LEFT JOIN tickets ON tickets.order_id = orders.id
GROUP BY
  orders.id
LIMIT 1;

I have found that by removing the ORDER BY statement it speeds it up to seconds. These are the results from the EXPLAIN

+----+-------------+---------+------------+-------+---------------+---------+---------+------+--------+----------+----------------------------------------------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra                                              |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+--------+----------+----------------------------------------------------+
|  1 | SIMPLE      | orders  | NULL       | index | PRIMARY       | PRIMARY | 4       | NULL | 121291 |   100.00 | Using index; Using temporary; Using filesort       |
|  1 | SIMPLE      | tickets | NULL       | ALL   | NULL          | NULL    | NULL    | NULL |   7347 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+--------+----------+----------------------------------------------------+

And yes i have indexes in both tables by id. Is there any way I can speed up my query?

This will be a lot faster, but has flaws:

SELECT  id,
      ( SELECT  count(*)
            FROM  tickets
            WHERE  order_id = o.id
      ) as attached_tickets
    FROM  orders AS o
    LIMIT  1;

It will need INDEX(order_id) on tickets.

Which order.id do you want?  There is no ORDER BY to say which one LIMIT 1 will pick.
This may not generalize well.  What is your real query?


How to remove Date from output? I have a query which gives the output as shown below.

SELECT *,
       DATEADD(SECOND, ( DATEDIFF(SECOND, InTime, OutTime) ), 0) AS Total
FROM   TableXYZ

EmpName InTime  OutTime DayStart    NameOfMonth YearSet Total
ABC 12:11:32    21:53:24    Day1    November    2016    1/1/00 9:41
DEF 12:05:56    21:20:58    Day1    November    2016    1/1/00 9:15

I tried to remove date from Total but doesn't work. how can I do that and get the output below?

EmpName InTime  OutTime DayStart    NameOfMonth YearSet Total
ABC 12:11:32    21:53:24    Day1    November    2016    9:41
DEF 12:05:56    21:20:58    Day1    November    2016    9:15

As you are working with SQL Server 2014 you could cast/convert as a time (started in SQL 2008)

SELECT *,
cast(DATEADD(SECOND, ( DATEDIFF(SECOND, InTime, OutTime) ), 0) as time(0)) AS Total
FROM   TableXYZ


Mysql -Which Column table the index should be added I have written this query (which returns only 67 records, which is fine)

The problem is with the query time, the Query took 0.0042 seconds which is slow, I heard that indexes can help fasten the query, shall I add indexes on UserID or OfficeID?

SELECT Distinct(u.UserID),
       u.Role,
       u.FirstName,
       u.LastName,
       u.Email
FROM   Users as u,
       Hires as h,
       Contractors as c,
       ContractorCompanies as cc
where  (u.UserID = h.User_ID AND h.Offices_OfficeID = 3)
OR     (u.UserID = c.User_ID AND c.Offices_OfficeID = 3)
OR     (u.UserID = cc.User_ID AND cc.Offices_OfficeID = 3)

Updated: When I added an index on UserID the Query took 0.0066 seconds.)

There are several semantic issues with the structure of the query.

Using OR with conditions from various different tables, in combination with the Cartesian product of these 4 tables looks very weird. I find unlikely that this is what you actually want.
Using DISTINCT. That's probably needed only because of the weird structure mentioned above.

So, I suggest a few things:

Get rid of the DISTINCT
Rewrite the joins using proper JOIN .. ON syntax or - probably makes more sense in this case - rewrite using EXISTS subqueries which can be combined with OR.

The query becomes:

SELECT u.UserID,
       u.Role,
       u.FirstName,
       u.LastName,
       u.Email
FROM   Users AS u
WHERE  EXISTS
         ( SELECT *
           FROM Hires as h
           WHERE u.UserID = h.User_ID AND h.Offices_OfficeID = 3
         )
   OR  EXISTS
         ( SELECT *
           FROM Contractors AS c
           WHERE u.UserID = c.User_ID AND c.Offices_OfficeID = 3
         )
   OR  EXISTS
         ( SELECT *
           FROM ContractorCompanies AS cc
           WHERE u.UserID = cc.User_ID AND cc.Offices_OfficeID = 3
         )
 ;

If the above query returns the wanted results, then add an index on (Offices_OfficeID, User_ID) on each of the 3 tables.

I assume you already have an index on Users (UserID).


Wordpress using varchar(255) for index with InnoDB and utf8mb4_unicode_ci? I'm trying to learn a bit more about the details of proper database construction and ran across something that confuses me. In a current Wordpress installation, I'm finding varchar fields used for indexes which are longer than the 191 characters, which the documentation seems to say is the max. I'm running MariaDB 10.x which I believe matches up with MySQL 5.7, and the documentation confirms the limit when using utf8mb4_unicode_ci collation and compact as the format.

I'm finding varchar(200) and varchar(255) indexed fields, e.g. wp_postmeta, the field meta_key is varchar(255). Am I wrong or confused on this? The db is using the InnoDB engine. Thanks for any clarification.

(This answer is probably more than you wanted.  It is an in-depth discussion of the source of the problem, plus multiple solutions.)

Long ago, when MySQL added CHARACTER SET utf8, it needed up to 3 bytes per character.  And indexing was limited to 767 bytes, enough for VARCHAR(255).

When utf8mb4 was added (because utf8 was incomplete), the encoding needed up to 4 bytes/char, but the index limit was not increased.  (Oops.)  So, indexing became limited (for utf8mb4) to 191 characters ((767-2)/4).

That problem lasted throughout 5.5 and 5.6 (MariaDB 5.5 - 10.1?).  5.7 cleaned things up.

There are many solutions to this issue:

Change back to utf8.  Most users can get away from this.  But, if you need Emoji or all of Chinese, you need utf8mb4.
Limit your indexed VARCHARs to 191 instead of 255.  This can usually be done safely, but you should check the data, and understand that it limits the strings that you can use.
(see jkavalik's comment) Use "prefix" indexing:  INDEX (col(191)).  This has two negative effects:  (1) If the index is really UNIQUE, you get the wrong interpretation of the uniqueness constraint. (2) Performance probably suffers because of the inefficiencies of prefixing.
In 5.5.14, 5.6.3, 5.7.7, MariaDB 5.5, the following combination raises the limit from 767 to 3072:  innodb_large_prefix=1 (defaulted on in 5.7.7), innodb_file_format=Barracuda, innodb_file_per_table=1, ROW_FORMAT = DYNAMIC or COMPRESSED.

It would be nice if WordPress proactively tackled the problem you encountered.

(Update)  In later versions, these are deprecated (5.7 and 10.2?) because they are no longer necessary: innodb_file_format, innodb_file_format_check, innodb_file_format_max and innodb_large_prefix.  If you get an error to that effect, simply don't bother setting them.  They are actually removed in 8.0 / 10.4(?)


Are there security vunerabilites/implications with SQL Mail, currently using SQL Server 2008 R2 I am an accidental DBA and responsible for series of data load jobs. I would like to use SQL Mail for altering me to job failures. SQL Mail is not currently turned on are there security concerns in setting it up?

I am using SQL Server 2008.

Please use Database Mail instead of SQL Mail.  SQL Mail had security issues on several layers and in SQL 2005 SP2 had 'inbound emails' removed.  They then further removed support for it and it is now in the depreciation list.

Database Mail is the solution you are looking for.  It passes audits easily, has lots of documentation, and is fully supported by MS.

Don't use this one:

EXEC sp_configure 'SQL Mail XPs', 1
GO
RECONFIGURE
GO

Do use this one:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO

Configuration Wizard Link To Help You Configure It.

To start the Database Mail Configuration Wizard
From Object Explorer, connect to an instance of SQL Server.

Expand Management,

right-click Database Mail,

and select Configure Database Mail.

Choose the Set up Database Mail option to set up Database Mail for the first time.

Choose one of the other options for specific maintenance tasks:

Manage Database Mail accounts and profiles.

Manage profile security.

View or change system parameters.


Database in "Restoring" state Good morning, everyone!
I am using SQL SERVER 2008, and a needed to use the database of our client. When I was going to do a backup in case something went wrong I misclicked to do a restore and didn't notice that I was in the restore screen and clicked the "OK" button (without changing any configurantion on the screen). Right when it began doing I hit the "Stop Action Now". It stoped and a message appears saying that the user had canceled the action. So far so good, but when I was going to do the backup for real the SQL didn't allow saying that already had a restore going on. When I saw the database state was "Restoring". I searched on internet and saw that the query:
DATABASE NomeDatabase WITH RECOVERY

would resolve my problem but before I execute I asked my boss if there was any problem executing this query and he said to don't run the query because it would restore to the most recent backup (and the most recent backup was on 08/23). So we only have the MDF e LDF files, and when we try to do an attach with this query
EXEC sp_attach_db @dbname = N'DBName',
    @filename1 = N'C:\PATH\DBName.mdf',
    @filename2 = N'C:\PATH\DBName_log.ldf'

it gives this answer:

Msg 1813, Level 16, State 2, Line 1 Could not open new database
'DBName'. CREATE DATABASE is aborted. Msg 9004, Level 21, State 1,
Line 1 An error occurred while processing the log for database
'DBName'. If possible, restore from backup. If a backup is not
available, it might be necessary to rebuild the log.

I also search this error message and saw that is necessary rebuild the log but nobody here knows how to do it. I would like to know if you could help me or if anyone already had been through that.
Here is the screen that I did the restore, just clicked the "ok" not changing any configuration: Image
(My boss detached and deleted the database to try to do the attachment query, so we don't have the DB on the SQL anymore only MDF and LDF files)
I saw some posts on the site but none of them worked with me.
Thanks and sorry if there is any gramatical mistake!

Thanks for the answer. I also tried this query, but got no lucky.

The log cannot be rebuilt because there were open transactions/users
when the database was shutdown, no checkpoint occurred to the
database, or the database was read-only. This error could occur if the
transaction log file was manually deleted or lost due to a hardware or
environment failure.
Message 1813: Could not open new database 'dbname'. CREATE DATABASE is aborted.

So I guess there is nothing to do but restore the August 23rd now? All the things done after it are completely lost, that's it?

Use sp_attach_single_file_db to attach the MDF file without the LDF file since sp_attach_db didn't work. This will likely fail too though as the restore had already kicked in and overwrote those files (check the SQL Server error log to see if the restore started). If it does fail, you are stuck with restoring from the August 23rd backup.

Get the backups in order after you are done with this emergency.


Why is ON DUPLICATE KEY not working as expected? Before I start, I'm not a web developer by trade. I'm self taught using google and StackOverflow.

So I recently moved my site from a shared hosting to a VPS.

My provider moved my site / files over to my new hosting. Both run cPanel.

I had a script which worked perfectly fine on my old hosting-

INSERT INTO `database`.`table`
    (`UUID`, `UpdateDate`, `JoinDate`)
VALUES
    ('$uuid',NOW(),NOW())
ON DUPLICATE KEY UPDATE
   `UpdateDate` = NOW()

The problem is it doesnt work any more on my new server.

The problem i think is it doesnt update or insert due to not updating all the columns in that table.

I created two test tables, Table A with the 3 columns in above UUID, UpdateDate, JoinDate
and a second
Table B with 4 columns in UUID, Username, UpdateDate, JoinDate.

UUID is both Unique and Primary in both tables.

Table A - This table works as expected.

Table B - This table doesnt insert/update due to the extra column Username.

Any idea why this query doesn't work with table B? Do you have to insert/update every table column when running the query? Is it a PHP settings I need to change on my hosting?

Sorry if I haven't provided all information, this is my first DBSE question.

CREATE TABLE TestUserA`
( UUID varchar(40) NOT NULL,
  UpdateDate datetime NOT NULL,
  JoinDate datetime NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

ALTER TABLE TestUser
  ADD PRIMARY KEY (UUID),
  ADD UNIQUE KEY UUID (UUID),
  ADD KEY UUID_2 (UUID);

Server type: MariaDB Server version: 10.2.14-MariaDB - MariaDB Server
Protocol version: 10 cpsrvd 11.68.0.36
Database client version: libmysql - 5.1.73
PHP extension: mysqli
PHP version: 5.6.30

Any idea why this query doesn't work with table B? Do you have to insert/update every table column when running the query?

Yes, UserName is a NOT NULL column and doesn't have a DEFAULT value. Therefore you need to provide a value for every row INSERT. Not for UPDATE though, the column can be updated or not, depending on your requirements.

  Is it a PHP settings I need to change on my hosting?

No, nothing to do with PHP.

  I had a script which worked perfectly fine on my old hosting ...

If the script was working perfectly in the previous server, when inserting in either table, then the tables were not transferred identically. The extra columns - besides the 3 that the script provides values for - would have been defined as NULL (so they'd have a DEFAULT NULL) or there might be a DEFAULT attribute defined when the table was created.

Another possibility - besides lack of DEFAULT attribute - is that the table had an INSERT trigger that supplied a value for the UserName automatically upon insert and that the triggers were not properly copied when the the database was transfered.


I'm performing a post-mortem on an Oracle database that's crashed its' host. Where should I begin? I have an Oracle database (10.2.0.1.0 I believe) on a Windows 2003 server that I believe has successfully crashed the OS itself twice.  Debates on platforms and stability aside, the evidence suggests that the server becomes unresponsive to any remote access (ports open but services don't respond) due to what I can only guess is a bad state in the Oracle's process. The IT staff noted the process appeared to be preventing it from successfully rebooting when they went to power-cycle it.

I don't have physical access to the server. However, since it's up and running again, are there any logs/dumps/etc. that I can check which may point me in the right direction? If you were me, where would you begin? Google hasn't been kind on the subject.

The first thing to look at would be the database's alert.log file.  If the database was having problems at the times that were identified, you'll get error messages in the alert.log and, most likely, you'll get pointers to detailed trace files.

Are you licensed to use the AWR?  Is statspack installed?  If none of the database processes was crashing, it's possible that the server was unresponsive because the application was issuing runaway SQL and Oracle was crushing the server.  An AWR/ statspack report from the time in question will show whether Oracle was actually doing anything at the time or not.  If you have any Windows monitoring information from the time in question, that would also be useful.  If the Windows performance monitors show a pile of activity and Oracle shows none, for example, that would be very interesting.


Is it possible to use ExpressMaint with SQL Server 2012 (Express)? When I try running ExpressMaint on a server with SQL Server 2012 installed, it fails with an error saying it cannot find the Microsoft.SqlServer.Smo libraries (SQL Management Objects). From what I've read, this is because ExpressMaint was compiled against the 2008 SMO libraries.

Various websites recommend installing the older SMO libraries. However, I just tried installing the 2008 R2 libraries on my 2012 box, and it wouldn't let me because there is a newer version installed.

There is a post on the CodePlex site where someone claims to have recompiled ExpressMaint against 2012, but I'm loathe to install something I found on the internet on my production box.

Has anyone had success with this? Is there a better inexpensive solution out there?

I've used ExpressMaint before, and I like it because it's so easy to use.

Turns out there's a stored-proc version of ExpressMaint, which does seem to work on 2012, and is just as simple to use. Not sure now why I didn't just use that all along! :-)


PostgreSQL streaming replication broken: invalid magic number, out-of-sequence timeline ID For some reason, my PostgreSQL slave no longer streaming-replicates changes on the master. It was working before, for a while, but I recently noticed that the slave's database contents is old, and there are errors in the slave's log files, namely "invalid magic number 0000" and "out-of-sequence timeline ID 1 (after 2)".

Do you have any idea how can I troubleshoot this? Or why does it happen?

Details follow.

Log file error messages: (on the slave, pg_log/postgresql-Sat.log)

LOG:  entering standby mode
LOG:  redo starts at 0/1C78848
LOG:  consistent recovery state reached at 0/1C7FA30
LOG:  database system is ready to accept read only connections
LOG:  invalid magic number 0000 in log file 0, segment 1, offset 14942208
LOG:  streaming replication successfully connected to primary
LOG:  out-of-sequence timeline ID 1 (after 2) in log file 0, segment 1, offset 0
FATAL:  terminating walreceiver process due to administrator command
LOG:  out-of-sequence timeline ID 1 (after 2) in log file 0, segment 1, offset 0
LOG:  out-of-sequence timeline ID 1 (after 2) in log file 0, segment 1, offset 0
LOG:  out-of-sequence timeline ID 1 (after 2) in log file 0, segment 1, offset 0
...

Transaction logs: (on the slave -- weird, log file 0...2...1 is from January 2012, but 0...2...2 is from December 2011!?)

-bash-4.1$ tree -D pg_xlog/
pg_xlog/
├── [Dec  6  6:27]  000000010000000000000001
├── [Jan 20 21:33]  000000020000000000000001
├── [Dec 21  5:29]  000000020000000000000002
├── [Dec  6  6:30]  00000002.history
└── [Dec  6  6:30]  archive_status
    └── [Dec  6  6:30]  00000002.history.ready

1 directory, 5 files
-bash-4.1$ cat pg_xlog/00000002.history
1       000000010000000000000001        no recovery target specified

Transaction logs: (on the master)

-bash-4.1$ tree -D pg_xlog/
pg_xlog/
├── [Dec  6  6:27]  000000010000000000000001
├── [Jan 21  8:55]  000000020000000000000001
├── [Dec 21  5:31]  000000020000000000000002
├── [Dec  6  6:30]  00000002.history
└── [Dec  6  6:30]  archive_status
    └── [Dec  6  6:30]  00000002.history.ready

-bash-4.1$ cat pg_xlog/00000002.history
1       000000010000000000000001        no recovery target specified

Config files: (on the slave)

---postgresql.conf---
wal_level = hot_standby
...
hot_standby = on
...

---recovery.conf---
standby_mode = 'on'
primary_conninfo = 'host=dw0azewdbpv11danny user=replicator password=...'
recovery_target_timeline = 'latest'

Finally, on the slave, ps aux shows that:

Jan20   0:01 postgres: startup process   waiting for 000000020000000000000001

Update one week later:

There seems to be a replication bug for PostgreSQL builds with gcc 4.6.0.
http://archives.postgresql.org/pgsql-general/2011-07/msg00686.php
and
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=45d792f70272ed57b932816562f31c2f79426c2a
However I'd guess that that bug would not affect 9.1?

PostgreSQL versions should be identical on master and slave, and SELECT version() looks like this on both master and slave:

-bash-4.1$ psql
psql (9.1.1)
Type "help" for help.

postgres=# SELECT version();
                                                   version
--------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.1.1 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.5 20110214 (Red Hat 4.4.5-6), 64-bit
(1 row)

Perhaps I should also mention that the current master was a slave, originally, and the current slave was the master, originally. -- I did one failover, to test that it worked okay.

(I'll ask this question on the Postgres mailing lists.)

This doesn't sound like the same thing.  The GCC version is different and your PostgreSQL version is different.  IIRC it was an issue with GCC 4.6 optimizations and was fixed.  I don't believe it should have impacted your setup.

More likely something changed which caused this.  Perhaps the slave was promoted to a master briefly and then demoted again?  That will create problems like this all the time.

The timeline issue suggests to me that this is in fact what happened. It doesn't matter whether you write to a db or not, just completing recovery and promoting is enough to break the timeline.


Copy database, problem with not existing principal I'm trying to copy a database from a server to my laptop, using Management Studio. However, after about an hour of copying, I get the error

Cannot find the principal 'MVGSQL01\itsac', because it does not exist or you do not have permission

I do not have the option of using backups, as I do not have access to the file system on the source server.

I found something while searching on Google, that suggested I should do:

CREATE LOGIN SEC_USER  WITH PASSWORD = 'YourPassword'
Alter User Sec_User with login  = SEC_USER;

but if I change SEC_USER for ITSAC and Sec_User for itsac, I get almost the same error message:

Cannot alter the user 'itsac', because it does not exist or you do not have permission.

Some advice would be appreciated!

The principal in the error you are seeing (MVGSQL01\itsac) is a Windows login from the computer MVGSQL01 and can't be created in SQL Server because it doesn't exist on the destination computer.

You would need to create a new Windows login on the computer you are copying your database to.

Then in SSMS using a new query window, you would run these two commands to add the login and then the database user for your new login

USE [master]
GO
CREATE LOGIN [NEWCOMPUTERNAME\itsac] FROM WINDOWS WITH DEFAULT_DATABASE=[mydatabase]
GO

USE [mydatabase]

CREATE USER [NEWCOMPUTERNAME\itsac] FOR LOGIN [NEWCOMPUTERNAME\itsac]
GO

Note: Since the computer name of the principal (login) is different though, you will probably get the same error. Another option would be to remove the login (MVGSQL01\itsac) from SQL Server on MVGSQL01 temporarily and then copy the database to your laptop.

There is a stored procedure called sp_help_revlogin http://support.microsoft.com/kb/918992 that can be used to backup and or transfer your logins.

Updated 7/15/2013

In SSMS, when you launch the SQL Server Copy Database Wizard, you will eventually get to the Select Server Objects Screen, if you are copying to or from a remote server.

From there you can select which users you want or don't want to copy for the database you wish to copy.

And Then you get the Logins screen. Click on Copy only the selected logins and then select the logins you wish to copy. In your case, you will be excluding MVGSQL01\itsac.


Postgres 9.6 AWS RDS loadbalancing I have an legacy application that has a number of very complex queries that take significant time and resource to execute. Rather than rewrite the application I am looking at the possibility of doing some form of load balancing.

I firstly looked at writing a script with pg_isready to determine if the database is able to respond, if not to potentially switch to another replica. However, this approach does not give an indication of the current load on the database.

I've read up on using haproxy to do some load balancing, but it seems as if it would suffer the same problem. My question is, has anyone faced a similar problem and found a neat solution. I would like to switch to a different replica is the database load is above a certain threshold. Is this at all possible ?

Is the complex query read-only ?  The answer to that kinda determines the solution.

You could do this with haproxy maybe.   See https://githubengineering.com/context-aware-mysql-pools-via-haproxy/ for inspiration on how to measure the load of your backend (this is mysql but same idea works on pgsql)

You could probably get some good config values from my repo here https://github.com/gplv2/haproxy-postgresql

it's made for master / standby cluster but you can add a standby pgsql backend pool to this config to the example of the guys at github with their mysql machines pool and the backup pool.  sounds like that is what you want


When is a row added to DBA_HIST_SQLSTAT? I don't understand the criteria that entails an additional record in the dba_hist_sqlstat view. I have expected there to be at most one entry per sql_id and snap_id, that is, the following statement to return no record:

select
  count(*),
  snap_id,
  sql_id
from
  dba_hist_sqlstat
group by
  snap_id,
  sql_id
having
  count(*) > 1
order by
  count(*) desc;

Yet, it returns many records, indicating that it is not possible, but the normality, that the same sql statement is captured multiple times per snap_id.

Additionaly, with more than one record per sql and snap period, how do I interpret the delta columns within this view? The documentation reads:

  The delta value is the value of the statistics from the BEGIN_INTERVAL_TIME to the END_INTERVAL_TIME in the DBA_HIST_SNAPSHOT view.

This doesn't really make sense to me.

Update as per Justin's comment: this is not in a RAC environment: so even if I group by snap_id, sql_id, dbid, instance_number, the query returns multiple records per snap_id and sql_id.

Oracle collects SQL statistics for each SQL execution plan/sql_id (plan_hash_value), rather than just for a specific sql_id.

To verify that this is indeed the case, this query should return 0 rows:

select
  count(*),
  snap_id,
  sql_id,
  plan_hash_value
from
  dba_hist_sqlstat
group by
  snap_id,
  sql_id,
  plan_hash_value
having
  count(*) > 1
order by
  count(*) desc;


MySQL+Web-App Performance Issues using different servers We are having a performance issue with our MySQL servers that does not make any sense. I have read countless articles from many people (mostly Percona) and have made my.cnf tweaks. We have even manage to squeeze out another 30% more TPS  thanks to those Percona articles. However, our problem is with our in-house web-app (a Tomcat/Java/Apache model). It performs poorly when connected to certain servers - the better hardware servers.

Here is the symptom:

If we point our test application server (Ubuntu, Apache, Tomcat, Java) to server MYSQL02, the applications performance is acceptable. However, if we point the application to MYSQL01 or MYSQL03 (and these two boxes are idle!) the application performance is poor. There are high latencies. Example:

Retrieving an account (via our web-app) from MYSQL02 takes
2 to 3 seconds. On MYSQL01 or MYSQL03 it takes 12 seconds or longer.

We cannot figure out why! The MySQL servers and MONyog do NOT report any problems! If we execute the statements (100's of them) manually they return instance results and their explanations show they are all using indexes. We do NOT get any slow query, deadlock, or contention notifications.

Here is some basic information about our MySQL systems. They are all DEDICATED MySQL servers:

PROD (current production, not in replication farm, standalone)

Best/newest hardware configuration (8 core/ 20GB / 15K RPM HW RAID 10)
MySQL: 5.0 (RHEL)
We are trying to migrate from this MySQL 5.0 to our MySQL 5.5 farm (see below).
This server will then be reloaded with MySQL 5.5 and return as the primary
production server.

MYSQL01

Second best hardware configuration (8 core / 12GB RAM / 15K RPM HW RAID 10)
Supermicro X8FAH; 2xXEON X5550 @ 2.66GHz
2x LSI MegaRAID SAS 9260-8i
MySQL: 5.5.31 (Ubuntu)
OS: Ubuntu 12.04 LTS x64

MYSQL02

Worst/oldest hardware configuration(4 core / 12GB RAM / 7.2K RPM SW {mdadm} RAID 5)
Supermicro X2DBE; 2xXEON 5150 @ 2.66GHz
MySQL: 5.5.31 (Ubuntu)
OS: Ubuntu 12.04 LTS x64

This is our EDI workhorse. It spends most of it's day importing data from
our old mainframe system. It replicates the data to 01 and 03.

MYSQL03

Third best hardware configuration (8 core / 12GB RAM / 7.2K RPM HW RAID 10)
Supermicro X7DGB8-X; XEON E5410 @ 2.33GHz
MySQL: 5.5.31 (Ubuntu)
OS: Ubuntu 12.04 LTS x64

We used sysbench to test and tweak all the above systems and here are the test results with notes.

NOTE: TPS = Transactions Per Second

Results before applying any new tweaks:

PROD: 1,179.72 TPS <- Expected
MYSQL01: 442.92 TPS <- Why?
MYSQL02: 543.22 TPS <- Expected
MYSQL03: 904.22 TPS <- Surprising

Results after my.cnf tweaks:

MYSQL01: 655.00 TPS <- 32% improvement. Not bad but still very poor compared to its siblings.
MYSQL02: 754.81 TPS <- 28% improvement. This was unexpected.
MYSQL03: 969.59 TPS <- 07% improvement.

We are unsure why MYSQL01's performance is so poor. We can only summarize that there is an OS, RAID CARD or BIOS setting(s) that may be improperly set. I am leaning towards the RAID Card/Configuration. They only way to know for sure is to shutdown this server and scrutinize the configuration. A reload may be necessary. However, since it is our ultimate plan to make the current PROD hardware the primary production MySQL server then we may leave MYSQL01 alone for now and re-purpose the hardware after migrating to the 5.5 farm. However, we can't migrate until we figure out why our application is behaving so poorly on certain hardware.

Anyone have any suggestions?

The OS even shows that we have an IO WAIT problem.

We think we have found the issue - the LSI MegaRAID SAS 9260-8i (Supermicro
SMC2108) RAID adapter. Either we have it configured incorrectly or it's just a poor performer.

We stumbled onto this blog:
http://artipc10.vub.ac.be/wordpress/2011/09/12/megacli-useful-commands/

and disabled the Patrol Read function and it increased our TPS by almost 130/second.

Trying to solicit tweaking recommendations here: https://askubuntu.com/questions/304488/tweaking-recommendations-for-smc2108-lsi-megaraid-sas-9260-8i-raid-adapter

This link http://www.randombugs.com/linux/tuning-linux-system-database.html was
very very useful. We implemented these changes which resulted in significant TPS improvements.


Create Log Shipping Step Failed After ServerName Changed I was doing some test on server name change and encounter some errors.

Here is the original setup: Server name - ServerA, SQL Server default instance - ServerA

Changes: Server name - ServerB

Before I change the SQL Server default instance 'servername'

SELECT @@SERVERNAME, SERVERPROPERTY('SERVERNAME')

Return ServerA, ServerB (since I have changed the server name)

exec sp_add_log_shipping_primary_database completed successfully. However, there was error when running the log shipping backup job,

  Error: A network-related or instance-specific error occurred while
  establishing a connection to SQL Server. The server was not found or
  was not accessible. Verify that the instance name is correct and that
  SQL Server is configured to allow remote connections.

As expected. So I change the SQL Server default instance 'servername',

EXEC sp_dropserver 'ServerB'
EXEC sp_addserver 'ServerA', 'local'

Restart the SQL Server service.

SELECT @@SERVERNAME, SERVERPROPERTY('SERVERNAME')

Return ServerB, ServerB. OK, I delete the previous log shipping backup job. Run the sp_add_log_shipping_primary_database but receive this error,

  Msg 14234, Level 16, State 1, Procedure sp_verify_jobstep, Line 214
  The specified '@server' is invalid (valid values are returned by
  sp_helpserver).

sp_helpserver return ServerB under name and network_name column with id 0

So question is, am I missing some steps over changing servername or is this a bug?

Version - SQL Server 2012 SP1

So in SQL Server 2012 SP1 I did this:

EXEC msdb..sp_helptext 'sp_verify_jobstep';

I scanned for 14234 (the error number you received), and right above the relevant RAISERROR call, I saw this:

IF (@server IS NOT NULL) AND (NOT EXISTS (SELECT *
  FROM master.dbo.sysservers
  WHERE (UPPER(srvname) = UPPER(@server))))

So, run this code with a PRINT:

IF NOT EXISTS (SELECT *
  FROM master.dbo.sysservers
  WHERE (UPPER(srvname) = UPPER(N'ServerB')))
BEGIN
  PRINT 'Not found.';
END

Then verify exactly what is in master.dbo.sysservers and also check master.sys.servers. I suspect either those two views don't match or the name in both is not exactly ServerB. Show the actual syntax you used to call the procedure and verify that the server wasn't renamed with leading/trailing spaces, whitespace or other non-printing characters.

Another idea is to not use the system procedure to set up log shipping in the first place. Log shipping isn't all that cumbersome to set up and in the past I've always just set up the backup / restore jobs manually. Also the stored procedure you called first only takes monitor_server as a potential server name where it could get into trouble connecting somehow, and it is nullable. Are you sure you need to pass it? Why would you want the monitor server to be the same as the source server? If one goes down they're both down by definition. What happens when you don't explicitly name a server as the monitor_server and leave that and other related parameters null?

Also these various hooks and potential conflicts are why I don't ever bother to rename servers that are already running. I'd rather pay the extra cost of wiping, reinstalling and restoring than dealing with all of this after the fact - especially if the quick rename was sold as something "hassle-free."


Getting the exact and closest Search using CONTAINS/CONTAINSTABLE in SQL I have a table that contains 30 000+ records named as ProductCodes that contains a column 'Value'.

I have made a query that'll return the records matching and containing a passed String using CONTAINS.

Select TOP 5 * from ProductCodes
WHERE CONTAINS(Value,'"pepper*"')

It does return the records contains 'Pepper' e.g. Pepper and Bayleaf, Peppermint, Dr. Pepper etc.
I also have an exact record that only contains the exact string, 'Pepper' which is displayed in the mid of the results. I only need to select the TOP 5 of the records and also the exacting match of passed string.

I also tried to use the CONTAINSTABLE

SELECT Value, RANK FROM
ProductCodes PC
INNER JOIN
CONTAINSTABLE(ProductCodes,Value,N'pepper') AS KEY_TBL
ON PC.Value = KEY_TBL.[KEY]
ORDER BY RANK DESC

I tried using a WHERE rank = 1000 which I read somewhere that's the rank of the closest match but as I figured out the rank of the record 'Pepper' is 128 and phrases that have more than 1 word match have a higher rank.

I am fairly new to using FREETEXT and Semantics so I am still learning on it. How can I take the exact match and 'partial' match in my TOP 5? TIA

edit:

I have added a UNION ALL query where it first selects the equal record and combining it with my CONTAINS query.

SELECT TOP 5 * FROM (
 SELECT TOP 5 * From ProductCodes
 WHERE Value = 'pepper'
 UNION ALL
 Select TOP 5 * from ProductCodes
 WHERE CONTAINS(Value,'"pepper*"')
) as Value

Now, I can get the exacting match and partial match but wonder how it'll make my query slower.

Your latest query allows the same row to be returned twice. Also it,'s possible to limit the results considering a predefined weight for the match in order to get a more relevant output.

You should get a more precise result with this:

WITH CTE
AS
(
    SELECT 10000 AS RANK,
        P.id,
        P.value
    FROM ProductCodes P
    WHERE P.value = 'pepper'

    UNION

    SELECT k.RANK,
        P.id,
        P.value
    FROM ProductCodes P
        INNER JOIN
          CONTAINSTABLE(ProductCodes, value, 'ISABOUT (pepper WEIGHT(0.2),
            "pepper*" WEIGHT(0.1))',
            5) AS K
          ON P.id = K.[KEY]
    WHERE value <> 'pepper'
)
SELECT TOP (5) RANK, id, value
FROM CTE
ORDER BY RANK DESC;

References:

Search for words or phrases using weighted values (Weighted Term)

Examples of Using RANK to Limit Search Results

WITH common_table_expression (Transact-SQL)


Check whether MySQL Innodb Rows are compressed After setting the various variables to enable compression on the Innodb Barracuda tables, what query will you use to check that those tables are indeed compressed?

Use the show command to see the table definition :

mysql:>show create table <table name >


setup table level Autovacuuming on postgres database I am trying to setup table level Autovacuuming on postgres database. What all metrices I need to collect and analyze specific to that particular table?
I have to enable autovacuuming on all the tables wherever it is required.(With most inserts/deletes probably). How do I proceed on this?
Thanks

Just leave all the default settings as they are. Autovacuum is enabled by default and will usually do the right thing. In my professional life I find that newbies who are trying to tune the autovacuum parameters usually do more harm than good.

One parameter I'd recommend changing is autovacuum_vacuum_cost_delay, which I would lower to 2ms (which is the default setting from v12 on). That way, you run less risk of having a too lazy autovacuum.

If you ask a more specific question, you can get a more specific answer.


Slow query on large table with GROUP BY and ORDER BY I have a table with 7.2 million tuples which looks like this:

                               table public.methods
 column |          type         |                      attributes
--------+-----------------------+----------------------------------------------------
 id     | integer               | not null DEFAULT nextval('methodkey'::regclass)
 hash   | character varying(32) | not null
 string | character varying     | not null
 method | character varying     | not null
 file   | character varying     | not null
 type   | character varying     | not null
Indexes:
    "methods_pkey" PRIMARY KEY, btree (id)
    "methodhash" btree (hash)

Now I want to select some values but the query is incredibly slow:

db=# explain
    select hash, string, count(method)
    from methods
    where hash not in
          (select hash from nostring)
    group by hash, string
    order by count(method) desc;
                                            QUERY PLAN
----------------------------------------------------------------------------------------
 Sort  (cost=160245190041.10..160245190962.07 rows=368391 width=182)
   Sort Key: (count(methods.method))
   ->  GroupAggregate  (cost=160245017241.77..160245057764.73 rows=368391 width=182)
       ->  Sort  (cost=160245017241.77..160245026451.53 rows=3683905 width=182)
             Sort Key: methods.hash, methods.string
             ->  Seq Scan on methods  (cost=0.00..160243305942.27 rows=3683905 width=182)
                   Filter: (NOT (SubPlan 1))
                   SubPlan 1
                   ->  Materialize  (cost=0.00..41071.54 rows=970636 width=33)
                     ->  Seq Scan on nostring  (cost=0.00..28634.36 rows=970636 width=33)

The hash column is the md5 hash of string and has an index. So I think my problem is that the whole table is sorted by id and not by hash, so it takes a while to sort it first and then group it?

The table nostring contains only a list of hashes I don't want to have. But I need both tables to have all values. So it's not an option to delete these.

additional info: none of the columns can be null (fixed that in the table definition) and i'm using postgresql 9.2.

The LEFT JOIN in @dezso's answer should be good. An index, however, will hardly be useful (per se), because the query has to read the whole table anyway - the exception being index-only scans in Postgres 9.2+ and favorable conditions, see below.

SELECT m.hash, m.string, count(m.method) AS method_ct
FROM   methods m
LEFT   JOIN nostring n USING (hash)
WHERE  n.hash IS NULL
GROUP  BY m.hash, m.string
ORDER  BY count(m.method) DESC;

Run EXPLAIN ANALYZE on the query. Several times to exclude cashing effects and noise. Compare the best results.

Create a multi-column index that matches your query:

CREATE INDEX methods_cluster_idx ON methods (hash, string, method);

Wait? After I said an index wouldn't help? Well, we need it to CLUSTER the table:

CLUSTER methods USING methods_cluster_idx;
ANALYZE methods;

Rerun EXPLAIN ANALYZE. Any faster? It should be.

CLUSTER is a one-time operation to rewrite the whole table in the order of the used index. It is also effectively a VACUUM FULL. If you want to be sure, you'd run a pre-test with VACUUM FULL alone to see what can be attributed to that.

If your table sees a lot of write operations, the effect will degrade over time. Schedule CLUSTER at off-hours to restore the effect. Fine tuning depends of your exact use-case. The manual about CLUSTER.

CLUSTER is a rather crude tool, needs an exclusive lock on the table. If you can't afford that, consider pg_repack which can do the same without exclusive lock. More in this later answer:

Configuring PostgreSQL for read performance

If the percentage of NULL values in the column method is high (more than ~ 20 percent, depending on actual row sizes), a partial index should help:

CREATE INDEX methods_foo_idx ON methods (hash, string)
WHERE method IS NOT NULL;

(Your later update shows your columns to be NOT NULL, so not applicable.)

If you are running PostgreSQL 9.2 or later (as @deszo commented) the presented indexes may be useful without CLUSTER if the planner can utilize index-only scans. Only applicable under favorable conditions: No write operations that would effect the visibility map since the last VACUUM and all columns in the query have to be covered by the index. Basically read-only tables can use this any time, while heavily written tables are limited. More details in the Postgres Wiki.

The above mentioned partial index could be even more useful in that case.

If, on the other hand, there are no NULL values in column method, you should
1.) define it NOT NULL and
2.) use count(*) instead of count(method), that's slightly faster and does the same in the absence of NULL values.

If you have to call this query often and the table is read-only, create a MATERIALIZED VIEW.

Exotic fine point:
Your table is named nostring, yet seems to contain hashes. By excluding hashes instead of strings, there is a chance that you exclude more strings than intended. Extremely unlikely, but possible.


Executing SSIS catalogue package from a different machine, authentication fail I am having an issue running a SSIS package which uses a C# script component to carry out a HTTPS file download and load the data into a SQL Server database.

On our infrastructure we have a management (MGT), SQL Server (SQL), and Integration Services (SIS) machines. The package is deployed into the Integration Services Catalog on the SIS machine.  The servers are on the same domain but on different vlans.

When the package is executed from SQL Server Management Studio (SSMS), running on the SIS machine, executing from Integration Services Catalog the package runs without issue.

However when I open SSMS on the MGT machine connect to the SQL Database on the SIS machine and run the package from the same catalog I get an error message.

  An error occurred while executing package xxx in the container DFT - Load 1260 the error details are

  0, System.Runtime.InteropServices.COMException (0xC001600C): Server authentication failed. This error occurs when login credentials are not provided, or the credentials are incorrect.

  at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSHttpClientConnection100.DownloadData()
     at ScriptMain.PreExecute()

  The package execution stopped execution. Please investigate.

The code being ran within the script task within the package is

var connMgr = ComponentMetaData.RuntimeConnectionCollection.GetRuntimeConnectionByName(Variables.pvDataConnectionName1260).ConnectionManager;
var hcc = (HttpClientConnection100)connMgr.AcquireConnection(null);
hcc.Timeout = 300;

if (Variables.pvDataConnectionAuthorisationMode1260 == "Server")
{
    hcc.UseServerCredentials = true;
    hcc.ServerUserName = Variables.pvDataConnectionUsername1260;
    hcc.ServerPassword = Variables.pvDataConnectionPassword1260;
}
_downloadBuffer = hcc.DownloadData(); //this is the line that fails

connMgr.ReleaseConnection(hcc);

The URL being requested is along the lines of https://reports.abc.com/def/data_extracts/filename_20170815.csv

The URL has requires a username and password which we are providing to the script task as per the code above.

SPN's for Kerboros have been configured.  And I can verify the connection is using Kerboros auth.  I've enabled kerboros logging but nothing is logged in System event log when the package executes.

Any ideas what else might be causing the authentication to fail?  or if there is any other logging I can do?

You're on the right track, but I suspect you're still running into the double-hop issue.  SPNs are a step in the right direction to resolve this, but next you need to setup delegation for your SQL Server Instance so that it can properly pass along the AD token.

I will recommend that you setup constrained delegation for SQL Server, but any delegation adjustments will require an account with Domain Admin privileges on your network, so you may need to loop in another person or team depending on your company's structure.

The link above is for the older versions of Active directory, but the same approach will likely apply if you're running a newer version.

Steps 3 - 6 on this article about configuring delegation for SSRS are also a good reference point and SSIS is no different from SSRS where these steps are concerned.


SQL Server Management Studio - Actual Execution Plan - Actual Row Size and Actual Data Size I am looking at an actual execution plan in Microsoft SQL Server Management Studio v 17.9.1 and I can't see a lot of actual values to compare against the estimates.

For example, we have a connector which holds the following data:

I can see the Estimate Row and Data Size, but where is their actual size? Is there a way to see this information?

Same question for actual CPU, I/O, number of executions and so on.

Update: MS SQL Server 2008 R2

I can see the Estimate Row and Data Size, but where is their actual
  size? Is there a way to see this information?

As commented by @Randi Vertongen I use SQL Sentry Plan explorer to see actual data size if I need. This is a free tool you can download (without even giving out your email address).

Plan Explorer Installation & Overview

Here is an example of that data you are looking for in plan explorer.

As far as actual number of rows and number of rows read is available in SSMS tool tip or properties. Here is an example.

  Same question for actual CPU, I/O, number of executions and so on.

For CPU and I/O you can right click individual operator and see it in properties or look at xml.  If you right click the leftmost node and see it's properties you will total use of total cpu and  memory grant details. Number of execution is also available in SSMS. See picture above.

Following is an example of overall cpu, time and memory grant information:

Following is an example of looking at details per operator. This will vary depending on the type of operator. This output is from a sort operator.

Here is a partial list of when some of these features were added and back ported to older versions.

Overall plan explorer is a far better tool to look at execution plan and many data professionals use it everyday.


SQL Server 2016 ColumnStore Clustered Index - Query plan showing a scan, performance not great I have a SQL 2016 table that looks as follows:

I have a columnstore clustered index on the table and no other indexes. I'm executing the following query:

Select *
From dbo.PanelWorkflow
Where ReadTime Between '4/1/2016' And '4/5/2016' And Lineage = 'PBG11A' And ProcessNumber = 5400

The query plan looks as follows:

This table has 1.1B rows. The query plan shows a scan and the query takes about 1 minute to execute. ~21K rows are returned. Is this what I can expect from columnstore clustered indexes? Will a scan always be done (assuming no other indexes on the table)? Is this the sort of performance I can expect (acceptable but not great)?

I set Statistics IO on and got the following output:

Table 'PanelWorkflow'. Scan count 2, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 9733431, lob physical reads 195, lob read-ahead reads 13045645.
Table 'PanelWorkflow'. Segment reads 955, segment skipped 336.

Finally, the query above is strictly a test query to get a feel for how CSIs perform and operate. This is not a production level query.

I have a columnstore clustered index on the table and no other indexes ... The query plan shows a scan

Well, pretty much that is the only option available for a CCI. With CCIs the performance gain come from column elimination, compression and segment elimination, all contributing to reduce IO and thus allow scans to perform fast. For your case I would look at two things:

check segment health: sys.column_store_row_groups. For 1.1B rows you should have ~1000 rowgroups, all closed and compressed.
check that you loaded the data in a manner that allows segment elimination. Read Understanding Segment Elimination. Given your date range filter your query should scan only 1 (one) of those 1000 segments, and skip the other 999. If this doesn't happen, it means you loaded the data in a manner that prevents segment elimination. Read Columnstore indexes - data loading guidance.

Now, your query:

Select *
From dbo.PanelWorkflow
Where ReadTime Between '4/1/2016' And '4/5/2016' And Lineage = 'PBG11A' And ProcessNumber = 5400

This is not what columnstores and big tables are about. You are selecting all columns that satisfy a date range and some other criteria.  What for? Analytical data is all about computing aggregates and making analysis. Don't ask for *. Compute relevant analysis on the server side. Redesign your app in a manner that does not require displaying pages upon pages of raw data.


How to create a csv with SQLCMD which can be imported into another database system I may be doing this all the wrong way so please point me in the right direction if I am missing something.

I have an Ubuntu server with SQLCMD installed and a Windows Server with a MSSQL database.

I have a working script to output data from a query out of the MSSQL database into a CSV file already BUT its not quite right. SQLCMD creates 2 excess features which I would like to remove and there is no real definitive guide on how to do it.

Sample output:

header1,header2
-----,-----
data1,data2
data3,data4

Network packet size (bytes): 4096
1 xact[s]:
Clock Time (ms.): total       445  avg   445.0 (2.2 xacts per sec.)

So the features here I do not want in a CSV are quite obvious. The --- lines and the output data at the bottom.

My script looks something like this so far:

sqlcmd -S databaseserver -U "sa" -d database -p -W -Q "set nocount on; SELECT x as data, count(*) as datacount FROM location WHERE x IS NOT NULL GROUP BY x" -s "," -o output.csv -r0

I am happy to make .sh files with actions which take place after so any native ubuntu scripting method which can "finish the job" is acceptable. I am not that confident at writing the scripts but can usually work out what they do.

If you are open to using Powershell...

Assume the following table definition:

--Demo setup
DROP TABLE IF EXISTS BillingData;
go
CREATE TABLE BillingData
    (BillingId int, Feature varchar(13), Usage int, Measurement varchar(9), Cost int)
;

INSERT INTO BillingData
    (BillingId, Feature, Usage, Measurement, Cost)
VALUES
    (1, 'Walks', 25, 'walks', 200.32),
    (1, 'Baths', 5, 'baths', 251.32),
    (2, 'Litter change', 53, 'changes', 110.21),
    (2, 'Groom', 25, 'brushings', 123.00),
    (2, 'Scratching', 213, 'clipping', 123.00),
    (3, 'Pilling', 11, 'medicate', 10.00),
    (4, 'Groom', 5, 'brushings', 50.00),
    (4, 'Exercise', 1, 'run', 25.12),
    (1, 'Walks', 500, 'walks', 12351.31),
    (1, 'Baths', 53, 'baths', 1235.00),
    (2, 'Baths', 53, 'baths', 1235.00)
;

Using Powershell:

$OutFile = "c:\test\testout.txt"
Invoke-SqlCmd -Query "select * from test.dbo.billingdata" -ServerInstance . |
ConvertTo-CSV -NoTypeInformation |
Select-Object | % {$_ -replace '"', ""} | Out-File $OutFile -Force -Encoding ascii

Results of output file:

BillingId,Feature,Usage,Measurement,Cost
1,Walks,25,walks,200
1,Baths,5,baths,251
2,Litter change,53,changes,110
2,Groom,25,brushings,123
2,Scratching,213,clipping,123
3,Pilling,11,medicate,10
4,Groom,5,brushings,50
4,Exercise,1,run,25
1,Walks,500,walks,12351
1,Baths,53,baths,1235
2,Baths,53,baths,1235


CHECKDB found 2 allocation errors and 0 consistency errors not associated with any single object Msg 8906, Level 16, State 1, Line 1
  Page (1:99122) in database ID 9 is allocated in the SGAM (1:3) and PFS (1:97056), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'.

  Msg 8906, Level 16, State 1, Line 1
  Page (1:291360) in database ID 9 is allocated in the SGAM (1:3) and PFS (1:291168), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'.

  CHECKDB found 2 allocation errors and 0 consistency errors not associated with any single object.

I ran a CHECKDB and received the above error. I have very little experience with SQL errors and our Database team is not an option. I have read that I will have to perform a repair and will incur data loss. Is there anyway to predict what sort of data loss and how much by the above error? Thanks in advance.

My first question would be do you have latest valid backup or not. Since database size is just 6G and also because minimum repair suggested by checkdb is repair_allow_data_loss , which can cause data loss and could also remove business constraints from database, it would be good to restore database from valid backup.

Before restoring the backup you need to check validity of the backup. You can do it using below

restore verifyonly from disk='Backup location'

If it comes out clean you can restore the backup to get back the database. The data loss also depends upon the recovery model of database if it is simple you can only restore full and differential backup if it is bulk logged and Full you can restore full ,differential and log backups to get minimum data loss.

Are you able to take backup of current database with continue after error. Please see if below command works, I guess it would

backup database db_name to disk='Location' with continue_after_error

This would generate corrupt backup of database now restore this backup file ON DIFFERENT SERVER using continue after clause normal restore wont be possible see TSQL Restore for restore command.

After above is done run checkdb within transaction like below on above restored database.

Begin transaction
dbcc checkdb('db_name', repair_allow_data_loss)
--commit

After this you can have look into database how much data checkdb has deleted to recover database. Please note checkdb also removes constraints so I would not ask you to run it. Please read Books Online Document  before proceeding

At last please see SQL Server errorlog and event viewer to see why database got corrupted


How to force mysqldump to produce /*!40101 SET character_set_client = utf8mb4 */; I am attempting to dump my database, which is set to, and only contains, utf8mb4 and appropriate collations. I use the following line:

mysqldump --add-drop-table --add-locks --allow-keywords --complete-insert=TRUE --compress --create-options --databases [database] --default-character-set=utf8mb4 --disable-keys --dump-date --extended-insert=FALSE --hex-blob --lock-tables=FALSE --no-autocommit --no-data --set-charset --order-by-primary --quote-names --routines --single-transaction --skip-quick --triggers -u [user] -h [server] > dump.sql

This works well and produces the output I want, with one exception. My dump is littered with this:

/*!40101 SET character_set_client  = utf8 */;
/*!50001 SET character_set_results = utf8 */;
/*!50001 SET collation_connection  = utf8_general_ci */;

I have read up on conditional comments, and as I understand it, this will always get executed on our clients/servers (Server Version: 5.6.46 - MySQL Community Server (GPL)). I don't understand why this is set to utf8 instead of utf8mb4, and would like it to reflect our proper charset.

None of the existing answers to this problem have helped me.

Edit: I originally had an incorrect version in my post

I have checked out mysql-5.4.46, and searched client/mysqldump.c for "SET character_set_client".

It finds

[:~/Source/mysql-server/client]↥ 9c3a49ec84b* ± grep 'SET character_set_client' mysqldump.c
          "/*!50003 SET character_set_client  = %s */ %s\n"
          "/*!50003 SET character_set_client  = @saved_cs_client */ %s\n"
                  "SET character_set_client = utf8;\n"
                  "SET character_set_client = @saved_cs_client;\n");
                "/*!40101 SET character_set_client = utf8 */;\n"
                "/*!40101 SET character_set_client = @saved_cs_client */;\n",
            "/*!50001 SET character_set_client      = %s */;\n"
            "/*!50001 SET character_set_client      = @saved_cs_client */;\n"

From this, it seems that some instances of "SET character_set_client" are fixed at utf8 in this version and are not even variable.

One of them is the /*!40101 SET character_set_client = utf8 */; line you quote as being faulty.

After checking out current 8.0 HEAD, I get

[:~/Source/mysql-server/client]↥ 8.0* ± grep 'SET character_set_client' mysqldump.cc
          "/*!50003 SET character_set_client  = %s */ %s\n"
          "/*!50003 SET character_set_client  = @saved_cs_client */ %s\n"
                  "/*!50503 SET character_set_client = utf8mb4 */;\n"
                  "SET character_set_client = @saved_cs_client;\n");
              "/*!50503 SET character_set_client = utf8mb4 */;\n"
              "/*!40101 SET character_set_client = @saved_cs_client */;\n",
        "/*!50001 SET character_set_client      = %s */;\n"
        "/*!50001 SET character_set_client      = @saved_cs_client */;\n"

So while there are still fixed SET character_set_client statements, they now default to utf8mb4. Note that all other statements are %s-ed, and further checking shows they defer to whatever you --default-character-set=utf8mb4 in your commandline.

My suggestion is you dump, possibly remotely, with a newer mysql 8.0 binary. In general, upgrading is adviseable, as 5.6 is an outdated unsupported version of the database (yes, I know, it's complicated. It always is.)


MS Access - protecting backend file I'm creating a split database in Access which has the backend on a drive accessible to all employees in the company at the moment. This isn't such a big deal at the moment because I'm still developing it and have a backup of the file.

The main reason I did this was because the protected drive I intended to use didn't allow read/write access and stopped my VBA code from executing.

As the database goes live it will be important that no one can move or delete my backend file, but I'm unsure how to approach this issue. Is there anything I can do to protect/hide this file to stop anything happening to it?

(Backing up will be done regularly regardless)

Thanks!

What about setting special permissions to the folder containing the backend file. This page has a table that might help.


Merge 2 columns and replace with specific output The setup is a Debian 8 with Postgres 9.4 (64bit if that matters).

Given following table I want to "search" for a combination of columns first and column sub and replace their output with another string.

CREATE TABLE public.rawdata (
 first integer,
 sub character varying COLLATE pg_catalog."default",
 value integer
)

Example data:

INSERT INTO rawdata VALUES
  ('1','A','5994'),('1','B','28525'),('1','C','18577'),
  ('2','A','30522'),('2','B','5238'),('2','C','18268'),
  ('3','A','982'),('3','B','13401'),('3','C','24158'),
  ('4','A','8544'),('4','B','31575'),('4','C','16661'),
  ('5','A','600'),('5','B','5242'),('5','C','8740'),
  ('6','A','2557'),('6','B','69'),('6','C','31572'),
  ('7','A','4212'),('7','B','26269'),('7','C','27918'),
  ('8','A','29821'),('8','B','22603'),('8','C','32578'),
  ('9','A','8027'),('9','B','13668'),('9','C','32000'),
  ('10','A','17673'),('10','B','11723'),('10','C','8937');

For example, the two column '1' and 'A' should read in the output as 'cat'.

There should be no alteration of the source data.

I have read about using WITH for that but I am uncertain how to join the columns so that I can convert '(1, A)' to 'cat' and '(2, A)' to 'dog'.

I only need the output where I have a defined "replacement name".

The end-result should look like that:

Name Value
Cat 5994
Dog 30522

Sadly, I am limited to providing the output that way (if possible at all).
Normally, I would write something like:

select * from rawdata where first < 3

1   "A" 5994
1   "B" 28525
1   "C" 18577
2   "A" 30522
2   "B" 5238
2   "C" 18268

But the next step is something I can't figure out.

Create a translation table, let's call it sub1_dict:

CREATE TABLE sub1_dict (
   first int NOT NULL
 , sub   varchar NOT NULL
 , name  text NOT NULL -- find a better name than "name"
 , CONSTRAINT t_pkey PRIMARY KEY (first, sub) -- INCLUDE (name) -- see below
   );

Enter all defined translations in this table.

Since you specified:

  I only need the output where I have a defined "replacement name"

... use an [INNER] JOIN in the query, excluding rows without translation:

SELECT s.name, r.value
FROM   rawdata r
JOIN   sub1_dict d USING (first, sub)
WHERE  r.first < 3;

The added PK should serve your purpose. The INCLUDE clause requires Postgres 11. The intention is to allow fast index-only scans - not important with small tables.

Same for your table rawdata: if it's big, add an index on (first, sub). Or a PK or UNIQUE constraint. Column first first to also serve your WHERE clause optimally. See:

Is a composite index also good for queries on the first field?

If you get index-only scans out of it, consider another covering index like above, with INCLUDE (name) in Postgres 11 or later.

Can Postgres use an index-only scan for this query with joined tables?


foreign key constraints on same table I have a table structure like this.

tblquestion
(
  nQuestionId1 INT UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT_NULL,
  nQuestionId2 INT UNSIGNED ALLOW NULL
);

Here..nQuestionId2 has a foreign key constraints to the nQuestionId1 on the same table i.e. tblquestion.

So..while restoring backup i am getting some warning and errors for foreign key on nQuestionId2..So,is it because of the foreign key reference to same table column??

Does this type of table structure can create any issues further?

Thanks

You will get errors if you restore a row that contains a value in nQuestionId2 that references a value in nQuestionId1 that has not been created yet.

There are a few ways to resolve this issue:

You could ensure that you create the data in the correct order.
You could disable the foreign keys before importing the data, then re-enable them after.
If disable/re-enable is not an option, you could drop/recreate them.
Alternatively you could split out the data so that the nQuestionId1 is in a separate table - this will allow you to restore all the nQuestionId1 values first.

I guess but the question really is about what you are trying to achieve.

I have seen designs like this that have a ID column and then a PARENT column that references the ID. In order to get the root parent you have to recurse through all of the parents of the object you are looking at. This means that even though a design like this may make sense logically, querying it could become quite difficult.

Database design is not just about mapping business rules and objects to tables and constraints, but also thinking about what is maintainable, how will the design hold up against large amounts of data, and being pragmatic about your approach.

I hope this helps you.


Query across multiple schemas, unknown until execution time I have multiple schemas that I need to query across. The schemas are not known in advance, for example:

U111
U222
U333

I need to select across these three schemas from a specific table in each of these schemas. Can I put this query across these three schemas? If so, how can I query across them? Remember, I will not know the schema names ahead of time, so they will need to be built dynamically.

If you just need results from multiple schemas, you can re-use the same query string and set the search_path in between:

SET search_path = u111, public;
SELECT * FROM foo;
SET search_path = u222, public;
SELECT * FROM foo;
...

The schema search path search_path in Postgres works much like the search path a file system. Related:

How does the search_path influence identifier resolution and the “current schema”
PostgreSQL and default Schemas

If you need to combine results from multiple schemas (probably your use-case), you can either build the statement in your client or use a plpgsql function with dynamic SQL and EXECUTE. That's what I would do. Plain SQL does not allow parametrized identifiers (schema, table, column, ...).

CREATE OR REPLACE FUNCTION foo(_schemas text[])
  RETURNS TABLE (bar int, baz text) AS  -- matching return type
$func$
BEGIN
   RETURN QUERY EXECUTE (
   SELECT string_agg(format('SELECT bar, baz FROM %I.foo', sch)  -- your query here
                          , E'\nUNION ALL\n')
   FROM   unnest(_schemas) sch
   );
END
$func$  LANGUAGE plpgsql;

Builds and executes a query of the following form dynamically:

SELECT bar, baz FROM u111.foo
UNION ALL
SELECT bar, baz FROM u222.foo
UNION ALL
SELECT bar, baz FROM u333.foo;

Schema names are escaped as identifiers properly to defend against SQL injection.

db<>fiddle here (returning query string as error msg instead of executing it)
Old sqlfiddle


How to make MySQL 5.1 connection ignore kills? According to MySQL 5.1's manual, section 13.7.6.4 KILL Syntax, when you kill a connection, a thread-specific kill flag is set for the thread. The flag will then be checked from time to time, even during some long-running queries, so that it can interrupt the query.
Is there a way to instruct a thread not to check the kill flag while inside some kind of delimited critical section?
What I'm looking for is something like:
BEGIN IGNORE_KILLS;
-- if this thread is killed here, it will simply ignore it
END IGNORE_KILLS;
-- here, the thread would be able to tell if someone tried to kill it previously

Assuming that it does not exist, or that someone could offer me a better solution to my problem, let me explain why would I want it.
I have an application that must react to lines that are inserted in a table, and this must be scalable. According to MySQL expert Baron Schwartz, one should not keep polling the database, and he proposes, as an alternative, to use SELECT SLEEP(...)/KILL.
The problem is that after the connection waiting on select sleep(...)  is notified (by being killed) that it has work to do, I'd like to guarantee that, during its work (what I called critical section above), it won't stop doing what it should do even in the case that the killer thread goes crazy and kills it again.

I might have solved my problem...

I was afraid that 2 different killer threads could: (1) show processlist, (2) choose the same connection to kill, (3) then both would kill it.

The first kill would trigger the execution of whatever I wanted, while the second kill could potentially blow things up.

The easiest, and nearly obvious, solution that I missed is to look at the other side of the problem: instead of making the worker thread unkillable, the killer threads must cooperate: the killer threads would (1) select get_lock('killer_thread'), then only after acquiring the lock they would (2) show processlist, (3) kill the chosen threads and finally (4) select release_lock('killer_thread').


SQL Server (Restore Options Page) Hello,
kindly can anybody tell me what is the difference between the 2 options (Recovery State and Leave Source Database in the restoring state (WITH NORECOVERY)), - see the snapshot below please - and When I have to check the option : Leave Source Database in the restoring state (WITH NORECOVERY)?
any help please,
Thanks in advance

When you restore a database you have the option to keep restoring additional files.  Such as if you are going to restore a FULL backup and then additional LOG or DIFF files to bring the database up to date.

WITH NORECOVERY - Leave the database in a state that allows additional files to be restored.

WITH RECOVERY - Brings the database online and ready for transactions.

If you use the WITH NORECOVERY switch, you will need to issue the RECOVERY command yourself, either with the last restore or in a separate command "RESTORE DATABASE [dbname] WITH RECOVERY;"


Advice on Measures that have properties on their own I'm looking for advice on how to model a database when the measures have an increasing number of properties - the values of which could be considered dimensions in their own right.

In a simplified case, let's say I have StateId and Sales:

StateId    Sales
----------------
1          20
2          25

Then later I need to store information about what type of measure 'Sales', a custom group it needs to be in, and say, an aggregation rule.

What would be a good way to model this? I could have a Measure table which links to other dimensions that contain Types and Groups.

Id  Name    TypeId  GroupId    Agg
-----------------------------------
1   Sales   1       5          Sum

But then I'd need to store what type of dimension it was, whether it relates to a real dimension or one that relates to a measure.

Curious as to how others have solved the problem of storing meta-data for a measure.

It sounds like you want to put aggregation rules (based on anything, doesn't matter) in the database.  Nothing particularly wrong with that, unless you're planning to use it to generate SQL dynamically.  That's a slippery surface: proceed with caution, because SQL is much easier to debug when you can see it.

Will the users be able to add to your putative Measure table?  If not, you might want to consider statically generating the SQL as stored procedures.  That would be easier to understand, both for the human being and likely for the query optimizer.

I'm also a little leery of your question because ISTM you're not quite sure yet what the properties of the measures will be.  In such a situation, it's often best to wait until a dozen or so use cases develop.  At that point, you'll have a concrete set that you can actually use, with identified properties.  Then you'll know enough that I expect the question will answer itself.  It wouldn't surprise me, though, if that many cases never do develop, in which event the Measure abstraction will have proved more trouble than it would have been worth, and was fortuitously avoided.

HTH.


how to integrate ola hallengren solution with log shipping? I need to set up log-shipping from my SQLPROD2 to server2

but I am facing the following problems:

1) I already use ola hallegren backup solution, and it provides the 15 min log backups for all databases - can I use this job instead of the job the log shipping wants me to use?

or alternatively should I remove the databases that I add to the log shipping from the Ola's user database backup job?

2) what about the full backups that I take once a day?
would that mess up the log shipping? should I do a full backup with copy_only?

If you are using Ola's solution, its intelligent to exclude log shipping dbs. See FAQs

Does the SQL Server Maintenance Solution support log shipping?
The SQL Server Maintenance Solution supports log shipping. Full and differential backup, integrity check, and index and statistics maintenance are performed on the primary database. Transaction log backups are not performed on databases that are involved in log shipping.

.

what about the full backups that I take once a day? would that mess up the log shipping? should I do a full backup with copy_only?

A full backup wont break log backup chain. Only an additional NON COPY_ONLY log backup will break the log chain.
If you have folks taking adhoc log backups, you can DENY BACKUP LOG to [user|group]


SELECT DISTINCT with another table I want to use distinct to display my data.
But I don't know how to do it.This is my code.I want to display table gtest_version field max(poly_version)

select distinct on (rule_name)
rule_name,rule_version,polyname,state
from rule
order by rule_name,rule_version DESC

If I understand your requirements correctly, you want to join the other table on polyname and then get the max() of poly_version, like

SELECT rule_name, rule_version, polyname, state, max(poly_version)
  FROM rule r JOIN gtest_version g ON r.polyname = g.polyname
 GROUP BY rule_name, rule_version, polyname, state
 ORDER BY rule_name, rule_version DESC;

However, your query and description does not tell how you want to pick the right row from the rule table.  My query will return all of these, while your DISTINCT ON will not...


How do we get an hour, minute and second difference between two datetimes? The table Task has columns as:
Task(TaskID, TaskName, CreatedDate)

And want to execute code block based on datetime difference as:

IF Task CreatedDate has passed 15 Minute THEN

Execute block #01

ELSE IF Task CreatedDate has not passed 15 Minute yet THEN

Execute block #02

I tested below query but it gives result as 61633440
SELECT DATEDIFF(MINUTE, CONVERT(VARCHAR(10), CreatedDate, 108), CURRENT_TIMESTAMP) AS MinuteDiff

How could we achieve above in sql?

CURRENT_TIMESTAMP returns the current datetime something like 2017-03-09 15:19:53.770.

CONVERT(VARCHAR(10), CreatedDate, 108) returns a string with only time 15:19:53.

The second parameter of DATEDIFF takes a datetime so your string is implicitly converted to a datetime. In lack of something better to use for a date SQL Server uses 1900-01-01 so your second parameter ends up to be 1900-01-01 15:19:53.000 and the function returns the number of minutes since the beginning of the previous century.

Remove the convert in the second parameter and it should work just fine for you.


Run a SQL statement in MySQL on each connection I must be going nuts here, but I could swear I read about a configuration option in MySQL that will run the defined SQL statement on every successful connection to the database, with the cavet that it would not run for root or those with a certain privilege to prevent shutting the server down with a bad sql statement here.

Was I imagining things or do folks know of such a parameter?

I have dug though the MySQL documents and so far I'm not turning up with any success.

Note, this is NOT for the client supplied sql statements, but instead for a statement that the server itself runs regardless of who connects and for what task, and great for tracking who connects with what account if general_log was not enabled for some reason or special handling is required.

Found it.

The parameter is named init_connect and will run the defined sql for each connection, except for those with the super privilege. No result sets are produced by this though so selects are not recommended for this.

The big cavet I see here though is that access to the objects is based on the connecting user's permissions, which make sense.. though it does make an 'autorun' style stored procedure harder to implement as that procedure needs to be accessible to everyone, which could cause problems if it's written with the expectation to be executed only once per connection.


What does the word "SARGable" really mean? SQL Server users use the term "sargable". I'm wondering if there is an objective implementation-agnostic timeless definition for "sargable."

For instance, WHERE foo LIKE '%bar%' is said by many to be not sargable, but some RDBMSs are able to use indexes on such queries. What then does "not sargable" mean?

Other References

What makes a SQL statement sargable?

The term "sargable" was first introduced by P. Griffiths Selinger et al. in their 1979 paper "Access Path Selection
in a Relational Database Management System", published by ACM. For non-ACM members there's a copy of that paper at http://cs.stanford.edu/people/chrismre/cs345/rl/selinger.pdf

The term is defined in this paragraph:

  Both index and segment1 scans may optionally take a set of predicates, called search arguments (or SARGS), which are applied to a tuple before it is returned to the RSI2 caller. If the tuple satisfies the predicates, it is returned; otherwise the scan continues until it either finds a tuple which satisfies the SARGS or exhausts the segment or the specified index value range. This reduces cost by eliminating the overhead of making RSI calls for tuples which can be efficiently rejected within the RSS. Not all predicates are of the form that can become SARGS. A sargable predicate is one of form (or which can be put into the form) "column  comparison-operator  value". SARGS are expressed as a boolean expression of such predicates in disjunctive normal form.

In other words, a sargable predicate is such that can be resolved by the storage engine (access method) by directly observing the table or index record. A non-sargable predicate, conversely, requires a higher level of the DBMS to take action. For example, the outcome of WHERE lastname = 'Doe' can be decided by the storage engine by simply looking at the contents of the field lastname of each record. On the other hand, WHERE UPPER(lastname) = 'DOE' requires execution of a function by the SQL engine, which means the storage engine will have to return all rows it reads (provided they match possible other, sargable predicates) back to the SQL engine for evaluation, incurring additional CPU costs.

You can see from the original definition that sargable predicates can apply not only to index scans, but also to table (segment in System R terminology) scans, as long as the conditions "column comparison-operator value" are met and therefore they can be evaluated by the storage engine. This is indeed the case with Db2, a descendant of System R in many ways:

  Index sargable predicates are not used to bracket a search, but are evaluated from the index if one is chosen, because the columns involved in the predicate are part of the index key. These predicates are also evaluated by the index manager.

  Data sargable predicates are predicates that cannot be evaluated by the index manager, but can be evaluated by Data Management Services (DMS). Typically, these predicates require the access of individual rows from a base table. If necessary, DMS will retrieve the columns needed to evaluate the predicate,

The fact that in SQL Server-speak sargable predicates are only those that can be resolved using index seeks is probably determined by its storage engine's inability to apply such predicates during table scans.

Sargable and non-sargable predicates are sometimes described as "stage 1" and "stage 2" predicates respectively (this also comes from Db2 terminology). Stage 1 predicates can be evaluated at the lowest level of query processing, while reading table or index records. Rows that match stage 1 conditions, if any, are sent to the next level, stage 2, of evaluation.

1 -- Segment in System R is the physical storage of a table's tuples; a segment scan is somewhat equivalent to a table scan in other DBMSes.

2 -- RSI -- RSS3 Interface, a tuple-oriented query interface. The interface function relevant to this discussion is NEXT, which returns the next row matching query predicates.

3 -- RSS, or Research Storage System, the storage subsystem of System R.


Performance Order By large table with Join Hello I have a table with 206,902 records and I am struggling to improve my query:

SELECT TOP (50) [ID]
      ,[Title]
      ,[Modified]
      ,[Created]
  FROM [Data]
 Inner Join [Permission]
    On ([Data].[ID] = [Permission].[FichaID]
   AND ([Permission].[PermID] = 1 AND [Permission].[IsGroup] = 0
        OR [Permission].[PermID] IN (46, 50, 53) AND [Permission].[IsGroup] = 1
        ))
Group By [ID]
      ,[Title]
      ,[Modified]
      ,[Created]
   Order By [Created] desc

The query is running fast but using the tool SQLQueryStress with 50 iterations complement all time is 3 minutes.
If I remove the order by the query takes to run less than a second.
I have created an index on the table Data:

CREATE INDEX facervo_created ON [Data] ([Created] desc);
CREATE INDEX facervo_status ON [Data] ([_ModerationStatus]);
CREATE INDEX facervo_expiration ON [Data] ([Expiration]);
CREATE INDEX facervo_reserva ON [Data] ([Reserva]);

Index on the table Permission:

ADD CONSTRAINT perm_key PRIMARY KEY (FichaID, PermID, IsGroup)

Still not had great success in performance.
It is possible to execute this query faster?

EDIT

I removed all the index of the two tables, and created this new index:

CREATE NONCLUSTERED INDEX [ix_include1]
ON [Data]([Created] Desc)
INCLUDE ([ID]
        ,[Title]
        ,[Modified]);

Now this query taking 16 seconds, an evolution.
Is it possible to improve the most?

This is a classic performance problem. You are ordering on the columns of one table and are filtering on the columns of another.

SQL Server can either scan Data and filter out rows by seeking into the other table (the current plan). Or it can access Permission to determine the Data rows that pass the filter, and then sort this set. Both plans require a lot of data to be touched because the filter is too selective for plan 1 and to little selective for plan 2.

There is no index that is able to cover both the sort and the filter because the columns belong to different tables.

Consider creating an indexed view that contains all the columns used here. This will result in near-perfect performance because you can now index all required columns.

Or, denormalize your data model. Store permission information directly in the Data table.


Replication fails with "Transaction log for database is full due to LOG_BACKUP" Replication is failing with the subject error.

The log_reuse_wait_desc states LOG_BACKUP.

I have performed both database and log backups.

After changing the recovery mode to single, performing backup, and then changing recovery mode back to FULL, log_reuse_wait_desc goes to NOTHING.

As soon as I start the replication jobs they fail with the subject error.

I did shrink on log file to no avail.  Any suggestions to resolve this?

there is no clear indication of what may be causing this upsetting in the transaction log.

but I would check it out what is happening in the distribution.

run the following procedure:

sp_helpdistributor

that will show you where the distribution database and server is.

Assuming the name of the distribution database is distribution:

 SELECT * FROM distribution.dbo.MSrepl_Commands

have a look at what you get and use the last transaction to see what is in it:

check it out:

 SELECT * FROM distribution.dbo.MSrepl_Commands
--- commands for all transactions for replication for seqno or all
  WHERE   xact_seqno = 0x000002470000F7D30021

if all good or at least nothing obviously bad:
use that xact_seqno here (I did not use the end one on this example - but if your replication is very busy you can limit it):

    exec distribution.dbo.sp_browsereplcmds @xact_seqno_start =  '0x000002470000F7D30021',  -- returns all/seqno info (sql query) on replication
   -- @xact_seqno_end  =  '0x00001C030000F4540018'

This allows you to see what has been replicated - what commands to each article (see on the right hand side of the picture below)

This is not a comprehensive answer but would give you a start.
Well after the obvious thing as looking at the replication monitor.

Check these scripts to give you some extra vision.

You can also check Some useful commands and sp for Transaction Replication