texts: SQL Server Frozen Ghost Cleanup workaround needed

texts:
I have several tables with amount of rows between 5M and 1.5G

Each table has its BLOB field, which size varies from 100 bytes to 30 MBytes and which is stored as 'large value types out of row' = ON

Tables are stored in different filegroups with 3-4 files each on different disk @ different LUNs @ very fast SAN

Every day these tables grow for 5-100 Gb in size and with 600k - 1.5M rows

After certain amount of time, which varies from 2 weeks to 6 months some of the rows are deleted or moved to archive DB, so - there is no any rows in worktables that older than 6 months.

Current configuration of server:

SQL server engine is 2008 R2 SP1 Enterprise @ 24 cores, @ 64Gb RAM
SQL Server runs with extra startup flags:

  -T 3640; (Eliminates sending DONE_IN_PROC messages to client for each statement in stored procedure. This is similar to the session setting
  of SET NOCOUNT ON, but when set as a trace flag, every client session
  is handled this way)

  -T 1118;(Switches allocations in tempDB from 1pg at a time (for first 8 pages) to one extent.)

  -T 2301;(Enables advanced optimizations that are specific to decision support queries. This option applies to decision support processing of
  large data sets)

  -T 1117;(Grows all data files at once, else it goes in turns.)

  -E; (Increases the number of extents that are allocated for each file in a filegroup. This option may be helpful for data warehouse
  applications that have a limited number of users running index or data
  scans)

  -T 834; (Causes SQL Server to use Windows large-page allocations for the memory that is allocated for the buffer pool,
  http://msdn2.microsoft.com/en-us/library/aa366720.aspx,
  http://support.microsoft.com/kb/920093)

SQL Server uses Large Page Extensions
SQL Server utilizes fast file initialization option
AUTOSHRINK is OFF for all the databases

The problem is - that starting from some point of server's uptime (from couple of days to months) GHOST CLEANUP process refuses to work out forced cleanups and simply do its usual job - cleans up several pages in several seconds (which is seen thru Extended Events), which is not suitable, because it is not able to clean up all the deleted rows

The problem persists from the times of SQL Server 2005 RTM Enterprise

How I was tried to solve the issue:

Tried to force SCAN operations on clustered indexes of the tables
Tried to force SCAN operations, which involving all the contents of BLOB column on clustered indexes of the tables
system sp_clean_db_free_space & sp_clean_db_file_free_space
manually dbcc cleanpage(@dbid , @fileid, @page) for all the files and pages in DB
clustered index rebuilds and reorganizing
recreating database
DBCC FORCEGHOSTCLEANUP
When I run the query:

select *
from sys.dm_db_index_physical_stats(db_id(), object_id('ProblemTable'), 1, 0, 'detailed')

I see millions and tens of millions ghost records, but only for
allocation unit type of LOB_DATA

The only things, that help:

stopping the server with SHUTDOWN command or restarting the whole
host - it helps, after restart GHOST CLEANUP process runs some hours
and actually cleans all the ghosted records
DBCC SHRINKFILE with EMPTYFILE option - moving all the data from one file to other or newly created files cleans up ghost records in this file only - the problem is that I really hate shrink operations. And this takes 3-4 days for ONE file

the question - is there exists any programmatic (preferable) or maintenance way to force GHOST CLEANUP without server downtime at all, because server downtime costs too much, even unacceptable - its from thousands to tens of thousands $ per hour

Problems were noticed alike mine are here:

http://support.microsoft.com/kb/932115
http://www.sqlservercentral.com/Forums/Topic496244-149-1.aspx

And just the same is here:

http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/c023e51c-92ad-471b-89a6-8c93732ee6cb/


texts:
I have a Notification table contains about 100 million rows host in Amazon RDS with 1000 IOPS, and I want to delete those rows older than one month.

If I do DELETE FROM NOTIFICATION WHERE CreatedAt < DATE_SUB(CURDATE(), INTERVAL 30 day);, all the IOPS will be taken, the process will take hours, and a lot of new entries cannot be inserted due to "Lock wait timeout exceeded; try restarting transaction".

I was trying to do the way describe in here:http://mysql.rjweb.org/doc.php/deletebig
However, I am using UUID instead of increment ID.

What is the correct and efficient way to delete those rows while not affect new data being insert/update?


texts: A good way to verify if the update has no problems?

texts:
I don't know if this is a stupid question, but I'm upgrading my test instances to SQL Server 2014. I've installed SP1, after I will install Cumulative Updates.

It's working fine, but is there a way to test it? Like some queries or procedures that would fail with a bad upgrade?

From sqlserverbuilds, I can see that I have the last SP1 ( the only one for SQL2014 ).


texts: Storing prices in SQLite, what data-type to use?

texts: I am using SQLite and need to store prices. SQLite's REAL data-type says it uses floating-point which is unacceptable storage for prices. Is there a data-type besides TEXT that I can use to store prices numerically so they sort correctly?


texts: PostgreSQL PAM authentication with Docker

texts:
I want to authenticate Linux host's users with the PostgreSQL database running in the docker container (If PG is installed natively, it works fine). I have a compose file like:
version: "3"

services:
  postgres:
    image: postgres:latest
    container_name: pg
    volumes:
      - /etc/passwd:/etc/passwd
      - /etc/shadow:/etc/shadow
      - /etc/pam.d/:/etc/pam.d/
    ports:
      - 5432:5432
    environment:
      POSTGRES_USERNAME: postgres
      POSTGRES_PASSWORD: postgres

My pg_hba.conf file is:

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# PAM Authentication
host    all             all             0.0.0.0/0               pam
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust

Under /var/lib/postgresql/data but I get the error when executing inside the container:
root@21cdb0e36245:/var/lib/postgresql/data# psql -h 127.0.0.1 -U testuser
Password for user testuser:
psql: error: FATAL:  PAM authentication failed for user "testuser"

Am I missing something in the volume mounts?


texts: Using start and end event logs to create a table/view containing spans between the times of each log


texts: Permissions granted are insufficient for this operation

texts:
I am using Report Builder 3.0 and have been trying to run reports using AdventureWorks 2014 in SQL Server Express 2014. My report Server is http://John-PC/ReportServer_SQLExpress.

When I click Run in Report Builder I get a message saying The permissions granted to User ‘John-PC\John’ are insufficient for performing this operation.

I have confirmed that there is a login for John-PC\John and that it is assigned to user John-PC\John. I have tried to give permissions using, among others: exec sp_addrolemember 'db_owner', 'John-PC\John' which resulted in command completed successfully but not in changing the error message.

When I connect to SQL Server my own username is John-PC\John; I suppose that is related but my own permissions seem fine.
I’ve been fighting this for about 3 weeks.

Can someone give me a different approach or correct the one I’m using so that I can run reports in Report Builder ?
Thank you very much.
John


texts: Change of index key columns order affects performance

texts:
I have the following table in my database:

create table [dbo].[tb_StatusLog](
[RequestID] [int] not null,
[statusID] [int] not null,
[startTime] [datetime] not null,
[endTime] [datetime] null,
)

with the following non-clustered index

create nonclusteredindex [index1] on [dbo].[tb_StatusLog]
(
requestID asc,
statusID asc
)
include ([startTime])

and the following table-valued function fn_getTable

select requestID, max(startTime)
from tb_StatusLog
where statusID=2
group by requestID, statusID

after running profiler trace and running the results by db engine tuning advisor, it proposed that I create the following index (which is the same index I have but with the key columns reversed):

create nonclusteredindex [index2] on [dbo].[tb_StatusLog]
(
statusID asc,
requestID asc
)
include ([startTime])

Now, when running fn_getTable, the execution plan uses index2 instead of index1 and the performance improved.

Why is that?


texts: Slow SQL query from app, fast from SQL Studio

texts:
I have a query that takes 30+ seconds when called from app, but takes <1sec when called directly from SQL SMS.

Here is the query :

SELECT T0.pk
    FROM   products T0
    WHERE  (( ( T0.pk IN (SELECT T1.target
                               FROM   catrel T1
                               WHERE  ( T1.source = 87960962008461 )
                                      AND ( T1.type = 87961011815221 ))
                AND T0.pk NOT IN (SELECT T2.target
                                       FROM   varexc T2
                                       WHERE  ( T2.source = 87960962008461 )
                                              AND (
                                      T2.type = 87961020990261 )) )
               OR ( T0.pk IN (SELECT T3.target
                                   FROM   varinc T3
                                   WHERE  ( T3.source = 87960962008461 )
                                          AND ( T3.type = 87961021066258
                                              )) ) )  )
           AND ( T0.type IN ( 87961022300198, 87916102426706 ) )
    ORDER  BY T0.p_code

I have no idea where to start debugging that. When run directly in the SQL databse, the response in instant :

The query does appear in the "recent expensive queries" (only when run from another client)

PS: SQL Server does not recommend any additional indexes.


texts: Dummy Objects vs. NULLs: optionality for three-way relationship

texts:
In a nutshell, I'm trying to model a relationship and can't decide whether to allow NULLs or to require values and use dummy objects (in place of NULL).

Here are the details.

I have three classes:

Client: a person (or company) that wants to buy some resources.
Broker: a person that sells resources.
Resource: a more-or-less infinitely renewable (in the sense that several clients can purchase the same one) product.  For example, access to a proprietary data set, use of an image library, contractor/professional services.

The typical use case is that a Client goes through a Broker who provides the desired Resource.  Brokers have areas of expertise and only sell some of the available Resources, and multiple Brokers may sell the same Resource.  So my initial data model looked like this:

fig 1. naive many-to-many-to-many.

It's basically a many-to-many-to-many relationship.  The problem is that I can't tell which Resources have been purchased by a given Client, and vice versa.  So I came up with this:

fig 2. three-way relationship.

Now a Client buys an Instance of a Resource from a Broker.  This is what happens most of the time, but there are some exceptions that the data model must accommodate, and that this one doesn't.

A few Resources are available as self-service and may be purchased w/out a Broker.
A Client can buy something from a Broker which is not a Resource (think ad-hoc consulting, or a free consultation, for example).
This model no longer captures which Resources may be sold by a particular Broker; if a Broker is allowed to sell a Resource, but hasn't yet, there's no way to record that.

Basically, I need a way to make any one of the three classes optional in any given instance of this otherwise three-way relationship.  So I can associate a Client w/ a Resource, but no Broker; and a Broker w/ a Resource, but no Client; and so on.

I can see two different approaches:

Option 1: Change all the "1..1" cardinalities in figure 2 to "0..1" so that each class is optional in the Instance relationship.  This would allow me to handle exceptions listed above, but then I'd have to deal with nulls and implement constraints at the application layer (to prevent an Instance with only one related object, for example).

Option 2: Keep all the "1..1" cardinalities, and use dummy objects to fill in the NULL holes in the model.  For example, use a dummy Broker called "self-service" to deal with Clients purchasing Resources w/out a Broker.  This would keep my data model simple, but I suspect dealing with all the special cases around dummy objects will be worse than using NULLs and application-enforced constraints on data.

So here's my specific question: Is there a standard data modeling design pattern that captures the sort of three-way relationship I've described above?  And if not, what are the other pros and cons I haven't yet thought of to each of the approaches above?


texts: Is a separate execution plan generated for each dynamic SQL query that is executed within a stored procedure?

texts:
If I have a stored procedure and I re-write the logic so that some of the queries are executed as dynamic SQL, will those queries now get their own individual execution plans in addition to the stored procedure getting it's own execution plan?

If so, could this be potentially more globally efficient on my server if there are other ad-hoc queries that exactly match the dynamic SQL queries in my procedure (or even if there are other procedures that use dynamic SQL to call the same exact queries as well)?


texts: Alter default privileges for a certain column

texts:
I want a RoleGroup to have certain privileges on a specific column in a table ….the roleGroup not DB owner :

grant  SELECT, INSERT ,UPDATE  (colOne) on table schemaOne.tableOne
to roleGroup;

When I want alter default privileges of this column (to include future users), I get ERROR saying can’t alter default privileges of one column only :

alter default privileges for role DB_OWNER in schema schemaOne grant
select,insert,update(colOne) on table schemaOne.tableOne to
roleGroup;

which forced me to do this :

Alter Default priviliges for role DB_OWNER in schema schemOne grant
select,insert,update to roleGroup ;

what’s the point of the first grant then ?!! Or am I making some mistake ?


texts: increment row value does not work properly with where condition

texts:
I have created sql fiddle as below
it is working fine if i remove where condition(WHERE rg.groupid= 1)

output without where condition

| ID | RANK | MYORDER | PREVIOUSRANK |
|----|------|---------|--------------|
|  3 |    7 |       1 |            7 |
|  2 |    3 |       2 |            3 |
|  4 |    3 |       2 |            3 |
|  1 |    2 |       3 |            2 |

i want my result order by(ORDER BY mro.rank DESC) but it does not return desired result

SQL Fiddle

CREATE TABLE rankGroup
    (
     id int auto_increment primary key,
     groupid int
    );

INSERT INTO rankGroup
(groupid)
VALUES
(1),(2),
(1),(1);

CREATE TABLE myRankorder
    (
     id int auto_increment primary key,
    groupid int,
     rank int
    );

INSERT INTO myRankorder
(groupid, rank)
VALUES
(1,2),(2,3),
(3,7),(4,3);

Query 1:

SELECT mro.id, mro.rank,
         IF (@prevrank = mro.rank, @rownum, @rownum := @rownum + 1) AS `myorder`,
         @prevrank := mro.rank AS `previousRank`
    FROM `myRankorder` mro
    INNER JOIN `rankGroup` rg
        ON rg.`id` = mro.`groupid`
    cross JOIN
        (SELECT @rownum := 0, @prevrank := 0) const
    WHERE rg.`groupid` = 1
    ORDER BY mro.rank DESC

Results:

| ID | RANK | MYORDER | PREVIOUSRANK |
|----|------|---------|--------------|
|  3 |    7 |       2 |            7 |
|  4 |    3 |       3 |            3 |
|  1 |    2 |       1 |            2 |

Desired Result

| ID | RANK | MYORDER | PREVIOUSRANK |
|----|------|---------|--------------|
|  3 |    7 |       1 |            7 |
|  4 |    3 |       2 |            3 |
|  1 |    2 |       3 |            2 |


texts: transaction isolation in MySQL workbench

texts:
MySQL Workbench has a bug where every new created session receives default session parameter values regardless of the server's definition for that parameter.
For example, when the server's "transaction-isolation" parameter is set to "READ-COMMITTED", connecting with workbench and issuing the command "show variables like 'tx_isolation'" will result in "REPEATABLE-READ", which is MySQL's default, but not this server's configuration (connecting with other clients like command-line or python results in the expected behavior).

There are currently some open bugs about this issue (https://bugs.mysql.com/bug.php?id=69800, https://bugs.mysql.com/bug.php?id=53341), and some questions in the subject (https://stackoverflow.com/questions/25268782/mysql-workbench-and-default-session-isolation-level, https://stackoverflow.com/questions/26208007/mysql-workbench-session-does-not-see-updates-to-the-database), but none of these have an actual answer to why this happens or suggests a workaround.

The issues in my organization is that at possibly any moment, a user may connect to one of our databases with MySQL workbench and execute a "SELECT" query that takes a very long time (for the sake of the example, a few hours long). Since the user's session is in "REPEATABLE-READ" mode the query will lock DML statements on the selected tables, regardless of it's auto-commit configuration status.

Which leads to my question:

How can MySQL Workbench "new session parameters" can be configured?

I have already tried the following:

install the latest MySQL workbench version (6.3.5)
Add the following lines to both global and user specific my.cnf files:

[client]

transaction-isolation="READ-COMMITTED"

[mysql]

transaction-isolation="READ-COMMITTED"
Add the following lines to both global and user specific my.cnf files:

[client]

init-command="SET SESSION tx_isolation='READ-COMMITTED'"

[mysql]

init-command="SET SESSION tx_isolation='READ-COMMITTED'"
Under workbench's installation directory i found the file modules/opts.py to contain the following line, so i changed the default to 'READ-COMMITTED':

  {'caption': 'transaction-isolation', 'versions': [(), ((4, 1), (5, 0),
  (5, 1), (5, 4), (5, 5), (5, 6), (5, 7), (6, 0)), ()], 'values':
  [{'default': 'REPEATABLE-READ', 'type': 'enum', 'choice':
  ['READ-UNCOMMITTED', 'READ-COMMITTED', 'REPEATABLE-READ',
  'SERIALIZABLE']}], 'description': 'Default transaction isolation
  level', 'name': 'transaction-isolation'}

Issues encountered on both windows 7 and ubuntu 14.04. Thanks

UPDATE 2017-03-26:

as @RickJames suggested i turned on general log on one instance and found the following commands being executed when i open a new MySQL Workbench connection:

select null #(this command is the value of "init_connect")
set autocommit=1
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
SHOW SESSION VARIABLES LIKE 'lower_case_table_names'
SELECT current_user()
SET CHARACTER SET utf8
SET NAMES utf8
SELECT CONNECTION_ID()
SHOW SESSION STATUS LIKE 'Ssl_cipher'
set autocommit=1
SHOW SESSION VARIABLES LIKE 'sql_mode'
SHOW SESSION VARIABLES LIKE 'version_comment'
SHOW SESSION VARIABLES LIKE 'version'
SELECT current_user()
SHOW SESSION VARIABLES LIKE 'lower_case_table_names'
SHOW SESSION VARIABLES LIKE 'version_compile_os'

So it confirms that MySQL workbench does indeed set the transaction isolation level.


texts: Sharing a single primary key sequence across a database?

texts:
Is it an acceptable practice to use a single sequence as a primary key across all tables (instead of a primary key being unique for a given table, it is unique for all tables)?  If so, is it objectively better than using a single primary key sequence across tables.

I'm a junior software developer, not a DBA, so I am still learning many of the basics of good database design.

Edit:  In case anyone is wondering, I recently read a critique of a database design by one of our company's DBAs who mentioned it was a problem that the design didn't use a single primary key across the entire database, which sounded different than what I've learned so far.

Edit2:  To answer a question in the comments, this is for Oracle 11g, but I was wondering on a non-database specific level.  If this question does depend upon the database, I would be interested to know why, but in such a case I would be looking for an answer specific to Oracle.


texts:
I was trying to execute a fairly large INSERT...SELECT in MySQL with JDBC, and I got the following exception:

Exception in thread "main" java.sql.SQLException: Out of memory (Needed 1073741824 bytes)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)

Since I'm not actually returning a ResultSet object, I thought the Java heap space shouldn't be an issue.  However, I tried to up it anyway and it did no good.  I then tried to execute the statement in MySQL Workbench and I got essentially the same thing:

Error Code 5: Out of memory (Needed 1073741816 bytes)

I should have plenty of RAM to complete these operations (enough to fit the whole table I'm selecting from), but I'm guessing there are various settings I need to tweak to take advantage of all my memory.  I'm running an Amazon EC2 High Memory Double Extra Large Instance with a Windows Server 2008 AMI.  I've tried fiddling with the my.ini file to use better settings, but for all I know I might have made things worse.  Here's a dump of that file:

[client]
port=3306
[mysql]
default-character-set=latin1
[mysqld]
port=3306
basedir="C:/Program Files/MySQL/MySQL Server 5.5/"
datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/"
character-set-server=latin1
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=100
query_cache_size=1024M
table_cache=256
tmp_table_size=25G
thread_cache_size=8
myisam_max_sort_file_size=100G
myisam_repair_threads = 2
myisam_sort_buffer_size=10G
key_buffer_size=5000M
bulk_insert_buffer_size = 4000M
read_buffer_size=8000M
read_rnd_buffer_size=8000M
sort_buffer_size=1G
innodb_additional_mem_pool_size=26M
innodb_flush_log_at_trx_commit=2
innodb_log_buffer_size=13M
innodb_buffer_pool_size=23G
innodb_log_file_size=622M
innodb_thread_concurrency=18
innodb_file_per_table=TRUE
join_buffer_size=4G
max_heap_table_size = 10G

So is this just a matter of changing the above settings to work better for my environment?  If so, what settings should I use?  I'm the only one who ever uses this instance; I use it for my personal hobby project that involves statistical analysis of large datasets.  As such, I'm free to let it consume all available resources for my own queries.

If this is not a matter of changing those settings, what is the problem?  Thanks for any help you can offer for how to better configure everything.


texts: PHP -> Mysql persistent connection pooling WITHOUT mysql_pconnect - Possible?

texts:
I have been trying to figure out a nice way of doing this for a while now. But have had a hard time finding the right pieces to do this. I am guessing this must be possible.

To put it in simple terms here is what I would like to accomplish:

PHP / Other front end -> [SOCKET] ->

Locally hosted 'pooler' -> [Pool of persistent TCP/IP connection(s)]->

Externally hosted MySQLD

Does such a tool / way of doing things exist?

We would basically like to implement persistent mysql connections WITHOUT using mysql_pconnect.

I respectfully ask that we not start to discuss about how persistent connections are not needed etc. They are. We are running out of TIME_WAIT ports, and are having other issues which would be solved if this type of system was implemented.

So yea, to summarize... We would to implement a mysql connection pooler that is socket based on the local end, and persists the connections that are made to a (LAN) externally hosted mysql server.

We do not use transactions or anything else that would be affected from the mysql connections being recycled.

We are running linux on the front end with a master + master percona 5.5 cluster.

Thanks!


texts: How to Select all child elements of a given Node in SqlHierarchyId?

texts:
I have a Node as /1/2/, Here I want to select all child Nodes of '/1/2/'
Ex /1/2/1/ , /1/2/2/1/.../


texts: optimize query with fulltext index in mysql

texts:
I have a column inside my mysql table that has a fulltext index and I need to make a query in which I get all the rows that do not have a specific word or those that start with those letters, so I have the following query:

SELECT *
FROM products
WHERE NOT MATCH(title) AGAINST('Do*' IN BOOLEAN MODE);

Technically this query works, but does it perform a full scan to my table, so is there another approach to be able to perform this query taking advantage of the index and not finishing with a total scan to my table?


texts: Lost persistance in long term in redis without TTL

texts:
Our keys are lost in some time and we cannot find the problem.

We are using AOF and RDB at the sametime with configs below:

RDB:

save 900 1
save 300 10
save 60 10000
stop-writes-on-bgsave-error yes
rdbcompression yes
rdbchecksum yes
dbfilename dump.rdb

AOF:

appendonly yes
appendfilename "appendonly.aof"
appendfsync everysec
no-appendfsync-on-rewrite no
auto-aof-rewrite-percentage 100
auto-aof-rewrite-min-size 64mb
aof-load-truncated yes

As you can see there is no fancy... all are redis defaults.

I have tried to create keys and reboot the server. At this circumstances the keys still. I couldn't determine when keys are lost or why? The log files are not helpful cause I don't know what to query in logs or when.

All I know is my keys are lost at a time interval about one month.

How can I debug this kind of problem in redis?


texts: Sybase 12 to behave like Oracle and SQL Server 2014 - non blocking transaction read

texts:
We utilize an ODBC connection so our code can access various databases - namely Oracle, Sybase 12 and SQL Server 2014.

NOTE : We have a table called which we use for locks. This table only ever has one record in it and it is either empty or populated with whom has the lock.

I have a scenario where whilst the application has an ODBC connection to Oracle or SQL Server 2014 the application runs fine, the test scenario being - two processes accessing the db - one doing a long running task the other doing short tasks. If I connect to Sybase and do the same activities one of the processes blocks (the short task one). I am guessing here that in Sybase the lock table is written to, locked grabbed, and released within a transaction and the other process is awaiting the commit\rollback on the transaction to use the table next.

Why does it work in SQL Server 2014 and Oracle but block in Sybase 12? What option needs to be ON\OFF in Sybase 12 to get the behavior of Oracle and SQL Server 2014?


texts: Oracle Nested Query with Group By and Having Clauses

texts:
So here's the question

Find the IDs of aircraft which have flown more distance for a destination than the average distance of all flights for that same destination

Here is the schema

Destinations(did: integer, dname: string, state: string)
Aircraft(aid: integer, aname: string, color: string)
Flights(aid: integer, did:integer, fdate:date, distance: real)

So far I have been able to find answers to two different parts of this query.
I have found the average distance flown to each destination and I have also found the total distance flown to each location by each aircraft.
The queries for the above are listed below:
SELECT D.DID, AVG(F.DIST) as AvgDist
from Destinations D
join Flights F ON F.DID = D.DID
GROUP BY D.DID;

SELECT F.AID,F.DID, SUM(F.DIST) as fDist
from Flights F
join Destinations D ON F.DID = D.DID
GROUP BY F.AID,F.DID;

My Problem is I do not see how to combine them to find the solution. I know all I need to do is figure out a way to check if the average distance of that destination is less than the distance flown to by that individual aircraft but i'm just not sure how to proceed. All my Having clauses seem to not work so i'm sort of stuck for now.


texts: In Memory Database Remove File and FileGroup

texts:
I have a SQL Server 2016 database and I configured in-memory table.
I want to drop this configuration, then I drop that table.
When I want to delete that file and filegroup, I got following error.

USE [InMem_Test]
GO
ALTER DATABASE [InMem_Test]  REMOVE FILE [InMemFile]
GO
ALTER DATABASE [InMem_Test] REMOVE FILEGROUP [InMemFileGroup]
GO

  Msg 41802, Level 16, State 1, Line 3 Cannot drop the last
  memory-optimized container 'InMemFile'. Msg 5042, Level 16, State 11,
  Line 5 The filegroup 'InMemFileGroup' cannot be removed because it is
  not empty.


texts: RMAN validate incremental level 1 backups?

texts:
I am somewhat new to RMAN, and have taken an Oracle class on it, but I am stumped on validating my backups when using level 0 and 1; in order to save space, I am considering doing a level 0 (full) incremental backup Sunday, level 1 differential backups Monday and Tuesday, and level 1 cumulative on Wednesday.  Then leve1 1 differential backups Thursday and Friday, and level 1 cumulative on Saturday.

The thought was after a successful level 1 cumulative backup on Wednesday, I could delete the differential level 1 backups from Monday and Tuesday.  So, on Wednesday, to verify before I do something drastic to test, I did a

RMAN> restore database validate;

And away it went, validating files; but it only looked at files from the level 0 Sunday backup.

I would have expected it to also validate at least the incremental level 1 backup from Wednesday.

So two questions:

Is my strategy of deleting differential level 1 backups after a successful incremental level 1 backup valid?
How can I test to ensure this is correct?


texts: How to prevent SQL Server service account from registering / deregistering SPN?

texts:
The service account is a domain account. It is not a domain admin (nor is it a member of any group that is a domain admin). It has neither "write servicePrincipalName" nor "Write public information" permission (nor a member of a group with these permissions). Yet it is still able to register / deregister it's SPN upon startup and shutdown.

What permission am I missing?


texts:
I have a virtual SQL Server 2016 SP2 CU2 with SAN storage. TempDB has 8 files. I know it is recommended to put TempDB on SSD but TempDB is hardly used so spinning disks are cheaper.
I've noticed that sometimes their is write latency on all my TempDB files of approx 200ms. I checked this using a snapshot on sys.dm_io_virtual_file_stats and calculated io_stall_write_ms/num_of_writes.

The first thing I did was look at sys.dm_exec_query_stats to check if there was a query with high number of spills but no such query. Next I wrote an extended event to look if there where queries executed on tempdb but also this returned nothing.

So where does the write latency come from? I decided to track the file_write_completed event on tempdb. Here I saw that on moment of the latency that session 22 wrote something with a size of 8192 (don't know the unit) to all files of tempdb. In sys.dm_exec_sessions the last command of this session was 'RESOURCE MONITOR'.
The first thing I did was checking sys.dm_os_ring_buffers with following query:

SELECT
    EventTime,
    record.value('(/Record/ResourceMonitor/Notification)[1]', 'varchar(max)') as [Type],
    record.value('(/Record/ResourceMonitor/IndicatorsProcess)[1]', 'int') as [IndicatorsProcess],
    record.value('(/Record/ResourceMonitor/IndicatorsSystem)[1]', 'int') as [IndicatorsSystem],
    record.value('(/Record/MemoryRecord/AvailablePhysicalMemory)[1]', 'bigint')/1024. AS [Avail Phys Mem, MB],
    record.value('(/Record/MemoryRecord/AvailableVirtualAddressSpace)[1]', 'bigint') AS [Avail VAS, Kb]
FROM (
    SELECT
        DATEADD (ss, (-1 * ((cpu_ticks / CONVERT (float, ( cpu_ticks / ms_ticks ))) - [timestamp])/1000), GETDATE()) AS EventTime,
        CONVERT (xml, record) AS record
    FROM sys.dm_os_ring_buffers
    CROSS JOIN sys.dm_os_sys_info
    WHERE ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR') AS tab
ORDER BY EventTime DESC;

But again the result of the query didn't match with the moment of latency.

I also monitored the tempdb-drive with perfmon, using the counters 'Avg. Disk sec/Write' and 'Current Disk Queue Length'. On moment of latency the latency was 6ms and the queue length was 18.

I ran CrystalDiskMark and this is the avg result of multiple runs:

╔═════════════╦═══════════╦═════════╦══════════╦═══════╗
║             ║ seq Q32T1 ║ 4k q8T8 ║ 4K Q32T1 ║ 4Q1T1 ║
╠═════════════╬═══════════╬═════════╬══════════╬═══════╣
║ Read(MB/s)  ║ 1596,57   ║ 505,77  ║ 336,27   ║ 20,94 ║
║ Write(MB/s) ║ 591,01    ║ 127,6   ║ 87,87    ║ 2,74  ║
╚═════════════╩═══════════╩═════════╩══════════╩═══════╝

After some extended research I discovered that on moment of latency the lazy writer process starts flushing pages to disk. If I understand correctly, SQL Server removes the least used pages (dirty and clean) from the buffer pool when memory is needed.
So If above is correct, I should see high number of physical reads at moment of latency, this I will investigate further.
Also at moment of latency there are no checkpoints running.

This is my conclusion so far:

Resource Monitor is writing to Tempdb (detected with extended events, what/why is written I don't know)
Lazy Writer process is flushing pages from  bufferpool because memory is needed.
(I don't think this can cause write latency on Tempdb)

All this looks like signs of memory pressure to me.
But what I don't understand is why is this causing write latency on Tempdb?
What can I do to investigate this problem further?


texts:
I can run

SELECT
  r.trx_id waiting_trx_id,
  r.trx_mysql_thread_id waiting_thread,
  r.trx_query waiting_query,
  b.trx_id blocking_trx_id,
  b.trx_mysql_thread_id blocking_thread,
  b.trx_query blocking_query
FROM       information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b
  ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r
  ON r.trx_id = w.requesting_trx_id

and see anything that is currently blocked and what is blocking it. However, my blocks are too sparse to reliably catch them.

It seems that there should be some way to persistently capture the blocks, so if it happens on Saturday night I can go back and see it on Monday morning. However, I have yet to find a solution (perhaps my Google Foo is FooBarred).

Is there a simple way to log this?
Is there a trigger to write the information to another (persistent) table?
Is there Do I need a script that constantly runs and logs the results?
Is there a tool that just handles this?


texts: Add a column with name as a result from a SELECT

texts:
I'm using PostgreSQL on my project and I have a Spring Boot Application that uses Flyway to create everything on my Database. I need to create a column that its type is based in a row on other table.
E.g.:
ALTER TABLE NAMES ADD COLUMN (SELECT n.name FROM NAMES AS n WHERE n.name = 'Tom') VARCHAR(255) NULL;

Is this possible?
Can I create a function to do the select work then just call it during the Alter Table code?
Context:
If you want to know why I need this, I have lots of clients that uses geometry types and each one has a different EPSG, I need to create the "geom" column based on the client's geometry so, I want to make a select based on my client's name to get its geometry type to then create the column with that type.
EDIT:
I think I got something near to what I need:
CREATE OR REPLACE FUNCTION client_schema.update_geom(my_table text, my_schema text, OUT alter_result boolean)
    LANGUAGE plpgsql
    AS $function$
    declare client_projection varchar(255);
    declare db_name varchar(255);
    begin
        CREATE extension if not EXISTS dblink;
        select split_part(current_database(), '-', 1) into db_name;
        select c.projection_key from client_schema.dblink('dbname=adm-3','SELECT projection_key, context_name FROM "adm-3".public.clients as c ')
        AS c(projection_key text, context_name text) where c.context_name = db_name into client_projection;
        EXECUTE 'ALTER TABLE '
            || esquema::text || '.' || tabela::text
            || ' ALTER COLUMN geom type geometry(Polygon, split_part($1, '':'', 2))'
            INTO alter_result
            USING client_projection;
        END;
    $function$
;

The problem now is that he don't let me update the column's type!
The error:
SQL Error [42601]: ERROR: type modifiers must be simple constants or identifiers


texts: SQL Server 2005 Express in VMware causing very high CPU load

texts:
I'm having the problems described in KB937745 - very high CPU usage and the Application Log is reporting something like this:

  The client was unable to reuse a session with SPID SPID, which had been reset for connection pooling.

I've downloaded the hotfixes and I can't run them - I suspect it is because SQL Server 2005 Express Edition is not in the "Applies to" section of the KB.

The machine is running on an ESX 3.5 host running Windows XP (patched).

Result of:

SELECT
    SERVERPROPERTY('productversion'),
    SERVERPROPERTY ('productlevel'),
    SERVERPROPERTY ('edition')

is

9.00.3068.00    SP2 Express Edition

Any ideas? I'm stumped why the CPU is getting pegged. This is a product from a vendor that has never seen this kind of problem with several installations.


texts: Better query to select multiple columns into a single output column?

texts:
I have the following query that sort of works, but I find it to be kludgy and inefficient.  Is there a better way to write this query?

I know this is a bit of an odd query, else I would probably have found some advice when searching, but it is necessary for our business logic.  We've been doing this in Hibernate queries and using software to output the file but we're needing to improve performance drastically and that means using raw sql.  The output of this query is to be used to partially populate a helper table that we query to figure out which of our 40-odd product tables a particular item is contained in.

Could the following query be replicated using something like GROUP_CONCAT in reverse? Note that the second and third column must be repeated on each line.  The fields all contain the same type of data.

SELECT 'Pn', 'Mfg', 'TableId', 'SourceColumn', 'OtherData'
SELECT PN1, Mfg, '26', 'PN1', OtherData FROM Table
UNION ALL
SELECT PN2, Mfg, '26', 'PN2', OtherData FROM Table
UNION ALL
SELECT PN3, Mfg, '26', 'PN3', OtherData FROM Table
UNION ALL
SELECT PN4, Mfg, '26', 'PN4', OtherData
INTO OUTFILE 'XXX.dat'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
FROM Table d;

Input looks like the following table:

    |  PN1  |  PN2  |  PN3    |  PN4    |  MFG  |  TableId  |  OtherData  |
    -----------------------------------------------------------------------
    |  asdf |  abde |  12354  |  asdfc  |  2    |  26       |  0.2456     |
    |  nupo |  1354 |  null   |  null   |  2    |  26       |  1.53       |
    |  ...  |  ...  |  ...    |  ...    |  ...  |  ...      |  ...        |
    |  ...  |  ...  |  ...    |  ...    |  ...  |  ...      |  ...        |
    |  ...  |  ...  |  ...    |  ...    |  ...  |  ...      |  ...        |

I want the output .dat file to look like this:

    "Pn","Mfg","TableId","SourceColumn","OtherData"
    "asdf",2,26,"PN1",0.2456
    "abde",2,26,"PN2",0.2456
    "12354",2,26,"PN3",0.2456
    "asdfc",2,26,"PN4",0.2456
    "nupo",2,26,"PN1",1.53
    "1354",2,26,"PN2",1.53
    ...
    ...
    ...

If I take the INTO OUTFILE clause out, I get an output that looks like this:

    |  Pn      |  Mfg     |  TableId    |  SourceColumn  | OtherData  |
    -----------------------------------------------------------------
    |  "Pn"    |  {blob}  |  "TableId"  |  "PN"          |  {blob}    |
    |  "asdf"  |  {blob}  |  "PN1"      |  "PN1"         |  {blob}    |
    |  "abde"  |  {blob}  |  "PN2"      |  "PN2"         |  {blob}    |
    |  "12354" |  {blob}  |  "PN3"      |  "PN3"         |  {blob}    |
    |  "asdfc" |  {blob}  |  "PN4"      |  "PN4"         |  {blob}    |
    |  "nupo"  |  {blob}  |  "PN1"      |  "PN1"         |  {blob}    |
    |  "1354"  |  {blob}  |  "PN2"      |  "PN2"         |  {blob}    |
    |  ...     |  ...     |  ...        |   ...          |   ...      |
    |  ...     |  ...     |  ...        |   ...          |   ...      |
    |  ...     |  ...     |  ...        |   ...          |   ...      |


texts: Finding number such as 111111,0000,4444, using REPLICATE

texts:
I have been trying to find repeated number such as 1111, 22222, 55555, in my table. After some research, I found that this is said to work:

SELECT *
FROM tablename
WHERE columnname = REPLICATE(LEFT(columnname,1),LEN(columnname))

It did work, but I want to understand why it works. If anyone understands what this is doing please enlighten me.


texts:
Am creating a schema dynamically like:

SELECT @xxx:=LAST_INSERT_ID();

SET @schemaname = concat('CREATE SCHEMA ','TrialDB_','',@xxx);

So once after creating the Schema I would like to dump some tables into that schema. So for switching the schema I am trying:

use @schemaname ; // Which is not Working

//Also tried the below scenario which is not working

SET @useSchema =concat('USE','TrialDB_','',@cloudclientid);

SELECT @useSchema ;

PREPARE statement1 from @useSchema;

EXECUTE statement1;

After dumping the tables my regular flow needs to continue.


texts: Does making a field unique make it indexed?

texts:
If I make a unique constraint on a field, do I also need to make an index on that field in order to get a scalable insert time? Or is this done for me (even if the index it uses isn't publicly accessible?)

Specifically, I'm working with Apache Derby for prototyping, although I will probably be moving it to MySQL in the semi-near future. I'm also hoping there might be something in the SQL standard that says something about this.

I will never have a need to search by this field, so I would rather not make a useless index. But I'd rather have a useless index than have an O(n) insert time.


texts:
What SQL Server login does the SQL server agent use to log in to the database instance?

I am running the SQL server agent as the built in local system account.

I have created a SQL agent job which has one step. That step inserts the value of SYSTEM_USER into a table.

When i Run the job and then select from the table when the job is complete, I can see the value of SYSTEM_USER was DOMAIN\SERVERNAME$ so it seems that is what the agent is logging into the instance as.

So why can the agent log in as DOMAIN\SERVERNAME$ when it is not an SQL Login?

What privileges does this account have?


texts: Insert into table where boolean value in another table is true

texts:
I want to insert a value into a table, but only if a boolean value in another table is true.

I currently do this using two separate SQL commands, testing the boolean value in PHP and then deciding to insert the value or not in the other table.
Is it possible to do this in a single SQL statement which should be much quicker than my two statement process. (In the example code below, I have hard coded my values, but they come from php variables in the actual code).

SELECT enabled FROM mydb.sensors WHERE pk=10;

In PHP I run the above query, then I test if enabled is true. If true, then I store the sensor data value into another table.

INSERT INTO mydb.sensordata(sensorkey,sensorvalue) values (10, 25.4);

What I am after is something like the following, but not sure how to implement it.

INSERT INTO mydb.sensordata(sensorkey,sensorvalue) values (10, 25.4) WHERE mydb.sensors.pk = 10 AND mydb.sensors.enabled = TRUE;

Any guidance is appreciated.


texts: limiting the rows to where the sum a column equals a certain value

texts:
i m trying to limit the rows to where the sum a column equals a certain value but i m getting unexpected results.

CREATE TABLE buy (
  id int not null,
  volume decimal(9,4) not null,
  price decimal(9,2) not null
 );

insert buy (id, volume, price) values
(1, 1.0000, 14250.00),
(2, 0.2500, 14251.10),
(3, 0.2500, 14250.00),
(4, 0.0050, 14200.00),
(5, 0.5000, 14255.01);

expected result selected ids 5,2,1(because ordering by higest price) but it selects ids 2,1,3

SELECT t.id, t.volume, t.price,
(SELECT SUM(volume) FROM buy WHERE id <= t.id) 'RequiredVolume'
FROM buy t
HAVING RequiredVolume <= '1.5000'
ORDER BY price DESC, id ASC;

| id | volume |   price | RequiredVolume |
|----|--------|---------|----------------|
|  2 |   0.25 | 14251.1 |           1.25 |
|  1 |      1 |   14250 |              1 |
|  3 |   0.25 |   14250 |            1.5 |

http://sqlfiddle.com/#!9/ef024/3/0


texts: Remote queries at SNAPSHOT isolation level

texts:
I'm experimenting with snapshot isolation level. Whenever I try to access a linked server, I get an error:

  Remote access is not supported for transaction isolation level "SNAPSHOT"

I wasn't able to find anything meaningful in BOL or Google on this issue.

The limitation seems rather frustrating. Is there a workaround, other than caching all the remote data locally?


texts: Mongo DB sharded cluster fails when one shard is down

texts:
I am using Mongo Sharded cluster with two shards.
There was an issue on my one shard and and it was down for around 30 minutes.
It stopped my writes to other shards also.
Logically if one shard is down other shard must be able to take part of writes but allow rites got failed in that duration.

Command failed with error 133: 'could not find host matching read     preference { mode: "primary", tags: [ {} ] } for set firstset' on server xxxx . The full response is { "code" : 133, "ok" : 0.0, "errmsg" : "could not find host matching read preference { mode: \"primary\", tags: [ {} ] } for set firstset" }

Could you please help me why this happened.

Mongo version : 3.2.9
Shard Key contentID : alphanumeric value

https://docs.mongodb.com/manual/sharding/#high-availability

Thanks
Virendra Agarwal


texts: InsertId in one table and insert the same id with concat of array at the end in other table

texts:
I have two tables namely orders and suborders.
table 1 - Order table
Columns - Order Id and qty
Table 2 - suborders
Column - SubId
Whenever a new order is placed the same id has to be inserted in the both table but in the table 2 would I like to generate a the subid as  same id+ with iteration of total number of qty .
Eg
Table1
Exak123 , 59

I would like to get
Table 2
Exak123_1
Exak123_2
..
..
Exak123_59


texts:
tbl_orders

order_id, qty
123, 2
124, 1
125, 3
126, 1
127, 1
128, 1
129, 4

tbl_score

order_id, name, created
123, product 1, 2018-06-01
124, product 1, 2018-06-01
125, product 1, 2018-06-02
126, product 3, 2018-06-02
127, product 2, 2018-06-03
128, product 3, 2018-06-03
129, product 3, 2018-06-03

Required output

             2018-06-01       2018-06-02        2018-06-03
product 1        3                  3               0
product 2        0                  0               1
product 3        0                  1               5

The following query produces the required output for 2018-06-01 but takes around 1 min and 45 seconds to complete on the live database. If I add more dates using case it takes more time. Can someone help me fine tune it or perhaps suggest another solution?

select s.name,
  sum( case when date(s.created) = '2018-06-01' then o.qty else 0 end ) as 'June 1'
from tbl_score s  inner join tbl_order o
 on s.order_id=o.order_id group by s.name;


texts:
I'm developing an application for my last year/degree at school.

This application is for a mechanical workshop;
It will have two types of users:

Administrator,
User;

Administrator will be just to add new users (with this I mean, just the owner of the workshop can add users to this application, via graphic interface);
User will be able to add Questions and Answers (that will be the same thing, because he will have a question, and then will put its resolution, it's like my own tutorial) about what he fix at the mechanical workshop;
User will sometimes need to search for one that he has already input as question/answer/solvedproblem/whatever.
-Questions or Answers procediments are step-by-step, so I guess that we'll need to have a table for components, I'm not sure.
-The application will ask you for noises, smells, whatever that the man that works at the place detect on a car or motorcycle being repaired. We cannot forget this is a step-by-step Q&A and that I will need to put what I'm trying to solve, how I solved and then one month or two later I'll find/searching what I've already did.

I'm very newbie with databases, how can I design this? I'm very good at using database info, but to make one I would really need someone's help. Let me know if you need more details.


texts: could sql create view with fix group even without data

texts:
Let say that I have a table as below :

day       | Qty
---------------
Sunday    | 10
Monday    | 5
Tuesday   | 20
Sunday    | 17

as I have another table that day value could only be 'Sunday' till 'Saturday', I would like to have a resume table as below:

day       | Sum(Qty)
---------------
Sunday    | 27
Monday    | 5
Tuesday   | 20
Wednesday | 0
Thursday  | 0
Friday    | 0
Saturday  | 0

The value for Wednesday till Saturday have zero value because of no data. Is there any solution?


texts: postgres: Insert multiple rows into a table with id from other table if not exists insert to other table

texts:
I have done similar task where I can insert a row into a table if data doesn't exists:
WITH
        user_exists AS (
            Select id from users where username='%s'
        ),
        user_new AS (
            INSERT INTO users (username)
            SELECT w.username FROM (values ('%s')) w(username)
                WHERE not exists
                (SELECT 1 FROM users u WHERE u.username = w.username)
                returning id
        )
INSERT INTO feedbacks ('static_row', userid)
SELECT
'static_data',
(SELECT id FROM users_exists UNION ALL SELECT id FROM users_new) AS userid

Above works well when we insert a new row to feedbacks table. If user doesn't exists it inserts data in users table and returns id which is used for inserting data to feedbacks table.
But now my use case is, I have to insert multiple rows into the feedback table. Something like this:
user_variable = ['a','b', ...]
Insert into feedbacks ('static_row', userid)
VALUES
('sample_data', (Select if from users where username='a')),
('sample_data', (Select if from users where username='b')),
('sample_data', (Select if from users where username='c'))

For above case, how we can insert a new row to users table if username='b' doesn't exists.


texts: In mysql, how to add a primary key and a composite key to a table so that it forbids duplicate rows, when there are null values within the columns?

texts:
In the following table (8 mi rows), every column has Null values. However, there can never be two identical rows (there will be always at least one column with a different value for each row).
How can I alter this table so that it has a composite key which enforces that no duplicate rows exist? Also, the table below does not have a primary key. How can I add a primary key to it?


texts: Improve performance on concurrent UPDATEs for a timestamp column in Postgres

texts:
I am using a timestamp column called updated_at for cache invalidation. More information on this particular technique here.

The queries all have the same format

UPDATE "managers" SET "updated_at" = '2014-07-25 15:00:24.212512' WHERE "managers"."id" = 1

The problem is that there is too much activity on this column, which leads to slow writes. I'm assuming the slowdowns are being caused by the locking mechanism.
There is no index on the column.

We have been trying to mitigate this by batching together operations that would cause multiple updates and this did help but the whole system is still suffering.

Would a different kind of column be faster? For example an integer.
Is there some way to not lock the column?
Any other tips to improve this?

We are actually thinking of moving this to redis and using INCR.

                                            Table "public.managers"
         Column          |            Type             |                          Modifiers
-------------------------+-----------------------------+-------------------------------------------------------------
 id                      | integer                     | not null default nextval('leads_managers_id_seq'::regclass)
 account_id              | integer                     |
 created_at              | timestamp without time zone |
 updated_at              | timestamp without time zone |
 notification_recipients | text                        | default ''::character varying
 uuid                    | character varying(255)      |
Indexes:
    "leads_managers_pkey" PRIMARY KEY, btree (id)
    "index_leads_managers_on_uuid" UNIQUE, btree (uuid)
    "index_leads_managers_on_account_id" btree (account_id)


texts: Optimize InnoDB settings for current VPS setup

texts:
I have VPS with following setup:

(4 x 2.40 GHz),
8192 MB RAM MEMORY,
20GB HDD space.

My web service is making almost: 30 mln inserts/day  or  ±350 inserts  per second. In near future amounts will be probably bigger (that will require more CPU).

Can you guys give me advices how to tune current MariaDB InnoDB engine to make the machine work better, in more optimized way?

Here are some current parameters:

aria_pagecache_buffer_size  134217728
aria_sort_buffer_size   268434432
bulk_insert_buffer_size 8388608
innodb_buffer_pool_dump_at_shutdown OFF
innodb_buffer_pool_dump_now OFF
innodb_buffer_pool_dump_pct 100
innodb_buffer_pool_filename ib_buffer_pool
innodb_buffer_pool_instances    8
innodb_buffer_pool_load_abort   OFF
innodb_buffer_pool_load_at_startup  OFF
innodb_buffer_pool_load_now OFF
innodb_buffer_pool_populate OFF
innodb_buffer_pool_size 134217728
innodb_change_buffer_max_size   25
innodb_change_buffering all
innodb_log_buffer_size  8388608
innodb_sort_buffer_size 1048576

If you need more parameters, just leave an comment below.


texts:
We have been having CPU issues with one of our servers lately and while we have been looking into this we have also noticed queries running slowly with waits of PAGEIOLATCH_XX. In particular, a reindex job is seemingly always having this wait type.

In response, I have run a collect against sys.dm_io_virtual_file_stats and then broken this down into time chunk and worked out the average stall per operation. While there are spikes mostly, the disk seems to have a value of regularly under 20 ms. From what I remember, 20 ms is the recommended value(?).

Further to this I have run Glenn Barry's script:

select db_name(database_id) as DatabaseName, file_id
,io_stall_read_ms
,num_of_reads
,cast(io_stall_read_ms/(1.0+num_of_reads) as numeric(10,1)) as 'avg_read_stall_ms'
,io_stall_write_ms
,num_of_writes
,cast(io_stall_write_ms/(1.0+num_of_writes) as numeric(10,1)) as 'avg_write_stall_ms'
,io_stall_read_ms + io_stall_write_ms as io_stalls
,num_of_reads + num_of_writes as total_io
,cast((io_stall_read_ms+io_stall_write_ms)/(1.0+num_of_reads +
num_of_writes) as numeric(10,1)) as 'avg_io_stall_ms'
from sys.dm_io_virtual_file_stats(null,null) --where db_name(database_id) = 'tempdb'
order by [DatabaseName] desc'

Which calculates the average I/O stall also and this also confirms stalls less than 20 ms.

I have also looked in the following to see if any pending tasks are taking longer than recommended, but this isn't throwing up any pending I/O operations taking regularly longer than 20 ms.

SELECT db_name(database_id) as 'Database',
file_name(file_id) as 'File',
io_stall,
io_pending_ms_ticks
FROM sys.dm_io_virtual_file_stats(NULL, NULL) iovfs,
 sys.dm_io_pending_io_requests as iopior
WHERE iovfs.file_handle = iopior.io_handle

My question now is: If the issue is not disk related, why am I seeing lots of PAGEIOLATCH_XX waits? In particular, why is the reindex running extremely slowly with this wait type?

Could this be related to CPU pressure?

================================================================================

I just wanted to update the thread.  After doing more analysis I have tracked down a particular proc that is causing significant reads.  The proc is as follows:

ALTER PROCEDURE [dbo].[GetActiveSessionCount]
    @SessionCount   INTEGER OUTPUT
AS
SET NOCOUNT ON
BEGIN
    DECLARE @Error              INTEGER,
            @RowCount           INTEGER,
            @nExpireAfter       INTEGER
    SELECT  @nExpireAfter = ExpireSessionsAfter FROM KSYSTEM
    SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT
IF(1 <> @RowCount)
BEGIN
RAISERROR (50003, 15, 1, 'GetActiveSessionCount')
RETURN 50003
END
    IF (0 <> @Error)
    BEGIN
        RETURN @Error
    END
    SELECT  @SessionCount = COUNT(SessionID)
    FROM    KSESSION  WITH (NOLOCK)
    WHERE
    (
        (
            Expirable = 0
        )
        OR
        (
            Expirable = 1
            AND
            (   --SessionID IS NOT NULL)
                EXISTS (SELECT SessionID FROM KFILESAWAITINGCOMMIT fac WITH (NOLOCK) WHERE SessionID = fac.SessionID)
                OR
                (
                    LastAccessDateTime IS NOT NULL
                    AND GETDATE() <= (DATEADD(minute, @nExpireAfter, LastAccessDateTime))
                )
            )
        )
    )

SELECT @Error = @@ERROR
IF(@Error <> 0)
BEGIN
RETURN @Error
END
    RETURN 0
END

Using STATISTICS IO I can see the problem line is

SELECT SessionID FROM KFILESAWAITINGCOMMIT fac WITH (NOLOCK) WHERE SessionID = fac.SessionID

Looking at the execution plan it is doing a Clustered Index Scan.  Now there is an non clustered index on that table already specifically for SessionID however it is not being used.

What I am finding in testing is if I run that SELECT by itself then it uses the non clustered index and performs well.  But if I use a hint in the proc to force it to use the non clustered index, then it actually performs worse.

Can anyone explain?


texts:
I'm developing a friendship relation in MySQL where friend relation is mutual. If A is friend of B, then B is friend of A. If one of the users end friendship then relation drops. I want to learn which way is better.

I have a running system;

user
-----------
userid p.k
name

friends
-------
userid
friendid
primary key (`userid`,`friendid`),
key `friendid` (`friendid`)

1 2
2 5
1 3

To get all of my friends;
SELECT u.name, f.friendid , IF(f.userid = $userid, f.friendid, f.userid) friendid
FROM friends f
    inner join user u  ON ( u.userid = IF(f.userid = $userid, f.friendid, f.userid))
WHERE ( f.userid = '$userid' or f.friendid = '$userid' )

This query works well.  Maybe I can add a UNION.  The Query is more complicated than the one below and the table contains half as many records as the one below.

Another way is to keep relations in separate rows;

1 2
2 1
2 5
5 2
1 3
3 1

SELECT u.name, f.friendid
FROM friends f inner join user u ON ( u.userid = f.friendid )
WHERE f.userid = '$userid'

This query is simple, although the table takes twice as much space.

My concern is; assuming that there are millions of users; which way will work faster?

What are the advantages and disadvantages of both ways?

What should I keep in mind or change for these ways? And what problems can I face for both ways?


texts:
We have several MySQL 5.0 databases.

We've been having security problems, like records being modified without authorization.

We need to implement some kind of audit on DML operations identifying users that perform UPDATE or DELETE operations on some critical tables.

I have been googling about any my.conf options that would activate this kind of audits, to no avail.


texts:
I have a table, as follows:

ID   |  Name     |  MyID
1    |  Alan     |  2
2    |  Alan     |  2
3    |  John     |  3

I want to select the records as follows:

If two records have the duplicate values in ‘Name’ column, then only the first record is selected and the other record is discarded, even if the two records have different values in other columns.
If two records have different ‘Name’ value, then they are ordered via ‘MyID’ column in ascendant order.
All columns of the records should be returned.

So for the above records, the query should select the following ones:

ID   |  Name     |  MyID
1    |  Alan     |  2
3    |  John     |  3

How to write the query? It seems I should use DISTINCT or GROUP BY, but my queries are all fail. Please help.

Thanks


texts: How to get the column name having a particular value from a SQL Server table?

texts: I have a value (say 5) but I do not know in which column of the table it is stored in. I want to get the column name which has this value. Please help me with this.


texts:
I wanted to use the parallel hint that would give the best performance in my query which is a equi-join query where the two tables are in a cluster and the join column is a cluster index. I wasn't sure if I should use the parallel option on one of the tables PARALLEL(table1, 4), both of the tables PARALLEL(4) or on the clusters index PARALLEL_INDEX(index_name, 4)? Here was my query:

SELECT /*+ USE_HASH(a, b)  */ a.NAME
    ,b.price
    ,
FROM Customer a
    ,Sales b
WHERE b.Cust_id < 2000
    AND b.Cust_id = a.Cust_id;

Here is the execution plan for this query:


texts:
I have three different schemas, ended up creating all the tables in one, but now I want to put them in their correct schemas.

How can I do this without using scripts?
the version of Workbench that use is 6.3.4


texts: In oracle XE, what is the difference between the autobackup and backupset direcories?

texts:
I am learning oracle db, and in Oracle XE, there are autobackup and backupset directories under flash_recovery_area/XE. In both directories, there are bkp files under the datestamped directoy.

What is the difference between the bkp files in  autobackup and backupset  directories?  In other words, what is the difference between the autobackup and backupset direcories? Thank you very much.


texts: Detailed behaviour of mongos in context of sharded Mongo cluster

texts:
I have a mongo cluster as follows:

3 shards, located in the 3 main global regions (APAC/EMEA/AMER)
The shardKey is "Region".
Each region has a mongos running locally, "close" to its users, "close" to its mongods.

Question: when the mongos receives an insert request for a document who's region is not the mongos "close" region, does the mongos:

a) route the insert into its "close" regional shard then let the balancer sort out moving the document to its correct shard.
OR
b) route the insert to the correct regional shard directly.

It seems to me that a) would give faster inserts than b) but takes longer for the database to settle down to its eventual state.

Which one is what mongos actually does?

Thanks!


texts: Centralized Database for user information

texts: Our current setup is that we have what I would call a centralized database that holds user information that is shared by several different databases. We have the several databases to separate out data from different apps, as only the user information is common between them. What is the best way of handling this? Currently we can't have foreign keys to user id as that is in a different database. Also, we have to hard code that separate database name in any stored procedures or queries that may need the user data, which makes it a pain to develop with as I can only have 1 development database rather than keeping one that works with different branches. Does anyone have a suggestion for a better way of handling this (other than bringing the databases together)?


texts: I would like to understand these ibdata files as these play vital role in the crash recovery procedure. I could not find proper resources over the web for this.


texts: Pgpool install - libpq is not installed or libpq is old

texts:
Based on the documentation here, I want to create a pgpool II with PostgreSQL databases. When I try to install, I encounter this error:

  configure: error: libpq is not installed or libpq is old

Searching the pgpool website, they mention to run ./configure with this command:

  --with-pgsql

OR

   --with-pgsql-includedir

OR

   --with-pgsql-libdir

But still I'm facing the same problem. Can anyone guide me what can I do from here?

I am running PostgreSQL 9.2 intalled from a yum package on CentOS 5.


texts:
I have a table of about 3.25M rows with the follow format in Postgres 9.4.1

CREATE TABLE stats
(
    id serial NOT NULL,
    type character varying(255) NOT NULL,
    "references" jsonb NOT NULL,
    path jsonb,
    data jsonb,
    "createdAt" timestamp with time zone NOT NULL,
    CONSTRAINT stats_pkey PRIMARY KEY (id)
)
WITH (
    OIDS=FALSE
);

The type is a simple string no longer than 50 characters.

The references column is an object with an list of key values. Basically any list of simple key values, and only ever 1 level deep, the values are always strings. It could be

{
    "fruit": "plum"
    "car": "toyota"
}

or it could be

{
    "project": "2532"
}

The createdAt timestamp is not always generated from the database (but it will by default if a value isn't supplied)

I'm currently using the table with only testing data. In this data every row has a project key as a reference. So there are 3.25M rows with a project key. There are exactly 400,000 distinct values for the project reference. There are only 5 distinct values for the type field, this would probably be no more than a few hundred in production.

So I'm trying to index the table to perform the following query quickly:

SELECT
  EXTRACT(EPOCH FROM (MAX("createdAt") - MIN("createdAt")))
FROM
  stats
WHERE
  stats."references"::jsonb ? 'project' AND
  (
    stats."type" = 'event1' OR
    (
      stats."type" = 'event2' AND
      stats."createdAt" > '2015-11-02T00:00:00+08:00' AND
      stats."createdAt" < '2015-12-03T23:59:59+08:00'
    )
  )
GROUP BY stats."references"::jsonb->> 'project'

The query returns the time distance between two events based on two stats rows that have the same reference. In this case project. There is only ever 1 row for each type and selected reference value, but there may also be no rows in which case the result returned is 0 (this is averaged out later in a different part of a larger query).

I've created an index on the createdAt type and references columns but the query execution plan appears to be doing a full scan instead.

The index

CREATE INDEX "stats_createdAt_references_type_idx"
    ON stats
    USING btree
    ("createdAt", "references", type COLLATE pg_catalog."default");

Execution plan:

 HashAggregate  (cost=111188.31..111188.33 rows=1 width=38)
                (actual time=714.499..714.499 rows=0 loops=1)
   Group Key: ("references" ->> 'project'::text)
      ->  Seq Scan on stats  (cost=0.00..111188.30 rows=1 width=38)
                             (actual time=714.498..714.498 rows=0 loops=1)
          Filter: (
              (("references" ? 'project'::text)
               AND ((type)::text = 'event1'::text)) OR
              (((type)::text = 'event2'::text)
               AND ("createdAt" > '2015-11-02 05:00:00+13'::timestamp with time zone)
               AND ("createdAt" < '2015-12-04 04:59:59+13'::timestamp with time zone)))

Rows Removed by Filter: 3258680
Planning time: 0.163 ms
Execution time: 714.534 ms

I'm really not that clued up on indexing and query execution plans so if someone could point me in the right direction that'd be great.

Edit

As noted by Erwin, it appears as though even if i did have correct indexes, a table scan would still occur as the portion of the table returned from the query is very large. Does this mean that for this set of data this is the fastest query time i can get? Im assuming if i added 60M more unrelated rows without a project reference, it might use an index (if i had the correct indexes), but i dont see how that could speed up the query by adding more data. Maybe im missing something.


texts: LEFT and RIGHT JOIN multiple table, show all null data on mariadb 10.3

texts:
i want to join 3 table with null value,
here i have 3 table folowing
tbl_reporting                               tbl_criteria                   tbl_student
|===============================|           |========================|     |===============|
| id | id_criteria | id_student |           | id |   name    |weight |     | id |   name   |
|===============================|           |========================|     |===============|
| 1  |     2       |      3     |           |  1 | worrying  |  3    |     | 1  | Nina     |
| 2  |     1       |      2     |           |  2 | naughty   |  2    |     | 2  | Adam     |
| 3  |     1       |      1     |           |  3 | usually   |  2    |     | 3  | Dodi     |
| 4  |     2       |      2     |           |  4 | good      |  1    |     | 4  | Zarah    |
| 5  |     1       |      1     |           |  5 | obey      |  1    |     | 5  | Udep     |

and the result i want, like this

result
| id | student_name   | criteria_name | weight |
|==============================================|
| 1  | Dodi           |    naughty    |   2    |
| 2  | Adam           |    worrying   |   3    |
| 3  | Nina           |    worrying   |   3    |
| 4  | Adam           |    naughty    |   2    |
| 5  | Nina           |    worrying   |   3    |
|NULL| Zarah          |      NULL     |   0    |
|NULL| Udep           |      NULL     |   0    |
|NULL| NULL           |     usualy    |   0    |
|NULL| NULL           |      good     |   0    |
|NULL| NULL           |      obey     |   0    |

i try myself.. but not what i expected.
here i code self : link

thanks


texts: Why configuring automatic failover for SQL availibility group feature on sql failover cluster instance not allowed

texts:
I have set up an availability group where a primary is standalone server and replica is SQL failover cluster. This setup allows me only manual failover and when ever I try to set it automatic in FCI instance it says, this is not allowed on FCI instance.

Can anybody tell me the exact reason why it is not allowed or if its allowed please tell me the steps.


texts: Postgres SUM over PARTITION BY "tolerate" null values

texts:
I have a view which calculates rolling sum taking in value form previous row based on PARTITION BY clause, and I need to indicate one field in PARTITION BY clause can have nulls in them.
sum(s."QuantityChange") OVER (PARTITION BY s."LocationId", s."PartId", s."SupplierFacilityId" ORDER BY s."DueDate" ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS "RunningSum"

Sometimes SupplierFacilityId is null and that's fine, it should still be included in the sum() calculation.
Is this possible with Postgres?


texts: Can a TRUNCATE or DROP table ever be very time consuming?

texts:
A couple of months ago, I discovered in a (not so well maintained) database a totally useless table containing 430 million unindexed rows, growing by about 80 000 rows per day.

It was a top candidate for removal. However, I never got the opportunity to achieve this (a.k.a. not the priority). I did not even have the opportunity to try to delete it in a weekly refreshed dev database.

I was wondering whether a simple TRUNCATE or DROP could ever be very time-consuming? I mean, could this operation block a production environment for several minute like DELETEs can when they are not well written. Or are these safe operations?


texts: Changing metadata changes query output issue

texts:
Recently stumbled across the following problem: changing metadata changes query output.

Here is how to get it.

CREATE TABLE [dbo].[prod]
(
[ID] [tinyint] NOT NULL,
[values] [tinyint] NOT NULL
)
GO

The following query always returns ‘This is the end’ (error always raised). Also the filter should be some sort of a random generator.

BEGIN TRY
SELECT [id] FROM
        (
        SELECT [id] as [id]
        FROM dbo.prod
        WHERE ([id]=1/0)
        ) t1
WHERE
-- Random generator 1 always returns false
(FLOOR(RAND()*(1))=1)
-- Random generator 2 does not always return false
/*
SUBSTRING( cast(NEWID() as varchar(max)), 1, 1)
IN ('1', '2', '3', '4', '5', '6', '7', '8', '9', '0', 'A', 'B', 'C', 'D', 'E', 'F')
*/
-- Random generator 3 does not always return false
/*
SUBSTRING(cast(cast( DATEPART(MILLISECOND,GETDATE()) as char(3)) as varchar(3)), 2, 1)
IN ('1', '2', '3', '4', '5', '6', '7', '8', '9', '0')
*/
END TRY
BEGIN CATCH
SELECT 'This is the end'
END CATCH;

Now add primary key constraint. Note: no data changes.

ALTER TABLE dbo.prod
ADD CONSTRAINT PK_1 PRIMARY KEY NONCLUSTERED(id);

And now the same query

BEGIN TRY
SELECT [id] FROM
        (
        SELECT [id] as [id]
        FROM dbo.prod
        WHERE ([id]=1/0)
        ) t1
WHERE
(FLOOR(RAND()*(1))=1)
END TRY
BEGIN CATCH
SELECT 'This is the end'
END CATCH;

never returns an error.

It is understandable that changing metadata may change execution plan but it was unexpected that it changes query’s output. Is this behaviour correct?


texts: Does dbms_lob.getlength return the compressed or uncompressed length?

texts: If you have tablespace compression turned on, does dbms_lob.getlength return the compressed or uncompressed length of lobs stored on that tablespace? What about LOB compression?


texts: Using lag function in mysql to get previous values

texts:
I am trying to create MySQL dataset which has entries from previous study (surgical study with the first study having value of 1) as well for that userid. Each user can have multiple studies. I am trying to use Lag function to get previous study data. I am getting duplicate values for study 1 while trying to use the lag function.

WITH Complete_Data AS ( SELECT *
                        FROM Data
                        GROUP BY UserId, studyno
                      )
SELECT *,
       LAG(indication_of_failure, 1) OVER ( PARTITION BY UserId
                                            ORDER BY studyno ) prev_study_indication_of_failure,
       LAG(BCVA_State,1) OVER ( PARTITION BY UserId
                                ORDER BY studyno ) prev_study_BCVA
FROM Complete_Data
GROUP BY UserId, studyno;

I am expecting nulls in prev_study_indication_of_failure and prev_study_BCVA when studyno = 1 but filled with values when studyno >=2.

Due to duplicate entries for studyno = 1, I am getting values in prev_study_indication_of_failure and prev_study_BCVA

Sample Data

Expected Outcome


texts: Use BCP To Copy When Data Has Apostrophe In It

texts:
Anytime my data has an apostrophe in it, the data is automatically split into the next field (which is skewing the result set).  Let's say this is my DDL

Create Table OneTwoThree
(
    userid varchar(max)
    ,name varchar(max)
    ,data varchar(max)
    ,data1 varchar(max)
    ,data2 varchar(max)
)

Insert Into OneTwoThree Values
('bb12', 'Hi Blee', 'Drop It Like It''s Hot', '', 'Boop')

And this is the BCP command I am using

Select @sql = 'bcp "SELECT ''userid'',''name'',''data'',
''data1'',''data2''
UNION ALL SELECT CAST([userid] As VARCHAR(MAX)),
CAST([name] As VARCHAR(MAX)),CAST([data] As VARCHAR(MAX)),
CAST([data1] As VARCHAR(MAX)),
CAST([data2] As VARCHAR(MAX)) dbo.OneTwoThree" queryout "'
SET @sql = @sql + 'C:\BCPTest.csv' + '" -c -t, -T -S '+ @@servername
exec master..xp_cmdshell @sql

This syntax will place "Drop It Like It" in data and "s Hot" in data1 when the entire phrase should go in data and data1 should be blank.

How should I alter this procedure so that the hyphen does not cause improper splitting?


texts:
I have 32core CPU 204GB RAM Mysql 5.6 server, hosted on GCP VM.
I keep getting RAM high usage issues.
I have around 200+ tables, most of which are Innodb.
At any time, i see arnd 1k+ active connections to my database and most of which are sleep connections, My application runs on ROR, cant skip sleep connections
Here is my.cnf details
[mysqld]
max_allowed_packet              = 512M
max_connect_errors              = 1000000
skip_external_locking
skip_name_resolve
sql_mode                        = NO_ENGINE_SUBSTITUTION
user                            = mysql
max_binlog_size         = 500M
expire_logs_days                = 14
log_slave_updates               = TRUE
master_info_repository          = TABLE
binlog_format                   = ROW

# InnoDB Settings
default_storage_engine          = InnoDB
innodb_buffer_pool_instances    = 9
innodb_buffer_pool_size         = 160G
innodb_file_per_table           = 1
innodb_flush_log_at_trx_commit  = 0
innodb_flush_method             = O_DIRECT
innodb_log_buffer_size          = 16M
innodb_log_file_size            = 512M
innodb_stats_on_metadata        = 0

innodb_read_io_threads          = 64
innodb_write_io_threads         = 64

# MyISAM Settings
query_cache_limit               = 4M
query_cache_size                = 48M
query_cache_type                = 1

key_buffer_size                 = 48M

low_priority_updates            = 1
concurrent_insert               = 2

# Connection Settings
max_connections                 = 5000

back_log                        = 512
thread_cache_size               = 100
thread_stack                    = 192K

interactive_timeout             = 180
wait_timeout                    = 180

# Buffer Settings
join_buffer_size                = 4M
read_buffer_size                = 3M
read_rnd_buffer_size            = 4M
sort_buffer_size                = 4M

# Table Settings
table_definition_cache          = 10000
table_open_cache                = 20000
open_files_limit                = 60000

max_heap_table_size             = 256M
tmp_table_size                  = 512M

# Search Settings
ft_min_word_len                 = 3     # Minimum length of words to be indexed for search results

symbolic-links                  = 0
max_allowed_packet              = 256M


texts: Run a SQL statement in MySQL on each connection

texts:
I must be going nuts here, but I could swear I read about a configuration option in MySQL that will run the defined SQL statement on every successful connection to the database, with the cavet that it would not run for root or those with a certain privilege to prevent shutting the server down with a bad sql statement here.

Was I imagining things or do folks know of such a parameter?

I have dug though the MySQL documents and so far I'm not turning up with any success.

Note, this is NOT for the client supplied sql statements, but instead for a statement that the server itself runs regardless of who connects and for what task, and great for tracking who connects with what account if general_log was not enabled for some reason or special handling is required.


texts:
How do I show the binlog_format on a MySQL server?

And if I dont like it how do I set it to XX permanently?

Where XX is STATEMENT, ROW or MIXED.


texts: count number of unique occurrence of each value in 1st or 2nd column and print in 3rd column

texts:
SELECT
    domain_Job.statusCode as "Status Code",
    lookup_JobStatusCode.name as "Status Code Description"
    FROM
    domain_Job, domain_Client, lookup_JobStatusCode, domain_MasterServer, lookup_JobType, lookup_JobState
    WHERE
    domain_Job.type = lookup_JobType.id AND
    domain_Job.statusCode > 1 AND
    domain_Job.statusCode = lookup_JobStatusCode.id AND
    domain_Job.id = domain_Job.ParentJobID AND
    domain_Job.state = lookup_JobState.id AND
    domain_Job.clientName = domain_Client.name AND
    DATEDIFF(hour,UTCBigIntToNomTime(domain_Job.endTime), GETDATE()) <=24 AND
    domain_Job.policyName != ''
    GROUP BY domain_Job.statusCode, lookup_JobStatusCode.name, domain_MasterServer.networkName, lookup_JobType.name, lookup_JobState.name

output :

Status Code |   Status Code Description
47          | host is unreachable
7648        | -
96          | unable to allocate new media for backup, storage unit has none available
191         | no images were successfully processed
806         | this mpx group is unjoinable
47          | host is unreachable
7648        | -
156         | snapshot error encountered
20          | invalid command parameter
4234        | VMware server login failure


texts:
I want to know what is an aggregated result of an event and what to do in order to get aggregated results?

Thanks for your time!


texts: SQL Server 2012 Replication, how to ignore action with specific trigger?

texts:
SQL Server 2012. Database is in merge replication, no conflicts presented. Current example is simply for understanding, but real schema is very huge to describe. Schema and trigger at SQLFiddle for clear view.

Database has two tables Store and Debug. Store is in replication and has actual data. Debug is outside of replication and presents only on publisher (no need to debug subscribers).

Table Store has trigger which activates at every delete query and dumps current time to Debug.

While debugging some queries in trigger I realized that any query that matches the insert/update/delete in trigger are transferring to subscribers. As described above subscribers don't have the table Debug. While SQL Server Agent synchronize it catches a lot of conflicts about insert to this table:

  Invalid object name 'Subscriber_dbname.dbo.Debug'.

I don't use NOT FOR REPLICATION parameter for trigger because subscriber can delete row too and I need to catch this moment only on publisher. What can you suggest to avoid conflicts except having this table in every subscriber and creating dummy trigger for delete after insert having debug table always clean in any subscriber? Maybe there's any way to bypass insert/delete/update made from specific triggers?


texts:
I have a table in my MySQL database which contains data about XRF analyses.

There are about 50 chemical elements and for each element there are two values to be stored (Area and Count). Users need to perform queries on a specific element. The idea is that when they upload a new record, they provide a csv file and the fields are automatically filled.

My question is: is it ok to have a table with more than 100 columns? Is there a smarter way to implement this?


texts: How to check if a sequence exist in a table

texts:
Let's assume I have a table made up of 6 columns, each contains a number. Hence a row of 6 numbers makes a sequence.

Now let's say I have an array made up of 6 numbers. How can I check if some of the array exists in any one of the table sequences? I know how to check if a whole sequence exists but not how to check parts of it.

To check if the whole sequence exist, I can do something like: where ar[0] in (c1,c2,c3,c4,c5,c6) and ar[1] in (c1,c2,c3,c4,c5,c6) and ... ar[5] in (c1,c2,c3,c4,c5,c6). However, this will tell me if the whole sequence exists or not. But how do I check if a table entry only contains 4 or 5 of the parts of the sequence but not all?


texts: Does number of backup tables hinder performance

texts:
Our main database (MySQL) has about 50 tables with about 2 million records each, many of the columns are indexed.
Before doing any major change, we either copy the entire DB under a new name like "DB_MAIN_Nov112018" and just leave it there as an online backup/copy or we copy individual tables and use the same principle.
Does this hurt the performance of the database server as it has to do the indexing of the original DB as well as the many tables and databases that are used as backups?


texts: Recommended SQL Server transaction logs settings for ephemeral databases?

texts:
I'm looking for recommendations on how should I set up transaction logs for databases that are used by a development team. These databases are ephemeral in the sense that we never care about data recovery in case of hardware/software failure. Instead, every time a developer starts a task, they will create a new database and fill it with data from scratch anyway, so they'd do so as well in case of hardware failure. Another use case is automated testing, where a new database is created for each test run.
Right now, due to the usage patterns of developers (testing different types of queries, frequent data bulk-loading), the logs grow a lot and are more of a hindrance than help. We've seen cases where the logs started taking half a terabyte after only an hour of the developer's work, forcing them to manually truncate the logs. As we don't want to manually truncate the logs during automated tests, we need to allocate larger machines for them. I suspect that more I/O is required, slowing down operation.
Any recommendations I could find in both the SQL Server documentation and other materials are for production server and focus on data recovery, which is exactly the opposite of what I'm looking for.
What are some good practices regarding configuration of transaction logs of SQL Server when data recovery is irrelevant, instead ease of operation, resource usage and raw speed are a bigger concern?


texts: How to create a master key on the master database?What are the roles required to GRANT to the login and user of the master db for the same?

texts:
I am trying to create a master key on a master database of my azure sql datawarehouse. But my user does not have required permissions to do so. What are the roles need to be assigned/granted to the user or login of the azure sql datawarehouse?
I was able to grant control access using GUI of SSMS to the user of sql server instance on-prem. And also I was able to create a master key with the help of that role on master database of that sql server.But there's no direct provision to provide such access to the user on azure sql warehouse using GUI of SSMS.
Is there any script to give permissions to user of master db so that I am able to create a master key on it?If yes, then what are those scripts and their sequence?I have really tried hard to find answers to these questions. Please help me to find the answers


texts: How do I show a running total from the previous records?

texts:
I wonder if someone can please assist.
I have the following code which basically inserts 10 records into a table. From this table I'm trying to show a running total for the amount of sales against the seller. I've tried using the LAG function but that does not appear to work.
Here is the code.

CREATE TABLE Sales
(
    OrderId Int NOT NULL,
    Seller  varchar(10) NULL,
    Amount  money NULL
)

INSERT INTO Sales VALUES
    (1, 'Alex', 10.00),
    (2, 'Sarah', 15.00),
    (3, 'Tracy', 10.25),
    (4, 'Pete', 10.25),
    (5, 'Tracy', 11.00),
    (6, 'Tracy', 10.25),
    (7, 'Alex', 10.25),
    (8, 'Jane', 10.25),
    (9, 'Alex', 20.55)

SELECT Seller, Amount, LAG(Amount,1) OVER (PARTITION BY Seller ORDER BY Seller) AS RunningTotal FROM Sales

Essentially in this example I would expect the following output
Seller|Amount|Running Total
Alex|10.00|10.00
Alex|1.25|20.25
Alex|20.55|40.90
Jane|10.25|10.25
Pete|10.25|10.25
Sarah|15.00|15.00
Tracy|10.25|10.25
Tracy|11.00|21.25
Tracy|10.25|31.50

Hope someone can help and thanks in advance.
Dave


texts: Writing SQL Query Results to Event Log --- PowerShell + SQL Server 2012

texts:
I have a PowerShell script that queries a database and returns two columns. Depending on when this script is run, the query can return no rows, one row, or multiple rows. With these two columns, I write the output to the event log.

Here is the part that writes to event log:

    if($missing) {
    # foreach easch row in missing, write to the event viewer eventlog

    ForEach ($row in $missing) {

        $serNames = $row.ServerName
        $names =  $row.name

        $output =  "Following backups missing for longer than threshold:   '$serNames' - '$names'"

        Write-EventLog -ComputerName DB0000 -LogName Application -Source $source -EntryType Error -EventID 9901 -Message $output
        }
    }
    else {
        continue
    }

My question is... Is there a way to have the result table be written as my output to the event log? Currently with my foreach loop, operational DBAs are getting spammed with emails so I would like to have the table written to event viewer as opposed to a foreach writing separate event logs. I tried removing the for each and playing with formatting but I have had no luck.

EDIT: Sorry I should specify further... Currently each row is separately written to the event viewer and SCOM picks these up and sends via email an alert. Would like the full result set of server/db pairs emailed in one single alert. Thank you.


texts: Error when only deploying a dimension on SSDT

texts:
I'm trying to follow a tutorial on SQL Server Analysis Services (SSAS) but I'm having trouble on deployment.

Summarized, what I'm doing is:

Open SQL Server Data Tools (SSDT)
Creating a new Multidimensional project
Creating a Data Source from a SQL Server database (succesfully connects)
Creating a Data Source View (can succesfully explore data)

If I try to deploy now, it works.

Creating a Dimension

The deployment doesn't work.

It gives me multiple errors such as:

Error in OLE DB or ODBC
Error in OLAP processing engine: An error ocurred when dimension, with ID "Dim Location", Name "Dim Location" was being processed.
Error in OLAP processing engine: An error ocurrer when attribute "Name" from dimension "Dim Location" from database "MultidimensionalProject02" was being processed.

If instead of creating a Dimension, I create a cube (with its dimensions), it gives the same errors (errors are related with the dimensions?).

The dimension I'm using is simple. Its name is DimLocation and has two columns, LocationKey (bigint) and Name (varchar).

The fact table when creating a cube is also simple. Its name is FactConsumption and has the columns, ConsumptionKey (bigint), LocationKey (bigint) and Value (int).

How can I create these elements and sucessfully deploy?

I'm using SQL Server 2016 Developer (I also have Express installed).


texts: Using results from one query as the parameters for another

texts:
Is there a way to have a SQL query automatically update its' parameters based on the results of a previous query?

Here's the scenario I'm trying to achieve:

A report should automatically be run daily that runs the following queries (in order):

SELECT id of rows that match a certain criteria (we'll call this result set "X")
SELECT certain fields from rows that are titled "Cancelled-[X]"
UPDATE rows from [X] with information from "Cancelled-[X]"


texts: When is a row added to DBA_HIST_SQLSTAT?

texts:
I don't understand the criteria that entails an additional record in the dba_hist_sqlstat view. I have expected there to be at most one entry per sql_id and snap_id, that is, the following statement to return no record:

select
  count(*),
  snap_id,
  sql_id
from
  dba_hist_sqlstat
group by
  snap_id,
  sql_id
having
  count(*) > 1
order by
  count(*) desc;

Yet, it returns many records, indicating that it is not possible, but the normality, that the same sql statement is captured multiple times per snap_id.

Additionaly, with more than one record per sql and snap period, how do I interpret the delta columns within this view? The documentation reads:

  The delta value is the value of the statistics from the BEGIN_INTERVAL_TIME to the END_INTERVAL_TIME in the DBA_HIST_SNAPSHOT view.

This doesn't really make sense to me.

Update as per Justin's comment: this is not in a RAC environment: so even if I group by snap_id, sql_id, dbid, instance_number, the query returns multiple records per snap_id and sql_id.


texts: SQL Server Activity Monitor - Resource Waits - Logging > 1400 ms/sec, how?

texts:
I have an SQL Server 2008 R2 server. I open "Activity Monitor", and inside the "Resource Waits" category, I see that the "Logging" category is taking up more than 1400 ms/sec.

That doesn't seem to be normal, what does that mean and how do I diagnose the cause?

UPDATE:

I ran this query, suggested by Shark:

select * from sys.dm_os_wait_stats order by wait_time_ms desc

and got these results:

wait_type                         waiting_tasks_count  wait_time_ms         max_wait_time_ms     signal_wait_time_ms
--------------------------------- ------------------- -------------------- --------------------- ----------------
LCK_M_IX                          3662                 102060386            31410                233
LAZYWRITER_SLEEP                  741527               63315760             305163               28805
WRITELOG                          3531635              42147142             4142                 309360
REQUEST_FOR_DEADLOCK_SEARCH       6327                 31676553             5038                 31676553


texts: After you create an index for a table, should we see performance improve immediately? Does the application using it need to re-connect or should it just stay running as normal?


texts:
I have a SQL Server 2008 R2 database with about 60 tables and around 300 columns. I want to reset all the data and auto increments, I found a question somehow similar but the answer there is to recreate database.

Is there any way to keep the database and just reset it?


texts:
This is my first time on this  beautiful content rich site.  I have a sql server 2008 database, but am not too experienced with good relational database design.

My project is this:
I have a school that has Students.  The students offer subjects which are graded on a termly basis in a session. There is a session which means 1 year and in this session, there are 3 terms namely 1st,2nd and 3rd terms. Each student has a results/continuous assessment that are reviewed on a termly basis in one session (3 terms) in a session.
How do I design m database to encompass all these details, so that I can check results or performance per term, in the 3 terms and in a session or more than one session.

STUDENT TABLE
STUID|STUNAME|STUADDRESS

CONTINOUS ASSESSMENT TABLE
CAID| FIRST_CA(20) |SECOND_CA(20)| EXAM SCORE(60)|TOTAL SCORE|SUBJECTID|RANK|YEARID

SUBJECT TABLE
SUBJECTID |SUBJECT_NAME |SUBJECT DESCRIPTION

YEAR/SESSION/TERM TABLE
ID|YEAR|TERM|STATUS|FROM|TO

CLASS TABLE
CLASSID | CLASS_NAME | CLASS_TEACHER

DEMO QUERY

SELECT RESULT FROM CONTINUOUS ASSESSMENT WHERE STUDENT ID = ID
AND CLASS = CLASS5 AND TERM = 1ST TERM AND YEAR = 2012

OR

SELECT  RESULT FROM CONTINUOUS ASSESSMENT WHERE STUDENT ID = ID
AND CLASS = CLASS5 AND TERM = 1ST TERM, 2ND TERM ,3RD TERM AND YEAR = 2012

How do I link all these tables in order to execute this query successfully?


texts: How can I install pgtap on rds postgresql?

texts:
I am using postgresql 10.7 under AWS RDS.

I downloaded pgtap-1.0.0.zip from https://pgxn.org/dist/pgtap/ because I saw an article in AWS forums saying you can just run pgtap.sql yourself.

However, in the zip file there is no pgtap.sql but a pgtap.sql.in...

I am using a Windows client. Tried to run make in wsl but complains about "pg_config: not found" while there is a pg_config.EXE in my PATH.

It did produce a pgtap.sql but running it fails on view "tap_funky" because it tries to use a non existing column pg_catalog.pg_proc.prokind

I replaced the missing prokind column by a hard coded "f" for (normal function) and got something apparently working but it creates everything in the public schema.

I tried to run that script in a specific schema "pgtap" but it failed straight way. Is there a proper way to do this?


texts:
How does the optimizer compare different execution plans of the same query?
Let's say we have two complex and structurally different execution plans, how can I know if they are providing the same result?


texts: Should I change my old datetime columns from SQL Server 2005 to just date datatype for SQL Server 2012

texts:
My database was developed in 2005 for SQL Server 2005 version.

In that time for columns which store pure date without time we used  datetime data type.
Our database has survived up today and now we are using SQL Server 2012.

Should I consider to change my columns from datetime to just date in places where I do not need to store time?

I am afraid what will happen with indexes on columns which changing type from datetime to date, what will happen whit statistic. Why I am thinking about changing datatype is because I think that I can gain more performance with just date type, also I want to save some space ? Does exist anywhere some general guidelines about switching from datetime to date. Is there really huge differences in performances between these two types.