texts: ORDER BY ASC is 300x faster than ORDER BY DESC

texts:
The ascending sort is 300x faster than descending sort on a column which has a B-Tree index.
I have table of around 20 million rows.
I have indexes on inserted_at and x columns.

Ascending Sort

select
  * from table_a a where
  a.inserted_at >= '2018-09-01 00:00:00.0' and a.x in ('some_x_val')
  order by a.inserted_at asc
  offset 0 limit 20;

Limit  (cost=0.44..2027.69 rows=20 width=51) (actual time=418.529..464.558 rows=20 loops=1)
  ->  Index Scan using table_a_inserted_at_index on table_a a  (cost=0.44..5406390.18 rows=53337 width=51) (actual time=418.527..464.543 rows=20 loops=1)
        Index Cond: (inserted_at >= '2018-09-01 00:00:00'::timestamp without time zone)
        Filter: (event_type_id = ''some_x_val'::uuid)
        Rows Removed by Filter: 4092
Planning time: 0.177 ms
Execution time: 464.596 ms

explain (analyze, verbose, buffers) select * from table_a a where a.inserted_at >= '2018-09-01 00:00:00.0' and a.x in ('some_x_val') order by a.inserted_at asc offset 0 limit 20;
                                                                          QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit  (cost=0.44..2027.77 rows=20 width=51) (actual time=527.032..575.516 rows=20 loops=1)
  Output: x, y, z
  Buffers: shared hit=2492 read=905
  ->  Index Scan using table_a_inserted_at_index on public.table_a a  (cost=0.44..5406596.96 rows=53337 width=51) (actual time=527.029..575.496 rows=20 loops=1)
        Output: x, y, z
        Index Cond: (a.inserted_at >= '2018-09-01 00:00:00'::timestamp without time zone)
        Filter: (a.x = 'some_x_val'::uuid)
        Rows Removed by Filter: 4092
        Buffers: shared hit=2492 read=905
Planning time: 0.162 ms
Execution time: 575.550 ms

Descending sort

select
  * from table_a a where
  a.inserted_at >= '2018-09-01 00:00:00.0' and a.x in ('some_x_val')
  order by a.inserted_at desc
  offset 0 limit 20;

Limit  (cost=0.44..2027.69 rows=20 width=51) (actual time=148377.757..148385.703 rows=20 loops=1)
  ->  Index Scan Backward using table_a_inserted_at_index on table_a a  (cost=0.44..5406383.67 rows=53337 width=51) (actual time=148377.756..148385.691 rows=20 loops=1)
        Index Cond: (inserted_at >= '2018-09-01 00:00:00'::timestamp without time zone)
        Filter: (x = 'some_x_val'::uuid)
        Rows Removed by Filter: 2737952
Planning time: 0.663 ms
Execution time: 148385.756 ms

explain (analyze, verbose, buffers) select * from table_a a where a.inserted_at >= '2018-09-01 00:00:00.0' and a.x in ('some_x_val') order by a.inserted_at desc offset 0 limit 20;
                                                                                QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit  (cost=0.44..2027.77 rows=20 width=51) (actual time=168306.377..168312.296 rows=20 loops=1)
  Output: x, y, z
  Buffers: shared hit=2439002 read=359523 dirtied=288
  ->  Index Scan Backward using table_a_inserted_at_index on public.table_a a  (cost=0.44..5406596.96 rows=53337 width=51) (actual time=168306.375..168312.282 rows=20 loops=1)
        Output: x, y, z
        Index Cond: (a.inserted_at >= '2018-09-01 00:00:00'::timestamp without time zone)
        Filter: (a.x = 'some_x_val'::uuid)
        Rows Removed by Filter: 2745418
        Buffers: shared hit=2439002 read=359523 dirtied=288
Planning time: 0.139 ms
Execution time: 168312.326 ms

What could be the problem?


texts: MySQL how to make a backup while the server is not running

texts:
I made a update on my mac to catalina few days ago. After this my MySQL-Server was broken and I would like to reinstall it. Before doing this I would like to make a backup of my database. Because the server is not running I can't use mysqldump or phpMyAdmin. Is it enough to save the directory /usr/local/mysql/data/ or is there anything else I should do?

Thanks Thomas


texts:
I have an ecommerce db built and I'm adding subscriptions. See the simplified structure below.

orders
id, customer_id

order_items
id, order_id, product_id

invoices
id, order_id

subscriptions
id, customer_id, product_id, run_date

The issue I'm having is the invoices table requires an order_id but a subscription just generates an invoice. I could put the original order_id in the subscription table to track the lineage but then it gets confusing as the invoices table would have duplicate order ids. I tried removing the order and order_items and keeping the invoices table and adding an invoice_items table to bypass the order ids altogether but I thought it makes more sense to keep them because people can also buy regular products like t-shirts and those are technically an order. Is there a recommended way to structure the tables so that invoices are generated correctly? Should orders be for single purchase products and invoices only for subscriptions?


texts:
Till today i am using the linked server with the user of sql having same username and password in both the servers. For now on i can not keep the 2 servers password same for some reason and i still need the sql server to have linked connection.

When searched over net i found that i can achieve the linked server connection with the separate username and password using the 'sp_addlinkedsrvlogin' as mentioned in the article. In the same article at the end i found that storing the password for linked server is not secure and not recommended.

So my question is is there any secure way of storing the password for the linked server? because i need the linked server to connect with the different credentials as of the source server. Or is there any way that i can make the script ask for password every time i run the linked server query.

Note: i use the linked server from both .net coding and manually in sql management studio.

Update:

I am not using windows authentication to connect the linked server, i created single sql user which is common (username and password) in both the servers and running the query in that user for linked server connections.


texts:
1.Intro

Hello, I am running web application on 2 separate geographical locations(e.g. Europe, Hong Kong). Let's suppose Hong Kong server is an Amazon EC2 instance(paying for disk space, IO disk operations and traffic). Inspect the following figure:

2.Problem

When you should remotely access DB server A and when you should add DB server B and replicate in between these 2 DB servers(MySQL master-master) ?

3.Additional questions

The cost of either approach can be easily calculated. I just need to "load test" the following variables for particular web application:

-replication traffic/db access traffic

-disk operations

-disk space

-num of users(load test)

Q: What's the best way to get the replication traffic ? Is there any good replication monitoring software or I should stick with wireshark, collectd ?

Q: Are there any good blogs, papers, articles writing about similar topic ?

Thank you :)


texts: In 8.0.17 EPSG Spatial reference systems are not available at installation?

texts:
In 8.0.17 INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS is not prepopulated with ORGANIZATION="EPSG" records, leaving the table with only a single record, that for the default SRID=0.

A count in INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS of 1 record instead of the 5152 records found in 8.0.15.

It's breaking my load scripts with the following error due to the previously available spacial reference systems being omitted.

ERROR 3548 (SR001) at line 1: There's no spatial reference system with SRID 4326.

Is this a documented intentional change, or am I missing something?

This is not mentioned in the release notes:

https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-16.html
https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-17.html

My installations are both default AWS RDS installations made at different times resulting in different mysql versions. The reference systems are present on the prior installation but missing on the second.

version   8.0.15
innodb_version    8.0.15

SELECT count(*) FROM INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS;
5152

version   8.0.17
innodb_version    8.0.17

SELECT count(*) FROM INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS;
1


texts: I know that replication uses Sql Agent jobs to pull subscriptions. Are these jobs supposed to stay in an 'executing' state? (Sql 2008 SP1)


texts:
Possible Duplicate:
  How do I attach a database in SQL Server?

I was given an MDF for a database, no backups and not LDF that I was asked to attach the database to a SQL server. I get the following error:

File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER2K8\MSSQL\DATA\something.LDF" may be incorrect.
The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.

I tried Paul Randall's method of creating a database of the same size, putting it in emergency, single user mode and attempting to set it online that way. Is there any other option or do I tell the person that gave me this file that the data is corrupt?


texts: Are innodb_large_prefix and innodb_file_format settings backwards compatible?

texts:
I got the following message when I tried to install a piece of software trough the command line. I use a mySQL db.

* innodb_large_prefix = ON  is required.
* innodb_file_format = Barracuda is required.

I have several questions I couldn't find an answer for by just searching Google:

Can I just change these to the required settings without breaking other software of the server?
Suppose it does break, can I just switch back?
Do I need to check for my mySQL db version prior to  changing this?


texts: What to do when Sql Server Agent refuses to run a Job Step that references a missing database snapshot

texts:
I have a SQL Server Agent Job creating a snapshot of my main database every minute:

-- delete snapshot if it exists
if exists (select * from sys.databases where name = 'mydb_snapshot' )
begin
    drop database [mydb_snapshot]
end;

-- create new snapshot (whether or not we just deleted an old one)
create database [mydb_snapshot] on ( NAME = 'MYDBYEAH', FILENAME = 'D:\mydb_snapshot.ss' ) as snapshot of [mydb];

The snapshot is used both for reporting, and for a query later in the same Job Step.

(I'm new to Sql Server Agent Jobs and Snapshots, so feel free to point out any WTFs so far.)

My problem is Sql Server Agent refuses to run the job at all, citing that [mydb_snapshot] doesn't exist:

  Database 'mydb_snapshot' does not exist. Make sure that the name is entered correctly. [SQLSTATE 08004] (Error 911).  The step failed.

(That error doesn't have a line number associated with it. Rather, it seems SS Agent is checking the whole script before it starts running it and is smart enough to see I reference a non-existant snapshot, but not smart enough to see I always create that snapshot in the previous line. With debug statements, I can see that it doesn't even run the first line of the TSQL in my Job Step).

Any ideas what I can do to fool SQL Server Agent into running this job?

Or perhaps a different approach altogether?

(One thing I tried: it does work if I run the above create database [mydb_snapshot] statement manually, first. From then on, the Job runs every minute, no issues. But that means if that snapshot is deleted - which has happened at least once, not sure why - my Job fails for no good reason).


texts:
I am looking for clear step by step instructions to restore superuser role to postgres user that comes at install. I had to perform the below instructions (which didn't work thus far), because in pgAdmin I created a new Login Role user and assigned superuser privileges. Then I checked off superuser privileges from postgres, only to find out my new superuser was no superuser at all.

Below instructions are also here:
Restoring the superuser account on the postgres server

1) Create a local user called 'postgres'

c:\windows\system32> net user username password /add

2) Assign write rights for this user on the data folder

right click on Postgresql 9.5 install folder at c:\Program files\Postgresql\9.5\
click properties
click security tab
click edit (computer as for administrative rights... ok)
select 'all users' and check box 'total control'
click apply and wait for computer to assign writing privileges

3) Stop the postgres service from services

open the services list on windows through task manager
right click on postgresql 9.5
stop the service

4) Open a command prompt as postgres user (e.g. runas /user:postgres cmd)

c:\windows\system32\runas /user:postgres cmd
enter postgres user password
wait for another cmd window open under postgres user

5) On postgres cmd window run postgres in single user mode

c:\windows\system32>cd c:\Program Files\PostgreSQL\9.5\bin
c:\Program Files\PostgreSQL\9.5\bin>postgres.exe --single -D c:\Program Files\PostgreSQL\9.5\data

At this point I get this error message:

  postgres não pode acessar o arquivo de configuração do servidor "c:/Program/postgresql.conf": No such file or directory

Translating the part from Portugues to English you get:

  postgres cannot access server's configuration file


texts: Can I use Oracle Standard or Enterprise edition for my own training purposes?

texts:
Question is about Oracle licensing terms.

I would like to learn more about Oracle Database administration. Unfortunatelly I am using 32 bit verison of LInux, so I can't install Oracle 11 XE (I can't find 32-bit edition though).

Is it OK, if I download and install Oracle Standard or Entrprise on my personal notebook, and use it with no additional costs and hidden license issuses, is it even possible? I won't be using it for any commerce, even open-source project - I will only be using this installation to improve my DBA skills.


texts: Extract Image URLs from html text using Postgres sql Query

texts:
I have a column in postgres db in which html contain stored. Like this

&iquest;C&Oacute;MO HACER HUEVO DURO?

Los huevos duros se han transformado en un cl&aacute;sico de la alimentaci&oacute;n de los chilenos, ya sea para llevarlos a diferentes actividades o simplemente como la prote&iacute;na que se necesita en una comida diaria. Pero, a pesar de tener una preparaci&oacute;n simple, no siempre se logra obtener el resultado que se desea.

ERRORES EN LA COCCI&Oacute;N

Uno de los errores cl&aacute;sicos al hacer un huevo duro, es el tiempo de cocci&oacute;n. Esto puede causar que la yema quede muy cocida o que esta quede cruda junto a la clara y al intentar retirar la c&aacute;scara, se nos rompa el huevo.

Para que esta quede centrada y no cargado hacia ning&uacute;n extremo, debes estar constantemente dando vueltas el huevo durante los primeros 4 minutos de la cocci&oacute;n, as&iacute; la yema ir&aacute; rotando dentro de la clara durante los minutos en donde comienza su solidificaci&oacute;n.

4- Sobrecocci&oacute;n

Una vez transcurrido el tiempo estimado en el agua hirviendo, retirar con precauci&oacute;n el huevo de la olla y ponerlo bajo agua fr&iacute;a para detener que el calor interno siga cociendo la yema.

&nbsp;

&nbsp;

Now I want to fetch all image urls also first image url from this text.
All image URLs:

https://images.unsplash.com/photo-1579121477063-4026649ad6d3?ixlib=rb-1.2.1&ixid=eyJhcHBfaWQiOjEyMDd9&auto=format&fit=crop&w=750&q=80
https://images.unsplash.com/photo-1550623627-2b70a7772e7a?ixlib=rb-1.2.1&ixid=eyJhcHBfaWQiOjEyMDd9&auto=format&fit=crop&w=750&q=80
https://images.unsplash.com/photo-1426023671131-18ce9c8d5f80?ixlib=rb-1.2.1&ixid=eyJhcHBfaWQiOjEyMDd9&auto=format&fit=crop&w=753&q=80

And first image URL from above URLs

https://images.unsplash.com/photo-1579121477063-4026649ad6d3?ixlib=rb-1.2.1&ixid=eyJhcHBfaWQiOjEyMDd9&auto=format&fit=crop&w=750&q=80

How to write query for this result?
Thanks!


texts: Is sql_variant compatible with First Normal Form (1NF)?

texts: 1NF rule says that we should not keep data of different types in one single column. Does it mean that sql_variant is not compatible with first normal form hence should not be used?


texts:
I use this query:

 SELECT
     logdate,
     SUM(bytes)/1024/1024/1024 as traffic,
     customer
 FROM
     `Logdata`
 WHERE
     customer is not null AND
     logdate >= "2016-07-01" AND
     logdate <= "2016-07-30"
 GROUP By logdate, customer

on this table with currently around 6 mio rows (but there will be 10 times more rows):

CREATE TABLE `Logdata` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `logdate` date DEFAULT NULL,
  `logtime` time DEFAULT NULL,
  `bytes` int(10) UNSIGNED DEFAULT NULL,
  `uri` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `customer` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
  `method` smallint(6) DEFAULT '200',
  `region` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

with this keys:

  ADD PRIMARY KEY (`id`),
  ADD KEY `IDX_logdate` (`logdate`),
  ADD KEY `IDX_method` (`method`),
  ADD KEY `IDX_customer` (`customer`),
  ADD KEY `IDX_customer_logdate` (`logdate`,`customer`);

and this execution plan:

id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra
1 | SIMPLE | Logdata | NULL | index | IDX_logdate,IDX_customer,idx_customer_logdate | idx_customer_logdate | 391 | NULL | 6247535 | 25.00 | Using where

InnoDB Config is:

InnoDB buffer pool / data size: 2.0G/1.1G
InnoDB buffer pool instances: 2

With ~6mio rows in the table the query takes 14 seconds. Which will get worse as the amount of rows is increasing in a very fast pace.

The questions:

Would i benefit in this case from the myisam table engine?
What could i do to further optimize the query or the settings?


texts:
SQL> startup;
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/12.2.0.1/db_1/dbs/initAWSDB.ora'

I created a new Oracle 12c database using dbca

the only files in the dbs directory

$ ls -ltrh
total 24K
-rw-r--r--. 1 oracle oinstall 3.1K May 15  2015 init.ora
-rw-r-----. 1 oracle oinstall   24 Mar 22 23:10 lkCFRDB
-rw-r-----. 1 oracle oinstall 3.5K Mar 22 23:13 orapwCFRDB
-rw-rw----. 1 oracle oinstall 1.6K Mar 22 23:27 hc_CFRDB.dat
-rw-r-----. 1 oracle oinstall 3.5K Mar 22 23:27 spfileCFRDB.ora
-rw-rw----. 1 oracle oinstall 1.6K Mar 22 23:42 hc_AWSDB.dat

When I tried to start up with init.ora

SQL> startup pfile=/u01/app/oracle/product/12.2.0.1/db_1/dbs/init.ora
ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated
ORA-01262: Stat failed on a file destination directory
Linux-x86_64 Error: 2: No such file or directory


texts: postgresql count null and blank value from jsonb column

texts:
In following JSON structure,

[
    ["a", 921],
    ["b", ""],
    ..
    ...
    ["c", null],
]

how to count occurrence of

blank value ""
null value


texts: Server default collation change or not change

texts:
The default collation of our new server is SQL_Latin1_General_CP1_CI_AS. However the collation of the other databases which are used as the source to migrate data into the target databases on this server are Latin1_General_CI_AS.

To avoid getting the error while we load data from database 1 (collation:Latin1_General_CI_AS) to database 2 (collation:SQL_Latin1_General_CP1_CI_AS), I changed the collation of database 2 (the target database) to Latin1_General_CI_AS.

However I'm wondering if it might any side-affect?
What is the reason that the server default collation is SQL_Latin1_General_CP1_CI_AS? Is it any better than other collation, in terms of performance, etc?

Thanks for your help.


texts: Why didn't primary file size change after moving large table to new file group?

texts:
After looking and finding the close answer, I'd like to post a follow-up, but separate, question to this one (split db to fit on ssds)

I have a 60 gig database that is growing quickly, due to one table that holds scanned documents. We are pushing to move old paper to scanned docs, and I have no choice that this is how we handle saving scanned documents.
I was thinking about adding a new file group, and move that table to the new FG by recreating the unique clustered index for that table to the new FG.

I did that on a test box and surprisingly, the OS file size of the original MDF did not significantly change, nor did the new secondary FG size up to the corresponding ratio of that one table's size to all the tables.

I was thinking the OS file sizes should change on the order of the size of the table that was moved.  What am I missing?

Thanks,
Bill


texts: Reclaim space from dropped column in SQLServer2008R2

texts:
I am trying to reduce the size of a database that is reaching the limit of the express edition of SQLServer2008R2.
We have identified that due to bad design, a large varchar(2000) field that contains on average 1100 bytes occupies most of the space.

Since this field is not needed to be retained, I want to drop the column, move it to a different table, use it there for the duration it is needed and then clean that table frequently.

I am trying to find the best solution to reclaim the space "wasted" in this column after it is dropped.

The table contains about 1.500.000 rows and has a Clustered PK on an ID (int) column.

I have succesfully reclaimed the space by moving all the contents of the table to a separate database, compacting the original database and then moving the table contents back to the original database having dropped the varchar column. This reclaims about 5Gb of space, which is what I need.

However, I'm looking for a faster and more streamlined option.

I have also succesfully reclaimed the space by dropping the varchar column and then using
DBCC DBREINDEX ({TABLENAME}, {PK_Name}, 100)

However, this process enlarges the size of the database while being executed, exceeding the 10Gb limit that would be enforced in the production environment, so it's not feasible.

I have also tried using DBCC CLEANTABLE that by the documentation appears to be the function best suited for the task, especially since the "Batch_Size" parameter may avoid the inflation of the database size during execution.

However, it looks like CLEANDATABASE is not doing it's intended job at all. It's a pretty simple command and I can't find how I can get it wrong, but here is what I did:

Restore the database (7.2Gb size, 198Mb free) - an old backup, the current database is about 9.5Gb large
Drop the varchar column (does not alter size and free size as expected)
Run DBCC CLEANTABLE ({DatabaseName}, {TableName}, 1000)
Database still shows the same size and free space

From that point on, the only way I've found to be able to reclaim the space is to reindex the table by using:
DBCC DBREINDEX ({TableName}, {PK_Name}, 100)
with the PK_Name being the Primary key, clustered, i.e. the actual data pages.
By doing so, the database shows to have become 8.1Gb with 4.8Gb free space.
From there on, I can reduce the size of the database file - for faster backups - by issuing a shrink command with NOTRUNCATE and then a second one to truncate the file at the end.

Although I have found a way that decently does what I need to do, I am baffled because CLEANTABLE doesn't seem to perform it's "advertized" purpose, even though it takes some time to complete.

Any suggestions or similar experiences?

Thanks in advance


texts: Multiple SPIDs in sp_who2, Deadlocks and Parallelism

texts:
There were reports of slowness on our web application - screens hanging, some unable to log in. It's an OLTP system with approx 50 concurrent users. Upon investigation I found a few things:

Number of connections to the backend were reaching the maximum 50.
There were a number of deadlocks, some processes deadlocking themselves. These locks did however release when I refreshed couple seconds after.
I ran sp_who2 and it returned 4 cases where an SPID was repeated 25 times (screenshot attached).

Am I correct in saying this is a result of parallelism? If so, is one obvious fix to amend our MAXDOP to a suitable setting and possibly change the threshold cost to higher than 5 second (server has 2 numa nodes, 24 threads total)?

If so, I'm thinking one drawback of this would be that overall performance on a day to day basis might be affected, as all the small queries will be using less resources and only the larger ones will get use of the full resources.

Please correct me if I'm wrong on that, I'm just looking to see if this change is advisable for an OTLP system and am looking to see possible negative impacts.


texts: In a multi-database AG, what decides which databases will have more than 1 redo thread?

texts:
We have a large physical server with more than 50 dbs on it; some are quite busy, others very quiet.
When having multiple DBs in an AG, only some of them multiple redo threads. We wanted the databases with those threads to be the busy ones.  During a recent migration, we decided to be careful of the order we restored the dbs in, as it was our understanding that this was decided based on databases creation date.  However, checking post migration, this isn't true.
SELECT databases.database_id,
       databases.create_date,
       dm_hadr_db_threads.name,
       dm_hadr_db_threads.num_redo_threads,
       dm_hadr_db_threads.num_parallel_redo_threads
  FROM sys.dm_hadr_db_threads
 INNER JOIN sys.databases ON dm_hadr_db_threads.name = databases.name
 ORDER BY
       dm_hadr_db_threads.num_redo_threads DESC
OPTION (RECOMPILE)

It isn't create date or database id.  It's not alphabetical.  It isn't order added to the ag. Does anyone know what decides this?
This is SQL server 2019; the query is from the secondary.
Here is where we found it was based on database_id: https://www.brentozar.com/archive/2018/06/first-responder-kit-release-just-when-you-think-theres-nothing-new-left-to-do/


texts: Determine which trace flags are set as a startup parameter vs. enabled globally?

texts:
Probably a quick question.

We are looking to test out a couple trace flags as startup parameters in our environment, and while I know we can use DBCC TRACESTATUS to get global-scoped trace flags, it doesn't specify whether this was set at startup or merely set with the -1 flag.  Is parsing the SQL error log the only method available?  Possibly a registry entry somewhere?

Any way this can be tackled programmatically would be useful.

Thanks for reading!


texts: How to force Redshift/Postgres to aggregate nth_value?

texts:
I'm trying to retrieve the 2nd, 3rd, 4th order/transaction from an orders table using a query that looks like this:

select
    customer_email,
    nth_value(created_at, 1) over (partition by customer_email),
    nth_value(created_at, 2) over (partition by customer_email),
    nth_value(created_at, 3) over (partition by customer_email),
    nth_value(created_at, 4) over (partition by customer_email)
from
    fact_orders
where
group by
    customer_email,
    created_at
limit 100

My goal is to get the 1st, 2nd, 3rd, and 4th order for each customer.
I'm forced to do a group by on customer_email and created_at, which results in a row for each order and date by a customer.

The nth_value yields the correct results, but I would prefer a result set that looks as so:

customer_email, first_order, second_order, third_order, fourth_order

With only 1 record per customer.

How can I achieve this?


texts: Why would someone store text in an SQL Image field?

texts:
I've been asked to get some info out of the database of a system we use. I've found what I'm looking for, but its left me... confused.

For some reason, the developers have the system storing text in an SQL Image field. I suspected this, but wasn't entirely sure, until I took the Hex output from my select statement, put it into a Hex to ASCII converter, and hey presto, it's exactly the kind of message I'm looking for.

What I'd like to know is: why? Is there some legitimate reason why someone would store text in an Image field?

Given the quite serious and private nature of these messages, the only thing I can think of is that someone was seriously mistaken in thinking that storing the text in an Image field would somehow make it more secure? Or is there really just something obvious I'm missing?

EDIT: Covered in the comments, but: At minimum, this text will occasionally likely include accented characters and such. Plus, my manager just let me know that the software was written using SQL Server 2000, or maybe even a version or two earlier. Possibly the simple answer is that there wasn't an analogue to nvarchar or similar back when it was written?


texts: Appending another column onto an existing UNION

texts:
I have the following query- I am trying to add another column, however I don't want any of the "WHERE" clauses from the previous 2 SELECT statements to take affect... so I can't just input the new column into both of the previous 2 SELECT statements.
Specifically, the query currently shows results for a given date range (note the date_opened>=##STARTDATE## and date_opened<=##ENDDATE## lines), however the column I wish to add should count all total results- not just results that fall between ##STARTDATE## and ##ENDDATE##.
How would somebody go about this? Another UNION?
The db is Sybase SQLAnywhere 16, syntax similar to t-sql/mssql.
The new column I wish to add (without any of the existing date range constraints):
(select
  count(sp_name(referred_link,1))
  from cases
  where sp_name(referred_link,1) = referrer
  group by cases.referred_link
) as num_refs_total,

And, the current query as a whole:
select casenum,
matcode as casetype,
date_opened,
sp_first_party(casenum) as client,
sp_name(referred_link,1) as referrer,
(select
  count(sp_name(referred_link,1))
  from cases
  where sp_name(referred_link,1) = referrer
  group by cases.referred_link
) as num_refs,
(case when mailing_list is null then 'No' else 'Yes' end) as old_mattar_star,
'repeat client' as reason
from cases
inner join party on cases.casenum=party.case_id
left join (select * from mailing_list_defined where mailing_list='Mattar Stars') a on party.party_id=a.names_id
where party.role='Plaintiff'
and party.our_client='Y'
and date_opened&gt;='##STARTDATE##'
and date_opened&lt;='##ENDDATE##'
and cases.case_date_6 is null
and party.party_id IN
  (select
    party_id
    from
      (select
        count(casenum) as casecount,
        party_id
        from cases
        inner join party on cases.casenum=party.case_id
        where party.role='Plaintiff'
        and party.our_client='Y'
        and matcode IN ('PMV','MVA','IMV','GPI','IGP','S%F','ISF')
        group by party_id) multicases
        where multicases.casecount>=2)
group by cases.referred_link, cases.casenum, cases.matcode, date_opened, mailing_list
UNION ALL
select casenum,
matcode,
date_opened,
sp_first_party(casenum) as client,
sp_name(referred_link,1) as referrer,
count(sp_name(referred_link,1)) as num_refs,
(case when mailing_list is null then 'No' else 'Yes' end) as old_mattar_star,
'personal referrer' as reason
from cases
inner join names on cases.referred_link=names.names_id
left join (select * from mailing_list_defined where mailing_list='Mattar Stars') a on names.names_id=a.names_id
left join provider on names.names_id=provider.name_id
left join party on cases.casenum=party.case_id
where names.person='Y'
and date_opened&gt;='##STARTDATE##'
and date_opened&lt;='##ENDDATE##'
and party.our_client='Y'
and party.role='Plaintiff'
and party.party_id != referred_link
and ((code not like '%Attorney%' and code not like '%Established%' and code not like '%Doctor%' and code not like '%Chiropractor%') or provider.code is null)
and matcode in ('PMV','IMV','MVA','S%F', 'ISF', 'GPI', 'IGP')
group by cases.referred_link, casenum, cases.matcode, date_opened, mailing_list
order by num_refs desc, date_opened asc


texts: How do you make manual/one-off log-based queries?

texts:
My app engine instances are logging incoming requests and I'd like to be able to run manual/one-off queries for data analysis.

As an example, I would like to be able to determine how many requests are made to each end-point, within a given period.
So an SQL query might look something like this:

SELECT path, count(path)
FROM request_log
WHERE time >= :start_time AND time <= :end_time
GROUP BY path

What is the simplest and most cost effective way to achieve these kinds of queries based on logging data?

Note: Monthly log ingestion is just under 1TB.


texts: When to prefere a truncate over a batch-delete?

texts:
When I browse DBA-Reactions I understand nearly all the .gifs but with this one I have trouble:

https://twitter.com/DBAReactions/status/613840893322338304

I mean truncate obviously doesn't create the transaction log as a batch delete does but still there are cases where you are pretty sure that you can simply truncate a complete table. Are there any other concerns besides that?
So basically I would love to understand the complete difference.


texts: Combine tsvector and timestamp in one index?

texts:
I have a table that has a text field, and a timestamp. I have various indexes, such as a btree on the timestamp (this works great for "get the most recent N") and a GIN on the text (for full text search, looks like CREATE INDEX foo ON bar USING GIN (to_tsvector('english', the_text)).
I need to support a query that looks like SELECT * FROM foo WHERE to_tsvector('english', the_text) @@ to_tsquery('english', ?) ORDER BY timestamp DESC LIMIT 1000. This works fine for when the text query has very few matches, but when the query has a lot of matches in the GIN, it takes an unbelievably long amount of time, since it seems to be grabbing the timestamp from the heap for each one, then sorting.
Is there any way to create a single index on both columns that supports this?
For example, if I had two normal columns a and b, I know that creating an index on (a, b) would speed up SELECT * FROM table WHERE a = ? ORDER BY b DESC LIMIT 1000. Is there an equivalent for when a is a full text search and b is a timestamp?
I've tried CREATE EXTENSION btree_gin then creating an index on (to_tsvector('english', the_text), timestamp) or (timestamp, to_tsvector('english', the_text)) using GIN or GiST. But none of those four indexes seem to change the query plan on a test table with dummy data. I could try them in production, but they would take a very long time to create (days).


texts: MySQL Slave database size >> Master Database

texts:
I have a Percona MySQL Master-Master setup (5.6) with second master as passive master(read-only).

Slave:

mysql> SELECT table_schema AS "Database", ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)" FROM information_schema.TABLES GROUP BY table_schema;
+--------------------+-----------+
| Database           | Size (MB) |
+--------------------+-----------+
| common_schema      |      1.89 |
| information_schema |      0.01 |
| mysql              |      1.70 |
| dataplay           |   1684.56 |
| dataplay1          |      0.14 |
| percona            |      0.20 |
| performance_schema |      0.00 |
+--------------------+-----------+

Master:

mysql> SELECT table_schema AS "Database", ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)" FROM information_schema.TABLES GROUP BY table_schema;
+--------------------+-----------+
| Database           | Size (MB) |
+--------------------+-----------+
| common_schema      |      1.89 |
| information_schema |      0.01 |
| mysql              |      1.69 |
| dataplay           |   1614.80 |
| dataplay1          |      0.14 |
| percona            |      0.20 |
| performance_schema |      0.00 |
+--------------------+-----------+
7 rows in set (0.07 sec)

The size difference is massive. Is it something wrong with the setup which is running? What is wrong?


texts: MySQL master/slave replication... structure not being replicated

texts:
Edit:  I might have answered my own question while going back thru the links for this post.  I would still like to get insight from more experienced DBA's and perhaps make the information easier to find for anyone that might need it in the future.

I have 2 MySQL servers.  One sitting right beside me, the other in a remote data center.  Both servers are identical.  Both are dedicated. Both are running Ubuntu 12.04 LTS, MySQL 5.5.28.  These servers power our in house web application, 2 web sites, virtual mail for 9 domains and backend for MS Access.  The slave is not replicating structure changes in the master.

I have reviewed:
dev.mysql.com/doc/refman/5.0/en/replication-howto-slavebaseconfig.html
dev.mysql.com/doc/refman/5.0/en/replication-howto-existingdata.html

And tracked down other issues with my configuration thanks to this question: serverfault.com/q/336888/143841.
Where I posted an answer that solved both mine and his issues at that time.  However, I am having trouble finding a real solution to ensure that structure queries are replicated right along with data queries.

Here is my configuration

Master:

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |  6547573 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

my.cnf on master:

#master/slave replication
server-id=172868
log-bin=mysql-bin
log-error=mysql-bin.err

Slave:

mysql> show slave status\G; | pastebin

my.cnf on slave:

# master/slave configuration
server-id=1357749477

I'm not highly concerned about the error listed in the slave status report.  I believe that issue was triggered by changes I made to the structure on the master.  I'm trying to configure the slave to apply any structural changes that occur on the Master.

I found a reference to some slave settings in a mysql bug report: bugs.mysql.com/bug.php?id=956

> replicate-do-db=rate
> replicate-ignore-table=rate.votes
> replicate-ignore-table=rate.clicks
> replicate-wild-ignore-table=rate.%cache

But I'm having trouble finding clear documentation on their usage and repercussions.... Well, on this page I found some information that says it won't replicate any statement made after a USE db; statement.  My ALTER statement was preceeded by a USE statement. But, I don't have replicate-do-db set.  Then further down it states that database-level changes are applied if replicate-wild-do-table=% is set.

So, is "replicate-wild-do-table=%" the correct way to get complete replication?  What are the ramifications of using this setting?  What is the correct way to configure the slave for complete replication of all databases.


texts: Is a Postgres long-running query aborted if the connection is lost/broken?

texts:
If I open a connection to Postgres and issue a long-running query, and then break the connection (e.g., kill the client process that opened the connection), will the long-running query continue to run, or will it be automatically aborted?  Is this configurable?

(I'm using Postgresql 9.2.9)


texts: How do I tell if a long running update won't lock the database?

texts:
There is a update query I need to run on a table in production. The update would touch millions of rows and I would take a couple of minutes.
The database is Mysql.
I was wondering if the table would be locked during this update? The desired behaviour is that it should not be locked as I still want other operations to be possible while the update is going on.
Is there a way I can check before hand? What could I look for to find out the effect of such query? Is there something like a query plan from where I can see if the table would be locked or not?


texts: Check all login users list from back 3 months

texts:
There is any way to check all database login users list and their activities from back 3 months in SQL Server?

I tried with SQL server Logs but I need more info about user activities from past 3 months. Can we get those users list? I didn't capture any data 3 months ago.

We are facing some DB status restoring mode for some reason, so we need to capture details of users at the time who login to server.


texts: "String or binary data would be truncated" error copying nvarchar(10) to char(10)

texts:
I am inserting values from one SQL table / column into another. The datatypes are different for these columns due to other reasons but I do not understand why nvarchar(10) source and char(10) destination results in an error sometimes in SQL Server 2014:

  String or binary data would be truncated.

len(sourcecol) = 10 and datalength(sourcecol) = 20.

Could it be because of some invisible spaces/characters stored in the source column which is of type nvarchar?


texts:
We are kind of struggling while creating the infrastructure for our BI sector.

I had read on this link that it is possible to create views on a read-only slave. Still, when I run the query I get

  "The MySQL server is running with the --read-only option so it cannot
  execute this statement."

Is there any way to acomplish this?

Or if it's not, is it possible to be able to have 2 MySQL masters one way sync, without loosing our views? (It's kind of a fake slave).

Our problem is that we won't nor want write access to the master server in order to create our views, and just in case both options aren't possible, is any way to achive something similar?


texts: How to aggregate over a "multi source" with recursive, in postgres?

texts:
Imagine I have a table "departments" like this:

name TEXT
parent_department TEXT (nullable)

I also have a "budgets" table like this:

department_name TEXT
budget INTEGER

I need to "traverse" this departments table from "leaf to root", following the parent department column. The end result is that I need to sum up the budgets of each department, but the twist is that the "budgets" table that I'll be using at any given time is very sparse (10s to 100s of rows), while departments is very dense (100m rows).

Normally, I would approach this by creating a "with recursive" CTE that "flattens" the department relationship into a temporary table of just "root department", "leaf department". Then I would join that table on leaf department to budgets, and group by root. The problem is that the "department" table is FAR too big now (the analogy to departments breaks down here a little bit I admit, no company has >100m departments).

Concretely, if I had departments like this (child -> parent):

C -> B
B -> A
A -> NULL
E -> D
D -> NULL
F -> G
G -> NULL

And budgets like this:

A 1
C 3
E 5

I would want to get as output:

A 4
D 5

And ideally, F and G, for example, would never be touched, because there are hundreds of millions of rows like F and G that shouldn't come up. But in my current query, they do. How can I fix this so that I only "traverse" the departments that I actually need to?


texts: SQL Server FullText searching on text that is slightly obfuscated before insert

texts:
Our app slightly obfuscates text before insertion to the database. This is done by the application (C#) and not the database. (This is not encryption but just to stop people who are looking at the data in Management Studio; not really preventing shoulder surfing, more like "can't read text even with correct permissions")

Here's a simplified example.

In the website I input HELLO into a textbox and click save.
The save method then replaces each character with the next in the
alphabet, so HELLO becomes IFMMP.
The save/update command is then called and IFMMP is saved into the
database.
When the data is read back from the database, each character is
replaced with the one before, so IFMMP becomes HELLO and gets
displayed in the textbox.

IFMMP won't be much good for fulltext searching. We have hundreds of existing sprocs & triggers which we don't want to update for this.

The question is, how can I "un-obfuscate" the data before it gets added to the Fulltext catalogue?

Update: Looks like we need a custom WordBreaker or IFilter. Will update if get to a solution.


texts: possible to query a database for an empty jsonb

texts:
I have a table in Postgres 9.6 db that is structured like this:

Table "public.pricings"
Column           |            Type             |                       Modifiers
---------------------------+-----------------------------+-------------------------------------------------------
id                        | integer                     | not null default nextval('pricings_id_seq'::regclass)
unconfirmed_matrix_prices | jsonb                       | not null default '"{}"'::jsonb

I'm new to using jsonb.

I'd like to search for any unconfirmed_matrix_prices that are empty(ie the default). I see I can do something like this:

solar_dev=# select count(*) from json_object_keys('{"f1":1,"f2":2}');
 count
-------
     2
(1 row)

Is there a way I can do a where phrase where json_object_keys is equal to 0? I know a bit of a runaround way - Is there a better way?


texts: SSIS: Using Event Viewer to Trace Errors of A Package

texts:
Overview

A package was developed due to a planned database server migration, and other teams with a dependency on legacy cross-database queries. There is a policy of not having Linked Servers, so an SSIS package was setup to push the latest data to the required tables on the old server.

There was a restriction defined for this new package, where it will need to finish before a backup to a read-only server that is set to run nightly against the old server. This read-only server allows for getting data without many hits to the PROD database server, and the cross-database queries do not impact mission-critical processes.

In case there are missed tables, some dynamic functionality was put in place to limit repetitive code/query changes. There are still scenarios that will require a physical change, but I will branch on that further down.

I am going to start at the dynamic functionality, but I cannot confirm that the error is explicitly occurring there.

I have two items that are attempts at being dynamic. The first loops over a lookup table to truncate the destination tables using a dataflow with a source and OLE DB Command. The second will loop over the same lookup table to define the tables in code and utilize SQLBulkCopy via a Script Task.

The first hurdle tackled was dealing with large datasets. Large meaning size of data. Some examples are XML columns or poorly structured tables containing duplicate information. (For a table with the XML column, the column contains 2 to 4 GB alone)

For those large datasets, they were pieced out of the dynamic SQLBulkCopy by using a normal data flow task in SSIS. A flag was added to the lookup table to determine the tables to exclude. For the table with an XML column, this was still fairly slow, so it was determined to only insert the delta and perform an update for changed rows.

All that to try saying the context of the package that produced an error.

Issue

Exception handling was not defined, since data was not being transformed to some new data. It was essentially a normally backup process without creating a backup file. An error did occur, but it only showed a message of "Exception has been thrown by the target of an invocation.". We are looking into adding exception handling, since that would allow us to get a better error message.

The confusing part begins that following day. The package ran successfully, and the way the package is setup picked up the missing data. No changes to the physical package, and data is only ever added or updated to the main tables. (no deletion of records)

In an attempt to find a root cause, I am trying to step through Event Viewer, where the packages are deployed and DTEXEC exists. I have found the event that states the package failing, but there are no clear events surrounding that timeframe to see a pattern of the failure.

Here are the events that I have found, but all may not actually apply:

Application

VSS: Event Id 12289; Volume Shadow Copy Service Error
SQLISPackage130: Event Id 12291; Package 'MyPackage' failed.

System

DistributedCOM: Event Id 10016; application-specific permission settings do not grant Local Activation Permission
disk: Disk 2 was surprise removed

Questions

Before I go further down this rabbit hole, is this even a viable way to trace that generic of an error message for SSIS?

If it is, then do any of the above events state anything that I could be missing?

As far as it throwing an error one day then not throwing error for the subsequent week (happened last week), is this throwing a flag or smell that I may not be understanding?

Lastly, is there another way to trace this kind of issue that I have not stated above? Again, there is no exception handling in the Script Task, so the available exception message at that level appears to be lost.

Admittedly, this might be something to let go and add the exception handling to trace down the road, if it happens again. Please let me know if I need to clarify anything, or if I am having a misconception on any of the above.


texts: Reason for failed query plan executions in SQL Server

texts:
We are using Azure SQL Database and recently we have experienced a severe performance degradation on our production database. When looking into Regressed queries in Query Store to find out the probable culprit, I found a regressed query where there were a few hundred failed executions of its usual query plan:

I was not able to find any information on how to figure out what exactly went wrong, just that an exception aborted the execution. The problem went away on its own after a couple of minutes.
I queried the sys.query_store_runtime_stats catalog, but saw nothing useful there.
Is there a way to get to the actual failures? Could I set up these errors to be sent out to e.g. Log Analytics automatically (as we're in Azure)?
Thank you.


texts: Run Powershell Script From Agent As Administrator

texts:
I need to recycle an AppPool in IIS on my server when the SQL Server Service starts.

The route I am taking is to have a startup stored procedure which runs an Agent job  that has a Powershell job step.

I have created / grabbed from the Internet a Powershell script which recycles the app pool:

# Load IIS module:
Import-Module WebAdministration
# Set a name of the site we want to recycle the pool for:
$site = "Default Web Site"
# Get pool name by the site name:
$pool = (Get-Item "IIS:\Sites\$site"| Select-Object applicationPool).applicationPool
# Recycle the application pool:
Restart-WebAppPool $pool

This works at the OS level but only when Powershell is run as an administrator (Even though the account I am logged into Windows as is in the Admin group)

Sure enough, if I create the job in agent with a Powershell step containing the above code, upon execution I get the error

  A job step received an error at line 6 in a PowerShell script. The
  corresponding line is '$pool = (Get-Item "IIS:\Sites\$site"|
  Select-Object applicationPool).applicationPool  '. Correct the script
  and reschedule the job. The error information returned by PowerShell
  is: 'Cannot retrieve the dynamic parameters for the cmdlet. Filename:
  redirection.config  Error: Cannot read configuration file due to
  insufficient permissions      '.  Process Exit Code -1.  The step
  failed.

So I need a way to "Run as Administrator" from SQL Server Agent

I had success at the OS level creating a new script which runs the original script with elevated permissions:

If (-NOT ([Security.Principal.WindowsPrincipal] [Security.Principal.WindowsIdentity]::GetCurrent()).IsInRole([Security.Principal.WindowsBuiltInRole]::Administrator))
{
  # Relaunch as an elevated process:
  Start-Process powershell.exe "-File",('"{0}"' -f $MyInvocation.MyCommand.Path) -Verb RunAs
  exit
}
# Now running elevated so launch the script:
& "d:\path\to\my\script.ps1"

which allows me to run the new script from either a non elevated CMD prompt or non elevated Powershell prompt. However, when i try to run from agent as either a Powershell step or CMD step I get

  The machine-default permission settings do not grant Local Activation
  permission for the COM Server application with CLSID  {longguidhere}
  and APPID  {longguidhere}  to the user NT SERVICE\SQLSERVERAGENT SID
  (longguidhere) from address LocalHost (Using LRPC) running in the
  application container Unavailable SID (Unavailable). This security
  permission can be modified using the Component Services administrative
  tool.

I have also tried

Start-Process powershell -Verb runAs -FilePath "d:\path\to\my\script.ps1"

which again, at the OS level worked but when run from Agent gives me the same error.

How can I get either

a: recycle the app pool without requiring elevated priveleges

or

b: run the script to recycle the app pool with elevated priveleges from SQL Server Agent?


texts:
I will probably get voted down for this, but does anyone have a good place to start other than the Best Practices Documentation for using Dynamic memory with SQL server.

We are experiencing issues and keep having to set to static for it to work.  The best practices guide basically says it should all work, but I am looking for some real world issues anyone might have faced.


texts: A question about foreign keys and many to many relationships

texts:
My question is mostly conceptual in nature. I have a database schema that involves a many-to-many relationship between a School table and Student table. This seems sensible to me, given that a school can have many students, and a student can attend many schools. I'm hung up on the problem of whether to include the school as a foreign key in the tables that are directly beneath the Student table in the information hierarchy (Behavior, Attendance, Grade).

In short, the many-to-many relationship between School and Student allows for the possibility that a student referenced in the Attendance table would have multiple schools listed in the linked Student table. This could result in unwanted ambiguity when going over attendance records. (i.e. Did they miss class in school A or school B?) My solution is obvious: include a School foreign key for any tables beneath Student to make their relationship to a given school explicit.

The problem: this seems inelegant. Aesthete that I am, I would like the information to flow through tables, instead of jumping tables.  A convenient solution I've encountered would be to rely on a polymorphic association, wherein the primary key passed to the tables beneath Student (Behavior, Attendance, Grade) provides unique values for both the student and school. Unfortunately, it appears that SQL won't tolerate that approach.

Is there a solution that allows me to enjoy the apparently sensible data flow offered by polymorphic association (illustrated below) without breaking the rules of SQL, or am I trying to have my cake and eat it to?


texts: How to determine or predict space on disk per table row?

texts:
I'm very new to Postgres so my math could be off here ...

This is my table:

CREATE TABLE audit (
    id BIGSERIAL PRIMARY KEY,
    content_id VARCHAR (50) NULL,
    type VARCHAR (100) NOT NULL,
    size bigint NOT NULL,
    timestamp1 timestamp NOT NULL DEFAULT NOW(),
    timestamp2 timestamp NOT NULL DEFAULT NOW()
);

I want to make some estimations on how much space 1 row would occupy. After some reading I've come up with this, is it correct?

1 row =
23 (heaptupleheader)
+ 1 (padding)
+ 8 (id)
+ 50 (content_id)
+ 6 (padding)
+ 100 (type)
+ 4 (padding)
+ 8 (size)
+ 8 (timestamp)
+ 8 (timestamp)
= 216 bytes

I also created this same table in my local Postgres DB but the numbers don't seem to match:

INSERT INTO public.audit(content_id, type, size)
    VALUES ('aaa', 'bbb', 100);

SELECT pg_size_pretty( pg_total_relation_size('audit') );  -- returns 24 kb

INSERT INTO public.audit(content_id, type, size)
    VALUES ('aaaaaaaaaaaaa', 'bbbbbbbbbbbbbb', 100000000000);

SELECT pg_size_pretty( pg_total_relation_size('audit') ); -- still returns 24 kb

Which brings me to think that Postgres reserves a space of 24 kb to start with and as I put in more data it will get incremented by 132 bytes once I go beyond 24 kb? But something inside me says that can't be right.

I want to see how much space 1 row would occupy in Postgres db so I can analyze how much data I can potentially store in it. Maybe I'm missing something very obvious.


texts: The SELECT permission was denied on the (View) object after explicit grant select on view to user

texts:
I have a database with a View Named '1098Statement'
We are doing testing for an application with user name 'appuser'.

I have tried the following solutions to granting select on the view and keep getting error:

Grant Select on View
Add user to db_datareader
Add user to a database role which has select granted on the view

Msg 229, Level 14, State 5, Line 2
The SELECT permission was denied on the object '1098Statement', database 'mydb', schema 'dbo'.

None of them work.

Are there any pointers anyone can provide on troubleshooting this issue.

My only remaining thoughts are to check

the user account to see if there are any explicit "deny" restrictions on either the user or any roles the account belongs to.
check the view to see if there are any explicit deny


texts: How to structure database in order to lookup and store post ID's in one PostgreSQL command?

texts:
I have a table with user info (each user has a unique SERIAL ID), a posts table (each post also has a unique SERIAL ID, and references the ID of the user who posts it), a following table with two columns, each is a user id. Then there is a viewed table with a user id column and a post id column, so a user won't see a post twice.

I currently have an API endpoint /feed and it performs two PostgreSQL commands. The first one gets the last five most recent posts from the accounts that a user follows, but I want the user to not see a post twice, so next I store the ID's of the posts that are sent in the viewed table. I want this to be in one single PostgreSQL command. How could I achieve this?


texts: What if all the non clustered indexes on my table were filtered indexes?

texts:
I have a table that is used heavily. This table has 370,370 records.
When I run the following select:
select  [ImageSize] , the_num = count(*)
from  [dbo].[ProductImages]
group by [ImageSize]
order by [ImageSize]

I get this result:

Basically there are only 5 different imageSize objects in that table.
This is the current table definition:
IF OBJECT_ID('[dbo].[ProductImages]') IS NOT NULL
DROP TABLE [dbo].[ProductImages]
GO

CREATE TABLE [dbo].[ProductImages] (
[ProductImageID]  INT              IDENTITY(1,1)   NOT NULL,
[SeasonItemID]    VARCHAR(5)                       NOT NULL,
[Language]        SMALLINT                         NOT NULL,
[Tier1]           VARCHAR(10)                      NOT NULL,
[Tier2]           VARCHAR(10)                      NOT NULL,
[Gender]          CHAR(1)                          NOT NULL,
[SortOrder]       SMALLINT                         NOT NULL,
[ImageFormat]     VARCHAR(20)                      NOT NULL,
[ImageSize]       VARCHAR(10)                      NOT NULL,
[ImageWidth]      SMALLINT                         NOT NULL,
[ImageHeight]     SMALLINT                         NOT NULL,
[BodenDomainURL]  NVARCHAR(40)                     NOT NULL,
[BodenImageURL]   NVARCHAR(210)                    NOT NULL,
CONSTRAINT   [PK_ProductImages]
PRIMARY KEY NONCLUSTERED ([SeasonItemID] asc,
                          [Language] asc, [Tier1] asc, [Tier2] asc, [Gender] asc,
                          [SortOrder] asc, [ImageFormat] asc,
                          [ImageSize] asc))

GO

And here are the definitions of the current inexes

1 clustered index

4 non clustered  indexes

1 filtered index

and the primary key non clustered defined above
  CREATE CLUSTERED INDEX
  [CIX_ProductImages__ProductImageID]
             ON [dbo].[ProductImages] ([ProductImageID] asc)

  CREATE NONCLUSTERED INDEX
  [IDX_SeasonItemID_Tier1_Tier2_ImageSizeBodenImageURL_INC_BodenDomainURL]
             ON [dbo].[ProductImages]
      ([SeasonItemID] asc, [Tier1] asc, [Tier2] asc,
      [ImageSize] asc, [ImageFormat] asc,
      [BodenImageURL] asc)
             INCLUDE ([BodenDomainURL])

  CREATE NONCLUSTERED INDEX
  [IX_ProductImages_ImageFormat_ImageSize_INCL]
      ON [dbo].[ProductImages]
      ([ImageFormat] asc, [ImageSize] asc)
             INCLUDE ([SeasonItemID], [Language],
       [Tier1], [Tier2], [BodenDomainURL],
      [BodenImageURL])

  CREATE NONCLUSTERED INDEX
  [IX_ProductImages_Language_Tier1_Tier2_ImageFormat_INCL]
      ON [dbo].[ProductImages]
      ([Language] asc, [ImageSize] asc, [Tier1] asc, [Tier2] asc,
      [ImageFormat] asc)
             INCLUDE ([ProductImageID], [BodenImageURL])

This is a filtered index:
    CREATE NONCLUSTERED INDEX
    [IDX_ProdImages_GetProductListingPageDenormalisedData]
        ON [dbo].[ProductImages]
        ([SeasonItemID] asc, [Tier1] asc, [Tier2] asc,
        [ImageFormat] asc, [ImageWidth] asc, [ImageHeight] asc)
               INCLUDE ([BodenImageURL])
               WHERE ([ImageWidth]=(195)
        AND [ImageHeight]=(252)
        AND [ImageFormat]='ProductMain')

Can I expect significant gains by changing the 4 non clustered indexes above to 20 FILTERED INDEXES according to the imageSize?
Most queries use the ImageSize as one of the parameters. It is even in the clustered index.
I could modify the indexes and table structure.
This table is mostly used for reading. There is only 1 write a day, and that would not normally affect many records.


texts: MobiLink: How to export database users to remote database created via Sybase Central

texts:
How to configure a Sybase Central MobiLink project in the way, it's also exporting the database users of consolidated database to a remote database?

In default it appears to only create dba with default password which seems to come direct from dbinit.

A manual trick would be to add some custom SQL to the create .sql-file or execute later some custom code. But I'm wondering whether I can add some customer code, or even better the users direct, to the Sybase Central project so it's always included when I'm using deployment etc.


texts: Covering Index Changes Execution Plan but is not used

texts:
I have the following occasionally slow running query:

SELECT C.CustomerID
FROM dbo.Customers C WITH (NOLOCK)
WHERE C.Forename = @Forename
    AND C.Surname = @Surname
OPTION (RECOMPILE)

CustomerID is the Primary Key on the Customers table. The Customers table also has the following two non-clustered indexes:

CREATE NONCLUSTERED INDEX idx_Forename ON Customers (Forename ASC)
CREATE NONCLUSTERED INDEX idx_Surname ON Customers (Surname ASC)

When I run the query with both a surname and forename entered the query optimiser uses the index 'idx_Surname' as in the following execution plan:

This query takes over two minutes to complete for this particular search and finds no results. For the values entered @Forename has no matches in the Customers table while @Surname matches 31,162 records. When I only search by the @surname the 31,162 records return in under a second with the following plan:

In an attempt to optimise the query for searches containing both Forename and Surname I added the following covering index:

CREATE NONCLUSTERED INDEX idx_Surname_Covering ON dbo.Customers (Surname) INCLUDE (Forename)

The query with both Forename and Surname then returns in less than one second. However, the covering index is not used in the actual execution plan:

So,

Is the covering index required or is there a better way to improve the performance and
Why does the additional covering index cause the change of index in the actual execution plan from idx_Forename to idx_Surname?

p.s. the query above is an isolated example, when in use either surname or forename or both may be searched for and the Customers table also includes other searchable columns with their own indexes. This detail was not considered relevant to the question so I have not included it.


texts: How to design database for monitoring the time trends of article tags?

texts:
My purpose is to monitor which tags increase/decrease rapidly last week on different bulletin boards. Or which tag do new appear. The current table schema of articles is as follows:
article_id e.g. 123
tags e.g. apple,orange
article_created_at e.g. 2021/5/7 01:23
borad_name e.g. database
author e.g. some_person

My current idea is to calculate the amount of each tag by board weekly. But I want to survey other solutions to reduce the development time. Is it ok to choose a time-series database engine or other possible solution? Thanks for your time.


texts: Is it possible to create a database in PostgreSQL move it into other computer and use this database with MS Access? How to do that?


texts:
I have a website where I want to create option for user to remove items from their bag. I am doing this task using many different queries.

Is there way to reduce the number of queries?

I have the following 3 tables:

BAG_TB
BAG_ITEM_TB
ACCOUNT_TB

Here is how I am doing it:

1- Get the bag_ID of user who is using the site. This is by account_id.

select Bag_ID
FROM   [BAG_TB] b
WHERE  Account_ID = @Account_ID;

2- Based on above step, it checks how many records are on BAG_ITEM_TB. This will tell me how many items are in current user bag.

Select Bag_ID
FROM   [BAG_ITEM_TB]
WHERE  [Bag_ID] = @Bag_ID;

3a- If there is 1 record returned from step #2, this means I have to delete bag. So do a delete on BAG_ITEM, BAG_TB, and ACCOUNT_TB.

3b- If there is more than 1 record returned from step #2, then only delete from BAG_ITEM_TB because there is more than 1 item in bag... so do not delete from BAG_TB and ACCOUNT_TB

Below one example of my delete queries:

delete FROM [BAG_ITEM_TB]
WHERE  [Bag_ID] = @Bag_ID
AND    [Product_ID] = @Prduct_ID;

delete FROM [BAG_TB]
WHERE  [Bag_ID] = [Bag_ID];

delete from [IS_ACCOUNT_TB]
WHERE  Account_ID = @Account_ID;


texts: Capture SQL Server queries without third-party tooling and without using deprecated features?

texts:
Is it possible to capture all queries sent to an MS SQL Server, without third-party tooling and without using deprecated features?

I'm looking for something similar to the "general query log" in MySQL.

Here's an example using a third-party tool:

https://blog.devart.com/capturing-sql-server-trace-data.html

Here's an alternative using deprecated features:

https://docs.microsoft.com/en-us/sql/tools/sql-server-profiler/sql-server-profiler?view=sql-server-2017

Is there a non-deprecated, native solution?


texts:
I need some help on reminder data model design with repeat.
Admin can set reminder for any client. Like, a reminder for calling as  client on so and so date, or mailing a client on so and so date. One reminder can be assigned to multiple user so that anybody can react to it.
Tables:- reminder_repeat
repeat_id
name  --> possible values ( Daily, Weekly, Monthly, Yearly )

Tables:- reminder
reminder_id
name
reminder_date
client_id
repeat_id
reminder_status --> 'O' for Open and 'C' for Closed

Table:- reminder_assigned_user
reminder_id
user_id

Here, Logged in user will be able to see the reminders assigned and react to that and mark the status as Completed. I need to make sure that non more than one user is reacting for the same reminder.
Not sure, How to handle the repeat as we need to maintain the status which will mark the reminder as completed. One approach I can think of is to run a batch Job every night which will add entry for that day if there is any repeat.


texts: SSMS Error when creating database "Method not found .. StringCollection"

texts:
I'm using SQL Server Management Studio 2016.
When I try to create a local database by right clicking on Databases and selecting "New Database" I get the following error:

I've tried restarting SSMS and the machine.

I can create the database through SQL, however I get the same error when trying to launch any GUI dialogs for the database. So creating it thru SQL doesn't work around my issue as I need to configure it also (and I don't want to write scripts for everything).


texts:
I am designing a partitioning based solution for a high performance large SQL Server 2016 database. Some of the data will be coming in with hundreds of millions of records per day. During the day we will be also running reporting queries and queries that looks for trends over multiple day and weeks.

My current solution will use 70 days in daily partitions using a dedicated filegroup per partition. After the data moves beyond the 70 day mark, it will go into a weekly partition for 42 weeks which are also using dedicated filegroups per partition, then 12 month and then 6 years, all set up in the same fashion.

We need really high performance and massive scale up capabilities (PB+ range). In order to minimize rework down the line, I am thinking about using multiple files per filegroup/partition for the daily and weekly filegroups/partitions. 4 per daily and 2 per weekly to be exact.

This way we could potentially increase read/load throughput per partition, as well as increase maximal capacity of the partitions (don't ask why, but we are worried to actually need that level of capacity on certain days).

Has anyone ever done it this way and what were your results? Is there any reason not to do it this way other than management overhead?

All the weekly, monthly, and yearly partitions would be in the same database on same server (application design issues, but multi-DB could be an option if properly motivated. Multiple servers or instances are not desirable).

The partition breaks are currently being discussed and evaluated. I chose the values above based on information I received on query patterns. Different number of days is of course possible but I kinda like the 10 weeks' worth of daily partitions.

We do have a seriously high-end datacenter, well 2 actually. We are discussing the purchase of a converged solution specific for this platform and one other. I personally would like to see dedicated AFA's (All Flash Arrays) but there are a some bridges to cross before I can get my hands on those.

I am aware of Data Warehouse Fast Track solutions, but they will not work for us. For one, we will be doing OLTP mostly so the benchmark figures will not be representative for what we will get. Second, they don't scale-up enough (at the moment). Some of the elements that come from the reference architectures, will be used of course but the 'turn-key' SKU's won't be an option. I am an ex-MS PFE so those resources are the first I look at.


texts: How to enable the NoDelay parameter for SQL Server?

texts:
We have a recommendation from performance audit to "enable the "NoDelay" parameter on our database".
I searched through Microsoft documentation and found that this parameter is configurable with SQL Configuration manager :

https://docs.microsoft.com/en-us/sql/tools/configuration-manager/tcp-ip-properties-protocols-tab

However, there is no "no delay" option configurable :

I also search through database advanced options and Windows Registry without success.
Google shows some pictures with the option :

Any idea how to enable this ?

We are running SQL 2008 R2

  Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Jun 28 2012
  08:36:30 Copyright (c) Microsoft Corporation Enterprise Edition
  (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1)


texts: What's the point of WHERE 1=2 for SELECT INTO table query

texts:
If we want to create a new table from an existing one in SQL Server we can do the following

SELECT * into Table2
from Table1
WHERE 1=2

Whats the point of where clause? I tried it without the where clause and it ran fine. I've seen this where clause in many examples on internet but not the reason why it is required.


texts: Informatica MD5 function vs SQL Hashbytes MD5 function

texts:
I am hoping someone has some crossover experience here.  I am attempting to utilize the Hashbytes function within SQL to match a value that is being generated utilizing the MD5 function from Informatica.

Has anyone ever been dove into this issue? My first assumption is that since the systems are different, the actual calculation of the checksum might have minute differences. It also could be how I am attempting to concatenate the values prior to using the hashbytes MD5 function.

I am at a loss, and am beginning to think it won't ever be possible to match these values (as many have said online) Any help would be greatly appreciated.


texts:
Having troubles with this and I'm not sure why..
I'm trying to access a mysql database from another server.

I changed the my.cnf file (uncommented the bind-address to access from all):

port=3306
#bind-address=12.34.56.78
local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# skip-networking

I set up the user, and the user works: (I know "ALL" is bad, but I'm just trying to get this to work...)

GRANT ALL PRIVILEGES ON 'MY_DATABASE'.* TO 'MY_USERNAME'@'12.34.56.78' IDENTIFIED BY 'MY_PASSWORD';

I restarted mysql:
/etc/init.d/mysqld restart

I opened up the port from within plesk, and then restarted the whole server. (It's a VPS)

now, when I try and telnet from my desktop, it connects, shows a bunch of garbage text then closes the connection 5 seconds later.

I'm trying to connect with php:

<?php

mysql_connect("DOMAIN.COM:3306", "MY_USERNAME", "MY_PASSWORD") or die(mysql_error());
echo "Connected to MySQL<br />";

?>

and I get a "Can't connect to MySQL server" error after a 10 second timeout.

The log files are empty, only showing when I restarted mysqld.

Any ideas or tips on how to troubleshoot this?


texts: CLR Procedure with XML parameter returns Data at the root level is invalid. Line 1, position 1

texts:
I have following CLR stored procedure, which is throwing error on this command

xmlDoc.LoadXml(inputXml);

code

    public static int spGetTaxOfficeXML(SqlXml _inputXml)
    {
        // this procedure rename Row elements name with NodeName attribute value
        string inputXml = _inputXml.ToString();
        XmlDocument xmlDoc = new XmlDocument();
        xmlDoc.LoadXml(inputXml);

// procedure Logic

        SqlContext.Pipe.Send(inputXml);
        return 0;
    }

it works well, when the XML text is inside variable inside visual studio as a text. But when i upload code to the SQL server as CLR, and try to execute it from SQL Management Studio:

DECLARE @XML XML
SET @XML = '<NodeA><NodeB></NodeB><NodeC AttributeX=""><Row NodeName="RowA" AttributeA="" AttributeB="abcd" AttributeC="efgh" /><Row NodeName="RowB" AttributeA="wxyz" /><Row NodeName="RowC" AttributeB="qwer" AttributeC="tyui" /><Row NodeName="RowD" AttributeA="stuv" AttributeB="erty" AttributeC="fghj" /></NodeC></NodeA>'

EXEC dbo.spGetTaxOfficeXML @XML

then this error is throwed:

Msg 6522, Level 16, State 1, Procedure spGetTaxOfficeXML, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "spGetTaxOfficeXML":
System.Xml.XmlException: Data at the root level is invalid. Line 1, position 1.
System.Xml.XmlException:
   at System.Xml.XmlTextReaderImpl.Throw(Exception e)
   at System.Xml.XmlTextReaderImpl.ParseRootLevelWhitespace()
   at System.Xml.XmlTextReaderImpl.ParseDocumentContent()
   at System.Xml.XmlLoader.Load(XmlDocument doc, XmlReader reader, Boolean preserveWhitespace)
   at System.Xml.XmlDocument.Load(XmlReader reader)
   at System.Xml.XmlDocument.LoadXml(String xml)
   at StoredProcedures.spGetTaxOfficeXML(String inputXml)

I tried to solve the error with following code, because i though the utf8 byte could cause the error, but it did not help.

// if byte mark exception happens
    string _byteOrderMarkUtf8 = Encoding.UTF8.GetString(Encoding.UTF8.GetPreamble());
    if (inputXml.StartsWith(_byteOrderMarkUtf8))
    {
        inputXml = inputXml.Remove(0, _byteOrderMarkUtf8.Length);
    }

What is wrong here ?


texts: Include scripts for Indexes on Alter Views

texts:
After a Developer altered an Indexed View to add a comment, the clustered index that was created on it was dropped with no warning.

This caused a failure as there are a couple of procedures that have the WITH (NOEXPAND) hint.

I have SSMS set up currently so that right-clicking the VIEW and selecting SCRIPT VIEW as, CREATE TO includes the index definition.

Is there a way to script out indexes on VIEWS for ALTER to?


texts:
Let's suppose that we are planning to execute a "risky action" on a database. Naturally we've tested on non-production databases and everything seems to be fine. We backup the database. Then we execute the risky action. After some number of additional transactions, we realize a horrible mistake. Now we'd like to "undo" the risky action but maintain the subsequent transactions.

I am aware that crisis tools exist that will help undo transactions based on transaction logs. Since I don't actually have a crisis, I am curious about whether this can be accomplished without those tools in the right situation.

I have in mind that something like the following could be accomplished, but I don't know whether it's actually possible on SQL Server.

Timeline of our invented crisis

Make full backup of database (B)
Execute risky action (T1)
Transaction 2 occurs (T2)
...
Transaction n occurs (Tn)
Realize that we need to undo T1.

"Undo"

Restore B into a new database (B')
Somehow apply T2 through Tn to B'

Is this something that can be done with built-in commands? I've tagged SQL Server 2008 since that is the package I most frequently use. However, if a solution also works on SQL Server 2005 (or only on 2008 R2), I'd like to hear about that too.


texts: Global Read Lock for Snapshot of MySQL Data

texts:
I have a database that is entirely comprised of MyISAM tables(yes, I did not create it). I need to create a slave and start replication so we could point read-only queries there. Here is my idea: Perform a Veritas cluster snapshot of the files, move them to the new place, and start replication.

Before I can start the cluster snapshot, I need to read lock so I don't lose any transaction (i.e., no SQL commands against the MyISAM table during the snapshot). I tried doing the following code but whenever I come out of the shell, the database becomes writable.

Is it the case that the read_only allows my ID to write since I own the lock? I am trying to google, but every article talks about mysqldump. Please help.

mysql> SET GLOBAL READ_ONLY=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> create table test2(a integer);
ERROR 1050 (42S01): Table 'test2' already exists
mysql> create table test3(a integer);
Query OK, 0 rows affected (0.00 sec)


texts: Unable to login to SQL Server using SSMS and SQL agent failing

texts:
I have SQL Server 2008 R2 ENT insalled on Windows server 2008 R2 ENT. I am unable to login to SQL Server locally after the installation. Getting below error:

  Connection handshake failed. An OS call failed: (80090331)
  0x80090331(The client and server cannot communicate, because they do
  not possess a common algorithm.). State 56.

The error led me to this article and I updated my DB engine to 10.50.6542.0. But still unable to login. What should I do in order to allow my SSMS to connect locally. Also the second issue is SQL agent service is not starting. It fails with below error:

  Agent could not be started ( unable to connect to server (local),
  SQLserveragent cannot start).

How to fix this as well? Below is my environment details:

Windows server 2008 R2 Ent. SP1
SQL Server 2008 R2 Ent (10.50.6542.0)
.Net version 4.6.01055

SCHANNEL Status:

  SSL 2.0:  Client: DisabledByDefault = 1 Enabled = 0  Server:
  DisabledByDefault = 1 Enabled = 0

  SSL 3.0:  Client: DisabledByDefault = 1 Enabled = 0  Server:
  DisabledByDefault = 1 Enabled = 0

  TLS 1.0:  Client: DisabledByDefault = 0 Enabled = 1  Server:
  DisabledByDefault = 0 Enabled = 1

  TLS 1.1:  Client: DisabledByDefault = 0 Enabled = 1  Server:
  DisabledByDefault = 0 Enabled = 1

  TLS 1.2:  Client: DisabledByDefault = 0 Enabled = 1  Server:
  DisabledByDefault = 0 Enabled = 1

Please let me know if you need any further details.

Update:

I made some progress on it. I installed ADO.NET - SqlClient (.NET Framework 3.5/.NET Framework 2.0 SP2) referring link https://support.microsoft.com/en-us/kb/3135244. I am now able to connect SQL server using SSMS locally and also able to start SQL agent service. Yipee.

But now the new issue is appearing. I can't connect the server remotely using windows authentication. I get below errors in windows eventviewer logs when I try to connect it. Also, no errors in SQL server error logs.

  Log Name:         System Source:         Schannel Event ID:      36888
  Task Category: None  Level:          Error Keywords:        User:
  SYSTEM Description: The following fatal alert was generated: 40. The
  internal error state is 1205.   Log Name:       System Source:
  Schannel Event ID:      36874 Task Category: None  Level:
  Error Keywords:        User:         SYSTEM Description: An TLS 1.0
  connection request was received from a remote client application, but
  none of the cipher suites supported by the client application are
  supported by the server. The SSL connection request has failed.


texts: PostgreSQL : Restrict (another) superuser from accessing database

texts:
I'm new in PostgreSQL and looking to find if this is possible.

There are two superusers, 'postgres' (which is default superuser from PostgreSQL) and 'super2'.

Superuser 'super2' creates a new database named 'Movies' and being the owner of it.

Is it possible to restrict superuser 'postgres' to access the database 'Movies' so that only superuser 'super2' could access it, because he is the owner of the database.

If it is possible, then how to make it happen?


texts:
Closely related to this question from a few weeks ago, but the answers didn't discuss how to do the dirty deed. Here's the situation:

We now 15 GB data in a database, but a logging bug in an application ran wild and ran this up to 80GB data giving us DB files around 130GB. We've fixed the bug, cleared out the affected tables, and I'd like to get some of the space back, maybe bring the DB back down to 40GB or so.

The biggest reason I'd like to do this is so that we can more easily restore backups to smaller drives on virtual test instances.

I get it: Shrink is evil, and will fragment both indexes and on-disk file. I'm sold. This is a one time event.

So how can I minimize the pain?
Seems like I should:

Use DBCC SHRINKFILE (DataFile1, 40000); to aim for 40 GB.
Immediately use some smart reindexing to reorganize and rebuild indexes
Defrag physical disks

Will that appropriately mitigate the side effects of a shrink? Or is this only going to end up on my application for the Evil League of Evil?


texts: Columnstore Aggregate Pushdown doesn´t work

texts:
Recently I started investigating columnstore indexes for some warehouse-behaving part of our system.

What I can´t achieve yet is to make aggregate pushdown work. I've checked data types and all requirements, but still it doesn't work.
Just to be sure, I just ran query from this question and I see that execution plans for all data-types are exactly the same.

The version of SQL Server is SP1-CU5.

Do you know what else could I check?

update: I think I got it, I have Standard edition and this is one of standard edition limitations.


texts: secure specific tables in Data Warehouse

texts:
I'm newbie in this domain, I have a project that aim to create java application (Desktop Application) to manage roles based on users profile in a data warehouse.

The thing that I'm not able to imagine is : How can I give some permissions to a specific table (eg. Fact table), and other permissions to others (eg. Dimension table)

Generally, how can we protect a spesific table in a data warehouse ?


texts: Moving postgresql data to different drive

texts:
I am using AWS as my cloud environment. I installed PostgreSQL on the same drive as my root instance volume. I have attached and mounted the second drive to my instance. Now I want to move all my PostgreSQL data to the different drive. I am still in development mode so I can delete the old data if it makes transfer easier at this point of time. What is the best way to do this?

Is PostgreSQL tablespace something which I should look at?


texts: Slow checkpoint and 15 second I/O warnings on flash storage

texts:
Last couple of weeks we've been working on getting to the root cause of what could likely be the cause of the occurrence of these I/O issues and slowdown of the checkpoints.

At first glance it looks to be clearly an I/O subsystem error and the SAN admin was to be blamed for it. But recently we changed the SAN to utilize Full Flash but as of today the error still pops up and I have no clue as to why since every metric, whether wait stats or any other metric, that I run to check if SQL server is a possible culprit seems out to return normal.

It doesn't really add up. It could also be very likely that something else is chewing the disk and SQL Server is becoming victimized here...but I am not able to find out what?

Dbs are in Availability Groups and as and when these events occur we do see role changes and flip overs occurring along with timeouts.

Any help in figuring this out would be highly appreciated. Let me know if any further details is needed.

Error msgs. below

  SQL Server has encountered 14212 occurrence(s) of I/O requests taking
  longer than 15 seconds to complete on file [E:\MSSQL\DATA\ABC.mdf] in
  database [ABC] (7).  The OS file handle is 0x0000000000000D64.  The
  offset of the latest long I/O is: 0x0000641262c000

  SQL Server has
  encountered 5347 occurrence(s) of I/O requests taking longer than 15
  seconds to complete on file [E:\MSSQL\DATA\XYZ.mdf] in database [XYZ]
  (7).  The OS file handle is 0x0000000000000D64.  The offset of the
  latest long I/O is: 0x0000506c060000

  FlushCache: cleaned up 111476 bufs with 62224 writes in 925084 ms
  (avoided 19 new dirty bufs) for db 7:0
              average throughput:   0.94 MB/sec, I/O saturation: 55144, context switches 98407
              last target outstanding: 10240, avgWriteLatency 14171 FlushCache: cleaned up 5616 bufs with 3126 writes in 248687 ms
  (avoided 3626 new dirty bufs) for db 6:0
              average throughput:   0.18 MB/sec, I/O saturation: 10080, context switches 20913
              last target outstanding: 2, avgWriteLatency 3

Here's the virtual file stats info over a 30 minute span:

And wait stats as well:

Here is the note from the system architect:

  We separate workloads for high I/O intense workloads (such as DB) so that we only have one per host. The specs for the current host is Dell R730 with 16 cores of Xeon E5-2620 (2 sockets), 512GB, and 2x10G interconnects for storage. No other VM on the cluster nor host are experiencing these issues. Storage for VMs and workloads is on Pure FA-x20.

General System Information:

SQL Server 2012 sp3-cu9 (Enterprise Edition)
Total RAM: 128 GB
Total DB size: Close to 1 TB


texts: CPU usage goes up at specific time of day probably because of Mysql

texts:
My server is showing high cpu usage between 10 am and 2 pm (IST) since last few days. The traffic is prretty much constant throughout the day with no significant jump or fall at any of these time. Even the background tasks are not running at that time. Analyzing the access logs, there is nothing specific about these time. Yet the cpu usage remains near 140% for 3-4 hours continuously.

We are using ubuntu 14.04, mysql 5.7, apache2, django1.10

Using top, I figured out the most of the CPU (around 90% +) is being used by Mysql continuously and the RAM usage is also too high.

Can anyone suggest me what to look for and how to reach a solution or even find the culprit.

Including the content of my.cnf

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld_safe]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
explicit_defaults_for_timestamp

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address    = 0.0.0.0

log-error       = /var/log/mysql/error.log

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

Also, conf.d/ is empty. Seems that they have removed most of the default options from my.cnf

RAM: 2GB. Earlier in django settings there was CONN_MAX_AGE = 100 but i have removed it as i read that almost always persistent connection serve no good now and will try to see the effect tomorrow afternoon.


texts: Are the index here will suffice to make the query faster?

texts:
This is the continuation of the question, What column/s do I need an index for my queries?, as Rick James advices me to ask a new question.

Here is the simpler code from my queries, and how will I implement the index in the column based on from what I understand while studying about it.
Note: fields and values are changed for example purposes only.

1) INDEX in id

SELECT a, b ... FROM users_table WHERE id = '1'

2) INDEX in user_email

SELECT a, b ... FROM users_table WHERE user_email = 'me@email.com'

3) INDEX in user_link

SELECT a, b ... FROM users_table WHERE user_link = 'www.somelink.com'

4) INDEX in user_recover_link

SELECT a, b ... FROM users_table WHERE user_recover_link = 'code'

5) INDEX in id, side note: I may try Rick James' answer related for pagination.

SELECT a, b ... FROM posts_table WHERE id > 0 ORDER BY date DESC LIMIT 0, 10

6) INDEX in id

SELECT a ... FROM posts_table WHERE id > 0

7) INDEX in category

SELECT a, b ... FROM posts_table WHERE category = 'some-category'
     ORDER BY date DESC LIMIT 0, 10

8) INDEX in category

SELECT a, b ... FROM posts_table WHERE category = 'some-category'

9) INDEX in post_url

SELECT a, b ... FROM posts_table WHERE post_url = 'some-url'

10) INDEX in user_url

SELECT a, b ... FROM users_table WHERE user_url = 'some-url'

11) INDEX in user_id

SELECT a ... FROM posts_table WHERE user_id = '1' ORDER BY date DESC

12) INDEX in user_id

SELECT a ... FROM posts_table WHERE user_id = '1'

13) INDEX in msg_receiver_id, msg_sender_id, date

SELECT a, b FROM msgs_table m1
    WHERE (m1.msg_receiver_id = '1' OR m1.msg_sender_id = '1')
      AND EXISTS (SELECT max(m2.date) maxDate FROM messages m2
            WHERE m1.msg_receiver_id = m2.msg_receiver_id
              AND m1.msg_sender_id = m2.msg_sender_id
            GROUP BY msg_receiver_id, msg_sender_id
            HAVING m1.date = maxDate)
    ORDER BY m1.date DESC

14) INDEX in id

SELECT a, b ... FROM users_table WHERE id IN ('1', '2', ...)
    ORDER BY FIELD (id, '1', '2', ...)

15) INDEX in msg_url

SELECT a, b ... FROM msgs_table
    WHERE msg_url IN ('some-url-1', 'some-url-2')
    ORDER BY date ASC

16) INDEX in msg_receiver_id, msg_status

SELECT a, b ... FROM msgs_table
    WHERE msg_receiver_id = ?
      AND msg_status = 'unread'

NOTE: Since id is a PRIMARY KEY I do not need to use index anymore. Is this an okay to increase the queries speed performance?


texts:
It seems xtrabackup is not backing up binary logs, although it creates xtrabackup_binlog* with apparently correct information inside.
Should the binary logs be copied manually beforehand or is this a bug?


texts:
When sp_BlitzIndex does index suggestions, the question in my head is as below:

Yes the suggested index would bring a lot of speed benefit when doing select statements. But there are also another costs for these indexes which are delete, insert, update queries

Do sp_BlitzIndex script take into consideration delete, insert, update costs as well? or only it does evaluate how much speed benefit would come with select statements?

Moreover, how can i list bad indexes via sp_BlitzIndex? and how are they determined to be bad?

Thank you very much for answers


texts: PostgreSQL Race condition and Serializable

texts:
Hello I try to debug a tricky race condition in my app (golang, pq, sqlx, postgresql Debian 10.6-1.pgdg90+1).

I have a table

CREATE TABLE j (
  id              BIGSERIAL PRIMARY KEY        NOT NULL,
  "type"          VARCHAR(10)                  NOT NULL,
  complete        BOOLEAN        DEFAULT FALSE NOT NULL,
  started_at      TIMESTAMP WITH TIME ZONE
                                 DEFAULT now() NOT NULL,
  error           TEXT           DEFAULT ''    NOT NULL
);

CREATE INDEX jtype ON j("type");
CREATE UNIQUE INDEX jtype_one_active ON j("type") WHERE complete = false;

to track some jobs

The job is created with complete=false, and later updated to complete=true. Errors are logged, so there are multiple rows per "type", but only one job should be active.

First, with a naive approach, I did that:

SELECT j.*
            FROM j
            WHERE "type" = ...
            ORDER BY started_at DESC
            LIMIT 1

-- some in app business logic

INSERT INTO j ("type")
            VALUES ($1)
        RETURNING *

It worked ok, but running parallel test I found a race condition on empty table:

Process A: SELECT ...
Process B: SELECT ... (both select no rows)
Process A: INSERT / UPDATE (process A inserts a new row AND proceeds to update it to complete=true, so jtype_one_active index won't fire)
Process B: INSERT ...

So the job is running twice, even if process A completes with no error.

I wrapped CREATE + INSERT into a single Serializable transaction (also, added FOR UPDATE to CREATE. And it worked for the most part.

The real question starts here:

But when I added more concurrency to my test I found, that sometimes the job is still running twice.

Here's what I managed to debug, but it might not be 100% true:

The table is empty

Process A: begins transaction
Process A: SELECT (no rows)
Process A: CREATE
Process B: begins transaction
Process B: runs SELECT and select is running slow due high concurrency
Process A: commits transaction
Process B: SELECT completes, no rows is found
Process B: INSERT (no serialisable error is fired!!)
Process B: commits transaction (no error is fired!!)

Does a long running SELECT FOR UPDATE query somehow "escapes" Serializable Isolation? Or there's another flow in my script?

Update

I've managed to get the same behaviour on just 2 parallel processes and record a PostgreSQL log: https://pastebin.com/HgzwC9Wh

Log looks close to my previous guess, though I'm not sure, if SELECT in process B really takes a lot of time.

For some reason, after the A transaction commits, the B SELECT doesn't see committed data! It returns no rows.

I tried to replay this from psql shell with no success.


texts:
I have implemented Transactional replication between two databases. Now I have to implement Transparent Data Encryption (TDE) on the Publisher database.
Is it possible to implement TDE in the master database only? Will the replicated data be encrypted along with replication?


texts: How to set up SQL Server online with public Wi-Fi

texts:
I am doing a school project where I have to set up a SQL Server database for my teammates to work on. Currently, I am able to host the server online with my home router, but I am facing problems bringing the database server to school, where the Wi-Fi does not allows me to do port forwarding.
I only have one laptop and the database server needs to be available online while connected to the school's secured Wi-Fi.
Hence, what would be the best way to host the database server in school? (using SQL Server) Secureness of the database is not an issue.
Additional information:

SQL Server is on my laptop, I cant seem to find the DNS server address to set the IP configuration? (Not shown in cmd)
There is only connection specific DNS suffix, link local ipv6, ipv4 address, subnet mask, default gateway, and connection specific DNS suffix, ipv6 address, link local ipv6 address, default gateway.
I can connect to my own server but no one else can connect to it as I cannot establish an internet connection without a DNS server IP dynamically given by my school Wi-Fi
We use another computer with the same iPv4 properties (IP address) on the same Wi-Fi and the same server name on SQL Server and this issue came up:

named pipes provider: error 40, could not open connection to server


texts: Create Oracle ASM Disks Without Installing Oracle

texts:
I have been asked to setup workstations (VMs) for an instructor's Oracle 11g R2 Workshop. I am finding the documentation that was downloaded from the Oracle Academy site extremely underwhelming. I am supposed to be configuring the environment with a set of ASM disks as LO devices that the students will then use to create an ASM disk group when they install Oracle Grid and as disks for the Fast Recovery Area.

My understanding is that the student will be installing Oracle Grid, Oracle Database, then creating a database, and that I do not need to have Oracle installed beforehand. However, the documentation makes use of the oracleasm command which comes with the Oracle installation.

Is there a way to set this up without installing Oracle? Has anyone ever setup/taught these workshops? I tried downloading the Oracle ASMLib tool here but it says it needs oracleasm as a dependency.