How does PARALLEL_MIN_PERCENT affect parallel execution? From the reference for PARALLEL_MIN_PERCENT:

PARALLEL_MIN_PERCENT lets you specify the minimum percentage of the requested number of parallel execution processes required for parallel execution. Setting this parameter ensures that parallel operations will not execute unless adequate resources are available. The default value of 0 means that no minimum percentage of processes has been set.
Consider the following settings:
PARALLEL_MIN_PERCENT = 50
PARALLEL_MIN_SERVERS = 5
PARALLEL_MAX_SERVERS = 10
If 8 of the 10 parallel execution processes are busy, only 2 processes are available. If you then request a query with a degree of parallelism of 8, the minimum 50% will not be met.

From Initializing and Tuning Parameters for Parallel Execution:

[PARALLEL_MIN_SERVERS] Specifies the minimum percentage of requested parallel execution processes required for parallel execution. With the default value of 0, a parallel statement executes serial if no parallel server processes are available.

What happens when the minimum 50% of servers is not available for the query when it runs? Is the query is queued, does it execute serially, or does it throw an error such as insufficient parallel query slaves available?

I believe for that configuration you will get an error like this:

  ORA-12827: insufficient parallel query slaves available

See the references at PSORG and ORAFAQ.

If parallelism is configured differently on the server it's possible for the DOP to be downgraded. In some cases it will be downgraded to execute serially if there aren't enough PX servers available. There are a few examples here for parallel_degree_policy=MANUAL:

  Since there are only 12 available PX servers this statement gets downgraded from DOP=8 to DOP=6 and gets all 12 available PX servers.

  Consider another query submitted at this time with DOP=8. Even though this query requires 16 PX servers it will be given none because there are no available PX servers left. This query gets downgraded to DOP=1 and runs serially.

I have experienced that when running my own code. One way to see when DOP is downgraded is through SQL monitor. Here are a few examples of the messages you can see:

  350 DOP downgrade due to adaptive DOP

  351 DOP downgrade due to resource manager max DOP

  352 DOP downgrade due to insufficient number of processes

  353 DOP downgrade because slaves failed to join


Two-step Replication between SQL 2008 and 2017 I am currently using transactional replication from several SQL 2008 publishers to a central 2014 subscriber/distributor. I need to replicate this data to a 2017 server, which would require a 2017 distributor.

Since 2008 -> 2017 transactional replication is not supported, I am trying to replicate in a second step from the 2014 server.

2008 -------------------> 2014 ------------------> 2017
         Dist: 2014                Dist: 2017

However, I cannot figure out how to get the 2014 server to recognize the 2017 server as the distributor for its publications, while still acting as the distributor for its subscriptions. Specifically, the 2014 server is configured as a publisher at the remote 2017 distributor, but when I try to enable a 2014 database for publication in the GUI, it asks me to first enable the server as a publisher on the local 2014 distributor.

So I want to know:
1. Is it possible to configure a local distributor for subscriptions and a remote distributor for publications?
2. Could I just use the 2017 distributor for both legs? Is 2017 supported to act as a distributor for the 2008 -> 2014 replication?
3. Is there some other best practice for transactional replication from 2008 -> 2017?

I need to replicate this data to a 2017 server, which would require a 2017 distributor.

A SQL 2017 subscriber can use a SQL 2014 Distributor.   See:

https://docs.microsoft.com/en-us/sql/database-engine/install-windows/upgrade-replicated-databases

https://blogs.msdn.microsoft.com/repltalk/2016/08/12/suppported-sql-server-versions-in-replication-topology/


Ubuntu, postgresql, show whole running sql command System

Linux vm16745 3.13.0-77-generic #121-Ubuntu SMP Wed Jan 20 10:50:42 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux
psql (PostgreSQL) 9.3.24

Issue

I don't know if it is right section/forum, but I will try. Is possible to show whole sql command in ps? Now, when I try

ps aux | grep postgres

it shows me only begin of command.

postgres 20211  0.0  0.9 2281708 74304 ?       Rs   09:12   0:00 postgres: shop shop_prod ::1(55623) SELECT

Thanks.

You would want to query the query column of pg_stat_activity with a query like

SELECT query
FROM pg_stat_activity
WHERE state = 'active';

This will show the first 1024 characters of the query.

If that is not enough, raise the track_activity_query_size parameter.


Avoiding code duplication for counting and selecting the same resultset I have a requirement to return the total number of records and paged data from a stored procedure. The row count should only be computed if a boolean parameter is true. How do I perform the 2 queries (counting + paged result) without having to replicate the entire FROM + WHERE sections?

My FROM and WHERE are non-trivial, so I'd like to avoid having to duplicate them across the 2 queries. My primary goal is avoiding duplicate code as opposed to improving performance.

This is what I have currently:

CREATE PROCEDURE [dbo].[myProcedure]
    @includeCount bit,
    --... where parameters
    @skip int,
    @take int,
    @totalCount int OUTPUT
AS
BEGIN
    IF (@includeCount = 1)
        SELECT @totalCount = COUNT(*) FROM <Complex joins + where>

    SELECT (alias1.field1, alias2.field2, alias3.field3)
    FROM <Complex joins + where>
    ORDER BY [alias1].[Key]
    OFFSET @skip ROWS
    FETCH NEXT @take ROWS ONLY
END
GO

Is there a way to "extract" the common parts (the FROM+SELECT statements) in a way as to avoid the massive duplication while retaining the same performance characteristics of the original queries? Would it pose a problem to my ORDER BY and SELECT sections which depend on aliased tables inside FROM? How would I access those?

I was checking ways to make this work and it appears that an inline table valued function is a decent option (even though I'd prefer something that didn't add extra stuff to the database).

CREATE FUNCTION [dbo].[myFunction]
(
    @filter1 int,
    @filter2 int
)
RETURNS TABLE
AS
RETURN
(
    SELECT (alias1.field1 as Key, alias2.field2, alias3.field3)
    FROM <Complex joins + where @filter1/@filter2>
)

By extracting the main complexity into the function, I can now reuse it for both queries easily enough:

CREATE PROCEDURE [dbo].[myProcedure]
    @includeCount bit,
    --... where parameters
    @skip int,
    @take int,
    @totalCount int OUTPUT
AS
BEGIN
    IF (@includeCount = 1)
        SELECT @totalCount = COUNT(*) FROM [dbo].[myFunction](where parameters)

    SELECT *
    FROM [dbo].[myFunction](where parameters)
    ORDER BY [Key]
    OFFSET @skip ROWS
    FETCH NEXT @take ROWS ONLY
END
GO

There is still some form of duplication due to the parameter passing, but I guess that's unavoidable as there is no such thing as a closure in SQL.

The ORDER BY needing to be outside the function is also not very intuitive, but SQL doesn't allow me to specify it by itself in the procedure without carrying over the paging statements.

This works for me so I'm marking my own answer as the answer for now. Obviously open to better suggestions if there are any.


SSIS ETLs deadlocking We have an SSIS package in our SQL Server 2008 server to load data onto our system every night. The SSIS package consists of several ETLs (around 20) that are currently run sequentially.

On some nights it spits out below deadlock messages for certain ETLs.
Some nights pass with no errors at all. Every night what's run on the server is the same.

The specific ETLs deadlocking are not the same. For instance,

Monday it'll be ETL1, ETL3, ETL9.
Tuesday & Wednesday – all OK.
Thursday – ETL2, ETL5, ETL6, ETL7.
Friday – all OK.

Below is the 19th night's error message in SQL Server Agent:

  Executed as user: localhost. Microsoft (R) SQL Server Execute Package Utility  Version 10.0.5500.0 for 64-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  04:26:33  Error: 2016-11-19 04:31:00.39     Code: 0xC0202009     Source: DFT_Extract_ETL1 OLE_DST_ETL_Destination [68]     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.  An OLE DB record is available.  Source: "Microsoft OLE DB Provider for SQL Server"  Hresult: 0x80004005  Description: "Transaction (Process ID 65) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.".  End Error  Error: 2016-11-19 04:31:00.61     Code: 0xC0209029     Source: DFT_Extract_ETL1 OLE_DST_ETL_Destination [68]     Description: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "input "RC_Rows_Inserted_Output_OLE_DST_ACTIVITY" (81)" failed because error code 0xC020907B occurred, and the error row disposition on "input "RC_Rows_Inserted_Output_OLE_DST_ACTIVITY" (81)" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.  End Error  Error: 2016-11-19 04:31:00.84     Code: 0xC0047022     Source: DFT_Extract_ETL1 SSIS.Pipeline     Description: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "OLE_DST_ETL_Destination" (68) failed with error code 0xC0209029 while processing input "RC_Rows_Inserted_Output_OLE_DST_ACTIVITY" (81). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.  End Error  Error: 2016-11-19 04:31:01.11     Code: 0xC02020C4     Source: DFT_Extract_ETL1 OLE_SRC_ETL1[1]     Description: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.  End Error  Error: 2016-11-19 04:31:01.31     Code: 0xC0047038     Source: DFT_Extract_ETL1 SSIS.Pipeline     Description: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on component "OLE_SRC_ETL1" (1) returned error code 0xC02020C4.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  04:26:33  Finished: 04:41:09  Elapsed:  875.54 seconds.  The package execution failed.  The step failed.

Check out this link. It has clear explanation of digging down the issue.

https://stackoverflow.com/questions/8390322/cause-of-a-process-being-a-deadlock-victim


MariaDB doesn't set authentication_string during installation I'm setting up a new server using MariaDB (current stable version of 10.3) and I notice that all of my users - mainly root and debian-sys-maint - do not have an authentication_string after the installation is done, even after running mysql_secure_installation.

I'm about to re-install my server again and wondering how do I set all my users to get an authentication_string during the installation? And if that isn't possible, would it cause problems if I set a password to debian-sys-maint user?

Unlike newer versions of MySQL (5.7+), MariaDB doesn't seem to use the authentication_string of the user table for normal passwords when using the default plugin (i.e. mysql_native_password). Instead, the passwords are found in the password column, like in older versions of MySQL.

However, you can get something into the authentication_string if you create a user using e.g. the pam plugin:

CREATE USER u1@localhost IDENTIFIED VIA pam USING 'mariadb';

(authentiation_string now contains 'mariadb' in plaintext for this user, though this is not the password, just a reference to a PAM configuration file. With PAM the password is no longer stored in MariaDB.)


Unused Indexes - Consider Primary Key Constraint Supporting Indexes? I have given a task to find out the unused indexes in my production environment. I'm using a DMV to find the unused indexes should i include the Primary key Clustered Indexes on it? If a Primary key Index isn't being used, shouldn't that be of some concern? Unless, perhaps, it is a small table that isn't accessed very often?

Primary Key and Unique constraints are enforced by a supporting index for performance and to enforce uniqueness. Just because the index is not used for searching does not mean it isn't being used to guarantee uniqueness of the key values.

Whether or not a table has a primary key constraint or any unique constraints is not a performance decision - it is a data integrity decision. The primary key gets a primary key constraint and the candidate keys get unique constraints.

Whether or not the primary key index is clustered or nonclustered, on the other hand, is a performance question. Doing a Binoogle search on that will find many hits, including https://stackoverflow.com/questions/2206541/index-view-how-to-choose-the-clustered-index.


XAMPP Installation - MySQL shutdown unexpectedly I'm installing XAMPP on my machine. I'm following Derek Banas youtube tutorial, and I also tried myself, but I'm constantly running into the same issues.

I have changed my Apache ports to 1234 (main), and 8181 (SSL) and MySQL ports to (3307). Apache runs fine, but when I run MySQL from the XAMPP control panel it get this error log.

  23:11:36  [mysql]  Error: MySQL shutdown unexpectedly.

  23:11:36  [mysql]  This may be due to a blocked port, missing dependencies,

  23:11:36  [mysql]  improper privileges, a crash, or a shutdown by another method.

  23:11:36  [mysql]  Press the Logs button to view error logs and check

  23:11:36  [mysql]  the Windows Event Viewer for more clues

  23:11:36  [mysql]  If you need more help, copy and post this

  23:11:36  [mysql]  entire log window on the forums.

I have also deleted files from the MySQL folder in the xampp folder recommended in a previous stack overflow question https://stackoverflow.com/a/18162264/9182716

Also when trying to navigate to localhost phpmyadmin I'm prompted with this warning screen.

https://imgur.com/a/7iagc

I had this problem too. If changing MySQL port doesn't solve the issue, which was my case. Simply copy the files in xampp/mysql/backup to xampp/mysql/data. Replace any conflicting files and leave the rest.
Then restart Apache and MySQL from the Xampp control panel.
This should solve the problem.


How to configure postgresql-9.2 to know blk_write_time in pg_stat_database Interested in the procedure for collecting block write statistics of PostgreSQL 9.2.

Steps followed for configuration, could get blk_read time but not blk_write_time

CREATE EXTENSION adminpack; and CREATE EXTENSION dblink;
Changed postgresql.conf file

# - Query/Index Statistics Collector -

track_activities = on
track_counts = on
track_io_timing = on
track_functions = all           # none, pl, all
track_activity_query_size = 1024    # (change requires restart)
update_process_title = on
stats_temp_directory = 'pg_stat_tmp'

# - Background Writer -

bgwriter_delay = 200ms          # 10-10000ms between rounds
bgwriter_lru_maxpages = 100     # 0-1000 max buffers written/round
bgwriter_lru_multiplier = 2.0       # 0-10.0 multipler on buffers scanned/round

Restarted Postgres Server
Performed around 2000 inserts to table
But could not view the blk_write_time.

configuration above is correct. Before viewing the blk_write_time, do a vacuum. If you still see a 0 try increasing the inserts from 2000 to say 1000000.

Step 5:
vacuum;
Step 6:
select datname, blk_write_time from pg_stat_database;


Postgres parallel seq scan making high CPU Utilization When I run a select query which runs on a view in Postgres, it is starting 3 processes for a single query, which is causing high CPU utilization when multiple job runs in parallel.

How to avoid using parallel sequential scans and increase my query performance at the same time?

You can disable parallel query by setting max_parallel_workers_per_gather to 0 in postgresql.conf.

Then the only ways of speeding up processing are

keep the table cached in RAM
avoid a sequential scan by creating an index

If possible, the second way is much better.


Why does Mongo query hang for a long time when creating index? We are migrating our single Mongo instance to Mongo cluster with sharding. I haven't started sharding yet. However, I found out when I try to add an index, I can't query any collection anymore. We don't have this issue in our current Mongo instance. At our current Mongo, we can apply indexing and query the collection at the same time.

This is the command I run to create index on new cluster:

mongos> db.lc_data.ensureIndex({"name": 1})

Now, I open another terminal and query the collection or other collections in the same database.

mongos> db.lc_other.find()

The query will hang!  Why does this happen?

As at MongoDB 3.2, this is expected behaviour for foreground index builds:

  By default, creating an index blocks all other operations on a database. When building an index on a collection, the database that holds the collection is unavailable for read or write operations until the index build completes. Any operation that requires a read or write lock on all databases (e.g. listDatabases) will wait for the foreground index build to complete.

To avoid this behaviour when building new indexes on collections with significant existing data, you should build the index in the background instead:

mongos> db.lc_data.ensureIndex({"name": 1}, {background:true})

MongoDB 2.4+ can build multiple indexes in the background. For more information, see: Background Index Builds.

You may also wish to upvote/watch SERVER-20960: Default index build option support in config/runtime in the MongoDB issue tracker, which is a feature request to make the default index build type configurable (eg. default to background index builds).


How to show NULL results from query? i'm trying to create a procedure with filters for search.

If the parameter is "NULL" the value of parameter will be column value;

Here my problem: When my parameter and column value is NULL.

I have no results because of this.

I know that query: "COLUMN_NAME IS NULL" return the result even if the value is NULL, but i don't know how to implement this.

I trying to do this with "CASE" but without success.

CREATE PROCEDURE SEARCH_QUERYS
        @ID   int,
        @LIVE bit,
        @DATA datetime
AS
BEGIN
        SELECT * FROM TB_SEARCH
        WHERE
        ID = ISNULL(@ID,ID) AND
        LIVE = ISNULL(@LIVE,LIVE) AND
        DATA = ISNULL(@DATA,DATA)
END

ALTER PROCEDURE dbo.SEARCH_QUERYS -- always use schema prefix!
        @ID   int,
        @LIVE bit,
        @DATA datetime
AS
BEGIN
        SELECT ID, LIVE, DATA --, ... don't use SELECT *!
        FROM dbo.TB_SEARCH -- always use schema prefix!
        WHERE
        (ID = @ID OR @ID IS NULL)
        AND (LIVE = @LIVE OR @LIVE IS NULL)
        AND (DATA = @DATA OR @DATA IS NULL); -- always use semi-colons!
END
GO

In some cases though you are better off building the query dynamically.

Refs:

Not using SELECT *
Using schema prefix
Using statement terminators


Handling error of foreign key How to set a FK value NULL upon receiving error for foreign key constraint?

Consider a simple table a

CREATE TABLE test
(
    id int(11) NOT NULL AUTO_INCREMENT
    ex_key int(11),
    FOREIGN KEY(ex_key) REFERENCES ex_table(ex_key) ON DELETE SET NULL,
    PRIMARY KEY (id)
)

If receiving FK error for an invalid ex_key

INSERT INTO test (id, ex_key) VALUES ('22', '22');

we can insert by setting NULL value for the foreign key

INSERT INTO test (id, ex_key) VALUES ('22', NULL);

It can be done by an IF statement, but is there a way to do so in the same query?

I hope to find a solution similar to available ON DUPLICATE KEY UPDATE for UNIQUE columns.

So, when you insert a row into test with an invalid ex_key value (one that isn't in the referenced column in the referenced table), you want that insert to succeed, instead of fail, and you want the invalid value inserted as NULL instead instead of the value you attempted to insert.  Right?

A "before insert" trigger should allow you to accomplish that, by doing the lookup and changing the value of ex_key to NULL if ex_key isn't a legit value.

Of course, there's some overhead on processing any trigger, but if you need this functionality, this is probably the most painless way to do it.

Also, If you already have a "before insert" trigger on this table, you would just add the IF ... END IF block to the body of the existing trigger.

DELIMITER //

CREATE TRIGGER test_bi BEFORE INSERT ON test FOR EACH ROW
BEGIN
    IF (SELECT ex_key FROM ex_table WHERE ex_table.ex_key = NEW.ex_key) IS NULL THEN
        SET NEW.ex_key = NULL;
    END IF;
END;

//

DELIMITER ;

I built a proof of concept of this with MySQL 5.5.27, here: http://sqlfiddle.com/#!2/d0a8e/1/0

Note, this also completely prevents the foreign key error from even occurring, because the trigger replaces NEW.ex_key's incoming value with NULL (when appropriate) before the validity of the value is checked against the foreign key constraint by InnoDB.


Mongodb high lock percentage / slow queries I'm working with an application using MongoDB that is getting a fairly large number of incoming requests and I've noticed that the responses are getting slower as the number of requests get higher.

I'm suspecting that there is some inefficiency with the database. I ran mongostat during one of our higher load periods and the result seems to indicate high lock percentage:

insert  query update delete getmore command flushes mapped  vsize    res faults           locked db idx miss %     qr|qw   ar|aw  netIn netOut  conn set repl       time
*0   1120    479     *0     179   472|0       0  72.1g   146g  1.31g      0 server-prod:15.7%          0       0|0     1|0     1m     4m   298 rs0  PRI   15:37:28
*0   1192    509     *0     178   428|0       0  72.1g   146g   1.3g      0 server-prod:18.9%          0       0|0     0|0     1m     5m   298 rs0  PRI   15:37:29
*0   1107    456     *0     174   362|0       0  72.1g   146g   1.3g      0 server-prod:20.4%          0       0|0     2|1     1m     5m   298 rs0  PRI   15:37:30
*0   1320    556     *0     211   532|0       0  72.1g   146g   1.3g      0 server-prod:19.5%          0       0|0     0|1     1m     6m   298 rs0  PRI   15:37:31
*0   1094    474     *0     179   449|0       0  72.1g   146g   1.3g      0 server-prod:15.7%          0       0|0     0|0     1m     5m   298 rs0  PRI   15:37:32
*0   1120    487     *0     184   458|0       0  72.1g   146g   1.3g      0 server-prod:20.8%          0       0|0     1|1     1m     5m   298 rs0  PRI   15:37:33
*0    807    299     *0     108   270|0       0  72.1g   146g   1.3g      0 server-prod:15.3%          0     103|1     0|3     1m     3m   298 rs0  PRI   15:37:34
*0   1613    709     *0     161   146|0       0  72.1g   146g   1.3g      0 server-prod:63.5%          0       0|0     1|0     2m     7m   298 rs0  PRI   15:37:35
*0   1133    472     *0     167   341|0       0  72.1g   146g  1.31g      0 server-prod:21.2%          0       1|0     0|1     1m     6m   298 rs0  PRI   15:37:36
*0   1292    555     *0     189   440|0       0  72.1g   146g  1.31g      0 server-prod:23.3%          0       5|0     1|4     2m     6m   298 rs0  PRI   15:37:37

The 3rd line from the bottom has a lock percentage of 60% which is pretty crazy considering the average lock percentage is around ~20%. On higher loads the average goes up to 35-40%.

I also ran show log in my production mongo shell and have not seen a single query that ran below 100ms. Some of the queries have an execution time of ~700ms to 1s.

The machine the database is running on might also be the problem (an n1-standard-1 (1 vCPU, 3.8 GB memory) google compute engine VM instance).
The database is also structured as a three-member replica set (one primary and two secondaries with no arbiter).

What are some ways I can troubleshoot and increase my query speed?

Thanks!

EDIT: Forgot to mention that I'm using Mongo v2.6

EDIT: Here is the output of db.stats()

{
"db" : "server-prod",
"collections" : 20,
"objects" : 36560,
"avgObjSize" : 359.20568927789935,
"dataSize" : 13132560,
"storageSize" : 43479040,
"numExtents" : 62,
"indexes" : 24,
"indexSize" : 6189232,
"fileSize" : 67108864,
"nsSizeMB" : 16,
"dataFileVersion" : {
    "major" : 4,
    "minor" : 5
},
"extentFreeList" : {
    "num" : 0,
    "totalSize" : 0
},
"ok" : 1

}

EDIT: Last night I saw my CPU go up to 128% during peak load hours, how is this possible? (This is a single core machine)

EDIT: I created indexes on some of the most queried fields and noticed an increase in performance, however, the update queries are still taking way too long (~500-800ms). What are some optimisations I can do to increase update/insert performance?

To see if the hardware is not limiting:

top/htop => cpu percentage
iostat -x 1 => sysstat tool to see disk r/w limits (%util)

Concerning locking:

Mongo 2.6 : database locking
Mongo 3.0 + MMAPv1 storage engine : collection locking
Mongo 3.0 + WiredTiger storage engine : document locking

If you have 1 huge collection (server-prod), maybe Sharding is an option to distribute the load, or more cores + less locking with Mongo3.0

Improve indexes:
 - More indexes = slower write + faster read
 - Less indexes = faster write + slower read

Read from Secondaries, Only write on Primary.

> db.setProvilingLevel(1,4)  ##  save slow logs for that db slower than 4ms
> db.system.profile.find({millis:{$gt:100}}).sort({ts:-1}) ## find queries slower than 100ms, order by timestamp descending
> ....query.explain()  ## find out which indexes it uses

Information:
http://docs.mongodb.org/manual/administration/optimization/


MySQL: What points should we consider when create composite index There are multiple views behind the logic of creating composite index in a table. Creating composite index is obvious when we have composite primary key.
Beside this, we create composite index on the basis of our SQL query requirements. Ordering of column in a composite index plays a vital role in the selection of right index in query execution which improves the performance.
If a composite index has only 2 columns then its easy to determine their order.

But, how we will decide the ordering of columns if we have many
columns and many different SQL queries.
What are the other points which should be focused when create composite index?
What is the role of cardinality and modality of a column in creating index and composite indices?

But, how we will decide the ordering of columns if we have many columns and many different SQL queries.

More selective columns should go first.

  What are the other points which should be focused when create composite index?

Not necessary all fields on an index may be used. For example, if you have index (a, b, c) and WHERE clause looks like

WHERE a = 5 and b < 10 and c = 6

then only a and b fields may be used. A field with < or > will be the last one that's used.

  What is the role of cardinality and modality of a column in creating index and composite indices?

Cardinality of a set is number of element in the set. The higher cardinality of a field values set the closer the fields should come in the composite index.


What does "GIN doesn't support full index scans" mean? I'm a RDBMS newbie and I have a PostgreSQL database of more than 50 millions lines, growing everyday. There is a column 'text', which contains human-generated text of around 20 words. I need to be able to retrieve the texts containing a word or two belonging to a pre-set, almost 'constant', list of words.

I believe that the best to do so is to use full text search, based on my own dictionary and configuration, with a GIN or GiST index. That was for some context.

The pgsql doc states:

  GIN doesn't support full index scans.

I don't understand what that means.
Does it mean that if I query the word 'hello' it is possible that a line which text contains the word 'hello' could be not returned?

That would not be great, I'd rather be positive that I get exactly and fully what I want.

Thanks,

Arthur

a word or two belonging to a pre-set, almost 'constant', list of
  words.

Have you considered scanning the texts as they are input and flagging them (possibly with a many-to-many link to another table containing your list of keywords)?


Is it recommended to install extensions into pg_catalog schema? Since objects in pg_catalog schema are implicitly in the search_path (docs), would it be recommended to install extensions in that schema?

Don't install extensions to pg_catalog (unless that's their default: very few extensions are designed that way), because you don't mess with system catalog, ever. @Chris demonstrates one reason why. There are others.

However, the "public" schema is in no way special. It's just the default schema that's pre-installed in standard distributions so we can get started right away. Some DB admins don't use the "public" schema at all, some even delete it.

CREATE EXTENSION is not affiliated to the "public" schema. It installs into the current schema unless instructed otherwise - except some extensions have a pre-set schema (like PGQ / Londiste). The documentation:

  schema_name

  The name of the schema in which to install the extension's objects,
  given that the extension allows its contents to be relocated. The
  named schema must already exist. If not specified, and the extension's
  control file does not specify a schema either, the current default
  object creation schema is used.

  Remember that the extension itself is not considered to be within any
  schema: extensions have unqualified names that must be unique
  database-wide. But objects belonging to the extension can be within schemas.

Bold emphasis mine.
Decide how to manage users, schemas and the search_path:

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

Then decide where to install extensions. You can install to any schema of your choice and include that schema in the default search_path for all users or just for some or no users at all (so that qualified references are required). It all depends on what you want to achieve.
Whatever you do, stay consistent.

I like to install extensions (that allow it) in a dedicated "extensions" schema, which I include in the default search_path after "public" (and "$user" - if you use that). Helps with a clean separation of my own public functions and other public objects. My setting in postgresql.conf:

search_path = "$user",public,extensions

Or:

search_path = public,extensions

And I install extensions with:

CREATE EXTENSION some_extension SCHEMA extensions;

One thing to note: This way you can "hide" (unqualified) objects in the extensions schema behind objects of the same name (and parameters) in the public schema.

Related:

Best way to install hstore on multiple schemas in a Postgres database?
How can I fake inet_client_addr() for unit tests in PostgreSQL?


Nightly reload of tables from a linked server I have a number of tables that need to be reloaded nightly from a linked server.  My current method of doing this is to create a nightly job to pull the data using something like the following:

TRUNCATE TABLE [TableA]
INSERT INTO dbo.[TableA]
    SELECT [Col1], [Col2], [Col3]
    FROM [linked].[server].[dbo].[TableA]

On some of these tables have millions of rows of data are taking hours to complete.  The job sometimes finishes after 8am which is causing performance issues for the applications using the database due to users logging in and querying the db.  Most of the tables have multiple indexes.

I'm curious if there is a better method to speed up the process. Furthermore, is it possible and would it be faster to not truncate the table and instead only pull over any new or changed data for each of the tables.

It depends, but options include (and are not limited to):

Restore a backup of the linked database on the local instance (for better uptime, restore to a new name, then DROP DATABASE the old one and then sp_rename the fresh restore), and either

create synonyms to the restored copy from your current local database so as to allow current queries to continue

optionally drop every table except the ones you need, SHRINK, and then do index maintenance to make up for the index fragmentation the SHRINK caused (all before the sp_rename)

do your "table refresh" from the restored database, then DROP DATABASE the restore database

Use OPENQUERY or EXECUTE AT to send a good JOIN and/or WHERE clause or set of clauses to the linked server, including the maximum update date your local server has, so you retrieve (and insert or update) only the rows that have been inserted or updated since your last refresh

deal with deleted rows painfully

Use a history table on the remote database to track inserts, updates, and deletes, and use that in your JOIN and/or WHERE clauses sent using OPENQUERY or EXECUTE AT.


Convert varchar2 column to number in Oracle I have a table emp_details.

The column emp_salary is of type varchar2 in which certain rows are null:

emp_salary
100,34
null
20,2
30,3
null

I need a result in which the column type is number:

emp_salary
100.34
null
20.2
30.3
null

My attempt in code is:

select emp_salary
from emp_details
where emp_salary = to_number(emp_salary ,'9999D99','nls_numeric_characters=,.');

Error:

  ORA-01722: invalid number
  01722. 00000 -  "invalid number"
  *Cause:    The specified number was invalid.
  *Action:   Specify a valid number.

How can I fix it?

I don't mean to compare the values. I just want to change the varchar to number. I will use it in a small part of my bigger function, using TO_NUMBER.

You are sort of almost there. You sound like you're doing a conversion of a selected value rather than a filter and you need to cater for NULLs so:-

SELECT TO_NUMBER(NVL(emp_salary,'0'),'9999D99','nls_numeric_characters=,.')
FROM emp_details


Finding parent by set of children Given a many-to-many relationship (product/component), I need to find a product (or all products) that are composed from the given set of components.  I can't wrap my head around the SQL query for this. A general solution, but if there is anything particular to SQLServer, that will work too.

Update: assume the standard many-to-many ternary mapping.

This is a case of "relational division". I tagged the question accordingly, you can find basic information in the tag info.

You did not provide details. Assuming a standard many-to-many (n:m) relationship between products and components which is implemented with a product_component table, a basic solution could look like this:

SELECT c1.product_id
FROM   product_component c1
JOIN   product_component c2 ON c2.product_id = c1.product_id
JOIN   product_component c3 ON c3.product_id = c1.product_id
WHERE  c1.component_id = 1
AND    c2.component_id = 2
AND    c3.component_id = 3;

This needs at least an index on product_id and on component_id to be fast, ideally on (component_id, product_id).

There are many other ways. The best query depends on your exact table definition and data distribution. We assembled an arsenal of queries under this related question on SO,  for Postgres / MySQL, but the SQL is (mostly) the same:

How to filter SQL results in a has-many-through relation

The above query finds products that contain at least the components with component_id 1, 2 and 3. To find products that consist of those components exactly (no others components):

SELECT c1.product_id
FROM   product_component c1
JOIN   product_component c2 ON c2.product_id = c1.product_id
JOIN   product_component c3 ON c3.product_id = c1.product_id
LEFT   JOIN product_component c0
                           ON c0.product_id = c1.product_id
                          AND c0.component_id NOT IN (c1.component_id
                                                    , c2.component_id
                                                    , c3.component_id)
WHERE  c1.component_id = 1
AND    c2.component_id = 2
AND    c3.component_id = 3
AND    c0.component_id IS NULL -- no other component

Again, there are various ways exclude products with additional components:

Select rows which are not present in other table


Incorrect syntax near the keyword 'Exec' Can you please tell me what is wrong in this query? I am trying to write a SQL query to create a table dynamically

DECLARE @SQLString NVARCHAR(MAX)

SET @SQLString =+ 'Create Table' + GETDATE() + '_' + 'Table' + '' + '(' + 'Column1' + ' ' + 'Nvarchar(50)' + 'Null' + ')' +

Exec @SQLString ;

There are multiple issues with your string @SQLString:

You don't require + at the beginning and at the end
A date will not concatenate with a nvarchar without conversion (CAST or CONVERT)
Because your table will contain spaces you need to put the table name in square brackets: [ and ].
You will be better off with exec sp_executesql @SQLString

So your script might work with this:

DECLARE @SQLString NVARCHAR(MAX)
SET @SQLString = 'Create Table [' + CAST(GETDATE() AS NVARCHAR(30))+ '_Table]' + ' (' + 'Column1' + ' ' + 'Nvarchar(50) Null' + ')'
PRINT @SQLString
-- Exec sp_executesql @SQLString

I'd recommend reading up on the CREATE TABLE statement and on the String Functions. Then carry on to the Data Type Conversion documentation and have a glimpse at the sp_executesql syntax.

Good Luck.


Help improving SQL Server 2005 query execution time One of my stored procedure has long execution time (average around 4 to 7 minutes). I want to make it run faster.

I am looking at the execution plan and two things are contributing most of the cost. First represents 68% of the cost with a "Clustered Index Scan" of one main tables for reporting. This table has primary key of two columns and 2.000.000 records inside.

Second most demanding task represents 26% of the cost with a "Merge join" (left outer join). This is joining table I mentioned above via a foreign key to one out of the two columns of primary key in above table.

I checked Index physical statistic and index for these tables are in good condition.

What can I do to increase performance of this stored procedure? Do I need to make new index on these tables? Would set transaction isolation level read uncommitted statement help to improve performance?

If you post schema and query etc, that would be useful, in addition to the other 3 answers.

Some links anyway, from Simple-Talk. Good stuff.

Execution Plan Basics
Graphical Execution Plans for Simple SQL Queries
Understanding More Complex Query Plans


MS SQL Server 2012 Error on TLog Backup I have a MS SQL Server 2012 installation on which I am restoring a SQL 2005 .bak which includes some full-text catalogues

The original .bak has no conflicts with 2012 (as the migrate advisor was used to clear all of these). Upon running a TLog backup I receive the following error message:

Executing the query "BACKUP DATABASE [databaseName] TO  DISK = N'..." failed with the following error: "An error occurred while processing 'BackupMetadata' metadata for database id 5 file id 1. Inconsistent metadata has been encountered. The only possible backup operation is a tail-log backup using the WITH CONTINUE_AFTER_ERROR or NO_TRUNCATE option. BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

After much searching I am at a loss as to why. Any suggestions greatly appreciated.

Turns out we needed to remove the full text catalogs from 2005 then take the backup. We then recreated the catalogs in 2012 and all is good. Following a single full backup, the t-log maintenance plan runs fine.


Most efficient way to structure segmented data I am faced with a set of data which I am currently unsure on how to store efficiently. The data in question relates to a calendar which stores information on two different categories of event which have information both common and specific to their categories. From my application, I need to be able to withdraw all events (of both cat a and b for user 1), as well as events specific to their type (e.g all of cat a for user 1 or all of cat b for user 1).

Currently, I am storing the two different categories in their own respective tables, for instance tbl_cat1_events and tbl_cat2_events. A sample is below.

tbl_cat1_events
------------------
event_id
user_id
start_time
end_time
cat1_foo1
cat1_foo2

tbl_cat2_events
------------------
event_id
user_id
start_time
end_time
cat2_foo3
cat2_foo4

This is problematic for several reasons. First of all, when submitting a new event, I have to query both tables in order to verify there isn't a conflicting event going on at the same time. Obviously this is caused by both tables containing common information. Furthermore, when retrieving events, I'll need two queries, selecting events from both tables, after which running a quick sanity check for clashes. Within my code, I'll also need different data structures for both categories.

How can I improve the structure of my database to be more centralized while requiring less queries and generally being more efficient?

One possibility, stated simply, is the events are all in a calendar table, along with any non-category-specific attributes... then tables for the separate types of events to store the category-specific attributes.

tbl_calendar
------------------
event_id (PK)
user_id (FK -> user.id)
start_time
end_time

tbl_cat1_event_info
------------------
event_id (FK -> tbl_calendar.event_id)
cat1_foo1
cat1_foo2

tbl_cat2_event_info
------------------
event_id (FK -> tbl_calendar.event_id)
cat2_foo3
cat2_foo4

This query would return essentially the same result as a union query of the two original tables.

SELECT *
  FROM tbl_calendar tc
  LEFT JOIN tbl_cat1_event_info c1 ON c1.event_id = tc.event_id
  LEFT JOIN tbl_cat1_event_info c2 ON c2.event_id = tc.event_id
 WHERE c1.event_id IS NOT NULL OR c2.event_id IS NOT NULL;
-- see the expanded version of this query below

That last line isn't technically necessary but it assures that no events that aren't either in c1 or c2 don't get returned.

Getting just the cat1 events:

SELECT *
  FROM tbl_calendar tc
  JOIN tbl_cat1_event_info c1 ON c1.event_id = tc.event_id;

Follow-up: Avoiding events inappropriately being assocated with more than one category

The business rules say that an event is always either a cat1 event or a cat2 event, never both... but the structure I suggested above does not contain any mechanism for avoiding the inconsistency of the same event_id being inserted in both the cat1 and cat2 event info tables, which would then indicate that one event was both types of events... which can't be true.

If we add an event_type_id column to tbl_calendar, we can also modify the joins so that only the appropriate *event_info table is joined.

-- assumes tbl_calendar has an event_type_id column
SELECT *
  FROM tbl_calendar tc
  LEFT JOIN tbl_cat1_event_info c1 ON tc.event_type_id = 1 AND c1.event_id = tc.event_id
  LEFT JOIN tbl_cat1_event_info c2 ON tc.event_type_id = 2 AND c2.event_id = tc.event_id
 WHERE c1.event_id IS NOT NULL OR c2.event_id IS NOT NULL;

This would not actually prevent the event from being inserted but it would result in it not being evaluated if it appears in the wrong table; only the correct table according to event_type_id would be joined for each event.

A more aggressive (but perhaps also more correct) approach in addition to the above would be before-insert and before-update triggers on the *event_info tables that checked the other *event_info table and used SIGNAL (introduced in MySQL 5.5) to throw an exception to prevent the incorrect data from being inserted or updated.  One example:

DELIMITER //
CREATE TRIGGER tbl_cat1_event_info_bi BEFORE INSERT ON tbl_cat1_event_info FOR EACH ROW
BEGIN
    IF EXISTS (SELECT c2.id FROM cat2_event_info c2 WHERE c2.id = NEW.id) THEN
        SIGNAL SQLSTATE '45000'
               SET MESSAGE_TEXT = 'Conflicting value found in cat2_event_info';
    END IF;
END;
//
DELIMITER ;


Storage pool configuration for SQL Server We are in the middle of building a new SQL Server 2017 always-on cluster in Azure VM. The data disk layout of VM are as follows

TempDb      :   2    Azure Disk Read Cache
TempDBLog   :   1    Azure Disk No Cache
UserDB      :   3  Azure Disk   Read Cache
UserDBLog   :   2  Azure Disk   No Cache

My plan is to create a multiple storage pool, i.e TempDB storage pool with 2 TempDB disk, UserDB storage pool with 3 User db Azure Disk.

However, according to the Microsoft's documentation on SQL Server in Azure VMs:

  If you are using a disk striping technique, such as Storage Spaces,
  you achieve optimal performance by having two pools, one for the log
  file(s) and the other for the data files. However, if you plan to use
  SQL Server Failover Cluster Instances (FCI), you must configure one
  pool.

Is this applicable to Always-On or just applicable to shared storage FCI. Please guide me, Please share good articles/docs for reference.

Thanks

Is [using a single pool] applicable to Always-On or just applicable to shared storage FCI.

FCI's in Azure use storage spaces direct for disk replication, which I imagine is the source of this requirement.  For AGs you can use multiple separate disks or pools.  You should ensure that the same logical volumes and paths are available on each node, otherwise AG replication will stop if you perform file and filegroup operations on the primary.  EG if you ad a file 'g:\sql\data\foo.ndf' to a database, that path needs to exist on all the other cluster nodes for that change to be replicated.  See Troubleshoot a Failed Add-File Operation (Always On Availability Groups).

Also, wherever you put Tempdb data files, put Tempdb log there too.  Splitting out tempdb's log is overkill as it's likely to be underutilized, and its IO access patterns are more like tembdb data files than user database log files.  In particular transaction commit doesn't flush the tempdb logs.


Getting each status change in a table I have a table that holds status changes per item.  The item can go in and out of a status multiple times.

ItemTable (ItemId int, Status varchar(50), DateOfChange date)

I need to pull the date of each change. However, since the statuses can be repeated for each Item, I can't just do the min(DateOfChange) and find the occurrences.

This is in an OLAP environment, and the subquery that's been used to pull this data is KILLING performance.

Is there a way to pull this stuff via partitioning/rownumber functions?  Or something else that would behave better in OLAP? (This is in SQL 2008.)

This type of requirement comes under the banner of "gaps and islands". A popular approach is

WITH T
     AS (SELECT *,
                DENSE_RANK() OVER (PARTITION BY ItemId ORDER BY DateOfChange) -
                DENSE_RANK() OVER (PARTITION BY ItemId, Status ORDER BY DateOfChange) AS Grp
         FROM   ItemTable)
SELECT ItemId,
       Status,
       MIN(DateOfChange) AS Start,
       MAX(DateOfChange) AS Finish
FROM   T
GROUP  BY ItemId,
          Status,
          Grp
ORDER  BY Start


Trigger To Find Deleting Job Agent History We are trying to debug why SQL Agent Job history is getting deleted every 8 hours. In reading the following article, it suggests many great ideas like:

Create a trigger to check what application is deleting the job history
Check to see if we have a Job that is perhaps purging the job history
Check if the sp_purge_jobhistory stored procedure in msdb database is being used manually to delete the SQL Server agent job history

In the article, the trigger they created returned the following as the "Application Name" value:

SQLAgent - Step History Logger
SQLAgent - Job Manager

How would I create a trigger like this?

Here is the code to create an audit table and an audit trigger containing the information you are requesting. I have tested this and it gives me the value SQLAgent - Job Manager under the column AppName. This logs consistently when jobs run and it deletes out data in my history. (I changed it to keep 2 rows in the history to force it to delete to test.)

So the function APP_NAME() is really what you were looking for.

You can find more information related to this by BateTech on Stack Exchange.

CREATE TABLE msdb.dbo.HistoryPurgeReport
(PurgeID INT Identity(1, 1) PRIMARY KEY, [instance_id] [int] NOT NULL, [job_id] [uniqueidentifier] NOT NULL,
[step_id] [int] NOT NULL, [step_name] [sysname] NOT NULL, [sql_message_id] [int] NOT NULL,
[sql_severity] [int] NOT NULL, [message] [nvarchar](4000) NULL, [run_status] [int] NOT NULL,
[run_date] [int] NOT NULL, [run_time] [int] NOT NULL, [run_duration] [int] NOT NULL,
[operator_id_emailed] [int] NOT NULL, [operator_id_netsent] [int] NOT NULL, [operator_id_paged] [int] NOT NULL,
[retries_attempted] [int] NOT NULL, [server] [sysname] NOT NULL, UpdatedBy NVARCHAR(128), UpdatedOn DATETIME,
AppName nvarchar(255), HostName nvarchar(255), ContextInfo nvarchar(255))
GO

USE MSDB

CREATE TRIGGER trg_HistoryPurgeReport ON msdb.dbo.sysjobhistory
AFTER DELETE
AS
BEGIN
    INSERT INTO HistoryPurgeReport ([instance_id], [job_id], [step_id], [step_name], [sql_message_id], [sql_severity],
    [message], [run_status], [run_date], [run_time], [run_duration], [operator_id_emailed], [operator_id_netsent],
    [operator_id_paged], [retries_attempted], [server], UpdatedBy, UpdatedOn, AppName, HostName, ContextInfo)
    SELECT [instance_id], [job_id], [step_id], [step_name], [sql_message_id], [sql_severity],
    [message], [run_status], [run_date], [run_time], [run_duration], [operator_id_emailed], [operator_id_netsent],
    [operator_id_paged], [retries_attempted], [server], SUSER_SNAME(), getdate(), APP_NAME(), HOST_NAME(), CONTEXT_INFO()
    FROM deleted
END
GO


All in one query for tracking changes in sql server Is there any feature in SQL Server 2012 or in any version, wherein I can track all DB activities (DDL and DML) and others in one single query with who has modified and from where and when? I need hostname or IP address as well.

I tried below features which has some limitations

SQL Audit (Unable to track IP)
Event (Unable to track DML operations)
Trace (Unable to track DML operations)

I don't want to use triggers.

I would set up an extended events session to monitor these kind of activities and then review the results of the session from time to time.  You can also set up policies that prevent DDL changes.


index rebuild job is failing I am new to DBA job and in my environment index rebuild job is failing and when I checked reason for failure it is showing  :

  "The transaction log for database 'x' is full due to 'AVAILABILITY_REPLICA'.

We are using SQL Server 2014 with full recovery mode.

It sounds like you are using Availability Groups and either one of your replicas is not syncing correctly, or it is getting so far behind that it is causing the log on your primary to grow until is reaches its maximum size (or you run out of disk).

Check that your secondary replica(s) are online and syncing
correctly.
See if you can increase the max size of your transaction
log on the primary
Increase the allocated storage on your transaction
log drive
Rebuild individual indexes with a wait between them to help
allow time for log blocks to flush down to your secondary replica(s) (check out Minion Reindex free tool that can help with this)


MSSQL NULL's Between specific dates I have a TSQL script that counts transactions for companies based on all approved, declined and failed trans in the previous month(s).

I also show companies that have 0 approved, declined and failed using the line

OR 'datecolumnName' IS NULL

The script is missing companies that HAVE values in 'datecolumnName' but not for the month I am interrogating.  I want somehow to say where 'datecolumnName' is NULL OR NULL for 'This Month'

E.G Company ABC didn't transact in DEC but did in NOV so the OR 'datecolumnName' NULL script doesn't bring back any data for Company ABC

(Apologies if this is confusing didn't find it easy to explain)

In response to Squirrels message I have attached the code I am running anonymized

DECLARE @startOfCurrentMonth DATETIME
SET @startOfCurrentMonth = DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP), 0)
DECLARE @startOfPreviousMonth DATETIME
SET @startOfPreviousMonth = DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP), 0)

SELECT Object1.Column1 AS CompName,
       SUM(CASE
               WHEN Object2.Column3 = '00'
               THEN 1
               ELSE 0
           END) AS Column4,
       SUM(CASE
               WHEN(Object2.Column3 != '00'
                    AND SUBSTRING(Object2.Column3, 1, 1) >= 0
                    AND SUBSTRING(Object2.Column3, 1, 1) <= 9)
               THEN 1
               ELSE 0
           END) AS Column5,
       SUM(CASE
               WHEN(Object2.Column3 != '00'
                    AND SUBSTRING(Object2.Column3, 1, 1) >= 'A'
                    AND SUBSTRING(Object2.Column3, 1, 1) <= 'Z')
               THEN 1
               ELSE 0
           END) AS Column6,
       Object1.Column7
FROM Object3 Object1 WITH(NOLOCK)
     FULL OUTER JOIN Object4 Object5 WITH(NOLOCK) ON Object1.Column7 = Object5.Column8
     FULL OUTER JOIN Object6 Object2 WITH(NOLOCK) ON Object5.Column9 = Object2.Column10

                            WHERE Object2.Column11 >= DATEADD(month, -1, @startOfCurrentMonth)
                            AND Object2.Column11 < @startOfCurrentMonth
                    OR Object2.Column11 IS NULL

     GROUP BY Object1.Column1,
         Object1.Column7
ORDER BY Object1.Column1

This is the output

The rows with all '0' means that company has never transacted but if a company has transacted in a different month the row is ignored.

Move the condition from WHERE to JOIN:

FROM Object3 Object1 WITH(NOLOCK)
FULL OUTER JOIN Object4 Object5 WITH(NOLOCK) ON Object1.Column7 = Object5.Column8
FULL OUTER JOIN Object6 Object2 WITH(NOLOCK) ON Object5.Column9 = Object2.Column10
                                            AND Object2.Column11 >= DATEADD(month, -1, @startOfCurrentMonth)
                                            AND Object2.Column11 < @startOfCurrentMonth


How to query from a linked server via a linked server in sql server? I have the following set-up:

SQL-Server 1  --linked-->  SQL-Server 2  --linked->  SQL-Server 3

The question is can I query SQL Server 3 from SQL Server 1 via SQL Server 2?

I have unfortunately several restrictions:

cannot use openquery
cannot use views on Server 2 (there would be many of them and they'd have to be maintained)
cannot create a link from Server 1 to Server 3 (due to firewalling issues)
servers are MS SQL Servers

Unfortunately, with the conditions you've placed on your question, there is no way to accomplish what you want.
SQL Server would need to support 5 part naming such as:
SELECT *
FROM server1.server2.database.schema.table;

Which is clearly not going to work.
If you're not afraid of a little dynamic SQL, you could use something like this:
DECLARE @cmd nvarchar(max) = N'
DECLARE @cmd nvarchar(max) = N''
SELECT @@SERVERNAME; --this would be where your query goes.
'';
EXEC (@cmd) AT linked_server_2;
';
EXEC (@cmd) AT linked_server_1;

Essentially, the above code executes a dynamic SQL string over linked_server_1, but the dynamic SQL is actually an embedded dynamic SQL string, which executes a query at linked_server_2.


Only if you count SQL itself :) The DB does not have any way of solving this type of thing other than the naive way, but the naive way of generating all possible distributions of rows to groups, filtering to only those that satisfy the num_users_in_longest<=(num_users_in_shortest+1) requirement, and then calculating the delta for each distribution and using a LIMIT 1 to filter to the smallest one can be expressed in SQL without too much awkwardness. Here's a fiddle that does what you want. Again, this is doing the underlying work in the naive, inefficient way, and this won't scale to large datasets. For that, you'll need to find some clever algorithm to do this, and probably do it in a procedural language that gives you the flow control needed to implement it.


How to deal with circular foreign key I have two tables:

Games(
    name,
    current_step, #foreign key to Steps table
    #other fields...
)

Steps(
    name,
    game_id, #foreign key to Games table
    #other fields...
)

In other words what I want to achieve is a Steps table where each step is linked to its game and in some way I want to keep track of current step. Which is the best design to do this? Is circular foreign key acceptable?

Don't put the step in the games table, it's unnecessary.

In your steps table, make a bit column like current_step, and add a unique filtered index on (game_id, current_step) WHERE current_step = 1.

This will prevent duplicates and prevent the circular reference and data integrity issues that are sure to ensue.


What is the difference between public_dependency and dba_dependencies? I know that PUBLIC_DEPENDENCY has only the objects and references Ids, and the DBA_DEPENDENCIES does not have the Ids, but the Type, Owner, Name, etc of them.

But beyond that, what is the difference between both? When I do a count on my database, they return different values. And PUBLIC_DEPENDENCY has more elements.

Check the source of both views:

select dbms_metadata.get_ddl('VIEW', 'PUBLIC_DEPENDENCY', 'SYS') from dual;
select dbms_metadata.get_ddl('VIEW', 'DBA_DEPENDENCIES', 'SYS') from dual;

PUBLIC_DEPENDENCY queries directly from the dictionary table dependency$ and nothing else.
DBA_DEPENDENCIES also queries dependency$, but joins some other tables and views as well, for example disk_and_fixed_objects. This view does not contain all the objects from dependency$, so this join eliminates some rows from dependency$ related to the missing objects. For example on my database:

select distinct p_obj# from dependency$ d where not exists (select 1 from disk_and_fixed_objects f where f.obj# = d.p_obj#);

    P_OBJ#
----------
4294951597
4294952371
4294952370
4294952376
4294952374
4294952368
4294952372
4294952375
4294951440
4294952364
4294952365
4294952366
4294952373

The source of disk_and_fixed_objects is:

  CREATE OR REPLACE FORCE NONEDITIONABLE VIEW "SYS"."DISK_AND_FIXED_OBJECTS" ("OBJ#", "OWNER#", "NAME", "TYPE#", "REMOTEOWNER", "LINKNAME", "EDITIONNAME") AS
  select co.obj#, co.owner#, co.name, co.type#, co.remoteowner, co.linkname,
       co.defining_edition
from sys."_CURRENT_EDITION_OBJ" co
union all
select kobjn_kqfp, 0, name_kqfp,
decode(type_kqfp, 1, 9, 2, 11, 3, 10, 0), NULL, NULL, NULL
from sys.x$kqfp
union all
select kqftaobj, 0, kqftanam, 2, NULL, NULL, NULL
from sys.x$kqfta
union all
select kqfviobj, 0, kqfvinam, 4, null, null, null
from sys.x$kqfvi

But the above 13 objects are not accounted for in the above underscore view or X$ tables, they are in another X$ table (X$KQFDT):

select * from x$kqfdt where kqfdtobj in ( select distinct p_obj# from dependency$ d where not exists (select 1 from disk_and_fixed_objects f where f.obj# = d.p_obj#));

ADDR                   INDX    INST_ID     CON_ID   KQFDTOBJ KQFDTNAM                       KQFDTEQU
---------------- ---------- ---------- ---------- ---------- ------------------------------ ------------------------------
000000000DF41A00          4          1          0 4294951440 X$KCVFHALL                     X$KCVFH
000000000DF41C80         20          1          0 4294951597 X$JOXFS                        X$JOXFT
000000000DF41D20         24          1          0 4294952364 X$JOXOBJ                       X$JOXFT
000000000DF41D48         25          1          0 4294952365 X$JOXSCD                       X$JOXFT
000000000DF41D70         26          1          0 4294952366 X$JOXRSV                       X$JOXFT
000000000DF41DC0         28          1          0 4294952368 X$JOXDRC                       X$JOXFT
000000000DF41E10         30          1          0 4294952370 X$JOXMOB                       X$JOXFM
000000000DF41E38         31          1          0 4294952371 X$JOXMIF                       X$JOXFM
000000000DF41E60         32          1          0 4294952372 X$JOXMIC                       X$JOXFM
000000000DF41E88         33          1          0 4294952373 X$JOXMFD                       X$JOXFM
000000000DF41EB0         34          1          0 4294952374 X$JOXMMD                       X$JOXFM
000000000DF41ED8         35          1          0 4294952375 X$JOXMAG                       X$JOXFM
000000000DF41F00         36          1          0 4294952376 X$JOXMEX                       X$JOXFM

13 rows selected.

Here you can see the name of these objects in the KQFDTNAM column. Another way to find them is querying the V$FIXED_TABLE view:

select * from v$fixed_table where object_id in (select distinct p_obj# from dependency$ d where not exists (select 1 from disk_and_fixed_objects f where f.obj# = d.p_obj#));

NAME                            OBJECT_ID TYPE   TABLE_NUM     CON_ID
------------------------------ ---------- ----- ---------- ----------
X$KCVFHALL                     4294951440 TABLE      65537          0
X$JOXFS                        4294951597 TABLE      65537          0
X$JOXOBJ                       4294952364 TABLE      65537          0
X$JOXSCD                       4294952365 TABLE      65537          0
X$JOXRSV                       4294952366 TABLE      65537          0
X$JOXDRC                       4294952368 TABLE      65537          0
X$JOXMOB                       4294952370 TABLE      65537          0
X$JOXMIF                       4294952371 TABLE      65537          0
X$JOXMIC                       4294952372 TABLE      65537          0
X$JOXMFD                       4294952373 TABLE      65537          0
X$JOXMMD                       4294952374 TABLE      65537          0
X$JOXMAG                       4294952375 TABLE      65537          0
X$JOXMEX                       4294952376 TABLE      65537          0

To sum up: DBA_DEPENDENCIES does not contain some dependencies related to some fixed objects. You can also check which objects are related to these "missing" dependencies, e.g.:

select o.owner, o.object_name, o.object_type from dependency$ d join dba_objects o on (d.d_obj# = o.object_id) where p_obj# in (select distinct p_obj# from dependency$ d where not exists (select 1 from disk_and_fixed_objects f where f.obj# = d.p_obj#))

OWNER OBJECT_NAME                      OBJECT_TYPE
----- -------------------------------- -----------------------
SYS   USER_SOURCE_AE                   VIEW
SYS   ALL_SOURCE_AE                    VIEW
SYS   DBA_SOURCE_AE                    VIEW
SYS   INT$DBA_SOURCE                   VIEW
SYS   KU$_JAVA_SOURCE_VIEW             VIEW
SYS   DBMS_PITR                        PACKAGE BODY
SYS   USER_JAVA_CLASSES                VIEW
SYS   ALL_JAVA_CLASSES                 VIEW
SYS   DBA_JAVA_CLASSES                 VIEW
SYS   USER_JAVA_LAYOUTS                VIEW
SYS   ALL_JAVA_LAYOUTS                 VIEW
SYS   DBA_JAVA_LAYOUTS                 VIEW
SYS   USER_JAVA_IMPLEMENTS             VIEW
SYS   ALL_JAVA_IMPLEMENTS              VIEW
SYS   DBA_JAVA_IMPLEMENTS              VIEW
SYS   USER_JAVA_INNERS                 VIEW
SYS   ALL_JAVA_INNERS                  VIEW
SYS   DBA_JAVA_INNERS                  VIEW
SYS   USER_JAVA_FIELDS                 VIEW
SYS   ALL_JAVA_FIELDS                  VIEW
SYS   DBA_JAVA_FIELDS                  VIEW
SYS   USER_JAVA_METHODS                VIEW
SYS   ALL_JAVA_METHODS                 VIEW
SYS   DBA_JAVA_METHODS                 VIEW
SYS   USER_JAVA_ARGUMENTS              VIEW
SYS   ALL_JAVA_ARGUMENTS               VIEW
SYS   DBA_JAVA_ARGUMENTS               VIEW
SYS   USER_JAVA_THROWS                 VIEW
SYS   USER_JAVA_THROWS                 VIEW
SYS   ALL_JAVA_THROWS                  VIEW
SYS   ALL_JAVA_THROWS                  VIEW
SYS   DBA_JAVA_THROWS                  VIEW
SYS   DBA_JAVA_THROWS                  VIEW
SYS   USER_JAVA_DERIVATIONS            VIEW
SYS   ALL_JAVA_DERIVATIONS             VIEW
SYS   DBA_JAVA_DERIVATIONS             VIEW
SYS   USER_JAVA_RESOLVERS              VIEW
SYS   ALL_JAVA_RESOLVERS               VIEW
SYS   DBA_JAVA_RESOLVERS               VIEW
SYS   USER_JAVA_NCOMPS                 VIEW
SYS   ALL_JAVA_NCOMPS                  VIEW
SYS   DBA_JAVA_NCOMPS                  VIEW


SQL Server 2008 R2 Express with management tools installation failure I've been trying to install SQL Server 2008 R2 with tools.

I've downloaded thisn version and it just won't work!

I've had some problems installing Visual Studio SP1 due to silverlight4 being already installed, so I figured i needed to uninstall everything that had something to do with sql server, so I did.

Still the errors show up.

These 2 errors show up at the beginning of the installation during "Setup support files".

(I can't post more than 2 url's yet, so Ive put all the screenshots in 1 image, sorry)

Here

The first error is the top left one, and retry fails, and when I hit cancel a few secs later the second error pops up and the installation terminates.

My OS is W7 x64 with all updates installed.

I don't really know how to go on actually. I've searched for hours yesterday without any results.

Anybody got a clue? Before I reinstall every .net-related programs.

So far I've tried:

I checked if the first file shown in error 1 is actually there, and it is.
Tried to run the setup explicitly as administrator
I checked if the second file/folder was present, and it was not. So I manually created the folder and file, which then causes the
first error to say the same, but then it says can't create
\SqlSupport_Cpu64_1_ComponentUpdate_1.log, then I tried
to create an empty file with that name and extention. This makes the installer
append the filename with "_1". If I create that file too, it just
appends with a higher count.
Uninstalled every sql server related program (e.g sql server compact)
Reset all the permissions on the e: disk and formatted it.

Thanks in advance!

I found the cause of the problem. Recently I'd set my default program files folder to my hdd, because my C: is a 60 GB SSD. The key is located in the register at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion. The keys "ProgramFilesDir" and "ProgramFilesDir (x86)" are the ones that need to be default ("C:\Program Files" and "C:\Program Files (x86)") in order for it to work. Sorry for the unnecessary post.


Using Replication for Scale-Out Having read Using Replication for Scale-Out, how can I route different queries to different servers, for example, SELECTI want to route to slaves and NON-SELECT to master. I assume as loadbalancer I can use haproxy, but I didn't find it's possible to distict between queries on the level of haproxy? In addition, let's say someone have reached master directly, how can master identify that this is SELECT query and show be sent to slave, or to loadbalancer.

There is a project called MySQL Proxy that is attempting to handle read/write splitting in the proxy layer, but it is not production-ready. You can read more about the issues on this page, paying attention to the known issues section.

For now, as others have noted, you have to handle the routing through your application.


Store extended properties and apply to another copy of the database I'm using SQL Doc to document a development copy of a database. SQL Doc uses extended properties to store user entered values. At some point, I would like to copy those extended properties to the production database. What is the best way to do this?

I don't know of an easy way to just extract them and reapply to another target, but you could use the data in sys.extended_properties to generate a script to add the properties to another database using calls to sp_addextendedproperty.

See https://msdn.microsoft.com/en-us/library/ms177541.aspx and https://msdn.microsoft.com/en-us/library/ms180047.aspx for a bit of documentation around that.

If the target database may already contain some properties (from earlier transfers, or added for other reasons) then the script you generate may need to be more clever and check to see if it should call sp_updateextendedproperty instead (also potentially sp_dropextendedproperty to remove properties that are now deprecated).


How to search with partitions in my query? Table structure:

mysql> show create table transaction_event_items\G
*************************** 1. row ***************************
Table: transaction_event_items
Create Table: CREATE TABLE `transaction_event_items` ( `tei_row_id` bigint(20) NOT NULL AUTO_INCREMENT,`tei_adp_type`
varchar(16) NOT NULL,`tei_trn_code` varchar(64) DEFAULT NULL,`tei_trn_date` datetime NOT NULL DEFAULT '2020-01-01 01:00:00',
`tei_item_seq` int(11) DEFAULT NULL,`tei_item_cln` int(11) DEFAULT NULL,`tei_item_type` varchar(16) NOT NULL `tei_item_code`
varchar(32) NOT NULL,`tei_item_date` timestamp NOT NULL DEFAULT '2020-01-01 01:00:00',`tei_item_amount` decimal(8,2) DEFAULT NULL,
`tei_item_description` varchar(128) DEFAULT NULL,`tei_att_row_id` int(11) NOT NULL, `tei_lastupdt_date` timestamp NOT NULL DEFAULT
CURRENT_TIMESTAMP,`tei_phy_id` varchar(64) DEFAULT NULL,`tei_item_quantity` int(10) DEFAULT '1',`tei_item_employee` varchar(64)
DEFAULT NULL,KEY `tei_row_id` (`tei_row_id`),KEY `tei_phy_id_trn_date_idx` (`tei_phy_id`,`tei_trn_date`),KEY `tei_item_code_date`
(`tei_phy_id`,`tei_item_code`,`tei_trn_date`),KEY `tei_item_description_date` (`tei_phy_id`,`tei_item_description`,`tei_trn_date`),
KEY `idx_tei_item_date` (`tei_item_date`) ) ENGINE=MyISAM AUTO_INCREMENT=2845272905 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE
(to_days(`tei_trn_date`))(PARTITION p111109 VALUES LESS THAN (734816) ENGINE = MyISAM, PARTITION p111110 VALUES LESS THAN (734817) ENGINE = MyISAM,
PARTITION p111111 VALUES LESS THAN (734818) ENGINE = MyISAM,
PARTITION p111112 VALUES LESS THAN (734819) ENGINE = MyISAM,
...
PARTITION p121129 VALUES LESS THAN (735202) ENGINE = MyISAM,
PARTITION p121130 VALUES LESS THAN (735203) ENGINE = MyISAM,
PARTITION p121201 VALUES LESS THAN (735204) ENGINE = MyISAM,
PARTITION p121202 VALUES LESS THAN (735205) ENGINE = MyISAM)

Query:

SELECT
    tei_phy_id as Physical_ID__c
  , max(tei_trn_date) as Last_Event_Received__c
  , tei_adp_type as Last_Event_Adapter_Type__c
FROM
    transaction_event_items t
WHERE
    tei_trn_date > date_sub(now(), interval 2 day)
GROUP BY
    tei_phy_id;

Explain:

+----+-------------+-------+------+---------------+------+---------+------+----------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows     | Extra                                        |
+----+-------------+-------+------+---------------+------+---------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | t     | ALL  | NULL          | NULL | NULL    | NULL | 10072085 | Using where; Using temporary; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+----------+----------------------------------------------+

This query is not using any indexes and partitions in the table. Table size is 160gb.

What can I do to make it use indexes and partitions?

As per my understanding, you are using a function in the where clause. That's why the explain plan is not able to identify the exact partition number in table's partitions.

Function You used : (date_sub(now(), interval 2 day))

For using index: you have to create an individual index for that column like

create index index_tei_trn_date on transaction_event_items(tei_trn_date);

If explain plan still doesn't use the index, use hint index. For more details on this read: http://dev.mysql.com/doc/refman/5.1/en/index-hints.html


Add a number of days to a timestamp I'd like to add (or rather subtract) a number of days from a timestamp.
For example from 2020-04-13 00:00:00 Europe/Berlin I want to subtract 20 days and I expect the result to be 2020-03-24 00:00:00 Europe/Berlin.
If I run
select ('2020-04-13 00:00:00 Europe/Berlin'::timestamptz - INTERVAL '20 DAY') AT TIME ZONE 'Europe/Berlin'

I get 2020-03-23 23:00:00.000000.
That seems to be because PostgreSQL subtracts 20 * 24 hours (a DST switch happens during that date range) which is different from subtracting 20 days.

The interval calculation is done in whatever timezone your session is set to.  This is documented here:

When adding an interval value to (or subtracting an interval value from) a timestamp with time zone value, the days component advances or decrements the date of the timestamp with time zone by the indicated number of days, keeping the time of day the same. Across daylight saving time changes (when the session time zone is set to a time zone that recognizes DST), this means interval '1 day' does not necessarily equal interval '24 hours'

I don't know what your session timezone is set to (you can do show timezone; to see), but apparently it is not 'Europe/Berlin'.  When set to 'Europe/Berlin', I get the answer you expected.  Whatever your session timezone is set to either doesn't do DST, or doesn't implement the transition during the time period in question. All if its days during this period really are 24 hours.


Table has 14 GB in unused space - How to shrink table size I use the following script to gather data from my database wich is SQL Server on Azure.

-- Script to run against database to gather metrics

CREATE TABLE #SpaceUsed (name sysname,rows bigint,reserved sysname,data sysname,index_size sysname,unused sysname)

DECLARE @Counter int
DECLARE @Max int
DECLARE @Table sysname

SELECT  name, IDENTITY(int,1,1) ROWID
INTO       #TableCollection
FROM    sysobjects
WHERE xtype = 'U'
ORDER BY lower(name)

SET @Counter = 1
SET @Max = (SELECT Max(ROWID) FROM #TableCollection)

WHILE (@Counter <= @Max)
    BEGIN
        SET @Table = (SELECT name FROM #TableCollection WHERE ROWID = @Counter)
        INSERT INTO #SpaceUsed
        EXECUTE sp_spaceused @Table
        SET @Counter = @Counter + 1
    END

SELECT * FROM #SpaceUsed

DROP TABLE #TableCollection
DROP TABLE #SpaceUsed

One of my tables is 53 GB in size, with 38 GB in data, 14 GB in unused. The indexes are 11 MB in size. How do I go about reclaiming that 14 GB that is unused? As this is in Azure the extra space is costing money.

After reading various articles I have tried the following to reduce the size of the table.

DBCC DBREINDEX ('myTableName', ' ')
DBCC SHRINKDATABASE (myDatabaseName, 10);

However, after these complete, the size of the table remains unchanged. How can I go about reducing the size of the table and database in order to reclaim unused space?

UPDATE: There are 24 columns in the table with 9 of the fields being varchar of large length (> 4000) or varchar(MAX). Another 8 columns are unique-identifier types.

Whether a heap or a clustered index, you should find that you will be able to reclaim space in a table by rebuilding:

ALTER INDEX ALL ON dbo.myTableName REBUILD;

Note that reclaiming space in a table, and shrinking a database, are two completely separate things. Shrinking a database should be an exceptional thing - don't shrink a database just to free up disk space, since in most cases, the database will just have to grow and use that space again anyway.

As for determining how the table is distributed, sp_spaceused is pretty useless, since it just gives you a single line item with all data. First, check to see if your table is partitioned. You can see this quickly using:

SELECT COUNT(*)
  FROM sys.partitions
  WHERE partition_number > 1
  AND object_id = OBJECT_ID(N'dbo.myTableName');

Next, see how the data is laid out across your seven indexes. If one of these is a lot bigger than it should be, you could try dropping/re-creating (including potentially removing, say, your XML column from the INCLUDE list), as there may be cases where LOB space for rows you've deleted or set to NULL doesn't get fully recovered, even after a rebuild.

 ;WITH x AS
  (
    SELECT i.name, type = pa.allocation_unit_type_desc, kb = COUNT(*) * 8
    FROM sys.indexes AS i
    CROSS APPLY sys.dm_db_database_page_allocations(DB_ID(), i.object_id, i.index_id,
      NULL, 'LIMITED') AS pa
    WHERE i.[object_id] = OBJECT_ID(N'dbo.myTableName')
    GROUP BY GROUPING SETS((), (i.name, pa.allocation_unit_type_desc))
  )
  SELECT name, type, kb,
    [%] = CONVERT(decimal(5,2), kb*100.0/(SELECT kb FROM x WHERE name IS NULL))
    FROM x
    WHERE name IS NOT NULL
    ORDER BY name, type;

If you want to see individual row sizes in the base table, you could do:

SELECT TOP (100) key, rowsize = DATALENGTH(col1) + DATALENGTH(col2) + ...
  FROM dbo.myTableName
  ORDER BY rowsize DESC;

If you have some that are quite large, you might want to make sure you need that data (otherwise set it to NULL, as it may be what's contributing to your size.


Production SQL Server High CPU for a Single Query I have a single query which is consuming a larger amount of CPU than I would expect in SQL 2008.  I am not able to determine why the CPU is spiking for this single query.

I can trigger an application to make a query and watch the CPU spike, typically between 40-80%, which is much higher than any other similar queries executing at the same time or when I execute this same query by hand.  In the Activity Monitor I see the spike occur and I see this single query pop up to the top of the Recent Expensive Queries list.  It will show only a handful of executions/min but will show several hundred for its CPU ms/sec (250-500 on average).  The logical reads is somewhere around several thousand.  The duration is typically 600 or so.  I have ran SQL Profiler and examined the logs to find the query being executed.  However, I'm not able to replicate the issue calling this query myself.  I can execute the query just as SQL Profile shows it, or execute it manually, and the CPU will not spike and the query never shows as a Recent Expensive Query.  It only spikes when the consuming applications makes the call.

The query is a stored procedure that joins a few large select statements together.  When I run this stored procedure manually I see very little cost in terms of CPU and I get results in less than a second. Examining the Profiler logs this stored procedure appears to be taking roughly 4 seconds to execute.

I have gone as far as to fire up duplicate images of the database and the application server.  However, making these requests from this duplicate environment doesn't replicate the issue, nor does testing in our staging, dev or local environments.

Has SQL improperly cached the query execution or something to this effect?  I recently added a couple indexes to the tables involved in this query and the execution plan I see from the Activity Monitor shows that the execution plan was updated for this change (no longer recommends these indexes).  This sounds dumb, but do I need to restart the SQL Server or something?  Or is there a way to better replicate the query that is coming into the server than pulling the query from the Profile trace?

Perhaps we can pull out the execution plan when it seems that particular query is running slow and compare it to the execution plan for when you run it.  If you recently updated indexes it might still have the old exec plan cached, however, it does not answer the question as to why it runs fast when you execute the stored procedure from SQL Server, unless you're using different parameters.

Did you check if AUTO_UPDATE stats is on?

Is your tempdb optimized?  Sometimes PAGELATCH_SH issues will be intermittent.

Can you view the wait stats, clear them, run the query, and then see the wait stats for when it was slow?

Viewing the plan cache:
http://www.sqlteam.com/article/what-query-plans-are-in-sql-server-memory


SQL Server 2016 - High page writes, but normal/low everything else I am a sysadmin and I am not very well versed in SQL Server metrics. I have been having issues with one of our DB's and the DBA doesn't seem too knowledgeable about measuring performance, so I'm trying to determine why we are having these issues.

This is a DB with 134 GB data file and 25 GB log file currently. The DB is encrypted at rest (TDE) and uses a key stored in Azure KeyVault (same region as the server). It is running in replication mode and replicating to a single SQL Server instance. The main instance runs on an Azure VM (8 vCPU, 56 GB RAM) and replicates to a backup instance in Azure SQL.

The main issue we are having is Page Writes/sec. It is currently always around 250 writes/sec or above, and rarely drops below 200. I have read that 90 writes/sec is a normal threshold. Sometimes, stored procedure executions can increase from a few milliseconds to half a second or more, severely affecting our app performance.

Meanwhile, the PLE is 60,766 secs, the Buffer Cache Hit Ratio is 100%, and Page Reads/sec are at 0.2/sec. So this is most likely not a memory issue, right?

So now what is the best way to determine what is causing the largest number of page writes on an ongoing basis? How can we know if it's simply poor design, code needing optimization, or some other issue?

So now what is the best way to determine what is causing the largest number of page writes on an ongoing basis?

Great question.  Page writes are caused by changing your database.  Full stop.  Changes are written to the log file before a commit, but database pages are changed in memory, but not written on commit.

So first of all, page writes are performed in the background when no user is waiting.  If you concurrently have page reads, this can be an issue, but you seem to have most of the relevant pages cached.  So page writes are not necessarily causing any problems.

That being said, how do you determine what's causing the writes?  It's not super-easy.

There's a DMV that will allow you to query over all the cached pages, and see which ones are dirty.  This DMV can be expensive to run on systems with lots of memory, however.  Something like:

select schema_name(o.schema_id) schema_name, object_name(p.object_id) table_name,  i.type_desc index_type, i.name index_name, bd.page_type, count(*) page_count
from  sys.dm_os_buffer_descriptors bd
join sys.allocation_units au
  on bd.allocation_unit_id = au.allocation_unit_id
join sys.partitions p
  on (p.hobt_id = au.container_id and au.type in (1,3))
   or(p.partition_id = au.container_id and au.type = 2)
join sys.indexes i
  on p.index_id = i.index_id
  and p.object_id = i.object_id
join sys.objects o
  on o.object_id = i.object_id
where database_id = db_id()
and is_modified = 1
group by o.schema_id, p.object_id, i.name, i.type_desc, bd.page_type
order by page_count desc


Should the referencing table have primary key, auto increment, or index? I'm trying to implement a list. Each list item will be associated with one or more tags. And each tag will be associated with one or more list items.

I'm still pretty new to this field. Fortunately, I managed to find a solution in this question, whose answer suggests the following database design:

Articles table
- id
- name
- etc

Relational Tag Arts table
- Fkey tags id
- Fkey article id

Tags table
- id
- name
- etc

I already created my three tables. Now what I don't understand is that, should the referencing table (the one that contains two foreign keys) have primary key, auto increment, or index?

[Solved] And although I'm not 100% sure, I guess the referencing table's content is purposely "repetitive". For example, if I add a new item which is associated with 5 tags, then the referencing table should be added with 5 more records, which all have the same item id but have different tag id's. Am I correct?

You may want to consider taking one step back a think about the design process. Once you are familiar with that you can often skip it and directly implement it. I would suggest to start out with the tables (in theory called relations). You seem to have found:

* Articles
* Tags

Next step is to think about what uniquely identifies a row (tuple) in  Articles and Tags. That may be a single column (attribute) or a combination of columns. What would you use to identify an article if you talked to someone about it? All such attributes or combination of attributes is known as candidate keys. In this process, you may choose to add an artificial (surrogate) key, I assume id is such an attribute. The reasons for adding surrogate keys can, for example, be that the natural keys are too complex, not stable enough, etc. You should still declare them as UNIQUE though.

Speaking of id, I strongly suggest that you name it article_id or similar, and stick with that name through the model. Names such as Id, Name, etc are too vague to be meaningful and usually collides with reserved words in the DBMS that you use. Assume we have concluded this:

CREATE TABLE Articles
( article_id ... not null
, article_name ... not null
, publishing_date date not null
, ...
,    primary key (article_id) -- surrogate key
,    unique ( article_name, publishing_date ) -- natural key
);

CREATE TABLE Tags
( tag_id ... not null primary key
, tag_name ... not null unique
);

How are these tables related? You seem to have concluded that there is an N-N relationship between an Article and a Tag and this is usually implemented with a third table (sometimes named junction or link table). Let's call this table:

CREATE TABLE Article_Tags
( article_id ... not null references Articles (article_id)
, tag_id ... not null references Tags (tag_id)

);

Now for your original question, what uniquely identifies this relationship? I would suggest here a composite key like:

CREATE TABLE Article_Tags
( article_id ... not null references Articles (article_id)
, tag_id ... not null references Tags (tag_id)
,     primary key (article_id, tag_id)
);

and not add an additional surrogate key (I'm not even sure what the purpose of such key would be, but far too often I see it added in a design by blindfold fashion). With the suggested composite key, you are guaranteed that the same tag won't be used more than once for each article.

If you sometimes want to retrieve all articles for a given tag you can add an index like:

CREATE UNIQUE INDEX ... ON Article_Tags (tag_id, article_id);

EDIT: some info on indexes versus keys

An index is a physical concept and is in fact not even mentioned in the SQL standard, meaning each vendor can implement them as they please, whereas a key of some kind is a logical concept. Note that in some DBMS there's a fuzzy line between these two concepts, for example, it is possible to have a foreign key reference a unique index in SQL-server, and MySQL call indexes keys.

Most DBMS:s (and everyone I know of) implements logical keys (UNIQUE, PRIMARY KEY) with physical indexes, so if you add a key, an index is created under the hood. The following keys are redundant:

PRIMARY KEY (a,b) <=> UNIQUE (b,a)

since if (a,b) is unique, so is (b,a). It is a fat chance that your DBMS won't let you create one if the other is present. On the other hand:

INDEX (a,b) is not redundant with INDEX(b,a)

since a query like:

SELECT ... FROM T WHERE a = ?

can use the first index to efficiently find rows that match, but the second index can't. You can think of it as if you have an index organized as first-name, last-name and you try to find someone with a last-name Gates or Thorvalds. You basically have to scan through a lot of first-names before you find all persons with those last-names.

So why not create one single column index for every column used in searches? Many DBMS can do INDEX ANDING (but some can't) and it is anyhow less efficient than using one multi-column index.
Once again you can compare it with a process of first looking at an index for first-name and find out that there's a Bill on page 14, 50, and 126. Then look at another index for last-name and find out that there's a Gates at page 21, 50, 123 and 212, and finally intersect those two sets to conclude that Bill Gates can be found at page 50. Compare with an index for first-name, last-name where you find Bill and then can find Gates very easily.

You can use a multicolumn index a,b,c,d,e,f for finding a, ab, abc, etc, but it won't be very efficient if you look for WHERE d=? and f=?.

The above is much simplified but may give you an idea or two on how to start thinking on the design process.


How can I prevent MongoDB from logging the contents of queries under any circumstance? I'm working with sensitive data in MongoDB (Community Server, v3.2, upgrading to latest in the near future). On the production server, it's essential that the data is not written to the log under any circumstances. This is because the log files are processed on a system which is not authorised to contain sensitive data.

The problem is that the sensitive data is often being mentioned explicitly in queries and aggregations etc. This means any kind of query logging needs to be disabled. I still want to retain basic informational logging so that system critical events can be monitored.

What I've done so far is:

Set all log levels to 0.
Set slow query timeout threshold to a very high value.

Is there anything else I need to do to ensure the sensitive data never gets logged? In particular, is it possible to disable slow query logging entirely, instead of relying on a high threshold?

Yes,with 3.4 Enterprise version it is possible. Log Redaction.


Why does the READPAST hint cause indexed views to be ignored? I am investigating using the READPAST hint to reduce resource locking in our application's financial subsystem.

It seemed like a good way to go because financial transaction records are only ever added, never updated or deleted. The only rows that would ever be skipped are brand new rows inserted inside of a transaction; they effectively don't exist to the outside world until the transaction is committed.

However, I noticed worse performance on queries that utilize indexed views that I had put the READPAST hint on. Comparing the query plans, it looks like with the hint, the query optimizer chooses to not use the indexed view and instead falls back to treating it like a regular view.

I'm not sure why that would be; I imagine indexed views to be like any other index in that keys can be locked during operations and adding READPAST would work similarly.

SELECT TOP 1 isa.InvoiceId
FROM Financial_InvoiceSummaryAmounts isa WITH (READPAST)
WHERE isa.TotalOwedAmount = 0.0

SELECT TOP 1 isa.InvoiceId
FROM Financial_InvoiceSummaryAmounts isa
WHERE isa.TotalOwedAmount = 0.0

Adding a NOEXPAND hint as well does seem to work, but I am interested in learning more about possibly why READPAST caused the query optimizer to make that choice in the first place (as part of a full answer).

Reusing the example table and indexed view from my article Another Reason to Use NOEXPAND hints in Enterprise Edition:

CREATE TABLE dbo.T
(
    col1 integer NOT NULL
);
GO
INSERT dbo.T WITH (TABLOCKX)
    (col1)
SELECT
    SV.number
FROM master.dbo.spt_values AS SV
WHERE
    SV.type = N'P';
GO
CREATE VIEW dbo.VT
WITH SCHEMABINDING
AS
SELECT T.col1
FROM dbo.T AS T;

Repro

This query matches the indexed view (albeit with a redundant aggregate):

SELECT DISTINCT
    VT.col1
FROM dbo.VT AS VT;

Adding a READPAST hint results in accessing the base table:

SELECT DISTINCT
    VT.col1
FROM dbo.VT AS VT
    WITH (READPAST);

Explanation

The READPAST hint is semantic-affecting. The optimizer resists rewriting queries such that the results change. To illustrate:

The following query executes without problems:

SELECT DISTINCT
    VT.col1
FROM dbo.VT AS VT
    WITH (READPAST);

However:

SELECT DISTINCT
    VT.col1
FROM dbo.VT AS VT
    WITH (READPAST)
OPTION
    (TABLE HINT (VT, FORCESCAN));

Produces the error:

Msg 8722, Level 16, State 1, Line 42
Cannot execute query.
Semantic affecting hint 'readpast' appears in the 'WITH' clause of object 'VT'
but not in the corresponding 'TABLE HINT' clause.
Change the OPTION (TABLE HINTS...) clause so the semantic affecting hints
match the WITH clause.

When you reference the indexed view without the NOEXPAND hint, the view is expanded (before compilation and optimization begins) to reference the underlying objects instead. Later in the process, the optimizer may consider matching the query tree back to an indexed view, in whole or in part.

When READPAST is used without NOEXPAND, the hint propagates to the base table, preventing view matching (different semantics).

With NOEXPAND, the hint applies to the view directly, so there is no problem.


How can I print the contents of a table to the console? My customer sends me complicated (and inconsistent) data. I am using C# and calling stored procedures in the database using the Entity Framework 6 to insert that data into the database. (The Entity Framework portion is only really handling data retrieval and ensuring a correct schema; inserts are done completely through stored procedures using table-valued parameter procedures.)

I use a series of Select DISTINCT statements to pull the unique entities out of the data and into temporary tables, then use those temporary tables to update the live database after verifying the data. (Credit to @srutzky for the idea.)

I want to be able to print the contents of those temporary tables during the procedure so I can inspect it and/or write it to a log. The data they send is messy, and I want to make sure that my SELECT DISTINCT queries are working right. (Spoilers: they are, but the customer's data has more holes in it than decaying Swiss cheese.)

The data can be inconsistent as well, so sometimes the same entity will have different types values on a field that it shouldn't, which will cause the DISTINCT query to select the same entity multiple times from the data and violate UNIQUE constraints in the table. I'm specifically trying to locate these "bad lines" of data both for logging purposes and to inform the customer that the data was wrong.

This all has to be automated in C#. The program, when finished, will run on a remote server that I will not have write access to, so I also need the entire process to be "hands-off" (no sqlcmd, no SSDT, etc. Just T-SQL.)

What is the best way to do this?

You should handle this all in the C# app code. Exporting data from T-SQL is not handled natively and requires SQLCLR, which is fine when needed, but it is not necessary here since you are already working within app code that can handle it quite cleanly.

You should have the app code do a SELECT (or grab the contents via EF if you say it can do that easily) and log it to a file after each step.

You should also have a command-line option/flag, or a check-box in the UI before clicking "Start", for "Debug mode" that will additionally display what is being written to disk along with an option to "Continue" or "Cancel".


MySQL query for 3 consecutive integers between records Given a table where each record has a year integer value, how do I query to see if there are 3 or more consecutive years?

| id | year |
+----+------+
| 1  | 1990 |
| 1  | 1991 |
| 1  | 1992 |
| 2  | 1973 |
| 3  | 1993 |
| 3  | 1994 |
| 3  | 1995 |
| 3  | 1996 |

Given the above table, the resulting records returned by the query would be:

| id |
+----+
| 1  |
| 3  |

The query would return ids 1 and 3 since those two records contain have consecutive years. How would I write such a query?

SELECT DISTINCT
t.id
FROM tablename t
WHERE EXISTS (
    SELECT 1 FROM tablename x
    WHERE x.id = t.id
    AND x.year = t.year+1
)
AND EXISTS (
    SELECT 1 FROM tablename x
    WHERE x.id = t.id
    AND x.year = t.year-1
)


SQL Server 2008 ignoring QUERYTRACEON 4136 and ARITHABORT ON A slow Stored Procedure is extremely faster (going from 2 secs to 8 millisecs) with QUERYTRACEON 4136 and ARITHABORT ON, on the local SQL Server 2008 I use to develop. The "fixed" SP, on another SQL Server 2008, goes back to slowness: with or without QUERYTRACEON 4136 and ARITHABORT ON, it doesn't make any difference.

Removing any fix and just adding WITH RECOMPILE gives me back the fast performances, so it's clear that the server is ignoring both fixes.

I didn't install that second server, while I installed my local dev server and it's "out of the box": I didn't touch any configuration. What I should check on the other server, what could cause it to ignore QUERYTRACEON and ARITHABORT ON?

An additional weirdness with ARITHABORT ON

Maybe this could help to figure out what's going on. Or maybe it will simply mud things even more.

This way:

SET ARITHABORT ON
SET NOCOUNT ON

ARITHABORT is ignored. Even using WITH RECOMPILE.

This way:

SET NOCOUNT ON
SET ARITHABORT ON

ARITHABORT is not ignored.

I tried to include most of the details in your other question but specific to this question arithabort and query trace are not solutions just tools to find problems.  You need to implement a fix once you find the problem.  Arithabort may work temporarily because it forces a new plan for a while but it might go right back to what it did before.

Option recompile is an okay bandaid but you are creating an overhead by getting a new plan every single execution and especially if your stored procedure is executed many many times this can create issues.  Setting a local parameter to the parameter ie- Set @name = @lname is an old trick to fix before sql server had an adequate solution and we found this method hit or miss.  Optimize for unknown the microsoft supported hint for parameter sniffing has had the best results and kept company critical processes from having issues.  It is just a hint that basically says to the engine be extra careful about changing plans we found a problem here.