How can I get rid of this Lazy Spool, or otherwise improve this query's performance? Here is the plan:  https://www.brentozar.com/pastetheplan/?id=rkM8d7ONS

I am mostly interested in how to get rid of the lazy spool?

This is the query:

SELECT DISTINCT
        SM.Security_ID 'Security_ID',
        Leg.Leg_Type 'Leg_Type',
        Leg.Leg_Side 'Leg_Side',
        Ct.Security_Type AS 'Swap_Type',
        Leg.CDX_Indicator AS 'CDS_CDX_Flag',
        SM.Currency AS 'Notional_Currency',
        Ct.Cross_Currency_Flag AS 'Cross_Currency_Flag',
        Ct.Custom_Overrides AS 'Special_Instructions',
        Leg.Protection_Indicator AS 'Buy_Sell_Protection',
        Leg.Commission_Direction AS 'Commission_Direction',
        Leg.Dividend_Payment_Indicator AS 'Undl_Asset_Dividend_Flag',
        SM.Issue_Date AS 'Effective_Date',
        SM.Maturity_Date AS 'Maturity_Date',
        Leg.Settlement_Frequency 'Settlement_Frequency',
        Leg.Reset_Frequency 'Reset_Frequency',
        Leg.Roll_Day AS 'Roll_Day',
        Leg.Reset_Business_Day_Convention AS 'Reset_Business_Day_Convn',
        Leg.Settlement_Business_Day_Convention AS 'Settlement_Business_Day_Convn',
        Leg.First_Payment_Date AS 'First_Period_End_Date',
        Leg.Day_Count AS 'Day_Count_Basis',
        Leg.Interest_Rate AS 'Interest_Rate',
        Leg.Spread AS 'Spread',
        Leg.CDX_Attachment AS 'CDX_Attachment',
        Leg.CDX_Detachment AS 'CDX_Detachment',
        Leg.Factor AS 'Factor',
        Leg.Commission AS 'Commission',
        Leg.Reset_Lag AS 'Reset_Lag',
        Leg.Initial_Index_Price AS 'Initial_Price',
        Ct.Principal_Exchange_Initial AS 'Principal_Exchange_Initial',
        Ct.Principal_Exchange_Final AS 'Principal_Exchange_Final',
        IsNull(Leg.Delay_Days, 0) AS 'Settlement_Delay_Days',
        Leg.Red_Code AS 'Red_Code',
        Leg.Referenced_Asset AS 'Referenced_Asset',
        SM.Short_Description AS 'Security_Description',
        Leg.Notional_Reset_Type AS 'Notional_Reset_Type',
        Leg.Reset_Arrears_Flag AS 'Reset_Arrears_Flag',
        SM.MIC AS 'Position_Market',
        SM.Currency AS 'Position_Currency',
        1,
        Ct.Security_Id,
        Txn.CLEARED_TRD_INDICATOR
    FROM
        ##AssetAddSwap_Tbl S
        INNER JOIN Sch_Core_Data.Security_Master SM (NOLOCK) ON S.Security_ID = SM.Security_ID
        INNER JOIN Sch_Core_Data.Security_Detail_SwapLeg Leg (NOLOCK) ON SM.Security_Id = Leg.Security_Id
        INNER JOIN Sch_Core_Data.Security_Detail_SwapContract Ct (NOLOCK) ON Leg.Contract_Security_Id = Ct.Security_Id
        LEFT JOIN Sch_Core_Data.VW_TRANSACTIONS_Abbreviated Txn WITH (NOLOCK) ON Leg.Security_ID = Txn.Security_ID
        LEFT JOIN Sch_Core_Data.Security_Alt_Identifier SAI (NOLOCK) ON SAI.Security_Id = Leg.Security_Id
            AND SAI.Identifier_Type = 'APXID' AND SAI.STATUS_FLAG = 'ACT'
    WHERE
        S.Retransmit_Flag = 1
        OR (--Txn.Transaction_ID IS NOT NULL
            --AND Txn.TRANSACTION_SOURCE_SYSTEM <> @TargetSystem
             Txn.TRANSACTION_SOURCE_SYSTEM NOT IN (SELECT svalue from SCH_CORE_DATA.DBL WHERE PROCESS = 'SYSTEMS' AND [FUNCTION] = 'LIST' AND DESCRIPTION3 = 'NON-TOM')
            AND Sch_Core_Code.Udf_Chk_BitValue(Txn.Account_Subscription, 3) = 1
            AND Sch_Core_Code.Udf_Chk_BitValue(Txn.Transmitted, 3) = 0
            AND IsNull(SAI.Identifier, '') = '')

Using this function:

-- =============================================
-- Author:      Anuj Kalra
-- =============================================
CREATE FUNCTION [SCH_CORE_CODE].[UDF_CHK_BITVALUE]
(
@VALUE INT,
@FIND INT
)
RETURNS BIT
WITH EXECUTE AS 'USR_CORE'
AS
BEGIN
    DECLARE @CHECK BIT;
    SET @CHECK = 0;
    IF ((POWER(2,@FIND) & @VALUE)<> 0)
    BEGIN
        SET @CHECK = 1;
    END
    RETURN @CHECK;

The use of a scalar user-defined function is inhibiting parallelism in this query, which is hinted at in the execution plan XML: NonParallelPlanReason="CouldNotGenerateValidParallelPlan"

One option would be to manually inline this function's implementation.  So this:

Sch_Core_Code.Udf_Chk_BitValue(Txn.Account_Subscription, 3) = 1

Becomes this:

CASE WHEN (POWER(2,3) & Txn.Account_Subscription)<> 0 THEN 1 ELSE 0 END = 1

Note: you would need to do this for both references to the function

There are some large index scans in the execution plan (22 million rows are read from the ACCOUNT table).  If the query really needs to process this many rows, parallelism can help a lot.

If the DISTINCT is not required for correct results, removing that could help with performance as well (preventing the potentially costly Sort (Distinct Sort) in the plan.

As far as the spool is concerned, it might actually be helping in this case.  You can try suppressing the spool by adding OPTION (QUERYTRACEON 8690) to the end of the query and seeing how the plan / query execution time is different. On SQL Server 2016 onward, the query hint NO_PEROFRMANCE_SPOOL is available.


Witnessing standby failure with repmgr We're setting up a PostgreSQL cluster, and we're looking at using repmgr to handle standby promotion and so on.

Our cluster has one master and a hot standby, and uses the streaming replication features of PostgreSQL 9.1.

Since we're using streaming replication, writes to the master will no longer go through when the standby fails. So we would like to use repmgr to not only promote the hot standby when the master fails, but also to use repmgr to disable streaming replication when the standby fails.

Are there any suggestions on how to do this? All the documentation seems centred around a failure with the master.

Since we're using streaming replication, writes to the master will no longer go through when the standby fails.

Not necessarily the case.  This is only the case when you are setting up synchronous streaming replication.  Normally the master will return immediately after it writes its own log files to disk and not wait for the slave.  It is then replicated out when the slave requests.

See the synchronous_commit parameter.

I don't see why you need to futz with repmgr for this.


Fastest way to get current user's OID in Postgres? I am thinking of storing the user's OID in the "owner" column of a table, so I don't have to do anything if they change their username.

I understand one can use pg_has_role() with OIDs, so that's good.

Is there a fast and easy way to get the current user's OID without just matching it against one of the system tables?

The fastest way I know of is a lookup in the system catalog view pg_roles:

SELECT * FROM pg_roles WHERE rolname = 'postgres';

Strictly speaking, it would be even slightly faster to use the underlying table pg_authid, but access to it is restricted to superusers for good reasons.

There is no object identifier type like for tables or types, which would allow a simple cast like 'mytable'::regclass.

Be aware though, that OIDs are not stable across a dump / restore cycle. So, the OID is no good for the use case!

In some databases I have a separate login table with a serial primary key that I use for similar purposes. Maintained manually. And functions using it are prepared to occasionally not find a user in this table. A very basic and fast table:

CREATE TABLE users.login (
  login_id serial PRIMARY KEY
 ,username text NOT NULL UNIQUE
);

When creating new users I use a plpgsql function that creates the new user in the system and enters it into my table at the same time. And I use this login_id in many places. For instance I keep track of who made the last change to a row in most tables. I use this simple function:

CREATE OR REPLACE FUNCTION public.f_login_id()
  RETURNS int AS
$func$
SELECT COALESCE((SELECT login_id FROM users.login
                 WHERE  username = session_user::text), 0::int)
$func$ LANGUAGE sql STABLE;

No foreign key constraints so to keep things fast and simple. Obviously, I don't need strict referential integrity ...


MongoDB Shutting down. Signal 15 Using MongoDB in a live server. Everyday in the morning, I find that mongod process is shutdown. The mongodb log is as follows:

2017-11-02T11:19:49.173+0000 I FTDC     [initandlisten] Initializing full-time diagnostic data capture with directory '/var/lib/mongodb/diagnostic.data'
2017-11-02T11:19:49.174+0000 I NETWORK  [thread1] waiting for connections on port 27017
2017-11-02T11:19:49.469+0000 I NETWORK  [thread1] connection accepted from 127.0.0.1:34826 #1 (1 connection now open)
2017-11-02T11:20:20.112+0000 I NETWORK  [thread1] connection accepted from 127.0.0.1:34828 #2 (2 connections now open)
2017-11-02T11:27:34.728+0000 I CONTROL  [signalProcessingThread] got signal 15 (Terminated), will terminate after current cmd ends
2017-11-02T11:27:34.728+0000 I NETWORK  [signalProcessingThread] shutdown: going to close listening sockets...
2017-11-02T11:27:34.728+0000 I NETWORK  [signalProcessingThread] closing listening socket: 7
2017-11-02T11:27:34.728+0000 I NETWORK  [signalProcessingThread] closing listening socket: 8
2017-11-02T11:27:34.728+0000 I NETWORK  [signalProcessingThread] removing socket file: /tmp/mongodb-27017.sock
2017-11-02T11:27:34.728+0000 I NETWORK  [signalProcessingThread] shutdown: going to flush diaglog...
2017-11-02T11:27:34.728+0000 I FTDC     [signalProcessingThread] Shutting down full-time diagnostic data capture
2017-11-02T11:27:34.730+0000 I STORAGE  [signalProcessingThread] WiredTigerKVEngine shutting down
2017-11-02T11:27:34.795+0000 I STORAGE  [signalProcessingThread] shutdown: removing fs lock...
2017-11-02T11:27:34.795+0000 I CONTROL  [signalProcessingThread] now exiting
2017-11-02T11:27:34.795+0000 I CONTROL  [initandlisten] shutting down with code:0

My mongodb configuration file is as follows:

# mongod.conf

# for documentation of all options, see:
#   http://docs.mongodb.org/manual/reference/configuration-options/

# Where and how to store data.
storage:
  dbPath: /var/lib/mongodb
  journal:
    enabled: true
#  engine:
#  mmapv1:
#  wiredTiger:

# where to write logging data.
systemLog:
  destination: file
  logAppend: true
  path: /var/log/mongodb/mongod.log

# network interfaces
net:
  port: 27017
  bindIp: 127.0.0.1

#processManagement:

#security:

#operationProfiling:

#replication:

#sharding:

## Enterprise-Only Options:

#auditLog:

#snmp:

Most of the solutions online are related to setting processManagement.fork to false, which I believe is it's default value.

If fork is false then mongo dies when shell (where you started mongo) die. Set fork to true


SQL server moved, now can't use as linked server So I have a SQL server database that was controlled by an outside vendor.  It is hosted remotely.  Then he needed to move the server to a new host.  Now, I'm able to connect to the server manually from SSMS and I can add it as a linked server, but I can't USE it as a linked server, or even browse the servers catalogs.

My instance is 2000, and I assume the remote server is 2008 or 2008 R2.

When I try to use the server (try to update a stored proc that points to the linked server) I get the following error:

Trying to browse the linked server's catalogs from SSMS throws this:

  Failed to retrieve data for this request.
  (Microsoft.SqlServer.Management.Sdk.Sfc)

  [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
  access denied. (Microsoft SQL Server, Error: 17)

A friend suggested I run Exec sys.sp_change_users_login 'Report', but it turns up no orphaned records, and anyway, I can login using the credentials, so that doesn't look like the problem.

EDIT: Can't connect at all with IP, but hostname connects then gives above error.  HUH?

I'm assuming that you are using a "sql server login" for the vendor's server, and you are using that when creating the linked server.

When you use a linked server, a query running on your server connects to the vendor's server. If a firewall blocks your local server from connecting to the vendor's server, your connection attempt will fail.

If connecting directly from your workstation to the vendor's server you go through a different firewall or no firewall, then the connection may succeed.

This scenario matches the behavior you describe. My usual tests would be:
1. Can I ping from my server to the vendor's server? Both by IP and by hostname? ping usually gets through firewalls.
2. Can I connect using SSMS from my server (using an RDP session) to the vendor's server? Both by IP and by hostname?

If you can ping OK but not connect with SSMS, this usually indicates that the ports for SQL Server probably need to be opened on the firewall.

In short, check all of the firewalls involved. That would include any software firewall on your server or on the vendor's server, or any hardware firewall between them.

In these situations, ping and traceroute are your friends. Ping and traceroute may help you locate the IP of the router that doesn't send your packets on to the vendor's server.


Limiting user access to tables based on a ROLE I have a SQL Server 2005 database to which I would like to LIMIT access. I have done the following:

Created a database role called NO_HR
Added the tables to this role under Securables that should be blocked
Set all permissions to DENY for these tables
Assigned my users this role under Database Users -> Role Members

I would expect the user to have whatever public access is allowed to the database but be denied access to the tables as defined in the role.

Problem is, users can access all tables as if the rules in the role are not taken into account. My question: How do I block access to tables based on role membership?

A few facts:

SQL Server 2005 Standard
Windows 2003 R2 Enterprise
Users are DOMAIN logins
Any help would be appreciated.

SG

If I had to guess you have them added to the db_owner role as well.  Either that or sysadmin (although less likely).  Administrative roles, specifically DBO and SYSADMIN can not be blocked from access to anything they have control over (dbo - database level, sysadmin - instance level).  I would go back and check first the role that you created and make sure that it is not a member of any other roles that might cause an issue.  db_datareader for instance shouldn't be a big deal.  Second I would check public and make sure IT isn't a member of any other roles.  Then I would check any other roles that the users might be a member of.

All that being said it's also possible you made a mistake when you set up your role and revoked instead of denyed (I've done that type of thing before).

Last but not least, you should change how you are doing this anyway.  Remove all access from public.  Create a role that has just the access you want them to have and add them to that role.  It is generally considered a bad idea to grant access to public.  Or for that matter to grant lots of access and then deny it to certain people.  It's far better to accidently forget to grant someone access to something, than to forget to deny it.


PostgreSQL: pg_upgrade 9.5 to 13 failing with "connection to database failed: FATAL: database "template1" does not exist" I'm trying to upgrade a 9.5 server to 13 but the pg_upgrade process is failing after the main checks.  Server is running CentOS 8 on EC2 and is using the pgdg repo installs of each server.
bash-4.4$ /usr/pgsql-13/bin/pg_upgrade --old-bindir /usr/pgsql-9.5/bin --new-bindir /usr/pgsql-13/bin --old-datadir /var/lib/pgsql/9.5/data --new-datadir /var/lib/pgsql/13/data --old-port=54320 --new-port=5432 --socketdir=/var/run/postgresql/
Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok
Checking database user is the install user                  ok
Checking database connection settings                       ok
Checking for prepared transactions                          ok
Checking for reg* data types in user tables                 ok
Checking for contrib/isn with bigint-passing mismatch       ok
Checking for tables WITH OIDS                               ok
Checking for invalid "sql_identifier" user columns          ok
Checking for invalid "unknown" user columns                 ok
Checking for roles starting with "pg_"                      ok
Creating dump of global objects                             ok
Creating dump of database schemas
                                                            ok

connection to database failed: FATAL:  database "template1" does not exist

could not connect to target postmaster started with the command:
"/usr/pgsql-13/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/pgsql/13/data" -o "-p 5432 -b -c synchronous_commit=off -c fsync=off -c full_page_writes=off -c vacuum_defer_cleanup_age=0  -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directories='/var/run/postgresql'" start
Failure, exiting

When I run that server start command manually, the server starts.  There's nothing in the log that indicates what the issue is for the pg_upgrade command.
Due to the size of the databases in question I can't do a dump/restore as in my testing it took over 48 hours to perform that process even when copying on the same machine.  So I'm hoping to get the upgrade process to work.

From the PostgreSQL mailing list:
The problem is that postgis, through gdal, ends up being linked to two different versions of proj at the same time.
You can check it by doing:
ldd /usr/pgsql-13/lib/postgis_raster-3.so | grep proj
If that shows up two different proj libraries, then you have that same problem.
In this case, uninstall the OS supplied proj library. If that removes postgis through dependency, let it and then install it with:
dnf install --excludepkg proj --excludepkg proj-datumgrid postgis30_13


ip address record in sybase ASE auditing database? everone.

I installed the sybsecurity database for auditing in sybase ase 15.5. The installation is OK. I configured the audit commands, for example, for auditing the sa user, and other users of the database.

I see that the column extrainfo for the table sysaudits_01 shows additional information. I need to add the IP address of of users who perform certain commands: for example, GRANT, INSERT, 'UPDATE,DELETE,SELECT, andCREATE`.

I see that rows in login attempts include the IP address in the extrainfo column.

I checked the code of the file C:\Sybase\ASE-15_0\scripts\instsecu but I can't find how to add the IP address to extrainfo to my table.

How can I capture the IP address for certain actions, and record it in extrainfo?

The contents of the extrainfo column is populated in two ways:

automatically by the auditing system; the actual contents are pre-defined based on what exactly is being audited (eg, audited logins could include the ip address)
when the sp_addauditrecord proc is called (eg, by a user-defined proc, trigger, batch process)

In order to match an ip address with the various commands you'd have to use the audited login record (that contains the ip address) to obtain the associated spid/suid, then use these values to search follow-on auditing records with the same spid/suid; basically a self-join of the sysaudits table.

Keep in mind that you really don't want to be hitting the sysaudits tables very often with this type of activity as that's not what the tables are designed for.  Instead, you'd want to periodically pull the auditing data into some other database/table/repository (with appropriate indexes) where you could run your queries.


mongodb sharding collection Im trying to shard my collection. when I execute the command:

  sh.enableSharding("testdb")

I get:

{
    "ok" : 1,
    "operationTime" : Timestamp(1552083125, 2),
    "$clusterTime" : {
        "clusterTime" : Timestamp(1552083125, 2),
        "signature" : {
            "hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
            "keyId" : NumberLong(0)
        }
    }
}

Now when I try to shard my collection with the command: "sh.shardCollection("testdb.testcollection", {testkey:1})"

I get the error:

{
    "ok" : 0,
    "errmsg" : "Cannot accept sharding commands if not started with --shardsvr",
    "code" : 193,
    "codeName" : "NoShardingEnabled",
    "operationTime" : Timestamp(1552083136, 4),
    "$clusterTime" : {
        "clusterTime" : Timestamp(1552083136, 4),
        "signature" : {
            "hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
            "keyId" : NumberLong(0)
        }
    }
}

I checked the status of the shard and it looks like it was successful:

sh.status()
--- Sharding Status ---
  sharding version: {
    "_id" : 1,
    "minCompatibleVersion" : 5,
    "currentVersion" : 6,
    "clusterId" : ObjectId("5c82df7b7a98360e9dd3c5da")
  }
  shards:
        {  "_id" : "shard0000",  "host" : "127.0.0.1:27023",  "state" : 1 }
        {  "_id" : "shard0001",  "host" : "127.0.0.1:27024",  "state" : 1 }
  active mongoses:
        "4.0.6" : 1
  autosplit:
        Currently enabled: yes
  balancer:
        Currently enabled:  yes
        Currently running:  no
        Failed balancer rounds in last 5 attempts:  0
        Migration Results for the last 24 hours:
                No recent migrations
  databases:
        {  "_id" : "config",  "primary" : "config",  "partitioned" : true }
        {  "_id" : "testdb",  "primary" : "shard0000",  "partitioned" : true,  "version" : {  "uuid" : UUID("ed327ab3-1de9-4a0a-89f5-8d0bf81015fc"),  "lastMod" : 1 } }

So why do I get that error when I try to shardCollection? I running all of this in the mongos instance

If a replicaset needs to be added as shard in a sharded cluster, --shardsvr argument is mandatory when starting a mongod process.

mongod --shardsvr --replSet <replSetname>  --dbpath <path> --bind_ip localhost,<hostname(s)|ip address(es)>

If is it a config server, then --configsvr argument is mandatory

mongod --configsvr --replSet <replica set name> --dbpath <path> --bind_ip localhost,<hostname(s)|ip address(es)>

Refer the following link to deploy

Deploy a Sharded Cluster


Postgres 10 Backup: Should I run the backup in the master or in a slave? I have been using the continuous-archiving and Point-in-Time Recovery for backing up my postgres 10 DB https://www.postgresql.org/docs/10/continuous-archiving.html, however, the master database is handling a lot of work load and my slaves, that are read only, are not that busy, so, am wondering:

Is there any difference in running the backups in the master database than running them in one of the slaves?

I create two backups daily, so, I'm confident that it might be one of the reasons why the master database is experiencing much more workload than the replica ones.

You can run pg_basebackup against the slave server just as well as against the master.


Any way to send notifications to different Emails on success/failure of SqlServerAgent job? SQL Server 2012 R2 on Windows Server 2008R2

Is there any way to send notifications to different Emails:

on success of an Sql Server Agent job to one Email address and on failure - to another ?
on success (or failure) of a particular job step (but not the whole job)?

You have to use tsql to do that.

in the job step after your main job step add 2 more job steps:

step 2--success email

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'YOUR MAIL PROFILE',
    @recipients = 'COMPANYMAIL@COMPANY.COM',
    @body = 'Copy and Purge BAKs success !!',
    @subject = 'Copy and Purge BAKs success',
-- to send file attachments
    @file_attachments = 'D:\logs\log.TXT',
  -- Change below if you want to include query results from a table in the email.
  -- Note that below is not in HTML
    @query = 'select column1, column2 from table1';

step 3-- failure email

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'YOUR MAIL PROFILE',
    @recipients = 'COMPANYMAIL@COMPANY.COM',
    @body = 'Copy and Purge BAKs failed !!',
    @subject = 'Copy and Purge BAKs failed',
-- to send file attachments
    @file_attachments = 'D:\logs\log.TXT',
  -- Change below if you want to include query results from a table in the email.
  -- Note that below is not in HTML
    @query = 'select column1, column2 from table1';

and then use tsql sp_send_dbmail

Note: If you want HTML email to be send out, I have a script for that to help you out.


SSAS Partitioning update questions I am looking into automating my SSAS cube builds using SSIS and/or scripts and have a few questions.

I have a cube with yearly partitions and a Date Dim that has attribute indicators by day for "last 30 days", "last 60 days", etc. Obviously as the current date changes, the indicators at the day level will change as well. I realize I will have to "process full" the Date Dim daily, but will I have to process a previous year partition whose date range falls into the "last 60 days"? And then keep on processing that partition until "day 61" of the current year?
I have a Postal Codes Dim with over 2 million entries that I intend to update only once a year. After I load the new postal codes and fully process the cube, can I remove the Postal Code Dim from the dimension update step once I start to process daily again (i.e., processing only the last partition)?
I have a cube with 4 measure groups, but yearly partitions on just one measure group as the 3 others do not have nearly the number of records. If I process the last yearly measure group in a script, do I then need to "process full" on the 3 other measure groups or on the 3 other single partitions in those associated measure groups? I plan to use the Analysis Services Processing Task in SSIS for that step, but not sure whether to include partitions or measure groups.

I would recommend you start by reading this excellent introduction to SSAS processing.

To answer your questions:

You should ProcessUpdate your Date dimension then ProcessIndexes your cube. ProcessUpdate will not unprocess (unload) your measure group partitions like ProcessFull which is important if you only want to process the latest year partition. But it is a best practice to ProcessIndexes at the end of the transaction to rebuild the flexible aggregations and indexes that were dropped when dimension rollups changed during ProcessUpdate.
Yes if the Postal Code dimension changes only yearly you can omit it from your daily processing scripts.
For the measure groups with just one partition, processing the partition has the same effect as processing the measure group. I would recommend a ProcessData on measure groups or partitions that need reprocessing then a ProcessIndexes on the cube.

By the way beware of the Analysis Services processing task in SSIS. When you set it up the database ID is hardcoded in the command it builds and editing the connection string of the SSAS connection manager to point at a different SSAS database name won't change which database it processes. So that SSIS task only works well if you are sure you will be processing the same database ID on each server and won't ever need to process a second SSAS database on the same server.


How to link disaggregate data to aggregate data when aggregation adds 0/NULL values? I have a table sensor_data in a PostgreSQL 9.6 database:

    Column     |            Type             |
---------------+-----------------------------+
 id            | bigint                      | bigserial
 station_id    | integer                     | not null
 datetime_bin  | timestamp without time zone | not null (one minute resolution)
 category_a    | integer                     | not null
 category_b    | integer                     | not null
 sensor_data   | integer                     | not null

Indexes:

"station_logs_pkey" PRIMARY KEY, btree (id),
UNIQUE CONSTRAINT (station_id, datetime_bin, category_a, category_b),
btree (datetime_bin)
btree (sensor_id)

If the value for a combination of station_id, datetime_bin, category_a, category_b is 0, there is no row. We're aggregating this data to 15 minute bins, and want to add in 0s to make averaging on the aggregated data more user-friendly. We also periodically need to refresh a month of data, so wanted to have deleting the disaggregate data cascade to aggregates based on it.

Our current approach is the following, but I'd be curious to know if there is a better/more proper way.

Current approach

Aggregate table

    Column     |            Type             |
---------------+-----------------------------+
 id            | bigint                      | bigserial
 station_id    | integer                     | not null
 datetime_bin  | timestamp without time zone | not null (fifteen minute resolution)
 category_a    | integer                     | not null
 category_b    | integer                     | not null
 summed_data   | integer                     | not null (with 0s)

Disaggregate table

    Column     |            Type             |
---------------+-----------------------------+
 id            | bigint                      | bigserial
 station_id    | integer                     | not null
 datetime_bin  | timestamp without time zone | not null (one minute resolution)
 category_a    | integer                     | not null
 category_b    | integer                     | not null
 sensor_data   | integer                     | not null (without 0s)
 aggregate_id  | bigint                      | FOREIGN KEY aggregate(id)

Zeroes crossover table

    Column     |            Type             |
---------------+-----------------------------+
 one_minute_id |bigint                       | FOREIGN KEY one_minute(id) ON DELETE CASCADE
 aggregate_id  |bigint                       | FOREIGN KEY aggregate(id)

Triggers

when deleting from disaggregate, delete from aggregate with same aggregate_id
when deleting from disaggregate, cascades to zeroes table with Foreign Key
when deleting from zeroes table, delete from aggregate with same aggregate_id.

It sounds to me like you want the TimeScaleDB extension with a hypertable and 15 min time buckets.

https://www.timescale.com/

It's a free and open source project, like Pg. And it's actively developed.

https://github.com/timescale/timescaledb


Rebuild a table from history records A small disaster happened to me on my PostgreSQL 8.4 database.

I follow visitors to my webpage in two tables:

History

+---------------+-------------+
| whereseenlast | varchar(50) |
| uniqid        | varchar(13) |
| whenseenlast  | timestamp   |
| bla1          | varchar(10) |
| bla2          | varchar(10) |
| bla3          | varchar(10) |
+---------------+-------------+

Sample data:

+-----------------+---+---------------------+----------+----------+----------+
| where1.html     | 1 | 2000-01-01 00:00:00 | somebla1 | somebla2 | somebla3 |
| where2.html     | 1 | 2005-05-05 05:06:07 | somebla1 | somebla2 | somebla3 |
| where12345.html | 1 | 2012-11-22 11:22:33 | somebla1 | somebla2 | somebla3 |
+-----------------+---+---------------------+----------+----------+----------+

Visitors

uniqid is primary key, each visitor can be found just once in this table

+---------------+----------------+
| wherseenfirst | varchar(50)    |
| whereseenlast | varchar(50)    |
| whenseenfirst | timestamp      |
| whenseenlast  | timestamp,     |
| uniqid        | varchar(13) PK |
| notes         | varchar(20)    |
| xyz           | varchar(20)    |
+---------------+----------------+

Sample data:
+-------------+-----------------+---------------------+---------------------+---+----------+----------+
| where1.html | where12345.html | 2000-01-01 00:00:00 | 2012-11-22 11:22:33 | 1 | somenote | somexyz  |
+-------------+-----------------+---------------------+---------------------+---+----------+----------+

Problem

I accidentally deleted the Visitors table and I have no backup. But I am sure it is easy for an expert to rebuild the Visitors table from the history with a query. I know I will not be able to restore the notes and xyz columns but that is not a big deal.

Any suggestions please?

Something like:

select uniqid,
       min(case when visit_seq = 1 then whenseenlast end) as whenseenfirst,
       min(case when visit_seq = 1 then whereseenlast end) as whereseenfirst,
       min(case when visit_seq = visits_thisuser then whenseenlast end) as whenseenlast,
       min(case when visit_seq = visits_thisuser then whereseenlast end) as whereseenlast
from (
  select uniqid,
         row_number() over(w) as visit_seq,
         count(*) over(w range between unbounded preceding and unbounded following) as visits_thisuser,
         whenseenlast, whereseenlast
  from history
  window w as (partition by uniqid order by whenseenlast)
) visit
group by visit.uniqid

http://sqlfiddle.com/#!11/fb6f8/14/0


Does DB2 order data by the primary key I've recently had a discussion with a colleague who was pushing to remove order by clauses from a production query because the order by column was the same as the primary key.

After a lengthy discussion in which I tried to explain that he can't guarantee ordering based on the primary key the final conclusion was that he wasn't going to push for the MSSQL queries to be changed.
But he was still going to change the DB2 queries.

I couldn't immediately find an article disproving that DB2 orders queries by the primary key, and am currently wondering whether or not it does.

So my question is, how does DB2 order a query if there is no order by clause? Does it use the primary key?
How can you guarantee data is coming out ordered correctly, without an order by clause, in a parallel system?

No, your colleague is wrong.

All SQL proroducts - DBMS that behave according to the SQL standards - provide no guarantee that the result of a query output will be ordered in any way, unless there is an ORDER BY clause in the query.

As the IBM DB2 docs mention:

  Ordering is performed in accordance with the comparison rules described in Language elements. The null value is higher than all other values. If your ordering specification does not determine a complete ordering, rows with duplicate values of the last identified sort-key have an arbitrary order. If you do not specify ORDER BY, the rows of the result table have an arbitrary order.


Is it a bad practice to query pg_type for enums on a regular basis? In Postgres, I'm thinking of query pg_type for an up-to-date list of enumerations I'm using on a regular basis. I'd be using something like this:

SELECT pg_type.typname AS enum_type, pg_enum.enumlabel AS enmu_label FROM
pg_type JOIN pg_enum ON pg_enum.enumtypid = pg_type.oid;

or

SELECT distinct pg_type.typname AS enum_type FROM pg_type JOIN pg_enum ON
pg_enum.enumtypid = pg_type.oid;

Is this bad practice?

What @Jack said. Plus, if all you need is the list of registered values for an enum type, there are some Enum Support Functions to do that. Based on Jack's example:

SELECT enum_range(null::mood);

enum_range
--------------
{sad,ok,happy}

That's simpler and resilient against (unlikely) changes in future major Postgres versions that might break your query.

dbfiddle here


Separate Address Table, Latitude, Longitude We are designing a DB for a limited functionality social networking application. Users (Registered Users), Retail Outlets, etc are several entities we need to store. We need to store Address of each of these entities. Also we need to store latitude and longitude of Registered Users, Retail Outlets, etc.

Initially we designed the tables as shown below. We created a separate Address tables as the format of the address will be same for Users, Retail Outlets and other entities.

User(UserID, UserName, AddressID)
RetailOutlet(RetailOutletID, OutletName, AddressID)
Address(AddressID, CityID, AddressLine1, AddressLine2, Latitude, Longitude)

Now we realized that we need to search for Retail Outlets near to User's current location(CurrentLatitude, CurrentLongitude)
So we would need to run a query like

SELECT OutletName
from RetaileOutlet
INNER JOIN Address ON RetailOutlet.AddressID = Address.AddressId
WHERE Address.Latitude BETWEEN CurrentLatitude - 10 and CurrentLatitude + 10 and
      Address.Longitude BETWEEN CurrentLongitude - 10 and CurrentLongitude + 10

Now we think that above query would run slow because the number of users will be much more than the number of retail outlets. From DB Design/Performance perspective which of the following should be done.

Create index on Latitude, Longitude in Address table and on AddressID in RetailOutlet table. So there would be no change in table structure.
Move Latitude, Longitude from Address table to RetailOutlet and User tables. Also create index on Latitude, Longitude in RetailOutlet table. So the table structure would be as shown below.

User(UserID, UserName, AddressID, Latitude, Longitude)
RetailOutlet(RetailOutletID, OutletName, AddressID, Latitude, Longitude)
Address(AddressID, CityID, AddressLine1, AddressLine2)

Change table structure as shown below and create index on Lat, Lng in UserAddress table and on UserAddressID in User table.

User(UserID, UserName, UserAddressID)
UserAddress(AddressID, CityID, AddressLine1, AddressLine2, Latitude, Longitude)
RetailOutlet(RetailOutletID, OutletName, AddressID)
RetailOutletAddress(RetailOutletAddressID, CityID, AddressLine1, AddressLine2, Latitude, Longitude)

Introduce DB redundancy by copying Lat, Lng from Address table in the RetailOutlet table and create index on Lat, Lng in RetailOutlet table. With this option there will be no change in Address table and we will be able to use it for storing User address without change. Also we may be able to use it for storing Address of other entities.

User(UserID, UserName, AddressID)
RetailOutlet(RetailOutletID, OutletName, AddressID, Latitude, Longitude)
Address(AddressID, CityID, AddressLine1, AddressLine2, Latitude, Longitude)

The right answer is choice #5: use PostGIS. An example from OpenGeo:

  "Note the magic <-> operator in the ORDER BY clause. This is where the magic occurs. The <-> is a “distance” operator, but it only makes use of the index when it appears in the ORDER BY clause. Between putting the operator in the ORDER BY and using a LIMIT to truncate the result set, we can very very quickly (less than 10ms on a 2M record table, in this case) get the 10 nearest points to our test point."

(emphasis added)


How to get aggregate data from a dynamic number of related rows in adjacent table EDIT: Unknowing of the rule that prohibits cross-posting, I also asked this on Stackoverflow and chose an answer over there. Since there's another (fully working) answer in this thread though, I won't delete it. But for the solution I chose, check out this thread - https://stackoverflow.com/questions/52024244/how-to-get-aggregate-data-from-a-dynamic-number-of-related-rows-in-adjacent-tabl

I have a table of matches played, roughly looking like this:

player_id | match_id | result | opponent_rank
----------------------------------------------
82        | 2847     |   w    |   42
82        | 3733     |   w    |  185
82        | 4348     |   l    |   10
82        | 5237     |   w    |  732
82        | 5363     |   w    |   83
82        | 7274     |   w    |    6
51        | 2347     |   w    |   39
51        | 3746     |   w    |  394
51        | 5037     |   l    |   90
...       | ...      |  ...   |  ...

To get all the winning streaks (not just top streak by any player), I use this query:

SELECT player.tag, s.streak, match.date, s.player_id, s.match_id FROM (
    SELECT streaks.streak, streaks.player_id, streaks.match_id FROM (
        SELECT w1.player_id, max(w1.match_id) AS match_id, count(*) AS streak FROM (
            SELECT w2.player_id, w2.match_id, w2.win, w2.date, sum(w2.grp) OVER w AS grp FROM (
                SELECT m.player_id, m.match_id, m.win, m.date, (m.win = false AND LAG(m.win, 1, true) OVER w = true)::integer AS grp FROM matches_m AS m
                WHERE matches_m.opponent_position<'100'
                    WINDOW w AS (PARTITION BY m.player_id ORDER BY m.date, m.match_id)
                    ) AS w2
                    WINDOW w AS (PARTITION BY w2.player_id ORDER BY w2.date, w2.match_id)
                ) AS w1
            WHERE w1.win = true
            GROUP BY w1.player_id, w1.grp
            ORDER BY w1.player_id DESC, count(*) DESC
        ) AS streaks
    ORDER BY streaks.streak DESC
    LIMIT 100
    ) AS s
LEFT JOIN player ON player.id = s.player_id
LEFT JOIN match ON match.id = s.match_id

And the result looks like this (note that this is not a fixed table/view, as the query above can be extended by certain parameters such as nationality, date range, ranking of players, etc):

player_id | match_id | streak
-------------------------------
82        | 3733     |  2
82        | 7274     |  3
51        | 3746     |  2
...       | ...      |  ...

What I want to add now is a bunch of aggregate data to provide details about the winning streaks. For starters, I'd like to know the average rank of the opponents during each those streaks. Other data are the duration of the streak in time, first and last date, opponent name who ended the streak or if it's still ongoing, and so on. I've tried various things - CTE, some elaborate joins, unions, or adding them in as lag functions in the existing code. But I'm completely stuck how to solve this.

As is obvious from the code, my SQL skills are very basic, so please excuse any mistakes or inefficient statements. Also new to DBA so let me know if my question can be phrased better. For complete context, I'm using Postgres 9.4 on Debian, the matches_m table is a materialized view with 550k lines (query takes 2.5s right now). The data comes from http://aligulac.com/about/db/, I just mirror it to create the aforementioned view.

For a beginner in sql you sure picked some fairly advanced concept to start with ;-) Your question seems to boil down to what is known as island and gaps problems. I find it easiest to handle these kinds of problems by creating a group for consecutive events. One trick to accomplish this is to use two enumerations and calculate the difference:

row_number() over ( partition by player_id
                    order by match_id )

row_number() over ( partition by player_id, result
                    order by match_id )

if the difference between first and second grp changes it means that a change in result occurred. Since you will probably use this grp for several things I put it in a CTE:

with t as (
  select player_id, match_id, result, opponent_rank
       , row_number() over ( partition by player_id
                             order by match_id )
         -
         row_number() over ( partition by player_id, result
                             order by match_id ) as grp
  from matches
)
select player_id, match_id, result, opponent_rank, grp
     , count(1) over (partition by player_id, grp
                      order by match_id) as streak
     , avg(opponent_rank) over (partition by player_id, grp) as avg_rnk
from t
where result = 'w' and player_id = 82
order by player_id, match_id;

I believe this should give you a start, so I'll stop there. Welcome to the forum btw.


Large string concatenation in query I am trying to concatenates strings in T-SQL like

'This is a test ' + CHAR(13) + CHAR(10) + ' string'

When the string is long (I do not know the limit), I get an error:

  Some part of your SQL statement is nested too deeply. Rewrite the
  query or break it up into smaller queries.

How can I work around this? How is the SQL statement "nested"?

Should I switch to 0x???? notation if the string length is exceeding some value?

This question was asked 10 years earlier on

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e7244cf6-049e-4b8a-b230-226fdd96a694/large-string-concatenation?forum=transactsql

without getting a satisfactory answer.

edit

The total string to insert (with the concatenations and ':s) is 369711 chars long.
There are 6301 instances of char(13) and the same of char(10).
There are 19050 instances of +, not all necessarily used as concatenation operator.

It might depend on what is meant by "too long":

Length: If you mean strings of many characters, then try converting some of them to a MAX type (i.e. NVARCHAR(MAX) or VARCHAR(MAX) ). For example:

CONVERT(VARCHAR(MAX), 'This is a test ') + CHAR(13) + CHAR(10) + ' string'

Concatenations: If you mean operations involving many concatenations, there is definitely a maximum number that can be done in a single statement. I tested this a while ago and was going to blog about it but got distracted ;-). I just reviewed my testing and while there are a few different ways to concatenate, the simplest way (which is what you are doing) has a limit of around 3300 per statement. I say "around" 3300 because it varies based on some other factors related to memory. Sometimes I can get 3310, then I tried 3311 and it works, try again and it fails, try again with 3310 and it fails, try again with 3309 and it works.

If you have over this amount then you have no choice but to split the operation into multiple steps (i.e. multiple SET / SELECT statements). Although, the error for reaching the max here is:

  Msg 8631, Level 17, State 1, Line XXXX
  Internal error: Server stack limit has been reached. Please look for potentially deep nesting in your query, and try to simplify it.

And you aren't getting exactly that, so not entirely sure yet until you do more testing, or at least provide the error number. In either case, you can reduce the number of concatenations by combining the "\r" and "\n" into a single variable or VARBINARY literal. The following example shows that both methods produce the same output:

PRINT 'This is a test ' + CHAR(13) + CHAR(10) + ' string';
PRINT '-----------------------------';
PRINT 'This is a test ' + CONVERT(CHAR(2), 0x0D0A) + ' string';

PRINT '-----------------------------';
DECLARE @CRLF CHAR(2) = CHAR(13) + CHAR(10);
PRINT 'This is a test ' + @CRLF + ' string';

The benefit of the variable is that it is shorter, which helps keep large scripts a little more readable and not as large. But, you can't highlight one or more random lines and execute, unless you also highlight the DECLARE but that might not always work. The benefit of the VARBINARY literal is that you can easily execute random groupings of statements, or move statements to other scripts  / section of this script and not worry about forgetting the DECLARE. In either case, this might reduce a few hundred of your concatenations (assuming you already have around 3300 of them), but it doesn't prevent the error if you get a string value that requires hundreds more concatenations.

It's also quite possible that there's a different and/or simpler approach to what you are ultimately trying to accomplish that doesn't rely on concatenations. So it would help if you could please update the question to specify why you are doing this in the first place :-).

  There are 6301 instances of char(13) and the same of char(10).

Given three concatenations per set of "CR" + "LF" (i.e. + CHAR(13) is one, + CHAR(10) is two, and + '...' is the third), that is 18,903 concatenations right there. Yep, that's a bit over 3300 ;-).

  I want the insert into statements to be oneliners.

Ok. But, why? What do you actually gain from this? Embedded CRLF's are just fine for inserting.

Also:

What is the source of this data? Is it coming from SQL Server?
Are the strings VARCHAR or NVARCHAR? In the example in the question, it is only VARCHAR, yet in a comment on the question, you give an example of converting a VARBINARY literal into NVARCHAR.

In either case, doing either of the two approaches I noted above to reduce concatenations by one of the two pieces per CRLF won't help here as that will still leave you with 12,602 concatenations. So, you have two options:

Use a replacement / escape sequence In a comment on the question, Martin Smith suggested using {crlf}. My preference would be one of the following:

CHAR(31) : This is the non-printable "Unit Separator" character. It's only a single byte, quite unlikely to be in your string data, and won't get converted to something else if you accidentally change encondings. Unfortunately, it's not very readable as it won't even show up as a space, hence the words it separates will appear to have nothing between them.

PRINT REPLACE('one' + CHAR(31) + 'two', CHAR(31), CHAR(13)+CHAR(10));

'\n' : This is the almost universal "newline" escape sequence. This is two bytes and definitely more readable.

PRINT REPLACE('one\ntwo', '\n', CHAR(13)+CHAR(10));

In either case, you just INSERT INTO Schema.Table (Column) VALUES (REPLACE(....));
Convert entire string to VARBINARY : this results in a very portable, single-line value. This isn't readable, but there won't be any unexpected character conversions either, and you can use a standard ANSI / code page 1252 file encoding, whereas you would need to use either UTF-8 (with signature / BOM) or Unicode / UTF-16 (with signature / BOM) for the file encoding if the string data is Unicode / NVARCHAR.

You mentioned in a comment on the question something about having a byte array. If the source of the data is .NET, then the strings data is already UTF-16 LE (i.e. NVARCHAR) so simply doing INSERT INTO Schema.Table (Column) VALUES (0x{hex_from_byte[]}); will work just fine. If the destination is a VARCHAR column, then you can either:

convert in .NET using something along the lines of: Encoding.GetEncoding(1252).GetBytes(_StringVariable)
convert in T-SQL using: INSERT INTO Schema.Table (Column) VALUES (CONVERT(NVARCHAR(MAX), 0x{hex_from_byte[]}));. Please note that we are converting to NVARCHAR because the hex representation is UTF-16 LE. The return value from the CONVERT function will then implicitly convert to VARCHAR as it is inserted into the column.


How to set up Oracle 19c and/or RHEL so new files created using UTL_FILE have -rw-rw-r-- (664) permissions on Linux Working on getting a 19c installation working on RHEL 7.8. The database works, but we're having a problem with files created using the UTL_FILE package. They're being created with -rw-r--r-- (644) permissions - but we need them to be -rw-rw-r-- (664). This works fine for us on our current system (Oracle 11.2 on HP-UX), but of course no one who currently works for the company was involved in the earlier installs and setups, yada-yada, etc-etc. Sysadmins are playing with umask settings but my understanding (which is shaky) is that umask can be used to turn permission bits off but not on. Just to make it a little more fun, the database server is separate from the file/application server, with the files created by UTL_FILE being made available on the app server via NFS. However, I'm given assurances by our DBA's that the files on the database server have 644 permissions (I do not have access to the database server) which is what we're seeing on the app server side.
Can anyone suggest a way to have 19c UTL_FILE create files with permissions set to 644?

This depends on how you use UTL_FILE and what setup you have. Do you create files using UTL_FILE from scheduled jobs/preallocated databases processes or from databases processes spawned through the listener? Is this a RAC or single instance database? If single instance, is Grid Infrastructure in use or not?
Simplest case: single instance without Grid Infrastructure. Here just set umask to the desired value before starting the listener and the database interactively in the shell or the script used for that.
Different Permission When the Data File is Created/Added Remotely (Doc ID 961568.1)
With Grid Infrastructure: set the necessary setting as described in How to set the permission of the files created by listener using UTL_FILE on RAC(11.2-) env. (Doc ID 1980622.1)
srvctl setenv listener -t ORA_RDBMS_UMASK=002

Then restart. This sets the umask only for remote sessions connecting through the listener which spawns their processes. For scheduled jobs or shared servers or DRCP, you will need more than this.
srvctl setenv database -d orcl -t ORA_RDBMS_UMASK=002

I never tried this for the database resource in 19c. I guess it should work, but I leave the testing for you now.


Execution plan has TOP operator for query without TOP or ORDER BY I have product sales by month temporary table.(subset of much larger historical table) I'm running query to find new product not previously sold in the previous months for a specified 6 month range. So take January as base, in Feb show me all new product sold that wasn't in Jan sales. In March all new product not sold in Jan or Feb sales, etc. For first 5 months query returns sub second. On 6th month, it takes ~5 minutes. Comparing execution plans, on that 6th month, it's showing Top operator in my execution plan that's taking the extra time. But I'm not sorting or using Top X in select. What in my query is causing this? I can wait 5 mins since it's one-off request, but I'm more asking from a learning perspective, I want to understand the why it added to Top operator. Thanks in advance.

select divn_nbr, dept_nbr, vendorNumber, pid, nrfcolorNumber, periodcode
from ##tmpSFA a
where periodCode = '201512'
and not exists
(
    select 1 from ##tmpSFA b
    where ISNULL(a.divn_nbr, -99) = isnull(b.divn_nbr, -99)
    and isnull(a.dept_nbr, -99) = isnull(b.dept_nbr, -99)
    and isnull(a.vendorNumber, -99) = isnull(b.vendorNumber, -99)
    and isnull(LTRIM(rtrim(upper(a.pid))), -99) = isnull(LTRIM(rtrim(upper(b.pid))), -99)
    and isnull(a.nrfcolorNumber, -99) = isnull(b.nrfColorNumber, -99)
    and b.periodCode IN ('201507', '201508', '201509', '201510', '201511')
)

Here are the results and plan:

NOT EXISTS can introduce a TOP 1 (as can EXCEPT in some cases). This is because it only cares about the fact that at least one row exists. This is called short circuiting, and you want this - it is much more likely to speed up your query than slow it down. And I'd be really surprised if the TOP didn't show up in the plan for different date ranges, unless there was a substantial difference in row counts and a different plan was compiled. Maybe you could post the two .sqlplan files somewhere so that we could associate each plan with the query and help determine the reasons for any differences. With a screen shot and the query that allegedly goes with it, there's not a ton to go on.

I'm not convinced that the TOP in your case is making this query run for 5 minutes. Rather than just look at the plan, I'd actually investigate what the query is doing for 5 minutes, while it is running, by looking at sys.dm_exec_requests - is it blocking? What is the wait type? Since you're using a ##global temp table (why?), this could be due to blocking, but it's unclear from just a screen shot of a plan. Why doesn't your ##temp table have a clustered index? What other indexes does it have? The TOP would probably be more efficient if it could sort on something sensible other than the output of a full table scan. You could consider changing the subquery to be TOP (1) ... ORDER BY but adding a clustered index (or an index that covers and satisfies all the where clauses in the subquery) should accomplish something quite similar.

For more info on NOT EXISTS and alternatives, see this blog post.


Fixing wildcard expansion resulting in too many terms error (DRG-51030) How can I resolve the wildcard_maxterm problem in Oracle 10g?

My index creation syntax is:

begin
    ctx_ddl.drop_preference('SUBSTRING_PREF');
end;
/

begin
    ctx_ddl.create_preference('SUBSTRING_PREF', 'BASIC_WORDLIST');
    ctx_ddl.set_attribute('SUBSTRING_PREF', 'SUBSTRING_INDEX','TRUE');
end;
/

begin
    ctx_ddl.drop_preference('bus_obj1_lexer');
end;
/

begin
    ctx_ddl.create_preference('bus_obj1_lexer','BASIC_LEXER');
    ctx_ddl.set_attribute('bus_obj1_lexer', 'base_letter', 'YES');
    ctx_ddl.set_attribute('bus_obj1_lexer', 'mixed_case', 'YES' );
    ctx_ddl.set_attribute('bus_obj1_lexer','printjoins', ';,@_');
    ctx_ddl.set_attribute('bus_obj1_lexer','skipjoins', '-');
end;
/

create index BUS_OBJ_FTS_Name on BUSINESS_OBJECTS1(name)
  indextype is ctxsys.context
  parameters ('wordlist SUBSTRING_PREF
               MEMORY 128M
               DATASTORE CTXSYS.DEFAULT_DATASTORE
               SECTION GROUP CTXSYS.AUTO_SECTION_GROUP
               STOPLIST CTXSYS.EMPTY_STOPLIST
               LEXER bus_obj1_lexer
               SYNC (ON COMMIT)');

And my query is:

select * from BUSINESS_OBJECTS1 where contains(name,'%WIN%')>0 and rownum<=100;

There are 15 million rows in that table, and more than 50000 rows match that query. I have set wildcard_maxterm=50000 and default=20000 but I'm still getting this error:

  DRG-51030: wildcard query expansion resulted in too many terms.

Can anybody hep me how to solve this error?

The wildcard_maxterm is set to the maximum. Actually, the maximum in 10g is 15,000. I think the best approach is to limit the search term. The Oracle error action is to make the query narrower. Here is Oracle's documentation on wildcards (%_). The % means zero or more characters. The _ means match in a single position. And 'WIN' is a broad term for a contains search.

I think you should try a very narrow query and then gradually broaden the query until you find right combinations. Can you remove the % before and after and try a % only at the end? Try to increase the characters before or after WIN. Try multiple queries for more specific terms. Try a fixed number of _ characters instead of a %?


Foreign key references on column definitions are ignored. Feature, not a bug — why? Good old references constrains. They work like a charm when defined at the table level.

create table foo (id int primary key);

create table bar (id int, foreign key(id) references foo(id));
insert into bar values (1);
-- ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (...)

But if you come from another ecosystem and are used to occasionally define foreign key constrains at the column level, this is what happens:

create table baz (id int references foo(id));
insert into baz values (1); -- happily takes a value that isn't there in foo
select id from baz;
-- 1

What happens is that the references has been recognized, but ignored.

It turns out that this is not a bug. The MySQL documentation says they do it, and that's all you need to know:

  MySQL parses but ignores “inline REFERENCES specifications” (as defined in the SQL standard) where the references are defined as part of the column specification. MySQL accepts REFERENCES clauses only when specified as part of a separate FOREIGN KEY specification.

The MariaDB documentation is slightly more verbose on their rationale:

  MariaDB accepts the REFERENCES clause in ALTER TABLE and CREATE TABLE statements, but that syntax does nothing. MariaDB simply parses it without returning any error or warning, for compatibility with other DBMS's. However, only the syntax described below creates foreign keys.

Now what could be the use for this "feature" that helps "compatibility" with other DBMS — and the standard — by silently breaking the very purpose of the reference, while at the same time, correctly implementing it does not look like a big effort since foreign key constrains are indeed enforced when declared at the table level? And don't tell me this cannot be fixed because people rely on the fact that foreign constrains can be broken when declared at the column level.

Please help me make sense out of this.

EDIT: I just realized that by "compatibility with other DBMS", the MariaDB documentation may actually be referring to MySQL. This could either be a good motive for MariaDB to stick to the (unmotivated) behavior of MySQL, or a missed opportunity to improve their fork.

By design MySQL has been less SQL standard compliant than other open source database (i.e PostgreSQL): I think this was an intented design choice in both cases and I think that for MySQL the road is still long even with all Oracle Corp. and MariaDB achievements in the last decade.


How do I generate scripts to drop indexes, constraints, and keys for a database to change columns from int to bigint? I've worked myself into a corner, and I'm not sure how to handle this.

I have a large set of databases with generally the same structure.  Each database has a list of ID columns that are currently int that i need to convert to bigint.  Presently, i'm creating scripts by hand to remove all of the constraints, indexes, and keys before modifying the columns to bigint and putting all of those objects back.

The problem I'm running into with slightly modifying the same script over and over is that I wind up losing objects or creating new objects along the way, which i'm not supposed to do.  There's a few catches to this:

1) Some indexes are used in custom logic on a database, so they'll
be present in some databases but not all.  So, losing indexes is
very bad.

2) Disk space is a major concern, so I need to make sure I don't
include additional indexes (which apparently cost this?)

3) There's a lot of logic that depends on keys and constraints
working, so I can't risk losing any of these.

Is there a way to create scripts for this, so I don't have to do these comparisons by hand and risk losing or adding objects that shouldn't be there?

The DDL to make the change is just a text string. The necessary meta data is in the relational database already. This meta data can be queried by SQL. SQL can produce a string as output.

Put this together and you get a meta-SQL-generator ... thing. Something like this:

select
    'ALTER TABLE ' +
    OBJECT_SCHEMA_NAME(c.object_id) +
    '.' +
    OBJECT_NAME(c.object_id) +
    ' ALTER COLUMN ' +
    c.name +
    ' bigint ;'
from sys.columns as c
inner join sys.objects as o
    on o.object_id = c.object_id
where c.name = 'id'
and o.type = 'u'

You may need to add some square brackets, depending on your naming convention.

Write a similar query for each object type you're dealing with - constraints, indexes etc. There is a sys. or INFORMATION_SCHEMA. table to cover all of them. At the end you'll have a script which will capture the current schema. Run it once per target database. Pipe the output to a file (or in SSMS use "results to file" setting). That file will be a new SQL DDL script which will effect your change and restore the database to its previous state, including all customisations. The meta-script and produced scripts can be pushed to source control for tracking.


Query timeout expired error on SQL Server via ASP page I am getting the error

  [Microsoft][Sql Server Native Client 11.0]
  Query timeout expired

on SQL Server via ASP page. Most of the time it is working fine but sometimes I am getting this error. Can anyone tell me If this a SQL Server issue or ASP.NET issue?

I have run the same query in SSMS, it completed within seconds. ASP page can query anything. I am using sa user for auth, so I think it has full permissions.

This can be issue with both depending on how you look at it.  You are getting a 'Query timeout' and not a 'Connection timeout'.
You have 2 options.

Increase your query timeout setting.  Most of the time by default it
is 30 seconds. See this links for
ODBC and
SqlCommand.
Set up an Extended Event trace (If you are not comfortable, do a
server side  trace with proper filters) and find the query that is
timing out.  Work on improving the run time till it is below 'Query Timeout' setting.  There were times I was
able to capture the query with out trace and just by using sp_whoisactive.  I was watching
total run time.


Insert on conflict return false otherwise true in single query in stored function At now I use code like that

create function something()
returns boolean
as
$$
begin
   if exists (select 1 from some_table where some_cond) then
       return false;
   end if;

   insert into some_table (value)
   values (2)
   -- on conflict do !!! return false
   returning true;
end
$$
language 'plpgsql';

Is there a way to return value from on conflict section without defining a variable or temp table in one query?

You can handle exceptions.

create table test (id int primary key, data text);

insert into test values (1,'a'), (2,'b'), (3, 'c');

create or replace function test_error(_id int, _data text)
returns boolean as $res$
declare res boolean;
begin
    res = true;
    begin
        -- uncomment to check other exception
        --select 1 / 0;
        insert into test values (_id, _data);
    exception
    when unique_violation then
        res = false;
    when others then
        raise exception 'Other exception';
    end;
    return res;
end;
$res$ language plpgsql;

select test_error(4, 'd');

| test_error |
| :--------- |
| t          |

If you try to insert a duplicate key:

select test_error(1, 'n');

| test_error |
| :--------- |
| f          |

db<>fiddle here


Detect if two time periods overlap I have an HR system and I need to detect if a new leave (time off) request conflicts with an existing one. I have written code to do this based on the entire day; I now need to do the comparison based on half days.

Here is my data structure (and the data format). I am using SQL Server 2008 R2.

StartDate     (datetime)
EndDate       (datetime)

The following 2 variables show if the user is taking a half or a full day off.
The options for each are listed and explained.

StartDateAMPM (int) 0=All Day (StartDate may or may not be the same as the EndDate),
                    1=AM, (User is out of the office only in the morning)
                    2=PM  (User is out of the office only in the afternoon)

EndDateAMPM   (int) 0="All Day" / "half day" / "StartDate = EndDate",
                    1=AM (The user is off for the morning only)

**NOTE This cannot be PM (as you cannot end a leave with a half day in the afternoon)

**IF this is a "0", there are 3 possibilities --
A) a full day leave where the StartDate <> EndDate
B) a full day where the StartDate = EndDate
C) a partial day where StartDate = EndDate
   and the user is off in the morning or the afternoon

I pass this info to the database and am trying to see if the new leave conflicts with existing leave. Here is the code to do it for a single day:

Legend:  @Start DateTime -- StartDate from new leave
         @end   DateTime -- EndDate from new leave
         @StartDateAMPM (int) 0,1,2 (see above for details)
         EndDateAMPM    (int) 0,1   (see above for details)

select count(ID)
from v_VacReqWHalfDays
where
(
        @Start BETWEEN StartDate and EndDate
    OR  @End   BETWEEN StartDate and EndDate
    OR
    (
        @Start <= StartDate
        AND @End >=EndDate
    )
)
AND kUserID = @UserID;

If Count(ID) > 0, there is a conflict.

My question is how to incorporate the 1/2 day leave. People can take full days or half days off (the morning or the afternoon).

I want to use TSQL to determine if the current leave request conflicts (overlaps) with an existing and can't figure out how to do it.

I realize this has been answered before on Stack Overflow. It got me going in the right direction.

I now realize I should add the actual time stamp of the leave to my Start and End Dates and do a simple compare. I wish I had figured this out months ago; it would have saved me much pain and suffering.

For anyone in the future who has a similar issue:

My new StartDate will be recorded as 08/22/2015 09:00.000 instead of 08/22/2015 00:00.000
The EndDate will be 8/23/2015 13:00:00.000

I can then compare as per the linked answer:

(
    (@Start > StartDate and @Start < EndDate)
    OR (@End > StartDate and @End < EndDate)
    OR (@Start < StartDate and @End >EndDate)
)


This is possible with ORDER BY score DESC and user variable.

Create table/insert data

CREATE TABLE logins
    (`id` INT, `login` VARCHAR(4), `score` INT)
;

INSERT INTO logins
    (`id`, `login`, `score`)
VALUES
    (1, 'log1', 4),
    (2, 'log2', 4),
    (3, 'log3', 28),
    (4, 'log4', 1),
    (5, 'log5', 3)
;

Query

With ORDER BY score DESC and user variable this also counts the same score up.

 SELECT
   *
   , (@rank := @rank + 1) AS rank
  FROM
   logins
  CROSS JOIN(
   SELECT
     @rank := 0
   )
   AS
    init_var_var
  ORDER BY
   logins.score DESC

Result

   id  login    score  @rank := 0    rank
------  ------  ------  ----------  --------
     3  log3        28           0         1
     1  log1         4           0         2
     2  log2         4           0         3
     5  log5         3           0         4
     4  log4         1           0         5

Query

As delivered table so you can filter out one record.

SELECT
   id
 , login
 , score
 , rank
FROM (
  SELECT
   *
   , (@rank := @rank + 1) AS rank
  FROM
   logins
  CROSS JOIN(
   SELECT
     @rank := 0
   )
   AS
    init_var_var
  ORDER BY
   logins.score DESC
)
 AS logins_ordered_ranked
WHERE
  id = 2

Result

    id  login    score    rank
------  ------  ------  --------
     2  log2         4         3

Query without WHERE id=2

SELECT
   id
 , login
 , score
 , rank
FROM (
  SELECT
   *
   , (@rank := @rank + 1) AS rank
  FROM
   logins
  CROSS JOIN(
   SELECT
     @rank := 0
   )
   AS
    init_var_var
  ORDER BY
   logins.score DESC
)
 AS logins_ordered_ranked

Result

    id  login    score    rank
------  ------  ------  --------
     3  log3        28         1
     1  log1         4         2
     2  log2         4         3
     5  log5         3         4
     4  log4         1         5


Mysql Inner join performance I have performance problem! I searched for Inner Join, tried optimize table but still the same. I have Users, Entries, Tags and tag_user tables.

This code works perfectly :

select * from `entries`
where (
    select count(*) from `users` where `entries`.`profile_id` = `users`.`id`
) >= 1
order by `id` desc

And I should add this to complete my query :

select count(*) from `tags`
inner join `tag_user` on `tags`.`id` = `tag_user`.`tag_id`
where `tag_user`.`user_id` = `users`.`id` and `tags`.`id` in (?,?)) = 2

It becomes like this :

select * from `entries`
where (
   select count(*) from `users`
   where `entries`.`profile_id` = `users`.`id` and (
        select count(*) from `tags`
        inner join `tag_user` on `tags`.`id` = `tag_user`.`tag_id`
        where `tag_user`.`user_id` = `users`.`id` and `tags`.`id` in (119,36)) = 2
    ) >= 1
order by `id` desc

Any solution for better performance ?

The first query will be faster this way:

select * from `entries`
    where EXISTS (
        select * from `users` where `entries`.`profile_id` = `users`.`id`
                 )
order by `id` desc

It needs an index (PRIMARY KEY?) on users.id.  It is faster because the subquery quits when finds one row, rather than counting all of them.

The query part cannot use that technique.  But I think it is much simpler than what you have:

SELECT  e.*
    FROM
      ( SELECT  user_id
            FROM  `tag_user`
            WHERE  tag_id IN (119, 36)
            GROUP BY  user_id
            HAVING  count(*) = 2
      ) x
    JOIN  entries AS e  ON e.profile_id = x.user_id

Notice that users does not seem to be of any use.

entries will need INDEX(profile_id).

I assume tag_user is a many-to-many mapping table?  Have you followed all the advice in this?  The advice there may provide further performance boost.

If you need to discuss this further, please provide SHOW CREATE TABLE.


postges: psql csv import fails although file exists Trying to import a csv file as follows:

psql -h 127.0.0.1 -d jira_statistics -U admin -p 5432 -c "COPY bugs FROM '/home/centos/bugs.csv' delimiter '|' csv;"
Password for user admin:
ERROR:  could not open file "/home/centos/bugs.csv" for reading: No such file or directory
HINT:  COPY FROM instructs the PostgreSQL server process to read a file. You may want a client-side facility such as psql's \copy.

However both the file and the table exist in jira_statistics db

[root@jira-statistics ~]# ls /home/centos/bugs.csv
/home/centos/bugs.csv

[root@jira-statistics ~]# psql -h 127.0.0.1 -p 5432 -U admin jira_statistics
Password for user admin:
psql (9.2.24, server 11.1 (Debian 11.1-3.pgdg90+1))
WARNING: psql version 9.2, server version 11.0.
         Some psql features might not work.
Type "help" for help.

jira_statistics=# \dt
         List of relations
 Schema |   Name   | Type  | Owner
--------+----------+-------+-------
 public | bugs     | table | admin
 public | devtasks | table | admin
 public | releases | table | admin
 public | stories  | table | admin

The most common reason for COPY failing is what is suggested in the hint, but then the error is a "Permission denied".

Since you get "No such file or directory", and a simple ls shows that the file exists, there are first a few plausible explanations that you want to check:

/home/centos/bugs.csv is a soft link pointing to an non-existing file. Use ls -l to be sure an check the destination if it's a soft link.
127.0.0.1:5432 is redirected at the network level to a different machine. This technique is widely used to connect to remote services through SSH tunnels.
Your OS uses fancy security/isolation features that forbids postgres to read /home/centos/bugs.csv without issuing a Permission denied error. Try ls -l /home/centos/bugs.csv under the postgres OS account.
Your server and client don't run under the same MOUNT namespace, due to containerization.

In cases #2 #3 or #4, just use \copy as suggested in the HINT part of the error message.


Why is DBMS_CRYPTO inaccessible for most users by default? Oracle's DBMS_CRYPTO package is locked down by default. Very few users have permissions on it; the only ones on my database are SYSMAN, OWBSYS, and APEX_030200. (Of course the owning SYS has privileges, too.) These notably don't include even the default SYSTEM account. The documentation states

  Oracle Database installs this package in the SYS schema. You can then grant package access to existing users and roles as needed.

in the "Security Model" section, so the behavior is documented as normal.

But what I don't understand is why. I could not locate any information about the reason behind this default either in the package's documentation or the guide on using it. Is there some risk that these functions pose? Would granting access to PUBLIC be a bad idea for some reason?

A fundamental principle of Oracle security is everything is prohibited unless specifically granted.  You see this even in creating a new user.  Unless you specifically grant them CREATE SESSION (or a role that includes it) they can't even log on, even with proper credentials.  There is no reason grant execute on that package by default.  And keep in mind that there is really nothing "special" about SYSTEM.  It is just a default DBA user.  Also consider that DBMS_CRYPTO is of more use (in general) to apps developers, so why should it break the Principle of Least Privilege by making effectively available to everyone?


Backup a database from Azure SQL Managed Instance and restore to on premise SQL Server I have three databases that I am migrating to Azure SQL Managed Instance. One is a Utility database, just a small collection of generic functions and procedures. One is the OLTP database for a web application. The third is a read-only archive of the OLTP database containing historic data for analysis and reporting as well as audit tables. I need to be able to restore these databases to on premise development and test servers. So far, I have been unsuccessful.

My first attempt was to use backup to Azure Blob Storage. But no matter what version of SQL Server I use locally, I always get an error that the Copy Only backup is from a newer version and restore is not supported.

Next, I tried Export Data tier application from SSMS v.18.1. Unfortunately, it never gets past the export. I get dozens of errors that read External references are not supported when creating a package from this platform. One of the greatest strengths of Managed Instance over Azure SQL DB is the ability to do cross-database queries. I have a number of views and procedures that link tables in the two databases. Additionally, some procedures in the OLTP database call the functions in the Utility database. Since MI is not Azure SQL database, you would think this wouldn't be a problem!

Searching Google, I found this question on Stack Overflow. So I tried the Visual Studio approach proposed as an answer. Although it appears to work, meaning it creates a multi-gigabyte file on my local machine, attempting to import that file using SSMS gives me an error that reads No data is present in this file. So then I tried the second possible solution using sqlpackage.exe. But that solution doesn't work for me either as it only references exporting the schema. And if I try to export the whole database with /Action:Export I get the same error message as SSMS gives.

I also examined this Database Administrators question. No help - that is what I originally attempted. If anyone has any other thoughts I would greatly appreciate hearing them!

I've been able to export data from an Azure Managed Instance (MI) to a SQL Server 2012 system successfully without the need of Visual Studio.

Using SQL Server Management Studio (SSMS) right click on the database in you want to copy choose Tasks > Export Data-tier Application. Choose where to save the .bacpac file that is created.

A .bacpac file is a zip file containing XML that defines your tables and relationships along with BCP files that store the data of each table.

If you have 7zip extract the bacpac file to see the contents. If you don't, you can rename the file extensions from .bacpac to .zip and extract it using File Explorer (right click > Extract All...)
Open the model.xml file in your favourite text editor and look for the attribute called CompatibilityMode. This will have been set to the compatibility level of the MI (currently 150). Change it to the compatibility level you are importing to. I was importing to SQL 2012 so changed it to 110.
Remove any other incompatible features by commenting out the relevant line in model.xml.

For example, SQL 2012 can't do In-Memory tables so I had to comment out this section from the model.xml file:

    <!--<Element Type="SqlFilegroup" Name="[XTP]">
            <Property Name="ContainsMemoryOptimizedData" Value="True" />
    </Element>-->

Setting the Value to false may also work.

Putting it back together. Microsoft have added some simple validation to tell if the bacpac file is corrupt or has been tampered with. If you have made any changes to model.xml file you will need to calculate a new checksum value for it. The value is stored in the Origin.xml file in the bacpac.

You can generate a new checksum value by using PowerShell:

Get-FileHash -Path C:\Path\To\Extracted\bacpac\model.xml | Format-Table -Property Hash

You should get an output like this:

Hash
----
ED8754D205946850F3CED0B033342BF3D88A43E146E57D861191AACC1AC1659A

Open the Origin.xml file from the extracted bacpac files and look for a line like this at the end and replace the existing hash value with the new one:

<Checksums>
  <Checksum Uri="/model.xml">ED8754D205946850F3CED0B033342BF3D88A43E146E57D861191AACC1AC1659A</Checksum>
</Checksums>

Make sure you don't add any spaces or extraneous characters.

Now zip the files back up making sure the top level of the archive is the files and not the folder you extracted everything too. If you open the new file you should see the xml files and not a single folder name.

You should now be able to import the bacpac into your non-Azure SQL database.

Some notes  about bacpac backups:

They may not be transactionally consistent. For an export to be transactionally consistent, you must ensure either that no write activity is occurring during the export, or that you are exporting from a transactionally consistent copy of your Azure SQL database.
If you are exporting to blob storage, the maximum size of a BACPAC file is 200 GB. To archive a larger BACPAC file, export to local storage.


Shrink database or DB files not reducing size even after dropping biggest table Our MDF file was originally 550GB in size (log is just 10MB), and the biggest table we had [HourlyCounters] was 140 million rows with 580 columns (varchar[50]). It's a table with a very inefficient schema, but there's nothing we can do about that.

Anyways, after moving some data around, I was able to drop [HourlyCounters]. So now, the new table (with the same name) has 50K rows. I then ran SHRINKDATABASE and SHRINKFILE, and the MDF went down about 40GB. I know that's a decent chunk, but I know for a fact that this dropped table was larger than that (the current backup of that table with only 7M rows is 63GB).

How can SQL Server release all that free space? The new version of [HourlyCounters] doesn't currently have any indexes, but I know the old one did have them.

It's worth mentioning how I went about dropping the table:

Rename the original table [HourlyCounters] to [HourlyCounters_Old] with a right-click in SSMS and Rename. This table had indexes.
Create a new table [HourlyCounters] with no indexes so that processes that stored in this table are not affected.
Create table [HourlyCounters_New] where I would store the most recent data from [HourlyCounters] (now called [HourlyCounters_Old]). [HourlyCounters_New] now has 7M rows.
Drop [HourlyCounters_Old]

Other info that may help:
The drive where the data files are stored (E:) is 1.89TB, with 1.44TB used and 468GB free.

This is the result of the following query:

SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0
AS AvailableSpaceInMB
FROM sys.database_files;

name      AvailableSpaceInMB
DB        53490.812500
DB_log    441.703125

Thanks.

Exactly how did you run the SHRINK commands?  My understanding is that by default it just releases unused space at the end of the file, but you can also have the database compact itself first to make more space available.  This is a slow and expensive operation, though; around an hour per 10 GB in my recent experience.

That said, I suspect your database is using more space than it ought to due to fragmentation.  If you run a query like this:

SELECT TOP 10
    '[' + S.name + '].[' + O.name + ']' AS TableName,
    CASE WHEN I.type_desc = 'HEAP' THEN '(heap)' ELSE '[' + I.name + ']' END AS IndexName,
    Stats.avg_fragmentation_in_percent, Stats.fragment_count
FROM
    sys.indexes AS I
    INNER JOIN sys.objects AS O ON I.object_id = O.object_id
    INNER JOIN sys.schemas AS S ON O.schema_id = S.schema_id
    CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(), O.object_id, I.index_id, NULL, NULL) AS Stats
WHERE
    S.name NOT IN ('sys')
ORDER BY
    Stats.avg_fragmentation_in_percent * Stats.fragment_count DESC

...you'll see the tables and indices which are both large and badly fragmented.  Running ALTER INDEX MyIndx ON MyTBl REBUILD on a few of those should recover some space.  As Aaron Bertrand said, you probably don't want to actually free this space with SHRINK; your database will need it sooner or later.

Heap tables are prone to wasted space; using ALTER TABLE MyTbl REBUILD on your bigger tables may help too.


Targeting MSX with job on Multi-Server Administration I am using Multi-Server Administration to execute jobs on multiple targets. This works well, but I am not able to enlist the master as a target. I receive the following error when attempting to enlist the MSX as a TSX:

  Server 'XXXXX' is an MSX. Cannot enlist one MSX into another MSX.
  (Microsoft SQL Server, Error: 14299)

I would think that running these same jobs on the master would not require managing a local job as well as the multi-server jobs.

Any help is appreciated.

Based on my understanding, the master server (MSX) cannot be a target server (TSX) that receives jobs from another master server i.e You cannot list one MSX server into another MSX, you have to defect it and make sure it is not enlisted with any other master server.
Its a simple two-level hierarchy: a server is a master server, a target server, or neither. Cannot be both at the same time.
FROM BOL :

When creating a multiserver environment, consider the following:

Each target server reports to only one master server. You must defect a target server from one master before you can enlist it into a different one.
The master and target servers must be running on the Windows NT 4.0 or Windows 2000 operating system.
When changing the name of a target server, you must defect it before changing the name and reenlist it after the change.
If you want to dismantle a multiserver configuration, you must defect all the target servers from the master server.

You may also want to read up : SQL Server Multiserver Administration: Master and Target Servers


SQL Prove memory pressure - high Buffer Cache Hit Ratio, but low Page Life Expectancy Our production server (2012, VM + SAN) has 32 GB of RAM, the database size is ~80GB. The application uses TempDB heavily - disk is hit ~100 MBps both reads & writes.  Seeing tons of SQL compilations/sec .. 95% of all batch requests are compilations.

Ideally would like to increase the RAM to 64GB or 128GB, but need to 'prove' to team that it's required.

Buffer Cache Hit Ratio (BCHR) is 99.9%, but Page Life Expectancy (PLE) is only ~400.
What's the explanation for this ?
I though PLE & BCHR had a linear relationship (i.e. they increase or decrease together)

On other VMs with larger databases and lot more RAM, both BCHR & PLE are high.

Current wait stats and perf counters

BCHR can be very misleading due to read-ahead:

The Database Engine supports a performance optimization mechanism called read-ahead. Read-ahead anticipates the data and index pages needed to fulfill a query execution plan and brings the pages into the buffer cache before they are actually used by the query. This allows computation and I/O to overlap, taking full advantage of both the CPU and the disk.[link]

You are very likely seeing the impact of read-ahead filling the buffer cache pre-emptively, which distorts the BCHR counter. Excellent and very in-depth article from Jonathan Kehayias titled Great SQL Server Debates: Buffer Cache Hit Ratio covers this.


Patients profiles database I am making software for patients in hospitals. I want to make an option that if a doctor has prescribed a drug that interacts with another drug that the patient already uses, a warning will appear explaining that the two drugs shouldn't be administered together.

There will be data in the database for every drug, with its composition and its interactions with other drugs.

How can I link between drug interactions and make that feature possible in my design?

For example:

patient: Albert Ricky
diagnosis: asthmatic patient
treatment: salbutamol inhalation

When salbutamol is written in the patient profile in the database, a warning message should appear:

  WARNING: that patient has been prescribed atenolol since 10-9-2010 as a hypertensive drug and that interacts with salbutamol, causing a severe rash and swollen edema.

You could have one table that tracks what a patient takes, and another that tracks which drugs interact with which:

patient_drugs
-------------
   patient_id (FK to main patient table)
   drug_id (FK to main drug table)

drug_interactions
-----------------
   drug_id (FK to main drug table)
   reacting_drug_id (FK to main drug table)
   interaction_description (text)

Then to find a patient's drug interaction you could maybe do this:

select *
from drug_interactions
where drug_interactions.drug_id in (select drug_id
                                    from patient_drugs
                                    where patient_id = $PATIENT_ID)


About "Transaction ID Wraparound" Now , I read the document about "Transaction ID Wraparound " , but there are something that I really
don't understand, the document is the following url
http://www.postgresql.org/docs/9.0/static/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND

23.1.4.  Preventing Transaction ID Wraparound Failures
PostgreSQL's MVCC transaction semantics depend on being able to compare transaction ID (XID) numbers: a row version with an insertion XID greater than the current transaction's XID is "in the future" and should not be visible to the current transaction. But since transaction IDs have limited size (32 bits) a cluster that runs for a long time (more than 4 billion transactions) would suffer transaction ID wraparound: the XID counter wraps around to zero, and all of a sudden transactions that were in the past appear to be in the future — which means their output become invisible. In short, catastrophic data loss. (Actually the data is still there, but that's cold comfort if you cannot get at it.) To avoid this, it is necessary to vacuum every table in every database at least once every two billion transactions.

I don't understand the statements "would suffer transaction ID wraparound: the XID counter wraps around to zero, and all of a sudden transactions that were in the past appear to be in the future — which means their output become invisible"
Can someone explain this ? Why after the database suffers transaction ID wraparound would transactions that were in the past appear to be in the future ? In short, I want to know if the PostgreSQL will in  the " data loss" situation after transaction ID wraparound by autovacuum。
For my personal views, we can get the current transaction ID by using txid_current() function whoes output is 64 bit and will not be cycled.So I think the Insertion transaction ID  of tuples which knows as xmin will nerver greater than the xid which get by txid_current() function. Except that  you will use pg_resetxlog reset reset transaction ID after shuting down PostgreSQL Server. Am I right ? Thanks

Why after the database suffers transaction ID wraparound would
  transactions that were in the past appear to be in the future ?

They don't. The quoted text just explains why postgres needs to use modulo 231 arithmatic (which means transactions can wrap around as long as old transactions are 'frozen' early enough):

  Normal XIDs are compared using modulo-2^31 arithmetic. This means that
  for every normal XID, there are two billion XIDs that are "older" and
  two billion that are "newer"

to be specific:

  old row versions must be reassigned the XID FrozenXID sometime before
  they reach the two-billion-transactions-old mark

or wrapping the XID around would cause things to break. To prevent that, postgres will start to emit warnings, and eventually shut down and refuse to start new transactons if necessary:

  If for some reason autovacuum fails to clear old XIDs from a table,
  the system will begin to emit warning messages like this when the
  database's oldest XIDs reach ten million transactions from the
  wraparound point:

WARNING:  database "mydb" must be vacuumed within 177009986 transactions
HINT:  To avoid a database shutdown, execute a database-wide VACUUM in "mydb".

  (A manual VACUUM should fix the
  problem, as suggested by the hint; but note that the VACUUM must be
  performed by a superuser, else it will fail to process system catalogs
  and thus not be able to advance the database's datfrozenxid.) If these
  warnings are ignored, the system will shut down and refuse to start
  any new transactions once there are fewer than 1 million transactions
  left until wraparound

In other words "transactions that were in the past appear to be in the future" and "data loss" are entirely theoretical and will not be caused by transaction ID wraparound in practice.


SET STATISTICS TIME ON elapsed time is different than actual execution time I have a stored procedure which I'm running while using SET STATISTICS TIME ON, and overall the execution time of the procedure is 31 seconds, but when looking at the information from STATISTICS TIME, the result of adding up all of the elapsed times for each query is greater than 31 seconds.

For example:

SQL Server Execution Times:   CPU time = 109 ms,  elapsed time = 128 ms.

SQL Server Execution Times:   CPU time = 156 ms,  elapsed time = 159 ms.

SQL Server Execution Times:   CPU time = 1528 ms,  elapsed time = 15901 ms.

SQL Server Execution Times:   CPU time = 1248 ms,  elapsed time = 8055 ms.

SQL Server Execution Times:   CPU time = 6037 ms,  elapsed time = 30433 ms.

SQL Server Execution Times:   CPU time = 6069 ms,  elapsed time = 30776 ms.

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

I feel like I'm getting things mixed up while trying to read the information here, since the execution time of the query definitely isn't 85452 ms.

Also, I know that if the CPU time is greater than the elapsed time then the query went parallel, and even when double-checking this on the actual execution plan, there aren't any parallel operations executed.

So, why is the elapsed time in STATISTICS TIME different from the actual execution time of a query?

The SET STATISTICS options will report the statistics for each statement, which when coupled with procedures can lead to including what are effectively "summary" results - as the call to the procedure itself resolves, an additional statistics record will be returned containing the statistics for the procedure itself.

This can lead to multiple "summary" results if a procedure does something like call another procedure.

To illustrate when the SET STATISTICS options return information, we can create up a couple of procedures, one of which that is simply going to call the other.

USE tempdb;
GO

IF ( OBJECT_ID( 'dbo.TestProc', 'P' ) IS NULL )
BEGIN
    EXEC( 'CREATE PROCEDURE dbo.TestProc AS SET NOCOUNT ON;' );
END;
GO

ALTER PROCEDURE dbo.TestProc
AS BEGIN
    -- SET NOCOUNT ON; -- Excluded to reduce noise for the example;
    SELECT  TOP 100000 sao1.*
    INTO    #t_Test
    FROM    sys.all_objects sao1
    CROSS APPLY sys.all_objects sao2;

    SELECT  TOP 100000 *
    INTO    #t_Test2
    FROM    sys.all_objects sao1;

    DROP TABLE #t_Test;
    DROP TABLE #t_Test2;
END;
GO

IF ( OBJECT_ID( 'dbo.TestSuperProc', 'P' ) IS NULL )
BEGIN
    EXEC( 'CREATE PROCEDURE dbo.TestSuperProc AS SET NOCOUNT ON;' );
END;
GO

ALTER PROCEDURE dbo.TestSuperProc
AS BEGIN
    -- SET NOCOUNT ON; -- Excluded to reduce noise for the example;
    EXECUTE dbo.TestProc;
END;
GO

With these procedures in place, let's turn STATISTICS TIME on and run the operations performed by executing either procedure without actually calling one:

-- Just procedure contents;
SET NOCOUNT ON;
SET STATISTICS TIME ON;
SELECT  TOP 100000 sao1.*
INTO    #t_Test
FROM    sys.all_objects sao1
CROSS APPLY sys.all_objects sao2;

SELECT  TOP 100000 *
INTO    #t_Test2
FROM    sys.all_objects sao1;

DROP TABLE #t_Test;
DROP TABLE #t_Test2;
SET NOCOUNT OFF;
SET STATISTICS TIME OFF;

The output will look a little bit like this:

 SQL Server Execution Times:
   CPU time = 171 ms,  elapsed time = 305 ms.

 SQL Server Execution Times:
   CPU time = 47 ms,  elapsed time = 40 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 2 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 8 ms.

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

Matching this all up looks as would be expected:

SET NOCOUNT ON;
SET STATISTICS TIME ON;
-- Statistics will now start being reported;
SELECT  TOP 100000 sao1.*
INTO    #t_Test
FROM    sys.all_objects sao1
CROSS APPLY sys.all_objects sao2;

  SQL Server Execution Times:
     CPU time = 171 ms,  elapsed time = 305 ms.

SELECT  TOP 100000 *
INTO    #t_Test2
FROM    sys.all_objects sao1;

  SQL Server Execution Times:
     CPU time = 47 ms,  elapsed time = 40 ms.

DROP TABLE #t_Test;

  SQL Server Execution Times:
     CPU time = 0 ms,  elapsed time = 2 ms.

DROP TABLE #t_Test2;

  SQL Server Execution Times:
     CPU time = 0 ms,  elapsed time = 8 ms.

SET NOCOUNT OFF;

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

-- Statistics will now stop being reported;
SET STATISTICS TIME OFF;

Four statements ( SELECT, SELECT, DROP, DROP ) plus a SET NOCOUNT OFF; for a fifth statement, five messages from STATISTICS TIME.

However, if we were to capture the statistics by running the procedure:

-- Procedure statistics;
SET NOCOUNT ON;
SET STATISTICS TIME ON;
EXECUTE dbo.TestProc;
SET NOCOUNT OFF;
SET STATISTICS TIME OFF;

 SQL Server Execution Times:
   CPU time = 140 ms,  elapsed time = 488 ms.

 SQL Server Execution Times:
   CPU time = 47 ms,  elapsed time = 40 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

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

 SQL Server Execution Times:
   CPU time = 187 ms,  elapsed time = 529 ms.

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

Suddenly, a sixth message is returned - this is a message indicating the total statistics for the procedure itself, and is the sum of the statements contained within the procedure.  We can generate a seventh summary record in the results of STATISTICS TIME by executing the dbo.TestSuperProc we created:

-- Super Proc statistics;
SET NOCOUNT ON;
SET STATISTICS TIME ON;
EXECUTE dbo.TestSuperProc;
SET NOCOUNT OFF;
SET STATISTICS TIME OFF;

 SQL Server Execution Times:
   CPU time = 187 ms,  elapsed time = 259 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 42 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 4 ms.

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

 SQL Server Execution Times:
   CPU time = 187 ms,  elapsed time = 307 ms.

 SQL Server Execution Times:
   CPU time = 187 ms,  elapsed time = 307 ms.

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

In effect, your second last message ( likely before a SET NOCOUNT OFF as in my example ) is reporting the 31 seconds you are expecting - the total CPU and elapsed time for the calling procedure.  As you seem to be aware, CPU time is really where you want to pay attention for performance tuning, as it can be higher than the elapsed time if the query goes parallel, or lower if the procedure spends time sleeping on wait signals and I/O operations.


How to make sure that mysqldump command is working in command prompt I'm getting this error:

C:\Users\IT-Admin>mysqldump
'mysqldump' is not recognized as an internal or external command,
operable program or batch file.

So just as dwjv suggested, set the PATH OS system environmental variable to include the path on the OS where the mysqldump.exe exists (http://www.computerhope.com/issues/ch000549.htm); otherwise, change the directory in your command window to that path where it is and then run the command.

Add the full path (exampleC:\Program Files\MySQL\MySQL Server\bin\mysqldump\) where the mysqldump.exe is located to the environmental system variable of the OS where you're running this.
Example:C:\Users\IT-Admin>CD /D "C:\Program Files\MySQL\MySQL Server\bin\mysqldump" and then press enter to get this in the command window where you'd type 'mysqldump' as shown here C:\Program Files\MySQL\MySQL Server\bin\mysqldump>mysqldump and then press enter.
Furthermore, you could put the full path to the mysqldump.exe in with double quotes around it and then press enter as well e.g. "C:\Program Files\MySQL\MySQL Server\bin\mysqldump" and then press enter.

So there are three potential solutions for you!