SQL Server tempdb on SSD or virtual RAM disc to increase temp-table performance On a monthly bases we have a lot of temp calculations that run 4 days in total. We can't really change so many scripts and therefore i am searching for a config / hardware solution.

Background

Its not a ETL process because we are creating bits of information out of at least 10 applications (therefore i would call it calculations or lots of update-statements). This combination of information is only 1 stage of a bigger process that saves the calculated data finally with a normal ETL process (SSIS) in a DWH. If this calculation stage fails (server crash), we can easily restart it.

Problem

The MSSQL server where this calculation is running has only 2 HDD-raid1. One raid1 for data, indices and tempdb and one for the log. This calculation produce so much I/O that the whole process takes days. The server has 180GB of RAM and theoretically the whole data and result of these calculations could fit in 5GB memory.  This indicates we are using the database/resources in the wrong way.

Possible Solution

I think we have to reduce the I/O in the first place and also handle the I/O that is needed anyway in a second step. BTW the server is SQL Server 2014 Standard Edition.

Move the tempdb on a virtual ram disc (ca. 40GB). That means we move the main I/O to memory, correct?
Use more temp tables to reduce server-logging and recovery time and use the new ram disc (is there really less logging?)
Put the logs, indices and important tables on a new SSD raid5
Put all other data on the old HDD-drives

Is this a good solution or would you suggest the standard solution tempdb on the SSDs as well?

Before you commit to a particular course of action, you first need to understand what is causing the poor performance.

Look at wait stats while the calculation process is underway.  Check this article by Paul Randal for an excellent starting point on how to gather and analyze them.

Use the system dmv, sys.dm_io_virtual_file_stats to understand the I/O requirements of your process.  I use this code, which shows the activity over a 10 minute period:

IF (COALESCE(OBJECT_ID('tempdb..#vfs_stats'), 0) = 0)
CREATE TABLE #vfs_stats
(
    run_num INT NOT NULL
    , database_id INT NOT NULL
    , file_id INT NOT NULL
    , sample_ms BIGINT NOT NULL
    , num_of_reads BIGINT NOT NULL
    , num_of_bytes_read BIGINT NOT NULL
    , io_stall_read_ms BIGINT NOT NULL
    , num_of_writes BIGINT NOT NULL
    , num_of_bytes_written BIGINT NOT NULL
    , io_stall_write_ms BIGINT NOT NULL
    , io_stall BIGINT NOT NULL
    , size_on_disk_bytes BIGINT NOT NULL
);

TRUNCATE TABLE #vfs_stats;

INSERT INTO #vfs_stats (run_num
    , database_id
    , file_id
    , sample_ms
    , num_of_reads
    , num_of_bytes_read
    , io_stall_read_ms
    , num_of_writes
    , num_of_bytes_written
    , io_stall_write_ms
    , io_stall
    , size_on_disk_bytes
)
SELECT 1
    , vfs.database_id
    , vfs.file_id
    , vfs.sample_ms
    , vfs.num_of_reads
    , vfs.num_of_bytes_read
    , vfs.io_stall_read_ms
    , vfs.num_of_writes
    , vfs.num_of_bytes_written
    , vfs.io_stall_write_ms
    , vfs.io_stall
    , vfs.size_on_disk_bytes
FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfs;

WAITFOR DELAY '00:10:00';

INSERT INTO #vfs_stats (run_num
    , database_id
    , file_id
    , sample_ms
    , num_of_reads
    , num_of_bytes_read
    , io_stall_read_ms
    , num_of_writes
    , num_of_bytes_written
    , io_stall_write_ms
    , io_stall
    , size_on_disk_bytes
)
SELECT 2
    , vfs.database_id
    , vfs.file_id
    , vfs.sample_ms
    , vfs.num_of_reads
    , vfs.num_of_bytes_read
    , vfs.io_stall_read_ms
    , vfs.num_of_writes
    , vfs.num_of_bytes_written
    , vfs.io_stall_write_ms
    , vfs.io_stall
    , vfs.size_on_disk_bytes
FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfs;

SELECT
    DatbaseName = d.name
    , FileName = mf.name
    , SampleTime_ms =   s2.sample_ms            - s1.sample_ms
    , Reads =           s2.num_of_reads         - s1.num_of_reads
    , BytesRead =       s2.num_of_bytes_read    - s1.num_of_bytes_read
    , IOStallRead =     s2.io_stall_read_ms     - s1.io_stall_read_ms
    , Writes =          s2.num_of_writes        - s1.num_of_writes
    , BytesWritten =    s2.num_of_bytes_written - s1.num_of_bytes_written
    , IOStallWRite =    s2.io_stall_write_ms    - s1.io_stall_write_ms
    , IOStall =         s2.io_stall             - s1.io_stall
    , GrowthOnDisk =    s2.size_on_disk_bytes   - s1.size_on_disk_bytes
FROM #vfs_stats s1
    INNER JOIN #vfs_stats s2 ON s1.run_num = (s2.run_num - 1)
        AND s1.database_id = s2.database_id
        AND s1.file_id = s2.file_id
    INNER JOIN sys.databases d ON s1.database_id = d.database_id
    INNER JOIN sys.master_files mf ON s1.database_id = mf.database_id
        AND s1.file_id = mf.file_id
ORDER BY d.name
    , mf.name;

You'll want to get an excellent understanding of the performance profile of the process that you can use as a baseline.  Once you do make changes, you can compare the performance profile after the changes to the baseline performance to understand what impact your change had.

Without understanding the performance profile you will be shooting in the dark by throwing hardware at the problem.  However, this can be a good strategy if you know you have underperforming hardware, such as a the data files on old 7200 rpm spinning rust, or old Xeon processors.  As a total off-the-cuff recommendation, you could try putting all the databases (tempdb, and the data database) onto a nice fast SSD, preferably a PCIe SSD, and making sure you have enough processor speed and memory bandwidth.


How do I configure AWS Aurora to separate write/read operations I want to migrate my database instance from AWS RDS MySQL to Aurora, but I have a doubt about replication and how Aurora management the write/read operations.

I have my application and I want to separate the write operations from the reads. I want to create a Master instance only for write operations and other instance  (Read Replica) only for read operations.

The problem is here, I read on AWS documentation that I need to do this separation on my Application and I think or I hope to find a way to do that and be transparent for my Application. I draw a simple schema do I have to do to get with Aurora (from AWS):

What AWS says do I have to do:

      -----------------
      |  Application  |
      -----------------
       |             |
       | writes      |reads
       |             |
------------      ------------------
|  Master  |      |  Read Replica  |
------------      ------------------
       ^            ^
       |replication |
       |____________|

What do I need:

I need the Master always keep with the write operations and it redirects the read to the Read Replica instance.

      -----------------
      |  Application  |
      -----------------
       |
       |write/read
       |
------------   Reads    ------------------
|  Master  | <------->  |  Read Replica  |
------------            ------------------
       ^                     ^
       |      replication    |
       |_____________________|

The replication is always running. But I want to separate the write and read process. So  my summary:

The master instance detecte the different between writes/reads operations and all read operation will be manage by Read Replica. I need this solution because aurora offer a good features to improve my RDS, but the only problem is that I need to create a balance between write  and read operations: The write operation be processed in the master and it send the read operation to the Read Replica. I don't want to define this process and make selection between them in my application code as Amazon propose.

AFAIK, you're right that AWS RDS Aurora (a MySQL 5.6 fork) does not support automatic or transparent read/write splitting:
http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Aurora.html

In order to do that in a way that's completely transparent to the application, you would need an intermediate proxy. Your application would then always connect to the proxy, the proxy would then have to do packet inspection to examine each incoming query to determine if it's read-write,  which then gets forwarded on to the master, or read-only, which can then get forwarded to any of N replicas.

Be aware that this has some notable implications:
1. This means that the proxy needs to understand the MySQL protocol
2. It needs to inspect each packet (query) and determine if it's RW or RO
3. It then needs to forward the query to the appropriate backend MySQL instance
4. It likely needs to keep track of each connection, maintaining a map of front-end connections between your app and the proxy, and backend connections to the mysqld instances. The front-end connection would remain stable, but the backend connection could change for each query.
5. You can potentially have some state issues as a result. For example, when you start an explicit transaction, create temporary tables, or set session variables in your connection... those could get lost when (transparently) switching backends.
6. This will have an impact on SSL and other security measures, as you are explicitly using a MITM
7. All of this typically adds quite a bit of overhead, and you will typically see noticeable query latency because of it.

This is a feature that we hope to have in the MySQL Router (the replacement for the old MySQL Proxy: http://mysqlhighavailability.com/mysql-router-on-labs-the-newest-member-of-the-mysql-family/), but we do not yet. It takes a lot of time and effort to do it properly, so as to minimize the effects noted above. One such proxy that does support that today is ProxySQL:
http://www.proxysql.com   (See the "Read write split" section)

You can grab the source and start playing with it here: https://github.com/renecannao/proxysql

Good luck!


How much memory is needed to keep a table cached? (SQL Server 2008) I run long and complex queries which mostly use one big table - 8GB, 40M rows. AFAIK, all/most rows are used in each query. I'm seeing lots of IO in the activity monitor - for the first query and for every subsequent one. The server is currently using 6.5GB of memory and I want to upgrade. Question is, how much memory is needed to avoid all these disk reads? Is it in the ballpark of the size of the table or more?

This is the SET STATISTICS IO output. BigTable is the one I'm asking about, SmallTable has a 1-to-many relation with BigTable. #entrance holds the output of the query (several hundred rows of output).

Table 'SmallTable'. Scan count 249005, logical reads 2829948, physical reads 2605, read-ahead reads 10395, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'BigTable'. Scan count 194004, logical reads 13482115, physical reads 33841, read-ahead reads 1181136, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#entrance__000000000023'. Scan count 0, logical reads 1568, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

This is a two part answer.

The first part is buy as much RAM as you can afford (as long as it'll fit into the server).

The second part is that you'll always see reads on data.  The trick is to see if those reads are physical or logical.  Physical reads are going down to the disk.  Logical reads are read from the buffer pool in memory.  You can see this by putting "SET STATISTICS IO ON" at the top of the SQL Script then running it in SSMS.  On the messages tab you'll get specific IO information for each query that is being run, specifically you'll get the number of both logical and physical operations (reads in your case) that are happening on a table by table basis.  If the physical read numbers are pretty high (these numbers are basically the number of blocks that were read so multiple by 8 then divide by 1024 to get a number in Megs) and then you need more RAM.  If the logical read numbers are high, then you aren't actually going to disk but your query may need tuning, indexes created, etc.

(Note: For the purposes of this answer, I'm assuming SELECT statements only.  INSERT/UPDATE/DELETE operations are a little more complex, but the same basics apply.)


Availabilty Groups vs. Log Shipping Assuming that an organization has everything needed to use Availability Groups successfully, and that they have been using Availability Groups successfully for a few years, are there any reasons why log shipping would be a better DR solution?  It seems that people are still putting in log shipping just because it can meet the RPO and RTO.  But if it doesn't cost anything extra to use an AG instead, and the DBA staff has experience with AGs, why would log shipping be better?

The only thing I can think of is that the built-in delay of log shipping allows one to recover quickly in the case that someone forgot to put a WHERE clause on an UPDATE or DELETE statement--if it's caught in time.  Unlikely, and not something we're concerned about.

My general experience is that log shipping requires more intervention and tweaking as the database load increases, is more difficult to fail over, and is generally a bit of a Rube Goldberg machine (albeit a quite successful specimen of that class of machine).

A few benefits come to mind:

Log shipping is simpler to set up (or re-initialize), as it doesn't require WSFC and some of the other AG requirements. I'm not sure what intervention and tweaking you are talking about, though I will agree that there is no automatic failover mechanism. I disagree with the Rube Goldberg assessment - it's a bit of repetitive restore statement logic that piggybacks off of log backups you're already taking (right?), it's really not that complex at all. I think the fact that there's a wizard leads people to believe there's a bunch of magic behind the scenes, but take a look at the output of that wizard and tell me what's complex about it. :-)
You can ship logs to as many secondaries as you like (and again these can be instances anywhere, with or without Windows clustering, in any domain or not in a domain at all, etc). And, as you mentioned, on whatever delay makes sense.
None of the overhead on the primary caused by hardening logs or facilitating other activity on the secondaries. Since you're taking log backups anyway (again, right?), log shipping causes no additional load on the primary at all. And read-only workloads against the log shipped copy don't impact the primary at all.

I blogged about using log shipping as a replacement for AGs, which might have some additional insight:

Readable Secondaries on a Budget

Of course there are a bunch of advantages AGs have over log shipping, but that wasn't really your question. :-)


Does DB2 support host based access control? Coming from MySQL I was wondering whether DB2 has something similar to MySQL's user@host access rights concept.

By default DB2 delegates all authentication to the operating system of the server, so the "host" is always the same -- the DB2 server host and the authenticated user ID does have the "host" component and all privileges are verified against the user ID only.

Depending on what you are actually trying to achieve, you may be able to restrict access to certain actions or objects via custom authentication plugin, workload manager controls, or row/column access control mechanisms.

Edit

Thanks to @IanBjorhovde for reminding us of yet another option: trusted contexts (see tutorial), although it still is not an exact match. Even though you can restrict clients with certain IPs from connecting to the database, authentication is still performed at the server, so it's not exactly "user@remote_host" but rather "user@server_host connecting from remote_host".


PAL Report - 64-bit Windows Server 2008 R2, issues I don't know anything about SQL Server, however I found the following issues while running the PAL report. It seems that RAM is the primary issue and it has to be increased.

Kindly suggest.

Critical:

Less than 5 percent of RAM is available or less than 64 MB of RAM is available
Less than 500 MB of free disk space
(Total Latch Wait Time) / (Latch Waits/ Sec) < 10

Warning:

A ratio of more than 1 workfile created for every 20 batch requests
A ratio of more than 1 page lookup for every 100 batch requests
A ratio of more than 1 SQL Full Scan for every 1000 Index Searches
Lock Requests/sec Greater than 1000
Deprecated Usage - Near 0
Log Shrinks Near 0
The working set of all processes combined is increasing at a rate greater than 100MBs per hour
Possible Handle Leak: More than 250 handles between overall Min and overall Max and an increasing trend of more than 100 handles per hour
Less than 90% Worktables from Cache Ratio

Less than 5 percent of RAM is available or less than 64 MB of RAM is available

  Less than 500 MB of free disk space

  (Total Latch Wait Time) / (Latch Waits/ Sec) < 10

Looking at first two alerts it says that SQL Server might be facing memory crunch. It would be better to determine using perfmon counters. I use below counters to check how much memory SQL Server needs. Taken from Here

SQLServer:Buffer Manager--Buffer Cache hit ratio(BCHR):

If your BCHR is high 90 to 100 Then it points to fact that You don't have memory pressure. Keep in mind that suppose somebody runs a query which request large amount of pages in that case momentarily BCHR might come down to 60 or 70 may be less but that does not means it is a memory pressure it means your query requires large memory and will take it. After that query completes you will see BCHR risiing again

SQLServer:Buffer Manager--Page Life Expectancy(PLE):

PLE shows for how long page remain in buffer pool. The longer it stays the better it is. Its common misconception to take 300 as a baseline for PLE.   But it is not,I read it from Jonathan Kehayias book( troubleshooting SQL Server) that this value was baseline when SQL Server was of 2000 version and max RAM one could see was from 4-6 G. Now with 200G or RAM coming into picture this value is not correct. He also gave the formula( tentative) how to calculate it. Take the base counter value of 300 presented by most resources, and then determine a multiple of this value based on the configured buffer cache size, which is the 'max server memory' sp_ configure option in SQL Server, divided by 4 GB.   So, for a server with 32 GB allocated to the buffer pool, the PLE value should be at least (32/4)*300 = 2400. So far this has done good to me so I would recommend you to use it.

SQLServer:Buffer Manager--CheckpointPages/sec:

Checkpoint pages /sec counter is important to know about memory pressure because if buffer cache is low then lots of new pages needs to be brought into and flushed out from buffer pool,  due to load checkpoint's work will increase and will start flushing out dirty pages very frequently. If this counter is high then your SQL Server buffer pool is not able to cope up with requests coming and we need to increase it by increasing buffer pool memory or by increasing physical RAM and then making adequate changes in Buffer pool size. Technically this value should be low if you are looking at line graph in perfmon this value should always touch base for stable system.

SQLServer:Buffer Manager--Freepages:

This value should not be less you always want to see high value for it.

SQLServer:Memory Manager--Memory Grants Pending:

If you see memory grants pending in buffer pool your server is facing SQL Server memory crunch and increasing memory would be a good idea. For memory grants please read this article: http://blogs.msdn.com/b/sqlqueryprocessing/archive/2010/02/16/understanding-sql-server-memory-grant.aspx.

SQLServer:memory Manager--Target Server Memory:

This is amount of memory SQL Server is trying to acquire.

SQLServer:memory Manager--Total Server memory

This is current memory SQL Server has acquired.

Few Points

1.If Target server memory is greater than Total server memory there can be memory pressure. Let me put emphasis on word can be ,it is not a sure shot signal.Please refer to this MSDN forum thread where OP had target server memory greater than total server memory but because there were no memory grants pending ,and page life expectancy was high so there was no memory pressure .

Generally on stable system these 2 values are equal.
Free Pages counter is removed from SQL Server 2012. And also its value does not holds importance as the values for BCHR,PLE,Target server memory and Total Server  memory

The last one is number of latches which could not be granted immediately. Are you seeing queries going to suspended state. Latches are common and necessary they are lighter form of locks. Did you checked top waits does they include latch_XX as top wait types


Return both one value and one column from pl/pgsql function I wish my pl/pgsql function to go fetch and return two informations in my database in a single connection:

one vector containing data whose type is unknown at call time (an array)
one string value containing information as how to interpret this data

Here is an illustration of the function architecture:
What should I use as a return type / return value / return instruction so both these informations are returned?

CREATE OR REPLACE FUNCTION public.retrieve_vector(
    vname bigint)  -- the vector name: actually a column name
    RETURNS RECORD -- * * * is that the return type I need?  * * *
    LANGUAGE 'plpgsql'
    VOLATILE
AS $BODY$
DECLARE
    info text; -- data interpretation information
BEGIN

-- Retrieve data information from, say, the such-information table
SELECT info FROM public."InfoTable"
    WHERE name = vname
    INTO info; -- (this is actually a more complicated process)

-- Retrieve the vector and return both
RETURN info,
      (SELECT vname FROM public."Vectors"); -- (this is also more complicated)
-- * * * but RETURN a, b; is not possible like this, right? How to do? * * *

END
$BODY$;

As dummy instances, I expect this function to return something like retrieve_vector('temperatures') -> ['old thermometer', [0, 1, 5, -3]] or retrieve_vector('words') -> ['small words only', ['at', 'the', 'and', 'or']].
(To those who might ask: in reality this is about meta-programming, so the "info" is actually refined type information.)

In such a situation, I think that:

it is legitimate that this function returns two values
it is legitimate that the vector type is unknown at call time
it is legitimate that the first value is scalar (single value) and the other is a vector (a column)
it is legitimate wanting to connect the database only once

Is it possible defining a function with such a flexible signature?
How would I do this?

but RETURN a, b; is not possible like this, right? How to do?

Right, not like this. But this is possible:

CREATE OR REPLACE FUNCTION public.retrieve_vector(
   _vname bigint
 , OUT _info text       -- "data interpretation information"
 , OUT _arr  text[]     -- "vector"
   )
  RETURNS record AS     -- that is the return type you need
$func$
BEGIN
   -- Retrieve data information
   SELECT info
   FROM   public."InfoTable"
   WHERE  name = _vname
   INTO   _info;        -- actually a more complicated process

   -- Retrieve "vector"
   SELECT vname         -- you may need explicit casting to text[] ...
   FROM   public."Vectors"
   INTO   _arr;         -- also actually more complicated

   -- RETURN;           -- optional
END
$func$  LANGUAGE plpgsql;

Don't quote the language name plpgsql.

Assuming you want to return a single row. For returning 0-n rows (a set) you would use RETURNS TABLE(_info text,_arr text[]) in combination with RETURN NEXT or RETURN QUERY or similar. See:

Difference between return next and return record

The term "vector" in your question does not help to clarify which actual data types we are dealing with. You also mention "array". Any Postgres array can be cast to text[], so I use that type as common denominator. PL/pgSQL SELECT INTO coerces compatible types into the target data typesd automatically. Else you may need to add an explicit cast like source_array_column::text[]. Not enough info in your question to narrow this down.

You asserted that:

  type is unknown at call time

Else you might be able to work with a polymorphic array type as well. See:

Refactor a PL/pgSQL function to return the output of various SELECT queries


One field vs multiple fields to reference ID values of dependent information This question is in the context of adding a new field or fields to a table. - Specifically in my following example, whether equipment's column to reference make-model.ID should be one field or two.
Say, there exists a table named make-model that stores dependent information.

ID
Name
Parent

1
Acme
NULL

2
1000
1

3
Anvil
1

4
Omni
NULL

5
1000
4

6
Photocell
4

This table is meant to represent a category/subcategory relationship that can be represented as:
Acme
- 1000
- Anvil
Omni
- 1000
- Photocell

Notice that there are two 1000 models, but they belong to different makes, this is why the ID column is the key.
There is also a table named equipment that stores information about the equipment, including the make/model.
Based on user input, the breed is set and could be specific to Anvil or stop at Acme. (Theoretically this could be more than two levels deep, but I'm keeping it at two for this question.)
In the program that the user uses to view/modify this data, they will see two fields, one that would show the higher level (Acme) and one that would show the more specific level (Anvil).
As far as actual columns on the Equipment table, what is the benefit of one column storing the most specific id given (3 if Anvil, 1 if Acme) or two columns, one for each level?
So, for example
Table A:

ID
Name
Make-Model

1
Max
2

2
Spot
3

3
Fluffy
4

Vs. Table B:

ID
Name
Make
Model

1
Max
1
2

2
Spot
1
3

3
Fluffy
4
NULL

My question is about the benefit of Table A's design vs. Table B's design
As far as getting the information, a simple enough query can find the parent of any given id or determine if it doesn't have any level above it - so the information is accessible either way.
The column won't store the pretty name, only the numerical id referencing the breeds table, so readability in the database isn't too much a factor.

Based on your update I understand your question better now. Table A is the more scalable, maintainable, and normalized solution that will lead to less data redundancy.
You can always join to the Breeds table to get the Parent Breed when needed (which may not always be the case). And if you ever have a reason to add a 3rd level (subcategory of a subcategory) then you don't need to change your schema at all to support this when going with Table A (and the Breeds Table) schema (as opposed to Table B where you'd need to add a 3rd column for Level 3, etc which defeats most of the purpose of the Breeds table). (E.g. You can get any level of relationships with a recursive query when using Table A schema.)

Based on my understanding of your schema, I think your Table structure is fine for the Parent-Child relationship as is. I just find the naming slightly confusing since Dog vs Lab isn't exactly the same thing, so to see them objectified the same way as "Breed" feels a little off.
Alternatively, as someone who is seeing the Table for the first time, I'd expect this table to have things like "Shepard" as the Parent under the Breed column and "German" and "Australian" as the children, since they're all breeds. And things like Dog and Cat would be in a different field called AnimalType.
But that's probably more my subjective opinion, I think for your goal this Table design makes sense, and maybe just thinking about a slightly different name for the "Breed" column could be a small change.


How to set minimal token length for to_tsvector() function? I parse xml documents with to_tsvector() function, and, sometimes, it produces tokens, which are shorter than 3 characters:

'1':89,91 '2019':14 '25':4

I know, that

to_tsvector([ config regconfig, ] document text) returns tsvector

accepts config as first param, but i can't find solution for setting a minimal token length here. Is there any way to do it?

Tokens are initially produced by the full text search parser bound to the text configuration. The default text search parser that ships with PostgreSQL is not configurable (it can be replaced by a custom parser, though).

After being output by the parser, tokens can be filtered out with dictionaries. It's relatively easy to create a dictionary that filters out short words, but it takes the form of a couple functions written in the C language.
As an example, here's a blog post explaining how to write a custom dictionary to filter out long words: Text search: a custom dictionary to avoid long words.

If there are only a reasonable number of short words that you want to filter out, another option would be to enumerate them all in a stop-words file and use the built-in simple dictionary. From the documentation:

  The simple dictionary template operates by converting the input token
  to lower case and checking it against a file of stop words. If it is
  found in the file then an empty array is returned, causing the token
  to be discarded. If not, the lower-cased form of the word is returned
  as the normalized lexeme


Postgres ignoring index on join, but all planner estimates are correct? I've got a query that looks like this:

SELECT *
FROM (
    SELECT *
    FROM (VALUES ('abc123')) AS user_ids(user_id)
) AS user_ids(user_id)
LEFT JOIN users ON users.id = user_id

But instead of using the index on users.id, the query planner opts for a seq scan on the ~2.5M row users table:

                                QUERY PLAN
--------------------------------------------------------------------------
 Hash Right Join  (cost=0.03..400966.32 rows=1 width=1065)
   Hash Cond: ((users.id)::text = ('abc123'::text))
   ->  Seq Scan on users  (cost=0.00..391452.66 rows=2536966 width=1033) <-- HERE
   ->  Hash  (cost=0.02..0.02 rows=1 width=32)
         ->  Result  (cost=0.00..0.01 rows=1 width=32)

Notice that the estimates (ie, on the size of the users table, and that exactly one row will be returned) are correct.

Also, Postgres will use the index on users.id (a unique primary key index) for other queries:

=> EXPLAIN SELECT *
-> FROM users
-> WHERE id IN ('abc123');
                                 QUERY PLAN
-----------------------------------------------------------------------------
 Index Scan using users_pkey on users  (cost=0.56..8.57 rows=1 width=1033)
   Index Cond: ((id)::citext = 'abc123'::citext)

The problem is that the string literals in the subquery / VALUES statement are of type text, while the users.id column is of type citext, and the citext index can't be used when comparing citext = text.

When the input type is citext the index is used:

=> explain select * from users where hash = 'abc123'::citext;
                                 QUERY PLAN
-----------------------------------------------------------------------------
 Index Scan using users_pkey on users  (cost=0.56..8.57 rows=1 width=1033)
   Index Cond: ((hash)::citext = 'abc123'::citext)
(2 rows)

But the index isn't used when the input type is text:

=> explain select * from users where hash = 'abc123'::text;
                          QUERY PLAN
--------------------------------------------------------------
 Seq Scan on users  (cost=0.00..397795.08 rows=1 width=1033)
   Filter: ((hash)::text = 'abc123'::text)
(2 rows)


Log shipping 2008 r2 SP2 server to a 2008 r2 SP3 CLUSTER We have recently configured one database to be replicated with log shipping in production, using SQL Server 2008 R2.

We have up and running more than 150 database replicas without any problems. But now, when we try to configure the new LS replica in a new server environment we have experiencing one error:

The log shipping is configured from a 2008 r2 SP2 server to a 2008 r2 SP3 CLUSTER (the only difference compared with our other replicas)

the LS backup works Ok in the primary
the LS Copy works OK and successfully copy the files from the primary to the secondary
the LS Restore restores one, two or three trn files,  and than it fails:

  Restored log backup file.

  Secondary DB: 'xxxxyyyy_mirror' File: 'P:\ xxxyyyy _TLogs_Mirror_LS\xxxyyyy\ xxxyyyy _20151120163351.trn'

  The restore operation was successful. Secondary Database: ' xxxyyyy _mirror', Number of log backup files restored: 1

The next job execution:

  Error: An error occurred while processing the log for database ' xxxyyyy _mirror'.  If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.

  RESTORE LOG is terminating abnormally.
  Processed 0 pages for database xxxyyyy _mirror', file 'Vitacare2k6_Dev' on file 1.

  Processed 1 pages for database ' xxxyyyy _mirror'
  file 'Vitacare2k6_Dev_log'
  on file 1.

  (Dot Net SqlClient Data Provider)

And then the database gets into Suspect Mode
the files (MDFs, LDFs and TRNs) are in an Clustered Disk shared by the cluster nodes.

I tried to restore the TRNs in the same full backup from the cluster my PC and I get no errors.

Can you guys provide me any guidance on where I could troubleshoot this further, I would greatly appreciate it.
Thanks for your time and input.
Rafael

Notes: I tried to restore the files in other instance (my Laptop) and i get no error, so i do not believe in corruption
I also tested in restore the Sp2 to an SP2 instance manually and sp2 to sp3 instance with no errors.

Logshipping is a death tested technique that has been around since many ages.

Looking at your specific error sequence on secondary server

The log restores errors out after a couple of successful restores.
The database goes into suspect mode
you tried to restore the log files and they restore it fine, so the log files are not corrupt.

You should check if the Bytes per Physical Sector on secondary server for the disk that you are trying to restore is NOT 512 bytes.

You can install the Hotfix for SQL Server 2008 R2 SP2 or correct the disk bytes per physical sector.


Recover database until cancel without prompt? The statement:

RECOVER DATABASE UNTIL CANCEL

prompts once to select the archive log file. Is there a way to pre specify the value so as to be able to use it in a script without prompt?

I have tried:

RECOVER AUTOMATIC DATABASE UNTIL CANCEL

But it still does prompt.


In PSQL, is it possible to insert rows in two different tables with not-nullable foreign key columns referencing each other? I want to insert a row into two tables that reference each other using not-nullable foreign key columns.

Let's say I have table A named make

make_id, bigint, (NOT NULL)
name, text
first_model_id, bigint, (NOT NULL) references model.model_id

And a table B named model

model_id, (NOT NULL)
name, text
make_id, (NOT NULL) references make.make_id

Is it possible within a transaction to insert a row into both of these tables, even though each of the rows does not exist? This is easily doable by making one of the columns NULL, inserting the first row, referencing it on the second insert, then getting the new row ID from the second insert.

I'm wondering if this is possible with columns that are NOT NULL.

Assuming the primary key columns are defined as serial (or bigserial) you can first get the new IDs using nextval() and then use them in the subsequent inserts.

This can either be done separate statements:

select nextval(pg_get_serial_sequence('make','make_id'));
select nextval(pg_get_serial_sequence('model','model_id'));

insert into make
  (make_id, name, first_model_id)
values (
  currval(nextval(pg_get_serial_sequence('make','make_id'))),
  'Make One',
  currval(pg_get_serial_sequence('model','model_id'))
);

insert into model
  (model_id, name, make_id)
values (
  currval(pg_get_serial_sequence('model','model_id')),
  'Make One',
  currval(nextval(pg_get_serial_sequence('make','make_id')))
);

or a single statement using a data modifying CTE

with new_ids as (
   select nextval(pg_get_serial_sequence('make','make_id')) as new_make_id,
          nextval(pg_get_serial_sequence('model','model_id')) as new_model_id
), new_make as (
  insert into make (make_id, name, first_model_id)
  select new_make_id, 'Make One', new_model_id
  from new_ids
)
insert into model (model_id, name, make_id)
  select new_model_id, 'Model 1', new_make_id
from new_ids;


Order By DateTime With 00:00:00 Last order by datetimecolumn orders a datetime column like this:

datetimecolumn
2014-09-04 00:00:00
2014-09-04 00:00:00
2014-09-04 01:00:00
2014-09-05 00:00:00
2014-09-05 01:00:00
2014-09-05 02:00:00

How can I order the data so the the values with 00:00:00 are last, but still order the values ascending by time like so:

datetimecolumn
2014-09-04 01:00:00
2014-09-04 00:00:00
2014-09-04 00:00:00
2014-09-05 01:00:00
2014-09-05 02:00:00
2014-09-05 00:00:00

Dumb business rule, I know I know... basically if "user" does not spec a time, the time does not matter (so it goes to the bottom of the list).

First order by just the date, then prioritize the values where the timestamp is not midnight over those where it is.

ORDER BY CONVERT(DATE, col),
  CASE WHEN CONVERT(DATE,col) = col
  THEN 2 ELSE 1 END,
  CONVERT(TIME, col);


SQL Server 2008 R2 Edition Upgrade Process I have several SQL Server 2008 R2 Standard Edition instances that we need to upgrade to SQL Server 2008 R2 Enterprise Edition.

If I have valid SQL Server 2017 Enterprise Edition (with SA) PIDs, do I need to have SQL Server 2008 R2 Enterprise Edition media to complete the upgrade?

Please don't recommend that I upgrade to SQL Server 2017 - it is not an option at this time. It has taken years of pleading with management to upgrade to a newer version, and 2+ staff years for regression testing. It has taken an order of magnitude increase in Transaction Rate/Database size to even get the edition upgrade approved.

The question is how to perform the edition upgrade, not what the licensing implications are.

Our thought was that running SQL Server Installation Center and choosing Maintenance -> Edition Upgrade might work since the basic bits are probably the same between editions.  My guess is that the only differences are some run-time switches, configuration values, etc. so the Enterprise Edition media might not be required?

You will need to use SQL Server 2008 R2 Enterprise Edition media to perform the in-place edition upgrade.  I am unaware of any option available that allows you to perform an edition upgrade without a version upgrade when using media from a different SQL Server version.

Will your licensing agreement cover this?  That wholly depends on your licensing (though I highly suspect it won't), but that question is best sent to your license partner/reseller.

If you have a MSDN license, you may still be able to pull down the binaries for SQL Server 2008 R2, so that would be one way to get the required media.


Is there any good tool for making Database Design and Prototypes? I would like to have a good tool for designing the Database Schema with all the tables, columns, data types and relations. Today I mostly do this with pen and paper but I would like to do it in a good design tool.

Is there any good (and maybe free) Database Design tool?

I started using NORMA (link fixed Oct 2011) which is useful to capture the relationships and constraints in plain English. See Object Role Modelling too.

This will generate XSDs and SQL scripts.

I hope never to see an ERD ever again...


Fast recovery vs Accelerated database recovery in SQL server 2019 while comparing the features available in different SQL server editions, i see that Fast recovery and Accelerated Database recovery are 2 separate features.
I am unable to find any documentation specific for Fast recovery, and other online blogs seem to use both features interchangeably.
Does anyone know how exactly are they different?
The screenshot is captured from Editions and supported features of SQL Server 2019 (15.x)

Here's the excerpt from the Recovery and the transaction log documentation that describes the Fast recovery feature:

Note
To maximize the availability of databases in an enterpirse
environment, SQL Server Enterprise Edition can bring a database online
after the Redo Phase, while the Undo Phase is still executing. This is
known as Fast Recovery.


Comparing Columns that can contain NULLS - is there a more elegant way? I know that you can't compare a value to NULL and expect a result without adding something like in the following code...

SELECT
    *
FROM
    A INNER JOIN
    B ON A.ID = B.ID
WHERE
    A.STRING <> B.STRING OR (A.STRING IS NULL AND B.STRING IS NOT NULL) OR (A.STRING IS NOT NULL AND B.STRING IS NULL) OR
    A.DT <> B.DT OR (A.DT IS NULL AND B.DT IS NOT NULL) OR (A.DT IS NOT NULL AND B.DT IS NULL) OR
    A.B <> B.B OR (A.B IS NULL AND B.B IS NOT NULL) OR (A.B IS NOT NULL AND B.B IS NULL) OR
    A.NUM <> B.NUM OR (A.NUM IS NULL AND B.NUM IS NOT NULL) OR (A.NUM IS NOT NULL AND B.NUM IS NULL)

My question is:

Is there a more elegant way to test for changed values across the two tables where either one could be null?

Solution needs to work uniformly across the data types.

Here is the code to set up the test tables...

CREATE TABLE A
(
    ID INT IDENTITY(1,1) NOT NULL,
    STRING VARCHAR(20) NULL,
    DT DATETIME NULL,
    B BIT NULL,
    NUM INT NULL
)

CREATE TABLE B
(
    ID INT IDENTITY(1,1) NOT NULL,
    STRING VARCHAR(20) NULL,
    DT DATETIME NULL,
    B BIT NULL,
    NUM INT NULL
)

INSERT INTO A (STRING, DT, B, NUM) VALUES ('TEST', '2012-03-16 16:39:04.893', 0, 23)
INSERT INTO A (STRING, DT, B, NUM) VALUES (NULL, '2012-03-16 16:39:04.893', 0, 23)
INSERT INTO A (STRING, DT, B, NUM) VALUES ('TEST', NULL, 0, 23)
INSERT INTO A (STRING, DT, B, NUM) VALUES ('TEST', '2012-03-16 16:39:04.893', NULL, 23)
INSERT INTO A (STRING, DT, B, NUM) VALUES ('TEST', '2012-03-16 16:39:04.893', 0, NULL)
INSERT INTO A (STRING, DT, B, NUM) VALUES ('TEST', '2012-03-16 16:39:04.893', 0, 23)
INSERT INTO A (STRING, DT, B, NUM) VALUES ('TEST', '2012-03-16 16:39:04.893', 0, 23)
INSERT INTO A (STRING, DT, B, NUM) VALUES ('TEST', '2012-03-16 16:39:04.893', 0, 23)
INSERT INTO A (STRING, DT, B, NUM) VALUES ('TEST', '2012-03-16 16:39:04.893', 0, 23)
INSERT INTO A (STRING, DT, B, NUM) VALUES ('TEST', '2012-03-16 16:39:04.893', 0, 23)

INSERT INTO B (STRING, DT, B, NUM) VALUES ('TEST', '2012-03-16 16:39:04.893', 0, 23)
INSERT INTO B (STRING, DT, B, NUM) VALUES ('TEST', '2012-03-16 16:39:04.893', 0, 23)
INSERT INTO B (STRING, DT, B, NUM) VALUES ('TEST', '2012-03-16 16:39:04.893', 0, 23)
INSERT INTO B (STRING, DT, B, NUM) VALUES ('TEST', '2012-03-16 16:39:04.893', 0, 23)
INSERT INTO B (STRING, DT, B, NUM) VALUES ('TEST', '2012-03-16 16:39:04.893', 0, 23)
INSERT INTO B (STRING, DT, B, NUM) VALUES ('STAGE', '2012-03-16 16:39:04.893', 0, 23)
INSERT INTO B (STRING, DT, B, NUM) VALUES ('TEST', '2555-11-11 00:00:00.000', 0, 23)
INSERT INTO B (STRING, DT, B, NUM) VALUES ('TEST', '2012-03-16 16:39:04.893', 0, 23)
INSERT INTO B (STRING, DT, B, NUM) VALUES ('TEST', '2012-03-16 16:39:04.893', 0, 23)
INSERT INTO B (STRING, DT, B, NUM) VALUES ('TEST', '2012-03-16 16:39:04.893', 0, 999)

You can use this approach from Paul White's article Undocumented Query Plans: Equality Comparisons

SELECT *
FROM   A
       INNER JOIN B
         ON A.ID = B.ID
            AND EXISTS(SELECT A.*
                       EXCEPT
                       SELECT B.*)


Measure time duration between events, partitioned by hours I have the following table in PostgreSQL 9.4, which persists users log on/off events (log on is event_type 1, log off is event_type 0).

CREATE TABLE user_online_offline_events (
  id serial,
  user_id int4,
  event_type int4,
  created_at timestamp
);

Sample data:

INSERT INTO user_online_offline_events
       (id, user_id, event_type, created_at)
VALUES (1,  123,     1,         '2015-10-07 12:15:00'),
       (2,  123,     0,         '2015-10-07 12:25:00'),
       (3,  123,     1,         '2015-10-07 12:45:00'),
       (4,  123,     0,         '2015-10-07 13:10:00');

I'd like to calculate the number of minutes each user was logged-on per hour:

| id | user_id |     time | minutes logged on |
+----+---------+----------+-------------------+
|  1 |     123 | 12:00:00 |                30 |
|  2 |     123 | 13:00:00 |                10 |

This is my WIP version. It's not really elegant, it still has the hour hard coded in, ignores user_ids and assumes online-offline events are consecutive:

select time, sum(minutes) / 60 as minutes from (
  SELECT
    date_trunc('hour', time) as time,
    CASE
    WHEN event_type = 0 AND lag(event_type, 1) OVER w = 1
      THEN
        extract(EPOCH FROM time - lag(time, 1) OVER w)
    WHEN event_type = 0 AND lag(event_type, 1) OVER w ISNULL
      THEN
        extract(EPOCH FROM time - date_trunc('hour', created_at))
    WHEN event_type = 1 AND lead(event_type, 1) OVER w ISNULL
      THEN
        extract(EPOCH FROM date_trunc('hour', time) + INTERVAL '1 hour' - time)
    ELSE 0
    END AS minutes
  FROM user_online_offline_events
  WHERE date_trunc('hour', time) = '2015-10-07 12:00:00'
  WINDOW w AS ( ORDER BY time )
  ORDER BY time
) m group by time;

How to do this properly?

This is more sophisticated than I first understood. One way to handle it is with generate-series(), range types and associated functions and operators:

SELECT user_id, hour, sum(upper(min_on) - lower(min_on)) AS minutes_on
FROM  (
   SELECT user_id, hour, u.range * h.range AS min_on
   FROM  (
      SELECT hour, tsrange(hour, hour + interval '1h') AS range
      FROM   generate_series('2015-10-07 00:00'::timestamp  -- defines range of interest
                           , '2015-10-08 03:00'::timestamp  -- cut off the rest
                           , interval '1h') hour
      ) h
   JOIN  (
      SELECT user_id, event_type
           , tsrange(created_at
                   , lead(created_at) OVER (PARTITION BY user_id ORDER BY created_at)
                    ) AS range
      FROM   user_online_offline_events
      -- add WHERE conditions to limit selection
      -- careful with cutting off leading "on" / trailing "off" events
      ) u ON u.event_type = 1 AND u.range && h.range
   ) sub
GROUP  BY  user_id, hour
ORDER  BY  user_id, hour;

SQL Fiddle with extended data.

Major points

Define the outer time frame in generate_series() once. The function generates one row per hour.
Minutes per hours of the day in multiple days are summed up - if the outer time frame spans multiple days.
Hours without any online time are not in the result. If you need that use LEFT JOIN instead of JOIN
Using the overlap operator && for ranges to identify matching hours and the intersection operator * to calculate actual overlaps.
Resulting times are given as interval. Since multiple days might be summed, the result can be more than one hour and a simple EXTRACT() would not capture that. You can extract minutes  as integer number this way:

EXTRACT('epoch' FROM sum(upper(min_on) - lower(min_on))) / 60 AS minutes_on

Basic timestamp ranges are computed naively with the window function lead(). This assumes that every log-on is followed by a log-off, which typically misses special cases:

Multiple log-on / log-off events for the same user in a row
No matching log-on / log-off

You need to define exactly which corner cases can exist and how you want to handle them. There is a solution either way.


Execution time of a stored procedure I have a stored procedure and several statements in it. The below is execution time statistics of the procedure:

  SQL Server parse and compile time:     CPU time = 0 ms, elapsed time =
  0 ms. SQL Server parse and compile time:     CPU time = 0 ms, elapsed
  time = 0 ms.

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

  (1 row(s) affected)

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

  (29 row(s) affected)

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

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

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

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

  (1 row(s) affected)

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

  (1 row(s) affected)

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

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

As you can see the total of 4562 ms. But when I look at the status bar I see that it took 2 seconds. I expect that it should be 4 seconds. Why are these two different?

This happens because SET STATISTICS TIME ON will give you summarized time results for a stored procedure in addition to time results for each query that's part of the stored procedure. Let's run a simple test to try to reproduce what you saw. The following code isn't important except that it runs for a few seconds on my machine:

set statistics time on;

WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),
        E02(N) AS (SELECT 1 FROM E00 a, E00 b),
        E04(N) AS (SELECT 1 FROM E02 a, E02 b),
        E08(N) AS (SELECT 1 FROM E04 a, E04 b),
        E16(N) AS (SELECT 1 FROM E08 a, E08 b),
        E32(N) AS (SELECT 1 FROM E16 a, E16 b),
   cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E32)
SELECT AVG(CAST(N AS BIGINT))
FROM cteTally
WHERE N <= 10000000;

WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),
        E02(N) AS (SELECT 1 FROM E00 a, E00 b),
        E04(N) AS (SELECT 1 FROM E02 a, E02 b),
        E08(N) AS (SELECT 1 FROM E04 a, E04 b),
        E16(N) AS (SELECT 1 FROM E08 a, E08 b),
        E32(N) AS (SELECT 1 FROM E16 a, E16 b),
   cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E32)
SELECT AVG(CAST(N AS BIGINT))
FROM cteTally
WHERE N <= 1000000;

Abbreviated output from SET STATISTICS TIME ON:

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

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

As expected, there are two separate measurements for the execution time with the second query running in about one tenth of the time of the first. Now I'll create and run a stored procedure that just calls the above code. Abbreviated output from SET STATISTICS TIME ON:

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

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

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

The math works out for my simple example: 1187 + 110 = 1297 CPU time and 1201 + 117 = 1319 elapsed time.


Uncommitable T-SQL transaction after trigger applied to table Good morning,
I have written a Trigger on a table that should evaluate the entered values (Length, width and Height) and based on those entered values, should update a column (Type) in the same record with a corresponding value. After applying the trigger, everything works perfectly, but at a certain time, I get the following error:

  Unexpected error:The current transaction cannot be committed and
  cannot support operations that write to the log file. Roll back the
  transaction. The statement has been terminated

I found a thread to handle errors using Try/Catch blocks with XACT_ABORT() and XACT_STATE(), but still the same issue is happening. Again, the issue is not occuring all the time, but sometimes only.
Here is my Trigger:

CREATE TRIGGER [dbo].[UTRAfterUpdate]
   ON  [dbo].[TBL_STOCK]
   AFTER UPDATE
AS
BEGIN

    SET NOCOUNT ON;

    DECLARE @CBM FLOAT, @T NVARCHAR(1);
    DECLARE C CURSOR FOR SELECT LEG_PRODUCT_ID, PR_LENGTH1,
                         PR_WIDTH1, PR_HEIGHT1, PR_TYPE FROM INSERTED
    DECLARE @ID BIGINT, @L INT, @W INT, @H INT, @TYPE NVARCHAR(1)

    SET XACT_ABORT ON;
    BEGIN TRY
        BEGIN TRANSACTION;
        OPEN C
        FETCH NEXT FROM C INTO @ID, @L, @W, @H, @TYPE
        WHILE @@FETCH_STATUS = 0
        BEGIN
            IF(UPDATE(PR_LENGTH1) OR UPDATE (PR_WIDTH1) OR UPDATE(PR_HEIGHT1))
            BEGIN
                SET @CBM = (cast(@l AS DECIMAL(18,2))/100
                                       *cast(@w AS DECIMAL(18,2))/100
                                       *cast(@h AS DECIMAL(18,2))/100)
                SET @T = CASE
                    WHEN (@CBM > 0.00  AND @CBM <= 13.00)
                          AND (@h <= 170) THEN 'C'
                    WHEN (@CBM > 0.00  AND @CBM <= 13.00)
                          AND (@h >  170 AND @h <= 220) THEN 'V'
                    WHEN (@CBM > 13.00 AND @CBM <= 20.00)
                          AND (@h <= 220) THEN 'V'
                    WHEN (@CBM > 0.00  AND @CBM <= 20.00)
                          AND (@h >  220 AND @h <= 250) THEN 'B'
                    WHEN (@CBM > 20.00 AND @CBM <= 30.00)
                          AND (@h <= 250) THEN 'B'
                    WHEN (@CBM > 0.00  AND @CBM <= 30.00)
                          AND (@h >  250) THEN 'T'
                    WHEN (@CBM > 30.00) THEN 'T'
                    ELSE @type
                END
                IF(@type <> @T)
                    UPDATE dbo.TBL_STOCK
                                    SET PR_TYPE = @t,
                                    LAST_ACTION = 'UTR',
                                    LAST_USER = 'System'
                    FROM TBL_STOCK STOCK INNER JOIN INSERTED INS
                            ON STOCK.LEG_PRODUCT_ID = INS.LEG_PRODUCT_ID
                    WHERE STOCK.LEG_PRODUCT_ID = @id
            END
        FETCH NEXT FROM C INTO @id, @l, @w, @h, @type
        END
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF (XACT_STATE()) = -1
        BEGIN
            PRINT 'The transaction is in an uncommittable state.'
            + ' Rolling back transaction.'
            ROLLBACK TRANSACTION;
        END;
        IF (XACT_STATE()) = 1
        BEGIN
            PRINT 'The transaction is committable.'
            + ' Committing transaction.'
            COMMIT TRANSACTION;
        END;
    END CATCH

CLOSE C
DEALLOCATE C
END

Can you please assist me n solving the issue?

Thanks in advance!

I definitely would not say that only 1 row is ever passed into the trigger.  Triggers handle every row updated in a single statement.  (I see Jon Seigel has made the same point.)

The problem is that inside a trigger an error can occur that results in a state where the transaction cannot be committed.  Your main issue will be determining why the transaction became uncommittable.

See http://msdn.microsoft.com/en-us/library/ms179296(v=sql.105).aspx

In part it says:

  Inside a TRY…CATCH construct, transactions can enter a state in which the transaction remains open but cannot be committed. The transaction cannot perform any action that would generate a write to the transaction log, such as modifying data or trying to roll back to a savepoint. However, in this state, the locks acquired by the transaction are maintained, and the connection is also kept open. The effects of the transaction are not reversed until a ROLLBACK statement is issued, or until the batch ends and the transaction is automatically rolled back by the Database Engine. If no error message was sent when the transaction entered an uncommittable state, when the batch finishes, an error message will be sent to the client application that indicates an uncommittable transaction was detected and rolled back.

So, your TRY/CATCH and testing against XACT_STATE is correct.  It is just too late to overcome whatever already happened to produce a "doomed" transaction.

Balmukund has a blog post on this topic below.  He points out that he cannot define every condition that might cause a doomed transaction, but he offers some hints:

http://blogs.msdn.com/b/sqlserverfaq/archive/2011/05/11/errors-raised-with-severity-level-16-may-cause-transactions-into-doomed-state.aspx

  As per our intensive research we asserted that XACT_STATE changes is not governed by a single rule and depends on the transaction state (user or system initiated and if an active transaction or not) and the type of error being raised. For example, transactions having CONVERT\CAST errors and DDL operations (ALTER TABLE\DATABASE\INDEX etc.) on failure will make the state of the transaction as -1 and will terminate the transaction.

  One more example which could get your transaction into doomed state is when you attempt to convert a value that is incompatible with target data type.


Fast paginated result, for various filter clauses I have been working on obtaining a paginated result from a table in my database (11g). While I have a query that does work (ie, the result is correct) it does not perform as well as I'd like to and I am trying to improve its efficiency (est. 60 calls per second on that query only).

So first of all, I read What is an Effective Way to Count the Number of Pages in Oracle?, and the article pointed too, but unfortunately it does not discuss the execution plan of the queries presented at all (I'll get to see them come Monday).

Here is the query I had come up with (the table is partitioned on part_code which is a date range):

select <all-columns> from (
    select rownum as rnum, <all-columns> from (
        select /*+index (my_table index)*/ <all-columns> from my_table
        where part_code in (?, ?, ?)
          and date between ? and ?
          and type in (?, ?, ?)
          and func_col1 = ?
          /*potentially: and func_col2 = ? and func_col3 = ? ... */
        order by date, type, id
     )
) where rnum between M and N; /* N-M ~= 30 */

Note: most of the queries will be carried out with a single func_xxx filter, and I think M will be small... but have no guarantee and in theory M can be up to 9999.

Note: ~72 partitions in total, but only ~39 active at most, ~300,000 different values of func_col1 (but with some having ~50,000 rows and some having 1 row), ~10 values of type, id is unique (generated through a sequence).

It does work, but there is a nasty surprise in the execution plan: the 2nd query (with rownum as rnum) is completely executed, fetching up to ~50,000 rows from the DB, before the pagination kicks in. Fetching ~50,000 rows from the DB to return some ~30 of them seems particularly inefficient.

In the execution-plan this shows up as:

View
\_ Filter (rnum)
   \_ View <= here comes the trouble
      \_ Sort
         \_ ...

I can create an index over the table if necessary, and I can transform the existing partitioned index (part_code, func_col1, date, type, id). The index has exactly the order required by my order by clause; but it does not seem to be sufficient (and removing the order by clause does not get me any closer anyway).

Is there a way to prevent the "materialization" of the view and have Oracle build it on the fly as the outer query needs more data (ie, moving to lazy evaluation of the inner queries) ?

I believe you should be aiming for a plan that avoid any actual sort operation, and "stops short" as soon as possible.

To avoid the sort (and "materializing" the inner view), your sort order must match exactly the index columns, or your where clauses must be strict equals only on all the leading columns. Otherwise there will be a need to sort subsets, and that will require evaluating the whole inner view.

Here's an example with a hypothetical foo table:

create table foo (a number, b number, c varchar(100));
-- fill with dummy data
exec dbms_stats.gather_table_stats(ownname => user, tabname => 'FOO');
create index foo_ix on foo(a, b, c);

Closest simple equivalent to your select:

select * from (
  select rownum r, i.* from (
    select a, b, c
    from foo
    where a in (3,4) and b = 3
    order by c
  )  i
) where r between 5 and 10;

The explain plan isn't good:

--------------------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |   163 | 14833 |     5  (20)| 00:00:01 |
|*  1 |  VIEW                 |        |   163 | 14833 |     5  (20)| 00:00:01 |
|   2 |   COUNT               |        |       |       |            |          |
|   3 |    VIEW               |        |   163 | 12714 |     5  (20)| 00:00:01 |
|   4 |     SORT ORDER BY     |        |   163 |  9291 |     5  (20)| 00:00:01 |
|   5 |      INLIST ITERATOR  |        |       |       |            |          |
|*  6 |       INDEX RANGE SCAN| FOO_IX |   163 |  9291 |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("R"<=10 AND "R">=5)
   6 - access(("A"=3 OR "A"=4) AND "B"=3)

The count is too late, and isn't actually (I think) "stopping things short" in this case.

Add the index columns to the order (might not meet your requirements, sorry):

select * from (
  select rownum r, i.* from (
    select a, b, c
    from foo
    where a in (3,4) and b = 3
    order by a, b, c
  )  i
) where r between 5 and 10;

-------------------------------------------------------------------------------
| Id  | Operation            | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |        |   163 | 14833 |     4   (0)| 00:00:01 |
|*  1 |  VIEW                |        |   163 | 14833 |     4   (0)| 00:00:01 |
|   2 |   COUNT              |        |       |       |            |          |
|   3 |    VIEW              |        |   163 | 12714 |     4   (0)| 00:00:01 |
|   4 |     INLIST ITERATOR  |        |       |       |            |          |
|*  5 |      INDEX RANGE SCAN| FOO_IX |   163 |  9291 |     4   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("R"<=10 AND "R">=5)
   5 - access(("A"=3 OR "A"=4) AND "B"=3)

At least we got rid of the sort. Now let's try and "stop short":

select * from (
  select rownum r, i.* from (
    select a, b, c
    from foo
    where a in (3,4) and b = 3
    order by a, b, c
  )  i where rownum < 10
) where r > 5;

-------------------------------------------------------------------------------
| Id  | Operation            | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |        |     9 |   819 |     4   (0)| 00:00:01 |
|*  1 |  VIEW                |        |     9 |   819 |     4   (0)| 00:00:01 |
|*  2 |   COUNT STOPKEY      |        |       |       |            |          |
|   3 |    VIEW              |        |     9 |   702 |     4   (0)| 00:00:01 |
|   4 |     INLIST ITERATOR  |        |       |       |            |          |
|*  5 |      INDEX RANGE SCAN| FOO_IX |     9 |   513 |     4   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("R">5)
   2 - filter(ROWNUM<10)
   5 - access(("A"=3 OR "A"=4) AND "B"=3)

This might be enough for your query: notice the count stopkey (rownum < magic, doesn't kick in with between in what I've seen) and the "rows" column - the inner scan doesn't have to bother about fetching more rows after it's found N.

With the above, you'll still be reading N rows from the table.

You could limit that if all the search criteria are indexed: do the above filtering but retrieving only the ROWID from each match rather than all the columns, and then access the table by ROWID.

select * from foo where rowid in (
 select rid from (
    select rownum r, i.* from (
      select rowid rid
      from foo
      where a in (3,4) and b = 3
      order by a, b, c
    )  i where rownum < 10
  ) where r > 5
);

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     1 |    78 |     6  (17)| 00:00:01 |
|   1 |  NESTED LOOPS               |          |     1 |    78 |     6  (17)| 00:00:01 |
|   2 |   VIEW                      | VW_NSO_1 |     9 |   108 |     4   (0)| 00:00:01 |
|   3 |    HASH UNIQUE              |          |     1 |   225 |            |          |
|*  4 |     VIEW                    |          |     9 |   225 |     4   (0)| 00:00:01 |
|*  5 |      COUNT STOPKEY          |          |       |       |            |          |
|   6 |       VIEW                  |          |     9 |   108 |     4   (0)| 00:00:01 |
|   7 |        INLIST ITERATOR      |          |       |       |            |          |
|*  8 |         INDEX RANGE SCAN    | FOO_IX   |     9 |   594 |     4   (0)| 00:00:01 |
|   9 |   TABLE ACCESS BY USER ROWID| FOO      |     1 |    66 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("R">5)
   5 - filter(ROWNUM<10)
   8 - access(("A"=3 OR "A"=4) AND "B"=3)

This does not work if any of the search fields are not in the index. And make sure you trace this with real data & usual search criteria to see if it materially makes a difference - it might actually be worse especially for low values of M (which is probably the case you want to be the fastest) or high values of N-M.


After processing complete, SSAS DB slowly consumes all available space on disk, w. high memory use also This problem is with an SSAS 2012 SP3 (11.0.6020.0) multi-dimensional server, hosting a single cube database, containing 1 cube and 16 dimensions. Aggregations have been designed using SQL Management Studio (to 30% gain), and extra aggregations added from the Usage Based Optimization wizard. The server has separate disks for Windows and the SSAS Data and Temp folders. This is what happens:

The cube database is fully processed successfully, and afterwards the disk containing the Data folder has a healthy 300GB out of 400GB free. This screenshot shows the largest files from particular partition folder under the .cub folder. All the files have the datestamp 21/04/2016 14:14 (the processing time).

Over the next few hours, the Data folder slowly grows, until it has consumed all of the available 400GB on the disk. This screenshot shows the largest files from same folder as above. Some of the original files can be seem, plus a number of new files. I don't think any of the files prefixed "176" were present immediately after processing.

During this time, the SSAS service is consuming almost all the available memory (24GB out of 32GB), and is very unresponsive - queries are very slow and attempts to access the properties of the server thru SQL Management Studio fail.

Once all the disk space is used, SSAS becomes unusable, and the SSAS service cannot be restarted.
Restarting Windows causes the disk containing the Data folder to get its 300GB free space back - the files dated after 21/04/2016 14:14 have disappeared. However, the process begins to repeat and the free disk space is again eaten up.

What could be going on and how do I stop it?

I found the answer eventually ....

There was a job set up to incrementally process the cube. Incremental cube processing requires a query to be defined to select new fact data to merge into the cube. In my case, this query was defined badly and was selecting the entire set of fact data, i.e. all the data that was already in the cube. Although fully processing the cube did not consume excessive disk space, for some reason incremental processing consumed all the available space. As a result the process never completed successfully. Rebooting the server caused the files created by the unfinished process to be deleted, but then minutes later the processing job would start again.


Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON' Hi I get this error when connecting to SQL Server remotely from a website.
I checked the SQL Server logs and get the error:

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Reason: Token-based server access validation failed

The SQL Service account is running as LocalSystem. From what I have read, SQL Server service automatically adds this account to SPN on startup.

TCP/IP is also enabled on the SQL Server.

Any ideas on what I need to do? Thanks!

This is almost always a Kerberos issue, due to an error somewhere along the way of setting up the SPNs.  See: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Reason: Token-based server access validation failed

One easy to make, but harder to find, problem is to have duplicate SPNs on your network.

If that might be your problem, read the following post to help you find the duplicate: http://blogs.msdn.com/b/psssql/archive/2009/02/13/searching-for-duplicate-spn-s-got-a-little-easier.aspx

A code snippet from that link:

setspn -q MSSQLSvc/mymachine.mydomain.com:1433
CN=MYMACHINE,OU=Workstations,DC=mydomain,DC=com
        MSSQLSvc/mymachine.mydomain.com:1433
        HOST/MYMACHINE
        HOST/MYMACHINE.MYDOMAIN.COM


Issues setting up Oracle 18c XE on Windows Server 2012 r2 I've been running into some problems getting my Oracle installation up and running on Windows Server 2012 r2 at work. Initially, I had installed Oracle on my Win10 laptop for test purposes - I connected to it using Toad for Oracle and had no problems.

However, after installing on Windows Server 2012 r2 I noticed that there were no control files and/or database files in the %ORACLE_BASE%\oradata\XE directory. In addition, I noticed that my listener.ora file was missing an entry for the XE service instance - once this was added, I could connect via SQLPlus but then the database could not be mounted because there were no control files. I even tried using DBCA to create a new database but ran into invalid memory parameter issue.

I'm starting to wonder if 18c is even compatible with Server 2012 r2 even though the documentation says it is - am I missing some configuration? Any help would be greatly appreciated - I've been trying to figure this one out for the last few days. Thanks.

So I ended up accidentally solving this problem...

After trying to setup a new database in DBCA (Database Configuration Assistant), I went to delete the newly created one only to realize that I had accidentally deleted the "XE" instance. I went ahead and deleted the new instance I had attempted to create as well and then went back into DBCA and created a new instance with SID of "XE" (if I tried with a different SID Oracle XE would complain about the restriction of one instance per install. I also adjusted the sga_target &  pga_aggregate_target values to sum to 2GB (another restriction on XE) and lowered the processes value (changed from initial of 4400 to 320 since that is why my laptop used and it worked) and the instance was recreated with all the necessary control & data files.

  In summary:

  When doing a fresh install of Oracle on Windows Server 2012 r2 (may work for other supported Win Server OS', but I only have 2012 r2 to test), if the default instance does not have the needed control and/or data files do the following (using Advanced Configuration in DBCA):

  Delete the default instance (this was 'XE' in my case). If there are any other instances you attempted to create, delete them as well.
  Create a new instance using the default SID (this was 'XE' in my case) - if needed, set it as a container databases for any pluggable databases you create in the future.

    If using Oracle 18c XE, ensure that the sga_target and pga_aggregate_target values sum to the restricted memory limits (2GB) set by XE (my values were sga_target=1536M & pga_aggregate_target=512M).

    Set processes value to an appropriate value. I'm not sure if this was necessary but , I kept getting ORA-47500 if this was not lowered after I had adjusted sga & pga values.

  Create pluggable databases for your instance as needed.

Like I mentioned this solved my issue - please feel free to correct this if I made any incorrect assumptions. Hopefully this will help others if they run into a similar situation - I'm assuming these directions will be tweaked depending on what server OS the install will be accomplished on.


PostgreSQL "at time zone" construct does not use index I have a simple table and am unable to understand why a certain query does not use an obvious index, and runs so slow.

This is the table:

create table impulse2 (
   account     smallint        NOT NULL,
   sensor      smallint        NOT NULL,
   datetime    timestamp       NOT NULL,
   last_value  numeric(10,2)
);
alter table impulse2 add constraint impulse_pkey2 primary key (account, sensor, datetime);

The slow query is here:

select
date_trunc('minute', datetime at time zone 'UTC' at time zone 'Europe/Luxembourg') as datetime,
round(avg(last_value), 1) as last_value
from impulse2
where account = 1
and sensor = 2
and date_trunc('hour', datetime at time zone 'UTC' at time zone 'Europe/Luxembourg') between '2016-09-08 00:00:00' and '2016-09-08 00:10:00'
group by 1
order by 1

Explain analyse returns this:

QUERY PLAN
GroupAggregate  (cost=38120.73..38263.54 rows=5193 width=14) (actual time=931.191..931.278 rows=60 loops=1)
  Group Key: (date_trunc('minute'::text, timezone('Europe/Luxembourg'::text, (datetime)::timestamp with time zone)))
  ->  Sort  (cost=38120.73..38133.72 rows=5193 width=14) (actual time=931.172..931.187 rows=165 loops=1)
        Sort Key: (date_trunc('minute'::text, timezone('Europe/Luxembourg'::text, (datetime)::timestamp with time zone)))
        Sort Method: quicksort  Memory: 32kB
        ->  Seq Scan on impulse2  (cost=0.00..37800.26 rows=5193 width=14) (actual time=537.536..931.131 rows=165 loops=1)
              Filter: ((account = 1) AND (sensor = 2) AND (date_trunc('hour'::text, timezone('Europe/Luxembourg'::text, (datetime)::timestamp with time zone)) >= '2016-09-08 00:00:00'::timestamp without time zone) AND (date_trunc('hour'::text, timezone('Europe/Luxembourg'::text, (datetime)::timestamp with time zone)) <= '2016-09-08 00:10:00'::timestamp without time zone))
              Rows Removed by Filter: 1038445
Planning time: 0.195 ms
Execution time: 931.341 ms

I found a way to "cheat" which uses the index, and also has the comfort of timezone conversion.

I basically extend the date range of selection to be sure to include enough values whatever timezone the user has, and then use a wrapper select which filters the result a second time and performs the timezone conversion:

select * from (
    select
    date_trunc('minute', datetime at time zone 'UTC' at time zone 'Europe/Luxembourg') as datetime,
    round(avg(last_value), 1) as last_value
    from impulse2
    where account = 1
    and sensor = 2
    and datetime between '2016-09-07 00:00' and '2016-09-09 23:59' -- uses index
    group by 1
    order by 1
) as r
where
r.datetime at time zone 'UTC' at time zone 'Europe/Luxembourg' between '2016-09-08 00:00:00' and '2016-09-08 00:20:00'


Optimised Schema for City-to-City Distance Table This is my first question, forgive me if this question is simple. I'm stuck here, trying to implement the relationships for the data represented below. I want to scale it vertically (new rows), instead of adding new columns as city names horizontally (new columns).

So far, I could only deduce two tables:

City tables with primary key. (6 rows)
Distance table with primary key. (13 rows)

How would I relate these two tables? Or should I proceed with one table as is in the representation below?

Most database management systems have a hard limit on either the number of columns you're allowed to use, the number of bytes in a row, or both. So your single table won't work in the general case, because you'll eventually either end up with either too many columns or too many bytes in a row.

To find out what the limitations are for your platform, Google something like

SQL Server limitations
PostgreSQL limitations
Oracle limitations

All you need to store your data is a single table of distances. A table of cities can be used to validate the values in the table of distances, but it's not essential. (I'd use one, though.)

But this won't work in the general case either, because it's subject to the same limitations as a base table. To see how it works for relatively small data sets, look at this example. And look closely at the CHECK() constraints.

create table distances (
  city_from varchar(25) not null,
  city_to varchar(25) not null,
  check (city_from < city_to),
  distance integer not null check (distance >= 0),
  primary key (city_from, city_to)
);

insert into distances values
('Bangalore', 'Chennai', 100),
('Bangalore', 'Hyderabad', 200),
('Bangalore', 'Mumbai', 300),
('Bangalore', 'Delhi', 400),
('Bangalore', 'Kolkata', 500),
('Chennai', 'Hyderabad', 150),
('Chennai', 'Mumbai', 250),
('Chennai', 'Delhi', 450),
('Chennai', 'Kolkata', 550);

Now you can create a pivot table with a SELECT statement. (SQL Server. Other dbms have different ways of dealing with pivot tables.)

SELECT *
FROM (
  SELECT city_from, city_to, distance
  FROM distances
  union all
  SELECT city_to, city_from, distance
  FROM distances
) AS t1
PIVOT
(
  max(distance)
  FOR [city_to]
  IN (
    [Bangalore], [Chennai], [Delhi], [Hyderabad], [Kolkata], [Mumbai]
  )
) AS t2
ORDER BY city_from;

Because of the limitations on the number of columns and the number of bytes per row, I think you're better off returning the base table (with or without the UNION ALL that you see in the query above) to the application, and letting the application format it for display. Application code doesn't generally have limits on the number of columns or the number of bytes.


How to identify which schema calls a function in another database without schema? I have a database with 3 schemas (db1) where each schema calls the “guardarTransaccion” function of a database without schema (db2).

When calling the “guardarTransaccion” function pass the record id and the db2 has to connect to the corresponding schema using DBLINK and load the detail data (eg db1.schema1).

The problem I have is when we call the function from a schema in db1 I dont have a way to identify which schema I called the function for to load the corresponding data.

The code of the function in DB2 is:

CREATE OR REPLACE FUNCTION "public"."guardarTransaccion"("tipo" text, "idRemito" int4, "idPicking" int4)
  RETURNS "pg_catalog"."int4" AS $BODY$
DECLARE numeroRemito TEXT;
BEGIN
SET search_path = 'public', 'public', 'pg_catalog';

    -- load db1 data using dblink view
    IF "tipo" = 'E' THEN
        UPDATE picking SET rom_id = "idRemito", pk_remitado = 1, pk_cerrado = 1 WHERE pk_id = "idPicking";
        SELECT rmv_numero_comprobante INTO numeroRemito FROM romaneos_venta WHERE rmv_id = "idRemito";
    ELSE
        UPDATE picking SET rmv_id = "idRemito", pk_remitado = 1, pk_cerrado = 1 WHERE pk_id = "idPicking";
        SELECT rmv_numero_comprobante INTO numeroRemito FROM remitos_venta WHERE rmv_id = "idRemito";
    END IF;

    -- operaciones con los datos / Operations with data

RETURN 1;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE COST 100
;

The way we find it to solve it is to create schema in the db2 only with the changes to each db1 schema, but it does not seem correct, what would be the best way to solve this?

You can get the current schema by using the System Information Functions

current_schema[()] returns text: name of current schema
current_schemas(boolean) returns text[]: names of schemas in search path, optionally including implicit schemas

I don't think dblink passes the context of the functions executed from the original database. So you'll have to wrap those functions and explicitly pass the context, perhaps by making the function variadic

CREATE OR REPLACE FUNCTION public.guardarTransaccion("tipo" text, "idRemito" int4, "idPicking" int4,
  context DEFAULT '{}'::text[] )

Then call it like the context explicitly. I would probably just think of a better way to engineer the system.


Querying number of subscription to each publication We have a production box that has about 60 publications on it. Some of these are used, and some seem to not be... What I need to do is create, or find, a script that will get the count of subscribers/subscriptions per publication so I can easily identify unused publications.

We are using SQL Server 2005. :(

Thank you,
Wes

I was able to get the necessary info I needed by poking around a bit:

use Distribution;
select count(s.publisher_id) as 'Number of Subscribers', p.publication_id, p.Publisher_db, p.publication from MSpublications p
    left outer join MSmerge_subscriptions s on p.publication_id = s.publication_id

 group by p.publication_id, p.publisher_db, p.publication
 Order by Publisher_db, Publication_id asc


Is it safe to rely on auto increments even after restoring data? I plan of using auto increments as a primary key on one of my table.

I need to store data related to each row in an other storage system and I plan to use each primary key id as the key for the data.

Is it guaranteed that even after the restoration of a pg_dump, each row will still have the same id?

A quick overview:

PostgreSQL does not have AUTO INCREMENTS. There is no special type.
In order to set this up PostgreSQL uses the standard IDENTITY COLUMNS which associates a SEQUENCE with the int type.

Let's create a table,

CREATE TABLE foo (
  foo_id int PRIMARY KEY,
  bar    int GENERATED BY DEFAULT AS IDENTITY
);

When you pg_dump that table with an IDENTITY COLUMN you'll see this,

CREATE TABLE public.foo (
    foo_id integer NOT NULL,
    bar integer
);

That's because the type is simply int type. Now, when you load you'll see right after the table this,

ALTER TABLE public.foo ALTER COLUMN foo_id ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME public.foo_foo_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);

This tells the pg_restore the state associated with the sequence the IDENTITY COLUMN was using.

# \d foo
                             Table "public.foo"
 Column |  Type   | Collation | Nullable |             Default
--------+---------+-----------+----------+----------------------------------
 foo_id | integer |           | not null | generated by default as identity
 bar    | integer |           |          |

Then the data is loaded and when it's loaded all of the values of that sequence for each row are specified in the load -- they maintain the old values from the dump.*

Note that the PostgreSQL implementation of an IDENTITY COLUMN which is more less sugar for SEQUENCE does not ensure a gaplessness.

Footnotes

I've left off the permission stuff for brevity.
*I've removed all of the stuff about serial, bigserial and what serial was and how it worked because moving forward it should not be a thing. PostgreSQL users should be using IDENTITY COLUMNS in all circumstances.


Adding a constant IF not in an other table So I have this query.

SELECT *
FROM `Comment`
WHERE `Target` = '".$_GET['pp']."'
AND `Id` NOT IN (SELECT `Target`
                 FROM `Delete`
                 WHERE `Type` = 'comment')
ORDER BY `Id` DESC;

This selects comments which aren't deleted and displays them to members.

How could I modify it to select everything but with an additional boolean (called Deleted)?

I've heard about IF statements in SQL but don't know whether it's appropriate here or how it works.

Is this what you are looking for?

    select *
    FROM Comment cm left join [Delete] del
     on del.[Target]=cm.Id
    where cm.[Target] = '".$_GET[''pp'']."'
    and Type = 'comment'
    and del.[Target] is null
    and deleted=0


SQL Server Grant execute on all stored procedures without ddl permissions I have some users in my database. They are member of db_datareader. They now also need to have execute permissions on stored procedures. I was just wondering what would happen if one of the stored procedures issue DDL? Would they be able to make schema changes to tables, drop etc. through that stored procedure?

Thank you

NO, With just Execute permission doesn't allow a user to execute a stored procedure successfully if it contains some DDL (CREATE, ALTER, DROP, TRUNCATE, RENAME) statements.


RAM Impact on Oracle database operations We are seeing drastic change in the amount of time taken for db operations depending on ram size.

The same content when inserted into a db running on a 4gb ram machine takes about 600% more than into a db running on a 16gb ram machine. The inserts are made through jdbc. Both the db machines were 64 bit machines and both were enterprise editions of db.

Interesting thing is that the memory consumption for the db running on 4gb machine never goes above 50-60% of the total capacity - so we do not understand why this difference. Is there any explanation for this behavior or some more pointers to look into this?

Praveen,

Oracle provides a lot of functionality to enable you to trace individual sessions (with waits and binds) or statements. You don't list the version in use, but most probably you can trace the inserts using DBMS_MONITOR package introduced in 10g. In 11g and if you have the relevant management pack licensed you could also use Real Time SQL Monitoring. http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/instance_tune.htm#CACGEEIF If you take this approach, of tracing the problematic SQL then you can know for certain where the time is going. The most common cause by the way for slow inserts is nothing to do with RAM and everything to do with the number of indexes on the table.


Fulltext match() against() returning empty set I have a full text indexed table set up with the data Family in it.  There is no White Space and the spelling is correct.  How can I make this query match results as score and display them by score in DESC order? I keep getting empty set and don't know why.

SELECT *, MATCH(MEDIA_TITLE) AGAINST('Family') AS SCORE
FROM MEDIA_DATA_VIDS
WHERE MATCH(MEDIA_TITLE) AGAINST('Family')
ORDER BY SCORE DESC ;

I found the solution.  Thanks Anyways

See https://dev.mysql.com/doc/refman/5.7/en/fulltext-natural-language.html:

The search result is empty because the “search word” is present in at least 50% of the row entries, and so is effectively treated as a stopword.

my solution was to add more db entries so that the search term is less than 50% of the of the rows values.


Postgres Repeatable Read vs Serializable I'm trying to understand whether the repeatable read isolation level is good enough for my scenario in an application that uses Postgres, but the docs are making it difficult to understand which is best suited.
I have an application that starts a transaction that first reads the current value of a single row based on the primary key of that row, calculates what the new state of that row should be after receiving an event from a message queue, and then updates the state of that row in the database.
Given that there are multiple instances of the application deployed, and that multiple events on the message queue can be received for the same primary key at the same time, it's possible that 2 transactions could attempt to enter the above described transaction at the same time. Is repeatable read isolation good enough for this case, or do I need to consider using serializable? My assumption is that if the first transaction attempts to commit its result to the database whilst the second transaction is in progress, the second transaction will fail with a conflict because it sees that the row it was attempting to update was modified by the first transaction - is this correct?
And a follow-up question: I'd like to understand how much more 'expensive' it is to use serializable transaction isolation vs repeatable read - I want to understand the underlying mechanism of what's going on within postgres - i.e. is there locking going on, and how does that affect performance of other queries running at the same time?

REPEATABLE READ is sufficient for your case. It will by definition prevent a “lost update”.
SERIALIZABLE is quite a bit more expensive than REPEATABLE READ, which is the “cheapest” of all isolation levels. More locks will be taken (SI locks that don't block anything, but can cause a transaction to abort), and these locks have to survive a commit. It is impossible to name a figure how much more expensive it will be; that depends on your workload.


SQL job is completing without sending email to configured recipients I have an SQL job completing without sending email to configured recipients. Here is the stored procedure I am using. Names changed accordingly. All other mail is getting sent via db mail through a stored procedure. I believe there is a size limit on the mail send.

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[spReport]    Script Date: 3/3/2016 3:29:27 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      J Romero
-- Create date: 10/21/2015
-- Description: Procedure to email a list of Subscribers for the last 12 months
-- =============================================
ALTER PROCEDURE [dbo].[spReport]

AS
BEGIN
    SET NOCOUNT ON;
    dECLARE @p_recipients as nvarchar(max), @p_profile_name as nvarchar(max)
    declare @TableHTML nvarchar(max)
    SET @p_recipients = N'email1@web.com;email2@web.com;email3@web.com'
    set @TableHTML =
    N'
eMail Subscribers-Last 12 Months
' +
    N'
' +
    N'
eMail Address	Date Subscribed
' +
    Cast ((select td = email,      '',
                    td = Date_Subscribed
    FROM email_subscriptions
    where date_subscribed > dateadd(D,-365,getdate())
    ORDER BY date_subscribed
    for xml path('tr'), type) as nvarchar(max)) + N'
' ;

    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Admin',
    @recipients = @p_recipients,
    @reply_to = 'email@web.com',
    @blind_copy_recipients = 'email@web.com',
    @subject = 'Subscribers-Last 12 Months',
    @body = @TableHTML,
    @body_format = 'HTML'
END

Every other Procedure is sending mail, and no errors are coming up in logs. Ok, Mail profile has been enabled. I have found that there is a size limit to the emails you can send from db. Is there a setting to change this?

The issue was the size limitation. We just made the file smaller using a shorter date range. Also changed size limit through sql function in case we wanted to select a larger date range later.

Reference: Database Mail Stored Procedures (Transact-SQL)

Particularly:

sysmail_help_configure_sp
sysmail_help_profile_sp
sysmail_help_account_sp
sysmail_help_principalprofile_sp
sysmail_help_profileaccount_sp


Exclude table's data from another table Is it possible to create a query and ask the following?
I have two tables (each one has one column with data) and I want to obtain as a result all data from the first table MINUS the data from the second table.

In standard SQL you could do that very easily with EXCEPT:

SELECT
  Column1
FROM
  Table1

EXCEPT

SELECT
  Column2
FROM
  Table2;

However, EXCEPT is unsupported in MS Access. Instead, you could use an anti-join. There are various ways of implementing an anti-join. One is using an EXISTS predicate:

SELECT
  Column1
FROM
  Table1 AS t1
WHERE
  NOT EXISTS
  (
    SELECT
      *
    FROM
      Table2 AS t2
    WHERE
      t1.Column1 = t2.Column2
  );


Why does MySQL forbid LOCK TABLES in stored procedures? I have read through several sections of the MySQL reference manual, and while it states repeatedly that LOCK TABLES cannot be used in a stored procedure, I have never found an explanation for the reasoning behind that.

I couldn't find a "here's why" statement either, but from reading the items at the links below, it appears to be a combination of:

preferred best practice: use SELECT...FOR UPDATE
preference for row-level-locking over table-level locking
performance issued related to concurrency and the finer-granularity of row-level-locking
other alternatives appearing on the MySQL Stored Procedure Forum included switching the storage engine (though that still wont get a LOCK TABLES statement into an SP) if table-level-locking is truly justified or using transactions rather than table-level-locking. Those elections probably come down to other considerations not appearing in the original question.
Also saw a statement on stored procedures declaring that "arbitrary SQL Statements" were not permitted. I didn't hunt for a list of "arbitrary SQL."

Good luck


Procedure MySQL cursor taking a long time to execute I have a MySQL procedure, with a cursor in it. The sample cursor query is given below

DECLARE cs CURSOR FOR
           (SELECT  a.mag_id FROM A a  WHERE
                a.creation_date BETWEEN (v_fromdate) AND (v_todate)
                AND a.type_id IN (SELECT type_id FROM key2 WHERE   sessionId=v_sessionId)
                AND a.mag_id IN (SELECT magid FROM    key1 WHERE   sessionId=v_sessionId order by magid)
                )
     UNION
                (SELECT  b.combo_mag_id FROM B b
                WHERE
                b.creation_date BETWEEN (v_fromdate) AND (v_todate)
                AND b.type_id IN (SELECT type_id FROM    key2 WHERE   sessionId=v_sessionId)
                AND b.combo_mag_id IN (SELECT magid FROM    key1 WHERE   sessionId=v_sessionId order by magid)
                );

DECLARE
CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
SET v_cur_time = now();
SET v_cur_query = 'cursor';
OPEN cs;
SELECT timestampdiff(SECOND,v_cur_time,now()) into v_diff;
SELECT CONCAT('Current Query: ', v_cur_query, ' Time taken: ', v_diff);

Both table A and B have millions of records.I have partitions created in both the tables with partition by range on creation_date.For 3 months of a date range the cursor takes almost 4 mins to execute.But when i tried running the same query in  MySQL workbench editor,by setting the parameters for the same date range it took only 22 seconds. Can somebody tell me why it executes faster in an SQL editor, and since i have to use it in the stored procedure, is there any way to optimize it ??

IN ( SELECT ... ) is often poorly optimized.  See if you can turn it into either a JOIN or EXISTS ( SELECT 1 ... )
Don't use a CURSOR if it is possible to write a JOIN or something to do the entire task rather than acting one row at a time.
PARTITIONing is unlikely to be beneficial; let's see the details.  (Composite indexes often do the job adequately.)
Remove the ORDER BY in subqueries; it has no effect.  If you need ordering, please elaborate; we should be able to find another way.
Are there parameters being fed in?  Which?


How to deploy the SQL scripts to the local database (one-click deployment)? How to deploy the SQL scripts to the local database (one-click deployment)?

On a periodic interval, we receive .sql files from Developers. I will have to automate the process of pushing all .sql files to mapped database.

I am trying to get some help on osql / batch script syntax to deploy .sql files.

I think you can get your answer from this link:

https://stackoverflow.com/questions/2583517/run-all-sql-files-in-a-directory

You can also check below site for more details on SQLCMD utility:

https://ssqlforest.wordpress.com/2016/10/31/how-to-use-sql-server-database-from-sqlcmd-prompt/

You can use database name within the script or -d option if all scripts have to go in the same database. Kindly ensure that queries are separated in batch i.e. go keyword after each SQL statement.


Storage accounts not showing up when creating Azure Storage Credential I'm trying to setup a Back Up Database Task in a Maintenance Plan on SQL Server 2017 (Web Edition) using SSMS 18.6 and when I get to the step to create an Azure credential I select my management certificate and subscription but when I try to select a storage account it refreshes, but no storage accounts show up. I've confirmed I have 2 storage accounts in that subscription, but they are not showing up for me to select them.

I see the same thing.  I suspect that the SSMS Maintenance Plan UI in SSMS for generating an SQL Server Credential to use with Azure Storage is out-of-date, or simply broken.
I would use the built-in (and newer) Managed Backup to Azure feature instead of the Maintenance Plan in the first place.
If you do want to use the Maintenace Plan, you'll need to create the credential yourself, per the docs here.  The tricky part (and the bit SSMS was trying to save you from) is generating the Shared Access Signature, which needs to be done in Powershell, or with the Azure Storage Explorer (not in the Azure Portal).