Setting up relationships on a gaming schema with multiple locales I've devised the following schema:

Where BotLocale holds locales like en-US, es-AR, etc. GameArtifact has a primary key which is artifact_id and GameClass with locale, identifier being the primary key.

How can I properly set up a relationship between the exclusive column of GameArtifact and identifier from GameClass taking into account that identifier will be repeated multiple times per row due to many locales?

This is how GameClass table looks like:

 id | locale | identifier  |      class
----+--------+-------------+------------------
    | en-US  | knight      | Knight
    | es-AR  | knight      | Caballero

One approach I thought for doing this is to separate GameClass into two tables, one which holds the identifier alone which would be referenced from GameArtifact and another table which holds the actual translations for a GameClass.

The issue in this approach would be that there's an extra table I'd need to JOIN when querying.

Is this the best way to achieve this or there's a better approach when handling this type of cases?

Your approach is the correct one if the typical workload consists of small transactions that access only a few rows at a time (OLTP).

The extra join won't hurt, because it will be a nested loop join. Databases are optimized for stuff like that.

Any small performance loss will be outweighed by the advantage of having foreign keys to guarantee data integrity.


Creating partitions on a production database I have a production MySQL 5.1 database that is running well but I want to improve query performances. I have never used Partitions and just going through the manuals.

I have two tables that involve a composite key on columns:

Bill_Num
Bill_Date

I want to create Partition on Bill_Date. The table consists records of four years. I want to know how a new partitioned table will accommodate future years? I also want to know whether I need to make changes to the table name in existing queries and replace the table name with the new partitioned table name?

I want to know how a new partitioned table will accommodate future years?

I'm fairly sure, according to the 5.1 documentation, that you'll have to alter table add partition to handle future years.

  I also want to know whether I need to make changes to the table name in existing queries and replace the table name with the new partitioned table name

Are you needing to use the 'old' table after you get this set up? If not, I would follow these steps:

Create partitioned table as you want it, under table name 'mytable_new'
INSERT INTO mytable_new SELECT * FROM mytable
RENAME TABLE mytable TO mytable_old
RENAME TABLE mytable_new TO mytable

This should avoid the need to alter your queries. If your table is extremely write-heavy (I assume it must be if you have the need to partition it!), you will probably want to have a maintenance timeframe where it's offline until the operation is complete.

Hope this helps.


What is the impact of changing the index creation memory setting in SQL Server? I am trying to create some indexes a SQL Server 2008 database but am getting the following error message.

  Msg 8606, Level 17, State 1, Line 1
  This index operation requires 1024 KB
  of memory per DOP. The total
  requirement of 2048 KB for DOP of 2 is
  greater than the sp_configure value of
  704 KB set for the advanced server
  configuration option "index create
  memory (KB)".  Increase this setting
  or reduce DOP and rerun the query.

Currently the index creation memory setting is set to 794KB and the minimum memory per query is set to 1024KB. What would be the impact of changing these values? Especially with regards to performance. Should I change them, create the index and change them back or should I set it to 0?

Basing my assumptions off this question, your error message seems to indicate to me that you need to set "index create memory" to [1024 * DOP].  The error also seems to indicate that your DOP is set to 2, so if I were you, I would set index create memory to 2048KB(as stated in the error message) and see what happens.

If that doesn't work, I would try reducing the minimum memory per query and setting index create memory to [minimum memory per query * DOP](assuming you don't change DOP).


PostgreSQL sometimes fails to detect serialization failures I have an http-server in application language that looks roughly like this:
item = SELECT item FROM table WHERE field = 'value'
if (item) {
  UPDATE another field on item
} else {
  INSERT item VALUE field = 'value' and so on
}

The field has unique constraint. About once every several minutes two requests with the same value come in at the same time. They both execute this same code so of course there is a race condition. I decided that if I wrap it in a transaction with SERIALIZABLE isolation level and retry on every serialization failure then the problem is solved. And on localhost it worked fine. I indeed see serialization failures thrown with error code 40001 as expected.
However on production in a database under load for some reason a Unique constraint violation is sporadically thrown with a code of 23505. It only happens sometimes. I put a lot of effort to  reproduce it on localhost but failed. Everytime I get a normal 40001. I tried to run server code in separate processes and to put delays at different points to enforce certain orders of execution. It only happens on production. So I added extensive logging to prod. The logs say that every time when a problem occurs the item is not found at first but when INSERT happens it triggers Unique constraint violation. So it looks just ad a race condition.
But why sometimes under some conditions Postgres SERIALIZABLE transactions don't detect it as they should? Or how to debug it? It happens like several times in a day out of several dozens of cases.
The (almost) exact SQL look like this:
(I omitted detailed fields lists and exact parameters)
START TRANSACTION
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

SELECT *
FROM products
WHERE product_code = $1
LIMIT 1 -- product_code is a parameter

SELECT *
FROM licenses
WHERE license_key = $1
LIMIT 1 -- license_key is another and it's unique across the table
-- At this point SELECT did not return anything but it was inserted before this INSERT by exactly the same transaction

INSERT INTO licenses ("license_key", "product_id", "other_data",...)
VALUES ($1, $2, $3,...) RETURNING "id"
query failed: error: duplicate key value violates unique constraint "licenses_license_key_key"

Doing this in a serialized transaction should work, unless there is a bug.
As a workaround, use this command, which tries to add a new license; if the license key already exists, it adds the new hours to the existing value:
INSERT INTO licenses(license_key, product_id, hours)
VALUES ($1,
        (SELECT product_id FROM products WHERE product_code = $2 LIMIT 1),
        $3)
ON CONFLICT (license_key) DO UPDATE
SET hours = hours + EXCLUDED.hours
RETURNING id;

documentation


Foreign key of multiple tables I have the following 3 tables:

╔═════════════════╗
║   Companies     ║
╠═════════════════╣
║ id              ║
║ name            ║
║ type            ║
║ etc...          ║
╚═════════════════╝
╔══════════════╗
║   Contacts   ║
╠══════════════╣
║ id           ║
║ company_id   ║
║ first_name   ║
║ last_name    ║
║ etc...       ║
╚══════════════╝
╔═══════════════╗
║   Addresses   ║
╠═══════════════╣
║ id            ║
║ foreign_key   ║
║ address_1     ║
║ address_2     ║
║ city          ║
║ state         ║
║ zip           ║
║ etc...        ║
╚═══════════════╝

My problem is that both Companies and Contacts can have multiple addresses associated with them. Their ID numbers might be overlapping. What would I store in the foreign_key column in the addresses table?

You have couple options. I'd describe one I like the most, but you can find others searching for 'resolving polymorphic association in database'.  Add a common table for companies and contacts (I'd prefer to rename contacts to people or persons), say party. Thus, companies and contacts will have FK to party. Then you add link table party_address(address_id,party_id) with FKs to addresses and party respectively.

UPDATE
For instance (I know it's oversimplified, normally you may have different address_type, address change history, etc, but I guess it illustrates idea).

Note: enum is used because mysql still doesn't have check constraints. Unique constraint on (party_id, party_type) added so child tables can have a foreign key references to it; thus, optional relationship implemented and enforced on database level- Party can be either Person or Organization; no way it can be Person and Organization at the same time.

  CREATE TABLE PARTY (party_id int not null auto_increment primary key,
party_type enum('person','organization') not null,
CONSTRAINT UQ_PARTY UNIQUE(party_id,party_type));

CREATE TABLE PERSON (party_id int not null primary key,
party_type enum('person') NOT NULL DEFAULT 'person',
....
CONSTRAINT FK_PERSON_PARTY FOREIGN KEY(party_id,party_type)
 REFERENCES PARTY(party_id,party_type));

CREATE TABLE ORGANIZATION (party_id int not null primary key,
party_type enum('organization') NOT NULL DEFAULT 'organization',
....
CONSTRAINT FK_PERSON_PARTY FOREIGN KEY(party_id,party_type)
 REFERENCES PARTY(party_id,party_type));

CREATE TABLE ADDRESS(address_id INT NOT NULL auto_increment PRIMARY KEY,
.... );

CREATE TABLE PARTY_ADDRESS (party_id INT NOT NULL, address_id INT NOT NULL,
CONSTRAINT PK_PARTY_ADDRESS PRIMARY KEY(party_id,address_id),
CONSTRAINT FK_PARTY_ADDRESS_PARTY FOREIGN KEY (party_id) REFERENCES PARTY(party_id),
CONSTRAINT FK_PARTY_ADDRESS_ADDRESS FOREIGN KEY (address_id) REFERENCES ADDRESS(address_id));


How to monitor for MySQL select queries that return too many records? I realize there are simple ways to limit results in SQL. I have a legacy implementation. I would like to get a handle on select queries that return results exceeding some user defined limit. The goal would be to have better insights to the kind of queries sent to the database. Some of these queries can translate into performance issues in the code that processes the results and/or excessive heap consumption.  To gain insights, I'd like to monitor a production databases with a log entry for any query exceeding a threshold. Configuring an alarm/alert would even be better. The solution needs to be one that can be deployed to production. I'm not looking for a QA or Performance tool solution.

SUGGESTION #1 : STATUS VARIABLES
You could check the status variable
SHOW GLOBAL STATUS LIKE 'Innodb_rows_read';

or read it from the performance_schema
SELECT variable_value FROM performance_schema.global_status
WHERE variable_name = 'Innodb_rows_read';

You could see other row metrics as well:
mysql> select version(); show global status like 'InnoDB_rows%';
+-----------+
| version() |
+-----------+
| 5.7.26    |
+-----------+
1 row in set (0.00 sec)

+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Innodb_rows_deleted  | 0     |
| Innodb_rows_inserted | 0     |
| Innodb_rows_read     | 8     |
| Innodb_rows_updated  | 0     |
+----------------------+-------+
4 rows in set (0.00 sec)

mysql>

SUGGESTION #2 : SLOW LOG
If you need to see the number of rows returned or examined by any given query, you will need to enable the slow query log.
Here is an example of a slow log entry that sees a mysqldump in progress
# Time: 150419  6:00:43
# User@Host: web[web] @ localhost []
# Query_time: 7.730519  Lock_time: 0.000070 Rows_sent: 167620  Rows_examined: 167620
SET timestamp=1429416043;
SELECT /*!40001 SQL_NO_CACHE */ * FROM `messages`;

As shown, the number of rows returned and examined are in the header.


Does Snapshot Isolation only give you what has been commited? (And General Snapshot Isolation questions.) So, I have some deadlocking issues.

I have seen two options to resolve it.  Add read uncommitted to my db or do Snapshot Isolation and add read committed snapshot.

After doing some research into both, it seems to me that read uncommitted can allow reads of data that could possibly never get committed to the database.

On the flip side read committed snapshot will give only the latest (it is the latest right?) committed version of data (even if the data that may be in the middle of a change/transaction.)

Is this right?

If so, I am leaning towards Snapshot Isolation (I don't really want to return data that is in the middle of a change.)

However, my DBA told me that last he heard there were "problems" with Snapshot Isolation.  (He did not expand on the the problems were, just that it was not a Enterprise ready feature.)

So, here are my questions on Snapshot Isolation:

Is Snapshot Isolation robust?  Does it just work?

Or are there "gotchas" that I need to look out for?

On the flip side read committed snapshot will give only the latest committed version of data

Incorrect. Snapshot will give you the data that was committed at the moment the snapshot was taken. This moment means either when you issued BEGIN TRAN if you use true SNAPSHOT isolation level, or the moment your statement started if you use read_committed_snapshot RCSI. That means that if multiple updates commit, on the same record, you will not see the latest, but instead the one that was committed when your 'took the snapshot'. Think of it a a photo: you take a picture of the data and from then on you can only see the picture, no matter how the 'real' scenery changes behind.

Snapshot is absolutely robust. If your DBA has objections, then he/she should be able to articulate them so that they can be addresses or dismissed, as is the case. Everything has trade offs and gotchas. Indexes have trade offs and gotchas, does that mean your DBA does not use indexes?


Utilizing a single core to 100% doesn't show in perf mon as single cpu being fully utilized Ever since Windows 2008 I've noticed the following behavior:

I'm using a windows 2008R2 VM with two vCPUs but this also works on a physical server.
I'm using SQL server 2008 R2 for this example.

Make sure you have processor affinity set to AUTO (the default):

ALTER SERVER CONFIGURATION SET PROCESS AFFINITY NUMANODE = 0

(takes affect immediately)

Run a query that will utilize a single core at 100%:

DECLARE @c INTEGER

WHILE 1=1
BEGIN
    SET @c=@c+1
END

Now, open Windows Performance Monitor and add the counters:

Processor: % Processor time _Total
Processor: % Processor time 0
Processor: % Processor time 1

In my pictures, _Total is highlighted (black) and both seperate CPUs are red and green.

What I would expect to see is, one of the cores at 100%, another near 0% and the _Total around 50%.

But instead I get this:

It seems as if the Windows OS Scheduler tries to divide the load across both cores by alternating the task between cores.

Set the processor affinity explicitly to use all cores. From a SQL cpu perpective everything is still the same. SQL Server still has goth both vCPUs are both usable.

ALTER SERVER CONFIGURATION SET PROCESS AFFINITY NUMANODE = 0

When I run the same query I get the graph that I was expecting:

For the most part of the time, the task is running on one core only. In this case the green one.

This last graph is basically how it was in pre windows 2008.

Questions:

What's happening under the hood in the first scenario?
Why is this behaviour changed once I explicitly tell SQL Server that my processor affinity is to use all cores. Instead of leaving it on auto.
In the old days it was very easy to spot a query that was limited by a single core by opening up perf mon and look at all separate cores and find one that was utilized at 100%. Any alternatives to do that now?

1: The scheduler is moving the load between cores. This is a known behavior.

2: Because the processor affinity is actually a core affinity and you can not move a task away from a core if that is the only core allowed to run it.

3: Not really. Things get a lot more complex because of parallelization anyway. In general, SQL Server - unless you do very complex analysis - is a lot more memory / IO bound than CPU bound. Now, with SSD you can actually overcome this - but mostly I dont see that.

You can find that a query uses a lot of CPU - which is always something nice to optimize - but not necessarily whether it is single core bound.

If you need that, maybe an instance configured to use only one core may work. I mostly find that to be of limited use - because in large scenarios, as I said, you either are not CPU bound or with paralellism not single core bound.


powershell problem when running on a remote server - access is denied as a follow up from this question below:

Run a PowerShell script on a different server - from inside a SQL Server job

there you can see that for me tu run a powershell script from inside a sql server job, I need to create a proxy.

Both the powershell script and the proxy script are on that link.

what the powershell script does is to delete old backup files on a remote server.

the question that I still have is:

for the whole thing to work on windows server 2012, I could find not other way than granting Administrator on the remote box to the Proxy account.
as you can see on the picture below.

another alternative was to
create a powershell script that do both, backups and deletes, and create a sql server job with the following code:

Is this a powershell limitation when running on a remote server?
Are there other ways to work around it, without having to grant admin to the proxy account?

PowerShell Basics: Managing Remote Servers & Sessions

The account being local Administrator is a requirement for using the Enter-PSSession:

  To connect to a remote computer, you must be a member of the Administrators group on the remote computer.

As far as workarounds, I did find this answer on SU. I would read the article in detail that the answer points to fully understand, but this is the main command to adjust permissions:

  If you decide you want to allow others, what you do is run the
  command:

Set-PSSessionConfiguration -Name Microsoft.PowerShell -showSecurityDescriptorUI

  Notice that this action could have a serious impact on your system so
  we ask you to confirm that you really want to do this.


what mistake i did in mysql query I am getting error

#1064 - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '"insert into all_booking(name,address,event_type,hallNo,arrival_time,arrival_da' at line 1

Query I tried

insert into hall_booking(name,address,event_type,hallNo,arrival_time,
arrival_date,dep_time,dep_date,identity,emailid,
contact,total_members,desc,catering,service,
decoration,other)   values
('$nm','$add','$typ','$roomno','$arrv',
'$arrivaldate','$departure','$dDate','$idt','$emailid',
'$cont','$desc','200','200','200',
'200')"

Use Backtick Sign  around columns names. As you have a column desc which is a keyword in MySQL, So you need to put Backtick around column the name

insert into hall_booking
(`name`,`address`,`event_type`,`hallNo`,`arrival_time`,
`arrival_date`,`dep_time`,`dep_date`,`identity`,`emailid`,
`contact`,`total_members`,`desc`,`catering`,`service`,
`decoration`,`other`)
values
('$nm','$add','$typ','$roomno','$arrv',
'$arrivaldate','$departure','$dDate','$idt','$emailid',
'$cont','$desc','200','200','200',
'200')

And one more thing in your insert you are missing value for one column, as you have 17 columns to insert but you are inserting only 16 values.

I hope you are missing value for column total_members,

Your insert should be like below

insert into hall_booking
(`name`,`address`,`event_type`,`hallNo`,`arrival_time`,
`arrival_date`,`dep_time`,`dep_date`,`identity`,`emailid`,
`contact`,`total_members`,`desc`,`catering`,`service`,
`decoration`,`other`)
values
('$nm','$add','$typ','$roomno','$arrv',
'$arrivaldate','$departure','$dDate','$idt','$emailid',
'$cont','$total_members','$desc','200','200','200', /** Added total_members **/
'200')


Erro MYSQL #1193 - Unknown system variable 'lc_messages' Can anyone help me with this error. From several searches on the Net I have not found a solution. I'm using version 7.0.10 of PHP and when I try to log in to phpMyAdmin, the following message appears:

Erro
Comando SQL: Edita Edita
SET lc_messages = 'pt_PT';
Mensagens do MySQL : Documentação
#1193 - Unknown system variable 'lc_messages'

One other detail is I have installed Workbench version 6 and MySQL 5.1.

SET lc_messages = ... doesn't work in MySQL Server 5.1.  This variable was introduced in 5.5.

  Before MySQL 5.5, the lc_messages_dir and lc_messages system variables were unavailable.

  To start mysqld with a particular language for error messages, the --language or -L option were used. The option value can be a language name or the full path to the error message file.

  https://dev.mysql.com/doc/refman/5.5/en/error-message-language.html


exclusive lock mysql I want to generate the next enroll_no.  It is incremented by group for each institute_id and every new institute has to start enroll_no at 1. When an institute has multiple branches then the problem happens.

If we fetch MAX(enroll_no) and then increment 1 and insert and at the same time branch 2 fetches the same MAX(enroll_no), then after incrementing they both send same enroll_no to the server.  This creates a duplicate enroll_no for the institute_id.

I need them to be exclusive and possibly use a lock there but i don't know how to go about that.

I'm making assumptions here based on the available data.  I do not know your table name so I'm going to assume it's called institute_enroll_table for now.  I also called your insert variables by fake names myid and my_instutute_id since I don't know what your current insert looks like.

The easiest way to fix this is going to be to do the MAX(enroll_no) in your INSERT query rather than do it as a separate query.  The second INSERT will wait on the first and therefore will get a different MAX.  The COALESCE makes the value 1 when there is no record found.

INSERT INTO institute_enroll_table(id, institute_id, enroll_no)
SELECT myid, my_institute_id,
    COALESCE(
        (SELECT MAX(enroll_no)+1 FROM institute_enroll_table ie WHERE
ie.institute_id=my_institute_id)
     ,1)AS enroll_no


Risk in creating a string domain In an effort to more easily migrate from Visual Basic to plpgsql I'd like to create a string as a domain in Postgres. Is there any inherent risk in doing so?

CREATE domain string AS TEXT;

I ask because of some c process or perhaps string is reserved?

Yes you can create create a domain/type as a work around  but I caution against this

All future upgrades have to maintain this new datatype that added no new feature only overhead.

Converting Access/MSSql SQL queries and VB scripts to PLpg/SQL is a complete rewrite there is no way around that..

There are enough subtle/major difference between MS implementation of SQL and every other implementation to make it not work with any other database at all.

Only the most simplest queries will can be reused

Select * from sometable

but MS typically generates

Select * from [schema].[table]


Error while Creating Stored Procedure I'm trying to create a stored procedure. Once I created it, it was successfully created. But it is not displaying in the list of stored procedures. I refreshed the list, but it is not appearing. I also tried to recreate it, but there was an error:

  Msg 2714, Level 16, State 3, Procedure spProducts_DeleteProduct, Line 6
  There is already an object named 'spProducts_DeleteProduct' in the database.

What is the reason behind not seeing that stored procedure in the list?

You need to run ALTER PROCEDURE to change a stored procedure definition. See docs here.

You also need to make sure you're using the correct database. I bet if you look in master, it'll be there.

In the meantime, put USE [YourDatabaseName] at the top of your code.


Can SQL Server full-text search tokenize words within words? Suppose I have the following schema and data in SQL Server 2012:

create table Exclamations
(
    ID int not null primary key identity(1,1),
    Exclamation nvarchar(150) not null
);

insert into Exclamations(Exclamation) values
    ('Ohmygosh!'),
    ('Ohmygoodness!'),
    ('It''sabird!'),
    ('It''saplane!'),
    ('Whattheheck!'),
    ('Lookoutbelow!'),
    ('Whatintheworld?'),
    ('Isheforreal?'),
    ('Takeahike!');

create fulltext index on Exclamations(Exclamation) key index ID;

And suppose I want to do full-text queries against my exclamations, but I want to be able to query for words within words using a high speed index. Is that possible? Perhaps with a custom dictionary, or custom word-breaking/boundary behavior?

For example, I want to be able to query for the word "out" and have it quickly locate row #6 ('Lookoutbelow!') without having to scan through all the rows using a LIKE-ish operator. This also implies some false positives, for example, suppose I want to query for the word "he" and find row #8 ('Isheforreal?'); the query would also return row #5 ('Whattheheck!') since it contains the same "he" substring. That's OK.

Note that I do not need the tokenizer to index every possible combination of characters. I don't mind if nonsense words like 'sapl' within row #4 are not indexed. I only need it to tokenize the words it already recognizes (and possibly some additional words that I would add, but that is only a nice-to-have.)

It is important to specify that for the purposes of this question I cannot pre-parse the exclamations to insert obvious tokenizing boundaries such as spaces, hyphens, or periods, before inserting into SQL Server. I want to know if SQL Server can somehow do that itself.

EDIT: After some more reading, it appears that what I want is generally called N-gram tokenizing. But I want it limited to tokens that are already present as, or variants from, words in the dictionary.

Looks like there is no such feature in SQL Server.


SQL Server 2008 R2: (OUTER APPLY) Why is SQL scanning all rows instead of the rows I specify? I have a reasonably large ETL table (170M rows, 40GB).

The clustered index is the EVENT_DTTM.

Every day a new batch of data is inserted and processed. Part of the processing is updating each row (only the newly inserted ones), based on other rows in the table. (e.g. compare the datetime of each row to the row before it and calculate "event time"; detect row with malformed data and use data from nearby rows to correct it.)

I'm using an OUTER APPLY (top 1) structure to do this. Basically, something of the form:

select *, event_duration = datediff(ss, alias.EVENT_DTTM, TABLE.EVENT_DTTM)
from TABLE
outer apply
  (select top 1 EVENT_DTTM
   from TABLE alias
   where alias.EVENT_DTTM >= @min_date
   and alias.EVENT_DTTM < @max_date
   and alias.ETC = TABLE.ETC) alias
where TABLE.EVENT_DTTM >= @min_date
and TABLE.EVENT_DTTM < @max_date

My problem is that SQL appears to be scanning the entire table for the OUTER APPLY, instead of just the range between @min_date and @max_date.

Here is an estimated query plan for the same query but with different parameters. The query does not run in parallel but the index spool is present.

Does anyone have an idea why this might be, and how to fix it?

I've tried building a CTE limited to the given date range, and then updating that. I've tried building a dynamic SQL string using literals for the dates. Neither of these has worked.

The only thing that I've found that works is to build a filtered index on the date range. SQL will then use the filtered index for the OUTER APPLY. This isn't very elegant and writing out the index costs a lot of time.

Unfortunately, I don't have time to implement and kind of partitioning scheme at this time.

For the sake of completeness, here is an example of my actual code:

update fact_cabinet_event_base
set assoc_event_device_id = assoc_event.device_id,
assoc_event_num = assoc_event.event_num
from fact_cabinet_event_base
outer apply
  (select top 1 device_id, event_num
   from fact_cabinet_event_base assoc_event
   where assoc_event.event_datetime >= dateadd(dd, -3, @populate_min_date)
   and assoc_event.event_datetime < @populate_max_date
   and assoc_event.event_type = 'dispense'
   and assoc_event.facility_applix_number = fact_cabinet_event_base.facility_applix_number
   and assoc_event.idr = fact_cabinet_event_base.idr
   and assoc_event.idri = fact_cabinet_event_base.idri
   and assoc_event.idria = fact_cabinet_event_base.idria
   and assoc_event.event_datetime < fact_cabinet_event_base.event_datetime
   order by assoc_event.event_datetime desc) assoc_event
where fact_cabinet_event_base.event_datetime >= @populate_min_date
and fact_cabinet_event_base.event_datetime < @populate_max_date
and (fact_cabinet_event_base.event_type = 'waste'
     or (fact_cabinet_event_base.event_type = 'return' and fact_cabinet_event_base.event_subtype <> 'return to pharmacy'))
and (fact_cabinet_event_base.assoc_event_num is NULL or fact_cabinet_event_base.assoc_event_num = 0)

When you see an index spool in a query plan what that usually means is that SQL Server thinks that the query really needs a suitable index on the table, so much so that it creates a temporary index for you. Often it can be resolved by creating a new index so that query plans aren't creating temporary indexes on the fly. As you might imagine query plans with index spools often aren't optimal for performance.

Let's examine why SQL Server thinks that the index spool is needed. In the query that you posted the execution plan for, SQL Server estimates that 351644 rows are going into the outer input for the nested loop join. If SQL Server used the clustered index on the table in the inner part of the nested loop join it would need to do the date filtering 351644 times. If you use local variables for the filtering as you did the optimizer isn't aware of the values and will fall back on a default cardinality estimate of 9% of the rows in the table. So from SQL Server's point of view, using the clustered index could result in 351644 * 176022000 * 0.09 = 5.5 trillion rows scanned from the table in the worst case.

For some queries it can help to add a RECOMPILE hint to the query to that SQL Server is aware of the values of the local variables when making the query plan. Since you're already using dynamic SQL you could also consider replacing the variables with date literals. Both of those will help with the estimate but I doubt that it will change the plan. In the query that you posted you have a date range of 5 days. With better information, if SQL Server used the index it could scan the entire same 5 days of data 351644 times in the worst case. In reality it could scan less rows than that because it only needs to find the first row that matches all of the predicates but that's a tough thing to estimate, especially with so many predicates.

So, SQL Server created an index spool in an attempt to speed up the query. There are a lot of things to dislike about the index spool: it will be built single-threaded even in a parallel plan, the query has to wait on the index to be built each time it runs, it scans the entire table, it takes up space in tempdb, and so on. If you want to avoid the index spool I recommend rewriting the query so that a nested loop join is not done, creating a more selective index against the Fact_Cabinet_Event_Base table, or creating a temp table to hold the rows from Fact_Cabinet_Event_Base that you need to query.

It should be possible to rewrite the query to not use APPLY. There isn't anything wrong with how you wrote the query, but in my experience APPLY tends to encourage the optimizer to use a nested loop join. If you want to keep the APPLY it's important to have a good index for the nested loop join. If you rewrite the query without APPLY perhaps you will get a hash join which could be faster. However, if it's important to avoid scanning the entire fact table then the hash join probably isn't what you want to happen.

If you wanted to create an index it's hard to give advice on which columns should be included and the order of the key columns. It'll depend on the selectivity of each column and how quickly you need this query to run. You could create a covering index for this query but I imagine it would be quite large.

If you can't afford to create an index then another option is to make your code use a temp table instead. Give the temp table the right clustered clustered index and filter out rows that you don't need based on event_datetime. This has a few advantages over letting SQL Server build an index spool for you. The query can be run in parallel, you don't need to scan the entire table because you can filter on event_datetime, and you can pick the order of the key columns in the temp table. However, your query will have to wait each time for the temp table to be created.


Unusual column comparisons and query performance We have some consultants working on expanding an inhouse data warehouse.  I was doing a code review and ran across this pattern in all of the load procs:

    MERGE [EDHub].[Customer].[Class] AS TARGET
    USING (
        SELECT <columns>
        FROM [dbo].[vw_CustomerClass]
            WHERE JHAPostingDate = @PostingDate
        ) AS SOURCE
        ON  TARGET.BankId = SOURCE.BankId       -- This join is on the business keys
            AND TARGET.Code = SOURCE.Code
    WHEN NOT MATCHED BY TARGET
        THEN
            <INSERT Statement>
    WHEN MATCHED
        AND TARGET.IsLatest = 1
        AND EXISTS (
            SELECT SOURCE.[HASH]
            EXCEPT
            SELECT TARGET.[Hash]
            )
        THEN
            <UPDATE Statement>

The gist is, if we have a new business key, insert but if the business key exists and the hash of the attributes don't match our current row then  update the old row and insert a new one (later in the code).  It all works fine but I paused when I got to this code

AND EXISTS (
            SELECT SOURCE.[HASH]
            EXCEPT
            SELECT TARGET.[Hash]
            )

It seems overly complicated compared to SOURCE.[HASH] <> TARGET.[Hash].  The EXCEPT will do an accurate NULL comparison but in our case hashes will never be NULL (or we have bigger problems).  I want our code to be easy to read so that when someone has to maintain it, it doesn't confuse.  I asked our consultants about it and they speculated that it might be faster because of set operations but I decided to write a simple test (test code below).

The first thing I noticed was the EXISTS/EXCEPT had a more complicated query plan but that's not always bad

I ran each select client statistics on and the <> join yielded total execution time of 12,000 vs 25,000 with the EXISTS/EXCEPT.  I want to take this to our consultants with the request to refactor that statement but wanted to get feedback here on:

is this a good test? - am I missing anything?
is there a case where EXISTS/EXCEPT would be a better comparison?

Test script:

CREATE TABLE r (hash VARBINARY(8000))
CREATE TABLE l (hash VARBINARY(8000))

SET NOCOUNT ON
DECLARE @x INT = 10000
WHILE @x <> 0 BEGIN

   INSERT INTO dbo.r ( hash ) SELECT HASHBYTES('SHA2_256',CAST(NEWID() AS VARCHAR(200)))
   INSERT INTO dbo.l ( hash ) SELECT HASHBYTES('SHA2_256',CAST(NEWID() AS VARCHAR(200)))

   SET @x = @x-1
END

INSERT INTO dbo.r ( hash ) VALUES ( NULL  )
INSERT INTO dbo.l ( hash ) VALUES ( NULL  )

SELECT COUNT(1)
FROM dbo.l
CROSS JOIN dbo.r
WHERE ISNULL(r.hash,0) <> ISNULL(l.hash,0)

SELECT COUNT(1)
FROM dbo.l
CROSS JOIN dbo.r
WHERE EXISTS(SELECT r.hash except select l.HASH)

I don't like ISNULL with sentinel values, it requires picking values that can't ever legitimately appear in the data now or forever after and personally I find expressions containing these more difficult to reason about.

For your test rig I tried four different ways of expressing the query and got the stated results.

SELECT COUNT(1)
FROM   dbo.l
       CROSS JOIN dbo.r
WHERE  r.hash <> l.hash
        OR ( r.hash IS NULL
             AND l.hash IS NOT NULL )
        OR ( l.hash IS NULL
             AND r.hash IS NOT NULL )

  SQL Server Execution Times:    CPU time = 30968 ms,  elapsed time =
  8230 ms.

SELECT COUNT(1)
FROM   dbo.l
       CROSS JOIN dbo.r
WHERE  ISNULL(r.hash, 0) <> ISNULL(l.hash, 0)

  SQL Server Execution Times:    CPU time = 31594 ms,  elapsed time =
  9230 ms.

SELECT COUNT(1)
FROM   dbo.l
       CROSS JOIN dbo.r
WHERE  EXISTS(SELECT r.hash
              EXCEPT
              SELECT l.HASH)

  SQL Server Execution Times:    CPU time = 46531 ms,  elapsed time =
  13191 ms.

SELECT COUNT(1)
FROM   dbo.l
       CROSS JOIN dbo.r
WHERE  NOT EXISTS(SELECT r.hash
                  INTERSECT
                  SELECT l.HASH)

  SQL Server Execution Times:    CPU time = 23812 ms,  elapsed time =
  6760 ms.

So on that basis the last one would be a clear winner - along with a code comment with a link to Undocumented Query Plans: Equality Comparisons for anyone unfamiliar with the pattern.

But you should test whether this pattern is reproducible with your actual MERGE query too.


How to automate CSV-exports of queries in PL/SQL Developer (Oracle)? I'm no pro in Oracle or PL/SQL Developer (I mostly use and admin mySQL and MS SQL Server).

I'm not an admin of any sort, but have read access to many tables in Oracle via PL/SQL Developer.

I need the ability to schedule an automatic (say, daily) export of a few tables (well, queries) in CSV format. Whether it's to my local computer, network, email --- anything.

Does anyone know how this might be achieved?

One option (on MS Windows) is to

create SQL file which spools data into a file
create BAT script which calls that SQL script
schedule a daily job in Task Scheduler

Prerequisite is to install SQLPlus, if you don't have it already. PL/SQL Developer is a GUI tool; SQLPlus is a command-line tool. It can be installed via Oracle Client software, downloadable from Oracle Technology Network's Download section.

SQL file, based on Scott's schema, might look like this:

set linesize 100
set pagesize 100
set colsep ';'

spool dept.txt
select * from dept;
spool off

spool emp.txt
select * from emp;
spool off;

exit

BAT file:

sqlplus -s -l scott/tiger@xe @daily.sql

Task Scheduler: create a job which will run the BAT script at desired time; make it run in a directory you have access to (such as C:\Temp or similar).

Resulting DEPT.TXT file looks like this:

    DEPTNO;DNAME         ;LOC
----------;--------------;-------------
        10;ACCOUNTING    ;NEW YORK
        20;RESEARCH      ;DALLAS
        30;SALES         ;CHICAGO
        40;OPERATIONS    ;BOSTON

List of SET commands in the SQL file is really simple - there are numerous options you might want to research. For example, this is a longer list with more options (including column formatting):

set termout off
set trimspool on
set echo off
set verify off
set autoprint off
set serveroutput off
set arraysize 1000
set pagesize 0
set linesize 100
set long 10000
set numwidth 10
set feedback off
set colsep ';'
col empno format 99999
col ename format a10
col sal format 999G990

You'll have to try these (and, possibly some more) to find a set which suits your needs.


Oracle implicit parameter casting - why doesn't it always work? (ORA-06502 error) I have a Java (Spring) app that uses Oracle 10 as DB. Some legacy code has wrong parametrization of PL/SQL procedures. For instance, some input parameters are declared as OracleTypes.VARCHAR in Java DAO layer, while in fact they're NUMBER in PL/SQL procedure. 99% times this works without a problem (of course, if the sent data is actually a number), but sometimes I get the ORA-06502: PL/SQL: numeric or value error: character to number conversion error.

I understand how to fix the problem, but I wonder why this occurs on some DB installations and on most it doesn't. This is of high importance for me because it's very difficult to inspect legacy code for wrong parameter types.

Interesting question - at first I would have said an overflow, but checking, that's ORA-01426 and if it wasn't a number, ORA-01722. Is it just datatype NUMBER which defaults to NUMBER(38)?

But this can be diagnosed with a trace to see what's exactly going on. If you can identify the session, or can modify the connection code, you can do:

SQL> alter session set events='6502 trace name errorstack level 12';

Session altered.

SQL> create or replace function fn_add(p1 number, p2 number) return number
  2  as
  3  v1 number(1);
  4  begin
  5  v1 := p1 + p2;
  6  return v1;
  7  end;
  8  /

Function created.

SQL> select fn_add('10', '1') from dual;
select fn_add('10', '1') from dual
       *
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: number precision too large

Now looking in the tracefile:

---- Error Stack Dump -----
ORA-06502: PL/SQL: numeric or value error: number precision too large
----- Current SQL Statement for this session (sql_id=a36psfjj50bpv) -----
select fn_add('10', '1') from dual

That should at least get you started on root cause analysis. But be warned that these trace files can get very large, very quickly!


Should Query Tuning be Proactive or Reactive? As a software developer and an aspiring DBA, I try to encorporate best practices when I design my SQL Server databases (99% of the time my software sits on top of SQL Server).  I make the best possible design prior to and during development.

But, just like any other software developer, there is added functionality, bugs, and just change of requirements that demands altered/created database objects.

My question is, should query tuning be proactive or reactive?  In other words, a few weeks after some heavy code/database modification, should I just set aside a day to check out query performance and tune based off of that?  Even if it seems to be running okay?

Or should I just be aware that less-than-average performance should be a database check and going back to the proverbial chalkboard?

Query tuning can take up a lot of time, and depending on the initial database design it could be minimal benefit.  I'm curious as to the accepted modus operandi.

Both, but mostly proactive

It's important to test during development against realistic volumes and quality of data. It's unbelievably common to have a query running on a developers 100 or 1000 rows then fall flat with 10 million production rows.

It allows you to make notes too about "index may help here". Or "revisit me". Or "will fix with new feature xxx in next DB version".

However, a few queries won't stand the test of time. Data distribution changes or it goes exponential because the optimiser decides to use a different join type. In this case, you can only react.

Saying that, for SQL Server at least, the various "missing index" and "longest query" DMV queries can indicate problem areas before the phone call

Edit: to clarify...

Proactive doesn't mean tune every query now. It means tune what you need to (frequently run) to a reasonable response time. Mostly ignore the weekly 3am Sunday report queries.


Duplicate entry for key. even though it is not Above image describes my tables 'Details'

And above is my entire table only one row. and  i have tried 'INSERT' to table

INSERT INTO `details`(
  `Name`,
  `dob`,
  `age`,
  `qualification`,
  `ExpYr`,
  `ExpMnth`,
  `Email`,
  `Contact`,
  `resume`)
VALUES (
  'viviek',
  '0899-06-21',
  '1117',
  'hihh',
  '2',
  '3',
  '1026viivek@gmail.com',
  '9645904409',
  'Array')

but it showing an error like

  Duplicate entry '2147483647' for key 'Contact'

Why is it showing error with different value than key value in table for contact?

This is because the maximum possible stored value for the int(11) (32-bit signed integer) Contact column is 2147483647.

MySQL is silently ignoring the fact that the value 9645904409 is larger than this & is "truncating" it down to 2147483647. Multiple values truncated to 2147483647 are causing the unique constraint violation.

If Contact is supposed to be a phone number, I suggest changing the datatype to VARCHAR(20) or similar.


SQL Server backups are suddenly smaller On a Windows server 2012 R2 with MSSQL 2012 SP4 Express, installed with all the default options, I do daily backups of a small database with a scheduled task running the backup command with default options. The database sees very little activity, maybe a dozen inserts a day in a specific table which has no indexes other than the PK. The recovery model is Full.

Yesterday's backup was 33mb and today's backup is 11mb. Auto-shrink is disabled, backup compression is disabled, and if I restore yesterday's backup then re-back it up, I get a 11mb file. I've verified we didn't lose any data and everything is still there. I also tried restoring and re-backing up on my PC running MSSQL2014 and I get the same result (33mb to 11mb).

I don't see any recent updates for MSSQL on the server nor on my machine, and I can't find any evidence of this behavior online. Is there some other process which could explain this? Not that I'm complaining mind you, I just find it weird and I'd rather understand what happenned.

Edit #1 -for LowlyDBA

The backup command is:

DECLARE @pathName NVARCHAR(512)
SET @pathName = 'D:\bkp\my_DB_' + REPLACE(Convert(nvarchar(20), GETDATE(), 120),':','-') + '.bak'
BACKUP DATABASE my_DB TO DISK = @pathName

results of sp_spaceused

backup:

database_name   database_size   unallocated space
my_DB           12.50 MB        0.13 MB

reserved    data     index_size unused
11136 KB    8584 KB  1936 KB    616 KB

current:

database_name   database_size   unallocated space
my_DB           13.50 MB        0.99 MB

reserved    data     index_size unused
11272 KB    8592 KB  1960 KB    720 KB

My first guess was wrong so I deleted that answer, the OP simply made 3 backups in the same file, while the last backup was the only backup in the file.

To avoid this issue in future, add with init in your backup command, so even if you press F5 two times at the same minute (your backup file has datetime in its name so it seems that you cannot have more than 1 backup in one file, but your database size is very small so the backup is nearly instant), the first backup will be overwritten with the second and there will be only 1 backup in 1 file.


Replication Monitor Information using T-SQL The image below shows a transaction replication problem that I am currently investigating.

The image comes from the Replication Monitor.

How can I get this information using T-SQL?

After doing some online search,I found what you needed,hope my answer helps you.

Answer 1

Note:

  It Only works with transactional and transactional peer to peer
  replication

T-SQL script which you can use to monitor the status of transactional replication and performance of publications and subscriptions.

Things to be considered before executing the below script

Requires permission on the following tables inside distribution and master databases

MSdistribution_status
MSdistribution_agents
MSArticles
MSreplication_monitordata
MSdistribution_history
servers

    USE [distribution]

    IF OBJECT_ID('Tempdb.dbo.#ReplStats') IS NOT NULL
    DROP TABLE #ReplStats

    CREATE TABLE [dbo].[#ReplStats](
    [DistributionAgentName] [nvarchar](100) NOT NULL,
    [DistributionAgentStartTime] [datetime] NOT NULL,
    [DistributionAgentRunningDurationInSeconds] [int] NOT NULL,
    [IsAgentRunning] [bit] NULL,
    [ReplicationStatus] [varchar](14) NULL,
    [LastSynchronized] [datetime] NOT NULL,
    [Comments] [nvarchar](max) NOT NULL,
    [Publisher] [sysname] NOT NULL,
    [PublicationName] [sysname] NOT NULL,
    [PublisherDB] [sysname] NOT NULL,
    [Subscriber] [nvarchar](128) NULL,
    [SubscriberDB] [sysname] NULL,
    [SubscriptionType] [varchar](64) NULL,
    [DistributionDB] [sysname] NULL,
    [Article] [sysname] NOT NULL,
    [UndelivCmdsInDistDB] [int] NULL,
    [DelivCmdsInDistDB] [int] NULL,
    [CurrentSessionDeliveryRate] [float] NOT NULL,
    [CurrentSessionDeliveryLatency] [int] NOT NULL,
    [TotalTransactionsDeliveredInCurrentSession] [int] NOT NULL,
    [TotalCommandsDeliveredInCurrentSession] [int] NOT NULL,
    [AverageCommandsDeliveredInCurrentSession] [int] NOT NULL,
    [DeliveryRate] [float] NOT NULL,
    [DeliveryLatency] [int] NOT NULL,
    [TotalCommandsDeliveredSinceSubscriptionSetup] [int] NOT NULL,
    [SequenceNumber] [varbinary](16) NULL,
    [LastDistributerSync] [datetime] NULL,
    [Retention] [int] NULL,
    [WorstLatency] [int] NULL,
    [BestLatency] [int] NULL,
    [AverageLatency] [int] NULL,
    [CurrentLatency] [int] NULL
    ) ON [PRIMARY]

    INSERT INTO #ReplStats
    SELECT da.[name] AS [DistributionAgentName]
    ,dh.[start_time] AS [DistributionAgentStartTime]
    ,dh.[duration] AS [DistributionAgentRunningDurationInSeconds]
    ,md.[isagentrunningnow] AS [IsAgentRunning]
    ,CASE md.[status]
    WHEN 1 THEN '1 - Started'
    WHEN 2 THEN '2 - Succeeded'
    WHEN 3 THEN '3 - InProgress'
    WHEN 4 THEN '4 - Idle'
    WHEN 5 THEN '5 - Retrying'
    WHEN 6 THEN '6 - Failed'
    END AS [ReplicationStatus]
    ,dh.[time] AS [LastSynchronized]
    ,dh.[comments] AS [Comments]
    ,md.[publisher] AS [Publisher]
    ,da.[publication] AS [PublicationName]
    ,da.[publisher_db] AS [PublisherDB]
    ,CASE
    WHEN da.[anonymous_subid] IS NOT NULL
    THEN UPPER(da.[subscriber_name])
    ELSE UPPER (s.[name]) END AS [Subscriber]
    ,da.[subscriber_db] AS [SubscriberDB]
    ,CASE da.[subscription_type]
    WHEN '0' THEN 'Push'
    WHEN '1' THEN 'Pull'
    WHEN '2' THEN 'Anonymous'
    ELSE CAST(da.[subscription_type] AS [varchar](64)) END AS [SubscriptionType]
    ,md.[distdb] AS [DistributionDB]
    ,ma.[article]    AS [Article]
    ,ds.[UndelivCmdsInDistDB]
    ,ds.[DelivCmdsInDistDB]
    ,dh.[current_delivery_rate] AS [CurrentSessionDeliveryRate]
    ,dh.[current_delivery_latency] AS [CurrentSessionDeliveryLatency]
    ,dh.[delivered_transactions] AS [TotalTransactionsDeliveredInCurrentSession]
    ,dh.[delivered_commands] AS [TotalCommandsDeliveredInCurrentSession]
    ,dh.[average_commands] AS [AverageCommandsDeliveredInCurrentSession]
    ,dh.[delivery_rate] AS [DeliveryRate]
    ,dh.[delivery_latency] AS [DeliveryLatency]
    ,dh.[total_delivered_commands] AS [TotalCommandsDeliveredSinceSubscriptionSetup]
    ,dh.[xact_seqno] AS [SequenceNumber]
    ,md.[last_distsync] AS [LastDistributerSync]
    ,md.[retention] AS [Retention]
    ,md.[worst_latency] AS [WorstLatency]
    ,md.[best_latency] AS [BestLatency]
    ,md.[avg_latency] AS [AverageLatency]
    ,md.[cur_latency] AS [CurrentLatency]
    FROM [distribution]..[MSdistribution_status] ds
    INNER JOIN [distribution]..[MSdistribution_agents] da
    ON da.[id] = ds.[agent_id]
    INNER JOIN [distribution]..[MSArticles] ma
    ON ma.publisher_id = da.publisher_id
    AND ma.[article_id] = ds.[article_id]
    INNER JOIN [distribution]..[MSreplication_monitordata] md
    ON [md].[job_id] = da.[job_id]
    INNER JOIN [distribution]..[MSdistribution_history] dh
    ON [dh].[agent_id] = md.[agent_id]
    AND md.[agent_type] = 3
    INNER JOIN [master].[sys].[servers]  s
    ON s.[server_id] = da.[subscriber_id]
    --Created WHEN your publication has the immediate_sync property set to true. This property dictates
    --whether snapshot is available all the time for new subscriptions to be initialized.
    --This affects the cleanup behavior of transactional replication. If this property is set to true,
    --the transactions will be retained for max retention period instead of it getting cleaned up
    --as soon as all the subscriptions got the change.
    WHERE da.[subscriber_db] <> 'virtual'
    AND da.[anonymous_subid] IS NULL
    AND dh.[start_time] = (SELECT TOP 1 start_time
                    FROM [distribution]..[MSdistribution_history] a
                    JOIN [distribution]..[MSdistribution_agents] b
                    ON a.[agent_id] = b.[id] AND b.[subscriber_db] <> 'virtual'
                    WHERE [runstatus] <> 1
                    ORDER BY [start_time] DESC)
    AND dh.[runstatus] <> 1

    SELECT 'Transactional Replication Summary' AS [Comments];
    SELECT [DistributionAgentName]
    ,[DistributionAgentStartTime]
    ,[DistributionAgentRunningDurationInSeconds]
    ,[IsAgentRunning]
    ,[ReplicationStatus]
    ,[LastSynchronized]
    ,[Comments]
    ,[Publisher]
    ,[PublicationName]
    ,[PublisherDB]
    ,[Subscriber]
    ,[SubscriberDB]
    ,[SubscriptionType]
    ,[DistributionDB]
    ,SUM([UndelivCmdsInDistDB]) AS [UndelivCmdsInDistDB]
    ,SUM([DelivCmdsInDistDB]) AS [DelivCmdsInDistDB]
    ,[CurrentSessionDeliveryRate]
    ,[CurrentSessionDeliveryLatency]
    ,[TotalTransactionsDeliveredInCurrentSession]
    ,[TotalCommandsDeliveredInCurrentSession]
    ,[AverageCommandsDeliveredInCurrentSession]
    ,[DeliveryRate]
    ,[DeliveryLatency]
    ,[TotalCommandsDeliveredSinceSubscriptionSetup]
    ,[SequenceNumber]
    ,[LastDistributerSync]
    ,[Retention]
    ,[WorstLatency]
    ,[BestLatency]
    ,[AverageLatency]
    ,[CurrentLatency]
    FROM #ReplStats
    GROUP BY [DistributionAgentName]
    ,[DistributionAgentStartTime]
    ,[DistributionAgentRunningDurationInSeconds]
    ,[IsAgentRunning]
    ,[ReplicationStatus]
    ,[LastSynchronized]
    ,[Comments]
    ,[Publisher]
    ,[PublicationName]
    ,[PublisherDB]
    ,[Subscriber]
    ,[SubscriberDB]
    ,[SubscriptionType]
    ,[DistributionDB]
    ,[CurrentSessionDeliveryRate]
    ,[CurrentSessionDeliveryLatency]
    ,[TotalTransactionsDeliveredInCurrentSession]
    ,[TotalCommandsDeliveredInCurrentSession]
    ,[AverageCommandsDeliveredInCurrentSession]
    ,[DeliveryRate]
    ,[DeliveryLatency]
    ,[TotalCommandsDeliveredSinceSubscriptionSetup]
    ,[SequenceNumber]
    ,[LastDistributerSync]
    ,[Retention]
    ,[WorstLatency]
    ,[BestLatency]
    ,[AverageLatency]
    ,[CurrentLatency]

    SELECT 'Transactional Replication Summary Details' AS [Comments];
    SELECT [Publisher]
    ,[PublicationName]
    ,[PublisherDB]
    ,[Article]
    ,[Subscriber]
    ,[SubscriberDB]
    ,[SubscriptionType]
    ,[DistributionDB]
    ,SUM([UndelivCmdsInDistDB]) AS [UndelivCmdsInDistDB]
    ,SUM([DelivCmdsInDistDB]) AS [DelivCmdsInDistDB]
    FROM #ReplStats
    GROUP BY [Publisher]
    ,[PublicationName]
    ,[PublisherDB]
    ,[Article]
    ,[Subscriber]
    ,[SubscriberDB]
    ,[SubscriptionType]
    ,[DistributionDB]

Answer 2

Script_2 found on MSDN

    --First you find the distributor servername using the below running in publisher
    Use master
    EXEC sp_helpdistributor;

    --Then you can run the below to find the type (use distributor database)

    SELECT
    (CASE
    WHEN mdh.runstatus =  '1' THEN 'Start - '+cast(mdh.runstatus as varchar)
    WHEN mdh.runstatus =  '2' THEN 'Succeed - '+cast(mdh.runstatus as varchar)
    WHEN mdh.runstatus =  '3' THEN 'InProgress - '+cast(mdh.runstatus as varchar)
    WHEN mdh.runstatus =  '4' THEN 'Idle - '+cast(mdh.runstatus as varchar)
    WHEN mdh.runstatus =  '5' THEN 'Retry - '+cast(mdh.runstatus as varchar)
    WHEN mdh.runstatus =  '6' THEN 'Fail - '+cast(mdh.runstatus as varchar)
    ELSE CAST(mdh.runstatus AS VARCHAR)
    END) [Run Status],
    mda.subscriber_db [Subscriber DB],
    mda.publication [PUB Name],
    CONVERT(VARCHAR(25),mdh.[time]) [LastSynchronized],
    und.UndelivCmdsInDistDB [UndistCom],
    mdh.comments [Comments],
    'select * from distribution.dbo.msrepl_errors (nolock) where id = ' + CAST(mdh.error_id AS VARCHAR(8)) [Query More Info],
    mdh.xact_seqno [SEQ_NO],
    (CASE
    WHEN mda.subscription_type =  '0' THEN 'Push'
    WHEN mda.subscription_type =  '1' THEN 'Pull'
    WHEN mda.subscription_type =  '2' THEN 'Anonymous'
    ELSE CAST(mda.subscription_type AS VARCHAR)
    END) [SUB Type],

    mda.publisher_db+' - '+CAST(mda.publisher_database_id as varchar) [Publisher DB],
    mda.name [Pub - DB - Publication - SUB - AgentID]
    FROM distribution.dbo.MSdistribution_agents mda
    LEFT JOIN distribution.dbo.MSdistribution_history mdh ON mdh.agent_id = mda.id
    JOIN
    (SELECT s.agent_id, MaxAgentValue.[time], SUM(CASE WHEN xact_seqno > MaxAgentValue.maxseq THEN 1 ELSE 0 END) AS UndelivCmdsInDistDB
    FROM distribution.dbo.MSrepl_commands t (NOLOCK)
    JOIN distribution.dbo.MSsubscriptions AS s (NOLOCK) ON (t.article_id = s.article_id AND t.publisher_database_id=s.publisher_database_id )
    JOIN
    (SELECT hist.agent_id, MAX(hist.[time]) AS [time], h.maxseq
    FROM distribution.dbo.MSdistribution_history hist (NOLOCK)
    JOIN (SELECT agent_id,ISNULL(MAX(xact_seqno),0x0) AS maxseq
    FROM distribution.dbo.MSdistribution_history (NOLOCK)
    GROUP BY agent_id) AS h
    ON (hist.agent_id=h.agent_id AND h.maxseq=hist.xact_seqno)
    GROUP BY hist.agent_id, h.maxseq
    ) AS MaxAgentValue
    ON MaxAgentValue.agent_id = s.agent_id
    GROUP BY s.agent_id, MaxAgentValue.[time]
    ) und
    ON mda.id = und.agent_id AND und.[time] = mdh.[time]
    where mda.subscriber_db<>'virtual' -- created when your publication has the immediate_sync property set to true. This property dictates whether snapshot is available all the time for new subscriptions to be initialized. This affects the cleanup behavior of transactional replication. If this property is set to true, the transactions will be retained for max retention period instead of it getting cleaned up as soon as all the subscriptions got the change.
    --and mdh.runstatus='6' --Fail
    --and mdh.runstatus<>'2' --Succeed
    order by mdh.[time]

Answer 3

Script featured on SimpleTalk TSQL as an agent job.

Step 1

Create in a DBA database installed on the subscriber server. The code to create the table is:

CREATE TABLE dbo.Replication_Qu_History(
            Subscriber_db varchar(50) NOT NULL,
            Records_In_Que numeric(18, 0) NULL,
            CatchUpTime numeric(18, 0) NULL,
            LogDate datetime NOT NULL,
        CONSTRAINT PK_EPR_Replication_Que_History PRIMARY KEY CLUSTERED
    (
            Subscriber_db ASC, LogDate DESC
    ) ON PRIMARY
    GO

Script 1

The data in this table is populated by the monitoring procedures and provides an historical context for examining issues.  But to monitor what is happening right now more is required.

There are three things that help to determine the health of replication.

The status of the replication related jobs
The latency (especially the distribution latency) as measured by the
counter Dist:Delivery Latency
The number of outstanding commands that are pending for the
subscription

DECLARE @cmd NVARCHAR(max)
DECLARE @publisher SYSNAME, @publisher_db SYSNAME, @publication SYSNAME, @pubtype INT
DECLARE @subscriber SYSNAME, @subscriber_db SYSNAME, @subtype INT
DECLARE @cmdcount INT, @processtime INT
DECLARE @ParmDefinition NVARCHAR(500)
DECLARE @JobName SYSNAME
DECLARE @minutes INT, @threshold INT, @maxCommands INT, @mail CHAR(1) = 'N'
SET @minutes = 60 --> Define how many minutes latency before you would like to be notified
SET @maxCommands = 80000  --->  change this to represent the max number of outstanding commands to be proceduresed before notification
SET @threshold = @minutes * 60

SELECT * INTO #PublisherInfo
FROM OPENROWSET('SQLOLEDB', 'SERVER=(LOCAL);TRUSTED_CONNECTION=YES;'
, 'SET FMTONLY OFF EXEC distribution.dbo.sp_replmonitorhelppublisher')

SELECT @publisher = publisher FROM #PublisherInfo

SET @cmd = 'SELECT * INTO ##PublicationInfo FROM OPENROWSET(''SQLOLEDB'',''SERVER=(LOCAL);TRUSTED_CONNECTION=YES''
,''SET FMTONLY OFF EXEC distribution.dbo.sp_replmonitorhelppublication @publisher='
+ @publisher + ''')'
--select @cmd
EXEC sp_executesql @cmd

SELECT @publisher_db=publisher_db, @publication=publication, @pubtype=publication_type  FROM ##PublicationInfo

SET @cmd = 'SELECT * INTO ##SubscriptionInfo FROM OPENROWSET(''SQLOLEDB'',''SERVER=(LOCAL);TRUSTED_CONNECTION=YES''
,''SET FMTONLY OFF EXEC distribution.dbo.sp_replmonitorhelpsubscription @publisher='
+ @publisher + ',@publication_type=' + CONVERT(CHAR(1),@pubtype) + ''')'
--select @cmd
EXEC sp_executesql @cmd

ALTER TABLE ##SubscriptionInfo
ADD  PendingCmdCount INT NULL,
EstimatedProcessTime INT NULL

To check the status of the distribution jobs

DECLARE cur_sub CURSOR READ_ONLY FOR
SELECT @publisher, s.publisher_db, s.publication, s.subscriber, s.subscriber_db, s.subtype, s.distribution_agentname
FROM ##SubscriptionInfo s

OPEN cur_sub
FETCH NEXT FROM cur_sub INTO @publisher, @publisher_db, @publication, @subscriber, @subscriber_db, @subtype, @JobName

WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'SELECT @cmdcount=pendingcmdcount, @processtime=estimatedprocesstime FROM OPENROWSET(''SQLOLEDB'',''SERVER=(LOCAL);TRUSTED_CONNECTION=YES''
,''SET FMTONLY OFF EXEC distribution.dbo.sp_replmonitorsubscriptionpendingcmds @publisher=' + @publisher
+ ',@publisher_db=' + @publisher_db + ',@publication=' + @publication
+ ',@subscriber=' + @subscriber + ',@subscriber_db=' + @subscriber_db
+ ',@subscription_type=' + CONVERT(CHAR(1),@subtype) + ';' + ''')'
SET @ParmDefinition = N'@cmdcount INT OUTPUT,
@processtime INT OUTPUT'
--select @cmd
EXEC sp_executesql @cmd,@ParmDefinition,@cmdcount OUTPUT, @processtime OUTPUT

UPDATE ##SubscriptionInfo
SET PendingCmdCount = @cmdcount
, EstimatedProcessTime = @processtime
WHERE subscriber_db = @subscriber_db

INSERT INTO DBA.dbo.Replication_Que_History
VALUES(@subscriber_db, @cmdcount, @processtime, GETDATE())
--  find out if the distribution job with the high number of outstanding commands running or not
--  if it is running then sometimes stopping and starting the agent fixes the issue
IF EXISTS(SELECT * FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '##JobInfo%')
DROP TABLE ##JobInfo

SET @cmd = 'SELECT * INTO ##JobInfo FROM OPENROWSET(''SQLOLEDB'',''SERVER=(LOCAL);TRUSTED_CONNECTION=YES''
,''SET FMTONLY OFF EXEC msdb.dbo.sp_help_job @job_name='''''
+ @JobName + ''''',@job_aspect=''''JOB'''''')'
EXEC sp_executesql @cmd

IF @cmdcount > @maxCommands OR (@processtime > @threshold AND @cmdcount > 0)
BEGIN
IF (SELECT current_execution_status FROM ##JobInfo) = 1 --  This means job is currently executing so stop/start it
BEGIN
EXEC distribution.dbo.sp_MSstopdistribution_agent
@publisher = @publisher
, @publisher_db = @publisher_db
, @publication = @publication
, @subscriber = @subscriber
, @subscriber_db = @subscriber_db
WAITFOR DELAY '00:00:05' ---- 5 Second Delay
SET @mail = 'Y'
END
END
--SELECT name, current_execution_status FROM ##JobInfo
IF (SELECT current_execution_status FROM ##JobInfo) <> 1 -- if the job is not running start it
BEGIN
EXEC distribution.dbo.sp_MSstartdistribution_agent
@publisher = @publisher
, @publisher_db = @publisher_db
, @publication = @publication
, @subscriber = @subscriber
, @subscriber_db = @subscriber_db
SET @mail = 'Y'      -- Send email if job has stopped and needed to be restarted
END
DROP TABLE ##JobInfo
FETCH NEXT FROM cur_sub INTO @publisher, @publisher_db, @publication, @subscriber, @subscriber_db, @subtype, @JobName
END

CLOSE cur_sub
DEALLOCATE cur_sub

Run the Microsoft-supplied procedure sp_replmonitorsubscriptionpendingcmds

--system stored procedure to run in distribution database
execute sp_replmonitorsubscriptionpendingcmds
--replication publisher server
@publisher ='PubServer',
--replication publisher database
@publisher_db = 'ProdDBPub',
--replication publication name
@publication ='ProdDBSub',
--replication subscriber server
@subscriber ='SubServer',
--replication subscriber database
@subscriber_db ='ProdDBSub1',
--choose type of subscription you have
@subscription_type ='1' --0 for push and 1 for pull
GO

The code below requires the Ad Hoc Distributed Queries server configuration option be enabled. Here I create the email to be sent assuming the previous Script 3 found an issue sp_replmonitorsubscriptionpendingcmds .

        IF @mail = 'Y'
        BEGIN
        DECLARE @msg VARCHAR(MAX) = 'Replication on ' + @@SERVERNAME
        + ' may be experiencing some problems.  Attempts to restart the distribution agent have been made. '
        + 'If this is not the first message like this that you have received within the last hour, please investigate.'
        DECLARE @body NVARCHAR(MAX)
        DECLARE @xml1 NVARCHAR(MAX)
        DECLARE @tab1 NVARCHAR(MAX)
        DECLARE @xml2 NVARCHAR(MAX)
        DECLARE @tab2 NVARCHAR(MAX)

        SET @xml1 = CAST(( SELECT subscriber AS 'td','',subscriber_db AS 'td','',
        latency AS 'td','', PendingCmdCount AS 'td','', EstimatedProcessTime AS 'td'
        FROM  ##SubscriptionInfo s
        FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

        SET @tab1 ='
Subscription Information

         Subscriber 	  Subscriber Database 	  Latency(seconds)
         Undistributed Commands 	  Estimated Catch Up Time
'
        --  this command gives us the last 10 measurements of latency for each subscriber
        SET @xml2 = CAST(( SELECT s.Subscriber_db AS 'td','', s.Records_In_Que AS 'td','', s.CatchUpTime AS 'td','', CONVERT(CHAR(22),LogDate, 100) AS 'td'
        FROM (SELECT ROW_NUMBER() OVER ( PARTITION BY subscriber_db ORDER BY Logdate DESC ) AS 'RowNumber',
        subscriber_db
        , Records_In_Que
        , CatchUpTime
        , Logdate
        FROM DBA.dbo.Replication_Que_History
        ) s
        WHERE RowNumber <= 8
        FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

        SET @tab2 ='

Historical Latency Information

        Subscriber	 Undistributed Commands	  Catch Up Time 	  Date\Time
'

        SET @body = @msg + @tab1 + @xml1 + '
'
        + @tab2 + @xml2 + ''

        DECLARE @to NVARCHAR(200)
        SELECT @to = '' -- INSERT YOUR EMAIL ADDRESS HERE
        EXEC msdb.dbo.sp_send_dbmail
        @body = @body,
        @body_format ='HTML',
        @recipients = @to,
        @subject = 'Possible Replication Problem' ;
        END
        DROP TABLE #PublisherInfo
        DROP TABLE ##PublicationInfo
        DROP TABLE ##SubscriptionInfo

Last process is to periodically delete rows from the replication status table so the data does not get stale

DECLARE @delDate datetime = getdate()-10
  DELETE FROM DBA.dbo.Replication_Que_History
  WHERE LogDate < @deldate

Pls also consider I also noted that
There is an issue with sp_replmonitorsubscriptionpendingcmds if you are still with SQL Server 2005 and 2008 Microsoft Connect

If any of the above answer are unclear you can follow the source I have provided

Thanks!


Query in PostgreSQL became slow and then got better on its own - why? I want to better understand how to debug and fix performance issues in PostgreSQL.

I have a query that usually costs 1 second to run. However, for some reason, it started taking 30 seconds to run for a period of time! After some minutes and some explain analyze invocations, it went back to normal. I didn't do anything to fix it, nor did I understand why it got better all of a sudden.

How do I debug a problem like this? What kind of logging and instrumentation should I have in place to understand what caused this and how to fix it?

It is a very broad question, even more so as you give absolutely no details about your setup and kind of database (volume, type of queries, active connections, size of RAM, dedicated server or not, etc.)

You can start by enabling PostgreSQL to log slow queries, see the log_min_duration_statement in the configuration. That will give you historical data that you would then be able to analyze and maybe correlate with other things (like from the list of @Vérace)

You will then have various tools to help, as described on https://wiki.postgresql.org/wiki/Logging_Difficult_Queries :

pgFouine
PQA
EPQA
pgsi


Outputting the results of two select queries on to a single table in Oracle SQL So after constructing a database for an "online auction website" I need to write a query that takes the average price of two different types of products and displays them in a table with the name of the product type next to it EG:

Gender   Averageprice
Mens     £65.34
Womens   £86.42

The price of the items and the gender of the clothing are in two seperate tables as a parent child relationship ie clothing is a subtype of item.

DESC TABLE ITEM
Name    Null     Type
------- -------- ------------
ITEM_ID NOT NULL NUMBER(38)
PRICE   NOT NULL NUMBER(12,6)

DESC TABLE ITEM_CLOTHING
Name         Null     Type
------------ -------- ------------
ITEM_ID      NOT NULL NUMBER(38)
CLOTHES_TYPE NOT NULL VARCHAR2(10)

I can list the names of the clothing types using...

SELECT DISTINCT CLOTHES_TYPE
FROM ITEM_CLOTHING;

and I can list the average price of the two different clothing types using...

select sum(PRICE) / count(*)
FROM ITEM,ITEM_CLOTHING
WHERE ITEM.ITEM_ID = ITEM_CLOTHING.ITEM_ID AND ITEM_CLOTHING.CLOTHES_TYPE = 'Mens'
UNION ALL
select sum(PRICE) / count(*)
FROM ITEM,ITEM_CLOTHING
WHERE ITEM.ITEM_ID = ITEM_CLOTHING.ITEM_ID AND ITEM_CLOTHING.CLOTHES_TYPE = 'Womens';

I just can't work out how to put it all together in to a single table as shown above.  Could you please help?

Look into GROUP BY queries.

SELECT gender, sum(PRICE) / count(*)
FROM ITEM, ITEM_CLOTHING
WHERE ITEM.ITEM_ID = ITEM_CLOTHING.ITEM_ID
GROUP BY gender;

Or to refactor further:

SELECT gender, AVG(Price) AveragePrice
FROM ITEM
JOIN ITEM_CLOTHING ON ITEM.ITEM_ID = ITEM_CLOTHING.ITEM_ID
GROUP BY gender;


How likely is fragmentation on a table with 40000 products likely to affect performance So database administration is not a strong point of mine, so I have a few questions.

This all relates to the performance of a website I'm looking at, and have spent a fair bit of time researching/diagnosing.

There are two sites, a test site and a live site.
I have noticed page load times are much longer on the live site as opposed to the test site.
The specs of the test site are really low - VPS 2 Virtual processors with 14GB RAM and SQL + website running on the same VPS
The specs of the live website are 4 dedicated servers all with 8 virtual processors and 16GB RAM
The live site uses a separate dedicated server for SQL with 16 virtual processors and 112GB RAM
The website averages 120 concurrent users average at any one time but can fluctuate from 70 to 150 and when we send out an email campaign it can hit 400. When we hit 400 the cpus as you would expect can hit 100% depending on what actions users are doing on the website, but they hold up fairly well same with the RAM.

I would expect the live website to actually perform faster than the test website, but this is not the case.
I have looked through the code but I strongly believe it's due to the database.
The database size of the live website is over 50GB when backed up, and the test site is 4GB.

Both databases are quite fragmented, and I'm wondering how likely the fragmentation will affect performance?

Also because the live website is many times larger than the test site, will high fragmentation affect the performance of the live site much more than the test site?

Here is a capture of one of the indexes in the products table.

Because I have never dealt with this before what are some tips around rebuilding/refreshing indexes.

Should I put the website into maintenance mode so no-one can interact with tables while I'm rebuilding/refreshing indexes?

How long would it take on a table with 12 indexes and 40000 products?

Thanks

Based on the data that you have provided -

there are 67 total pages. Index fragmentation for such a small table would not affect the performance.  I would not worry about index fragmentation for the table that you have mentioned.

You should update your table statistics so that sql server can generate better query plan.

I would start my troubleshooting by

Running sp_Blitz: Overall Health Check

Read : Stop Worrying About SQL Server Fragmentation


Why can't I view the SQL Server database created by a web application? I have an ASP.NET MVC web application deployed to IIS on a Windows Server 2012 machine.  When the application first starts, it will create the database and tables it needs if necessary through Entity Framework.  This all works fine, and now I want to do some administration to the database through SQL Server Management Studio.  However, when I open up that tool I only see the System Databases.  If I remove "User Instance=true" from the connection string and let the application create the database again, I can the see the database.

Why couldn't I view the database created by the web application when User Instance=true was in the connection string?  If I am viewing the server instance through Management Studio with an account that has all roles, I would expect it to be able to see any and all databases.

Some details that may help:

The application uses the following connection string in Web.config:

<add name="DefaultConnection" connectionString="Data Source=.\SqlExpress;
  Initial Catalog=Foo.Dashboard;  Integrated Security=SSPI;User Instance=true"
  providerName="System.Data.SqlClient" />

In IIS, the application pool is set to run with the identity LocalService and the Load User Profile setting is True.  On SQL Server Express, we added a login for the Local Service account and gave it the dbcreator role.  When the application started up, it was then able to create the database and tables, and everything appears to be functioning.

I then connect to the server with remote desktop and open SQL Server Management Studio. To connect to the database, I enter .\SQLEXPRESS as the server name and authenticate with Windows Authentication. I then connect and only see the system databases. I’ve added all the possible database roles to my login, and I see that I have the VIEW ANY DATABASE permission in the effective tab.

Please stop using the user instance=true setting. Not only has this feature been deprecated, but what is actually happening here is that Management Studio is firing up a different user instance of SQL Server than the one your application is using, so it is not surprising that you can't see the same thing in both cases.

(Usually, the symptom of this is that someone runs an update from their app, then checks the table in Management Studio, thinking there is a bug but not realizing that the update affected a different copy of the database.)

Create the database on your running SQL Server instance (or attach it there). Then connect to that server the same way, without the User Instance setting, from both SSMS and your app. Lots more information here. Don't create the database every time the application starts - that just sounds horrendously wrong.


How do I export data from Oracle 11.1 to 10.1? I have a 11.1 oracle server and I need to export a dump file for a client who has a 10.1 server.

While reading the documentation, I figured out I needed the 10.1 Export Utility. I searched the Oracle site and I couln't find it.

How can I get this utility?
Thanks

To do this, you'll be wanting to use the 11.1 Data Pump (expdp/impdp) rather than exp/imp. With Data Pump you export using the higher version export utility with the VERSION= parameter.

For example:

expdp scott/tiger version=10.1 directory=DUMPDIR dumpfile=DUMPFILE.dmp logfile=DUMP.log

Don't forget to create the directory (using CREATE DIRECTORY DUMPDIR AS '/path/to/dir';) first.


Bacpac Import fails with error 'The Element or Annotation class SqlDatabaseOptions does not contain the Property class CatalogCollation' I want to restore some production azure database on my local machine for development purposes.
So I exported the production database to a .bacpack file & downloaded that file on local.
Now I am trying to restore that file on my local machine using sqlpackage utility but it is throwing following error

  *** Error importing database:An error occurred during deployment plan
  generation. Deployment cannot continue. The Element or Annotation
  class SqlDatabaseOptions does not contain the Property class
  CatalogCollation.

Can someone please tell me what does this error means & how to fix it?

Thanks in advance.

This has been fixed as part of build 14.0.17230.0 (not 14.0.17320.0) of SSMS.
Please update to the latest version.
You can read more about here:
https://blogs.msdn.microsoft.com/azuresqldbsupport/2018/02/15/what-will-happen-with-catalog_collation-and-case-sensitive-vs-case-insensitive/


Run subquery once / optimize join with subquery without needing to use temporary table Until today I thought I had the hang of optimizing queries with joins on subqueries.  But I've been trying to optimize a query and on a whim I tried an old method that I thought I didn't need to use anymore - I used temporary tables.

Slow query (takes 5 minutes)...

select (columns) from big_transactions_table t inner join

  (select (columns) from small_info_table where (conditions)) q1

on q1.key = t.key
group by (columns)

Fast query using a temporary table...

select (columns) from small_info_table into #q1 where (conditions)

go

select (columns) from big_transactions_table t inner join #q1
on #q1.key = t.key
group by (columns)

Took 14 seconds!

I was under the impression that when you have a subquery in a join like this the SQL engine would get the data first before joining it with the outer query.  Now I'm not so sure.   Can anyone tell me why there is a big difference in execution time, and if there's a simple way to speed up the query without using a temporary table?

I was under the impression that when you have a subquery in a join like this the SQL engine would get the data first before joining it with the outer query.

The query optimizer considers various physical execution strategies that are guaranteed to produce the same results as the original logical query specification. So, no, there is no general inference you can make from the written form of the query about how it will actually be executed.

The decision the optimizer makes is based on estimated costs, which are determined to a large extent by the size (cardinality and width) of the data expected at each stage of the pipeline, and the type of physical operation performed.

If your query happens to result in inaccurate cost estimates, the strategy chosen by the optimizer is quite likely not to be optimal, perhaps not even very good at all.

Inaccurate costs have many causes, including unrepresentative statistics on the base objects, and the use of opaque, or difficult to estimate, predicates in the query. With good statistics, a reasonable sized query, simple comparison operations, and a relational schema, the chances are quite good that estimates (and hence the optimizer's physical plan selection) will be at least good enough.

One way to simplify the optimizer's work is to materialize parts of a complex query (or one that simply does not get good estimates) into temporary tables. These give the optimizer direct cardinality information, and SQL Server can usually automatically create statistics on the temporary table to provide further good information. Last, but not least, the temporary table allows you to add specific indexes to it, where that is worthwhile.

When used sensibly*, temporary tables are a great way to provide information to the optimizer, producing reliable execution plans even as the distribution and size of the underlying data changes.

If you have done all you can with statistics, indexing, and the written form of the query, using a temporary table might well be optimal here. It's impossible to say for sure, based on the information given in the question, and not knowing the expertise level of the author.

* Sensible use means e.g. limiting the number, size, and width of temporary tables required, as well as the frequency of use. SELECT * INTO #temp FROM dbo.MyHugeTable; is not sensible, for example.


Does composite cluster index affect performance of seek/scan non-cluster index which is part of the composite? I have bank transaction table with columns

Division
Brcode
TrDate
ProjectCode
Accountnumber
EffectiveDate
DrCr
Amount
EndBalance

In the other table the Brcode itself is unique and is assigned under only 1 division.

I am planing to set Division,Brcode,TrDate as cluster index(composite) and set each of them as non-cluster index individually.

What I curious is

since Brcode is unique for all division so clustering Division,Brcode,TrDate is implicitly clustering Brcode,does Brcode index get benefit from this? Does it need to search all division?
If I search by TrDate it won't get benefit from cluster index,is that right? this column shouldn't include in composite?
If I search by TrDate will cause full table scan or index scan?

ps.this table have 7 millions record so even minor factor will cause a huge different :(

First off, don't mix "primary key" and "clustered index". One is "how to identify a row", the other is "on-disk structure"

Then, you don't need an index on Division by itself because it is the left hand column of the clustered index. However, it won't be covering (see links below)

And for each point in turn:

this table doesn't know that "Brcode is unique for all division". If it is, then brCode should be first and unique and the clustered index by itself
a search on TrDate won't benefit from the clustered index
it depends on selectivity, statistics, how many columns are required. It may seek with a key lookup or it may scan the clustered index (= a table scan) or scan/seek the index on TrDate

Finally, I'd suggest some background reading:

SQL Server Index Basics
Using Covering Indexes to Improve Query Performance


Why is Clustered Index on Primary Key compulsory? Why Primary Key constraint creates Clustered Index on the PK column by default?

Can we create a table which has a primary key, but NO clustered index. I need PK constraint to maintain the unique values + not null on the column, but i do not want a clustered index as
there are too inserts in this table.

I have read that having clustered index has performance impacts on inserts.

Why Primary Key constraint creates Clustered Index on the PK column by default?

That is what MS-SQL-Server programmers decided the default to be. A good clustered index is one that has unique values (as the Primary Key), is narrow (as most primary keys are or at least should be) and is ever-increasing. So, most of the times, the primary key is a good (or the best) choice for the clustered key (there can be at most one clustered key per table).

  Can we create a table which has a primary key, but NO clustered index?

Yes, you can. By explicitely defining all indices and especially the primary key as non-clustered. If you think that you don't need a clustered key on a table, you can do that and have the primary key as non-clustered. The unique and not null constraints will still be enforced.


What happens when a stored procedure executes one of two different queries based on an input parameter? I have encountered a stored procedure that performs one of two different queries based on an input parameter.  Does this remove the plan from cache and force the query plan to be recompiled if the input parameter changes?  What are the performance implications of implementing control flow in this way.

Example:
CREATE PROCEDURE ControlledFlow
    @Flow bit
AS
BEGIN
IF (@Flow =1)
 BEGIN
  SELECT ColA FROM TableA
 END
ELSE
 BEGIN
  SELECT ColB FROM TableB
 END
END

To explain what Erik mentioned I created a stored procedure using your example.

--Creating table

CREATE TABLE [dbo].[Hotel](
    [HotelID] [int] NOT NULL,
    [HotelName] [nvarchar](50) NULL,
    [Rating] [nvarchar](10) NULL,
 CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
(
    [HotelID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
--Creating stored procedure
CREATE PROCEDURE ControlledFlow
    @Flow bit
AS
BEGIN
IF (@Flow =1)
 BEGIN
  SELECT HotelID FROM Hotel
 END
ELSE
 BEGIN
  SELECT HotelName FROM Hotel
 END
END

As soon as you make the first call (irrespective of what value you pass for @Flow parameter) you get a plan for both paths. Below pictures shows both path.

In order to have separate plan for @Flow value you will need something like this.

CREATE PROCEDURE ControlledFlow
    @Flow bit
AS
BEGIN
IF (@Flow =1)
 BEGIN
  EXECUTE SubProcedure1
 END
ELSE
 BEGIN
  EXECUTE SubProcedure1
 END
END


SQL Server: ensure primary key uniqueness over two tables I have a system with an Events table that holds a list of events to process. Once they are processed, their status field is marked such, and they remain there for archive/history purposes. The PK of the Events table is identity.

The Events table is frequently queried for event type, due time, etc. Most of the time only unprocessed events are of interest. But due to the number of records constantly growing, these queries start taking up more time than they should.

So, I'd like to implement a process that regularly brings the old processed events to another table, so that the primary events table remains lean. Ideally, I'd like that other table be named like EventsArchive and keep exactly the same field setup, with one difference that the PK field is not identity. However, now there is a risk that upon inserting new record in Events table, the PK value is something that's already used in the EventsArchive table.

Is there any way how I can guarantee that the PK field values of Events table do not collide with the archived values in EventsArchive table? I can imagine switching to finding the Events.PK value by means of generating random long value and checking if this value is not taken in both Events and EventsArchive. But that doesn't sound right.

For performance in this scenario, you should consider Filtered Index.

It looks like it is ideal solution for your case. Just filter only events with appropiate status and index will be small and fast.

Other pros will be minimal effort to implement this solution (add new index).


Postgres Pass Subquery results to function that returns TABLE I have a function that accepts arrays as parameters and returns a table. I can get the arguments passed in correctly, but it is returning rows of type record. If I try to expand the record with FROM I get syntax errors

SELECT
  zdb.filters(
      sub.idx
    , sub.labels
    , variadic sub.filters
    )
    FROM (
      SELECT
        'conversations_zdx' as idx
        , ARRAY_AGG(id)::TEXT[] as labels
        , ARRAY_AGG(filters) as filters
        FROM conversation_views
        group by organization_id
) as sub;

+------------------------------------------+
| filters                                  |
|------------------------------------------|
| (32a430c6-eb33-4ac1-8ce7-9fb64e5b465c,1) |
| (a9177e44-95bf-4b1b-8724-b48efc97e3cf,4) |
+------------------------------------------+

The function signature is as follows

FUNCTION zdb.filters(
    index regclass,
    labels text[],
    filters JSON[])
RETURNS TABLE (
    label text,
    doc_count bigint)

This will give me errors

SELECT
  label
, doc_count
FROM  zdb.filters(
      sub.idx
    , sub.labels
    , variadic sub.filters
    )
    FROM (
      SELECT
        'conversations_zdx' as idx
        , ARRAY_AGG(id)::TEXT[] as labels
        , ARRAY_AGG(filters) as filters
        FROM conversation_views
        group by organization_id
) as sub;

update 8/26/19

The hangup is that I want to join the result of the function with another table, but I can't seem to get it.

this is the closest I can get, but the result of the function call, is still a record that I can't expand

WITH cviews AS (
    select
        id
    , organization_id
    , created_by
    , created_at
    , modified_by
    , modified_at
    , name
    , description
    , cache
    from conversation_views
    where organization_id = '3b440f43-c5f1-4338-a904-601c4dc8fd15'
)
, aggs AS (
    select organization_id, array_agg(id::text) as labels, array_agg(cache) as filters
      from cviews
      group by organization_id
  )
, counts AS (
  select
    '3b440f43-c5f1-4338-a904-601c4dc8fd15' as organization_id
    , zdb.filters(
        'conversations_zdx'
    , aggs.labels
    , aggs.filters
    )
  from aggs
)
select
cviews.*, counts.*
from cviews
inner join counts on counts.organization_id::UUID = cviews.organization_id::UUID

Write a PL/pgSQL function and iterate through the conversation_view results:

DECLARE
   sub record;
BEGIN
   FOR sub IN
      SELECT 'conversations_zdx' as idx,
             ARRAY_AGG(id)::TEXT[] as labels,
             ARRAY_AGG(filters) as filters
      FROM conversation_views
      GROUP BY organization_id
   LOOP
      RETURN QUERY
         SELECT idx, labels, filters
         FROM zdb.filters(
                 sub.idx,
                 sub.labels,
                 sub.filters
              ) AS q(idx,labels,filters);
   END LOOP;
END;


How to create columns with Count and percentage using MS Access Query I have a data table below, I would like to create a query to produce a result table like below, with count and percentage, using MS Access Query. I am unable to create this.

Name
Alex
Alex
Bob
Bob
Roger
Tim

Below result table

 Name    Count Percentage
 Alex    2     33%
 Bob     2     33%
 Roger   1     16.6%
 Tim     1     16.6%

Something like this?

SELECT Person, Count(Person) AS [Count], 100*(Count(Person)/DCount("*","T1")) AS Percentage
FROM T1
GROUP BY Person;


Dropping a group of schemas with similar name patterns Consider a situation where one need to perform a bunch of essentially identical operations, with the only variable being the name of some object.

In my case, I need to drop some schemas, all of the form ceu_shard_test_merge_*, to use shell globbing terminology. So, conceptually, this can be written as

DROP SCHEMA ceu_shard_test_merge_* CASCADE;

by analogy with a Unix shell.

Of course this command doesn't work, so how can one do this with a single command? My understanding is that this cannot be done portably. I'm using PostgreSQL 8.4, but methods for more recent versions of PG are fine too.

It would be nice if the solution had a dry run or dummy option, so one could see what commands were going to be run before actually running them. Perhaps a way to just print the commands?

Also, an indication of how to deal with more general patterns than the example given would be nice.

This query on the system catalog creates the necessary DDL script:
SELECT string_agg(format('DROP SCHEMA %I CASCADE;', nspname), E'\n')
FROM   pg_namespace
WHERE  nspname LIKE 'ceu_shard_test_merge_%';

Note the use of format() to escape identifiers if necessary.
format() requires PostgreSQL 9.1+.
Replace with quote_ident() in older versions.
string_agg() requires PostgreSQL 9.0+.
Replace with array_to_string(array_agg(...), E'\n') in older versions.
For Postgres 8.4, that would be:
SELECT array_to_string(
          array_agg('DROP SCHEMA ' || quote_ident(nspname) || ' CASCADE;')
        , E'\n')
FROM ...

Returns:
DROP SCHEMA ceu_shard_test_merge_1 CASCADE;
DROP SCHEMA ceu_shard_test_merge_2 CASCADE;
...

Which you can inspect before executing.
You can put it all into a DO command for automatic execution or create a function for repeated use. Consider this closely related answer for a complete code example:

Bug in PL/pgSQL function creation

Or this related answer on SO.
Output in psql
In response to @Stew's comment: To display unadorned text as result in psql you can use the \pset meta-command:
\pset format unaligned
\pset tuples_only

Or use the short commands \x \a


SQL Server 2016 Plugin's not working Recently I installed SQL Server 2016 on my PC.

Here is the detailed information of my server :

  Microsoft SQL Server 2016 (RC1) - 13.0.1200.242 (X64)
  Mar 10 2016 16:49:45
  Copyright (c) Microsoft Corporation
  Enterprise Evaluation Edition (64-bit) on Windows 10 Pro 6.3  (Build 10586: )

After installing server on my PC then tried to install the below plugin's

SQL Pretty Printer Add-In for SSMS V3.6.1
SQLSentry Plan explorer

Problems:

SQL Pretty Printer does not even install it was throwing error like

But the SQL Pretty Printer site says it works on SQL Server 2016 you can check the link.

Next SQLSentry Plan explorer was installed successfully but the plugin was not showing up in SSMS.

Version: v2.8 (Build 9.0.9252.0)

Can anyone help me out with this thing. Note both worked perfectly in SQL Server 2014.

Vendors will need to catch up, as the add-in model and API have changed. This will lead to better extensibility, but for now you'll need to wait for the vendors to do this work. There is usually not much advantage to doing all of that work while the product is still in beta and the add-in model is still changing (the version of SSMS released on Friday is on a completely different VS shell and is radically different, which vendors knew would happen, and that would have rendered any work done against earlier RCs and CTPs a waste).

Please wait for RTM before expecting all vendors to have perfect integration. If you need these add-ins in the meantime, keep a supported version of SSMS installed too.

Or, in the case of Plan Explorer, use a simple workaround - all our add-in really does is provide a shortcut to right-clicking a plan, saving as .sqlplan, and then double-clicking in file explorer or using file/open in Plan Explorer. Or better yet, generate actual plans inside Plan Explorer, where you get much more useful metrics anyway.


ora-04091 table is mutating trigger/function may not see it I have two tables say tab1, tab2
here tab1 is the parent table of tab2, Both have triggers enabled say tab1_trg, tab2_trg. Also in tab2_trg there is a SELECT on tab1.

Now I try to delete some records in tab1, it throws error:
ora-04091 table is mutating trigger/function may not see it
ora-04088 error during execution of trigger tab2_trg

I got the issue that I can not select a table that is being changed at the same time.

But I am not getting this error while constraints are disabled, error is occurring  only when constraints are enabled.
Any idea ?

Thanks in Advance

This is normal if you have your FK constraint defined with the ON DELETE CASCADE option. When you delete from tab1, that will cause deletes from tab2 also because of the constraint definition. If you disable the constraint, rows from tab2 will not be deleted, the trigger on tab2 will not even run. Below is an example:

create table t1 (t1_id number, value varchar2(10), constraint pk_t1 primary key (t1_id));
create table t2 (t2_id number, t1_id number, constraint fk_t1 foreign key (t1_id) references t1(t1_id) on delete cascade);

insert into t1 select rownum, 'HELLO' from dual connect by level<= 5;
insert into t2 select rownum, rownum from dual connect by level <= 5;
commit;

create or replace trigger t2_trg
after delete or insert or update
on t2
for each row
declare
  n number;
begin
  select count(*) into n from t1;
end;
/

If you try to delete now, you will get the infamous table is mutating error:

SQL> delete from t1 where t1_id = 1;
delete from t1 where t1_id = 1
            *
ERROR at line 1:
ORA-04091: table BP.T1 is mutating, trigger/function may not see it
ORA-06512: at "BP.T2_TRG", line 4
ORA-04088: error during execution of trigger 'BP.T2_TRG'

Now with the constraint disabled:

SQL> alter table t2 disable constraint fk_t1;

Table altered.

SQL> delete from t1 where t1_id = 1;

1 row deleted.

Obviously, this works.

You should rethink/rewrite your trigger or constraint considering the above.


How do I force the delete of users from my database? I am making a clean backup of our development database for easy fresh spin-ups of the database. This database has about 200 test users that should be deleted to make it truly clean, but the problem is I am unable to delete any of them.

When I run a command like below:

DROP USER [MyGarbageUser]

It prompts me following error:

  Msg 15284, Level 16, State 1, Line 5 The database principal has
  granted or denied permissions to objects in the database and cannot be
  dropped.

These users neither own any objects in this database(as far as I know) nor they should. After a ton of searching on the net I have still not been able to find solution.

How can I force MyGarbageUser to be deleted from this database?

It seems the user that you are trying to drop has played a role of grantor and hence you need to revoke those accesses before performing drop of user.

Try running below command to get details of grantee first:

select
     permission_name,
     state_desc,
     object_name(major_id) as securable,
     user_name(grantor_principal_id) as grantor
from sys.database_permissions
where grantee_principal_id = user_id('User Name to be dropped')

Next will be to find details on grantor using below:

select *
from sys.database_permissions
where grantor_principal_id = user_id ('User Name to be dropped');

Basis result what you get from above query, you need to perform one of below:

REVOKE VIEW DEFINITION ON USER::User Name to be dropped TO public
REVOKE CONTROL ON USER::User Name to be dropped TO public
REVOKE ALTER ON USER::User Name to be dropped TO public

REVOKE ALTER ON USER::User Name to be dropped TO *grantee*
REVOKE CONTROL ON USER::User Name to be dropped TO *grantee*
REVOKE VIEW DEFINITION ON USER::User Name to be dropped TO *grantee*

This is just a possibility, there could be some other type of access which has been granted. so, you need to check the same and execute revoke accordingly.

You may find more of these examples from below forum:

https://ask.sqlservercentral.com/questions/24721/the-database-principal-has-granted-or-denied-permi.html

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/07b40291-6c4d-427e-a5e2-568df482c550/drop-user-fails-with-quotthe-database-principal-has-granted-or-denied-permissions-to-catalog?forum=sqlsecurity

https://stackoverflow.com/questions/41817820/how-to-solve-sql-server-error-15284-the-database-principal-has-granted

Hope above helps.