Verification that my SSRS subscription was emailed to a valid address I would like to know if it is possible to verify that an SSRS subscription that was set up to send an email sent it to a real address.  That is, if I put in an email address that is not valid, can I find out that an undeliverable message was sent back from the destination server?

I tested this with an email address that does return an undeliverable message when I emailed to it from Outlook.  After the subscription ran, its status simply said Mail sent to abc@abc.com.

OUR SOLUTION: We set up a shared Outlook account with a generic email address.  Those of us who have access to create subscriptions have access to this email account.  We then modified the RSReportServer.config file on the ReportServer to send emails from this new generic address.  Now, any time a subscription is set up to send to an incorrect email address, we can see the delivery failure in the shared Outlook inbox.  Woo hoo!

The easiest way to do it is to set up a Reply email: address when you you are configuring database mail.  This will send the bounce back messages to that email account.

UPDATE:

The bounce-back for invalid email addresses will go to the FROM address.  The Reply-To address is used only by an email program when a user tries to reply to the message.  Change the FROM email address and see if you get the bounce-back your inbox.

If you get the bounce-back to the from address you can setup a mailbox to collect these messages.


Best Azure Service for Large no of SQLServer Databases We have a dedicated SQL Server Machine for a SAAS application with about 200+ databases, the server has E5-2620 with 64 GB RAM, Now we want to move all the databases to the Azure, so for looking into the options we can either go with a Virtual Machine like (DS13 v2) which can support all these DBs or can use the Azure SQL Elastic Pools like Standard with 400 eDTUs.

My Question is that in the Long Run and from management and scalability point of view which option is better, and also will the Standard Elastic Pool with 400 eDTU's can provide the same performance as the Virtual Machine?

One of the major concerns for us is the performance of the SQL DBs in Azure, as this is one of the reason to move the whole application to the Azure so that in the future as the no of users grow we can easily upgrade our instances to cater the increased load.

My Question is that in the Long Run and from management and scalability point of view which option is better

For "management and scalability" Azure SQL Database is clearly better.

Azure SQL Database was designed for this scenario.  You can spread out your databases across multiple pools, as needed, or move specific databases to non-pooled DTUs.  And Azure SQL Database has built-in patching, HA/DR, monitoring, and backup.

Azure Virtual Machines give you way more flexibility to design SQL Servers that exactly fit your needs, but that comes with a cost of complexity and management.

In both cases you should plan to spread your databases out over a few Elastic Pools / VMs, so you can scale out as well as up.


How to design a database for storing a sorted list? I am looking to store a sorted list inside a database. I want to perform the following operations efficiently.

Insert(x) - Insert record x into the table
Delete(x) - Delete record x from the table
Before(x,n) - Return the 'n' records preceding the record x in the
sorted list.
After(x,n) - Return the 'n' records succeeding the record x in the
sorted list.
First(n) - Return the first 'n' records from the sorted list.
Last(n) - Return the last 'n' records from the sorted list.
Compare(x,y) - Given two records x and y from the table, find if x >
y.

The simple method I could think of is to store some kind of a 'rank' attribute in the table and query by sorting on that attribute. But in this method inserting/modifying a record with a rank becomes a costly operation. Is there a better method?

Specifically, I am looking to implement the table using Amazon's SimpleDB. But a general answer for a relational database should also be helpful.

Update on load profile:

Since I am planning this for a web application, it depends on the number of users that use the app.

If there are 100k active users (super optimism :P), then my very approximate estimate per day would be

500k selects, 100k inserts and deletes, 500k updates

I would expect the table to grow up to 500k in total.

I am looking to optimize on the updates, insert and the Compare operations. The rank of the items will be constantly changing and I need to keep the table updated.

If rank isn't completely arbitrary but is instead derivable from some other property (e.g. name, player score, etc.) then take a good look at Joel's answer.

If it is an arbitrary property of your data, then that should be stored as a column in your table of records. Assuming Amazon's SimpleDB is similar to the typical RDBMS, you can then index this column and quickly satisfy all your above queries with the appropriate indexing strategy. This is normal for an RDBMS.

Given that you expect high insert and update activity, but also relatively high read activity, I recommend doing the following:

Cluster the table on the rank, especially if the vast majority of your queries are against rank. If not, or if choosing a clustering key is not available in SimpleDB, then just create an index with rank as the leading column. This would satisfy queries 3-6.
An index on the record first and then rank (or, in the SQL Server world, just record and INCLUDE-ing rank, or just record if you've clustered on rank) would satisfy query 7.
Operations 1 and 2 can be optimized by spacing out your data appropriately (i.e. setting the FILLFACTOR in SQL Server). This is especially important if you cluster on rank.
As you insert or update ranks, maintain as much of a gap between rank numbers as possible to minimize that possibility that you will need to re-rank an existing record to accommodate a rank insert or update. For example, if you rank your records in steps of 1000 you leave enough room for about half that many changes and inserts with minimal chance you'll need to re-rank a record not directly involved in those changes.
Every night re-rank all records to reset the rank gaps between them.
You can tune the frequency of the mass re-rankings as well as the rank gap size to accommodate your expected number of inserts or updates relative to the number of existing records. So if you have 100K records and expect your inserts and updates to be 10% of that, leave enough room for 10K new ranks and re-rank nightly.
Re-ranking 500K records is an expensive operation, but done once a day or week off-hours should be fine for a database like that.  This off-hours mass re-ranking to maintain the rank gaps is what saves you having to re-rank many records for each rank update or insert during your normal and peak hours.

If you expect 100K+ reads on a 100K+ sized table I do not recommend using the linked list approach.  It will not scale well to those sizes.


Where to place derived attributes I have an assignment which requires me to design a database.

I have 3 entity types WARD, BED, and BED_ASSIGMENT (which is the "composite" entity type between BED and WARD).

The BED primary key consists of the attributes bed_id and ward_id (each ward has bed number 1 for example, so I need ward_id to properly identify it)

My question is:

I have two attributes: total_bed_ count and total_vacant_bed_count. In which entity type should I place them?

Don't store redundant information. Both total_bed_count and total_vacant_bed_count can be queried.

total_bed_count would be something like select count(*) from bed, while total_vacant_bed_count could be queried if you know which beds don't have patients assigned to them, maybe something like select count(*) from bed where patient is null.


Why the job's next_run_time is wrong? I got an issue regarding job stats. So I have a job with the following schedule: Occurs every day every 5 minute(s) between 12:00:00 AM and 11:59:59 PM. Schedule will be used starting on 12/13/2017.

in 11:27 AM 2019-10-20 I ran two scripts to get last_run_time and next_run_time, below are the scripts and their corresponding results:

USE msdb
GO
SELECT J.Name                     AS 'Job Name'
         ,'Job Enabled' =
          CASE J.Enabled
                 WHEN 1 THEN 'Yes'
                 WHEN 0 THEN 'No'
          END
         ,STUFF(
          STUFF(CAST([active_start_date] AS VARCHAR(8)), 5, 0, '-')
          , 8, 0, '-')                   AS 'Job Schedule Start Date'
         ,STUFF(
          STUFF(CAST([active_end_date] AS VARCHAR(8)), 5, 0, '-')
          , 8, 0, '-')                   AS 'Job Schedule End Date'
         ,'Job Frequency' =
          CASE S.freq_type
                 WHEN 1 THEN 'Once'
                 WHEN 4 THEN 'Daily'
                 WHEN 8 THEN 'Weekly'
                 WHEN 16 THEN 'Monthly'
                 WHEN 32 THEN 'Monthly relative'
                 WHEN 64 THEN 'When SQLServer Agent starts'
          END
         ,'Last Run Date' = CONVERT(DATETIME, RTRIM(LASTRUN.run_date) + ' '
          + STUFF(STUFF(REPLACE(STR(RTRIM(LASTRUN.run_time), 6, 0),
          ' ', '0'), 3, 0, ':'), 6, 0, ':'))
         , 'Last Run Status' =
          CASE
                 WHEN LASTRUN.run_status = 0 THEN 'Failed'
                 WHEN LASTRUN.run_status = 1 THEN 'Succeeded'
                 WHEN LASTRUN.run_status = 2 THEN 'Retry'
                 WHEN LASTRUN.run_status = 3 THEN 'Cancelled'
                 ELSE 'Unknown'
          END
         ,'Last Run Message' = LASTRUN.message
FROM dbo.sysjobs J
LEFT OUTER JOIN dbo.sysjobschedules JS
       ON J.job_id = JS.job_id
LEFT OUTER JOIN dbo.sysschedules S
       ON JS.schedule_id = S.schedule_id
LEFT OUTER JOIN (SELECT
              job_id
          ,MAX(run_duration) AS RUN_DURATION
       FROM dbo.sysjobhistory
       GROUP BY job_id) MAXDUR
       ON J.job_id = MAXDUR.job_id
LEFT OUTER JOIN (SELECT
              J1.job_id
          ,J1.RUN_DURATION
          ,J1.run_date
          ,J1.run_time
          ,J1.message
          ,J1.run_status
       FROM dbo.sysjobhistory J1
       WHERE instance_id = (SELECT
                     MAX(instance_id)
              FROM dbo.sysjobhistory J2
              WHERE J2.job_id = J1.job_id)) LASTRUN
       ON J.job_id = LASTRUN.job_id
ORDER BY 'Job Name'

use msdb
go
Select [job name] = jb.name
    , [schedule name] = sc.name
    , [next run date] = js.next_run_date
    , [next run time] = js.next_run_time
From dbo.sysjobs as jb
Inner Join dbo.sysjobschedules as js on js.job_id = jb.job_id
Inner Join dbo.sysschedules as sc on js.schedule_id = sc.schedule_id

So, according to the pictures next_run_date should be 11:30 AM instead of 11:15 AM. Have you ever faced with this kind of phenomenon? Could you please help me understand this?

The docs on sysjobschedules reveal that the data inside refreshes every 20 minutes.

  NOTE: The sysjobschedules table refreshes every 20 minutes, which may
  affect the values returned by the sp_help_jobschedule stored
  procedure.

Which is why your next_run_date & next_run_time columns could show out of date data.

For a much longer answer & deep dive. Check out this answer by Aaron Bertrand

He talks about using dbo.sysjobactivity.

You could use the next_scheduled_run_date, which is a datetime column from this dmv.

Example query:

SELECT sj.Name,
next_scheduled_run_date
FROM msdb.dbo.sysjobs sj
JOIN msdb.dbo.sysjobactivity sja
ON sj.job_id = sja.job_id
WHERE session_id = (
    SELECT MAX(session_id) FROM msdb.dbo.sysjobactivity);

With the max(session) used because the agent starts a new session each time it restarts.

This results in

Name                            next_scheduled_run_date
LSAlert_My_machine              2019-10-20 11:34:00.000
LSRestore_My_machine_Test42     2019-10-20 11:45:00.000
LSCopy_My_machine_Test42        2019-10-20 11:45:00.000
syspolicy_purge_history         2019-10-21 02:00:00.000

SSMS

When tracing what SSMS is doing to get the job info when calling job activity monitor. the last_run_date & last_run_time are taken from the xp_sqlagent_enum_jobs procedure, in a cursor wise manner.

You could get the job info & place a cursor over it by using this procedure.

An example of getting the info for one job:

DECLARE @job_id uniqueidentifier
DECLARE @job_owner varchar(255)
SELECT @job_owner = SUSER_SNAME()

SELECT @job_id=job_id FROM msdb..sysjobs
WHERE name = 'LSAlert_My_machine'
    EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, @job_owner, @job_id;

2nd until 5th columns returned by the procedure:

Last Run Date   Last Run Time   Next Run Date   Next Run Time
20191020        114400          20191020        114600

If you wish to run the procedure for all jobs:

DECLARE @job_id uniqueidentifier, @job_name varchar(255)
DECLARE @job_owner varchar(255)
SELECT @job_owner = SUSER_SNAME()
DECLARE @xp_results TABLE(Job_name varchar(255),Job_Id varbinary(16),last_run_date varchar(10),last_run_time varchar(10),next_run_date varchar(10), next_run_time varchar(10), next_run_schedule_id int, requested_to_run bit,request_source int, request_source_id int, running bit, current_step int, current_retry_attempt int, [State] int)

DECLARE C CURSOR FAST_FORWARD READ_ONLY FOR
SELECT [name],job_id FROM msdb..sysjobs
OPEN C
FETCH NEXT FROM C INTO @job_name,@job_id
WHILE @@FETCH_STATUS = 0
BEGIN

INSERT INTO @xp_results(Job_Id,last_run_date ,last_run_time ,next_run_date , next_run_time , next_run_schedule_id , requested_to_run ,request_source , request_source_id , running , current_step , current_retry_attempt , [State] )
    EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, @job_owner, @job_id;

UPDATE @xp_results
SET Job_name = @job_name
WHERE Job_Id = @job_id;

FETCH NEXT FROM C INTO @job_name,@job_id
END
CLOSE C
DEALLOCATE C
SELECT Job_name,last_run_date,last_run_time,next_run_date,next_run_time
FROM @xp_results;

Example output

Job_name                        last_run_date   last_run_time   next_run_date   next_run_time
LSAlert_My_machine              20191020        115800          20191020        120000
LSCopy_My_machine_Test42        20191020        114500          20191020        120000
LSRestore_My_machine_Test42     20191020        114500          20191020        120000


what does posix/Europe/Paris stand for? If you execute something like the following query on Postgres:

SELECT * FROM pg_timezone_names() WHERE name LIKE '%Rome%';

you get back a bunch of "weird" timezones:

postgres=# SELECT * FROM pg_timezone_names() WHERE name LIKE '%Rome%';
       name        | abbrev | utc_offset | is_dst
-------------------+--------+------------+--------
 Europe/Rome       | CET    | 01:00:00   | f
 posix/Europe/Rome | CET    | 01:00:00   | f
(2 rows)

I'm wondering why posix/ exists, what they stand for (they are not IANA official timezones right?) and when they are used. They look like a blend of POSIX standard and Olson DB, but posix should have -1 as utc_offset, right? I'm considering using the pg_timezone_names() function for giving the users the chance of choosing their own timezone, but I can't understand the meaning of this specific type of timezone in Postgres.

Generally, you shouldn't be using the Posix/ or Etc/ timezones if they're on your system. This isn't a PostgreSQL thing, it's your distributions libc database (sometimes packaged as tzdata/zoneinfo) for the internal timezone functions. Most of that POSIX stuff is nasty and old. PostgreSQL has a document on it which mentions it.

I guess to answer the question specifically,

  Two different versions are provided: - The "posix" version is based on the Coordinated Universal Time (UTC). - The "right" version is based on the International Atomic Time (TAI), and it includes the leap seconds.

There really is no reason that I know of not to use the IANA names.


How to execute multiple postgres script from command prompt I have 100 script files which has insert scripts for the table. How can I execute these?

Scrip1.sql , script2.sql, script3.sql .... script100.sql

To execute single file, I am using the below command. But how can I execute all files?

psql -d DBName -p 5444 -U schemaname -f P_Scrip1.sql  2> err.log

If you don't need to provide a password, it's easy:

for FILE in `ls -1 *.sql`
do
  psql -d DBName -p 5444 -U schemaname -f $FILE  2>$FILE.err.log
done

If you need to provide a password, concatenate all of the files together, then run psql:

for FILE in `ls -1 *.sql`
do
  cat $FILE >> /tmp/allfiles.sql
done

psql -d DBName -p 5444 -U schemaname -f /tmp/allfiles.sql 2>allfiles.err.log

rm /tmp/allfiles.sql


pg_relation_size does not show any difference after VACUUM ANALYZE I wrote a script to backup a postgresql using COPY and then delete those records. I have used pg_relation_size before and after delete.

pg_relation_size
DELETE
VACUUM ANALYZE
pg_relation_size

But both pg_relation_size shows same results. Does this mean VACUUM ANALYZE was not helpful?

VACUUM does not necessarily make unused space available to the filesystem, it merely makes the blocks re-usable for further INSERTs (or UPDATEs).

If I'm not mistaken the only way to also actively reduce the size on the file system would be a VACUUM FULL, but beware that needs an exclusive lock on the table(s).

Do you expect the table to not get any new rows or updates? Because if it will get new rows, then it doesn't really make sense to try to shrink the files phyiscally now if they are going to be increased again later due to new rows arriving.


Oracle Equivalent of the following SQL Server syntax I'm looking for the Oracle equivalent of the following SQL Server syntax:

select Cast(0x41424344 as varchar(max))

In SQL Server, it selects  'ABCD'.

0x41424344 is the hex of 'ABCD'


ERROR 1005 (HY000): Can't create table 'phact_greenland.cache' (errno: 121) I get an error when I try to create a table:

  ERROR 1005 (HY000): Can't create table 'phact_greenland.cache' (errno: 121)

CREATE TABLE IF NOT EXISTS `cache`
(
    `cid` varchar(255) NOT NULL DEFAULT ''
        COMMENT 'Primary Key: Unique cache ID.'
    , `data` longblob
        COMMENT 'A collection of data to cache.'
    , `expire` int(11) NOT NULL DEFAULT '0'
        COMMENT 'A Unix timestamp indicating when the cache entry
                should expire, or 0 for never.'
    , `created` int(11) NOT NULL DEFAULT '0'
        COMMENT 'A Unix timestamp indicating when the cache entry was created.'
    , `serialized` smallint(6) NOT NULL DEFAULT '0'
        COMMENT 'A flag to indicate whether content is serialized (1) or not (0).'
    , PRIMARY KEY (`cid`)
    , KEY `expire` (`expire`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
    COMMENT='Generic cache table for caching things not separated out...';

How do i solve it ?

You get Error 121 when you try to create a table that contains a constraint with the same name as a constraint defined elsewhere in your database. You can avoid this issue by naming the constraints yourself in the creation script. More information can be found below:

InnoDb Error Codes

http://dev.mysql.com/doc/refman/5.5/en/innodb-error-codes.html

Create Table Statement

http://dev.mysql.com/doc/refman/5.5/en/create-table.html

I hope this helps you.


Connection to PostgreSQL disconnects every hour regardless of activity I'm monitoring my PostgreSQL clients using a periodic netstat -an | grep 5432 | grep EST to check the ESTABLISHED status of the connection every 5 minutes. I have noticed that all of the connections become TIME_WAIT after around minutes, and then a new connection is re-opened (the client port changes), forming a new ESTABLISHED connection. This behavior persists regardless of the status or activity of the postgres client.

Any thoughts on what might be causing this recurring reconnection on the clients? I've tried setting the tcp_keepalives_idle to no avail.

Turns out, it uses a connection pool by HikariCP. The default behavior of the maxLifetime and idleTimeout configurations are causing the reconnection. Setting both configurations to 0 disables them and removes the reconnecting issue.

Shoutout to this comment for helping me understand my problem.


Indexing strategy when using the between operator SQL Server 2008 I have a large table ~25 million rows with the structure
CREATE TABLE [dbo].[rx](
            [pat_id] [int] NOT NULL,
            [fill_Date] [date] NOT NULL,
            [script_End_Date]  AS (dateadd(day,[dayssup],[filldate])) persisted,
            [drug_Name] [varchar](50) NULL,
            [days_Sup] [int] NOT NULL,
            [quantity] [float] NOT NULL,
            [drug_Class] [char](3) NOT  NULL,
            CHECK(fill_Date <=script_End_Date
PRIMARY KEY NONCLUSTERED
(
          [clmid]
)

create clustered index ix_rx_temporal on rx(fill_date asc, script_end_date asc, pat_id asc)

The primary key on this table is never queried on.  This table by far is used most often with queries that involve date ranges.  I have a calendar table with the structure
CREATE TABLE [dbo].[Calendar](
             [cal_date] [date] PRIMARY KEY,
[Year] AS YEAR(cal_date) PERSISTED,
[Month] AS MONTH(cal_date) PERSISTED,
[Day] AS DAY(cal_date) PERSISTED,
             [julian_seq] AS 1+DATEDIFF(DD, CONVERT(DATE, CONVERT(varchar,YEAR(cal_date))+'0101'),cal_date));

The query that I'm trying to speed up is:
;WITH x
     AS (
        --join finds the amount of distinct drugs that a person was prescribed on a given day
        SELECT rx.pat_id,
               c.cal_date,
               Count(DISTINCT rx.drug_name) AS distinctDrugs
         FROM   rx,
                calendar AS c
         WHERE  c.cal_date BETWEEN rx.fill_date AND rx.script_end_date
         GROUP  BY rx.pat_id,
                   c.cal_date),
     y
     AS (
        --makes a sequence so that contiguous dates can be grouped together as a date range
        SELECT x.pat_id,
               x.distinctdrugs,
               c2.julian_seq- Row_number()
                         OVER(
                           partition BY x.pat_id, distinctdrugs
                           ORDER BY x.cal_date) AS rn,
               x.cal_date
         FROM   x,
                calendar AS c2
         WHERE  c2.cal_date = x.cal_date)
--finds the max and minimum dates which a person was taking X amoung of drugs
SELECT y.pat_id,
       Min(y.cal_date)    AS min_overlap,
       Max(y.cal_date)    AS max_overlap,
       Min(distinctdrugs) AS distinctDrugs
FROM   y
GROUP  BY y.pat_id,
          rn

I tried a couple of the following indexes
CREATE NONCLUSTERED INDEX [ix_rx2] ON [dbo].[rx]
(
[drug_name] ASC,
[drug_class] ASC,
[fill_date] ASC,
[script_end_date] ASC
)

and
CREATE NONCLUSTERED INDEX [ix_rx3] ON [dbo].[rx]
(
[fill_date] ASC,
[script_end_date] ASC
)
INCLUDE ( [pat_id],
[drug_class],
[drug_name])

Whenever I check out the execution plan, none of the indexes on the rx table are being used.  There are other aspects of the execution plan, but the bulk of it  looks like

I even tried removing the current clustered index and just using (fill_date,admi_date) and yet I still can't find a way to keep from running into that hash match operator in the execution plan that is taking up the vast majority of the resources for this query.  The primary key on the rx table is never queried on, and I'll primarily be doing queries involving date ranges with this table.  The indexes on the tables have nominal fragmentation and the densities of the indexes are all very low.  What can I do do speed up this query, or am I going to be limited by hardware?

This is not a complete answer, I do not have the time now, so let me just share a few thoughts. The complete answer would be huge, and I am not sure you want to know the details.

I have been working with various temporal queries for several years already, and learned a lot in the process. As such, I would rather not have to optimize your query in my production system. I would try very hard to avoid solving it with T-SQL. It is a complex problem. Itzik Ben-Gan has written about "gaps and islands" several times, including a chapter in his latest book on OLAP functions. Your problem is a variation of gaps and islands.

First, I would consider reading all the data to the client and solve it there using loops. I know, it requires sending data over the network, but fast loops in Java/C++/C# work very well for me most of the time. For instance, once I was struggling with a query involving time series and temporal data. When I moved most of the logic to the client, the C# solution was several times shorter and it ran 20,000 times faster. That's not a typo - twenty thousand times faster.

There is another problem with solving such problems in T-SQL - your performance may be unstable. If a query is complex, all of a sudden the optimizer can choose another plan and it will run many times slower, and we have to optimize it again.

Alternatively, I would consider storing data differently. Right now I see two possible approaches.

First, instead of storing intervals we could use this table:

ClientId,
PrescriptionId,
DrugId,
Date

We can use trusted constraints to make sure each PrescriptionId covers a range of dates without gaps and overlaps, so that one interval stores as one unbroken sequence of dates.

Note: I know you are using DISTINCT in your first subquery, so you are assuming that one person can take one drug on one day from more than one prescription. I am not mkaing this assumption, for simplicity. Are you sure it is a correct assumption? If yes, we will have to change the design.

Once we have this table, we can essentially materialize your first subquery as an indexed view:

SELECT ClientId,Date,COUNT_BIG(*) AS DistinctDrugs
GROUP BY ClientId,
Date

THat done, you can use your second subquery to group data points into interval, or just run it on a client, where is can be solved as one trivial loop.

Second approach: instead of intervals I would store sequence of events. there woudl be two kinds of events: interval start and interval end. With every I would store a running total, the number of open events after this event has happened. Essentially this running total is the number of prescriptions active after the event has happened.

As in the previous approach, much of the data you calculate on the fly every time you run your query is pre-calculated in this table. We can use trusted constraints to ensure the integrity of precalculated data.

I can describe it in more detail later if you are interested.


Is pg12 cache used with two SELECT on same table but different fields Figure a table with fields a, b and c
Figure two requests on this table :
SELECT a, b from table

SELECT b, c from table

Is postgres able to reuse cache for the second request?

The cache stores blocks read from from disk. A single block contains one or more rows from a table.
As both queries read the same data, they request the same blocks. So yes, the second query will be reading the blocks from the cache.


How to control execution plans in SQL Server I've faced a problem with SQL Server generating poor execution plans.
I have 3 databases with the same structure and the same set of procedures.

My query looks like this:

select
d.Id as DirectoryId,
d.MeetingId  as MeetingId,
--...
from dbo.MeetingsDirectories d
left join dbo.Meetings m ON m.Id = d.MeetingId
left join (select * from dbo.fnLatestDirectoryData()) dat on d.Id = dat.MeetingDirectoryId
...

The difference is

The first db cached following execution plan. Which is terrible because basically it executes fnLatestDirectoryData as many times as there are rows after I joined dbo.Meetings with dbo.MeetingsDirectories. My evidence is that it number of executions corresponds with the number of actual rows that come into final nested loop in upper left corner. But execution of fnLatestDirectoryData is way too expensive and if number of rows is ~3k it starts to lag (about 32sec)

I just recompiled procedure by altering it. And instead of doing clustered index scan it inserted table spool right before going to nested loops. I think it just multiplied results of fnLatestDirectoryData and cached them. But it's size is about 1000x1000 which worries me. Nevertheless the execution time dropped to 8sec.

In the the third case I added hash hint to left join and observed that my function called once and execution time dropped even further. I also tried adding merge hint but they both work relatively the same time.

left hash join (select * from dbo.fnLatestDirectoryData())

My questions are:

Can I prevent degradation of execution plans from second stage to first, so I always have fixed execution plan for each stored procedure? I don't have enough evidences, but I feel like sometimes SQL Server decides to recompile execution plans, and if they are bad my app performance drops significantly.
Is the third option is a right way to grantee that I would have only one execution of fnLatestDirectoryData?
Can I reliably fix the order of joins? I see joins happen in different order, so if I do something like group them in pair (select (select from tableA join tableB) join tableC) it will actually help.

1) Can I prevent degradation of execution plans from second stage to
  first, so I always have fixed execution plan for each stored
  procedure? I dont have enough evidences, but I feel like sometimes sql
  server decides to recompile execution plans, and if they are bad my
  app performance drops significantly.

The main things to watch out for here are statistics getting stale and parameter sniffing. Without going into too much depth here are some ways to combat this.

Statistics out of date

Enabling trace flag 2371 (for databases below compatibility level 130) as to lower the modification threshold when statistics are updated.
Updating your statistics daily / weekly
Changing the sample rate of your stat updates (and possibly persisting these rates Starting from SQL Server 2016 SP1 CU4 / SQL Server 2017 CU1)

Parameter sniffing

Sometimes SQL Server needs to create a new plan due to statistic updates, the plan was removed from cache, etc..

Sometimes this plan is not optimal for other parameters.

One solution for this is adding the OPTION(RECOMPILE) hint to your query as to increase cpu usage by compiling the plan on each execution, but getting a better plan for the parameters specified. This is mostly dependent on the amount of times the query is executed.

  2) Is the third option is a right way to grantee that I would have
  only one execution of fnLatestDirectoryData?

The executions

The executions are corresponding to normal nested loop join operator behaviour.
Finding matches by using a row from the outer input, these executions should match the amount of rows in the outer input (ignoring special cases like nested loop prefetching)

This does not mean that the function is called these 269 times, as the inline table valued function is expanded into the query plan, behaviour you would also see in views.

Reliability of the hint

The hint makes the optimizer create a plan with a HASH JOIN, and that is the only guarantee.

  3) Can I reliably fix the order of joins? I see joins happen in
  different order, so if I do something like group them in pair (select
  (select from tableA join tableB) join tableC) it will actually help.

The brackets

Splitting up the joins with brackets should not do anything as the optimizer removes them and optimizes the query the same way.

Forcing the order

If you really  want to force the order in which the joins are specified, OPTION(FORCE ORDER) can be used or SET FORCEPLAN ON.

Splitting up the query

Another method could be inserting the result of a part of the query into a temporary table, and joining the rest of the query to this temp table.

More information (as of time of  writing)

Without more information about what is going inside the function, and the query plan it is hard to say. An index or query adaption could very well resolve your issue.


Mistake in a problem solution I am trying to solve this problem from hackerrank
this far I've written this solution

select Start_Date, min(End_Date)
from
   (select Start_Date from projects where Start_Date not in (select End_Date from projects)),
   (select End_Date from projects where End_Date not in (select Start_Date from projects))
where Start_Date < End_Date
group by Start_Date
order by datediff(day, min(End_Date), Start_Date) desc

But it shows that this answer is wrong though when I compare I get the results I planned to get. I'd much appreciate a little help to find out where I am wrong.

-- SET A MARK (RST=1) EVERY TIME PREVIOUS END_DATE IS NOT EQUAL START_DATE
WITH CT AS
(
    SELECT
        TASK_ID, START_DATE, END_DATE,
        CASE WHEN (COALESCE(LAG(END_DATE) OVER (ORDER BY START_DATE), END_DATE) = START_DATE)
             THEN 0 ELSE 1 END RST
    FROM
        TASK
)
-- SUM(RST) TO SET A GROUP (1, 2, 3, ...)
, CT2 AS
(
    SELECT
        TASK_ID, START_DATE, END_DATE,
        SUM(RST) OVER (ORDER BY START_DATE) AS GRP
    FROM
        CT
)
SELECT
    MIN(START_DATE) AS START_DATE,
    MAX(END_DATE) AS END_DATE
FROM
    CT2
GROUP BY
    GRP;

START_DATE          | END_DATE
:------------------ | :------------------
01/10/2015 00:00:00 | 04/10/2015 00:00:00
13/10/2015 00:00:00 | 15/10/2015 00:00:00
28/10/2015 00:00:00 | 29/10/2015 00:00:00
30/10/2015 00:00:00 | 31/10/2015 00:00:00

db<>fiddle here


How to check values for records from different rows I have a view I want to select those records whose (Letter, pro, record, and education) column values are not null.

For details, I want to check record MI13900454 that his (Letter, pro, record, and education) should not be null. Note: if we select ... from xxx where record_id is not null and letter_id is not null ..... it is not correct because values are not in same row - they are in different rows and columns.

Plaintext table:

|        ID | LETTER_ID | MEDICAL_ID | SICKNESS_ID |  PRO_ID | EDU_ID | TRAINING_ID | RECORD_ID |    NID |
|-----------|-----------|------------|-------------|---------|--------|-------------|-----------|--------|
| MI1390454 |           |            |             |         |        |       47584 |           |        |
| MI1390454 |           |            |             | 4673735 |        |             |           |        |
| MI1390033 |           |            |             |  105419 |        |             |           |        |
| MI1390033 |           |            |             |         |        |             |           |        |
| MI1390033 |           |            |             |         |        |             |           |        |
| MI1390033 |    299080 |            |             |         |        |             |           |        |
| MI1390033 |           |            |             |         | 659652 |             |           |        |
| MI1390033 |           |     309609 |             |         |        |             |           |        |
| MI1390033 |           |            |             |         |        |             |           |        |
| MI1390033 |           |            |             |         |        |             |           |        |
| MI1390033 |           |            |             |         | 659650 |             |           |        |
| MI1390033 |           |            |             |         | 659654 |             |           |        |
| MI1390033 |           |            |             |         |        |             |           | 161077 |
| MI1390033 |           |     309608 |             |         |        |             |           |        |
| MI1390033 |           |            |             |         | 659651 |             |           |        |
| MI1390454 |           |            |             |         |        |             |    154745 |        |
| MI1390033 |           |            |             |         | 659653 |             |           |        |
| MI1390033 |           |            |             |         |        |             |           |        |
| MI1390033 |           |            |             |         |        |             |           |        |
| MI1390033 |           |            |             |         |        |             |     94272 |        |
| MI1390033 |           |            |             |         |        |       44917 |           |        |
| MI1390033 |           |            |             |  105418 |        |             |           |        |

I guess that what you would like is not exactly what you are asking for... so, correct me if I missinterpreted you.

Assuming this is your data (I represent it with a table, but it can be a view):

 CREATE TABLE v
 (
     ID varchar(9),
     LETTER_ID int,
     MEDICAL_ID int,
     SICKNESS_ID int,
     PRO_ID int,
     EDU_ID int,
     TRAINING_ID int,
     RECORD_ID int,
     NID int
 ) ;

 INSERT INTO v
     (ID,         LETTER_ID, MEDICAL_ID, SICKNESS_ID, PRO_ID, EDU_ID, TRAINING_ID, RECORD_ID, NID)
 VALUES
     ('MI1390454', NULL,     NULL, NULL, NULL, NULL, 47584, NULL, NULL),
     ('MI1390454', NULL,     NULL, NULL, 4673735, NULL, NULL, NULL, NULL),
     ('MI1390033', NULL,     NULL, NULL, 105419, NULL, NULL, NULL, NULL),
     ('MI1390033', NULL,     NULL, NULL, NULL, NULL, NULL, NULL, NULL),
     ('MI1390033', NULL,     NULL, NULL, NULL, NULL, NULL, NULL, NULL),
     ('MI1390033', 299080,   NULL, NULL, NULL, NULL, NULL, NULL, NULL),
     ('MI1390033', NULL,     NULL, NULL, NULL, 659652, NULL, NULL, NULL),
     ('MI1390033', NULL,     309609, NULL, NULL, NULL, NULL, NULL, NULL),
     ('MI1390033', NULL,     NULL, NULL, NULL, NULL, NULL, NULL, NULL),
     ('MI1390033', NULL,     NULL, NULL, NULL, NULL, NULL, NULL, NULL),
     ('MI1390033', NULL,     NULL, NULL, NULL, 659650, NULL, NULL, NULL),
     ('MI1390033', NULL,     NULL, NULL, NULL, 659654, NULL, NULL, NULL),
     ('MI1390033', NULL,     NULL, NULL, NULL, NULL, NULL, NULL, 161077),
     ('MI1390033', NULL,     309608, NULL, NULL, NULL, NULL, NULL, NULL),
     ('MI1390033', NULL,     NULL, NULL, NULL, 659651, NULL, NULL, NULL),
     ('MI1390454', NULL,     NULL, NULL, NULL, NULL, NULL, 154745, NULL),
     ('MI1390033', NULL,     NULL, NULL, NULL, 659653, NULL, NULL, NULL),
     ('MI1390033', NULL,     NULL, NULL, NULL, NULL, NULL, NULL, NULL),
     ('MI1390033', NULL,     NULL, NULL, NULL, NULL, NULL, NULL, NULL),
     ('MI1390033', NULL,     NULL, NULL, NULL, NULL, NULL, 94272, NULL),
     ('MI1390033', NULL,     NULL, NULL, NULL, NULL, 44917, NULL, NULL),
     ('MI1390033', NULL,     NULL, NULL, 105418, NULL, NULL, NULL, NULL)
 ;

What you actually want is to flatten these data first.

That is, GROUP all rows with the same ID, and take the only non null value of the other columns (or, if there are more than one, the one value that actually is of interest to you).

I have chosen to do this using the MAX aggregate function. You may want another one, such as MIN, or a MIN || MAX or something like a string_agg or group_concat (which MS-SQL Server doesn't yet provide):

 SELECT
     ID,
     max(LETTER_ID) AS LETTER_ID,
     max(MEDICAL_ID) AS MEDICAL_ID,
     max(SICKNESS_ID) AS SICKNESS_ID,
     max(PRO_ID) AS PRO_ID,
     max(EDU_ID) AS EDU_ID,
     max(TRAINING_ID) AS TRAINING_ID,
     max(RECORD_ID) AS RECORD_ID,
     max(NID) AS NID
 FROM
     v
 GROUP BY
     ID ;

This would be the intermediate result:

 ID        | LETTER_ID | MEDICAL_ID | SICKNESS_ID |  PRO_ID | EDU_ID | TRAINING_ID | RECORD_ID |    NID
 :-------- | --------: | ---------: | ----------: | ------: | -----: | ----------: | --------: | -----:
 MI1390033 |    299080 |     309609 |        null |  105419 | 659654 |       44917 |     94272 | 161077
 MI1390454 |      null | null       |        null | 4673735 |   null |       47584 |    154745 |   null

At this point, you can select only the rows with (Letter, pro, record, education) not being null:

 SELECT
    *
 FROM
    (SELECT
         ID,
         max(LETTER_ID) AS LETTER_ID,
         max(MEDICAL_ID) AS MEDICAL_ID,
         max(SICKNESS_ID) AS SICKNESS_ID,
         max(PRO_ID) AS PRO_ID,
         max(EDU_ID) AS EDU_ID,
         max(TRAINING_ID) AS TRAINING_ID,
         max(RECORD_ID) AS RECORD_ID,
         max(NID) AS NID
     FROM
         v
     GROUP BY
         ID
    ) AS flatten
 WHERE
        LETTER_ID is not null
    and PRO_ID    is not null
    and RECORD_ID is not null
    and EDU_ID    is not null ;

You will get:

 ID        | LETTER_ID | MEDICAL_ID | SICKNESS_ID | PRO_ID | EDU_ID | TRAINING_ID | RECORD_ID |    NID
 :-------- | --------: | ---------: | ----------: | -----: | -----: | ----------: | --------: | -----:
 MI1390033 |    299080 |     309609 |        null | 105419 | 659654 |       44917 |     94272 | 161077

dbfiddle here

If you're only interested in the ID value, but not the rest, you can use a different approach:

SELECT DISTINCT
    ID
FROM
    v AS v0
WHERE
        EXISTS (SELECT * FROM v AS v1 WHERE v1.ID = v0.ID AND LETTER_ID is not null)
   AND  EXISTS (SELECT * FROM v AS v1 WHERE v1.ID = v0.ID AND PRO_ID is not null)
   AND  EXISTS (SELECT * FROM v AS v1 WHERE v1.ID = v0.ID AND RECORD_ID is not null)
   AND  EXISTS (SELECT * FROM v AS v1 WHERE v1.ID = v0.ID AND EDU_ID is not null) ;

dbfiddle here


How to script alerts in SQL Server? after trying out this script: Script out all alerts By Carolyn Richardson

or even the traditional way of ssms -> right click -> script alert as

none of these methods are fully comprehensive because they miss out information that is inside the alert, like the job it calls, etc.

for example I have been Setting Up Failover Alerts

and I get many actions set up as you can see on the script below:

-- 1480 - AG Role Change (failover)
EXEC msdb.dbo.sp_add_alert
        @name = N'AG Role Change',
        @message_id = 1480,
    @severity = 0,
    @enabled = 1,
    @delay_between_responses = 0,
    @include_event_description_in = 1;
GO
EXEC msdb.dbo.sp_add_notification
        @alert_name = N'AG Role Change',
        @operator_name = N'<OperatorName,sysname,Alerts>',
        @notification_method = <Notification,INT,1>;
GO

is there a way to script all these info along with the alert?

I was able to get all the properties scripted out.

Open SSMS
Right click on the job
Click properties
Use script button on the top

I created a test job with many features.

This is what I get when I scripted out.

USE [msdb]
GO
EXEC msdb.dbo.sp_update_alert @name=N'Blocked Tasks > 5',
        @message_id=0,
        @severity=0,
        @enabled=1,
        @delay_between_responses=183,
        @include_event_description_in=3,
        @database_name=N'',
        @notification_message=N'This is a test',
        @event_description_keyword=N'',
        @performance_condition=N'Workload Group Stats|Blocked tasks|default|>|5',
        @wmi_namespace=N'',
        @wmi_query=N'',
        @job_id=N'b97f9d4d-a22d-4745-9d19-9fadd168b4b1'
GO
EXEC msdb.dbo.sp_update_notification
      @alert_name=N'Blocked Tasks > 5',
      @operator_name=N'sysadmin',
      @notification_method = 1
GO

I ran an extended event trace to see what get called. I changed the select statement to select * to see all the values.

EXEC Sp_executesql
N'  create table #tmp_sp_help_alert  (id int null, name nvarchar(128) null,  event_source nvarchar(100) null, event_category_id int null, event_id int null, message_id int null,  severity int null, enabled tinyint null, delay_between_responses int null, last_occurrence_date int null, last_occurrence_time int null, last_response_date int null, last_response_time int null, notification_message nvarchar(512) null, include_event_description tinyint null, database_name nvarchar(128) null, event_description_keyword nvarchar(100) null, occurrence_count int null, count_reset_date int null, count_reset_time int null, job_id uniqueidentifier null, job_name nvarchar(128) null, has_notification int null, flags int null, performance_condition nvarchar(512) null, category_name nvarchar(128) null,  wmi_namespace nvarchar(max) null,  wmi_query nvarchar(max) null, type int null)   insert into #tmp_sp_help_alert exec msdb.dbo.sp_help_alert

SELECT * FROM #tmp_sp_help_alert AS tsha WHERE (tsha.name=@_msparam_0)   drop
  table #tmp_sp_help_alert',
    N'@_msparam_0 nvarchar(4000)',
    @_msparam_0=N'Blocked Tasks > 5'


MS Access 2000 Application - SQL Server I have an old MS Access 2000 application which connects to a SQL Server 2000 database.  I need to migrate this database and application to a new server with SQL Server 2012.  The Access application is running locally on my user's workstation.

Should I upgrade the Access database to a new version then create a new connection to the 2012 SQL Server (new adp file)?  The application is unsupported and we need to move the database to a new server.

I highly recommend you use an .accdb file, not an .adp file. Support for .adp has been removed since Access 2013.

Migrating will vary depending on the application, but quite often isn't an issue at all. Access has high compatibility between versions, and so has MS SQL server.

You can ask about specific problems here or on Stack Overflow depending on the nature of the problem.


Getting slaves of a master-master setup stopped in sync I have a master-master setup where each master has it's own slave.  They layout might be illustrated as:

 M1 <===> M2
 |         |
 \/        \/
 S1        S2

That is both S1 and M2 slave from M1;  both S2 and M1 slave from M2.

I'm wanting to get it to a point where S1 and S2 are stopped and should be in exact sync.

As I try thinking this out I keep running into gotchas that won't quite work out.

Initial niave approach:

Flush tables with read lock on both masters, ensure the slaves are all caught up
to their respective masters position and just stop slave.  Even if that's scripted to happen one after another it's just a race condition where say a write got to M2 and subsequently S2, but had been been blocked from getting to S1 b/c of M1's read lock (which made it in before).

My second plan was:

Stop slaving on S1, M2.
Flush tables with read lock on M1.
Start slave until M1's master log pos on S1,M2
Stop slave on S2, M1.
Flush tables with read lock on M2.
Start slave until M2's master log pos on (S2,M1)
Start slave on M1, M2, leaving s1,s2 stopped

The problem though is by step 4, there might have been writes on M2 which had since propagated to S2 but where blocked from getting to S1 b/c of M1's read lock.

Another idea:

Stop slaving on M2 and S1.
Start slaving on M2/S1 until some common position on M1.
Change master on s1 to M2 (s1 and s2 are now slaving directly from M2)
stop slaving s1, s2, start until common position on M2

Problem again being during the shuffling there could have been some M2 writes that made it to s2 but never s1.

Can anyway think of a clean way to tweak these approached to make it work?

One of my requirements is having minimal downtime for the Master-Master cycle while allowing more extended replication downtime for the slave-only instances.

Even if it's overly complicated what ever approach I go would with end up being scripted so if there's some kind of rapid looping/checking until just the right time that shouldn't be a problem.

Edit In Response to Rolondo's solution:

An end goal of what I'm trying to accomplish with this is to stop slaving (at least sql_thread execution) on S1 and S2 to have time to do some lengthy comparisons (checksuming etc).

A concern I have is after step 04 is even though these steps are stopping/starting the master SQL threads, the slaves (S1/S2) replication to their respective masters (M1/M2) is still going.  There is a non zero amount of time that occurs between measuring/comparing the M's relationship with each other.  Even if they look good by the time I would issue stop slave to S1/S2 something could have come into M1 or M2 and made it into just their slave.

Would you agree with this or am I missing something?

All of these approaches show that you gave these things a lot of thought.

You are worried about any pending changes when running FLUSH TABLES WITH READ LOCK;.

Think about this: When you issue FLUSH TABLES WITH READ LOCK;, how is replication affected? Recall that replication has two threads

IO Thread
SQL Thread

The IO Thread is responsible for communication between Master and Slave. It downloads binary log entries from the Master and stores them in the Slave's relay logs.

The SQL Thread is responsible for

reading the next SQL statement from the Slave's relay logs and processing them
maintain are temp tables created within the session of the SQL Thread

When you run FLUSH TABLES WITH READ LOCK;, only the SQL Thread gets affected because it needs to connect to tables. The IO Thread can still collect binary log entries from the Master and store them in the Slave's relay logs. Any replication lag will simply be caught off guard as is. In light of this, STOP SLAVE; should be faster than FLUSH TABLES WITH READ LOCK;. If you are concerned about pending changes, then use STOP SLAVE SQL_THREAD; instead of STOP SLAVE;. That way, whatever is last executed on each Master should be checked.

When you do SHOW SLAVE STATUS\G look for two lines

Relay_Master_Log_File (line 10)
Exec_Master_Log_Pos (line 22)

This tells you what was the SQL statement downloaded to the Slave that was last executed.

Knowing this, you could try the following

Step 01 : On M1 and M2, STOP SLAVE SQL_THREAD;
Step 02 : Run SHOW MASTER STATUS; on M1 and M2
Step 03 : Run SHOW SLAVE STATUS\G on M1 and M2
Step 04 : Evaluate this condition

Does M1's File = M2's Relay_Master_Log_File ?
Does M2's File = M1's Relay_Master_Log_File ?
Does M1's Position = M2's Exec_Master_Log_Pos ?
Does M2's Position = M1's Exec_Master_Log_Pos ?

Step 05 : If any one of the four conditions in Step 04 is not met

On M1 and M2, START SLAVE SQL_THREAD;
SELECT SLEEP(30);
Go Back to Step 01

If you get past Step 05 with all four conditions in Step 04, M1 and M2 are in sync.

Once M1 and M2 are frozen simultaneously

S1 should match M1

Wait until S1's Seconds_Behind_Master = 0
M1's File = S1's Relay_Master_Log_File
M1's Position = S1's Exec_Master_Log_Pos

S2 should match M2

Wait until S2's Seconds_Behind_Master = 0
M2's File = S2's Relay_Master_Log_File
M2's Position = S2's Exec_Master_Log_Pos

No need to run STOP SLAVE; on S1 or S2

I hope this helps

UPDATE 2012-05-11 17:30 EDT

Once S1 and S2 match up with their respective Master, you could STOP SLAVE; if you want to. Since M1 and M2 are frozen, no other changes can reach S1 or S2. Thus, STOP SLAVE; is not a requirement but you do so anyway.

UPDATE 2012-05-11 21:29 EDT

Your Comment

  M1/M2 are frozen from receiving updates from one another but not from receiving a legit update from an external client/application, no?

Are you still accepting incoming feeds? You did say in the original question

  As I try thinking this out I keep running into gotchas that won't quite work out.

That would certainly be one gotcha. Therefore, discontinue incoming feeds.

Since you want to do FLUSH TABLES WITH READ LOCK; to M1 and M2, I have one recommendation. Please set this one hour before syncing everything:

SET GLOBAL innodb_max_dirty_pages_pct = 0;

This will clear all dirty pages from the InnoDB Buffer Pool. That way, the time for FLUSH TABLES WITH READ LOCK; is as fast as possible. When all syncing is done, set it back to 90 (if running MySQL 5.5) or 75 (otherwise).

Your Comment

  I could see how M1/M2 were locked if they flushed w/ read lock but it seemed your steps were not including such a step

I was not including such a step because I was under the impression you would disable outside feeds.


Compact Large Objects in Reorganize Index task I'm setting up a maintenance plan in SQL Server 2008 R2.

On the Reorganize Index Task it has an option for "Compact Large Objects".  What does that mean and do I want to turn it on?

Large objects (LOBs) are stored in different "pages".  A page is a small section of a database, typically an 8kb chunk.  For rows that contain LOB columns, the normal page will contain a reference to the LOB page.
From the alter index manual page:

WITH ( LOB_COMPACTION = { ON | OFF } )
Specifies that all pages that
contain large object (LOB) data are compacted. The LOB data types are
image, text, ntext, varchar(max), nvarchar(max), varbinary(max), and
xml. Compacting this data can improve disk space use. The default is
ON.

The default is "on" and it's hard to think of a case where you'd want to turn it off.  Perhaps if you had a strict time window for reindexing, and turning LOB compaction off would allow you to meet the window.


Several PC Cannot Connect To MySQL Server on Certain Time I have a network of 10 PCs and 1 Server. I installed MySQL Community Server v5.6.10 64-bit on the Server, and all the PCs are Windows XP, using ODBC Connection 5.2(a).

Some of the PC (previously it was 3, now become 4) cannot connect to the MySQL at a certain time, which is 2.00 pm. Before, it was OK, the client can connect to the Server normally, but at 2.00 pm, those PC cannot connect anymore, with this error:

Your connection attempt failed for user xxx@xxx from your host to server at
xxx.xxx.xxx.xxx:xxxx. Cannot connect to MySQL Server on xxx.xxx.xxx.xxx (10055)
Please:
1. Check that mysql is running on server xxx.xxx.xxx.xxx
2. Check that mysql is running on port xxxx
3. Check the xxxxxx has rights to connect to xxx.xxx.xxx.xxx from your address (mysql rights define what clients can connect to the server and from which machines)
4. Make sure you are both providing a password if needed and using the correct password for xxx.xxx.xxx.xxx connection from the host address you're connecting from

From the above error message:

I already check that mysql is running (other PC can access it normally)
I also check MySQL to run in port xxxx
I also check the user xxxxxx has rights to connect (because it can connect to the server before 2 pm)
I also check the password has been provided correctly
I also check the maximum number of connection in my.ini, which I set to 0 (unlimited connection)
I also check the network connection, but find no problem since I can still ping the server and other PC

The only action I do to make everything work again is by restarting the PC (client), however, I don't want such solution. I want to know what's the cause, is it because of the ODBC connector, the PC, or any other reason. Could anyone give me some hints on what I should check?

Thank you

This has nothing to do with the MySQL server, so no troubleshooting on that side should be needed.  It is also unrelated to usernames, machine names, and passwords.

C:\>perror 10055
Win32 error code 10055: An operation on a socket could not be performed because
the system lacked sufficient buffer space or because a queue was full.

The MySQL server is not returning this error.  Windows, on the client side, is generating this error locally, when you're trying to connect, because of a resource shortage on the client system.  Of course, a reboot of the client would clear this right up... until you hit the limit again.

The most likely cause of this would be that you have an application running on those same machines is not releasing system resources properly... It wouldn't necessarily even have to be the application that's accessing MySQL but that's probably a good bet if that's the only application where you're experiencing problems.

This command, run on the client that is experiencing the problems, will list all of your currently open network sockets, as well as the name of the application that owns them:

C:\netstat -a -b -n

Running this on a machine that is unable to connect should give you an idea of which application is causing the problem because you will likely see an inordinate amount of sockets owned by one application.

The Windows error is also known as WSAENOBUFS.


MongoDB production security and backup I'm quite new to MongoDB and now I was trying to understand the best practices to secure a DB in production and how to automatically backup it.

At the moment for security issues I just changed auth true in the mongod.conf, created a new user for my test DB and changed the port to be something different than 27017.

First question: There is something else to do to enhance security?

Regarding the backup, I have seen different blogs but I didn't understand few things:

Do Mongo create an automatic file for the backup (something called BSON file)?
Is it a good idea to backup the DB every time a value changed?

I wish to understand very well how to be totally safe.

I hope in your help to understand very well the steps I should do to be safe.

Many Thanks

At the moment for security issues I just changed auth true in the mongod.conf, created a new user for my test DB and changed the port to be something different than 27017.

  First question: There is something else to do to enhance security?

The MongoDB documentation has a Security Checklist and information on best practices. Aside from enabling authentication you will want to limit network exposure with proper firewall configuration and ensure you are running with secure configuration options (eg. HTTP and REST interfaces disabled). See the checklist for full details.

Changing the port from the default doesn't really enhance security, as that is trivial to discover with a port scanner. You want to have proper network security so potential attackers don't even get as far as connecting.

  Regarding the backup, I have seen different blogs but I didn't understand few things:

  Do Mongo create an automatic file for the backup (something called BSON file)?

No, MongoDB does not create backups by default. Recommended backup methods are described in the MongoDB manual. I would consider the MongoDB manual the definitive reference source as it is kept current with product changes. Blog articles may be outdated or contain incomplete/incorrect advice.

  Is it a good idea to backup the DB every time a value changed?

This will depend on your backup strategy. In the general sense, you would not be taking a full backup everything a single value changes using command-line tools like mongodump.

However, a typical deployment uses a replica set which provides for data redundancy and failover across multiple servers. Replication is based on an oplog (operations log) which contains a rolling history of all changes to data that gets replicated to all members of the replica set. Administrative tools like the MongoDB Management Service (MMS) can also use the oplog to create continuous backups and do point-in-time restores.


Incrementing an integer every 150 rows I'm trying to write a SELECT query that calculates a BatchNumber column that increments by 1 every 150 rows, starting at 1001.

The expected BatchNumber should be 1001 for the first 150 rows, then 1002 for rows 151-300, and so on...

I've tried using a division on a dynamic ROW_NUMBER(), but can't get it.

SELECT ID
    ,ROW_NUMBER() OVER (ORDER BY (SELECT ID)) AS RowNumber
    ,CASE
        WHEN CONVERT(INT, (ROW_NUMBER() OVER (ORDER BY (SELECT ID)) % 150))=0
        THEN 0 ELSE 1001 END
    + CONVERT(INT,(ROW_NUMBER() OVER (ORDER BY (SELECT ID)) / 150))
    AS BatchNumber
FROM Table1 WITH (NOLOCK);

The simplest way I can get it is:

SELECT ID
    ,1001
    + (ROW_NUMBER() OVER (ORDER BY (SELECT ID)) - 1) / 150
    AS BatchNumber
FROM Table1;


MongoDB Replica Set member cannot start due "84 key/value already in index" I have a Replica Set with 3 members (1 primary and 2 secondaries), and one of the secondaries was working just fine and thrown the following error

> 2018-03-12T11:03:54.868-0400 E REPL [repl writer worker 13] writer
> worker caught exception: :: caused by :: 84 key/value already in index
> on: { ts: Timestamp 1520867034000|8, h: 287037468373256260, v: 2, op:
> "u", ns: "Sitecore_analytics_PROD.Interactions", o2:
>
> { _id: BinData(3, EFC3DD1A15B75442986344FDE9CC71EB) } , o: { $set:
>
> { ContactVisitIndex: 2 } } } 2018-03-12T11:03:54.868-0400 I - [repl
> writer worker 13] Fatal Assertion 16360 2018-03-12T11:03:54.868-0400 I
> - [repl writer worker 13]
> ***aborting after fassert() failure

After that the MongoDB cannot start anymore, and keep throwing the same error!

How can I determine the root cause of it? Is there a way to prevent? How to solve it?

Do initial sync for that problematic secondary node. Just delete files from dbpath and start mongod. Node will do initial sync automatically. Other and faster way (if you can) is copy snapshot of dbpath of existing node (can be primary or secondary) and start mongod.

The problem here is that there is already entry at unique index and system tries to apply opLog line and crashes to that error.


How to prevent PostgreSQL from rewriting OUTER JOIN queries? My query is:

SELECT  Acol1, Acol2, Bcol1, Bcol2, Ccol1, Ccol2
FROM    tableA LEFT JOIN
            (tableB FULL JOIN tableC ON (Bcol1 = Ccol1))
            ON (Acol1 = Bcol1)

EXPLAIN ANALYZE gives me:

                                                      QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
 Hash Right Join  (cost=99.65..180.45 rows=1770 width=24) (actual time=0.043..0.103 rows=3 loops=1)
   Hash Cond: (tableb.bcol1 = tablea.acol1)
   ->  Hash Left Join  (cost=49.83..104.08 rows=1770 width=16) (actual time=0.011..0.062 rows=3 loops=1)
         Hash Cond: (tableb.bcol1 = tablec.ccol1)
         ->  Seq Scan on tableb  (cost=0.00..27.70 rows=1770 width=8) (actual time=0.001..0.002 rows=3 loops=1)
         ->  Hash  (cost=27.70..27.70 rows=1770 width=8) (actual time=0.004..0.004 rows=3 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 1kB
               ->  Seq Scan on tablec  (cost=0.00..27.70 rows=1770 width=8) (actual time=0.001..0.002 rows=3 loops=1)
   ->  Hash  (cost=27.70..27.70 rows=1770 width=8) (actual time=0.014..0.014 rows=3 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 1kB
         ->  Seq Scan on tablea  (cost=0.00..27.70 rows=1770 width=8) (actual time=0.009..0.011 rows=3 loops=1)
 Total runtime: 0.151 ms

Postgres changed the full outerjoin between tableB and tableC to a right outerjoin, since later the left outerjoin with tableA will eliminate null values anyway. It is equivalent to the original query.

However, I am hacking Postgres to implement my join enumeration related algorithm and to do experiments. I do not want Postgres to change the full outerjoin to a left outerjoin. Is there a way to do so?

You can introduce optimization barriers for your purpose.

Preface

Plain EXPLAIN (without ANALYZE) is enough for the purpose of this question. Parentheses around the ON expression are just noise. Adding table aliases to be unambiguous.

We do see "Full Join" for the full join by itself:

SELECT * FROM tableB b FULL JOIN tableC c ON b.Bcol1 = c.Ccol1;

We could rewrite the full join with a subquery:

SELECT a.Acol1, a.Acol2, d.Bcol1, d.Bcol2, d.Ccol1, d.Ccol2
FROM   tableA a
LEFT   JOIN (
   SELECT *  -- sort out conflicting names with aliases
   FROM   tableB b FULL JOIN tableC c ON b.Bcol1 = c.Ccol1
   ) d ON a.Acol1 = d.Bcol1;

You have to sort out conflicting names with aliases in the subquery - but then again, you would need to do this in any case in the outer SELECT for multiple columns of the same name in the underlying tables.

The query is still optimized as a whole since subqueries do not impose optimization barriers. You'll still see a "Left Join" or "Right Join". However, we can expand on this form to arrive at a solution:

Solution 1. OFFSET 0 hack (undocumented)

EXPLAIN
SELECT a.Acol1, a.Acol2, d.Bcol1, d.Bcol2, d.Ccol1, d.Ccol2
FROM   tableA a
LEFT   JOIN (
   SELECT *  -- you'll have to sort out conflicting names with aliases
   FROM   tableB b FULL JOIN tableC c ON b.Bcol1 = c.Ccol1
   OFFSET 0  -- undocumented hack
   ) d ON a.Acol1 = d.Bcol1;

You'll see a "Full Join".

Why? As soon as a subquery uses an OFFSET clause, the query planner / optimiser plans the subquery separately. OFFSET 0 is logical noise, but Postgres still considers the clause which makes this a query hint to materialize the subquery, effectively. (Even though Postgres does not otherwise support query hints.) It's a much debated issue. Related:

How can I prevent Postgres from inlining a subquery?
http://blog.2ndquadrant.com/postgresql-ctes-are-optimization-fences/

Solution 2. Use a CTE (documented)

EXPLAIN
WITH cte AS (
   SELECT *  -- you'll have to sort out conflicting names with aliases
   FROM   tableB b FULL JOIN tableC c ON b.Bcol1 = c.Ccol1
   )
SELECT a.Acol1, a.Acol2, d.Bcol1, d.Bcol2, d.Ccol1, d.Ccol2
FROM   tableA a
LEFT   JOIN cte d ON a.Acol1 = d.Bcol1;

You'll see a "Full Join" as well.

The manual:

  A useful property of WITH queries is that they are evaluated only once
  per execution of the parent query, even if they are referred to more
  than once by the parent query or sibling WITH queries. Thus, expensive
  calculations that are needed in multiple places can be placed within a
  WITH query to avoid redundant work. Another possible application is to
  prevent unwanted multiple evaluations of functions with side-effects.
  However, the other side of this coin is that the optimizer is less
  able to push restrictions from the parent query down into a WITH query
  than an ordinary subquery. The WITH query will generally be evaluated
  as written, without suppression of rows that the parent query might
  discard afterwards. (But, as mentioned above, evaluation might stop
  early if the reference(s) to the query demand only a limited number of rows.)

Bold emphasis mine.

SQL Fiddle. (For Postgres 9.3, no later version available yet)


ParallelPeriod returning null for Feb. 29 in date dimension I have a calendar date dimension backed by a physical table of dates (originally created on SQL Server 2000, hence the datetime instead of date):

CREATE TABLE [dbo].[PostDate_Dimension](
    [post_date] [datetime] NOT NULL PRIMARY KEY,
    [day_of_year] [int] NOT NULL,
    [day_of_month] [int] NOT NULL,
    [month_of_year] [int] NOT NULL,
    [post_year]  AS (datepart(year,[post_date])),
    [post_month]  AS (datepart(month,[post_date])),
    [post_day]  AS (datepart(day,[post_date]))
)

The Post Date dimension has four attributes (with member key columns listed, some of which are calculated in the DSV):

Day (Dimension Key) - post_date
Month - post_year, post_month
Quarter - post_year, post_quarter = DatePart(quarter, "post_date"))
Year - post_year

It's nothing too fancy, obviously. I also have a few calculated measures that use ParallelPeriod to calculate YTD figures from the previous year, for quick side-by-side comparison without requiring the user to choose a specific slice of dates. Just pick the current year, and it will find the latest date with sales in it, then compare to that same range from the previous year.

Finding the appropriate date in the previous year normally boils down to this:

ParallelPeriod(
    [Post Date].[Post Date].[Year],
    1,
    Tail(
        NonEmpty(
            Descendants(
                [Post Date].CurrentMember,
                ,
                Leaves
            ),
            Measures.[Total Price]
        ),
        1
    ).Item(0)
)

The Tail call is where it finds the latest date beneath the currently selected Post Date member (typically the current year). That works fine. But if that returns Feb. 29, meaning the last sale for a particular combination of dimension members occurred on Feb. 29, then it passes Feb. 29 into the ParallelPeriod function, which subsequently returns null. And then the previous-year YTD measure also returns null.

So, in a nutshell: Based on this particular schema, is there a simple way to have ParallelPeriod behave nicely for Feb. 29 inputs? If it just returns Feb. 28 of the previous year, that's fine.

EDIT:

A few things I've tried:

Using this expression to adjust the Post Date member:
Iif(MONTH([Post Date].[Post Date].CurrentMember.Member_Caption) = 2 And DAY([Post Date].[Post Date].CurrentMember.Member_Caption) = 29, [Post Date].[Post Date].CurrentMember.PREVMEMBER, [Post Date].[Post Date].CurrentMember)
This works, but the code would be atrocious, since I'd have to replace all [Post Date].[Post Date].CurrentMember with Tail(NonEmpty(Descendants([Post Date].CurrentMember,, Leaves), Measures.[Total Price]), 1).Item(0)).
Using Except to remove all Feb. 29 dates from the results of NonEmpty(Descendants([Post Date].CurrentMember,, Leaves), Measures.[Total Price]). I can't figure out the proper syntax (if any) to get a set of all Feb. 29s from the dimension.
Creating a .NET assembly with a user-defined function that takes a member as a parameter, and returns the previous member if it's a Feb. 29. It seems like the classes in Microsoft.AnalysisServices.AdomdServer are extremely limited and don't even allow for this basic task (nor even retrieving the member key as a date value).

Alright, I think I've got a workaround I can live with. First, I created a named set in the cube, called Feb29:

Filter(
    [Post Date].[Post Date].[Day].ALLMEMBERS,
    MONTH([Post Date].CurrentMember.Member_Caption) = 2 And DAY([Post Date].CurrentMember.Member_Caption) = 29
)

Then I've amended the portion of the calculation which looks for the latest sale date (note the - Feb29):

ParallelPeriod(
    [Post Date].[Post Date].[Year],
    1,
    Tail(
        NonEmpty(
            Descendants(
                [Post Date].CurrentMember,
                ,
                Leaves
            ),
            Measures.[Total Price]
        ) - Feb29,
        1
    ).Item(0)
)

Thus when it looks for the latest sale date in, say, 2012, it will ignore Feb. 29, and compare to the previous year's sales from Jan. 1 to Feb. 28 instead. I'm still open to suggestions if there's a better way to do this (particularly that awful Filter expression I used).


Connecting to mysql using management tool So far I have set up my MySQL database only in the terminal using a homebrew.

Now I want to connect to the database and access all of its tables using Sequel Pro, which is a database management tool.

I'm wondering how I can connect to my local database that I have created on my terminal; Sequel Pro gives three types of ways you can connect : standard, socket, and ssh.  I'm not sure which I should select.

Socket implies that client and server are on the same machine.

SSH is for security.

So, try "standard".


What causes sql server to generate a new plan for a stored procedure? We've ran into an issue in production a couple of times now where it looks like a stored procedure gets a new, bad plan and starts causing havoc.  Unfortunately, we weren't able to capture the plan before and after to see what the bad plan looked like before we did a dbcc freeproccache, we also don't have query store set up.

My question is, what all can cause sql server to generate a new plan for a stored procedure?  Especially for an sp that is ran all of the time.  Some of the ones I know are:

dbcc freeproccache
rebooting the server
option recompile

For SQL 2016 here is the list why 'statement_recompile_cause' can happen.

Schema changed
Statistics changed
Deferred compile
Set option change
Temp table changed
Remote rowset changed
For browse permissions changed
Query notification environment changed
PartitionView changed
Cursor options changed
Option (recompile) requested
Parameterized plan flushed
Test plan linearization
Plan affecting database version changed
Query Store plan forcing policy changed
Query Store plan forcing failed
Query Store missing the plan

You can get the same list for any other versions that support Extended Event tracing.

 Select
  [name],
  map_key,
  map_value
FROM sys.dm_xe_map_values
WHERE [name] ='statement_recompile_cause'

Few good references:

A good article to read by Bhavesh Patel
Another Q&A in StackOverflow.
When does a Stored Procedure recompile?(Sql Server)


How does max_allowed_packet affect the backup and restore of a database? I have a database based on mysql 5.0.75-0Ubuntu10.2, running under Ubuntu 9.04. I have a table in that database with a medium blob column. I have been using automysqlbackup to do nightly backups/dumps of the database, then restoring them periodically to either a staging server we have or to my MacBookPro.

We recently had some trouble restoring with errors related to the packet size. Late last week, I changed the max_packet_size setting within automysqlbackup to 1MB (it was unspecified). After that change, the database backups stop at the table with the blob column. Actually, it stopped with the first record in that table. Do I have to set the max_packet_size setting to be at LEAST as large as the largest blob stored in that column?

I saw an interesting answer to a question about the biggest blob you may have. Here is the statement I saw in ServerFault : innodb_log_file_size and innodb_log_buffer_size combined must be larger than ten times your biggest blob object if you have a lot of large ones.

Based on that ServerFault post from Nils-Anders Nøttseter, you should query the table and find out which BLOB is the biggest, multiply that number by 11 or more, and use that answer as the max_allowed_packet going forward.

It's funny that I addressed another question where I suggested sizing the max_allowed packet to hopefully solve the issue.

CAVEAT

According to the page 99 of "Understanding MySQL Internals" (ISBN 0-596-00957-7), here are paragraphs 1-3 explaining MySQL Packets:

  MySQL network communication code was
  written under the assumption that
  queries are always reasonably short,
  and therefore can be sent to and
  processed by the server in one chunk,
  which is called a packet in MySQL
  terminology. The server allocates the
  memory for a temporary buffer to store
  the packet, and it requests enough to
  fit it entirely. This architecture
  requires a precaution to avoid having
  the server run out of memory---a cap
  on the size of the packet, which this
  option accomplishes.

  The code of interest in relation to
  this option is found in
  sql/net_serv.cc. Take a look at my_net_read(), then follow the call to my_real_read() and pay
  particular attention to
  net_realloc().

  This variable also limits the length
  of a result of many string functons.
  See sql/field.cc and
  sql/intem_strfunc.cc for details.

Knowing this about MySQL Packets allows a DBA to size them up to accommodate multiple BLOBs inside one packet even if they are obnoxiously large.

In the past, I have had issues like this with MySQL Replication where the Master had a smaller value for the max_allowed packet than the Slave. It required me to bump up max_allowed_packet to 256M and restart mysql on the Master. In 99% of issues I have had, this would solve it. A few times I had to set to 512M.


Loading XML data from SQL Server directly into MarkLogic? Has anyone loaded XML data from SQL Server to a MarkLogic document store? This would be preferable to dumping XML to file system and then using mlcp.

Maybe with SSIS?

JShean,

I am with the MarkLogic product team, I saw your question and wanted to point you to some information.  In general, mlcp is the most efficient way to load documents into MarkLogic, though I know many customers don't like dumping all the data to disk.

Have you considered doing the process in batch - dump a certain set to disk, use mlcp to load and repeat?

The alternative is to use the REST API and batch write documents to Marklogic.  I have included a few links to our documentation.

Introduction to REST API

docs.marklogic.com/guide/rest-dev/intro

Manipulating Documents (general summary)

docs.marklogic.com/guide/rest-dev/documents

Reading and Writing Multiple Documents

docs.marklogic.com/guide/rest-dev/bulk

If you have additional questions, you can reach me at susan.lee@marklogic.com.

Thanks, and have a GREAT day!

susan.


ORA-39244:Event to disable dropping null bit image header during relational select I was trying to import a dump file using IMPDP command and has end up with the following errors:

ORA-31626: job does not exist
ORA-31637: cannot create job SYS_IMPORT_FULL_01 for user user1
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT_INT", line 798
ORA-39244: Event to disable dropping null bit image header during relational select
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.KUPC$QUE_INT", line 1825
ORA-04063: package body "SYS.DBMS_AQADM_SYS" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_AQADM_SYS"

I have searched on oracle forums and google for any solution. Not sure why this error is happening. Is this related to the db configuration?

For me this line seems most telling:

PL/SQL: could not find program unit being called: "SYS.DBMS_AQADM_SYS"

You should check to see if the user that you exported had a job scheduled using a program owned by someone else. If so you are trying to schedule a job for a program that does not exist. You can create the program, then do the import as long as the user who will own the program exists before you create the program as that user.


What is search space when it comes to query optimization? I am quite new to learning about databases and I have come across the term "search space" in the context of query optimization. I am a little unsure of what the term means and why it is important? I have tried to search the web but haven't found any easy to understand answers.
Thanks in advance!

This search space is part of mathematical optimisation.
From Wikipedia's article on Feasible Region
(emphasis mine)

In mathematical optimization, a feasible region, feasible set, search space, or solution space is the set of all possible points (sets of values of the choice variables) of an optimization problem that satisfy the problem's constraints, potentially including inequalities, equalities, and integer constraints.1 This is the initial set of candidate solutions to the problem, before the set of candidates has been narrowed down.

From Wikipedia's article on Search Algorithm
(emphasis mine)

Classes
[...]
For virtual search spaces
[...]
This class also includes various tree search algorithms, that view the elements as vertices of a tree, and traverse that tree in some special order. Examples of the latter include the exhaustive methods such as depth-first search and breadth-first search, as well as various heuristic-based search tree pruning methods such as backtracking and branch and bound. Unlike general metaheuristics, which at best work only in a probabilistic sense, many of these tree-search methods are guaranteed to find the exact or optimal solution, if given enough time. This is called "completeness".

I'm only including this answer, because it wouldn't fit in a comment and it's a Community wiki entry which can be improved upon by the community.


Is a dropped (or altered) function still available inside already opened transactions? I found

PostgreSQL - Are CREATE FUNCTION and DROP FUNCTION locking when used inside different transactions?

but there are no answers and isn't exactly the same as my question (though very similar).

Let's say I do the following:

Create a function myfunc()
Start a transaction from client A
Start a transaction from client B
In transaction B, use "create or replace function" to revise the definition of myfunc()
Commit transaction B
Call myfunc() from transaction A

What happens in step 6?  Am I calling the original function as defined in step 1?  Or the modified form from step 4 (committed in step 5)?

And if the function is dropped in step 4 rather than being modified, will step 6 fail or succeed?  (This is probably the same question but modifications may work differently.)

Where is the documentation about this?

Interesting question.

From a small test, it appears that function modifications and deletes are transactional. Meaning, that - in any isolation level - when transaction 2 modifies or deletes the function, transaction 1 is oblivious to it and still uses the old version of the function.

Any changes to the function become visible only after the transaction commits and only to transactions that start after that commit. The isolation level is irrelevant, as the function tested was not reading any data from any tables.

-- Create Function
x=# create or replace function f() returns integer as
$$ select 1 ; $$ immutable language sql ;
CREATE FUNCTION

-- TRAN 1
x=# begin ;
BEGIN
x=# select * from f() ;
 f
---
 1
(1 row)
                    -- TRAN 2
                    x=# begin ;
                    BEGIN
                    x=# drop function f () ;
                    DROP FUNCTION
                    x=# commit ;
                    COMMIT
-- TRAN 1
x=# select * from f() ;
 f
---
 1
(1 row)
x=# commit ;
COMMIT

-- After COMMIT
x=# select * from f() ;
ERROR:  function f() does not exist
LINE 1: select * from f() ;
                      ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
x=#

In a slightly different scenario, if both transactions try to modify the function, then only one succeeds and the other gets blocked and then fails if the first commits.


Replication and table locks during alter table Recently we have noticed that a number of our Rails migrations end up deadlocking / freezing our app+DB in production. Preliminary investigation reveals that this is probably due to concurrent access by the app and the migration on a table with very high reads.

Would it make sense to explore a replicated PG setup (master-slave perhaps), where all writes and migrations are executed against the master, and all high volume reads are executed against the slave?

How does PG behave when an ALTER TABLE statement is replicated to a slave? Does the slave also acquire the same table locks? Will replication solve the problems that we are currently facing?

Would it make sense to explore a replicated PG setup (master-slave perhaps), where all writes and migrations are executed against the master, and all high volume reads are executed against the slave?

Yes, that's one option. But if you write your migrations more carefully (Rails's defaults are pretty simplistic) you can greatly reduce the locking that's required on the master anyway.

For example, instead of

ALTER TABLE tblah ADD COLUMN cblah DEFAULT dblah NOT NULL

you can write

ALTER TABLE tblah ADD COLUMN cblah;
ALTER TABLE tblah ALTER COLUMN cblah DEFAULT dblah;
UPDATE tblah SET cblah = dblah WHERE cblah IS NULL;
ALTER TABLE tblah ALTER COLUM cblah NOT NULL;

The latter will not hold a strong lock over the table rewrite, and will be much less disruptive. It does more work overall, but with lower lock levels.

If you take more care with your migration writing and testing, you'll find that this issue mostly goes away. I've helped with near-zero downtime schema changes on multi-terabyte databases and the same principles apply there.

  How does PG behave when an ALTER TABLE statement is replicated to a slave? Does the slave also acquire the same table locks?

Yes. You can always switch reads to the master while the replia applies a big schema update that's already committed on the master, though.


Update a JSON column with WHERE Clause in JSON Column Postgres I am having trouble writing a WHERE clause to update a JSON column in Postgres database:

Given a table of the form:

create table Test_Table (
    clusterId INT,
    owner VARCHAR(50),
    data JSONB
)

And three rows of data:

insert into Test_Table
values
(1, 'Jacky',{data}),
(2, 'Bob', NULL),
(3, 'Wolf',NULL);

...where the {data} in row 1 is a json document of the form...

{
    "certified": [
        {
            "Type": "New Instance",
            "Status": "Active",
            "created": "2020-02-03",
            "id_no": "f6b2197c4889"
        },
        {
            "Type": "New Instance",
            "Status": "Failed",
            "created": "2020-02-04",
            "id_no": "216430036272"
        },
        {
            "Type": "New Instance",
            "Status": "Active",
            "created": "2020-02-04",
            "id_no": "3bb515f781c6"
        },
        {
            "Type": "New Instance",
            "Status": "Active",
            "created": "2020-02-04",
            "id_no": "26ae740e9b21"
        },
        {
            "Type": "New Instance",
            "Status": "Active",
            "created": "2020-02-04",
            "id_no": "7e30a80211a2"
        }
    ],
    "in_common": [
        {
            "name": "Kodali",
            "owner": "Jet"
        },
        {
            "name": "Lakers",
            "owner": "Fantastic"
        },
    ]
}

I want to write an update statement to update the data column and change the "Status" to 'Active' where cluster_id is 1 and "id_no" is "216430036272". How do I write a WHERE clause that will update only this attribute?

As noted in comments, this would be a much easier task if the data was properly normalised. However, in the interest of wasting a morning playing with postgres json functions, you could use the following query (fiddle)

with js as (
    select
        jsonb_array_elements((data->>'certified')::jsonb) as elems
    from test_table
    where clusterid = 1
), to_keep as (
    select elems
    from js
    where js.elems->>'id_no' != '216430036272'
), to_upd as (
    select
        jsonb_set(elems,'{Status}','"Active"') as elems
    from js
    where js.elems->>'id_no' = '216430036272'
), final_array as (
    select jsonb_agg(elems) as elems
    from (
        select elems from to_keep
        union all
        select elems from to_upd
    ) j
)
update test_table set
    data = jsonb_set(data,'{certified}',elems)
from final_array fa
where clusterid = 1;

Pretty clearly this is very specifically answering your narrow question, so you'll need to make your own modifications to generalise it. By way of a breakdown though...

  I want to ... update the data column and change the "Status" to "Active" where "cluster_id" is 1 and "id_no" is "216430036272"

js

select
    jsonb_array_elements((data->>'certified')::jsonb) as elems
from test_table
where clusterid = 1

The first common table expression (CTE) filters down to clusterid = 1 out of all the rows in Test_Table

The element certified in your json document is itself an array of objects - as indicated by the [{},{}] notation. For this reason, you need to unwrap the array. jsonb_array_elements turns a single row into many rows. Thus the cell...

+---------+
| array   |
+---------+
| [{},{}] |
+---------+

...becomes...

+---------+
| objects |
+---------+
| {}      |
| {}      |
+---------+

to_keep

Now that you've unwrapped the objects in the array, you need to preserve them. This CTE isolates those objects that you do not want to modify (js.elems->>'id_no' != '216430036272') so that they can be re-aggregated unchanged into the array later.

to_upd

This is the object you've stated you want to update (specified by js.elems->>'id_no' = '216430036272'). jsonb_set(elems,'{Status}','"Active"') modifies the Status element as desired (case sensitive).

final_array

select jsonb_agg(elems) as elems
from (
    select elems from to_keep
    union all
    select elems from to_upd
) j

You need to turn many rows back into one row to re-create the array-of-objects you decomposed to perform the update in the first place. UNION ALL re-connects the rowsets from to_keep and to_upd and then jsonb_agg causes

+---------+
| objects |
+---------+
| {}      |
| {}      |
+---------+

...to turn back into...

+---------+
| array   |
+---------+
| [{},{}] |
+---------+

...which allows us to use it in...

The actual UPDATE statement

update test_table set
    data = jsonb_set(data,'{certified}',elems)
from final_array fa
where clusterid = 1;

You must re-specify clusterid = 1 in order to target the correct row for modification. jsonb_set is seen here again, this time supplanting the entire certified node with the reconstructed data from final_array.elems


Is there a faster way to count JSONB tags? I'm trying to squeeze some more performance out of this query in Postgres 9.5. I'm running it over 400,000 rows.

In playing around with it, I've noticed that the CASE statements are adding quite a bit to the query cost - if I replace them with simply summing some existing column it halves the execution time. Is there a more efficient way to calculate these sums?

SELECT sum("tag1"), sum("tag2"), sum("total_tags")
FROM (
    SELECT people.data->'recruiter_id' AS recruiter_id,
          (CASE WHEN people.data->'tags' ? 'tag1' THEN 1 END) AS "tag1",
          (CASE WHEN people.data->'tags' ? 'tag2' THEN 1 END) AS "tag2",
          ((CASE WHEN people.data->'tags' ? 'tag1' THEN 1 ELSE 0 END) +
           (CASE WHEN people.data->'tags' ? 'tag2' THEN 1 ELSE 0 END)) AS total_tags
    FROM people WHERE people.data->'tags' ?| ARRAY['tag1','tag2'] ) AS target
GROUP BY recruiter_id

Output of EXPLAIN ANALYSE:

HashAggregate  (cost=1076.30..1078.22 rows=550 width=202) (actual time=7043.115..7043.208 rows=449 loops=1)
  Group Key: (people.data -> 'recruiter_id'::text)
  ->  Bitmap Heap Scan on people  (cost=12.85..1072.72 rows=550 width=202) (actual time=13.908..2619.878 rows=48492 loops=1)
        Recheck Cond: ((data -> 'tags'::text) ?| '{tag1,tag2}'::text[])
        Heap Blocks: exact=26114
        ->  Bitmap Index Scan on index_people_on_data_tags  (cost=0.00..12.82 rows=550 width=0) (actual time=9.219..9.219 rows=48493 loops=1)
              Index Cond: ((data -> 'tags'::text) ?| '{tag1,tag2}'::text[])
Planning time: 0.139 ms
Execution time: 7043.291 ms

Running on:

  PostgreSQL 9.5.5 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit

The inner query and outer query are generated by separate portions of the application. Is it possible to optimise without restructuring?

This should be faster and simpler overall:

SELECT *, tag1 + tag2 AS total_tags
FROM  (
   SELECT (data->>'recruiter_id')::int AS recruiter_id  -- cheaper to group by int
        , count(*) FILTER (WHERE data->'tags' ? 'tag1') AS tag1
        , count(*) FILTER (WHERE data->'tags' ? 'tag2') AS tag2
   FROM   people
   WHERE  data->'tags' ?| ARRAY['tag1','tag2']
   GROUP  BY 1
   ) target;

Assuming that recruiter_id is an integer entity, it's cheaper to group by the integer value than the jsonb object containing an integer value. I also assume, you'd rather get the integer value in the result anyway.
Only count once in the subquery, then add counts for the total sum in the outer SELECT.
Use the aggregate FILTER clause for conditional counts:

Return counts for multiple ranges in a single SELECT statement

If you want shorter syntax, this gets you the same result and performance:

count(data->'tags' ? 'tag1' OR NULL) AS tag1

Index and missing statistics for jsonb

Typically, indexes are the deciding factor for performance with big tables. But since your query retrieves 48.493 of 400.000 rows, i.e. > 12 %, the index is not helping this query at all.

Why the poor decision? The query planner has no statistics for values inside a json / jsonb object and has to chose the best query plans based on generic selectivity estimates. It expects to find rows = 550 while the query actually finds ~ 90 x as many (rows=48493). The query plan using bitmap index scans is a poor decision. A sequential scan would be faster (not using the index at all).

The index may still be instrumental for less frequent tags (if you have those), an expression index on data->'tags' should serve best. Maybe even a jsonb_path_ops index, combined with an adapted query. More:

Index for finding an element in a JSON array

However, for this and other reasons, while working with common tags, a plain Postgres array or a fully normalized schema will beat the performance of your jsonb object by a long shot.

This discussion in the postgresql-performance list is about your problem exactly:

https://www.postgresql.org/message-id/54C738E1.8080405@agliodbs.com


XQquery for specific attribute of all specific elements in XML I am, looking for a query to get all attribute values "NetAmount" of Element "PRODUCT" from the following samle XML:

<ROOT>
 <CATEGORY name="A">
  <PRODUCT NetAmount="10.50"/>
  <PRODUCT NetAmount="20.50"/>
 </CATEGORY>
 <CATEGORY name="B">
  <PRODUCT NetAmount="30.50"/>
  <PRODUCT NetAmount="40.50"/>
  <PRODUCT NetAmount="75.50"/>
 </CATEGORY>
</ROOT>

For my scenario it does not matter wether the XML is stored in a table or variable.

I tried several different approaches but all failed.

Sample:

SELECT
  NetAmount.value('(@NetAmount)[1])','varchar(250)') AS Products
FROM
  @x.nodes('//ROOT/Category/Product')
    AS P(NetAmount)

I usually get the following error message that makes no sense to me:

  Meldung 2370, Ebene 16, Status 1, Zeile 5 XQuery [value()]: Am Ende
  des XQuery-Ausdrucks wurden keine Token mehr erwartet. Gefunden: ')'.

Almost get it:

select
    d.lin.value('./@NetAmount', 'decimal(10,2)') as Amount
from
    t
cross apply
    data.nodes('ROOT/CATEGORY/PRODUCT') as d(lin);

| Amount |
| :----- |
| 10.50  |
| 20.50  |
| 30.50  |
| 40.50  |
| 75.50  |

db<>fiddle here


Remote Access to SQL Server I have tried everything to be able to connect to my MS SQL Server 2012 remotely.
I have read and setup everything like Kyralessa wrote in his answer HERE.

Enable TCP/IP done
Add 1433 TCP Port done
enable SQL Server Browser done
Add 1433 TCP, 1434 UDP ports to Windows Firewall done
Add sqlserver.exe and sqlbrowser.exe to Windows Firewall done
Use ip address and port to connect done

I am still receiving an error message: Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - A connection attampt failed because the connected party did not properly respond after a period of time.

Nothing works
Do you maybe have any other solution I could try to solve this problem?

I have solved this problem with Port Forwarding.
I've seen no tutorial for Remote Access to SQL Server with this part.
So, setup 1433 TCP Port forwarding in your Router Settings and everything will work as it should.


Should fields that aren't needed often be kept in separate tables? I have a tagging system on my site. I also want to add descriptions to the tags just like on the S.E. sites.

The tags are used for a lot of stuff. Its how categories know what products to display, how users can further drill down into categories, how product attributes are set, how coupons know which products they can be applied too, etc.

Because if this I want the tag table to be as efficient as possible. The tag descriptions won't be needed for any of those tasks, I just want to add them so the people entering data can keep track of what each tag is for (if the tag name is not self explanatory)

I would like to just add a description column to the tag table, but I am worried about hurting the performance on all the other logic that doesn't need the description. I am wondering if it would be better for performance to keep them in a separate tag_descriptions table?

By the way I am using the CakePHP framework which uses ORM (and I think it selects * for model associations)

I sympathize with this situation because of the SELECT * FROM statements from ORMs. General MySQL (and probably other RDBMs) index theory is to only select the columns that you need, preferably entirely from an index.

That being said, the answer depends on how much traffic you are expecting to get to see a noticeable degradation of performance. It also depends on how much usage you'll get out of the category descriptions.

I wouldn't think storing the descriptions in the same table would reduce performance enough to warrant 2x calls to the database when you DO need to grab the descriptions.

To sum it up, I think you've got a bigger performance problem in using an ORM than in storing the descriptions in the same tag table.


Is there any way to get original query based on lsn Is there any way or tool to get original query against the lsn.

For example, I have a lsn range which cause the data set modified. Can I get the actual query against the lsn. Is there any mapping between them in SQL Server ?

Is there any way or tool to get original query against the lsn.

No, there isn't a way to get the "original query text". I quote this, because the log records record what changes happen in that database. Since select statements, cte's, etc., do not generate* log records there is no way to get that information. Additionally, the log records describe the changes that have happened, of which a query could be generated to create the same changes again, reverse them, or show what was changed... however there is no way to find what the larger part - if any - it may play in the batch.

Let's take a quick and simple example:

Suppose we wanted to find all of the accounts using gift cards and give them an extra $1 if they made a purchase over $5 in the last 7 days. The log records would show the $1 increase to any of the accounts that met the above logic, but there is no place for us to see what that predicate was... we'd just see that some accounts (if any) were updated to have 1 added to a column (or whatever your model was).

Thus we could re-create the UPDATE of the individual record, but no way to figure out what the original query was, however we could look at the entire transaction and make a logically equal batch.

What if you wanted to do this in the future - to know all of the queries that have happened?

Native to SQL Server there are various ways to log all of the queries that happen. Additionally there are 3rd party services and applications that can log this as well.

SQL Audit
(deprecated) SQL Trace
Extended Events